J2EE中如何使用SQLSERVER數據庫的語句？

經過歸納得出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;

}

}