--- /dev/null
+package test.com.lavans.luz2.dao.old;\r
+\r
+import java.lang.reflect.InvocationTargetException;\r
+import java.lang.reflect.Method;\r
+import java.sql.ResultSet;\r
+import java.sql.ResultSetMetaData;\r
+import java.sql.SQLException;\r
+import java.util.ArrayList;\r
+import java.util.LinkedHashMap;\r
+import java.util.List;\r
+import java.util.Map;\r
+\r
+import org.apache.commons.logging.Log;\r
+import org.apache.commons.logging.LogFactory;\r
+\r
+import com.lavans.luz2.commons.StringUtils;\r
+import com.lavans.luz2.sql.DBManager;\r
+import com.lavans.luz2.sql.bind.BindConnection;\r
+import com.lavans.luz2.sql.bind.BindPreparedStatement;\r
+import com.lavans.luz2.util.Config;\r
+import com.lavans.luz2.util.PageInfo;\r
+import com.lavans.luz2.util.Pager;\r
+\r
+/**\r
+ * SQL実行用汎用Daoクラス\r
+ * 接続先ごとにインスタンスを持つが、connectionNameを使い分けているだけで\r
+ * all staticでも可能\r
+ *\r
+ * @author dobashi\r
+ *\r
+ * @param <T>\r
+ */\r
+public class SqlDao{\r
+ /** logger */\r
+ private static Log logger = LogFactory.getLog(SqlDao.class);\r
+\r
+ /**\r
+ * xmlからSQL取得。共通部。\r
+ * @return\r
+ * @throws SQLException\r
+ */\r
+ public static String getSql(Class<?> clazz, String key){\r
+ Config config = Config.getInstance(clazz.getName().replace(".","/")+".xml");\r
+ String sql="";\r
+ try {\r
+ sql = config.getNodeValue("/sql/"+key).trim();\r
+ } catch (Exception e) {\r
+ }\r
+ if(StringUtils.isEmpty(sql)){\r
+ logger.error("Can not find SQL["+ key +"]");\r
+ }\r
+ return sql;\r
+ }\r
+\r
+\r
+ /**\r
+ * インスタンス取得。\r
+ * デフォルトコネクション(default)\r
+ * @return\r
+ */\r
+ public static SqlDao getInstance(){\r
+ return getInstance("deafault");\r
+ }\r
+\r
+ /**\r
+ * インスタンス取得。\r
+ * 接続先DB指定\r
+ * @return\r
+ */\r
+ public static SqlDao getInstance(String connectionName){\r
+ return getInstance("deafault");\r
+ }\r
+\r
+ private String connectionName=null;\r
+\r
+ /**\r
+ * Executes the given SQL statement, which returns ResultSet object. This method convert ResultSet to List<Map<String, Object>>\r
+ *\r
+ * @return converted data.\r
+ */\r
+ public List<Map<String, Object>> executeQuery(String sql) throws SQLException {\r
+ return executeQuery(sql, null);\r
+ }\r
+ public List<Map<String, Object>> executeQuery(String sql, Map<String, Object> params) throws SQLException {\r
+ logger.debug(sql);\r
+ List<Map<String, Object>> result = null;\r
+ BindConnection con = null;\r
+ BindPreparedStatement st = null;\r
+ try {\r
+ con = DBManager.getConnection(connectionName);\r
+ st = con.bindPrepareStatement(sql);\r
+ st.setParams(params);\r
+ // execute SQL.\r
+ ResultSet rs = st.executeQuery();\r
+ result = rsToMapList(rs);\r
+ logger.debug("result count = "+ result.size());\r
+ }catch (SQLException e) {\r
+ // SQLException needs rethrow.\r
+ throw e;\r
+ } finally {\r
+ try { st.close(); } catch (Exception e) {}\r
+ try { con.close(); } catch (Exception e) {}\r
+ }\r
+ return result;\r
+ }\r
+\r
+ /**\r
+ * ResultSetからList<Map<String, Object>>に変換\r
+ * @param rs\r
+ * @return\r
+ * @throws SQLException\r
+ */\r
+ private List<Map<String, Object>> rsToMapList(ResultSet rs) throws SQLException{\r
+ List<Map<String, Object>> result = new ArrayList<Map<String,Object>>();\r
+ while (rs.next()) {\r
+ Map<String, Object> record = new LinkedHashMap<String, Object>();\r
+ ResultSetMetaData metaData = rs.getMetaData();\r
+ for(int i=1; i<metaData.getColumnCount(); i++){\r
+ record.put(metaData.getColumnName(i), rs.getObject(i));\r
+ }\r
+ result.add(record);\r
+ }\r
+ return result;\r
+ }\r
+\r
+ /**\r
+ * Executes the given SQL statement, which returns effective rows(INSERT/DELETE/UPDATE) or returns nothing(DDL);\r
+ */\r
+\r
+ public int executeUpdate(String sql, Map<String, Object> params) throws SQLException {\r
+ logger.debug(sql);\r
+ int result = -1;\r
+ BindConnection con = null;\r
+ BindPreparedStatement st = null;\r
+ try {\r
+ con = DBManager.getConnection(connectionName);\r
+ st = con.bindPrepareStatement(sql);\r
+ st.setParams(params);\r
+ // execute SQL.\r
+ result = st.executeUpdate();\r
+ }catch (SQLException e) {\r
+ // SQLException needs rethrow.\r
+ throw e;\r
+ } finally {\r
+ try { st.close(); } catch (Exception e) {}\r
+ try { con.close(); } catch (Exception e) {}\r
+ }\r
+ return result;\r
+ }\r
+\r
+ /**\r
+ * list for pager.\r
+ * You have to insert ":offset" and ":limit" like this:\r
+ * "SELECT * FROM MEMBER OFFSET :offset LIMIT :limit".\r
+ *\r
+ * @param <T>\r
+ * @param countSql SQL string for count all.\r
+ * @param pageInfo\r
+ * @param sql SQL string for select.\r
+ * @param params\r
+ * @return\r
+ * @throws SQLException\r
+ * @throws InvocationTargetException\r
+ * @throws NoSuchMethodException\r
+ * @throws IllegalAccessException\r
+ * @throws InstantiationException\r
+ */\r
+ 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
+ // execute count sql\r
+ List<Map<String, Object>> list = executeQuery(sql);\r
+ int count = (Integer)list.get(0).values().toArray()[0];\r
+\r
+ // make select sql\r
+ int start = pageInfo.getPage() * pageInfo.getRows();\r
+ sql.replace(":offset", String.valueOf(start));\r
+ sql.replace(":limit", String.valueOf(pageInfo.getRows()));\r
+ // これでもいける？\r
+// params.put(":offset", start);\r
+// params.put(":limit", pageInfo.getRows());\r
+\r
+ // execute sql\r
+ list = executeQuery(sql, params);\r
+\r
+ // make entity instance from result data.\r
+ Pager<T> pager = new Pager<T>(pageInfo);\r
+ pager.setTotalCount(count);\r
+ for(Map<String, Object> dataMap: list){\r
+ pager.add(mapToEntity(dataMap, clazz));\r
+ }\r
+ return pager;\r
+ }\r
+\r
+\r
+ /**\r
+ * SQLから呼び出した汎用Map<String, Object>からEntityに変換\r
+ * try to set all columns to entity with java.lang.reflection\r
+ *\r
+ * @param <T>\r
+ * @param record\r
+ * @param clazz\r
+ * @return\r
+ * @throws InstantiationException\r
+ * @throws IllegalAccessException\r
+ * @throws NoSuchMethodException\r
+ * @throws InvocationTargetException\r
+ */\r
+ private <T> T mapToEntity(Map<String, Object> record, Class<T> clazz) throws InstantiationException, IllegalAccessException, NoSuchMethodException, InvocationTargetException{\r
+ T entity = clazz.newInstance();\r
+ // for each map member, set value to entity\r
+ for(Map.Entry<String, Object> column: record.entrySet()){\r
+ // "MEMBER_ID_SEQ" -> "member_id_seq" -> "memberIdSeq"(Camel) -> "MemberIdSeq"(Capitalize) -> "setMemberId"\r
+ String setterName = "set"+StringUtils.capitalize(StringUtils.toCamelCase(column.getKey().toLowerCase()));\r
+ logger.debug(setterName);\r
+ try {\r
+ Method setterMethod = clazz.getMethod(setterName, column.getValue().getClass());\r
+ setterMethod.invoke(entity, column.getValue());\r
+ } catch (SecurityException e) {\r
+ logger.error(setterName, e);\r
+ } catch (NoSuchMethodException e) {\r
+ logger.error(setterName, e);\r
+ throw e;\r
+ } catch (IllegalArgumentException e) {\r
+ logger.error(setterName, e);\r
+ throw e;\r
+ } catch (InvocationTargetException e) {\r
+ logger.error(setterName, e);\r
+ throw e;\r
+ }\r
+ }\r
+\r
+\r
+ return entity;\r
+ }\r
+\r
+ /**\r
+ *\r
+ *\r
+ * @return\r
+ * @throws SQLException\r
+ * @throws InvocationTargetException\r
+ * @throws NoSuchMethodException\r
+ * @throws IllegalAccessException\r
+ * @throws InstantiationException\r
+ */\r
+ public <T> List<T> list(Class<T> clazz, String sql, Map<String, Object> params) throws SQLException, InstantiationException, IllegalAccessException, NoSuchMethodException, InvocationTargetException {\r
+ // execute sql\r
+ List<Map<String, Object>> list = executeQuery(sql, params);\r
+\r
+ // make entity instance from result data.\r
+ List<T> resultList = new ArrayList<T>();\r
+ for(Map<String, Object> dataMap: list){\r
+ resultList.add(mapToEntity(dataMap, clazz));\r
+ }\r
+ return resultList;\r
+ }\r
+}\r
OSDN Git Service
