目录
- 问题描述
- 思路
- 实现
- 测试
- 运行结果
- 总结
问题描述
mysql的查询语句输出如下:
mysql> select * from instructor;
+-------+------------+------------+-----------+| ID | name | dept_name | salary |+-------+------------+------------+-----------+| 10101 | Srinivasan | Comp. Sci. | 65000.00 || 12121 | Wu | Finance | 90000.00 || 15151 | Mozart | Music | 40000.00 || 22222 | Einstein | Physics | 95000.00 || 25566 | Brown | Biology | 100000.00 || 30765 | Green | Music | NULL || 32343 | El Said | History | 60000.00 || 33456 | Gold | Physics | 87000.00 || 45565 | Katz | Comp. Sci. | 75000.00 || 58583 | Califieri | History | 62000.00 || 76543 | Singh | Finance | 80000.00 || 76766 | Crick | Biology | 72000.00 || 77987 | Kim | Physics | 98000.00 || 83821 | Brandt | Comp. Sci. | 92000.00 || 88877 | Perry | Finance | 125000.00 || 88878 | Perry | Finance | 125000.00 || 98345 | Kim | Elec. Eng. | 80000.00 |+-------+------------+------------+-----------+17 rows in set (0.04 sec)
使用JDBC执行上述的查询语句,将结果集中的查询结果以表格的形式打印出来。
思路
- 通过结果集的元数据可以知道结果集中的列数,和列名.
- 然后遍历结果集,分别统计每一列中的最大字符数。
- 然后通过System.out.printf()方法进行格式化输出。
实现
package tools; import Java.sql.ResultSet; import java.sql.ResultSetMetaData; import java.sql.SQLException; import java.util.ArrayList; import java.util.Iterator; /** * 结果集打印机.将结果集中的数据打印成表格. */ public class ResultSetPrinter { public static void printResultSet(Res编程ultSet rs) throws SQLException { ResultSetMetaData resultSetMetaData = rs.getMetaData(); // 获取列数 int ColumnCount = resultSetMetaData.getColumnCount(); // 保存当前列最大长度的数组 int[] columnMaxLengths = new int[ColumnCount]; // 缓存结果集,结果集可能有序,所以用ArrayList保存变得打乱顺序. ArrayList<String[]> resuphplts = new ArrayList<>(); // 按行遍历 while (rs.next()) { // 保存当前行所有列 String[] columnStr = new String[ColumnCount]; // 获取属性值. for (int i = 0; i < ColumnCount; i++) { // 获取一列 columnStr[i] = rs.getString(i + 1); // 计算当前列的最大长度 columnMaxLengths[i] = Math.max(columnMaxLen开发者_Python开发gths[i], (columnStr[i] == null) ? 0 : columnStr[i].length()); } // 缓存这一行. results.add(columnStr); } printSeparator(columnMaxLengths); printColumnName(resultSetMetaData, columnMaxLengths); printSeparator(columnMaxLengths); // 遍历集合输出结果 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 结果集的元数据对象. * @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 保存结果集中每一列的最长的字符串的长度. */ 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:python3306/university?serverTimezone=UTC"; private static String user = "root"; private static String password = "root"; /** * JDBC测试. */ private static void JDBCexample() { // 1.加载数据库驱动 try { Class.forName(driver); } catch (ClassNotFoundException e) { e.printStackTrace(); } testStatement(); } /** * 测试Statement的用法. */ private static void testStatement() { try (// 2.打开数据库连接 Connection conn = DriverManager.getConnection(URL, user, password); // 3.创建语句 Statement stmt = conn.createStatement()) { // 模拟SQL注入 testSqlInjecton(stmt); } catch (SQLException sqle) { System.out.println("SQLException : "js + sqle); } } /** * 模拟SQL注入. * * @param stmt Statement对象. javascript * @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(); } }
运行结果
+-----+----------+----------+---------+
| ID| name| dept_name| salary|+-----+----------+----------+---------+|10101|Srinivasan|Comp. Sci.| 65000.00||12121| Wu| Finance| 90000.00||15151| Mozart| Music| 40000.00||22222| Einstein| Physics| 95000.00||25566| Brown| Biology|100000.00||30765| Green| Music| null||32343| El Said| History| 60000.00||33456| Gold| Physics| 87000.00||45565| Katz|Comp. Sci.| 75000.00||58583| Califieri| History| 62000.00||76543| Singh| Finance| 80000.00||76766| Crick| Biology| 72000.00||77987| Kim| Physics| 98000.00||83821| Brandt|Comp. Sci.| 92000.00||88877| Perry| Finance|125000.00||88878| Perry| Finance|125000.00||98345| Kim|Elec. Eng.| 80000.00|+-----+----------+----------+---------+
总结
以上为个人经验,希望能给大家一个参考,也希望大家多多支持我们。
精彩评论