JDBC 快速入门

使用步骤

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
//0. 创建工程，导入驱动jar包
mysql-connector-j-8.0.31.jar
//1. 注册驱动
Class.forName("com.mysql.cj.jdbc.Driver");
//2.获取连接
Connection conn = DriverManager.getConnection(url, username, password);
//3.定义SQL语句
String sql = "update···";
//4.执行SQL语句
Statement stmt = conn.createStatement();
//5.执行SQL
stmt.executeUpdate(sql);
//6.处理结果
System.out.println(count);
//7.释放资源
stmt.close();//先关闭内部的statement
conn.close();//在关闭最先的Connection

更新用例

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
package com.itheima.jdbc;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;

/**
 * JDBC快速入门
 */
public class JDBCDemo {
    public static void main(String[] args) throws Exception{
        //1.注册驱动(myql8.0版本的驱动需要增加.cj) —— Mysql5之后的驱动jar包以下这行代码可以不写！
        Class.forName("com.mysql.cj.jdbc.Driver");
        //2.获取连接
        String url = "jdbc:mysql://127.0.0.1:3306/itheima";
        String username = "root";
        String password = "123456";
        Connection conn = DriverManager.getConnection(url, username, password);
        //3.定义sql语句
        String sql = "update emp set salary = 5000 where id = 6;";
        //4.获取执行sql的对象。
        Statement stmt = conn.createStatement();
        //5.执行sql(受影响行数的返回值）
        int count = stmt.executeUpdate(sql);
        //6.处理结果
        System.out.println(count);
        //7. 释放资源
        stmt.close();
        conn.close();

    }
}

查询用例

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
package com.itheima.jdbc;

import java.sql.*;

public class JDBCDemo {
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        //1.注册驱动 —— Mysql5之后的驱动jar包以下这行代码可以不写！
        //Class.forName("com.mysql.cj.jdbc.Driver");
        //2.获取连接
        String url = "jdbc:mysql://127.0.0.1:3306/itheima";
        String username = "root";
        String password = "123456";
        Connection conn = DriverManager.getConnection(url, username, password);
        //3.定义sql语句
        String sql = "select * from emp;";
        //String sql2 = "update emp set salary = 5000 where id = 6;";
        //4.获取执行SQL对象
        Statement stmt = conn.createStatement();
        //5.执行SQL
//        int count = stmt.executeUpdate(sql);
        ResultSet res = stmt.executeQuery(sql);
        //6. 处理返回结果
//        System.out.println(count);
        while (res.next()){
            int id = res.getInt("id");
            String name = res.getString("name");
            System.out.println("id: " + id);
            System.out.println("name: " + name);
        }
        //7.释放资源
        res.close();
        stmt.close();
        conn.close();
    }
}

image-20221116160546204

简化版本

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
package com.itheima.jdbc;

import java.sql.*;

public class JDBCDemo2_DriverManager {
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        //1.注册驱动 —— Mysql5之后的驱动jar包以下这行代码可以不写！
        //Class.forName("com.mysql.cj.jdbc.Driver");
        //2.获取连接(如果连接的是本地数据库，则IP和端口可以简写///,?useSSL=false可以去掉安全提示)
        String url = "jdbc:mysql:///itheima?useSSL=false";
        String username = "root";
        String password = "123456";
        Connection conn = DriverManager.getConnection(url, username, password);
        //3.定义sql语句
        String sql = "select * from emp;";
        String sql2 = "update emp set salary = 5000 where id = 6;";
        //4.获取执行SQL对象
        Statement stmt = conn.createStatement();
        //5.执行SQL
//        int count = stmt.executeUpdate(sql);
        ResultSet res = stmt.executeQuery(sql);
        //6. 处理返回结果
//        System.out.println(count);
        while (res.next()){
            int id = res.getInt("id");
            String name = res.getString("name");
            System.out.println("id: " + id);
            System.out.println("name: " + name);
        }
        //7.释放资源
        stmt.close();
        conn.close();

    }
}

事务用例

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
package com.itheima.jdbc;

import java.sql.*;

public class JDBCDemo2_DriverManager {
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        //1.注册驱动
        //Class.forName("com.mysql.cj.jdbc.Driver");
        //2.获取连接(如果连接的是本地数据库，则IP和端口可以简写///,?useSSL=false可以去掉安全提示)
        String url = "jdbc:mysql:///itheima?useSSL=false";
        String username = "root";
        String password = "123456";
        Connection conn = DriverManager.getConnection(url, username, password);
        //3.定义sql语句
        String sql1 = "update emp set salary = 1000 where id = 6;";
        String sql2 = "update emp set salary = 1000 where id = 5;";
        //4.获取执行SQL对象
        Statement stmt = conn.createStatement();

        //5.执行SQL
        try {
            //开启事务
            conn.setAutoCommit(false);
            int count1 = stmt.executeUpdate(sql1);
            //6. 处理返回结果
            System.out.println(count1);

            //插入异常，第二个发生异常，事务回滚，并没有发生数据修改(如果关闭事务相关内容，则第一个数据会发生变化，第二个则不会)
            int i = 3 / 0;

            int count2 = stmt.executeUpdate(sql2);
            System.out.println(count1);
            //提交事务
            conn.commit();
        } catch (Exception e) {
            //出现异常，会滚事务（java在try···catch···里面的catch里面进行事务的回滚）
            conn.rollback();
            throw new RuntimeException(e);
        }

        //要求：让这两个语句要么都修改成功，要么都修改失败，则需要事务。


        //7.释放资源
        stmt.close();
        conn.close();

    }
}

单元测试

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
package com.itheima.jdbc;

import org.testng.annotations.Test;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;

public class JDBCDemo4_Statement {
    /**
     * 执行DML语句
     *
     */
    @Test
    public void testDML() throws SQLException {
        //1.注册驱动
        //Class.forName("com.mysql.cj.jdbc.Driver");
        //2.获取连接(如果连接的是本地数据库，则IP和端口可以简写///,?useSSL=false可以去掉安全提示)
        String url = "jdbc:mysql:///itheima?useSSL=false";
        String username = "root";
        String password = "123456";
        Connection conn = DriverManager.getConnection(url, username, password);
        //3.定义sql语句
        String sql1 = "update emp set salary = 8000 where id = 6;";
        String sql2 = "update emp set salary = 10000 where id = 5;";
        //4.获取执行SQL对象
        Statement stmt = conn.createStatement();

        //5.执行SQL
        try {
            //开启事务
            conn.setAutoCommit(false);
            //执行完DML语句后，受影响的行数
            int count1 = stmt.executeUpdate(sql1);
            //6. 处理返回结果
            System.out.println(count1);

            int count2 = stmt.executeUpdate(sql2);
            System.out.println(count1);
            //提交事务
            conn.commit();
        } catch (Exception e) {
            //出现异常，会滚事务（java在try···catch···里面的catch里面进行事务的回滚）
            conn.rollback();
            throw new RuntimeException(e);
        }

        //7.释放资源
        stmt.close();
        conn.close();
    }
}

DDL 测试

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
package com.itheima.jdbc;

import org.testng.annotations.Test;

import java.sql.*;

public class JDBCDemo_DDL {
    @Test
    public void TestDDL() throws SQLException {
        //2.获取连接
        String url = "jdbc:mysql://localhost:3306/itheima";
        String username = "root";
        String password = "123456";
        Connection conn = DriverManager.getConnection(url, username, password);
        //3.定义sql语句
        String sql = "drop database db2";
        //4.获取执行SQL对象
        Statement stmt = conn.createStatement();
        //5.执行SQL (执行DML语句)
        int count = stmt.executeUpdate(sql);
        //不能关键count进行判断，操作成功后，返回的可能是0
//        if(count > 0){
//            System.out.println("修改成功！");
//        }else {
//            System.out.println("修改失败！");
//        }
        System.out.println(count);
        //6. 处理返回结果
        System.out.println(count);
        //7.释放资源
        stmt.close();
        conn.close();
    }
}

查询用例

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
package com.itheima.jdbc;

import org.testng.annotations.Test;

import java.sql.*;

public class JDBCDemo_ResultSet {
    @Test
    public void TestDDL() throws SQLException {
        //1.获取连接
        String url = "jdbc:mysql://localhost:3306/itheima";
        String username = "root";
        String password = "123456";
        Connection conn = DriverManager.getConnection(url, username, password);

        //2.定义sql
        String sql = "select * from emp;";
        //3.获取执行对象
        Statement stmt = conn.createStatement();
        //4.执行sql
        ResultSet res = stmt.executeQuery(sql);
        //5.处理结果
        //光标向下移动一行，并且判断当前行是否有数据
//        while(res.next()){
         	  //使用列号获取(注意：列号是从1开始)
//            int id = res.getInt(1);
//            String name = res.getString(2);
//            int age = res.getInt(3);
//
//            System.out.println(id);
//            System.out.println(name);
//            System.out.println(age);
//
//            System.out.println("-----------------");
//        }
        while(res.next()){
            //使用名称获取
            int id = res.getInt("id");
            String name = res.getString("name");
            int age = res.getInt("age");

            System.out.println(id);
            System.out.println(name);
            System.out.println(age);

            System.out.println("-----------------");
        }
        //6.释放资源
        res.close();
        stmt.close();
        conn.close();
    }
}

ResultSet案例

需求：查询emp表数据，并将数据封装为empees对象，以及存储至ArrayList集合中

image-20221116171929954

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
package com.itheima.pojo;

import java.util.Date;

//员工类
public class Empees {
    private int id;
    private String name;
    private int age;
    private String job;
    private int salary;
    private Date entryDate;
    private int managerId;
    private int deptId;

    public int getId() {
        return id;
    }

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

    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 getJob() {
        return job;
    }

    public void setJob(String job) {
        this.job = job;
    }

    public int getSalary() {
        return salary;
    }

    public void setSalary(int salary) {
        this.salary = salary;
    }

    public Date getEntryDate() {
        return entryDate;
    }

    public void setEntryDate(Date entryDate) {
        this.entryDate = entryDate;
    }

    public int getManagerId() {
        return managerId;
    }

    public void setManagerId(int managerId) {
        this.managerId = managerId;
    }

    public int getDeptId() {
        return deptId;
    }

    public void setDeptId(int deptId) {
        this.deptId = deptId;
    }

    @Override
    public String toString() {
        return "Empees{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", age=" + age +
                ", job='" + job + '\'' +
                ", salary=" + salary +
                ", entryDate=" + entryDate +
                ", managerId=" + managerId +
                ", deptId=" + deptId +
                '}';
    }
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
package com.itheima.jdbc;

import com.itheima.pojo.Empees;
import org.testng.annotations.Test;

import java.sql.*;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

public class JDBCDemo_ResultSet {
    @Test
    public void TestDDL() throws SQLException {
        //1.获取连接
        String url = "jdbc:mysql://localhost:3306/itheima";
        String username = "root";
        String password = "123456";
        Connection conn = DriverManager.getConnection(url, username, password);

        //2.定义sql
        String sql = "select * from emp;";
        //3.获取执行对象
        Statement stmt = conn.createStatement();
        //4.执行sql
        ResultSet res = stmt.executeQuery(sql);

        //创建集合
        List<Empees> list = new ArrayList<>();

        //5.处理结果
        //光标向下移动一行，并且判断当前行是否有数据
        while(res.next()){
            Empees empees = new Empees();
            int id = res.getInt("id");
            String name = res.getString("name");
            int age = res.getInt("age");
            String job = res.getString("job");
            int salary = res.getInt("salary");
            Date entryDate = res.getDate("entrydate");
            int managerId = res.getInt("managerid");
            int deptId = res.getInt("dept_id");

            empees.setId(id);
            empees.setName(name);
            empees.setAge(age);
            empees.setJob(job);
            empees.setSalary(salary);
            empees.setEntryDate(entryDate);
            empees.setManagerId(managerId);
            empees.setDeptId(deptId);

            //存入集合
            list.add(empees);
//            System.out.println(id);
//            System.out.println(name);
//            System.out.println(age);
//            System.out.println(job);
//            System.out.println(salary);
//            System.out.println(entryDate);
//            System.out.println(managerId);
//            System.out.println(deptId);
//            System.out.println("-----------------");
        }
        System.out.println(list);
        //6.释放资源
        res.close();
        stmt.close();
        conn.close();
    }
}

image-20221116185245683

SQL 注入

问题描述：一般来说只有输入正确的用户名和密码才能够进入系统，当用户名或密码输入不正确的时候，是不能够进入系统的。但是在某些情况下，在用户随便输入用户名，并且输入一些特定的密码内容后， 却是可以进入系统的！

比如：当用户随便输入用户名之后，在密码输入框输入 ‘ or ‘1’= ‘1 那么用户就可以进入系统了！为什么呢？因为处理过后的后台的查询密码变成如下形式：

1
select * from tb_user where username = 'zhangsadasfasfn' and password = '' or '1'= '1'

如何防止这种非法渗透呢？可以使用 PreparedStatement ！

image-20221116195221091

用户表：

image-20221116192103497

原始代码：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
package com.itheima.jdbc;

import org.testng.annotations.Test;

import java.sql.*;

public class JDBCDemo_UserLogin {
    @Test
    public void TestLogin() throws SQLException {
        //1.获取连接
        String url = "jdbc:mysql://localhost:3306/itheima";
        String username = "root";
        String password = "123456";
        Connection conn = DriverManager.getConnection(url, username, password);

        //接收用户输入用户名和密码
        String name = "zhangsan";
        String pwd = "123";

        String sql = "select * from tb_user where username = '"+name+"' and password = '"+pwd+"'";

        //获取stmt对象
        Statement stmt = conn.createStatement();
        //执行sql
        ResultSet res = stmt.executeQuery(sql);

        //判断登录是否成功
        if(res.next()){
            System.out.println("登录成功!");
        }else{
            System.out.println("登录失败!");
        }

        //6.释放资源
        res.close();
        stmt.close();
        conn.close();
    }

    /**
     * 演示SQL注入
     * @throws SQLException
     */
    @Test
    public void TestLogin_Inject() throws SQLException {
        //1.获取连接
        String url = "jdbc:mysql://localhost:3306/itheima";
        String username = "root";
        String password = "123456";
        Connection conn = DriverManager.getConnection(url, username, password);

        //接收用户输入用户名和密码
        String name = "zhangsadasfasfn";
        String pwd = "' or '1'= '1";

        String sql = "select * from tb_user where username = '"+name+"' and password = '"+pwd+"'";
        //打印一下sql语句
        System.out.println(sql);

        //获取stmt对象
        Statement stmt = conn.createStatement();
        //执行sql
        ResultSet res = stmt.executeQuery(sql);

        //判断登录是否成功
        if(res.next()){
            System.out.println("登录成功!");
        }else{
            System.out.println("登录失败!");
        }

        //6.释放资源
        res.close();
        stmt.close();
        conn.close();
    }
}

SQL注入代码

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
package com.itheima.jdbc;

import org.testng.annotations.Test;

import java.sql.*;

public class JDBCDemo_PreparedStatement {
    /**
     * 演示SQL注入
     * @throws SQLException
     */
    @Test
    public void testPreparedStatement() throws SQLException {
        //1.获取连接
        String url = "jdbc:mysql://localhost:3306/itheima";
        String username = "root";
        String password = "123456";
        Connection conn = DriverManager.getConnection(url, username, password);

        //接收用户输入用户名和密码
        String name = "zhangsadasfasfn";
        String pwd = "123";

        String sql = "select * from tb_user where username = ? and password = ?";

        //获取stmt对象
        PreparedStatement pstmt = conn.prepareStatement(sql);
        pstmt.setString(1, name);
        pstmt.setString(2, pwd);
        //执行sql(不用传参sql参数了！)
        ResultSet res = pstmt.executeQuery();

        //判断登录是否成功
        if(res.next()){
            System.out.println("登录成功!");
        }else{
            System.out.println("登录失败!");
        }

        //6.释放资源
        res.close();
        pstmt.close();
        conn.close();
    }
}

image-20221116200347291

更换正确的登录密码之后，即可登录成功！

image-20221116200529716

思考：PreparedStatement 是如何做到的呢? 将敏感词汇进行转义！

1
' or '1'= '1 => \' or \'1\'= \'1 (转义后即当做文本数据来看了！当然，还会判断关键字啥的，也会进行转义等)

image-20221116201054421

Druid 连接池

获取当前程序路径：

1
System.out.println(System.getProperty("user.dir"));

druid.properties

1
2
3
4
5
6
7
8
9
10
driverClassName=com.mysql.cj.jdbc.Driver
url=jdbc:mysql:///itheima?useSSL=false&useServerPrepStmts=true
username=root
password=123456
# ????????
initialSize=5
# ??????
maxActive=10
# ??????
maxWait=3000
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
package com.itheima.druid;

import com.alibaba.druid.pool.DruidDataSourceFactory;

import javax.sql.DataSource;
import java.io.FileInputStream;
import java.sql.Connection;
import java.util.Properties;

/**
 * Druid 数据库连接池演示
 */
public class DruidDemo {
    public static void main(String[] args) throws Exception {
        //1.导入jar包
        //2.定义配置文件
        //3.加载配置文件
        Properties prop = new Properties();
        prop.load(new FileInputStream("D:\\Codes\\JAVA\\JavaWeb\\JavaWeb\\jdbc-demo\\src\\druid.properties"));
        //4.获取连接池对象
        DataSource dataSource = DruidDataSourceFactory.createDataSource(prop);
        //5.获取数据库链接 Connection
        Connection connection = dataSource.getConnection();
        System.out.println(connection);
    }
}

通用模板：

image-20221116215138004

商品小练习

需求：完成商品品牌数据的增删改查操作

  • 查询：查询所有数据
  • 添加：添加数据
  • 修改：根据 id 修改
  • 删除：根据 id 删除

商品信息：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
package com.itheima.pojo;

/**
 * 品牌
 * alt + 鼠标左键 —— 整列编辑
 * 在实体类当中，基本数据类型建议使用其对应的包装类型
 */
public class Brand {
    //id 主键
    private Integer id;
    //品牌名称
    private String brandName;
    //企业名称
    private String companyName;
    //排序字段
    private Integer ordered;
    //描述信息
    private String description;
    //状态：0 禁String 用 1 启用
    //对象也是有默认值的，但是Integer的默认值是null，不会对我们当下的业务产生影响
    private Integer status;

    public Integer getId() {
        return id;
    }

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

    public String getBrandName() {
        return brandName;
    }

    public void setBrandName(String brandName) {
        this.brandName = brandName;
    }

    public String getCompanyName() {
        return companyName;
    }

    public void setCompanyName(String companyName) {
        this.companyName = companyName;
    }

    public Integer getOrdered() {
        return ordered;
    }

    public void setOrdered(Integer ordered) {
        this.ordered = ordered;
    }

    public String getDescription() {
        return description;
    }

    public void setDescription(String description) {
        this.description = description;
    }

    public Integer getStatus() {
        return status;
    }

    public void setStatus(Integer status) {
        this.status = status;
    }

    @Override
    public String toString() {
        return "Brand{" +
                "id=" + id +
                ", brandName='" + brandName + '\'' +
                ", companyName='" + companyName + '\'' +
                ", ordered=" + ordered +
                ", description='" + description + '\'' +
                ", status=" + status +
                '}';
    }
}

查询功能：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
package com.itheima.example;

import com.alibaba.druid.pool.DruidDataSourceFactory;
import com.itheima.pojo.Brand;
import org.testng.annotations.Test;

import javax.sql.DataSource;
import java.io.FileInputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;

/**
 * 品牌数据的增删改查操作
 */
public class BrandTest {
    /**
     * 查询所有
     * 1. SQL：select * from tb_brand
     * 2. 参数：不需要
     * 3. 结果：List<Brand>
     */
    @Test
    public void testSelectAll() throws Exception {
        //1.获取Connection对象
        //2. 设置配置信息
        //3.加载配置文件
        Properties prop = new Properties();
        prop.load(new FileInputStream("D:\\Codes\\JAVA\\JavaWeb\\JavaWeb\\jdbc-demo\\src\\druid.properties"));
        //4.获取连接池对象
        DataSource dataSource = DruidDataSourceFactory.createDataSource(prop);
        //5.获取数据库链接 Connection
        Connection conn = dataSource.getConnection();

        //2.定义SQL
        String sql = "select * from tb_brand;";

        //3.获取pstml对象
        PreparedStatement pstmt = conn.prepareStatement(sql);

        //4.设置参数
        //5.执行SQL
        ResultSet res = pstmt.executeQuery();

        //6.处理结果 List<Brand> -> 封装Brand对象，装载到List集合
        Brand brand = null;
        List<Brand> brands = new ArrayList<>();
        while (res.next()){
            //获取数据
            int id = res.getInt("id");
            String brandName = res.getString("brand_name");
            String companyName = res.getString("company_name");
            int ordered = res.getInt("ordered");
            String description = res.getString("description");
            int status = res.getInt("status");

            //封装对象
            brand = new Brand();
            brand.setId(id);
            brand.setBrandName(brandName);
            brand.setCompanyName(companyName);
            brand.setOrdered(ordered);
            brand.setDescription(description);
            brand.setStatus(status);
            //装载集合
            brands.add(brand);
        }

        System.out.println(brands);

        //7.释放资源
        res.close();
        pstmt.close();
        conn.close();
    }
}

添加功能**

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
package com.itheima.example;

import com.alibaba.druid.pool.DruidDataSourceFactory;
import com.itheima.pojo.Brand;
import org.testng.annotations.Test;

import javax.sql.DataSource;
import java.io.FileInputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;

/**
 * 品牌数据的增删改查操作
 */
public class BrandTest {
    /**
     * 添加
     * 1. SQL：insert into tb_brand···
     * 2. 参数：需要(除id外的所有参数)
     * 3. 结果：Boolean
     */
    @Test
    public void testAdd() throws Exception {
        //模拟：接收页面提交的参数
        String brandName = "香飘飘";
        String companyName = "香飘飘";
        int ordered = 1;
        String description = "绕地球一圈";
        int status = 1;

        //1.获取Connection对象
        //2. 设置配置信息
        //3.加载配置文件
        Properties prop = new Properties();
        prop.load(new FileInputStream("D:\\Codes\\JAVA\\JavaWeb\\JavaWeb\\jdbc-demo\\src\\druid.properties"));
        //4.获取连接池对象
        DataSource dataSource = DruidDataSourceFactory.createDataSource(prop);
        //5.获取数据库链接 Connection
        Connection conn = dataSource.getConnection();

        //2.定义SQL
        String sql = "insert into tb_brand(brand_name, company_name, ordered, description, status) values (?, ?, ?, ?, ?);";

        //3.获取pstml对象
        PreparedStatement pstmt = conn.prepareStatement(sql);

        //4.设置参数
        pstmt.setString(1, brandName);
        pstmt.setString(2, companyName);
        pstmt.setInt(3, ordered);
        pstmt.setString(4, description);
        pstmt.setInt(5, status);
        //5.执行SQL
        int count = pstmt.executeUpdate();

        //6.处理结果
        System.out.println(count > 0);

        //7.释放资源
        pstmt.close();
        conn.close();
    }
}

image-20221116223408335

更新功能**

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
package com.itheima.example;

import com.alibaba.druid.pool.DruidDataSourceFactory;
import com.itheima.pojo.Brand;
import org.testng.annotations.Test;

import javax.sql.DataSource;
import java.io.FileInputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;

/**
 * 品牌数据的增删改查操作
 */
public class BrandTest {
    /**
     * 修改
     * 1. SQL：update tb_brand set brand_name=?, company_name=?, ordered=?, description=?, status=? where id=?;
     * 2. 参数：需要(所有数据)
     * 3. 结果：Boolean
     */
    @Test
    public void testUpdate() throws Exception {
        //模拟：接收页面提交的参数
        String brandName = "香飘飘奶茶";
        String companyName = "香飘飘奶茶";
        int ordered = 10;
        String description = "绕地球3圈";
        int status = 1;
        int id = 4;

        //1.获取Connection对象
        //2. 设置配置信息
        //3.加载配置文件
        Properties prop = new Properties();
        prop.load(new FileInputStream("D:\\Codes\\JAVA\\JavaWeb\\JavaWeb\\jdbc-demo\\src\\druid.properties"));
        //4.获取连接池对象
        DataSource dataSource = DruidDataSourceFactory.createDataSource(prop);
        //5.获取数据库链接 Connection
        Connection conn = dataSource.getConnection();

        //2.定义SQL
        String sql = "update tb_brand set brand_name=?, company_name=?, ordered=?, description=?, status=? where id=?;";
        //3.获取pstml对象
        PreparedStatement pstmt = conn.prepareStatement(sql);

        //4.设置参数
        pstmt.setString(1, brandName);
        pstmt.setString(2, companyName);
        pstmt.setInt(3, ordered);
        pstmt.setString(4, description);
        pstmt.setInt(5, status);
        pstmt.setInt(6, id);
        //5.执行SQL
        int count = pstmt.executeUpdate();

        //6.处理结果
        System.out.println(count > 0);

        //7.释放资源
        pstmt.close();
        conn.close();
    }
}

image-20221116223448440

删除功能

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
package com.itheima.example;

import com.alibaba.druid.pool.DruidDataSourceFactory;
import com.itheima.pojo.Brand;
import org.testng.annotations.Test;

import javax.sql.DataSource;
import java.io.FileInputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;

/**
 * 品牌数据的增删改查操作
 */
public class BrandTest {
    /**
     * 查询所有
     * 1. SQL：select * from tb_brand
     * 2. 参数：不需要
     * 3. 结果：List<Brand>
     */
    @Test
    public void testSelectAll() throws Exception {
        //1.获取Connection对象
        //2. 设置配置信息
        //3.加载配置文件
        Properties prop = new Properties();
        prop.load(new FileInputStream("D:\\Codes\\JAVA\\JavaWeb\\JavaWeb\\jdbc-demo\\src\\druid.properties"));
        //4.获取连接池对象
        DataSource dataSource = DruidDataSourceFactory.createDataSource(prop);
        //5.获取数据库链接 Connection
        Connection conn = dataSource.getConnection();

        //2.定义SQL
        String sql = "select * from tb_brand;";

        //3.获取pstml对象
        PreparedStatement pstmt = conn.prepareStatement(sql);

        //4.设置参数
        //5.执行SQL
        ResultSet res = pstmt.executeQuery();

        //6.处理结果 List<Brand> -> 封装Brand对象，装载到List集合
        Brand brand = null;
        List<Brand> brands = new ArrayList<>();
        while (res.next()){
            //获取数据
            int id = res.getInt("id");
            String brandName = res.getString("brand_name");
            String companyName = res.getString("company_name");
            int ordered = res.getInt("ordered");
            String description = res.getString("description");
            int status = res.getInt("status");

            //封装对象
            brand = new Brand();
            brand.setId(id);
            brand.setBrandName(brandName);
            brand.setCompanyName(companyName);
            brand.setOrdered(ordered);
            brand.setDescription(description);
            brand.setStatus(status);
            //装载集合
            brands.add(brand);
        }

        System.out.println(brands);

        //7.释放资源
        res.close();
        pstmt.close();
        conn.close();
    }

    /**
     * 查询所有
     * 1. SQL：insert into tb_brand···
     * 2. 参数：需要(除id外的所有参数)
     * 3. 结果：Boolean
     */
    @Test
    public void testAdd() throws Exception {
        //模拟：接收页面提交的参数
        String brandName = "香飘飘";
        String companyName = "香飘飘";
        int ordered = 1;
        String description = "绕地球一圈";
        int status = 1;

        //1.获取Connection对象
        //2. 设置配置信息
        //3.加载配置文件
        Properties prop = new Properties();
        prop.load(new FileInputStream("D:\\Codes\\JAVA\\JavaWeb\\JavaWeb\\jdbc-demo\\src\\druid.properties"));
        //4.获取连接池对象
        DataSource dataSource = DruidDataSourceFactory.createDataSource(prop);
        //5.获取数据库链接 Connection
        Connection conn = dataSource.getConnection();

        //2.定义SQL
        String sql = "insert into tb_brand(brand_name, company_name, ordered, description, status) values (?, ?, ?, ?, ?);";

        //3.获取pstml对象
        PreparedStatement pstmt = conn.prepareStatement(sql);

        //4.设置参数
        pstmt.setString(1, brandName);
        pstmt.setString(2, companyName);
        pstmt.setInt(3, ordered);
        pstmt.setString(4, description);
        pstmt.setInt(5, status);
        //5.执行SQL
        int count = pstmt.executeUpdate();

        //6.处理结果
        System.out.println(count > 0);

        //7.释放资源
        pstmt.close();
        conn.close();
    }

    /**
     * 修改
     * 1. SQL：update tb_brand set brand_name=?, company_name=?, ordered=?, description=?, status=? where id=?;
     * 2. 参数：需要(所有数据)
     * 3. 结果：Boolean
     */
    @Test
    public void testUpdate() throws Exception {
        //模拟：接收页面提交的参数
        String brandName = "香飘飘奶茶";
        String companyName = "香飘飘奶茶";
        int ordered = 10;
        String description = "绕地球3圈";
        int status = 1;
        int id = 4;

        //1.获取Connection对象
        //2. 设置配置信息
        //3.加载配置文件
        Properties prop = new Properties();
        prop.load(new FileInputStream("D:\\Codes\\JAVA\\JavaWeb\\JavaWeb\\jdbc-demo\\src\\druid.properties"));
        //4.获取连接池对象
        DataSource dataSource = DruidDataSourceFactory.createDataSource(prop);
        //5.获取数据库链接 Connection
        Connection conn = dataSource.getConnection();

        //2.定义SQL
        String sql = "update tb_brand set brand_name=?, company_name=?, ordered=?, description=?, status=? where id=?;";
        //3.获取pstml对象
        PreparedStatement pstmt = conn.prepareStatement(sql);

        //4.设置参数
        pstmt.setString(1, brandName);
        pstmt.setString(2, companyName);
        pstmt.setInt(3, ordered);
        pstmt.setString(4, description);
        pstmt.setInt(5, status);
        pstmt.setInt(6, id);
        //5.执行SQL
        int count = pstmt.executeUpdate();

        //6.处理结果
        System.out.println(count > 0);

        //7.释放资源
        pstmt.close();
        conn.close();
    }

    /**
     * 删除
     * 1. SQL：delete from tb_brand where id = ?;
     * 2. 参数：需要(id)
     * 3. 结果：Boolean
     */
    @Test
    public void testDelete() throws Exception {
        //模拟：接收页面提交的参数
        int id = 4;

        //1.获取Connection对象
        //2. 设置配置信息
        //3.加载配置文件
        Properties prop = new Properties();
        prop.load(new FileInputStream("D:\\Codes\\JAVA\\JavaWeb\\JavaWeb\\jdbc-demo\\src\\druid.properties"));
        //4.获取连接池对象
        DataSource dataSource = DruidDataSourceFactory.createDataSource(prop);
        //5.获取数据库链接 Connection
        Connection conn = dataSource.getConnection();

        //2.定义SQL
        String sql = "delete from tb_brand where id = ?;";
        //3.获取pstml对象
        PreparedStatement pstmt = conn.prepareStatement(sql);

        //4.设置参数
        pstmt.setInt(1, id);
        //5.执行SQL
        int count = pstmt.executeUpdate();

        //6.处理结果
        System.out.println(count > 0);

        //7.释放资源
        pstmt.close();
        conn.close();
    }
}

image-20221116223846137

完整代码

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
package com.itheima.example;

import com.alibaba.druid.pool.DruidDataSourceFactory;
import com.itheima.pojo.Brand;
import org.testng.annotations.Test;

import javax.sql.DataSource;
import java.io.FileInputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;

/**
 * 品牌数据的增删改查操作
 */
public class BrandTest {
    /**
     * 查询所有
     * 1. SQL：select * from tb_brand
     * 2. 参数：不需要
     * 3. 结果：List<Brand>
     */
    @Test
    public void testSelectAll() throws Exception {
        //1.获取Connection对象
        //2. 设置配置信息
        //3.加载配置文件
        Properties prop = new Properties();
        prop.load(new FileInputStream("D:\\Codes\\JAVA\\JavaWeb\\JavaWeb\\jdbc-demo\\src\\druid.properties"));
        //4.获取连接池对象
        DataSource dataSource = DruidDataSourceFactory.createDataSource(prop);
        //5.获取数据库链接 Connection
        Connection conn = dataSource.getConnection();

        //2.定义SQL
        String sql = "select * from tb_brand;";

        //3.获取pstml对象
        PreparedStatement pstmt = conn.prepareStatement(sql);

        //4.设置参数
        //5.执行SQL
        ResultSet res = pstmt.executeQuery();

        //6.处理结果 List<Brand> -> 封装Brand对象，装载到List集合
        Brand brand = null;
        List<Brand> brands = new ArrayList<>();
        while (res.next()){
            //获取数据
            int id = res.getInt("id");
            String brandName = res.getString("brand_name");
            String companyName = res.getString("company_name");
            int ordered = res.getInt("ordered");
            String description = res.getString("description");
            int status = res.getInt("status");

            //封装对象
            brand = new Brand();
            brand.setId(id);
            brand.setBrandName(brandName);
            brand.setCompanyName(companyName);
            brand.setOrdered(ordered);
            brand.setDescription(description);
            brand.setStatus(status);
            //装载集合
            brands.add(brand);
        }

        System.out.println(brands);

        //7.释放资源
        res.close();
        pstmt.close();
        conn.close();
    }

    /**
     * 查询所有
     * 1. SQL：insert into tb_brand···
     * 2. 参数：需要(除id外的所有参数)
     * 3. 结果：Boolean
     */
    @Test
    public void testAdd() throws Exception {
        //模拟：接收页面提交的参数
        String brandName = "香飘飘";
        String companyName = "香飘飘";
        int ordered = 1;
        String description = "绕地球一圈";
        int status = 1;

        //1.获取Connection对象
        //2. 设置配置信息
        //3.加载配置文件
        Properties prop = new Properties();
        prop.load(new FileInputStream("D:\\Codes\\JAVA\\JavaWeb\\JavaWeb\\jdbc-demo\\src\\druid.properties"));
        //4.获取连接池对象
        DataSource dataSource = DruidDataSourceFactory.createDataSource(prop);
        //5.获取数据库链接 Connection
        Connection conn = dataSource.getConnection();

        //2.定义SQL
        String sql = "insert into tb_brand(brand_name, company_name, ordered, description, status) values (?, ?, ?, ?, ?);";

        //3.获取pstml对象
        PreparedStatement pstmt = conn.prepareStatement(sql);

        //4.设置参数
        pstmt.setString(1, brandName);
        pstmt.setString(2, companyName);
        pstmt.setInt(3, ordered);
        pstmt.setString(4, description);
        pstmt.setInt(5, status);
        //5.执行SQL
        int count = pstmt.executeUpdate();

        //6.处理结果
        System.out.println(count > 0);

        //7.释放资源
        pstmt.close();
        conn.close();
    }

    /**
     * 修改
     * 1. SQL：update tb_brand set brand_name=?, company_name=?, ordered=?, description=?, status=? where id=?;
     * 2. 参数：需要(所有数据)
     * 3. 结果：Boolean
     */
    @Test
    public void testUpdate() throws Exception {
        //模拟：接收页面提交的参数
        String brandName = "香飘飘奶茶";
        String companyName = "香飘飘奶茶";
        int ordered = 10;
        String description = "绕地球3圈";
        int status = 1;
        int id = 4;

        //1.获取Connection对象
        //2. 设置配置信息
        //3.加载配置文件
        Properties prop = new Properties();
        prop.load(new FileInputStream("D:\\Codes\\JAVA\\JavaWeb\\JavaWeb\\jdbc-demo\\src\\druid.properties"));
        //4.获取连接池对象
        DataSource dataSource = DruidDataSourceFactory.createDataSource(prop);
        //5.获取数据库链接 Connection
        Connection conn = dataSource.getConnection();

        //2.定义SQL
        String sql = "update tb_brand set brand_name=?, company_name=?, ordered=?, description=?, status=? where id=?;";
        //3.获取pstml对象
        PreparedStatement pstmt = conn.prepareStatement(sql);

        //4.设置参数
        pstmt.setString(1, brandName);
        pstmt.setString(2, companyName);
        pstmt.setInt(3, ordered);
        pstmt.setString(4, description);
        pstmt.setInt(5, status);
        pstmt.setInt(6, id);
        //5.执行SQL
        int count = pstmt.executeUpdate();

        //6.处理结果
        System.out.println(count > 0);

        //7.释放资源
        pstmt.close();
        conn.close();
    }

    /**
     * 删除
     * 1. SQL：delete from tb_brand where id = ?;
     * 2. 参数：需要(id)
     * 3. 结果：Boolean
     */
    @Test
    public void testDelete() throws Exception {
        //模拟：接收页面提交的参数
        int id = 4;

        //1.获取Connection对象
        //2. 设置配置信息
        //3.加载配置文件
        Properties prop = new Properties();
        prop.load(new FileInputStream("D:\\Codes\\JAVA\\JavaWeb\\JavaWeb\\jdbc-demo\\src\\druid.properties"));
        //4.获取连接池对象
        DataSource dataSource = DruidDataSourceFactory.createDataSource(prop);
        //5.获取数据库链接 Connection
        Connection conn = dataSource.getConnection();

        //2.定义SQL
        String sql = "delete from tb_brand where id = ?;";
        //3.获取pstml对象
        PreparedStatement pstmt = conn.prepareStatement(sql);

        //4.设置参数
        pstmt.setInt(1, id);
        //5.执行SQL
        int count = pstmt.executeUpdate();

        //6.处理结果
        System.out.println(count > 0);

        //7.释放资源
        pstmt.close();
        conn.close();
    }
}