Maven和spring jdbcTemplate实现数据库的增删改查和批量修改

1.导入相关依赖。

<dependencies>
    <dependency>
      <groupId>junit</groupId>
      <artifactId>junit</artifactId>
      <version>4.12</version>
    </dependency>


    <!--数据库相关依赖-->
    <dependency>
      <groupId>mysql</groupId>
      <artifactId>mysql-connector-java</artifactId>
      <version>5.1.47</version>
    </dependency>
    <dependency>
      <groupId>org.springframework</groupId>
      <artifactId>spring-jdbc</artifactId>
      <version>5.1.2.RELEASE</version>
    </dependency>
    <dependency>
      <groupId>com.alibaba</groupId>
      <artifactId>druid</artifactId>
      <version>1.2.4</version>
    </dependency>

    <!--Spring相关依赖-->
    <dependency>
      <groupId>org.springframework</groupId>
      <artifactId>spring-context</artifactId>
      <version>5.0.6.RELEASE</version>
    </dependency>

    <dependency>
      <groupId>commons-logging</groupId>
      <artifactId>commons-logging</artifactId>
      <version>1.2</version>
    </dependency>

    <dependency>
      <groupId>org.springframework</groupId>
      <artifactId>spring-orm</artifactId>
      <version>5.3.3</version>
    </dependency>

    <dependency>
      <groupId>org.springframework</groupId>
      <artifactId>spring-tx</artifactId>
      <version>5.3.3</version>
    </dependency>

  </dependencies>

2.在entity包下创建User类。

package entity;

public class Book {
    private String uname;
    private String upass;
    private int type;
    private int id;

    public int getId() {
        return id;
    }

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

    public String getUname() {
        return uname;
    }

    public void setUname(String uname) {
        this.uname = uname;
    }

    public String getUpass() {
        return upass;
    }

    public void setUpass(String upass) {
        this.upass = upass;
    }

    public int getType() {
        return type;
    }

    public void setType(int type) {
        this.type = type;
    }
}

3.在dao包下创建接口UserDao.

package dao;

import entity.User;

public interface UserDao {
    void add(User user);
}
**```
4.在dao包下创建UserDao的实现类**

```java
package dao;

import entity.User;
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.Arrays;
import java.util.List;

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

//添加用户
    public void add(User user) {
        String sql="insert into user(id,uname,upass,type) values(?,?,?,?)";
        Object[] args={user.getId(),user.getUname(),user.getUpass(),user.getType()};
        int res=jdbcTemplate.update(sql,args);
        System.out.println(res);
    }
//根据用户id删除用户
    public void delete(int id) {
        String sql="delete from user where id=?";
        int res = jdbcTemplate.update(sql,id);
        System.out.println(res);
    }
//修改用户
    public void update(User user) {
    String sql="update user set uname=?,upass=?,type=? where id=?";
    Object[] args={user.getUname(),user.getUpass(),user.getType(),user.getId()};
    int res=jdbcTemplate.update(sql,args);
        System.out.println(res);
    }
//查询表的行数
    public int selectCount() {
        String sql="select count(*) from user";
        int res = jdbcTemplate.queryForObject(sql,Integer.class);
        System.out.println(res);
        return res;
    }

    public User query(int id) {

        String sql = "select * from user where id = ?";
        User user = jdbcTemplate.queryForObject(sql,new BeanPropertyRowMapper<User>(User.class),id);
        return user;
    }

    public List<User> queryList() {
        String sql = "select * from user";
        List<User> userList = jdbcTemplate.query(sql,new BeanPropertyRowMapper<User>(User.class));
        return userList;
    }

    public void batchAddUsers(List<Object[]> batchArgs){
        String sql="insert into user(id,uname,upass,type) values(?,?,?,?)";
        int[] ints = jdbcTemplate.batchUpdate(sql,batchArgs);
        System.out.println(ints);
    }

    public void batchUpdateUser(List<Object[]> batchArgs) {
        String sql="update user set uname=?,upass=?,type=? where id=?";
        int[] ints = jdbcTemplate.batchUpdate(sql,batchArgs);
        System.out.println(Arrays.toString(ints));
    }

    public void batchDeleteUser(List<Object[]> batchArgs) {
        String sql="delete from user where id=?";
        int[] ints = jdbcTemplate.batchUpdate(sql, batchArgs);
        System.out.println(Arrays.toString(ints));
    }
}

5.在service包下创建UserService类

package dao;

import entity.User;
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.Arrays;
import java.util.List;

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

//添加用户
    public void add(User user) {
        String sql="insert into user(id,uname,upass,type) values(?,?,?,?)";
        Object[] args={user.getId(),user.getUname(),user.getUpass(),user.getType()};
        int res=jdbcTemplate.update(sql,args);
        System.out.println(res);
    }
//根据用户id删除用户
    public void delete(int id) {
        String sql="delete from user where id=?";
        int res = jdbcTemplate.update(sql,id);
        System.out.println(res);
    }
//修改用户
    public void update(User user) {
    String sql="update user set uname=?,upass=?,type=? where id=?";
    Object[] args={user.getUname(),user.getUpass(),user.getType(),user.getId()};
    int res=jdbcTemplate.update(sql,args);
        System.out.println(res);
    }
//查询表的行数
    public int selectCount() {
        String sql="select count(*) from user";
        int res = jdbcTemplate.queryForObject(sql,Integer.class);
        System.out.println(res);
        return res;
    }

    public User query(int id) {

        String sql = "select * from user where id = ?";
        User user = jdbcTemplate.queryForObject(sql,new BeanPropertyRowMapper<User>(User.class),id);
        return user;
    }

    public List<User> queryList() {
        String sql = "select * from user";
        List<User> userList = jdbcTemplate.query(sql,new BeanPropertyRowMapper<User>(User.class));
        return userList;
    }

    public void batchAddUsers(List<Object[]> batchArgs){
        String sql="insert into user(id,uname,upass,type) values(?,?,?,?)";
        int[] ints = jdbcTemplate.batchUpdate(sql,batchArgs);
        System.out.println(ints);
    }

    public void batchUpdateUser(List<Object[]> batchArgs) {
        String sql="update user set uname=?,upass=?,type=? where id=?";
        int[] ints = jdbcTemplate.batchUpdate(sql,batchArgs);
        System.out.println(Arrays.toString(ints));
    }

    public void batchDeleteUser(List<Object[]> batchArgs) {
        String sql="delete from user where id=?";
        int[] ints = jdbcTemplate.batchUpdate(sql, batchArgs);
        System.out.println(Arrays.toString(ints));
    }
}

6.创建aop.xml配置文件

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
       xmlns:context="http://www.springframework.org/schema/context"
       xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
       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">


    <!-- 开启组件扫描-->
    <context:component-scan base-package="dao,entity,service"></context:component-scan>

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

    </bean>




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

</beans>

7.编写测试类UserTest

import entity.User;
import junit.framework.TestCase;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import service.UserService;

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

public class UserTest extends TestCase {
    public void testJdbcTemplate(){
        ApplicationContext context = new ClassPathXmlApplicationContext("aop.xml");
        UserService userService = context.getBean("userService",UserService.class);
//        User user = new User();

        //添加用户
//        user.setId(15);
//        user.setType(1);
//        user.setUname("张三");
//        user.setUpass("456");
//        userService.addUser(user);

//        //删除用户
//        userService.deleteUser(15);

//        //根据用户id更新用户名字，密码和type
//        User user = new User();
//        user.setUname("兴");
//        user.setUpass("兴");
//        user.setType(2);
//        user.setId(12);
//        userService.updateUser(user);

//        //查询表的行数
//        userService.findCount();

//        //根据用户id查询用户信息
//        System.out.println(userService.queryUser(12));;

//

//        //批量添加用户
//        List<Object[]> batchArgs = new ArrayList<Object[]>();
//        Object[] ob1 = {15, "张三丰",123,1};
//        Object[] ob2 = {16, "张无忌",129,1};
//        batchArgs.add(ob1);
//        batchArgs.add(ob2);
//        userService.batchAdd(batchArgs);

        批量更新用户
//        List<Object[]> batchArgs = new ArrayList<Object[]>();
//        Object[] ob1 = {"张三三",12,2,15};
//        Object[] ob2 = {"张崔三",12,2,16};
//        batchArgs.add(ob1);
//        batchArgs.add(ob2);
//        userService.batchUpdate(batchArgs);


//        //根据id批量删除用户
//        List<Object[]> batchArgs = new ArrayList<Object[]>();
//        Object[] ob1 = {15};
//        Object[] ob2 = {16};
//        batchArgs.add(ob1);
//        batchArgs.add(ob2);
//        userService.batchDelete(batchArgs);

    }
}