01. JDBC

JDBC介绍

JDBC快速入门

如果报错则在url上加入?useSSL=false即可。

 

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

        // 2. 获取连接
        String url = "jdbc:mysql://localhost:3306/test?useSSL=false";
        String username = "root";
        String password = "123456";
        Connection conn = DriverManager.getConnection(url, username, password);

        // 3. 定义sql
        String sql = "update account set money = 2000 where id = 1";

        // 4. 获取执行sql的对象 Statement
        Statement stmt = conn.createStatement();

        // 5. 执行sql
        int count = stmt.executeUpdate(sql); //受影响的行数

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

        // 7.释放资源 顺序不能变
        stmt.close();
        conn.close();

    }
}

JDBC API详解

 

 

/**
 * JDBC API :DriverManager
 */
public class JDBCDemo_DriverManager {
    public static void main(String[] args) throws Exception {
        // 1. 注册驱动
        //Class.forName("com.mysql.jdbc.Driver");

        // 2. 获取连接
        String url = "jdbc:mysql:///test?useSSL=false";
        String username = "root";
        String password = "123456";
        Connection conn = DriverManager.getConnection(url, username, password);

        // 3. 定义sql
        String sql = "update account set money = 2000 where id = 1";

        // 4. 获取执行sql的对象 Statement
        Statement stmt = conn.createStatement();

        // 5. 执行sql
        int count = stmt.executeUpdate(sql); //受影响的行数

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

        // 7.释放资源 顺序不能变
        stmt.close();
        conn.close();

    }
}

 

/**
 * JDBC API :Connection
 */
public class JDBCDemo_Connection {
    public static void main(String[] args) throws Exception {
        // 1. 注册驱动
        //Class.forName("com.mysql.jdbc.Driver");

        // 2. 获取连接
        String url = "jdbc:mysql:///test?useSSL=false";
        String username = "root";
        String password = "123456";
        Connection conn = DriverManager.getConnection(url, username, password);

        // 3. 定义sql
        String sql1 = "update account set money = 30000 where id = 1";
        String sql2 = "update account set money = 30000 where id = 2";

        // 4. 获取执行sql的对象 Statement
        Statement stmt = conn.createStatement();

        try {
            // 开启事务
            conn.setAutoCommit(false);
            // 5. 执行sql
            int count1 = stmt.executeUpdate(sql1); //受影响的行数
            int count2 = stmt.executeUpdate(sql2); //受影响的行数

            // 6. 处理结果
            System.out.println(count1);
            System.out.println(count2);
            int i = 3/0;

            // 提交事务
            conn.commit();
        } catch (Exception e) {
            // 回滚事务
            conn.rollback();
            e.printStackTrace();
        }

        // 提交事务


        // 7.释放资源 顺序不能变
        stmt.close();
        conn.close();

    }
}

/**
 * JDBC API :Statement
 */
public class JDBCDemo_Statement {

    /**
     * 执行DML语句
     * @throws Exception
     */
    @Test
    public void testDML() throws Exception {
        // 1. 注册驱动
        //Class.forName("com.mysql.jdbc.Driver");

        // 2. 获取连接
        String url = "jdbc:mysql:///test?useSSL=false";
        String username = "root";
        String password = "123456";
        Connection conn = DriverManager.getConnection(url, username, password);

        // 3. 定义sql
        String sql = "update account set money = 3000 where id = 1";

        // 4. 获取执行sql的对象 Statement
        Statement stmt = conn.createStatement();

        // 5. 执行sql
        int count = stmt.executeUpdate(sql); //受影响的行数

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

        // 7.释放资源 顺序不能变
        stmt.close();
        conn.close();
    }

    /**
     * 执行DDL语句
     * @throws Exception
     */
    @Test
    public void testDDL() throws Exception {
        // 1. 注册驱动
        //Class.forName("com.mysql.jdbc.Driver");

        // 2. 获取连接
        String url = "jdbc:mysql:///test?useSSL=false";
        String username = "root";
        String password = "123456";
        Connection conn = DriverManager.getConnection(url, username, password);

        // 3. 定义sql
        String sql = "drop database test2";

        // 4. 获取执行sql的对象 Statement
        Statement stmt = conn.createStatement();

        // 5. 执行sql
        int count = stmt.executeUpdate(sql); //受影响的行数

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

        // 7.释放资源 顺序不能变
        stmt.close();
        conn.close();
    }
}

 

/**
 * JDBC API :ResultSet
 */
public class JDBCDemo_ResultSet {

    /**
     * 执行DQL
     * @throws Exception
     */
    @Test
    public void testResultSet() throws Exception {
        // 1. 注册驱动
        //Class.forName("com.mysql.jdbc.Driver");

        // 2. 获取连接
        String url = "jdbc:mysql:///test?useSSL=false";
        String username = "root";
        String password = "123456";
        Connection conn = DriverManager.getConnection(url, username, password);

        // 3. 定义sql
        String sql = "select * from account";

        // 4. 获取statement对象
        Statement stmt = conn.createStatement();

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

        // 6. 处理结果 遍历rs中的所有数据
        // 6.1 光标下移一行，并判断当前行是否有数据
        while (rs.next()) {
            // 6.2 获取数据 getxxx()
            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("---------------------------");
        }

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

    }
}

 

    /**
     * 1. 定义实体类Account
     * 2. 查询数据并封装到Account对象中
     * 3. 将Account对象存入ArrayList集合中
     * @throws Exception
     */
    @Test
    public void testResultSet2() throws Exception {
        //Class.forName("com.mysql.jdbc.Driver");

        String url = "jdbc:mysql:///test?useSSL=false";
        String username = "root";
        String password = "123456";
        Connection conn = DriverManager.getConnection(url, username, password);
        
        String sql = "select * from account";
        
        Statement stmt = conn.createStatement();
        
        ResultSet rs = stmt.executeQuery(sql);

        // 创建集合
        List<Account> list = new ArrayList<>();
        
        while (rs.next()) {
            Account account = new Account();
            
            int id = rs.getInt("id");
            String name = rs.getString("name");
            double money = rs.getDouble("money");

            // 赋值
            account.setId(id);
            account.setName(name);
            account.setMoney(money);

            // 存入集合
            list.add(account);
        }

        System.out.println(list);
        
        rs.close();
        stmt.close();
        conn.close();

    }

 

 

/**
 * 用户登陆
 */
public class JDBCDemo_UserLogin {

    /**
     * 执行DQL
     * @throws Exception
     */
    @Test
    public void testResultSet() throws Exception {
        // 2. 获取连接
        String url = "jdbc:mysql:///test?useSSL=false";
        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 name = '"+name+"' and password = '"+pwd+"'";

        // 获取stmt对象
        Statement stmt = conn.createStatement();

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

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

        rs.close();
        stmt.close();
        conn.close();

    }

    /**
     * 演示sql注入
     * @throws Exception
     */
    @Test
    public void testLogin_Inject() throws Exception {
        // 2. 获取连接
        String url = "jdbc:mysql:///test?useSSL=false";
        String username = "root";
        String password = "123456";
        Connection conn = DriverManager.getConnection(url, username, password);

        // 接收用户名密码 用户输入
        String name = "afhkjdh";
        //select * from tb_user where name = '' and password = '' or '1'='1"
        String pwd = "' or '1'='1";

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

        // 获取stmt对象
        Statement stmt = conn.createStatement();

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

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

        rs.close();
        stmt.close();
        conn.close();

    }

}

 

    @Test
    public void testPrepareStatement() throws Exception {
        // 2. 获取连接
        String url = "jdbc:mysql:///test?useSSL=false";
        String username = "root";
        String password = "123456";
        Connection conn = DriverManager.getConnection(url, username, password);

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

        // 定义sql
        String sql = "select *from tb_user where name = ? and password = ?";

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

        // 设置问号的值
        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();
        conn.close();

    }

 

数据库连接池

 

 

/**
 * Druid数据库连接池演示
 */
public class DruidDemo {
    public static void main(String[] args) throws Exception {
        // 1. 倒入jar包

        // 2. 定义配置

        // 3. 加载配置文件
        Properties prop = new Properties();
        prop.load(new FileInputStream("jdbc-demo/src/druid.properties"));
        // 4. 获取连接池对象
        DataSource dataSource = DruidDataSourceFactory.createDataSource(prop);

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

        System.out.println(connection);
    }
}

练习

 

​​​​​​​

-- 删除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;

 

/**
 * 品牌
 * alt + 鼠标左键：整列编辑
 *
 * 在实体类中，基本数据类型建议使用其对应的包装类型
 */
public class Brand {

    //id 主键
    private Integer id;
    // 品牌名称
    private String brandName;
    // 企业名称
    private String companyName;
    // 排序字段
    private Integer ordered;
    // 描述信息
    private String description;
    // 状态：0：禁用  1：启用
    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 +
                '}';
    }
}

 

package com.test.example;

import com.alibaba.druid.pool.DruidDataSourceFactory;
import com.test.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.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对象
        Properties prop = new Properties();
        prop.load(new FileInputStream("jdbc-demo/src/druid.properties"));
        DataSource dataSource = DruidDataSourceFactory.createDataSource(prop);

        Connection conn = dataSource.getConnection();

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

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

        // 4. 设置参数

        // 5. 执行SQL
        ResultSet rs = pstmt.executeQuery();

        // 6. 处理结果 List<Brand> 封装Brand对象，装载到List集合
        Brand brand = null;
        List<Brand> brands = new ArrayList<>();
        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();
            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. 释放资源
        rs.close();
        pstmt.close();
        conn.close();




    }
}

 

    /**
     * 添加数据
     * 1. SQL：insert into tb_brand(brand_name, company_name, ordered, description, status) VALUES ();
     * 2. 参数：需要: 除id外所有参数信息
     * 3. 结果：boolean
     */
    @Test
    public void testAdd() throws Exception {
        // 接收页面提交的参数
        String brandName = "香飘飘";
        String companyName = "香飘飘";
        int ordered = 1;
        String description = "绕地球一圈";
        int status = 1;



        // 1. 获取连接的Connection对象
        Properties prop = new Properties();
        prop.load(new FileInputStream("/Users/panxuchao/Desktop/jdbc/jdbc-demo/src/druid.properties"));
        DataSource dataSource = DruidDataSourceFactory.createDataSource(prop);

        Connection conn = dataSource.getConnection();

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

        // 3. 获取pstmt对象
        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 {
        // 接收页面提交的参数
        int id = 4;
        String brandName = "香飘飘";
        String companyName = "香飘飘";
        int ordered = 1000;
        String description = "绕地球三圈";
        int status = 1;

        // 1. 获取连接的Connection对象
        Properties prop = new Properties();
        prop.load(new FileInputStream("/Users/panxuchao/Desktop/jdbc/jdbc-demo/src/druid.properties"));
        DataSource dataSource = DruidDataSourceFactory.createDataSource(prop);

        Connection conn = dataSource.getConnection();

        // 2. 定义SQl
        String sql = "update tb_brand set brand_name = ?,company_name = ?,ordered = ?,description = ?,status = ? where id = ?;";

        // 3. 获取pstmt对象
        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(5,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. 参数：需要: 所有参数信息
     * 3. 结果：boolean
     */
    @Test
    public void testDelete() throws Exception {
        // 接收页面提交的参数
        int id = 4;

        // 1. 获取连接的Connection对象
        Properties prop = new Properties();
        prop.load(new FileInputStream("/Users/panxuchao/Desktop/jdbc/jdbc-demo/src/druid.properties"));
        DataSource dataSource = DruidDataSourceFactory.createDataSource(prop);

        Connection conn = dataSource.getConnection();

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

        // 3. 获取pstmt对象
        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();

    }