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) {
		// 释放ResultSet
		try {
			if (rs != null)
				rs.close();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			// 释放Statement
			try {
				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];
				//读写数据方式1
				int 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); // 关闭资源
		}
	}
	
}