介绍
因为数据库连接对象的创建比较消耗性能,所以可以在应用程序启动时就在内存中开辟一片空间(集合)存放多个数据库连接对象,后面需要连接时直接从该空间中取而不用新创建;使用完毕后归还连接(将连接重新放回空间),确保连接对象能重复使用。
知识储备
装饰者模式
package com.zze.test; public interface IWaiter { void service(); }
IWaiter.java
package com.zze.test; public class Waiter implements IWaiter{ public void service(){ System.out.println("正在服务"); } }
Waiter.java
package com.zze.test; public class WaiterWrapper implements IWaiter { public WaiterWrapper(Waiter waiter) { this.waiter = waiter; } private Waiter waiter; @Override public void service() { System.out.println("服务之前"); waiter.service(); System.out.println("服务之后"); } }
WaiterWrapper.java
@Test public void wrapperTest() { IWaiter waiter = new WaiterWrapper(new Waiter()); waiter.service(); /* 服务之前 正在服务 服务之后 */ }
test
自己实现一个连接池
通常一个连接使用完毕后我们要调用它的 close 方法关闭它,而这里我们是要让它归还到连接池,这里我们可以通过装饰者模式修改它的 close 方法实现:
package com.zze.util; import java.sql.*; import java.util.List; import java.util.Map; import java.util.Properties; import java.util.concurrent.Executor; public class ConnectionWrap implements Connection { private Connection connection = null; private List<Connection> connectionList; /** * 构造函数 * @param connection 要装饰的连接 * @param connectionList 连接池储存连接的集合 */ public ConnectionWrap(Connection connection, List<Connection> connectionList) { super(); this.connection = connection; this.connectionList = connectionList; } @Override public void close() throws SQLException { connectionList.add(connection); } @Override public Statement createStatement() throws SQLException { return connection.createStatement(); } @Override public PreparedStatement prepareStatement(String sql) throws SQLException { return connection.prepareStatement(sql); } @Override public CallableStatement prepareCall(String sql) throws SQLException { return connection.prepareCall(sql); } @Override public String nativeSQL(String sql) throws SQLException { return connection.nativeSQL(sql); } @Override public void setAutoCommit(boolean autoCommit) throws SQLException { connection.setAutoCommit(autoCommit); } @Override public boolean getAutoCommit() throws SQLException { return connection.getAutoCommit(); } @Override public void commit() throws SQLException { connection.commit(); } @Override public void rollback() throws SQLException { connection.rollback(); } @Override public boolean isClosed() throws SQLException { return connection.isClosed(); } @Override public DatabaseMetaData getMetaData() throws SQLException { return connection.getMetaData(); } @Override public void setReadOnly(boolean readOnly) throws SQLException { connection.setReadOnly(readOnly); } @Override public boolean isReadOnly() throws SQLException { return connection.isReadOnly(); } @Override public void setCatalog(String catalog) throws SQLException { connection.setCatalog(catalog); } @Override public String getCatalog() throws SQLException { return connection.getCatalog(); } @Override public void setTransactionIsolation(int level) throws SQLException { connection.setTransactionIsolation(level); } @Override public int getTransactionIsolation() throws SQLException { return connection.getTransactionIsolation(); } @Override public SQLWarning getWarnings() throws SQLException { return connection.getWarnings(); } @Override public void clearWarnings() throws SQLException { connection.clearWarnings(); } @Override public Statement createStatement(int resultSetType, int resultSetConcurrency) throws SQLException { return connection.createStatement(resultSetType, resultSetConcurrency); } @Override public PreparedStatement prepareStatement(String sql, int resultSetType, int resultSetConcurrency) throws SQLException { return connection.prepareStatement(sql, resultSetType, resultSetConcurrency); } @Override public CallableStatement prepareCall(String sql, int resultSetType, int resultSetConcurrency) throws SQLException { return connection.prepareCall(sql,resultSetType,resultSetConcurrency); } @Override public Map<String, Class<?>> getTypeMap() throws SQLException { return connection.getTypeMap(); } @Override public void setTypeMap(Map<String, Class<?>> map) throws SQLException { connection.setTypeMap(map); } @Override public void setHoldability(int holdability) throws SQLException { connection.setHoldability(holdability); } @Override public int getHoldability() throws SQLException { return connection.getHoldability(); } @Override public Savepoint setSavepoint() throws SQLException { return connection.setSavepoint(); } @Override public Savepoint setSavepoint(String name) throws SQLException { return connection.setSavepoint(name); } @Override public void rollback(Savepoint savepoint) throws SQLException { connection.rollback(); } @Override public void releaseSavepoint(Savepoint savepoint) throws SQLException { connection.releaseSavepoint(savepoint); } @Override public Statement createStatement(int resultSetType, int resultSetConcurrency, int resultSetHoldability) throws SQLException { return connection.createStatement(resultSetType, resultSetConcurrency, resultSetHoldability); } @Override public PreparedStatement prepareStatement(String sql, int resultSetType, int resultSetConcurrency, int resultSetHoldability) throws SQLException { return connection.prepareStatement(sql, resultSetType, resultSetConcurrency, resultSetHoldability); } @Override public CallableStatement prepareCall(String sql, int resultSetType, int resultSetConcurrency, int resultSetHoldability) throws SQLException { return connection.prepareCall(sql,resultSetType,resultSetConcurrency,resultSetHoldability); } @Override public PreparedStatement prepareStatement(String sql, int autoGeneratedKeys) throws SQLException { return connection.prepareStatement(sql, autoGeneratedKeys); } @Override public PreparedStatement prepareStatement(String sql, int[] columnIndexes) throws SQLException { return connection.prepareStatement(sql, columnIndexes); } @Override public PreparedStatement prepareStatement(String sql, String[] columnNames) throws SQLException { return connection.prepareStatement(sql, columnNames); } @Override public Clob createClob() throws SQLException { return connection.createClob(); } @Override public Blob createBlob() throws SQLException { return connection.createBlob(); } @Override public NClob createNClob() throws SQLException { return connection.createNClob(); } @Override public SQLXML createSQLXML() throws SQLException { return connection.createSQLXML(); } @Override public boolean isValid(int timeout) throws SQLException { return connection.isValid(timeout); } @Override public void setClientInfo(String name, String value) throws SQLClientInfoException { connection.setClientInfo(name,value); } @Override public void setClientInfo(Properties properties) throws SQLClientInfoException { connection.setClientInfo(properties); } @Override public String getClientInfo(String name) throws SQLException { return connection.getClientInfo(name); } @Override public Properties getClientInfo() throws SQLException { return connection.getClientInfo(); } @Override public Array createArrayOf(String typeName, Object[] elements) throws SQLException { return connection.createArrayOf(typeName,elements); } @Override public Struct createStruct(String typeName, Object[] attributes) throws SQLException { return connection.createStruct(typeName,attributes); } @Override public void setSchema(String schema) throws SQLException { connection.setSchema(schema); } @Override public String getSchema() throws SQLException { return connection.getSchema(); } @Override public void abort(Executor executor) throws SQLException { connection.abort(executor); } @Override public void setNetworkTimeout(Executor executor, int milliseconds) throws SQLException { connection.setNetworkTimeout(executor,milliseconds); } @Override public int getNetworkTimeout() throws SQLException { return connection.getNetworkTimeout(); } @Override public <T> T unwrap(Class<T> iface) throws SQLException { return connection.unwrap(iface); } @Override public boolean isWrapperFor(Class<?> iface) throws SQLException { return connection.isWrapperFor(iface); } }
ConnectionWrap.java
对于连接池,java 已经给我们提供了接口 javax.sql.DataSource ,我们要做的就是实现它:
package com.zze.util; import javax.sql.DataSource; 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; public class MyDataSource implements DataSource { // 定义一个集合用来存放连接 private List<Connection> connections = new ArrayList<>(); public MyDataSource() { // 初始化 10 个连接 for (int i = 0; i < 10; i++) { connections.add(JDBCUtil.getConnection()); } } /** * 获取连接 * * @return 从连接池取出的连接 */ @Override public Connection getConnection() throws SQLException { // 取连接但集合没有连接时,新添加 5 个连接 if (connections.size() == 0) { for (int i = 0; i < 5; i++) { connections.add(JDBCUtil.getConnection()); } } Connection connection = connections.remove(0); // 返回我们定义的连接包装类 ConnectionWrap connectionWrap = new ConnectionWrap(connection, connections); return connectionWrap; } @Override public Connection getConnection(String username, String password) throws SQLException { return null; } @Override public <T> T unwrap(Class<T> iface) throws SQLException { return null; } @Override public boolean isWrapperFor(Class<?> iface) throws SQLException { return false; } @Override public PrintWriter getLogWriter() throws SQLException { return null; } @Override public void setLogWriter(PrintWriter out) throws SQLException { } @Override public void setLoginTimeout(int seconds) throws SQLException { } @Override public int getLoginTimeout() throws SQLException { return 0; } @Override public Logger getParentLogger() throws SQLFeatureNotSupportedException { return null; } }
MyDataSource.java
此时就可以通过我们自己编写的连接池获取连接了:
@Test public void getConnTest() { try { MyDataSource dataSource = new MyDataSource(); Connection connection = dataSource.getConnection(); System.out.println(connection); // com.zze.util.ConnectionWrap@6c629d6e } catch (SQLException e) { e.printStackTrace(); } }
test
第三方连接池使用
DBCP
DBCP (DataBase Connection Pool) 是 java 数据库连接池的一种,由 Apache 开发,通过它可以让程序自动管理数据库连接的释放和断开。
# 连接设置 driverClassName=com.mysql.cj.jdbc.Driver url=jdbc:mysql://localhost:3306/test username=root password=root # 初始化连接 initialSize=10 # 最大连接数量 maxActive=50 # 最大空闲连接 maxIdle=20 # 最小空闲连接 minIdle=5 # 超时等待时间以毫秒为单位 6000毫秒/1000等于60秒 maxWait=60000 # JDBC驱动建立连接时附带的连接属性属性的格式必须为这样:[属性名=property;] # 注意:"user" 与 "password" 两个属性会被明确地传递,因此这里不需要包含他们。 connectionProperties=useUnicode=true;characterEncoding=gbk;serverTimezone=GMT # 指定由连接池所创建的连接的自动提交(auto-commit)状态。 defaultAutoCommit=true # driver default 指定由连接池所创建的连接的事务级别(TransactionIsolation)。 # 可用值为下列之一:(详情可见javadoc。)NONE,READ_UNCOMMITTED, READ_COMMITTED, REPEATABLE_READ, SERIALIZABLE defaultTransactionIsolation=READ_UNCOMMITTED
src:dbcpconfig.properties
package com.zze.test; import org.apache.commons.dbcp.BasicDataSource; import org.apache.commons.dbcp.BasicDataSourceFactory; import org.junit.Test; import javax.sql.DataSource; import java.io.FileInputStream; import java.sql.Connection; import java.sql.SQLException; import java.util.Properties; public class DemoTest { /** * 通过代码配置创建连接池 */ @Test public void dbcpTest1() { try { // 构建数据源对象 BasicDataSource dataSource = new BasicDataSource(); // 数据库驱动 dataSource.setDriverClassName("com.mysql.cj.jdbc.Driver"); // 用户名 dataSource.setUsername("root"); // 密码 dataSource.setPassword("root"); // 数据库链接 dataSource.setUrl("jdbc:mysql://localhost:3306/test?serverTimezone=GMT"); // 得到连接对象 Connection connection = dataSource.getConnection(); System.out.println(connection.getClass()); // class org.apache.commons.dbcp.PoolingDataSource$PoolGuardConnectionWrapper } catch (SQLException e) { e.printStackTrace(); } } /** * 通过配置文件创建连接池 */ @Test public void dbcpTest2() { try { Properties properties = new Properties(); properties.load(new FileInputStream("src//dbcpconfig.properties")); DataSource dataSource = BasicDataSourceFactory.createDataSource(properties); Connection connection = dataSource.getConnection(); System.out.println(connection.getClass()); } catch (Exception e) { e.printStackTrace(); } } }
C3P0
C3P0 也是一个开源的 JDBC 连接池,它实现了数据源和 JNDI 绑定,支持 JDBC3 规范和 JDBC2 的标准扩展。目前使用它的开源项目有 Hibernate、Sping 等。
<?xml version="1.0" encoding="UTF-8"?> <c3p0-config> <!--默认配置--> <default-config> <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> <!--配置 mysql 连接池--> <named-config name="mysql"> <property name="driverClass">com.mysql.jdbc.Driver</property> <property name="jdbcUrl">jdbc:mysql://localhost:3306/test?useUnicode=true&serverTimezone=GMT&characterEncoding=utf-8</property> <property name="user">root</property> <property name="password">root</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> </named-config> </c3p0-config>
src:c3p0-config.xml
<!--acquireIncrement:链接用完了自动增量3个。 --> <property name="acquireIncrement">3</property> <!--acquireRetryAttempts:链接失败后重新试30次。--> <property name="acquireRetryAttempts">30</property> <!--acquireRetryDelay;两次连接中间隔1000毫秒。 --> <property name="acquireRetryDelay">1000</property> <!--autoCommitOnClose:连接关闭时默认将所有未提交的操作回滚。 --> <property name="autoCommitOnClose">false</property> <!--automaticTestTable:c3p0测试表,没什么用。--> <property name="automaticTestTable">Test</property> <!--breakAfterAcquireFailure:出错时不把正在提交的数据抛弃。--> <property name="breakAfterAcquireFailure">false</property> <!--checkoutTimeout:100毫秒后如果sql数据没有执行完将会报错,如果设置成0,那么将会无限的等待。 --> <property name="checkoutTimeout">100</property> <!--connectionTesterClassName:通过实现ConnectionTester或QueryConnectionTester的类来测试连接。类名需制定全路径。Default: com.mchange.v2.c3p0.impl.DefaultConnectionTester--> <property name="connectionTesterClassName"></property> <!--factoryClassLocation:指定c3p0 libraries的路径,如果(通常都是这样)在本地即可获得那么无需设置,默认null即可。--> <property name="factoryClassLocation">null</property> <!--forceIgnoreUnresolvedTransactions:作者强烈建议不使用的一个属性。--> <property name="forceIgnoreUnresolvedTransactions">false</property> <!--idleConnectionTestPeriod:每60秒检查所有连接池中的空闲连接。--> <property name="idleConnectionTestPeriod">60</property> <!--initialPoolSize:初始化时获取三个连接,取值应在minPoolSize与maxPoolSize之间。 --> <property name="initialPoolSize">3</property> <!--maxIdleTime:最大空闲时间,60秒内未使用则连接被丢弃。若为0则永不丢弃。--> <property name="maxIdleTime">60</property> <!--maxPoolSize:连接池中保留的最大连接数。 --> <property name="maxPoolSize">15</property> <!--maxStatements:最大链接数。--> <property name="maxStatements">100</property> <!--maxStatementsPerConnection:定义了连接池内单个连接所拥有的最大缓存statements数。Default: 0 --> <property name="maxStatementsPerConnection"></property> <!--numHelperThreads:异步操作,提升性能通过多线程实现多个操作同时被执行。Default: 3--> <property name="numHelperThreads">3</property> <!--overrideDefaultUser:当用户调用getConnection()时使root用户成为去获取连接的用户。主要用于连接池连接非c3p0的数据源时。Default: null--> <property name="overrideDefaultUser">root</property> <!--overrideDefaultPassword:与overrideDefaultUser参数对应使用的一个参数。Default: null--> <property name="overrideDefaultPassword">password</property> <!--password:密码。Default: null--> <property name="password"></property> <!--preferredTestQuery:定义所有连接测试都执行的测试语句。在使用连接测试的情况下这个一显著提高测试速度。注意: 测试的表必须在初始数据源的时候就存在。Default: null--> <property name="preferredTestQuery">select id from test where id=1</property> <!--propertyCycle:用户修改系统配置参数执行前最多等待300秒。Default: 300 --> <property name="propertyCycle">300</property> <!--testConnectionOnCheckout:因性能消耗大请只在需要的时候使用它。Default: false --> <property name="testConnectionOnCheckout">false</property> <!--testConnectionOnCheckin:如果设为true那么在取得连接的同时将校验连接的有效性。Default: false --> <property name="testConnectionOnCheckin">true</property> <!--user:用户名。Default: null--> <property name="user">root</property> <!--usesTraditionalReflectiveProxies:动态反射代理。Default: false--> <property name="usesTraditionalReflectiveProxies">false</property>
src:c3p0-config.xml
package com.zze.test; import com.mchange.v2.c3p0.ComboPooledDataSource; import org.junit.Test; import javax.sql.DataSource; import java.sql.Connection; public class DemoTest { /** * 代码配置创建连接池 */ @Test public void c3p0Test1() { try { ComboPooledDataSource comboPooledDataSource = new ComboPooledDataSource(); comboPooledDataSource.setDriverClass("com.mysql.cj.jdbc.Driver"); comboPooledDataSource.setJdbcUrl("jdbc:mysql://localhost:3306/test?serverTimezone=GMT"); comboPooledDataSource.setUser("root"); comboPooledDataSource.setPassword("root"); Connection connection = comboPooledDataSource.getConnection(); System.out.println(connection); // com.mchange.v2.c3p0.impl.NewProxyConnection@7a0ac6e3 } catch (Exception e) { e.printStackTrace(); } } /** * 配置文件创建连接池 */ @Test public void c3p0Test2() { try { //参数对应使用哪个config,如果不写,表示使用默认的config,即default-config里的配置,否则使用参数指定的named-config里的配置。 DataSource ds = new ComboPooledDataSource("mysql"); Connection connection = ds.getConnection(); System.out.println(connection); // com.mchange.v2.c3p0.impl.NewProxyConnection@3796751b } catch (Exception e) { e.printStackTrace(); } } }
抽取工具类
package com.zze.util; import com.mchange.v2.c3p0.ComboPooledDataSource; import javax.sql.DataSource; import java.sql.Connection; import java.sql.SQLException; public class JDBCUtil { private static final ComboPooledDataSource DATA_SOURCE = new ComboPooledDataSource("mysql"); private static final ThreadLocal<Connection> t = new ThreadLocal<>(); public static Connection getConnection() { Connection conn = null; try { conn = t.get(); if (conn == null) { conn = DATA_SOURCE.getConnection(); t.set(conn); } } catch (Exception e) { e.printStackTrace(); } return conn; } public static void beginTransaction() throws SQLException { Connection conn = getConnection(); conn.setAutoCommit(false); } public static void commitTransaction() throws SQLException { Connection conn = getConnection(); conn.commit(); } public static DataSource getDataSource() { return DATA_SOURCE; } }
DBUtils
使用
要使用 dbutils 需要提供一个连接池,这里我使用 c3p0 。
package com.zze.bean; public class User { public User() { } public User(Integer id, String username, String password) { this.id = id; this.username = username; this.password = password; } private Integer id; private String password; private String username; public Integer getId() { return id; } public void setId(Integer id) { this.id = id; } public String getPassword() { return password; } public void setPassword(String password) { this.password = password; } public String getUsername() { return username; } public void setUsername(String username) { this.username = username; } @Override public String toString() { return String.format("id=%d,username=%s", this.id, this.username); } }
com.zze.bean.User
package com.zze.test; import com.mchange.v2.c3p0.ComboPooledDataSource; import com.zze.bean.User; import org.apache.commons.dbutils.QueryRunner; import org.apache.commons.dbutils.ResultSetHandler; import org.junit.Test; import javax.sql.DataSource; import java.sql.ResultSet; import java.sql.SQLException; import java.util.ArrayList; import java.util.List; public class DemoTest { /** * 查询所有 */ @Test public void listAllTest() { try { DataSource ds = new ComboPooledDataSource("mysql"); QueryRunner qr = new QueryRunner(ds); List<User> userList = qr.query("select * from user", new ResultSetHandler<List<User>>() { @Override public List<User> handle(ResultSet resultSet) throws SQLException { List<User> users = new ArrayList<>(); while (resultSet.next()) { int id = resultSet.getInt("id"); String username = resultSet.getString("username"); String password = resultSet.getString("password"); users.add(new User(id, username, password)); } return users; } }); System.out.println(userList); // [id=1,username=张三, id=2,username=李四] } catch (Exception e) { e.printStackTrace(); } } /** * 根据 Id 查询 */ @Test public void getByIdTest() { try { Integer id = 1; DataSource ds = new ComboPooledDataSource("mysql"); QueryRunner qr = new QueryRunner(ds); User user = qr.query("select * from user where id=?", new ResultSetHandler<User>() { @Override public User handle(ResultSet resultSet) throws SQLException { if (resultSet.next()) { int id = resultSet.getInt("id"); String username = resultSet.getString("username"); String password = resultSet.getString("password"); return new User(id, username, password); } return null; } }, id); System.out.println(user); // id=1,username=张三 } catch (Exception e) { e.printStackTrace(); } } /** * 新增 */ @Test public void testAdd() { Integer id = 1; DataSource ds = new ComboPooledDataSource("mysql"); QueryRunner qr = new QueryRunner(ds); try { int count = qr.update("insert into user (username,password) values(?,?)", "王五", "1226"); System.out.println(count > 0 ? "success" : "failed"); } catch (SQLException e) { e.printStackTrace(); } } /** * 更新 */ @Test public void testUpdate() { DataSource ds = new ComboPooledDataSource("mysql"); QueryRunner qr = new QueryRunner(ds); try { int count = qr.update("update user set username=? where id=?", "赵六", 3); System.out.println(count > 0 ? "success" : "failed"); } catch (SQLException e) { e.printStackTrace(); } } /** * 删除 */ @Test public void testDelete() { DataSource ds = new ComboPooledDataSource("mysql"); QueryRunner qr = new QueryRunner(ds); try { int count = qr.update("delete from user where id=?", 3); System.out.println(count > 0 ? "success" : "failed"); } catch (SQLException e) { e.printStackTrace(); } } }
自己实现一个QueryRunner
package com.zze.util; import java.sql.ResultSet; public interface ResultHandler<T> { T handle(ResultSet resultSet); }
com.zze.util.ResultHandler
package com.zze.util; import javax.sql.DataSource; import javax.xml.crypto.Data; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; public class MyQueryRunner { public MyQueryRunner(DataSource dataSource){ this.dataSource = dataSource; } private DataSource dataSource; public int update(String sql, Object... args) { try { Connection conn = dataSource.getConnection(); PreparedStatement preparedStatement = conn.prepareStatement(sql); int parameterCount = preparedStatement.getParameterMetaData().getParameterCount(); for (int i = 0; i < parameterCount; i++) { preparedStatement.setObject(i + 1, args[i]); } return preparedStatement.executeUpdate(); } catch (Exception e) { e.printStackTrace(); } return 0; } public <T> T query(String sql, ResultHandler<T> handler, Object... args) { try { Connection conn = dataSource.getConnection(); PreparedStatement preparedStatement = conn.prepareStatement(sql); int parameterCount = preparedStatement.getParameterMetaData().getParameterCount(); for (int i = 0; i < parameterCount; i++) { preparedStatement.setObject(i + 1, args[i]); } ResultSet resultSet = preparedStatement.executeQuery(); T result = handler.handle(resultSet); return result; } catch (Exception e) { e.printStackTrace(); } return null; } }
com.zze.util.MyQueryRunner
package com.zze.test; import com.mchange.v2.c3p0.ComboPooledDataSource; import com.zze.bean.User; import com.zze.util.MyQueryRunner; import com.zze.util.ResultHandler; import org.junit.Test; import javax.sql.DataSource; import java.sql.ResultSet; import java.sql.SQLException; import java.util.ArrayList; import java.util.List; /** * 自定义的 DBUtils 测试 */ public class MyDBUtilsTest { /** * 查询所有 */ @Test public void listAllTest() { DataSource ds = new ComboPooledDataSource("mysql"); MyQueryRunner myQueryRunner = new MyQueryRunner(ds); List<User> userList = myQueryRunner.query("select * from user", new ResultHandler<List<User>>() { @Override public List<User> handle(ResultSet resultSet) { try { List<User> users = new ArrayList<>(); while (resultSet.next()) { int id = resultSet.getInt("id"); String username = resultSet.getString("username"); String password = resultSet.getString("password"); users.add(new User(id, username, password)); } return users; } catch (Exception e) { e.printStackTrace(); } return null; } }); System.out.println(userList); } @Test public void getByIdTest() { DataSource ds = new ComboPooledDataSource("mysql"); MyQueryRunner myQueryRunner = new MyQueryRunner(ds); int id = 1; User user = myQueryRunner.query("select * from user where id=?", new ResultHandler<User>() { @Override public User handle(ResultSet resultSet) { try { if (resultSet.next()) { int id = resultSet.getInt("id"); String username = resultSet.getString("username"); String password = resultSet.getString("password"); return new User(id, username, password); } } catch (SQLException e) { e.printStackTrace(); } return null; } }, id); System.out.println(user); } /** * 新增 */ @Test public void testAdd() { Integer id = 1; DataSource ds = new ComboPooledDataSource("mysql"); MyQueryRunner qr = new MyQueryRunner(ds); try { int count = qr.update("insert into user (username,password) values(?,?)", "王五", "1226"); System.out.println(count > 0 ? "success" : "failed"); } catch (Exception e) { e.printStackTrace(); } } /** * 更新 */ @Test public void testUpdate() { DataSource ds = new ComboPooledDataSource("mysql"); MyQueryRunner qr = new MyQueryRunner(ds); try { int count = qr.update("update user set username=? where id=?", "赵六", 7); System.out.println(count > 0 ? "success" : "failed"); } catch (Exception e) { e.printStackTrace(); } } /** * 删除 */ @Test public void testDelete() { DataSource ds = new ComboPooledDataSource("mysql"); MyQueryRunner qr = new MyQueryRunner(ds); try { int count = qr.update("delete from user where id=?", 6); System.out.println(count > 0 ? "success" : "failed"); } catch (Exception e) { e.printStackTrace(); } } }
DBUtils提供的结果集处理器
@Test public void beanHandlerTest() { try { Integer id = 1; DataSource ds = new ComboPooledDataSource("mysql"); QueryRunner qr = new QueryRunner(ds); User user = qr.query("select * from user where id=?", new BeanHandler<>(User.class), id); System.out.println(user); } catch (Exception e) { e.printStackTrace(); } } @Test public void beanListHandlerTest() { try { DataSource ds = new ComboPooledDataSource("mysql"); QueryRunner qr = new QueryRunner(ds); List<User> userList = qr.query("select * from user", new BeanListHandler<>(User.class)); System.out.println(userList); } catch (Exception e) { e.printStackTrace(); } } @Test public void mapHandlerTest() { try { DataSource ds = new ComboPooledDataSource("mysql"); QueryRunner qr = new QueryRunner(ds); Map<String, Object> user = qr.query("select * from user where id=?", new MapHandler(), 2); System.out.println(user); // {password=1226, id=2, username=李四} } catch (Exception e) { e.printStackTrace(); } } @Test public void mapListHandlerTest() { try { DataSource ds = new ComboPooledDataSource("mysql"); QueryRunner qr = new QueryRunner(ds); List<Map<String, Object>> users = qr.query("select * from user", new MapListHandler()); System.out.println(users); // [{password=1226, id=1, username=张三}, {password=1226, id=2, username=李四}] } catch (Exception e) { e.printStackTrace(); } }
练习
这个练习算是对之前知识的总结,一个包含 CRUD、模糊查询、分页功能的简易版学生管理系统,点击下载。