JDBC 連接 Sybase ASE 實戰三法：JSP 直連、JavaBean 封裝、Swing 圖形化查詢工具完整代碼

Sybase ASE (Adaptive Server Enterprise) JDBC 連接實戰

Sybase ASE (現 SAP ASE) 廣泛用於金融、電信核心系統。本文提供三種層次的完整可運行代碼：JSP 原型驗證、JavaBean 封裝復用、Swing 桌面查詢工具，均基於 **jConnect (Type 4 純 Java 驅動)**。

驅動與環境準備

• 驅動 JAR：jconn4.jar (jConnect 7+, JDBC 4.0, Java 6+) 或 jconn3.jar (舊版)。
• 驅動類名：com.sybase.jdbc4.jdbc.SybDriver (jConnect 7+) / com.sybase.jdbc3.jdbc.SybDriver (jConnect 6)。
• JDBC URL 格式：jdbc:sybase:Tds:host:port/database (Tds = Tabular Data Stream 協議)。
• 默認端口：5000 (可配置)。
• 字符集：建議 URL 參數 ?CHARSET=utf8 或 Properties 設置。

方法一：JSP 直連測試 (原型/快速驗證)

<%@ page import="java.sql.*" contentType="text/html;charset=UTF-8" %>
<%
    String driver = "com.sybase.jdbc4.jdbc.SybDriver";
    String url = "jdbc:sybase:Tds:localhost:5000/mydb?CHARSET=utf8";
    String user = "dba";
    String pwd = "sql";

    Connection conn = null;
    Statement stmt = null;
    ResultSet rs = null;

    try {
        Class.forName(driver).newInstance();
        conn = DriverManager.getConnection(url, user, pwd);
        stmt = conn.createStatement();
        String sql = "SELECT emp_lname, dept_id, street, city, state FROM employee ORDER BY emp_lname";
        rs = stmt.executeQuery(sql);
        
        out.println("<table border='1'><tr><th>姓氏</th><th>部門</th><th>街道</th><th>城市</th><th>州</th></tr>");
        while (rs.next()) {
            out.println("<tr><td>" + rs.getString("emp_lname") + "</td>" +
                        "<td>" + rs.getString("dept_id") + "</td>" +
                        "<td>" + rs.getString("street") + "</td>" +
                        "<td>" + rs.getString("city") + "</td>" +
                        "<td>" + rs.getString("state") + "</td></tr>");
        }
        out.println("</table>");
    } catch (Exception e) {
        out.println("<div style='color:red'>錯誤: " + e.getMessage() + "</div>");
        e.printStackTrace();
    } finally {
        try { if (rs != null) rs.close(); } catch (Exception ignore) {}
        try { if (stmt != null) stmt.close(); } catch (Exception ignore) {}
        try { if (conn != null) conn.close(); } catch (Exception ignore) {}
    }
%>

方法二：JSP + JavaBean 封裝 (分層復用)

SybaseBean.java (放置於 WEB-INF/classes/com/example/bean/)

package com.example.bean;

import java.sql.*;

public class SybaseBean {
    private static final String DRIVER = "com.sybase.jdbc4.jdbc.SybDriver";
    private static final String URL = "jdbc:sybase:Tds:localhost:5000/mydb?CHARSET=utf8";
    private static final String USER = "dba";
    private static final String PWD = "sql";

    // 靜態加載驅動 (類加載時一次)
    static {
        try {
            Class.forName(DRIVER).newInstance();
        } catch (Exception e) {
            throw new ExceptionInInitializerError("Sybase 驅動加載失敗: " + e.getMessage());
        }
    }

    // 獲取連接 (建議生產環境改用 DataSource 連接池)
    private Connection getConnection() throws SQLException {
        return DriverManager.getConnection(URL, USER, PWD);
    }

    // 通用查詢方法 (返回 ResultSet, 調用者負責關閉)
    public ResultSet executeQuery(String sql) throws SQLException {
        Connection conn = getConnection();
        Statement stmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
        return stmt.executeQuery(sql); // 注意: 返回 ResultSet 綁定連接, 調用者必須關閉 rs/stmt/conn
    }

    // 更安全: 封裝完整查詢邏輯, 返回 List

list.jsp (調用 Bean)

<%@ page import="java.util.*, com.example.bean.SybaseBean" contentType="text/html;charset=UTF-8" %>
<%
    SybaseBean bean = new SybaseBean();
    java.util.List> rows = bean.queryForList("SELECT * FROM tjck_dh");
%>
<table border="1">
    <tr><th>Name</th><th>Card No</th><th>Amount</th><th>Home Call</th><th>Office Call</th></tr>
    <%
        for (java.util.Map row : rows) {
    %>
    <tr>
        <td><%= row.get("name") %></td>
        <td><%= row.get("card_no") %></td>
        <td><%= row.get("amount") %></td>
        <td><%= row.get("home_call") %></td>
        <td><%= row.get("Office_call") %></td>
    </tr>
    <% } %>
</table>

方法三：Swing 圖形化查詢工具 (桌面應用/運行時調試)

import java.awt.*;
import java.awt.event.*;
import java.sql.*;
import javax.swing.*;

public class SybaseQueryTool extends JFrame {
    private JTextArea outputArea;
    private JTextField sqlField;
    private JButton execBtn;
    private Connection conn;

    // 數據庫配置 (實際應從配置文件讀取)
    private static final String DRIVER = "com.sybase.jdbc4.jdbc.SybDriver";
    private static final String URL = "jdbc:sybase:Tds:localhost:5000/mydb?CHARSET=utf8";
    private static final String USER = "dba";
    private static final String PWD = "sql";

    public SybaseQueryTool() {
        super("Sybase ASE JDBC 查詢工具");
        initUI();
        initDB();
    }

    private void initUI() {
        setLayout(new BorderLayout());
        setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
        setSize(800, 600);
        setLocationRelativeTo(null);

        // 頂部 SQL 輸入區
        JPanel topPanel = new JPanel(new BorderLayout());
        sqlField = new JTextField("SELECT * FROM employee");
        execBtn = new JButton("執行查詢");
        topPanel.add(new JLabel("SQL: "), BorderLayout.WEST);
        topPanel.add(sqlField, BorderLayout.CENTER);
        topPanel.add(execBtn, BorderLayout.EAST);
        add(topPanel, BorderLayout.NORTH);

        // 中央結果顯示區
        outputArea = new JTextArea();
        outputArea.setFont(new Font("Monospaced", Font.PLAIN, 12));
        outputArea.setEditable(false);
        add(new JScrollPane(outputArea), BorderLayout.CENTER);

        // 事件
        execBtn.addActionListener(e -> executeQuery());
        sqlField.addActionListener(e -> executeQuery()); // 回車執行
    }

    private void initDB() {
        try {
            Class.forName(DRIVER).newInstance();
            conn = DriverManager.getConnection(URL, USER, PWD);
            appendOutput("數據庫連接成功: " + URL);
        } catch (Exception e) {
            appendOutput("連接失敗: " + e.getMessage());
            e.printStackTrace();
        }
    }

    private void executeQuery() {
        String sql = sqlField.getText().trim();
        if (sql.isEmpty()) return;
        appendOutput("\n>>> 執行: " + sql);
        Statement stmt = null;
        ResultSet rs = null;
        try {
            stmt = conn.createStatement();
            boolean hasResult = stmt.execute(sql);
            if (hasResult) {
                rs = stmt.getResultSet();
                printResultSet(rs);
            } else {
                int updateCount = stmt.getUpdateCount();
                appendOutput("更新行數: " + updateCount);
            }
        } catch (SQLException e) {
            appendOutput("錯誤: " + e.getMessage());
            e.printStackTrace();
        } finally {
            closeQuietly(rs, stmt);
        }
    }

    private void printResultSet(ResultSet rs) throws SQLException {
        ResultSetMetaData meta = rs.getMetaData();
        int cols = meta.getColumnCount();
        // 打印表頭
        StringBuilder header = new StringBuilder();
        for (int i = 1; i <= cols; i++) {
            header.append(String.format("%-20s", meta.getColumnLabel(i)));
        }
        appendOutput(header.toString());
        appendOutput("-".repeat(header.length()));
        // 打印數據
        int rowCount = 0;
        while (rs.next()) {
            StringBuilder row = new StringBuilder();
            for (int i = 1; i <= cols; i++) {
                Object val = rs.getObject(i);
                row.append(String.format("%-20s", val != null ? val.toString() : "NULL"));
            }
            appendOutput(row.toString());
            rowCount++;
        }
        appendOutput("-".repeat(header.length()));
        appendOutput("共 " + rowCount + " 行");
    }

    private void appendOutput(String text) {
        SwingUtilities.invokeLater(() -> outputArea.append(text + "\n"));
    }

    private void closeQuietly(AutoCloseable... resources) {
        for (AutoCloseable r : resources) {
            if (r != null) try { r.close(); } catch (Exception ignore) {}
        }
    }

    public static void main(String[] args) {
        SwingUtilities.invokeLater(() -> new SybaseQueryTool().setVisible(true));
    }
}

編譯運行命令

# 編譯
javac -cp ".;jconn4.jar" SybaseQueryTool.java

# 運行 (需 jconn4.jar 在 classpath)
java -cp ".;jconn4.jar" SybaseQueryTool

生產環境關鍵升級點

1. 替換 DriverManager 為 DataSource 連接池：HikariCP / Tomcat JDBC Pool / WebSphere 資源適配器，配置 JNDI 查找。
2. PreparedStatement 防注入：所有參數化查詢使用 ? 佔位符。
3. 事務邊界明確：業務方法控制 commit/rollback，或 Spring @Transactional。
4. 異常統一處理：自定義 DAOException 包裝 SQLException，保留堆棧信息。
5. 連接/語句/結果集 必須關閉：Try-with-resources (Java 7+) 或 finally 塊逆序關閉。

總結

掌握 Sybase ASE JDBC 連接的驅動類名、TDS 協議 URL、字符集配置、jConnect 版本對應，是維護金融/電信舊系統、構建遷移工具的基礎。三層代碼示例涵蓋從原型驗證到分層封裝再到桌面工具的完整演進，生產環境務必引入連接池、參數化查詢、事務管理與資源安全釋放機制。