NamedParameterJdbcTemplate学习总结

NamedParameterJdbcTemplate学习总结NamedParameterJdbcTemplate学习总结

大家好,又见面了,我是你们的朋友全栈君。

环境

版本信息

spring的版本为4.1.4(spring3我也用过,就配置信息略有不同,其用法还是一样的)

配置信息

需要在applicationContext.xml中配置以下信息

<bean id="namedParameterJdbcTemplate" class="org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate">
    <constructor-arg ref="dataSource"/>
</bean>

如果已经在文件中配置了jdbctemplate的话还可以使用以下方法配置

<bean id="namedParameterJdbcTemplate" class="org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate">
    <constructor-arg ref="jdbcTemplate"/>
</bean>

当然也可以使用最原始的方法(该方法也需要配置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<String, Object> paramMap = new HashMap<String, Object>();
        paramMap.put("id", id);
        RowMapper<User> 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<User> rm = BeanPropertyRowMapper.newInstance(User.class);
        return namedParameterJdbcTemplate.queryForObject(sql, ps, rm);
    }

查询多条数据

普通查询

    public List<User> selectUser() {
        String sql = "SELECT id,username,sex,password,address FROM user";
        Map<String, Object> paramMap = new HashMap<String, Object>();
        RowMapper<User> rm = BeanPropertyRowMapper.newInstance(User.class);
        List<User> userList = namedParameterJdbcTemplate.query(sql, rm);
        return userList;
    }

模糊查询

    public List<User> selectUserLikeByName(String name) {
        //'%'空格:userName空格'%' 一定要有空格,不然会报错
        String sql = "SELECT id,username,sex,password,address FROM user WHERE username LIKE '%' :userName '%'";
        Map<String, Object> paramMap = new HashMap<String, Object>();
        paramMap.put("userName", name);
        RowMapper<User> rm = BeanPropertyRowMapper.newInstance(User.class);
        List<User> 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<String, Object> paramMap = new HashMap<String, Object>();
        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<String, Object> paramMap = new HashMap<String, Object>();
        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<User> userList = new ArrayList<>();
        userList.add(chen);
        userList.add(alex);
        List<Map<String, Object>> 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<User> 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<User> userList = new ArrayList<>();
        userList.add(chen);
        userList.add(alex);
        List<Map<String, Object>> 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<User> 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);
    }

批量删除

同上

版权声明:本文内容由互联网用户自发贡献,该文观点仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请发送邮件至 举报,一经查实,本站将立刻删除。

发布者:全栈程序员-用户IM,转载请注明出处:https://javaforall.cn/107312.html原文链接:https://javaforall.cn

【正版授权,激活自己账号】: Jetbrains全家桶Ide使用,1年售后保障,每天仅需1毛

【官方授权 正版激活】: 官方授权 正版激活 支持Jetbrains家族下所有IDE 使用个人JB账号...

(0)


相关推荐

发表回复

您的电子邮箱地址不会被公开。

关注全栈程序员社区公众号