Spring详细内容-Spring JdbcTemplate En(5)

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

1.Spring JdbcTemplate

  • Spring JdbcTemplateis 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 from 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

在这里插入图片描述

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

1)PreparedStatementCallback interface

It has only one method doInPreparedStatement. Syntax of the method

  1. public T doInPreparedStatement(PreparedStatement ps)throws SQLException, DataAccessException

OracleDataBase

  1. create table employee(
  2. id number(10),
  3. name varchar2(100),
  4. salary number(10)
  5. );

Employee.java

  1. public class Employee {
  2. private int id;
  3. private String name;
  4. private float salary;
  5. public int getId() {
  6. return id;
  7. }
  8. public Employee(int id, String name, float salary) {
  9. this.id = id;
  10. this.name = name;
  11. this.salary = salary;
  12. }
  13. public void setId(int id) {
  14. this.id = id;
  15. }
  16. public String getName() {
  17. return name;
  18. }
  19. public void setName(String name) {
  20. this.name = name;
  21. }
  22. public float getSalary() {
  23. return salary;
  24. }
  25. public void setSalary(float salary) {
  26. this.salary = salary;
  27. }
  28. }

EmployeeDao.java

  1. public class EmployeeDao {
  2. private JdbcTemplate jdbcTemplate;
  3. public void setJdbcTemplate(JdbcTemplate jdbcTemplate){
  4. this.jdbcTemplate= jdbcTemplate;
  5. }
  6. public Boolean saveEmployeeByPreparedStatement(final Employee e){
  7. String query="insert into employee values(?,?,?)";
  8. return jdbcTemplate.execute(query,new PreparedStatementCallback<Boolean>(){
  9. @Override
  10. public Boolean doInPreparedStatement(PreparedStatement ps)
  11. throws SQLException, DataAccessException {
  12. ps.setInt(1,e.getId());
  13. ps.setString(2,e.getName());
  14. ps.setFloat(3,e.getSalary());
  15. return ps.execute();
  16. }
  17. });
  18. }
  19. }

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 DriverManagerDataSourceobject in the JdbcTemplate class for the datasource property.

    <?xml version=”1.0” encoding=”UTF-8”?>



    thin:@localhost:1521:xe" />






  1. </beans>

Test.java

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

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

  1. create table employee(
  2. id number(10),
  3. name varchar2(100),
  4. salary number(10)
  5. );

Employee.java

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

  1. package com.javatpoint;
  2. public class Employee {
  3. private int id;
  4. private String name;
  5. private float salary;
  6. //no-arg and parameterized constructors
  7. //getters and setters
  8. public String toString(){
  9. return id+" "+name+" "+salary;
  10. }
  11. }

EmployeeDao.java

It contains on property jdbcTemplate and one method getAllEmployees.

  1. public class EmployeeDao {
  2. private JdbcTemplate template;
  3. public void setTemplate(JdbcTemplate template) {
  4. this.template = template;
  5. }
  6. public List<Employee> getAllEmployees(){
  7. return template.query("select * from employee",new ResultSetExtractor<List<Employee>>(){
  8. @Override
  9. public List<Employee> extractData(ResultSet rs) throws SQLException,
  10. DataAccessException {
  11. List<Employee> list=new ArrayList<Employee>();
  12. while(rs.next()){
  13. Employee e=new Employee();
  14. e.setId(rs.getInt(1));
  15. e.setName(rs.getString(2));
  16. e.setSalary(rs.getInt(3));
  17. list.add(e);
  18. }
  19. return list;
  20. }
  21. });
  22. }
  23. }

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”?>





    thin:@localhost:1521:xe" />












Test.java

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

4.RowMapper

  • Like ResultSetExtractor, we can useRowMapper interface tofetch the records from the database using query() methodof JdbcTemplate class.

    public T query(String sql,RowMapper 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.

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

OracleDataBase

  1. create table employee(
  2. id number(10),
  3. name varchar2(100),
  4. salary number(10)
  5. );

Employee.java

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

  1. public class Employee {
  2. private int id;
  3. private String name;
  4. private float salary;
  5. //no-arg and parameterized constructors
  6. //getters and setters
  7. public String toString(){
  8. return id+" "+name+" "+salary;
  9. }
  10. }

EmployeeDao.java

It contains on property jdbcTemplate and one method getAllEmployeesRowMapper.

  1. public class EmployeeDao {
  2. private JdbcTemplate template;
  3. public void setTemplate(JdbcTemplate template) {
  4. this.template = template;
  5. }
  6. public List<Employee> getAllEmployeesRowMapper(){
  7. return template.query("select * from employee",new RowMapper<Employee>(){
  8. @Override
  9. public Employee mapRow(ResultSet rs, int rownumber) throws SQLException {
  10. Employee e=new Employee();
  11. e.setId(rs.getInt(1));
  12. e.setName(rs.getString(2));
  13. e.setSalary(rs.getInt(3));
  14. return e;
  15. }
  16. });
  17. }
  18. }

applicationContext.xml

  1. <?xml version="1.0" encoding="UTF-8"?>
  2. <beans
  3. xmlns="http://www.springframework.org/schema/beans"
  4. xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  5. xmlns:p="http://www.springframework.org/schema/p"
  6. xsi:schemaLocation="http://www.springframework.org/schema/beans
  7. http://www.springframework.org/schema/beans/spring-beans-3.0.xsd">
  8. <bean id="ds" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
  9. <property name="driverClassName" value="oracle.jdbc.driver.OracleDriver" />
  10. <property name="url" value="jdbc:oracle:thin:@localhost:1521:xe" />
  11. <property name="username" value="system" />
  12. <property name="password" value="oracle" />
  13. </bean>
  14. <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
  15. <property name="dataSource" ref="ds"></property>
  16. </bean>
  17. <bean id="edao" class="com.javatpoint.EmployeeDao">
  18. <property name="jdbcTemplate" ref="jdbcTemplate"></property>
  19. </bean>
  20. </beans>

Test.java

  1. package com.javatpoint;
  2. import java.util.List;
  3. import org.springframework.context.ApplicationContext;
  4. import org.springframework.context.support.ClassPathXmlApplicationContext;
  5. public class Test {
  6. public static void main(String[] args) {
  7. ApplicationContext ctx=new ClassPathXmlApplicationContext("applicationContext.xml");
  8. EmployeeDao dao=(EmployeeDao)ctx.getBean("edao");
  9. List<Employee> list=dao.getAllEmployeesRowMapper();
  10. for(Employee e:list)
  11. System.out.println(e);
  12. }
  13. }

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

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

OracleDataBase

  1. create table employee(
  2. id number(10),
  3. name varchar2(100),
  4. salary number(10)
  5. );

Employee.java

  1. public class Employee {
  2. private int id;
  3. private String name;
  4. private float salary;
  5. //no-arg and parameterized constructors
  6. //getters and setters
  7. }

EmployeeDao.java

  1. public class EmpDao {
  2. NamedParameterJdbcTemplate template;
  3. public EmpDao(NamedParameterJdbcTemplate template) {
  4. this.template = template;
  5. }
  6. public void save (Emp e){
  7. String query="insert into employee values (:id,:name,:salary)";
  8. Map<String,Object> map=new HashMap<String,Object>();
  9. map.put("id",e.getId());
  10. map.put("name",e.getName());
  11. map.put("salary",e.getSalary());
  12. template.execute(query,map,new PreparedStatementCallback() {
  13. @Override
  14. public Object doInPreparedStatement(PreparedStatement ps)
  15. throws SQLException, DataAccessException {
  16. return ps.executeUpdate();
  17. }
  18. });
  19. }
  20. }

applicationContext.xml

  1. <?xml version="1.0" encoding="UTF-8"?>
  2. <beans
  3. xmlns="http://www.springframework.org/schema/beans"
  4. xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  5. xmlns:p="http://www.springframework.org/schema/p"
  6. xsi:schemaLocation="http://www.springframework.org/schema/beans
  7. http://www.springframework.org/schema/beans/spring-beans-3.0.xsd">
  8. <bean id="ds" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
  9. <property name="driverClassName" value="oracle.jdbc.driver.OracleDriver" />
  10. <property name="url" value="jdbc:oracle:thin:@localhost:1521:xe" />
  11. <property name="username" value="system" />
  12. <property name="password" value="oracle" />
  13. </bean>
  14. <bean id="jtemplate"
  15. class="org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate">
  16. <constructor-arg ref="ds"></constructor-arg>
  17. </bean>
  18. <bean id="edao" class="com.javatpoint.EmpDao">
  19. <constructor-arg>
  20. <ref bean="jtemplate"/>
  21. </constructor-arg>
  22. </bean>
  23. </beans>

SimpleTest.java

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

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

  1. create table employee(
  2. id number(10),
  3. name varchar2(100),
  4. salary number(10)
  5. );

Employee.java

  1. public class Employee {
  2. private int id;
  3. private String name;
  4. private float salary;
  5. //no-arg and parameterized constructors
  6. //getters and setters
  7. }

EmployeeDao.java

  1. public EmpDao(SimpleJdbcTemplate template) {
  2. this.template = template;
  3. }
  4. public int update (Emp e){
  5. String query="update employee set name=? where id=?";
  6. return template.update(query,e.getName(),e.getId());
  7. //String query="update employee set name=?,salary=? where id=?";
  8. //return template.update(query,e.getName(),e.getSalary(),e.getId());
  9. }
  10. }

applicationContext.xml

  1. <?xml version="1.0" encoding="UTF-8"?>
  2. <beans
  3. xmlns="http://www.springframework.org/schema/beans"
  4. xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  5. xmlns:p="http://www.springframework.org/schema/p"
  6. xsi:schemaLocation="http://www.springframework.org/schema/beans
  7. http://www.springframework.org/schema/beans/spring-beans-3.0.xsd">
  8. <bean id="ds" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
  9. <property name="driverClassName" value="oracle.jdbc.driver.OracleDriver" />
  10. <property name="url" value="jdbc:oracle:thin:@localhost:1521:xe" />
  11. <property name="username" value="system" />
  12. <property name="password" value="oracle" />
  13. </bean>
  14. <bean id="jtemplate" class="org.springframework.jdbc.core.simple.SimpleJdbcTemplate">
  15. <constructor-arg ref="ds"></constructor-arg>
  16. </bean>
  17. <bean id="edao" class="com.javatpoint.EmpDao">
  18. <constructor-arg>
  19. <ref bean="jtemplate"/>
  20. </constructor-arg>
  21. </bean>
  22. </beans>

SimpleTest.java

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

发表评论

表情:
评论列表 (有 0 条评论,244人围观)

还没有评论,来说两句吧...

相关阅读