1、存放连接信息
db.properties
driver=com.mysql.jdbc.Driver url=jdbc:mysql://localhost:3306/web08?useUnicode=true&characterEncoding=utf8 username=root password=root
2、工具类用来获取链接数据库
JDBCUtils_V3.java
package cn.itheima.jdbc.utils; import java.io.IOException; import java.io.InputStream; import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.util.Properties; import java.util.ResourceBundle; /** * 提供获取连接和释放资源的 方法*/ public class JDBCUtils_V3 { private static String driver; private static String url; private static String username; private static String password; /** * 静态代码块加载配置文件信息 */ static { try { // 1.通过当前类获取类加载器 ClassLoader classLoader = JDBCUtils_V3.class.getClassLoader(); // 2.通过类加载器的方法获得一个输入流 InputStream is = classLoader.getResourceAsStream("db.properties"); // 3.创建一个properties对象 Properties props = new Properties(); // 4.加载输入流 props.load(is); // 5.获取相关参数的值 driver = props.getProperty("driver"); url = props.getProperty("url"); username = props.getProperty("username"); password = props.getProperty("password"); } catch (IOException e) { e.printStackTrace(); } } /** * 获取连接方法 * * @return */ public static Connection getConnection() { Connection conn = null; try { Class.forName(driver); conn = DriverManager.getConnection(url, username, password); } catch (Exception e) { e.printStackTrace(); } return conn; } /** * 释放资源方法 * * @param conn * @param pstmt * @param rs */ public static void release(Connection conn, PreparedStatement pstmt, ResultSet rs) { if (rs != null) { try { rs.close(); } catch (SQLException e) { e.printStackTrace(); } } if (pstmt != null) { try { pstmt.close(); } catch (SQLException e) { e.printStackTrace(); } } if (conn != null) { try { conn.close(); } catch (SQLException e) { e.printStackTrace(); } } } }
3、自定义Connection类用来自定义close()关闭连接的方法（配合连接池使用）
MyConnection.java
package cn.itheima.jdbc.DataSource; 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.LinkedList; import java.util.Map; import java.util.Properties; import java.util.concurrent.Executor; //1.实现同一个接口Connection public class MyConnection implements Connection { //3.定义一个变量 private Connection conn; private LinkedList<Connection> pool; // 2.编写一个构造方法(参数使用了面相对象的多态特性) public MyConnection(Connection conn,LinkedList<Connection> pool) { this.conn=conn; this.pool=pool; } //4.书写需要增强的方法 @Override public void close() throws SQLException { pool.add(conn); } /** * 此方法必须覆盖！否则会出现空指针异常！！！ */ @Override public PreparedStatement prepareStatement(String sql) throws SQLException { return conn.prepareStatement(sql); } @Override public <T> T unwrap(Class<T> iface) throws SQLException { // TODO Auto-generated method stub return null; } @Override public boolean isWrapperFor(Class<?> iface) throws SQLException { // TODO Auto-generated method stub return false; } @Override public Statement createStatement() 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 String nativeSQL(String sql) throws SQLException { // TODO Auto-generated method stub return null; } @Override public void setAutoCommit(boolean autoCommit) throws SQLException { // TODO Auto-generated method stub } @Override public boolean getAutoCommit() throws SQLException { // TODO Auto-generated method stub return false; } @Override public void commit() throws SQLException { // TODO Auto-generated method stub } @Override public void rollback() throws SQLException { // TODO Auto-generated method stub } @Override public boolean isClosed() throws SQLException { // TODO Auto-generated method stub return false; } @Override public DatabaseMetaData getMetaData() throws SQLException { // TODO Auto-generated method stub return null; } @Override public void setReadOnly(boolean readOnly) throws SQLException { // TODO Auto-generated method stub } @Override public boolean isReadOnly() throws SQLException { // TODO Auto-generated method stub return false; } @Override public void setCatalog(String catalog) throws SQLException { // TODO Auto-generated method stub } @Override public String getCatalog() throws SQLException { // TODO Auto-generated method stub return null; } @Override public void setTransactionIsolation(int level) throws SQLException { // TODO Auto-generated method stub } @Override public int getTransactionIsolation() throws SQLException { // TODO Auto-generated method stub return 0; } @Override public SQLWarning getWarnings() throws SQLException { // TODO Auto-generated method stub return null; } @Override public void clearWarnings() throws SQLException { // TODO Auto-generated method stub } @Override public Statement createStatement(int resultSetType, int resultSetConcurrency) 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 CallableStatement prepareCall(String sql, int resultSetType, int resultSetConcurrency) throws SQLException { // TODO Auto-generated method stub return null; } @Override public Map<String, Class<?>> getTypeMap() throws SQLException { // TODO Auto-generated method stub return null; } @Override public void setTypeMap(Map<String, Class<?>> map) throws SQLException { // TODO Auto-generated method stub } @Override public void setHoldability(int holdability) throws SQLException { // TODO Auto-generated method stub } @Override public int getHoldability() throws SQLException { // TODO Auto-generated method stub return 0; } @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 rollback(Savepoint savepoint) throws SQLException { // TODO Auto-generated method stub } @Override public void releaseSavepoint(Savepoint savepoint) throws SQLException { // TODO Auto-generated method stub } @Override public Statement createStatement(int resultSetType, int resultSetConcurrency, int resultSetHoldability) 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 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 Clob createClob() throws SQLException { // TODO Auto-generated method stub return null; } @Override public Blob createBlob() 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 boolean isValid(int timeout) throws SQLException { // TODO Auto-generated method stub return false; } @Override public void setClientInfo(String name, String value) throws SQLClientInfoException { // TODO Auto-generated method stub } @Override public void setClientInfo(Properties properties) throws SQLClientInfoException { // TODO Auto-generated method stub } @Override public String getClientInfo(String name) throws SQLException { // TODO Auto-generated method stub return null; } @Override public Properties getClientInfo() throws SQLException { // TODO Auto-generated method stub return null; } @Override public Array createArrayOf(String typeName, Object[] elements) 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 void setSchema(String schema) throws SQLException { // TODO Auto-generated method stub } @Override public String getSchema() 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 setNetworkTimeout(Executor executor, int milliseconds) throws SQLException { // TODO Auto-generated method stub } @Override public int getNetworkTimeout() throws SQLException { // TODO Auto-generated method stub return 0; } }
4、设置数据源连接池（数据源提供了一种简单获取数据库连接的方式，并能在内部通过一个池的机制来复用数据库连接，这样就大大减少创建数据库连接的次数，提高了系统性能。）
MyDataSource1.java
package cn.itheima.jdbc.DataSource; import java.io.PrintWriter; import java.sql.Connection; import java.sql.SQLException; import java.sql.SQLFeatureNotSupportedException; import java.util.LinkedList; import java.util.logging.Logger; import javax.sql.DataSource; import cn.itheima.jdbc.utils.JDBCUtils_V3; public class MyDataSource1 implements DataSource{ //1.创建1个容器用于存储Connection对象 private static LinkedList<Connection> pool = new LinkedList<Connection>(); //2.创建5个连接放到容器中去 static{ for (int i = 0; i < 5; i++) { Connection conn = JDBCUtils_V3.getConnection(); //放入池子中connection对象已经经过改造了 MyConnection myconn = new MyConnection(conn, pool); pool.add(myconn); } } /** * 重写获取连接的方法 */ @Override public Connection getConnection() throws SQLException { Connection conn = null; //3.使用前先判断 if(pool.size()==0){ //4.池子里面没有，我们再创建一些 for (int i = 0; i < 5; i++) { conn = JDBCUtils_V3.getConnection(); //放入池子中connection对象已经经过改造了 MyConnection myconn = new MyConnection(conn, pool); pool.add(myconn); } } //5.从池子里面获取一个连接对象Connection conn = pool.remove(0); return conn; } /** * 归还连接对象到连接池中去 */ public void backConnection(Connection conn){ pool.add(conn); } @Override public PrintWriter getLogWriter() throws SQLException { // TODO Auto-generated method stub return null; } @Override public void setLogWriter(PrintWriter out) throws SQLException { // TODO Auto-generated method stub } @Override public void setLoginTimeout(int seconds) throws SQLException { // TODO Auto-generated method stub } @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 <T> T unwrap(Class<T> iface) throws SQLException { // TODO Auto-generated method stub return null; } @Override public boolean isWrapperFor(Class<?> iface) throws SQLException { // TODO Auto-generated method stub return false; } @Override public Connection getConnection(String username, String password) throws SQLException { // TODO Auto-generated method stub return null; } }
5、测试类
TestMyDataSource.java
package cn.itheima.jdbc.test; import java.sql.Connection; import java.sql.PreparedStatement; import javax.sql.DataSource; import org.junit.Test; import cn.itheima.jdbc.DataSource.MyDataSource; import cn.itheima.jdbc.DataSource.MyDataSource1; import cn.itheima.jdbc.utils.JDBCUtils_V3; public class TestMyDataSource { /** * 添加用户 * 使用未改造过的connection */ @Test public void testAddUser() { Connection conn = null; PreparedStatement pstmt = null; // 1.创建自定义连接池对象 MyDataSource dataSource = new MyDataSource(); try { // 2.从池子中获取连接 conn = dataSource.getConnection(); String sql = "insert into tbl_user values(null,?,?)"; pstmt = conn.prepareStatement(sql); pstmt.setString(1, "吕布"); pstmt.setString(2, "貂蝉"); int rows = pstmt.executeUpdate(); if (rows > 0) { System.out.println("添加成功!"); } else { System.out.println("添加失败!"); } } catch (Exception e) { throw new RuntimeException(e); } finally { dataSource.backConnection(conn); } } /** * 添加用户 * 使用改造过的connection */ @Test public void testAddUser1() { Connection conn = null; PreparedStatement pstmt = null; // 1.创建自定义连接池对象 DataSource dataSource = new MyDataSource1(); try { // 2.从池子中获取连接 conn = dataSource.getConnection(); String sql = "insert into tbl_user values(null,?,?)"; //3.必须在自定义的connection类中重写prepareStatement(sql)方法 pstmt = conn.prepareStatement(sql); pstmt.setString(1, "吕布1"); pstmt.setString(2, "貂蝉1"); int rows = pstmt.executeUpdate(); if (rows > 0) { System.out.println("添加成功!"); } else { System.out.println("添加失败!"); } } catch (Exception e) { throw new RuntimeException(e); } finally { JDBCUtils_V3.release(conn, pstmt, null); } } }