KickJava   Java API By Example, From Geeks To Geeks.

Java > Open Source Codes > com > jdon > model > query > JdbcTemp


1 /*
2  * Copyright 2003-2006 the original author or authors.
3  * Licensed under the Apache License, Version 2.0 (the "License");
4  * you may not use this file except in compliance with the License.
5  * You may obtain a copy of the License at
6  *
7  * http://www.apache.org/licenses/LICENSE-2.0
8  *
9  * Unless required by applicable law or agreed to in writing, software
10  * distributed under the License is distributed on an "AS IS" BASIS,
11  * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
12  * See the License for the specific language governing permissions and
13  * limitations under the License.
14  *
15  */
16 package com.jdon.model.query;
17
18 import java.sql.Connection JavaDoc;
19 import java.sql.PreparedStatement JavaDoc;
20 import java.sql.ResultSet JavaDoc;
21 import java.sql.SQLException JavaDoc;
22 import java.util.ArrayList JavaDoc;
23 import java.util.Collection JavaDoc;
24 import java.util.List JavaDoc;
25
26 import javax.sql.DataSource JavaDoc;
27
28 import org.apache.log4j.Logger;
29
30 import com.jdon.util.DbUtil;
31
32 /**
33  * JDBC Template using this class, don't need write jdbc
34  * operations.
35  *
36  * @author <a HREF="mailto:banqiao@jdon.com">banq </a>
37  *
38  */
39 public class JdbcTemp {
40     private final static Logger logger = Logger.getLogger(JdbcTemp.class);
41
42     private DataSource JavaDoc dataSource;
43
44     private JdbcUtil jdbcUtil;
45
46     public JdbcTemp(DataSource JavaDoc dataSource) {
47         this.dataSource = dataSource;
48         this.jdbcUtil = new JdbcUtil();
49     }
50
51     /**
52      * get a single object from database
53      *
54      * fit for this sql: select name from user where id=?
55      *
56      * the "name" is single result
57      *
58      * @param queryParams
59      * @param sqlquery
60      * @return
61      * @throws Exception
62      */
63     public Object JavaDoc querySingleObject(Collection JavaDoc queryParams, String JavaDoc sqlquery)
64             throws Exception JavaDoc {
65         logger.debug("[JdonFramework]--> enter getSingleObject ");
66         Connection JavaDoc c = null;
67         PreparedStatement JavaDoc ps = null;
68         ResultSet JavaDoc rs = null;
69         Object JavaDoc o = null;
70         try {
71             c = dataSource.getConnection();
72             ps = c.prepareStatement(sqlquery,
73                     ResultSet.TYPE_SCROLL_INSENSITIVE,
74                     ResultSet.CONCUR_READ_ONLY);
75             logger.debug(sqlquery);
76             jdbcUtil.setQueryParams(queryParams, ps);
77
78             rs = ps.executeQuery();
79             if (rs.first()) {
80                 o = rs.getObject(1);
81                 logger.debug("[JdonFramework]-->in db found it:" + o.getClass().getName());
82             }
83         } catch (SQLException JavaDoc se) {
84             throw new Exception JavaDoc("SQLException: " + se.getMessage());
85         } catch (Exception JavaDoc ex) {
86             logger.error(ex);
87             throw new Exception JavaDoc(ex);
88         } finally {
89             if (rs != null)
90                 rs.close();
91             if (ps != null)
92                 ps.close();
93             if (c != null)
94                 c.close();
95         }
96
97         return o;
98     }
99         
100
101     /**
102      * this method is fit for this sql:
103      *
104      * select id, name, password from user where id = ?
105      *
106      * the " id, name, password " and its values are packed in a map of returen
107      * list.
108      *
109      *
110      * @param queryParams
111      * @param sqlquery
112      * @return
113      * @throws Exception
114      */
115     public List JavaDoc queryMultiObject(Collection JavaDoc queryParams, String JavaDoc sqlquery)
116             throws Exception JavaDoc {
117         logger.debug("[JdonFramework]--> enter queryMultiObject ");
118         Connection JavaDoc c = null;
119         PreparedStatement JavaDoc ps = null;
120         ResultSet JavaDoc rs = null;
121         List JavaDoc list = new ArrayList JavaDoc();
122         try {
123             c = dataSource.getConnection();
124             ps = c.prepareStatement(sqlquery,
125                     ResultSet.TYPE_SCROLL_INSENSITIVE,
126                     ResultSet.CONCUR_READ_ONLY);
127             logger.debug("[JdonFramework]" + sqlquery);
128             jdbcUtil.setQueryParams(queryParams, ps);
129
130             rs = ps.executeQuery();
131             list = jdbcUtil.extract(rs);
132         } catch (SQLException JavaDoc se) {
133             throw new Exception JavaDoc("SQLException: " + se.getMessage());
134         } catch (Exception JavaDoc ex) {
135             logger.error(ex);
136             throw new Exception JavaDoc(ex);
137         } finally {
138             if (rs != null)
139                 rs.close();
140             if (ps != null)
141                 ps.close();
142             if (c != null)
143                 c.close();
144         }
145         return list;
146     }
147
148     /**
149      * same as queryMultiObject(Collection queryParams, String sqlquery)
150      * but the result is a block result, the result'size is the value of count,
151      * and the result's start poiny is the value of start.
152      *
153      * @param queryParams
154      * @param sqlquery
155      * @param start
156      * @param count
157      * @return
158      * @throws Exception
159      */
160     public List JavaDoc queryMultiObject(Collection JavaDoc queryParams, String JavaDoc sqlquery, int start, int count)
161     throws Exception JavaDoc {
162         logger.debug("[JdonFramework]--> enter queryMultiObject from:" + start + " size:" + count);
163         Connection JavaDoc c = null;
164         PreparedStatement JavaDoc ps = null;
165         ResultSet JavaDoc rs = null;
166         List JavaDoc items = new ArrayList JavaDoc(count);
167         boolean hasNext = false;
168         try {
169             c = dataSource.getConnection();
170             DbUtil.testConnection(c);
171             ps = c.prepareStatement(sqlquery,
172                     ResultSet.TYPE_SCROLL_INSENSITIVE,
173                     ResultSet.CONCUR_READ_ONLY);
174             logger.debug(sqlquery);
175             jdbcUtil.setQueryParams(queryParams, ps);
176             rs = ps.executeQuery();
177             if (DbUtil.supportsFetchSize)
178                 rs.setFetchSize(count);
179             if (start >= 0 && rs.absolute(start + 1)) {
180                 do {
181                     items = jdbcUtil.extract(rs);
182                 } while ((hasNext = rs.next()) && (--count > 0));
183             }
184         } catch (SQLException JavaDoc se) {
185             throw new Exception JavaDoc("SQLException: " + se.getMessage());
186         } catch (Exception JavaDoc ex) {
187             logger.error(ex);
188             throw new Exception JavaDoc(ex);
189         } finally {
190             if (rs != null)
191                 rs.close();
192             if (ps != null)
193                 ps.close();
194             if (c != null)
195                 c.close();
196         }
197         return items;
198     }
199     
200     /**
201      * this method can used for insert/update/delete for database
202      *
203      * In insertParams the parameter type only supports: String Integer Float or Long Double Bye Short
204      * if you need operate other types, you must use JDBC directly replacing this method.
205
206      * @param insertParams the parameter that will be insert into sql.
207      * @param sql the standard sql sentence.
208      * @throws Exception
209      */
210     public void operate(Collection JavaDoc insertParams, String JavaDoc sql)
211             throws Exception JavaDoc {
212         logger.debug("[JdonFramework]--> enter getSingleObject ");
213         Connection JavaDoc c = null;
214         PreparedStatement JavaDoc ps = null;
215         ResultSet JavaDoc rs = null;
216         Object JavaDoc o = null;
217         try {
218             c = dataSource.getConnection();
219             ps = c.prepareStatement(sql);
220             logger.debug(sql);
221             jdbcUtil.setQueryParams(insertParams, ps);
222
223             ps.executeUpdate();
224         } catch (SQLException JavaDoc se) {
225             throw new Exception JavaDoc("SQLException: " + se.getMessage());
226         } catch (Exception JavaDoc ex) {
227             logger.error(ex);
228             throw new Exception JavaDoc(ex);
229         } finally {
230             if (rs != null)
231                 rs.close();
232             if (ps != null)
233                 ps.close();
234             if (c != null)
235                 c.close();
236         }
237     }
238
239 }
240
Popular Tags