Spring -- JdbcTemplate, jdbctemplate

Spring -- JdbcTemplate, jdbctemplate

Introduction to JdbcTemplate

Sample Code:

First, configure the database. The database contains two tables: employee and department.

1 jdbc.user=scott2 jdbc.password=tiger3 jdbc.driverClass=oracle.jdbc.driver.OracleDriver4 jdbc.jdbcUrl=jdbc:oracle:thin:@localhost:1521:orcl5 6 jdbc.initialPoolSize=57 jdbc.maxPoolSize=10

Then the spring configuration file

1 <? Xml version = "1.0" encoding = "UTF-8"?> 2 <beans xmlns = "http://www.springframework.org/schema/beans" 3 xmlns: xsi = "http://www.w3.org/2001/XMLSchema-instance" 4 xmlns: context = "http://www.springframework.org/schema/context" 5 xsi: schemaLocation = "http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd http://www.springframework.org/schema/context 6 http://www.springframework.org/schema/contex T/spring-context-4.1.xsd "> 7 8 <context: component-scan base-package =" com. yl. spring. jdbc "> </context: component-scan> 9 10 <! -- Import the property file --> 11 <context: property-placeholder location = "classpath: db. properties"/> 12 <! -- Configure the c3p0 data source --> 13 <bean id = "dataSource" class = "com. mchange. v2.c3p0. comboPooledDataSource "> 14 <property name =" user "value =" $ {jdbc. user} "> </property> 15 <property name =" password "value =" $ {jdbc. password} "> </property> 16 <property name =" driverClass "value =" $ {jdbc. driverClass} "> </property> 17 <property name =" jdbcUrl "value =" $ {jdbc. jdbcUrl} "> </property> 18 19 <property name =" initialPoolSize "value =" $ {jdbc. I NitialPoolSize} "> </property> 20 <property name =" maxPoolSize "value =" $ {jdbc. maxPoolSize} "> </property> 21 </bean> 22 23 <! -- Configure Spring JdbcTemplate --> 24 <bean id = "jdbcTemplate" class = "org. springframework. jdbc. core. jdbcTemplate "> 25 <property name =" dataSource "ref =" dataSource "> </property> 26 </bean> 27 </beans>

There are two entity classes: Employee and Department.

 1 package com.yl.spring.jdbc; 2  3 public class Employee { 4     private Integer id; 5     private String lastName; 6     private String email; 7      8     private Department department; 9 10     public Integer getId() {11         return id;12     }13 14     public void setId(Integer id) {15         this.id = id;16     }17 18     public String getLastName() {19         return lastName;20     }21 22     public void setLastName(String lastName) {23         this.lastName = lastName;24     }25 26     public String getEmail() {27         return email;28     }29 30     public void setEmail(String email) {31         this.email = email;32     }33 34     public Department getDepartment() {35         return department;36     }37 38     public void setDepartment(Department department) {39         this.department = department;40     }41 42     @Override43     public String toString() {44         return "Employee [id=" + id + ", lastName=" + lastName + ", email="45                 + email + ", department=" + department + "]";46     }47     48     49 }

 1 package com.yl.spring.jdbc; 2  3 public class Department { 4     private Integer id; 5     private String name; 6     public Integer getId() { 7         return id; 8     } 9     public void setId(Integer id) {10         this.id = id;11     }12     public String getName() {13         return name;14     }15     public void setName(String name) {16         this.name = name;17     }18     @Override19     public String toString() {20         return "Department [id=" + id + ", name=" + name + "]";21     }22     23     24 }

Then the test class

1 package com. yl. spring. jdbc; 2 3 4 import java. SQL. SQLException; 5 import java. util. arrayList; 6 import java. util. list; 7 8 import javax. SQL. dataSource; 9 10 import org. junit. test; 11 import org. springframework. context. applicationContext; 12 import org. springframework. context. support. classPathXmlApplicationContext; 13 import org. springframework. jdbc. core. beanPropertyRowMapper; 14 import org. spring Framework. jdbc. core. jdbcTemplate; 15 import org. springframework. jdbc. core. rowMapper; 16 17 public class JDBCTest {18 19 private ApplicationContext ctx = null; 20 private JdbcTemplate jdbcTemplate; 21 private EmployeeDao employeeDao; 22 23 {24 ctx = new ClassPathXmlApplicationContext ("applicationContext. xml "); 25 jdbcTemplate = (JdbcTemplate) ctx. getBean ("jdbcTemplate"); 26 employeeDao = ctx. getBe An (EmployeeDao. class); 27} 28 29 @ Test 30 public void testEmployeeDao () {31 System. out. println (employeeDao. get (1); 32} 33 34/** 35 * get the value of a single column, or make a statistical query 36 */37 @ Test 38 public void testQueryForObject2 () {39 String SQL = "SELECT count (id) FROM employee"; 40 long count = jdbcTemplate. queryForObject (SQL, Long. class); 41 System. out. println (count); 42} 43 44/*** 45 * query the collection of entity classes 46 */47 @ Test 48 publ Ic void testQueryForList () {49 String SQL = "SELECT id, last_name, email FROM employee WHERE id>? "; 50 RowMapper <Employee> rowMapper = new BeanPropertyRowMapper <Employee> (Employee. class); 51 List <Employee> employees = jdbcTemplate. query (SQL, rowMapper, 5); 52 System. out. println (employees); 53} 54 55/** 56 * get a record from the database, and get the corresponding object 57*1. rowMapper specifies how to map rows in the result set. The common implementation class is BeanPropertyRowMapper 58*2. use the column alias in SQL to map column names and Class Attribute names. For example, last_name and lastName 59*3. Cascade attributes are not supported. JdbcTemplate is a JDBC tool, not an ORM framework. 60 */61 @ Test 62 public void TestForQueryObject () {63 String SQL = "SELECT id, last_name, email FROM employee WHERE id =? "; 64 RowMapper <Employee> rowMapper = new BeanPropertyRowMapper <Employee> (Employee. class); 65 66 Employee employee = jdbcTemplate. queryForObject (SQL, rowMapper, 1); 67 System. out. println (employee); 68} 69 70/*** 71 * batch UPDATE: Batch INSERT, UPDATE, DELETE 72 * The last parameter is the list type of Object: because modifying a record requires an array of objects, multiple objects require an array of 73 */74 @ Test 75 public void testBatchUpdate () {76 String SQL = "INSERT INTO e Mployee (id, last_name, email, dept_id) VALUES (?, ?, ?, ?) "; 77 List <Object []> batchArgs = new ArrayList <Object []> (); 78 batchArgs. add (new Object [] {6, "AA", "AA@163.com", 1}); 79 batchArgs. add (new Object [] {7, "BB", "BB@163.com", 2}); 80 batchArgs. add (new Object [] {8, "CC", "CC@163.com", 3}); 81 batchArgs. add (new Object [] {9, "DD", "DD@163.com", 3}); 82 batchArgs. add (new Object [] {10, "EE", "EE@163.com", 2}); 83 jdbcTemplate. batchUpdate (SQL, batchArgs); 84} 85 86/** 87 * execute INSERT, UPDATE, DELETE 88 */89 @ Test 90 public void testUpdate () {91 String SQL = "UPDATE employee SET last_name =? WHERE id =? "; 92 jdbcTemplate. update (SQL, "Jack", 5); 93} 94 95 @ Test 96 public void test () throws SQLException {97 DataSource dataSource = (DataSource) ctx. getBean ("dataSource"); 98 System. out. println (dataSource. getConnection (); 99} 100 101}

If JdbcTemplate is used in the project, you can refer to the following method to write the corresponding dao:

 1 package com.yl.spring.jdbc; 2  3 import org.springframework.beans.factory.annotation.Autowired; 4 import org.springframework.jdbc.core.BeanPropertyRowMapper; 5 import org.springframework.jdbc.core.JdbcTemplate; 6 import org.springframework.jdbc.core.RowMapper; 7 import org.springframework.stereotype.Repository; 8  9 @Repository10 public class EmployeeDao {11     12     @Autowired13     private JdbcTemplate jdbcTemplate;14     15     public Employee get(Integer id) {16         String sql = "SELECT id, last_name, email FROM employee WHERE id = ?";17         RowMapper<Employee> rowMapper = new BeanPropertyRowMapper<Employee>(Employee.class);18         19         Employee employee = jdbcTemplate.queryForObject(sql, rowMapper, id);20         21         return employee;22     }23 }