关闭

DBUtil的简单封装(dbutil+tomcat jdbcpool)

617人阅读 评论(0) 收藏 举报
大家都知道oschina是使用dbutil操作数据库的,我试了一下dbutil,感觉非常的简单和高效。

标签: <无>

代码片段(1)

[代码] [Java]代码

001 package org.home.util;
002  
003 import java.sql.Connection;
004 import java.sql.SQLException;
005 import java.util.List;
006 import org.apache.commons.dbutils.QueryRunner;
007 import org.apache.commons.dbutils.ResultSetHandler;
008 import org.apache.commons.dbutils.handlers.BeanListHandler;
009 import org.apache.tomcat.jdbc.pool.DataSource;
010 import org.apache.tomcat.jdbc.pool.PoolProperties;
011  
012 public class DBUtil {
013     private static DataSource ds;
014     private static QueryRunner runner;
015     static {
016         PoolProperties p = new PoolProperties();
017         p.setUrl("jdbc:mysql://localhost:3306/home");
018         p.setDriverClassName("com.mysql.jdbc.Driver");
019         p.setUsername("root");
020         p.setPassword("sa");
021         p.setJmxEnabled(true);
022         p.setTestWhileIdle(false);
023         p.setTestOnBorrow(true);
024         p.setValidationQuery("SELECT 1");
025         p.setTestOnReturn(false);
026         p.setValidationInterval(30000);
027         p.setTimeBetweenEvictionRunsMillis(30000);
028         p.setMaxActive(100);
029         p.setInitialSize(10);
030         p.setMaxWait(10000);
031         p.setRemoveAbandonedTimeout(60);
032         p.setMinEvictableIdleTimeMillis(30000);
033         p.setMinIdle(10);
034         p.setLogAbandoned(true);
035         p.setRemoveAbandoned(true);
036         p.setJdbcInterceptors("org.apache.tomcat.jdbc.pool.interceptor.ConnectionState;"
037                 + "org.apache.tomcat.jdbc.pool.interceptor.StatementFinalizer");
038         ds = new DataSource();
039         ds.setPoolProperties(p);
040         runner = new QueryRunner();
041     }
042  
043     private static Connection getConnection() throws SQLException {
044         return ds.getConnection();
045     }
046  
047     /**
048      * 删除
049      * @param tableName
050      * @param id
051      * @return
052      */
053     public static boolean delete(String tableName, long id) {
054         String sql = "delete from " + tableName + " where id=?";
055         int i = 0;
056         boolean flag = false;
057         Connection con = null;
058         try {
059             con = getConnection();
060             i = runner.update(con, sql, id);
061             if (i >= 0) {
062                 flag = true;
063             }
064         } catch (SQLException e) {
065             System.out.println(e.getMessage());
066         } finally {
067             if (con != null) {
068                 try {
069                     con.close();
070                 } catch (SQLException e) {
071                     System.out.println(e.getMessage());
072                 }
073             }
074         }
075         return flag;
076     }
077  
078     /**
079      * 修改/添加
080      * @param sql
081      * @param pring
082      * @return
083      */
084     public static boolean update(String sql, Object pring[]) {
085         int i = 0;
086         boolean flag = false;
087         Connection con = null;
088         try {
089             con = getConnection();
090             i = runner.update(con, sql, pring);
091             if (i >= 0) {
092                 flag = true;
093             }
094         } catch (SQLException e) {
095             e.printStackTrace();
096         } finally {
097             if (con != null) {
098                 try {
099                     con.close();
100                 } catch (SQLException e) {
101                     e.printStackTrace();
102                 }
103             }
104         }
105         return flag;
106     }
107  
108     /**
109      * 查询
110      * @param sql
111      * @param rsh
112      * @return
113      */
114     public static List<?> query(String sql, ResultSetHandler<?> rsh) { 
115         List<?> result = null
116         Connection con = null;
117         try
118             con = getConnection();
119             result = (List<?>) runner.query(con, sql, rsh);
120         } catch (SQLException e) {           
121             e.printStackTrace(); 
122         } finally {
123             if (con!=null) {
124                 try {
125                     con.close();
126                 } catch (SQLException e) {
127                     e.printStackTrace();
128                 }
129             }
130         }
131         return result; 
132     
133      
134     /**
135      * 分页查询
136      * @param sql
137      * @param rsh
138      * @param page
139      * @param pageSize
140      * @return
141      */
142     public static List<?> query(String sql, ResultSetHandler<?> rsh, int page, int pageSize) { 
143         List<?> result = null
144         Connection con = null;
145         try
146             con = getConnection();
147             result = (List<?>) runner.query(con, sql + " limit " + page*pageSize + "," + pageSize, rsh);
148         } catch (SQLException e) {           
149             e.printStackTrace(); 
150         } finally {
151             if (con!=null) {
152                 try {
153                     con.close();
154                 } catch (SQLException e) {
155                     e.printStackTrace();
156                 }
157             }
158         }
159         return result; 
160     }
161      
162     /**
163      * 根据id查询
164      * @param clasz
165      * @param id
166      * @return
167      */
168     public static Object get(Class<?> clasz, int id) {
169         Connection con = null;
170         Object obj = null;
171         try
172             con = getConnection();
173             @SuppressWarnings({ "unchecked", "rawtypes" })
174             List<?>result = (List<?>) runner.query(con,
175                     "select * from " + clasz.getSimpleName().toLowerCase() + " where id=" + id,
176                     new BeanListHandler(clasz));
177             if (result!=null && result.size()>0) {
178                 obj = result.get(0);
179             }
180         } catch (SQLException e) {           
181             e.printStackTrace(); 
182         } finally {
183             if (con!=null) {
184                 try {
185                     con.close();
186                 } catch (SQLException e) {
187                     e.printStackTrace();
188                 }
189             }
190         }
191         return obj;        
192     }
193 }

0
0

查看评论
* 以上用户言论只代表其个人观点,不代表CSDN网站的观点或立场
    个人资料
    • 访问:361551次
    • 积分:31
    • 等级:
    • 排名:千里之外
    • 原创:42篇
    • 转载:164篇
    • 译文:0篇
    • 评论:119条
    最新评论