什么是JdbcTemplate
![在这里插入图片描述](https://img-blog.csdnimg.cn/20190304212259122.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQzMzg2NzU0,size_16,color_FFFFFF,t_70)
![在这里插入图片描述](https://img-blog.csdnimg.cn/2019030421243634.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQzMzg2NzU0,size_16,color_FFFFFF,t_70)
配置JdbcTemplate模版Bean
CREATE TABLE `employee` (
`empno` int(11) NOT NULL,
`ename` varchar(200) default NULL,
`age` int(3) default NULL,
`job` varchar(255) default NULL,
`salary` double(100,2) default NULL,
PRIMARY KEY (`empno`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
package mao.shu.spring.jdbc.template;
public class Employee {
private Integer empno;
private Integer age;
private String ename;
private String job;
private Double salary;
public Integer getEmpno() {
return empno;
}
public void setEmpno(Integer empno) {
this.empno = empno;
}
public Integer getAge() {
return age;
}
public void setAge(Integer age) {
this.age = age;
}
public String getEname() {
return ename;
}
public void setEname(String ename) {
this.ename = ename;
}
public String getJob() {
return job;
}
public void setJob(String job) {
this.job = job;
}
public Double getSalary() {
return salary;
}
public void setSalary(Double salary) {
this.salary = salary;
}
@Override
public String toString() {
return "Employee{" +
"empno=" + empno +
", age=" + age +
", ename='" + ename + '\'' +
", job='" + job + '\'' +
", salary=" + salary +
'}';
}
}
package mao.shu.spring.jdbc.template;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;
@Repository
public class EmployeeDao {
@Autowired
private JdbcTemplate jdbcTemplate;
public boolean doCreate(Employee employee){
String sql = "INSERT INTO employee(empno,ename,age,job,salary)VALUES(?,?,?,?,?)";
int result = this.jdbcTemplate.update(
sql,
employee.getEmpno(),
employee.getEname(),
employee.getAge(),
employee.getJob(),
employee.getSalary());
return result > 0;
}
}
- 配置db.properties文件,配置数据库链接数据源
jdbc.user=root
jdbc.password=mysqladmin
jdbc.driverClass=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://localhost/maoshu
<?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:context="http://www.springframework.org/schema/context"
xmlns:aop="http://www.springframework.org/schema/aop"
xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context.xsd http://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop.xsd">
<aop:config proxy-target-class="true"></aop:config>
<context:component-scan base-package="mao.shu.spring.jdbc.template"/>
<context:property-placeholder location="classpath:db.properties"/>
<bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource">
<property name="user" value="${user}"/>
<property name="password" value="${password}"/>
<property name="driverClass" value="${driver}"/>
<property name="jdbcUrl" value="${url}"/>
</bean>
<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
<property name="dataSource" ref="dataSource"/>
</bean>
</beans>
package mao.shu.spring.jdbc.template;
import com.mchange.v2.c3p0.ComboPooledDataSource;
import org.junit.Before;
import org.junit.Test;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import static org.junit.Assert.*;
public class EmployeeDaoTest {
private ApplicationContext app;
private JdbcTemplate template;
private EmployeeDao employeeDao;
@Before
public void before() {
this.app = new ClassPathXmlApplicationContext("mao/shu/spring/jdbc/template/template.xml");
this.template = this.app.getBean("jdbcTemplate", JdbcTemplate.class);
this.employeeDao = this.app.getBean("employeeDao", EmployeeDao.class);
}
@Test
public void doCreate() throws SQLException {
Employee employee = new Employee();
employee.setEmpno(1234);
employee.setEname("xiemaoshu");
employee.setAge(22);
employee.setJob("程序员");
employee.setSalary(12345.5);
this.employeeDao.doCreate(employee);
}
@Test
public void doCreateBath() {
String sql = "INSERT INTO employee(empno,ename,age,job,salary)VALUES(?,?,?,?,?)";
Object[] objects ;
List<Object[]> batchList = new ArrayList<>();
for (int i = 0; i < 100; i++) {
objects = new Object[5];
Employee employee = new Employee();
objects[0] = i;
objects[1] = "xiemaoshu " + i;
objects[2] = 22;
objects[3] = "程序员" + i;
objects[4] = 12345.5 + i;
batchList.add(objects);
}
this.template.batchUpdate(sql,batchList);
}
@Test
public void doGet(){
String sql = "SELECT empno,ename,age,job,salary FROM employee WHERE empno=?";
RowMapper<Employee> rowMapper = new BeanPropertyRowMapper<>(Employee.class);
Employee employee = this.template.queryForObject(sql,rowMapper,1);
System.out.println(employee);
}
@Test
public void getBath(){
String sql = "SELECT empno,ename,age,job,salary FROM employee WHERE empno > ?";
RowMapper<Employee> rowMapper = new BeanPropertyRowMapper<>(Employee.class);
List<Employee> list = this.template.query(sql,rowMapper,85);
System.out.println(list);
}
@Test
public void doUpdate(){
String sql = "UPDATE employee SET ename = ?,job = ? WHERE empno=?";
this.template.update(sql,"测试数据","测试数据",0);
}
@Test
public void doDelete(){
String sql = "DELETE FROM employee WHERE empno=?";
this.template.update(sql,0);
}
}