import java.sql.Connection;
import java.sql.SQLException;
import java.util.List;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.tomcat.jdbc.pool.DataSource;
import org.apache.tomcat.jdbc.pool.PoolProperties;
public class DBUtil {
private static DataSource ds;
private static QueryRunner runner;
static {
    PoolProperties p = new PoolProperties();
    p.setUrl("jdbc:mysql://localhost:3306/home");
    p.setDriverClassName("com.mysql.jdbc.Driver");
    p.setUsername("root");
    p.setPassword("sa");
    p.setJmxEnabled(true);
    p.setTestWhileIdle(false);
    p.setTestOnBorrow(true);
    p.setValidationQuery("SELECT 1");
    p.setTestOnReturn(false);
    p.setValidationInterval(30000);
    p.setTimeBetweenEvictionRunsMillis(30000);
    p.setMaxActive(100);
    p.setInitialSize(10);
    p.setMaxWait(10000);
    p.setRemoveAbandonedTimeout(60);
    p.setMinEvictableIdleTimeMillis(30000);
    p.setMinIdle(10);
    p.setLogAbandoned(true);
    p.setRemoveAbandoned(true);
    p.setJdbcInterceptors("org.apache.tomcat.jdbc.pool.interceptor.ConnectionState;"
            + "org.apache.tomcat.jdbc.pool.interceptor.StatementFinalizer");
    ds = new DataSource();
    ds.setPoolProperties(p);
    runner = new QueryRunner();
}

private static Connection getConnection() throws SQLException {
    return ds.getConnection();
}

/**
 * 删除
 * @param tableName
 * @param id
 * @return
 */
public static boolean delete(String tableName, long id) {
    String sql = "delete from " + tableName + " where id=?";
    int i = 0;
    boolean flag = false;
    Connection con = null;
    try {
        con = getConnection();
        i = runner.update(con, sql, id);
        if (i >= 0) {
            flag = true;
        }
    } catch (SQLException e) {
        System.out.println(e.getMessage());
    } finally {
        if (con != null) {
            try {
                con.close();
            } catch (SQLException e) {
                System.out.println(e.getMessage());
            }
        }
    }
    return flag;
}

/**
 * 修改/添加 
 * @param sql
 * @param pring
 * @return
 */
public static boolean update(String sql, Object pring[]) {
    int i = 0;
    boolean flag = false;
    Connection con = null;
    try {
        con = getConnection();
        i = runner.update(con, sql, pring);
        if (i >= 0) {
            flag = true;
        }
    } catch (SQLException e) {
        e.printStackTrace();
    } finally {
        if (con != null) {
            try {
                con.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
    return flag;
}

/**
 * 查询
 * @param sql
 * @param rsh
 * @return
 */
public static List<?> query(String sql, ResultSetHandler<?> rsh) {  
    List<?> result = null;  
    Connection con = null;
    try {  
        con = getConnection();
        result = (List<?>) runner.query(con, sql, rsh);
    } catch (SQLException e) {            
        e.printStackTrace();  
    } finally {
        if (con!=null) {
            try {
                con.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
    return result;  
}  

/**
 * 分页查询
 * @param sql
 * @param rsh
 * @param page
 * @param pageSize
 * @return
 */
public static List<?> query(String sql, ResultSetHandler<?> rsh, int page, int pageSize) {  
    List<?> result = null;  
    Connection con = null;
    try {  
        con = getConnection();
        result = (List<?>) runner.query(con, sql + " limit " + page*pageSize + "," + pageSize, rsh);
    } catch (SQLException e) {            
        e.printStackTrace();  
    } finally {
        if (con!=null) {
            try {
                con.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
    return result;  
} 

/**
 * 根据id查询
 * @param clasz
 * @param id
 * @return
 */
public static Object get(Class<?> clasz, int id) {
    Connection con = null;
    Object obj = null;
    try {  
        con = getConnection();
        @SuppressWarnings({ "unchecked", "rawtypes" })
        List<?>result = (List<?>) runner.query(con, 
                "select * from " + clasz.getSimpleName().toLowerCase() + " where id=" + id, 
                new BeanListHandler(clasz));
        if (result!=null && result.size()>0) {
            obj = result.get(0);
        }
    } catch (SQLException e) {            
        e.printStackTrace();  
    } finally {
        if (con!=null) {
            try {
                con.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
    return obj;         
     }
   }
Last modification:September 19th, 2019 at 08:51 am
如果觉得我的文章对你有用,请随意赞赏