DBUtils及数据库连接池(DBCP、C3P0)
这里我们会用到apache DBUtils工具类,具体使用文档请移步官网
Apache DBUtils
Maven依赖
<!-- https://mvnrepository.com/artifact/commons-dbutils/commons-dbutils -->
<dependency>
<groupId>commons-dbutils</groupId>
<artifactId>commons-dbutils</artifactId>
<version>1.7</version>
</dependency>
Apache DBUtils实际上就是对jdbc的封装,方便用户的使用,类似于我们之前的文章.
反射技术实现及代码分层
JDBC代码分层
一下是简单查询操作的使用
public static void testQuery() throws SQLException {
Connection connection = DBUtil.getConnection();
String sql = "select * from emp where empname=?";
QueryRunner queryRunner = new QueryRunner();
Emp query = queryRunner.query(connection, sql, new BeanHandler<Emp>(Emp.class), "lisi");
System.out.println(query);
}
public static void testQueryList() throws SQLException {
Connection connection = DBUtil.getConnection();
String sql = "select * from emp ";
QueryRunner queryRunner = new QueryRunner();
List<Emp> query = queryRunner.query(connection, sql, new BeanListHandler<Emp>(Emp.class));
for (Emp emp : query) {
System.out.println(emp);
}
}
public static void testArray() throws SQLException {
Connection connection = DBUtil.getConnection();
String sql = "select * from emp ";
QueryRunner queryRunner = new QueryRunner();
Object[] query = queryRunner.query(connection, sql, new ArrayHandler());
for (Object o : query) {
System.out.println(o);
}
}
```java
public static void testArrayList() throws SQLException {
Connection connection = DBUtil.getConnection();
String sql = "select * from emp ";
QueryRunner queryRunner = new QueryRunner();
List<Object[]> query = queryRunner.query(connection, sql, new ArrayListHandler());
for (Object[] objects : query) {
System.out.println(objects[0]+"--------"+objects[1]);
}
}
public static void testMap() throws SQLException {
Connection connection = DBUtil.getConnection();
String sql = "select * from emp ";
QueryRunner queryRunner = new QueryRunner();
Map<String, Object> query = queryRunner.query(connection, sql, new MapHandler());
Set<Map.Entry<String, Object>> entries = query.entrySet();
for (Map.Entry<String, Object> entry : entries) {
System.out.println(entry.getKey()+"----"+entry.getValue());
}
}
public static void testMapList() throws SQLException {
Connection connection = DBUtil.getConnection();
String sql = "select * from emp ";
QueryRunner queryRunner = new QueryRunner();
List<Map<String, Object>> query = queryRunner.query(connection, sql, new MapListHandler());
for (Map<String, Object> stringObjectMap : query) {
Set<Map.Entry<String, Object>> entries = stringObjectMap.entrySet();
for (Map.Entry<String, Object> entry : entries) {
System.out.println(entry.getKey()+"------"+entry.getValue());
}
}
}
上面是一些简单handler的使用,如果不够的话,我们可以自定义handler对象
public static void testOwnhandler() throws SQLException {
Connection connection = DBUtil.getConnection();
String sql = "select * from emp where empname=?";
QueryRunner queryRunner = new QueryRunner();
Emp query = queryRunner.query(connection, sql, new ResultSetHandler<Emp>() {
@Override
public Emp handle(ResultSet resultSet) throws SQLException {
if (resultSet.next()) {
Emp emp = new Emp();
emp.setEmpnum(resultSet.getInt("empnum"));
emp.setEmpname(resultSet.getString("empname"));
return emp;
}
return null;
}
}, "lisi");
System.out.println(query);
}
下面是一些插入操作的使用
public static void insert() throws SQLException {
Connection connection = DBUtil.getConnection();
String sql="insert into emp(empnum,empname) values(?,?)";
QueryRunner queryRunner = new QueryRunner();
queryRunner.update(connection, sql, 144, "zhaojun");
}
修改
public static void update() throws SQLException {
Connection connection = DBUtil.getConnection();
String sql="update emp set job=? where empname=?";
QueryRunner queryRunner = new QueryRunner();
queryRunner.update(connection, sql, "tester","zhaojun");
}
删除
public static void delete() throws SQLException {
Connection connection = DBUtil.getConnection();
String sql="delete from emp where empname=?";
QueryRunner queryRunner = new QueryRunner();
queryRunner.update(connection, sql, "zhaojun");
}
数据库连接池
数据库连接池的目的是为了减少频繁开关连接的时间,提高整个系统的响应能力.通过分析发现应该具备的几个属性值
- 初始大小
- 每次扩容的大小(由于现实场景中,连接会时少时多,在出现连接数过大的情况,可以动态扩容)
- 连接池的最大个数(限制动态扩容的最大数值,防止服务器压力过大)
- 空闲连接的死亡时间(回收空闲连接,释放压力)
这是设计连接池时应该要考虑的几个基础属性
目前用的比较多的数据库连接池有:
DBCP、C3P0、Durid、hikariCP
(Durid、hikariCP目前各企业用的比较多)
我们接下来讲解的是DBCP的使用,详细文档请移步至官网
DBCP官网
DBCP简单使用
public class DbcpTest {
public static void main(String[] args) {
//数据库的连接池资源,在之后操作的时候只需要从池中获取即可
BasicDataSource basicDataSource = new BasicDataSource();
basicDataSource.setDriverClassName("com.mysql.jdbc.Driver");
basicDataSource.setUrl("jdbc:mysql://localhost:3306/test");
basicDataSource.setUsername("root");
basicDataSource.setPassword("Azog095874");
Connection connection =null;
PreparedStatement preparedStatement=null;
ResultSet resultSet =null;
try {
connection = basicDataSource.getConnection();
String sql = "select * from emp";
preparedStatement = connection.prepareStatement(sql);
resultSet = preparedStatement.executeQuery();
while (resultSet.next()){
System.out.println(resultSet.getString("empname"));
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
try {
connection.close();//不会真的关闭,只是将连接放回连接池中
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
连接池也有close()方法,但我们在前后端交互的项目中一般是不会对它进行关闭的.
JDBC目前基本已经没有公司使用,我们在这做一个了解即可.
接下来我们了解C3P0的使用.详细文档请移步C3P0官网
以下是简单使用
public static void main(String[] args) throws Exception {
ComboPooledDataSource cpds = new ComboPooledDataSource();
cpds.setDriverClass( "com.mysql.jdbc.Driver" ); //loads the jdbc driver
cpds.setJdbcUrl( "jdbc:mysql://localhost:3306/test" );
cpds.setUser("root");
cpds.setPassword("AzoAAAAA");
Connection connection = cpds.getConnection();
System.out.println(connection);
connection.close();
}
在项目开发中,连接配置一般是不会写在类文件里面的,一般会将配置信息单独存放在一个配置文件里面.这样方便文件管理
c3p0的配置文件可为properties格式的,也可为xml格式的,文件里面的配置为键值对形式,且配置文件必须放在src目录下(注意:如果是maven项目的话,该配置文件需要放置在src/main/resources目录下)
这是文件里面的配置项
直接使用c3p0,配置文件会被自动加载
ComboPooledDataSource cpds = new ComboPooledDataSource();
Connection connection = cpds.getConnection();
System.out.println(connection);
ResultSet resultSet = connection.prepareStatement("select * from Emp").executeQuery();
while (resultSet.next()){
System.out.println(resultSet.getString("empname"));
}
connection.close();
接下来我们尝试使用使用xml格式的配置文件,xml文件需要被命名为c3p0-config.xml,同样也需要将其放置在src目录下(注意:如果是maven项目的话,该配置文件需要放置在src/main/resources目录下)
<?xml version="1.0" encoding="utf-8" ?>
<c3p0-config>
<default-config>
<property name="driverClass">com.mysql.jdbc.Driver</property>
<property name="jdbcUrl">jdbc:mysql://localhost:3306/test</property>
<property name="user">root</property>
<property name="password">Azog095874</property>
</default-config>
</c3p0-config>
设置好配置文件后,我们直接ComboPooledDataSource就可以直接调用配置项使用数据库连接池.
以上是第一种使用c3p0数据库连接池的方式,第二种方式是直接使用我们的工厂类.
public static void main(String[] args) throws SQLException {
//获取datasource源
DataSource dataSource = DataSources.unpooledDataSource("jdbc:mysql://localhost:3306/test","root","Azog095874");
DataSource dataSource1 = DataSources.pooledDataSource(dataSource);
Connection connection = dataSource1.getConnection();//获取连接
System.out.println(connection);
connection.close();
}
可设置连接池参数
public static void main(String[] args) throws SQLException {
//获取datasource源
DataSource dataSource = DataSources.unpooledDataSource("jdbc:mysql://localhost:3306/test","root","Azog095874");
//通过Map设置数据连接池参数
Map override = new HashMap();
override.put("maxStatements",200);
override.put("maxPoolSize",new Integer(20));
DataSource dataSource1 = DataSources.pooledDataSource(dataSource,override);
Connection connection = dataSource1.getConnection();//获取连接
System.out.println(connection);
connection.close();
}
如果我们觉得这个数据库连接池方法不好用的话,可以在c3p0的基础上封装成自己想要的方法
public static void main(String[] args) throws Exception {
// fetch a JNDI-bound DataSource
InitialContext ictx = new InitialContext();
DataSource ds = (DataSource) ictx.lookup( "java:comp/env/jdbc/myDataSource" );
// make sure it's a c3p0 PooledDataSource
if ( ds instanceof PooledDataSource)
{
PooledDataSource pds = (PooledDataSource) ds;
System.err.println("num_connections: " + pds.getNumConnectionsDefaultUser());
System.err.println("num_busy_connections: " + pds.getNumBusyConnectionsDefaultUser());
System.err.println("num_idle_connections: " + pds.getNumIdleConnectionsDefaultUser());
System.err.println();
}
else
System.err.println("Not a c3p0 PooledDataSource!");
}
c3p0数据库连接池基础配置项
acquireIncrement:单次增长量
initialPoolSize:初始池大小
maxPoolSize:池最大容量
maxIdleTime:最大空闲连接死亡时间
minPoolSize:最小池容量
c3p0依赖:
<!-- https://mvnrepository.com/artifact/c3p0/c3p0 -->
<dependency>
<groupId>c3p0</groupId>
<artifactId>c3p0</artifactId>
<version>0.9.1.2</version>
</dependency>
<!-- https://mvnrepository.com/artifact/com.mchange/c3p0 -->
<dependency>
<groupId>com.mchange</groupId>
<artifactId>c3p0</artifactId>
<version>0.9.5.5</version>
</dependency>