Spring详细内容-Spring JdbcTemplate En（5）

r囧r小猫 2023-09-24 19:44 130阅读 0赞

# 1.Spring JdbcTemplate #

*  `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.

## 1)Problems of JDBC API ##

*  We need to **write a lot of code before and after executing the query**, such as creating connection, statement, closing resultset, connection etc.
 *  We need to perform exception handling code on the `database logic`.
 *  We need to `handle transaction`.
 *  Repetition of all these codes fro**m one to another database logic** is **a time consuming task**.

## 2)Advantage of Spring JdbcTemplate ##

*  Spring JdbcTemplate **eliminates all the above mentioned problems** of JDBC API.
 *  It provides you methods to **write the queries directly,** so it saves a lot of work and time.

## 3)Spring Jdbc Approaches ##

*  JdbcTemplate
 *  NamedParameterJdbcTemplate
 *  SimpleJdbcTemplate
 *  SimpleJdbcInsert and SimpleJdbcCall

## 4) JdbcTemplate class ##

*  It is **the central class** in the Spring JDBC support classes.
 *  It **takes care of creation and release of resources** such as creating and closing of connection object etc.
 *  So it will not lead to any problem if you forget to close the connection.
 *  It handles the exception and provides the informative exception messages by the help of excepion classes defined in the `org.springframework.dao package`.

\-We can perform all the database operations by the help of JdbcTemplate class such as **insertion, updation, deletion and retrieval of the data from the databas**

![在这里插入图片描述][576f24c9c2884f0d84c4e3264c9bd06d.png]

# 2.PreparedStatement in Spring JDBC Template #

*  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>);

## 1)PreparedStatementCallback interface ##

It has only one method `doInPreparedStatement`. Syntax of the method

public T doInPreparedStatement(PreparedStatement ps)throws SQLException, DataAccessException

## OracleDataBase ##

create table employee(  
    id number(10),  
    name varchar2(100),  
    salary number(10)  
    );

### Employee.java ###

public class Employee {
        
        private int id;
        private String name;
        private float salary;
    
        public int getId() {
        
            return id;
        }
    
        public Employee(int id, String name, float salary) {
        
            this.id = id;
            this.name = name;
            this.salary = salary;
        }
    
        public void setId(int id) {
        
            this.id = id;
        }
    
        public String getName() {
        
            return name;
        }
    
        public void setName(String name) {
        
            this.name = name;
        }
    
        public float getSalary() {
        
            return salary;
        }
    
        public void setSalary(float salary) {
        
            this.salary = salary;
        }
    
    }

### EmployeeDao.java ###

public class EmployeeDao {
        
        private JdbcTemplate jdbcTemplate;
        public void setJdbcTemplate(JdbcTemplate jdbcTemplate){
        
            this.jdbcTemplate= jdbcTemplate;
        }
        public Boolean saveEmployeeByPreparedStatement(final Employee e){
        
            String query="insert into employee values(?,?,?)";
            return jdbcTemplate.execute(query,new PreparedStatementCallback<Boolean>(){
        
                @Override
                public Boolean doInPreparedStatement(PreparedStatement ps)
                        throws SQLException, DataAccessException {
        
    
                    ps.setInt(1,e.getId());
                    ps.setString(2,e.getName());
                    ps.setFloat(3,e.getSalary());
    
                    return ps.execute();
                }
            });
        }
    }

### applicationContext.xml ###

*  The `DriverManagerDataSource` is used to contain the information about the database such as **driver class name, connnection URL, username and password.**
 *  There are a property named `datasource` in the JdbcTemplate class of DriverManagerDataSource type.
 *  So, we need to provide the reference of `DriverManagerDataSource`object in the `JdbcTemplate` class for the datasource property.

<?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="oracle.jdbc.driver.OracleDriver" />  
    <property name="url" value="jdbc:oracle:thin:@localhost:1521:xe" />  
    <property name="username" value="system" />  
    <property name="password" value="oracle" />  
    </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>

### Test.java ###

package com.javatpoint;  
      
    import org.springframework.context.ApplicationContext;  
    import org.springframework.context.support.ClassPathXmlApplicationContext;  
    public class Test {
          
      
    public static void main(String[] args) {
          
        ApplicationContext ctx=new ClassPathXmlApplicationContext("applicationContext.xml");  
              
        EmployeeDao dao=(EmployeeDao)ctx.getBean("edao");  
        dao.saveEmployeeByPreparedStatement(new Employee(108,"Amit",35000));  
    }  
    }

# 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)`

## 1)ResultSetExtractor Interface ##

*  `ResultSetExtractor interface` can be used to fetch records from the database.
 *  It accepts a ResultSet and returns the list.

## OracleDataBase ##

create table employee(  
    id number(10),  
    name varchar2(100),  
    salary number(10)  
    );

### Employee.java ###

This class contains **3 properties with constructors and setter and getters.** It defines one extra method toString().

package com.javatpoint;  
      
    public class Employee {
          
    private int id;  
    private String name;  
    private float salary;  
    //no-arg and parameterized constructors  
    //getters and setters  
      
    public String toString(){
          
        return id+" "+name+" "+salary;  
    }  
    }

### EmployeeDao.java ###

It contains on **property jdbcTemplate and one method getAllEmployees.**

public class EmployeeDao {
          
    private JdbcTemplate template;  
      
    public void setTemplate(JdbcTemplate template) {
          
        this.template = template;  
    }  
      
    public List<Employee> getAllEmployees(){
          
     return template.query("select * from employee",new ResultSetExtractor<List<Employee>>(){
          
        @Override  
         public List<Employee> extractData(ResultSet rs) throws SQLException,  
                DataAccessException {
          
          
            List<Employee> list=new ArrayList<Employee>();  
            while(rs.next()){
          
            Employee e=new Employee();  
            e.setId(rs.getInt(1));  
            e.setName(rs.getString(2));  
            e.setSalary(rs.getInt(3));  
            list.add(e);  
            }  
            return list;  
            }  
        });  
      }  
    }

### applicationContext.xml ###

*  The `DriverManagerDataSource` is used to contain the information about the database such as **driver class name, connnection URL, username and password.**

<?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="oracle.jdbc.driver.OracleDriver" />  
    <property name="url" value="jdbc:oracle:thin:@localhost:1521:xe" />  
    <property name="username" value="system" />  
    <property name="password" value="oracle" />  
    </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>

### Test.java ###

public class Test {
          
      
    public static void main(String[] args) {
          
        ApplicationContext ctx=new ClassPathXmlApplicationContext("applicationContext.xml");  
        EmployeeDao dao=(EmployeeDao)ctx.getBean("edao");  
        List<Employee> list=dao.getAllEmployees();  
              
        for(Employee e:list)  
            System.out.println(e);  
              
        }  
      
    }

# 4.RowMapper #

*  Like `ResultSetExtractor`, we can use`RowMapper interface` to`fetch the records` from the `database` using `query() method`of JdbcTemplate class.

public T query(String sql,RowMapper<T> rm)

## 1)RowMapper Interface ##

*  `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**.

### Advantage of RowMapper over ResultSetExtractor ###

*  RowMapper saves a lot of code becuase it internally adds the data of ResultSet into the collection.

## 2）Method of RowMapper interface ##

It defines only one method **mapRow** that accepts ResultSet instance and int as the parameter list.

public T mapRow(ResultSet rs, int rowNumber)throws SQLException

## OracleDataBase ##

create table employee(  
    id number(10),  
    name varchar2(100),  
    salary number(10)  
    );

## Employee.java ##

This class contains 3 properties with constructors and setter and getters and one extra method toString().

public class Employee {
          
    private int id;  
    private String name;  
    private float salary;  
    //no-arg and parameterized constructors  
    //getters and setters  
    public String toString(){
          
        return id+" "+name+" "+salary;  
    }  
    }

## EmployeeDao.java ##

It contains on **property jdbcTemplate** and one method **getAllEmployeesRowMapper**.

public class EmployeeDao {
          
    private JdbcTemplate template;  
      
    public void setTemplate(JdbcTemplate template) {
          
        this.template = template;  
    }  
      
    public List<Employee> getAllEmployeesRowMapper(){
          
     return template.query("select * from employee",new RowMapper<Employee>(){
          
        @Override  
        public Employee mapRow(ResultSet rs, int rownumber) throws SQLException {
          
            Employee e=new Employee();  
            e.setId(rs.getInt(1));  
            e.setName(rs.getString(2));  
            e.setSalary(rs.getInt(3));  
            return e;  
        }  
        });  
    }  
    }

## applicationContext.xml ##

## Test.java ##

package com.javatpoint;  
      
    import java.util.List;  
      
    import org.springframework.context.ApplicationContext;  
    import org.springframework.context.support.ClassPathXmlApplicationContext;  
    public class Test {
          
    public static void main(String[] args) {
          
        ApplicationContext ctx=new ClassPathXmlApplicationContext("applicationContext.xml");  
        EmployeeDao dao=(EmployeeDao)ctx.getBean("edao");  
        List<Employee> list=dao.getAllEmployeesRowMapper();  
              
        for(Employee e:list)  
            System.out.println(e);  
    }  
    }

# 5.Spring NamedParameterJdbcTemplate #

*  Spring provides another way to insert data `by named parameter`.
 *  In such way, we use names instead of ?(question mark). So it is better to remember the data for the column.

insert into employee values (:id,:name,:salary)

Method of NamedParameterJdbcTemplate class

pubic T execute(String sql,Map map,PreparedStatementCallback psc)

## OracleDataBase ##

create table employee(  
    id number(10),  
    name varchar2(100),  
    salary number(10)  
    );

## Employee.java ##

public class Employee {
          
    private int id;  
    private String name;  
    private float salary;  
    //no-arg and parameterized constructors  
    //getters and setters  
    }

## EmployeeDao.java ##

public class EmpDao {
          
    NamedParameterJdbcTemplate template;  
      
    public EmpDao(NamedParameterJdbcTemplate template) {
          
            this.template = template;  
    }  
    public  void save (Emp e){
          
    String query="insert into employee values (:id,:name,:salary)";  
      
    Map<String,Object> map=new HashMap<String,Object>();  
    map.put("id",e.getId());  
    map.put("name",e.getName());  
    map.put("salary",e.getSalary());  
      
    template.execute(query,map,new PreparedStatementCallback() {
          
        @Override  
        public Object doInPreparedStatement(PreparedStatement ps)  
                throws SQLException, DataAccessException {
          
            return ps.executeUpdate();  
        }  
    });  
    }  
    }

## applicationContext.xml ##

<?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="oracle.jdbc.driver.OracleDriver" />  
    <property name="url" value="jdbc:oracle:thin:@localhost:1521:xe" />  
    <property name="username" value="system" />  
    <property name="password" value="oracle" />  
    </bean>  
      
    <bean id="jtemplate"   
     class="org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate">  
    <constructor-arg ref="ds"></constructor-arg>  
    </bean>  
      
    <bean id="edao" class="com.javatpoint.EmpDao">  
    <constructor-arg>  
    <ref bean="jtemplate"/>  
    </constructor-arg>  
    </bean>  
      
    </beans>

## SimpleTest.java ##

public class SimpleTest {
          
      public static void main(String[] args) {
          
          
        Resource r=new ClassPathResource("applicationContext.xml");  
        BeanFactory factory=new XmlBeanFactory(r);  
          
        EmpDao dao=(EmpDao)factory.getBean("edao");  
        dao.save(new Emp(23,"sonoo",50000));  
          
      }  
    }

# 6.Spring SimpleJdbcTemplate Example #

*  Spring 3 JDBC supports the java 5 feature var-args (variable argument) and autoboxing by the help of SimpleJdbcTemplate class.
 *  SimpleJdbcTemplate class **wraps the JdbcTemplate class and provides the update method** where we can pass arbitrary number of arguments.

int update(String sql,Object... parameters)

## OracleDataBase ##

create table employee(  
    id number(10),  
    name varchar2(100),  
    salary number(10)  
    );

## Employee.java ##

public class Employee {
          
    private int id;  
    private String name;  
    private float salary;  
    //no-arg and parameterized constructors  
    //getters and setters  
    }

## EmployeeDao.java ##

public EmpDao(SimpleJdbcTemplate template) {
          
            this.template = template;  
    }  
    public int update (Emp e){
          
    String query="update employee set name=? where id=?";  
    return template.update(query,e.getName(),e.getId());  
      
    //String query="update employee set name=?,salary=? where id=?";  
    //return template.update(query,e.getName(),e.getSalary(),e.getId());  
    }  
      
    }

## applicationContext.xml ##

<?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="oracle.jdbc.driver.OracleDriver" />  
    <property name="url" value="jdbc:oracle:thin:@localhost:1521:xe" />  
    <property name="username" value="system" />  
    <property name="password" value="oracle" />  
    </bean>  
      
    <bean id="jtemplate" class="org.springframework.jdbc.core.simple.SimpleJdbcTemplate">  
    <constructor-arg ref="ds"></constructor-arg>  
    </bean>  
      
    <bean id="edao" class="com.javatpoint.EmpDao">  
    <constructor-arg>  
    <ref bean="jtemplate"/>  
    </constructor-arg>  
    </bean>  
      
    </beans>

## SimpleTest.java ##

public class SimpleTest {
          
    public static void main(String[] args) {
          
          
        Resource r=new ClassPathResource("applicationContext.xml");  
        BeanFactory factory=new XmlBeanFactory(r);  
          
        EmpDao dao=(EmpDao)factory.getBean("edao");  
        int status=dao.update(new Emp(23,"Tarun",35000));  
        System.out.println(status);  
    }  
    }

[576f24c9c2884f0d84c4e3264c9bd06d.png]: https://img-blog.csdnimg.cn/576f24c9c2884f0d84c4e3264c9bd06d.png