随笔 - 41  文章 - 29  trackbacks - 0
<2009年3月>
22232425262728
1234567
891011121314
15161718192021
22232425262728
2930311234

常用链接

留言簿(5)

随笔分类(28)

随笔档案(23)

收藏夹(6)

Inside JVM

Java

java performance

Solr

搜索

  •  

最新评论

阅读排行榜

评论排行榜

JDBC has been used as the major Java Database Access technique for long time. However, JDBC access always need try-catch block and repeated JDBC codes, such as connection, statement and resultset.
And recently, JDK 6.0 already released a simplified JDBC access approach. This article will introduce a similar and very simple design to reduce most of JDBC codes

1. Typical JDBC Code Template

A typical JDBC code template is as following -
 1     public void example() {
 2 
 3         Connection connection = null;
 4         PreparedStatement cstmt = null;
 5         ResultSet resultSet = null;
 6 
 7         try {
 8             connection = AccessUtil.getInstance().getConnection();
 9             cstmt = connection.prepareStatement("SELECT * FROM VENDOR");
10             // cstmt.setString(1, getIdString(hotelIds, ","));
11             cstmt.executeQuery();
12             resultSet = cstmt.getResultSet();
13             // handle result set
14             while (resultSet.next()) {
15                  //get result one by one
16             }
17 
18         } catch (SQLException e) {
19             log.error(e.getMessage(), e);
20             throw new CommonSearchRuntimeExcetion(e);
21         } finally {
22             try {
23                 if (resultSet != null) {
24                     resultSet.close();
25                 }
26             } catch (Exception e) {
27                 /* swallow */
28             }
29 
30             try {
31                 if (cstmt != null) {
32                     cstmt.close();
33                 }
34             } catch (Exception e) {
35                 /* swallow */
36             }
37 
38             try {
39                 if (connection != null) {
40                     connection.close();
41                 }
42             } catch (Exception e) {
43                 /* swallow */
44             }
45         }
46     }
As you see in the above example, actually, we only need execute one query "SELECT * FROM VEDNOR", however, we used 46 lines of codes to execute this query and most of codes are exactly same among different access method. It caused big block mess codes.
We need remove them.

2. Use Annotation & Proxy to Remove most of repeated JDBC codes

Let's think about what are the necessary information to execute a query.
  1. the SQL to be executed
  2. the SQL parameters
  3. because of JDBC update and query need be handled separated, we also need know it is update or select operation
So, the design could be
  1. a Method annotation to get SQL statement and operation type (udpate including insert or select)
  2. an InvocationHandler (proxy) to execute the query behind the DAO interface

Method Annotation Implementation

 1 @Target(ElementType.METHOD)
 2 @Retention(RetentionPolicy.RUNTIME)
 3 public @interface DataAccessor {
 4 
 5     
 6     /**
 7      * query string
 8      * NOTE: the query string can be "SELECT * FROM VS_VendorMeetingRooms WHERE vendorUniqueID in (??)"
 9      * However, if it is collection value, need use "??" instead of "?"
10      */
11     String query() default "";
12     
13     /**
14      * Data operation type 
15      * @return
16      */
17     DataOperationType type() default DataOperationType.SELECT_OPERATION; 
18     
19 
20 }

The annotation is very easy to understand, however, here is just one thing. Because JDBC cannot directly set collection parameters, so, if we want to set an collection parameter, we have to do a little tricky thing. So, the SQL used "??" to represent a collection parameter. You can see the example on how to use it

Parameter Annotation Example 

1 @Target(ElementType.PARAMETER)
2 @Retention(RetentionPolicy.RUNTIME)
3 public @interface ParamIndex {
4     
5     int index();
6     
7 }

InvocationHandler Implementation Example

  1 public class DataAccessorInvocationHandler implements InvocationHandler {
  2 
  3     /**
  4      * Just sits here to hold onto the representation of the finder method so we
  5      * don't have to setup the context each time.
  6      */
  7     private static final Map<String, DataAccessorDescriptor> finderCache = new WeakHashMap<String, DataAccessorDescriptor>();
  8     
  9     private static final Log log = LogFactory.getLog(DataAccessorInvocationHandler.class);
 10 
 11 
 12     /**
 13      * This is the basic method interceptor that the proxy builds on.
 14      */
 15     public Object invoke(Object proxy, Method method, Object[] args) throws Throwable {
 16 
 17        
 18         DataAccessorDescriptor descriptor = null;
 19         RowSetDynaClass rowSetDynaClass = null;
 20         try {
 21             
 22             // obtain a cached finder descriptor (or create a new one)
 23             descriptor = getFinderDescriptor(method);
 24 
 25             createConnection(descriptor);
 26             
 27             //fill parameters 
 28             buildPreparedStatement(descriptor, args);
 29             
 30             //get the result
 31             rowSetDynaClass = createReturnResult(descriptor);
 32             
 33         } finally {
 34             
 35             if (descriptor != null) {
 36                 
 37                 ResultSet rs = descriptor.resultSet;
 38                 PreparedStatement pstmt = descriptor.statement;
 39                 Connection dbConn = descriptor.connection;
 40                 
 41                 try {
 42                     if (rs != null) {
 43                         rs.close();
 44                     }
 45                 } catch (Exception e) {
 46                     log.error(e.getMessage(), e);
 47                     throw new CommonSearchRuntimeException(e);
 48                 }
 49 
 50                 
 51                 try {
 52                     if (pstmt != null) {
 53                         pstmt.close();
 54                     }
 55                 } catch (Exception e) {
 56                     log.error(e.getMessage(), e);
 57                     throw new CommonSearchRuntimeException(e);
 58                 }
 59 
 60                 try {
 61                     if (dbConn != null) {
 62                         dbConn.close();
 63                     }
 64                 } catch (Exception e) {
 65                     log.error(e.getMessage(), e);
 66                     throw new CommonSearchRuntimeException(e);
 67                 }
 68             }
 69             
 70            
 71            
 72         }
 73         return rowSetDynaClass;
 74     }
 75 
 76     /**
 77      * Creates return result
 78      * @param pstmt
 79      * @param descriptor
 80      * @return
 81      * @throws SQLException
 82      * @throws IllegalAccessException 
 83      * @throws InstantiationException 
 84      */
 85     private RowSetDynaClass createReturnResult(DataAccessorDescriptor descriptor)
 86          throws SQLException, InstantiationException, IllegalAccessException {
 87 
 88         PreparedStatement statement = descriptor.statement;
 89 
 90         if (DataOperationType.SELECT_OPERATION.equals(descriptor.operationType)) {
 91             
 92             ResultSet rs = statement.executeQuery();
 93             
 94             RowSetDynaClass rowSetDynaClass = new RowSetDynaClass(rs);
 95 
 96             return rowSetDynaClass;
 97         } else if (DataOperationType.UPDATE_OPERATION.equals(descriptor.operationType)) {
 98             statement.executeUpdate();
 99             return null;
100         }
101         
102         return null;
103     }
104 
105     /**
106      * 
107      * @param descriptor
108      */
109     private void createConnection(DataAccessorDescriptor descriptor) {
110         descriptor.connection = AccessUtil.getInstance().getConnection();
111     }
112     
113     
114     /**
115      * This method will handle the binding of named parameters
116      * 
117      * @param hibernateQuery
118      * @param descriptor
119      * @param arguments
120      * @throws SQLException 
121      */
122     private void buildPreparedStatement(DataAccessorDescriptor descriptor, 
123             Object[] arguments) throws SQLException {
124         
125         class SQLParameter {
126             //0 = collection parameter, which means the "?" will be replaced by a string, 
127             //1 = normal JDBC parameter, use PreparedStatement.setObject to set parameters
128             int type; 
129             Object value;
130         };
131         
132         //ordered parameter map, the 
133         SQLParameter[] parameters = null;
134         if (arguments == null) {
135             parameters = new SQLParameter[0]; 
136         } else {
137             parameters = new SQLParameter[arguments.length]; 
138             for (int i = 0; i < arguments.length; i++) {
139                 Object argument = arguments[i];
140                 Object annotation = descriptor.parameterAnnotations[i];
141 
142                 if (null == annotation){
143                     continue// skip param as it's not bindable
144                 } else if (annotation instanceof ParamIndex) {
145                     
146                     ParamIndex param = (ParamIndex) annotation;
147                     SQLParameter parameter = new SQLParameter();
148                     //if collection, we have to build Query string by ourselves
149                     if (argument instanceof Collection) {
150                         Collection<?> collection = (Collection<?>)argument;
151                         parameter.type = 0;
152                         parameter.value = StringAppendUtil.buildStringSmartly(collection, ",");
153                     } else {
154                         parameter.type = 1;
155                         parameter.value = argument;
156                     }
157                     parameters[param.index() - 1= parameter;
158                 }
159             }
160         }
161         
162         //firstly, replace all collection parameters by string value
163         if (parameters.length > 0) {
164             for (int i = 0; i < parameters.length; i++) {
165                 SQLParameter parameter = parameters[i];
166                 if (parameter.type == 0) {
167                     descriptor.query = descriptor.query.replaceFirst("\\?\\?", (String)parameter.value);
168                 } 
169             }
170         }
171         //secondly, create statement
172         descriptor.statement = descriptor.connection.prepareStatement(descriptor.query);
173         //finally, fill parameters
174         if (parameters.length > 0) {
175             int index = 1;
176             for (int i = 0; i < parameters.length; i++) {
177                 SQLParameter parameter = parameters[i];
178                 if (parameter.type == 1) {
179                     descriptor.statement.setObject(index, parameter.value);
180                     index++;
181                 } 
182             }
183         }
184     }
185 
186     /**
187      * This is the method that goes ahead and looks at the method to create the
188      * descriptor of it.
189      * 
190      * @param method
191      * @return
192      */
193     private DataAccessorDescriptor getFinderDescriptor(Method method) {
194         
195         String messageKey = getMethodKey(method);
196         
197         DataAccessorDescriptor descriptor = finderCache.get(messageKey);
198         if (null != descriptor) {
199             return descriptor;
200         }
201         
202         // otherwise reflect and cache finder info
203         descriptor = new DataAccessorDescriptor();
204         finderCache.put(messageKey, descriptor);
205 
206         DataAccessor accessor = method.getAnnotation(DataAccessor.class);
207         String query = accessor.query();
208         if (!"".equals(query.trim())) {
209             descriptor.query = query;
210         }
211         
212         descriptor.operationType = accessor.type();
213         
214         // determine parameter annotations
215         Annotation[][] parameterAnnotations = method.getParameterAnnotations();
216         Object[] discoveredAnnotations = new Object[parameterAnnotations.length];
217         for (int i = 0; i < parameterAnnotations.length; i++) {
218             Annotation[] annotations = parameterAnnotations[i];
219             // each annotation per param
220             for (Annotation annotation : annotations) {
221                 Class<? extends Annotation> annotationType = annotation.annotationType();
222                 if (ParamIndex.class.equals(annotationType)) {
223                     discoveredAnnotations[i] = annotation;
224                     //finderDescriptor.isBindAsRawParameters = false;
225                     break;
226                 } 
227             }
228         }
229 
230         // set the discovered set to our finder cache object
231         descriptor.parameterAnnotations = discoveredAnnotations;
232 
233         return descriptor;
234     }
235     
236     /**
237      * Constructs a String key by method name and parameters 
238      * @param method
239      * @return
240      */
241     private String getMethodKey(Method method){
242         StringBuilder methodString = new StringBuilder();
243         methodString.append(method.getName());
244         for(Class<?> clazz: method.getParameterTypes()){
245             methodString.append(clazz.getName());
246         }
247         return methodString.toString();
248     }
249 
250     /**
251      * A simple class that is used in the cache for information about a finder
252      * method.
253      */
254     private static class DataAccessorDescriptor {
255         
256         Object[] parameterAnnotations;
257         
258         //the query string to be executed  
259         String query;
260         
261         DataOperationType operationType = DataOperationType.SELECT_OPERATION; 
262 
263         Connection connection = null;
264         
265         PreparedStatement statement = null;
266         
267         ResultSet resultSet = null;
268     }
269     
270 }

NOTE:
(1) for every JDBC call, we will always return RowSetDynaClass, which is one dynamic java bean implementation provided by Apache Common BeanUtils lib. In this way, we don't need any complicated O-R mapping tool.
(2) Collection parameters such as the parameters in "in(??)" and single parameter are handled seperatedly, you will see the example as following.

DatabaseAccessor Example

Now, the implementation of DatabaseAccssor will be very simple and straight-forward.
 
 1 public interface VendorAccessor {
 2         //SELECT example
 3     @DataAccessor(query =  "SELECT i.* FROM VS_Image i WHERE i.vendorID in (??)")
 4     public RowSetDynaClass getVendorImage(@ParamIndex(index = 1) List<Long> vendorIds);
 5 
 6         //UPDATE example
 7     @DataAccessor(query = "update VS_VendorSearch set changeFlag = ?",
 8             type = DataOperationType.UPDATE_OPERATION)
 9     public void updateChangeFlagAsTrueForVendorSearch(@ParamIndex(index = 1) String changeFlag);
10 }

DatabaseAccessorFactory Example


 1 public class DataAccessorFactory {
 2 
 3     private static final Map<Class<?>, Object> proxyCache = new HashMap<Class<?>, Object>();
 4     
 5     /**
 6      * Create proxy for accessor
 7      * @param accessor
 8      * @return
 9      */
10     public synchronized static Object getInstance(Class<?> accessor) {
11 
12         Object accessorProxy = null;
13         if ((accessorProxy = proxyCache.get(accessor)) == null) {
14             accessorProxy = Proxy.newProxyInstance(DataAccessorInvocationHandler.class.getClassLoader(), 
15                     new Class[] {accessor}, new DataAccessorInvocationHandler());
16             proxyCache.put(accessor, accessorProxy);
17         }
18 
19         return accessorProxy;
20     }
21     
22 }

Accessor Client Example

    private static final ExampleAccessor accessor = (ExampleAccessor)DataAccessorFactory.getInstance(ExampleAccessor.class);
    public List<Vendor> getVendors(Boolean needAll) {
        List
<Vendor> vendors = new ArrayList<Vendor>();
        
        RowSetDynaClass beans  
= accessor.getVendors();

        
for(Object o : beans.getRows()) {
            DynaBean bean  
= (DynaBean) o;
            Vendor v 
= new Vendor();
            v.setID((Long)getProperty(bean, 
"id"));
        
            
            purchasedVendors.add(v);
        }
        
        
return purchasedVendors;
    }

As you see, the client is very clean and straight-forward, there is no "try-catch, connection, statement, result set" etc any more.

3. Why we need this design

Let me summarize it again
  • for the DAO class (accessor), it is very clean, such as
 1 public interface VendorAccessor {
 
2         //SELECT example
 3     @DataAccessor(query =  "SELECT i.* FROM VS_Image i WHERE i.vendorID in (??)")
 
4     public RowSetDynaClass getVendorImage(@ParamIndex(index = 1) List<Long> vendorIds);
 
5 
 
6         //UPDATE example
 7     @DataAccessor(query = "update VS_VendorSearch set changeFlag = ?",
 
8             type = DataOperationType.UPDATE_OPERATION)
 
9     public void updateChangeFlagAsTrueForVendorSearch(@ParamIndex(index = 1) String changeFlag);
10 }
  • for the DAO class client, the usage on DAO class is also very clean and straight-forwar. such as 

 1     private static final ExampleAccessor accessor = (ExampleAccessor)DataAccessorFactory.getInstance(ExampleAccessor.class);
 2     public List<Vendor> getVendors(Boolean needAll) {
 3         List<Vendor> vendors = new ArrayList<Vendor>();
 4         
 5         RowSetDynaClass beans  = accessor.getVendors();
 6 
 7         for(Object o : beans.getRows()) {
 8             DynaBean bean  = (DynaBean) o;
 9             Vendor v = new Vendor();
10             v.setID((Long)getProperty(bean, "id"));
11         
12             
13             purchasedVendors.add(v);
14         }
15         
16         return purchasedVendors;
17     }
Any comment, please share with me .

posted on 2009-03-20 10:07 Justin Chen 阅读(1999) 评论(2)  编辑  收藏 所属分类: Database Access Layer & Persistence Layer

FeedBack:
# re: [绝对原创] Simplify Duplicated JDBC Code By Annotation - How can we remove Connection, Statement, and ResultSet Codes from DAO method 2009-03-20 19:37 super2
splendid !  回复  更多评论
  
# re: [绝对原创] Simplify Duplicated JDBC Code By Annotation - How can we remove Connection, Statement, and ResultSet Codes from DAO method 2009-03-23 11:15 545
mark  回复  更多评论
  

只有注册用户登录后才能发表评论。


网站导航: