JDBC 从入门到放弃

穿金头戴帽

已于 2022-09-22 11:26:35 修改

阅读量387

点赞数

分类专栏：数据库 SSM 文章标签： java mybatis sql

于 2022-09-21 17:45:09 首次发布

本文链接：https://blog.csdn.net/qq_41149775/article/details/126976658

版权

数据库同时被 2 个专栏收录

17 篇文章 0 订阅

订阅专栏

SSM

11 篇文章 0 订阅

订阅专栏

简述

Java数据库连接，（Java Database Connectivity，简称JDBC）是Java语言中用来规范客户端程序如何来访问数据库的应用程序接口，提供了诸如查询和更新数据库中数据的方法。JDBC也是Sun Microsystems的商标。我们通常说的JDBC是面向关系型数据库的。

官方文档说明

详细查看官方文档，培养看懂官方文档的能力。

JDBC官方文档https://docs.oracle.com/javase/tutorial/jdbc/basics/transactions.html

过程

JDBC的顺序可以分为注册驱动、获取链接、创建传输器、执行Sql语句、处理结果、关闭资源。

基本操作

<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
</dependency>

一个简单的例子

import lombok.SneakyThrows;
import java.sql.*;

public class TestController {

    @SneakyThrows
    public static void main(String[] args) {
        // 注册驱动
        Class.forName("com.mysql.cj.jdbc.Driver");
        String url = "jdbc:mysql://192.168.64.128:3306/bj?characterEncoding=utf8";
        String username = "root";
        String pwd = "root";

        // 获取链接
        Connection connection = DriverManager.getConnection(url, username, pwd);

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

        // 执行语句
        ResultSet resultSet = statement.executeQuery("select * from user");

        // 处理结果
        while (resultSet.next()) {
            String id = resultSet.getString("id");
            System.out.println(id);
        }

        // 关闭资源
        resultSet.close();
        statement.close();
        connection.close();
    }
}

防止Sql注入

上面的传输器使用的是Statement不能防止Sql注入问题，推荐使用PrepareStatement。之所以PrepareStatement可以防止Sql注入问题其原因是采用了预处理的方式，先将Sql语句的骨架发送给数据库后再将用户传入的参数进行转义后填充进之前的Sql语句中。

import lombok.SneakyThrows;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

public class TestController {

    @SneakyThrows
    public static void main(String[] args) {
        // 注册驱动
        Class.forName("com.mysql.cj.jdbc.Driver");
        String url = "jdbc:mysql://192.168.64.128:3306/bj?characterEncoding=utf8";
        String username = "root";
        String pwd = "root";

        // 获取链接
        Connection connection = DriverManager.getConnection(url, username, pwd);
        
        // 预处理传输器
        PreparedStatement ps = connection.prepareStatement("select * from user where name=? and password=?");

        // 将值根据位置填充
        ps.setString(1, "北京用户");
        ps.setString(2, "123456");

        ResultSet resultSet = ps.executeQuery();
        
        // 处理结果
        while (resultSet.next()) {
            String id = resultSet.getString("id");
            System.out.println(id);
        }

        // 关闭资源
        ps.close();
        connection.close();
    }
}

通过DataSource对象获取链接

import com.mysql.cj.jdbc.MysqlDataSource;
import java.sql.*;

public class DataSourceTest extends MysqlDataSource {

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

        MysqlDataSource mysqlDataSource = new MysqlDataSource();

        mysqlDataSource.setServerName("192.168.64.128");
        mysqlDataSource.setDatabaseName("bj");
        mysqlDataSource.setDescription("测试数据库");

        Connection connection = mysqlDataSource.getConnection("root", "root");

        String sql  = "select * from user where id = ?";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setString(1,"1");

        ResultSet resultSet = preparedStatement.executeQuery();

        while (resultSet.next()) {
            ResultSetMetaData metaData = resultSet.getMetaData();
            int columnCount = metaData.getColumnCount();

            for (int i = 1; i <= columnCount; i++) {
                String columnName = metaData.getColumnName(i);
                System.out.print(columnName + "   ");
            }
            System.out.println();

            for (int i = 1; i <= columnCount; i++) {
                String string = resultSet.getString(i);

                System.out.print(string +"   ");
            }
        }

        resultSet.close();
        preparedStatement.close();
        connection.close();
        
    }
}

连接池应用

1.怎么验证使用了连接池？

import com.mysql.cj.jdbc.*;
import javax.sql.PooledConnection;
import java.sql.*;

public class DataSourcePoolTest {
    public static void main(String[] args) throws SQLException {
        MysqlConnectionPoolDataSource poolDataSource = new MysqlConnectionPoolDataSource();
        poolDataSource.setServerName("192.168.64.128");
        poolDataSource.setDatabaseName("bj");
        poolDataSource.setPassword("root");
        poolDataSource.setUser("root");

        PooledConnection pooledConnection = poolDataSource.getPooledConnection();
        Connection connection = pooledConnection.getConnection();
        
    }
}

事务管理

本地事务管理

JDBC默认每个Sql语句都是一个完整的事务，如果需要将多个SQL语句交由一个事务进行处理，首先需要关闭自动提交事务后合理运用Commit和Rollback即可。conObject.setSavepoint()方法可以设置一个保存点，rollback(Savepoint) 回滚时可以指定回滚到保存点。conObject.releaseSavepoint方法可以释放保存点。

import lombok.SneakyThrows;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;

public class TestController {

    @SneakyThrows
    public static void main(String[] args) {
        // 注册驱动
        Class.forName("com.mysql.cj.jdbc.Driver");
        String url = "jdbc:mysql://192.168.64.128:3306/bj?characterEncoding=utf8";
        String username = "root";
        String pwd = "root";

        // 获取链接
        Connection connection = DriverManager.getConnection(url, username, pwd);
        //关闭 自动提交
        connection.setAutoCommit(false);

        String updateSql = "insert into user values(?,?,?)";

        // 预处理传输器
        PreparedStatement ps = connection.prepareStatement(updateSql);
        // 将值根据位置填充
        ps.setString(1, "1");
        ps.setString(2, "北京用户");
        ps.setString(3, "123456");

        try {
            ps.execute();
            connection.commit();
        }catch (Exception e){
            System.out.printf("出现异常 %s 执行回滚",e.getMessage());
            connection.rollback();
        }


        // 关闭资源
        ps.close();
        connection.close();
    }
}

分布式事务管理

~~~

异常处理

在应用程序出现异常后，盲目的打印出所有的信息不利于异常关键信息的捕获，查看官方文档可以对JDBC相关的异常进行合理的拆分已便出现异常后快速的定位问题。关于JDBC的异常可以分为两类，异常：会中断程序的执行、警告：不会中断程序的执行。还有两种子类异常没有记录可以查看官方文档。

异常

import com.mysql.cj.jdbc.MysqlConnectionPoolDataSource;
import javax.sql.PooledConnection;
import java.sql.Connection;
import java.sql.SQLException;

public class DataSourcePoolTest {
    public static void main(String[] args) {
        MysqlConnectionPoolDataSource poolDataSource = new MysqlConnectionPoolDataSource();
        poolDataSource.setServerName("192.168.64.128");
        poolDataSource.setDatabaseName("bj");
        poolDataSource.setPassword("root");
        poolDataSource.setUser("root111");

        try {
            PooledConnection pooledConnection = poolDataSource.getPooledConnection();
            Connection connection = pooledConnection.getConnection();
        } catch (SQLException e) {
            printException(e);
        }


    }

    public static void printException(SQLException ex) {
        for (Throwable e : ex) {
            if (e instanceof SQLException) {
                if (!ignoreSQLException(((SQLException) e).getSQLState())) {
                    e.printStackTrace(System.err);
                    System.err.println("SQLState:" + ((SQLException) e).getSQLState());
                    System.err.println("Error Code:" + ((SQLException) e).getErrorCode());
                    System.err.println("Error Message:" + e.getMessage());

                    Throwable cause = ex.getCause();
                    while (cause != null) {
                        System.out.println("Cause:" + cause);
                        cause = ex.getCause();
                    }
                }
            }
        }
    }

    public static boolean ignoreSQLException(String sqlState) {

        if (sqlState == null) {
            System.out.println("The SQL state is not defined!");
            return false;
        }

        // X0Y32: Jar file already exists in schema
        if (sqlState.equalsIgnoreCase("X0Y32"))
            return true;

        // 42Y55: Table already exists in schema
        if (sqlState.equalsIgnoreCase("42Y55"))
            return true;

        return false;
    }
}

警告

警告一般是执行了一些不按规定的SQL，例如可以这么做，但不建议这个做，此时就会产生警告内容，警告由Object.getWarnings方法获取。

import com.mysql.cj.jdbc.MysqlConnectionPoolDataSource;
import javax.sql.PooledConnection;
import java.sql.*;

public class DataSourcePoolTest {
    public static void main(String[] args) throws SQLException {
        MysqlConnectionPoolDataSource poolDataSource = new MysqlConnectionPoolDataSource();
        poolDataSource.setServerName("192.168.64.128");
        poolDataSource.setDatabaseName("bj");
        poolDataSource.setPassword("root");
        poolDataSource.setUser("root111");


        PooledConnection pooledConnection = poolDataSource.getPooledConnection();
        Connection connection = pooledConnection.getConnection();

        Statement statement = connection.createStatement();
        getWarningsFromStatement(statement);
        String sql = "select * from user where 1 = 1";
        ResultSet resultSet = statement.executeQuery(sql);
        getWarningsFromResultSet(resultSet);

    }

    public static void getWarningsFromResultSet(ResultSet rs)
            throws SQLException {
        printWarnings(rs.getWarnings());
    }

    public static void getWarningsFromStatement(Statement stmt)
            throws SQLException {
        printWarnings(stmt.getWarnings());
    }

    public static void printWarnings(SQLWarning warning)
            throws SQLException {

        if (warning != null) {
            System.out.println("\n---Warning---\n");

            while (warning != null) {
                System.out.println("Message: " + warning.getMessage());
                System.out.println("SQLState: " + warning.getSQLState());
                System.out.print("Vendor error code: ");
                System.out.println(warning.getErrorCode());
                System.out.println("");
                warning = warning.getNextWarning();
            }
        }
    }
}