luz2/src/com/lavans/luz2/sql/dao/CommonDao.java

   1 package com.lavans.luz2.sql.dao;\r
   2 \r
   3 import java.lang.reflect.InvocationTargetException;\r
   4 import java.lang.reflect.Method;\r
   5 import java.sql.ResultSet;\r
   6 import java.sql.ResultSetMetaData;\r
   7 import java.sql.SQLException;\r
   8 import java.util.ArrayList;\r
   9 import java.util.LinkedHashMap;\r
  10 import java.util.List;\r
  11 import java.util.Map;\r
  12 \r
  13 import org.apache.commons.logging.Log;\r
  14 import org.apache.commons.logging.LogFactory;\r
  15 \r
  16 import com.lavans.luz2.commons.ClassUtils;\r
  17 import com.lavans.luz2.commons.StringUtils;\r
  18 import com.lavans.luz2.sql.DBManager;\r
  19 import com.lavans.luz2.sql.bind.BindConnection;\r
  20 import com.lavans.luz2.sql.bind.BindPreparedStatement;\r
  21 import com.lavans.luz2.util.PageInfo;\r
  22 import com.lavans.luz2.util.Pager;\r
  23 \r
  24 /**\r
  25  *\r
  26  * @author dobashi\r
  27  *\r
  28  * @param <T>\r
  29  */\r
  30 public class CommonDao{\r
  31         /** logger */\r
  32         private static Log logger = LogFactory.getLog(CommonDao.class);\r
  33         /** default connection name */\r
  34         private static final String DEFALUT_CONNECTION = "default";\r
  35         //, seq;\r
  36         /**\r
  37          * Constructor.\r
  38          */\r
  39         public  CommonDao(){\r
  40         }\r
  41 \r
  42         /**\r
  43          * Executes the given SQL statement, which returns ResultSet object. This method convert ResultSet to List<Map<String, Object>>\r
  44          *\r
  45          * @return converted data.\r
  46          */\r
  47         public List<Map<String, Object>> executeQuery(String sql) throws SQLException {\r
  48                 return executeQuery(sql, null, DEFALUT_CONNECTION);\r
  49         }\r
  50         public List<Map<String, Object>> executeQuery(String sql, Map<String, Object> params) throws SQLException {\r
  51                 return executeQuery(sql, params, DEFALUT_CONNECTION);\r
  52         }\r
  53         public List<Map<String, Object>> executeQuery(String sql, Map<String, Object> params, String connectionName) throws SQLException {\r
  54                 logger.debug(sql);\r
  55                 List<Map<String, Object>> result = new ArrayList<Map<String,Object>>();\r
  56                 BindConnection con = null;\r
  57                 BindPreparedStatement st = null;\r
  58                 try {\r
  59                         con = DBManager.getConnection(connectionName);\r
  60                         st = con.bindPrepareStatement(sql);\r
  61                         st.setParams(params);\r
  62                         // execute SQL.\r
  63                         ResultSet rs = st.executeQuery();\r
  64                         result = rsToMapList(rs);\r
  65                         logger.debug("result count = "+ result.size());\r
  66                 }catch (SQLException e) {\r
  67                         // SQLException needs rethrow.\r
  68                         throw e;\r
  69                 } finally {\r
  70                         try { st.close(); } catch (Exception e) {}\r
  71                         try { con.close(); } catch (Exception e) {}\r
  72                 }\r
  73                 return result;\r
  74         }\r
  75 \r
  76         /**\r
  77          * ResultSetからList<Map<String, Object>>に変換\r
  78          * @param rs\r
  79          * @return\r
  80          * @throws SQLException\r
  81          */\r
  82         private List<Map<String, Object>> rsToMapList(ResultSet rs) throws SQLException{\r
  83                 List<Map<String, Object>> result = new ArrayList<Map<String,Object>>();\r
  84                 while (rs.next()) {\r
  85                         Map<String, Object> record = new LinkedHashMap<String, Object>();\r
  86                         ResultSetMetaData metaData = rs.getMetaData();\r
  87                         for(int i=1; i<metaData.getColumnCount()+1; i++){\r
  88                                 record.put(metaData.getColumnName(i), rs.getObject(i));\r
  89                         }\r
  90                         result.add(record);\r
  91                 }\r
  92                 return result;\r
  93         }\r
  94 \r
  95         /**\r
  96          *  Executes the given SQL statement, which returns effective rows(INSERT/DELETE/UPDATE) or returns nothing(DDL);\r
  97          */\r
  98         public int executeUpdate(String sql, Map<String, Object> params) throws SQLException {\r
  99                 return executeUpdate(sql, params, DEFALUT_CONNECTION);\r
 100         }\r
 101         public int executeUpdate(String sql, Map<String, Object> params, String connectionName) throws SQLException {\r
 102                 logger.debug(sql);\r
 103                 int result = -1;\r
 104                 BindConnection con = null;\r
 105                 BindPreparedStatement st = null;\r
 106                 try {\r
 107                         con = DBManager.getConnection(connectionName);\r
 108                         st = con.bindPrepareStatement(sql);\r
 109                         st.setParams(params);\r
 110                         // execute SQL.\r
 111                         result = st.executeUpdate();\r
 112                 }catch (SQLException e) {\r
 113                         // SQLException needs rethrow.\r
 114                         throw e;\r
 115                 } finally {\r
 116                         try { st.close(); } catch (Exception e) {}\r
 117                         try { con.close(); } catch (Exception e) {}\r
 118                 }\r
 119                 return result;\r
 120         }\r
 121 \r
 122         /**\r
 123          *\r
 124          *\r
 125          * @return\r
 126          * @throws SQLException\r
 127          * @throws InvocationTargetException\r
 128          * @throws NoSuchMethodException\r
 129          * @throws IllegalAccessException\r
 130          * @throws InstantiationException\r
 131          */\r
 132         public <T> List<T> list(Class<T> clazz, String sql, Map<String, Object> params) throws SQLException {\r
 133                 return list(clazz, sql, params, DEFALUT_CONNECTION);\r
 134         }\r
 135         public <T> List<T> list(Class<T> clazz, String sql, Map<String, Object> params, String connectionName) throws SQLException {\r
 136                 if(StringUtils.isEmpty(sql)){\r
 137                         throw new SQLException("sql is empty["+ sql +"]");\r
 138                 }\r
 139 \r
 140                 // execute sql\r
 141                 List<Map<String, Object>> list = executeQuery(sql, params);\r
 142 \r
 143                 // make entity instance from result data.\r
 144                 List<T> resultList = new ArrayList<T>();\r
 145                 for(Map<String, Object> dataMap: list){\r
 146                         resultList.add(mapToEntity(dataMap, clazz));\r
 147                 }\r
 148                 return resultList;\r
 149         }\r
 150 \r
 151         /**\r
 152          * list for pager.\r
 153          * You have to insert ":offset" and ":limit" like this:\r
 154          * "SELECT * FROM MEMBER OFFSET :offset LIMIT :limit".\r
 155          *\r
 156          * @param <T>\r
 157          * @param countSql      SQL string for count all.\r
 158          * @param pageInfo\r
 159          * @param sql           SQL string for select.\r
 160          * @param params\r
 161          * @return\r
 162          * @throws SQLException\r
 163          * @throws InvocationTargetException\r
 164          * @throws NoSuchMethodException\r
 165          * @throws IllegalAccessException\r
 166          * @throws InstantiationException\r
 167          */\r
 168         public <T> Pager<T> list(Class<T> clazz, String countSql, PageInfo pageInfo, String sql, Map<String, Object> params) throws SQLException{\r
 169                 return list(clazz, countSql, pageInfo, sql, params, DEFALUT_CONNECTION);\r
 170         }\r
 171         public <T> Pager<T> list(Class<T> clazz, String countSql, PageInfo pageInfo, String sql, Map<String, Object> params, String connectionName) throws SQLException {\r
 172                 // execute count sql\r
 173                 List<Map<String, Object>> list = executeQuery(sql);\r
 174                 int count = (Integer)list.get(0).values().toArray()[0];\r
 175 \r
 176                 // make select sql\r
 177                 int start = pageInfo.getPage() * pageInfo.getRows();\r
 178                 sql.replace(":offset", String.valueOf(start));\r
 179                 sql.replace(":limit",  String.valueOf(pageInfo.getRows()));\r
 180                 // これでもいける？\r
 181 //              params.put(":offset", start);\r
 182 //              params.put(":limit", pageInfo.getRows());\r
 183 \r
 184                 // execute sql\r
 185                 list = executeQuery(sql, params);\r
 186 \r
 187                 // make entity instance from result data.\r
 188                 Pager<T> pager = new Pager<T>(pageInfo);\r
 189                 pager.setTotalCount(count);\r
 190                 for(Map<String, Object> dataMap: list){\r
 191                         pager.add(mapToEntity(dataMap, clazz));\r
 192                 }\r
 193                 return pager;\r
 194         }\r
 195 \r
 196         /**\r
 197          * SQLから呼び出した汎用Map<String, Object>からEntityに変換\r
 198          * try to set all columns to entity with java.lang.reflection\r
 199          *\r
 200          * @param <T>\r
 201          * @param record\r
 202          * @param clazz\r
 203          * @return\r
 204          * @throws InstantiationException\r
 205          * @throws IllegalAccessException\r
 206          * @throws NoSuchMethodException\r
 207          * @throws InvocationTargetException\r
 208          */\r
 209         private <T> T mapToEntity(Map<String, Object> record, Class<T> clazz) throws SQLException{\r
 210                 T entity;\r
 211                 try {\r
 212                         entity = clazz.newInstance();\r
 213                         // for each map member, set value to entity\r
 214                         for(Map.Entry<String, Object> column: record.entrySet()){\r
 215                                 // "MEMBER_ID_SEQ" -> "member_id_seq" -> "memberIdSeq"(Camel) -> "MemberIdSeq"(Capitalize) -> "setMemberIdSeq"(add "set")\r
 216                                 String setterName = "set"+StringUtils.capitalize(StringUtils.toCamelCase(column.getKey().toLowerCase()));\r
 217                                 Class<?> valueClass = column.getValue().getClass();\r
 218 \r
 219 //                              logger.debug(setterName +"("+valueClass.getSimpleName() +")");\r
 220 \r
 221                                 // プリミティブなら引数の方を変更する\r
 222                                 Class<?> primitiveType = ClassUtils.wrapperToPrimitive(valueClass);\r
 223                                 if(primitiveType!=null){\r
 224                                         valueClass = primitiveType;\r
 225                                 }\r
 226 \r
 227                                 // SQLTimestamp型はjava.util.Date型で処理する\r
 228                                 if(valueClass.getName().equals(java.sql.Timestamp.class.getName())){\r
 229                                         valueClass = java.util.Date.class;\r
 230                                 }\r
 231 \r
 232                                 // セッターの実行\r
 233                                 Method setterMethod = clazz.getMethod(setterName, valueClass);\r
 234                                 setterMethod.invoke(entity, column.getValue());\r
 235                         }\r
 236                 } catch (Exception e) {\r
 237                         throw new SQLException(e);\r
 238                 }\r
 239                 return entity;\r
 240         }\r
 241 }\r