本範例展示一個完整的 Servlet 連線 Oracle 資料庫 (使用 JDBC Thin 驅動)，包含詳細的生命週期日誌、JDBC 驅動日誌啟用、連線參數設定與查詢執行。適合作為整合測試、環境驗證或教學參考。
import java.io.*;
import java.sql.*;
import java.util.Date;
import javax.servlet.*;
import javax.servlet.http.*;
public class OracleTestServlet extends HttpServlet {
private static final String SERVLET_NAME = "OracleTestServlet";
private static final String JDBC_LOG_FILE = "./OracleServletJDBC.log";
private long servletStartTimeStamp;
private boolean servletInitialized = false;
@Override
public void init(ServletConfig config) throws ServletException {
super.init(config);
servletStartTimeStamp = System.currentTimeMillis();
log(SERVLET_NAME + ": Init Completed at " + new Date(servletStartTimeStamp));
servletInitialized = true;
}
@Override
protected void doGet(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
long startTime = System.currentTimeMillis();
log(SERVLET_NAME + " doGet Start");
response.setContentType("text/html;charset=UTF-8");
PrintWriter out = response.getWriter();
out.println("Oracle JDBC Test ");
out.println("Employee List Test
");
out.println("");
// 1. 啟用 JDBC 驅動日誌 (除錯用)
enableJdbcLogging();
Connection conn = null;
Statement stmt = null;
ResultSet rset = null;
try {
// 2. 註冊驅動 (JDBC 4.0+ SPI 自動載入，可省略)
log(SERVLET_NAME + " Registering Driver");
DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
// 3. 建立連線 (Thin 模式: host:port:SID)
// 格式: jdbc:oracle:thin:@::
// 或服務名: jdbc:oracle:thin:@//:/
String url = "jdbc:oracle:thin:@9.12.2.10:1621:MPM5";
String user = "system";
String pwd = "manager"; // 實際專案請勿硬編碼，使用 JNDI DataSource
log(SERVLET_NAME + " Getting Connection...");
conn = DriverManager.getConnection(url, user, pwd);
log(SERVLET_NAME + " Connection Established");
// 4. 執行查詢
stmt = conn.createStatement();
log(SERVLET_NAME + " Statement Created");
String sql = "SELECT EMPNO, ENAME FROM SCOTT.EMP";
rset = stmt.executeQuery(sql);
log(SERVLET_NAME + " Query Executed");
// 5. 處理結果
out.println("EMPNO\tENAME");
out.println("----\t-----");
while (rset.next()) {
out.println(rset.getString(1) + "\t" + rset.getString(2));
}
log(SERVLET_NAME + " Results Processed");
} catch (SQLException sqle) {
log(SERVLET_NAME + " SQLException: " + sqle.getMessage(), sqle);
out.println("SQL Error: " + sqle.getMessage() + "");
} catch (Exception e) {
log(SERVLET_NAME + " Exception: " + e.getMessage(), e);
out.println("Error: " + e.getMessage() + "");
} finally {
// 6. 關閉資源 (逆序)
closeQuietly(rset);
closeQuietly(stmt);
closeQuietly(conn);
log(SERVLET_NAME + " Resources Closed");
}
out.println("
");
out.println("Execution time: " + (System.currentTimeMillis() - startTime) + " ms
");
out.println("");
out.close();
log(SERVLET_NAME + " doGet End");
}
@Override
public void destroy() {
log(SERVLET_NAME + ": Terminated at " + new Date());
super.destroy();
}
@Override
public String getServletInfo() {
return "Oracle JDBC Thin Driver Test Servlet";
}
// --- Helper Methods ---
private void enableJdbcLogging() {
try {
File logFile = new File(JDBC_LOG_FILE);
// 確保目錄可寫
File parent = logFile.getParentFile();
if (parent != null && !parent.canWrite()) {
log(SERVLET_NAME + " Log directory not writable: " + parent.getAbsolutePath());
return;
}
// 追加模式
DriverManager.setLogStream(new PrintStream(
new FileOutputStream(logFile, true), true, "UTF-8"));
log(SERVLET_NAME + " JDBC Logging enabled: " + logFile.getAbsolutePath());
} catch (IOException ioe) {
log(SERVLET_NAME + " Failed to enable JDBC logging: " + ioe.getMessage());
}
}
private void closeQuietly(AutoCloseable resource) {
if (resource != null) {
try { resource.close(); } catch (Exception ignored) {}
}
}
private void closeQuietly(ResultSet rs) {
if (rs != null) {
try { rs.close(); } catch (SQLException ignored) {}
}
}
private void closeQuietly(Statement stmt) {
if (stmt != null) {
try { stmt.close(); } catch (SQLException ignored) {}
}
}
private void closeQuietly(Connection conn) {
if (conn != null) {
try { conn.close(); } catch (SQLException ignored) {}
}
}
}| 項目 | 說明 |
|---|---|
| JDBC Thin 驅動 | Type 4 純 Java，無需 Oracle Client 安裝。Maven: com.oracle.database.jdbc:ojdbc11:23.4.0.0 (需 Oracle 帳號下載或從 Maven Central 取較舊版本) |
| 連線 URL 格式 | jdbc:oracle:thin:@host:port:SID (SID) 或 jdbc:oracle:thin:@//host:port/service_name (服務名，RAC/PDB 推薦) |
| 驅動日誌 | DriverManager.setLogStream() 捕獲驅動內部網路封包、協定交互，極難除錯時啟用，**生產環境關閉** (效能影響大) |
| 生命週期日誌 | 覆寫 init()/destroy() 記錄 Servlet 載入/卸載時間，配合 log() 寫入容器日誌 (catalina.out 等) |
| 資源管理 | 統一 closeQuietly() 避免 finally 區塊重複 try-catch，JDK 7+ 建議改用 try-with-resources |
| 硬編碼帳密 | **教學示範用**。實際專案**必須**使用 JNDI DataSource + 連線池，帳密由容器/設定中心管理 |
ojdbc11.jar 放入 WEB-INF/lib 或容器共用庫目錄 (Tomcat: $CATALINA_HOME/lib)javac -cp servlet-api.jar:ojdbc11.jar OracleTestServlet.javahttps://host:port/context/OracleTestServletOracleServletJDBC.log 確認連線流程lsnrctl status 監聽器註冊的服務名oracle.jdbc.defaultNChar=true、Servlet response.setCharacterEncoding("UTF-8")telnet host 1521 測試埠口連通性// Spring Boot 2.7+ / Spring 6 整合範例
@Repository
public class EmployeeRepository {
@Autowired JdbcTemplate jdbcTemplate;
public List findAll() {
return jdbcTemplate.query(
"SELECT EMPNO, ENAME FROM SCOTT.EMP",
(rs, rowNum) -> new Employee(rs.getInt("EMPNO"), rs.getString("ENAME"))
);
}
}
// application.yml
spring:
datasource:
url: jdbc:oracle:thin:@//localhost:1521/ORCLPDB
username: app_user
password: ${DB_PASSWORD}
hikari:
maximum-pool-size: 20
jpa:
properties:
hibernate:
dialect: org.hibernate.dialect.OracleDialect 本 Servlet 示範了 Oracle JDBC Thin 驅動的標準連線、查詢、日誌、資源管理完整流程。實際專案請遷移至 **連線池 + JNDI/DataSource + 框架封裝 (Spring JDBC/MyBatis/JOOQ)** 架構，並嚴格管理憑證與連線參數。
