Java JDBC操作Mysql数据库增删改查，分页查询实例详解(源码)

江山如画 • 2020年2月19日下午7:34 • 编程开发 • 阅读 8108

配置信息

public static final String USER_NAME = "root";
public static final String PWD = "123456789";
public static final String DRIVER = "com.mysql.jdbc.Driver";
public static final String URL = "jdbc:mysql://localhost:3306/web_demon";
/** 分页查询默认每页记录条数 */
public static final int PAGE_SIZE_DEFAULT = 10;

获取数据库连接对象

**
   * 获取数据库连接
   * @return 数据库连接对象
   */
  public Connection getConnection(Connection conn) {
    if(conn == null){
      try {
        Class.forName(Config.DRIVER);
        conn = DriverManager.getConnection(Config.URL, Config.USER_NAME, Config.PWD);
      } catch (Exception e) {
        e.printStackTrace();
      }
    }
    return conn;
  }

封装增删改操作

/**
   * 新增，删除，插入操作
   * @param sql 执行的sql语句
   *      例如：新增语句 "insert into user (name,sex) values (?,?)";
   *       删除语句  "delete from user where id=?";
   *       修改语句  "update user set name=?,sex=? where id=? and sex=?";
   * @param values 对应的参数值
   * @return 影响条数，-1为异常
   */
  private int execute(String sql,Object... values){
    Connection conn = null;
    PreparedStatement pStmt = null;
    try {
      conn = this.getConnection(conn);
      pStmt = conn.prepareStatement(sql);
      //设置参数
      if(pStmt != null && values != null && values.length > 0){
        for (int i = 0; i < values.length; i++) {
          pStmt.setObject(i+1, values[i]);
        }
      }
      int i =pStmt.executeUpdate();
      return i;
    } catch (Exception e) {
      e.printStackTrace();
    } finally {
      if (conn != null) {
        this.closeConnection(conn);
      }
    }
    return -1;
  }

新增，删除，修改方法调用示例

//新增
public static void insert(){
    String sql = "insert into user (name,sex) values (?,?)";
    Object[] values = new Object[]{"李四",0};
    int res = baseImp.execute(sql, values);
    System.out.println("insert res="+res);
  }
//删除
public static void delete(){
    String sql = "delete from user where id=?";
    Object[] values = new Object[]{2};
    int res = baseImp.execute(sql, values);
    System.out.println("delete res="+res);
  }
//更新
public static void update(){
    String sql = "update user set name=?,sex=? where id=? and sex=?";
    Object[] values = new Object[]{"张三",1,1,0};
    int res = baseImp.execute(sql, values);
    System.out.println("update res="+res);
  }