Spring Transaction Management


While working with the database utilizing plain old JDBC, it ends up bulky to compose pointless code to deal with special cases, opening and shutting database associations, and so forth. Nonetheless, Spring JDBC Framework deals with all the low-level subtleties beginning from opening the association, get ready and execute the SQL articulation, process exemptions, handle exchanges lastly close the connection.

So what you need to do is simply characterize the association parameters and indicate the SQL proclamation to be executed and do the required work for every cycle while getting information from the database.

Spring JDBC gives a few methodologies and correspondingly extraordinary classes to interface with the database. I'm going to take exemplary and the most mainstream approach which makes utilization of JdbcTemplate class of the structure. This is the focal structure class that deals with all the database correspondence and exemption handling.

JdbcTemplate Class

The JDBC Template class executes SQL questions, refreshes articulations, stores system calls, performs emphasis over ResultSets, and concentrates returned parameter esteems. It likewise gets JDBC special cases and makes an interpretation of them to the conventional, increasingly instructive, exemption chain of command characterized in the org.springframework.dao package.

Instances of the JdbcTemplate class are threadsafe once arranged. So you can arrange a solitary example of a JdbcTemplate and afterward securely infuse this mutual reference into numerous DAOs.

A regular practice when utilizing the JDBC Template class is to arrange a DataSource in your Spring setup document, and after that reliance infuse that mutual DataSource bean into your DAO classes, and the JdbcTemplate is made in the setter for the DataSource.

Configuring Data Source

Let us make a database table Student in our database TEST. We expect you are working with MySQL database, in the event that you work with some other database, at that point you can change your DDL and SQL inquiries accordingly.

CREATE TABLE Student( 

ID INT NOT NULL AUTO_INCREMENT, 

NAME VARCHAR(20) NOT NULL, 

AGE INT NOT NULL, 

Essential KEY (ID) 

);

Now we have to supply a DataSource to the JDBC Template so it can design itself to get database get to. You can design the DataSource in the XML document with a bit of code as appeared in the accompanying code piece −

<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/TEST"/> 

<property name = "username" value = "root"/> 

<property name = "password" value = "password"/> 

</bean>

Data Access Object (DAO)

DAO represents Data Access Object, which is ordinarily utilized for database communication. DAOs exist to give a way to peruse and compose information to the database and they should uncover this usefulness through an interface by which whatever remains of the application will get to them.

The DAO support in Spring makes it simple to work with information get to advancements like JDBC, Hibernate, JPA, or JDO in a predictable way.

Executing SQL statements

Let us perceive how we can perform CRUD (Create, Read, Update and Delete) activity on database tables utilizing SQL and JDBC Template object.

Querying for an integer

String SQL = "select count(*) from Student"; 

int rowCount = jdbcTemplateObject.queryForInt( SQL );

Querying for a long

String SQL = "select count(*) from Student"; 

long rowCount = jdbcTemplateObject.queryForLong( SQL );

A straightforward inquiry utilizing a quandary variable

String SQL = "select age from Student where id = ?"; 

int age = jdbcTemplateObject.queryForInt(SQL, new Object[]{10});

Querying for a String

String SQL = "select name from Student where id = ?"; 

String name = jdbcTemplateObject.queryForObject(SQL, new Object[]{10}, String.class);

Querying and restoring an object

String SQL = "select * from Student where id = ?"; 

Student understudy = jdbcTemplateObject.queryForObject( 

SQL, new Object[]{10}, new StudentMapper()); 

public class StudentMapper implements RowMapper<Student> { 

public Student mapRow(ResultSet rs, int rowNum) throws SQLException { 

Student understudy = new Student(); 

student.setID(rs.getInt("id")); 

student.setName(rs.getString("name")); 

student.setAge(rs.getInt("age")); 

return student;
} 

}

Querying and restoring different objects

String SQL = "select * from Student"; 

List<Student> understudies = jdbcTemplateObject.query( 

SQL, new StudentMapper()); 

public class StudentMapper implements RowMapper<Student> { 

public Student mapRow(ResultSet rs, int rowNum) throws SQLException { 

Student understudy = new Student(); 

student.setID(rs.getInt("id")); 

student.setName(rs.getString("name")); 

student.setAge(rs.getInt("age")); 

return student; 

} 

}

Inserting a column into the table

String SQL = "insert into Student (name, age) values (?, ?)"; 

jdbcTemplateObject.update( SQL, new Object[]{"Zara", 11} );

Updating a column into the table

String SQL = "update Student set name = ? where id = ?"; 

jdbcTemplateObject.update( SQL, new Object[]{"Zara", 10} );

Deleting a column from the table

String SQL = "delete Student where id = ?"; 

jdbcTemplateObject.update( SQL, new Object[]{20} );

Executing DDL Statements

You can utilize the execute(..) technique from jdbcTemplate to execute any SQL proclamations or DDL explanations. Following is a guide to utilize CREATE proclamation to make a table −

String SQL = "CREATE TABLE Student( " + 

"ID INT NOT NULL AUTO_INCREMENT, " + 

"NAME VARCHAR(20) NOT NULL, " + 

"AGE INT NOT NULL, " + 

"PRIMARY KEY (ID));" 

jdbcTemplateObject.execute( SQL );





© Javacodegeeks 2018 -2019
All Right Reserved and you agree to have read and accepted our term and condition.

Python 3