Spring + JdbcTemplate + JdbcDaoSupport examples
In Spring JDBC development, you can use JdbcTemplate
and JdbcDaoSupport
classes to simplify the overall database operation processes.
In this tutorial, we will reuse the last Spring + JDBC example, to see the different between a before (No JdbcTemplate support) and after (With JdbcTemplate support) example.
1. Example Without JdbcTemplate
Witout JdbcTemplate, you have to create many redundant codes (create connection , close connection , handle exception) in all the DAO database operation methods – insert, update and delete. It just not efficient, ugly, error prone and tedious.
private DataSource dataSource;
public void setDataSource(DataSource dataSource) {
this.dataSource = dataSource;
}
public void insert(Customer customer){
String sql = "INSERT INTO CUSTOMER " +
"(CUST_ID, NAME, AGE) VALUES (?, ?, ?)";
Connection conn = null;
try {
conn = dataSource.getConnection();
PreparedStatement ps = conn.prepareStatement(sql);
ps.setInt(1, customer.getCustId());
ps.setString(2, customer.getName());
ps.setInt(3, customer.getAge());
ps.executeUpdate();
ps.close();
} catch (SQLException e) {
throw new RuntimeException(e);
} finally {
if (conn != null) {
try {
conn.close();
} catch (SQLException e) {}
}
}
}
2. Example With JdbcTemplate
With JdbcTemplate, you save a lot of typing on the redundant codes, becuase JdbcTemplate will handle it automatically.
private DataSource dataSource;
private JdbcTemplate jdbcTemplate;
public void setDataSource(DataSource dataSource) {
this.dataSource = dataSource;
}
public void insert(Customer customer){
String sql = "INSERT INTO CUSTOMER " +
"(CUST_ID, NAME, AGE) VALUES (?, ?, ?)";
jdbcTemplate = new JdbcTemplate(dataSource);
jdbcTemplate.update(sql, new Object[] { customer.getCustId(),
customer.getName(),customer.getAge()
});
}
See the different?
3. Example With JdbcDaoSupport
By extended the JdbcDaoSupport, set the datasource and JdbcTemplate in your class is no longer required, you just need to inject the correct datasource into JdbcCustomerDAO. And you can get the JdbcTemplate by using a getJdbcTemplate() method.
public class JdbcCustomerDAO extends JdbcDaoSupport implements CustomerDAO
{
//no need to set datasource here
public void insert(Customer customer){
String sql = "INSERT INTO CUSTOMER " +
"(CUST_ID, NAME, AGE) VALUES (?, ?, ?)";
getJdbcTemplate().update(sql, new Object[] { customer.getCustId(),
customer.getName(),customer.getAge()
});
}
<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-2.5.xsd">
<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/mkyongjava" />
<property name="username" value="root" />
<property name="password" value="password" />
</bean>
</beans>
<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-2.5.xsd">
<bean id="customerDAO" class="com.mkyong.customer.dao.impl.JdbcCustomerDAO">
<property name="dataSource" ref="dataSource" />
</bean>
</beans>
In Spring JDBC development, it’s always recommended to use
JdbcTemplate
and JdbcDaoSupport
, instead of coding JDBC code yourself.
this article was super helpful in clarifying these concepts for me, thank you so much!
Hi Sir ,
Please upload Spring with JPA Integration.
Thanks
Hi sir,
Please upload Spring with jpa integration with explanation
very good example,,,one thing dont understand why dont you have mentioned schema name in the datasource.
In DataSource there is only URL, username and password dont have schema name also in the insert query there is not a schema name.
String sql = “INSERT INTO CUSTOMER ” +
“(CUST_ID, NAME, AGE) VALUES (?, ?, ?)”;
getJdbcTemplate().update(sql, new Object[] { customer.getCustId(),
customer.getName(),customer.getAge()
});
Can we use this for select statement
//insert with named parameter
public void insertNamedParameter(Customer customer){
//not supported
}
Hello Young,
not supported means jdbcdaosupprt class doesnot support namedparameter
please explain young or some one else
Thanks & Regards,
Devi Patnaik
could anyone please help me
what is the need of above bean? if It is links with db then how?
could anyone please help me
what is the need of above bean? if It is links with db then how?
could anyone please help me
what is the need of above bean? if It is links with db then how?
could anyone please help me
what is the need of above bean? if It is links with db then how?
Hi,
I have a requirement where I need to use 2 different datasources.This is what I am trying to do in spring xml and DAOImpl, but its not working.
In the DAOImpl I am doing
public OpsysDAOImpl(DataSource dataSource1, DataSource dataSource2 ){
setDataSource(dataSource1);
setDataSource(dataSource2);
JdbcTemplate jdbcTemplate = getJdbcTemplate();
}
thank you so much … it would be great if u can mention what jar files are needed to be added
Really big thanks for this tuto 🙂
Hi,
Such a wonderful post.I have implemented this in code.
But I am not able to log the SQLs executed by the jdbctemplate.
Can you help me out ?
Thanks.
when i start the server it will through the following error
Class not found error :org/springframework/dao/DataAccessException
Im using spring 3.2.1
Can we create a GenericDao that extends the jdbcDaoSupport and then inyect it to other Daos ?
Hi, nice post. Can u tell me how to create a GenericDao that extends the jdbc template and then inyect it to other Daos. Ty
Hi,
I have followed this guide, but apparently I am using an Oracle database so instead of this:
I used this:
—
Everything is good, aside from the fact that when I call:
getJdbcTemplate().queryForList(sql);
the getJdbcTemplate() returns null.
How do I debug where is the error? I am pretty sure that the values I insert in the xml file are correct because I used those values in a normal jdbc.
Thanks in advance.
Regards,
Alvin
Hi,
Before using JdbcDaoSupport, my beans were autowired in the test class and all tests were passing. But after implementing JdbcDaoSupport, the beans are not getting injected to the test classed and my tests are not passing. If I use applicationContext and get the bean, I am getting the bean but as proxy.
Can you please let me know what could be wrong?
Thanks
why dont u use try catch block with jdbcTemplate
pleas
Provide the solution for jdbctemplate with to use mysql view s and stored procedures
Thank you for easily understandable tutorial! Really helpful!
Hi,
I am new to Spring. Your tutorials are really nice and quick to pick up.
Thanks
Hi sir how to write simple spring jdbc code to print student details into a table plz give me the code am new to spring
thanks for tutorial.
can i set up multiple dataSource?
can i create 2 datasources in xml file?
such as
thanks
Hi Taka,
Thanks, it is nice post.
I got stuck at same thing. I have to work with multiple data sources. Could you please let me know if you are already done, thanks in advance.
Thanks for wonderful tutorial..
Can we have same thing for Stored Procedure ?
Thank you for the another compact and clear tutorial!
How to control a transaction here?
hmmmmmmmmmmm nice usage of jdbcTemplate
well wat is your CustomerDAO code can you show it…
mostly i use samething like that in spring
com.dps.midsurvey.dataaccess.orm.MaintainQuestionair
com.dps.midsurvey.dataaccess.orm.campus
com.dps.midsurvey.dataaccess.orm.Survey
org.hibernate.dialect.SQLServerDialect
true
and in code of DAO class simply use hibernate session
SessionFactory sessionFactory = null;
Session session = null;
BasicQuestionair basicQuestionair;
/**
*
* @param sessionFactory
*/
public campusDAO(SessionFactory sessionFactory) {
this.sessionFactory = sessionFactory;
}
/**
* @param entity
* BasicQuestionair entity to persist
* @throws RuntimeException
* when the operation fails
*/
public void createcampus(campus entity) {
LoogingHelper.log(“Try to saving BasicQuestionair instance”,
Level.INFO, null);
try {
session = getSession();
Transaction tx = session.beginTransaction();
session.save(entity);
tx.commit();
LoogingHelper.log(“save successful”, Level.INFO, null);
} catch (HibernateException re) {
session.getTransaction().rollback();
LoogingHelper.log(“save failed”, Level.SEVERE, re);
throw re;
}
}
simply use of hibernate in spring
Hi All ,
The corrected mysql query used for this program is as follows –
1. create database springJdbcTemplateExample;
2. use springJdbcTemplateExample
3. CREATE TABLE customer(CUST_ID int(10) unsigned NOT NULL AUTO_INCREMENT,
NAME varchar(100) NOT NULL,
AGE smallint(5) unsigned NOT NULL,
PRIMARY KEY (CUST_ID))
ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;;
4. Run the program…