java--spring5之spring实现jdbc增删改查----4

1.引入依赖：
aspectjweaver-1.6.8.jar
cglib-2.2.0.jar.zip
commons-logging-1.1.1.jar
druid-1.1.9.jar
hamcrest-core-1.3.jar
junit-4.12.jar
mysql-connector-java-5.1.8.jar
spring-beans-5.2.6.RELEASE.jar
spring-context-5.2.6.RELEASE.jar
spring-core-5.2.6.RELEASE.jar
spring-expression-5.2.6.RELEASE.jar
spring-jdbc-5.2.6.RELEASE.jar
spring-orm-5.2.6.RELEASE.jar
spring-tx-5.2.6.RELEASE.jar

2.spring的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"
       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">

    <bean id="dataSource" class="com.alibaba.druid.pool.DruidDataSource" destroy-method="close">
        <property name="url" value="jdbc:mysql:///user_db"/>
        <property name="username" value="root"/>
        <property name="password" value="root"/>
        <property name="driverClassName" value="com.mysql.jdbc.Driver"/>
    </bean>

    <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
        <property name="dataSource" ref="dataSource"/>
    </bean>
    <context:component-scan base-package="com.my.jdbcTemplate"/>
</beans>

3.接口UserDao.java

package com.my.jdbcTemplate;

import java.util.List;

public interface UserDao {
    void add(User user);

    void batchAdd(List<Object[]> users);

    void update(User user);

    void batchUpdate(List<Object[]> users);

    void delete(String userName);

    void batchDelete(List<Object[]> userNames);

    int queryCount(String name);

    User queryObject(String name);

    List<User> queryObjects(String name);
}

4.实现类

package com.my.jdbcTemplate;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;

import java.util.List;

@Repository
public class UserDaoImpl implements UserDao {
    @Autowired
    private JdbcTemplate jdbcTemplate;

    @Override
    public void add(User user) {
        String sql = "insert into t_user values(?,?,?)";
        Object[] args = {user.getName(), user.getAge(), user.getHubby()};
        int updateResult = jdbcTemplate.update(sql, args);
        System.out.println("addResult: " + updateResult);
    }

    @Override
    public void batchAdd(List<Object[]> users) {
        String sql = "insert into t_user values(?,?,?)";
        int[] ints = jdbcTemplate.batchUpdate(sql, users);
        System.out.println("batchAddResult: " + ints.length);
    }

    @Override
    public void update(User user) {
        String sql = "update t_user set age=?,hubby=? where name=?";
        Object[] args = {user.getAge(), user.getHubby(), user.getName()};
        int updateResult = jdbcTemplate.update(sql, args);
        System.out.println("updateResult: " + updateResult);
    }

    @Override
    public void batchUpdate(List<Object[]> users) {
        String sql = "update t_user set age=?,hubby=? where name=?";
        int[] ints = jdbcTemplate.batchUpdate(sql, users);
        System.out.println("batchUpdateResult: " + ints.length);
    }

    @Override
    public void delete(String userName) {
        String sql = "delete from t_user where name=?";
        int updateResult = jdbcTemplate.update(sql, userName);
        System.out.println("deleteResult: " + updateResult);
    }

    @Override
    public void batchDelete(List<Object[]> userNames) {
        String sql = "delete from t_user where name=?";
        int[] updateResult = jdbcTemplate.batchUpdate(sql,userNames);
        System.out.println("deleteResult: " + updateResult.length);
    }

    @Override
    public int queryCount(String name) {
        String sql = "select count(*) from t_user where name=?";
        Integer integer = jdbcTemplate.queryForObject(sql, new String[]{name},int.class);
        return integer;
    }

    @Override
    public User queryObject(String name) {
        String sql = "select * from t_user where name=?";
        User user = jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<User>(User.class), name);
        return user;
    }

    @Override
    public List<User> queryObjects(String name) {
        String sql = "select * from t_user where name=?";
        List<User> users = jdbcTemplate.query(sql, new BeanPropertyRowMapper<User>(User.class), name);
        return users;
    }
}

5.service类

package com.my.jdbcTemplate;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.util.List;

@Service
public class UserService {
    @Autowired
    private UserDao userDao;

    public void addUser(User user) {
        userDao.add(user);
    }

    public void batchAddUser(List<Object[]> users) {
        userDao.batchAdd(users);
    }

    public void updateUser(User user) {
        userDao.update(user);
    }

    public void batchUpdateUser(List<Object[]> users) {
        userDao.batchUpdate(users);
    }

    public void deleteUser(String name) {
        userDao.delete(name);
    }

    public void batchDeleteUser(List<Object[]> names) {
        userDao.batchDelete(names);
    }

    public int queryUserCount(String name) {
        return userDao.queryCount(name);
    }

    public User queryUserObject(String name) {
        return userDao.queryObject(name);
    }

    public List<User> queryUserObjects(String name) {
        return userDao.queryObjects(name);
    }
}

6.实体类

package com.my.jdbcTemplate;

public class User {
    private String name;
    private int age;
    private String hubby;

    public User() {
    }

    public User(String name, int age, String hubby) {
        this.name = name;
        this.age = age;
        this.hubby = hubby;
    }

    public String getName() {
        return name;
    }

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

    public int getAge() {
        return age;
    }

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

    public String getHubby() {
        return hubby;
    }

    public void setHubby(String hubby) {
        this.hubby = hubby;
    }
}

7.测试类

package com.my.jdbcTemplate;

import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;

import java.util.ArrayList;
import java.util.List;

public class jdbcTemplateTest {
    public static void main(String[] args) {

        ApplicationContext context = new ClassPathXmlApplicationContext("jdbc.xml");
        UserService userService = context.getBean("userService", UserService.class);


        userService.addUser(new User("Jay Chou",43,"Movie")); // 增

        Object[] user1 = {"Lee Hom",40,"Music"};
        Object[] user2 = {"Lee Hom",41,"Sing"};
        Object[] user3 = {"Lee Hom",42,"Sleep"};
        List<Object[]> list = new ArrayList<>();
        list.add(user1);
        list.add(user2);
        list.add(user3);
        userService.batchAddUser(list); // 批量增

        User user4 = new User("Jay Chou",45,"Dau");
        userService.updateUser(user4); // 改


        Object[] user5 = {"Lee Hom",40,"Music"};
        Object[] user6 = {"Jay Chou",41,"Sing"};
        List<Object[]> list1 = new ArrayList<>();
        list1.add(user5);
        list1.add(user6);
        userService.batchUpdateUser(list1); // 批量改

        int res = userService.queryUserCount("Lee Hom");  // 查数量
        System.out.println("queryCountResult: "+ res);


        User user = userService.queryUserObject("Jay Chou"); // 查对象
        System.out.println(user.getName()+"--"+user.getAge()+"--"+user.getHubby());

        List<User> users = userService.queryUserObjects("Lee Hom"); // 查多个对象
        System.out.println("queryObjectsResult: "+users.size());

        /*userService.deleteUser("Lee Hom"); // 删
        userService.deleteUser("Jay Chou"); // 删*/
        Object[] name1 = {"Lee Hom"};
        Object[] name2 = {"Jay Chou"};
        List<Object[]> list2 = new ArrayList<>();
        list2.add(name1);
        list2.add(name2);
        userService.batchDeleteUser(list2); // 批量删

    }
}

8.输出结果
addResult: 1
batchAddResult: 3
updateResult: 1
batchUpdateResult: 2
queryCountResult: 3
Jay Chou–45–Dau
queryObjectsResult: 3
deleteResult: 2