java 代码
package com.spring.jdbc;
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 javax.sql.DataSource;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
public class JDBCTest {
private ApplicationContext app = null;
private JdbcTemplate jdbcTemplate;
private EmployeeDao employeeDao;
private DepartmentDao departmentDao;
{
app = new ClassPathXmlApplicationContext("applicationContext.xml");
jdbcTemplate = (JdbcTemplate) app.getBean("jdbcTemplate");
employeeDao = (EmployeeDao) app.getBean("employeeDao");
departmentDao = (DepartmentDao) app.getBean("departmentDao");
}
@Test
public void testDepartmentDao() {
System.out.println(departmentDao.get(2));
}
@Test
public void testEmployeeDao() {
System.out.println(employeeDao.get(1));
}
/**
* 获取单个列的值，或做统计查询
* 使用 queryForObject(String sql, Class<Long> requiredType) 方法
*/
@Test
public void testQueryForObject2() {
String sql = "SELECT count(id) FROM employees";
long count = jdbcTemplate.queryForObject(sql, Long.class);
System.out.println(count);
}
/**
* 查到实体类的集合
* 注意调用的不是 queryForList 方法
*/
@Test
public void testQueryForList() {
String sql = "SELECT id, last_name lastName, email FROM employees WHERE id > ?";
RowMapper<Employee> rowMapper = new BeanPropertyRowMapper<>(Employee.class);
List<Employee> employees = jdbcTemplate.query(sql, rowMapper, 5);
System.out.println(employees);
}
/**
* 从数据库中获取一条记录，实际得到对应的一个对象
* 注意不是调用 queryForObject(String sql, Class<Employee> requiredType, Object... args) 方法
* 而需要调用 queryForObject(String sql, RowMapper<Employee> rowMapper, Object... args)
* 1.其中的 RowMapper 指定如何去映射结果集的行，常用的实现类为 BeanPropertyRowMapper
* 2.使用 SQL 中的别名完成列名和类的属性名的映射，例如 last_name laseName
* 3.不支持级联属性，JdbcTemplate 到底是一个 JDBC 的小工具，而不是 ORM 框架
*/
@Test
public void testQueryForObject() {
String sql = "SELECT id, last_name lastName, email FROM employees WHERE id = ?";
RowMapper<Employee> rowMapper = new BeanPropertyRowMapper<>(Employee.class);
Employee employee = jdbcTemplate.queryForObject(sql, rowMapper, 1);
System.out.println(employee);
}
/**
* 执行批量更新：批量 INSERT，UPDATE，DELETE
* 最后一个参数是 Object[] 的 List 类型：因为修改一条记录需要一个 Object 的数组，那么多条就需要多个 Object 的数组
*/
@Test
public void testBatchUpdate() {
String sql = "INSERT INTO employees(last_name, email, dept_id) VALUES(?,?,?)";
List<Object[]> batchArgs = new ArrayList<Object[]>();
batchArgs.add(new Object[]{"AA", "aa@123.com", 1});
batchArgs.add(new Object[]{"BB", "bb@123.com", 2});
batchArgs.add(new Object[]{"CC", "cc@123.com", 3});
batchArgs.add(new Object[]{"DD", "cc@123.com", 3});
batchArgs.add(new Object[]{"EE", "ee@123.com", 2});
jdbcTemplate.batchUpdate(sql, batchArgs);
}
/**
* 执行 INSERT，UPDATE，DELETE
*/
@Test
public void testUpdate() {
String sql = "UPDATE employees SET last_name = ? WHERE id = ?";
jdbcTemplate.update(sql, "Jack", 5);
}
@Test
public void testDataSource() throws SQLException {
DataSource dataSource = (DataSource) app.getBean("dataSource");
System.out.println(dataSource.getConnection());
}
}
package com.spring.jdbc;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.support.JdbcDaoSupport;
import org.springframework.stereotype.Repository;
import javax.sql.DataSource;
/**
* 不推荐使用 JdbcDaoSupport，而推荐使用 JdbcTemplate 作为 Dao 类的成员变量
*/
@Repository
public class DepartmentDao extends JdbcDaoSupport {
@Autowired
public void setDataSource2(DataSource dataSource) {
setDataSource(dataSource);
}
public Department get(Integer id) {
String sql = "SELECT id, dept_name name FROM departments WHERE id = ?";
RowMapper<Department> rowMapper = new BeanPropertyRowMapper<>(Department.class);
Department department = getJdbcTemplate().queryForObject(sql, rowMapper, id);
return department;
}
}
package com.spring.jdbc;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;
@Repository
public class EmployeeDao {
@Autowired
private JdbcTemplate jdbcTemplate;
public Employee get(Integer id) {
String sql = "SELECT id, last_name lastName, email FROM employees WHERE id = ?";
RowMapper<Employee> rowMapper = new BeanPropertyRowMapper<>(Employee.class);
Employee employee = jdbcTemplate.queryForObject(sql, rowMapper, id);
return employee;
}
}
在 JDBC 模板中使用具名参数
private NamedParameterJdbcTemplate namedParameterJdbcTemplate = (NamedParameterJdbcTemplate) app.getBean("namedParameterJdbcTemplate");
/**
* 可以为参数起名字，
* 1.好处：若有多个参数则不用再去对应位置，直接对应参数名，便于维护
* 2.缺点：较为麻烦
*/
@Test
public void testNamedParameterJdbcTemplate() {
String sql = "INSERT INTO employees(last_name, email, dept_id) VALUES(:ln, :email, :deptid)";
Map<String, Object> paramMap = new HashMap<>();
paramMap.put("ln", "FF");
paramMap.put("email", "ff@123.com");
paramMap.put("deptid", 2);
namedParameterJdbcTemplate.update(sql, paramMap);
}
/**
* 使用具名参数时，可以使用 update(String sql, SqlParameterSource paramSource) 方法进行更新操作
* 1. sql 语句中的参数名和类的属性一致
* 2.使用 SqlParameterSource 的 BeanPropertySqlParameterSource 实现类作为参数
*/
@Test
public void testNamedParameterJdbcTemplate2() {
String sql = "INSERT INTO employees(last_name, email, dept_id) VALUES(:lastName, :email, :deptId)";
Employee employee = new Employee();
employee.setLastName("XZJ");
employee.setEmail("xzj@95.com");
employee.setDeptId(1);
SqlParameterSource parameterSource = new BeanPropertySqlParameterSource(employee);
namedParameterJdbcTemplate.update(sql, parameterSource);
}
