DBUtils的增删改查操作及ResultSetHandler的实现类

DBUtils简介

对JDBC的简单封装，而且没有影响性能。简化JDBC编程。

DBUtils的增删改

 //增
 public static void add() {
  //创建核心类，queryrunner
  QueryRunner queryRunner = new QueryRunner(JDBCUtils.getDataSource());
  try {
   queryRunner.update("insert into test_dbutils values(null,?,?)","adada",5000);
  } catch (SQLException e) {
   e.printStackTrace();
  }
  
 }
 //改
 public static void update() {
  //创建核心类
  QueryRunner queryRunner = new QueryRunner(JDBCUtils.getDataSource());
  try {
   queryRunner.update("update test_dbutils set name=?,money=? where id=?","aaaaa",20000,4);
  } catch (SQLException e) {
   e.printStackTrace();
  }
 }
 //删
 public static void delete() {
  QueryRunner queryRunner = new QueryRunner(JDBCUtils.getDataSource());
  try {
   queryRunner.update("delete from test_dbutils where id=?",4);
  } catch (SQLException e) {
   e.printStackTrace();
  }
 }
 

DBUtils的查询操作

• 查询一条记录：
  首先创建一个实体类，用于存储所查询到的数据库信息。

//查
 public static void select() {
  QueryRunner queryRunner = new QueryRunner(JDBCUtils.getDataSource());
  Account account = null;
  try {
   account = queryRunner.query("select * from test_dbutils where id=?", new ResultSetHandler<Account>(){
    @Override
    public Account handle(ResultSet rs) throws SQLException {
     Account account = new Account();
     while(rs.next()) {
      account.setId(rs.getInt("id"));
      account.setName(rs.getString("name"));
      account.setMoney(rs.getInt("money"));
     }
     return account;
    }
    
   }, 1);
  } catch (SQLException e) {
   e.printStackTrace();
  }
  
  System.out.println(account);
 }

• 查询多条记录

//查多条
  public static void selectList() {
   QueryRunner queryRunner = new QueryRunner(JDBCUtils.getDataSource());
   List<Account> list = null;
   try {
    list = queryRunner.query("select * from test_dbutils", new ResultSetHandler<List<Account>>(){
     @Override
     public List<Account> handle(ResultSet rs) throws SQLException {
      List<Account> list = new ArrayList<Account>();
      while(rs.next()) {
       Account account = new Account();
       account.setId(rs.getInt("id"));
       account.setName(rs.getString("name"));
       account.setMoney(rs.getInt("money"));
       list.add(account);
      }
      return list;
     }
     
    });
   } catch (SQLException e) {
    e.printStackTrace();
   }
   
   for(Account account : list) {
    System.out.println(account);
   }
  }

文件目录：

ResultSetHandler的实现类

• ArrayHandler和ArrayListHandler

//ArrayHandler：将一条记录封装到一个object数组中
 public static void selectOne() throws SQLException {
  QueryRunner queryRunner = new QueryRunner(JDBCUtils.getDataSource());
  Object[] objs = queryRunner.query("select * from test_dbutils where id = ?", new ArrayHandler(), 1);
  System.out.println(Arrays.toString(objs)); 
 }
 //ArrayListHandler:将多条记录封装到一个装有object数组的list集合中
 public static void selectAll() throws SQLException {
  QueryRunner queryRunner = new QueryRunner(JDBCUtils.getDataSource());
  List<Object[]> list = queryRunner.query("select * from test_dbutils", new ArrayListHandler());
  
  for(Object[] obj:list) {
   System.out.println(Arrays.toString(obj));
  }
 }

• BeanHandler和BeanListHandler（重要）

// BeanHandler：将一条记录封装到一个JavaBean中
	public static void selectOne() throws SQLException {
		QueryRunner queryRunner = new QueryRunner(JDBCUtils.getDataSource());
		Account account = queryRunner.query("select * from test_dbutils where id = ?",
				new BeanHandler<Account>(Account.class), 1);
		System.out.println(account);
	}

	// BeanListHandler:将多条记录封装到一个装有JavaBean的list集合中
	public static void selectAll() throws SQLException {
		QueryRunner queryRunner = new QueryRunner(JDBCUtils.getDataSource());
		List<Account> list = queryRunner.query("select * from test_dbutils",
				new BeanListHandler<Account>(Account.class));

		for (Account account : list) {
			System.out.println(account);
		}
	}

• MapHandler和MapListHandler

// MapHandler：将一条记录封装到一个Map集合中，key是列名，value就是列的值
	public static void selectOne() throws SQLException {
		QueryRunner queryRunner = new QueryRunner(JDBCUtils.getDataSource());
		Map<String, Object> map = queryRunner.query("select * from test_dbutils where id = ?", new MapHandler(), 1);
		System.out.println(map);
	}

	// MapListHandler:将多条记录封装到一个装有Map的list集合中
	public static void selectAll() throws SQLException {
		QueryRunner queryRunner = new QueryRunner(JDBCUtils.getDataSource());
		List<Map<String, Object>> list = queryRunner.query("select * from test_dbutils", new MapListHandler());

		for (Map map : list) {
			System.out.println(map);
		}
	}

ColumnListHandler、ScalarHandler、KeyedHandler(了解)

//ColumnListHandler：将某列的值封装到list集合中
	public static void selectLine() throws SQLException {
		QueryRunner queryRunner = new QueryRunner(JDBCUtils.getDataSource());
		List<Object> list = (List<Object>) queryRunner.query("select name,money from test_dbutils", new ColumnListHandler("name"));

		for (Object obj : list) {
			System.out.println(obj);
		}
	}

//ScalarHandler:单值封装
	public static void selectAlone() throws SQLException {
		QueryRunner queryRunner = new QueryRunner(JDBCUtils.getDataSource());
		Object object = queryRunner.query("select count(*) from test_dbutils", new ScalarHandler());
		System.out.println(object);	
	}

KeyedHandler：将多条记录封装到一个装有Map集合的Map集合当中，而且外面Map的key是可以指定的。