编程知识 cdmana.com

How to operate JDBC through spring framework?

 How to use  Spring  Framework for JDBC operation ?

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.jarmchange-commons-java-0.2.11.jar

Spring jdbc relevant jar

spring-jdbc-4.3.2.RELEASE.jarspring-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&amp;serverTimezone=UTC&amp;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.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/aop
    http://www.springframework.org/schema/aop/spring-aop.xsd
    http://www.springframework.org/schema/task
    http://www.springframework.org/schema/task/spring-task.xsd">
    <!--  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 .

版权声明
本文为[osc_ cyo5y1ey]所创,转载请带上原文链接,感谢
https://cdmana.com/2020/12/20201225135751693K.html

Scroll to Top