When using a normal JDBC database, it can be troublesome to write unnecessary 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.
So when fetching data from a database, all you do is define the connection parameters, specify the SQL statements to execute, and do what you need to do with each iteration.
Spring JDBC provides several methods and corresponding different classes and interfaces in the database. I’ll show you the classic and most popular way to use the JdbcTemplate class framework. This is the central framework class that manages all database communication and exception handling.
JdbcTemplate class
The JdbcTemplate class performs SQL queries, update statements, and stored procedure calls, performs iterating result sets, and extracts return parameter values. It also catches JDBC exceptions and transforms them into generic classes, more information, and exception hierarchies defined in the org.springFramework. dao package.
Instances of the JdbcTemplate class are thread-safe configured. So you can configure a single instance of the JdbcTemplate, and then safely inject the shared reference into multiple DAOs.
A common practice when using the JdbcTemplate class is to configure the data source in your Spring configuration file, then inject the shared data source bean dependency into the DAO class and create the JdbcTemplate in the data source set function.
To understand the concepts associated with the Spring JDBC framework with JDBC template classes, let’s write a simple example that implements all CRUD operations for the Student table below.
CREATE TABLE Student(
ID INT NOT NULL AUTO_INCREMENT,
NAME VARCHAR(20) NOT NULL,
AGE INT NOT NULL,
PRIMARY KEY (ID)
);
Copy the codeBefore we continue, let’s use the Eclipse IDE properly and create a Spring application by following these steps:
| steps | |
|---|---|
| 1 | Create a file namedSpringExampleProject, and in the created projectsrcCreate package under foldercom.cnblogs. |
| 2 | useAdd External JARsOption to add the required Spring libraries |
| 3 | Add the latest library specified by Spring JDBC to the projectmysql-connector-java.jar.org.springframework.jdbc.jar 和 org.springframework.transaction.jar. If these libraries don’t exist, you can download them. |
| 4 | Creating the DAO InterfaceStudentDAOList all necessary methods. Although this step is not required and you can write it directlyStudentJDBCTemplateClass, but as a good practice, it’s best to do this step. |
| 5 | incom.cnblogsCreate other required Java classes under the packageStudent.StudentMapper.StudentJDBCTemplate 和 MainApp 。 |
| 6 | Make sure you have created it in the TEST databaseStudentTable. Make sure your MySQL server is running properly and that you can read/write to the database using the given username and password. |
| 7 | insrcCreate a Beans configuration file under this folderBeans.xml. |
| 8 | The last step is to create the contents of all the Java files and Bean configuration files and run the application as shown below. |
StudentDAO. Java: StudentDAO. Java: StudentDAO.
package com.cnblogs;
import java.util.List;
import javax.sql.DataSource;
public interface StudentDAO {
/**
* This is the method to be used to initialize
* database resources ie. connection.
*/
public void setDataSource(DataSource ds);
/**
* This is the method to be used to create
* a record in the Student table.
*/
public void create(String name, Integer age);
/**
* This is the method to be used to list down
* a record from the Student table corresponding
* to a passed student id.
*/
public Student getStudent(Integer id);
/**
* This is the method to be used to list down
* all the records from the Student table.
*/
public List<Student> listStudents();
/**
* This is the method to be used to delete
* a record from the Student table corresponding
* to a passed student id.
*/
public void delete(Integer id);
/**
* This is the method to be used to update
* a record into the Student table.
*/
public void update(Integer id, Integer age);
}
Copy the codeHere is the contents of the student.java file:
package com.cnblogs; public class Student { private Integer age; private String name; private Integer id; public void setAge(Integer age) { this.age = age; } public Integer getAge() { return age; } public void setName(String name) { this.name = name; } public String getName() { return name; } public void setId(Integer id) { this.id = id; } public Integer getId() { return id; }}Copy the codeHere’s what the studentmapper. Java file says:
package com.cnblogs; import java.sql.ResultSet; import java.sql.SQLException; import org.springframework.jdbc.core.RowMapper; public class StudentMapper implements RowMapper<Student> { public Student mapRow(ResultSet rs, int rowNum) throws SQLException { Student student = new Student(); student.setId(rs.getInt("id")); student.setName(rs.getString("name")); student.setAge(rs.getInt("age")); return student; }}Copy the codeStudentjdbctemplate.java: studentJdbctemplate.java: studentJdbctemplate.java: studentJdbctemplate.java
package com.cnblogs; import java.util.List; import javax.sql.DataSource; import org.springframework.jdbc.core.JdbcTemplate; public class StudentJDBCTemplate implements StudentDAO { private DataSource dataSource; private JdbcTemplate jdbcTemplateObject; public void setDataSource(DataSource dataSource) { this.dataSource = dataSource; this.jdbcTemplateObject = new JdbcTemplate(dataSource); } public void create(String name, Integer age) { String SQL = "insert into Student (name, age) values (? ,?) "; jdbcTemplateObject.update( SQL, name, age); System.out.println("Created Record Name = " + name + " Age = " + age); return; } public Student getStudent(Integer id) { String SQL = "select * from Student where id = ?" ; Student student = jdbcTemplateObject.queryForObject(SQL, new Object[]{id}, new StudentMapper()); return student; } public List<Student> listStudents() { String SQL = "select * from Student"; List <Student> students = jdbcTemplateObject.query(SQL, new StudentMapper()); return students; } public void delete(Integer id){ String SQL = "delete from Student where id = ?" ; jdbcTemplateObject.update(SQL, id); System.out.println("Deleted Record with ID = " + id ); return; } public void update(Integer id, Integer age){ String SQL = "update Student set age = ? where id = ?" ; jdbcTemplateObject.update(SQL, age, id); System.out.println("Updated Record with ID = " + id ); return; }}Copy the codeHere is the contents of the mainapp.java file:
package com.cnblogs; import java.util.List; import org.springframework.context.ApplicationContext; import org.springframework.context.support.ClassPathXmlApplicationContext; import com.cnblogs.StudentJDBCTemplate; public class MainApp { public static void main(String[] args) { ApplicationContext context = new ClassPathXmlApplicationContext("Beans.xml"); StudentJDBCTemplate studentJDBCTemplate = (StudentJDBCTemplate)context.getBean("studentJDBCTemplate"); System.out.println("------Records Creation--------" ); studentJDBCTemplate.create("Zara", 11); studentJDBCTemplate.create("Nuha", 2); studentJDBCTemplate.create("Ayan", 15); System.out.println("------Listing Multiple Records--------" ); List<Student> students = studentJDBCTemplate.listStudents(); for (Student record : students) { System.out.print("ID : " + record.getId() ); System.out.print(", Name : " + record.getName() ); System.out.println(", Age : " + record.getAge()); } System.out.println("----Updating Record with ID = 2 -----" ); studentJDBCTemplate.update(2, 20); System.out.println("----Listing Record with ID = 2 -----" ); Student student = studentJDBCTemplate.getStudent(2); System.out.print("ID : " + student.getId() ); System.out.print(", Name : " + student.getName() ); System.out.println(", Age : " + student.getAge()); }}Copy the codeHere is the content of the configuration file beans.xml:
<? The XML version = "1.0" encoding = "utf-8"? > <beans xmlns="http://www.springframework.org/schema/beans" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-3.0.xsd "> <! -- Initialization for 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/TEST"/> <property name="username" value="root"/> <property name="password" value="password"/> </bean> <! -- Definition for studentJDBCTemplate bean --> <bean id="studentJDBCTemplate" class="com.cnblogs.StudentJDBCTemplate"> <property name="dataSource" ref="dataSource" /> </bean> </beans>Copy the codeWhen you are finished creating the source and bean configuration files, run the application. If everything goes well with your application, it will output a message like this:
------Records Creation--------
Created Record Name = Zara Age = 11
Created Record Name = Nuha Age = 2
Created Record Name = Ayan Age = 15
------Listing Multiple Records--------
ID : 1, Name : Zara, Age : 11
ID : 2, Name : Nuha, Age : 2
ID : 3, Name : Ayan, Age : 15
----Updating Record with ID = 2 -----
Updated Record with ID = 2
----Listing Record with ID = 2 -----
ID : 2, Name : Nuha, Age : 20
Copy the codeYou can try to remove the operations that I didn’t use in my example, but now that you have a working application based on the Spring JDBC framework, you can extend the framework to add complex functionality based on your project needs. There are other ways to access database of NamedParameterJdbcTemplate and SimpleJdbcTemplate class you use, so if you are interested in learning the class, so you can view the Spring framework reference manual.