环境
版本信息
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);
} 批量删除
同上
版权声明：本文内容由互联网用户自发贡献，该文观点仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 举报，一经查实，本站将立刻删除。
如需转载请保留出处：https://bianchenghao.cn/hz/137142.html