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

   1 package test.com.lavans.luz2.dao.old;\r
   2 \r
   3 import java.lang.reflect.Field;\r
   4 import java.sql.ResultSet;\r
   5 import java.sql.ResultSetMetaData;\r
   6 import java.sql.SQLException;\r
   7 import java.util.ArrayList;\r
   8 import java.util.HashMap;\r
   9 import java.util.List;\r
  10 import java.util.Map;\r
  11 \r
  12 import org.apache.commons.logging.Log;\r
  13 import org.apache.commons.logging.LogFactory;\r
  14 \r
  15 import com.lavans.luz2.sql.DBManager;\r
  16 import com.lavans.luz2.sql.bind.BindConnection;\r
  17 import com.lavans.luz2.sql.bind.BindPreparedStatement;\r
  18 import com.lavans.luz2.util.PageInfo;\r
  19 import com.lavans.luz2.util.Pager;\r
  20 \r
  21 /**\r
  22  *\r
  23  * @author dobashi\r
  24  *\r
  25  * @param <T>\r
  26  */\r
  27 public class GenericDao<T> {\r
  28         private static Log logger = LogFactory.getLog(GenericDao.class);\r
  29         /** select SQL */\r
  30         private static final String SQL_SELECT = "SELECT $select_columns FROM $table $condition $order $limit";\r
  31         /** nextval SQL for PostgreSQL */\r
  32         private static final String SQL_NEXTVAL = "SELECT NEXTVAL('$seq')";\r
  33         /** insert SQL */\r
  34         private static final String SQL_INSERT = "INSERT INTO $table VALUES( $insert_columns )";\r
  35         /** update SQL */\r
  36         private static final String SQL_UPDATE = "UPDATE $table SET $update_columns $condition_pk";\r
  37         /** delete SQL */\r
  38         private static final String SQL_DELETE = "DELETE FROM $table $condition";\r
  39 \r
  40         //private Class<T> clazz;\r
  41         private EntityMetaData<T> entityMetaData;\r
  42         private String connectionName = "default";\r
  43         //, seq;\r
  44         /**\r
  45          * Constructor.\r
  46          */\r
  47         public  GenericDao(Class<T> value){\r
  48                 super();\r
  49                 entityMetaData = new EntityMetaData<T>(value);\r
  50                 //clazz = value;\r
  51         }\r
  52 \r
  53         /**\r
  54          * Constructor.\r
  55          */\r
  56         public  GenericDao(Class<T> value, String connectionName){\r
  57                 this(value);\r
  58                 this.connectionName = connectionName;\r
  59         }\r
  60 \r
  61         /**\r
  62          * Executes the given SQL statement, which returns ResultSet object. This method convert ResultSet to List<Map<String, Object>>\r
  63          *\r
  64          * @return converted data.\r
  65          */\r
  66         public List<Map<String, Object>> executeQuery(String sql) throws SQLException {\r
  67                 return executeQuery(sql, null);\r
  68         }\r
  69         public List<Map<String, Object>> executeQuery(String sql, Map<String, Object> params) throws SQLException {\r
  70                 logger.debug(sql);\r
  71                 List<Map<String, Object>> result = new ArrayList<Map<String,Object>>();\r
  72                 BindConnection con = null;\r
  73                 BindPreparedStatement st = null;\r
  74                 try {\r
  75                         con = DBManager.getConnection(connectionName);\r
  76                         st = con.bindPrepareStatement(sql);\r
  77                         st.setParams(params);\r
  78                         // execute SQL.\r
  79                         ResultSet rs = st.executeQuery();\r
  80                         if (rs.next()) {\r
  81                                 result.add(makeDataMap(rs));\r
  82                         }\r
  83                         logger.debug("result count = "+ result.size());\r
  84                 }catch (SQLException e) {\r
  85                         // SQLException needs rethrow.\r
  86                         throw e;\r
  87                 } finally {\r
  88                         try { st.close(); } catch (Exception e) {}\r
  89                         try { con.close(); } catch (Exception e) {}\r
  90                 }\r
  91                 return result;\r
  92         }\r
  93 \r
  94         /**\r
  95          * make a dataMap from ResultSet.\r
  96          *\r
  97          * @return\r
  98          * @throws SQLException\r
  99          */\r
 100         private Map<String, Object> makeDataMap(ResultSet rs) throws SQLException {\r
 101                 Map<String, Object> result = new HashMap<String, Object>();\r
 102                 ResultSetMetaData metaData = rs.getMetaData();\r
 103                 for(int i=1; i<metaData.getColumnCount(); i++){\r
 104                         result.put(metaData.getColumnName(i), rs.getObject(i));\r
 105                 }\r
 106                 return result;\r
 107         }\r
 108 \r
 109         /**\r
 110          *  Executes the given SQL statement, which returns effective rows(INSERT/DELETE/UPDATE) or returns nothing(DDL);\r
 111          */\r
 112 \r
 113         public int executeUpdate(String sql, Map<String, Object> params) throws SQLException {\r
 114                 logger.debug(sql);\r
 115                 int result = -1;\r
 116                 BindConnection con = null;\r
 117                 BindPreparedStatement st = null;\r
 118                 try {\r
 119                         con = DBManager.getConnection(connectionName);\r
 120                         st = con.bindPrepareStatement(sql);\r
 121                         st.setParams(params);\r
 122                         // execute SQL.\r
 123                         result = st.executeUpdate();\r
 124                 }catch (SQLException e) {\r
 125                         // SQLException needs rethrow.\r
 126                         throw e;\r
 127                 } finally {\r
 128                         try { st.close(); } catch (Exception e) {}\r
 129                         try { con.close(); } catch (Exception e) {}\r
 130                 }\r
 131                 return result;\r
 132         }\r
 133 \r
 134 \r
 135         /**\r
 136          * Load single instance from PK.\r
 137          * If data is not exist, return null.\r
 138          */\r
 139         public <PK> T load(PK pk) throws SQLException {\r
 140                 // result entity instance\r
 141                 T result = null;\r
 142 \r
 143                 // make sql\r
 144                 String sql = SQL_SELECT;\r
 145                 sql.replace("$select_columns", DaoUtils.getSelectColumns(entityMetaData.getEntityFields()));\r
 146                 sql.replace("$table", DaoUtils.getTableName(entityMetaData.getEntityClass()));\r
 147                 sql.replace("$condition", DaoUtils.getConditions(entityMetaData.getPkFields()));\r
 148                 sql.replace("$order", "");\r
 149                 sql.replace("$limit", "");\r
 150 \r
 151                 // make sql conditions\r
 152                 Map<String, Object> params = null;\r
 153                 try {\r
 154                         params = entityMetaData.toParams(pk, entityMetaData.getPkFields());\r
 155                 }catch (Exception e) {\r
 156                         // other exception has to be catched for only debug.\r
 157                         logger.error("",e);\r
 158                 }\r
 159 \r
 160                 // execute sql\r
 161                 List<Map<String, Object>> list = executeQuery(sql, params);\r
 162 \r
 163                 // make entity instance from result data.\r
 164                 if(list.size()>0){\r
 165                         result = entityMetaData.toEntity(list.get(0));\r
 166                 }\r
 167 \r
 168                 return result;\r
 169         }\r
 170 \r
 171         /**\r
 172          * get next sequence id if id is serial.\r
 173          * if pk is not serial, this method will throw SQLException.\r
 174          * @return\r
 175          */\r
 176         public long getNextId() throws SQLException{\r
 177                 // if pk fiesld is not exist, throw exception;\r
 178                 List<Field> pkFields = entityMetaData.getPkFields();\r
 179                 if(pkFields == null || pkFields.size()==0){\r
 180                         logger.debug("PK Field is NULL");\r
 181                 }\r
 182                 long id = -1;\r
 183 \r
 184                 String sql = SQL_NEXTVAL;\r
 185                 sql.replace("$seq", DaoUtils.getSequenceName(pkFields.get(0)));\r
 186 \r
 187                 // execute sql\r
 188                 List<Map<String, Object>> list = executeQuery(sql);\r
 189                 id = (Long)list.get(0).values().toArray()[0];\r
 190                 return id;\r
 191         }\r
 192 \r
 193         /**\r
 194          * insert single instance.\r
 195          * @return count of insert rows. usually 1.\r
 196          */\r
 197         public int insert(T entity) throws SQLException {\r
 198                 //long id = -1;\r
 199                 //id = getNextId();\r
 200 \r
 201                 // make sql\r
 202                 String sql = SQL_INSERT;\r
 203                 sql.replace("$insert_columns", DaoUtils.getInsertColumns(entityMetaData.getEntityFields()));\r
 204                 sql.replace("$table", DaoUtils.getTableName(entityMetaData.getEntityClass()));\r
 205 \r
 206                 // make sql conditions\r
 207                 Map<String, Object> params = null;\r
 208                 try {\r
 209                         params = entityMetaData.toParams(entity, entityMetaData.getEntityFields());\r
 210                 }catch (Exception e) {\r
 211                         // other exception has to be catched for only debug.\r
 212                         logger.error("",e);\r
 213                 }\r
 214 \r
 215                 // execute sql\r
 216                 int result = executeUpdate(sql, params);\r
 217                 if (result != 1) {\r
 218                         logger.info("INSERT result != 1.[" + result +"]");\r
 219                 }\r
 220 \r
 221                 return result;\r
 222         }\r
 223 \r
 224         /**\r
 225          * update entity.\r
 226          * @return count of update rows. 1 or 0(fail).\r
 227          */\r
 228         public int update(T entity) throws SQLException {\r
 229                 // make sql\r
 230                 String sql = SQL_UPDATE;\r
 231                 sql.replace("$update_columns", DaoUtils.getUpdateColumns(entityMetaData.getEntityFields()));\r
 232                 sql.replace("$table", DaoUtils.getTableName(entityMetaData.getEntityClass()));\r
 233 \r
 234                 // make sql conditions\r
 235                 Map<String, Object> params = null;\r
 236                 try {\r
 237                         params = entityMetaData.toParams(entity, entityMetaData.getEntityFields());\r
 238                 }catch (Exception e) {\r
 239                         // other exception has to be catched for only debug.\r
 240                         logger.error("",e);\r
 241                 }\r
 242 \r
 243                 // execute sql\r
 244                 int result = executeUpdate(sql, params);\r
 245                 if (result != 1) {\r
 246                         logger.info("UPDATE result != 1.[" + result +"]");\r
 247                 }\r
 248 \r
 249                 return result;\r
 250         }\r
 251 \r
 252         /**\r
 253          * delete 1 instance\r
 254          */\r
 255         public <PK> int delete(PK pk) throws SQLException {\r
 256                 // make sql\r
 257                 String sql = SQL_DELETE;\r
 258                 sql.replace("$table", DaoUtils.getTableName(entityMetaData.getEntityClass()));\r
 259                 sql.replace("$condition", DaoUtils.getConditions(entityMetaData.getPkFields()));\r
 260 \r
 261                 // make sql conditions\r
 262                 Map<String, Object> params = null;\r
 263                 try {\r
 264                         params = entityMetaData.toParams(pk, entityMetaData.getPkFields());\r
 265                 }catch (Exception e) {\r
 266                         // other exception has to be catched for only debug.\r
 267                         logger.error("",e);\r
 268                 }\r
 269 \r
 270                 // execute sql\r
 271                 int result = executeUpdate(sql, params);\r
 272                 if (result != 1) {\r
 273                         logger.info("DELETE result != 1.[" + result +"]");\r
 274                 }\r
 275                 return result;\r
 276         }\r
 277 \r
 278         /**\r
 279          * delete from condition. no need?\r
 280          */\r
 281 //      public int delete(String condition, Map<String, Object> params)\r
 282 //                      throws SQLException {\r
 283 //              // make sql\r
 284 //              String sql = SQL_DELETE;\r
 285 //              sql.replace("$table", DaoUtils.getTableName(entityMetaData.getEntityClass()));\r
 286 //              sql.replace("$condition", condition);\r
 287 //\r
 288 //              // execute sql\r
 289 //              int result = executeUpdate(sql, params);\r
 290 //              return result;\r
 291 //      }\r
 292 \r
 293         /**\r
 294          * list for pager\r
 295          *\r
 296          * @return\r
 297          * @throws SQLException\r
 298          */\r
 299         public Pager<T> list(PageInfo pageInfo, String condition, String order, Map<String, Object> params) throws SQLException {\r
 300                 if (condition == null) condition = "";\r
 301                 if (order == null) order = "";\r
 302 \r
 303                 // make count sql\r
 304                 String sql = SQL_SELECT;\r
 305                 sql.replace("$select_columns", "COUNT(1)");\r
 306                 sql.replace("$table", DaoUtils.getTableName(entityMetaData.getEntityClass()));\r
 307                 sql.replace("$condition", condition);\r
 308                 sql.replace("$order", "");\r
 309                 sql.replace("$limit", "");\r
 310 \r
 311                 // execute count sql\r
 312                 List<Map<String, Object>> list = executeQuery(sql);\r
 313                 int count = (Integer)list.get(0).values().toArray()[0];\r
 314 \r
 315 \r
 316                 // make select sql\r
 317                 sql = SQL_SELECT;\r
 318                 sql.replace("$select_columns", DaoUtils.getSelectColumns(entityMetaData.getEntityFields()));\r
 319                 sql.replace("$table", DaoUtils.getTableName(entityMetaData.getEntityClass()));\r
 320                 sql.replace("$condition", condition);\r
 321                 sql.replace("$order", order);\r
 322                 int start = pageInfo.getPage() * pageInfo.getRows();\r
 323                 sql.replace("$limit", "OFFSET "+ start +" LIMIT "+ pageInfo.getRows());\r
 324 \r
 325                 // execute sql\r
 326                 list = executeQuery(sql, params);\r
 327 \r
 328                 // make entity instance from result data.\r
 329                 Pager<T> pager = new Pager<T>(pageInfo);\r
 330                 pager.setTotalCount(count);\r
 331                 for(Map<String, Object> dataMap: list){\r
 332                         pager.add(entityMetaData.toEntity(dataMap));\r
 333                 }\r
 334                 return pager;\r
 335         }\r
 336 \r
 337         /**\r
 338          *\r
 339          *\r
 340          * @return\r
 341          * @throws SQLException\r
 342          */\r
 343         public List<T> list(String condition, Map<String, Object> params) throws SQLException {\r
 344                 if (condition == null) condition = "";\r
 345 \r
 346                 // make select sql\r
 347                 String sql = SQL_SELECT;\r
 348                 sql.replace("$select_columns", DaoUtils.getSelectColumns(entityMetaData.getEntityFields()));\r
 349                 sql.replace("$table", DaoUtils.getTableName(entityMetaData.getEntityClass()));\r
 350                 sql.replace("$condition", condition);\r
 351 \r
 352                 // execute sql\r
 353                 List<Map<String, Object>> list = executeQuery(sql, params);\r
 354 \r
 355                 // make entity instance from result data.\r
 356                 List<T> resultList = new ArrayList<T>();\r
 357                 for(Map<String, Object> dataMap: list){\r
 358                         resultList.add(entityMetaData.toEntity(dataMap));\r
 359                 }\r
 360                 return resultList;\r
 361         }\r
 362 }\r