J2EE 初学者 sqlserver 连接数据库的三种配置

经过归纳得出J2EE sqlserver 连接数据库的三种配置：

 

一、连接池方式：

       1、连接迟3个包+sqlserver驱动包复制到tomcat/common/lib

       2、配置tomcat/conf/context.xml，注意2000和2005 驱动名字和路径

              <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"/>   

              //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添加节点

              <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、得到连接的方法内导如以下几个包：

      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; }

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

 

 

二、读取属性文件方式

       *.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要添加以下节点：

       <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监听接口，如下：

             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中

 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; } }