Spring5——JdbcTemplate笔记

Spring5——JdbcTemplate笔记

概念

template，翻译过来是模板的意思，顾名思义，JdbcTemplate就是一个JDBC的模板，它对JDBC进行了封装，可以很方便地实现对数据库的CRUD（增、删、改、查）的操作。

JdbcTemplate准备工作

引入相关的依赖

• druid-1.1.9.jar
• mysql-connector-java-5.1.7-bin.jar
• spring-tx-5.2.6.RELEASE.jar
• spring-orm-5.2.6.RELEASE.jar
• 在这里要注意相关jar包的版本，比如说Java连接MySQL的驱动包的版本应该与目的数据库的版本一致

配置数据库连接池、在JdbcTemplate对象中注入DataSource（数据源）

这里用xml配置文件来配置相关的信息

JdbcTemplate的使用

创建service类、创建dao类

package com.spring.service;
//  service类
@Service
public class UserService {

}

package com.spring.dao;
//  dao类
public interface UserDao {
}

package com.spring.dao;
//  dao的实现类
@Repository
public class UserDaoImpl implements UserDao {
}

在service类中注入dao类的对象属性

package com.spring.service;

@Service
public class UserService {
    //注入dao对象
    @Autowired
    private UserDao userDao;
}

在dao类中注入JdbcTemplate对象

package com.spring.dao;

@Repository
public class UserDaoImpl implements UserDao {
    //注入jdbcTemplate对象
    @Autowired
    private JdbcTemplate jdbcTemplate;

}

增删改查

• 实体类

package com.spring.entiey;

public class User {
    private Integer id;
    private String name;
    private Integer age;
    private String email;

    public User() {
    }

    public User(Integer id, String name, Integer age, String email) {
        this.id = id;
        this.name = name;
        this.age = age;
        this.email = email;
    }

    @Override
    public String toString() {
        return "User{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", age=" + age +
                ", email='" + email + '\'' +
                '}';
    }

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public Integer getAge() {
        return age;
    }

    public void setAge(Integer age) {
        this.age = age;
    }

    public String getEmail() {
        return email;
    }

    public void setEmail(String email) {
        this.email = email;
    }
}

• UserDao

package com.spring.dao;

public interface UserDao {
    //添加的方法
    void add(User user);

    //修改的方法
    void updateUser(User user);

    //删除的方法
    void deleteUser(int id);

    //查询表记录数
    int selectCount();

    //查询返回对象
    User findUserInfo(int id);

    //查询返回集合
    List findAllUser();

    //批量添加数据
    void batchAddUser(List batchArgs);
}

• UserDaoImpl

package com.spring.dao;

@Repository
public class UserDaoImpl implements UserDao {
    //注入jdbcTemplate对象
    @Autowired
    private JdbcTemplate jdbcTemplate;

    @Override
    public void add(User user) {
        //用jdbcTemplate来操作数据库
        String sql = "insert into userinfo values (?,?,?,?)";
        Object[] args = {user.getId(),user.getName(),user.getAge(),user.getEmail()};
        int update = jdbcTemplate.update(sql,args);
        System.out.println(update);
    }

    @Override
    public void updateUser(User user) {
        String sql = "update userinfo set name=? where id=?";
        Object[] args = {user.getName(),user.getId()};
        int update = jdbcTemplate.update(sql, args);
        System.out.println("修改了："+update);
    }

    @Override
    public void deleteUser(int id) {
        String sql = "delete from userinfo where id=?";
        int update = jdbcTemplate.update(sql, id);
        System.out.println("删除了："+update);
    }

    //查询表中的记录数
    @Override
    public int selectCount() {
        String sql = "select count(*) from userinfo";
        Integer count = jdbcTemplate.queryForObject(sql, Integer.class);
        return count;
    }

    //查询返回对象
    @Override
    public User findUserInfo(int id) {
        String sql  = "select * from userinfo where id=?";
        User user = jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper(User.class), id);
        return user;
    }

    //查询返回集合
    @Override
    public List findAllUser() {
        String sql = "select * from userinfo";
        List users = jdbcTemplate.query(sql, new BeanPropertyRowMapper(User.class));
        return users;
    }

    //批量添加
    @Override
    public void batchAddUser(List batchArgs) {
        String sql = "insert into userinfo values (?,?,?,?)";
        int[] ints = jdbcTemplate.batchUpdate(sql, batchArgs);
        System.out.println(Arrays.toString(ints));
    }

}

• UserService

package com.spring.service;

@Service
public class UserService {
    //注入dao对象
    @Autowired
    private UserDao userDao;

    //添加数据的方法
    public void addUser(User user){
        userDao.add(user);
    }

    //修改数据的方法
    public void updateUser(User user){
        userDao.updateUser(user);
    }

    //删除数据的方法
    public void deleteUser(int id){
        userDao.deleteUser(id);
    }

    //查询表中记录数
    public int findCount(){
        int count = userDao.selectCount();
        return count;
    }

    //查询返回对象
    public User findOne(int id){
        User userInfo = userDao.findUserInfo(id);
        return userInfo;
    }

    //查询返回集合
    public List findAll(){
        return userDao.findAllUser();
    }

    //批量添加数据
    public void batchAdd(List batchArgs){
        userDao.batchAddUser(batchArgs);
    }
}

• 测试类

package com.spring.testdemo;

@SuppressWarnings({"all"})
public class TestSpringDemo {

    //添加单条数据
    @Test
    public void testAdd(){
        ApplicationContext context =
                new ClassPathXmlApplicationContext("bean.xml");

        UserService userService = context.getBean("userService", UserService.class);

        User user = new User();
        user.setName("111");
        user.setId(10);
        user.setAge(18);
        user.setEmail("shuaiwang2019@126.com");
        System.out.println("user= "+user);

        userService.addUser(user);
    }

    //修改单条数据
    @Test
    public void testUpdate(){
        ApplicationContext context =
                new ClassPathXmlApplicationContext("bean.xml");

        UserService userService = context.getBean("userService", UserService.class);

        User user = new User();
        user.setName("test");
        user.setId(1);
        System.out.println("user= "+user);

        userService.updateUser(user);
    }

    //删除单条数据
    @Test
    public void testDelete(){
        ApplicationContext context =
                new ClassPathXmlApplicationContext("bean.xml");

        UserService userService = context.getBean("userService", UserService.class);

        userService.deleteUser(1);
    }

}

package com.spring.testdemo;

public class TestJdbcTemplateSelect {

    //查询记录数
    @Test
    public void testSelectCount(){
        ApplicationContext context =
                new ClassPathXmlApplicationContext("bean.xml");

        UserService userService = context.getBean("userService", UserService.class);

        int count = userService.findCount();
        System.out.println(count);
    }

    //查询返回对象
    @Test
    public void testSelectUserInfo(){
        ApplicationContext context =
                new ClassPathXmlApplicationContext("bean.xml");

        UserService userService = context.getBean("userService", UserService.class);

        User userInfo = userService.findOne(10);
        System.out.println(userInfo);
    }

    //查询返回集合
    @Test
    public void testSelectAll(){
        ApplicationContext context =
                new ClassPathXmlApplicationContext("bean.xml");

        UserService userService = context.getBean("userService", UserService.class);

        List users = userService.findAll();

        System.out.println(users);
    }

    //批量添加
    @Test
    public void testBatchAdd(){
        ApplicationContext context =
                new ClassPathXmlApplicationContext("bean.xml");

        UserService userService = context.getBean("userService", UserService.class);

        //要添加的集合
        List batchArgs = new ArrayList<>();
        Object[] o1 = {11,"java",24,"java@126.com"};
        Object[] o2 = {12,"python",30,"python@126.com"};
        Object[] o3 = {13,"c++",40,"c++@126.com"};
        batchArgs.add(o1);
        batchArgs.add(o2);
        batchArgs.add(o3);
        userService.batchAdd(batchArgs);
    }

}

​

Original: https://www.cnblogs.com/nanfengashuai/p/16398226.html
Author: Blueshadow
Title: Spring5——JdbcTemplate笔记