Tomcat+mysql连接池配置

<?xml version="1.0" encoding="UTF-8"?>
<!--
  Licensed to the Apache Software Foundation (ASF) under one or more
  contributor license agreements.  See the NOTICE file distributed with
  this work for additional information regarding copyright ownership.
  The ASF licenses this file to You under the Apache License, Version 2.0
  (the "License"); you may not use this file except in compliance with
  the License.  You may obtain a copy of the License at

      http://www.apache.org/licenses/LICENSE-2.0

  Unless required by applicable law or agreed to in writing, software
  distributed under the License is distributed on an "AS IS" BASIS,
  WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
  See the License for the specific language governing permissions and
  limitations under the License.
--><!-- The contents of this file will be loaded for each web application --><Context>

    <!-- Default set of monitored resources -->
    <WatchedResource>WEB-INF/web.xml</WatchedResource>

    <!-- Uncomment this to disable session persistence across Tomcat restarts -->
    <!--
    <Manager pathname="" />
    -->

    <!-- Uncomment this to enable Comet connection tacking (provides events
         on session expiration as well as webapp lifecycle) -->
    <!--
    <Valve className="org.apache.catalina.valves.CometConnectionManagerValve" />
    -->
    <Resource name="jdbc/mysql" 
auth="Container" 
type="javax.sql.DataSource" 
maxActive="50" 
maxIdle="10" 
maxWait="5000" 
username="root" 
password="sql123" 
driverClassName="com.mysql.jdbc.Driver"
url="jdbc:mysql://localhost:3306/labtmp?useUnicode=true&amp;characterEncoding=UTF-8&amp;zeroDateTimeBehavior=convertToNull" />
</Context>

 

package com.my;

import java.io.InputStream;
import java.sql.*;
import java.util.ArrayList;
import javax.sql.*;
import javax.naming.*;

public class Mysql {
    private Connection conn = null;
    private PreparedStatement ps = null;
    private Statement stm = null;

    // constructor 1
    public Mysql() throws Exception {
             
        conn=lookForConnection();
        if(conn!=null)
           stm = conn.createStatement();
        else
           System.out.println("get DB Connection Failure!");
    }

    // constructor 2
    public Mysql(String sql) throws Exception {
        conn=lookForConnection();  
         if(conn!=null)
         {   
             this.prepareStatement(sql);
             stm = conn.createStatement();
         }
         else
             System.out.println("get DB Connection Failure!");
    }
    
    private Connection lookForConnection()throws Exception
    {
        DataSource ds = null; 
         try{ 
             InitialContext ctx=new InitialContext(); 
             ds=(DataSource)ctx.lookup("java:comp/env/jdbc/mysql"); 
             Connection con= ds.getConnection();  
             return con;
         }
        catch(Exception ex){
             ex.printStackTrace();
             return null;
        } 
    }
    public void prepareStatement(String sql) throws SQLException {
        ps = conn.prepareStatement(sql);
    }

    public void clearParameters() throws SQLException {
        ps.clearParameters();
    }

    // return the connection object
    public Connection getConnection() {
        return conn;
    }

    // return the preparedstatement
    public PreparedStatement getPreparedStatement() {
        return ps;
    }

    // return the set of query
    public ResultSet executeQuery(String sql) throws SQLException {
        if (stm != null) {
            return stm.executeQuery(sql);
        } else
            return null;
    }

    // return the set of query
    public ResultSet executeQuery() throws SQLException {
        if (ps != null) {
            return ps.executeQuery();
        } else
            return null;
    }

    // execute the operation of update
    public void executeUpdate(String sql) throws SQLException {
        if (stm != null)
            stm.executeUpdate(sql);
        else
           System.out.println("the Statement is null");
    }

    // execute the operation of update
    public void executeUpdate() throws SQLException {
        if (ps != null)
        {
          ps.executeUpdate();
        }
        else
            System.out.println("the prepareStatement is null");   
    }

    // close the connection and free resource
    public void closeCon() throws SQLException {
        if (stm != null) {
            stm.close();
            stm = null;
        }
        if (ps != null) {
            ps.close();
            ps = null;
        }
        conn.close();
        conn = null;
    }

    // lock the table
    public boolean lockTable(String table, String privilege)throws SQLException {
        String locksql = "lock tables " + table + " " + privilege;
            if(this.executeQuery(locksql)!=null)
            {
                // System.out.println("lock");
                return true;
            }
            else
            return false;
    }

    // unlock the table
    public boolean unLockTable() throws SQLException{
        String unlocksql = "unlock tables ";
        if(this.executeQuery(unlocksql)!=null)
        {
            // System.out.println("unlock");
            return true;
        }
        else
            return false;
    }

    // set the parameter
    public void setString(int index, String value) throws SQLException {
        ps.setString(index, value);
    }

    public void setInt(int index, int value) throws SQLException {
        ps.setInt(index, value);
    }

    public void setBoolean(int index, boolean value) throws SQLException {
        ps.setBoolean(index, value);
    }

    public void setDate(int index, Date value) throws SQLException {
        ps.setDate(index, value);
    }

    public void setLong(int index, long value) throws SQLException {
        ps.setLong(index, value);
    }

    public void setFloat(int index, float value) throws SQLException {
        ps.setFloat(index, value);
    }
    public void setBinaryStream(int index,InputStream in,int length) throws SQLException {
        ps.setBinaryStream(index,in,length);
        }
}

 

我的环境：

 Tomcat7.0.22，Mysql5.5，Windows xp Sp3

 最近需要在tomcat下配置mysql的连接池，在网上找了些资料参考了下，不同的版本tomcat配置介绍都略有不同，下面我介绍一下我配置的方法。

 1.首先，要安装JDK1.6,Tomcat,mysql等，这些就不在此多说了。

  值得注意的是，mysql表字符集设置,很有可能在客户端和JSP中乱码,可参考文章:

  http://c02949.blog.163.com/blog/static/485037200962345218147/ 

  我是在eclipse下布署运行Tomcat的，在eclipse的window-->preference--->Server里，右面的里面添加tomcat的路径，指定jdk,然后在下面的servers选项卡里新建一个服务器，这时候就会在eclipse的project explore中生成一个Servers项目

 

 

   2.在context.xml的<context></context>之间添加连接池如下:

 <Resource name="jdbc/mysql"

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

maxIdle="10"

maxWait="5000" 
username="你的mysql用户"

password="你的mysql密码" 

driverClassName="com.mysql.jdbc.Driver"
url="jdbc:mysql://localhost/dbname?useUnicode=true&amp;characterEncoding=UTF-8&amp;zeroDateTimeBehavior=convertToNull"/>

<WatchedResource>WEB-INF/web.xml </WatchedResource>  

XML里 & 要用&amp ;替代，不然会被过滤掉。

 

配置文件中Resource标签中各属性的含义：

driverClassName - JDBC 所用到的数据库驱动的类全名.

maxActive - 连接池在同一时刻内所提供的最大活动连接数。

maxIdle - 连接池在空闲时刻保持的最大连接数.

maxWait - 当发生异常时数据库等待的最大毫秒数 (当没有可用的连接时).

password - 连接数据库的密码.

url - 连接至驱动的URL. (为了向后兼容, DRIVERNAME也被允许.)

username - 数据库用户名.

各种配置方式的范围也应该是不一样的。我在这就不细说了，总之就是在Context标签下面配置个Resource标签即可。

 

我的配置：

最后，将mysql 的jdbc驱动复制到配置tomcat下的lib目录;

OK,到此tomcat就配置完了,接下来就是新建工程，测试:

3.打开Eclipse其手工建个WEB project(我的工程名AJAX),加入mysql JDBC驱动(我的这步没加入也可以正常运行);

配置WEB-INFO下面的web.xml文件：

<web-app></web-app>之间加入:
<resource-ref> 
       <description>DB Connection</description> 
       <res-ref-name>jdbc/mysql</res-ref-name> 
       <res-type>javax.sql.DataSource</res-type> 
       <res-auth>Container</res-auth> 
    </resource-ref>

 我的配置：

<?xml version="1.0" encoding="UTF-8"?>
<web-app xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://java.sun.com/xml/ns/javaee" xmlns:web="http://java.sun.com/xml/ns/javaee/web-app_2_5.xsd" xsi:schemaLocation="http://java.sun.com/xml/ns/javaee http://java.sun.com/xml/ns/javaee/web-app_3_0.xsd" id="WebApp_ID" version="3.0">
  <display-name>AJAX</display-name>
  <welcome-file-list>
    <welcome-file>index.html</welcome-file>
    <welcome-file>index.htm</welcome-file>
    <welcome-file>index.jsp</welcome-file>
    <welcome-file>default.html</welcome-file>
    <welcome-file>default.htm</welcome-file>
    <welcome-file>default.jsp</welcome-file>
  </welcome-file-list>
  <resource-ref> 
       <description>DB Connection</description> 
       <res-ref-name>jdbc/mysql</res-ref-name> 
       <res-type>javax.sql.DataSource</res-type> 
       <res-auth>Container</res-auth> 
    </resource-ref>
</web-app>

 

注意的地方: context.xml文件中的name="jdbc/mysql"要和web.xml中的<res-ref-name>jdbc/mysql</res-ref-name>要一致;

新建JSP文件：

<%@ page language="java" pageEncoding="UTF-8"%>
<%@ page import="java.sql.*"%> 
<%@ page import="javax.sql.*"%> 
<%@ page import="javax.naming.*"%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
   <head>
<title>mysql连接池测试</title>
<meta http-equiv="pragma" content="no-cache">
<meta http-equiv="cache-control" content="no-cache">
<meta http-equiv="expires" content="0"> 
<meta http-equiv="keywords" content="keyword1,keyword2,keyword3">
<meta http-equiv="description" content="This is my page">
   </head>
  
   <body> 
<%  
out.print("我的测试开始<br>"); 
DataSource ds = null; 
Connection conn=null;
       try{ 
InitialContext ctx=new InitialContext(); 
ds=(DataSource)ctx.lookup("java:comp/env/jdbc/mysql"); 
conn = ds.getConnection();  

    }catch(Exception ex){
     ex.printStackTrace();
    } 
%>  
<%   
    if(conn!=null){ 
%> 
     <%= conn %>;  
<% 
    } 
%> 
   </body>
</html>

 调试，运行，如果配置成功，则会在网页中输出以下内容：

这样就配置完成了。

下面附上我写的数据操作类（Mysql.java）：

 

package com.my;

import java.io.InputStream;
import java.sql.*;
import java.util.ArrayList;
import javax.sql.*;
import javax.naming.*;

public class Mysql {
    private Connection conn = null;
    private PreparedStatement ps = null;
    private Statement stm = null;

    // constructor 1
    public Mysql() throws Exception {
             
        conn=lookForConnection();
        if(conn!=null)
           stm = conn.createStatement();
        else
           System.out.println("get DB Connection Failure!");
    }

    // constructor 2
    public Mysql(String sql) throws Exception {
        conn=lookForConnection();  
         if(conn!=null)
         {   
             this.prepareStatement(sql);
             stm = conn.createStatement();
         }
         else
             System.out.println("get DB Connection Failure!");
    }
    
    private Connection lookForConnection()throws Exception
    {
        DataSource ds = null; 
         try{ 
             InitialContext ctx=new InitialContext(); 
             ds=(DataSource)ctx.lookup("java:comp/env/jdbc/mysql"); 
             Connection con= ds.getConnection();  
             return con;
         }
        catch(Exception ex){
             ex.printStackTrace();
             return null;
        } 
    }
    public void prepareStatement(String sql) throws SQLException {
        ps = conn.prepareStatement(sql);
    }

    public void clearParameters() throws SQLException {
        ps.clearParameters();
    }

    // return the connection object
    public Connection getConnection() {
        return conn;
    }

    // return the preparedstatement
    public PreparedStatement getPreparedStatement() {
        return ps;
    }

    // return the set of query
    public ResultSet executeQuery(String sql) throws SQLException {
        if (stm != null) {
            return stm.executeQuery(sql);
        } else
            return null;
    }

    // return the set of query
    public ResultSet executeQuery() throws SQLException {
        if (ps != null) {
            return ps.executeQuery();
        } else
            return null;
    }

    // execute the operation of update
    public void executeUpdate(String sql) throws SQLException {
        if (stm != null)
            stm.executeUpdate(sql);
        else
           System.out.println("the Statement is null");
    }

    // execute the operation of update
    public void executeUpdate() throws SQLException {
        if (ps != null)
        {
          ps.executeUpdate();
        }
        else
            System.out.println("the prepareStatement is null");   
    }

    // close the connection and free resource
    public void closeCon() throws SQLException {
        if (stm != null) {
            stm.close();
            stm = null;
        }
        if (ps != null) {
            ps.close();
            ps = null;
        }
        conn.close();
        conn = null;
    }

    // lock the table
    public boolean lockTable(String table, String privilege)throws SQLException {
        String locksql = "lock tables " + table + " " + privilege;
            if(this.executeQuery(locksql)!=null)
            {
                // System.out.println("lock");
                return true;
            }
            else
            return false;
    }

    // unlock the table
    public boolean unLockTable() throws SQLException{
        String unlocksql = "unlock tables ";
        if(this.executeQuery(unlocksql)!=null)
        {
            // System.out.println("unlock");
            return true;
        }
        else
            return false;
    }

    // set the parameter
    public void setString(int index, String value) throws SQLException {
        ps.setString(index, value);
    }

    public void setInt(int index, int value) throws SQLException {
        ps.setInt(index, value);
    }

    public void setBoolean(int index, boolean value) throws SQLException {
        ps.setBoolean(index, value);
    }

    public void setDate(int index, Date value) throws SQLException {
        ps.setDate(index, value);
    }

    public void setLong(int index, long value) throws SQLException {
        ps.setLong(index, value);
    }

    public void setFloat(int index, float value) throws SQLException {
        ps.setFloat(index, value);
    }
    public void setBinaryStream(int index,InputStream in,int length) throws SQLException {
        ps.setBinaryStream(index,in,length);
        }
}