1. jdbc.properties
driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql://127.0.0.1:3306/test?useUnicode=true&characterEncoding=UTF-8
username=admin
password=admin
filters=stat
initialSize=100
maxActive=300
maxWait=60000
timeBetweenEvictionRunsMillis=60000
minEvictableIdleTimeMillis=300000
validationQuery=SELECT 1
testWhileIdle=true
testOnBorrow=false
testOnReturn=false
poolPreparedStatements=false
rewriteBatchedStatements=true
maxPoolPreparedStatementPerConnectionSize=200
2. 常量类
package conf;
public class SysConstants {
/** jdbc配置文件路径(src/main/resources) **/
public static final String JDBC_CONF_FILE = "jdbc.properties";
}
3. Jdbc使用Druid管理连接
package jdbc;
import java.io.IOException;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.Properties;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import com.alibaba.druid.pool.DruidDataSource;
import com.alibaba.druid.pool.DruidDataSourceFactory;
import conf.SysConstants;
@Deprecated
public class JdbcConnectionPool {
public static Logger logger = LoggerFactory.getLogger(JdbcConnectionPool.class);
private static ThreadLocal connectionThreadLocal = new ThreadLocal();
private static DruidDataSource druidDataSource = null;
static {
Properties properties = loadPropertiesFile(SysConstants.JDBC_CONF_FILE);
try {
druidDataSource = (DruidDataSource) DruidDataSourceFactory.createDataSource(properties);
} catch (Exception e) {
logger.error("[JDBC Exception] --> "
+ "Failed to configured the Druid DataSource, the exceprion message is:" + e.getMessage());
}
}
public static Connection getConnection() {
Connection connection = connectionThreadLocal.get();
try {
if(null == connection){
connection = druidDataSource.getConnection();
connectionThreadLocal.set(connection);
}
} catch (SQLException e) {
logger.error("[JDBC Exception] --> "
+ "Failed to create a connection, the exceprion message is:" + e.getMessage());
}
return connection;
}
public static void closeConnection() {
Connection connection = connectionThreadLocal.get();
if(null != connection){
try {
connection.close();
connectionThreadLocal.remove();
} catch (SQLException e) {
logger.error("[JDBC Exception] --> "
+ "Failed to close the DruidPooledConnection, the exceprion message is:" + e.getMessage());
}
}
}
public static void startTransaction() {
Connection conn=connectionThreadLocal.get();
try{
if(conn==null){
conn=getConnection();
connectionThreadLocal.set(conn);
}
conn.setAutoCommit(false);
}catch(Exception e){
logger.error("[JDBC Exception] --> "
+ "Failed to start the transaction, the exceprion message is:" + e.getMessage());
}
}
public static void commit(){
try{
Connection conn=connectionThreadLocal.get();
if(null!=conn){
conn.commit();
}
}catch(Exception e){
logger.error("[JDBC Exception] --> "
+ "Failed to commit the transaction, the exceprion message is:" + e.getMessage());
}
}
public static void rollback(){
try{
Connection conn=connectionThreadLocal.get();
if(conn!=null){
conn.rollback();
connectionThreadLocal.remove();
}
}catch(Exception e){
logger.error("[JDBC Exception] --> "
+ "Failed to rollback the transaction, the exceprion message is:" + e.getMessage());
}
}
private static Properties loadPropertiesFile(String fullFile) {
if (null == fullFile || fullFile.equals("")) {
throw new IllegalArgumentException(
"Properties file path can not be null" + fullFile);
}
Properties prop = new Properties();
try {
prop.load(JdbcConnectionPool.class.getClassLoader().getResourceAsStream(fullFile));
} catch (IOException e) {
logger.error("[Properties Exception] --> "
+ "Can not load jdbc properties, the exceprion message is:" + e.getMessage());
}
return prop;
}
}
4. CRUD
package jdbc;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.List;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
@Deprecated
public class JdbcTemplate {
public static Logger logger = LoggerFactory.getLogger(JdbcTemplate.class);
private static JdbcTemplate jdbcTemplate = null;
private JdbcTemplate(){ }
public static JdbcTemplate getInstance() {
if(jdbcTemplate == null)
jdbcTemplate = new JdbcTemplate();
return jdbcTemplate;
}
public boolean insert(String sql){
boolean f = false;
PreparedStatement prep = null;
Connection conn = null;
try {
conn = JdbcConnectionPool.getConnection();
prep = conn.prepareStatement(sql);
f = prep.execute();
} catch (SQLException e) {
logger.error("[JDBC Exception] --> "
+ "Can not insert, the exceprion message is:" + e.getMessage());
} finally {
try {
if(null != prep)
prep.close();
} catch (SQLException e) {
logger.error("[JDBC Exception] --> "
+ "Failed to close connection, the exceprion message is:" + e.getMessage());
}
}
return f;
}
public void insert(String sql, List> data){
PreparedStatement prep = null;
Connection conn = null;
try {
conn = JdbcConnectionPool.getConnection();
prep = conn.prepareStatement(sql);
JdbcConnectionPool.startTransaction();
for(int i=0; i
List l = data.get(i);
for(int j=0; j
prep.setObject(j+1, l.get(j));
}
prep.addBatch();
}
prep.executeBatch();
prep.clearBatch();
JdbcConnectionPool.commit();
} catch (SQLException e) {
logger.error("[JDBC Exception] --> "
+ "Can not insert, the exceprion message is:" + e.getMessage());
} finally {
try {
if(null != prep)
prep.close();
JdbcConnectionPool.closeConnection();
} catch (SQLException e) {
logger.error("[JDBC Exception] --> "
+ "Failed to close connection, the exceprion message is:" + e.getMessage());
}
}
return;
}
public String select(String sql){
String r = "";
ResultSet rs=null;
PreparedStatement prep = null;
Connection conn = null;
try {
conn = JdbcConnectionPool.getConnection();
prep = conn.prepareStatement(sql);
rs = prep.executeQuery();
r = ResultSetToJson(rs);
} catch (SQLException e) {
logger.error("[JDBC Exception] --> "
+ "Can not select, the exceprion message is:" + e.getMessage());
} finally {
try {
if(null != rs)
rs.close();
if(null != prep)
prep.close();
JdbcConnectionPool.closeConnection();
} catch (SQLException e) {
logger.error("[JDBC Exception] --> "
+ "Failed to close connection, the exceprion message is:" + e.getMessage());
}
}
return r;
}
public static String ResultSetToJson(ResultSet rs){
JSONArray array = new JSONArray();
try {
ResultSetMetaData metaData = rs.getMetaData();
int columnCount = metaData.getColumnCount();
while (rs.next()) {
JSONObject jsonObj = new JSONObject();
for (int i = 1; i <= columnCount; i++) {
String columnName =metaData.getColumnLabel(i);
String value = rs.getString(columnName);
jsonObj.put(columnName, value);
}
array.add(jsonObj);
}
} catch (SQLException e) {
logger.error("[ResultSetToJson Exception] --> "
+ "Failed to covert ResultSet Data to Json, the exceprion message is:" + e.getMessage());
}
return array.toString();
}
}