目录

JDBC介绍

JDBC的工作原理

JDBC访问数据库步骤

步骤1：加载驱动

步骤2：获取数据库连接

步骤3：创建Statement执行SQL语句

步骤4：处理ResultSet结果集

步骤5：释放资源 close();

案例

User定义

使用Statement查询User

JDBC的PreparedStatement

使用PreparedStatement查询

新增

修改和删除

封装DbUtils工具类

调用存储过程

本人IDEA完整版

JDBC介绍

• 数据库访问技术简介：

  • JDBC（Java DataBase Connectivity）是由Sun Microsystem公司提供的API（Application Programming Interface应用程序编程接口）；它为Java应用程序提供了一系列的类，使其能够快速高效地访问数据库；这些功能是由一系列的类和对象来完成的，我们只需使用相关的对象，即可完成对数据库的操作

JDBC的工作原理

JDBC访问数据库步骤

步骤1：加载驱动

• 使用Class类的forName方法，将驱动程序类加载到JVM（Java虚拟机）中；

步骤2：获取数据库连接

• 成功加载驱动后，必须使用DriverManager类的静态方法getConnection来获得连接对象；

步骤3：创建Statement执行SQL语句

• 通过Connection对象创建

• 用于执行SQL语句

步骤4：处理ResultSet结果集

• 用于存储查询结果

  • 只在执行select语句时返回

步骤5：释放资源 close();

案例

User定义

DROP TABLE IF EXISTS `user`;
CREATE TABLE `user` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `username` varchar(255) NOT NULL,
  `password` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of user
-- ----------------------------
INSERT INTO `user` VALUES ('1', 'jim', '11111');
INSERT INTO `user` VALUES ('2', 'tom', '22222');
INSERT INTO `user` VALUES ('3', 'lucy', '3333');
INSERT INTO `user` VALUES ('4', 'laowang', '4444');
INSERT INTO `user` VALUES ('5', 'laoli', '5555');

使用Statement查询User

引入MySql驱动jar

public class User {
    
    private Long id;
    private String username;
    private String password;

    public User(String username, String password) {
        this.username = username;
        this.password = password;
    }

    public User(Long id, String username, String password) {
        this.id = id;
        this.username = username;
        this.password = password;
    }

    public Long getId() {
        return id;
    }

    public void setId(Long 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;
    }

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

import java.sql.*;

public class JdbcTest {
    public static void main(String[] args) {
        User jim = query("jim", "11111");
        System.out.println(jim);
    }

    
    private static User query(String username, String password) {
        Connection conn = null;
        Statement statement = null;
        ResultSet rs = null;
        try {
            //1、加载驱动,MySQL8的驱动
            Class.forName("com.mysql.cj.jdbc.Driver");
            //Class.forName("com.mysql.jdbc.Driver");//MySQL5的驱动
            //2、获取连接
            String url = "jdbc:mysql://localhost:3306/ddbb?useUnicode=true&characterEncoding=utf8&useSSL=false&serverTimezone=Asia/Shanghai";
            //通过url,登录数据库的用户名和密码
            conn = DriverManager.getConnection(url, "root", "123456");
            //3、创建Statement对象并查询
            statement = conn.createStatement();
            String sql = "SELECT * FROM `user` WHERe username='" + username + "' AND `password`='" + password + "'";
            rs = statement.executeQuery(sql);
            //4、处理结果集
            User user = null;
            if (rs.next()) {//rs.next()移动游标返回true,表示有数据,可以获取数据,返回false,表示没有数据
                //获取结果集的值（根据索引）
                Long id = (Long) rs.getObject(1);
                String myUsername = (String) rs.getObject(2);
                String myPassword = (String) rs.getObject(3);
                user = new User(id, myUsername, myPassword);
            }
            return user;
        } catch (ClassNotFoundException | SQLException e) {
            e.printStackTrace();
        } finally {
            //5、关闭资源
            try {
                if (rs != null) {
                    rs.close();
                }
                if (statement != null) {
                    statement.close();
                }
                if (conn != null) {
                    conn.close();
                }
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        return null;
    }
}

      

JDBC的PreparedStatement

• SQL语句使用“？”作为数据占位符

• 在创建时对SQL语句进行预编译

• 使用setXxx()方法设置数据

使用PreparedStatement查询

import java.sql.*;
import java.util.Scanner;

public class JdbcTest {
    public static void main(String[] args) {
        login();
    }

    //模拟登陆
    private static void login() {
        Scanner sc = new Scanner(System.in);
        System.out.println("请输入用户名:");
        String username = sc.nextLine();
        System.out.println("请输入密码:");
        String password = sc.nextLine();
        User user = query(username, password);
        if (user != null) {
            System.out.println("欢迎" + username + "登陆成功");
        } else {
            System.out.println("用户名或密码不正确！");
        }
    }

    private static User query(String username, String password) {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            //1、加载驱动
            Class.forName("com.mysql.cj.jdbc.Driver");
            //2、创建连接
            //协议：子协议：端口，IP：数据库名称          中文乱码:加密通道
            String url = "jdbc:mysql://127.0.0.1:3306/ddbb?useUnicode=true&characterEncoding=utf8&useSSL=false&serverTimezone=Asia/Shanghai";
            conn = DriverManager.getConnection(url, "root", "123456");
            //3、获取PrepareStatement对象，执行sql语句
            //String sql = "select * from `user` where username='"+username+"' and `password`='"+password+"'";
            //sql的问号表示参数占位符
            String sql = "select * from `user` where username=? and `password`=?";
            ps = conn.prepareStatement(sql);
            //把参数占位符?替换成参数
            ps.setObject(1, username);
            ps.setObject(2, password);
            rs = ps.executeQuery();
            //4、处理结果集
            User user = null;
            if (rs.next()) {
                //根据列的索引获取结果集对应的数据
                Long id = (Long) rs.getObject(1);
                String myUsername = (String) rs.getObject(2);
                String myPassword = (String) rs.getObject(3);
                user = new User(id, myUsername, myPassword);
            }
            return user;
            //5、关闭资源
        } catch (ClassNotFoundException | SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
                if (ps != null) {
                    ps.close();
                }
                if (conn != null) {
                    conn.close();
                }
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        return null;
    }
}

新增

private static void insert() throws ClassNotFoundException, SQLException {
    //1、加载jdbc驱动
    Class.forName("com.mysql.cj.jdbc.Driver");
    //2、获取连接
    //在mysql8.0的数据库 serverTimezone=Asia/shanghai是必须的参数
    String url = "jdbc:mysql://localhost:3306/db1?useUnicode=true&characterEncoding=utf8&useSSL=false&serverTimezone=Asia/Shanghai";
    Connection conn = DriverManager.getConnection(url, "root", "123456");
    //3、获取PreparedStatement对象
    //注意点：sql语句中使用？作为参数的占位符
    String sql = "insert into `user`(username,password) values(?,?)";
    PreparedStatement ps = conn.prepareStatement(sql,Statement.RETURN_GENERATED_KEYS);
    //把问号替换成参数
    ps.setObject(1, "jim");
    ps.setObject(2, 30);
    //4、执行sql，新增 要使用executeUpdate()
    ps.executeUpdate();
    // 执行此 Statement 对象而创建的所有自动生成的键
    ResultSet rs = ps.getGeneratedKeys();
    if (rs.next()) {
        // 指定返回生成的主键
        Object id = rs.getObject(1);
        System.out.println("新增的主键为："+id);
    }
    //5、关闭资源
    ps.close();
    conn.close();
}

修改和删除

private static void update() throws ClassNotFoundException, SQLException {
    //1、加载jdbc驱动
    Class.forName("com.mysql.cj.jdbc.Driver");
    //2、获取连接
    //在mysql8.0的数据库 serverTimezone=Asia/shanghai是必须的参数
    String url = "jdbc:mysql://localhost:3306/db1?useUnicode=true&characterEncoding=utf8&useSSL=false&serverTimezone=Asia/Shanghai";
    Connection conn = DriverManager.getConnection(url, "root", "123456");
    //3、获取PreparedStatement对象
    //注意点：sql语句中使用？作为参数的占位符
    String sql = "update user set password=? where id = ?";
    PreparedStatement ps = conn.prepareStatement(sql);
    //把问号替换成参数
    ps.setObject(1, "888");
    ps.setObject(2, 1);
    //4、执行sql，更新或者删除 要使用executeUpdate()，处理返回值，返回值代表的含义：影响的行数
    int result = ps.executeUpdate();
    if (result > 0) {
        System.out.println("更新密码成功");
    } else {
        System.out.println("更新密码失败");
    }
    //5、关闭资源
    ps.close();
    conn.close();
}

封装DbUtils工具类

import java.io.IOException;
import java.io.InputStream;
import java.sql.*;
import java.util.*;

public class DbUtils {
    private static String driverName;
    private static String url;
    private static String username;
    private static String password;

    static {
        InputStream inputStream = null;
        try {
            //创建db.properties的流
            inputStream = DbUtils.class.getClassLoader().getResourceAsStream("db.properties");
            //创建Properties对象
            Properties p = new Properties();
            //把数据流读入Properties对象中
            p.load(inputStream);
            //从Properties对象中获取配置数据
            url = p.getProperty("url");
            username = p.getProperty("username");
            password = p.getProperty("password");
            driverName = p.getProperty("driverName");
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            try {
                if (inputStream != null) {
                    inputStream.close();
                }
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }

    
    public static Object insert(Connection connection, String sql, Object[] params) throws SQLException {
        PreparedStatement ps = null;
        Object id = null;
        try {
            //创建PreparedStatement
            ps = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
            //设置参数
            setPreparedStatementParam(ps, params);
            //执行sql
            ps.executeUpdate();
            // 执行此 Statement 对象而创建的所有自动生成的键
            ResultSet rs = ps.getGeneratedKeys();
            if (rs.next()) {
                // 指定返回生成的主键
                id = rs.getObject(1);
            }
        } finally {
            close(ps);
        }
        return id;
    }

    
    public static Object insert(String sql, Object[] params) throws SQLException, ClassNotFoundException {
        Connection conn = null;
        Object id;
        try {
            //创建连接
            conn = getConnection();
            id = insert(conn, sql, params);
        } finally {
            close(conn);
        }
        return id;
    }

    
    public static boolean update(Connection connection, String sql, Object[] params) throws SQLException {
        PreparedStatement ps = null;
        try {
            //步骤2：设置SQL语句以及对应的参数
            ps = connection.prepareStatement(sql);
            setPreparedStatementParam(ps, params);
            //步骤3：执行update
            int result = ps.executeUpdate();
            //返回执行的结果
            return result > 0 ? true : false;
        } finally {
            //步骤4：关闭资源
            close(ps);
        }
    }

    
    public static boolean update(String sql, Object[] params) throws SQLException, ClassNotFoundException {
        Connection connection = null;
        try {
            //步骤1：获取链接
            connection = getConnection();
            return update(connection, sql, params);
        } finally {
            //步骤2：关闭连接资源
            close(connection);
        }
    }

    
    public static Map selectOne(Connection connection, String sql, Object[] params) throws SQLException {
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            //步骤2：设置SQL语句以及对应的参数
            ps = connection.prepareStatement(sql);
            setPreparedStatementParam(ps, params);

            //步骤3：执行查询，把查询结果的列作为key，列对应的值作为value，保存到Map中
            rs = ps.executeQuery();

            if (rs.next()) {
                return getResultMap(rs);
            }
        } finally {
            //步骤4：关闭资源
            close(rs, ps);
        }
        return null;
    }

    
    private static Map getResultMap(ResultSet rs) throws SQLException {
        //获取到result的元数据，包含了列的信息
        ResultSetMetaData metaData = rs.getMetaData();

        //获取到当前表的所有的列的列数
        int columnCount = metaData.getColumnCount();

        //存储数据库列与值的map
        Map map = new HashMap<>();

        //根据列的数量，获取到每一个列的列名以及对应的值
        for (int i = 0; i < columnCount; i++) {
            //能够获取到每一个列的名称，参数是每个列的序号值
            String columnLabel = metaData.getColumnLabel(i + 1);
            Object columnValue = rs.getObject(columnLabel);
            map.put(columnLabel, columnValue);

        }
        return map;
    }

    
    public static Map selectOne(String sql, Object[] params) throws SQLException, ClassNotFoundException {
        Connection connection = null;
        try {
            //步骤1：获取链接
            connection = getConnection();
            return selectOne(connection, sql, params);
        } finally {
            //步骤4：关闭资源
            close(connection);
        }
    }

    
    public static List> selectList(Connection connection, String sql, Object[] params) throws SQLException {
        PreparedStatement ps = null;
        ResultSet rs = null;
        List> list;
        try {
            //步骤2：设置SQL语句以及对应的参数
            ps = connection.prepareStatement(sql);
            setPreparedStatementParam(ps, params);

            //步骤3：执行查询，把查询结果的列作为key，列对应的值作为value，保存到Map中
            rs = ps.executeQuery();
            list = new ArrayList<>();

            while (rs.next()) {
                list.add(getResultMap(rs));
            }
        } finally {
            //步骤4：关闭资源
            close(rs, ps);
        }
        return list;
    }

    
    public static List> selectList(String sql, Object[] params) throws SQLException, ClassNotFoundException {
        Connection connection = null;
        try {
            connection = getConnection();
            return selectList(connection, sql, params);
        } finally {
            close(connection);
        }
    }

    
    private static void setPreparedStatementParam(PreparedStatement ps, Object[] params) throws SQLException {
        if (params != null && params.length > 0) {
            for (int i = 0; i < params.length; i++) {
                ps.setObject(i + 1, params[i]);
            }
        }
    }

    
    public static Connection getConnection() throws ClassNotFoundException, SQLException {
        //加载数据库驱动
        Class.forName(driverName);
        return DriverManager.getConnection(url, username, password);
    }

    //开启事务
    public static void beginTransaction(Connection conn) throws SQLException {
        conn.setAutoCommit(false);
    }

    //提交事务
    public static void commit(Connection conn) throws SQLException {
        conn.commit();
    }

    //回滚事务
    public static void rollback(Connection conn) throws SQLException {
        conn.rollback();
    }

    public static void close(Connection connection) {
        if (connection != null) {
            try {
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }


    
    private static void close(PreparedStatement ps, Connection connection) {
        close(ps);
        close(connection);
    }

    
    private static void close(ResultSet rs, PreparedStatement ps) {
        close(rs);
        close(ps);
    }

    
    private static void close(ResultSet rs, PreparedStatement ps, Connection conn) {
        close(rs);
        close(ps);
        close(conn);
    }


    private static void close(Statement statement) {
        if (statement != null) {
            try {
                statement.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    private static void close(PreparedStatement statement) {
        if (statement != null) {
            try {
                statement.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    private static void close(ResultSet rs) {
        if (rs != null) {
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

调用存储过程

• 调用存储过程的步骤

  (1)prepareCall(sql)：创建执行存储过程的callableStatement对象。

  (2)CallableStatement：继承自PreparedStatement接口，由方法prepareCall创建，用于调用存储过程。

  (3)执行：和其它对象使用的方法一样 eg:cs.executeUpdate()

  (4)调用存储过程的sql: String sql = “{call 存储过程名字(参数1,参数2....)}”

• 案例

  • 定义存储过程

    
    DROP PROCEDURE IF EXISTS proc_search_user;
    CREATE PROCEDURE proc_search_user
    (IN page_index INT,IN page_size INT, OUT total_count INT, OUT total_page INT)
    BEGIN        
    	DECLARE begin_no INT;        
    	SET begin_no = (page_index-1)*page_size;        
         -- 分页查询列表       
     	SELECT * FROM `user`        
    	ORDER BY id ASC        
    	LIMIT begin_no,page_size;        
         -- 计算数据总数        
    	SELECt COUNT(1) INTO total_count FROM `user`;        
         -- 计算总页数       
    	 SET total_page = FLOOR((total_count + page_size - 1) / page_size);
    END;

  • Navicat工具调用存储过程

    CALL proc_search_user(1,2,@total_count,@total_page);
    select @total_count,@total_page;

  • JDBC调用存储过程

    private static void selectPro() throws ClassNotFoundException, SQLException {
        //1、加载jdbc驱动
        Class.forName("com.mysql.cj.jdbc.Driver");
        //2、获取连接
        //在mysql8.0的数据库 serverTimezone=Asia/shanghai是必须的参数
        String url = "jdbc:mysql://localhost:3306/db1?useUnicode=true&characterEncoding=utf8&useSSL=false&serverTimezone=Asia/Shanghai";
        Connection conn = DriverManager.getConnection(url, "root", "123456");
        //3、创建CallableStatement对象
        CallableStatement callStatement = conn.prepareCall("{CALL proc_search_user(?,?,?,?)}");
    
        // 设置输入参数
        callStatement.setInt(1, 1); // 查询第1页数据
        callStatement.setInt(2, 2); // 每页2条数据
    
        // 注册输出参数
        callStatement.registerOutParameter(3, Types.INTEGER);
        callStatement.registerOutParameter(4, Types.INTEGER);
    
        // 执行调用存储过程，并获取结果集
        ResultSet rs  = callStatement.executeQuery();
        //4、执行sql返回ResultSet
        ResultSet rs = ps.executeQuery();
        if (rs.next()) {
            int id = (Integer) rs.getObject(1);
            String username1 = (String) rs.getObject(2);
            String password1 = (String) rs.getObject(3);
    
            User user = new User();
            user.setId(id);
            user.setUsername(username1);
            user.setPassword(password1);
            System.out.println(user);
        }
    
        //5、关闭资源
        rs.close();
        ps.close();
        conn.close();
    }

本人IDEA完整版

User类与以上User相同

import java.math.BigInteger;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.Scanner;

public class JdbcTest {
    public static void main(String[] args) {
//        login();
//        List users = queryPage(2, 2);
//        List users = queryPage1(2, 2);
//        List users = queryForLike("a");
//        System.out.println(users);

//        Long id = insert();
//        System.out.println(id);

//        boolean updateResult = update();
//        System.out.println("更新是否成功:" + updateResult);

        selectPro();
    }

    //模拟登陆
    private static void login() {
        Scanner sc = new Scanner(System.in);
        System.out.println("请输入用户名:");
        String username = sc.nextLine();
        System.out.println("请输入密码:");
        String password = sc.nextLine();
        User user = query(username, password);
        if (user != null) {
            System.out.println("欢迎" + username + "登陆成功");
        } else {
            System.out.println("用户名或密码不正确！");
        }
    }

    //获取数据
    private static User query(String username, String password) {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            //1、加载驱动
//            Class.forName("com.mysql.cj.jdbc.Driver");
            //2、创建连接
//            //协议：子协议：端口，IP：数据库名称          中文乱码:加密通道
//            String url = "jdbc:mysql://127.0.0.1:3306/ddbb?useUnicode=true&characterEncoding=utf8&useSSL=false&serverTimezone=Asia/Shanghai";
//            conn = DriverManager.getConnection(url, "root", "123456");
            //1、加载驱动，2、创建连接
            conn = DbUtils.getConnection();
            //3、获取PrepareStatement对象，执行sql语句
            //String sql = "select * from `user` where username='"+username+"' and `password`='"+password+"'";
            //sql的问号表示参数占位符
            //查询字段使用别名时，处理结果集的时候，要主要看列名称要看结果集的名称，跟表的列名称没有关系
            String sql = "select * from `user` where username=? and `password`=?";
//            String sql="select id,`username` u,`password` p from user order by id asc limit ?,?";
            ps = conn.prepareStatement(sql);
            //把参数占位符?替换成参数
            ps.setObject(1, username);
            ps.setObject(2, password);
            rs = ps.executeQuery();
            //4、处理结果集
            User user = null;
            if (rs.next()) {
                //根据列的索引获取结果集对应的数据
                Long id = (Long) rs.getObject(1);
                String myUsername = (String) rs.getObject(2);
                String myPassword = (String) rs.getObject(3);
                user = new User(id, myUsername, myPassword);
            }
            return user;
            //5、关闭资源
        } catch (ClassNotFoundException | SQLException e) {
            e.printStackTrace();
        } finally {
            DbUtils.close(conn, ps, rs);
        }
        return null;
    }

    
    private static List queryPage(int pageNum, int pageSize) {
        List users = new ArrayList<>();
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            //1、加载驱动；2、获取连接
            conn = DbUtils.getConnection();
            //3、创建PreparedStatement对象，执行sql语句
            String sql = "select * from `user` order by id asc limit ?,?";
            ps = conn.prepareStatement(sql);
            ps.setObject(1, (pageNum - 1) * pageSize);
            ps.setObject(2, pageSize);
            //4、处理结果集
            rs = ps.executeQuery();
            //循环读取结果集
            while (rs.next()) {//rs.next() 表示结果集的数据处理完了,退出循环
//                Long id = (Long) rs.getObject(1);
//                String username = (String) rs.getObject(2);
//                String password = (String) rs.getObject(3);
                Long id = (Long) rs.getObject("id");
                String username = (String) rs.getObject("username");
                String password = (String) rs.getObject("password");
                //创建User对象
                User user = new User(id, username, password);
                //User对象添加到集合中
                users.add(user);
            }
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            //5、关闭资源
            DbUtils.close(conn, ps, rs);
        }
        return users;
    }

    //分页查询
    public static List queryPage1(int pageNum, int pageSize) {
        List users = new ArrayList<>();
        String sql = "select * from `user` order by id asc limit ?,?";
        Object[] params = {(pageNum - 1) * pageSize, pageSize};
        try {
            List> datas = DbUtils.selectList(sql, params);
            for (Map data : datas) {
                Long id = (Long) data.get("id");
                String username = (String) data.get("username");
                String password = (String) data.get("password");
                User user = new User(id, username, password);
                users.add(user);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return users;
    }

    //模糊查询
    private static List queryForLike(String usernameLike) {
        //假如：usernameLike为a
        String sql = "select * from `user` where username like ?";
        //参数
        Object[] params = {"%" + usernameLike + "%"};
        List users = new ArrayList<>();
        try {
            List> datas = DbUtils.selectList(sql, params);
            for (Map data : datas) {
                Long id = (Long) data.get("id");
                String username = (String) data.get("username");
                String password = (String) data.get("password");
                User user = new User(id, username, password);
                users.add(user);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return users;
    }

    //插入添加
    private static Long insert() {
        //创建user对象
        User user = new User("bxxiao", "85105");
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        Long id = null;
        try {
            //1、加载驱动；2、获取连接
            conn = DbUtils.getConnection();
            //3、获取prepareStatement对象
            String sql = "insert into `user`(`username`,`password`) values(?,?)";
            //第一个参数是sql语句，第二个参数是获取自增主键值
            ps = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
            //把问号替换成参数
            ps.setObject(1, user.getUsername());
            ps.setObject(2, user.getPassword());
            //执行sql,  增,删,改都要使用executeUpdate()
            //int i = ps.executeUpdate();//表示影响行数
            ps.executeUpdate();
            //4、处理结果，获取自增的主键值
            rs = ps.getGeneratedKeys();
            if (rs.next()) {
                // 指定返回生成的主键
                Object objId = rs.getObject(1);
                id = ((BigInteger) objId).longValue();
                System.out.println("新增的主键为：" + objId);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } finally {
            //5、关闭资源
            DbUtils.close(conn, ps, rs);
        }
        return id;
    }

    //修改和删除
    private static boolean update() {
        Connection conn = null;
        PreparedStatement ps = null;
//        ResultSet rs = null;
        try {
            //1、加载驱动；2、获取连接
            conn = DbUtils.getConnection();
            //3、获取prepareStatement对象，执行sql
            String sql = "update `user` set `password`=? where id=?";
            ps = conn.prepareStatement(sql);
            //设置参数
            ps.setObject(1, "232323");
            ps.setObject(2, 3);
            int i = ps.executeUpdate();//执行SQL，获取返回值影响行数
            //4、处理结果
            if (i > 0) {
                //影响行数 > 0,更新成功
                return true;
            } else {
                //影响行数 <= 0,更新失败
                return false;
            }
            //5、关闭资源
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            DbUtils.close(conn, ps);
        }
        return false;
    }

    //调用存储过程
    //存储过程名称proc_search_user
    private static void selectPro() {
        Connection conn = null;
        CallableStatement cs = null;
        ResultSet rs = null;
        try {
            //1、加载驱动；2、获取连接
            conn = DbUtils.getConnection();
            //3、prepareCall创建CallableStatement对象,执行存储过程
            cs = conn.prepareCall("{call proc_search_user(?,?,?,?)}");
            // 设置输入参数
            cs.setInt(1, 1); // 查询第1页数据
            cs.setInt(2, 2); // 每页2条数据
            // 设置存储过程的输出参数
            cs.registerOutParameter(3, Types.INTEGER);
            cs.registerOutParameter(4, Types.INTEGER);
            // 执行调用存储过程，并获取结果集
            rs = cs.executeQuery();
            //4、执行sql返回ResultSet
            while (rs.next()) {
                Long id = (Long) rs.getObject(1);
                String myUsername = (String) rs.getObject(2);
                String myPassword = (String) rs.getObject(3);
                User user = new User(id, myUsername, myPassword);
                System.out.println(user);
            }
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            //5、关闭资源
            DbUtils.close(conn, cs, rs);//cs继承自
        }
    }
}

import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class DbUtils {
    private static String driverName = "com.mysql.cj.jdbc.Driver";
    private static String url = "jdbc:mysql://127.0.0.1:3306/ddbb?useUnicode=true&characterEncoding=utf8&useSSL=false&serverTimezone=Asia/Shanghai";
    private static String username = "root";
    private static String password = "123456";

    
    public static Connection getConnection() throws ClassNotFoundException, SQLException {
        //1、加载驱动
        Class.forName(driverName);
        //2、创建连接
        //协议：子协议：端口，IP：数据库名称          中文乱码:加密通道
        //String url = "jdbc:mysql://127.0.0.1:3306/ddbb?useUnicode=true&characterEncoding=utf8&useSSL=false&serverTimezone=Asia/Shanghai";
        Connection conn = DriverManager.getConnection(url, username, password);
        return conn;
    }

    //设置参数
    private static void setPrepareStatementParams(PreparedStatement ps, Object[] parameters) throws SQLException {
        if (parameters != null && parameters.length > 0) {
            for (int i = 0; i < parameters.length; i++) {
                ps.setObject(i + 1, parameters[i]);
            }
        }
    }

    public static List> selectList(String sql, Object[] parameters) throws SQLException {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        List> list = null;
        try {
            conn = getConnection();
            ps = conn.prepareStatement(sql);
            //设置参数
            setPrepareStatementParams(ps, parameters);
            //4、处理结果集
            rs = ps.executeQuery();
            //获取结果集的元数据，其中包括列名称，列的数量
            ResultSetMetaData metaData = rs.getMetaData();
            //获取列的数量
            int columnCount = metaData.getColumnCount();
            list = new ArrayList<>();
            //循环读取结果集
            while (rs.next()) {
                //rowMap表示每行数据
                Map rowMap = new HashMap<>();
                for (int i = 0; i < columnCount; i++) {
                    //根据列的索引（从1开始）获取列的名称
                    String columnLabelName = metaData.getColumnName(i + 1);
                    //根据列的名称获取列对应的值
                    Object value = rs.getObject(columnLabelName);
                    //每列的名称作为key，每列值作为value存储到Map中
                    rowMap.put(columnLabelName, value);
                }
                list.add(rowMap);
            }
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } finally {
            DbUtils.close(conn, ps, rs);
        }
        return list;
    }

    
    public static void close(Connection conn, PreparedStatement ps, ResultSet rs) {
        try {
            if (rs != null) {
                rs.close();
            }
            if (ps != null) {
                ps.close();
            }
            if (conn != null) {
                conn.close();
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }
    public static void close(Connection conn, PreparedStatement ps) {
        try {
            if (ps != null) {
                ps.close();
            }
            if (conn != null) {
                conn.close();
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }
    public static void close(Connection conn) {
        try {
            if (conn != null) {
                conn.close();
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }
    public static void close(PreparedStatement ps) {
        try {
            if (ps != null) {
                ps.close();
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }
    public static void close(ResultSet rs) {
        try {
            if (rs != null) {
                rs.close();
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }
}