SatyaCodes

Spring Data -JdbcTemplate

Spring Data -JdbcTemplate class

  • JdbcTemplate class is given in org.springframework.jdbc.core.* package and this class will provide methods for executing the SQL commands on a database

  • JdbcTemplate class follows template design pattern, where a template class accepts input from the user and produces output to the user by hiding the interval details

Method Description
public void execute(String query) is used to execute DDL query.
public int update(String query) is used to insert, update and delete records.
List queryForInt(-query”) List queryForObject(-query”) List queryForXXX(-query”) For selecting the records from Database.
public T execute(String sql, PreparedStatementCallback action) executes the query by using PreparedStatement callback.
public T query(String sql, ResultSetExtractor rse) is used to fetch records using ResultSetExtractor.
public List query(String sql, RowMapper rse) is used to fetch records using RowMapper.


JdbcTemplate: Simple SQL Statements Example

1.select Database

CREATE TABLE `student` (
`sno` INT(11) NOT NULL AUTO_INCREMENT,
`name` VARCHAR(50) NULL DEFAULT NULL,
`address` VARCHAR(50) NULL DEFAULT NULL,
PRIMARY KEY (`sno`)
);

2.Student.java

This class contains 3 properties with constructors and setter and getters.

//File: Student.java
public class Student {
	private int sno;
	private String name;
	private String address;

	public Student() {
 super();
	}
	public Student(int sno, String name, String address) {
 this.sno = sno;
 this.name = name;
 this.address = address;
	}
	//Setters & getters
}

3.StudentDao

  • JdbcTemplate class executes SQL queries or updates, initiating iteration over ResultSet and catching JDBC exceptions and translating.

  • To call JdbcTemplate methods, we need initialize JdbcTemplate object in our DAO class.

  • For that we declared JdbcTemplate property in our StudentDao class & will inject JdbcTemplate object from SpringConfig.xml file

package jdbc;

import java.util.Iterator;
import java.util.List;

import org.springframework.jdbc.core.JdbcTemplate;

public class StudentDao {
	private JdbcTemplate jdbcTemplate;

	public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
 this.jdbcTemplate = jdbcTemplate;
	}

	public int saveStudent(Student s) {
 String query = "insert into student values('" + s.getSno() + "','" + s.getName() + "','" + s.getAddress()
  + "')";
 return jdbcTemplate.update(query);
	}

	public int updateStudent(Student s) {
 String query = "update student set name='" + s.getName() + "',address='" + s.getAddress() + "' where sno='"
  + s.getSno() + "' ";
 return jdbcTemplate.update(query);
	}

	public int deleteStudent(Student s) {
 String query = "delete from student where sno='" + s.getSno() + "' ";
 return jdbcTemplate.update(query);
	}

	public void selectStudents() {
 List l = jdbcTemplate.queryForList("select * from student");
 Iterator it = l.iterator();
 while (it.hasNext()) {
 	Object o = it.next();
 	System.out.println(o.toString());
 }
	}
}

4. SpringConfig.java

We have to configure 3 properties in SpringConfig.xml. they are

1. Create DataSource object

  • Spring-JDBC, the programmer no need to open and close the database connection and it will be taken care by the spring framework.

  • Spring framework uses DataSource interface to obtain the connection with database internally.

  • will use any one of the following 2 implementation classes of DataSource interface 
    org.springframework.jdbc.datasource.DriverManagerDataSource
org.apache.commons.dbcp.BasicDataSource
  • We have to provide connection details to DataSource object 
    <!-- 1. Creating DataSource object  -->
<bean id="ds"	class="org.springframework.jdbc.datasource.DriverManagerDataSource">
	<property name="driverClassName" value="com.mysql.jdbc.Driver" />
	<property name="url" value="jdbc:mysql://localhost:3306/smlcodes" />
	<property name="username" value="root" />
	<property name="password" value="root" />
</bean>

2. Create JdbcTemplate

JdbcTemplate class depends on DataSource object only, as it will open database connection internally with DataSource. So we must give this DataSource object to JdbcTemplate.

<!-- 2. Creating JdbcTemplate object  -->
<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
   <property name="dataSource" ref="ds"></property>
</bean>

3. Inject JdbcTemplate object to StudentDao class property.

File : SpringConfig.xml
<?xml version="1.0" encoding="UTF-8"?>
<beans>
<!-- 1. Creating DataSource object  -->
<bean id="ds"	class="org.springframework.jdbc.datasource.DriverManagerDataSource">
 <property name="driverClassName" value="com.mysql.jdbc.Driver" />
 <property name="url" value="jdbc:mysql://localhost:3306/smlcodes" />
 <property name="username" value="root" />
 <property name="password" value="root" />
	</bean>
	
	<!-- 2. Creating JdbcTemplate object  -->
	<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
 <property name="dataSource" ref="ds"></property>
	</bean>
	<!-- 3. Injecting JdbcTemplate object to StudentDao class property  -->
	<bean id="dao" class="jdbc.StudentDao">
 <property name="jdbcTemplate" ref="jdbcTemplate"></property>
	</bean>
</beans>

JdbcTestApplication.java

//File: JdbcTestApplication.java
public class JdbcTestApplication {
	public static void main(String[] args) {
 Resource res = new ClassPathResource("jdbc/SpringConfig.xml");
 BeanFactory factory = new XmlBeanFactory(res);

 System.out.println("1.INSERT \n ------------");
 StudentDao dao = (StudentDao) factory.getBean("dao");
 Student s = new Student(102, "Satya", "HYDERABAD");
 int r = dao.saveStudent(s);
 System.out.println(r + " Records are Effected");

 System.out.println(" \n 2.SELECT \n ------------");
 dao.selectStudents();

 System.out.println(" \n 3.UPDATE \n ------------");
 s.setName("RAVI");
 dao.updateStudent(s);
 dao.selectStudents();

 System.out.println(" \n 4.DELETE \n ------------");
 dao.deleteStudent(s);
 dao.selectStudents();
	}
}
------------------------------ output ----------------------
1.INSERT 
 ------------
1 Records are Effected
 
 2.SELECT 
 ------------
{sno=102, name=Satya, address=HYDERABAD}
 
 3.UPDATE 
 ------------
{sno=102, name=RAVI, address=HYDERABAD}
 
 4.DELETE 
 ------------


PreparedStatementCallback

We can execute parameterized query using Spring JdbcTemplate by the help of execute() method of JdbcTemplate class. To use parameterized query, we pass the instance of PreparedStatementCallback in the execute method.

public T execute (String sql,PreparedStatementCallback <T>);

It has only one method doInPreparedStatement(PreparedStatement ps)

JdbcTemplate-PreparedStatement Example

//File : StudentPreparedStmntDao.java
public class StudentPreparedStmntDao {
	private JdbcTemplate jdbcTemplate;

	public JdbcTemplate getJdbcTemplate() {
 return jdbcTemplate;
	}

	public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
 this.jdbcTemplate = jdbcTemplate;
	}

	public Boolean saveStudentByPreparedStatement(final Student s) {
 String query = "insert into student values(?,?,?)";
 return jdbcTemplate.execute(query, new PreparedStatementCallback<Boolean>() {
 	@Override
 	public Boolean doInPreparedStatement(PreparedStatement ps) throws SQLException, DataAccessException {

  ps.setInt(1, s.getSno());
  ps.setString(2, s.getName());
  ps.setString(3, s.getAddress());
  return ps.execute();
 	}
 });
	}
}

//File : PreparedStmtTestApplication.java
public class PreparedStmtTestApplication {
	public static void main(String[] args) {
 Resource res = new ClassPathResource("jdbc/SpringConfig.xml");
 BeanFactory factory = new XmlBeanFactory(res);
 
 StudentPreparedStmntDao dao = (StudentPreparedStmntDao) factory.getBean("dao");
 Student s = new Student(102, "Satya", "HYDERABAD");
 boolean r = dao.saveStudentByPreparedStatement(s);
 System.out.println(" Data Inserted : "+r); 
	}
}

<!-- File : SpringConfig.xml -->
<beans>
	<bean id="ds"
 class="org.springframework.jdbc.datasource.DriverManagerDataSource">
 <property name="driverClassName" value="com.mysql.jdbc.Driver" />
 <property name="url" value="jdbc:mysql://localhost:3306/smlcodes" />
 <property name="username" value="root" />
 <property name="password" value="root" />
	</bean>
	
	<!-- 1. Creating JdbcTemplate object  -->
	<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
 <property name="dataSource" ref="ds"></property>
	</bean>
	<!-- 2. Injecting JdbcTemplate object to StudentDao class property  -->
	<bean id="dao" class="jdbc.StudentPreparedStmntDao">
 <property name="jdbcTemplate" ref="jdbcTemplate"></property>
	</bean>
</beans>

3. ResultSetExtractor

We can easily fetch the records from the database using query() method of JdbcTemplate class where we need to pass the instance of ResultSetExtractor.

public T query(String sql,ResultSetExtractor<T> rse)

It defines only one method public T extractData(ResultSet rs) that accepts ResultSet instance as a parameter

ResultSetExtractor Fetching Records Example

//File : StudentPreparedStmntDao.java
public class StudentPreparedStmntDao {
	private JdbcTemplate jdbcTemplate;

	public JdbcTemplate getJdbcTemplate() {
 return jdbcTemplate;
	}

	public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
 this.jdbcTemplate = jdbcTemplate;
	}
	public List<Student> getAllstudents() {
 return jdbcTemplate.query("select * from student", new ResultSetExtractor<List<Student>>() {
 	@Override
 	public List<Student> extractData(ResultSet rs) throws SQLException, DataAccessException {

  List<Student> list = new ArrayList<Student>();
  while (rs.next()) {
  	Student e = new Student();
  	e.setSno(rs.getInt(1));
  	e.setName(rs.getString(2));
  	e.setAddress(rs.getString(3));
  	list.add(e);
  }
  return list;
 	}
 });
	}
}

//File : PreparedStmtTestApplication.java
public class PreparedStmtTestApplication {
	public static void main(String[] args) {
 Resource res = new ClassPathResource("jdbc/SpringConfig.xml");
 BeanFactory factory = new XmlBeanFactory(res);

 StudentPreparedStmntDao dao = (StudentPreparedStmntDao) factory.getBean("dao");
 List<Student> list = dao.getAllstudents();
 for (Student e : list)
 	System.out.println(e);
 }
}
Search