Description
Spring JDBC, there are multiple ways to create data base by using like JDBC, IBATIS, Hibernate, JPA, JCA.DAO plays the crucial role to communicate with Data base by querying the request. It returns the data in the form object that required by the service layer. The advantage of DAO are as follows.
Poor exception hierarchy like SQLException, DataTruncation, SQLwarning, BatchUpdateException. Spring exception hierarchy overcome this problem and advantages are as follows.
- Application is independent of the data access techniques and database dependently.
- It offers loose coupling with other layers of application.
- Allows to unit test the service layer using mock objects with out connecting to data base.
- More elaborate and detailed exception.
- Exception not specific to an API. can be used with any ORM and data access API.
- Create a connection.
- Create a statement.
- Start the transaction.
- Execute the statement.
- Iterate the result set and get the values.
- Commit the transaction and Rollback when transaction occurs.
- Close the statement.
- Close the coonection.
- Creates and releases resources.
- Avoids common problems such as closing the connection.
- It executes queries , updates or stored procedures.
- It catches SQLExceptions and translates them into unchecked exceptions.
Description
To proceed with the example developer has to create one table like as follows.
[java]CREATE TABLE Student(
ID INT NOT NULL AUTO_INCREMENT,
NAME VARCHAR(20) NOT NULL,
AGE INT NOT NULL,
PRIMARY KEY (ID)
);[/java]
While proceeding with coding in Eclipse, required jar files needs to be added and JDBC latest libraries like mysql-connector-java.jar, org.springframework.jdbc.jar, org.springframework.transaction.jar in the project. Include DAO interface studentDAO, student table should be created in test database.
StudentDAO.java
[java]package splessons;
import java.util.List;
import javax.sql.DataSource;
public interface StudentDAO {
/**
* It is the method to initialize
*/
public void setDataSource(DataSource ds);
/**
* method used to create
*/
public void create(String name, Integer age);
/**
* method used to list down
*/
public Student getStudent(Integer id);
/**
* method used to list down
* total files from the table.
*/
public List<Student> listStudents();
/**
* method used to delete
*/
public void delete(Integer id);
/**
*method used to update
* a file into the table.
*/
public void update(Integer id, Integer age);
}[/java]
Student.java
[java]package splessons;
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;
}
}[/java]
Here just created the data members such as age, name, id and also performed SET and GET operations to get the data.
StudentMapper.java
[java]package splessons;
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;
}
}[/java]
RowMapper interface allows to map a row of the relations with the instance of user-defined class. It iterates the ResultSet internally and adds it into the collection. So we don't need to write a lot of code to fetch the records as ResultSetExtractor. RowMapper saves a lot of code becuase it internally adds the data of ResultSet into the collection.
StudentJDBCTemplate.java
[java]package splessons;
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;
}
}[/java]
This is the implementation class file StudentJDBCTemplate.java for the defined DAO interface StudentDAO. Add Spring JDBC specific latest libraries mysql-connector-java.jar, org.springframework.jdbc.jar and org.springframework.transaction.jar in the project.
MainApp.java
[java]package splessons;
import java.util.List;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import com.tutorialspoint.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("sai", 22);
studentJDBCTemplate.create("sravan", 23);
studentJDBCTemplate.create("shiva", 25);
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, 26);
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());
}
}[/java]
The Application Context is spring's more best in class holder. Like BeanFactory it can stack bean definitions, wire beans together and administer beans upon solicitation. Also it includes more enterprise-specific usefulness, for example, the capacity to determine literary messages from a properties document and the capacity to distribute application events to interested event listeners. This container is characterized by the org.springframework.context.ApplicationContext interface.
Beans.xml
[java]<?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="splessons.StudentJDBCTemplate">
<property name="dataSource" ref="dataSource" />
</bean>
</beans>[/java]
Make sure that data base connector needs to imported to the code. Spring JdbcTemplate is a powerful mechanism to connect to the database and execute SQL queries. It internally uses JDBC api, but eliminates a lot of problems of JDBC API.
Output
[java]------Records Creation--------
Created Record Name = sai Age = 22
Created Record Name = sravan Age = 23
Created Record Name = shiva Age = 25
------Listing Multiple Records--------
ID : 1, Name : sai, Age : 22
ID : 2, Name : sravan, Age : 23
ID : 3, Name : shiva, Age : 25
----Updating Record with ID = 2 -----
Updated Record with ID = 2
----Listing Record with ID = 2 -----
ID : 2, Name : sravan, Age : 26[/java]
Key Points
- Spring JDBC Template class to handle with integration of jdbc.
- Spring JDBC Template class manages releases and creation of resources.
- Spring JDBC Template class resolves the common error like always forgetting termination of code.