MySQL笔记10：JDBC

10. JDBC（重点）

10.1 数据库驱动

驱动：声卡、显卡、数据库...

驱动由数据库产商提供，用于连接应用程序和数据库

image

10.2 JDBC

JDBC： SUN公司提供的，用于简化开发人员对数据库的统一操作的，Java操作数据库的规范

具体实现由数据库厂商实现，开发人员只需要掌握操作

image

导入包：java.sql, javax.sql, 数据驱动包

10.3 第一个JDBC程序

  • 创建测试数据库
CREATE DATABASE jdbcStudy CHARACTER SET utf8 COLLATE utf8_general_ci;

USE jdbcStudy;

CREATE TABLE `users`(
id INT PRIMARY KEY,
NAME VARCHAR(40),
PASSWORD VARCHAR(40),
email VARCHAR(60),
birthday DATE
);

INSERT INTO `users`(id,NAME,PASSWORD,email,birthday)
VALUES(1,'zhansan','123456','zs@sina.com','1980-12-04'),
(2,'lisi','123456','lisi@sina.com','1981-12-04'),
(3,'wangwu','123456','wangwu@sina.com','1979-12-04')
  1. 创建一个普通项目
  2. 导入jar包： 创建lib - 复制jar包到lib目录下 - 右键 Add as Library
  3. 编写测试代码

代码步骤

  1. 加载驱动
  2. 连接数据库 DriverManager
  3. 获得执行sql的对象 Statement (不安全的对象)
  4. 获得返回的结果集
  5. 释放连接
// 第一个JDBC程序
public class JdbcDemo01 {
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        // 1. 加载驱动
        Class.forName("com.mysql.jdbc.Driver"); // 加载驱动的固定写法

        // 2. 用户信息和url
        // useUnicode=true&characterEncoding=utf8&useSSL=true 支持中文编码,设定字符编码方式,使用安全的连接
        String url = "jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=true";
        String username = "root";
        String password = "123456";

        // 3. 连接成功 数据库对象 Connection代表数据库
        Connection connection = DriverManager.getConnection(url, username, password);

        // 4. 执行SQL的对象 Statement
        Statement statement = connection.createStatement();

        // 5. 执行SQL的对象 去 执行SQL, 可能存在结果, 查看返回结果
        String sql = "SELECT * FROM users";

        // 返回结果集,结果集中封装了全部的查询结果
        ResultSet resultSet = statement.executeQuery(sql);

        while (resultSet.next()) {
            System.out.println("id = " + resultSet.getObject("id")); //使用列名查询内容
            System.out.println("name = " + resultSet.getObject("NAME"));
            System.out.println("password = " + resultSet.getObject("PASSWORD"));
            System.out.println("email = " + resultSet.getObject("email"));
            System.out.println("birthday = " + resultSet.getObject("birthday"));
            System.out.println("========================");
        }

        // 6. 释放连接
        resultSet.close();
        statement.close();
        connection.close();
    }
}

DriverManager

// DriverManager.registerDriver(new com.mysql.jdbc.Driver()); 中间有静态代码块,之间可以直接创建对象
Class.forName("com.mysql.jdbc.Driver"); // 加载驱动的固定写法 

// connection代表数据库
Connection connection = DriverManager.getConnection(url, username, password);

connection.setAutoCommit(); // 数据库设置自动提交
connection.rollback(); 		// 事务回滚
connection.commit(); 		// 事务提交

URL

// 协议://主机地址:端口/数据库名？参数1&参数2&参数3
// mysql默认端口 3306
String url = "jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=true";

// Oralce 默认端口 1521
// jdbc:oralce:thin:@localhost:1521:sid

Statement / PrepareStatement 执行SQL的对象

String sql = "SELECT * FROM users"; // 编写sql

statement.executeQuery(); 	// 查询操作,返回一个结果集resultSet
statement.executeUpdate(); 	// 用于更新, 插入, 删除 返回SQL匹配到的行数
statement.execute(); 		// 执行任何SQL

ResultSet查询的结果集：封装了所有的查询结果

  • 获得制定的数据类型
resultSet.getObject(); // 在不知道列类型时使用
resultSet.getInt();
resultSet.getFloat();
resultSet.getString();
...
  • 遍历，指针
resultSet.next(); 		// 移动到下一个
resultSet.afterLast();	// 移动到最后
resultSet.beforeFirst();// 移动到最前面
resultSet.previous();	// 移动到前一行
resultSet.absolute(row);// 移动到指定行

释放资源

resultSet.close();
statement.close();
connection.close();

10.4 Statement对象

JDBC中的statement对象用于向数据库发送SQL语句，想完成对数据库的增删改查，只需要通过这个对象向数据库发送增删改查语句即可。
Statement对象的executeUpdate方法，用于向数据库发送增、删、改的sql语句， executeUpdate执行完后， 将会返回一个整数(即增删改语句导致了数据库几行数据发生了变化)。
Statement.executeQuery方法用于向数据库发生查询语句，executeQuery方法返回代表查询结果的ResultSet对象。

CRUD操作 - create

Statement statement = connection.createStatement();
String sql = "insert into user(...) values(...)";
int num = statement.executeUpdate(sql);
if(num>0){
	System.out.println("插入成功");
}

CRUD操作 - delete

Statement statement = connection.createStatement();
String sql = "insert into user(...) values(...)";
int num = statement.executeUpdate(sql);
if(num>0){
	System.out.println("删除成功");
}

CURD操作 - update

Statement statement = connection.createStatement();
String sql = "insert into user(...) values(...)";
int num = statement.executeUpdate(sql);
if(num>0){
	System.out.println("修改成功");
}

CURD操作 - read

Statement statement = connection.createStatement();
String sql = "select * from  user where id =1";
ResultSet rs= statement.executeQuery(sql);
if(rs.next()){
	System.out.println("");
}

代码实现

1. 配置文件

在mysql8.0中：Driver位置由com.mysql.jdbc.Driver 变为com.mysql.cj.jdbc.Driver

# 配置文件
driver=com.mysql.cj.jdbc.Driver
url=jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=true
username=root
password=123456

2. 工具类

public class JdbcUtils {
    private static String driver = null;
    private static String url = null;
    private static String username = null;
    private static String password = null;

    static {
        try {
            // 读取配置文件为输入流
            InputStream in = JdbcUtils.class.getClassLoader().getResourceAsStream("db.properties");
            Properties properties = new Properties();
            properties.load(in);
            driver = properties.getProperty("driver");
            url = properties.getProperty("url");
            username = properties.getProperty("username");
            password = properties.getProperty("password");

            // 1. 驱动只需要加载一次
            Class.forName(driver);

        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    // 获取连接
    public static Connection getConnection() throws SQLException {
        return DriverManager.getConnection(url, username, password);
    }

    // 释放连接资源
    public static void release(Connection conn, Statement st, ResultSet rs) {
        if (rs != null) {
            try {
                rs.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }

        if (st != null) {
            try {
                st.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }

        if (conn != null) {
            try {
                conn.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
    }
}

3. 增删改的应用executeUpdate

  • 插入
public class TestInsert {
    public static void main(String[] args) {
        Connection conn = null;
        Statement st = null;
        ResultSet rs = null;
        try {
            conn = JdbcUtils.getConnection(); // 获得数据库连接
            st = conn.createStatement(); // 获得sql的执行对象
            String sql = "INSERT INTO users(id,`NAME`,`PASSWORD`,`email`,`birthday` )" +
                    "VALUES(4, 'chachan', '123456', '123@qq.com', '2022-6-1')";
            int i = st.executeUpdate(sql); //执行并返回结果
            if (i > 0) {
                System.out.println("插入成功");
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            JdbcUtils.release(conn, st ,rs);
        }

    }
}
  • 删除

修改sql语句为删除的语句

String sql = "DELETE FROM users WHERE id = 4";
  • 修改

修改sql语句为更新的语句

String sql = "UPDATE users SET `NAME` = 'cha' WHERE id = 1";

4. 查的应用executeQuery

使用executeQuery查询，返回结果集

public class TestSelect {
    public static void main(String[] args) {
        Connection conn = null;
        Statement st = null;
        ResultSet rs = null;

        try {
            conn = JdbcUtils.getConnection();
            st = conn.createStatement();

            // sql
            String sql = "select * from users where id = 1";
            rs = st.executeQuery(sql); // 查询使用executeQuery,返回结果集
            while (rs.next()) {
                System.out.println(rs.getString("name"));
            }

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            JdbcUtils.release(conn, st, rs);
        }
    }
}

SQL注入的问题

sql存在漏洞，会被攻击导致数据泄露， SQL数据会因为or的拼接被泄露

package com.chachan53.class2;

import com.chachan53.class2.utils.JdbcUtils;

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

public class SQLInjection {
    public static void main(String[] args) {
        // login("cha","123456");
        login("' or '1=1","' OR '1=1" );
    }

    // 登录业务
    public static void login(String username, String password) {
        Connection conn = null;
        Statement st = null;
        ResultSet rs = null;

        try {
            conn = JdbcUtils.getConnection();
            st = conn.createStatement();

            // SELECT * FROM users WHERE `NAME` = '' OR '1=1' AND `PASSWORD` = '' OR '1=1'
            String sql = "SELECT * FROM users WHERE `NAME` = '"+ username +"' AND `PASSWORD` = '"+ password +"'";

            rs = st.executeQuery(sql);
            while (rs.next()) {
                System.out.println(rs.getString("name"));
                System.out.println(rs.getString("password"));
                System.out.println("================");
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }
}

输出结果：

cha
123456
================
lisi
123456
================
wangwu
123456
================

10.5 PreparedStatement对象

可以防止SQL注入，并效率更高

与Statement的区别

  • 使用 ? 占位符替代参数
  • 预编译
  • 手动赋值参数（索引从1开始）
// 区别
// 使用 ? 占位符替代参数
String sql = "insert into users(id,`NAME`,`PASSWORD`,`email`,`birthday`) value(?,?,?,?,?)";
st = conn.prepareStatement(sql); //预编译sql, 先写sql, 然后不执行

// 手动给参数赋值, 索引从1开始
st.setInt(1,4);
st.setString(2,"chachan");
st.setString(3,"123456");
st.setString(4,"1234@163.com");
// 注意点 sql.Date 数据库         java.sql.Date()
//       util.Date java用        new Date().getTime() 获得时间戳
st.setDate(5, new java.sql.Date(new Date().getTime()));

插入数据

删除、更新修改SQL语句

package com.chachan53.class3;

import com.chachan53.class2.utils.JdbcUtils;

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

public class TestInsert {
    public static void main(String[] args) {
        Connection conn = null;
        PreparedStatement st = null; // 不同

        try {
            conn = JdbcUtils.getConnection();

            // 区别
            // 使用 ? 占位符替代参数
            String sql = "insert into users(id,`NAME`,`PASSWORD`,`email`,`birthday`) value(?,?,?,?,?)";
            st = conn.prepareStatement(sql); //预编译sql, 先写sql, 然后不执行

            // 手动给参数赋值, 索引从1开始
            st.setInt(1,4);
            st.setString(2,"chachan");
            st.setString(3,"123456");
            st.setString(4,"1234@163.com");
            // 注意点 sql.Date 数据库         java.sql.Date()
            //       util.Date java用        new Date().getTime() 获得时间戳
            st.setDate(5, new java.sql.Date(new Date().getTime()));

            // 执行
            int i = st.executeUpdate();
            if (i > 0) {
                System.out.println("插入成功");
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            JdbcUtils.release(conn, st ,null);
        }
    }
}

查询数据

执行使用executeQuery，并返回结果

public class TestSelect {
    public static void main(String[] args) {
        Connection conn = null;
        PreparedStatement st = null;
        ResultSet rs = null;

        try {
            conn = JdbcUtils.getConnection();

            // 区别
            // 使用 ? 占位符替代参数
            String sql = "select * from users where id = ?";
            st = conn.prepareStatement(sql); //预编译sql, 先写sql, 然后不执行

            // 手动给参数赋值, 索引从1开始
            st.setInt(1,1);

            // 执行
            rs = st.executeQuery();
            while (rs.next()) {
                System.out.println(rs.getObject("name"));
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            JdbcUtils.release(conn, st ,null);
        }
    }
}

防止SQL注入

  • PrepareStatement 防止SQL注入, 把传递的参数当成字符
  • 其中的转义字符会被直接转义
package com.chachan53.class2;

import com.chachan53.class2.utils.JdbcUtils;

import java.sql.*;

public class SQLInjection {
    public static void main(String[] args) {
        login("cha","123456");
        // login("'' or 1=1","123456" );
    }

    // 登录业务
    public static void login(String username, String password) {
        Connection conn = null;
        PreparedStatement st = null;
        ResultSet rs = null;

        try {
            conn = JdbcUtils.getConnection();
            // SELECT * FROM users WHERE `NAME` = '' OR '1=1' AND `PASSWORD` = '' OR '1=1'
            String sql = "select * from users where `NAME`= ?  and `PASSWORD`= ? ";

            // PrepareStatement 防止SQL注入, 把传递的参数当成字符
            // 其中的转义字符会被直接转义
            st = conn.prepareStatement(sql);
            st.setString(1,username);
            st.setString(2,password);
            rs = st.executeQuery();
            while (rs.next()) {
                System.out.println(rs.getString("name"));
                System.out.println(rs.getString("password"));
                System.out.println("================");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

10.6 使用IDEA连接数据库

社区版

Setting - Plugins - 搜索database - 安装

专业版

右侧 database

10.7 事务

要么都成功，要么都失败

ACID

代码实现

  1. 开启事务conn.setAutoCommit(false)
  2. 一组业务执行完毕，提交事务
  3. 可以在catch语句中显示的定义回滚，但默认失败就会回滚
package com.chachan53.class4;

import com.chachan53.class2.utils.JdbcUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class TestTransaction1 {
    public static void main(String[] args) {
        Connection conn = null;
        PreparedStatement st = null;
        ResultSet rs = null;

        try {
            conn = JdbcUtils.getConnection();
            // 关闭数据库的自动提，自动开启事务
            conn.setAutoCommit(false); // 开启事务
            String sql1 = "update account set money = money-100 where name = 'A'";
            String sql2 = "update account set money = money+100 where name = 'B'";
            st = conn.prepareStatement(sql1);
            st.executeUpdate();

            // int x = 1/0; // 业务失败测试

            st = conn.prepareStatement(sql2);
            st.executeUpdate();

            // 业务完毕，提交事务
            conn.commit();

        } catch (SQLException e) {
            try {
                conn.rollback(); // 失败默认回滚，不写也会回滚
            } catch (SQLException e1) {
                e1.printStackTrace();
            }
            e.printStackTrace();
        } finally {
            JdbcUtils.release(conn, st, rs);
        }
    }
}

10.8 数据库连接池

数据库 - 执行完毕 - 释放

连接 - 释放的过程非常浪费系统资源

池化技术：预先准备一些资源，直接连接准备好的

最小连接数：预先准备资源数

最大连接数：业务连接上线，超出上限，业务等待

等待超时：等待时间上限

编写连接池：实现DataSourse接口

开源数据源实现（拿来即用）

DBCP、C3P0、Druid(阿里)

使用数据库连接池后，在项目开发中不需要编写连接数据的代码

本质：无论使用什么数据源，本质相同，DataSourse接口不变，使用方法不变

DBCP

jar包：dbcp, pool, logging

使用配置文件，在工具类中修改

// 读取配置文件为输入流
InputStream in = JdbcUtils.class.getClassLoader().getResourceAsStream("dbcpconfig.properties");
Properties properties = new Properties();
properties.load(in);

// 创建数据源 工厂模式: 创建对象，设计模式
dataSource = BasicDataSourceFactory.createDataSource(properties);

使用时改变连接方式

C3P0

jar包：c3p0, mchange

使用xml的配置文件，在工具类时修改配置方法

// 代码配置
// dataSource = new ComboPooledDataSource();
// dataSource.setDriverClass();
// dataSource.setUser();
// dataSource.setPassword();
// dataSource.setJdbcUrl();
//
// dataSource.setMaxPoolSize();
// dataSource.setMinPoolSize();

// 配置文件配置
dataSource = new ComboPooledDataSource("MySQL");
posted @ 2022-05-23 11:32  chachan53  阅读(35)  评论(0)  编辑  收藏  举报