例如,我有一个表单,我将在其中输入数据库连接的用户,密码,端口等,并在提交时建立并保存与数据库的连接。通常,这是在应用程序运行之前在应用程序属性中完成的,但是我可以在运行时将其添加到应用程序中吗?我这是可能的,我该怎么做?
解决方案是创建一个具有您提到的所有必要连接属性(以及active: boolean
属性(的DatabaseConnection
实体。使用 Spring Data 为该实体创建存储库并创建上层组件。向客户端提供控制器,以在该实体上进行 CRUD。
DatabaseConnectionService 可以提供与其他组件的活动 JDBC 连接以进行查询/更新。
我在我的一个项目中遇到了类似的情况,使用 spring-jdbc 编写代码和维护将很困难。您可以使用普通 jdbc 通过连接参数获取指定的连接对象。
package com;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import org.apache.commons.lang3.exception.ExceptionUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Component;
import org.springframework.util.Assert;
@Component
public class DB2Connection {
private static final Logger logger = LoggerFactory.getLogger(DB2Connection.class);
private static String DRIVER_NAME;
private static String VALID_QUERY;
@Value("${db.valid-query}")
public void setValidQuery(String query){
VALID_QUERY = query;
}
@Value("${db.driverName}")
public void setDriverName(String driverName){
DRIVER_NAME = driverName;
}
/**
* Getting the Database connection
* @param url url of the database
* @param userName username of the db
* @param password password of the db
* @return Connection returns DB Connection object
* */
public static Connection getConnection(final String url, final String userName, final String password){
logger.info("Getting DB Connection...");
Assert.notNull(url, "Database URL can't be null");
Assert.notNull(userName, "Database username can't be null");
Assert.notNull(password, "Database password can't be null");
Connection con = null;
try{
Class.forName(DRIVER_NAME);
con = DriverManager.getConnection(url,userName,password);
// testing for the valid connection
if(testConnection(con)){
logger.info("Valid DB Connection # Connection Tested...");
}else{
logger.info("In-Valid DB Connection...");
}
}catch(ClassNotFoundException cla){
logger.error("Class Not found exception..."+ExceptionUtils.getStackTrace(cla));
}catch(SQLException sqe){
logger.error("SQL Exception..."+ExceptionUtils.getStackTrace(sqe));
}catch(Exception exe){
logger.error("Exception occured while making DB Connection..."+ExceptionUtils.getStackTrace(exe));
}
return con;
}
/**
* Closing the java.sql.Connection class object
*
* @param con Connection object which need to be closed
*
* */
public static void close(Connection con){
logger.info("Closing the connection object...");
try {
if(con != null ){
con.close();
}
} catch (SQLException e) {
logger.error("Exception occured while closing DBConnection..."+ExceptionUtils.getStackTrace(e));
}
}
/**
* This method will test connection if proper or not
* @param con Connection object which need to be checked
* @return flag true if connection is fine, false in case of connection is wrong
* */
public static synchronized boolean testConnection(Connection con){
boolean flag = false;
logger.info("Testing the connection before providing to another process...");
ResultSet rs = null;
Statement stmt = null;
try{
stmt= con.createStatement();
// running the query for validation of the database
rs = stmt.executeQuery(VALID_QUERY);
while(rs.next()){
// if we get Timestamp as return type - valid query is successfully run in the database
Timestamp t = rs.getTimestamp(1);
if( t != null){
flag =true;
}
}
}catch(SQLException e){
logger.error("SQL Exception error..."+ExceptionUtils.getStackTrace(e));
}finally{
if(rs != null){
try {
rs.close();
} catch (SQLException e) {
logger.error("Error in closing Result Set"+ExceptionUtils.getStackTrace(e));
}
}if( stmt != null){
try {
stmt.close();
} catch (SQLException e) {
logger.error("Error in closing Statement"+ExceptionUtils.getStackTrace(e));
}
}
}
return flag;
}
}
在 application.properties 文件中定义 db.valid-query 和 db.driverName 的值。调用 getConnection(( 来获取连接对象。
您还可以创建自己的自定义执行器,该执行器可以显示应用程序中当前处于活动状态的所有当前连接对象。维护当前对象数据库连接对象所需的额外代码。