继续上篇,我们这篇主要讲解数据库连接池。
我们来看下什么是数据库连接池:
连接池基本的思想是在系统初始化的时候,将数据库连接作为对象存储在内存中,当用户需要访问数据库时,并非建立一个新的连接,而是从连接池中取出一个已建立的空闲连接对象。使用完毕后,用户也并非将连接关闭,而是将连接放回连接池中,以供下一个请求访问使用。而连接的建立、断开都由连接池自身来管理。同时,还可以通过设置连接池的参数来控制连接池中的初始连接数、连接的上下限数以及每个连接的最大使用次数、最大空闲时间等等。也可以通过其自身的管理机制来监视数据库连接的数量、使用情况等常用的开源数据库连接池有很多,这里我们以DBCP为例:
一,引入依赖包:
<!-- dbcp 数据源 -->
<dependency>
<groupId>org.apache.commons</groupId>
<artifactId>commons-dbcp2</artifactId>
<version>2.1.1</version>
</dependency>
<!-- dbcp 依赖包 -->
<dependency>
<groupId>org.apache.commons</groupId>
<artifactId>commons-pool2</artifactId>
<version>2.4.2</version>
</dependency>
二,添加配置项dbcp.properties:
注意这里配置参数名是固定的,具体每个参数的说明,可以跳转这里查看:http://blog.csdn.net/kerafan/article/details/50382998
driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/yun?serverTimezone=UTC&characterEncoding=utf-8
username=root
password=centos
#\u521D\u59CB\u5316\u8FDE\u63A5
initialSize=10
#\u6700\u5927\u8FDE\u63A5\u6570\u91CF
maxActive=50
#\u6700\u5927\u7A7A\u95F2\u8FDE\u63A5
maxIdle=20
#\u6700\u5C0F\u7A7A\u95F2\u8FDE\u63A5
minIdle=5
#\u8D85\u65F6\u7B49\u5F85\u65F6\u95F4\u4EE5\u6BEB\u79D2\u4E3A\u5355\u4F4D
maxWait=60000
#\u6307\u5B9A\u81EA\u52A8\u63D0\u4EA4\u72B6\u6001
defaultAutoCommit=true
三,重写工具类DbcpUtil,主要是获取数据库连接的方法重写,其他不变
package com.example.util;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import java.util.Properties;
import javax.sql.DataSource;
import org.apache.commons.dbcp2.BasicDataSourceFactory;
public class DbcpUtil {
private static DataSource ds = null;
//在静态代码块中创建数据库连接池
static {
//加载dbcp.properties配置文件
try {
InputStream in = DbcpUtil.class.getClassLoader().getResourceAsStream("/dbcp.properties");
Properties prop = new Properties();
prop.load(in);
//获取数据源
ds = BasicDataSourceFactory.createDataSource(prop);
} catch (IOException e) {
e.printStackTrace();
} catch (Exception e) {
e.printStackTrace();
}
}
/**
* 获取连接
* @return
*/
public static Connection getConnection () {
Connection conn = null;
try {
conn = ds.getConnection();
} catch (SQLException e) {
e.printStackTrace();
}
return conn;
}
/**
* 关闭数据库连接
* @param conn
*/
public static void closeConnection(Connection conn){
if (conn != null) {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
/**
* 关闭语句对象
* @param stmt
*/
public static void closeStatement(Statement stmt){
if (stmt != null) {
try {
stmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
/**
* 关闭结果集对象
* @param stmt
*/
public static void closeResultSet (ResultSet rs) {
if (rs != null) {
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
/**
* 获取结果集数据集合
* @param table_name
* @param sql
* @return
*/
public static List<Map<String,Object>> getColumnDatas (String sql) {
List<Map<String,Object>> datas = new ArrayList<Map<String,Object>>();
Connection conn = getConnection();
Statement stmt = null;
ResultSet rs = null;
try {
stmt = conn.createStatement();
rs = stmt.executeQuery(sql);
ResultSetMetaData rsmd = rs.getMetaData();
int count = rsmd.getColumnCount();
//这里需要使用rs.next()做判断,不然会报错:Before start of result set
while(rs.next()){
//按照数据库列顺序排列
Map<String,Object> map = new LinkedHashMap<String,Object>();
for (int i = 1; i <= count; i++) {
String columnName = rsmd.getColumnLabel(i);
Object columnData = rs.getObject(i);
map.put(columnName, columnData);
}
datas.add(map);
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
closeResultSet(rs);
closeStatement(stmt);
closeConnection(conn);
}
return datas;
}
/**
* 预编译方式获取结果集数据集合
* @param table_name
* @param sql
* @return
*/
public static List<Map<String,Object>> getColumnDatas (String sql, Object... args) {
List<Map<String,Object>> datas = new ArrayList<Map<String,Object>>();
Connection conn = getConnection();
PreparedStatement stmt = null;
ResultSet rs = null;
try {
stmt = conn.prepareStatement(sql);
// 为pStement对象设置SQL参数值
for (int i = 0; i < args.length; i++) {
stmt.setObject(i + 1, args[i]);
}
stmt.execute();
rs = stmt.getResultSet();
ResultSetMetaData rsmd = rs.getMetaData();
int count = rsmd.getColumnCount();
//这里需要使用rs.next()做判断,不然会报错:Before start of result set
while(rs.next()){
for (int i = 1; i <= count; i++) {
String columnName = rsmd.getColumnLabel(i);
Object columnData = rs.getObject(i);
Map<String,Object> map = new LinkedHashMap<String,Object>();
map.put(columnName, columnData);
datas.add(map);
}
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
closeResultSet(rs);
closeStatement(stmt);
closeConnection(conn);
}
return datas;
}
/**
* 增删改操作
* @param sql
* @return
* @throws SQLException
*/
public static int getUpdateResult(String sql) {
int result = 0;
Connection conn = getConnection();
Statement stmt = null;
try {
stmt = conn.createStatement();
result = stmt.executeUpdate(sql);
} catch (SQLException e) {
e.printStackTrace();
} finally {
closeStatement(stmt);
closeConnection(conn);
}
return result;
}
/**
* 预编译方式实现增删改操作
* @param sql
* @return
* @throws SQLException
*/
public static int getUpdateResult(String sql, Object... args) {
int result = 0;
Connection conn = getConnection();
PreparedStatement stmt = null;
try {
stmt = conn.prepareStatement(sql);
// 为pStement对象设置SQL参数值
for (int i = 0; i < args.length; i++) {
stmt.setObject(i + 1, args[i]);
}
result = stmt.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
} finally {
closeStatement(stmt);
closeConnection(conn);
}
return result;
}
}
四,我们也可以实现自己的数据库连接池,具体代码如下
1) 在db.properties中添加配置:
#jdbcpool
db.jdbcPoolInitSize=10
2) 新建连接池模拟类JdbcPool
package com.example.util;
import java.io.PrintWriter;
import java.lang.reflect.InvocationHandler;
import java.lang.reflect.Method;
import java.lang.reflect.Proxy;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.SQLFeatureNotSupportedException;
import java.util.LinkedList;
import java.util.logging.Logger;
import javax.sql.DataSource;
/**
* 创建数据库连接池, 重写获取数据库连接方法 让数据库连接池中保持一定数量的连接数 不必每个请求都发往数据库,进行一次开关操作,消耗性能
*
* @author Administrator
*
*/
public class JdbcPool implements DataSource {
/**
* 模拟连接池
*/
private static LinkedList<Connection> listConnections = new LinkedList<Connection>();
/**
* 初始化连接池数量
*/
static {
try {
// 加载数据库驱动
Class.forName("com.mysql.jdbc.Driver");
String url = PropertiesUtil.getInstance().getProperty("db.url");
String username = PropertiesUtil.getInstance().getProperty("db.username");
String password = PropertiesUtil.getInstance().getProperty("db.password");
int jdbcPoolInitSize = Integer.parseInt(PropertiesUtil.getInstance().getProperty("db.jdbcPoolInitSize"));
for (int i = 0; i < jdbcPoolInitSize; i++) {
Connection conn = DriverManager.getConnection(url, username, password);
listConnections.add(conn);
}
} catch (ClassNotFoundException e1) {
e1.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}
}
public PrintWriter getLogWriter() throws SQLException {
// TODO Auto-generated method stub
return null;
}
public void setLogWriter(PrintWriter out) throws SQLException {
// TODO Auto-generated method stub
}
public void setLoginTimeout(int seconds) throws SQLException {
// TODO Auto-generated method stub
}
public int getLoginTimeout() throws SQLException {
// TODO Auto-generated method stub
return 0;
}
public Logger getParentLogger() throws SQLFeatureNotSupportedException {
// TODO Auto-generated method stub
return null;
}
public <T> T unwrap(Class<T> iface) throws SQLException {
// TODO Auto-generated method stub
return null;
}
public boolean isWrapperFor(Class<?> iface) throws SQLException {
// TODO Auto-generated method stub
return false;
}
/**
* 获取数据库连接
*/
public Connection getConnection() throws SQLException {
// 如果数据库连接池中的连接对象的个数大于0
if (listConnections.size() > 0) {
// 从listConnections集合中获取一个数据库连接
final Connection conn = listConnections.removeFirst();
System.out.println("listConnections数据库连接池大小是" + listConnections.size());
// 返回Connection对象的代理对象
return (Connection) Proxy.newProxyInstance(JdbcPool.class.getClassLoader(), conn.getClass().getInterfaces(),
new InvocationHandler() {
public Object invoke(Object proxy, Method method, Object[] args) throws Throwable {
if (!method.getName().equals("close")) {
return method.invoke(conn, args);
} else {
// 如果调用的是Connection对象的close方法,就把conn还给数据库连接池
listConnections.add(conn);
System.out.println(conn + "被还给listConnections数据库连接池了!!");
System.out.println("listConnections数据库连接池大小为" + listConnections.size());
return null;
}
}
});
} else {
throw new RuntimeException("对不起,数据库忙");
}
}
public Connection getConnection(String username, String password) throws SQLException {
// TODO Auto-generated method stub
return null;
}
}
五,再新建一个工具类JdbcUtil,更改获取数据库连接的方法
package com.example.util;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
public class JdbcUtil {
private static JdbcPool pool = new JdbcPool();
/**
* 获取数据库连接
* @return
*/
public static Connection getConnection () {
Connection conn = null;
try {
conn = pool.getConnection();
} catch (SQLException e) {
e.printStackTrace();
}
return conn;
}
/**
* 关闭数据库连接
* @param conn
*/
public static void closeConnection(Connection conn){
if (conn != null) {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
/**
* 关闭语句对象
* @param stmt
*/
public static void closeStatement(Statement stmt){
if (stmt != null) {
try {
stmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
/**
* 关闭结果集对象
* @param stmt
*/
public static void closeResultSet (ResultSet rs) {
if (rs != null) {
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
/**
* 获取结果集数据集合
* @param table_name
* @param sql
* @return
*/
public static List<Map<String,Object>> getColumnDatas (String sql) {
List<Map<String,Object>> datas = new ArrayList<Map<String,Object>>();
Connection conn = getConnection();
Statement stmt = null;
ResultSet rs = null;
try {
stmt = conn.createStatement();
rs = stmt.executeQuery(sql);
ResultSetMetaData rsmd = rs.getMetaData();
int count = rsmd.getColumnCount();
//这里需要使用rs.next()做判断,不然会报错:Before start of result set
while(rs.next()){
//按照数据库列顺序排列
Map<String,Object> map = new LinkedHashMap<String,Object>();
for (int i = 1; i <= count; i++) {
String columnName = rsmd.getColumnLabel(i);
Object columnData = rs.getObject(i);
map.put(columnName, columnData);
}
datas.add(map);
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
closeResultSet(rs);
closeStatement(stmt);
closeConnection(conn);
}
return datas;
}
/**
* 预编译方式获取结果集数据集合
* @param table_name
* @param sql
* @return
*/
public static List<Map<String,Object>> getColumnDatas (String sql, Object... args) {
List<Map<String,Object>> datas = new ArrayList<Map<String,Object>>();
Connection conn = getConnection();
PreparedStatement stmt = null;
ResultSet rs = null;
try {
stmt = conn.prepareStatement(sql);
// 为pStement对象设置SQL参数值
for (int i = 0; i < args.length; i++) {
stmt.setObject(i + 1, args[i]);
}
stmt.execute();
rs = stmt.getResultSet();
ResultSetMetaData rsmd = rs.getMetaData();
int count = rsmd.getColumnCount();
//这里需要使用rs.next()做判断,不然会报错:Before start of result set
while(rs.next()){
for (int i = 1; i <= count; i++) {
String columnName = rsmd.getColumnLabel(i);
Object columnData = rs.getObject(i);
Map<String,Object> map = new LinkedHashMap<String,Object>();
map.put(columnName, columnData);
datas.add(map);
}
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
closeResultSet(rs);
closeStatement(stmt);
closeConnection(conn);
}
return datas;
}
/**
* 增删改操作
* @param sql
* @return
* @throws SQLException
*/
public static int getUpdateResult(String sql) {
int result = 0;
Connection conn = getConnection();
Statement stmt = null;
try {
stmt = conn.createStatement();
result = stmt.executeUpdate(sql);
} catch (SQLException e) {
e.printStackTrace();
} finally {
closeStatement(stmt);
closeConnection(conn);
}
return result;
}
/**
* 预编译方式实现增删改操作
* @param sql
* @return
* @throws SQLException
*/
public static int getUpdateResult(String sql, Object... args) {
int result = 0;
Connection conn = getConnection();
PreparedStatement stmt = null;
try {
stmt = conn.prepareStatement(sql);
// 为pStement对象设置SQL参数值
for (int i = 0; i < args.length; i++) {
stmt.setObject(i + 1, args[i]);
}
result = stmt.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
} finally {
closeStatement(stmt);
closeConnection(conn);
}
return result;
}
}
六,新建测试类 JdbcPoolServlet
package com.example.servlet;
import java.io.IOException;
import java.io.PrintWriter;
import java.util.List;
import java.util.Map;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import com.alibaba.fastjson.JSON;
import com.example.util.DbcpUtil;
import com.example.util.JdbcUtil;
/**
* 测试jdbc连接池
* @author Administrator
*
*/
public class JdbcPoolServlet extends HttpServlet {
private static final long serialVersionUID = -545856862126300163L;
@Override
protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
//测试数据库连接池是否生效,简单查询操作
String sql = "select * from user_info";
//自定义的连接池
List<Map<String,Object>> list1 = JdbcUtil.getColumnDatas(sql);
String json1 = JSON.toJSONString(list1);
//dbcp 数据源
List<Map<String,Object>> list2 = DbcpUtil.getColumnDatas(sql);
String json2 = JSON.toJSONString(list2);
resp.setContentType("text/html");
resp.setCharacterEncoding("utf-8");
PrintWriter out = resp.getWriter();
out.println("使用自定义连接池:"+json1);
out.print("<br/>");
out.println("使用dbcp数据源:"+json2);
out.close();
}
@Override
protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
// TODO Auto-generated method stub
super.doPost(req, resp);
}
}
七,在web.xml中添加映射:
<!-- 测试自定义的数据库连接池 -->
<servlet>
<servlet-name>JdbcPoolServlet</servlet-name>
<servlet-class>com.example.servlet.JdbcPoolServlet</servlet-class>
</servlet>
<servlet-mapping>
<servlet-name>JdbcPoolServlet</servlet-name>
<url-pattern>/test_jdbcpool</url-pattern>
</servlet-mapping>
八,启动测试:http://localhost:8088/webDemo/test_jdbcpool
==============================================================================================
jdbc 作为所有持久化数据库访问框架的基础,还有许多需要学习的地方,建议参看博客:
http://www.cnblogs.com/xdp-gacl/tag/JavaWeb%E5%AD%A6%E4%B9%A0%E6%80%BB%E7%BB%93/default.html?page=1
下篇我们再回过去讲,如何从数据库获取数据并导出excel,java_web 学习记录(十):jxl excel export(二)