本文接上一篇继续
研究JDBC模板。
之前说的都是插入操作,在我们使用自增
主键的时候有时我们想立刻获得数据库为我们生成的主键值,那么jdbcTemplate是支持这个操作的,只是写起来麻烦一些,可以这么来做:
JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
KeyHolder keyHolder = new GeneratedKeyHolder();
jdbcTemplate.update(new PreparedStatementCreator() {
public PreparedStatement createPreparedStatement(Connection conn)
throws SQLException {
String sql = "insert into vehicle(PLATE,CHASSIS,COLOR,WHEEL,SEAT) values(?,?,?,?,?)";
PreparedStatement pstat = conn.prepareStatement(sql);
pstat.setString(1, vehicle.getPlate());
pstat.setString(2, vehicle.getChassis());
pstat.setString(3, vehicle.getColor());
pstat.setInt(4, vehicle.getWheel());
pstat.setInt(5, vehicle.getSeat());
return pstat;
}
}, keyHolder);
System.out.println("PK: " + keyHolder.getKey().intValue());
不过HSQL不支持这种写法,那么只能用原始的方法了。调用它特有的获取最后主键值的函数来查找了:
JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
String sql = "insert into vehicle(PLATE,CHASSIS,COLOR,WHEEL,SEAT) values(?,?,?,?,?)";
jdbcTemplate.update(sql, vehicle.getPlate(), vehicle.getChassis(),
vehicle.getColor(), vehicle.getWheel(), vehicle.getSeat());
int id = jdbcTemplate.queryForInt("CALL IDENTITY()");
System.out.println("PK: " + id);
这样就会获取到生成主键的值。插入操作的最后一部分是批量插入,批量插入属于和数据库交互比较密集的操作,容易出现性能问题,而jdbc模板为我们提供了简便的方法来执行批量操作,我们来看:
public void insertBatch(final List<Vehicle> vehicles) {
String sql = "insert into vehicle(PLATE,CHASSIS,COLOR,WHEEL,SEAT) values(?,?,?,?,?)";
JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
jdbcTemplate.batchUpdate(sql, new BatchPreparedStatementSetter() {
public void setValues(PreparedStatement pstat, int i)
throws SQLException {
Vehicle vehicle = vehicles.get(i);
pstat.setString(1, vehicle.getPlate());
pstat.setString(2, vehicle.getChassis());
pstat.setString(3, vehicle.getColor());
pstat.setInt(4, vehicle.getWheel());
pstat.setInt(5, vehicle.getSeat());
}
public int getBatchSize() {
return vehicles.size();
}
});
}
测试时,也很简单:
public static void main(String[] args) {
ApplicationContext ctx = new ClassPathXmlApplicationContext( "classpath:org/ourpioneer/vehicle/spring/applicationContext.xml");
VehicleDAO vehicleDAO = (VehicleDAO) ctx.getBean("vehicleDAO");
Vehicle vehicle1 = new Vehicle("辽B-000000", "1A00000001", "RED", 4, 4);
Vehicle vehicle2 = new Vehicle("辽B-000001", "1A00000002", "RED", 4, 4);
vehicleDAO.insertBatch(Arrays
.asList(new Vehicle[] { vehicle1, vehicle2 }));
}
到此,JDBC模板的更新操作就介绍完了,以插入操作为例来说明,逐步深入,使用JDBC模板的各种实现方式,其中以可变参数形式传递参数最为简单。下面来看看数据查询,这是概念比较多的一块儿。先从通用的方法来开始看:
public Vehicle findById(int id) {
String sql = "select * from vehicle where ID=?";
JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
final Vehicle vehicle = new Vehicle();
jdbcTemplate.query(sql, new Object[] { id }, new RowCallbackHandler() {
public void processRow(ResultSet rs) throws SQLException {
vehicle.setId(rs.getInt("ID"));
vehicle.setPlate(rs.getString("PLATE"));
vehicle.setChassis(rs.getString("CHASSIS"));
vehicle.setColor(rs.getString("COLOR"));
vehicle.setWheel(rs.getInt("WHEEL"));
vehicle.setSeat(rs.getInt("SEAT"));
}
});
return vehicle;
}
用的还是内部类,用返回的结果集对象来填充bean,就得到了结果。第二种方法是使用RowMapper来映射字段到对象。这个也很简单,单独写一个RowMapper的实现:
package org.ourpioneer.vehicle.jt;
import java.sql.ResultSet;
import java.sql.SQLException;
import org.ourpioneer.vehicle.bean.Vehicle;
import org.springframework.jdbc.core.RowMapper;
public class VehicleRowMapper implements RowMapper<Vehicle> {
public Vehicle mapRow(ResultSet rs, int rowNum) throws SQLException {
Vehicle vehicle = new Vehicle();
vehicle.setId(rs.getInt("ID"));
vehicle.setPlate(rs.getString("PLATE"));
vehicle.setChassis(rs.getString("CHASSIS"));
vehicle.setColor(rs.getString("COLOR"));
vehicle.setWheel(rs.getInt("WHEEL"));
vehicle.setSeat(rs.getInt("SEAT"));
return vehicle;
}
}
那么在调用时,就可以使用jdbcTemplate的queryForObject方法了。比如:
public Vehicle findById(int id) {
String sql = "select * from vehicle where ID=?";
JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
Vehicle vehicle = (Vehicle) jdbcTemplate.queryForObject(sql,
new Object[] { id }, new VehicleRowMapper());
return vehicle;
}
第
三种方法可以使用BeanPropertyRowMapper,这样就会自动为我们映射了,写起来更加简便。
public Vehicle findById(int id) {
String sql = "select * from vehicle where ID=?";
JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
BeanPropertyRowMapper<Vehicle> vehicleRowMapper=BeanPropertyRowMapper.newInstance(Vehicle.class);
Vehicle vehicle=jdbcTemplate.queryForObject(sql, vehicleRowMapper, id);
return vehicle;
}
到此都是单行数据的查询,都是很简单的操作,下面我们来看使用JDBC模板查询多行数据的方法。首先在DAO中添加一个findAll方法,并实现该方法,如下:
public List<Vehicle> findAll() {
String sql = "select * from vehicle";
JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
List<Vehicle> vehicles = new ArrayList<Vehicle>();
List<Map<String, Object>> rows = jdbcTemplate.queryForList(sql);
for (Map<String, Object> row : rows) {
Vehicle vehicle = new Vehicle();
vehicle.setId((Integer) row.get("ID"));
vehicle.setPlate((String) row.get("PLATE"));
vehicle.setChassis((String) row.get("CHASSIS"));
vehicle.setColor((String) row.get("COLOR"));
vehicle.setWheel((Integer) row.get("WHEEL"));
vehicle.setSeat((Integer) row.get("SEAT"));
vehicles.add(vehicle);
}
return vehicles;
}
启动HSQL服务器,就可以直接在主函数中运行该方法,就能得到所有的查询结果了。
public static void main(String[] args) {
ApplicationContext ctx = new ClassPathXmlApplicationContext(
"classpath:org/ourpioneer/vehicle/spring/applicationContext.xml");
VehicleDAO vehicleDAO = (VehicleDAO) ctx.getBean("vehicleDAO");
List<Vehicle> vehicles = vehicleDAO.findAll();
for (Vehicle vehicle : vehicles) {
System.out.println(vehicle);
}
}
若使用RowMapper来自动映射,那么代码会大大简化,比如:
public List<Vehicle> findAll() {
String sql = "select * from vehicle";
JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
RowMapper<Vehicle> rows=BeanPropertyRowMapper.newInstance(Vehicle.class);
List<Vehicle> vehicles=jdbcTemplate.query(sql, rows);
return vehicles;
}
使用JDBC模板来查询多条记录也是这么简单,下面来看看如何使用JDBC模板来查询单个值,这也很简单,因为JDBC模板为我们提供了大量简化的API。比如我们只获取车辆的底盘号信息,要计算系统内车辆的数量,那么我们需要再为DAO定义两个方法:String getChassis(int id)和int countVehicle(),写出这两个方法的具体实现:
public String getChassis(int id) {
String sql = "select COLOR from vehicle where ID=?";
JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
String color = (String) jdbcTemplate.queryForObject(sql,
new Object[] { id }, String.class);
return color;
}
public int countVehicle() {
String sql = "select count(*) from vehicle";
JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
int count = jdbcTemplate.queryForInt(sql);
return count;
}
queryForObject()方法有很多
重载的方法,这里我们使用的是传递sql语句,参数和返回值的类型,这样JDBC模板处理后的结果就是String类型的了,我们就可以直接获取到值了,这种
查询方式适用于单值查询的情形,使用非常简单。对于集合函数,JDBC模板更加简化,为我们提供了queryForInt()方法和queryForLong()方法,那么我们直接使用该方法就可以获取到集合函数计算的结果了,当然,如果可以确定返回值的类型为Int和Long时,也可直接使用它们,而对于其它数据类型则需要使用queryForObject()方法了。
测试方法也很简单:
public static void main(String[] args) {
ApplicationContext ctx = new ClassPathXmlApplicationContext(
"classpath:org/ourpioneer/vehicle/spring/applicationContext.xml");
VehicleDAO vehicleDAO = (VehicleDAO) ctx.getBean("vehicleDAO");
int count = vehicleDAO.countVehicle();
System.out.println("Vehicle Count: " + count);
String chassis = vehicleDAO.getChassis(1);
System.out.println("Chassis For No.1: " + chassis);
}
得到如下执行结果:
下一篇将继续探讨Spring中的数据库访问,对JDBC模板进行更深一步的讨论。(未完待续)