package tools;

import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Iterator;

/**
 * 結(jié)果集打印機(jī).將結(jié)果集中的數(shù)據(jù)打印成表格.
 */
public class ResultSetPrinter {
    public static void printResultSet(ResultSet rs) throws SQLException {
        ResultSetMetaData resultSetMetaData = rs.getMetaData();
        // 獲取列數(shù)
        int ColumnCount = resultSetMetaData.getColumnCount();
        // 保存當(dāng)前列最大長度的數(shù)組
        int[] columnMaxLengths = new int[ColumnCount];
        // 緩存結(jié)果集,結(jié)果集可能有序,所以用ArrayList保存變得打亂順序.
        ArrayList<String[]> results = new ArrayList<>();
        // 按行遍歷
        while (rs.next()) {
            // 保存當(dāng)前行所有列
            String[] columnStr = new String[ColumnCount];
            // 獲取屬性值.
            for (int i = 0; i < ColumnCount; i++) {
                // 獲取一列
                columnStr[i] = rs.getString(i + 1);
                // 計(jì)算當(dāng)前列的最大長度
                columnMaxLengths[i] = Math.max(columnMaxLengths[i], (columnStr[i] == null) ? 0 : columnStr[i].length());
            }
            // 緩存這一行.
            results.add(columnStr);
        }
        printSeparator(columnMaxLengths);
        printColumnName(resultSetMetaData, columnMaxLengths);
        printSeparator(columnMaxLengths);
        // 遍歷集合輸出結(jié)果
        Iterator<String[]> iterator = results.iterator();
        String[] columnStr;
        while (iterator.hasNext()) {
            columnStr = iterator.next();
            for (int i = 0; i < ColumnCount; i++) {
                // System.out.printf("|%" + (columnMaxLengths[i] + 1) + "s", columnStr[i]);
                System.out.printf("|%" + columnMaxLengths[i] + "s", columnStr[i]);
            }
            System.out.println("|");
        }
        printSeparator(columnMaxLengths);
    }

    /**
     * 輸出列名.
     *
     * @param resultSetMetaData 結(jié)果集的元數(shù)據(jù)對(duì)象.
     * @param columnMaxLengths  每一列最大長度的字符串的長度.
     * @throws SQLException
     */
    private static void printColumnName(ResultSetMetaData resultSetMetaData, int[] columnMaxLengths) throws SQLException {
        int columnCount = resultSetMetaData.getColumnCount();
        for (int i = 0; i < columnCount; i++) {
            // System.out.printf("|%" + (columnMaxLengths[i] + 1) + "s", resultSetMetaData.getColumnName(i + 1));
            System.out.printf("|%" + columnMaxLengths[i] + "s", resultSetMetaData.getColumnName(i + 1));
        }
        System.out.println("|");
    }

    /**
     * 輸出分隔符.
     *
     * @param columnMaxLengths 保存結(jié)果集中每一列的最長的字符串的長度.
     */
    private static void printSeparator(int[] columnMaxLengths) {
        for (int i = 0; i < columnMaxLengths.length; i++) {
            System.out.print("+");
            // for (int j = 0; j < columnMaxLengths[i] + 1; j++) {
            for (int j = 0; j < columnMaxLengths[i]; j++) {
                System.out.print("-");
            }
        }
        System.out.println("+");
    }

}

package tools;
import java.sql.*;

public class Test {
    private static String driver = "com.mysql.cj.jdbc.Driver";
    private static String URL = "jdbc:mysql://127.0.0.1:3306/university?serverTimezone=UTC";
    private static String user = "root";
    private static String password = "root";

    /**
     * JDBC測(cè)試.
     */
    private static void JDBCexample() {
        // 1.加載數(shù)據(jù)庫驅(qū)動(dòng)
        try {

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

    /**
     * 測(cè)試Statement的用法.
     */
    private static void testStatement() {
        try (// 2.打開數(shù)據(jù)庫連接
             Connection conn = DriverManager.getConnection(URL, user, password);
             // 3.創(chuàng)建語句
             Statement stmt = conn.createStatement()) {
            // 模擬SQL注入
            testSqlInjecton(stmt);

        } catch (SQLException sqle) {
            System.out.println("SQLException : " + sqle);
        }
    }

    /**
     * 模擬SQL注入.
     *
     * @param stmt Statement對(duì)象.
     * @throws SQLException
     */
    private static void testSqlInjecton(Statement stmt) throws SQLException {
        String name = "X' or 'Y' = 'Y";
        String sql = "select * from instructor where name = '" + name + "'";
        ResultSet rs = stmt.executeQuery(sql);
        ResultSetPrinter.printResultSet(rs);
    }

    public static void main(String[] args) {
        JDBCexample();
    }
}