JDBC篇--创建连接池

创建具有10个Connection连接的连接池

（1）创建连接池的目的是减少了每次访问数据库时建立连接的时间，大大提高访问速度。

（2）MyDatabase类用于创建数据池

package cn.itcast.jdbc.datasource;import java.sql.Connection;import java.sql.DriverManager;import java.sql.SQLException;import java.util.LinkedList;public class MyDataSource {private LinkedList<Connection> conectionsPool = new LinkedList<>();//使用linkedlist保存Connection对象，增删快private static String url = "jdbc:mysql://localhost:3306/mydatabase"; private static String user = "root"; //数据库用户名private static String password = "123"; //用户密码public MyDataSource() {//在构造方法中创建连接池，具有10个connection连接for (int i = 0; i < 10; i++) {try {this.conectionsPool.addLast(this.createConection(url, user, password));} catch (SQLException e) {throw new ExceptionInInitializerError(e);}}}/** * 创建新连接 *  * @param url * @param user * @param passworld * @return * @throws SQLException */private Connection createConection(String url, String user, String passworld) throws SQLException {return DriverManager.getConnection(url, user, passworld);}/** * 释放connection连接对象，将其放回连接池中 * @param conn */public void free(Connection conn){this.conectionsPool.addLast(conn);}/** * 从连接池中拿一个connection连接对象 * @return */public Connection getConnection(){return this.conectionsPool.removeFirst();}}

（3）JdbcUtils工具类（静态方法）用于建立连接和释放连接。

package cn.itcast.jdbc;import java.sql.CallableStatement;import java.sql.Connection;import java.sql.DriverManager;import java.sql.ResultSet;import java.sql.SQLException;import com.mysql.jdbc.Statement;import cn.itcast.jdbc.datasource.MyDataSource;public final class JdbcUtils {private static MyDataSource myDataSource = null;private JdbcUtils() {}/** * 注册驱动 */static {try {Class.forName("com.mysql.jdbc.Driver");myDataSource = new MyDataSource(); //创建连接池} catch (ClassNotFoundException e) {e.printStackTrace();}}/** * 建立连接 *  * @return */public static Connection getConnection() {return myDataSource.getConnection(); //从连接池获取connection对象}/** * 释放资源 *  * @param rs * @param st * @param conn */public static void free(ResultSet rs, java.sql.Statement st, Connection conn) {// 释放ResultSettry {if (rs != null)rs.close();} catch (SQLException e) {e.printStackTrace();} finally {// 释放Statementtry {if (st != null)st.close();} catch (SQLException e) {e.printStackTrace();} finally {if (conn != null) {myDataSource.free(conn); //将connection放回连接池}}}}}

（4）主函数：用工具类实现的数据库的读和写

package cn.itcast.jdbc;import java.io.BufferedOutputStream;import java.io.File;import java.io.FileInputStream;import java.io.FileOutputStream;import java.io.IOException;import java.io.InputStream;import java.io.OutputStream;import java.sql.ResultSet;import java.sql.SQLException;import com.mysql.jdbc.Connection;import com.mysql.jdbc.PreparedStatement;public class BlobDemo {public static void main(String[] args) throws IOException {create();read();}/** * 读取text数据 * @throws IOException */static void read() throws IOException {Connection conn = null;PreparedStatement ps = null;ResultSet rs = null;try {// 2.建立连接conn = (Connection) JdbcUtils.getConnection();// 3.创建语句String sql = "select big_bin from my_blob where id = 1";ps = (PreparedStatement) conn.prepareStatement(sql);// 4.执行语句rs = ps.executeQuery();//5.处理结果while(rs.next()){//以上两句语句可以使用下面一句代码代替InputStream in =  rs.getBinaryStream(1);//封装数据源File file = new File("copy.jpg");//创建写数据流OutputStream out =new BufferedOutputStream(new FileOutputStream(file));//创建缓存区byte[] bys = new byte[1024];//读写数据方式1int len=0;while((len = in.read(bys))>0){out.write(bys,0,len);}in.close();out.close();}} catch (SQLException e) {e.printStackTrace();} finally {JdbcUtils.free(rs, ps, conn); // 关闭资源}}/** * 写数据到表中 * @param name * @param birthday * @param money * @throws IOException  */static void create() throws IOException {Connection conn = null;PreparedStatement ps = null;ResultSet rs = null;try {// 2.建立连接conn = (Connection) JdbcUtils.getConnection();// 3.创建语句String sql = "insert into my_blob(big_bin) values(?)";ps = (PreparedStatement) conn.prepareStatement(sql);//将该目录下的文件内容写到数据库的my_clob_test表中File file = new File("1.jpg"); FileInputStream fis = new FileInputStream(file);//将“？”代替成数据流ps.setBinaryStream(1, fis, file.length());// 4.执行语句int i = ps.executeUpdate();fis.close();System.out.println("i=" + i);} catch (SQLException e) {e.printStackTrace();} finally {JdbcUtils.free(rs, ps, conn); // 关闭资源}}}