Spring 4 JDBC - 如何加载数据库属性和优化(使用缓存或数据库连接池) [英] Spring 4 JDBC - How to load DB Properties and optimize (using Cache or DB Connection Pool)
问题描述
我正在维护一个用 Spring MVC 4.3.9.RELEASE(不是 Spring Boot)编写的代码库...
Am maintaining a codebase which was written in Spring MVC 4.3.9.RELEASE (not Spring Boot)...
在 src/main/resources 下:
Under src/main/resources:
有两个不同的数据库配置文件:
There are two different database configuration files:
sampledb.xml
sampledb.xml
<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:context="http://www.springframework.org/schema/context"
xsi:schemaLocation="
http://www.springframework.org/schema/beans
http://www.springframework.org/schema/beans/spring-beans-3.0.xsd
http://www.springframework.org/schema/context
http://www.springframework.org/schema/context/spring-context-3.0.xsd">
<!-- Initialization for data source dbcp -->
<bean id="sampleDatabase" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
<property name="driverClassName"><value>com.mysql.jdbc.Driver</value></property>
<property name="url"><value>jdbc:mysql://localhost/sampledb?zeroDateTimeBehavior=convertToNull</value></property>
<property name="username"><value>root/value></property>
<property name="password"><value></value></property>
<property name="maxIdle" value="10"/>
<property name="maxActive" value="50"/>
<property name="maxWait" value="100"/>
<property name="defaultAutoCommit" value="false"/>
<property name="removeAbandoned" value="true"/>
<property name="removeAbandonedTimeout" value="1"/>
<property name="minIdle" value="0"></property>
<property name="timeBetweenEvictionRunsMillis" value="1000"></property>
<property name="minEvictableIdleTimeMillis" value="1000"></property>
</bean>
</beans>
eventsdb.xml
eventsdb.xml
<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:context="http://www.springframework.org/schema/context"
xsi:schemaLocation="
http://www.springframework.org/schema/beans
http://www.springframework.org/schema/beans/spring-beans-3.0.xsd
http://www.springframework.org/schema/context
http://www.springframework.org/schema/context/spring-context-3.0.xsd">
<!-- Initialization for data source dbcp -->
<bean id="eventsDatabase" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
<property name="driverClassName"><value>com.mysql.jdbc.Driver</value></property>
<property name="url"><value>jdbc:mysql://localhost/eventsdb?zeroDateTimeBehavior=convertToNull</value></property>
<property name="username"><value>root</value></property>
<property name="password"><value></value></property>
<property name="maxIdle" value="10"/>
<property name="maxActive" value="50"/>
<property name="maxWait" value="100"/>
<property name="defaultAutoCommit" value="false"/>
<property name="removeAbandoned" value="true"/>
<property name="removeAbandonedTimeout" value="1"/>
<property name="minIdle" value="0"></property>
<property name="timeBetweenEvictionRunsMillis" value="1000"></property>
<property name="minEvictableIdleTimeMillis" value="1000"></property>
</bean>
</beans>
WEB-INF/web.xml:
WEB-INF/web.xml:
<?xml version="1.0" encoding="UTF-8"?>
<web-app id="WebApp_ID" version="2.4"
xmlns="http://java.sun.com/xml/ns/j2ee"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://java.sun.com/xml/ns/j2ee http://java.sun.com/xml/ns/j2ee/web-app_2_4.xsd">
<display-name>MyApp</display-name>
<welcome-file-list>
<welcome-file>index.html</welcome-file>
<welcome-file>index.jsp</welcome-file>
</welcome-file-list>
<servlet>
<servlet-name>mvc-dispatcher</servlet-name>
<servlet-class>org.springframework.web.servlet.DispatcherServlet</servlet-class>
<load-on-startup>1</load-on-startup>
</servlet>
<servlet-mapping>
<servlet-name>mvc-dispatcher</servlet-name>
<url-pattern>/</url-pattern>
</servlet-mapping>
<context-param>
<param-name>contextConfigLocation</param-name>
<param-value>/WEB-INF/mvc-dispatcher-servlet.xml</param-value>
</context-param>
<listener>
<listener-class>org.springframework.web.context.ContextLoaderListener</listener-class>
</listener>
</web-app>
WEB-INF/mvc-dispatcher-servlet.xml:
WEB-INF/mvc-dispatcher-servlet.xml:
<beans xmlns="http://www.springframework.org/schema/beans"
xmlns:context="http://www.springframework.org/schema/context"
xmlns:mvc="http://www.springframework.org/schema/mvc"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:util="http://www.springframework.org/schema/util"
xsi:schemaLocation="
http://www.springframework.org/schema/beans
http://www.springframework.org/schema/beans/spring-beans.xsd
http://www.springframework.org/schema/context
http://www.springframework.org/schema/context/spring-context.xsd
http://www.springframework.org/schema/mvc
http://www.springframework.org/schema/mvc/spring-mvc.xsd
http://www.springframework.org/schema/util
http://www.springframework.org/schema/util/spring-util.xsd">
<context:component-scan base-package="com.myapp.rest.controllers" />
<mvc:annotation-driven />
<bean id="viewResolver"
class="org.springframework.web.servlet.view.InternalResourceViewResolver">
<property name="prefix">
<value>/WEB-INF/pages/</value>
</property>
<property name="suffix">
<value>.jsp</value>
</property>
</bean>
</beans>
示例网络服务代码:
package com.myapp.rest.controllers;
@Controller
@RequestMapping("/v2")
public class MyController {
@RequestMapping(value="users/{userId}",method=RequestMethod.GET)
public @ResponseBody Object getUserDetails(@PathVariable String userId){
Object response=null;
UserDAO dao = UserDAO.getInstance();
response=dao.getUser(userId);
return response;
}
}
用户道:
public class UserDAO {
private static UserDAO instance = null;
private JdbcTemplate jdbcTemplateObject = null;
public static UserDAO getInstance() {
if(instance == null) {
synchronized(UserDAO.class) {
if(instance == null) {
instance = new UserDAO();
}
}
}
return instance ;
}
UserDAO() {
try {
initializeDB();
}
catch(Exception e) {
e.printStackTrace();
}
}
private void initializeDB() {
try {
ApplicationContext context = new ClassPathXmlApplicationContext("sampledb.xml");
DataSource dataSource = (DataSource) context.getBean("sampleDatabase");
this.jdbcTemplateObject = new JdbcTemplate(dataSource);
}
catch (Exception e) {
e.printStackTrace();
}
}
// others methods which do the actual queries using Spring JDBC
}
以前的作者在每个 DAO 中都使用了这种模式(使用 ApplicationContext 初始化数据库)(代码库中有 20 个不同的 DAO，每个都使用相同的两个数据库配置文件做同样的事情)！
The previous author has used this pattern (initializing the DB using ApplicationContext) in every single DAO (there's like 20 different ones in the codebase each doing the same thing with the same two database config files)!
问题:
这似乎很不够(好像应该做一次)，怎么能在war文件加载到Tomcat时就做一次(加载基于Spring的DB配置文件)?
This seems very inadequate (seems like should be done once), how can this (loading Spring based DB config files) be done once as soon as the war file loadings into Tomcat?
提高性能的最佳技术是什么(例如，我应该使用缓存系统还是数据库连接池)?
What's the best techniques for performance gains (e.g. should I use a caching system or a database connection pool)?
非常感谢任何建议...
Any advice is greatly appreciated...
推荐答案
private void initializeDB() {
try {
ApplicationContext context = new ClassPathXmlApplicationContext("sampledb.xml");
DataSource dataSource = (DataSource) context.getBean("sampleDatabase");
this.jdbcTemplateObject = new JdbcTemplate(dataSource);
}
catch (Exception e) {
e.printStackTrace();
}
}
这段代码非常危险，根据上下文的大小，您最终会遇到问题.这里发生的情况是，每次需要一个对象时，您都在加载整个应用程序，您将打开与数据库的连接(由于连接太多，最终将停止工作)您将遇到奇怪的事务问题，并且可能(取决于大小)) 内存问题.(当然，如果那是您想要的，请按此方式进行).
This code is very dangerous, depending on the size of your context, you eventually will run into issues. What happens here is you are loading the whole application each time you need an object, you will open up connections to the db (which eventually will stop working due to too many connections) you will have weird transaction issues and probably (depending on the size) memory issues. (Of course if that is what you want by all means proceed like this).
相反，您应该使用依赖注入.将所有需要的依赖项声明为字段并让 spring 进行自动连接，这在启动时只会发生一次.
Instead you should be using dependency injection. Declare all needed dependencies as fields and let spring do the auto wiring, which will happen just once at startup.
@Controller
@RequestMapping("/v2")
public class MyController {
private final UserDAO dao;
@Autowired
public MyController(UserDAO Dao) {
this.dao=dao;
}
@RequestMapping(value="users/{userId}",method=RequestMethod.GET)
public @ResponseBody Object getUserDetails(@PathVariable String userId){
return dao.getUser(userId);;
}
}
在你的 UserDAO 中做这样的事情.
In your UserDAO do something like this.
@Repository
public class UserDAO {
private final JdbcTemplate jdbcTemplate;
@Autowired
public UserDAO(@Qualifier("sampleDatabase") DataSource dataSource) {
this.jdbcTemplate=new JdbcTemplate(dataSource);
}
// others methods which do the actual queries using Spring JDBC
}
另一件事是在您的 web.xml 中，您都有一个 ContextLoaderListener 和 DispatcherServlet.现在这不一定是问题，但在您的情况下，两个类都加载相同的应用程序上下文，导致您的应用程序被加载两次，而一个实例什么都不做.
Another thing is in your web.xml you both have a ContextLoaderListener and DispatcherServlet. Now this doesn't have to be a problem but in your case both classes load the same application context resulting in your application being loaded twice with one instance doing nothing.
从您的 web.xml 中删除 ContextLoaderListener 和 context-param.
Remove the ContextLoaderListener and the context-param from your web.xml.
<?xml version="1.0" encoding="UTF-8"?>
<web-app id="WebApp_ID" version="2.4"
xmlns="http://java.sun.com/xml/ns/j2ee"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://java.sun.com/xml/ns/j2ee http://java.sun.com/xml/ns/j2ee/web-app_2_4.xsd">
<display-name>MyApp</display-name>
<welcome-file-list>
<welcome-file>index.html</welcome-file>
<welcome-file>index.jsp</welcome-file>
</welcome-file-list>
<servlet>
<servlet-name>mvc-dispatcher</servlet-name>
<servlet-class>org.springframework.web.servlet.DispatcherServlet</servlet-class>
<load-on-startup>1</load-on-startup>
</servlet>
<servlet-mapping>
<servlet-name>mvc-dispatcher</servlet-name>
<url-pattern>/</url-pattern>
</servlet-mapping>
</web-app>
现在在您的 mvc-dispatcher-servlet.xml 中添加以下 2 行.
Now in your mvc-dispatcher-servlet.xml add the following 2 lines.
<import resource="classpath:sampledb.xml" />
<import resource="classpath:eventsdb.xml" />
或者将两个文件的内容移动到mvc-dispatcher-servlet.xml.
Or move the contents of both files to the mvc-dispatcher-servlet.xml.
这篇关于Spring 4 JDBC - 如何加载数据库属性和优化(使用缓存或数据库连接池)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
