[Servlet/JSP] 取得連接 Eclipse.Tomcat <-> SQL server *使用DataSource

請拋棄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 。 補充:另一種方法)

     (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 一樣