自写JdbcUtil工具类&在Tomcat下的service设置实现其简化

JDBC连接数据库

JdbcUtil.java

package JdbcUtil;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class JdbcUtil {
	private static String CLASSNAME="com.mysql.jdbc.Driver";
	private static String URL="jdbc:mysql://localhost:3306/test";
	private static String USERNAME="root";
	private static String PASSWORD="root";
	private static Connection conn=null;
	private static PreparedStatement pstm=null;
	private static ResultSet rs=null;
	
	static {
		try {
			Class.forName(CLASSNAME);
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
	}
	
	public Connection getConnection() {
		try {
			conn=DriverManager.getConnection(URL, USERNAME, PASSWORD);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return conn;
	}
	
	public ResultSet Query(String sql,Object...param) {
		getConnection();
		try {
			pstm=conn.prepareStatement(sql);
			if(param!=null) {
				for(int i=0;i<param.length;i++) {
					pstm.setObject(i+1, param[i]);
				}			
			}
			rs=pstm.executeQuery();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return rs;
	}
	
	public int Update(String sql,Object...param) {
		getConnection();
		try {
			pstm=conn.prepareStatement(sql);
			if(param!=null) {
				for(int i=0;i<=param.length;i++) {
					pstm.setObject(i+1, param[i]);
				}
				int i=pstm.executeUpdate();
				return i;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return 0;
	}
	
	public void close() {
		try {
			if(conn!=null) {
				conn.close();
			}
			if(pstm!=null) {
				pstm.close();
			}
			if(rs!=null) {
				rs.close();
			}
		}catch(Exception e) {
			e.printStackTrace();
		}
	}
}

Tomcat安装后的service下的Context.xml中Context标签内加入：

<Resource name="jdbc/test" auth="Container" type="javax.sql.DataSource"
    maxActive="100" maxIdle="20" maxWait="30000" username="root" password="root"
    driverClassName="com.mysql.jdbc.Driver" url="jdbc:mysql://localhost:3306/test" 
    removeAbandoned="true" removeAbandonedTimeout="30"  />

简化后的JdbcUtil，"//"为被简化的代码

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.sql.DataSource;

public class JdbcUtil {
        //private static String CLASSNAME="com.mysql.jdbc.Driver";
        //private static String URL="jdbc:mysql://localhost:3306/test";
        //private static String USERNAME="root";
        //private static String PASSWORD="root";
        private Connection conn=null;
        private PreparedStatement pst=null;
        private ResultSet rs=null;
        /*static{
                try {
                        Class.forName(CLASSNAME);
                } catch (ClassNotFoundException e) {
                        e.printStackTrace();
                }
        }*/
        
        public Connection getConnection(){
                try {
                        //conn = DriverManager.getConnection(URL,USERNAME,PASSWORD);
                        Context context = new InitialContext();
                        DataSource ds = (DataSource)context.lookup("java:comp/env/jdbc/test");
                        conn = ds.getConnection();
                } catch (Exception e) {
                        e.printStackTrace();
                }
                return conn;
        }
        
        public ResultSet query(String sql,Object... param){
                getConnection();
                try {
                        pst = conn.prepareStatement(sql);
                        if(param!=null){
                                for(int i=0;i<param.length;i++){
                                        pst.setObject(i+1, param[i]);
                                }
                        }
                        rs = pst.executeQuery();
                        return rs;
                } catch (SQLException e) {
                        e.printStackTrace();
                }
                return null;
        }
        public int update(String sql,Object... param){
                getConnection();
                try {
                        pst = conn.prepareStatement(sql);
                        if(param!=null){
                                for(int i=0;i<param.length;i++){
                                        pst.setObject(i+1, param[i]);
                                }
                        }
                        int i = pst.executeUpdate();
                        return i;
                } catch (SQLException e) {
                        e.printStackTrace();
                }
                return 0;
        }
        
        public void close(){
                try{
                        if(conn!=null){
                                conn.close();
                        }
                        if(pst!=null){
                                pst.close();
                        }
                        if(rs!=null){
                                rs.close();
                        }
                }catch(Exception e){
                        e.printStackTrace();
                }
        }
}