回覆列表
  • 1 # 使用者990540253822

    經過歸納得出J2EE sqlserver 連線資料庫的三種配置:

    一、連線池方式:

    1、連線遲3個包+sqlserver驅動包複製到tomcat/common/lib

    2、配置tomcat/conf/context.xml,注意2000和2005 驅動名字和路徑

    [xhtml] view plaincopyprint?

    <Resource name="jdbc/pubs"

    auth="Container" type="javax.sql.DataSource" maxActive="100"

    maxIdle="30" maxWait="10000" username="sa" password="120010"

    driverClassName="com.microsoft.jdbc.sqlserver.SQLServerDriver"

    url="jdbc:microsoft:sqlserver://localhost:1433;DatabaseName=books"/>

    [xhtml] view plaincopyprint?

    <Resource name="jdbc/pubs"

    auth="Container" type="javax.sql.DataSource" maxActive="100"

    maxIdle="30" maxWait="10000" username="sa" password="120010"

    driverClassName="com.microsoft.jdbc.sqlserver.SQLServerDriver"

    url="jdbc:microsoft:sqlserver://localhost:1433;DatabaseName=books"/>

    [java] view plaincopyprint?

    //2000:

    driverClassName="com.microsoft.jdbc.sqlserver.SQLServerDriver"

    url="jdbc:microsoft:sqlserver://localhost:1433;DatabaseName=books"/>

    //2005:

    driverClassName="com.microsoft.sqlserver.jdbc.SQLServerDriver"

    url="jdbc:sqlserver://localhost:1433;DatabaseName=books"/>

    [java] view plaincopyprint?

    //2000:

    driverClassName="com.microsoft.jdbc.sqlserver.SQLServerDriver"

    url="jdbc:microsoft:sqlserver://localhost:1433;DatabaseName=books"/>

    //2005:

    driverClassName="com.microsoft.sqlserver.jdbc.SQLServerDriver"

    url="jdbc:sqlserver://localhost:1433;DatabaseName=books"/>

    3、在工程web.xml新增節點

    [c-sharp] view plaincopyprint?

    <resource-ref>

    <res-ref-name>jdbc/pubs</res-ref-name>

    <res-type>javax.sql.DataSource</res-type>

    <res-auth>Container</res-auth>

    </resource-ref>

    [c-sharp] view plaincopyprint?

    <resource-ref>

    <res-ref-name>jdbc/pubs</res-ref-name>

    <res-type>javax.sql.DataSource</res-type>

    <res-auth>Container</res-auth>

    </resource-ref>

    4、得到連線的方法內導如以下幾個包:

    [c-sharp] view plaincopyprint?

    import javax.naming.Context;

    import javax.naming.InitialContext;

    import javax.naming.NamingException;

    import javax.sql.DataSource;

    // 得到Connection物件的方法

    public static Connection getConnection(){

    try {

    Context ic = new InitialContext();

    DataSource source = (DataSource)ic.lookup

    ("java:comp/env/jdbc/bbs");

    con = source.getConnection();

    }catch(NamingException ex){

    ex.printStackTrace();

    }catch(SQLException ex){

    ex.printStackTrace();

    }

    return con;

    }

    [c-sharp] view plaincopyprint?

    import javax.naming.Context;

    import javax.naming.InitialContext;

    import javax.naming.NamingException;

    import javax.sql.DataSource;

    // 得到Connection物件的方法

    public static Connection getConnection(){

    try {

    Context ic = new InitialContext();

    DataSource source = (DataSource)ic.lookup

    ("java:comp/env/jdbc/bbs");

    con = source.getConnection();

    }catch(NamingException ex){

    ex.printStackTrace();

    }catch(SQLException ex){

    ex.printStackTrace();

    }

    return con;

    }

    ================================================================================

    二、讀取屬性檔案方式

    [java] view plaincopyprint?

    *.properties檔案

    driverName=com.microsoft.sqlserver.jdbc.SQLServerDriver

    url=jdbc:sqlserver://localhost:1433;DatabaseName=books

    user=sa

    password=123

    //讀取*.properties檔案的類

    import java.io.InputStream;

    import java.util.Properties;

    public final class Env extends Properties {

    private static Env instance;

    public static Env getInstance(){

    if(instance != null){

    return instance;

    }else{

    makeInstance();

    return instance;

    }

    }

    //synchronized 同步方法,保證同一時間只能被一個使用者呼叫

    private static synchronized void makeInstance(){

    if(instance == null){

    instance = new Env();

    }

    }

    private Env(){

    InputStream is =getClass().getResourceAsStream("db.properties");//配置檔案位置

    try{

    load(is);

    }catch(Exception ex){

    System.err.println("請確認讀取的檔案是否存在!");

    }

    }

    //測試連線是否成功方法方法

    public static void main(String[] args) {

    System.out.println(getInstance().getProperty("driverName"));

    }

    }

    [java] view plaincopyprint?

    *.properties檔案

    driverName=com.microsoft.sqlserver.jdbc.SQLServerDriver

    url=jdbc:sqlserver://localhost:1433;DatabaseName=books

    user=sa

    password=123

    //讀取*.properties檔案的類

    import java.io.InputStream;

    import java.util.Properties;

    public final class Env extends Properties {

    private static Env instance;

    public static Env getInstance(){

    if(instance != null){

    return instance;

    }else{

    makeInstance();

    return instance;

    }

    }

    //synchronized 同步方法,保證同一時間只能被一個使用者呼叫

    private static synchronized void makeInstance(){

    if(instance == null){

    instance = new Env();

    }

    }

    private Env(){

    InputStream is =getClass().getResourceAsStream("db.properties");//配置檔案位置

    try{

    load(is);

    }catch(Exception ex){

    System.err.println("請確認讀取的檔案是否存在!");

    }

    }

    //測試連線是否成功方法方法

    public static void main(String[] args) {

    System.out.println(getInstance().getProperty("driverName"));

    }

    }

    注意類的呼叫:譬如String url = Env.getInstance().getProperties("url");就能得到相應

    的字元竄

    驅動driverName,使用者user, 密碼password獲取方式同上

    ==========================================================

    三、讀取xml檔案中的節點

    首先報連線池包3個和1個數據庫驅動包複製到工程下WEB-INF/lib中

    1、工程下的web.xml要新增以下節點:

    [xhtml] view plaincopyprint?

    <context-param>

    <param-name>driverName</param-name>

    <param-value>com.microsoft.sqlserver.jdbc.SQLServerDriver</param-value>

    </context-param>

    <context-param>

    <param-name>url</param-name>

    <param-value>jdbc:sqlserver://localhost:1433;DatabaseName=bbs</param-value>

    </context-param>

    <context-param>

    <param-name>userName</param-name>

    <param-value>sa</param-value>

    </context-param>

    <context-param>

    <param-name>passWord</param-name>

    <param-value>123</param-value>

    </context-param>

    [xhtml] view plaincopyprint?

    <context-param>

    <param-name>driverName</param-name>

    <param-value>com.microsoft.sqlserver.jdbc.SQLServerDriver</param-value>

    </context-param>

    <context-param>

    <param-name>url</param-name>

    <param-value>jdbc:sqlserver://localhost:1433;DatabaseName=bbs</param-value>

    </context-param>

    <context-param>

    <param-name>userName</param-name>

    <param-value>sa</param-value>

    </context-param>

    <context-param>

    <param-name>passWord</param-name>

    <param-value>123</param-value>

    </context-param>

    2、新建一個普通類繼承HttpServlet類,並實現ServletContextListener監聽介面,如下:

    [c-sharp] view plaincopyprint?

    import java.sql.Connection;

    import java.sql.ResultSet;

    import javax.servlet.ServletContext;

    import javax.servlet.ServletContextEvent;

    import javax.servlet.ServletContextListener;

    import javax.servlet.http.HttpServlet;

    public class ContextListener extends HttpServlet implements ServletContextListener {

    /**

    * 銷燬servlet

    */

    public void contextDestroyed(ServletContextEvent sc) {

    }

    /**

    * 初始化

    */

    public void contextInitialized(ServletContextEvent sc) {

    System.out.println("開啟服務:");

    ServletContext servletContext = sc.getServletContext();

    String driverName = servletContext.getInitParamete("driverName");

    String url = servletContext.getInitParameter("url");

    String userName = servletContext.getInitParameter("userName");

    String passWord = servletContext.getInitParameter("passWord");

    BaseDAO.setDriverName(driverName);

    BaseDAO.setUrl(url);

    BaseDAO.setUser(userName);

    BaseDAO.setPassword(passWord);

    }

    }

    [c-sharp] view plaincopyprint?

    import java.sql.Connection;

    import java.sql.ResultSet;

    import javax.servlet.ServletContext;

    import javax.servlet.ServletContextEvent;

    import javax.servlet.ServletContextListener;

    import javax.servlet.http.HttpServlet;

    public class ContextListener extends HttpServlet implements ServletContextListener {

    /**

    * 銷燬servlet

    */

    public void contextDestroyed(ServletContextEvent sc) {

    }

    /**

    * 初始化

    */

    public void contextInitialized(ServletContextEvent sc) {

    System.out.println("開啟服務:");

    ServletContext servletContext = sc.getServletContext();

    String driverName = servletContext.getInitParamete("driverName");

    String url = servletContext.getInitParameter("url");

    String userName = servletContext.getInitParameter("userName");

    String passWord = servletContext.getInitParameter("passWord");

    BaseDAO.setDriverName(driverName);

    BaseDAO.setUrl(url);

    BaseDAO.setUser(userName);

    BaseDAO.setPassword(passWord);

    }

    }

    3、在公共類BaseDao中

    [java] view plaincopyprint?

    import java.sql.Connection;

    import java.sql.DriverManager;

    import java.sql.ResultSet;

    import java.sql.SQLException;

    import java.sql.Statement;

    import javax.naming.Context;

    import javax.naming.InitialContext;

    import javax.naming.NamingException;

    import javax.sql.DataSource;

    import org.apache.commons.dbcp.BasicDataSource; //連線池要到包

    /*

    * 獲取資料庫連線

    */

    public class BaseDAO {

    private static Connection con;

    private static String driverName;

    private static String url;

    private static String userName;

    private static String passWord;

    //獲取連線物件Connection

    public static Connection getConnection(){

    BasicDataSource dataSource = new BasicDataSource();

    dataSource.setDriverClassName(driverName);

    dataSource.setUrl(url);

    dataSource.setUsername(userName);

    dataSource.setPassword(passWord);

    try{

    con = dataSource.getConnection();

    } catch(SQLException ex) {

    ex.printStackTrace();

    }

    return con;

    }

    /*

    * 配置:從web.xml

    */

    //驅動名稱

    public static String getDriverName() {

    return getDriverName();

    }

    public static void setDriverName(String driverName) {

    BaseDAO.driverName = driverName;

    }

    //URL

    public static String getUrl(){

    return getUrl();

    }

    public static void setUrl(String url) {

    BaseDAO.url = url;

    }

    //使用者名稱

    public static String getUser(){

    return getUser();

    }

    public static void setUser(String userName) {

    BaseDAO.userName = userName;

    }

    //密碼

    public static String getPassWord(){

    return getPassWord();

    }

    public static void setPassword(String passWord) {

    BaseDAO.passWord = passWord;

    }

    }

    [java] view plaincopyprint?

    import java.sql.Connection;

    import java.sql.DriverManager;

    import java.sql.ResultSet;

    import java.sql.SQLException;

    import java.sql.Statement;

    import javax.naming.Context;

    import javax.naming.InitialContext;

    import javax.naming.NamingException;

    import javax.sql.DataSource;

    import org.apache.commons.dbcp.BasicDataSource; //連線池要到包

    /*

    * 獲取資料庫連線

    */

    public class BaseDAO {

    private static Connection con;

    private static String driverName;

    private static String url;

    private static String userName;

    private static String passWord;

    //獲取連線物件Connection

    public static Connection getConnection(){

    BasicDataSource dataSource = new BasicDataSource();

    dataSource.setDriverClassName(driverName);

    dataSource.setUrl(url);

    dataSource.setUsername(userName);

    dataSource.setPassword(passWord);

    try{

    con = dataSource.getConnection();

    } catch(SQLException ex) {

    ex.printStackTrace();

    }

    return con;

    }

    /*

    * 配置:從web.xml

    */

    //驅動名稱

    public static String getDriverName() {

    return getDriverName();

    }

    public static void setDriverName(String driverName) {

    BaseDAO.driverName = driverName;

    }

    //URL

    public static String getUrl(){

    return getUrl();

    }

    public static void setUrl(String url) {

    BaseDAO.url = url;

    }

    //使用者名稱

    public static String getUser(){

    return getUser();

    }

    public static void setUser(String userName) {

    BaseDAO.userName = userName;

    }

    //密碼

    public static String getPassWord(){

    return getPassWord();

    }

    public static void setPassword(String passWord) {

    BaseDAO.passWord = passWord;

    }

    }

  • 中秋節和大豐收的關聯?
  • 冬至將至,如果天堂郵局開張一天,你會寫信給誰?又會寫些什麼?