MySQL进阶（一）：JDBC

最新推荐文章于 2023-02-03 09:52:49 发布

1橘猫

最新推荐文章于 2023-02-03 09:52:49 发布

阅读量293

点赞数

分类专栏： # 数据库 # JavaSE 后端学习

本文链接：https://blog.csdn.net/konley233/article/details/107186969

版权

后端学习同时被 3 个专栏收录

59 篇文章 2 订阅

订阅专栏

JavaSE

13 篇文章 0 订阅

订阅专栏

数据库

12 篇文章 0 订阅

订阅专栏

by konley

mysql进阶第一篇，JDBC

一、JDBC

1.1 概念

JDBC : Java DataBase Connectivity , 即为Java数据库连接、Java语言操作数据库

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

1.2 快速入门

1.2.1 使用步骤

	1. 导入驱动jar包 mysql-connector-java-5.1.37-bin.jar
		1.复制mysql-connector-java-5.1.37-bin.jar到项目的libs目录下
		2.右键-->Add As Library
	2. 注册驱动
	3. 获取数据库连接对象 Connection
	4. 定义sql
	5. 获取执行sql语句的对象 Statement
	6. 执行sql，接受返回结果
	7. 处理结果
	8. 释放资源

1.2.2 代码实现

package JDBC;

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

/**
 * @author konley
 * @date 2020-07-06 13:09
 * JDBC简单入门
 */
public class Demo01JDBC {
    public static void main(String[] args) throws Exception {
        //注册驱动
        Class.forName("com.mysql.jdbc.Driver");
        //获取连接对象
        Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/jdbc_test", "root", "123456");
        //定义sql
        String sql = "update emp set salary=8500 where id=1001";
        //获取执行sql对象 statement
        Statement statement = connection.createStatement();
        //执行sql
        int i = statement.executeUpdate(sql);
        //处理结果
        System.out.println(i);
        //释放资源
        statement.close();
        connection.close();
    }
}

1.2.3 DriverManager：驱动管理对象

1. 注册驱动：告诉程序该使用哪一个数据库驱动jar

注册与给定的驱动程序 DriverManager的方法

static void registerDriver(Driver driver)

实际编码

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

为什么能获取到DriverManager？查看源码发现：在com.mysql.jdbc.Driver类中存在静态代码块

//Driver类源码
static {
    try {
	 	java.sql.DriverManager.registerDriver(new Driver());
	} catch (SQLException E) {
		 throw new RuntimeException("Can't register driver!");
	}
}

注意：mysql5之后的驱动可以省略注册驱动的步骤

2. 获取数据库连接

方法：

static Connection getConnection(String url,String user,String password);

参数：

url：指定连接的路径
- 语法：jdbc:mysql://ip地址(域名):端口号/数据库名称
- 例子：jdbc:mysql://localhost:3306/jdbc_test
- 细节：如果连接的是本机mysql服务器，并且mysql服务默认端口是3306，则url可以简写为：jdbc:mysql:///数据库名称
user：用户名
password：密码

1.2.4 Connection：数据库连接对象

1. 获取执行sql对象（Statement）

获取Statement

Statement createStatement();

获取PreparedStatement

PreparedStatement prepareStatement(String sql)

2. 管理事务

1.2.5 Statement：执行sql的对象

1. 执行sql语句

boolean execute(String sql)：可以执行任意的sql(不常用)
int executeUpdate(String sql) ：执行DML（insert、update、delete）语句、DDL(create，alter、drop)语句
ResultSet executeQuery(String sql) ：执行DQL（select)语句

2. CRUD练习

/**
 * @author konley
 * @date 2020-07-06 20:40
 * JDBC更新
 */
public class Demo02JDBCUpdate {
    public static void main(String[] args){
        Connection connection = null;
        Statement statement = null;
        try {
            //注册驱动
            Class.forName("com.mysql.jdbc.Driver");
            //定义连接信息
            String url = "jdbc:mysql://localhost:3306/jdbc_test";
            String user = "root";
            String password = "123456";
            //获得连接
            connection = DriverManager.getConnection(url,user,password);
            //定义sql
            String sql = "update emp set salary = 8000 where id=1001";
            //定义statement对象
            statement = connection.createStatement();
            //执行sql
            int i = statement.executeUpdate(sql);
            //查看结果
            if (i>0){
                System.out.println("执行成功!");
            }else{
                System.out.println("执行失败!");
            }
        }catch (SQLException e){
            System.out.println("执行sql异常"+e.getMessage());
        }catch (ClassNotFoundException e){
            System.out.println("数据库连接异常"+e.getMessage());
        }
        finally {
            try {
                //关闭statement和connection
                if(statement!=null) {
                    statement.close();
                }
                if(connection!=null){
                    connection.close();
                }
            }catch (SQLException e){
                System.out.println("关闭对象异常"+e.getMessage());
            }
        }
    }
}

/**
 * @author konley
 * @date 2020-07-06 21:14
 * JDBC删除
 */
public class Demo04JDBCDelete {
    public static void main(String[] args) throws SQLException {
        Connection con = null;
        Statement stmt = null;
        try {
            Class.forName("com.mysql.jdbc.Driver");
            con = DriverManager.getConnection("jdbc:mysql://localhost/jdbc_test","root","123456");
            String sql = "delete from emp where id=1015";
            stmt = con.createStatement();
            stmt.executeUpdate(sql);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            if(con != null){
                con.close();
            }
            if(stmt != null){
                stmt.close();
            }
        }
    }
}

/**
 * @author konley
 * @date 2020-07-06 21:03
 * JDBC添加
 */
public class Demo03JDBCInsert {
    public static void main(String[] args) throws SQLException {
        Connection con = null;
        Statement stmt = null;
        try {
            Class.forName("com.mysql.jdbc.Driver");
            String url = "jdbc:mysql://localhost/jdbc_test";
            String user = "root";
            String password = "123456";
            con = DriverManager.getConnection(url,user,password);
            stmt = con.createStatement();
            String sql = "insert into emp values(1015,'konley',4,1006,'2020-06-14',13000.00,null,10)";
            stmt.executeUpdate(sql);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            if (stmt != null){
                stmt.close();
            }
            if (con != null){
                stmt.close();
            }
        }
    }
}

/**
 * @author konley
 * @date 2020-07-06 21:14
 * JDBC查询
 */
public class Demo05JDBCQuery {
    public static void main(String[] args) throws SQLException {
        Connection con = null;
        Statement stmt = null;
        ResultSet resultSet = null;
        try {
            Class.forName("com.mysql.jdbc.Driver");
            con = DriverManager.getConnection("jdbc:mysql://localhost/jdbc_test","root","123456");
            String sql = "select * from job";
            stmt = con.createStatement();
            resultSet = stmt.executeQuery(sql);
            while (resultSet.next()){
                System.out.print("编号:"+resultSet.getInt(1)+",");
                System.out.print("名称:"+resultSet.getString(2)+",");
                System.out.print("描述:"+resultSet.getString(2)+"\n");
            }
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            if(con != null){
                con.close();
            }
            if(resultSet != null){
                resultSet.close();
            }
            if(stmt != null){
                stmt.close();
            }
        }
    }
}

1.2.6 ResultSet：结果集对象,封装查询结果

1. 方法

boolean next()：游标向下移动一行，判断当前行是否是最后一行末尾(是否有数据)，如果是，则返回false，如果不是则返回true
getXXX(参数)：获取结果集中的数据
- xxx：代表参数类型，如int getInt()、String getString()
- 参数：
  - int ：代表列的编号,从1开始，如getString（1）
  - String：代表列名称。如getDouble("balance")

2. 注意

使用步骤：
1. 游标向下移动一行
1. 判断是否有数据
2. 获取数据

代码示范

//循环判断游标是否是最后一行末尾。
while (resultSet.next()){
   System.out.print("编号:"+resultSet.getInt(1)+",");
   System.out.print("名称:"+resultSet.getString(2)+",");
   System.out.print("描述:"+resultSet.getString(2)+"\n");
}

3. 练习

定义一个方法，查询emp表的数据将其封装为对象，然后装载集合，返回。
1. 定义Emp类
1. 定义方法 public List findAll(){}
2. 实现方法 select * from emp;

代码示范

/**
 * @author konley
 * @date 2020-07-06 22:09
 * 员工实体类
 */
public class Demo06Emp {
    private int id;
    private String ename;
    private int job_id;
    private int mgr;
    private String date;
    private double salary;
    private double bonus;
    private int dept_id;

    public Demo06Emp() {
    }

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getEname() {
        return ename;
    }

    public void setEname(String ename) {
        this.ename = ename;
    }

    public int getJob_id() {
        return job_id;
    }

    public void setJob_id(int job_id) {
        this.job_id = job_id;
    }

    public int getMgr() {
        return mgr;
    }

    public void setMgr(int mgr) {
        this.mgr = mgr;
    }

    public String getDate() {
        return date;
    }

    public void setDate(String date) {
        this.date = date;
    }

    public double getSalary() {
        return salary;
    }

    public void setSalary(double salary) {
        this.salary = salary;
    }

    public double getBonus() {
        return bonus;
    }

    public void setBonus(double bonus) {
        this.bonus = bonus;
    }

    public int getDept_id() {
        return dept_id;
    }

    public void setDept_id(int dept_id) {
        this.dept_id = dept_id;
    }

    @Override
    public String toString() {
        return "Demo06Emp{" +
                "id=" + id +
                ", ename='" + ename + '\'' +
                ", job_id=" + job_id +
                ", mgr=" + mgr +
                ", date='" + date + '\'' +
                ", salary=" + salary +
                ", bonus=" + bonus +
                ", dept_id=" + dept_id +
                '}';
    }

    public Demo06Emp(int id, String ename, int job_id, int mgr, String date, double salary, double bonus, int dept_id) {
        this.id = id;
        this.ename = ename;
        this.job_id = job_id;
        this.mgr = mgr;
        this.date = date;
        this.salary = salary;
        this.bonus = bonus;
        this.dept_id = dept_id;
    }
}

/**
 * @author konley
 * @date 2020-07-06 22:13
 * JDBC查询并封装实体类
 */
public class Demo06JDBCQuery2 {
    public static void main(String[] args) throws SQLException {
        List<Demo06Emp> list = findAll();
        for (Demo06Emp demo06Emp : list) {
            System.out.println(demo06Emp);
        }
    }

    public static List<Demo06Emp> findAll() throws SQLException {
        Connection con = null;
        Statement stmt = null;
        ResultSet rs = null;
        List<Demo06Emp> list = new ArrayList<>();
        try {
            Class.forName("com.mysql.jdbc.Driver");
            con = DriverManager.getConnection("jdbc:mysql://localhost/jdbc_test","root","123456");
            stmt = con.createStatement();
            rs = stmt.executeQuery("select * from emp");
            while (rs.next()){
                Demo06Emp emp = new Demo06Emp(
                    rs.getInt(1),
                    rs.getString(2),
                    rs.getInt(3),
                    rs.getInt(4),
                    rs.getString(5),
                    rs.getDouble(6),
                    rs.getDouble(7),
                    rs.getInt(8)
                );
                list.add(emp);
            }
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } finally {
            if(con != null){
                con.close();
            }
            if(stmt != null){
                stmt.close();
            }
            if(rs != null){
                rs.close();
            }
        }
        return list;
    }
}

1.2.6 PreparedStatement：执行sql的对象

1. sql注入问题

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

例子（以用户登录为例）：

输入用户随便，输入密码：a’ or ‘a’ = 'a
此时sql为：select * from user where username = 'fhdsjkf' and password = 'a' or 'a' = 'a'
直接绕过登录了

解决sql注入问题：使用PreparedStatement对象来解决

PreparedStatement使用预编译的SQL：参数使用?作为占位符

2. 步骤

1. 导入驱动jar包 mysql-connector-java-5.1.37-bin.jar
2. 注册驱动
3. 获取数据库连接对象 Connection
4. 定义sql
	* 注意：sql的参数使用？作为占位符。 
	如：select * from user where username = ? and password = ?;
	
5. 获取执行sql语句的对象 
	PreparedStatement Connection.prepareStatement(String sql) 

6. 给？赋值：
	* 方法： setXxx(参数1,参数2)
		* 参数1：？的位置编号 从1 开始
		* 参数2：？的值
		
7. 执行sql，接受返回结果，不需要传递sql语句
8. 处理结果
9. 释放资源

3. 提示

尽量使用PreparedStatement来完成增删改查的操作
可以防止SQL注入
效率更高

二、JDBC工具类：JDBCUtils

2.1 目的

使用工具类可以简化书写，大大的提高效率

2.2 分析

封装注册驱动（使用静态代码块）
封装一个方法获取连接对象

需求：不想传递参数（麻烦），还得保证工具类的通用性。
解决：创建properties配置文件

封装两个方法释放资源

2.3 代码实现

/*
	jdbc.properties -- 数据库连接配置文件
*/
driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost/jdbc_test
user=root
password=123456

/**
 * @author konley
 * @date 2020-07-06 22:28
 * JDBC工具类
 */

public class Demo07JDBCUtils {

    private static String url;
    private static String user;
    private static String password;
    private static String driver;

    static {
        try {
            Properties properties = new Properties();
            properties.load(new FileReader("src/JDBC/jdbc.properties"));
            driver = properties.getProperty("driver");
            url = properties.getProperty("url");
            user = properties.getProperty("user");
            password = properties.getProperty("password");
            Class.forName(driver);
        } catch (FileNotFoundException e) {
            System.out.println("找不到Properties配置文件:"+e.getMessage());
        } catch (IOException e) {
            System.out.println("读取properties配置文件异常:"+e.getMessage());
        } catch (ClassNotFoundException e) {
            System.out.println("找不到驱动:"+e.getMessage());
        }
    }

    public static Connection getConnection(){
        Connection con = null;
        try {
            con = DriverManager.getConnection(url,user,password);
        }  catch (SQLException e) {
            System.out.println("数据库异常"+e.getMessage());
        }
        return con;
    }
    public static void close(Connection con, Statement stmt, ResultSet rs){
        try {
            if(con!=null){
                con.close();
            }
            if(stmt!=null){
                stmt.close();
            }
            if(rs!=null){
                rs.close();
            }
        }catch (SQLException e){
            System.out.println("关闭Connection失败:"+e.getMessage());
        }

    }
    public static void close(Connection con, Statement stmt){
        try {
            if(con!=null){
                con.close();
            }
            if(stmt!=null){
                stmt.close();
            }
        }catch (SQLException e){
            System.out.println("关闭Connection失败:"+e.getMessage());
        }
    }
}

2.4 练习

使用JDBCUtils编写一个简单的登录验证，从控制台获取输入。

--创建数据库表user
    CREATE TABLE USER(
	id INT PRIMARY KEY AUTO_INCREMENT,
		username VARCHAR(32),
		PASSWORD VARCHAR(32)
	);
--添加一行数据
INSERT INTO USER VALUES(NULL,'konley','123456');

/**
 * @author konley
 * @date 2020-07-06 22:46
 * 使用JDBC工具类简化代码 并实现用户登录
 */
public class Demo07JDBCUtilsTest {
    public static void main(String[] args) throws SQLException {
        Scanner sc = new Scanner(System.in);

        System.out.println("请输入用户名:");
        String username = sc.next();
        System.out.println("请输入密码:");
        String password = sc.next();
        if (findOne(username, password)) {
            System.out.println("登录成功!");

        } else {
            System.out.println("登录失败!");
        }
    }

    public static boolean findOne(String username, String password) throws SQLException {
        Connection con = null;
        Statement stmt = null;
        ResultSet rs = null;
        try {
            con = Demo07JDBCUtils.getConnection();
            stmt = con.createStatement();
            String sql = "select * from user where username='" + username + "' and password='" + password + "'";
            rs = stmt.executeQuery(sql);
            return rs.next();
        } finally {
            Demo07JDBCUtils.close(con, stmt, rs);
        }
    }
}

为了防止SQL注入，使用PreparedStatement，改进后的代码

/**
 * @author konley
 * @date 2020-07-07 16:31
 * 使用preparedStatement对象
 */
public class Demo08JDBCPreparedStatement {
    private Scanner sc;

    public static void main(String[] args) throws SQLException {
        Scanner sc = new Scanner(System.in);
        System.out.println("请输入用户名:");
        String username = sc.next();
        System.out.println("请输入密码:");
        String password = sc.next();
        if (findOne(username, password)) {
            System.out.println("登录成功!");
        } else {
            System.out.println("登录失败!");
        }

    }

    public static boolean findOne(String username, String password) throws SQLException {
        Connection con = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            con = Demo07JDBCUtils.getConnection();
            String sql = "select * from user where username = ? and password = ?";
            ps = con.prepareStatement(sql);
            ps.setString(1, username);
            ps.setString(2, password);
            rs = ps.executeQuery();
            return rs.next();
        } finally {
            Demo07JDBCUtils.close(con, ps, rs);
        }
    }
}

三、JDBC事务管理

3.1 事务：

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

3.2 操作步骤：

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

3.3 使用Connection对象来管理事务

开启事务：setAutoCommit(boolean autoCommit)：调用该方法设置参数为false，即开启事务
- 在执行sql之前开启事务
提交事务：commit()
- 当所有sql都执行完提交事务
回滚事务：rollback()
- 在catch中回滚事务

3.4 代码示范

/**
 * @author konley
 * @date 2020-07-07 16:43
 * JDBC 事务操作 : 在转账中防止出现异常后一方扣钱而另一方没有收到钱
 */
public class Demo09Commit {
    public static void main(String[] args) {

        Connection conn = null;
        PreparedStatement pstmt1 = null;
        PreparedStatement pstmt2 = null;

        try {
            //1.获取连接
            conn = Demo07JDBCUtils.getConnection();
            //开启事务
            conn.setAutoCommit(false);

            //2.定义sql 转账
            //2.1 张三 - 500
            String sql1 = "update account set balance = balance - ? where id = ?";
            //2.2 李四 + 500
            String sql2 = "update account set balance = balance + ? where id = ?";
            //3.获取执行sql对象
            pstmt1 = conn.prepareStatement(sql1);
            pstmt2 = conn.prepareStatement(sql2);
            //4. 设置参数
            pstmt1.setDouble(1,500);
            pstmt1.setInt(2,1);

            pstmt2.setDouble(1,500);
            pstmt2.setInt(2,2);
            //5.执行sql
            pstmt1.executeUpdate();
            // 手动制造异常
            int i = 3/0;
            pstmt2.executeUpdate();
            //提交事务
            conn.commit();
        } catch (Exception e) {
            //事务回滚
            try {
                if(conn != null) {
                    conn.rollback();
                }
            } catch (SQLException e1) {
                e1.printStackTrace();
            }
            e.printStackTrace();
        }finally {
            Demo07JDBCUtils.close(conn,pstmt1);
            Demo07JDBCUtils.close(null,pstmt2);
        }


    }

}