Spring JDBC常用方法详细示例

Spring JDBC使用简单，代码简洁明了，非常适合快速开发的小型项目。下面对开发中常用的增删改查等方法逐一示例说明使用方法

1 环境准备

启动MySQL, 创建一个名为test的数据库

创建Maven项目，依赖如下：

	<dependencies>
		<dependency>
			<groupId>org.springframework</groupId>
			<artifactId>spring-core</artifactId>
			<version>4.2.5.RELEASE</version>
		</dependency>
		<dependency>
			<groupId>org.springframework</groupId>
			<artifactId>spring-context</artifactId>
			<version>4.2.5.RELEASE</version>
		</dependency>
		<dependency>
			<groupId>org.springframework</groupId>
			<artifactId>spring-jdbc</artifactId>
			<version>4.2.5.RELEASE</version>
		</dependency>
		<dependency>
			<groupId>mysql</groupId>
			<artifactId>mysql-connector-java</artifactId>
			<version>5.1.18</version>
		</dependency>
		<dependency>
			<groupId>org.apache.commons</groupId>
			<artifactId>commons-lang3</artifactId>
			<version>3.1</version>
		</dependency>
	</dependencies>

如果不使用maven可以自行添加jar包

代码目录结构

Car.java，一个普通的java bean，充当数据库表的实体对象

public class Car {

	public Car() {}
	
	public Car(int id, String name, float price) {
		super();
		this.id = id;
		this.name = name;
		this.price = price;
	}

	private int id;
	private String name;
	private float price;
	public int getId() {
		return id;
	}
	public void setId(int id) {
		this.id = id;
	}
	public String getName() {
		return name;
	}
	public void setName(String name) {
		this.name = name;
	}
	public float getPrice() {
		return price;
	}
	public void setPrice(float price) {
		this.price = price;
	}
	public String toString() {
		return ToStringBuilder.reflectionToString(this);
	}
}

CarDao.java，暂时只设置了一个JdbcTemplate属性，这是Spring JDBC的核心，取得它的实例后就可以随心所欲了

public class CarDao {

	private JdbcTemplate jdbcTemplate;
	public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
		this.jdbcTemplate = jdbcTemplate;
	}
}

appllicationContext.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:p="http://www.springframework.org/schema/p"
	xmlns:context="http://www.springframework.org/schema/context"
	xsi:schemaLocation="http://www.springframework.org/schema/beans
	http://www.springframework.org/schema/beans/spring-beans-3.0.xsd
	http://www.springframework.org/schema/context
  	http://www.springframework.org/schema/context/spring-context-3.0.xsd">

	<bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
		<property name="driverClassName" value="com.mysql.jdbc.Driver" />
		<property name="url"
			value="jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=UTF8" />
		<property name="username" value="root" />
		<property name="password" value="root123456" />
	</bean>
	<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
		<property name="dataSource" ref="dataSource"></property>
	</bean>
	<bean id="carDao" class="examples.jdbc.CarDao">
		<property name="jdbcTemplate" ref="jdbcTemplate" />
	</bean>
</beans>

下面是测试代码，运行后如果没有报错，说明环境搭建成功

	public static void main(String[] args) {
		ApplicationContext context = 
				new ClassPathXmlApplicationContext("applicationContext.xml");
		CarDao dao = (CarDao)context.getBean("carDao");
	}

2 示例

下面开始给CarDao逐个添加常用方法

2.1 删除表

	public void dropTable() {
		String sql = "drop table if exists t_car";
		jdbcTemplate.execute(sql);
	}

2.2 建表

	public void createTable() {
		String sql = "create table t_car(car_id int not null AUTO_INCREMENT,car_name varchar(50),car_price float,PRIMARY KEY(car_id)) default charset=utf8";
		jdbcTemplate.execute(sql);
	}

2.3 插入一条记录

	public void addCar(Car car) {
		String sql = "insert into t_car(car_name,car_price) values(?, ?)";
		jdbcTemplate.update(sql, new Object[] { car.getName(), car.getPrice() }, new int[] { Types.VARCHAR, Types.FLOAT });
	}

测试

dao.addCar(new Car(0, "buick", 150000));

修改和删除操作除了SQL不一样，其他代码和插入记录的写法相同

注意这个表使用了自增主键，有时候插入记录后我们需要获得主键用于后续操作

2.4 获取自增主键

	public int addCarAndGetKey(Car car) {
		String sql = "insert into t_car(car_name,car_price) values(?, ?)";
		KeyHolder keyHolder = new GeneratedKeyHolder();
		jdbcTemplate.update(new PreparedStatementCreator() {
			@Override
			public PreparedStatement createPreparedStatement(Connection conn)
					throws SQLException {
				PreparedStatement ps = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
				ps.setString(1, car.getName());
				ps.setFloat(2, car.getPrice());
				return ps;
			}
		}, keyHolder);
		return keyHolder.getKey().intValue();
	}

2.5 批量插入

	public void batchAddCar() {
		String sql = "insert into t_car(car_name,car_price) values(?, ?)";
		Car[] cars = new Car[] {
			new Car(1, "audi", 300000f),
			new Car(2, "benz", 310000f),
			new Car(3, "bmw", 320000f)
		};
		jdbcTemplate.batchUpdate(sql, new BatchPreparedStatementSetter() {
			@Override
			public int getBatchSize() {
				return cars.length;
			}

			@Override
			public void setValues(PreparedStatement ps, int index)
					throws SQLException {
				ps.setString(1, cars[index].getName());
				ps.setFloat(2, cars[index].getPrice());
			}
		});
	}

2.6 查询

	public Car getCarById(final int carId) {
		String sql = "select * from t_car where car_id=?";
		final Car car = new Car();
		jdbcTemplate.query(sql, new Object[]{ carId }, new int[]{ Types.INTEGER }, new RowCallbackHandler() {
			@Override
			public void processRow(ResultSet rs) throws SQLException {
				car.setId(carId);
				car.setName(rs.getString("car_name"));
				car.setPrice(rs.getFloat("car_price"));
			}
		});
		return car;
	}

2.7 匹配多条记录查询(使用RowCallbackHandler)

	public List<Car> queryForList(final int fromId, final int toId) {
		String sql = "select * from t_car where car_id between ? and ?";
		final List<Car> cars = new ArrayList<>();
		jdbcTemplate.query(sql, new Object[]{ fromId, toId }, new int[]{ Types.INTEGER, Types.INTEGER }, new RowCallbackHandler() {
			@Override
			public void processRow(ResultSet rs) throws SQLException {
				Car car = new Car();
				car.setId(rs.getInt("car_id"));
				car.setName(rs.getString("car_name"));
				car.setPrice(rs.getFloat("car_price"));
				cars.add(car);
			}
		});
		return cars;
	}

2.8 匹配多条记录查询(使用RowMapper)

	public List<Car> queryForList(final float fromPrice) {
		String sql = "select * from t_car where car_price>?";
		return jdbcTemplate.query(sql, new Object[]{ fromPrice }, new int[]{ Types.FLOAT }, new RowMapper<Car>() {

			@Override
			public Car mapRow(ResultSet rs, int index) throws SQLException {
				Car car = new Car();
				car.setId(rs.getInt("car_id"));
				car.setName(rs.getString("car_name"));
				car.setPrice(rs.getFloat("car_price"));
				return car;
			}
		});
	}

注意使用RowMapper无法控制返回的List大小，查询结果集会全部读入内存，如果结果集比较大会占用较多内存。

2.9 直接返回SqlRowSet对象

	public SqlRowSet queryForRowSet(final float fromPrice) {
		String sql = "select * from t_car where car_price>?";
		return jdbcTemplate.queryForRowSet(sql, new Object[]{ fromPrice }, new int[]{ Types.FLOAT });
	}

2.10 拼接SQL

开发中有时候会用到一个方法匹配多种查询条件的方式，可以利用拼接查询SQL来实现

	public List<Car> findCars(int carId, String carName,
			float fromPrice, float toPrice) {
		List<Object> paramList = new ArrayList<>();
		List<Integer> typesList = new ArrayList<>();
		StringBuilder sql = new StringBuilder().append("SELECT * FROM t_car WHERE 1=1");
		if(carId > 0) {
			sql.append(" AND car_id=?");
			paramList.add(carId);
			typesList.add(Types.INTEGER);
		}
		if(StringUtils.isNotBlank(carName)) {
			sql.append(" AND locate(?,car_name)>0");
			paramList.add(carName);
			typesList.add(Types.VARCHAR);
		}
		if(fromPrice > -1) {
			sql.append(" AND car_price>=?");
			paramList.add(fromPrice);
			typesList.add(Types.FLOAT);
		}
		if(toPrice > -1) {
			sql.append(" AND car_price<=?");
			paramList.add(toPrice);
			typesList.add(Types.FLOAT);
		}
		int[] types = ArrayUtils.toPrimitive(typesList.toArray(new Integer[0]));
		final List<Car> cars = new ArrayList<>();
		jdbcTemplate.query(sql.toString(), paramList.toArray(), types, new RowCallbackHandler() {

			@Override
			public void processRow(ResultSet rs) throws SQLException {
				Car car = new Car();
				car.setId(rs.getInt("car_id"));
				car.setName(rs.getString("car_name"));
				car.setPrice(rs.getFloat("car_price"));
				cars.add(car);
			}
		});
		return cars;
	}

2.11 调用存储过程

创建一个测试存储过程

DELIMITER $$
USE `test`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `proc_FindCar`(
$fromPrice float,
$toPrice float
)
begin
	select * from t_car where car_price between $fromPrice and $toPrice;
end;$$
DELIMITER ;

调用示例

	public List<Car> queryByProcedure(final int fromId, final int toId) {
		String sql = "{call proc_FindCar(?,?)}";
		return jdbcTemplate.execute(sql, new CallableStatementCallback<List<Car>>() {

			@Override
			public List<Car> doInCallableStatement(CallableStatement cs)
					throws SQLException, DataAccessException {
				cs.setFloat(1, fromId);
				cs.setFloat(2, toId);
				List<Car> cars = new ArrayList<>();
				ResultSet rs = cs.executeQuery();
				while(rs.next()) {
					Car car = new Car();
					car.setId(rs.getInt("car_id"));
					car.setName(rs.getString("car_name"));
					car.setPrice(rs.getFloat("car_price"));
					cars.add(car);
				}
				return cars;
			}
			
		});
	}