37、JDBC与数据库连接池：实用工具与应用案例

JDBC与数据库连接池：实用工具与应用案例

1. 引言

在数据库操作中，Java数据库连接（JDBC）是一个重要的技术，它允许Java程序与各种数据库进行交互。本文将详细介绍一些JDBC实用工具以及它们在实际应用中的使用方法，包括查询结果的获取、表格的创建和显示等。

2. JDBC实用工具概述

为了简化数据库操作，提供了一些实用工具类，主要包括 DatabaseUtilities 和 DBResults 。

2.1 DatabaseUtilities类

DatabaseUtilities 类包含了多个静态方法，用于执行数据库查询、创建表格等操作。以下是该类的主要方法：

• getQueryResults方法 ：用于连接数据库，执行指定的查询，并将结果累积到 DBResults 对象中。有两个重载方法，一个接受驱动程序、URL、用户名、密码、查询语句和是否关闭连接的参数；另一个接受已有的连接对象、查询语句和是否关闭连接的参数。

package coreservlets;
import java.sql.*;
public class DatabaseUtilities {
    public static DBResults getQueryResults(String driver,
                                            String url,
                                            String username,
                                            String password,
                                            String query,
                                            boolean close) {
        try {
            Class.forName(driver);
            Connection connection =
                    DriverManager.getConnection(url, username, password);
            return(getQueryResults(connection, query, close));
        } catch(ClassNotFoundException cnfe) {
            System.err.println("Error loading driver: " + cnfe);
            return(null);
        } catch(SQLException sqle) {
            System.err.println("Error connecting: " + sqle);
            return(null);
        }
    }
    public static DBResults getQueryResults(Connection connection,
                                            String query,
                                            boolean close) {
        try {
            DatabaseMetaData dbMetaData = connection.getMetaData();
            String productName =
                    dbMetaData.getDatabaseProductName();
            String productVersion =
                    dbMetaData.getDatabaseProductVersion();
            Statement statement = connection.createStatement();
            ResultSet resultSet = statement.executeQuery(query);
            ResultSetMetaData resultsMetaData =
                    resultSet.getMetaData();
            int columnCount = resultsMetaData.getColumnCount();
            String[] columnNames = new String[columnCount];
            for(int i=1; i<columnCount+1; i++) {
                columnNames[i-1] =
                        resultsMetaData.getColumnName(i).trim();
            }
            DBResults dbResults =
                    new DBResults(connection, productName, productVersion,
                            columnCount, columnNames);
            while(resultSet.next()) {
                String[] row = new String[columnCount];
                for(int i=1; i<columnCount+1; i++) {
                    String entry = resultSet.getString(i);
                    if (entry != null) {
                        entry = entry.trim();
                    }
                    row[i-1] = entry;
                }
                dbResults.addRow(row);
            }
            if (close) {
                connection.close();
            }
            return(dbResults);
        } catch(SQLException sqle) {
            System.err.println("Error connecting: " + sqle);
            return(null);
        }
    }
    public static Connection createTable(String driver,
                                         String url,
                                         String username,
                                         String password,
                                         String tableName,
                                         String tableFormat,
                                         String[] tableRows,
                                         boolean close) {
        try {
            Class.forName(driver);
            Connection connection =
                    DriverManager.getConnection(url, username, password);
            return(createTable(connection, username, password,
                    tableName, tableFormat,
                    tableRows, close));
        } catch(ClassNotFoundException cnfe) {
            System.err.println("Error loading driver: " + cnfe);
            return(null);
        } catch(SQLException sqle) {
            System.err.println("Error connecting: " + sqle);
            return(null);
        }
    }
    public static Connection createTable(Connection connection,
                                         String username,
                                         String password,
                                         String tableName,
                                         String tableFormat,
                                         String[] tableRows,
                                         boolean close) {
        try {
            Statement statement = connection.createStatement();
            try {
                statement.execute("DROP TABLE " + tableName);
            } catch(SQLException sqle) {}
            String createCommand =
                    "CREATE TABLE " + tableName + " " + tableFormat;
            statement.execute(createCommand);
            String insertPrefix =
                    "INSERT INTO " + tableName + " VALUES";
            for(int i=0; i<tableRows.length; i++) {
                statement.execute(insertPrefix + tableRows[i]);
            }
            if (close) {
                connection.close();
                return(null);
            } else {
                return(connection);
            }
        } catch(SQLException sqle) {
            System.err.println("Error creating table: " + sqle);
            return(null);
        }
    }
    public static void printTable(String driver,
                                  String url,
                                  String username,
                                  String password,
                                  String tableName,
                                  int entryWidth,
                                  boolean close) {
        String query = "SELECT * FROM " + tableName;
        DBResults results =
                getQueryResults(driver, url, username,
                        password, query, close);
        printTableData(tableName, results, entryWidth, true);
    }
    public static void printTable(Connection connection,
                                  String tableName,
                                  int entryWidth,
                                  boolean close) {
        String query = "SELECT * FROM " + tableName;
        DBResults results =
                getQueryResults(connection, query, close);
        printTableData(tableName, results, entryWidth, true);
    }
    public static void printTableData(String tableName,
                                      DBResults results,
                                      int entryWidth,
                                      boolean printMetaData) {
        if (results == null) {
            return;
        }
        if (printMetaData) {
            System.out.println("Database: " +
                    results.getProductName());
            System.out.println("Version: " +
                    results.getProductVersion());
            System.out.println();
        }
        System.out.println(tableName + ":");
        String underline =
                padString("", tableName.length()+1, "=");
        System.out.println(underline);
        int columnCount = results.getColumnCount();
        String separator =
                makeSeparator(entryWidth, columnCount);
        System.out.println(separator);
        String row = makeRow(results.getColumnNames(), entryWidth);
        System.out.println(row);
        System.out.println(separator);
        int rowCount = results.getRowCount();
        for(int i=0; i<rowCount; i++) {
            row = makeRow(results.getRow(i), entryWidth);
            System.out.println(row);
        }
        System.out.println(separator);
    }
    private static String makeRow(String[] entries,
                                  int entryWidth) {
        String row = "|";
        for(int i=0; i<entries.length; i++) {
            row = row + padString(entries[i], entryWidth, " ");
            row = row + " |";
        }
        return(row);
    }
    private static String makeSeparator(int entryWidth,
                                        int columnCount) {
        String entry = padString("", entryWidth+1, "-");
        String separator = "+";
        for(int i=0; i<columnCount; i++) {
            separator = separator + entry + "+";
        }
        return(separator);
    }
    private static String padString(String orig, int size,
                                    String padChar) {
        if (orig == null) {
            orig = "<null>";
        }
        StringBuffer buffer = new StringBuffer("");
        int extraChars = size - orig.length();
        for(int i=0; i<extraChars; i++) {
            buffer.append(padChar);
        }
        buffer.append(orig);
        return(buffer.toString());
    }
}

• createTable方法 ：用于创建具有指定格式和行的表格。同样有两个重载方法，一个接受驱动程序、URL、用户名、密码、表名、表格格式、行数据和是否关闭连接的参数；另一个接受已有的连接对象、用户名、密码、表名、表格格式、行数据和是否关闭连接的参数。
• printTable方法 ：用于打印指定表格的所有条目。有两个重载方法，一个接受驱动程序、URL、用户名、密码、表名、条目宽度和是否关闭连接的参数；另一个接受已有的连接对象、表名、条目宽度和是否关闭连接的参数。
• printTableData方法 ：根据 DBResults 对象打印表格数据，包括数据库信息、表名、列名和行数据。

2.2 DBResults类

DBResults 类用于存储JDBC查询的完整结果，与 ResultSet 不同，它具有以下特点：
- 存储所有查询结果，而不是像 ResultSet 那样可能需要重新连接数据库来获取后续行。
- 将结果存储为字符串数组。
- 包含数据库元数据（数据库产品名称和版本）和结果集元数据（列名）。
- 提供 toHTMLTable 方法，将结果转换为对应的HTML表格字符串。

package coreservlets;
import java.sql.*;
import java.util.*;
public class DBResults {
    private Connection connection;
    private String productName;
    private String productVersion;
    private int columnCount;
    private String[] columnNames;
    private Vector queryResults;
    String[] rowData;
    public DBResults(Connection connection,
                     String productName,
                     String productVersion,
                     int columnCount,
                     String[] columnNames) {
        this.connection = connection;
        this.productName = productName;
        this.productVersion = productVersion;
        this.columnCount = columnCount;
        this.columnNames = columnNames;
        rowData = new String[columnCount];
        queryResults = new Vector();
    }
    public Connection getConnection() {
        return(connection);
    }
    public String getProductName() {
        return(productName);
    }
    public String getProductVersion() {
        return(productVersion);
    }
    public int getColumnCount() {
        return(columnCount);
    }
    public String[] getColumnNames() {
        return(columnNames);
    }
    public int getRowCount() {
        return(queryResults.size());
    }
    public String[] getRow(int index) {
        return((String[])queryResults.elementAt(index));
    }
    public void addRow(String[] row) {
        queryResults.addElement(row);
    }
    public String toHTMLTable(String headingColor) {
        StringBuffer buffer =
                new StringBuffer("<TABLE BORDER=1>\n");
        if (headingColor != null) {
            buffer.append("  <TR BGCOLOR=\"" + headingColor +
                    "\">\n    ");
        } else {
            buffer.append("  <TR>\n    ");
        }
        for(int col=0; col<getColumnCount(); col++) {
            buffer.append("<TH>" + columnNames[col]);
        }
        for(int row=0; row<getRowCount(); row++) {
            buffer.append("\n  <TR>\n    ");
            String[] rowData = getRow(row);
            for(int col=0; col<getColumnCount(); col++) {
                buffer.append("<TD>" + rowData[col]);
            }
        }
        buffer.append("\n</TABLE>");
        return(buffer.toString());
    }
}

3. 实用工具的应用案例

3.1 查询员工表格数据

以下是一个使用 DatabaseUtilities 类打印 employees 表格数据的示例：

package coreservlets;
import java.sql.*;
public class EmployeeTest {
    public static void main(String[] args) {
        if (args.length < 5) {
            printUsage();
            return;
        }
        String vendorName = args[4];
        int vendor = DriverUtilities.getVendor(vendorName);
        if (vendor == DriverUtilities.UNKNOWN) {
            printUsage();
            return;
        }
        String driver = DriverUtilities.getDriver(vendor);
        String host = args[0];
        String dbName = args[1];
        String url =
                DriverUtilities.makeURL(host, dbName, vendor);
        String username = args[2];
        String password = args[3];
        DatabaseUtilities.printTable(driver, url,
                username, password,
                "employees", 12, true);
    }
    private static void printUsage() {
        System.out.println("Usage: EmployeeTest host dbName " +
                "username password oracle|sybase.");
    }
}

当连接到Oracle或Sybase数据库时，运行该程序将输出 employees 表格的所有条目，包括数据库信息、列名和行数据。

3.2 以HTML表格形式显示查询结果

以下是一个将 employees 表格查询结果以HTML表格形式显示的示例：

package coreservlets;
import java.sql.*;
public class EmployeeTest2 {
    public static void main(String[] args) {
        if (args.length < 5) {
            printUsage();
            return;
        }
        String vendorName = args[4];
        int vendor = DriverUtilities.getVendor(vendorName);
        if (vendor == DriverUtilities.UNKNOWN) {
            printUsage();
            return;
        }
        String driver = DriverUtilities.getDriver(vendor);
        String host = args[0];
        String dbName = args[1];
        String url =
                DriverUtilities.makeURL(host, dbName, vendor);
        String username = args[2];
        String password = args[3];
        String query = "SELECT * FROM employees";
        DBResults results =
                DatabaseUtilities.getQueryResults(driver, url,
                        username, password,
                        query, true);
        System.out.println(results.toHTMLTable("CYAN"));
    }
    private static void printUsage() {
        System.out.println("Usage: EmployeeTest2 host dbName " +
                "username password oracle|sybase.");
    }
}

运行该程序将输出一个带有青色背景标题的HTML表格，包含 employees 表格的所有数据。

3.3 创建员工表格

以下是一个使用 DatabaseUtilities 类创建 employees 表格的示例：

package coreservlets;
import java.sql.*;
public class EmployeeCreation {
    public static Connection createEmployees(String driver,
                                             String url,
                                             String username,
                                             String password,
                                             boolean close) {
        String format =
                "(id int, firstname varchar(32), lastname varchar(32), " +
                        "language varchar(16), salary float)";
        String[] employees =
                {"(1, 'Wye', 'Tukay', 'COBOL', 42500)",
                        "(2, 'Britt', 'Tell',   'C++',   62000)",
                        "(3, 'Max',  'Manager', 'none',  15500)",
                        "(4, 'Polly', 'Morphic', 'Smalltalk', 51500)",
                        "(5, 'Frank', 'Function', 'Common Lisp', 51500)",
                        "(6, 'Justin', 'Timecompiler', 'Java', 98000)",
                        "(7, 'Sir', 'Vlet', 'Java', 114750)",
                        "(8, 'Jay', 'Espy', 'Java', 128500)" };
        return(DatabaseUtilities.createTable(driver, url,
                username, password,
                "employees",
                format, employees,
                close));
    }
    public static void main(String[] args) {
        if (args.length < 5) {
            printUsage();
            return;
        }
        String vendorName = args[4];
        int vendor = DriverUtilities.getVendor(vendorName);
        if (vendor == DriverUtilities.UNKNOWN) {
            printUsage();
            return;
        }
        String driver = DriverUtilities.getDriver(vendor);
        String host = args[0];
        String dbName = args[1];
        String url =
                DriverUtilities.makeURL(host, dbName, vendor);
        String username = args[2];
        String password = args[3];
        createEmployees(driver, url, username, password, true);
    }
    private static void printUsage() {
        System.out.println("Usage: EmployeeCreation host dbName " +
                "username password oracle|sybase.");
    }
}

运行该程序将创建一个名为 employees 的表格，并插入相应的数据。

4. 交互式查询查看器

在实际应用中，查询可能是根据用户输入动态生成的。为了实现交互式查询，提供了一个查询查看器。

4.1 查询查看器的工作流程

当用户按下“Show Results”按钮时，系统将执行以下步骤：
1. 从用户界面元素中读取主机、端口、数据库名称、用户名、密码和驱动程序类型。
2. 提交查询并存储结果到 DBResults 对象中。
3. 将 DBResults 对象传递给自定义的表格模型 DBResultsTableModel 。
4. 创建一个 JTable 并将其放置在 JFrame 的底部区域。
5. 调用 pack 方法调整 JFrame 的大小以适应表格。

graph TD;
    A[用户输入信息] --> B[读取信息];
    B --> C[提交查询];
    C --> D[获取DBResults对象];
    D --> E[创建DBResultsTableModel];
    E --> F[创建JTable];
    F --> G[放置JTable到JFrame];
    G --> H[调整JFrame大小];

4.2 查询查看器代码实现

以下是查询查看器的主要代码：

package coreservlets;
import java.awt.*;
import java.awt.event.*;
import javax.swing.*;
import javax.swing.table.*;
public class QueryViewer extends JFrame
        implements ActionListener{
    public static void main(String[] args) {
        new QueryViewer();
    }
    private JTextField hostField, dbNameField,
            queryField, usernameField;
    private JRadioButton oracleButton, sybaseButton;
    private JPasswordField passwordField;
    private JButton showResultsButton;
    private Container contentPane;
    private JPanel tablePanel;
    public QueryViewer () {
        super("Database Query Viewer");
        WindowUtilities.setNativeLookAndFeel();
        addWindowListener(new ExitListener());
        contentPane = getContentPane();
        contentPane.add(makeControlPanel(), BorderLayout.NORTH);
        pack();
        setVisible(true);
    }
    public void actionPerformed(ActionEvent event) {
        String host = hostField.getText();
        String dbName = dbNameField.getText();
        String username = usernameField.getText();
        String password =
                String.valueOf(passwordField.getPassword());
        String query = queryField.getText();
        int vendor;
        if (oracleButton.isSelected()) {
            vendor = DriverUtilities.ORACLE;
        } else {
            vendor = DriverUtilities.SYBASE;
        }
        if (tablePanel != null) {
            contentPane.remove(tablePanel);
        }
        tablePanel = makeTablePanel(host, dbName, vendor,
                username, password,
                query);
        contentPane.add(tablePanel, BorderLayout.CENTER);
        pack();
    }
    private JPanel makeTablePanel(String host,
                                  String dbName,
                                  int vendor,
                                  String username,
                                  String password,
                                  String query) {
        String driver = DriverUtilities.getDriver(vendor);
        String url = DriverUtilities.makeURL(host, dbName, vendor);
        DBResults results =
                DatabaseUtilities.getQueryResults(driver, url,
                        username, password,
                        query, true);
        JPanel panel = new JPanel(new BorderLayout());
        if (results == null) {
            panel.add(makeErrorLabel());
            return(panel);
        }
        DBResultsTableModel model =
                new DBResultsTableModel(results);
        JTable table = new JTable(model);
        table.setFont(new Font("Serif", Font.PLAIN, 17));
        table.setRowHeight(28);
        JTableHeader header = table.getTableHeader();
        header.setFont(new Font("SansSerif", Font.BOLD, 13));
        panel.add(table, BorderLayout.CENTER);
        panel.add(header, BorderLayout.NORTH);
        panel.setBorder
                (BorderFactory.createTitledBorder("Query Results"));
        return(panel);
    }
    private JPanel makeControlPanel() {
        JPanel panel = new JPanel(new GridLayout(0, 1));
        panel.add(makeHostPanel());
        panel.add(makeUsernamePanel());
        panel.add(makeQueryPanel());
        panel.add(makeButtonPanel());
        panel.setBorder
                (BorderFactory.createTitledBorder("Query Data"));
        return(panel);
    }
    private JPanel makeHostPanel() {
        JPanel panel = new JPanel();
        panel.add(new JLabel("Host:"));
        hostField = new JTextField(15);
        panel.add(hostField);
        panel.add(new JLabel("    DB Name:"));
        dbNameField = new JTextField(15);
        panel.add(dbNameField);
        panel.add(new JLabel("    Driver:"));
        ButtonGroup vendorGroup = new ButtonGroup();
        oracleButton = new JRadioButton("Oracle", true);
        vendorGroup.add(oracleButton);
        panel.add(oracleButton);
        sybaseButton = new JRadioButton("Sybase");
        vendorGroup.add(sybaseButton);
        panel.add(sybaseButton);
        return(panel);
    }
    private JPanel makeUsernamePanel() {
        JPanel panel = new JPanel();
        usernameField = new JTextField(10);
        passwordField = new JPasswordField(10);
        panel.add(new JLabel("Username: "));
        panel.add(usernameField);
        panel.add(new JLabel("    Password:"));
        panel.add(passwordField);
        return(panel);
    }
    private JPanel makeQueryPanel() {
        JPanel panel = new JPanel();
        queryField = new JTextField(40);
        queryField.addActionListener(this);
        panel.add(new JLabel("Query:"));
        panel.add(queryField);
        return(panel);
    }
    private JPanel makeButtonPanel() {
        JPanel panel = new JPanel();
        showResultsButton = new JButton("Show Results");
        showResultsButton.addActionListener(this);
        panel.add(showResultsButton);
        return(panel);
    }
    private JLabel makeErrorLabel() {
        JLabel label = new JLabel("No Results", JLabel.CENTER);
        label.setFont(new Font("Serif", Font.BOLD, 36));
        return(label);
    }
}

5. 总结

通过使用这些JDBC实用工具，我们可以简化数据库操作，包括查询结果的获取、表格的创建和显示等。同时，交互式查询查看器允许用户动态输入查询并查看结果，提高了数据库操作的灵活性和交互性。在实际开发中，可以根据具体需求对这些工具进行扩展和优化，以满足不同的业务场景。

JDBC与数据库连接池：实用工具与应用案例

6. 表格模型与窗口工具类

6.1 DBResultsTableModel类

DBResultsTableModel 类是一个简单的类，它的作用是告诉 JTable 如何从 DBResults 对象中提取相关数据。 DBResults 对象用于存储数据库查询的结果。以下是该类的代码：

package coreservlets;
import javax.swing.table.*;
public class DBResultsTableModel extends AbstractTableModel {
    private DBResults results;
    public DBResultsTableModel(DBResults results) {
        this.results = results;
    }
    public int getRowCount() {
        return(results.getRowCount());
    }
    public int getColumnCount() {
        return(results.getColumnCount());
    }
    public String getColumnName(int column) {
        return(results.getColumnNames()[column]);
    }
    public Object getValueAt(int row, int column) {
        return(results.getRow(row)[column]);
    }
}

这个类重写了 AbstractTableModel 的几个重要方法：
| 方法名 | 作用 |
| ---- | ---- |
| getRowCount | 返回结果集中的行数 |
| getColumnCount | 返回结果集中的列数 |
| getColumnName | 根据列索引返回列名 |
| getValueAt | 根据行索引和列索引返回对应单元格的值 |

6.2 WindowUtilities类

WindowUtilities 类提供了一些简化Swing窗口使用的实用方法，主要用于设置窗口的外观和感觉。以下是该类的代码：

package coreservlets;
import javax.swing.*;
import java.awt.*;
public class WindowUtilities {
    public static void setNativeLookAndFeel() {
        try {
            UIManager.setLookAndFeel(UIManager.getSystemLookAndFeelClassName());
        } catch(Exception e) {
            System.out.println("Error setting native LAF: " + e);
        }
    }
    public static void setJavaLookAndFeel() {
        try {
            UIManager.setLookAndFeel(UIManager.getCrossPlatformLookAndFeelClassName());
        } catch(Exception e) {
            System.out.println("Error setting Java LAF: " + e);
        }
    }
    public static void setMotifLookAndFeel() {
        try {
            UIManager.setLookAndFeel("com.sun.java.swing.plaf.motif.MotifLookAndFeel");
        } catch(Exception e) {
            System.out.println("Error setting Motif LAF: " + e);
        }
    }
}

这个类包含了三个方法：
- setNativeLookAndFeel ：设置系统的本地外观和感觉，让窗口看起来和操作系统的其他应用程序一致。
- setJavaLookAndFeel ：设置Java的跨平台外观和感觉，即Metal外观。
- setMotifLookAndFeel ：设置Motif外观和感觉。

7. 实际应用中的注意事项

7.1 异常处理

在使用JDBC进行数据库操作时，异常处理是非常重要的。在上述的代码中，我们可以看到在连接数据库、执行查询和创建表格等操作时，都使用了 try-catch 块来捕获可能出现的异常，如 ClassNotFoundException 和 SQLException 。在实际应用中，我们应该根据具体的业务需求对异常进行更细致的处理，例如记录日志、给用户友好的提示等。

7.2 资源管理

在进行数据库操作时，要注意资源的管理，特别是数据库连接、语句和结果集。在上述代码中，我们可以看到在 getQueryResults 和 createTable 方法中，都有根据 close 参数来决定是否关闭数据库连接的逻辑。在实际应用中，我们应该确保在不需要使用这些资源时及时关闭，避免资源泄漏。

7.3 安全性

在处理用户输入的查询语句时，要注意防止SQL注入攻击。可以使用预编译语句（ PreparedStatement ）来避免这个问题。在上述的代码中，虽然没有涉及到预编译语句的使用，但在实际应用中，如果查询语句包含用户输入的参数，应该优先使用预编译语句。

8. 总结与展望

通过本文的介绍，我们了解了一些JDBC实用工具的使用方法，包括 DatabaseUtilities 和 DBResults 类，以及它们在查询结果获取、表格创建和显示等方面的应用。同时，我们还介绍了交互式查询查看器的实现，它允许用户动态输入查询并查看结果，提高了数据库操作的灵活性和交互性。

在未来的开发中，我们可以进一步扩展这些工具的功能，例如支持更多的数据库类型、优化查询性能、增强用户界面的交互性等。同时，我们也可以将这些工具集成到更大的项目中，为项目提供更强大的数据库操作能力。

graph LR;
    A[现有工具] --> B[扩展功能];
    B --> C[支持更多数据库类型];
    B --> D[优化查询性能];
    B --> E[增强用户界面交互性];
    A --> F[集成到项目];
    F --> G[提供强大数据库操作能力];

总之，JDBC是一个非常强大的数据库连接技术，通过合理使用相关的实用工具，我们可以更高效地进行数据库操作，满足不同的业务需求。