packagecom.aff.spring.jdbc;importjava.sql.SQLException;importjava.util.ArrayList;importjava.util.HashMap;importjava.util.List;importjavax.sql.DataSource;importorg.junit.Test;importorg.springframework.context.ApplicationContext;importorg.springframework.context.support.ClassPathXmlApplicationContext;importorg.springframework.jdbc.core.BeanPropertyRowMapper;importorg.springframework.jdbc.core.JdbcTemplate;importorg.springframework.jdbc.core.RowMapper;importorg.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;importorg.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;importorg.springframework.jdbc.core.namedparam.SqlParameterSource;public classJDBCTest {private ApplicationContext ctx = null;privateJdbcTemplate jdbcTemplate;privateEmployeeDAO employeeDAO;privateDepartmentDAO departmentDAO;privateNamedParameterJdbcTemplate namedParameterJdbcTemplate;
{
ctx= new ClassPathXmlApplicationContext("applicationContext.xml");
jdbcTemplate= (JdbcTemplate) ctx.getBean("jdbcTemplate");
employeeDAO= ctx.getBean(EmployeeDAO.class);
departmentDAO= ctx.getBean(DepartmentDAO.class);
namedParameterJdbcTemplate=ctx.getBean(NamedParameterJdbcTemplate.class);
}/**
* 使用具名参数时 , 可以使用 NamedParameterJdbcTemplate.update(String sql, SqlParameterSource paramSource)
* 进行操作,
* 1.SQL参数名和类的属性名一致
* 2.使用 SqlParameterSource 的BeanPropertySqlParameterSource 实现类作为参数
*/@Testpublic voidtestNamedParameterJdbcTemplate2(){
String sql="insert into employees(last_name,email,dept_id) values(:lastName,:email,:deptId)";
Employee employee= newEmployee();
employee.setLastName("hxl");
employee.setEmail("hxl@qq.com");
employee.setDeptId(4);
SqlParameterSource paramSource= newBeanPropertySqlParameterSource(employee);;
namedParameterJdbcTemplate.update(sql, paramSource);
}/*** 可以为参数气起名字。
* 好处: 若有多个参数, 则不用去对应位置, 直接对应参数名, 便于维护
* 缺点: 很麻烦*/@Testpublic voidtestNamedParameterJdbcTemplate(){
String sql="insert into employees(last_name,email,dept_id) values(:ln,:email,:deptid)";
HashMap paramMap = new HashMap<>();
paramMap.put("ln", "hh");
paramMap.put("email", "hh@qq.com");
paramMap.put("deptid", 3);
namedParameterJdbcTemplate.update(sql, paramMap);
}
@Testpublic voidtestDepartmentDao(){
System.out.println(departmentDAO.get(1));//Department [id=1, name=财务部]
}
@Testpublic voidtestEmployeeDAO(){
System.out.println(employeeDAO.get(7));//Employee [id=7, lastName=jack, email=222@QQ.COM, department=null]
}/**
* 获取单个列的值, 或做统计查询
* 使用JdbcTemplate.queryForObject(String sql, Class requiredType)
*/@Testpublic voidtestQueryForObject2(){
String sql="select count(id) from employees";
Long count= jdbcTemplate.queryForObject(sql, Long.class);
System.out.println(count);//27
}/**
* 查到实体类的集合
* 注意 调用的不是queryForList 方法
*/@Testpublic voidtestQueryForList(){
String sql= "select id, last_name lastName ,email from employees where id";
RowMapper rowMapper = new BeanPropertyRowMapper<>(Employee.class);
List employees = jdbcTemplate.query(sql, rowMapper,3);
System.out.println(employees);//[Employee [id=1, lastName=Tom, email=tom@163.com, department=null],//Employee [id=2, lastName=Jerry, email=jerry@126.com, department=null]]
}/**
* 从数据库中获取一条记录, 实际得到对应的对象
* 注意不是调用JdbcTemplate.queryForObject(String sql, Class requiredType, Object... args)
* 而是调用.JdbcTemplate.queryForObject(String sql, RowMapper rowMapper, Object... args)
* 1.其中的 RowMapper 指定如何去映射结果集的行,常用的实现类为BeanPropertyRowMapper
* 2.使用SQL 中列的别名 完成类名和类的属性名的映射, 如:last_name lastName
* 3.不支持级联属性,JdbcTemplate 到底是一个JDBC 的小工具, 而不是ORM框架
*/@Testpublic voidtestQueryForObject(){
String sql= "select id, last_name lastName ,email ,dept_id as\"department.id\" from employees where id=?";
RowMapper rowMapper = new BeanPropertyRowMapper<>(Employee.class);
Employee employee= jdbcTemplate.queryForObject(sql, rowMapper,1);
System.out.println(employee);//Employee [id=1, lastName=Tom, email=tom@163.com, department=null]
}/**
* 执行批量的更新: 批量的 insert update delete
* 最后一个一个参数 是Object[] 的list 类型 ,因为 修改一条记录需要一个Object数组,多条记录,就需要多个Objec数组,构成的list 集合
*/@Testpublic voidtestBatchUpdate() {
String sql="insert into employees(last_name, email, dept_id) values(?,?,?)";
List batchArgs = new ArrayList();
batchArgs.add(new Object[]{"aa","aa@qq.com",2});
batchArgs.add(new Object[]{"bb","bb@qq.com",3});
batchArgs.add(new Object[]{"cc","cc@qq.com",3});
batchArgs.add(new Object[]{"dd","dd@qq.com",3});//一条记录三个字段构成一个Object数组,多条记录构成Object 数组的集合
jdbcTemplate.batchUpdate(sql, batchArgs);
}/*** 执行 insert update delete*/@Testpublic voidtestUpdate() {
String sql= "update employees set last_name = ? where id=?";
jdbcTemplate.update(sql,"jack", 7);
}//测试连接
@Testpublic void test() throwsSQLException {
DataSource dataSource= (DataSource) ctx.getBean("dataSource");
System.out.println(dataSource.getConnection());
}
}