局部数据源的使用

1.我们都知道JDBC的固定步骤是以下几步组成：：

(1)Class.forName();

(2)Connection con  = DriverManager.getConnection();

(3)PreparedStatement stat = con.prepareStatement(sql);

(4)stat.executeQuery();

(5)con.close();

而(1)(2)(5)步是每次JDBC操作都要执行的，重复执行是非常耗时的，为了解决重复操作的问题，引入了数据源。

2.首先在WEB应用程序中的META-INF文件夹下，建立一个context.xml的文件。以下代码表示数据源名是jdbc/sampleDS.数据库的用户名是root.root用户对应的密码是123456.

<?xml version="1.0" encoding="UTF-8"?>

<Context reloadable="true">
<Resource
   name="jdbc/sampleDS"
   type="javax.sql.DataSource"
   maxActive="4"
   maxIdle="2"
   username="root"
   maxWait="5000"
   driverClassName="com.mysql.jdbc.Driver"
   password="123456"
   url="jdbc:mysql://127.0.0.1:3306/echarts" />
</Context>

3.通过DataSource的名字查找对应的DataSource，通过一个DataSource在连接池中获得一个Connection

4.在servlet中使用

import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import javax.naming.Context;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.servlet.RequestDispatcher;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.sql.DataSource;


public class UserQueryServlet extends HttpServlet{
    DataSource dataSource;
    
    public void init(){
        try{
            //创建一个上下文对象
            Context context = new InitialContext();
            //然后通过它的lookup方法查找数据源对象
            dataSource = (DataSource)context.lookup("java:comp/env/jdbc/sampleDS");
        }catch(NamingException ne){
            log("Exception:" + ne);
        }
    }

    public void doPost(HttpServletRequest request,HttpServletResponse response)
             throws ServletException,IOException{
        Connection dbConnection=null;
        try{
            //获得数据库连接对象
            dbConnection = dataSource.getConnection();
        }catch(SQLException se){
            log("Exctption:" + se);
        } 
        response.setContentType("text/html;charset=UTF-8"); 
        String UserName,password;    
        if( request.getParameter("username") !=null){
            UserName = request.getParameter("username");
            password = request.getParameter("password");
            UserDAO userdao = new UserDAO();
            UserBean userbean = userdao.searchUser(UserName);
            request.getSession().setAttribute("user", userbean);
            
            try{
                String sql = "SELECT*FROM user WHERE userName=?";
                System.out.println(sql);
                PreparedStatement pstmt = dbConnection.prepareStatement(sql);
                pstmt.setString(1,UserName);
                ResultSet rst = pstmt.executeQuery();
                if(rst.next()) {
                    UserBean user = new UserBean();
                    user.setUserName(rst.getString("userName"));
                    user.setPassword(rst.getString("userPassword"));
                    request.getSession().setAttribute("username", UserName);
                    if(user.getPassword().equals(password)){
                        RequestDispatcher view = request.getRequestDispatcher("/showUser.jsp");
                        view .forward(request, response);
                    }else{
                        RequestDispatcher view = request.getRequestDispatcher("/error.jsp");
                        view .forward(request, response);
                    }                    
                }else{
                    RequestDispatcher view = request.getRequestDispatcher("/error.jsp");
                    view .forward(request, response);
                }                        
            }catch(SQLException e){
                log("Exception: ",e);
            }finally{
                try{
                    dbConnection.close();
                }catch(SQLException e){}
            }
        
        }
    }
    public void doGet(HttpServletRequest request,HttpServletResponse response)
             throws ServletException,IOException{
        doPost(request,response);
    }
}

5.在JSP中使用

<%@ page contentType="text/html; charset=GBK" language="java" errorPage="" %>
<%@ page import="javax.naming.*,java.sql.*,javax.sql.*" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
    "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
    <title>测试Tomcat局部数据源</title>
</head>
<body>
<%
//初始化Context，使用InitialContext初始化Context
Context ctx=new InitialContext(); 
/*
通过JNDI查找数据源，该JNDI为java:comp/env/jdbc/dstest，分成两个部分
java:comp/env是Tomcat固定的，Tomcat提供的JNDI绑定都必须加该前缀
jdbc/dstest是定义数据源时的数据源名
*/
DataSource ds=(DataSource)ctx.lookup("java:comp/env/jdbc/echarts");
//获取数据库连接
Connection conn=ds.getConnection();
//获取Statement
Statement stmt=conn.createStatement();
//执行查询，返回ResulteSet对象
ResultSet rs=stmt.executeQuery("select * from adminstore");
while(rs.next())
{
    out.println(rs.getString(1) 
        + "	" + rs.getString(2) + "<br/>");
}
%>
</body>
</html>