Java基礎開發(fā)之JDBC操作數(shù)據(jù)庫增刪改查，分頁查詢實例詳解

更新時間：2020年02月19日 15:51:24 作者：spaceAt

這篇文章主要介紹了Java基礎開發(fā)之JDBC操作數(shù)據(jù)庫增刪改查，分頁查詢實例詳解,需要的朋友可以參考下

對數(shù)據(jù)庫的操作無非就是增刪改查，其中數(shù)查詢操作最為復雜，所以將查詢單獨講解，我這里用的Mysql數(shù)據(jù)庫

增刪改查操作

分頁查詢操作

1.查詢結(jié)果以list返回

2.查詢結(jié)果以jsonArray返回

3.查詢總記錄條數(shù)

先看一下相關(guān)的配置信息

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";
/** 分頁查詢默認每頁記錄條數(shù) */
public static final int PAGE_SIZE_DEFAULT = 10;

增刪改操作

獲取數(shù)據(jù)庫連接對象

/**
   * 獲取數(shù)據(jù)庫連接
   * @return 數(shù)據(jù)庫連接對象
   */
  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 執(zhí)行的sql語句
   *      例如：新增語句 "insert into user (name,sex) values (?,?)";
   *       刪除語句  "delete from user where id=?";
   *       修改語句  "update user set name=?,sex=? where id=? and sex=?";
   * @param values 對應的參數(shù)值
   * @return 影響條數(shù)，-1為異常
   */
  private int execute(String sql,Object... values){
    Connection conn = null;
    PreparedStatement pStmt = null;
    try {
      conn = this.getConnection(conn);
      pStmt = conn.prepareStatement(sql);
      //設置參數(shù)
      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;
  }

調(diào)用方法

//新增
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);
  }

查詢操作

1.查詢結(jié)果以list返回

/**
   * 查詢結(jié)果以list返回
   * @param pageIndex 頁數(shù)
   * @param pageSize 每頁記錄條數(shù)
   * @param attachTableName 在結(jié)果集中是否給key值附加表名，例如：user.id,與id
   * @param sql 查詢語句 例如："select u.id,u.name,u.sex,u.depart_id AS departId,d.* from user u,depart d where u.depart_id=d.id"
   * @param values
   * @throws SQLException 
   */
  private List<Map<String, Object>> queryList(int pageIndex,int pageSize,boolean attachTableName,String sql,Object... values) throws SQLException{
    Connection conn = null;
    PreparedStatement pStmt = null;
    List<Map<String, Object>> dataList = null;
    //校驗參數(shù)
    if(pageIndex <= 0){
      pageIndex = 1;
    }
    if(pageSize <= 0){
      pageSize = Config.PAGE_SIZE_DEFAULT;
    }
    conn = this.getConnection(conn);
    pStmt = conn.prepareStatement(sql);
    //設置參數(shù)
    if(pStmt != null && values != null && values.length > 0){
      for (int i = 0; i < values.length; i++) {
        pStmt.setObject(i+1, values[i]);
      }
    }
    //設置最大查詢到第幾條記錄
    pStmt.setMaxRows(pageIndex*pageSize);
    ResultSet rs = pStmt.executeQuery();
    //游標移動到要輸出的第一條記錄
    rs.relative((pageIndex-1)*pageSize);
    if(rs != null){
      try {
        dataList = new ArrayList<Map<String,Object>>();
        ResultSetMetaData md = rs.getMetaData(); //得到結(jié)果集(rs)的結(jié)構(gòu)信息，比如字段數(shù)、字段名等 
        //遍歷結(jié)果集
        while(rs.next()){
          Map<String, Object> map = new LinkedHashMap();
          for (int i = 1; i <= md.getColumnCount(); i++) {
            map.put(attachTableName?(formatTableName(md.getTableName(i))+"."+md.getColumnLabel(i)):(md.getColumnLabel(i)), rs.getObject(i));
          }
          dataList.add(map);
        }
      }finally{
        if(rs != null){
          rs.close();
        }
        if(pStmt != null){
          pStmt.close();
        }
        if (conn != null) {
          this.closeConnection(conn);
        }
      }
    }
    return dataList;
  }

調(diào)用list查詢

public static void queryList(){
    String sql = "select u.*,d.* from user u,depart d where u.depart_id=d.id";
    try {
      List<Map<String, Object>> dataList = baseImp.queryForListAttachTableName(2,2,sql, null);
//     List<Map<String, Object>> dataList = baseImp.queryForList(2,2,sql, null);
      for (Map<String, Object> map : dataList) {
        for (String key : map.keySet()) {
          System.out.print(key+"="+map.get(key)+" ");
        }
        System.out.println();
      }
    } catch (SQLException e) {
      e.printStackTrace();
    }
  }

結(jié)果

2.查詢結(jié)果以jsonArray返回

/**
   * 查詢結(jié)果以ArrayList返回
   * @param 在結(jié)果集中是否給key值附加表名，例如：user.id,與id
   * @param sql 查詢語句 例如："select u.id,u.name,u.sex,u.depart_id AS departId,d.* from user u,depart d where u.depart_id=d.id"
   * @param values
   * @throws SQLException 
   */
  private JSONArray queryJsonArray(int pageIndex,int pageSize,boolean attachTableName,String sql,Object... values) throws SQLException{
    JSONArray jsonArray = null;
    Connection conn = null;
    PreparedStatement pStmt = null;
    //校驗參數(shù)
    if(pageIndex <= 0){
      pageIndex = 1;
    }
    if(pageSize <= 0){
      pageSize = Config.PAGE_SIZE_DEFAULT;
    }
    conn = this.getConnection(conn);
    pStmt = conn.prepareStatement(sql);
    //設置參數(shù)
    if(pStmt != null && values != null && values.length > 0){
      for (int i = 0; i < values.length; i++) {
        pStmt.setObject(i+1, values[i]);
      }
    }
    //設置最大查詢到第幾條記錄
    pStmt.setMaxRows(pageIndex*pageSize);
    ResultSet rs = pStmt.executeQuery();
    //游標移動到要輸出的第一條記錄
    rs.relative((pageIndex-1)*pageSize);
    if(rs != null){
      try {
        jsonArray = new JSONArray();
        ResultSetMetaData md = rs.getMetaData(); //得到結(jié)果集(rs)的結(jié)構(gòu)信息，比如字段數(shù)、字段名等 
        //遍歷結(jié)果集
        while(rs.next()){
          JSONObject jsonObject = new JSONObject();
          for (int i = 1; i <= md.getColumnCount(); i++) {
            jsonObject.put(attachTableName?(formatTableName(md.getTableName(i))+"."+md.getColumnLabel(i)):(md.getColumnLabel(i)), rs.getObject(i)+"");
          }
          jsonArray.add(jsonObject);
        }
        }finally{
          if(rs != null){
            rs.close();
          }
          if(pStmt != null){
            pStmt.close();
          }
          if (conn != null) {
            this.closeConnection(conn);
          }
        }
    }
    return jsonArray;
  }

調(diào)用jsonArray查詢

public static void queryJsonArray(){
//   String sql = "select * from user u";
    String sql = "select u.*,d.* from user u,depart d where u.depart_id=d.id";
//   String sql = "select u.id AS uid,u.name,u.sex,u.depart_id AS departId,d.name from user u,depart d where u.depart_id=d.id";
//   String sql = "select u.id,u.name,u.sex,u.depart_id AS departId,d.* from user u,depart d where u.depart_id=d.id";
    try {
      JSONArray jsonArray = baseImp.queryForJsonArrayAttachTableName(2,2,sql, null);
//     JSONArray jsonArray = baseImp.queryForJsonArray(sql, null);
      System.out.println(jsonArray.toString());
      for (int i = 0; i < jsonArray.size(); i++) {
        JSONObject jsonObject = jsonArray.getJSONObject(i);
        Iterator<?> iterator = jsonObject.keys();
        Object key = null;
        while (iterator.hasNext()) {
          key = iterator.next();
          System.out.print(key+" "+jsonObject.get(key)+" ");
        }
        System.out.println();
      }
    } catch (SQLException e) {
      e.printStackTrace();
    }
  }

結(jié)果

[{"user.id":"4","user.name":"王五","user.sex":"0","user.depart_id":"3","depart.id":"3","depart.name":"研發(fā)部","depart.desc":"這是研發(fā)部"},{"user.id":"5","user.name":"趙六","user.sex":"1","user.depart_id":"1","depart.id":"1","depart.name":"測試部","depart.desc":"這是測試部"}]
user.id 4 user.name 王五 user.sex 0 user.depart_id 3 depart.id 3 depart.name 研發(fā)部 depart.desc 這是研發(fā)部 
user.id 5 user.name 趙六 user.sex 1 user.depart_id 1 depart.id 1 depart.name 測試部 depart.desc 這是測試部

3.查詢總記錄條數(shù)

/**
   * 查詢記錄條數(shù)
   * @param sql 例如："select count(*) from user where xxx"
   * @param values
   * @throws SQLException 
   */
  public int queryCount(String sql,Object... values) throws SQLException{
    int count = -1;
    Connection conn = null;
    PreparedStatement pStmt = null;
    conn = this.getConnection(conn);
    pStmt = conn.prepareStatement(sql);
    //設置參數(shù)
    if(pStmt != null && values != null && values.length > 0){
      for (int i = 0; i < values.length; i++) {
        pStmt.setObject(i+1, values[i]);
      }
    }
    ResultSet rs = pStmt.executeQuery();
    if(rs != null){
      try {
        while(rs.next()){
          count = rs.getInt(1);
        }
      }finally{
        if(rs != null){
          rs.close();
        }
        if(pStmt != null){
          pStmt.close();
        }
        if (conn != null) {
          this.closeConnection(conn);
        }
      }
    }
    return count;
  }

調(diào)用查詢總記錄條數(shù)

public static void queryCount(){
    String sql = "select count(*) from user u";
    try {
      System.out.println("count="+baseImp.queryCount(sql, null));
    } catch (SQLException e) {
      e.printStackTrace();
    }
  }

結(jié)果