DBUtils工具包
DBUtils是Apache组织提供的一个开源JDBC工具类库,它是对JDBC的简单封装,学习成本极低,并且使用dbutils能极大简化JDBC编码的工作量,同时也不会影响程序的性能。
QueryRunner类简单化了SQL查询,它与ResultSetHandler接口组合在一起使用可以完成大部分的数据库操作,能够大大减少编码量。ResultSetHandler接口提供将数据按要求转换为另一种形式。
一、常用实现类
1、BeanHandler
将结果集中的第一行数据封装到一个对应的JavaBean实例中
例如:
@Test
public void testBeanHandler() {
QueryRunner queryRunner = new QueryRunner();
Connection conn = null;
try {
conn = JdbcUtils.getConnectionForC3P0();
String sql = "select username,password,register_time,sex,user_role,id_card from user where username=?";
User user = queryRunner.query(conn, sql, new BeanHandler<>(User.class), "小王");
System.out.println(user);
} catch (Exception e) {
e.printStackTrace();
} finally {
if (conn != null) {
JdbcUtils.release(null, conn, null);
}
}
}
2、BeanListHandler
将结果集中的每一行数据都封装到一个对应的JavaBean实例中,存放到List里
例如:
https://blog.csdn.net/sturgsslecofwe/article/details/97937891
https://blog.csdn.net/sturgsslecofwe/article/details/97938152
https://blog.csdn.net/sturgsslecofwe/article/details/97938462
@Test
public void testBeanListHandler() {
QueryRunner queryRunner = new QueryRunner();
Connection conn = null;
try {
conn = JdbcUtils.getConnectionForC3P0();
String sql = "select username,password,register_time,sex,user_role,id_card from user";
List<User> user = queryRunner.query(conn, sql, new BeanListHandler<>(User.class));
System.out.println(user);
} catch (Exception e) {
e.printStackTrace();
} finally {
if (conn != null) {
JdbcUtils.release(null, conn, null);
}
}
}
3、MapHandler
将结果集中的第一行数据封装为一个Map
例如:
@Test
public void testMapHandler() {
QueryRunner queryRunner = new QueryRunner();
Connection conn = null;
try {
conn = JdbcUtils.getConnectionForC3P0();
String sql = "select username,password,register_time,sex,user_role,id_card from user where username=?";
Map<String, Object> user = queryRunner.query(conn, sql, new MapHandler(), "小王");
System.out.println(user);
} catch (Exception e) {
e.printStackTrace();
} finally {
if (conn != null) {
JdbcUtils.release(null, conn, null);
}
}
}
4、MapListHandler
将结果集中的每一行数据都封装到一个Map里,然后再存放到List
例如:
@Test
public void testMapListHandler() {
QueryRunner queryRunner = new QueryRunner();
Connection conn = null;
try {
conn = JdbcUtils.getConnectionForC3P0();
String sql = "select username,password,register_time,sex,user_role,id_card from user";
List<Map<String, Object>> user = queryRunner.query(conn, sql, new MapListHandler());
System.out.println(user);
} catch (Exception e) {
e.printStackTrace();
} finally {
if (conn != null) {
JdbcUtils.release(null, conn, null);
}
}
}
https://www.qichamao.com/person/ae5d6ef1a95b8080d088a1a72f8f93427fe781fbde2bcda89ba77c83920e98f1-2a6e52ffdd387b1510b9e27362ee9011
https://www.qichamao.com/person/0174bbc9c8ceb7ff61d86b77ceb647444a4820559f8433e73343d31d43da85e2448972c40050321b48cec764bc127cf3-2a6e52ffdd387b1510b9e27362ee9011
https://www.qichamao.com/person/09c6e9fdca1fe252cef0170975bf64735f407a6a50d3a2484bec761f3a676a0c-2a6e52ffdd387b1510b9e27362ee9011
https://www.qichamao.com/person/db0815df08d01fb046885eea1e7e9454d4a0ca943093f7462996226488058b2a-2a6e52ffdd387b1510b9e27362ee9011
https://www.qichamao.com/person/067122cc9eecb36566564661a16209cfa576d8391120b692f061742720e94fd7-2a6e52ffdd387b1510b9e27362ee9011
https://www.qichamao.com/person/067122cc9eecb36566564661a16209cfa576d8391120b692f061742720e94fd7-2a6e52ffdd387b1510b9e27362ee9011
https://www.qichamao.com/person/dbca3542d584daec1f906edfdb5ab48efb6be2c69a954f6085a8da20e823515a-2a6e52ffdd387b1510b9e27362ee9011
https://www.qichamao.com/person/fab26fb2ea7b7de984281b8910f0c519ec65f9506bb909242695aa902592e941-2a6e52ffdd387b1510b9e27362ee9011
https://www.qichamao.com/person/24acd363d63f35495b5e64d79cb73344f08d9180eb14fd821622f9d742685248-2a6e52ffdd387b1510b9e27362ee9011
https://www.qichamao.com/person/986cec2686222f944a10be50bbc2f50b07edfa49369444af8483df45109cafe0-2a6e52ffdd387b1510b9e27362ee9011
https://www.qichamao.com/person/64196456d95df49043296065bbe42dcc703ec23b700e63584c8331d1c31ed815-2a6e52ffdd387b1510b9e27362ee9011
https://www.qichamao.com/person/1ba8d4a1119f1cf3e72c2cf9beea047f084fac403f2e65dbf515d819baa60f96-2a6e52ffdd387b1510b9e27362ee9011
https://www.qichamao.com/person/46cc5f283d77ded378404a3c515148cd261984118dffd8a936b3056fe8e9b742-2a6e52ffdd387b1510b9e27362ee9011
https://www.qichamao.com/person/cc7ca0a193d381bffb7332d37c575340d06ebae58cf845cb19efd9731633c9fb-2a6e52ffdd387b1510b9e27362ee9011
https://www.qichamao.com/person/07299de7123548c74e04cd4f1f2b88def83c51ced62385ae3b4fdd91d44a5412-2a6e52ffdd387b1510b9e27362ee9011
https://www.qichamao.com/person/196abe3e294b2eb6bd1bded230d7c705fdf1e12dbd717af3cdead6fb3b7a4d68-2a6e52ffdd387b1510b9e27362ee9011
https://www.qichamao.com/person/7f618de477dbdb1c5a7c6308ad32007078daebc7a63526da67af9cd41a49b23e-2a6e52ffdd387b1510b9e27362ee9011
https://www.qichamao.com/person/d4e920a0658c35b0b16378fa88aeb592e79eb8e807304943c9d609b4c931418c-2a6e52ffdd387b1510b9e27362ee9011
https://www.qichamao.com/person/9a592e5271f9663559ec37a154922735bb6af65040a07dfdb155e123065a5e58-2a6e52ffdd387b1510b9e27362ee9011
https://www.qichamao.com/person/5021756fb0ea38620b7f0b158591e5690372f08ca5c2e3feb6a57b94ba5b8b5f-2a6e52ffdd387b1510b9e27362ee9011
https://www.qichamao.com/person/6a11e418ae89a0e29a9d0af13750a888079e7905eaa22563fce3b3834671c5214a36eaa1aee60eceb9b59c32235bb677-2a6e52ffdd387b1510b9e27362ee9011
https://www.qichamao.com/person/d94db413ea8f375779f823a9e3019eda1b5ccb9d30d85b71e8cf77ba00a1c70e-2a6e52ffdd387b1510b9e27362ee9011
https://www.qichamao.com/person/670123cb53cb5810cc34ccb7533a154d8bcf6012a894805a99333188d51de39d-2a6e52ffdd387b1510b9e27362ee9011
https://www.qichamao.com/person/edca17131b00544b224bd45bbbf31e1e12e74bbae9d3d5ec521f2487194f6693-2a6e52ffdd387b1510b9e27362ee9011
https://www.qichamao.com/person/7121c569c83c80452e9239bca59a4a413ab4a0256c5a6de63c7db91e74ce165e-2a6e52ffdd387b1510b9e27362ee9011
5、ScalarHandler
将结果集中的单个值返回
例如:
@Test
public void testScalarHandler2() {
QueryRunner queryRunner = new QueryRunner();
Connection conn = null;
try {
conn = JdbcUtils.getConnectionForC3P0();
String sql = "select register_time from user where id_card=?";
Object register_time = queryRunner.query(conn, sql, new ScalarHandler(), "321...");
System.out.println(register_time);
} catch (Exception e) {
e.printStackTrace();
} finally {
if (conn != null) {
JdbcUtils.release(null, conn, null);
}
}
}
6、需注意的坑
通过BeanHandler和BeanListHandler得到的结果集会有不存在(NULL)的情况,比如这种情况下就会导致NULL,图:
出现这种情况的原因是:实体类(User)中的对应属性名与数据库的字段(列名)无法匹配,数据库的数据如下图:
其中register_time、user_role、id_card与类变量registerTime、userRole、idCard无法对应赋值,这也是BeanHandler的一种缺陷,而其它如MapHandler、ScalarHandler就不会出现这种情况。
原因是:MapHandler、ScalarHandler的底层运用了反射来获取元数据的列名,所以它可以获取的到结果。
作者:威威喵
来源:CSDN
原文:https://blog.csdn.net/smile_Running/article/details/87009755
版权声明:本文为博主原创文章,转载请附上博文链接!