Spring JDBC template

When using a normal JDBC database, you need to write code to handle exceptions, open and close database connections, and so on. But the Spring JDBC framework takes care of all the low-level details, starting with opening the connection, preparing and executing SQL statements, handling exceptions, handling transactions, and finally closing the connection. When retrieving data from a database, all you need to do is define the connection parameters and specify the SQL statement to execute.

The Spring framework provides a JDBC template pattern, called JdbcTemplate, which simplifies a lot of code, but JdbcTemplate is not commonly used in practice. More often, Hibernate framework and MyBatis framework are used for database programming.

1.1 JdbcTemplate configuration

Context constraints, AOP constraints, TX constraints need to be introduced in configuration files.
Register the DriverManagerDataSource class as the connection pool (spring’s default connection pool; you can modify the registered bean class if you use another connection pool); Set the driver class, database connection address, user name, and password in the properties (see JDBC for other connection Settings).
Register springJDBC’s core JdbcTemplate class and instantiate the bean in use to create a database connection.


      
<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:tx="http://www.springframework.org/schema/tx"
       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/tx http://www.springframework.org/schema/tx/spring-tx.xsd">

    <bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
        <property name="driverClassName" value="com.mysql.jdbc.Driver"/>
        <property name="url" value="jdbc:mysql://localhost:3306/selection_course"/>
        <property name="username" value="root"/>
        <property name="password" value="root"/>
    </bean>

    <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
        <property name="dataSource" ref="dataSource"/>
    </bean>
    
</beans>
Copy the code

1.2 JdbcTemplate Common methods

public int update(String sql, Object[] args)

The return value is int, representing the number of rows affected. SQL is the SQL statement that needs to be executed. Args is the parameter that needs to be passed to the SQL statement as an array.

String insertSql = "insert into user values(null,? ,?) ";
Object param1[] = {"chenheng1"."Male"};
jdbcTemplate.update(sql, param1);
Copy the code

To query a data table, rowMapper maps the result set to a user-defined class. In practical applications, BeanPropertyRowMapper is commonly used. The properties in the user-defined class must correspond to the fields in the data table.

1.3 JdbcTemplate instance

public List<T> query (String sql, RowMapper<T> rowMapper, Object args[])

Create the database and the corresponding Po class

Myuser.java needs the corresponding setter and getter, otherwise it will not be assigned correctly during select

package com.pojo;

public class MyUser {
	private Integer uid;
	private String uname;
	private String usex;

	public Integer getUid(a) {
		return uid;
	}

	public void setUid(Integer uid) {
		this.uid = uid;
	}

	public String getUname(a) {
		return uname;
	}

	public void setUname(String uname) {
		this.uname = uname;
	}

	public String getUsex(a) {
		return usex;
	}

	public void setUsex(String usex) {
		this.usex = usex;
	}
	
	public String toString(a) {
		return "myUser [uid=" + uid +", uname=" + uname + ", usex=" + usex + "]"; }}Copy the code

The dataSource is the datapool class. The attributes to be configured include the driver class, URL, user name, and user password. Inject the dataSource into the JdbcTemplate.


      
<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:tx="http://www.springframework.org/schema/tx"
       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/tx http://www.springframework.org/schema/tx/spring-tx.xsd">

    <bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
        <property name="driverClassName" value="com.mysql.jdbc.Driver"/>
        <property name="url" value="jdbc:mysql://localhost:3306/sql_springtest? characterEncoding=utf8&amp;useSSL=false" />
        <property name="username" value="root"/>
        <property name="password" value="20011017lh"/>
    </bean>

    <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
        <property name="dataSource" ref="dataSource"/>
    </bean>
    
</beans>
Copy the code

The test class

Testjdbc.java uses @test unit tests in the Test code. This Test does not support annotation injection by default

@RunWith(SpringJUnit4ClassRunner.class)//SpringJUnit support, thus introducing spring-test framework support!
@ContextConfiguration(locations="classpath:applicationContext.xml")/ / load
Copy the code

package com.dao;
import java.util.Arrays;
import java.util.List;
import org.junit.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.annotation.Configuration;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;
import com.pojo.MyUser;

import org.junit.runner.RunWith;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;

@RunWith(SpringJUnit4ClassRunner.class)//SpringJUnit support, thus introducing spring-test framework support!
@ContextConfiguration(locations="classpath:applicationContext.xml")/ / load the applicationContext. XML
@Repository("testJdbc")
public class TestJdbc {
	@Autowired
	private JdbcTemplate jdbcTemplate;
	@Test
	public void select(a) {
		String sql = "select * from user where uid = ? and uname = ?";
		RowMapper<MyUser> rowMapper = new BeanPropertyRowMapper<MyUser>(MyUser.class);
		Object[] ids = new Object[]{2."Wang Xiaohu"};
		List<MyUser> ans = jdbcTemplate.query(sql,rowMapper,ids);
		System.out.println(Arrays.toString(ans.toArray()));
	}
	
	@Test
	public void update(a) {
		String sql = "insert into user values(null, ? ,?) ";
		Object[] params = {"King of the Tigers"."Male"};
		intans = jdbcTemplate.update(sql,params); System.out.println(ans); }}Copy the code

Spring Transaction Management – programmatic

Transactions: A series of operations that must be performed consecutively, and there are many ways to manage things in Spring.

2.1 Transaction manager

The code explicitly calls beginTransaction(), COMMIT (), rollback() and other transaction-related methods. Programmatic transaction management is appropriate when there are only a few transaction operations.

PlatformTransactionManager

The central interface to Spring’s transaction infrastructure, commonly used implementation classes
- DataSourceTransactionManager: Uses JDBC to manage transactions
- HibernateTransactionManager: Use Hibernate to manage transactions
TransactionDefinition

Spring’s transaction definition, which defines transaction-related information. For example, isolation level, propagation behavior, whether read-only, timeout information, and so on

TransactionStatus

It is used to record the status information of transactions in the process of transaction management

Spring for transaction management, according to the management of the affairs of the TransactionDefinition PlatformTransactionManager, all kinds of status information from the process will be recorded in the TransactionStatus

Example:

In the XML configuration DataSourceTransactionManager bean, he used with JDBC template, JDBC database template for operation, DataSourceTransactionManager to supervise and control the transaction


      
<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:tx="http://www.springframework.org/schema/tx"
       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/tx http://www.springframework.org/schema/tx/spring-tx.xsd">
    
    <! -- Configure data source -->
    <bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
        <property name="driverClassName" value="com.mysql.jdbc.Driver"/>
        <property name="url" value="jdbc:mysql://localhost:3306/sql_springtest? characterEncoding=utf8&amp;useSSL=false" />
        <property name="username" value="root"/>
        <property name="password" value="20011017lh"/>
    </bean>
    
    <! -- Configure JDBC template -->
    <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
        <property name="dataSource" ref="dataSource"/>
    </bean>
    
    <! -- Configure transaction manager -->
    <bean id="txManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
        <property name="dataSource" ref="dataSource" />
    </bean>
</beans>
Copy the code

The test uses a transaction manager for transaction management

package com.dao;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.TransactionDefinition;
import org.springframework.transaction.TransactionStatus;
import org.springframework.transaction.support.DefaultTransactionDefinition;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;

@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration(locations="classpath:applicationContext.xml")
@Repository("codeTransaction")
public class CodeTransaction {
	@Autowired
	private JdbcTemplate jdbcTemplate;
	@Autowired
	private DataSourceTransactionManager txManager;
	
	@Test
	public void test(a)  {
		TransactionDefinition tf = new DefaultTransactionDefinition();  // Default transaction definitions, such as isolation level, propagation behavior, etc
		TransactionStatus ts = txManager.getTransaction(tf);
		String message = "Execution successful, no transaction rollback";
		try {
			String sql1 = " insert into user values(? ,? ,?) ";
			Object param[] = { 1."Chen Heng"."Male" };
			jdbcTemplate.update(sql1, param);  // Add a piece of data
			jdbcTemplate.update(sql1, param);  // Add the same data to duplicate the primary key
			// Commit the transaction
			txManager.commit(ts);
		} catch (Exception e) {
			txManager.rollback(ts); // Rollback by the transaction manager
			message = "Transaction Rollback";
			//e.printStackTrace();} System.out.println(message); }}Copy the code

2.2 Transaction manager template

Both JDBC templates and transaction management templates are designed to simplify code and encapsulate the original JDBC/ transaction management approach using the template method design pattern.

TransactionTemplate

The execute() method of TransactionTemplate takes an argument of type TransactionCallback interface, which defines a doInTransaction() method, The TransactionCallback interface is typically implemented as an anonymous inner class and the business logic code is written in its doInTransaction() method.

Here you can use the default transaction commit and rollback rules without explicitly calling any transaction processing apis in the business code. The doInTransaction() method has an argument of type TransactionStatus, whose setRollbackOnly() method can be called anywhere in the method to identify the transaction as rollback to perform the transaction rollback.

By default, if an unchecked exception is thrown during the execution of a callback method, or if the setRollbackOnly() method is explicitly called, the transaction is rolled back; If the transaction completes or throws an exception of type Checked, the transaction is committed.

The basic structure

public Object getObject(String str) {
        /* * Performs transaction management */ with the return value 
        transactionTemplate.execute(new TransactionCallback<Object>() {
            @Override
            public Object doInTransaction(TransactionStatus transactionStatus) {

                try{.../ /... Business code
                    return new Object();
                } catch (Exception e) {
                    / / rollback
                    transactionStatus.setRollbackOnly();
                    return null; }}}); }Copy the code

Example:

XML to add a transaction manager template


      
<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:tx="http://www.springframework.org/schema/tx"
       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/tx http://www.springframework.org/schema/tx/spring-tx.xsd">
    
    <! -- Configure data source -->
    <bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
        <property name="driverClassName" value="com.mysql.jdbc.Driver"/>
        <property name="url" value="jdbc:mysql://localhost:3306/sql_springtest? characterEncoding=utf8&amp;useSSL=false" />
        <property name="username" value="root"/>
        <property name="password" value="20011017lh"/>
    </bean>
    
    <! -- Configure JDBC template -->
    <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
        <property name="dataSource" ref="dataSource"/>
    </bean>
    
    <! -- Configure transaction manager -->
    <bean id="txManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
        <property name="dataSource" ref="dataSource" />
    </bean>
	
    <! Create transactionTemplate for txManager -->
    <bean id="transactionTemplate" class="org.springframework.transaction.support.TransactionTemplate">
        <property name="transactionManager" ref="txManager" />
    </bean>
</beans>
Copy the code

test

package com.dao;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.TransactionStatus;
import org.springframework.transaction.support.TransactionCallback;
import org.springframework.transaction.support.TransactionTemplate;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;
@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration(locations="classpath:applicationContext.xml")

@Repository("transactionTemplateDao")
public class TransactionTemplateDao {
	@Autowired
	private JdbcTemplate jdbcTemplate;
	@Autowired
	private TransactionTemplate transactionTemplate;
	String message = "";
	
	@Test
	public void test(a) {
		// Implement the TransactionCallback interface as an anonymous inner class, using the default transaction commit and rollback rules, without explicitly calling any transaction apis in the business code
		transactionTemplate.execute(new TransactionCallback<Object>() {
			@Override
			public Object doInTransaction(TransactionStatus ts) {
				String sql1 = " insert into user values(? ,? ,?) ";
				Object param[] = { 2."Chen Heng"."Male" };
				try {
					jdbcTemplate.update(sql1, param);
					jdbcTemplate.update(sql1, param);
					message = "Execution successful, no transaction rollback";
				} catch (Exception e) {
					message = "Duplicate primary key, transaction rollback";
					//e.printStackTrace();
					ts.setRollbackOnly();
				}
				returnmessage; }}); System.out.println(message); }}Copy the code

Spring Transaction Management – declarative

Spring’s declarative transaction management is implemented through AOP technology. Its essence is to intercept methods before and after, and then create or join a transaction before the target method starts. After the target method is executed, the transaction is committed or rolled back according to the execution status.

The biggest advantage of declarative transaction management is that there is no need to manage transactions programmatically, so there is no need to adulterate transaction processing code in the business logic code. Only the relevant transaction rule declaration can apply the transaction rules to the business logic. Declarative transactions are often used in development, not only because of their simplicity, but also because they keep pure business code from being corrupted, making it easier to maintain code later.

It is implemented using AOP, so it can be implemented in both XML and annotations

3.1 based on XML

Xml-based declarative transaction management is implemented by configuring transaction rule declarations in configuration files.

The Spring framework provides the TX namespace to configure transactions and the < TX: Advice > element to configure notifications for transactions.

The

configuration element specifies the ID, which is the unique identifier of the configuration file, and the transaction-manager attribute, which specifies the transaction manager.

child you can configure multiple < TX: Method > child elements to specify the details of the transaction to be executed.

When the < TX: Advice > element is configured for enhanced handling of transactions, you can write an AOP configuration to have Spring automatically generate proxies for the target object.

Full Project Address

The three layers of Dao, Service and Controller are created, and the specific implementation steps are as follows:

Configure the JDBC template to connect to the database in XML

<! -- Configure data source -->
<bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
    <property name="driverClassName" value="com.mysql.jdbc.Driver"/>
    <property name="url" value="jdbc:mysql://localhost:3306/sql_springtest? characterEncoding=utf8&amp;useSSL=false" />
    <property name="username" value="root"/>
    <property name="password" value="20011017lh"/>
</bean>

<! -- Configure JDBC template -->
<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
    <property name="dataSource" ref="dataSource"/>
</bean>
Copy the code

Create and add annotations to the Dao layer, which operates on the database through JDBC templates

package com.dao;
@Repository("testDao")
public class TestDaoImpl implements TestDao{
	@Autowired
	private JdbcTemplate jdbcTemplate;
	@Override
	public int save(String sql, Object[] param) {
		return jdbcTemplate.update(sql,param);
	}
	@Override
	public int delete(String sql, Object[] param) {
		returnjdbcTemplate.update(sql,param); }}Copy the code

Create and add annotations to the Service layer, which provides save and DELETE methods to the control layer through methods provided by the Dao layer

package com.service;
import com.dao.TestDao;
@Service("testService")
public class TestServiceImpl implements TestService{
	@Autowired
	private TestDao testDao;
	@Override
	public int save(String sql, Object[] param) {
		return testDao.save(sql, param);
	}
	@Override
	public int delete(String sql, Object[] param) {
		returntestDao.delete(sql, param); }}Copy the code

Set up the Controller layer and add annotations, and write the Test method to test a transaction consisting of three operations using the methods provided by the service layer

package com.controller;
import com.service.TestService;
@Controller("statementController")
public class StatementController {
	@Autowired
	private TestService testService;
	public String test(a) {
		int i=0;
		String message = "";
		String deleteSql ="delete from user where id = 2";
		String saveSql = "insert into user values(? ,? ,?) ";
		Object param[] = {1222."chenheng1"."Male"};
		try{
			i=testService.delete(deleteSql, null);
			i=testService.save(saveSql, param);
			i=testService.save(saveSql, param);
		}catch(Exception e){
			message = Duplicate primary key, transaction rollback!+e.getClass()+" ,i = "+i;
			//e.printStackTrace();
			TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
		}
		returnmessage; }}Copy the code

Add an annotation scan to the XML

<context:component-scan base-package="com"/>
Copy the code

Add transaction management to THE XML, create aspect classes for the transaction manager, and connect the aspect classes to the pointcuts via AOP

<! -- Configure transaction manager -->
<bean id="txManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
    <property name="dataSource" ref="dataSource" />
</bean>

<! Write a notification declaration transaction, that is, the aspect class -->
<tx:advice id="myAdvice" transaction-manager="txManager">
    <tx:attributes>
        <! -- * denotes any method -->
        <tx:method name="*" />
    </tx:attributes>
</tx:advice>


<! Write AOP to let Spring automatically generate proxies for target objects, using AspectJ expressions.
<aop:config>
    <! Define pointcuts for all methods of all classes in the control layer.
    <aop:pointcut id="txPointCut" expression="execution(* com.controller.*.*())"/>
    <! -- Aspect: Associate pointcuts with advice -->
    <aop:advisor advice-ref="myAdvice" pointcut-ref="txPointCut"/>
</aop:config>
Copy the code

3.2 Annotation-based

The @Transactional annotation works on interfaces, interface methods, classes, and class methods, but the Spring team recommends not using this annotation on interfaces or interface methods because it only works when using interface-based proxies.

When used on a class, all public methods of that class will have transaction attributes of that type, and if used at the method level this annotation overrides the class-level definition.

The example also establishes Dao, Service, and Controller, with two differences

Enable scanning annotations in the XML configuration file, configure the JDBC template, configure the transaction manager, and enable annotation transactions

<! -- Specify the packages (including subpackages) to be scanned for annotations to take effect -->
<context:component-scan base-package="com"/>

<! -- Configure data source -->
<bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
    <property name="driverClassName" value="com.mysql.jdbc.Driver"/>
    <property name="url" value="jdbc:mysql://localhost:3306/sql_springtest? characterEncoding=utf8&amp;useSSL=false" />
    <property name="username" value="root"/>
    <property name="password" value="20011017lh"/>
</bean>

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

<! -- Configure transaction manager -->
<bean id="txManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
    <property name="dataSource" ref="dataSource" />
</bean>

<! Register annotation driver for transaction manager -->
<tx:annotation-driven transaction-manager="txManager" />
Copy the code

In Spring MVC, transaction management is usually done through the Service layer, so you need to add the @Transactional annotation to the Service layer.

@Service("testService")
@Transactional
Annotating @Transactional, you specify that this class needs to be transaction-managed by Spring
// Note that @Transactional can only be added for methods within the scope of the public attribute
public class TestServiceImpl implements TestService{
    @Autowired
    private TestDao testDao;
    @Override
    public int save(String sql, Object[] param) {
        return testDao.save(sql, param);
    }
    @Override
    public int delete(String sql, Object[] param) {
        returntestDao.delete(sql, param); }}Copy the code

mo4tech.com (Moment For Technology) is a global community with thousands techies from across the global hang out!Passionate technologists, be it gadget freaks, tech enthusiasts, coders, technopreneurs, or CIOs, you would find them all here.

Java EE SSM framework integration development (five) Spring JDBC template and transaction management

Spring JDBC template

1.1 JdbcTemplate configuration

1.2 JdbcTemplate Common methods

1.3 JdbcTemplate instance

Spring Transaction Management – programmatic

2.1 Transaction manager

2.2 Transaction manager template

Spring Transaction Management – declarative

3.1 based on XML

3.2 Annotation-based

Java EE SSM framework integration development (five) Spring JDBC template and transaction management

Spring JDBC template

1.1 JdbcTemplate configuration

1.2 JdbcTemplate Common methods

1.3 JdbcTemplate instance

Spring Transaction Management – programmatic

2.1 Transaction manager

2.2 Transaction manager template

Spring Transaction Management – declarative

3.1 based on XML

3.2 Annotation-based

Related Posts

Markdown + 7 ox Clouds make writing easier

How do I change the default terminal in Ubuntu

GitHub’s top 100 Android and iOS projects