MySQL数据库（JDBC）

1.JDBC基本介绍

1.概述

2.JDBC原理图

2.JDBC快速入门

1.JDBC API

2.JDBC程序编写步骤

3.环境配置

1.创建src/lib文件夹，放入jar包

2.加入到项目中

3.配置代码提示

4.代码实例

package jdbc_;


import com.mysql.cj.jdbc.Driver; //注意sql8是这个驱动

import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;

/**
 * @author 孙显圣
 * @version 1.0
 * 完成简单的jdbc操作
 */
public class Jdbc01 {
    public static void main(String[] args) throws SQLException {
        //1.注册mysql驱动类
        Driver driver = new Driver();

        //2.得到连接

        //（1）jdbc:mysql：//表示jdbc连接mysql的协议
        //（2）localhost：主机，这个是数据库的ip地址
        //（3）3306：mysql：监听的端口号
        //（4）hsp_db02：是数据库的名字
        //（5）mysql的本质就是socket连接
        String url= "jdbc:mysql://localhost:3306/hsp_db02";

        //将用户名和密码封装到properties对象
        //这里的user和password是固定的
        Properties properties = new Properties();
        properties.setProperty("user", "root");
        properties.setProperty("password", "root");

        //获取连接
        Connection connect = driver.connect(url, properties);

        //3.执行sql
        String sql = "insert into actor values(null, '刘德华', '男', '1970-1-1', '110')";
        //statement用于执行静态sql并返回其生成对的结果的对象
        Statement statement = connect.createStatement();
        //执行
        int i = statement.executeUpdate(sql);
        System.out.println(i > 0 ? "成功" : "失败");

        //4.关闭连接源
        statement.close();
        connect.close();
    }
}

3.数据库五种连接方式（推荐使用4、5）

1.代码实例

package jdbc_;

import com.mysql.cj.jdbc.Driver;
import org.junit.jupiter.api.Test;

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

/**
 * @author 孙显圣
 * @version 1.0
 */
public class JdbcConnect {
    public static void main(String[] args) throws SQLException {
        //方式一
        //1.注册mysql驱动类
        Driver driver = new Driver();

        //2.得到连接

        //（1）jdbc:mysql：//表示jdbc连接mysql的协议
        //（2）localhost：主机，这个是数据库的ip地址
        //（3）3306：mysql：监听的端口号
        //（4）hsp_db02：是数据库的名字
        //（5）mysql的本质就是socket连接
        String url= "jdbc:mysql://localhost:3306/hsp_db02";

        //将用户名和密码封装到properties对象
        //这里的user和password是固定的
        Properties properties = new Properties();
        properties.setProperty("user", "root");
        properties.setProperty("password", "root");

        //获取连接
        Connection connect = driver.connect(url, properties);
        System.out.println("方式一：" + connect);

        connect.close();
    }
    @Test
    public void method02() throws ClassNotFoundException, InstantiationException, IllegalAccessException, SQLException {
        //方式二
        Class<?> aClass = Class.forName("com.mysql.cj.jdbc.Driver");
        Driver driver = (Driver) aClass.newInstance();
        String url= "jdbc:mysql://localhost:3306/hsp_db02";

        //将用户名和密码封装到properties对象
        //这里的user和password是固定的
        Properties properties = new Properties();
        properties.setProperty("user", "root");
        properties.setProperty("password", "root");

        //获取连接
        Connection connect = driver.connect(url, properties);
        System.out.println("方式二：" + connect);

        connect.close();
    }
    @Test
    public void method03() throws ClassNotFoundException, InstantiationException, IllegalAccessException, SQLException {
        //方式三，使用DriverManager来替代driver进行统一管理
        Class<?> aClass = Class.forName("com.mysql.cj.jdbc.Driver");
        Driver driver = (Driver) aClass.newInstance();

        //创建url、user和password
        String url= "jdbc:mysql://localhost:3306/hsp_db02";
        String user= "root";
        String password= "root";

        DriverManager.registerDriver(driver); //注册driver驱动

        Connection connection = DriverManager.getConnection(url, user, password); //获取连接
        System.out.println("方式三：" + connection);

        connection.close();
    }

    @Test
    public void method04() throws ClassNotFoundException, SQLException {
        //方式四，在加载驱动时使用静态代码块自动注册驱动
        Class<?> aClass = Class.forName("com.mysql.cj.jdbc.Driver");

        //创建url、user和password
        String url= "jdbc:mysql://localhost:3306/hsp_db02";
        String user= "root";
        String password= "root";

        //使用DriverManager获取连接
        Connection connection = DriverManager.getConnection(url, user, password); //获取连接
        System.out.println("方式四：" + connection);

        connection.close();
    }
    @Test
    public void method05() throws IOException, ClassNotFoundException, SQLException {
        //获取properties文件的信息
        Properties properties = new Properties();
        properties.load(new FileInputStream("src\\mysql.properties")); //加载到properties文件中
        String driver = properties.getProperty("driver");
        String url = properties.getProperty("url");
        String user = properties.getProperty("user");
        String password = properties.getProperty("password");

        //加载驱动类
        Class<?> aClass = Class.forName(driver);
        //使用DriverManager获取连接
        Connection connection = DriverManager.getConnection(url, user, password);
        System.out.println("方式五：" + connection);

        connection.close();
    }


}

2.细节说明

1.第四种方式是自动加载驱动原因

1. Driver类加载的时候有个静态代码块
2. 这个静态代码块会自动创建一个驱动实例并且注册

2.可不可以不写类加载语句？

1. 答案是可以的
2. 原因是在mysql的驱动4以后是自动加载的
3. mysql4以后自带配置文件会自动加载驱动并注册

3.方式五的连接方式（建议使用）

1.src下（或者任意位置）创建配置文件（自定义名字）

src\mysql.properties

user=root
password=root
url=jdbc:mysql://localhost:3306/hsp_db02
driver=com.mysql.cj.jdbc.Driver

注意这个文件不能加空格

2.获取连接

        //获取properties文件的信息
        Properties properties = new Properties();
        properties.load(new FileInputStream("src\\mysql.properties")); //加载到properties文件中
        String driver = properties.getProperty("driver");
        String url = properties.getProperty("url");
        String user = properties.getProperty("user");
        String password = properties.getProperty("password");

        //加载驱动类
        Class<?> aClass = Class.forName(driver);
        //使用DriverManager获取连接
        Connection connection = DriverManager.getConnection(url, user, password);

4.课堂练习

题目

答案

package jdbc_;

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

/**
 * @author 孙显圣
 * @version 1.0
 */
public class ConnectExercise {
    public static void main(String[] args) throws IOException, ClassNotFoundException, SQLException {
        //加载配置文件
        Properties properties = new Properties();
        properties.load(new FileInputStream("src\\mysql.properties"));
        //获取内容
        String driver = properties.getProperty("driver");
        String user = properties.getProperty("user");
        String password = properties.getProperty("password");
        String url = properties.getProperty("url");

        //加载驱动类
        Class<?> aClass = Class.forName(driver);
        //获取连接
        Connection connection = DriverManager.getConnection(url, user, password);

        //编写sql
        //1.插入五条语句
//        String sql = "insert into actor values(null, '孙显圣', '男', '2002-12-8', '123456'), " +
//                "(null, '孙显圣2', '男', '2005-2-8', '123456'), " +
//                "(null, '孙显圣3', '男', '2001-12-8', '123456'), " +
//                "(null, '孙显圣4', '男', '2004-12-8', '123456'), " +
//                "(null, '孙显圣5', '男', '2006-12-8', '123456')";
        //2.修改id=1的数据，将名字改成自己的
//        String sql = "update actor set name = '孙显圣' where id = 1";
        //3.删除id = 1的记录
        String sql = "delete from actor where id = 1";

        //创建statement
        Statement statement = connection.createStatement();
        //执行sql
        int i = statement.executeUpdate(sql);
        System.out.println(i > 0 ? "成功" : "失败");

        //关闭连接
        statement.close();
        connection.close();
    }
}

4.ResultSet查询

1.基本介绍

2.ResultSet查询表中所有数据

package jdbc_;

import java.io.FileInputStream;
import java.io.IOException;
import java.sql.*;
import java.util.Properties;

/**
 * @author 孙显圣
 * @version 1.0
 */
public class ResultSet_ {
    public static void main(String[] args) throws IOException, ClassNotFoundException, SQLException {
        //加载配置文件
        Properties properties = new Properties();
        properties.load(new FileInputStream("src\\mysql.properties"));
        //获取内容
        String driver = properties.getProperty("driver");
        String user = properties.getProperty("user");
        String password = properties.getProperty("password");
        String url = properties.getProperty("url");

        //加载驱动类
        Class<?> aClass = Class.forName(driver);
        //获取连接
        Connection connection = DriverManager.getConnection(url, user, password);

        //编写sql
        //查询所有记录
        String sql = "select * from actor ";

        //创建statement
        Statement statement = connection.createStatement();

        //执行查询
        ResultSet resultSet = statement.executeQuery(sql); //结果保存在resultSet中

        while (resultSet.next()) { //初始的resultSet指向的是ArrayList的表头，如果下一条记录不为空，则将光标移动到下一条记录
            //取出所有列的元素
            int anInt = resultSet.getInt(1);
            String string = resultSet.getString(2);
            String string1 = resultSet.getString(3);
            Date date = resultSet.getDate(4);
            String string2 = resultSet.getString(5);
            System.out.println(anInt + " " + string + " " + string1 + " " + date + " " + string2);
        }
        
        //关闭连接
        resultSet.close();
        statement.close();
        connection.close();
    }
}

3.SQL注入

5.预处理

1.基本介绍

2.预处理查询

package jdbc_;

import java.io.FileInputStream;
import java.io.IOException;
import java.sql.*;
import java.util.Properties;

/**
 * @author 孙显圣
 * @version 1.0
 */
public class PreparedStatement_ {
    public static void main(String[] args) throws IOException, ClassNotFoundException, SQLException {
        //加载配置文件
        Properties properties = new Properties();
        properties.load(new FileInputStream("src\\mysql.properties"));
        //获取内容
        String driver = properties.getProperty("driver");
        String user = properties.getProperty("user");
        String password = properties.getProperty("password");
        String url = properties.getProperty("url");

        //加载驱动类
        Class<?> aClass = Class.forName(driver);
        //获取连接
        Connection connection = DriverManager.getConnection(url, user, password);

        //编写sql
        //查询记录
        String sql = "select id, name from actor where id = ? and name = ?";

        //创建Prestatement
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        //进行预处理
        preparedStatement.setInt(1, 4);
        preparedStatement.setString(2, "孙显圣");

        //执行查询
        ResultSet resultSet = preparedStatement.executeQuery(); //结果保存在resultSet中，这里是不带sql的

        while (resultSet.next()) { //初始的resultSet指向的是ArrayList的表头，如果下一条记录不为空，则将光标移动到下一条记录
            //取出所有列的元素
            int anInt = resultSet.getInt(1);
            String string = resultSet.getString(2);
            System.out.println(anInt + " " + string);
        }

        //关闭连接
        resultSet.close();
        preparedStatement.close();
        connection.close();
    }
}

3.预处理DML

package jdbc_;

import java.io.FileInputStream;
import java.io.IOException;
import java.sql.*;
import java.util.Properties;

/**
 * @author 孙显圣
 * @version 1.0
 */
public class PreparedStatement_01 {
    public static void main(String[] args) throws IOException, ClassNotFoundException, SQLException {
        //加载配置文件
        Properties properties = new Properties();
        properties.load(new FileInputStream("src\\mysql.properties"));
        //获取内容
        String driver = properties.getProperty("driver");
        String user = properties.getProperty("user");
        String password = properties.getProperty("password");
        String url = properties.getProperty("url");

        //加载驱动类
        Class<?> aClass = Class.forName(driver);
        //获取连接
        Connection connection = DriverManager.getConnection(url, user, password);

        //编写sql
        //修改
        String sql = "update actor set name = ? where id = ?";

        //创建Prestatement
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        //进行预处理
        preparedStatement.setString(1, "孙显圣");
        preparedStatement.setInt(2, 2);

        //执行更新
        int i = preparedStatement.executeUpdate();//结果保存在resultSet中，这里是不带sql的
        System.out.println(i > 0 ? "成功" : "失败");

        //关闭连接
        preparedStatement.close();
        connection.close();
    }
}

4.课堂练习

package jdbc_;

import java.io.FileInputStream;
import java.io.IOException;
import java.sql.*;
import java.util.Properties;

/**
 * @author 孙显圣
 * @version 1.0
 * 预处理课堂练习
 */
public class PreStatementExercise {
    public static void main(String[] args) throws IOException, ClassNotFoundException, SQLException {
        //读取配置文件
        Properties properties = new Properties();
        properties.load(new FileInputStream("src\\mysql.properties"));

        //获取信息
        String driver = properties.getProperty("driver");
        String user = properties.getProperty("user");
        String password = properties.getProperty("password");
        String url = properties.getProperty("url");

        //类加载
        Class.forName(driver);
        //获取连接
        Connection connection = DriverManager.getConnection(url, user, password);

//        //编写sql语句
//        String sql = "insert into actor values(null, ?, ?, ?, ?)";
//        //对sql进行预处理
//        PreparedStatement preparedStatement = connection.prepareStatement(sql);
//        preparedStatement.setString(1, "李华");
//        preparedStatement.setString(2, "男");
//        preparedStatement.setDate(3, Date.valueOf("2021-3-9")); //这里注意把日期变成string类型
//        preparedStatement.setString(4, "123456");

//        //修改tom的记录，将name改成king
//        String sql = "update actor set name = ? where name = ?";
//        //对sql进行预处理
//        PreparedStatement preparedStatement = connection.prepareStatement(sql);
//        preparedStatement.setString(1, "king");
//        preparedStatement.setString(2, "tom");

//        //删除一条记录
//        String sql = "delete from actor where id = ?";
//        //对sql进行预处理
//        PreparedStatement preparedStatement = connection.prepareStatement(sql);
//        preparedStatement.setInt(1, 3);

        //查询全部记录并显示在控制台上
        String sql = "select * from actor";
        //预处理
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        //执行sql
        ResultSet resultSet = preparedStatement.executeQuery();
        while (resultSet.next()) {
            //取出全部数据，并显示
            int anInt = resultSet.getInt(1);
            String string = resultSet.getString(2);
            String string1 = resultSet.getString(3);
            Date date = resultSet.getDate(4);
            String string2 = resultSet.getString(5);
            System.out.println(anInt + " " + string + " " +  string1 + " " +  date + " " +  string2);
        }

//        //执行sql
//        int i = preparedStatement.executeUpdate();
//        System.out.println(i > 0 ? "成功" : "失败");

        //关闭连接
        resultSet.close();
        preparedStatement.close();
        connection.close();
    }
}

6.JDBC_API

7.JDBCUtils

1.JDBCUtils工具类代码

package utils;

import java.io.FileInputStream;
import java.io.IOException;
import java.sql.*;
import java.util.Properties;

/**
 * @author 孙显圣
 * @version 1.0
 * 这是一个工具类，完成mysql的连接和关闭资源
 */
public class JDBCUtils {
    //四个静态属性，因为每次程序运行的时候只需要一份
    private static String user;
    private static String password;
    private static String url;
    private static String driver;

    static { //静态代码块为四个静态属性初始化
        //读取配置文件
        Properties properties = new Properties();
        try {
            properties.load(new FileInputStream("src\\mysql.properties")); //读取的配置文件的位置
        } catch (IOException e) {
            throw new RuntimeException(e);
        }
        //将信息给静态属性
        user = properties.getProperty("user");
        password = properties.getProperty("password");
        url = properties.getProperty("url");
        driver = properties.getProperty("driver");
    }

    //连接数据库
    public static Connection getConnection() {
        try {
            return DriverManager.getConnection(url, user, password);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    //关闭数据库
    //1.ResultSet
    //2.Statement,PrepareStatement,由于后一个是前一个的子接口，所以只传前面的就可以
    //3.Connection
    public static void close(ResultSet resultSet, Statement statement, Connection connection) {
        if (resultSet != null) {
            try {
                resultSet.close();
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }
        if (statement != null) {
            try {
                statement.close();
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }
        if (connection != null) {
            try {
                connection.close();
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }

    }


}

2.JDBCUtils_DML演示

package jdbc_;

import org.junit.jupiter.api.Test;
import utils.JDBCUtils;

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

/**
 * @author 孙显圣
 * @version 1.0
 */
public class JDBCUtils_Use {

    @Test
    public void testDML() {
        //建立连接
        Connection connection = JDBCUtils.getConnection();
        
        //编写sql语句进行修改
        String sql = "update actor set name = ? where id = ?";
        
        PreparedStatement preparedStatement = null;
        try {
            //进行预处理
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1, "刘德华");
            preparedStatement.setInt(2, 6);
            //执行sql
            int i = preparedStatement.executeUpdate();
            System.out.println(i > 0 ? "成功" : "失败");
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            //关闭资源
            JDBCUtils.close(null, preparedStatement, connection);
        }

    }
    
}

3.JDBCUtils_select演示

package jdbc_;

import org.junit.jupiter.api.Test;
import utils.JDBCUtils;

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

/**
 * @author 孙显圣
 * @version 1.0
 */
public class JDBCUtils_Use {
    @Test
    public void testSelect() {
        //建立连接
        Connection connection = JDBCUtils.getConnection();
        //编写sql语句进行查询
        String sql = "select name, phone from actor where id = ?";
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            //进行预处理
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1, 8);
            //执行查询
            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()) {
                String string = resultSet.getString("name");
                String string1 = resultSet.getString("phone");
                System.out.println(string + " " + string1);
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            //关闭资源
            JDBCUtils.close(resultSet, preparedStatement, connection);
        }

    }

}

8.事务

1.基本介绍

2.案例——使用事务处理银行转账

1.创建表account

CREATE TABLE account( 
 id INT PRIMARY key auto_increment,
 name VARCHAR(32) NOT NULL DEFAULT'',
 blance DOUBLE NOT NULL DEFAULT 0
)

INSERT INTO account VALUES(null, '马云', 3000);
INSERT INTO account VALUES(null, '马化腾', 10000);

SELECT * FROM account

2.编写代码

package jdbc_;

import utils.JDBCUtils;

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

/**
 * @author 孙显圣
 * @version 1.0
 */
public class Transaciton {
    public static void main(String[] args) {

        Connection connection = null;
        PreparedStatement preparedStatement = null;
        PreparedStatement preparedStatement1 = null;

        try {
            //连接数据库
            connection = JDBCUtils.getConnection();
            //1.设置不自动提交事务
            connection.setAutoCommit(false);

            //编写sql进行模拟转账
            String sql1 = "update account set blance = blance - 100 where name = ?";
            //编写sql进行模拟接受
            String sql2 = "update account set blance = blance + 100 where name = ?";
            //预处理
            preparedStatement = connection.prepareStatement(sql1);
            preparedStatement.setString(1, "马云");
            preparedStatement1 = connection.prepareStatement(sql2);
            preparedStatement1.setString(1, "马化腾");

            //执行1
            preparedStatement.executeUpdate();

//            //假设有个错误，则会回滚
//            System.out.println(10/0);

            //执行2
            preparedStatement1.executeUpdate();

            //3.如果都顺利执行完了也没有错误则提交事务
            connection.commit();
        } catch (SQLException e) {
            try {
                //2.如果中间出错了则回滚
                connection.rollback();
            } catch (SQLException ex) {
                throw new RuntimeException(ex);
            }
            throw new RuntimeException(e);
        } finally {
            //关闭连接
            JDBCUtils.close(null, preparedStatement, connection);
            JDBCUtils.close(null, preparedStatement1, null);

        }
    }
}