数据库链接池-jdbc

1：DBHelp

package util;

import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;

import org.apache.commons.dbcp.BasicDataSource;

import util.exception.DataAccessUtil;

public class DBHelp<T> {
private static BasicDataSource bs = bilderDataSource();
private static BasicDataSource bilderDataSource(){
String driver;
String url;
String username;
String password;
Properties pro = new Properties();
try {
pro.load(DBHelp.class.getClassLoader().getResourceAsStream("db.properties"));
driver=pro.getProperty("driver");
url=pro.getProperty("url");
username=pro.getProperty("username");
password=pro.getProperty("password");
} catch (IOException e) {
throw new DataAccessUtil("数据库配置文件读取错误！",e);
}
bs= new BasicDataSource();
bs.setDriverClassName(driver);
bs.setUrl(url);
bs.setUsername(username);
bs.setPassword(password);

bs.setMaxActive(20);
bs.setMinIdle(5);
bs.setMaxWait(3000);
bs.setInitialSize(10);
return bs;
}
private Connection getConnection() {
try {
return bs.getConnection();
} catch (Exception e) {
throw new DataAccessUtil("获取数据库连接异常",e);
}
}

/**
* 增删改操作
*
* @param sql
* @param args
*/
public void Updata(String sql, Object... args) {
Connection conn = null;
PreparedStatement stat = null;

try {
conn = getConnection();
stat = conn.prepareStatement(sql);

for (int i = 0; i < args.length; i++) {
stat.setObject(i + 1, args[i]);
}
stat.executeUpdate();
} catch (SQLException e) {
throw new DataAccessUtil("执行"+sql+"异常");
} finally {
close(conn, stat, null);
}
}

/**
* 查询返回唯一值对象
*
* @param rm
* @param sql
* @param args
* @return
*/
public T FindByObject(RowMapper<T> rm, String sql, Object... args) {
T result = null;
Connection conn = null;
PreparedStatement stat = null;
ResultSet rs = null;

try {
conn = getConnection();
stat = conn.prepareStatement(sql);

for (int i = 0; i < args.length; i++) {
stat.setObject(i + 1, args[i]);
}
rs = stat.executeQuery();
if (rs.next()) {
result = rm.mapRow(rs);
}
}catch (SQLException e) {
throw new DataAccessUtil("执行"+sql+"异常");
} finally {
close(conn, stat, rs);
}

return result;
}

public List<T> FindAll(RowMapper<T> rm, String sql, Object... args) {
List<T> list = new ArrayList<T>();
Connection conn = null;
PreparedStatement stat = null;
ResultSet rs = null;

try {
conn = getConnection();
stat = conn.prepareStatement(sql);
for (int i = 0; i < args.length; i++) {
stat.setObject(i + 1, args[i]);
}
rs = stat.executeQuery();
while (rs.next()) {
T result = rm.mapRow(rs);
list.add(result);
}
} catch (SQLException e) {
throw new DataAccessUtil("执行"+sql+"异常");
} finally {
close(conn, stat, rs);
}
return list;
}
/*
*返回记录总数
*/
public int count(String sql, Object... args) {
int t=0;
Connection conn = null;
PreparedStatement stat = null;
ResultSet rs = null;

try {
conn = getConnection();
stat = conn.prepareStatement(sql);
for (int i = 0; i < args.length; i++) {
stat.setObject(i + 1, args[i]);
}
rs = stat.executeQuery();
if (rs.next()) {
t = rs.getInt(0);
}
} catch (SQLException e) {
throw new DataAccessUtil("执行"+sql+"异常");
} finally {
close(conn, stat, rs);
}
return t;
}

/**
* 释放资源
*/
private void close(Connection conn, PreparedStatement stat, ResultSet rs) {
try {
if (conn != null) {
conn.close();
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
if (stat != null) {
stat.close();
}
} catch (SQLException e) {
e.printStackTrace();
} finally {

try {
if (rs != null) {
rs.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
}

 

2：RowMapper

package util;

import java.sql.ResultSet;
import java.sql.SQLException;

public interface RowMapper<T> {
public T mapRow(ResultSet rs) throws SQLException;
}

 

3：db.properties

driver=com.mysql.jdbc.Driver
url=jdbc:mysql:///todo
username=root
password=root