数据库连接池介绍:
- 数据库的连接对象创建工作,比较消耗性能。
- 一开始现在内存中开辟一块空间(集合),一开始先往池子里面放置 多个连接对象。后面需要连接的话 ,直接从池子里面去。不要去创建连接了。使用完毕,要记得归还连接。确保连接对象能循环利用。
代码演示:
取名规范 连接池 一般类名 叫做MyDataSource.java。这是因为sun 公司发布的连接池规范 里面就叫DataSource.
实现 DataSource.
package com.st.web.util;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.SQLFeatureNotSupportedException;
import java.util.logging.Logger;
import javax.sql.DataSource;
public class MyDataSource implements DataSource{
@Override
public Connection getConnection() throws SQLException {
// TODO Auto-generated method stub
return null;
}
@Override
public PrintWriter getLogWriter() throws SQLException {
// TODO Auto-generated method stub
return null;
}
@Override
public int getLoginTimeout() throws SQLException {
// TODO Auto-generated method stub
return 0;
}
@Override
public Logger getParentLogger() throws SQLFeatureNotSupportedException {
// TODO Auto-generated method stub
return null;
}
@Override
public void setLogWriter(PrintWriter arg0) throws SQLException {
// TODO Auto-generated method stub
}
@Override
public void setLoginTimeout(int arg0) throws SQLException {
// TODO Auto-generated method stub
}
@Override
public boolean isWrapperFor(Class<?> arg0) throws SQLException {
// TODO Auto-generated method stub
return false;
}
@Override
public <T> T unwrap(Class<T> arg0) throws SQLException {
// TODO Auto-generated method stub
return null;
}
@Override
public Connection getConnection(String username, String password) throws SQLException {
// TODO Auto-generated method stub
return null;
}
}
整个数据库连接池大体架构:
1.开始创建十个连接
List<Connection> list=new ArrayList<Connection>();
public MyDataSource() {
for(int i=0;i<10;i++) {
Connection connection=JDBCUtil.getConn();
list.add(connection);
}
}
2.来的程序通过getConnection后去连接
//移除list里面的第一个
Connection conn=list.remove(0);
return conn;
3.用完之后,使用addBack归还连接.
public void addBack(Connection conn) {
list.add(conn);
}
4.扩容
if (list.size()==0) {
for(int i=0;i<5;i++) {
Connection connection=JDBCUtil.getConn();
list.add(connection);
}
}
完整代码:
package com.st.web.util;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.SQLFeatureNotSupportedException;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Logger;
import javax.sql.DataSource;
/*
* 这是一个数据库连接池
* 一开始往数据库连接池放十个连接
* 1.开始创建十个连接
*
* 2.来的程序通过getConnection后去连接
*
* 3.用完之后,使用addBack归还连接.
*
* 4.扩容
*
* */
public class MyDataSource implements DataSource{
List<Connection> list=new ArrayList<Connection>();
public MyDataSource() {
for(int i=0;i<10;i++) {
Connection connection=JDBCUtil.getConn();
list.add(connection);
}
}
//该连接池对外公布的获取连接的方法
@Override
public Connection getConnection() throws SQLException {
//判断过来的连接池是否用完 用完就在添加5个.
if (list.size()==0) {
for(int i=0;i<5;i++) {
Connection connection=JDBCUtil.getConn();
list.add(connection);
}
}
//移除list里面的第一个
Connection conn=list.remove(0);
return conn;
}
//退还连接
public void addBack(Connection conn) {
list.add(conn);
}
@Override
public PrintWriter getLogWriter() throws SQLException {
// TODO Auto-generated method stub
return null;
}
@Override
public int getLoginTimeout() throws SQLException {
// TODO Auto-generated method stub
return 0;
}
@Override
public Logger getParentLogger() throws SQLFeatureNotSupportedException {
// TODO Auto-generated method stub
return null;
}
@Override
public void setLogWriter(PrintWriter arg0) throws SQLException {
// TODO Auto-generated method stub
}
@Override
public void setLoginTimeout(int arg0) throws SQLException {
// TODO Auto-generated method stub
}
@Override
public boolean isWrapperFor(Class<?> arg0) throws SQLException {
// TODO Auto-generated method stub
return false;
}
@Override
public <T> T unwrap(Class<T> arg0) throws SQLException {
// TODO Auto-generated method stub
return null;
}
@Override
public Connection getConnection(String username, String password) throws SQLException {
// TODO Auto-generated method stub
return null;
}
}
数据库连接池的简单使用:
建立一个测试类DataSourceTest.java
package com.st.web.util;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import org.junit.jupiter.api.Test;
import javafx.css.PseudoClass;
public class DataSourceTest {
@Test
public void testdatasource(){
MyDataSource dataSource=new MyDataSource();
PreparedStatement ps=null;
Connection conn=null;
try {
conn=dataSource.getConnection();
String sql="insert into account values(null, 'xiaoming',500)";
ps=conn.prepareStatement(sql);
ps.executeUpdate();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
try {
//关闭资源
ps.close();
} catch (SQLException e2) {
// TODO: handle exception
e2.printStackTrace();
}
//归还连接
dataSource.addBack(conn);
}
};
}
运行结果
自定义数据库连接池出现的问题?
- 需要额外记住addBack的这个归还方法
- 单列。
- 无法面向接口编程
DataSource dataSource=new MyDataSource();
这个是因为接口里面本身就没有addBack方法 这个是自己定义的
怎么解决?
以addBack为切入点。
解决自定义数据库连接池出现的问题:
由于多了一个addBack方法,所有使用这个连接池的地方,需要额外记住这个方法。并且还不能面向接口编程。
所以 可以修改接口中的那个close方法。原来的Connection对象的close方法,是真的关闭连接。
这里也就是说修改conn.close 方法让他变成归还数据库连接池。
如何扩展某一个方法?
原有的方法逻辑,不是我们想要的。想修改自己的逻辑.
- 直接改源码 无法实现
- 继承, 必须知道这个接口的具体是谁,Connection这个是个接口 不知道具体,所以无法实现
- 使用装饰者模式。可以解决
- 动态代理。 可以解决 动态代理很灵活 所以相对来说比较复杂
装饰者模式解决:
例子:
1.现建立一个接口interface:
2.实现接口里面的方法
3.实现接口 并且装饰 让程序先走你写的方法
4.创建 装饰实例对象 实现 装饰中自定义的方法
Demo
Water.java
public interface Water {
void service();
}
Waters.java
public class Waters implements Water{
@Override
public void service() {
// TODO Auto-generated method stub
System.out.println("服务中....");
}
}
WatersWrap.java
public class WatersWrap implements Water{
Water water;
public WatersWrap(Water water) {
// TODO Auto-generated constructor stub
this.service();
}
@Override
public void service() {
// TODO Auto-generated method stub
System.out.println("微笑...");
water.service();
}
}
TestWaterWarp.java
public class TestWaterWrap {
public static void main(String[] args) {
// TODO Auto-generated method stub
/*Water water=new Waters();
water.service();*/
WatersWarp warp=new WatersWrap(new Waters());
warp.service();
}
}
结果:
所以按照这个面向接口的思想来 应该implements Connection. 并且装饰 close这个方法
新建ConnectionWrap.java
让这个类继承Connection
package com.st.web.util;
import java.sql.Array;
import java.sql.Blob;
import java.sql.CallableStatement;
import java.sql.Clob;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.NClob;
import java.sql.PreparedStatement;
import java.sql.SQLClientInfoException;
import java.sql.SQLException;
import java.sql.SQLWarning;
import java.sql.SQLXML;
import java.sql.Savepoint;
import java.sql.Statement;
import java.sql.Struct;
import java.util.List;
import java.util.Map;
import java.util.Properties;
import java.util.concurrent.Executor;
public class ConnectionWrap implements Connection{
Connection conn=null;
private List<Connection> list;
public ConnectionWrap(Connection conn,List <Connection> list) {
super();
this.conn = conn;
this.list=list;
}
@Override
public void close() throws SQLException {
// TODO Auto-generated method stub
//conn.close();
System.out.println("有人来归还连接对象了归还之前池子是:"+list.size());
list.add(conn);
System.out.println("有人来归还连接对象了归还之后池子是:"+list.size());
}
@Override
public PreparedStatement prepareStatement(String sql) throws SQLException {
// TODO Auto-generated method stub
return conn.prepareStatement(sql);
}
@Override
public boolean isWrapperFor(Class<?> arg0) throws SQLException {
// TODO Auto-generated method stub
return false;
}
@Override
public <T> T unwrap(Class<T> arg0) throws SQLException {
// TODO Auto-generated method stub
return null;
}
@Override
public void abort(Executor executor) throws SQLException {
// TODO Auto-generated method stub
}
@Override
public void clearWarnings() throws SQLException {
// TODO Auto-generated method stub
}
@Override
public void commit() throws SQLException {
// TODO Auto-generated method stub
}
@Override
public Array createArrayOf(String typeName, Object[] elements) throws SQLException {
// TODO Auto-generated method stub
return null;
}
@Override
public Blob createBlob() throws SQLException {
// TODO Auto-generated method stub
return null;
}
@Override
public Clob createClob() throws SQLException {
// TODO Auto-generated method stub
return null;
}
@Override
public NClob createNClob() throws SQLException {
// TODO Auto-generated method stub
return null;
}
@Override
public SQLXML createSQLXML() throws SQLException {
// TODO Auto-generated method stub
return null;
}
@Override
public Statement createStatement() throws SQLException {
// TODO Auto-generated method stub
return null;
}
@Override
public Statement createStatement(int resultSetType, int resultSetConcurrency) throws SQLException {
// TODO Auto-generated method stub
return null;
}
@Override
public Statement createStatement(int resultSetType, int resultSetConcurrency, int resultSetHoldability)
throws SQLException {
// TODO Auto-generated method stub
return null;
}
@Override
public Struct createStruct(String typeName, Object[] attributes) throws SQLException {
// TODO Auto-generated method stub
return null;
}
@Override
public boolean getAutoCommit() throws SQLException {
// TODO Auto-generated method stub
return false;
}
@Override
public String getCatalog() throws SQLException {
// TODO Auto-generated method stub
return null;
}
@Override
public Properties getClientInfo() throws SQLException {
// TODO Auto-generated method stub
return null;
}
@Override
public String getClientInfo(String name) throws SQLException {
// TODO Auto-generated method stub
return null;
}
@Override
public int getHoldability() throws SQLException {
// TODO Auto-generated method stub
return 0;
}
@Override
public DatabaseMetaData getMetaData() throws SQLException {
// TODO Auto-generated method stub
return null;
}
@Override
public int getNetworkTimeout() throws SQLException {
// TODO Auto-generated method stub
return 0;
}
@Override
public String getSchema() throws SQLException {
// TODO Auto-generated method stub
return null;
}
@Override
public int getTransactionIsolation() throws SQLException {
// TODO Auto-generated method stub
return 0;
}
@Override
public Map<String, Class<?>> getTypeMap() throws SQLException {
// TODO Auto-generated method stub
return null;
}
@Override
public SQLWarning getWarnings() throws SQLException {
// TODO Auto-generated method stub
return null;
}
@Override
public boolean isClosed() throws SQLException {
// TODO Auto-generated method stub
return false;
}
@Override
public boolean isReadOnly() throws SQLException {
// TODO Auto-generated method stub
return false;
}
@Override
public boolean isValid(int timeout) throws SQLException {
// TODO Auto-generated method stub
return false;
}
@Override
public String nativeSQL(String sql) throws SQLException {
// TODO Auto-generated method stub
return null;
}
@Override
public CallableStatement prepareCall(String sql) throws SQLException {
// TODO Auto-generated method stub
return null;
}
@Override
public CallableStatement prepareCall(String sql, int resultSetType, int resultSetConcurrency) throws SQLException {
// TODO Auto-generated method stub
return null;
}
@Override
public CallableStatement prepareCall(String sql, int resultSetType, int resultSetConcurrency,
int resultSetHoldability) throws SQLException {
// TODO Auto-generated method stub
return null;
}
@Override
public PreparedStatement prepareStatement(String sql, int autoGeneratedKeys) throws SQLException {
// TODO Auto-generated method stub
return null;
}
@Override
public PreparedStatement prepareStatement(String sql, int[] columnIndexes) throws SQLException {
// TODO Auto-generated method stub
return null;
}
@Override
public PreparedStatement prepareStatement(String sql, String[] columnNames) throws SQLException {
// TODO Auto-generated method stub
return null;
}
@Override
public PreparedStatement prepareStatement(String sql, int resultSetType, int resultSetConcurrency)
throws SQLException {
// TODO Auto-generated method stub
return null;
}
@Override
public PreparedStatement prepareStatement(String sql, int resultSetType, int resultSetConcurrency,
int resultSetHoldability) throws SQLException {
// TODO Auto-generated method stub
return null;
}
@Override
public void releaseSavepoint(Savepoint savepoint) throws SQLException {
// TODO Auto-generated method stub
}
@Override
public void rollback() throws SQLException {
// TODO Auto-generated method stub
}
@Override
public void rollback(Savepoint savepoint) throws SQLException {
// TODO Auto-generated method stub
}
@Override
public void setAutoCommit(boolean autoCommit) throws SQLException {
// TODO Auto-generated method stub
}
@Override
public void setCatalog(String catalog) throws SQLException {
// TODO Auto-generated method stub
}
@Override
public void setClientInfo(Properties properties) throws SQLClientInfoException {
// TODO Auto-generated method stub
}
@Override
public void setClientInfo(String name, String value) throws SQLClientInfoException {
// TODO Auto-generated method stub
}
@Override
public void setHoldability(int holdability) throws SQLException {
// TODO Auto-generated method stub
}
@Override
public void setNetworkTimeout(Executor executor, int milliseconds) throws SQLException {
// TODO Auto-generated method stub
}
@Override
public void setReadOnly(boolean readOnly) throws SQLException {
// TODO Auto-generated method stub
}
@Override
public Savepoint setSavepoint() throws SQLException {
// TODO Auto-generated method stub
return null;
}
@Override
public Savepoint setSavepoint(String name) throws SQLException {
// TODO Auto-generated method stub
return null;
}
@Override
public void setSchema(String schema) throws SQLException {
// TODO Auto-generated method stub
}
@Override
public void setTransactionIsolation(int level) throws SQLException {
// TODO Auto-generated method stub
}
@Override
public void setTypeMap(Map<String, Class<?>> map) throws SQLException {
// TODO Auto-generated method stub
}
}
MyDataSource.java
package com.st.web.util;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.SQLFeatureNotSupportedException;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Logger;
import javax.sql.DataSource;
/*
* 这是一个数据库连接池
* 一开始往数据库连接池放十个连接
* 1.开始创建十个连接
*
* 2.来的程序通过getConnection后去连接
*
* 3.用完之后,使用addBack归还连接.
*
* 4.扩容
*
* */
public class MyDataSource implements DataSource{
List<Connection> list=new ArrayList<Connection>();
public MyDataSource() {
for(int i=0;i<10;i++) {
Connection connection=JDBCUtil.getConn();
list.add(connection);
}
}
//该连接池对外公布的获取连接的方法
@Override
public Connection getConnection() throws SQLException {
//判断过来的连接池是否用完 用完就在添加5个.
if (list.size()==0) {
for(int i=0;i<5;i++) {
Connection connection=JDBCUtil.getConn();
list.add(connection);
}
}
//移除list里面的第一个
Connection conn=list.remove(0);
Connection connection=new ConnectionWrap(conn, list);
return connection;
}
//退还连接
public void addBack(Connection conn) {
list.add(conn);
}
@Override
public PrintWriter getLogWriter() throws SQLException {
// TODO Auto-generated method stub
return null;
}
@Override
public int getLoginTimeout() throws SQLException {
// TODO Auto-generated method stub
return 0;
}
@Override
public Logger getParentLogger() throws SQLFeatureNotSupportedException {
// TODO Auto-generated method stub
return null;
}
@Override
public void setLogWriter(PrintWriter arg0) throws SQLException {
// TODO Auto-generated method stub
}
@Override
public void setLoginTimeout(int arg0) throws SQLException {
// TODO Auto-generated method stub
}
@Override
public boolean isWrapperFor(Class<?> arg0) throws SQLException {
// TODO Auto-generated method stub
return false;
}
@Override
public <T> T unwrap(Class<T> arg0) throws SQLException {
// TODO Auto-generated method stub
return null;
}
@Override
public Connection getConnection(String username, String password) throws SQLException {
// TODO Auto-generated method stub
return null;
}
}
DataSourceTest.java里面
package com.st.web.util;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import org.junit.jupiter.api.Test;
import com.mysql.jdbc.Statement;
import javafx.css.PseudoClass;
public class DataSourceTest {
@Test
public void testdatasource(){
MyDataSource dataSource=new MyDataSource();
PreparedStatement ps=null;
Connection conn=null;
try {
conn=dataSource.getConnection();
String sql="insert into account values(null, 'xiaoming',500)";
ps=conn.prepareStatement(sql);
ps.executeUpdate();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
try {
//关闭资源
ps.close();
} catch (SQLException e2) {
// TODO: handle exception
e2.printStackTrace();
}
//归还连接
//dataSource.addBack(conn);
JDBCUtil.release((Statement) ps, conn);
}
};
}
结果
开源连接池
Java发展至今 早就有一套开源免费的连接池如:
- DBCP
- C3P0
如何使用DBCP?
创建工程导入dbcp支持jar包
DBCPDemo.java
package com.st.web.dbcp;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import org.apache.commons.dbcp.BasicDataSource;
import org.junit.Test;
import com.mysql.jdbc.Statement;
import com.st.web.util.JDBCUtil;
public class DBCPDemo {
@Test
public void testdbcp () {
Connection conn=null;
PreparedStatement ps=null;
try {
BasicDataSource dataSource=new BasicDataSource();
dataSource.setDriverClassName("com.mysql.jdbc.Driver");
dataSource.setUrl("jdbc:mysql://localhost/bank?useUnicode=true&characterEncoding=utf8");
dataSource.setUsername("root");
dataSource.setPassword("123456");
conn=dataSource.getConnection();
String sql="insert into account values(null, ?,?)";
ps=conn.prepareStatement(sql);
ps.setString(1, "liu");
ps.setInt(2, 800);
ps.executeUpdate();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
运行结果:
上文代码 要用到一些set手动实现。其实还有一种方式:
使用配置文件方式
创建配置文件
dbcpconfig.properties
#连接设置
driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost/bank?useUnicode=true&characterEncoding=utf8
username=root
password=123456
#<!-- 初始化连接 -->
initialSize=10
#最大连接数量
maxActive=50
#<!-- 最大空闲连接 -->
maxIdle=20
#<!-- 最小空闲连接 -->
minIdle=5
#<!-- 超时等待时间以毫秒为单位 6000毫秒/1000等于60秒 -->
maxWait=60000
#JDBC驱动建立连接时附带的连接属性属性的格式必须为这样:[属性名=property;]
#注意:"user" 与 "password" 两个属性会被明确地传递,因此这里不需要包含他们。
connectionProperties=useUnicode=true;characterEncoding=gbk
#指定由连接池所创建的连接的自动提交(auto-commit)状态。
defaultAutoCommit=true
#driver default 指定由连接池所创建的连接的事务级别(TransactionIsolation)。
#可用值为下列之一:(详情可见javadoc。)NONE,READ_UNCOMMITTED, READ_COMMITTED, REPEATABLE_READ, SERIALIZABLE
defaultTransactionIsolation=READ_UNCOMMITTED
读取配置文件 执行数据库操作
package com.st.web.dbcp;
import java.io.FileInputStream;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.Properties;
import javax.sql.DataSource;
import org.apache.commons.dbcp.BasicDataSource;
import org.apache.commons.dbcp.BasicDataSourceFactory;
import org.junit.Test;
import com.mysql.jdbc.Statement;
import com.st.web.util.JDBCUtil;
public class DBCPDemo1 {
@Test
public void testdbcp () {
Connection conn=null;
PreparedStatement ps=null;
try {
BasicDataSourceFactory factory=new BasicDataSourceFactory();
Properties properties=new Properties();
InputStream is=new FileInputStream("src//dbcpconfig.properties");
properties.load(is);
DataSource dataSource=factory.createDataSource(properties);
// BasicDataSource dataSource1=new BasicDataSource();
conn=dataSource.getConnection();
String sql="insert into account values(null, ?,?)";
ps=conn.prepareStatement(sql);
ps.setString(1, "liu");
ps.setInt(2, 800);
ps.executeUpdate();
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
JDBCUtil.release((Statement) ps, conn);
}
}
}
结果:
C3P0
如何使用C3P0?
创建工程导入dbcp支持jar包
package com.st.web.c3p0;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import org.junit.Test;
import com.mchange.v2.c3p0.ComboPooledDataSource;
import com.mysql.jdbc.Statement;
import com.st.web.util.JDBCUtil;
public class C3P0Demo {
@Test
public void c3p0test() {
//创建combopooleddatasource对象
Connection conn=null;
PreparedStatement ps=null;
try {
//设置连接账户信息
ComboPooledDataSource dataSource=new ComboPooledDataSource();
dataSource.setJdbcUrl("jdbc:mysql://localhost/bank?useUnicode=true&characterEncoding=utf8");
dataSource.setDriverClass("com.mysql.jdbc.Driver");
dataSource.setUser("root");
dataSource.setPassword("123456");
conn=dataSource.getConnection();
String sql="insert into account values(null, ?,?)";
ps=conn.prepareStatement(sql);
ps.setString(1, "zhanger");
ps.setInt(2, 800);
ps.executeUpdate();
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
JDBCUtil.release(conn, ps);
}
}
}
使用配置文件方式
创建配置文件c3p0-config.xml
这里注意配置文件名字不能有错误
<?xml version="1.0" encoding="UTF-8"?>
<c3p0-config>
<!-- default-config 默认的配置, -->
<default-config>
<property name="driverClass">com.mysql.jdbc.Driver</property>
<property name="jdbcUrl">jdbc:mysql://localhost/bank?useUnicode=true&characterEncoding=utf8</property>
<property name="user">root</property>
<property name="password">123456</property>
<property name="initialPoolSize">10</property>
<property name="maxIdleTime">30</property>
<property name="maxPoolSize">100</property>
<property name="minPoolSize">10</property>
<property name="maxStatements">200</property>
</default-config>
<!-- This app is massive! -->
<named-config name="oracle">
<property name="acquireIncrement">50</property>
<property name="initialPoolSize">100</property>
<property name="minPoolSize">50</property>
<property name="maxPoolSize">1000</property>
<!-- intergalactoApp adopts a different approach to configuring statement caching -->
<property name="maxStatements">0</property>
<property name="maxStatementsPerConnection">5</property>
<!-- he's important, but there's only one of him -->
<user-overrides user="master-of-the-universe">
<property name="acquireIncrement">1</property>
<property name="initialPoolSize">1</property>
<property name="minPoolSize">1</property>
<property name="maxPoolSize">5</property>
<property name="maxStatementsPerConnection">50</property>
</user-overrides>
</named-config>
</c3p0-config>
代码:
package com.st.web.c3p0;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import org.junit.Test;
import com.mchange.v2.c3p0.ComboPooledDataSource;
import com.mysql.jdbc.Statement;
import com.st.web.util.JDBCUtil;
public class C3P0Demo {
@Test
public void c3p0test() {
//创建combopooleddatasource对象
Connection conn=null;
PreparedStatement ps=null;
try {
//设置连接账户信息
ComboPooledDataSource dataSource=new ComboPooledDataSource();
conn=dataSource.getConnection();
String sql="insert into account values(null, ?,?)";
ps=conn.prepareStatement(sql);
ps.setString(1, "zhanger");
ps.setInt(2, 800);
ps.executeUpdate();
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
JDBCUtil.release(conn, ps);
}
}
}
结果:
DButil使用配置相关
数据库连接池 等都有相应的工具 那么jdbcutil也应该有相应的工具类。(包含了各种查询方式)
导入DButil工具类
commons-dbutils-1.4.jar
先创建一个Javaben
Account.java
package com.st.web.ben;
public class Account {
private int money;
private String name;
public int getMoney() {
return money;
}
public void setMoney(int money) {
this.money = money;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
@Override
public String toString() {
return "Account [money=" + money + ", name=" + name + "]";
}
}
TestDBUtils.java如下:
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
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.junit.Test;
import com.itheima.domain.Account;
import com.mchange.v2.c3p0.ComboPooledDataSource;
/*
//针对增加 、 删除 、 修改
queryRunner.update(sql)
//针对查询
queryRunner.query(sql, rsh);*/
public class TestDBUtils {
@Test
public void testInsert() throws SQLException, InstantiationException, IllegalAccessException{
// ComboPooledDataSource dataSource = new ComboPooledDataSource();
//dbutils 只是帮我们简化了CRUD 的代码, 但是连接的创建以及获取工作。 不在他的考虑范围
QueryRunner queryRunner = new QueryRunner(new ComboPooledDataSource());
//增加
//queryRunner.update("insert into account values (null , ? , ? )", "aa" ,1000);
//删除
// queryRunner.update("delete from account where id = ?", 5);
//更新
//queryRunner.update("update account set money = ? where id = ?", 10000000 , 6);
//去执行查询,查询到的数据还是在哪个result里面。 然后调用下面的handle方法,由用户手动去封装。
/*Account account = queryRunner.query("select * from account where id = ?", new ResultSetHandler<Account>(){
@Override
public Account handle(ResultSet rs) throws SQLException {
Account account = new Account();
while(rs.next()){
String name = rs.getString("name");
int money = rs.getInt("money");
account.setName(name);
account.setMoney(money);
}
return account;
}
}, 6);
System.out.println(account.toString());
*/
// 通过类的字节码得到该类的实例
/*
Account a = new Account();
//创建一个类的实例。
Account a1= Account.class.newInstance();
*/
//查询单个对象
Account account = queryRunner.query("select * from account where id = ?",
new BeanHandler<Account>(Account.class), 8);
System.out.println(account.toString());
/*List<Account> list = queryRunner.query("select * from account ",
new BeanListHandler<Account>(Account.class));
for (Account account : list) {
System.out.println(account.toString());
}*/
}
DButil总结:
- 导入DButil支持jar包
- 执行 SQL语句
- 如果查询 先实例化 在 进行查询。
ResultSetHandler 常用的实现类
以下两个是使用频率最高的
**BeanHandler**, 查询到的单个数据封装成一个对象
**BeanListHandler,** 查询到的多个数据封装 成一个List<对象>
ArrayHandler, 查询到的单个数据封装成一个数组
ArrayListHandler, 查询到的多个数据封装成一个集合 ,集合里面的元素是数组。
MapHandler, 查询到的单个数据封装成一个map
MapListHandler,查询到的多个数据封装成一个集合 ,集合里面的元素是map。
ColumnListHandler
KeyedHandler
ScalarHandler