Loading
 
What's Next: View All Tutorial →

Spring JDBC Transaction Management using @Transactional

A simple program for @Transactional implementation using spring jdbc Maven Dependency <project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XML...

Published Date: 04/09/2018  Last Modified Date: 04/09/2018    New Post



A simple program for @Transactional implementation using spring jdbc

Maven Dependency

<project xmlns="http://maven.apache.org/POM/4.0.0"
	xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
	<modelVersion>4.0.0</modelVersion>
	<groupId>com.demo</groupId>
	<artifactId>spring-jdbc</artifactId>
	<version>1.0</version>
	<name>spring-jdbc</name>
	<properties>
		<maven.compiler.source>1.8</maven.compiler.source>
		<maven.compiler.target>1.8</maven.compiler.target>
		<spring.version>5.0.8.RELEASE</spring.version>
	</properties>
	<dependencies>
		<dependency>
			<groupId>org.springframework</groupId>
			<artifactId>spring-context</artifactId>
			<version>${spring.version}</version>
		</dependency>
		<dependency>
			<groupId>org.apache.logging.log4j</groupId>
			<artifactId>log4j-core</artifactId>
			<version>2.8.2</version>
		</dependency>
		<dependency>
			<groupId>org.springframework</groupId>
			<artifactId>spring-jdbc</artifactId>
			<version>${spring.version}</version>
		</dependency>

		<dependency>
			<groupId>org.springframework</groupId>
			<artifactId>spring-tx</artifactId>
			<version>${spring.version}</version>
		</dependency>
		<dependency>
			<groupId>mysql</groupId>
			<artifactId>mysql-connector-java</artifactId>
			<version>5.1.46</version>
		</dependency>
	</dependencies>
</project>


log4j2.xml

 
<?xml version="1.0" encoding="UTF-8"?>
<Configuration status="WARN">
	<Appenders>
		<Console name="Console" target="SYSTEM_OUT">
			<PatternLayout
				pattern="%d{HH:mm:ss.SSS} [%t] %-5level %logger{36} - %msg%n" />
		</Console>
	</Appenders>
	<Loggers>
		<Logger name="org.springframework" level="DEBUG" />
		<Root level="error">
			<AppenderRef ref="Console" />
		</Root>
	</Loggers>
</Configuration>
 

DaoConfig.java

package com.demo.spring;

import javax.sql.DataSource;

import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.ComponentScan;
import org.springframework.context.annotation.Configuration;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import org.springframework.jdbc.datasource.DriverManagerDataSource;
import org.springframework.transaction.annotation.EnableTransactionManagement;

@Configuration
@ComponentScan(basePackages = "com.demo.spring")
@EnableTransactionManagement
public class DaoConfig {

	@Bean
	public DriverManagerDataSource ds() {
		DriverManagerDataSource ds = new DriverManagerDataSource();
		ds.setDriverClassName("com.mysql.jdbc.Driver");
		ds.setUrl("jdbc:mysql://localhost:3306/springdb?useSSL=false");
		ds.setUsername("root");
		ds.setPassword("admin");
		return ds;
	}

	@Bean
	public JdbcTemplate jt(DataSource ds) {
		JdbcTemplate jt = new JdbcTemplate();
		jt.setDataSource(ds);
		return jt;
	}

	@Bean
	public DataSourceTransactionManager transactionManager(DataSource ds) {
		DataSourceTransactionManager txn = new DataSourceTransactionManager(ds);
		return txn;
	}
}
 

Entity Class

package com.demo.spring.entity;

public class Emp {

	private int empId;
	private String name;
	private String city;
	private double salary;

	public Emp() {

	}

	public Emp(int empId, String name, String city, double salary) {

		this.empId = empId;
		this.name = name;
		this.city = city;
		this.salary = salary;
	}

	public int getEmpId() {
		return empId;
	}

	public void setEmpId(int empId) {
		this.empId = empId;
	}

	public String getName() {
		return name;
	}

	public void setName(String name) {
		this.name = name;
	}

	public String getCity() {
		return city;
	}

	public void setCity(String city) {
		this.city = city;
	}

	public double getSalary() {
		return salary;
	}

	public void setSalary(double salary) {
		this.salary = salary;
	}

	@Override
	public String toString() {
		return "Emp [empId=" + empId + ", name=" + name + ", city=" + city + ", salary=" + salary + "]";
	}

}
 

Service Class - EmpService.java

package com.demo.spring.entity;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

@Repository
public class EmpService {

	@Autowired
	JdbcTemplate jdbcTemplate;

	@Transactional
	public int saveEmployee(final List<Emp> empList) {
		int count = 0;

		for (Emp emp : empList)
			count = jdbcTemplate.update(new PreparedStatementCreator() {

				@Override
				public PreparedStatement createPreparedStatement(Connection con) throws SQLException {

					String query = "insert into emp (empno,name,address,salary) values (?,?,?,?)";
					PreparedStatement pst = con.prepareStatement(query);
					pst.setInt(1, emp.getEmpId());
					pst.setString(2, emp.getName());
					pst.setString(3, emp.getCity());
					pst.setDouble(4, emp.getSalary());
					return pst;
				}
			});

		return count;
	}
}

App.java - Main Class

package com.demo.spring;

import java.util.ArrayList;
import java.util.List;

import org.springframework.context.ApplicationContext;
import org.springframework.context.annotation.AnnotationConfigApplicationContext;

import com.demo.spring.entity.Emp;
import com.demo.spring.entity.EmpService;

public class App {

	public static void main(String args[]) {
		ApplicationContext ctx = new AnnotationConfigApplicationContext(DaoConfig.class);

		EmpService empService = ctx.getBean(EmpService.class);

		List<Emp> empList = new ArrayList<Emp>();
		empList.add(new Emp(101, "John", "NewYork", 50000));
		empList.add(new Emp(102, "Peter", "Indiana", 60000));

		empService.saveEmployee(empList);

	}
}




Output:

 
15:53:54.296 [main] DEBUG org.springframework.beans.factory.support.DefaultListableBeanFactory - Finished creating instance of bean 'Sybase'
15:53:54.296 [main] DEBUG org.springframework.jdbc.support.SQLErrorCodesFactory - SQLErrorCodes loaded: [DB2, Derby, H2, HDB, HSQL, Informix, MS-SQL, MySQL, Oracle, PostgreSQL, Sybase]
15:53:54.296 [main] DEBUG org.springframework.jdbc.support.SQLErrorCodesFactory - Looking up default SQLErrorCodes for DataSource [org.springframework.jdbc.datasource.DriverManagerDataSource@77659b30]
15:53:54.298 [main] DEBUG org.springframework.jdbc.support.SQLErrorCodesFactory - SQL error codes for 'MySQL' found
15:53:54.298 [main] DEBUG org.springframework.jdbc.support.SQLErrorCodesFactory - Caching SQL error codes for DataSource [org.springframework.jdbc.datasource.DriverManagerDataSource@77659b30]: database product name is 'MySQL'
15:53:54.298 [main] DEBUG org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator - Translating SQLException with SQL state '23000', error code '1062', message [Duplicate entry '101' for key 'PRIMARY'] for task [PreparedStatementCallback]
15:53:54.299 [main] DEBUG org.springframework.jdbc.datasource.DataSourceTransactionManager - Initiating transaction rollback
15:53:54.299 [main] DEBUG org.springframework.jdbc.datasource.DataSourceTransactionManager - Rolling back JDBC transaction on Connection [com.mysql.jdbc.JDBC4Connection@4808bc9b]
15:53:54.300 [main] DEBUG org.springframework.jdbc.datasource.DataSourceTransactionManager - Releasing JDBC Connection [com.mysql.jdbc.JDBC4Connection@4808bc9b] after transaction
15:53:54.300 [main] DEBUG org.springframework.jdbc.datasource.DataSourceUtils - Returning JDBC Connection to DataSource
Exception in thread "main" org.springframework.dao.DuplicateKeyException: PreparedStatementCallback; ]; Duplicate entry '101' for key 'PRIMARY'; nested exception is com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Duplicate entry '101' for key 'PRIMARY'
	at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(SQLErrorCodeSQLExceptionTranslator.java:242)
Awaiting for Administrator approval





Tags: Spring Core

← Back to list


Related Post




×