Java基础开发之JDBC操作数据库增删改查，分页查询实例详解

对数据库的操作无非就是增删改查，其中数查询操作最为复杂，所以将查询单独讲解，我这里用的Mysql数据库

1.查询结果以list返回

2.查询结果以jsonArray返回

3.查询总记录条数

先看一下相关的配置信息

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;

获取数据库连接对象

  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;
  }

封装增删改操作

  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);
  }

  private List> queryList(int pageIndex,int pageSize,boolean attachTableName,String sql,Object... values) throws SQLException{
    Connection conn = null;
    PreparedStatement pStmt = null;
    List> dataList = null;
    //校验参数
    if(pageIndex <= 0){
      pageIndex = 1;
    }
    if(pageSize <= 0){
      pageSize = Config.PAGE_SIZE_DEFAULT;
    }
    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]);
      }
    }
    //设置最大查询到第几条记录
    pStmt.setMaxRows(pageIndex*pageSize);
    ResultSet rs = pStmt.executeQuery();
    //游标移动到要输出的第一条记录
    rs.relative((pageIndex-1)*pageSize);
    if(rs != null){
      try {
 dataList = new ArrayList>();
 ResultSetmetaData md = rs.getmetaData(); //得到结果集(rs)的结构信息，比如字段数、字段名等 
 //遍历结果集
 while(rs.next()){
   Map 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;
  }

调用list查询

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

结果

  private JSonArray queryJsonArray(int pageIndex,int pageSize,boolean attachTableName,String sql,Object... values) throws SQLException{
    JSonArray jsonArray = null;
    Connection conn = null;
    PreparedStatement pStmt = null;
    //校验参数
    if(pageIndex <= 0){
      pageIndex = 1;
    }
    if(pageSize <= 0){
      pageSize = Config.PAGE_SIZE_DEFAULT;
    }
    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]);
      }
    }
    //设置最大查询到第几条记录
    pStmt.setMaxRows(pageIndex*pageSize);
    ResultSet rs = pStmt.executeQuery();
    //游标移动到要输出的第一条记录
    rs.relative((pageIndex-1)*pageSize);
    if(rs != null){
      try {
 jsonArray = new JSonArray();
 ResultSetmetaData md = rs.getmetaData(); //得到结果集(rs)的结构信息，比如字段数、字段名等 
 //遍历结果集
 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;
  }

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();
    }
  }

结果

[{"user.id":"4","user.name":"王五","user.sex":"0","user.depart_id":"3","depart.id":"3","depart.name":"研发部","depart.desc":"这是研发部"},{"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 研发部 depart.desc 这是研发部 
user.id 5 user.name 赵六 user.sex 1 user.depart_id 1 depart.id 1 depart.name 测试部 depart.desc 这是测试部

  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);
    //设置参数
    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;
  }

调用查询总记录条数

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();
    }
  }

结果

至此我们介绍完了Java基础开发中JDBC对数据库进行增删改查操作与分页查询，如果想了解更多关于这方面的文章大家可以查看下面的相关链接