day35-javaWeb

JDBC

快速入门

package com.itheima.jdbc;

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

/**
 * jdbc快速入门
 */
public class JDBCDemo01 {
    public static void main(String[] args) throws Exception {
        //1.导入mysql驱动jar包
        //2.注册驱动
        Class.forName("com.mysql.jdbc.Driver");

        //3.获取连接
        String url = "jdbc:mysql://127.0.0.1:3306/db1";
        String user = "shifan";
        String password = "123";
        Connection connection = DriverManager.getConnection(url, user, password);

        //4.定义sql语句
        String sql = "update account set money = 3000 where name = '张三'";

        //5.获取执行sql的对象
        Statement statement = connection.createStatement();

        //6.执行sql语句,被影响的行数
        int i = statement.executeUpdate(sql);

        //7.处理结果
        System.out.println(i);

        //8.释放资源
        statement.close();
        connection.close();
    }
}

API详解

DriverManager

package com.itheima.jdbc;

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

/**
 * jdbcAPI详解 DriverManager
 * 功能:
 * 1.注册驱动
 * 2.获取数据库连接
 */
public class JDBCDemo02_DriverManager {
    public static void main(String[] args) throws Exception {
        //1.导入mysql驱动jar包
        //2.注册驱动,mysql5之后的驱动可省略注册驱动的操作
        // 自动加载jar包中META-INF/services/java.sql.Driver文件中的驱动类
        //Class.forName("com.mysql.jdbc.Driver");

        //3.获取连接 url参数:jdbc:mysql://是协议,后面跟ip地址和端口号/数据库名称?参数键值对1&参数键值对2
//        String url = "jdbc:mysql://127.0.0.1:3306/db1";
//        如果使用的是本机mysql服务器及默认3306端口,url可简写为:jdbc:mysql:///数据库名称?参数键值对
        //配置useSSL=false参数,禁用安全连接,解决警告提示
        String url = "jdbc:mysql:///db1?useSSL=false";
        String user = "shifan";
        String password = "123";
        Connection connection = DriverManager.getConnection(url, user, password);

        //4.定义sql语句
        String sql = "update account set money = 3000 where name = '张三'";

        //5.获取执行sql的对象
        Statement statement = connection.createStatement();

        //6.执行sql语句,被影响的行数
        int i = statement.executeUpdate(sql);

        //7.处理结果
        System.out.println(i);

        //8.释放资源
        statement.close();
        connection.close();
    }
}

Connection

package com.itheima.jdbc;

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

/**
 * jdbcAPI详解 Connection
 * 功能:
 * 1.获取执行sql的对象
 * Statement createStatement()  普通sql执行对象
 * PreparedStatement prepareStatement(sql) 预编译sql的执行对象 防止sql注入
 * CallableStatement prepareCall(sql) 执行存储过程的对象 不常用
 * 2.管理事务
 * void setAutoCommit(boolean autoCommit) 开启事务,true表示自动提交,false表示手动提交
 * void commit() 提交事务
 * void rollback() 回滚事务
 */
public class JDBCDemo03_Connection {
    public static void main(String[] args) throws Exception {

        String url = "jdbc:mysql:///db1?useSSL=false";
        String user = "shifan";
        String password = "123";
        Connection connection = DriverManager.getConnection(url, user, password);

        //4.定义sql语句
        String sql = "update account set money = 3000 where name = '张三'";
        String sql1 = "update account set money = 2000 where name = '李四'";

        //5.获取执行sql的对象
        Statement statement = connection.createStatement();

        try {
            //开启事务
            connection.setAutoCommit(false);

            //6.执行sql语句,返回被影响的行数
            int i = statement.executeUpdate(sql);
            System.out.println(i);

//            System.out.println(10/0);
            int i1 = statement.executeUpdate(sql1);

            //7.处理结果
            System.out.println(i1);

            //提交事务
            connection.commit();
        } catch (SQLException e) {
            e.printStackTrace();
            //出现异常,则回滚事务
            connection.rollback();
        }

        //8.释放资源
        statement.close();
        connection.close();
    }
}

Statement

package com.itheima.jdbc;

import org.junit.Test;

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

/**
 * jdbcAPI详解 Statement
 * 功能:执行sql语句
 * 1.执行DDL DML 
 * int executeUpdate(String sql)  返回被影响的行数
 */
public class JDBCDemo04_Statement {
    /**
     * 执行DML语句
     * @throws Exception
     */
    @Test
    public void testDML() throws Exception {
        //3.获取连接
        String url = "jdbc:mysql://127.0.0.1:3306/db1?useSSL=false";
        String user = "shifan";
        String password = "123";
        Connection connection = DriverManager.getConnection(url, user, password);

        //4.定义sql语句
        String sql = "update account set money = 3000 where name = '张三'";

        //5.获取执行sql的对象
        Statement statement = connection.createStatement();

        //6.执行DML语句,被影响的行数
        int i = statement.executeUpdate(sql);

        //7.处理结果
//        System.out.println(i);
        if (i>0){
            System.out.println("修改成功");
        }else {
            System.out.println("修改失败");
        }

        //8.释放资源
        statement.close();
        connection.close();
    }
    
    /**
     * 执行DDL语句,很少使用
     * @throws Exception
     */
    @Test
    public void testDDL() throws Exception {
        //3.获取连接
        String url = "jdbc:mysql://127.0.0.1:3306/db1?useSSL=false";
        String user = "shifan";
        String password = "123";
        Connection connection = DriverManager.getConnection(url, user, password);

        //4.定义sql语句
        String sql = "create database db2";

        //5.获取执行sql的对象
        Statement statement = connection.createStatement();

        //6.执行DML语句,被影响的行数
        int i = statement.executeUpdate(sql);

        //7.处理结果
//        System.out.println(i);
        if (i>0){
            System.out.println("修改成功");
        }else {
            System.out.println("修改失败");
        }

        //8.释放资源
        statement.close();
        connection.close();
    }
}

ResultSet

package com.itheima.jdbc;

import com.itheima.pojo.Account;
import org.junit.Test;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;

/**
 * jdbcAPI详解 ResultSet(结果对象集)
 * 功能:
 * 封装sql语句查询结果
 * 执行DQL 
 * ResultSet executeQuery(String sql)  返回ResultSet对象
 */
public class JDBCDemo05_ResultSet {
    /**
     * 执行DQL语句
     * @throws Exception
     */
    @Test
    public void testDQL() throws Exception {
        //3.获取连接
        String url = "jdbc:mysql://127.0.0.1:3306/db1?useSSL=false";
        String user = "shifan";
        String password = "123";
        Connection connection = DriverManager.getConnection(url, user, password);

        //4.定义sql语句
        String sql = "select * from account";

        //5.获取执行sql的对象
        Statement statement = connection.createStatement();

        //6.执行DML语句,被影响的行数
        ResultSet rs = statement.executeQuery(sql);

        //7.处理结果
/*        while (rs.next()){
            //方法参数:列序号,从1开始
            int id = rs.getInt(1);
            String name = rs.getString(2);
            double money = rs.getDouble(3);
            System.out.println(id);
            System.out.println(name);
            System.out.println(money);
            System.out.println("-----------------");
        }   */
        while (rs.next()){
            //方法参数为:列名
            int id = rs.getInt("id");
            String name = rs.getString("name");
            double money = rs.getDouble("money");
            System.out.println(id);
            System.out.println(name);
            System.out.println(money);
            System.out.println("-----------------");
        }

        //8.释放资源
        statement.close();
        connection.close();
    }
    
    /**
     * ResultSet案例
     * 需求:
     * 查询account表数据,封装为Account对象,并存储到ArrayList集合中去
     */
    @Test
    public void testResultSet() throws Exception {
        //3.获取连接
        String url = "jdbc:mysql://127.0.0.1:3306/db1?useSSL=false";
        String user = "shifan";
        String password = "123";
        Connection connection = DriverManager.getConnection(url, user, password);

        //4.定义sql语句
        String sql = "select * from account";

        //5.获取执行sql的对象
        Statement statement = connection.createStatement();

        //6.执行DML语句,被影响的行数
        ResultSet rs = statement.executeQuery(sql);

        //7.处理结果
        ArrayList<Account> accounts = new ArrayList<>();
        Account acc  = null;
        while (rs.next()){
            //方法参数为:列名
            int id = rs.getInt("id");
            String name = rs.getString("name");
            double money = rs.getDouble("money");
            acc = new Account(id, name, money);
            accounts.add(acc);
        }

        //输出所有对象
        System.out.println(accounts);

        //8.释放资源
        statement.close();
        connection.close();
    }
}

Account类

package com.itheima.pojo;

public class Account {
    private int id;
    private String name;
    private double money;

    @Override
    public String toString() {
        return "Account{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", money=" + money +
                '}';
    }

    public Account() {
    }

    public Account(int id, String name, double money) {
        this.id = id;
        this.name = name;
        this.money = money;
    }

    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 double getMoney() {
        return money;
    }

    public void setMoney(double money) {
        this.money = money;
    }
}

PreparedStatement

package com.itheima.jdbc;

import org.junit.Test;

import java.sql.*;

/**
 * jdbcAPI详解 PreparedStatement
 * SQL注入演示
 */
public class JDBCDemo06_PreparedStatement {
    /**
     * 用户登录
     * SQL注入演示
     * @throws Exception
     */
    @Test
    public void testUserLogin() throws Exception {
        //3.获取连接
        String url = "jdbc:mysql://127.0.0.1:3306/test?useSSL=false";
        String user = "shifan";
        String password = "123";
        Connection connection = DriverManager.getConnection(url, user, password);

//        String name = "zhangsan";
//        String pwd = "123";登录成功
        String name = "fafgarg";
        //特殊的密码字符串
        String pwd = "' or '1' = '1";//登录成功

        //4.定义sql语句,拼接字符串
        String sql = "select * from user where username = '"+name+"' and password = '"+pwd+"'";
        //拼接后:select * from user where username = 'fafgarg' and password = '' or '1' = '1';

        //5.获取执行sql的对象
        Statement statement = connection.createStatement();

        //6.执行DML语句,被影响的行数
        ResultSet rs = statement.executeQuery(sql);

        //7.处理结果
        if (rs.next()){
            System.out.println("登录成功");
        }else {
            System.out.println("登录失败");
        }

        //8.释放资源
        rs.close();
        statement.close();
        connection.close();
    }

    /**
     * PreparedStatement
     * 功能:预编译sql,性能更高
     * 防止sql注入
     */
    @Test
    public void testPreparedStatement() throws Exception {
        //3.获取连接,配置useServerPrepStmts=true,启用预编译
        String url = "jdbc:mysql://127.0.0.1:3306/test?useSSL=false&useServerPrepStmts=true";
        String user = "shifan";
        String password = "123";
        Connection connection = DriverManager.getConnection(url, user, password);

        String name = "zhangsan";
        String pwd = "' or '1' = '1";//登录失败

        //定义sql字符串,使用占位符?替代参数值
        String sql = "select * from user where username = ? and password = ?";

        //获取sql执行对象
        PreparedStatement pstmt = connection.prepareStatement(sql);

        //设置?参数值,参数一:?的序号,从1开始.参数二:参数值
        pstmt.setString(1,name);
        pstmt.setString(2,pwd);

        //执行sql语句
        ResultSet rs = pstmt.executeQuery();

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

        //释放资源
        rs.close();
        pstmt.close();
        connection.close();
    }

    /**
     * PreparedStatement原理
     * 对敏感字符进行转义
     * 预编译后,再次设置值执行时不需要再次编译,故性能更高
     * @throws Exception
     */
    @Test
    public void testPreparedStatement1() throws Exception {
        //3.获取连接,配置useServerPrepStmts=true,启用预编译
        String url = "jdbc:mysql://127.0.0.1:3306/test?useSSL=false&useServerPrepStmts=true";
        String user = "shifan";
        String password = "123";
        Connection connection = DriverManager.getConnection(url, user, password);

        String name = "zhangsan";
        String pwd = "' or '1' = '1";//登录失败

        //定义sql字符串,使用占位符?替代参数值
        String sql = "select * from user where username = ? and password = ?";

        //获取sql执行对象
        PreparedStatement pstmt = connection.prepareStatement(sql);

        //测试预编译是否在上一条语句执行后执行
        Thread.sleep(10000);

        //设置?参数值
        pstmt.setString(1,name);
        pstmt.setString(2,pwd);

        //底层原理即对敏感字符进行了转义,如下
        //select * from user where username = 'zhangsan' and password = '\' or \'1\' = \'1'
        ResultSet rs = null;
        //执行sql语句
         rs = pstmt.executeQuery();

        //再次设置?参数值
        pstmt.setString(1,"aaa");
        pstmt.setString(2,"fsf");

        //再次执行sql语句,测试是否仅预编译一次
        rs = pstmt.executeQuery();

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

        //释放资源
        rs.close();
        pstmt.close();
        connection.close();
    }
}

mysql日志

MySQL, Version: 5.7.24-log (MySQL Community Server (GPL)). started with:
TCP Port: 0, Named Pipe: (null)
Time                 Id Command    Argument
2023-03-11T12:19:20.461025Z	    2 Connect	shifan@localhost on test using TCP/IP
2023-03-11T12:19:20.466436Z	    2 Query	/* mysql-connector-java-5.1.48 ( Revision: 29734982609c32d3ab7e5cac2e6acee69ff6b4aa ) */SELECT  @@session.auto_increment_increment AS auto_increment_increment, @@character_set_client AS character_set_client, @@character_set_connection AS character_set_connection, @@character_set_results AS character_set_results, @@character_set_server AS character_set_server, @@collation_server AS collation_server, @@collation_connection AS collation_connection, @@init_connect AS init_connect, @@interactive_timeout AS interactive_timeout, @@license AS license, @@lower_case_table_names AS lower_case_table_names, @@max_allowed_packet AS max_allowed_packet, @@net_buffer_length AS net_buffer_length, @@net_write_timeout AS net_write_timeout, @@performance_schema AS performance_schema, @@query_cache_size AS query_cache_size, @@query_cache_type AS query_cache_type, @@sql_mode AS sql_mode, @@system_time_zone AS system_time_zone, @@time_zone AS time_zone, @@transaction_isolation AS transaction_isolation, @@wait_timeout AS wait_timeout
2023-03-11T12:19:20.492728Z	    2 Query	SET character_set_results = NULL
2023-03-11T12:19:20.493059Z	    2 Query	SET autocommit=1
2023-03-11T12:19:20.556925Z	    2 Prepare	select * from user where username = ? and password = ?
2023-03-11T12:19:20.558031Z	    2 Execute	select * from user where username = 'zhangsan' and password = '\' or \'1\' = \'1'
2023-03-11T12:19:20.558553Z	    2 Close stmt	
2023-03-11T12:19:20.567083Z	    2 Quit	
2023-03-11T12:20:30.275702Z	    3 Connect	shifan@localhost on test using TCP/IP
2023-03-11T12:20:30.280887Z	    3 Query	/* mysql-connector-java-5.1.48 ( Revision: 29734982609c32d3ab7e5cac2e6acee69ff6b4aa ) */SELECT  @@session.auto_increment_increment AS auto_increment_increment, @@character_set_client AS character_set_client, @@character_set_connection AS character_set_connection, @@character_set_results AS character_set_results, @@character_set_server AS character_set_server, @@collation_server AS collation_server, @@collation_connection AS collation_connection, @@init_connect AS init_connect, @@interactive_timeout AS interactive_timeout, @@license AS license, @@lower_case_table_names AS lower_case_table_names, @@max_allowed_packet AS max_allowed_packet, @@net_buffer_length AS net_buffer_length, @@net_write_timeout AS net_write_timeout, @@performance_schema AS performance_schema, @@query_cache_size AS query_cache_size, @@query_cache_type AS query_cache_type, @@sql_mode AS sql_mode, @@system_time_zone AS system_time_zone, @@time_zone AS time_zone, @@transaction_isolation AS transaction_isolation, @@wait_timeout AS wait_timeout
2023-03-11T12:20:30.308429Z	    3 Query	SET character_set_results = NULL
2023-03-11T12:20:30.308735Z	    3 Query	SET autocommit=1
2023-03-11T12:20:30.323741Z	    3 Query	select * from user where username = 'zhangsan' and password = '\' or \'1\' = \'1'
2023-03-11T12:20:30.328393Z	    3 Quit	
2023-03-11T12:29:09.575033Z	    4 Connect	shifan@localhost on test using TCP/IP
2023-03-11T12:29:09.580352Z	    4 Query	/* mysql-connector-java-5.1.48 ( Revision: 29734982609c32d3ab7e5cac2e6acee69ff6b4aa ) */SELECT  @@session.auto_increment_increment AS auto_increment_increment, @@character_set_client AS character_set_client, @@character_set_connection AS character_set_connection, @@character_set_results AS character_set_results, @@character_set_server AS character_set_server, @@collation_server AS collation_server, @@collation_connection AS collation_connection, @@init_connect AS init_connect, @@interactive_timeout AS interactive_timeout, @@license AS license, @@lower_case_table_names AS lower_case_table_names, @@max_allowed_packet AS max_allowed_packet, @@net_buffer_length AS net_buffer_length, @@net_write_timeout AS net_write_timeout, @@performance_schema AS performance_schema, @@query_cache_size AS query_cache_size, @@query_cache_type AS query_cache_type, @@sql_mode AS sql_mode, @@system_time_zone AS system_time_zone, @@time_zone AS time_zone, @@transaction_isolation AS transaction_isolation, @@wait_timeout AS wait_timeout
2023-03-11T12:29:09.611070Z	    4 Query	SET character_set_results = NULL
2023-03-11T12:29:09.611489Z	    4 Query	SET autocommit=1
2023-03-11T12:29:09.632929Z	    4 Prepare	select * from user where username = ? and password = ?
2023-03-11T12:29:09.633914Z	    4 Execute	select * from user where username = 'zhangsan' and password = '\' or \'1\' = \'1'
2023-03-11T12:29:09.634315Z	    4 Execute	select * from user where username = 'aaa' and password = 'fsf'
2023-03-11T12:29:09.634641Z	    4 Close stmt	
2023-03-11T12:29:09.643245Z	    4 Quit	
2023-03-11T12:31:10.656788Z	    5 Connect	shifan@localhost on test using TCP/IP
2023-03-11T12:31:10.673508Z	    5 Query	/* mysql-connector-java-5.1.48 ( Revision: 29734982609c32d3ab7e5cac2e6acee69ff6b4aa ) */SELECT  @@session.auto_increment_increment AS auto_increment_increment, @@character_set_client AS character_set_client, @@character_set_connection AS character_set_connection, @@character_set_results AS character_set_results, @@character_set_server AS character_set_server, @@collation_server AS collation_server, @@collation_connection AS collation_connection, @@init_connect AS init_connect, @@interactive_timeout AS interactive_timeout, @@license AS license, @@lower_case_table_names AS lower_case_table_names, @@max_allowed_packet AS max_allowed_packet, @@net_buffer_length AS net_buffer_length, @@net_write_timeout AS net_write_timeout, @@performance_schema AS performance_schema, @@query_cache_size AS query_cache_size, @@query_cache_type AS query_cache_type, @@sql_mode AS sql_mode, @@system_time_zone AS system_time_zone, @@time_zone AS time_zone, @@transaction_isolation AS transaction_isolation, @@wait_timeout AS wait_timeout
2023-03-11T12:31:10.734915Z	    5 Query	SET character_set_results = NULL
2023-03-11T12:31:10.737516Z	    5 Query	SET autocommit=1
2023-03-11T12:31:10.766624Z	    5 Prepare	select * from user where username = ? and password = ?
2023-03-11T12:31:20.767826Z	    5 Execute	select * from user where username = 'zhangsan' and password = '\' or \'1\' = \'1'
2023-03-11T12:31:20.768379Z	    5 Execute	select * from user where username = 'aaa' and password = 'fsf'
2023-03-11T12:31:20.768996Z	    5 Close stmt	
2023-03-11T12:31:20.776656Z	    5 Quit	

数据库连接池

数据库连接池简介

• 数据库连接池是个容器，负责分配、管理数据库连接(Connection)
• 它允许应用程序重复使用一个现有的数据库连接，而不是再重新建立一个；
• 释放空闲时间超过最大空闲时间的数据库连接来避免因为没有释放数据库连接而引起的数据库连接遗漏
• 好处
  • 资源重用
  • 提升系统响应速度
  • 避免数据库连接遗漏

数据库连接池实现

• 标准接口：DataSource

  官方(SUN) 提供的数据库连接池标准接口，由第三方组织实现此接口。该接口提供了获取连接的功能

  常见的数据库连接池

  • DBCP
  • C3P0
  • Druid

  我们现在使用更多的是Druid，它的性能比其他两个会好一些。

• Druid（德鲁伊）

  • Druid连接池是阿里巴巴开源的数据库连接池项目
  • 功能强大，性能优秀，是Java语言最好的数据库连接池之一

Druid操作步骤

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 DruidDemo01 {
    public static void main(String[] args) throws Exception {
        //1.导入jar包

        //2.定义配置文件

        //3.加载配置文件
        Properties prop = new Properties();
        prop.load(new FileInputStream("day01-jdbc/src/druid.properties"));

        //4.获取数据库连接池
        DataSource dataSource = DruidDataSourceFactory.createDataSource(prop);

        //5.获取数据库连接
        Connection connection = dataSource.getConnection();

        System.out.println(connection);
    }
}

配置文件druid.properties

driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql:///db1?useSSL=false&useServerPrepStmts=true
username=shifan
password=123
#初始连接数
initialSize=5
#最大连接数
maxActive=10
#最大等待时间
maxWait=3000

JDBC练习

数据准备

-- 删除tb_brand表
drop table if exists tb_brand;
-- 创建tb_brand表
create table tb_brand
(
    -- id 主键
    id           int primary key auto_increment,
    -- 品牌名称
    brand_name   varchar(20),
    -- 企业名称
    company_name varchar(20),
    -- 排序字段
    ordered      int,
    -- 描述信息
    description  varchar(100),
    -- 状态：0：禁用  1：启用
    status       int
);
-- 添加数据
insert into tb_brand (brand_name, company_name, ordered, description, status)
values ('三只松鼠', '三只松鼠股份有限公司', 5, '好吃不上火', 0),
       ('华为', '华为技术有限公司', 100, '华为致力于把数字世界带入每个人、每个家庭、每个组织，构建万物互联的智能世界', 1),
       ('小米', '小米科技有限公司', 50, 'are you ok', 1);


SELECT * FROM tb_brand;

DruidDemo02

package com.itheima.druid;

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

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

/**
 * JDBC练习
 * 需求:完成商品品牌数据的增删改查操作
 * 注意:测试方法中无法使用相对路径
 */
public class DruidDemo02 {
    /**
     * 查询所有品牌
     * 1.sql:select * from tb_brand;
     * 2.参数:不需要
     * 3.返回结果:boolean
     * @throws Exception
     */
    @Test
    public void testSelectAll() throws Exception{
        //3.加载配置文件
        Properties prop = new Properties();
        prop.load(DruidDemo02.class.getResourceAsStream("/druid.properties"));

        //4.获取数据库连接池
        DataSource dataSource = DruidDataSourceFactory.createDataSource(prop);

        //5.获取数据库连接
        Connection connection = dataSource.getConnection();

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

        //获取sql执行对象
        PreparedStatement pst = connection.prepareStatement(sql);

        //设置参数

        //执行sql
        ResultSet rs = pst.executeQuery();

        ArrayList<Brand> brands = new ArrayList<>();
        Brand brand = null;
        while (rs.next()){
            int id = rs.getInt("id");
            String brandName = rs.getString("brand_name");
            String companyName = rs.getString("company_name");
            int ordered = rs.getInt("ordered");
            String description = rs.getString("description");
            int status = rs.getInt("status");
            //封装Brand对象
            brand = new Brand(id,brandName,companyName,ordered,description,status);
            //装载集合
            brands.add(brand);
        }
        System.out.println(brands);

        //释放资源
        rs.close();
        pst.close();
        connection.close();
    }
    /**
     * 添加品牌
     * 1.sql:insert into tb_brand(brand_name,company_name,ordered,description,status) values(?,?,?,?,?);
     * 2.参数:除了id之外的所有参数
     * 3.返回结果:boolean
     * @throws Exception
     */
    @Test
    public void testAdd() throws Exception{
        //准备参数
        String brandName = "香飘飘";
        String companyName = "香飘飘有限公司";
        int ordered = 1000;
        String description = "每年卖出去的香飘飘连起来能绕地球一圈";
        int status = 1;

        //3.加载配置文件
        Properties prop = new Properties();
        prop.load(DruidDemo02.class.getResourceAsStream("/druid.properties"));

        //4.获取数据库连接池
        DataSource dataSource = DruidDataSourceFactory.createDataSource(prop);

        //5.获取数据库连接
        Connection connection = dataSource.getConnection();

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

        //获取sql执行对象
        PreparedStatement pst = connection.prepareStatement(sql);

        //设置参数
        pst.setString(1,brandName);
        pst.setString(2,companyName);
        pst.setInt(3,ordered);
        pst.setString(4,description);
        pst.setInt(5,status);

        //执行sql,返回被影响的行数
        int count = pst.executeUpdate();

        //处理数据
        System.out.println(count>0);

        //释放资源
        pst.close();
        connection.close();
    }
    /**
     * 修改品牌信息
     * 1.sql:update tb_brand set brand_name = ?,company_name = ?,
     * ordered = ?,description = ?,status = ? where id = ?;
     * 2.参数:所有参数
     * 3.返回结果:boolean
     * @throws Exception
     */
    @Test
    public void testUpdate() throws Exception{
        //准备参数
        String brandName = "香飘飘";
        String companyName = "香飘飘有限公司";
        int ordered = 500;
        String description = "每年卖出去的香飘飘连起来能绕地球一圈";
        int status = 1;
        int id = 4;

        //3.加载配置文件
        Properties prop = new Properties();
        prop.load(DruidDemo02.class.getResourceAsStream("/druid.properties"));

        //4.获取数据库连接池
        DataSource dataSource = DruidDataSourceFactory.createDataSource(prop);

        //5.获取数据库连接
        Connection connection = dataSource.getConnection();

        //定义sql
        String sql = "update tb_brand set brand_name = ?,company_name = ?,\n" +
                "  ordered = ?,description = ?,status = ? where id = ?;";

        //获取sql执行对象
        PreparedStatement pst = connection.prepareStatement(sql);

        //设置参数
        pst.setString(1,brandName);
        pst.setString(2,companyName);
        pst.setInt(3,ordered);
        pst.setString(4,description);
        pst.setInt(5,status);
        pst.setInt(6,id);

        //执行sql,返回被影响的行数
        int count = pst.executeUpdate();

        //处理数据
        System.out.println(count>0);

        //释放资源
        pst.close();
        connection.close();
    }
    /**
     * 删除品牌
     * 1.sql:delete from tb_brand where id = ?;
     * 2.参数:id
     * 3.返回结果:boolean
     * @throws Exception
     */
    @Test
    public void testDelete() throws Exception{
        //准备参数
        int id = 4;

        //3.加载配置文件
        Properties prop = new Properties();
        prop.load(DruidDemo02.class.getResourceAsStream("/druid.properties"));

        //4.获取数据库连接池
        DataSource dataSource = DruidDataSourceFactory.createDataSource(prop);

        //5.获取数据库连接
        Connection connection = dataSource.getConnection();

        //定义sql
        String sql = "delete from tb_brand where id = ?;";

        //获取sql执行对象
        PreparedStatement pst = connection.prepareStatement(sql);

        //设置参数
        pst.setInt(1,id);

        //执行sql,返回被影响的行数
        int count = pst.executeUpdate();

        //处理数据
        System.out.println(count>0);

        //释放资源
        pst.close();
        connection.close();
    }
}