JDBC 简单的增删改查 MySQL 数据库

// 实体类 User 对应 user 表，有三个字段 id，username，password
public class User {
	// id
	private Integer id;

	// 用户名
	private String username;

	// 密码
	private String password;

	public Integer getId(){
		return id;
	}

	public void setId(Integer id){
		this.id = id;
	}

	public String getUsername(){
		return username;
	}

	public void setUsername(String username){
		this.username = username;
	}

	public String getPassword(){
		return password;
	}

	public void setPassword(String password){
		this.password = password;
	}

}

一般 jdbc 操作要引用这些类：

import com.mysql.jdbc.Connection;
import com.mysql.jdbc.PreparedStatement;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;

 

获得数据库连接

// getConn
private static Connection getConn() {
    String driver = "com.mysql.jdbc.Driver";
    String url = "jdbc:mysql://localhost:3306/testDB";
    String username = "root";
    String password = "";
    Connection conn = null;

    try {
        Class.forName(driver); //classLoader, 加载对应驱动
        conn = (Connection) DriverManager.getConnection(url, username, password);
    } catch (ClassNotFoundException e) {
        e.printStackTrace();
    } catch (SQLException e) {
        e.printStackTrace();
    }
    return conn;
}

 

关闭数据库连接

	// 关闭 PreparedStatement
	public static void closeThem(PreparedStatement pstmt){
		try{
			if(pstmt != null){
				pstmt.close();
			}
		}catch(Exception e){
			e.printStackTrace();
		}
	}

	// 关闭 Connection
	public static void closeThem(Connection conn){
		try{
			if(conn != null){
				conn.close();
			}
		}catch(Exception e){
			e.printStackTrace();
		}
	}

 

插入一个对象

//
private static int insert(User user){
    Connection conn = getConn();    
    PreparedStatement pstmt = null;

    String sql = "insert into user(id, username, password) values(?,?,?)";

    int i = 0;   
    
    try{
        pstmt = (PreparedStatement)conn.prepareStatement(sql);
        pstmt.setString(1, user.getId());
        pstmt.setString(2, user.getUsername());
        pstmt.setString(3, user.getPassword());

        i = pstmt.executeUpdate();
    }catch(SQLException e){
        e.printStackTrace();
    }finally{
	closeThem(pstmt);
	closeThem(conn);
    }
    return i;
}

 

删除一个对象

private static int delete(String username){
    Connection conn = getConn();
    PreparedStatement pstmt = null;

    int i = 0;
    String sql = "delete from user where username ='" + username + "'";    

    try{
        pstmt = (PreparedStatement) conn.prepareStatement(sql);
        i = pstmt.executeUpdate();
        System.out.println("result: " + i);
       
    }catch(SQLException e){
        e.printStackTrace();
    }finally{
	closeThem(pstmt);
	closeThem(conn);
    }
    return i;
}

 

修改一条记录

private static int update(User user){
    Connection conn = getConn();
    PreparedStatement pstmt = null;
   
    int i = 0;

    String sql = "update user set username = '" + "testIt" + "' where username ='" + user.getUsername() + "'";
   
    try {
        pstmt = (PreparedStatement)conn.prepareStatement(sql);
        i = pstmt.executeUpdate();

        System.out.println("result: " + i);
    } catch (SQLException e) {
        e.printStackTrace();
    }finally{
	closeThem(pstmt);
	closeThem(conn);
    }

    return i;
}

查询全部记录

private static Integer getAll() {
    Connection conn = getConn();
    PreparedStatement pstmt = null;

    String sql = "select * from user";

    try {
        pstmt = (PreparedStatement)conn.prepareStatement(sql);
        ResultSet rs = pstmt.executeQuery();

        int col = rs.getMetaData().getColumnCount();

        System.out.println("=====");

        while (rs.next()) {
            for (int i = 1; i <= col; i++) {
                System.out.print(rs.getString(i) + " ");
                if ((i == 2) && (rs.getString(i).length() < 8)) {
                    System.out.print(" ");
                }
             }
            System.out.println();
        }

        System.out.println("=====");
    } catch (SQLException e) {
        e.printStackTrace();
    }finally{
	closeThem(pstmt);
	closeThem(conn);
    }

    return null;
}