编程知识 cdmana.com

Knowledge sharing: how to operate JDBC through spring framework?

Spring Integrate JDBC The way

  • Add dependency
  • Writing configuration files db.properties
  • bean.xml Configuration modification
  • Configure data sources
  • Template class configuration
  • Test integration results

Case practice

Add dependency

Database driven jar package

mysql-connector-java-5.1.25-bin.jar

Database connection pool related jar package

c3p0-0.9.5.2.jar、mchange-commons-java-0.2.11.jar

Spring jdbc relevant jar

spring-jdbc-4.3.2.RELEASE.jar、spring-tx-4.3.2.RELEASE.jar

<!-- spring Frame coordinates depend on adding -->
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-context</artifactId>
<version>4.3.2.RELEASE</version>
</dependency>
<!-- aop -->
<dependency>
<groupId>org.aspectj</groupId>
<artifactId>aspectjweaver</artifactId>
<version>1.8.9</version>
</dependency>
<!-- mysql Drive pack -->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.39</version>
</dependency>
<!-- c3p0 Connection pool -->
<dependency>
<groupId>c3p0</groupId>
<artifactId>c3p0</artifactId>
<version>0.9.1.2</version>
</dependency>
<!-- spring jdbc -->
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-jdbc</artifactId>
<version>4.3.2.RELEASE</version>
</dependency>
<!-- springs Business -->
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-tx</artifactId>
<version>4.3.2.RELEASE</version>
</dependency>

The configuration file db.properties

jdbc.driver=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3306/spring_jdbc?useUnicode=true&characterEncod
ing=utf8
jdbc.user=root
jdbc.password=root

mysql8 Above version
jdbc.driver=com.mysql.cj.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3306/user?useSSL=false&serverTimezone=UTC&allowPublicKeyRetrieval=true
jdbc.user=root
jdbc.password=root

The following is an optional configuration

initialPoolSize=20
maxPoolSize=100
minPoolSize=10
maxIdleTime=600
acquireIncrement=5
maxStatements=5
idleConnectionTestPeriod=60

bean.xml Configuration modification

load properties File configuration

<!-- load properties The configuration file -->
<context:property-placeholder location="db.properties" />

<?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"
xmlns:aop="http://www.springframework.org/schema/aop"
xmlns:task="http://www.springframework.org/schema/task"
xsi:schemaLocation="http://www.springframework.org/schema/beans
http://www.springframework.or...
http://www.springframework.or...
http://www.springframework.or...
http://www.springframework.or...
http://www.springframework.or...
http://www.springframework.or...
http://www.springframework.or...d">
<!-- load properties The configuration file -->
<context:property-placeholder location="db.properties" />
<bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource">
<property name="driverClass" value="${jdbc.driver}"></property>
<property name="jdbcUrl" value="${jdbc.url}"></property>
<property name="user" value="${jdbc.user}"></property>
<property name="password" value="${jdbc.password}"></property>
</bean>
<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
<property name="dataSource" ref="dataSource"/>
</bean>
</beans>

Configure data sources

Because establishing a database connection is a very time-consuming and resource consuming behavior , So through the connection pool Make some connections to the database , In memory , When an application needs to establish a database connection, it should go directly to the connection Just apply for one from the pool , Put it back after use .

C3P0 And dbcp One out of two

DBCP(DataBase connection pool), Database connection pool . yes apache On the one java Connection pool project , It's also tomcat Connection pool components used . Use alone dbcp need 2 A package :commons-dbcp.jar,commons-pool.jar dbcp, There is no automatic recycling of idle connections .

C3P0 It's an open source JDBC Connection pool , It implements the data source , Support JDBC3 Normative and JDBC2 Standard extension of . At present, the open source projects using it are Hibernate,Spring etc. .c3p0 It has the function of automatically reclaiming idle connections

C3P0 Data source configuration

<!-- To configure c3p0 data source -->
<bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource">
<property name="driverClass" value="${driver}"></property>
<property name="jdbcUrl" value="${url}"></property>
<property name="user" value="${user}"></property>
<property name="password" value="${password}"></property>
</bean>

C3P0 Other extras ( The corresponding value is in db.properties The document specifies )

<!-- Specifies the maximum number of connections to keep in the connection pool . Default:15-->
<property name="maxPoolSize" value="${maxPoolSize}"/>
<!-- Specifies the minimum number of connections to keep in the connection pool -->
<property name="minPoolSize" value="${minPoolSize}"/>
<!-- Specifies the number of initial connections to the connection pool The value should be in minPoolSize And maxPoolSize And between .Default:3-->
<property name="initialPoolSize" value="${initialPoolSize}"/>
<!-- Maximum free time ,60 If not used within seconds, the connection is discarded . if 0 Then never abandon . Default:0-->
<property name="maxIdleTime" value="${maxIdleTime}"/>
<!-- When the connections in the connection pool are exhausted c3p0 Number of connections acquired at one time . Default:3-->
<property name="acquireIncrement" value="${acquireIncrement}"/>
<!-- JDBC Standards for , To control the loading in the data source PreparedStatements Number .
But due to pre cached statements Belongs to a single connection Instead of the entire connection pool, so setting this parameter requires
Considering many factors . If maxStatements And maxStatementsPerConnection Are all 0, Cache
Shut down .Default:0-->
<property name="maxStatements" value="${maxStatements}"/>
<!-- Every time 60 Seconds to check all free connections in the connection pool .Default:0 -->
<property name="idleConnectionTestPeriod" value="${idleConnectionTestPeriod}"/>

about dbcp The data source configuration is as follows :

<!-- To configure dbcp data source -->
<bean id="myDataSource" class="org.apache.commons.dbcp2.BasicDataSource">
<property name="driverClassName" value="${driver}" />
<property name="url" value="${url}"/>
<property name="username" value="${user}"/>
<property name="password" value="${password}"/>
<!-- Initial value when connection pool starts -->
<property name="initialSize" value="1"/>
<!-- Maximum free . After a rush hour , The connection pool can slowly release a part of the connection that is no longer available , Down to maxIdle until -->
<property name="maxIdle" value="2"/>
<!-- Minimum free . When the number of idle connections is less than the threshold , The connection pool will pre request some connections , In order to avoid the performance cost caused by applying again when the flood peak comes -->
<property name="minIdle" value="1"/>
</bean>

Template class configuration

Spring hold JDBC A template class is created by repeating operations in :org.springframework.jdbc.core.JdbcTemplate , Add to profile

<!-- jdbcTemplate To configure -->
<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
<property name="dataSource" ref="dataSource"></property>
</bean>

Test integration results

adopt junit test jdbcTemplate bean Whether or not to get

public class TestSpringJdbc {
private JdbcTemplate jdbcTemplate;
@Before
public void init(){
ApplicationContext ctx=new ClassPathXmlApplicationContext("beans.xml");
jdbcTemplate=(JdbcTemplate) ctx.getBean("jdbcTemplate");
}
@Test
public void test() {
String sql="select count(1) from account";
Integer total= jdbcTemplate.queryForObject(sql, Integer.class);
System.out.println(" Total routes :"+total);
}
}

Expand

JDBC Business

If you use JDBC To persist , At this time to use DataSourceTransactionManager To handle transaction boundaries . In order to use DataSourceTransactionManager, You need to use the following XML Assemble it into the context definition of the application :

<bean id="transactionManager"
class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
<property name="dataSource" ref="dataSource" />
</bean>

actually ,DataSourceTransactionManager By calling java.sql.Connection To manage things , The latter is through DataSource Acquired . Connected by calling commit() Method to commit a transaction , Again , If the transaction fails, it will call rollback() Method to roll back .

版权声明
本文为[Little Tinker who loves programming]所创,转载请带上原文链接,感谢
https://cdmana.com/2020/12/20201224153205212e.html

Scroll to Top