idea JDBC数据连接

超、凢脫俗 2024-03-26 11:31 97阅读 0赞

## 在 pom.xml 添加 mysql 依赖 ##

### 下载依赖包 ###

1.  使用网站 https://cn.bing.com/ 搜索 maven repository
2.  找到这个网站  
    ![在这里插入图片描述][e26d4f64066b43498ae0e0bccba09a9f.png]  
    3.搜索 mysql  
    ![在这里插入图片描述][3b762f1da818435383862994021f6ee3.png]
3.  选择使用人数多的一个  
    ![在这里插入图片描述][7d613e6e22e34845bd4dc5fb00253f35.png]
4.  选择使用人数多的一个版本，我这里选的是 8.0.28 版本  
    ![在这里插入图片描述][6b32d68337c74d6ea4f8c50bf6a2902e.png]
5.  找到这部分直接复制到 pom.xml文件里  
    ![在这里插入图片描述][755be3fb7726428ab30b373d2d6ca190.png]

<dependencies>
            
            <dependency>
                <groupId>mysql</groupId>
                <artifactId>mysql-connector-java</artifactId>
                <version>8.0.28</version> 
            </dependency>
        </dependencies>

## 连接 JDBC ##

![在这里插入图片描述][47f9523de3d045489b959cb7b184e53d.png]

package jdbc;
    
    import java.sql.Connection;
    import java.sql.DriverManager;
    import java.sql.SQLException;
    import java.sql.Statement;
    
    public class JDBCDemo1 {
        
        public static void main(String[] args) throws ClassNotFoundException, SQLException {
        
            /*
                使用JDBC连接数据库的大致步骤
                1. 加载驱动： Class.forName()
                2. 使用 DriverManager 进行数据库连接并得到 Connection 对象（连接对象）
                3. 使用Connection 对象创建执行 SQL 语句的 Statement 对象
                4. 通过 Statement 执行 SQL 语句
             */
            // 1.
            Class.forName("com.mysql.cj.jdbc.Driver");
    
            /*
                2. 使用 DriverManager 进行数据库连接
                   参数1：数据库地址（不同·的数据库地址不一样，但是格式是固定的）
                   参数2：数据库名称
                   参数3：数据库密码
    
                   mysql 的 url 地址格式是固定的：
                   jdbc:mysql://localhost:3306/birddb?characterEncoding=utf8&useSSL=false&serverTimezone=Asia/Shanghai&rewriteBatchedStatements=true
                                               ^^^^^^
                                               数据库名
             */
            Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/birddb?characterEncoding=utf8&useSSL=false&serverTimezone=Asia/Shanghai&rewriteBatchedStatements=true",
                    "root","root");
    
            System.out.println("连接数据库成功！");
    
            // 3. 通过连接对象获取执行 SQL 语句的 Statement 对象
            Statement statement = connection.createStatement();
    
            /*
                 创建一张表 userinfo
             */
            String sql = "CREATE TABLE userinfo( " +
                    "         id INT PRIMARY KEY AUTO_INCREMENT, " +
                    "         username VARCHAR(30), " +
                    "         password VARCHAR(30), " +
                    "         nickname VARCHAR(30), " +
                    "         age INT(3) " +
                    ")";
    
            /*
                boolean execute(String sql)
                该方法可以执行任意种类的 sql 语句。但是 DML 和 DQL 有专门的方法，隐藏 execute 方法通常用来执行 DDL 语句
                该方法返回值表达执行这条 sql 后是否有结果集    
             */
            statement.execute(sql);   // 执行 sql 语句
    
            System.out.println("执行完毕！");
            connection.close();     // 关闭连接
        }
    }

### 执行 DML(插入、修改、删除) 语句 ###

package jdbc;
    
    import java.sql.Connection;
    import java.sql.DriverManager;
    import java.sql.SQLException;
    import java.sql.Statement;
    import java.util.Scanner;
    
    /**
     * 执行 DML 语句
     */
    public class JDBCDemo2 {
        
        public static void main(String[] args) throws ClassNotFoundException, SQLException {
        
            Class.forName("com.mysql.cj.jdbc.Driver");
            Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/birddb?characterEncoding=utf8&useSSL=false&serverTimezone=Asia/Shanghai&rewriteBatchedStatements=true",
                    "root","root");
            Statement statement = connection.createStatement();
    
            Scanner scanner = new Scanner(System.in);
            System.out.println("请输入用户名");
            String username = scanner.nextLine();
            System.out.println("请输入密码");
            String password = scanner.nextLine();
            System.out.println("请输入昵称");
            String nickname = scanner.nextLine();
            System.out.println("请输入年龄");
            int age = scanner.nextInt();
    
            String sql = "INSERT INTO userinfo (username, password, nickname, age) " +
                    "     VALUES ('" + username + "', '" + password + "', '" + nickname + "', " + age + " ) ";
    
            /*
                int executeUpdate(String sql)
                是专门用于执行 DML 语句的，返回的 int 值表达执行该 DML 操作影响了表中多少条记录
             */
            int num = statement.executeUpdate(sql);
    
            if (num > 0) {
        
                System.out.println("插入成功");
            } else {
        
                System.out.println("插入失败");
            }
    
            connection.close();
        }
    }

### 修改密码 ###

package jdbc;
    
    import java.sql.Connection;
    import java.sql.DriverManager;
    import java.sql.SQLException;
    import java.sql.Statement;
    import java.util.Scanner;
    
    /**
     * 修改密码功能
     * 程序启动后要求输入用户名和新密码
     * 然后将 userinfo 表中该用户名对应的记录中密码修改
     */
    public class Test1 {
        
        public static void main(String[] args) throws ClassNotFoundException, SQLException {
        
            Scanner scanner = new Scanner(System.in);
            System.out.println("请输入用户名");
            String username = scanner.nextLine();
            System.out.println("请输入密码");
            String password = scanner.nextLine();
    
            Class.forName("com.mysql.cj.jdbc.Driver");
    
            Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/birddb?characterEncoding=utf8&useSSL=false&serverTimezone=Asia/Shanghai&rewriteBatchedStatements=true",
                    "root", "root");
            Statement statement = connection.createStatement();
    
            String sql = "UPDATE userinfo " +
                         " SET password = '" + password + "'" +
                         "WHERE username = '" + username + "'";
    
            int num = statement.executeUpdate(sql);
    
            if (num > 0) {
        
                System.out.println("密码修改成功");
            } else {
        
                System.out.println("密码修改失败");
            }
        }
    }

### 删除用户 ###

package jdbc;
    
    import java.sql.Connection;
    import java.sql.DriverManager;
    import java.sql.SQLException;
    import java.sql.Statement;
    import java.util.Scanner;
    
    /**
     * 删除用户
     * 程序启动后，要求在控制台输入要删除的用户的用户名
     * 执行 sql 语句将 userinfo 表中该用户进行删除
     */
    public class Test2 {
        
        public static void main(String[] args) throws ClassNotFoundException, SQLException {
        
            Scanner scanner = new Scanner(System.in);
            System.out.println("请输入用户名");
            String username = scanner.nextLine();
    
            Class.forName("com.mysql.cj.jdbc.Driver");
    
            Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/birddb?characterEncoding=utf8&useSSL=false&serverTimezone=Asia/Shanghai&rewriteBatchedStatements=true",
                    "root", "root");
    
            Statement statement = connection.createStatement();
    
            String sql = "DELETE  FROM userinfo WHERE username = '" + username + "'";
    
            int num = statement.executeUpdate(sql);
    
            if (num > 0) {
        
                System.out.println("删除成功");
            } else {
        
                System.out.println("删除失败");
            }
        }
    }

### 查询语句 ###

package jdbc;
    
    import java.sql.Connection;
    import java.sql.ResultSet;
    import java.sql.SQLException;
    import java.sql.Statement;
    
    /**
     * 执行 DQL(查询语句) 语句
     */
    public class JDBCDemo3 {
        
        public static void main(String[] args) {
        
            // 查看每位老师的 id, 姓名，性别，工资
            try (
                    Connection connection = DBUtil.getConnection();
            ) {
        
                Statement statement = connection.createStatement();
    
               String sql = "select id, name, gender, salary " +
                       " from teacher";
    
               /*
                   ResultSet executeQuery(String sql)
                   用于执行 DQL 语句，返回的值为查询的结果集
                */
               ResultSet set = statement.executeQuery(sql);
               while (set.next()) {
        
                   int id = set.getInt(1);  //  获取该记录中第一个字段的值（int 类型）
                   // int id = set.getInt("id");
                   String name = set.getString("name");
                   String gender = set.getString("gender");
                   int salary = set.getInt("salary");
                   System.out.println(name + ", " + gender + ", " + salary);
               }
            } catch (SQLException throwables) {
        
                throwables.printStackTrace();
            }
        }
    }

### 关联查询 ###

package jdbc;
    
    import java.sql.Connection;
    import java.sql.ResultSet;
    import java.sql.SQLException;
    import java.sql.Statement;
    
    /**
     * 关联查询
     */
    public class JDBCDemo4 {
        
        public static void main(String[] args) {
        
            try (
                    Connection connection = DBUtil.getConnection();
            ) {
        
                Statement statement = connection.createStatement();
    
                // 查看刘苍松所带班级的学生信息（学生的姓名，性别，年龄，班级，班主任）
                String sql = "SELECT s.name, s.gender, s.age, c.name, t.name " +
                        "     FROM student s " +
                        "     JOIN class c ON s.class_id = c.id " +
                        "     JOIN teacher t ON c.teacher_id = t.id " +
                        "     WHERE t.name = '刘苍松'";
    
                ResultSet rs = statement.executeQuery(sql);
                while (rs.next()) {
        
                    String sname = rs.getString("s.name");
                    String sgender = rs.getString("s.gender");
                    int sage = rs.getInt("s.age");
                    String cname = rs.getString("c.name");
                    String tname = rs.getString("t.name");
    
                    System.out.println(sname + ", " + sgender + ", " + sage + ", " + cname + ", " + tname);
                }
            } catch (SQLException throwables) {
        
                throwables.printStackTrace();
            }
        }
    }

### 别名 ###

package jdbc;
    
    import java.sql.Connection;
    import java.sql.ResultSet;
    import java.sql.SQLException;
    import java.sql.Statement;
    
    /**
     * 别名
     */
    public class JDBCDemo5 {
        
        public static void main(String[] args) {
        
            try(
                    Connection connection = DBUtil.getConnection();
            ) {
        
                Statement statement = connection.createStatement();
    
                // 统计刘苍松老师所带班级的学生人数
                String sql = "SELECT COUNT(*) count " +
                        "     FROM student s " +
                        "     JOIN class c ON s.class_id = c.id " +
                        "     JOIN teacher t ON c.teacher_id = t.id " +
                        "     WHERE t.name = '刘苍松'";
    
                ResultSet rs = statement.executeQuery(sql);
                if (rs.next()) {
        
                    int count = rs.getInt("count");
                    System.out.println("人数：" + count);
                }
            } catch (SQLException throwables) {
        
                throwables.printStackTrace();
            }
        }
    }

### SQL 预编译，防止注入攻击 ###

package jdbc;
    
    import java.sql.Connection;
    import java.sql.ResultSet;
    import java.sql.SQLException;
    import java.sql.Statement;
    import java.util.Scanner;
    
    /**
     * 登录
     */
    public class JDBCDemo6 {
        
        public static void main(String[] args) {
        
            Scanner scanner = new Scanner(System.in);
            System.out.println("请输入用户名");
            String username = scanner.nextLine();
            System.out.println("请输入密码");
            String password = scanner.nextLine();
    
            try(
                    Connection connection = DBUtil.getConnection();
            ) {
        
                /*
                    使用 Statement 执行的 SQL 应当知识静态 SQL ，不应当含有拼接的用户输入内容，否则存在 SQL 注入攻击
                    例如，用户输入的密码为： a' OR '1' = '1
                 */
                Statement statement = connection.createStatement();
                String sql = "SELECT id, username, password, nickname, age " +
                        "     FROM userinfo " +
                        "     WHERE username = '" + username + "' " + "AND password = '" + password + "'" ;
                ResultSet rs = statement.executeQuery(sql);
                if (rs.next()) {
        
                    System.out.println("登录成功！");
                } else {
        
                    System.out.println("登录失败！");
                }
            } catch (SQLException throwables) {
        
                throwables.printStackTrace();
            }
        }
    }

package jdbc;
    
    import java.sql.Connection;
    import java.sql.PreparedStatement;
    import java.sql.ResultSet;
    import java.sql.SQLException;
    
    /**
     * 执行的 SQL 语句中含有用户输入的信息时，应当使用预编译 SQL
     */
    public class JDBCDemo7 {
        
        public static void main(String[] args) {
        
            try(
                    Connection connection = DBUtil.getConnection();
            ) {
        
                /*
                    在预编译 SQL 中， 会变化的数据可以先用“？”占位，注意：“？”只能表示值
                 */
                String sql = "SELECT id, username, password, nickname, age " +
                        "     FROM userinfo " +
                        "     WHERE username = ? AND password = ?" ;
                // 现将预编译 SQL 发送给数据库，使其理解语义，但是还无法真正执行，因为缺少 ？ 对应的值
                PreparedStatement ps = connection.prepareStatement(sql);
                ps.setString(1, "范传奇");
                ps.setString(2, "123456");
                ResultSet rs = ps.executeQuery();   // 3无需再传入 sql
                if (rs.next()) {
        
                    System.out.println("登录成功！");
                } else {
        
                    System.out.println("登录失败！");
                }
            } catch (SQLException throwables) {
        
                throwables.printStackTrace();
            }
        }
    }

### 数据库连接池 ###

1.  在 pom.xml 里添加

<dependency>
                <groupId>com.alibaba</groupId>
                <artifactId>druid</artifactId>
                <version>1.1.21</version>
            </dependency>

[e26d4f64066b43498ae0e0bccba09a9f.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/26/9d3a876dd8ce4822b2c977999740a426.png
[3b762f1da818435383862994021f6ee3.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/26/ad38afad7a274e83a5fff9211124ba08.png
[7d613e6e22e34845bd4dc5fb00253f35.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/26/777c5831562d4eff828de6fd5a539662.png
[6b32d68337c74d6ea4f8c50bf6a2902e.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/26/1e376abbdc6d40838515136ec740a963.png
[755be3fb7726428ab30b373d2d6ca190.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/26/7a56b1a6ebd44dde8b926162d84541c3.png
[47f9523de3d045489b959cb7b184e53d.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/26/8d9ddb8950c24a07b99750d80a09c3e9.png