因为在项目中,要使用到多数据源,且对该项目框架不熟悉,故写出用连接池操作其它的数据库
代码:
package com.ces.component.issuetable.action;
import org.apache.commons.dbcp.BasicDataSource;
import org.apache.commons.dbcp.BasicDataSourceFactory;
import org.apache.struts2.ServletActionContext;
import org.springframework.stereotype.Component;
import java.io.*;
import java.sql.*;
import java.util.*;
/**
* @author Cc
* @data 2019/5/22 15:05
*/
@Component
public class JzsDBPoolConnection {
private voliate static JzsDBPoolConnection jzsDBPoolConnection = null;
private static BasicDataSource dataSource = null;
private static Connection connection = null;
static {
Properties properties = new Properties();
try {
//你当前 路径下的数据库配置文件
String realPath = ServletActionContext.getServletContext().getRealPath("/WEB-INF/conf/db/db.properties");
/*properties.setProperty("driverClassName","com.mysql.jdbc.Driver");
properties.setProperty("url","jdbc:mysql://localhost:3306/arch_szda_jzs");
properties.setProperty("username","root");
properties.setProperty("password","123");
properties.setProperty("initialSize","5");
properties.setProperty("maxActive","10");
properties.setProperty("minIdle","5");
properties.setProperty("maxIdle","10");*/
properties.load(new FileInputStream(realPath));
dataSource = (BasicDataSource) BasicDataSourceFactory.createDataSource(properties);
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
} catch (Exception e) {
e.printStackTrace();
}
}
// volatile + 双重检测机制 -> 禁止指令重排
public static JzsDBPoolConnection getInstance(){
if (jzsDBPoolConnection == null) {
synchronized (JzsDBPoolConnection.class){
if (jzsDBPoolConnection == null) {
jzsDBPoolConnection = new JzsDBPoolConnection();
}
}
}
return jzsDBPoolConnection;
}
public Connection getConnection(){
try {
connection = dataSource.getConnection();
} catch (SQLException e) {
e.printStackTrace();
}
return connection;
}
/**
* @Description 插入数据
* @param:
* @param sql
* @param obj
* @return void
* @author Cc
* @date 2019/5/22 16:00
*/
public static void insertMess(String sql,Object [] obj){
Connection connection = null;
PreparedStatement ps = null;
try {
connection = JzsDBPoolConnection.getInstance().getConnection();
ps = connection.prepareStatement(sql);
for (int i = 0; i < obj.length; i++) {
ps.setObject(i+1,obj[i]);
}
ps.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
}finally {
try {
if (ps != null) {
ps.close();
}
if (connection != null) {
connection.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
/** 批处理更新 **/
public static void batchUpdate(String sql,List<Object[]> list){
Connection connection = null;
PreparedStatement ps = null;
try {
connection = JzsDBPoolConnection.getInstance().getConnection();
ps = connection.prepareStatement(sql);
for (int j = 0; j < list.size(); j++) {
for (int i = 0; i < list.get(j).length; i++) {
ps.setObject(i+1,list.get(j)[i]);
}
ps.addBatch();
}
ps.executeBatch();
ps.clearBatch();
} catch (SQLException e) {
e.printStackTrace();
}finally {
try {
if (ps != null) {
ps.close();
}
if (connection != null) {
connection.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
/**
* @Description 查询数据。 这个方法模板是大家值得借鉴的地方
* @param:
* @param sql
* @param objects
* @return java.util.List<java.util.Map<java.lang.String,java.lang.Object>>
* @author Cc
* @date 2019/5/22 16:10
*/
public static List<Map<String,Object>> queryMess(String sql, Object [] objects){
List<Map<String,Object>> cList = new ArrayList<>();
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
try {
System.out.println("进入查询");
conn = JzsDBPoolConnection.getInstance().getConnection();
ps = conn.prepareStatement(sql);
for (int i = 0; i < objects.length; i++) {
ps.setObject(i+1,objects[i]);
}
rs = ps.executeQuery(sql);
//这里是精华
ResultSetMetaData rsd = rs.getMetaData();
while (rs.next()) {
Map<String,Object> cMap = new HashMap<>();
for (int i = 0; i < rsd.getColumnCount(); i++) {
cMap.put(rsd.getColumnName(i+1),rs.getObject(i+1));
}
cList.add(cMap);
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
try {
if (rs != null) {
rs.close();
}
if (ps != null) {
ps.close();
}
if (conn != null) {
conn.close();
}
}catch (SQLException e){
e.printStackTrace();
}
}
return cList;
}
}
调用:
List<Map<String, Object>> jMaps = JzsDBPoolConnection.queryMess(cjzsSql, new Object[0])