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配置文件来配置相关的信息

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
       xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
       xmlns:context="http://www.springframework.org/schema/context"
       xmlns:aop="http://www.springframework.org/schema/aop"
       xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd
                           http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context.xsd
                           http://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop.xsd">

<!--    开启注解扫描-->
    <context:component-scan base-package="com.ws.spring"></context:component-scan>

    <!-- 数据库连接池 -->
    <bean id="dataSource" class="com.alibaba.druid.pool.DruidDataSource"
          destroy-method="close">
        <property name="url" value="jdbc:mysql:///study" />
        <property name="username" value="root" />
        <property name="password" value="123" />
        <property name="driverClassName" value="com.mysql.jdbc.Driver" />
    </bean>

    <!-- JdbcTemplate -->
    <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
        <!-- 注入DataSource -->
        <property name="dataSource" ref="dataSource"></property>
    </bean>
</beans>

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<User> findAllUser();

    //批量添加数据
    void batchAddUser(List<Object[]> 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>(User.class), id);
        return user;
    }

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

    //批量添加
    @Override
    public void batchAddUser(List<Object[]> 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<User> findAll(){
        return userDao.findAllUser();
    }

    //批量添加数据
    public void batchAdd(List<Object[]> 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<User> 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<Object[]> 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);
    }

}

相关阅读:
time 类
 案例：手动输入一个字符串，所有字符反转，大小写相互转
 案例：使用列表年月日计算这个日期是这一天的多少天
 用python输出1-100之间所有的质数
 案例：手动输入一个字符串，打散放进一个列表，小写字母反序大写字母保持不变
 安装数据库教程
 PHP use MySQLi
PHP logging
Vagrant
Flask基础
原文地址：https://www.cnblogs.com/nanfengashuai/p/16398226.html