摘要：批量操作數(shù)據(jù)批量操作數(shù)據(jù)一共有兩種方法批量添加方法一男男方法二男男批量修改方法一男男方法二男男批量刪除同上
環(huán)境
版本信息
spring的版本為4.1.4(spring3我也用過，就配置信息略有不同，其用法還是一樣的)
配置信息
需要在applicationContext.xml中配置以下信息
如果已經(jīng)在文件中配置了jdbctemplate的話還可以使用以下方法配置
當(dāng)然也可以使用最原始的方法(該方法也需要配置jdbctemplate)
NamedParameterJdbcTemplate namedParameterJdbcTemplate = new NamedParameterJdbcTemplate(jdbcTemplate);
持久化類
本文章中的所有案例都是根據(jù)以下類來試驗的:
public class User {
private int id;
private String userName;
private String sex;
private String password;
private String address;
//省略 set/get
}
簡單操作數(shù)據(jù)
查詢
查詢一條數(shù)據(jù)
傳入?yún)?shù)是基本數(shù)據(jù)類型的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;
}
傳入的參數(shù)是對象時
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);
}
查詢多條數(shù)據(jù)
普通查詢
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;
}
添加數(shù)據(jù)
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);
}
修改數(shù)據(jù)
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);
}
可以與添加數(shù)據(jù)再進(jìn)行對比，這樣就能發(fā)現(xiàn)當(dāng)占位符比較多的情況下傳入對象時多么的方便
刪除數(shù)據(jù)
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);
}
看到此處可以發(fā)現(xiàn)添加，修改，刪除數(shù)據(jù)namedParameterJdbcTemplate提供的方法都是一樣的，都是update方法。
批量操作數(shù)據(jù)
批量操作數(shù)據(jù)一共有兩種方法
批量添加
方法一
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);
}
批量刪除
同上
