21. Spring JdbcTemplte & Insert Example with MySQL Database

1. JdbcTemplate
·         It takes care of creation and release of resources
·         It handles the exception
·         We can perform all the database operations insertion, updating, deletion

1)      public int update(String query)
is used to insert, update and delete records.

2)      public int update(String query,Object... args)
is used to insert, update and delete records using PreparedStatement using given arguments.

3)      public void execute(String query)
is used to execute DDL query.

4)      public T execute(String sql, PreparedStatementCallback action)
executes the query by using PreparedStatement callback.

5)      public T query(String sql, ResultSetExtractor )
is used to fetch records using ResultSetExtractor.

6)      public List query(String sql, RowMapper rse)
is used to fetch records using RowMapper.

Steps:
1.      First we have to configure org.springframework.jdbc.datasource.DriverManagerDataSource with  driverName, url, username, password all these
2.      We have to pass DriverManagerDataSource object to org.springframework.jdbc.core.JdbcTemplate
3.      JdbcTemplate class Contains datasource as a property
4.      In our DAO class we must has a JdbcTemplate as a property
5.      In xml we have to inject JdbcTemplate reference to our DAO class as an Object
6.      Implement the methods
Example
//StudentBo.java
package bo;

public class StudentBo {
private int sno;
private String name;
public int getSno() {
       return sno;
}
public void setSno(int sno) {
       this.sno = sno;
}
public String getName() {
       return name;
}
public void setName(String name) {
       this.name = name;
}
public String getAddress() {
       return address;
}
public void setAddress(String address) {
       this.address = address;
}
private String address;
}


///StudentDAO.java
package dao;

import org.springframework.jdbc.core.JdbcTemplate;

import bo.StudentBo;


public class StudentDAO {
      
 private JdbcTemplate template;

public JdbcTemplate getTemplate() {
       return template;
}

public void setTemplate(JdbcTemplate template) {
       this.template = template;
}

public int save(StudentBo bo){
       String qry = "INSERT INTO student (`name`, `address`) VALUES ('"+bo.getName()+"', '"+bo.getAddress()+"')";
       System.out.println("Qry--> "+qry);
       return template.update(qry);
      
}

}

//Main.java
package main;

import org.springframework.beans.factory.BeanFactory;
import org.springframework.context.support.ClassPathXmlApplicationContext;


import bo.StudentBo;

import dao.StudentDAO;

public class Main {
       public static void main(String[] args) {
               
              BeanFactory factory = new ClassPathXmlApplicationContext("res/s.xml");
              StudentDAO dao = (StudentDAO) factory.getBean("dao");
              StudentBo bo = new StudentBo();
              bo.setName("RAKESH");
              bo.setAddress("HYDERABD");
              System.out.println("SAVING : "+dao.save(bo));
       }

}


<!DOCTYPE beans PUBLIC "-//SPRING//DTD BEAN 2.0//EN"
                "http://www.springframework.org/dtd/spring-beans-2.0.dtd">

                <beans>
                <!-- 1. set  Properties to  DriverManagerDataSource  -->
                     <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/test" />
                           <property name="username" value="root" />
                           <property name="password" value="infy" />
                     </bean>
               
                 <!-- 2.set JdbcTemplate  Properties -->     
                     <bean id="jt" class="org.springframework.jdbc.core.JdbcTemplate">
                            <property name="dataSource" ref="ds"></property>                           
                     </bean>
                    
                     <!-- 3.set DAO  Properties -->
                <bean id="dao" class="dao.StudentDAO">
                            <property name="template" ref="jt"></property>                      
                     </bean>
                </beans>

Output
--------
log4j:WARN No appenders could be found for logger (org.springframework.context.support.ClassPathXmlApplicationContext).
log4j:WARN Please initialize the log4j system properly.
Qry--> INSERT INTO student (`name`, `address`) VALUES ('RAKESH', 'HYDERABD')
SAVING : 1
Database
+-----+--------+------------+
| sno | name   | address    |
+-----+--------+------------+
|   1 | Satya  | Vijayawada |
|   2 | asa    | sa         |
|   3 | RAKESH | HYDERABD   |
+-----+--------+------------+