Java的JDBC编程

1.Java的数据库编程：JDBC

1.1 JDBC介绍

JDBC，即Java Database Connectivity，专门处理java数据库连接。

1.2 JDBC工作原理

JDBC 为多种关系数据库提供了统一访问方式，作为特定厂商数据库访问API的一种高级抽象，它主要包含一些通用的接口类。
举例对比：

1.3 各个对象的生命周期

假如一个应用中要执行多条SQL

1.3.1 DataSource

整个应用只需要有一个对象即可；

1.3.2 Connection

每次打一个电话，用一个对象；注意：Connection对象不是线程安全的，保证多个线程之间共享一个对象；

1.3.3 PrepareStatement对象和 ResultSet对象

在一次Connection里，可以多次说，保证每次都正确的close就可以；

2.JDBC使用

2.1 JDBC开发案例

准备数据库驱动包，并添加到项目的依赖中：

在项目中创建文件夹lib，并将依赖包mysql-connector-java-5.1.47.jar复制到lib中。再配置该jar 包到本项目的依赖中：右键点击项目Open Module Settings，在Modules中，点击项目，配置 Dependencies，点击+，JARS or Directories，将该lib文件夹配置进依赖中，表示该文件夹下的 jar包都引入作为依赖。

2.1.1 执行查询操作

package com.cc;

import com.mysql.jdbc.jdbc2.optional.MysqlDataSource;

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

/**
 * Created with IntelliJ IDEA.
 * Description:
 * User:DELL
 * Date:2021-11-24
 * Time:20:15
 */
public class JDBCDemo1 {
    public static void main(String[] args) throws SQLException {
        //链接MySQL的配置
        MysqlDataSource mysqlDataSource = new MysqlDataSource();
        mysqlDataSource.setServerName("127.0.0.1");
        mysqlDataSource.setPort(3306);//端口号
        mysqlDataSource.setUser("root");
        mysqlDataSource.setPassword("123456");
        mysqlDataSource.setDatabaseName("db_11_24");
        mysqlDataSource.setCharacterEncoding("utf8");
        mysqlDataSource.setServerTimezone("Asia/Shanghai"); //东八时区
        mysqlDataSource.setUseSSL(false);
        //第二种  Spring 比较常用
//        mysqlDataSource.setURL("jdbc:mysql://127.0.0.1:3306/db_11_24?characterEncoding=utf8&useSSL=false&serverTimezonr=Asia/Shanghai");
//        mysqlDataSource.setUser("root");
//        mysqlDataSource.setPassword("123456");

        //从dateSource 中获取连接（connection）
        Connection connection1 = null;// 创建时可能为空
        try {
            connection1 = mysqlDataSource.getConnection();

            //准备好要执行的SQL
            // 在 Java中，以字符串的形式体现sql
            String sql = "select * from exam_result order by id";
            
            // 要执行 SQL ，需要从 Connection 对象中得到 XXXStatement  statement--》语句，相当于对SQL语句的封装
            PreparedStatement preparedStatement = null;//preparedStatement--》准备好一条sql
            try {
                preparedStatement = connection1.prepareStatement(sql);

                //通过XX Statement 对象执行查询过程--->就是excute操作
                // excute -->两个变型
                // excuteQuery:执行查询类的SQL  有查询结果
                // excuteUpdate:用来执行 增、删、改类似的操作，没有查询结果
                ResultSet resultSet = null;// resulr->结果，set->集合，resultSet 查询后的结果集
                try {
                    resultSet = preparedStatement.executeQuery(); //上文中使用select  所以使用excuteQuery

                    // 遍历我们的结果集,得到所有行数据   遍历过程是以行记录作为单位进行的操作（一次操作是一行）
                    // resultSet.next()作用  1.返回值代表是否还有新行  2.让游标(curaor)走到下一行
                   // boolean hasNextLine = resultSet.next(); // true 还有下一行  false 遍历结束
                   while (resultSet.next()) {
                       //拿数据 1、以字段名称去拿
//                       int id = resultSet.getInt("id");
//                       System.out.println(id);

                       //2、通过字段顺序拿（顺序：SELECT 后边的顺序，*就是建表顺序）  从 1开始
//                       int id2 = resultSet.getInt(1);
//                       System.out.println(id2);

                       int id = resultSet.getInt("id");
                       String name = resultSet.getString("name");
                       double chinese = resultSet.getDouble("chinese");
                       double math = resultSet.getDouble("math");
                       double english = resultSet.getDouble("english");
                       System.out.printf("%d %s %.1f %.1f %.1f\n", id, name, chinese, math ,english);
                   }
                } finally {
                    if (resultSet != null) {
                        resultSet.close();
                    }
                }
            } finally {
                if (preparedStatement != null) {
                    preparedStatement.close();
                }
            }
        } finally {
            // 执行结束时。需要关闭 Connection
            if (connection1 != null) {
                connection1.close();
            }
        }
    }
}

2.1.2 执行非查询类操作

package com.cc;

import com.mysql.jdbc.jdbc2.optional.MysqlDataSource;

import java.sql.*;

/**
 * Created with IntelliJ IDEA.
 * Description:
 * User:DELL
 * Date:2021-11-24
 * Time:10:33
 */
public class JDBCNotSelectDemo {
    //插入操作时，我们需要拿到插入后的自增id是什么
    public static void main(String[] args) throws SQLException {
        MysqlDataSource mysqlDataSource = new MysqlDataSource();
        mysqlDataSource.setURL("jdbc:mysql://127.0.0.1:3306/db_11_24?characterEncoding=utf8&useSSL=false&serverTimezonr=Asia/Shanghai");
        mysqlDataSource.setUser("root");
        mysqlDataSource.setPassword("123456");
        
        try (Connection connection = mysqlDataSource.getConnection()) {
            String sql = "insert into exam_result (name, chinese, math, english) values ('二郎神', 38, 99, 78.5)";
            // 要得到自增id    RETURN_GENERATED_KEYS -->返回_生成的_主键s（批量插入）
            try (PreparedStatement preparedStatement = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS)) {
                int affectRow = preparedStatement.executeUpdate();
                System.out.println("影响行数：" + affectRow);
                try (ResultSet resultSet = preparedStatement.getGeneratedKeys()) {
                    // 因为知道只有一行 id 返回
                    resultSet.next();  // 让游标走到第一行上
                    int pk = resultSet.getInt(1);  // 字段名  无法用列名称，只能用下标，取第一个值
                    System.out.println(pk);
                }
            }
        }
    }


    public static void main1(String[] args) throws SQLException {
        MysqlDataSource mysqlDataSource = new MysqlDataSource();
        mysqlDataSource.setURL("jdbc:mysql://127.0.0.1:3306/db_11_24?characterEncoding=utf8&useSSL=false&serverTimezonr=Asia/Shanghai");
        mysqlDataSource.setUser("root");
        mysqlDataSource.setPassword("123456");
        try (Connection connection = mysqlDataSource.getConnection()) {
            //真正SQL，如果只有一条语句，没有分号是可以的
            String sql = "insert into exam_result (name, chinese, math, english) values ('哪吒', 38, 99, 78.5)";
            try (PreparedStatement preparedStatement = connection.prepareStatement(sql)) {
                //不是查询语句--》没有查询结果
                int affectRow = preparedStatement.executeUpdate();
                System.out.println("影响行数：" + affectRow);
            }
        }
    }
}

2.1.3 举例：类似于SQL的命令行界面

读取用户的一行输入（一条SQL）；
执行这条SQL，
- 如果失败，给出异常原因，不结束；
- 如果查询，给出结果；
- 如果不是查询，给出影响行数；

package com.cc;

import com.mysql.jdbc.jdbc2.optional.MysqlDataSource;

import java.sql.*;
import java.util.Scanner;

/**
 * Created with IntelliJ IDEA.
 * Description:
 * User:DELL
 * Date:2021-11-24
 * Time:22:13
 */
public class MySQLClientCLI {
    //模拟SQL命令行界面
    private static Connection connection = null;
    public static void main(String[] args) throws SQLException {
        //初始化Connection
        initConnection();
        Scanner s = new Scanner(System.in);
        System.out.print("> ");
        while (s.hasNextLine()) {
            String sql = s.nextLine().trim();//trim()把空格都删掉
            if (sql.isEmpty()) {
                System.out.print("> ");
                continue;
            }
            //执行sql
            excuteSql(sql);
            System.out.print("> ");
        }
    }

    private static void initConnection() throws SQLException {
        MysqlDataSource ds = new MysqlDataSource();

        ds.setServerName("localhost");
        ds.setPort(3306);
        ds.setUser("root");
        ds.setPassword("123456");
        ds.setDatabaseName("db_11_24");
        ds.setCharacterEncoding("utf8");
        ds.setServerTimezone("Asia/Shanghai");
        ds.setUseSSL(false);

        connection = ds.getConnection();
    }

    private static void excuteSql(String sql) {
        //需要一个Connection对象，希望全局都可以使用，把变量定义成静态变量
        try (PreparedStatement s = connection.prepareStatement(sql)) {
            //判断是否是查询sql
            if (isQuerySql(sql)) {
                try (ResultSet rs = s.executeQuery()) {
                    //meta:元   关于数据的数据  关于结果集本身的一些数据 ---》相当于表中字段名
                    ResultSetMetaData metaData = rs.getMetaData();
                    int columnCount = metaData.getColumnCount();//一共有多少列
//                    String catalogName = metaData.getCatalogName(1);//拿到第一列的字段名称，从1开始
                    //拿到列的姓名 准备打印一行字段名称
                    StringBuilder sb = new StringBuilder();
                    for (int i = 1; i <= columnCount; i++) {
                        String name = metaData.getColumnName(i);
                        sb.append(name);
                        sb.append(", ");
                    }
                    //把最后一个字段的,和空格去掉
                    sb.delete(sb.length() - 2, sb.length());
                    System.out.println(sb.toString());

                    while (rs.next()) {
                        //由于不知道用户输入的sql是什么--->即不知道有几个字段，也不知道各个字段的名称
                        StringBuilder sbValue = new StringBuilder();
                        for (int i = 1; i <= columnCount; i++) {
                            //不知道数据类型，但是只是想显示，所以统一使用String类型
                            String column = rs.getString(i);
                            sbValue.append(column);
                            sbValue.append(", ");
                        }
                        sbValue.delete(sbValue.length() - 2, sbValue.length());
                        System.out.println(sbValue.toString());
                    }
                }
            } else {
                    int an = s.executeUpdate();
                    System.out.println("影响行数：" + an);
            }
        } catch (SQLException exc) {
            String reason = exc.getMessage();
            System.out.println(reason);
        }
    }

    private static boolean isQuerySql(String sql) {
        String[] s = sql.split(" ");
        String firstWord = s[0].toLowerCase();//转换成小写好判断
        //暂时认为 show  和 select 就是查询sql，其他的暂时都认为是非查询sql
        return firstWord.equals("show") || firstWord.equals("select");
    }
}