一 JAVA项目扩展多数据库连接背景与选择技术方案。
有时候一些旧项目因业务变更而需要多数据库连接,但原本项目往往只配置了一个数据源。像SpringBoot框架,如果需要重新配置多数据,往往原有的数据源也要重新调整配置,至少要设置一个默认数据源。于是就开始使用多数据源配置方案。个人认为如果项目一开始就多数据源的可以这样扩展,要是后续项目变更只为了多连接一个数据库操作就大可不必这样,修改因尽量不影响系统原有功能及架构,应尽量不要修改原有代码及配置。我们可以选择增加一个jdbc的数据源,只需要手工增加两个类即可满足实现多连接一个数据库操作,这不更简单与实用。
二 JAVA 代码实现数据库连接池
使用 JAVA 代码实现一个简单的数据库连接池:
1 在spring配置文件自定义新db连接参数配置:
sys-param:
use_db2: Y
db2:
url: jdbc:mysql://127.0.0.1:3306/qyhua?timeZone=TMC&useUnicode=true&characterEncoding=UTF-8&autoReconnect=true&useSSL=false&zeroDateTimeBehavior=convertToNull&allowMultiQueries=true&useTimezone=true&serverTimezone=GMT%2B8
username: root
password: 123456
2 获取注入参数
import org.springframework.beans.factory.annotation.Value;
import org.springframework.context.annotation.Configuration;
import org.springframework.stereotype.Service;
/**
* @author: hua
*/
@Service
@Configuration
public class DataManagerConfig {
public static String use_db2="N";
@Value("${sys-param.use_db2}")
public void setUseDb2(String use_db2) {
this.use_db2=use_db2;
}
public static String url = "";
public static String username = "";
public static String password = "";
@Value(value = "${sys-param.db2.url}")
public void setUrl(String url) {
this.url = url;
}
@Value(value = "${sys-param.db2.username}")
public void setUsername(String username) {
this.username = username;
}
@Value(value = "${sys-param.db2.password}")
public void setPassword(String password) {
this.password = password;
}
}
3 一个简单的jdbc连接池
import org.springframework.stereotype.Service;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
/**
* @author hua
* @date 2024-01-26 10:59
*/
@Service
public class MySqlConnPool {
private int initialPoolSize=5;
private List<Connection> connectionPool;
public MySqlConnPool() {
if("Y".equals(DataManagerConfig.use_db2)){
try {
Class.forName("com.mysql.cj.jdbc.Driver");
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
this.connectionPool = new ArrayList<>(initialPoolSize);
initializePool(initialPoolSize);
}
}
private void initializePool(int initialPoolSize) {
try {
for (int i = 0; i < initialPoolSize; i++) {
Connection connection = DriverManager.getConnection(DataManagerConfig.url, DataManagerConfig.username, DataManagerConfig.password);
connectionPool.add(connection);
}
} catch (Exception e) {
e.printStackTrace();
}
}
public synchronized Connection getConnection() {
if (connectionPool.isEmpty()) {
// 如果连接池为空,则创建新连接
try {
Connection connection = DriverManager.getConnection(DataManagerConfig.url, DataManagerConfig.username, DataManagerConfig.password);
connectionPool.add(connection);
} catch (SQLException e) {
e.printStackTrace();
}
}
// 从连接池中获取一个连接
return connectionPool.remove(connectionPool.size() - 1);
}
public synchronized void releaseConnection(Connection connection) {
try {
if (connection != null && !connection.isClosed()) {
// 将连接放回连接池
connectionPool.add(connection);
}
} catch (SQLException e) {
e.printStackTrace();
}
}
public void closeAllConn() {
for (Connection connection : connectionPool) {
try {
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
connectionPool.clear();
}
}
4 使用jdbc连接操作数据库:
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.time.LocalDateTime;
import java.util.List;
/**
* @author hua
* @date 2024-01-25 16:45
*/
@Service
public class JdbcService {
@Autowired
MySqlConnPool myConnPool;
/**
* 执行保存日记
* @param list
*/
public void exeUpdate(List<AliIotLog> list) {
// 数据库连接
Connection conn = myConnPool.getConnection();
PreparedStatement stmt = null;
try {
// 创建Statement对象
stmt = conn.prepareStatement("INSERT INTO xxxx (in_hex_str, out_hex_str, addr, ctime, charge_addr) VALUES(?,?,?,?,?);");
conn.setAutoCommit(false);
for (int i = 0; i < list.size(); i++) {
AliIotLog l=list.get(i);
stmt.setString(1, l.getInHexStr());
stmt.setString(2, l.getOutHexStr());
stmt.setString(3, l.getAddr());
stmt.setObject(4, LocalDateTime.now());
stmt.setString(5, l.getChargeAddr());
stmt.addBatch();
}
stmt.executeBatch();
conn.commit();
} catch (Exception e) {
e.printStackTrace();
} finally {
// 关闭资源
try {
if (stmt != null) {
stmt.close();
stmt = null;
}
} catch (Exception e) {
e.printStackTrace();
}
myConnPool.releaseConnection(conn);
}
}
}