Spring_对JDBC的支持
使用JdbcTemplate更新数据库
导入jar包
创建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:context="http://www.springframework.org/schema/context" 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-4.0.xsd"> <!-- 导入资源文件 --> <context:property-placeholder location="classpath:db.properties"></context:property-placeholder> <!-- 配置C3p0数据源 --> <bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource"> <property name="user" value="${jdbc.user}"></property> <property name="password" value="${jdbc.password}"></property> <property name="jdbcUrl" value="${jdbc.jdbcUrl}"></property> <property name="driverClass" value="${jdbc.driverClass}"></property> <property name="initialPoolSize" value="${jdbc.initPoolSize}"></property> <property name="maxPoolSize" value="${jdbc.maxPoolSize}"></property> </bean> <!-- 配置Spring的jdbcTemplate --> <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate"> <property name="dataSource" ref="dataSource"></property> </bean> </beans>
创建db.properties文件
jdbc.user=root jdbc.password=password jdbc.driverClass=com.mysql.jdbc.Driver jdbc.jdbcUrl=jdbc:mysql://localhost:3306/spring4?serverTimezone=GMT%2B8 jdbc.initPoolSize=5 jdbc.maxPoolSize=10
创建测试类
package com.tanlei.pojo.jdbc; import static org.junit.jupiter.api.Assertions.*; import java.sql.SQLException; import java.util.ArrayList; import java.util.List; import javax.sql.DataSource; import org.junit.jupiter.api.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 junit.framework.TestListener; public class JDBCTest {
private ApplicationContext ctx=null; private JdbcTemplate jdbcTemplate; { ctx=new ClassPathXmlApplicationContext("applicationcontext.xml"); jdbcTemplate=(JdbcTemplate) ctx.getBean("jdbcTemplate"); }
/** * *tanlei *2018年12月26日 *执行INSERT UPDATE DELETE */ //单独修改一条语句 //update @Test public void testUpdate() { String sql="update employee set EMP_NAME=? where EMP_ID=?"; jdbcTemplate.update(sql,"tanlei",769); } //批量增加数据,修改,删除 //batchUpdate @Test public void testAlladd() { String sql="insert into DEPARTMENT(DEPT_ID,DEPT_NAME,DEPT_NO) values (?,?,?)"; List<Object[]> batchArgs=new ArrayList<>(); batchArgs.add(new Object[] {1,"AA","D1"}); batchArgs.add(new Object[] {2,"BB","D2"}); jdbcTemplate.batchUpdate(sql, batchArgs); } //从数据库获取一条记录,实际上得到一个对象 //queryForObject 使用sql中的列的 别名完成列名和属性名的映射 //jdbcTemplate是一个jdbc的小工具,不是orm框架 @Test public void testemployee() { String sql="select EMP_ID as id,EMP_NAME as empname,EMP_NO as empno,JOB as job from employee where EMP_ID=?"; RowMapper<Employee> rowMapper=new BeanPropertyRowMapper<>(Employee.class); Employee employee=jdbcTemplate.queryForObject(sql, rowMapper,769); System.out.println(employee); } //查到实体类的集合 @Test public void TestList() { String sql="select EMP_ID as id,EMP_NAME as empname,EMP_NO as empno,JOB as job from employee where EMP_ID>?"; RowMapper<Employee> rowMapper=new BeanPropertyRowMapper<>(Employee.class); List<Employee> employees=jdbcTemplate.query(sql, rowMapper,769); System.out.println(employees); } //获取某一个属性值,或做统计查询 @Test public void testListForObject() { String sql="select count(EMP_ID) as id from employee "; long count=jdbcTemplate.queryForObject(sql, Long.class); System.out.println(count); }
@Test public void testDataSource() throws SQLException { DataSource dataSource=ctx.getBean(DataSource.class); System.out.println(dataSource.getConnection()); }
}
1.查询单行数据
1.1 自定义RowMapper
public class RowMapper implements org.springframework.jdbc.core.RowMapper {
@Override
public Object mapRow(ResultSet resultSet, int i) throws SQLException {
Customer customer=new Customer();
customer.setCustid(resultSet.getInt("cus_id"));
customer.setName(resultSet.getString("name"));
customer.setAge(resultSet.getInt("age"));
return null;
}
}
它传递给 queryForObject()方法，返回的结果将调用自定义 mapRow()方法的值匹配到属性
public Customer findByCustomerId(int custId){
String sql = "SELECT * FROM CUSTOMER WHERE CUST_ID = ?";
Customer customer = (Customer)getJdbcTemplate().queryForObject(
sql, new Object[] { custId }, new CustomerRowMapper());
return customer;
}
1.2 BeanPropertyRowMapper
在Spring2.5中，带有一个方便 RowMapper 实现所谓“BeanPropertyRowMapper”，它可以通过匹配行的名字的列值映射到一个属性。只要确保这两个属性和列具有相同的名称，如属性“CUSTID'将匹配到列名为：”CUSTID'或下划线“CUST_ID”。
public Customer findByCustomerId2(int custId){
String sql = "SELECT * FROM CUSTOMER WHERE CUST_ID = ?";
Customer customer = (Customer)getJdbcTemplate().queryForObject(
sql, new Object[] { custId },
new BeanPropertyRowMapper(Customer.class));
return customer;
}
2，查询多行
现在，查询或从数据库中提取多行，并且将它转换成一个列表。
2.1手动映射它
返回多行，RowMapper 不支持 queryForList()方法，需要手动映射它。
public List<Customer> findAll(){
String sql = "SELECT * FROM CUSTOMER";
List<Customer> customers = new ArrayList<Customer>();
List<Map> rows = getJdbcTemplate().queryForList(sql);
for (Map row : rows) {
Customer customer = new Customer();
customer.setCustId((Long)(row.get("CUST_ID")));
customer.setName((String)row.get("NAME"));
customer.setAge((Integer)row.get("AGE"));
customers.add(customer);
}
return customers;
}
2.2 BeanPropertyRowMapper
最简单的解决方案是使用 BeanPropertyRowMapper 类。
public List<Customer> findAll(){
String sql = "SELECT * FROM CUSTOMER";
List<Customer> customers = getJdbcTemplate().query(sql,
new BeanPropertyRowMapper(Customer.class));
return customers;
}
继承JdbcDaoSupport(不推荐使用,而推进直接使用
jdbcTemplate