springJDBC增删改查，批量操作，分页查询

最新推荐文章于 2022-10-08 13:47:01 发布

野生java研究僧

最新推荐文章于 2022-10-08 13:47:01 发布

阅读量394

点赞数

分类专栏： java后端文章标签： java

本文链接：https://blog.csdn.net/m0_46188681/article/details/115350129

版权

java后端专栏收录该内容

44 篇文章 2 订阅

订阅专栏

springJDBC

1.准备数据库表

/*
 Navicat Premium Data Transfer

 Source Server         : WindowsMysql
 Source Server Type    : MySQL
 Source Server Version : 50732
 Source Host           : localhost:3306
 Source Schema         : component

 Target Server Type    : MySQL
 Target Server Version : 50732
 File Encoding         : 65001

 Date: 31/03/2021 14:43:02
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for t_user
-- ----------------------------
DROP TABLE IF EXISTS `t_user`;
CREATE TABLE `t_user`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `password` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `email` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `phone` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 16 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of t_user
-- ----------------------------
INSERT INTO `t_user` VALUES (1, 'tom', 'tom', 'tom@qq.com', '123456');
INSERT INTO `t_user` VALUES (3, 'admin', 'admin123', 'admin@qq.com', '123456');
INSERT INTO `t_user` VALUES (4, 'root', 'root123', 'root@qq.com', '123456');
INSERT INTO `t_user` VALUES (5, 'test', 'test123', 'test@qq.com', '123456');
INSERT INTO `t_user` VALUES (6, '小明', '小明123', '小明@qq.com', '123456');
INSERT INTO `t_user` VALUES (7, '小王', '小王123', '小王@qq.com', '123456');
INSERT INTO `t_user` VALUES (8, 'compass', 'compass123', 'compass@qq.com', '123456');
INSERT INTO `t_user` VALUES (9, '卡夫卡', '卡夫卡123', '卡夫卡@qq.com', '123456');

SET FOREIGN_KEY_CHECKS = 1;

2.导入maven依赖

 <dependencies>

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


        <!-- spring 依赖 -->
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-context</artifactId>
            <version>5.2.6.RELEASE</version>
        </dependency>


        <!--aop 切面需要的依赖 -->

        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-aspects</artifactId>
            <version>5.2.6.RELEASE</version>
        </dependency>

        <dependency>
            <groupId>net.sourceforge.cglib</groupId>
            <artifactId>com.springsource.net.sf.cglib</artifactId>
            <version>2.2.0</version>
        </dependency>

        <dependency>
            <groupId>org.aopalliance</groupId>
            <artifactId>com.springsource.org.aopalliance</artifactId>
            <version>1.0.0</version>
        </dependency>

        <dependency>
            <groupId>org.aspectj</groupId>
            <artifactId>com.springsource.org.aspectj.weaver</artifactId>
            <version>1.6.4.RELEASE</version>
        </dependency>


        <!-- 测试依赖-->
        <dependency>
            <groupId>org.junit.jupiter</groupId>
            <artifactId>junit-jupiter-api</artifactId>
            <version>5.7.0</version>
            <scope>test</scope>
        </dependency>

        <!-- spring 测试依赖集成-->
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-test</artifactId>
            <version>5.1.3.RELEASE</version>
        </dependency>


        <!-- c3p0连接池 -->
        <dependency>
            <groupId>com.mchange</groupId>
            <artifactId>c3p0</artifactId>
            <version>0.9.5.2</version>
        </dependency>


        <!--数据库连接池-->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid</artifactId>
            <version>1.2.3</version>
        </dependency>
        <!--mysql驱动-->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.21</version>
        </dependency>
        <!--spring对jdbc的封装-->
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-jdbc</artifactId>
            <version>5.2.6.RELEASE</version>
        </dependency>
        <!--spring整合其他框架的使用-->
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-orm</artifactId>
            <version>5.2.6.RELEASE</version>
        </dependency>



    </dependencies>

3.SpringConfig.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.springjdbc.crud"> </context:component-scan>

    <!--配置数据库连接池-->
    <bean id="myDataSource" class="com.alibaba.druid.pool.DruidDataSource" destroy-method="close">
        <property name="url" value="jdbc:mysql://localhost/component?serverTimezone=GMT%2B8"> </property>
        <property name="username" value="root"> </property>
        <property name="password" value="自己数据库密码"> </property>
        <property name="driverClassName" value="com.mysql.cj.jdbc.Driver"> </property>
    </bean>




    <!--    配置 JdbcTemplate 对象，注入 DataSource -->
    <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate"  >
        <!-- 将数据库连接池的信息注入到 dataSource 对象中-->
        <property name="dataSource" ref="myDataSource"> </property>
    </bean>


</beans>

4.entity（实体类）

package com.springjdbc.crud.entity;

public class User {
    private  Integer  id;
    private String username;
    private String password;
    private String email;
    private String phone;

    public User(Integer id, String username, String password, String email, String phone) {
        this.id = id;
        this.username = username;
        this.password = password;
        this.email = email;
        this.phone = phone;
    }

    public User() {
    }

    public Integer getId() {
        return id;
    }

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

    public String getUsername() {
        return username;
    }

    public void setUsername(String username) {
        this.username = username;
    }

    public String getPassword() {
        return password;
    }

    public void setPassword(String password) {
        this.password = password;
    }

    public String getEmail() {
        return email;
    }

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

    public String getPhone() {
        return phone;
    }

    public void setPhone(String phone) {
        this.phone = phone;
    }

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

5.Dao(层代码)

dao层接口代码：

import java.util.List;

public interface UserDao {


    /**
     * 添加一个user
     * @param user user对象
     * @return  影响的行数
     */
    int addUser(User user);

    /**
     * 删除一个user
     * @param id userId
     * @return 影响的行数
     */
    int deleteUserById(String id);

    /**
     * 查询所有的user
     * @return List集合
     */
    List<User> queryUserAll();

    /**
     * 修改user信息
     * @param user 需要修改的user对象信息
     * @return 影响的行数
     */
     int updateUserById(User user);

    /**
     * 根据id查询user
     * @param id userId
     * @return 返回一个 user对象
     */
     User queryUserOne(int id);

    /**
     * 统计表中有多少个用户
     * @return 返回int类型的数量
     */
    int getUserCount();

    /**
     * 按条件模糊查询
     * @return 返回一个List集合
     */
    List<User> selectUserLike(String username);


    /**
     * 分页查询
     * @return 返回一个List集合
     */
    List<User> selectUserPage(int begin, int stop );

    /**
     * 批量添加数据
     * @param batchArgs list集合数组
     */
    void batchAddUser(List<Object[]> batchArgs);

    /**
     *  批量修改数据
     * @param batchArgs list集合数组
     */
    void batchUpdateUser(List<Object[]> batchArgs);

    /**
     * 批量删除数据
     * @param batchArgs list集合数组
     */
    void batchDeleteUser(List<Object[]> batchArgs);
}

daoImpl:代码：

import com.springjdbc.crud.dao.UserDao;
import com.springjdbc.crud.entity.User;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowCallbackHandler;
import org.springframework.stereotype.Repository;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

@Repository
public class UserDaoImpl implements UserDao {

    @Autowired
    private JdbcTemplate jdbcTemplate;

    @Override
    public int addUser(User user) {

        String sql = "insert into t_user(id,username,password,email,phone) values(?,?,?,?,?)";
        Object args[] = {user.getId(),user.getUsername(),user.getPassword(),user.getEmail(),user.getPhone(),};
        int updateCount = jdbcTemplate.update(sql, args);
        return updateCount;
    }

    @Override
    public int deleteUserById(String id) {
        String sql = "delete from t_user where id=?";

        int deleteCount = jdbcTemplate.update(sql, id);
        return deleteCount;
    }

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

    @Override
    public User queryUserOne( final int id) {

        String sql="select id,username,password,email,phone from t_user where id =?";
        final  User user = new User();

        //将结果集数据行中的数据抽取到forum对象中
        jdbcTemplate.query(sql, new Object[]{id}, new RowCallbackHandler() {
            public void processRow(ResultSet resultSet) throws SQLException {
                 user.setId(id);
                 user.setUsername(resultSet.getString("username"));
                 user.setPassword(resultSet.getString("password"));
                 user.setEmail(resultSet.getString("email"));
                 user.setPhone(resultSet.getString("phone"));

            }
        });
        return user;




    }

    /**
     * 统计表中有多少个用户
     *
     * @return 返回int类型的数量
     */
    @Override
    public int getUserCount() {
        String sql =" select count(*) from t_user";
        int userCount = jdbcTemplate.queryForObject(sql, Integer.class);
        return userCount;
    }

    @Override
    public int updateUserById(User user) {
        String sql = "update t_user set username=?,password=?,email=?,phone=? where id=?";
        Object args [] ={user.getUsername(),user.getPassword(),user.getEmail(),user.getPhone(),user.getId()};
        int updateCount = jdbcTemplate.update(sql, args);

        return  updateCount;
    }



    @Override
    public List<User> selectUserLike(String username) {

        String sql = "select id,username,password,email,phone from t_user where  username like ?";
        List<User> userList = jdbcTemplate.query(sql, new BeanPropertyRowMapper<User>(User.class),username);
        return userList;
    }


    @Override
    public List<User> selectUserPage(int begin, int stop) {
        String sql ="select id,username,password,email,phone from t_user limit ? , ?";
        List<User> userList = jdbcTemplate.query(sql, new BeanPropertyRowMapper<User>(User.class), begin, stop);
        return userList;
    }


    @Override
    public void batchAddUser(List<Object[]> batchArgs) {

        String sql = "insert into t_user( username, password, email, phone) values(?,?,?,?)";
        int[] args = jdbcTemplate.batchUpdate(sql, batchArgs);


    }

    @Override
    public void batchUpdateUser(List<Object[]> batchArgs) {
        String sql = "update t_user set username=?,password=?,email=?,phone=? where id=?";
        int[] args = jdbcTemplate.batchUpdate(sql, batchArgs);

    }


    @Override
    public void batchDeleteUser(List<Object[]> batchArgs) {
        String sql = "delete from t_user where id=?";
        int[] args = jdbcTemplate.batchUpdate(sql, batchArgs);


    }




}

6.Service（层代码）

service层接口代码：

import java.util.List;

/**
 * @author compass
 * @version 1.0
 * @date 2021-03-31 12:32
 */
public interface UserService {


    /**
     * 添加一个user
     * @param user user对象
     * @return  影响的行数
     */
    int addUser(User user);

    /**
     * 删除一个user
     * @param id userId
     * @return 影响的行数
     */
    int deleteUserById(String id);

    /**
     * 查询所有的user
     * @return List集合
     */
    List<User> queryUserAll();

    /**
     * 修改user信息
     * @param user 需要修改的user对象信息
     * @return 影响的行数
     */
    int updateUserById(User user);

    /**
     * 根据id查询user
     * @param id userId
     * @return 返回一个 user对象
     */
    User queryUserOneById(int id);


    /**
     * 统计表中有多少个用户
     * @return 返回int类型的数量
     */
    int getUserCount();

    /**
     * 按条件模糊查询
     * @return 返回一个List集合
     */
    List<User> selectUserLike(String username);


    /**
     * 分页查询
     * @return 返回一个List集合
     */
    List<User> selectUserPage(int begin, int stop );

    /**
     * 批量添加数据
     * @param batchArgs list集合数组
     */
    void batchAddUser(List<Object[]> batchArgs);

    /**
     *  批量修改数据
     * @param batchArgs list集合数组
     */
    void batchUpdateUser(List<Object[]> batchArgs);

    /**
     * 批量删除数据
     * @param batchArgs list集合数组
     */
    void batchDeleteUser(List<Object[]> batchArgs);
}

serviceImpl代码：

import com.springjdbc.crud.dao.UserDao;
import com.springjdbc.crud.entity.User;
import com.springjdbc.crud.service.UserService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.util.List;

/**
 * @author compass
 * @version 1.0
 * @date 2021-03-31 12:33
 */
@Service
public class UserServiceImpl implements UserService {

    @Autowired
    private UserDao userDao;

    @Override
    public int addUser(User user) {

        return userDao.addUser(user);
    }

    @Override
    public int deleteUserById(String id) {


        return userDao.deleteUserById(id);
    }

    @Override
    public List<User> queryUserAll() {

        return  userDao.queryUserAll();
    }

    @Override
    public int updateUserById(User user) {
        return userDao.updateUserById(user);
    }

    /**
     * 根据id查询user
     *
     * @param id userId
     * @return 返回一个 user对象
     */
    @Override
    public User queryUserOneById(int id) {

        return userDao.queryUserOne(id);
    }

    /**
     * 统计表中有多少个用户
     *
     * @return 返回int类型的数量
     */
    @Override
    public int getUserCount() {

        return   userDao.getUserCount();
    }

    /**
     * 按条件模糊查询
     *
     * @param username
     * @return 返回一个List集合
     */
    @Override
    public List<User> selectUserLike(String username) {

        return  userDao.selectUserLike("%"+username+"%");
    }

    /**
     * 分页查询
     *
     * @param begin
     * @param stop
     * @return 返回一个List集合
     */
    @Override
    public List<User> selectUserPage(int begin, int stop) {

        return userDao.selectUserPage(begin,stop);
    }

    /**
     * 批量添加数据
     *
     * @param batchArgs list集合数组
     */
    @Override
    public void batchAddUser(List<Object[]> batchArgs) {

        userDao.batchAddUser(batchArgs);

    }

    /**
     * 批量修改数据
     *
     * @param batchArgs list集合数组
     */
    @Override
    public void batchUpdateUser(List<Object[]> batchArgs) {

        userDao.batchUpdateUser(batchArgs);


    }

    /**
     * 批量删除数据
     *
     * @param batchArgs list集合数组
     */
    @Override
    public void batchDeleteUser(List<Object[]> batchArgs) {

    userDao.batchDeleteUser(batchArgs);

    }


}

7.集成spring5测试

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

@ExtendWith(SpringExtension.class)
@ContextConfiguration("classpath:springConfig.xml")
public class UserServiceTest {

    @Autowired
    private UserService userService;


   // 添加一条数据
  @Test
    void  addUserTest(){

      User user = new User(null,"卡夫卡","卡夫卡123","卡夫卡@qq.com","123456");
      Integer addUserCount = userService.addUser(user);
  }

    // 查询所有数据
    @Test
    void  queryUserTest(){

      List<User> userList = userService.queryUserAll();
        for (User user : userList) {
            System.out.println(user);
        }
    }

    // 删除一条数据
    @Test
    void  deleteUserTest(){

        Integer integer = userService.deleteUserById("2");
        System.out.println(integer);
    }

    // 修改一条数据
    @Test
    void  updateUserTest(){

        User user = new User(1,"kfk","kfk123","tom@qq.com","123456");
        Integer addUserCount = userService.updateUserById(user);
    }

    // 按照id查询一条数据
    @Test
    void  queryUserOneById(){

        User user = userService.queryUserOneById(1);
        System.out.println(user);
    }

     // 获取表中有多少个user用户
    @Test
     void getUserCount() {

         int userCount = userService.getUserCount();
         System.out.println(userCount);
     }


    // 根据 username 字段模糊查询
    @Test
    void  selectUserLike(){

        List<User> userList = userService.selectUserLike("ad");
        for (User user : userList) {
            System.out.println(user);
        }
    }


    // 分页查询
    @Test
     void selectUserPage() {

        List<User> userList = userService.selectUserPage(0, 3);
        for (User user : userList) {
            System.out.println(user);
        }

    }

    // 批量添加
    @Test
     void batchAddUser() {

       List<Object[]> batchArgs = new ArrayList<>();
        Object[] o1 = {"adminOne","admin123","admin@qq.com1","123456"};
        Object[] o2 = {"adminTwo","admin123","admin@qq.com1","123457"};
        Object[] o3 = {"adminThree","admin123","admin@qq.com1","123458"};

        batchArgs.add(o1);
        batchArgs.add(o2);
        batchArgs.add(o3);
        userService.batchAddUser(batchArgs);
    }

    // 批量修改
    @Test
     void batchUpdateUser() {

        List<Object[]> batchArgs = new ArrayList<>();
        Object[] o1 = {"adminOne1","admin123","admin@qq.com1","123456",10};
        Object[] o2 = {"adminTwo2","admin123","admin@qq.com1","123457",11};
        Object[] o3 = {"adminThree3","admin123","admin@qq.com1","123458",12};

        batchArgs.add(o1);
        batchArgs.add(o2);
        batchArgs.add(o3);
        userService.batchUpdateUser(batchArgs);
    }

    // 批量删除
    @Test
     void batchDeleteUser() {
        List<Object[]> batchArgs = new ArrayList<>();
        Object[] o1 = {10};
        Object[] o2 = {11};
        Object[] o3 = {12};
        batchArgs.add(o1);
        batchArgs.add(o2);
        batchArgs.add(o3);
        userService.batchDeleteUser(batchArgs);
    }



}

8.原生jdbc获取数据库连接对象

public class JDBCTest {
    @Test
    void getConnectionTest(){

        Connection connection = getConnections();
        System.out.println(connection);


    }

  public static Connection   getConnections(){

      try {
          Class.forName("com.mysql.cj.jdbc.Driver");
      } catch (ClassNotFoundException e) {
          e.printStackTrace();
      }

      Connection connection = null;
        String user="root";
        String password ="admin";
        String url = "mysql://localhost/compass?serverTimezone=GMT%2B8";
      try {
           connection = DriverManager.getConnection(url, user, password);
      } catch (SQLException throwables) {
          throwables.printStackTrace();
      }

      return connection;
    }
}

9.注意点补充：

1.同的数据库连接池，它的urt，password，user，driverClassName这个几个值是不也一样的。

2.查询单条数据和查询全部的方式是不一样的，虽然利用BeanPropertyRowMapper的方式既可以查询全部，也可以查询单条数据，但是不推荐在查询单条数据的时候使用 BeanPropertyRowMapper ，因为BeanPropertyRowMapper是直接把全部数据加载出来，而利用结果集封装的方式是按需加载。

3.需要注意本地数据库和mysql驱动包版本是否一致，如果是高版本的时候，在写数据库的url的时候记得的加上时区，dirver也是需要注意的。

高版本的dirver写法：com.mysql.cj.jdbc.Driver

低版本的dirver写法：com.mysql.jdbc.Driver

野生java研究僧

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
springJDBC增删改查，批量操作，分页查询

springJDBC1.准备数据库表2.导入maven依赖3.SpringConfig.xml配置文件4.entity（实体类）5.Dao(层代码)6.Service（层代码）7.集成spring5测试8.注意点补充：1.准备数据库表/* Navicat Premium Data Transfer Source Server : WindowsMysql Source Server Type : MySQL Source Server Version : 50732 S
复制链接

扫一扫