請拋棄DataManager.getConnection (... , ... , ...)
查詢 Doc. -> JNDI Datasource HOW-TO
<DataSource 設定>
1.將JDBC Driver 加入Tomcat
> sqljdbc4.jar 加入Tomcat安裝目錄\lib
* 如果使用Tomcat的 launch configuration ，在Java Resource -> Libraries ->Apache Tomcat v7.0 中看不到sqljdbc4.jar
2.在Tomcat裡設定DataSource
> WebContent -> META-INF -> 新增 context.xml
(context內容可以在文件中可以找到)
<?xml version="1.0" encoding="UTF-8"?>
<Context> <Resource name="jdbc/MyDB" auth="Container"
type="javax.sql.DataSource"
maxActive="100" maxIdle="30" maxWait="10000" username="sa" password="passw0rd"
driverClassName="com.microsoft.sqlserver.jdbc.SQLServerDriver"
url="jdbc:sqlserver://localhost:1433;database=JDBC"/>
</Context>
PS.若使用文件中的格式，需要改: name >常取”jdbc/XXX”的格式；
type >”javax.sql.DataSource”;
username >資料庫用戶帳號
password >資料庫用戶密碼
driveClassName >"com.microsoft.sqlserver.jdbc.SQLServerDriver"
*資料庫驅動 可從
Java Resource -> Libraries ->Apache Tomcat v7.0 ->sqljdbc4.jar -> com.microsoft.sqlserver -> jdbc.SQLServerDriver.class
按 右鍵 -> Copy Qualified Name ->貼到driveClassName (記得刪掉 .class)
url >"jdbc:sqlserver://localhost:1433;database=JDBC"
*google : "sql server url"
---補充---
maxIdle: 資料庫連接的最大閒置時間。超過閒置時間，資料庫連接將被標記为不可用，然後被釋放。設為0表示無限制。
maxActive: Connection Pool 的最大資料庫連接數。設為0表示無限制。
maxWait: 最大建立連接等待時間。如果超過此時間將接到異常。設為-1表示無限制。
3.將web.xml 設定DataSource (此處的 web.xml 是在WebContent\WEB-INF中)
<resource-ref> <res-ref-name>jdbc/MyDB</res-ref-name> <res-type>javax.sql.DataSource</res-type> <res-auth>Container</res-auth> <res-sharing-scope>Shareable</res-sharing-scope></resource-ref>
* <res-ref-name>、<res-type>、 <res-auth> 同 context.xml 中 name、type、auth
* <res-sharing-scope> 只有 Shareable , Unshareable 兩種選項(請使用alt+/選擇)
what it exactly means to have <res-sharing-scope> ?
The Bean Provider can control the shareability of the connections acquired from the resource manager connection factory. By default, connections to a resource manager are shareable across other enterprise beans in the application that use the same resource in the same transaction context. The Bean Provider can specify that connections obtained from a resource manager connection factory reference are not shareable by specifying the value of the "res-sharing-scope" deployment descriptor element to be "Unshareable". The sharing the connections to a resource manager allows the container to optimize the use of connections and enables the container's use of local transactions optimizations.
4.寫程式測試DataSource (test.jsp)
-test.jsp-
<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>Insert title here</title>
</head>
<body>
<%@ page import="javax.sql.DataSource" %>
<%@ page import="javax.naming.Context" %>
<%@ page import="javax.naming.InitialContext" %>
<%@ page import="java.sql.*" %>
<%
Context ctx = new InitialContext();
DataSource ds = (DataSource) ctx.lookup("java:comp/env/jdbc/MyDB"); Connection conn = ds.getConnection(); //JDBC Statement stmt = conn.createStatement(); ResultSet rset = stmt.executeQuery("select * from dept"); while(rset.next()) { String col1 = rset.getString(1); out.println(col1+"<Br>"); } rset.close(); stmt.close(); conn.close(); %>
</body>
</html>
---------
*lookup("java:comp/env/jdbc/MyDB") 底線部分記得要跟web.xml中 res-ref-name 一樣
