Tag: spring jdbctemplate example

Spring provides a functionality for database connectivity which is known as SpringJDBC template.

Using Spring JdbcTemplate, we can easily connect to database and execute sql queries.

Compared to JDBC, Spring JdbcTemplate scores in the below areas:

1)No need to manaully create db connection objects and close it on completion. Spring will take care of all.

2)Spring automatically handles the db exceptions at their side.

3)Transaction Management.

We can perform all the database operations with the help of JdbcTemplate class such as insertion, updation, deletion and retrieval of the data from the database.

Let us see an example for the same.

//DataBase Table
create table Book
(
id int,
bookName varchar(50),
isbnNo varchar(12)
)

//Book DTO Class
public class Book {  
private int id;  
private String bookName;  
private String isbnNo;  
}  

//DAO implementation  
public class BookDao {  

@Autowired
private JdbcTemplate jdbcTemplate;  \\line 3
  
public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {  
    this.jdbcTemplate = jdbcTemplate;  
}  
  
public int saveBook(Book b){  
    String query="insert into Book values(  
    '"+b.getId()+"','"+b.getBookName()+"','"+b.getIsbnNo()+"')";  
    return jdbcTemplate.update(query);  
}  
public int updateBook(Book b){  
    String query="update Book set   
    bookName='"+b.getBookName()+"',isbnNo='"+b.getIsbnNo()+"' where id='"+b.getId()+"' ";  
    return jdbcTemplate.update(query);  
}  
public int deleteBook(Book b){  
    String query="delete from Book where id='"+b.getId()+"' ";  
    return jdbcTemplate.update(query);  
}  
  
}  

If you see above we are inserting,updating and deleting into the Book table with the help of JdbcTemplate class.

But now the ques is how the JdbcTemplate object is getting built?

It is configured in the sprint context file. Another way to do is through annotations. Let us look into the example of configuring it through context file.

<?xml version="1.0" encoding="UTF-8"?>  
<beans  
    xmlns="http://www.springframework.org/schema/beans"  
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"  
    xmlns:p="http://www.springframework.org/schema/p"  
    xsi:schemaLocation="http://www.springframework.org/schema/beans   
http://www.springframework.org/schema/beans/spring-beans-3.0.xsd">  
  
<bean id="ds" class="org.springframework.jdbc.datasource.DriverManagerDataSource">  
    <property name="driverClassName" value="com.microsoft.sqlserver.jdbc.SQLServerDriver" />  
    <property name="url" value="jdbc:sqlserver://localhost;databaseName" />  
    <property name="username" value="sa" />  
    <property name="password" value="sa123" />  
</bean>  
  
<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">  
    <property name="dataSource" ref="ds"></property>  
</bean>  
  
<bean id="edao" class="com.javatpoint.EmployeeDao">  
    <property name="jdbcTemplate" ref="jdbcTemplate"></property>  
</bean>  
  
</beans>

When the application runs, the beans defined in the above context file is created in the container as objects.

In the DAO class, the JdbcTemplate reference at line 3 is autowired with the bean created for JdbcTemplate class in the container.