使用DBUtils的QueryRunner进行增删改(插入)操作
public class QueryRunnerTest {
//测试插入
@Test
public void testInsert() {
Connection conn = null;
try {
QueryRunner runner = new QueryRunner();
conn = JDBCUtils.getConnection3();
String sql = "insert into customers(name,email,birth)values(?,?,?)";
int insertCount = runner.update(conn, sql, "张靓颖", "zly@126.com", "1984-10-11");
System.out.println("添加了" + insertCount + "条记录");
} catch (Exception e) {
e.printStackTrace();
} finally {
JDBCUtils.closeResource(conn, null);
}
}
}
//输出
十二月 23, 2019 5:25:36 下午 com.mchange.v2.log.MLog <clinit>
信息: MLog clients using java 1.4+ standard logging.
十二月 23, 2019 5:25:36 下午 com.mchange.v2.c3p0.C3P0Registry banner
信息: Initializing c3p0-0.9.1.2 [built 21-May-2007 15:04:56; debug? true; trace: 10]
十二月 23, 2019 5:25:37 下午 com.alibaba.druid.pool.DruidDataSource info
信息: {dataSource-1} inited
添加了1条记录
WX20191223-173100@2x.png
使用DBUtils的QueryRunner进行查询操作
1.查询单条数据:分别用BeanHandler或MapHandler接收，BeanHandler:是ResultSetHandler接口的实现类，用于封装表中的一条记录。MapHandler:是ResultSetHandler接口的实现类，用于封装表中的一条记录。将字段及相应字段的值作为map中的key和value。
//测试查询
/*
* BeanHandler:是ResultSetHandler接口的实现类，用于封装表中的一条记录。
*/
@Test
public void testQuery() {
Connection conn = null;
try {
QueryRunner runner = new QueryRunner();
conn = JDBCUtils.getConnection3();
String sql = "select id,name,email,birth from customers where id = ?";
BeanHandler<Customer> beanHandler = new BeanHandler<Customer>(Customer.class);
Customer customer = runner.query(conn, sql, beanHandler, 9);
System.out.println(customer);
} catch (Exception e) {
e.printStackTrace();
} finally {
JDBCUtils.closeResource(conn, null);
}
}
//输出
十二月 24, 2019 5:28:53 下午 com.mchange.v2.log.MLog <clinit>
信息: MLog clients using java 1.4+ standard logging.
十二月 24, 2019 5:28:53 下午 com.mchange.v2.c3p0.C3P0Registry banner
信息: Initializing c3p0-0.9.1.2 [built 21-May-2007 15:04:56; debug? true; trace: 10]
十二月 24, 2019 5:28:54 下午 com.alibaba.druid.pool.DruidDataSource info
信息: {dataSource-1} inited
Customer{id=9, name='周杰伦', email='zhoujl@sina.com', birth=1979-11-15 00:00:00.0}
/*
* MapHandler:是ResultSetHandler接口的实现类，用于封装表中的一条记录。
* 将字段及相应字段的值作为map中的key和value
*/
@Test
public void testQuery2() {
Connection conn = null;
try {
QueryRunner runner = new QueryRunner();
conn = JDBCUtils.getConnection3();
String sql = "select id,name,email,birth from customers where id = ?";
MapHandler mapHandler = new MapHandler();
Map<String, Object> map = runner.query(conn, sql, mapHandler, 9);
System.out.println(map);
} catch (Exception e) {
e.printStackTrace();
} finally {
JDBCUtils.closeResource(conn, null);
}
}
//输出
十二月 24, 2019 5:30:52 下午 com.mchange.v2.log.MLog <clinit>
信息: MLog clients using java 1.4+ standard logging.
十二月 24, 2019 5:30:54 下午 com.mchange.v2.c3p0.C3P0Registry banner
信息: Initializing c3p0-0.9.1.2 [built 21-May-2007 15:04:56; debug? true; trace: 10]
十二月 24, 2019 5:30:54 下午 com.alibaba.druid.pool.DruidDataSource info
信息: {dataSource-1} inited
{name=周杰伦, birth=1979-11-15 00:00:00.0, id=9, email=zhoujl@sina.com}
2.查询多条数据:分别用BeanListHandler或MapListHandler接收。BeanListHandler:是ResultSetHandler接口的实现类，用于封装表中的多条记录构成的集合。MapListHandler:是ResultSetHandler接口的实现类，用于封装表中的多条记录。将字段及相应字段的值作为map中的key和value，将这些map添加到list中。
/*
* BeanListHandler:是ResultSetHandler接口的实现类，用于封装表中的多条记录构成的集合。
*/
@Test
public void testQuery1() {
Connection conn = null;
try {
QueryRunner runner = new QueryRunner();
conn = JDBCUtils.getConnection3();
String sql = "select id,name,email,birth from customers where id < ?";
BeanListHandler<Customer> beanListHandler = new BeanListHandler<Customer>(Customer.class);
List<Customer> list = runner.query(conn, sql, beanListHandler, 9);
list.forEach(System.out::println);
} catch (Exception e) {
e.printStackTrace();
} finally {
JDBCUtils.closeResource(conn, null);
}
}
//输出
十二月 24, 2019 5:34:37 下午 com.mchange.v2.log.MLog <clinit>
信息: MLog clients using java 1.4+ standard logging.
十二月 24, 2019 5:34:38 下午 com.mchange.v2.c3p0.C3P0Registry banner
信息: Initializing c3p0-0.9.1.2 [built 21-May-2007 15:04:56; debug? true; trace: 10]
十二月 24, 2019 5:34:38 下午 com.alibaba.druid.pool.DruidDataSource info
信息: {dataSource-1} inited
Customer{id=1, name='汪峰', email='wf@126.com', birth=2010-02-02 00:00:00.0}
Customer{id=2, name='王菲', email='wf@126.com', birth=1988-12-16 00:00:00.0}
Customer{id=4, name='汤唯', email='tw@126.com', birth=1986-05-19 00:00:00.0}
Customer{id=5, name='成龙', email='Jackey@gmail.com', birth=1955-07-14 00:00:00.0}
Customer{id=7, name='刘亦菲', email='liuyifei@qq.com', birth=1991-11-16 00:00:00.0}
Customer{id=8, name='陈道明', email='bdf@126.com', birth=1963-09-16 00:00:00.0}
/*
* MapListHandler:是ResultSetHandler接口的实现类，用于封装表中的多条记录。
* 将字段及相应字段的值作为map中的key和value，将这些map添加到list中
*/
@Test
public void testQuery3() {
Connection conn = null;
try {
QueryRunner runner = new QueryRunner();
conn = JDBCUtils.getConnection3();
String sql = "select id,name,email,birth from customers where id < ?";
MapListHandler mapListHandler = new MapListHandler();
List<Map<String, Object>> list = runner.query(conn, sql, mapListHandler, 9);
list.forEach(System.out::println);
} catch (Exception e) {
e.printStackTrace();
} finally {
JDBCUtils.closeResource(conn, null);
}
}
//输出
十二月 24, 2019 5:36:09 下午 com.mchange.v2.log.MLog <clinit>
信息: MLog clients using java 1.4+ standard logging.
十二月 24, 2019 5:36:09 下午 com.mchange.v2.c3p0.C3P0Registry banner
信息: Initializing c3p0-0.9.1.2 [built 21-May-2007 15:04:56; debug? true; trace: 10]
十二月 24, 2019 5:36:09 下午 com.alibaba.druid.pool.DruidDataSource info
信息: {dataSource-1} inited
{name=汪峰, birth=2010-02-02 00:00:00.0, id=1, email=wf@126.com}
{name=王菲, birth=1988-12-16 00:00:00.0, id=2, email=wf@126.com}
{name=汤唯, birth=1986-05-19 00:00:00.0, id=4, email=tw@126.com}
{name=成龙, birth=1955-07-14 00:00:00.0, id=5, email=Jackey@gmail.com}
{name=刘亦菲, birth=1991-11-16 00:00:00.0, id=7, email=liuyifei@qq.com}
{name=陈道明, birth=1963-09-16 00:00:00.0, id=8, email=bdf@126.com}
使用DBUtils的QueryRunner查询特殊值:ScalarHandler用于查询特殊值。
/*
* ScalarHandler用于查询特殊值
*/
@Test
public void testQuery4() {
Connection conn = null;
try {
QueryRunner runner = new QueryRunner();
conn = JDBCUtils.getConnection3();
String sql = "select count(*) from customers";
ScalarHandler handler = new ScalarHandler();
Long lon = (Long) runner.query(conn, sql, handler);
System.out.println(lon);
} catch (Exception e) {
e.printStackTrace();
} finally {
JDBCUtils.closeResource(conn, null);
}
}
//输出
十二月 24, 2019 5:55:16 下午 com.mchange.v2.log.MLog <clinit>
信息: MLog clients using java 1.4+ standard logging.
十二月 24, 2019 5:55:16 下午 com.mchange.v2.c3p0.C3P0Registry banner
信息: Initializing c3p0-0.9.1.2 [built 21-May-2007 15:04:56; debug? true; trace: 10]
十二月 24, 2019 5:55:16 下午 com.alibaba.druid.pool.DruidDataSource info
信息: {dataSource-1} inited
18
@Test
public void testQuery5() {
Connection conn = null;
try {
QueryRunner runner = new QueryRunner();
conn = JDBCUtils.getConnection3();
String sql = "select max(birth) from customers";
ScalarHandler handler = new ScalarHandler();
Date date = (Date) runner.query(conn, sql, handler);
System.out.println(date);
} catch (Exception e) {
e.printStackTrace();
} finally {
JDBCUtils.closeResource(conn, null);
}
}
//输出
十二月 24, 2019 5:55:51 下午 com.mchange.v2.log.MLog <clinit>
信息: MLog clients using java 1.4+ standard logging.
十二月 24, 2019 5:55:51 下午 com.mchange.v2.c3p0.C3P0Registry banner
信息: Initializing c3p0-0.9.1.2 [built 21-May-2007 15:04:56; debug? true; trace: 10]
十二月 24, 2019 5:55:52 下午 com.alibaba.druid.pool.DruidDataSource info
信息: {dataSource-1} inited
2019-12-19 00:00:00.0
自定义ResultSetHandler的实现类
/*
* 自定义ResultSetHandler的实现类
*/
@Test
public void testQuery6() {
Connection conn = null;
try {
QueryRunner runner = new QueryRunner();
conn = JDBCUtils.getConnection3();
String sql = "select id,name,email,birth from customers where id = ?";
ResultSetHandler<Customer> handler = new ResultSetHandler<Customer>() {
@Override
public Customer handle(ResultSet resultSet) throws SQLException {
if (resultSet.next()){
int id = resultSet.getInt("id");
String name = resultSet.getString("name");
String email = resultSet.getString("email");
Date date = resultSet.getDate("birth");
Customer customer = new Customer(id,name,email,date);
return customer;
}
return null;
}
};
Customer customer = runner.query(conn, sql, handler,9);
System.out.println(customer);
} catch (Exception e) {
e.printStackTrace();
} finally {
JDBCUtils.closeResource(conn, null);
}
}
//输出
十二月 24, 2019 6:10:47 下午 com.mchange.v2.log.MLog <clinit>
信息: MLog clients using java 1.4+ standard logging.
十二月 24, 2019 6:10:48 下午 com.mchange.v2.c3p0.C3P0Registry banner
信息: Initializing c3p0-0.9.1.2 [built 21-May-2007 15:04:56; debug? true; trace: 10]
十二月 24, 2019 6:10:48 下午 com.alibaba.druid.pool.DruidDataSource info
信息: {dataSource-1} inited
Customer{id=9, name='周杰伦', email='zhoujl@sina.com', birth=1979-11-15}
使用DbUtils类关闭资源
/**
* @Description: 使用dbutils.jar中提供的DbUtils工具类，实现资源的关闭
* @Param:
* @return:
* @Author: fish
* @Date: 6:25 PM 2019/12/24
*/
public static void closeResource0(Connection conn, Statement ps, ResultSet rs){
// try {
// DbUtils.close(conn);
// } catch (SQLException e) {
// e.printStackTrace();
// }
// try {
// DbUtils.close(ps);
// } catch (SQLException e) {
// e.printStackTrace();
// }
// try {
// DbUtils.close(rs);
// } catch (SQLException e) {
// e.printStackTrace();
// }
DbUtils.closeQuietly(conn);
DbUtils.closeQuietly(ps);
DbUtils.closeQuietly(rs);
}
网友评论