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是可以指定的。