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

问题描述

我想创建两个静态函数： java.sql.Connection Connection.getConnection（）和 void Connection.closeConection（）获取并终止连接，以便我可以在远程RDS实例上执行JDBC查询。这是我写的：

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

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：

---

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

---

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());
        }
    }
}

---

这可行，但是我想知道是否有更好的方法可以做到这一点。还有一种方法可以使 RDSConection 中的静态变量也最终化，因为我知道 spring.datasource。* 价值观不会改变吗？如果是这样，如何从 application.properties 注入值？

---

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...

首先，我建议您使用SPRING DATA，可以使用EntityManager并获得与此的连接：

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) ...
}

有关更多信息：

要在spring boot中使用spring数据，只需使用：

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);
    }

}

第二，如果您确实要使用JDBC，希望您使用JAVA 7 / +

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

在这种情况下，请先使用try-with-resources，
将自动关闭连接。

In this case, first use try-with-resources,this will close the connection automatically.

类似的东西：

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());
    }
}

有关更多信息：

和 Class.forName（driver）; 不再需要。在Java 6+中，因为DriverManager将自动加载在类路径中找到的驱动程序。
有关更多信息：

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?

最后，如果您真的不想使用Spring，设置，则没有理由使用application.properties。

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：请记住，您必须关闭所有ResultSet和PreparedStatement，而不仅仅是关闭连接。

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

这篇关于使用JDBC和Spring Boot创建java.sql.Connection的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！