import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import javax.naming.Context;
import javax.naming.InitialContext;
import javax.sql.DataSource;
import org.apache.commons.dbutils.DbUtils;
import org.apache.commons.dbutils.QueryRunner;
/**
* 获得线程安全的数据库连接
*
*/
public class DBManager {
// 数据库配置
private static final String driver = "com.mysql.jdbc.Driver";
private static final String url = "jdbc:mysql://192.168.99.61:3306/XXX?useUnicode=true&characterEncoding=utf-8";
private static final String username = "root";
private static final String password = "root";
private static final String jndiName ="jndi/XXX";//jndi name
// 定义一个用于放置数据库连接的局部线程变量(使每个线程都拥有自己的连接)
private static ThreadLocal<Connection> connContainer = new ThreadLocal<Connection>();
private static ThreadLocal<DataSource> dataSourceContainer = new ThreadLocal<DataSource>();
// 通过配置的全局数据源名称获取连接,线程安全
public static Connection getConnection() {
Connection conn = connContainer.get();
try {
if (conn == null) {
// Obtain our environment naming context
Context initCtx = new InitialContext();
Context envCtx = (Context) initCtx.lookup("java:comp/env");
// Look up our data source
DataSource ds = (DataSource)envCtx.lookup(jndiName);
// Allocate and use a connection from the pool
conn = ds.getConnection();
}
} catch (Exception e) {
e.printStackTrace();
} finally {
connContainer.set(conn);
}
return conn;
}
public static DataSource getDataSource() {
DataSource dataSource = dataSourceContainer.get();
try {
if (dataSource == null) {
// Obtain our environment naming context
Context initCtx = new InitialContext();
Context envCtx = (Context) initCtx.lookup("java:comp/env");
// Look up our data source
dataSource= (DataSource)envCtx.lookup(jndiName);
}
} catch (Exception e) {
e.printStackTrace();
} finally {
dataSourceContainer.set(dataSource);
}
return dataSource;
}
// 获取连接(仅做测试),线程安全
public static Connection getConnection2() {
Connection conn = connContainer.get();
try {
if (conn == null) {
Class.forName(driver);
conn = DriverManager.getConnection(url, username, password);
}
} catch (Exception e) {
e.printStackTrace();
} finally {
connContainer.set(conn);
}
return conn;
}
// 关闭连接
public static void closeConnection() {
Connection conn = connContainer.get();
try {
if (conn != null) {
conn.close();
}
} catch (Exception e) {
e.printStackTrace();
} finally {
connContainer.remove();
}
}
/**
* remove dataSource from ThreadLocal.
*/
public static void removeDataSource(){
dataSourceContainer.remove();
}
public void testJndi2() {
Connection conn = DBManager.getConnection();
try {
QueryRunner run = new QueryRunner(true);
String sql = "insert into sem_tmp_yearly_report (org_name,aim) values (?,?)";
//int inserts = run.update( "insert into sem_tmp_yearly_report (org_name,aim) values (?,?)", "单位1", "范围1" );
Object[][] params = { { "单位1", "范围1" }, { "单位2", "范围2" }
};
// return The number of rows updated per statement
int[] records = run.batch(conn, sql, params);
int rows = 0;
for (int i : records) {
rows += i;
}
System.out.println("影响的行数:" + rows);
} catch (SQLException e) {
e.printStackTrace();
}
DbUtils.closeQuietly(conn);
}
public void testJndi3() {
try {
QueryRunner run = new QueryRunner(DBManager.getDataSource());
String sql = "insert into sem_tmp_yearly_report (org_name,aim) values (?,?)";
Object[][] params = { { "单位11", "范围11" }, { "单位12", "范围12" } };
// return The number of rows updated per statement
int[] records = run.batch(sql, params);
int rows = 0;
for (int i : records) {
rows += i;
}
System.out.println("影响的行数:" + rows);
} catch (SQLException e) {
e.printStackTrace();
}
DBManager.removeDataSource();
}
}