Tomcat+mysql连接池配置

阅读量：6194 次

发布时间：2019-06-21

本文共 11589 字，大约阅读时间需要 38 分钟。

View Code


      
      
      
          
           
       
        WEB-INF/web.xml

View Code

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&characterEncoding=UTF-8&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>

我的配置：

View Code


      
        
       
        AJAX
         
           
        
         index.html
            
        
         index.htm
            
        
         index.jsp
            
        
         default.html
            
        
         default.htm
            
        
         default.jsp
          
         
               
        
         DB Connection
                
        
         jdbc/mysql
                
        
         javax.sql.DataSource
                
        
         Container

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

新建JSP文件：

View Code

<%@ page language="java" pageEncoding="UTF-8"%><%@ page import="java.sql.*"%> <%@ page import="javax.sql.*"%> <%@ page import="javax.naming.*"%>   mysql连接池测试
      
      
       
      
               <%  out.print("我的测试开始
"); 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 %>;  <%     } %>

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

这样就配置完成了。

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

View Code

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

转载于:https://www.cnblogs.com/cxtczzy/archive/2013/03/25/Tomcat.html

你可能感兴趣的文章

如何通过WallpaperManager(壁纸管理器)设置与修改壁纸？

查看>>