关于dbutils

原文地址： http://www.showcoo.net/51.html 

Common Dbutils是操作数据库的组件，对传统操作数据库的类进行二次封装，可以把结果集转化成List。

补充一下，传统操作数据库的类指的是JDBC（java database connection：java数据库连接，java的数据库操作的基础API。）。

DBUtils是java编程中的数据库操作实用工具，小巧简单实用，

特色：

1.对于数据表的读操作，他可以把结果转换成List，Array，Set等java集合，便于程序员操作；

2.对于数据表的写操作，也变得很简单（只需写sql语句）

3.可以使用数据源，使用JNDI，数据库连接池等技术来优化性能–重用已经构建好的数据库连接对象，而不像php，asp那样，费时费力的不断重复的构建和析构这样的对象。

以上为dbutils的介绍部分,说了那么多的废话,以某个项目的使用来做DEMO

数据库连接类DBManager.java

DBManager

Java

 

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139

import java . sql . * ; import java . util . * ; import java . lang . reflect . * ; import javax . sql . DataSource ; import org . apache . commons . beanutils . BeanUtils ; import org . apache . commons . logging . Log ; import org . apache . commons . logging . LogFactory ; /** * 数据库管理 * */ public class DBManager {     private final static Log log = LogFactory . getLog ( DBManager . class ) ;     private final static ThreadLocal <Connection> conns = new ThreadLocal <Connection> ( ) ;     private static DataSource dataSource ;     private static boolean show_sql = false ;     static {         initDataSource ( null ) ;     }     /**      * 初始化连接池      *      * @param props      * @param show_sql      */     private final static void initDataSource ( Properties dbProperties ) {         try {             if ( dbProperties == null ) {                 dbProperties = new Properties ( ) ;                 dbProperties . load ( DBManager . class . getResourceAsStream ( "db.properties" ) ) ;             }             Properties cp_props = new Properties ( ) ;             for ( Object key : dbProperties . keySet ( ) ) {                 String skey = ( String ) key ;                 if ( skey . startsWith ( "jdbc." ) ) {                     String name = skey . substring ( 5 ) ;                     cp_props . put ( name , dbProperties . getProperty ( skey ) ) ;                     if ( "show_sql" . equalsIgnoreCase ( name ) ) {                         show_sql = "true" . equalsIgnoreCase ( dbProperties . getProperty ( skey ) ) ;                     }                 }             }             dataSource = ( DataSource ) Class . forName ( cp_props . getProperty ( "datasource" ) ) . newInstance ( ) ;             if ( dataSource . getClass ( ) . getName ( ) . indexOf ( "c3p0" ) > 0 ) {                 // Disable JMX in C3P0                 System . setProperty ( "com.mchange.v2.c3p0.management.ManagementCoordinator" , "com.mchange.v2.c3p0.management.NullManagementCoordinator" ) ;             }             log . info ( "Using DataSource : " + dataSource . getClass ( ) . getName ( ) ) ;             BeanUtils . populate ( dataSource , cp_props ) ;             Connection conn = getConnection ( ) ;             DatabaseMetaData mdm = conn . getMetaData ( ) ;             log . info ( "Connected to " + mdm . getDatabaseProductName ( ) + " " + mdm . getDatabaseProductVersion ( ) ) ;             closeConnection ( ) ;         } catch ( Exception e ) {             e . printStackTrace ( ) ;         }     }     /**      * 断开连接池      */     public final static void closeDataSource ( ) {         try {             dataSource . getClass ( ) . getMethod ( "close" ) . invoke ( dataSource ) ;         } catch ( NoSuchMethodException e ) {         } catch ( Exception e ) {             log . error ( "Unabled to destroy DataSource!!! " , e ) ;         }     }     public final static Connection getConnection ( ) throws SQLException {         Connection conn = conns . get ( ) ;         if ( conn == null || conn . isClosed ( ) ) {             conn = dataSource . getConnection ( ) ;             conns . set ( conn ) ;         }         return ( show_sql && ! Proxy . isProxyClass ( conn . getClass ( ) ) ) ? new _DebugConnection ( conn ) . getConnection ( ) : conn ;     }     /**      * 关闭连接      */     public final static void closeConnection ( ) {         Connection conn = conns . get ( ) ;         try {             if ( conn != null && ! conn . isClosed ( ) ) {                 conn . setAutoCommit ( true ) ;                 conn . close ( ) ;             }         } catch ( SQLException e ) {             log . error ( "Unabled to close connection!!! " , e ) ;         }         conns . set ( null ) ;     }     /**      * 用于跟踪执行的SQL语句      *      */     static class _DebugConnection implements InvocationHandler {         private final static Log log = LogFactory . getLog ( _DebugConnection . class ) ;         private Connection conn = null ;         public _DebugConnection ( Connection conn ) {             this . conn = conn ;         }         /**          * Returns the conn.          *          * @return Connection          */         public Connection getConnection ( ) {             return ( Connection ) Proxy . newProxyInstance ( conn . getClass ( ) . getClassLoader ( ) , conn . getClass ( ) . getInterfaces ( ) , this ) ;         }         public Object invoke ( Object proxy , Method m , Object [ ] args ) throws Throwable {             try {                 String method = m . getName ( ) ;                 if ( "prepareStatement" . equals ( method ) || "createStatement" . equals ( method ) )                     log . info ( "[SQL] >>> " + args [ 0 ] ) ;                 return m . invoke ( conn , args ) ;             } catch ( InvocationTargetException e ) {                 throw e . getTargetException ( ) ;             }         }     } }

数据库操作助手QueryHelper.java

Java

 

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250

import java . io . Serializable ; import java . math . BigInteger ; import java . sql . * ; import java . util . * ; import my . cache . CacheManager ; import org . apache . commons . dbutils . QueryRunner ; import org . apache . commons . dbutils . handlers . * ; import org . apache . commons . lang . ArrayUtils ; /** * 数据库查询助手 * */ @SuppressWarnings ( "unchecked" ) public class QueryHelper {     private final static QueryRunner _g_runner = new QueryRunner ( ) ;     private final static ColumnListHandler _g_columnListHandler = new ColumnListHandler ( ) {         @Override         protected Object handleRow ( ResultSet rs ) throws SQLException {             Object obj = super . handleRow ( rs ) ;             if ( obj instanceof BigInteger )                 return ( ( BigInteger ) obj ) . longValue ( ) ;             return obj ;         }     } ;     private final static ScalarHandler _g_scaleHandler = new ScalarHandler ( ) {         @Override         public Object handle ( ResultSet rs ) throws SQLException {             Object obj = super . handle ( rs ) ;             if ( obj instanceof BigInteger )                 return ( ( BigInteger ) obj ) . longValue ( ) ;             return obj ;         }     } ;     private final static List < Class <? >> PrimitiveClasses = new ArrayList < Class <? > > ( ) {         {             add ( Long . class ) ;             add ( Integer . class ) ;             add ( String . class ) ;             add ( java . util . Date . class ) ;             add ( java . sql . Date . class ) ;             add ( java . sql . Timestamp . class ) ;         }     } ;     private final static boolean _IsPrimitive ( Class < ? > cls ) {         return cls . isPrimitive ( ) || PrimitiveClasses . contains ( cls ) ;     }     /**      * 获取数据库连接      *      * @return      */     public static Connection getConnection ( ) {         try {             return DBManager . getConnection ( ) ;         } catch ( SQLException e ) {             e . printStackTrace ( ) ;         }         return null ;     }     /**      * 读取某个对象      *      * @param sql      * @param params      * @return      */     public static <T> T read ( Class <T> beanClass , String sql , Object . . . params ) {         try {             return ( T ) _g_runner . query ( getConnection ( ) , sql , _IsPrimitive ( beanClass ) ? _g_scaleHandler : new BeanHandler ( beanClass ) , params ) ;         } catch ( SQLException e ) {             e . printStackTrace ( ) ;         }         return null ;     }     public static <T> T read_cache ( Class <T> beanClass , String cache , Serializable key , String sql , Object . . . params ) {         T obj = ( T ) CacheManager . get ( cache , key ) ;         if ( obj == null ) {             obj = read ( beanClass , sql , params ) ;             CacheManager . set ( cache , key , ( Serializable ) obj ) ;         }         return obj ;     }     /**      * 对象查询      *      * @param <T>      * @param beanClass      * @param sql      * @param params      * @return      */     public static <T> List <T> query ( Class <T> beanClass , String sql , Object . . . params ) {         try {             return ( List <T> ) _g_runner . query ( getConnection ( ) , sql , _IsPrimitive ( beanClass ) ? _g_columnListHandler : new BeanListHandler ( beanClass ) , params ) ;         } catch ( SQLException e ) {             e . printStackTrace ( ) ;         }         return null ;     }     /**      * 支持缓存的对象查询      *      * @param <T>      * @param beanClass      * @param cache_region      * @param key      * @param sql      * @param params      * @return      */     public static <T> List <T> query_cache ( Class <T> beanClass , String cache_region , Serializable key , String sql , Object . . . params ) {         List <T> objs = ( List <T> ) CacheManager . get ( cache_region , key ) ;         if ( objs == null ) {             objs = query ( beanClass , sql , params ) ;             CacheManager . set ( cache_region , key , ( Serializable ) objs ) ;         }         return objs ;     }     /**      * 分页查询      *      * @param <T>      * @param beanClass      * @param sql      * @param page      * @param count      * @param params      * @return      */     public static <T> List <T> query_slice ( Class <T> beanClass , String sql , int page , int count , Object . . . params ) {         if ( page < 0 || count < 0 )             throw new IllegalArgumentException ( "Illegal parameter of 'page' or 'count', Must be positive." ) ;         int from = ( page - 1 ) * count ;         count = ( count > 0 ) ? count : Integer . MAX_VALUE ;         return query ( beanClass , sql + " LIMIT ?,?" , ArrayUtils . addAll ( params , new Integer [ ] { from , count } ) ) ;     }     /**      * 支持缓存的分页查询      *      * @param <T>      * @param beanClass      * @param cache      * @param cache_key      * @param cache_obj_count      * @param sql      * @param page      * @param count      * @param params      * @return      */     public static <T> List <T> query_slice_cache ( Class <T> beanClass , String cache , Serializable cache_key , int cache_obj_count , String sql , int page , int count , Object . . . params ) {         List <T> objs = ( List <T> ) CacheManager . get ( cache , cache_key ) ;         if ( objs == null ) {             objs = query_slice ( beanClass , sql , 1 , cache_obj_count , params ) ;             CacheManager . set ( cache , cache_key , ( Serializable ) objs ) ;         }         if ( objs == null || objs . size ( ) == 0 )             return objs ;         int from = ( page - 1 ) * count ;         if ( from < 0 )             return null ;         if ( ( from + count ) > cache_obj_count ) // 超出缓存的范围             return query_slice ( beanClass , sql , page , count , params ) ;         int end = Math . min ( from + count , objs . size ( ) ) ;         if ( from >= end )             return null ;         return objs . subList ( from , end ) ;     }     /**      * 执行统计查询语句，语句的执行结果必须只返回一个数值      *      * @param sql      * @param params      * @return      */     public static long stat ( String sql , Object . . . params ) {         try {             Number num = ( Number ) _g_runner . query ( getConnection ( ) , sql , _g_scaleHandler , params ) ;             return ( num != null ) ? num . longValue ( ) : - 1 ;         } catch ( SQLException e ) {             e . printStackTrace ( ) ;         }         return 0 ;     }     /**      * 执行统计查询语句，语句的执行结果必须只返回一个数值      *      * @param cache_region      * @param key      * @param sql      * @param params      * @return      */     public static long stat_cache ( String cache_region , Serializable key , String sql , Object . . . params ) {         Number value = ( Number ) CacheManager . get ( cache_region , key ) ;         if ( value == null ) {             value = stat ( sql , params ) ;             CacheManager . set ( cache_region , key , value ) ;         }         return value . longValue ( ) ;     }     /**      * 执行INSERT/UPDATE/DELETE语句      *      * @param sql      * @param params      * @return      */     public static int update ( String sql , Object . . . params ) {         try {             return _g_runner . update ( getConnection ( ) , sql , params ) ;         } catch ( SQLException e ) {             e . printStackTrace ( ) ;         }         return - 1 ;     }     /**      * 批量执行指定的SQL语句      *      * @param sql      * @param params      * @return      */     public static int [ ] batch ( String sql , Object [ ] [ ] params ) {         try {             return _g_runner . batch ( getConnection ( ) , sql , params ) ;         } catch ( SQLException e ) {             e . printStackTrace ( ) ;         }         return null ;     } }

对应的数据库连接资源文件:db.properties

 

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16

# DataSource jdbc . datasource = com . mchange . v2 . c3p0 . ComboPooledDataSource jdbc . show_sql = true # Database Configurations jdbc . driverClass = com . mysql . jdbc . Driver jdbc . jdbcUrl = jdbc : mysql : //localhost:3306/showcoo jdbc . user = root jdbc . password = root jdbc . maxPoolSize = 100 jdbc . minPoolSize = 2 jdbc . initialPoolSize = 2 jdbc . acquireIncrement = 2 jdbc . maxStatements = 1000 jdbc . maxIdleTime = 300 jdbc . checkoutTimeout = 5000

以上代码均可直接运行,对应用到的jar包这里就不再列出