JDBC --学习笔记

JDBC

数据库驱动

驱动：声卡、显卡、鼠标、数据库

程序开发时，会通过 数据库驱动和数据库打交道。

JDBC

SUN公司为了简化开发人员（对数据库的统一）的操作，提供了一个（Java操作数据库的）规范，俗称JDBC（Java Database Connectivity），这些规范由具体厂商去实现。

对于开发人员来说，只需掌握JDBC接口的操作即可！

需要两个包：java.sql、javax.sql

还需要导入一个数据库驱动包：mysql-connector-java

如果数据库版本是8.X，则使用8.X版本的，如果数据库版本是5.7，则下在5.X版本的。

官方5.x下载地址（后期可用maven管理包）：https://downloads.mysql.com/archives/c-j/

第一个JDBC程序

首先打开MySQL，创建测试数据库`jdbcStudy`

-- 创建一个JDBC
CREATE DATABASE jdbcstudy CHARACTER SET utf—8 COLLATE utf8_general_ci;
USE `jdbcStudy`;
-- 创建用户表
CREATE TABLE users(
   `id` INT(10) NOT NULL AUTO_INCREMENT,
   `name` VARCHAR(50) NOT NULL,
   `password` VARCHAR(40) NOT NULL,
   `email` VARCHAR(40),
   `birthday` DATE,
   PRIMARY KEY(`id`) 
)ENGINE INNODB DEFAULT CHARSET = utf8;
-- 创建测试数据
INSERT INTO `users`(`id`,`name`,`password`,`email`,`birthday`) 
VALUES ('1','张三','123456','zs.qq.com','1990-1-1'),
('2','李四','123456','ls.q.com','1992-2-2'),
('3','王五','123456','ww.qq.com','1995-5-5');

使用IDEA创建JDBC项目

创建一个JAVA普通项目：一路next，选择目标项目地址。

导入驱动

创建lib目录，鼠标右键点击项目名称，选择New->Directory，输入lib

将下载的.jar包放进lib目录中

鼠标右键点击lib，选择 Add as Library...

编写测试代码

加载驱动
登录数据库（数据库url、用户名、密码）
连接成功，返回数据库对象
操作SQL的对象去执行SQL,如果存在结果则返回结果
释放连接

package TestJDBC.Demo01;

import java.sql.*;

public class JdbcTestDemo01 {
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        // 加载驱动
        Class.forName("com.mysql.jdbc.Driver");  //固定写法，加载驱动
        //登录数据库（数据库url、用户名、密码）
        //?使用unicode支持中文，字符集tuf8，使用SSL安全连接
        String url = "jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf-8&serverTimezone=Asia/Shanghai";
        String username = "root";
        String password = "123456";
        //连接成功，返回数据库对象 connection 代表数据库
        Connection connection = DriverManager.getConnection(url,username,password);
        //创建操作SQL的对象 Statement
        Statement statement = connection.createStatement();
        //执行SQL的对象去执行SQL,如果存在结果则返回结果 到resultSet。
        //写入SQL语言，用resultSet调用。
        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"));
        }
        //释放连接，依次关闭几个对象类。
        resultSet.close();
        statement.close();
        connection.close();
    }
}

步骤总结

加载驱动 DriverManager、class.forName
连接数据库 Connection
获取执行SQL的对象 Statement
获得返回的结果集 ResultSet
释放连接，依次关闭ResultSet、Statement、Connection

JDBC对象

`DriverManager`

需要提前加载

 //以前的写法，不建议使用，会加载两次。
DriverManager.registerDriver(new com.mysql.jdbc.Driver());
//现在的写法
Class.forName("com.mysql.jdbc.Driver");
//以下是源码
package com.mysql.jdbc;

import java.sql.DriverManager;
import java.sql.SQLException;

public class Driver extends NonRegisteringDriver implements java.sql.Driver {
    public Driver() throws SQLException {
    }

    static {
        try {
            //这里已经是静态调用，所以不需要再执行一遍命令
            DriverManager.registerDriver(new Driver());
        } catch (SQLException var1) {
            throw new RuntimeException("Can't register driver!");
        }
    }
}

URL

商用环境连接数据库，需要设置参数useSSL=True;

String url = "jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf-8&serverTimezone=Asia/Shanghai";
//mysql ----3306
//jdbc:mysql://主机地址:端口号/数据库名?参数1&参数2&参数3...

//Oracle ---1521
//jdbc:oracle:thin@主机地址:端口号:sid

`Connection`

代表数据库

//以事务相关操作为例
connection.setAutoCommit();  //设置事务自动提交
connection.rollback(); // 事务回滚
connection.commit();  // 事务提交

`Statement` `PrepareStatement`

JDBC中的Statement对象用于向数据库发送SQL语句，想完成对数据库的增、删、改、查，只需要通过这个对象向数据库发送增删改查语句即可。

Statement对象的executeUpdate方法，用于向数据库发送增、删、改的SQL语句，executeUpdate执行完后，将会返回一个整数（即增、删、改语句导致了数据库几行数据发生了变化）。

executeQuery方法用于向数据库发送查询语句，executeQuery方法返回代表查询结果的ResultSet对象。

Statement.executeQuery(); //查询操作返回 ResultSet()
Statement.execute();   //执行任何SQL操作，一般不用
Statement.executeUpdate();   //更新、插入、删除都用这个，返回一个受影响的行数

`ResultSet`

用于创建结果集对象，封装了所有的查询结果。

resultSet.getObject();  //获取任何类型数据
resultSet.getInt();  //获取指定类型数据，比如int
resultSet.beforeFirst(); //移动到最前面
resultSet.afterLast(); //移动到最后面
resultSet.next();   //移动到下一个
resultSet.previous(); //移动到下一行
resultSet.absolute(rows); // 移动到某一行

`.close()`

释放资源，打开的各类对象会占用内存资源，用完记得将这些都释放掉。

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

JDBC程序编写

如果直接在程序中写入url，用户名，密码，那么程序的耦合度太高，一般把工具类、配置参数、代码分离。

案例：通过java实现插入一条数据。首先创建3个文件：

JdbcUtils工具类，用于实现连接、释放等方法
db.properties配置参数，用于存放driver，url，username，password等配置参数
JdbcTest01 main()所在类，用于测试插入数据库命令。

`db.properties`配置

driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&serverTimezone=Asia/Shanghai
username=root
password=123456

`JdbcUtils`配置

获取配置参数
加载驱动
提供连接数据库方法getConnection()
提供释放连接方法release()

package TestJDBC.Demo02;

import java.io.IOException;
import java.io.InputStream;
import java.sql.*;
import java.util.Properties;
//工具类JdbcUtils
public class JdbcUtils {
    //创建4个变量，用于接收配置参数，默认值为空
    private static String driver = null;
    private static String url = null;
    private static String username = null;
    private static String password = null;
    static {
        try { //获取文件时可能遇到异常，所以需要抛出异常
            //从文件db.properties中获取配置参数，存放到相应的变量中
            //创建输入流对象resourceAsStream，从文件中获取数据
            InputStream resourceAsStream = JdbcUtils.class.getClassLoader().getResourceAsStream("db.properties");
            //创建配置参数对象
            Properties properties = new Properties();
            //配置参数对象从输入流对象中resourceAsStream加载数据
            properties.load(resourceAsStream);
            //获取driver参数
            driver = properties.getProperty("driver");
            //获取url参数
            url = properties.getProperty("url");
            //获取username参数
            username = properties.getProperty("username");
            //获取password参数
            password = properties.getProperty("password");
            //加载驱动
            Class.forName(driver);
        } catch (IOException | ClassNotFoundException e) {
            e.printStackTrace();
        }
    }
    //创建连接数据库的方法，传入3个参数（url、用户名、密码），返回数据库操作对象
    public static Connection getConnection() throws SQLException {
        return DriverManager.getConnection(url,username,password);
    }
    //释放连接方法，如果有的话将ResultSet、Statement、Connection对象依次关闭
    public static void release(Connection conn, Statement st, ResultSet rs){
        if(rs!=null) {
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if(st!=null) {
            try {
                st.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        if(conn!=null){
            try {
                conn.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
    }
}

`JdbcTest01`配置实现增（删、改类似）

package TestJDBC.Demo02;

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

public class JdbcTest01 {
    public static void main(String[] args) {
        //创建测试对象
        Connection conn = null;
        Statement st = null;
        ResultSet rs = null;
        try { // 有异常需要抛出
            //创建数据库对象，从Jdbc工具类中调用getConnection()方法
            //并把返回Connection对象放入conn变量中
            conn = JdbcUtils.getConnection();
            //调用Statement方法创建数据库操作对象st
            st = conn.createStatement();
            //创建需要插入数据的SQL语句放入变量sql中
            String sql = "INSERT INTO `users` (`id`,`name`,`password`,`email`,`birthday`) VALUES('4','王二麻子','123456','we.qq.com','2000-1-1');";
            //调用数据库操作方法executeUpdate，传入SQL语句
            int i = st.executeUpdate(sql);
            //根据返回值显示插入结果，如果数据变化行>0则表示插入成功
            if(i>0){
                System.out.println("插入成功");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally { // 最后需要释放连接
            JdbcUtils.release(conn,st,rs);
        }

    }
}

结果参考

下图为程序执行结果，显示插入成功。

另外红字部分为建立连接时，未加useSSL=True参数导致，测试环境可以无视。

通过SQLyog观察，发现id=4的条目已经添加成功。

`JdbcTest02`配置实现查询

package TestJDBC.Demo02;

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

public class JdbcTest02 {
    public static void main(String[] args) {
        Connection conn = null;
        Statement st = null;
        ResultSet rs = null;
        try {
            conn = JdbcUtils.getConnection();
            st = conn.createStatement();
            //查询users表中所有数据
            String sql = "SELECT * FROM `users`;";
            //查询使用executeQuery()方法
            rs = st.executeQuery(sql);
            //使用while逐条显示查询到的数据
            while(rs.next()){
                System.out.println("id :" + rs.getObject("id") + "\t" +"name :" + rs.getObject("name") + "\t" +"password :" + rs.getObject("password") + "\t" +"email :" + rs.getObject("email") + "\t" +"birthday :" + rs.getObject("birthday"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JdbcUtils.release(conn,st,rs);
        }
    }
}

结果参考：

SQL注入问题

SQL存在漏洞，会被攻击导致数据泄露，SQL会被拼接 OR

正常的登录流程，前端传入用户名和密码，后端与数据库做比对。

数据库操作实现语句：

String sql = "SELECT * FROM `users` WHERE `name`='" + username + "' AND `password`='" + password +"';";
//当传入的username = 张三，password = 123456时，sql语句为
SELECT * FROM `users` WHERE `name`='张三' AND `password`='123456';
//此为标准的SQL查询语句，符合要求。

测试代码：

package TestJDBC.Demo02;

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

public class JdbcTest03 {
    public static void main(String[] args) {
        login("张三","123456");
    }

    //登录业务，传入用户名和密码
    public static void login(String username, String password) {
        Connection conn = null;
        Statement st = null;
        ResultSet rs = null;
        try {
            conn = JdbcUtils.getConnection();
            st = conn.createStatement();
            //查询users表中是否存在匹配的用户名和密码组合
            String sql = "SELECT * FROM `users` WHERE `name`='" + username + "' AND `password`='" + password +"';";
            //查询使用executeQuery()方法
            rs = st.executeQuery(sql);
            //使用while显示匹配结果
            while(rs.next()){
                System.out.println(rs.getString("name"));
                System.out.println(rs.getString("password"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JdbcUtils.release(conn, st, rs);
        }
    }
}

结果验证：正常登录可以获取

SQL注入问题：

-- 当传入的username = "' or '1=1" ,password = "' or '1=1"时
-- SQL语句变成
SELECT * FROM `users` WHERE `name`='' OR '1=1' AND `password` = '' OR '1=1';

查询条件变成：name = 空 或 1=1 并且 密码为空或1=1，由于1=1恒成立，所有where查询条件恒成立，返回结果为，查询到的所有值。那么也就失去了验证账号密码正确的作用。

即拼接一个违法的字符串出来。

测试代码：传入带SQL注入的用户名和密码。

public class JdbcTest03 {
    public static void main(String[] args) {
        //用户名：' or '1=1，密码：' or '1=1
        login("' or '1=1","' or '1=1");
    }

执行结果：

返回了数据库中所有的结果。

`PreparedStatement`防止SQL注入

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

package TestJDBC.Demo02;

import java.sql.*;

public class JdbcTest04 {
    public static void main(String[] args) {
        Connection conn = null;
        PreparedStatement pst = null;
        ResultSet rs = null;
        try {
            conn = JdbcUtils.getConnection();
            //区别
            //使用问号占位符代替参数
            String sql = "INSERT INTO `users` (`id`,`name`,`password`,`email`,`birthday`) VALUES(?,?,?,?,?);";
            //预编译SQL，先写SQL不执行
            pst = conn.prepareStatement(sql);
            //手动给参数赋值，(第几个问号，值)
            pst.setInt(1,5);
            pst.setString(2,"大川");
            pst.setString(3,"123456");
            pst.setString(4,"dc.qq.com");
            //注意sql.Date   数据库
            //   util.Date   Java
            //需要转换时间戳格式
            pst.setDate(5,new java.sql.Date(new java.util.Date().getTime()));
            //调用执行语句
            int i = pst.executeUpdate();
            if(i>0){
                System.out.println("插入成功");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JdbcUtils.release(conn,pst,rs);
        }
    }
}

测试结果：

控制台输出，插入成功。

SQLyog查看结果：

测试SQL注入：

首先测试正常的查询指令：

package TestJDBC.Demo02;

import java.sql.*;

public class JdbcTest05 {
    public static void main(String[] args) {
        Connection conn = null;
        PreparedStatement pst = null;
        ResultSet rs = null;
        try {
            conn = JdbcUtils.getConnection();
            //区别
            //使用问号占位符代替参数
            String sql = "SELECT * FROM `users` WHERE `name`=? AND `password`=?";
            //预编译SQL，先写SQL不执行
            pst = conn.prepareStatement(sql);
            //手动给参数赋值，(第几个问号，值)
            pst.setString(1,"大川");
            pst.setString(2,"123456");
            //调用执行语句
            rs = pst.executeQuery();
            //打印结果
            while(rs.next()){
                System.out.println("name:"+ rs.getObject("name"));
                System.out.println("password:"+ rs.getObject("password"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JdbcUtils.release(conn,pst,rs);
        }
    }
}

测试SQL注入：

用户名：' or '1=1 密码：' or '1=1

package TestJDBC.Demo02;

import java.sql.*;

public class JdbcTest05 {
    public static void main(String[] args) {
        Connection conn = null;
        PreparedStatement pst = null;
        ResultSet rs = null;
        try {
            conn = JdbcUtils.getConnection();
            //区别
            //使用问号占位符代替参数
            String sql = "SELECT * FROM `users` WHERE `name`=? AND `password`=?";
            //预编译SQL，先写SQL不执行
            pst = conn.prepareStatement(sql);
            //手动给参数赋值，(第几个问号，值)
            //这里将用户名和密码替换成SQL注入语句
            pst.setString(1,"' or '1=1");
            pst.setString(2,"' or '1=1");
            //调用执行语句
            rs = pst.executeQuery();
            while(rs.next()){
                System.out.println("name:"+ rs.getObject("name"));
                System.out.println("password:"+ rs.getObject("password"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JdbcUtils.release(conn,pst,rs);
        }
    }
}

测试结果：

没有返回任何值，说明PrepareStatement有防止SQL注入能力。有转义字符会直接被忽略。

使用IDEA连接数据库

选择右侧database，点击 +->Data Source -> MySQL，如下图所示：

按要求填入url、用户名、密码登录。可能需要按照提示下载相应的包。

在Advanced标签中，配置时区参数serverTimezone = Asia/Shanghai。

修改连接信息，点击修改配置->Schemas->选中相应的数据库jdbcstudy

双击jdbcstudy中的users即可查看数据库内的数据。

这里可以打开命令行窗口。

用命令行进行查询：console框中输入SQL语句，点击左上方绿色箭头执行，执行成功后，会有绿色的√下方会显示结果。

修改命令测试：

也可以用图形化修改数据：需要点击DB图标 才会生效，如下图所示：

刷新按钮用来更新显示内容：效果同SQLyog的刷新。

JDBC事务操作

回顾事务

原子性 要么都完成，要么都不完成
一致性 总数不变
隔离性 多个进程互不干扰
持久性 一旦提交不可回滚

隔离性问题

脏读：一个事务读取了另一个没有提交的事务
不可重复读：在通过个十五内，重复读取了表中的数据，但数据发生变化
幻读（虚读）：在一个事务内，读取了别人插入的数据，导致前后不一致

创建测试表和测试数据

-- 创建账号表
CREATE TABLE `account`(
    `id` INT(4) AUTO_INCREMENT NOT NULL,
    `name` VARCHAR(20) NOT NULL,
    `money` DECIMAL(9,2) NOT NULL,
    PRIMARY KEY (`id`)
    )ENGINE=INNODB DEFAULT CHARSET=utf8;
-- 创建测试数据
INSERT INTO `account`(`name`,`money`) VALUES ('A','1000'),('B','1000');

回顾配置过程

关闭事务自动提交
开始事务
写SQL语句
提交
重新打开事务自动提交

编写相应的JAVA代码

package TestJDBC.Demo02;

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

public class JdbcTest06 {
    public static void main(String[] args) {
        Connection conn = null;
        PreparedStatement pst = null;
        ResultSet rs = null;
        try {
            conn = JdbcUtils.getConnection();
            //关闭自动提交事务，JDBC会自动开启事务
            conn.setAutoCommit(false);
            //写入SQL语句
            String sql1 = "UPDATE `account` SET `money` = money - 500 WHERE `name` = 'A';";
            pst=conn.prepareStatement(sql1);
            pst.executeUpdate();
            String sql2 = "UPDATE `account` SET `money` = money + 500 WHERE `name` = 'B';";
            pst=conn.prepareStatement(sql2);
            pst.executeUpdate();
            //提交事务
            conn.commit();
            //重新开启自动提交事务
            conn.setAutoCommit(true);
            System.out.println("操作成功");
        } catch (SQLException e) {
            //如果失败，回滚业务
            try {
                conn.rollback();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
            e.printStackTrace();
        }finally {
            JdbcUtils.release(conn,pst,rs);
        }
    }
}

结果：

测试回滚

将A、B账号重新改成1000，在两条SQL语句中插入错误代码，测试看是否会回滚。

package TestJDBC.Demo02;

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

public class JdbcTest06 {
    public static void main(String[] args) {
        Connection conn = null;
        PreparedStatement pst = null;
        ResultSet rs = null;
        try {
            conn = JdbcUtils.getConnection();
            //关闭自动提交事务，JDBC会自动开启事务
            conn.setAutoCommit(false);
            //写入SQL语句
            String sql1 = "UPDATE `account` SET `money` = money - 500 WHERE `name` = 'A';";
            pst=conn.prepareStatement(sql1);
            pst.executeUpdate();
            //插入错误代码，上面已经执行，但这里报错后，下方代码不再执行。
            int x = 1/0
            String sql2 = "UPDATE `account` SET `money` = money + 500 WHERE `name` = 'B';";
            pst=conn.prepareStatement(sql2);
            pst.executeUpdate();
            //提交事务
            conn.commit();
            //重新开启自动提交事务
            conn.setAutoCommit(true);
            System.out.println("操作成功");
        } catch (SQLException e) {
            //如果失败，回滚业务
            try {
                conn.rollback();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
            e.printStackTrace();
        }finally {
            JdbcUtils.release(conn,pst,rs);
        }
    }
}

运行代码报错：

测试是否回滚，数据未发生变化：

数据库连接池

数据库的操作：数据库连接->执行完毕->释放。

连接->释放：这是十分浪费系统资源的。

池化技术：准备一些预留的资源，过来就连接预备好的资源。

准备一些连接数预留在那，设置最小连接数和最大连接数（业务最高承载上线）

如果超过最大连接数，就排队等待。

如果等待超过某一时间，则等待超时

参数：

最小连接数：10

最大连接数：50

等待超时：100ms

……

实现类：

开源数据源实现：

DBCP

C3P0

Druid:阿里巴巴出品

使用了这些数据库连接池之后，我们在项目开发中就不需要编写连接数据库的代码。

了解即可，后续框架会用到。

以上为Java操作MySQL的基础知识笔记。

至此MySQL基础知识已全部完结。

posted @ 2021-02-12 12:41 大川NV 阅读(92) 评论(0) 收藏举报

刷新页面返回顶部

大川NV的空间

JDBC --学习笔记

JDBC

数据库驱动

JDBC

第一个JDBC程序

首先打开MySQL，创建测试数据库`jdbcStudy`

使用IDEA创建JDBC项目

导入驱动

编写测试代码

步骤总结

JDBC对象

`DriverManager`

URL

`Connection`

`Statement` `PrepareStatement`

`ResultSet`

`.close()`

JDBC程序编写

`db.properties`配置

`JdbcUtils`配置

`JdbcTest01`配置实现增（删、改类似）

`JdbcTest02`配置实现查询

SQL注入问题

`PreparedStatement`防止SQL注入

使用IDEA连接数据库

JDBC事务操作

创建测试表和测试数据

数据库连接池

参数：

实现类：

公告

大川NV的空间

JDBC --学习笔记

JDBC

数据库驱动

JDBC

第一个JDBC程序

首先打开MySQL，创建测试数据库jdbcStudy

使用IDEA创建JDBC项目

导入驱动

编写测试代码

步骤总结

JDBC对象

DriverManager

URL

Connection

Statement PrepareStatement

ResultSet

.close()

JDBC程序编写

db.properties配置

JdbcUtils配置

JdbcTest01配置实现增（删、改类似）

JdbcTest02配置实现查询

SQL注入问题

PreparedStatement防止SQL注入

使用IDEA连接数据库

JDBC事务操作

创建测试表和测试数据

数据库连接池

参数：

实现类：

公告

首先打开MySQL，创建测试数据库`jdbcStudy`

`DriverManager`

`Connection`

`Statement` `PrepareStatement`

`ResultSet`

`.close()`

`db.properties`配置

`JdbcUtils`配置

`JdbcTest01`配置实现增（删、改类似）

`JdbcTest02`配置实现查询

`PreparedStatement`防止SQL注入