java创建connection,使用JDBC和Spring Boot创建java.sql.Connection

I wanted to create two static functions: java.sql.Connection Connection.getConnection() and void Connection.closeConection() to obtain and terminate connections so that I can execute JDBC queries on my remote RDS instance. Here's what I wrote:

application.properties

spring.datasource.url=jdbc:mysql://myRDSEndpoint:3306/mySchemaName

spring.datasource.username=myUsername

spring.datasource.password=myPassword

spring.datasource.driver-class-name=com.mysql.jdbc.Driver

RDSConnection.java:

import org.springframework.beans.factory.annotation.Autowired;

import org.springframework.beans.factory.annotation.Value;

import org.springframework.stereotype.Component;

@Component

public class RDSConnection {

public static String driver;

public static String url;

public static String user;

public static String pass;

@Autowired

public RDSConnection getRDSConnection(@Value("${spring.datasource.driver-class-name}") String driver,

@Value("${spring.datasource.url}") String url,

@Value("${spring.datasource.username}") String user,

@Value("${spring.datasource.password}") String pass) {

RDSConnection.driver = driver;

RDSConnection.url = url;

RDSConnection.user = user;

RDSConnection.pass = pass;

return this;

}

}

Connection.java

import lombok.extern.slf4j.Slf4j;

import org.springframework.stereotype.Component;

import java.sql.DriverManager;

import java.sql.SQLException;

@Component

@Slf4j

public class Connection {

private static java.sql.Connection sqlConnection;

public static java.sql.Connection getConnection() {

String driver = RDSConnection.driver;

String url = RDSConnection.url;

String user = RDSConnection.user;

String pass = RDSConnection.pass;

if(sqlConnection != null) return sqlConnection;

try {

Class.forName(driver);

sqlConnection = DriverManager.getConnection(url, user, pass);

} catch (ClassNotFoundException | SQLException e) {

log.error(e.getMessage());

}

return sqlConnection;

}

public static void closeConnection(java.sql.Connection sqlConnection) {

try {

sqlConnection.close();

} catch (SQLException e) {

log.error(e.getMessage());

}

}

}

This works, but I was wondering if there is a better way to do this. Is there a way to make the static variables in RDSConection final as well, since I know that the spring.datasource.* values are not going to change? If so, how do I inject the values from application.properties?

解决方案

There are some things that can be improved...

First, I you recommend you using SPRING DATA, you can use EntityManager and get the connection with this:

@PersistenceContext

private EntityManager entityManager;

public void method() {

... entityManager.unwrap(Session.class) ...

}

For use spring data with spring boot, just use:

@SpringBootApplication

@EntityScan(basePackages = {

"com.your.project"

})

@EnableJpaRepositories(basePackages = {

"com.your.project"

})

public class Application {

public static void main(String[] args) {

SpringApplication.run(Application.class, args);

}

}

Second, if you really want to use JDBC, I hope you use JAVA 7 / +

In this case, first use try-with-resources,

this will close the connection automatically.

Something like:

try (Connection sqlConnection = DriverManager.getConnection(url, user, pass);

PreparedStatement ps = sqlConnection.createPreparedStatement(sql);

ResultSet rs = ps.executeQuery()) {

// process the resultset here, all resources will be cleaned up

} catch (SQLException e) {

log.error(e.getMessage());

}

}

And Class.forName(driver); It is no longer necessary. In Java 6+ because DriverManager will auto-load drivers found on the classpath.

For more info: Why do I need to explicitly write Class.forName() when using JDK 8?

Finally, if you really do not want to use the Spring settings, you do not have a reason to use application.properties. You can use constants that will be static and final.

PS: Remember that you have to close all ResultSet and PreparedStatement, not just connection as a precaution.