java打印表格将ResultSet中的数据打印成表格问题
问题描述
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(ResultSet rs) throws SQLException {
ResultSetMetaData resultSetMetaData = rs.getMetaData();
// 获取列数
int ColumnCount = resultSetMetaData.getColumnCount();
// 保存当前列最大长度的数组
int[] columnMaxLengths = new int[ColumnCount];
// 缓存结果集,结果集可能有序,所以用ArrayList保存变得打乱顺序.
ArrayList<String[]> results = 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(columnMaxLengths[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);
}
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("|");
}
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";
private static void JDBCexample() {
// 1.加载数据库驱动
try {
Class.forName(driver);
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
testStatement();
}
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 : " + sqle);
}
}
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|
+-----+----------+----------+---------+
总结
以上为个人经验,希望能给大家一个参考,也希望大家多多支持编程网。
免责声明:
① 本站未注明“稿件来源”的信息均来自网络整理。其文字、图片和音视频稿件的所属权归原作者所有。本站收集整理出于非商业性的教育和科研之目的,并不意味着本站赞同其观点或证实其内容的真实性。仅作为临时的测试数据,供内部测试之用。本站并未授权任何人以任何方式主动获取本站任何信息。
② 本站未注明“稿件来源”的临时测试数据将在测试完成后最终做删除处理。有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341