Apache-DBUtils实现CRUD操作、druid数据库连接池、QueryRunner

最新推荐文章于 2022-10-26 22:18:57 发布

SammeryD

最新推荐文章于 2022-10-26 22:18:57 发布

阅读量296

点赞数

分类专栏：数据库连接池文章标签：数据库 java jdbc mysql

本文链接：https://blog.csdn.net/SammeryD/article/details/108599033

版权

数据库连接池专栏收录该内容

5 篇文章 0 订阅

订阅专栏

Apache-DBUtils实现CRUD操作

1.Apache-DBUtils简介

commons-dbutils 是 Apache 组织提供的一个开源JDBC工具类库，它是对JDBC的封装，学习成本极低，并且使用dbutils能极大简化jdbc编码的工作量，同时也不会影响程序的性能。
API介绍：
- org.apache.commons.dbutils.QueryRunner
- org.apache.commons.dbutils.ResultSetHandler
- 工具类：org.apache.commons.dbutils.DBUtils

2.创建JDBCUtils.java 获取连接，关闭连接（druid数据库连接池）

package com.sammery.util;

import java.io.InputStream;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;

import javax.sql.DataSource;

import com.alibaba.druid.pool.DruidDataSourceFactory;

public class JDBCUtils {
	/*
	 * 使用DBCP数据库连接池技术获取数据库连接*/
	private static DataSource source;
	
	static {
		try {
			Properties pros = new Properties();
			InputStream is = ClassLoader.getSystemClassLoader().getResourceAsStream("druid.properties");
			pros.load(is);
			source = DruidDataSourceFactory.createDataSource(pros);
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	
	public static Connection getConnection() throws SQLException{
		return source.getConnection(); 
	}
	
	public static void closeResource(Connection connection,Statement statement){
		//关闭资源
		try {
			if(statement != null)
				statement.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		try {
			if(connection != null)
				connection.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	public static void closeResource(Connection connection,Statement statement,ResultSet rs){
		//关闭资源
		try {
			if(rs != null)
				rs.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		try {
			if(statement != null)
				statement.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		try {
			if(connection != null)
				connection.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
}

3.使用QueryRunner测试添加数据操作、查询操作（BeanHandler、BeanListHandler、MapHandler、MapListHandler、ScalarHandler、自定义ResultSetHandler的实现类）

package com.sammery.dbutils;

import java.sql.Connection;
import java.sql.Date;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.MapHandler;
import org.apache.commons.dbutils.handlers.MapListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import org.junit.Test;

import com.sammery.bean.Customer;
import com.sammery.util.JDBCUtils;

public class QueryRunnerTest {
	 
	//使用QueryRunner测试添加数据操作(增删改操作相同)
	@Test
	public void testInsert() {
		Connection conn = null;
		try {
			QueryRunner runner = new QueryRunner();
			conn = JDBCUtils.getConnection();
			String sql = "INSERT INTO customers(`name`,email,birth) VALUES(?,?,?)";
			int insertCount = runner.update(conn, sql, "哪吒","nz@163.com","1000-01-01");
			System.out.println("添加了"+insertCount+"条数据！");
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			JDBCUtils.closeResource(conn, null);
		}
	}
	/* 九月 11, 2020 3:18:07 下午 com.alibaba.druid.pool.DruidDataSource info
	 * 信息: {dataSource-1} inited
	 * 添加了1条数据！*/
	
	
	
	
	//使用QueryRunner测试查询数据操作
	
	/*
	 * BeanHandler:是ResultSetHandler接口的实现类，用于封装表中的一条记录。
	 */
	@Test
	public void testQuery1() {
		Connection conn = null;
		try {
			QueryRunner runner = new QueryRunner();
			conn = JDBCUtils.getConnection();
			String sql = "SELECT id,`name`,email,birth FROM customers WHERE id = ?";
			BeanHandler<Customer> handler = new BeanHandler<>(Customer.class);
			Customer customer = runner.query(conn, sql, handler, 1);
			System.out.println(customer);
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			JDBCUtils.closeResource(conn, null);
		}
	}
	/*
	 * 九月 11, 2020 3:19:54 下午 com.alibaba.druid.pool.DruidDataSource info
	 * 信息: {dataSource-1} inited
	 * Customer [id=1, name=汪峰, email=wf@126.com, birth=2010-02-02]*/


	
	
	/*
	 * BeanListHandler:是ResultSetHandler接口的实现类，用于封装表中的多条记录构成的集合。
	 */
	@Test
	public void testQuery2() {
		Connection conn = null;
		try {
			QueryRunner runner = new QueryRunner();
			conn = JDBCUtils.getConnection();
			String sql = "SELECT id,`name`,email,birth FROM customers WHERE id < ?";
			BeanListHandler<Customer> handler = new BeanListHandler<>(Customer.class);
			
			List<Customer> list = runner.query(conn, sql, handler, 5);
			for (Customer a:list) {   //本人jdk版本为1.7用不了jdk1.8的list.forEach(System.out::println);
				System.out.println(a);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			JDBCUtils.closeResource(conn, null);
		}
	}
	/*九月 11, 2020 3:21:03 下午 com.alibaba.druid.pool.DruidDataSource info
	 *信息: {dataSource-1} inited
	 *Customer [id=1, name=汪峰, email=wf@126.com, birth=2010-02-02]
	 *Customer [id=2, name=王菲, email=wangf@163.com, birth=1988-12-26]
	 *Customer [id=3, name=林志玲, email=linzl@gmail.com, birth=1984-06-12]
	 *Customer [id=4, name=汤唯, email=tangw@sina.com, birth=1986-06-13]*/
	
	
	
	
	/*
	 * MapHandler:是ResultSetHandler接口的实现类，对应表中的一条记录。
	 * 将字段及相应字段的值作为map中的key和value
	 */
	@Test
	public void testQuery3() {
		Connection conn = null;
		try {
			QueryRunner runner = new QueryRunner();
			conn = JDBCUtils.getConnection();
			String sql = "SELECT id,`name`,email,birth FROM customers WHERE id = ?";
			MapHandler handler = new MapHandler();
			Map<String, Object> map = runner.query(conn, sql, handler, 1);
			System.out.println(map);
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			JDBCUtils.closeResource(conn, null);
		}
	}
	/*九月 11, 2020 3:22:05 下午 com.alibaba.druid.pool.DruidDataSource info
	 *信息: {dataSource-1} inited
	 *{name=汪峰, birth=2010-02-02, id=1, email=wf@126.com}*/
	
	
	
	/*
	 * MapListHandler:是ResultSetHandler接口的实现类，对应表中的多条记录。
	 * 将字段及相应字段的值作为map中的key和value，并将map添加到list中。
	 */
	@Test
	public void testQuery4() {
		Connection conn = null;
		try {
			QueryRunner runner = new QueryRunner();
			conn = JDBCUtils.getConnection();
			String sql = "SELECT id,`name`,email,birth FROM customers WHERE id < ?";
			MapListHandler handler = new MapListHandler();
			List<Map<String, Object>> list = runner.query(conn, sql, handler, 4);
			for (Map<String, Object> a:list) { 
				System.out.println(a);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			JDBCUtils.closeResource(conn, null);
		}
	}
	/*{name=汪峰, birth=2010-02-02, id=1, email=wf@126.com}
	 *{name=王菲, birth=1988-12-26, id=2, email=wangf@163.com}
	 *{name=林志玲, birth=1984-06-12, id=3, email=linzl@gmail.com}*/
	
	
	
	/*
	 * ScalarHandler:是ResultSetHandler接口的实现类，用于查询表中的特殊值。
	 */
	@Test
	public void testQuery5() {
		Connection conn = null;
		try {
			QueryRunner runner = new QueryRunner();
			conn = JDBCUtils.getConnection();
			String sql = "SELECT count(*) FROM customers";
			ScalarHandler handler = new ScalarHandler();
			Long count = (Long) runner.query(conn, sql, handler);
			System.out.println(count);
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			JDBCUtils.closeResource(conn, null);
		}
	}
	/* 九月 11, 2020 3:27:32 下午 com.alibaba.druid.pool.DruidDataSource info
	 * 信息: {dataSource-1} inited
	 * 16*/
	
	@Test
	public void testQuery6() {
		Connection conn = null;
		try {
			QueryRunner runner = new QueryRunner();
			conn = JDBCUtils.getConnection();
			String sql = "SELECT max(birth) FROM customers";
			ScalarHandler handler = new ScalarHandler();
			Date maxBirth = (Date) runner.query(conn, sql, handler);
			System.out.println(maxBirth);
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			JDBCUtils.closeResource(conn, null);
		}
	}
	/* 九月 11, 2020 3:27:32 下午 com.alibaba.druid.pool.DruidDataSource info
	 * 信息: {dataSource-1} inited
	 * 2014-01-17*/
	
	
	/*
	 * 自定义ResultSetHandler的实现类*/
	@Test
	public void testQuery7() {
		Connection conn = null;
		try {
			QueryRunner runner = new QueryRunner();
			conn = JDBCUtils.getConnection();
			String sql = "SELECT id,`name`,email,birth FROM customers WHERE id = ?";
			ResultSetHandler<Customer> handler = new ResultSetHandler<Customer>() {
				
				@Override
				public Customer handle(ResultSet rs) throws SQLException {
//					return null; //输出为null
					
//					return new Customer(100,"蜘蛛侠","zzx@163.com",new Date(2341223411234L));
//						输出 ：Customer [id=100, name=蜘蛛侠, email=zzx@163.com, birth=2044-03-10]
					
					if(rs.next()){
						int id = rs.getInt("id");
						String name = rs.getString("name");
						String email = rs.getString("email");
						Date birth = rs.getDate("birth");
						return new Customer(id, name, email, birth);
//						输出 ： Customer [id=10, name=周杰伦, email=zhoujl@sina.com, birth=1979-11-15]
					}
					return null;
				}
			};
			Customer customer = runner.query(conn, sql, handler,10);
			System.out.println(customer);
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			JDBCUtils.closeResource(conn, null);
		}
	}
}

4.DBUtils类关闭资源操作

package com.sammery.util;

import java.io.InputStream;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;

import javax.sql.DataSource;

import org.apache.commons.dbutils.DbUtils;

import com.alibaba.druid.pool.DruidDataSourceFactory;

public class JDBCUtils {

	/*
	 * 使用DBCP数据库连接池技术获取数据库连接*/
	private static DataSource source;
	
	static {
		try {
			Properties pros = new Properties();
			InputStream is = ClassLoader.getSystemClassLoader().getResourceAsStream("druid.properties");
			pros.load(is);
			source = DruidDataSourceFactory.createDataSource(pros);
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	
	public static Connection getConnection() throws SQLException{
		return source.getConnection(); 
	}
	
	public static void closeResource1(Connection connection,Statement statement){
		//关闭资源
		try {
			if(statement != null)
				statement.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		try {
			if(connection != null)
				connection.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	public static void closeResource1(Connection connection,Statement statement,ResultSet rs){
		//关闭资源
		try {
			if(rs != null)
				rs.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		try {
			if(statement != null)
				statement.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		try {
			if(connection != null)
				connection.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	/*使用DBUtils.jar中提供的DBUtils工具类，实现资源的关闭*/
	public static void closeResource(Connection conn,Statement ps,ResultSet rs){
//		try {
//			DbUtils.close(rs);
//		} catch (SQLException e) {
//			e.printStackTrace();
//		}
//		try {
//			DbUtils.close(ps);
//		} catch (SQLException e) {
//			e.printStackTrace();
//		}
//		try {
//			DbUtils.close(conn);
//		} catch (SQLException e) {
//			e.printStackTrace();
//		}
		
		DbUtils.closeQuietly(rs);
		DbUtils.closeQuietly(ps);
		DbUtils.closeQuietly(conn);
	}
}

SammeryD

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
Apache-DBUtils实现CRUD操作、druid数据库连接池、QueryRunner

Apache-DBUtils实现CRUD操作1.Apache-DBUtils简介commons-dbutils 是 Apache 组织提供的一个开源JDBC工具类库，它是对JDBC的封装，学习成本极低，并且使用dbutils能极大简化jdbc编码的工作量，同时也不会影响程序的性能。API介绍：org.apache.commons.dbutils.QueryRunnerorg.apache.commons.dbutils.ResultSetHandler工具类：org.apache.commo
复制链接

扫一扫