摘要：批量操作数据批量操作数据一共有两种方法批量添加方法一男男方法二男男批量修改方法一男男方法二男男批量删除同上
环境
版本信息
spring的版本为4.1.4(spring3我也用过，就配置信息略有不同，其用法还是一样的)
配置信息
需要在applicationContext.xml中配置以下信息
如果已经在文件中配置了jdbctemplate的话还可以使用以下方法配置
当然也可以使用最原始的方法(该方法也需要配置jdbctemplate)
NamedParameterJdbcTemplate namedParameterJdbcTemplate = new NamedParameterJdbcTemplate(jdbcTemplate);
持久化类
本文章中的所有案例都是根据以下类来试验的:
public class User {
private int id;
private String userName;
private String sex;
private String password;
private String address;
//省略 set/get
}
简单操作数据
查询
查询一条数据
传入参数是基本数据类型的map时
public User selectUserById(String id) {
String sql = "SELECT id,username,sex,password,address FROM user WHERE id = :id";
Map paramMap = new HashMap();
paramMap.put("id", id);
RowMapper rm = BeanPropertyRowMapper.newInstance(User.class);
User user = namedParameterJdbcTemplate.queryForObject(sql, paramMap, rm);
return user;
}
传入的参数是对象时
public User queryByUser(User user) {
String sql = "SELECT id,username,sex,password,address FROM user WHERE id = :id";
SqlParameterSource ps = new BeanPropertySqlParameterSource(user);
RowMapper rm = BeanPropertyRowMapper.newInstance(User.class);
return namedParameterJdbcTemplate.queryForObject(sql, ps, rm);
}
查询多条数据
普通查询
public List selectUser() {
String sql = "SELECT id,username,sex,password,address FROM user";
Map paramMap = new HashMap();
RowMapper rm = BeanPropertyRowMapper.newInstance(User.class);
List userList = namedParameterJdbcTemplate.query(sql, rm);
return userList;
}
模糊查询
public List selectUserLikeByName(String name) {
//"%"空格:userName空格"%" 一定要有空格，不然会报错
String sql = "SELECT id,username,sex,password,address FROM user WHERE username LIKE "%" :userName "%"";
Map paramMap = new HashMap();
paramMap.put("userName", name);
RowMapper rm = BeanPropertyRowMapper.newInstance(User.class);
List users = namedParameterJdbcTemplate.query(sql, paramMap, rm);
return users;
}
添加数据
public void insertUser(User user) {
String sql = "INSERT INTO user (username, sex, password, address) VALUES (:username,:sex,:password,:address)";
Map paramMap = new HashMap();
paramMap.put("username", user.getUserName());
paramMap.put("sex", user.getSex());
paramMap.put("password", user.getPassword());
paramMap.put("address", user.getAddress());
namedParameterJdbcTemplate.update(sql, paramMap);
}
修改数据
public void updateUser(User user) {
String sql = "UPDATE user SET username = :userName,sex = :sex,password=:password,address=:address WHERE id = :id;";
SqlParameterSource ps = new BeanPropertySqlParameterSource(user);
namedParameterJdbcTemplate.update(sql, ps);
}
可以与添加数据再进行对比，这样就能发现当占位符比较多的情况下传入对象时多么的方便
删除数据
public void deleteUser(String id) {
String sql = "DELETE FROM user WHERE id = :id";
Map paramMap = new HashMap();
paramMap.put("id", id);
namedParameterJdbcTemplate.update(sql, paramMap);
}
看到此处可以发现添加，修改，删除数据namedParameterJdbcTemplate提供的方法都是一样的，都是update方法。
批量操作数据
批量操作数据一共有两种方法
批量添加
方法一
public void batchInsert() {
User chen = new User("chen", "男", "123456789", "fuzhou");
User alex = new User("alex", "男", "123456", "fuzhou");
List userList = new ArrayList<>();
userList.add(chen);
userList.add(alex);
List> batchValues = new ArrayList<>(userList.size());
for (User user : userList) {
batchValues.add(
new MapSqlParameterSource("username", user.getUserName())
.addValue("sex", user.getSex())
.addValue("password", user.getPassword())
.addValue("address", user.getAddress())
.getValues());
}
String sql = "INSERT INTO user (username, sex, password, address) VALUES (:username,:sex,:password,:address)";
int[] updateCounts = namedParameterJdbcTemplate.batchUpdate(sql, batchValues.toArray(new Map[userList.size()]));
}
方法二
public void batchInsertUser() {
User chen = new User("chen", "男", "123456789", "fuzhou");
User alex = new User("alex", "男", "123456", "fuzhou");
List userList = new ArrayList<>();
userList.add(chen);
userList.add(alex);
String sql = "INSERT INTO user (username, sex, password, address) VALUES (:username,:sex,:password,:address)";
SqlParameterSource[] batch = SqlParameterSourceUtils.createBatch(userList.toArray());
int[] updateCounts = namedParameterJdbcTemplate.batchUpdate(sql, batch);
}
批量修改
方法一
public void batchUpdate() {
User chen = new User(12,"chen", "男", "111111111", "fuzhou");
User alex = new User(13,"alex", "男", "111111111", "fuzhou");
List userList = new ArrayList<>();
userList.add(chen);
userList.add(alex);
List> batchValues = new ArrayList<>(userList.size());
for (User user : userList) {
batchValues.add(
new MapSqlParameterSource("username", user.getUserName())
.addValue("id",user.getId())
.addValue("sex", user.getSex())
.addValue("password", user.getPassword())
.addValue("address", user.getAddress())
.getValues());
}
String sql = "UPDATE user SET username = :username,sex = :sex,password=:password,address=:address WHERE id = :id;";
int[] updateCounts = namedParameterJdbcTemplate.batchUpdate(sql, batchValues.toArray(new Map[userList.size()]));
}
方法二
public void batchUpdateUser() {
User chen = new User(14,"chen", "男", "111111111", "fuzhou");
User alex = new User(15,"alex", "男", "111111111", "fuzhou");
List userList = new ArrayList<>();
userList.add(chen);
userList.add(alex);
String sql = "UPDATE user SET username = :username,sex = :sex,password=:password,address=:address WHERE id = :id;";
SqlParameterSource[] batch = SqlParameterSourceUtils.createBatch(userList.toArray());
int[] updateCounts = namedParameterJdbcTemplate.batchUpdate(sql, batch);
}
批量删除
同上
