[SQL] JDBC

程序员文章站 2024-03-01 18:19:28

...

第一章 JDBC

1.1 基本概念

概念：( Java DataBase Connectivity standard Java数据库连接，Java语言操作数据库**）** 他定义了操作所有关系型数据库的规则（接口）。

具体操作什么数据库用接口实现类实现，这个实现类叫做数据库驱动

JDBC本质：其实是官方（sun公司）定义的一套操作所有关系型数据库的规则，即接口。各个数据库厂商去实现这套接口，提供数据库驱动jar包。我们可以使用这套接口（JDBC）编程，真正执行的代码时驱动jar包中的实现类 （其实就是使用了多态来编程）

1.2 快速入门

步骤

导入驱动jar包（导入到项目中的libs文件夹里面，然后右键这个包，选择将这个包加入到库）
注册驱动
获取数据库连接对象 Connection
定义sql
获取执行sql语句的对象 statement
执行sql ，接受返回结果
处理结果
释放资源

package demo12;



/*
 * JDBC快速入门
 * */

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

public class JDBCDemo {

    public static void main(String[] args) throws Exception {

        //2 注册驱动
        Class.forName("com.mysql.cj.jdbc.Driver");


        //3 获取数据库的连接对象
        Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/cjy?serverTimezone=GMT%2B8", "root", "root");//注意数据库后面的设置时区，不然会报错

        //4 定义sql语句
        String sql = "update account set balance = 1500";

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


        //6 执行sql
        int count = stmt.executeUpdate(sql);

        //7 处理结果
        System.out.println(count);//返回2， 因为这里有两行数据被修改了

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

上面我们会发现这里涉及到了三个我们没有见过的东西

DriverManager：驱动管理对象
Connection：数据库连接对象
Statement：执行sql的对象

**当然，除此之外，JDBC中还有两个常用的接口东西

ResultSet：结果集对象
PreparedStatement：执行sql的对象

下面，我们来介绍这些类

1.3 DriverManager

在API文档中，我们发现他是一个类

他有如下的功能:

注册驱动

其作用就是告诉该程序要使用哪个数据库驱动jar包

方法如下(一个静态方法):

static void registerDriver (Driver driver) 使用 DriverManager注册给定的驱动程序。

但是我们写代码的时候,用下面这个语句来注册驱动

Class.forName("com.mysql.cj.jdbc.Driver");

那么两者之间有什么关系呢?

我们观察这个字节码文件的源码

package com.mysql.cj.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!");
        }
    }
}

我们发现了,在加载这个字节码文件的时候,里面有一个静态代码块(里面的方法一定会被执行),在这里面就有DriverManger的这个注册方法,也就是说,加载这个字节码文件的时候,这个方法就被执行了

其作用就是告诉该程序要使用哪个数据库驱动jar包(再次强调)

注意: Mysql 5 之后的驱动jar包可以省略注册驱动的步骤( 因为jar包里面保存的注册驱动的配置文件)

图片所指位置就是配置保存的地方

[SQL] JDBC

获取数据库连接

方法如下(一个静态方法)

static Connection getConnection (String url, String user, String password) 尝试建立与给定数据库URL的连接。

我们来说一下url参数

url: 指定连接的路径

我们看看例子中的代码

Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/cjy?serverTimezone=GMT%2B8", "root", "root");

Mysql的url语法(适用于mysql)

jdbc:mysql://ip地址(域名):端口号/数据库名称[?一些参数]

参数是可选项

细节: 如果连接的是本机的服务器,并且端口也是默认的,那么语法可以简写为

jdbc:mysql:///数据库名称[?一些参数]

1.4 Connection

他是一个接口

功能

获取执行sql的对象

方法1

	Statement createStatement() 创建一个 Statement对象，用于将SQL语句发送到数据库。  
    
	Statement createStatement(int resultSetType, int resultSetConcurrency) 创建一个 Statement对象，该对象将生成具有给定类型和并发性的 ResultSet对象。  
    
	Statement createStatement(int resultSetType, int resultSetConcurrency, int resultSetHoldability)

方法2

	PreparedStatement prepareStatement(String sql) 创建一个 PreparedStatement对象，用于将参数化的SQL语句发送到数据库。  
    
	PreparedStatement prepareStatement(String sql, int autoGeneratedKeys) 创建一个默认的 PreparedStatement对象，该对象具有检索自动生成的**的能力。 
    
	PreparedStatement prepareStatement(String sql, int[] columnIndexes) 创建一个默认的 PreparedStatement对象，能够返回由给定数组指定的自动生成的键。
    
	PreparedStatement prepareStatement(String sql, int resultSetType, int resultSetConcurrency) 创建一个 PreparedStatement对象，该对象将使用给定类型和并发性生成 ResultSet对象。  
    
	PreparedStatement prepareStatement(String sql, int resultSetType, int resultSetConcurrency, int resultSetHoldability) 创建一个 PreparedStatement对象，该对象将生成具有给定类型，并发和 ResultSet对象。 
    
	PreparedStatement prepareStatement(String sql, String[] columnNames) 创建一个默认的 PreparedStatement对象，能够返回给定数组指定的自动生成的键

管理事务

//开启事务
void setAutoCommit(boolean autoCommit) 将此连接的自动提交模式设置为给定状态。  (将这个布尔值设为false则是开启事务)

//提交事务
void commit() 使上次提交/回滚之后所做的所有更改都将永久性，并释放此 Connection对象当前持有的任何数据库锁。 

//回滚事务
 void rollback() 撤消在当前事务中所做的所有更改，并释放此 Connection对象当前持有的任何数据库锁。

1.5 Statement

他是一个接口

功能

执行静态SQL语句

//掌握 executeUpdate方法 (执行的是DML和DDL语句,这个两个语句就是表的修改的语句(insert,update,delete)和创建表和库(create,drop,alter)的语句)\

//long返回值的意思是影响的行数,如果影响的行数是0,那么有可能就是执行失败了,注意,如果我们用这个运行的是DDL语句,那么我们返回值就是0

default long executeLargeUpdate(String sql) 执行给定的SQL语句，这可能是 INSERT ， UPDATE ，或 DELETE声明，或者不返回任何内容，如SQL DDL语句的SQL语句。  
    
default long executeLargeUpdate(String sql, int autoGeneratedKeys) 执行给定的SQL语句，并用给定的标志来向驱动程序发出信号，指出这个 Statement对象生成的自动生成的**是否应该可用于检索。  
    
default long executeLargeUpdate(String sql, int[] columnIndexes) 执行给定的SQL语句，并向驱动程序发出信号，指出给定数组中指示的自动生成的键应该可用于检索。  
    
default long executeLargeUpdate(String sql, String[] columnNames) 执行给定的SQL语句，并向驱动程序发出信号，指出给定数组中指示的自动生成的键应该可用于检索。 


//掌握 executeQuery语句(执行DQL语句)
 
 ResultSet executeQuery(String sql) 执行给定的SQL语句，返回一个 ResultSet对象。  
    

//了解execute方法即可 (可以执行任意sql语句)
boolean execute(String sql) 执行给定的SQL语句，这可能会返回多个结果。
    
boolean execute(String sql, int autoGeneratedKeys) 执行给定的SQL语句，这可能返回多个结果，并向驱动程序发出信号，指出任何自动生成的**应该可用于检索。  
    
boolean execute(String sql, int[] columnIndexes) 执行给定的SQL语句，这可能返回多个结果，并向驱动程序发出信号，指出给定数组中指示的自动生成的键应该可用于检索。  
    
boolean execute(String sql, String[] columnNames) 执行给定的SQL语句，这可能返回多个结果，并向驱动程序发出信号，指出给定数组中指示的自动生成的键应该可用于检索。

练习

account 添加一条记录

package demo12;

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

public class JDBC {
    public static void main(String[] args) {
        Statement sta = null;
        Connection conn = null;
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");

            String sql = "insert into account values(NULL,'wangwu',3000)";

            conn = DriverManager.getConnection("jdbc:mysql:///cjy?serverTimezone=GMT%2B8", "root", "root");

            sta = conn.createStatement();

            System.out.println(sta.executeUpdate(sql) > 0 ? "添加成功" : "添加失败");


        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            //为了避免空指针异常
            if (sta != null) {
                try {
                    sta.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }
        }


    }
}

account 修改一条记录

懒得演示了,就是sql字符串那里修改一下就行了

account 删除一条记录

这里也是,字符串我随便改一个"delete from account where id = 3"就行了

1.6 ResultSet对象

1.6.1 概述

通俗来讲,每次我们使用DQL语句返回的表就是一个结果集对象

那么,我们如何得到这个对象里面的数据呢?

下面我对这个对象储存数据的形式进行一下讲解

[SQL] JDBC

我们查找数据是要通过**"游标"来查找的**,而这个游标默认的位置是第一行(就是id name balance这些字样的位置),因此,我们如果要读取到数据,那么我们就要把游标指到正确的位置并且读取数据

有什么方法呢?

boolean next() 将光标从当前位置向前移动一行。  
    
XXX getXXX(参数)  这里的XXX就是我们游标指针指向的数据类型,一定要类型匹配才能正确读取到数据
    //这里的参数有两种
    	//1 int类型    int类型要求输入列号,从1开始
    	//2 String    String类型就是输入列的名字

下面我们来尝试query这些数据

练习

代码如下

package demo12;

import java.sql.*;

public class JDBC {
    public static void main(String[] args) {
        Statement sta = null;
        Connection conn = null;
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");

            String sql = "select * from account";

            conn = DriverManager.getConnection("jdbc:mysql:///cjy?serverTimezone=GMT%2B8", "root", "root");

            sta = conn.createStatement();


            //执行查询的方法
            ResultSet res = sta.executeQuery(sql);

            //我们来查看结果
            //1 让游标向下移动一行
            res.next();
            //2 获取数据
            int id = res.getInt(1);
            String name = res.getString(2);
            double balance = res.getDouble(3);
            System.out.println(id + "  " + name + "  " + balance);
            res.next();
            id = res.getInt(1);
            name = res.getString(2);
            balance = res.getDouble(3);
            System.out.println(id + "  " + name + "  " + balance);

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            //为了避免空指针异常
            if (sta != null) {
                try {
                    sta.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }
        }
    }
}
//运行结果
1  zhangsan  2000.0
2  lisi  2000.0

    //不错,很巴适

但是我们发现有点麻烦,我不想这么读取数据,那么我们该怎么遍历呢?

真正的遍历方法

步骤

游标向下移动一行
判断是否有数据 (用next()方法,他的返回值是布尔,他会判断游标有没有超过界限,超过了就会返回false)
有数据就获取数据

代码如下

while (res.next()) {
                int id = res.getInt(1);
                String name = res.getString(2);
                double balance = res.getDouble(3);
                System.out.println(id + "  " + name + "  " + balance);
            }

和iterator迭代器和字节流的读取方法有异曲同工之妙

1.7 工具类

我们会发现,每次我们想要调用数据库,总是要导入字节码文件,连接到数据库,获取数据库连接对象,还有获取statement对象来执行sql语句,最后还要释放资源,特别麻烦,特别多冗余的步骤,因此,我们自己写一个工具类,来简化这些步骤

注意

首先,写工具类,一般命名最后都会加s, 例如: collections, Arrays,Objects等工具类
其次,工具类里面我们很容易发现一般里面都是静态方法,所以我们写的方法也尽量保持是静态方法

所以将自定义的工具类取名叫做JDBCUtils

分析:

自动注册驱动的抽取(调用这个类的时候自动就会注册驱动,不需要专门执行方法)
抽取一个方法获取连接对象
- 我这个方法不想传递参数,还得保证工具类的通用性
- 解决方法: 配置文件(jdbc.properties)
- ```
//文件内容
url = xxx
user = xxx
password = xxx
driver = xxx
```
- 注意,这中方法很常用,注意一定要掌握!
抽取一个方法释放资源

下面我们直接放代码

//配置文件放在根目录,取名jdbc.properties
className = demo11.domain.Student
methodName = sleep
//我们做的工具类
package demo12.util;

import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;

/**
 * JDBC工具类
 */

public class JDBCUtils {
    private static final String url;
    private static final String user;
    private static final String password;
    private static final String driver;

    //配置文件的读取,只需要一次就可以了,用静态代码块
    static {
        Properties pro = new Properties();

        ClassLoader cl = JDBCUtils.class.getClassLoader();

        InputStream is = cl.getResourceAsStream("jdbc.properties");

        try {
            pro.load(is);
        } catch (IOException e) {
            e.printStackTrace();
        }

        url = pro.getProperty("url");

        user = pro.getProperty("user");

        password = pro.getProperty("password");

        driver = pro.getProperty("driver");

        try {
            Class.forName(driver);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }

    }




    //获取连接对象
    public static Connection getConnection() throws SQLException {

        return DriverManager.getConnection(url, user, password);

    }

    //关闭资源
    public static void close(Statement stmt, Connection conn) {
        judge(stmt, conn);


    }

    public static void judge(Statement stmt, Connection conn) {
        if (stmt != null) {
            try {
                stmt.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        if (conn != null) {
            try {
                conn.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
    }

}
//测试函数
package demo12;

import demo12.util.JDBCUtils;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;

public class JDBCDemo {

    public static void main(String[] args) throws Exception {


        Connection conn = JDBCUtils.getConnection();


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

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


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

        //7 处理结果
        while (res.next()){
            System.out.println(res.getInt(1) + " \t" + res.getString(2) + "\t" +  res.getDouble(3));
        }

        //8 释放资源
        JDBCUtils.close(stmt,conn);
    }
}

我们发现已经简单了特别多

1.8 综合练习

通过工具类来实现

某用户通过键盘录入用户名和密码

判断用户是否登陆成功

分析

我们的数据库里面保存了许多用户的用户名其密码
通过检测数据库的用户名和密码来提示用户成功还是失败

我们创建一个数据库,然后创建一个表,里面保存账户和密码

-- 创建代码如下
CREATE DATABASE db4;

USE db4;

CREATE TABLE USER(
	id INT  PRIMARY KEY AUTO_INCREMENT,
	username VARCHAR(32),
	PASSWORD VARCHAR(32)
);


INSERT INTO USER VALUES(NULL,'zahngsan','123');
INSERT INTO USER VALUES(NULL,'lisi','666');
INSERT INTO USER VALUES(NULL,'wangwu','888');

数据库图示:

[SQL] JDBC

现在我们来实现(个人方法)

package demo12;

import demo12.util.JDBCUtils;

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

public class JDBCRegister {
    public static void main(String[] args) throws SQLException {
        Scanner cin = new Scanner(System.in);

        System.out.println("请输入账号:");

        String userName = cin.next();

        System.out.println("请输入密码:");

        String password = cin.next();

        //先做一个判断,如果输入为空,那么直接return
        if (userName == null || password ==null){
            System.out.println("您输入的是空值,请重新输入!");
            return;
        }

        //获取连接
        Connection conn = JDBCUtils.getConnection();

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

        String sql  = "select * from user where username = '" + userName + "' and password = '" + password + "'";

        //结果
        ResultSet res = stmt.executeQuery(sql);

        //因为结果只有可能有一个,所以直接判断有没有下一行
        boolean flag = res.next();

        //做判断
        if (flag){
            System.out.println("恭喜您登录成功!");
        }else{
            System.out.println("您输入的用户名或者账号有误,请重新输入!");
        }

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

    }
}



//运行结果
请输入账号:
lisi
请输入密码:
666
恭喜您登录成功!

1.9 PreparedStatement接口

分析上面这个案例,我们发现输入的时候存在严重的bug, 我们可以在密码输入一些sql的条件(例如: 在password的地方输入a' or 'a' = 'a),让他的条件判断为真,这样就算我们账号或密码是乱输的,照样可以登录成功,这就是MySQL注入问题

**MySQL注入问题: **

在拼接sql的时候,有一些sql的特殊关键字参与字符串的拼接 , 会造成字符串安全性问题.

那么如何解决呢?

使用PreparedStatement接口来解决这个问题,这个接口专门用来解决sql的注入问题
- 这个接口遗传自Statement类

那么Statement和这个PreparedStatement有什么区别呢?

Statement用的是静态的sql语句(即拼接完成之后就固定了)
PreparedStatement使用的是预编译的SQL: 参数使用? 作为占位符

使用PreparedStatement的步骤(无法运行,知道意思就好)

Scanner cin = new Scanner(System.in);

System.out.println("请输入账号:");

String userName = cin.next();

System.out.println("请输入密码:");

String password = cin.next();
//获取连接
Connection conn = JDBCUtils.getConnection();

//上面sql语句我们使用?来作为占位符
String sql  = "select * from user where username = ?  AND  password = ?";

//用Connection的 prepareStatement()方法获取prepareStatement对象 (这里代替了Statement),并且这一步相比于statement,这里直接提前传入了sql语句(但是?要赋值)
PrepareStatement pstmt = conn.prepareStatement(sql);

//给? 赋值 对象名.setXXX(?的位置编号(从1开始), ?的值)  XXX是参数类型
pstmt.setString(1,userName);
pstmt.setString(2,password);

//这里不用传递sql了,直接执行sql语句
ResultSet res = pstmt.executeQuery();//免参

//剩下的都一样了

这样我们就可以解决注入问题了,我们其实一直使用的是这个类来工作的

解决注入问题
效率更高

第二章事务管理

2.1 概述

事务 : 一个包含多个步骤的业务操作,. 如果这个业务操作被事务管理,则这多个步骤要么同时成功,要么同时失败.

操作

开启事务
提交事务
回滚事务

那么,我们在Java中通过什么实现这些呢?

用Connection接口里面的方法

//开启事务
void setAutoCommit(boolean autoCommit) 将此连接的自动提交模式设置为给定状态。  (将这个布尔值设为false则是开启事务)

//提交事务
void commit() 使上次提交/回滚之后所做的所有更改都将永久性，并释放此 Connection对象当前持有的任何数据库锁。 

//回滚事务
 void rollback() 撤消在当前事务中所做的所有更改，并释放此 Connection对象当前持有的任何数据库锁。

2.2 使用

比较简单,就不演示了哈哈哈哈哈哈哈哈哈哈

注意

执行sql之前开启事务
当所有sql都执行完提交之后,结束资源之前提交事务

相关标签：笔记 Java SQL 数据库 mysql jdbc

上一篇：缺失值的前期处理

下一篇： SpringBoot项目访问任意接口出现401错误的解决方案

[SQL] JDBC

这一章十分重要，建议重点掌握

第一章 JDBC

1.1 基本概念

1.2 快速入门

1.3 DriverManager

注册驱动

获取数据库连接

1.4 Connection

获取执行sql的对象

管理事务

1.5 Statement

执行静态SQL语句

练习

account 添加一条记录

account 修改一条记录

account 删除一条记录

1.6 ResultSet对象

1.6.1 概述

练习

真正的遍历方法

1.7 工具类

1.8 综合练习

1.9 PreparedStatement接口

第二章事务管理

2.1 概述

2.2 使用

sql：mysql：列转行

[SQL] JDBC

MySQL复制表结构和内容到另一张表中的SQL语句

在ASP.NET中连接SQL Server的简单方法

sql模糊查询多个条件写法

mysql 查询重复的数据的SQL优化方案

JDBC对MySQL数据库布尔字段的操作方法

利用python模拟sql语句对员工表格进行增删改查

JDBC批处理sql

JDBC 处理查询

[SQL] JDBC

这一章十分重要，建议重点掌握

第一章 JDBC

1.1 基本概念

1.2 快速入门

1.3 DriverManager

注册驱动

获取数据库连接

1.4 Connection

获取执行sql的对象

管理事务

1.5 Statement

执行静态SQL语句

练习

account 添加一条记录

account 修改一条记录

account 删除一条记录

1.6 ResultSet对象

1.6.1 概述

练习

真正的遍历方法

1.7 工具类

1.8 综合练习

1.9 PreparedStatement接口

第二章 事务管理

2.1 概述

2.2 使用

sql：mysql：列转行

[SQL] JDBC

MySQL复制表结构和内容到另一张表中的SQL语句

在ASP.NET中连接SQL Server的简单方法

sql模糊查询多个条件写法

mysql 查询重复的数据的SQL优化方案

JDBC对MySQL数据库布尔字段的操作方法

利用python模拟sql语句对员工表格进行增删改查

JDBC批处理sql

JDBC 处理查询

第二章事务管理