Spring Tutorial – Connecting database using spring framework
Overview:
The Spring JDBC template provides a better way than that by JDBC ODBC drivers for integrating Java calls or requests with popular database programs. While the objective of both Spring JDBC and JDBC and ODBC is the same, the approaches vary greatly. In case of the latter, the developer needs to worry about a lot of overheads such as opening and closing connections and query execution. But Sprint JDBC template allows the developer to focus on the core task of integration.
What are the Pain points of normal JDBC access?
JDBC provides access to database programs through four drivers. Each driver has its own disadvantages which are explained below.
JDBC-ODBC Bridge
- This is used when there is no Java driver available for a database. The driver converts JDBC calls into ODBC calls or requests which then get routed to the database. This result in performance overhead because the JDBC calls have to first go to the ODBC driver through the JDBC-ODBC Bridge.
- The ODBC driver needs to be installed on the client machine and since there is no Java driver available for the database, the client-side software might not be suitable for applets.
Native-API Driver
- This driver converts JDBC requests into native requests of the database API which the database understands. The driver uses the client-side libraries. To send JDBC calls to the database, the client library needs to be installed into the client machine. Due to various reasons, the driver cannot be used over the Internet.
- The driver might be redundant if the database declines access to its library.
Network-Protocol Driver
- Since this driver uses a middle tier or an application server to integrate JDBC calls to database programs, there is an effort required to code on the middle tier.
- Adding the middle tier may be time-consuming.
Note: To integrate Java requests with database programs, the Native-Protocol driver appears to be the best option because it converts JDBC calls directly into vendor-specific database calls.
Must Read – Build your application using AngularJS and Spring data JPA?
What are the Spring JDBC approaches?
There are a few problems with the JDBC drivers, as can be seen in the descriptions above. JDBC drivers depend on external entities such as JDBC-ODBC bridge, middleware and database client libraries. Problems can arise in the event of unavailability of these entities. When you work with JDBC drivers, you may also need to code a lot. Too much dependence on other entities make accessing database programs a time consuming process. How is Spring JDBC different?
To solve the problem of integrating Java requests with database programs, Spring JDBC takes the following approaches:
JDBC Template class
This is a support class that comprises many methods which perform different tasks. For example, public int update method can insert, update or delete records and the public void execute method can execute DDL query. The availability of readymade methods gets tasks done easily.
NamedParameter
This capability is used to insert data by named parameter. To use this, the user needs to remember the data for the column.
SimpleJdbcTemplate
This wraps up the JDBC Template class and allows the user to pass arguments to access the database.
To summarise, Spring JDBC simplifies the integration of Java calls with databases with readymade methods and parameters and taking care of all low-level details that make JDBC API hard to work with.
Must Read – Steps to learn Spring Batch Service
What is Spring JDBC template?
The Spring JDBC template is a powerful way to integrate Java calls with database programs. While there are other ways such as the JDBC and ODBC combination to connect Java calls with databases, Spring JDBC lets the developer focus on the integration and response part while taking care of other low-level activities such as process exception, transaction handling, opening and closing connections, executing statements, setting up loops to iterate through results and close statement and result set.
What are the advantages of Spring JDBC template?
The main advantage of Spring JDBC template is it makes connecting with database programs a simpler and hassle-free experience for the developer. With other programs such as the JDBC drivers, the developer needs to work on other overheads such as coding on middle tier, fulfilling dependency between Java calls and libraries and database APIs and so on. The Spring JDBC Template takes away a lot of such tasks. Following are some of the important tasks managed by Spring JDBC template.
- Opening the connection
- Preparing and executing statement
- Setting up loop for iterating through results
- Exception processing
- Handling transactions
- Closing connections
Must Read – Steps to schedule tasks using Spring framework
Environment setup:
Setting up the environment for Spring based application development involves just three major steps.
- Setting up Java Development Kit
Download JDK from the Oracle site and then, install and configure it. Then the PATH and JAVA_HOME environment variables must be set.
- Setting of Eclipse IDE
Eclipse can be downloaded from the official website. Once downloaded, unpack the binaries and then set the PATH as well.
- Setting up Spring libraries
The Spring libraries may be obtained from http://repo.spring.io/release/org/springframework/spring/. Again, it is important to set the CLASSPATH correctly.
Now your environment is ready to start developing Spring with Java applications.
Sample application:
In this section we will code some example programs to understand how Spring JDBC template works with databases
We will create a student object and insert the data in a database. We will also check the inserted data.
Listing 1: This is the student object
package com.techalpine.demo;
public class Student {
private String sname;
private String semail;
public String getSname() {
return sname;
}
public void setSname(String sname) {
this.sname = sname;
}
public String getSemail() {
return semail;
}
public void setSemail(String semail) {
this.semail = semail;
}
@Override
public String toString() {
return “Student [name is =” + sname + “, email is =” + semail + “]”;
}
}
Following is the DAO class to insert student data in a MySQL database. And also fetch data from the DB.
Listing 2: This is the student DAO object
package com.techalpine.demo;
import java.util.List;
import org.springframework.jdbc.core.JdbcTemplate;
public class StudentDao {
private JdbcTemplate jdbctemplate;
public JdbcTemplate getJdbcTemplate() {
return jdbctemplate;
}
public void setJdbcTemplate(JdbcTemplate jdbctemplate) {
this.jdbctemplate = jdbctemplate;
}
public void insert(Student student){
String sqlinsert =”INSERT INTO STUDENT (NAME, EMAIL) VALUES(?,?);”;
String sname = student.getSname();
String semail = student.getSemail();
getJdbcTemplate().update(sqlinsert,new Object[]{sname,semail});
}
public List<Student> selectAll(){
String selectAllSql = “SELECT * FROM STUDENT;”;
return getJdbcTemplate().query(selectAllSql, new StudentRowMapper());
}
}
Following is the service class to access student data.
Listing 3: This is the student service class
package com. techalpine.demo;
import java.util.List;
public class StudentService {
private StudentDao studentDao;
public StudentDao getStudentDao() {
return studentDao;
}
public void setStudentDao(StudentDao studentDao) {
this.studentDao = studentDao;
}
public void addStudent(Student student) {
getStudentDao().insert(student);
}
public List<Student> fetchAllStudents() {
return getStudentDao().selectAll();
}
}
Following is the row mapper class to match the result set data with the corresponding columns.
Listing 4: This is the Row mapper class
package com. techalpine.demo;
import java.sql.ResultSet;
import java.sql.SQLException;
import org.springframework.jdbc.core.RowMapper;
public class StudentRowMapper implements RowMapper<Student>{
@Override
public Student mapRow(ResultSet resultSet, int rowNumber) throws SQLException {
Student student = new Student();
student.setSname(resultSet.getString(1));
student.setSemail(resultSet.getString(2));
return student;
}
}
Now, following is the main class to insert the data and fetch it from the DB by using spring JDBC template and configuration.
Listing 5: This is the main class
package com.techalpine.demo;
import java.util.List;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
public class TestSpringDBDemo {
public static void main(String[] args) {
ApplicationContext context = new ClassPathXmlApplicationContext(“spring-config.xml”);
StudentService studentService = (StudentService) context.getBean(“studentnService”);
Student student = new Student();
student.setSname(“Nick”);
student.setSemail(“nick@gmail.com”);
studentService.addStudent(student);
System.out.println(“Student : ” + student.getSname() + ” added successfully”);
List<Student> students = studentService.fetchAllStudents();
System.out.println(“List of students = ” + students);
}
}
Once the program is executed it will show the following output.
Student Nick added successfully
List if students = Nick
Conclusion:
The Spring JDBC template is obviously more powerful than the JDBC and ODBC combination when it comes to connecting to database programs. Probably the most attractive feature is how simple it makes integration with database programs. It comes loaded with methods which are supposed to perform specific tasks. As a result, the developer saves time and effort.
