src/test/com/lavans/luz2/dao/old/SqlDao.java

   1 package test.com.lavans.luz2.dao.old;\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.StringUtils;\r
  17 import com.lavans.luz2.sql.DBManager;\r
  18 import com.lavans.luz2.sql.bind.BindConnection;\r
  19 import com.lavans.luz2.sql.bind.BindPreparedStatement;\r
  20 import com.lavans.luz2.util.Config;\r
  21 import com.lavans.luz2.util.PageInfo;\r
  22 import com.lavans.luz2.util.Pager;\r
  23 \r
  24 /**\r
  25  * SQL実行用汎用Daoクラス\r
  26  * 接続先ごとにインスタンスを持つが、connectionNameを使い分けているだけで\r
  27  * all staticでも可能\r
  28  *\r
  29  * @author dobashi\r
  30  *\r
  31  * @param <T>\r
  32  */\r
  33 public class SqlDao{\r
  34         /** logger */\r
  35         private static Log logger = LogFactory.getLog(SqlDao.class);\r
  36 \r
  37         /**\r
  38          * xmlからSQL取得。共通部。\r
  39          * @return\r
  40          * @throws SQLException\r
  41          */\r
  42          public static String getSql(Class<?> clazz, String key){\r
  43                 Config config = Config.getInstance(clazz.getName().replace(".","/")+".xml");\r
  44                 String sql="";\r
  45                 try {\r
  46                         sql = config.getNodeValue("/sql/"+key).trim();\r
  47                 } catch (Exception e) {\r
  48                 }\r
  49                 if(StringUtils.isEmpty(sql)){\r
  50                         logger.error("Can not find SQL["+ key +"]");\r
  51                 }\r
  52                 return sql;\r
  53         }\r
  54 \r
  55 \r
  56         /**\r
  57          * インスタンス取得。\r
  58          * デフォルトコネクション(default)\r
  59          * @return\r
  60          */\r
  61         public static SqlDao getInstance(){\r
  62                 return getInstance("deafault");\r
  63         }\r
  64 \r
  65          /**\r
  66           * インスタンス取得。\r
  67           * 接続先DB指定\r
  68           * @return\r
  69           */\r
  70         public static SqlDao getInstance(String connectionName){\r
  71                 return getInstance("deafault");\r
  72         }\r
  73 \r
  74         private String connectionName=null;\r
  75 \r
  76         /**\r
  77          * Executes the given SQL statement, which returns ResultSet object. This method convert ResultSet to List<Map<String, Object>>\r
  78          *\r
  79          * @return converted data.\r
  80          */\r
  81         public List<Map<String, Object>> executeQuery(String sql) throws SQLException {\r
  82                 return executeQuery(sql, null);\r
  83         }\r
  84         public List<Map<String, Object>> executeQuery(String sql, Map<String, Object> params) throws SQLException {\r
  85                 logger.debug(sql);\r
  86                 List<Map<String, Object>> result = null;\r
  87                 BindConnection con = null;\r
  88                 BindPreparedStatement st = null;\r
  89                 try {\r
  90                         con = DBManager.getConnection(connectionName);\r
  91                         st = con.bindPrepareStatement(sql);\r
  92                         st.setParams(params);\r
  93                         // execute SQL.\r
  94                         ResultSet rs = st.executeQuery();\r
  95                         result = rsToMapList(rs);\r
  96                         logger.debug("result count = "+ result.size());\r
  97                 }catch (SQLException e) {\r
  98                         // SQLException needs rethrow.\r
  99                         throw e;\r
 100                 } finally {\r
 101                         try { st.close(); } catch (Exception e) {}\r
 102                         try { con.close(); } catch (Exception e) {}\r
 103                 }\r
 104                 return result;\r
 105         }\r
 106 \r
 107         /**\r
 108          * ResultSetからList<Map<String, Object>>に変換\r
 109          * @param rs\r
 110          * @return\r
 111          * @throws SQLException\r
 112          */\r
 113         private List<Map<String, Object>> rsToMapList(ResultSet rs) throws SQLException{\r
 114                 List<Map<String, Object>> result = new ArrayList<Map<String,Object>>();\r
 115                 while (rs.next()) {\r
 116                         Map<String, Object> record = new LinkedHashMap<String, Object>();\r
 117                         ResultSetMetaData metaData = rs.getMetaData();\r
 118                         for(int i=1; i<metaData.getColumnCount(); i++){\r
 119                                 record.put(metaData.getColumnName(i), rs.getObject(i));\r
 120                         }\r
 121                         result.add(record);\r
 122                 }\r
 123                 return result;\r
 124         }\r
 125 \r
 126         /**\r
 127          *  Executes the given SQL statement, which returns effective rows(INSERT/DELETE/UPDATE) or returns nothing(DDL);\r
 128          */\r
 129 \r
 130         public int executeUpdate(String sql, Map<String, Object> params) throws SQLException {\r
 131                 logger.debug(sql);\r
 132                 int result = -1;\r
 133                 BindConnection con = null;\r
 134                 BindPreparedStatement st = null;\r
 135                 try {\r
 136                         con = DBManager.getConnection(connectionName);\r
 137                         st = con.bindPrepareStatement(sql);\r
 138                         st.setParams(params);\r
 139                         // execute SQL.\r
 140                         result = st.executeUpdate();\r
 141                 }catch (SQLException e) {\r
 142                         // SQLException needs rethrow.\r
 143                         throw e;\r
 144                 } finally {\r
 145                         try { st.close(); } catch (Exception e) {}\r
 146                         try { con.close(); } catch (Exception e) {}\r
 147                 }\r
 148                 return result;\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, InstantiationException, IllegalAccessException, NoSuchMethodException, InvocationTargetException {\r
 169                 // execute count sql\r
 170                 List<Map<String, Object>> list = executeQuery(sql);\r
 171                 int count = (Integer)list.get(0).values().toArray()[0];\r
 172 \r
 173                 // make select sql\r
 174                 int start = pageInfo.getPage() * pageInfo.getRows();\r
 175                 sql.replace(":offset", String.valueOf(start));\r
 176                 sql.replace(":limit",  String.valueOf(pageInfo.getRows()));\r
 177                 // これでもいける？\r
 178 //              params.put(":offset", start);\r
 179 //              params.put(":limit", pageInfo.getRows());\r
 180 \r
 181                 // execute sql\r
 182                 list = executeQuery(sql, params);\r
 183 \r
 184                 // make entity instance from result data.\r
 185                 Pager<T> pager = new Pager<T>(pageInfo);\r
 186                 pager.setTotalCount(count);\r
 187                 for(Map<String, Object> dataMap: list){\r
 188                         pager.add(mapToEntity(dataMap, clazz));\r
 189                 }\r
 190                 return pager;\r
 191         }\r
 192 \r
 193 \r
 194         /**\r
 195          * SQLから呼び出した汎用Map<String, Object>からEntityに変換\r
 196          * try to set all columns to entity with java.lang.reflection\r
 197          *\r
 198          * @param <T>\r
 199          * @param record\r
 200          * @param clazz\r
 201          * @return\r
 202          * @throws InstantiationException\r
 203          * @throws IllegalAccessException\r
 204          * @throws NoSuchMethodException\r
 205          * @throws InvocationTargetException\r
 206          */\r
 207         private <T> T mapToEntity(Map<String, Object> record, Class<T> clazz) throws InstantiationException, IllegalAccessException, NoSuchMethodException, InvocationTargetException{\r
 208                 T entity = clazz.newInstance();\r
 209                 // for each map member, set value to entity\r
 210                 for(Map.Entry<String, Object> column: record.entrySet()){\r
 211                         // "MEMBER_ID_SEQ" -> "member_id_seq" -> "memberIdSeq"(Camel) -> "MemberIdSeq"(Capitalize) -> "setMemberId"\r
 212                         String setterName = "set"+StringUtils.capitalize(StringUtils.toCamelCase(column.getKey().toLowerCase()));\r
 213                         logger.debug(setterName);\r
 214                         try {\r
 215                                 Method setterMethod = clazz.getMethod(setterName, column.getValue().getClass());\r
 216                                 setterMethod.invoke(entity, column.getValue());\r
 217                         } catch (SecurityException e) {\r
 218                                 logger.error(setterName, e);\r
 219                         } catch (NoSuchMethodException e) {\r
 220                                 logger.error(setterName, e);\r
 221                                 throw e;\r
 222                         } catch (IllegalArgumentException e) {\r
 223                                 logger.error(setterName, e);\r
 224                                 throw e;\r
 225                         } catch (InvocationTargetException e) {\r
 226                                 logger.error(setterName, e);\r
 227                                 throw e;\r
 228                         }\r
 229                 }\r
 230 \r
 231 \r
 232                 return entity;\r
 233         }\r
 234 \r
 235         /**\r
 236          *\r
 237          *\r
 238          * @return\r
 239          * @throws SQLException\r
 240          * @throws InvocationTargetException\r
 241          * @throws NoSuchMethodException\r
 242          * @throws IllegalAccessException\r
 243          * @throws InstantiationException\r
 244          */\r
 245         public <T> List<T> list(Class<T> clazz, String sql, Map<String, Object> params) throws SQLException, InstantiationException, IllegalAccessException, NoSuchMethodException, InvocationTargetException {\r
 246                 // execute sql\r
 247                 List<Map<String, Object>> list = executeQuery(sql, params);\r
 248 \r
 249                 // make entity instance from result data.\r
 250                 List<T> resultList = new ArrayList<T>();\r
 251                 for(Map<String, Object> dataMap: list){\r
 252                         resultList.add(mapToEntity(dataMap, clazz));\r
 253                 }\r
 254                 return resultList;\r
 255         }\r
 256 }\r