jdbc pool java连接池技术

ConnectPool .java:

  package pool;

/**
 * Title: ConnectPool.Java
 * Description: 连接池治理器
 * Copyright: Copyright (c) 2002/12/25
 * Company:
 * Author :
 * Version 2.0
 */

import java.io.*;
import java.sql.*;
import java.util.*;
import java.util.Date;

/**
 * 治理类DBConnectionManager支持对一个或多个由属性文件定义的数据库连接
 * 池的访问.客户程序可以调用getInstance()方法访问本类的唯一实例.
 */
public class ConnectPool {
    static public ConnectPool instance; // 唯一实例
    static public int clients;
    public Vector drivers = new Vector(); //驱动
    public PrintWriter log;
    public Hashtable pools = new Hashtable(); //连接

    /**
     * 返回唯一实例.假如是第一次调用此方法,则创建实例
     *
     * @return DBConnectionManager 唯一实例
     */
    static synchronized public ConnectPool getInstance() {
        if (instance == null) {
            instance = new ConnectPool();
        }

        clients++;
        return instance;
    }

    /**
     * 返回唯一实例.假如是第一次调用此方法,则创建实例
     *
     * @return DBConnectionManager 唯一实例
     */
    static synchronized public ConnectPool getInstance(String url) {
        if (instance == null) {
            instance = new ConnectPool(url);
        }
        clients++;
        return instance;
    }


    /**
     * 建构函数私有以防止其它对象创建本类实例
     */
    public ConnectPool() {
        init();
    }

    /**
     * 建构造带参数的函数
     *
     */
    public ConnectPool(String url) {
        init(url);
    }

    /**
     * 将连接对象返回给由名字指定的连接池
     *
     * @param name 在属性文件中定义的连接池名字
     * @param con 连接对象
     */
    public void freeConnection(String name, Connection con) {
        DBConnectionPool pool = (DBConnectionPool) pools.get(name);
        if (pool != null) {
            pool.freeConnection(con);
        } else {
            // System.out.println("pool ==null");
        }
        clients--;
    }

    /**
     * 获得一个可用的(空闲的)连接.假如没有可用连接,且已有连接数小于最大连接数
     * 限制,则创建并返回新连接
     *
     * @param name 在属性文件中定义的连接池名字
     * @return Connection 可用连接或null
     */
    public Connection getConnection(String name) {
        DBConnectionPool pool = (DBConnectionPool) pools.get(name);
        if (pool != null) {
            //return pool.getConnection();
            return pool.returnConnection();
        }
        return null;
    }

    /**
     * 获得一个可用连接.若没有可用连接,且已有连接数小于最大连接数限制,
     * 则创建并返回新连接.否则,在指定的时间内等待其它线程释放连接.
     *
     * @param name 连接池名字
     * @param time 以毫秒计的等待时间
     * @return Connection 可用连接或null
     */
    public Connection getConnection(String name, long time) {
        DBConnectionPool pool = (DBConnectionPool) pools.get(name);
        if (pool != null) {
            return pool.getConnection(time);
        }
        return null;
    }

    /**
     * 关闭所有连接,撤销驱动程序的注册
     */
    public synchronized void release() {
// 等待直到最后一个客户程序调用
        if (--clients != 0) {
            return;
        }

        Enumeration allPools = pools.elements();
        while (allPools.hasMoreElements()) {
            DBConnectionPool pool = (DBConnectionPool) allPools.nextElement();
            pool.release();
        }
        Enumeration allDrivers = drivers.elements();
        while (allDrivers.hasMoreElements()) {
            Driver driver = (Driver) allDrivers.nextElement();
            try {
                DriverManager.deregisterDriver(driver);

                log("撤销JDBC驱动程序 " + driver.getClass().getName() + "的注册");
            } catch (SQLException e) {
                log(e, "无法撤销下列JDBC驱动程序的注册: " + driver.getClass().getName());
            }
        }
    }

    /**
     * 根据指定属性创建连接池实例.
     *
     * @param props 连接池属性
     */
    private void createPools(Properties props) {
        Enumeration propNames = props.propertyNames();
        while (propNames.hasMoreElements()) {
            String name = (String) propNames.nextElement();
            if (name.endsWith(".url")) {
                String poolName = name.substring(0, name.lastIndexOf("."));
                String url = props.getProperty(poolName + ".url");
                if (url == null) {
                    log("没有为连接池" + poolName + "指定URL");
                    continue;
                }
                String user = props.getProperty(poolName + ".user");
                String passWord = props.getProperty(poolName + ".password");
                String maxconn = props.getProperty(poolName + ".maxconn", "0");
                int max;
                try {
                    max = Integer.valueOf(maxconn).intValue();
                } catch (NumberFormatException e) {
                    log("错误的最大连接数限制: " + maxconn + " .连接池: " + poolName);
                    max = 0;
                }
                DBConnectionPool pool = new DBConnectionPool(poolName, url,
                        user, passWord, max);
                pools.put(poolName, pool);
                log("成功创建连接池" + poolName);
            }
        }
    }

    /**
     * 读取属性完成初始化
     */
    private void init() {
        try {
            Properties p = new Properties();
            String configs = System.getProperty("user.dir") + "//conf//poolName.properties";
            //String configs = System.getProperty("user.dir") + "//conf//poolName.properties";

            //System.out.println("configs file local at " + configs);
            FileInputStream is = new FileInputStream(configs);
            Properties dbProps = new Properties();
            try {
                dbProps.load(is);
            } catch (Exception e) {
                System.err.println("不能读取属性文件. " +
                                   "请确保db.properties在CLASSPATH指定的路径中");
                return;
            }
            String logFile = dbProps.getProperty("logfile",
                                                 "DBConnectionManager.log");
            try {

                log = new PrintWriter(new FileWriter(logFile, true), true);
            } catch (IOException e) {
                System.err.println("无法打开日志文件: " + logFile);
                log = new PrintWriter(System.err);
            }
            loadDrivers(dbProps);
            createPools(dbProps);
        } catch (Exception e) {}
    }

    /**
     * 读取属性完成初始化
     */
    private void init(String url) {
        try {
            Properties p = new Properties();
            String configs = System.getProperty("user.dir") +
                             "//conf//"+url;
            //System.out.println("configs file local at " + configs);
            FileInputStream is = new FileInputStream(configs);
            Properties dbProps = new Properties();
            try {
                dbProps.load(is);
            } catch (Exception e) {
                System.err.println("不能读取属性文件. " +
                                   "请确保db.properties在CLASSPATH指定的路径中");
                return;
            }
            String logFile = dbProps.getProperty("logfile",
                                                 "DBConnectionManager.log");
            try {

                log = new PrintWriter(new FileWriter(logFile, true), true);
            } catch (IOException e) {
                System.err.println("无法打开日志文件: " + logFile);
                log = new PrintWriter(System.err);
            }
            loadDrivers(dbProps);
            createPools(dbProps);
        } catch (Exception e) {}
    }


    /**
     171 * 装载和注册所有JDBC驱动程序
     172 *
     173 * @param props 属性
     174 */
    private void loadDrivers(Properties props) {
        String driverClasses = props.getProperty("drivers");
        StringTokenizer st = new StringTokenizer(driverClasses);
        while (st.hasMoreElements()) {
            String driverClassName = st.nextToken().trim();
            try {
                Driver driver = (Driver)
                                Class.forName(driverClassName).newInstance();
                DriverManager.registerDriver(driver);
                drivers.addElement(driver);
                //System.out.println(driverClassName);
                log("成功注册JDBC驱动程序" + driverClassName);
            } catch (Exception e) {
                log("无法注册JDBC驱动程序: " +
                    driverClassName + ", 错误: " + e);
            }
        }
    }

    /**
     * 将文本信息写入日志文件
     */
    private void log(String msg) {
        log.println(new Date() + ": " + msg);
    }

    /**
     * 将文本信息与异常写入日志文件
     */
    private void log(Throwable e, String msg) {
        log.println(new Date() + ": " + msg);
        e.printStackTrace(log);
    }

    /**
     * 此内部类定义了一个连接池.它能够根据要求创建新连接,直到预定的最
     * 大连接数为止.在返回连接给客户程序之前,它能够验证连接的有效性.
     */

    class DBConnectionPool {
//private int checkedOut;
        private Vector freeConnections = new Vector();
        private int maxConn;
        private String name;
        private String password;
        private String URL;
        private String user;

        /**
         * 创建新的连接池
         *
         * @param name 连接池名字
         * @param URL 数据库的JDBC URL
         * @param user 数据库帐号,或 null
         * @param password 密码,或 null
         * @param maxConn 此连接池答应建立的最大连接数
         */
        public DBConnectionPool(String name, String URL, String user,
                                String password, int maxConn) {
            this.name = name;
            this.URL = URL;
            this.user = user;
            this.password = password;
            this.maxConn = maxConn;
        }

        /**
         * 将不再使用的连接返回给连接池
         *
         * @param con 客户程序释放的连接
         */
        public synchronized void freeConnection(Connection con) {
// 将指定连接加入到向量末尾
            try {
                if (con.isClosed()) {
                    //  System.out.println("before freeConnection con is closed");
                }
                freeConnections.addElement(con);
                Connection contest = (Connection) freeConnections.lastElement();
                if (contest.isClosed()) {
                    // System.out.println("after freeConnection contest is closed");
                }
                notifyAll();
            } catch (SQLException e) {
                // System.out.println(e);
            }
        }

        /**
         * 从连接池获得一个可用连接.如没有空闲的连接且当前连接数小于最大连接
         * 数限制,则创建新连接.如原来登记为可用的连接不再有效,则从向量删除之,
         * 然后递归调用自己以尝试新的可用连接.
         */
        public synchronized Connection getConnection() {
            Connection con = null;
            if (freeConnections.size() > 0) {
// 获取向量中第一个可用连接
                con = (Connection) freeConnections.firstElement();
                freeConnections.removeElementAt(0);
                try {
                    if (con.isClosed()) {
                        log("从连接池" + name + "删除一个无效连接");
                        System.out.println("从连接池" + name + "删除一个无效连接");
// 递归调用自己,尝试再次获取可用连接
                        con = getConnection();
                    }
                } catch (SQLException e) {
                    log("从连接池" + name + "删除一个无效连接时错误");
                    System.out.println("从连接池" + name + "删除一个无效连接出错");
// 递归调用自己,尝试再次获取可用连接
                    con = getConnection();
                }
                if (freeConnections.size() > maxConn) {
                    System.out.println(" 删除一个溢出连接 ");
                    releaseOne();
                }
            } else if ((maxConn == 0) || (freeConnections.size() < maxConn)) {
                con = newConnection();
            }

            return con;
        }

        public synchronized Connection returnConnection() {
            Connection con = null;
//假如闲置小于最大连接,返回一个新连接
            if (freeConnections.size() < maxConn) {
                con = newConnection();
            }
//假如闲置大于最大连接，返回一个可用的旧连接
            else if (freeConnections.size() >= maxConn) {

                con = (Connection) freeConnections.firstElement();
                System.out.println(" [a 连接池可用连接数 ] : " + "[ " +
                                   freeConnections.size() + " ]");
                freeConnections.removeElementAt(0);
                System.out.println(" [b 连接池可用连接数 ] : " + "[ " +
                                   freeConnections.size() + " ]");
                try {
                    if (con.isClosed()) {
                        log("从连接池" + name + "删除一个无效连接");
                        System.out.println("从连接池" + name + "删除一个无效连接");
                        returnConnection();
                    }
                } catch (SQLException e) {
                    log("从连接池" + name + "删除一个无效连接时错误");
                    System.out.println("从连接池" + name + "删除一个无效连接出错");
                    returnConnection();
                }
            }
            return con;
        }

        /**
         * 从连接池获取可用连接.可以指定客户程序能够等待的最长时间
         * 参见前一个getConnection()方法.
         *
         * @param timeout 以毫秒计的等待时间限制
         */
        public synchronized Connection getConnection(long timeout) {
            long startTime = new Date().getTime();
            Connection con;
            while ((con = getConnection()) == null) {
                try {
                    wait(timeout);
                } catch (InterruptedException e) {}
                if ((new Date().getTime() - startTime) >= timeout) {
// wait()返回的原因是超时
                    return null;
                }
            }
            return con;
        }

        /**
         * 关闭所有连接
         */
        public synchronized void release() {
            Enumeration allConnections = freeConnections.elements();
            while (allConnections.hasMoreElements()) {
                Connection con = (Connection) allConnections.nextElement();
                try {
                    con.close();
                    log("关闭连接池" + name + "中的一个连接");
                } catch (SQLException e) {
                    log(e, "无法关闭连接池" + name + "中的连接");
                }
            }
            freeConnections.removeAllElements();
        }

        /**
         * 关闭一个连接
         */
        public synchronized void releaseOne() {
            if (freeConnections.firstElement() != null) {
                Connection con = (Connection) freeConnections.firstElement();
                try {
                    con.close();
                    System.out.println("关闭连接池" + name + "中的一个连接");
                    log("关闭连接池" + name + "中的一个连接");
                } catch (SQLException e) {

                    System.out.println("无法关闭连接池" + name + "中的一个连接");
                    log(e, "无法关闭连接池" + name + "中的连接");
                }
            } else {
                System.out.println(
                        "releaseOne() bug.......................................................");

            }
        }

        /**
         * 创建新的连接
         */
        private Connection newConnection() {
            Connection con = null;
            try {
                if (user == null) {
                    con = DriverManager.getConnection(URL);
                } else {
                    con = DriverManager.getConnection(URL, user, password);
                }
                log("连接池" + name + "创建一个新的连接");

            } catch (SQLException e) {
                log(e, "无法创建下列URL的连接: " + URL);
                return null;
            }
            return con;
        }
    }
}

-----------------------------------------------------------------------------------------------------------------

PoolMan.java:

package pool;

/**
 * Title: ConnectPool.java
 * Description: 数据库操作
 * Copyright: Copyright (c) 2002/12/25
 * Company:
 * Author :
 * remark : 加入指针回滚
 * Version 2.0
 */

import java.io.*;
import pool.*;
import java.sql.*;
import java.util.*;
import java.util.Date;
import java.net.*;

public class PoolMan extends ConnectPool {

    private ConnectPool connMgr;
    private Statement stmt;
    private Connection con;
    private ResultSet rst;

    /**
     *对象连接初始化
     * */

    public Connection getPool(String name) throws Exception {
        try {
            connMgr = ConnectPool.getInstance();
            con = connMgr.getConnection(name);
        } catch (Exception e) {
            System.err.println("不能创建连接!请尝试重启应用服务器");

        }
        return con;
    }

    /**
     * 2008-10-14
     *对象连接初始化
     * */

    public Connection getPool(String name,String url) throws Exception {
        try {
            connMgr = ConnectPool.getInstance(url);
            con = connMgr.getConnection(name);
        } catch (Exception e) {
            System.err.println("不能创建连接!请尝试重启应用服务器");

        }
        return con;
    }


    /**
     *同以上方法,加入连接空闲等待时间
     *待用方法
     * */

    public Connection getPool_t(String name, long time) throws Exception {
        try {
            connMgr = ConnectPool.getInstance();
            con = connMgr.getConnection(name, time);
        } catch (Exception e) {
            System.err.println("不能创建连接!");

        }
        return con;
    }

    /**
     *执行查询方法1
     * */
    public ResultSet executeQuery(String SqlStr) throws Exception {
        ResultSet result = null;
        try {
            stmt = con.createStatement();
            result = stmt.executeQuery(SqlStr);
// here add one line by jnma 12.11
            con.commit();
        } catch (java.sql.SQLException e) {
            throw new Exception("执行查询语句出错");
        }
        return result;
    }

    /**
     *执行查询方法2
     * */
    public ResultSet getRst(String SqlStr) throws Exception {
// ResultSet result = null;
        try {
            stmt = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
                                       ResultSet.CONCUR_UPDATABLE);
            rst = stmt.executeQuery(SqlStr);
// here add one line by jnma 12.11
            con.commit();
        } catch (java.sql.SQLException e) {
            throw new Exception("执行查询语句出错");
        }
        return rst;
    }

    /**
     *执行更新
     * */
    public int Update(String SqlStr) throws Exception {
        int result = -1;
        try {
            stmt = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
                                       ResultSet.CONCUR_UPDATABLE);
            result = stmt.executeUpdate(SqlStr);
// here add one line by jnma 12.11
            con.commit();
            if (result == 0) {
                System.out.println("执行delete,update,insert SQL出错");
            }
        } catch (java.sql.SQLException e) {
            System.err.println("执行delete,update,insert SQL出错");
        }
        return result;
    }

    /**
     *执行事务处理
     * */
    public boolean handleTransaction(Vector SqlArray) throws Exception {
        boolean result = false;
        int ArraySize = SqlArray.size();
        try {
            stmt = con.createStatement();
            con.setAutoCommit(false);
            System.out.println("ArraySize is" + ArraySize);
            for (int i = 0; i < ArraySize; i++) {
                System.out.println(" 开始执行语句" + (String) SqlArray.elementAt(i));
                stmt.executeUpdate((String) SqlArray.elementAt(i));
                System.out.println(" 执行成功");
            }
            con.commit();
            con.setAutoCommit(true); //必须
            System.out.println("事务执行成功");
            result = true;
        } catch (java.sql.SQLException e) {
            try {
                System.out.println(e.toString());
                System.out.println("数据库操作失败");
                con.rollback();
            } catch (java.sql.SQLException Te) {
                System.err.println("事务出错回滚异常");
            }
        }
        try {
            con.setAutoCommit(true);
        } catch (java.sql.SQLException e) {
            System.err.println("设置自动提交失败");
        }
        return result;
    }

    /**
     *释放连接
     * */
    public void close(String name) throws Exception {
        try {
            if (stmt != null) {
                stmt.close();
            }
            if (con != null) {
                connMgr.freeConnection(name, con);

                System.out.println(" [c 正在释放一个连接 ] ");

            }
        } catch (java.sql.SQLException e) {
            System.err.println("释放连接出错");
        }
    }

}

-----------------------------------------------------------------------------------------------------------

MyDB.java

 

package db;

import java.sql.*;
import pool.PoolMan;
import base.Constants;
import java.util.List;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import org.apache.commons.beanutils.DynaBean;
import org.apache.commons.beanutils.RowSetDynaClass;
import java.util.Vector;
import bean.Log;
import bean.AdminBean;
import base.FunctionStatic;
import forms.system.DeptForm;
import org.apache.commons.beanutils.BeanUtils;

/*
//数据层续承此类包
例如：public class Function extends MyDB {。。。。。。
注意：当调用数据层都必需关闭数据库
Function fun=new Function()
fun.Close
*/
public class MyDB {
    public Connection conn = null;
    public ResultSet rs = null;
    public ResultSet rs1 = null;
    public java.sql.PreparedStatement prepar = null;
    public Statement stmt = null;
    public boolean flag = false;
    public java.sql.CallableStatement proc = null;
    public int pagecount = 0;
    public int pagedata = 0;
    PoolMan PoolMan = new PoolMan();
    public MyDB() {

        //通过名称得到连接池
        conn = PoolMan.getConnection(Constants.SYSTEM_DB_POOL_NAME);
       //System.out.println("连接成功");
    }

    //关闭连接
    public void Close() {
        try {
            if (rs != null) {
                rs.close();
            }
            if (rs1 != null) {
                rs1.close();
            }
            if (prepar != null) {
                prepar.close();
            }
            if (proc != null) {
                proc.close();
            }
            if (stmt != null) {
                stmt.close();
            }
            if (conn != null) {
                conn.close();
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
//        System.out.println("关闭成功");
    }

    public int executeSql(String sql)throws DbAccessException{
        int flag = 0;
        try {
            stmt = conn.createStatement();
            flag = stmt.executeUpdate(sql);
           // return flag;
        } catch (Exception e) {
            throw new DbAccessException("操作数据库出错");
        }finally{
            //return flag;
        }
        return flag;
    }

    //结果集的数据转存入List
    public List setResultToList(ResultSet rs) {
        List list = new ArrayList();
        try {
            ResultSetMetaData md = rs.getMetaData();

            /*获取结果集的列数*/
            int columnCount = md.getColumnCount();
            while (rs.next()) {
                HashMap map = new HashMap();
                /*把每一行以(key,value)存入HashMap, 列名做为key,列值做为value */
                for (int i = 1; i <= columnCount; ++i) {
                    String tempStr = rs.getString(i);
                    if (tempStr == null) {
                        tempStr = "";
                    }
                    map.put(md.getColumnName(i), tempStr);
                }
                /*把装有一行数据的HashMap存入list*/
                list.add(map);
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        //System.out.println(list.size());
        return list;
    }

    /*
     结果集的数据转存入List  3hsjc

     selectsql:查询的字段
     wheresql:查询条件的组合语句
     sortsql：排序的组合语句
     PageSize：每页显示的条数
     PageNum：当前条数
     tableName：表名
     keyName：表中的主键字段名

     页面调查方法：
     <%@page import="java.util.*"%>

     List list = (List) request.getAttribute("list"); //得到类包里存的值
     if (list!=null){
       for (int i = 0; i < list.size(); i++) {
          HashMap map = (HashMap) list.get(i);
          System.out.println(map.get("id"));    //字段名同库字段名
       }
     }

     */
    public List getListByMap(String selectsql, String wheresql, String sortsql,
                          int PageSize, int PageNum, String tableName,
                          String keyName) { //获取 列表信息
        List list = new ArrayList();

        String sql = "";
        try {
            if ("".equals(selectsql)) {
                selectsql = "*";
            }
            if ("".equals(tableName) || "".equals(keyName)) {
                return null;
            }
            sql = "SELECT top " + PageSize + " " + selectsql + "  FROM " +
                  tableName + " WHERE 1=1 " + wheresql +
                  " and " + keyName + " not in (select top " +
                  PageSize * (PageNum - 1) + " " + keyName + " from " +
                  tableName + " where 1=1 " + wheresql + sortsql + ")" +
                  sortsql + "";

            System.out.println("sql = " + sql );
            stmt = conn.createStatement();
            rs = stmt.executeQuery(sql);

            list = this.setResultToList(rs);
            return list;
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        return null;
    }


    /*
     结果集的数据转存入List  3hzyh

     selectsql:查询的字段
     wheresql:查询条件的组合语句
     sortsql：排序的组合语句
     PageSize：每页显示的条数
     PageNum：当前条数
     tableName：表名
     keyName：表中的主键字段名

     页面调查方法：
     <%@page import="org.apache.struts.util.RequestUtils"%>
     <%@page import="org.apache.commons.beanutils.*"%>
     <%@page import="java.util.*"%>

     Vector list = (Vector) request.getAttribute("list"); //得到类包里存的值
     if (list!=null){
       for (int i = 0; i < list.size(); i++) {
          DynaBean info = (DynaBean) list.get(i);
          System.out.println(BeanUtils.getSimpleProperty(info,"id"));    //字段名全小写
       }
     }

     */
    public Vector getList(String selectsql, String wheresql, String sortsql,
                          int PageSize, int PageNum, String tableName,
                          String keyName) { //获取 列表信息
        Vector vector = new Vector();

        String sql = "";
        try {
            if ("".equals(selectsql)) {
                selectsql = "*";
            }
            if ("".equals(tableName) || "".equals(keyName)) {
                return null;
            }
            sql = "SELECT top " + PageSize + " " + selectsql + "  FROM " +
                  tableName + " WHERE 1=1 " + wheresql +
                  " and " + keyName + " not in (select top " +
                  PageSize * (PageNum - 1) + " " + keyName + " from " +
                  tableName + " where 1=1 " + wheresql + sortsql + ")" +
                  sortsql + "";

            System.out.println(sql);
            stmt = conn.createStatement();
            rs = stmt.executeQuery(sql);

            /*对查询结果集RS进行行集的转换*/
            RowSetDynaClass rsdc = new RowSetDynaClass(rs);
            /*把行集的每一行另存为LIST，以便存取操作*/
            List rsDynaClass = rsdc.getRows();

            Iterator itr = rsDynaClass.iterator();

            int i = 0;
            while (itr.hasNext()) {
                i++;
                /*对LIST的每一行数据，动态生成虚拟BEAN*/
                DynaBean dBean = (DynaBean) itr.next();
                vector.add(dBean);

            }
            return vector;
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        return null;
    }

    //======================================================================
    //Created by linjunna 2009-08-13
    //增加用户操作日志
    /*
    ActionCode:
    1:登录系统
    2:......
    */
    public boolean InsertLog(Log log) {
        String sql = "INSERT INTO SU_UserLog(userName,note,userAction,ActionCode,dateCreated) VALUES (?,?,?,?,getdate() )";
        int flag=0;
        try {
            prepar = conn.prepareStatement(sql);
            prepar.setString(1, log.getUserName());
            prepar.setString(2, log.getNote());
            prepar.setString(3, log.getUserAction());
            prepar.setString(4, log.getActionCode());
            flag = prepar.executeUpdate();
            if (flag>1) return true;
        } catch (Exception ex) {
        }
        return false;
    }

    /*
    函数说明：获取表中某一字段的值
    参数说明你个：field:要获取字段的名称；keyName:判断的条件；keyValue:判断的条件的值；tableName:数据库表名
    返回值说明：String
     */
    public String getFieldValue(String field,String keyName,String keyValue,String tableName) {
        String result = "";
        FunctionStatic fun = new FunctionStatic();
        try {
            String sql = " select "+field+" from "+tableName+" where "+keyName+" ='" + keyValue + "' ";

            prepar = conn.prepareStatement(sql);
            rs = prepar.executeQuery();
            while (rs.next()){
                result = fun.getNullString(rs.getString(field));
                break;
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return result;
    }


    /*
    函数描述：修改表的某一字段
    */
    public int updateTableField(String TableName, String FieldName,String FieldValue, String PKID) {
        int ret = 0;
        String sql = "update ? set ? = ? where ID=?";
        try {
            prepar = conn.prepareStatement(sql);
            prepar.setString(1, TableName);
            prepar.setString(2, FieldName);
            prepar.setString(3, FieldValue);
            prepar.setString(4, PKID);

            ret = prepar.executeUpdate(sql);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return ret;
    }

    /*
      函数说明：批量删除
      参数说明：FieldName:表的字段名；FieldValues：字段值,以逗号分隔，形式如："h0001,h0002,h0003,..."
      返回值：整数
      */
      public int BatchDeleteByIds( String TableName, String FieldName,String FieldValues)
      {
          String[] strs=base.FunctionStatic.splitSkins(FieldValues,",");
          int flag = strs.length;
          try {

              conn.setAutoCommit(false);
              for (int i=0;i<strs.length;i++)
              {
                 flag-= Delete(TableName,FieldName,strs[i]);
              }
              conn.commit();
          } catch (Exception e) {
              try {
                  conn.rollback();
              } catch (SQLException ex) {
                  ex.printStackTrace();
              }
              e.printStackTrace();
          }
          if (flag==0)
              return 1;
          else
              return 0;

      }

      /*
      功能描述：根据表的主键id删除记录
      参数：TableName为要删除记录的表名；tids为一个或多个主键id字符串，以逗号（,）隔开；AutoCommit为是否使用事务
      author：曾令启
      */
      public int Delete(String TableName, String WhereSQL,boolean AutoCommit) {
          int flag = 0;
          try {
              String sql = "";
              conn.setAutoCommit(AutoCommit);
              sql = " delete from " + TableName + " where 1=1 " + WhereSQL + " ";
              flag = executeSql(sql);
              conn.commit();
          } catch (Exception e) {
              try {
                  conn.rollback();
              } catch (SQLException ex) {
                  ex.printStackTrace();
              }
              e.printStackTrace();
          }
          return flag;
      }

      /**
     * 获得用于数据交换的主键ID，使用的存储过程为生成通用主键ID的PR_MakeMax_TYPK
     * @param @Bureau_id String 用户登录时选择的机构ID
     * @param @lab_Id String 用户登录时选择的实验室ID
     * @param @TableName String 表名
     * @param @IDNewFormatOut String 输出的ID
     * @return String 可以返回以下格式的ID："Bureau_id+lab_id+0000000001"
     */
    public String getExchangePKID(String Bureau_id,String lab_Id,String TableName) {
        String sql ="set nocount on  begin declare @NewID varchar(50)"
                  +" set @NewID=NULL"
                  +" exec PR_MakeMax_TYPK N'', N'"+Bureau_id+"', N'"+lab_Id+"',N'"+TableName+"',@NewID out "
                  +" select @NewID end ";
        String result ="";
        try{
            prepar = conn.prepareStatement(sql);
            rs = prepar.executeQuery();
            if (rs.next()) {
                result = rs.getString(1);
            }
        }
        catch(SQLException e){
            e.printStackTrace();
        }
        return result;
    }

    public String getExchangePKID(AdminBean ab,String TableName) {
    String result ="";
    result=getExchangePKID(ab.getLoginBureaus(),ab.getLoginLab(),TableName);
    return result;
}

 

 

 

 

 

 

      //====================私有方法 start=============================================================================

      /*
           功能描述：根据表的主键id删除记录
           参数：TableName为要删除记录的表名；FieldName为数据表字段名；FieldValue为数据库表字段值
           返回值：int。成功返回1，失败返回0
       */
      private int Delete(String TableName, String FieldName, String FieldValue) {
          int flag = 0;
          String where = " and " + FieldName + " ='" + FieldValue + "'";
          flag = Delete(TableName, where);
          return flag;
      }


      //用于批量删除
      private int Delete(String TableName, String WhereSQL) {
         int flag = 0;
         try {
             String sql = "";
             sql = " delete from " + TableName + " where 1=1 " + WhereSQL + " ";
             flag = executeSql(sql);

         } catch (Exception e) {
             e.printStackTrace();
         }
         return flag;
   }

   //====================私有方法 end=============================================================================

   public String getListToJason(String sql) throws Exception {
       StringBuffer jasonStr= new StringBuffer();
       stmt = conn.createStatement();
       rs = stmt.executeQuery(sql);
       ResultSetMetaData rsmd = rs.getMetaData();
       RowSetDynaClass rsdc = new RowSetDynaClass(rs);
       List list = rsdc.getRows();
       Iterator ite = list.iterator();
       int resultCout = list.size();
       jasonStr.append("{totalCount:"+String.valueOf(resultCout)).append(",data:[");
       while(ite.hasNext()){
           DynaBean dyna = (DynaBean)ite.next();
           jasonStr.append("{");
           for(int i=1;i<=rsmd.getColumnCount();i++){
               if (i==rsmd.getColumnCount())
                   jasonStr.append( rsmd.getColumnName(i)).append(":/"").append(BeanUtils.getSimpleProperty(dyna,rsmd.getColumnName(i).toLowerCase())).append("/"");
               else
                   jasonStr.append( rsmd.getColumnName(i)).append(":/"").append(BeanUtils.getSimpleProperty(dyna,rsmd.getColumnName(i).toLowerCase())).append("/",");
           }
           jasonStr.append("},");
       }
       String te = jasonStr.toString();
       te = te.substring(0,te.length()-1)+"],success:true}";
       return te;
   }

   public String getListToJason(String sql,int start,int limit) throws Exception {
        StringBuffer jasonStr= new StringBuffer();
        stmt = conn.createStatement();
        rs = stmt.executeQuery(sql);
        ResultSetMetaData rsmd = rs.getMetaData();
        RowSetDynaClass rsdc = new RowSetDynaClass(rs);
        List list = rsdc.getRows();
        int resultCout = list.size();
        list = list.subList(start,start+limit);
        Iterator ite = list.iterator();
        jasonStr.append("{totalCount:"+String.valueOf(resultCout)).append(",data:[");
        while(ite.hasNext()){
            DynaBean dyna = (DynaBean)ite.next();
            jasonStr.append("{");
            for(int i=1;i<=rsmd.getColumnCount();i++){
                if (i==rsmd.getColumnCount())
                    jasonStr.append( rsmd.getColumnName(i)).append(":/"").append(BeanUtils.getSimpleProperty(dyna,rsmd.getColumnName(i).toLowerCase())).append("/"");
                else
                    jasonStr.append( rsmd.getColumnName(i)).append(":/"").append(BeanUtils.getSimpleProperty(dyna,rsmd.getColumnName(i).toLowerCase())).append("/",");
            }
            jasonStr.append("},");
        }
        String te = jasonStr.toString();
        te = te.substring(0,te.length()-1)+"],success:true}";
        return te;
    }

}

-----------------------------------------------------------------------------------------------------------------------------

connect.properties

drivers=net.sourceforge.jtds.jdbc.Driver
#drivers=com.microsoft.jdbc.sqlserver.SQLServerDriver
logfile=D://DBConnectPool-logfile.txt

poolName.maxconn=100
poolName.url=jdbc:jtds:sqlserver://localhost:1433;DatabaseName=my_DB;tds=8.0;lastupdatecount=true
#poolName.url=jdbc:jtds:sqlserver://localhost:1433;DatabaseName=my_db;tds=8.0;lastupdatecount=true
poolName.user=sa
poolName.password=