1 package test.com.lavans.luz2.dao.old;
\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
13 import org.apache.commons.logging.Log;
\r
14 import org.apache.commons.logging.LogFactory;
\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
26 * 接続先ごとにインスタンスを持つが、connectionNameを使い分けているだけで
\r
33 public class SqlDao{
\r
35 private static Log logger = LogFactory.getLog(SqlDao.class);
\r
40 * @throws SQLException
\r
42 public static String getSql(Class<?> clazz, String key){
\r
43 Config config = Config.getInstance(clazz.getName().replace(".","/")+".xml");
\r
46 sql = config.getNodeValue("/sql/"+key).trim();
\r
47 } catch (Exception e) {
\r
49 if(StringUtils.isEmpty(sql)){
\r
50 logger.error("Can not find SQL["+ key +"]");
\r
58 * デフォルトコネクション(default)
\r
61 public static SqlDao getInstance(){
\r
62 return getInstance("deafault");
\r
70 public static SqlDao getInstance(String connectionName){
\r
71 return getInstance("deafault");
\r
74 private String connectionName=null;
\r
77 * Executes the given SQL statement, which returns ResultSet object. This method convert ResultSet to List<Map<String, Object>>
\r
79 * @return converted data.
\r
81 public List<Map<String, Object>> executeQuery(String sql) throws SQLException {
\r
82 return executeQuery(sql, null);
\r
84 public List<Map<String, Object>> executeQuery(String sql, Map<String, Object> params) throws SQLException {
\r
86 List<Map<String, Object>> result = null;
\r
87 BindConnection con = null;
\r
88 BindPreparedStatement st = null;
\r
90 con = DBManager.getConnection(connectionName);
\r
91 st = con.bindPrepareStatement(sql);
\r
92 st.setParams(params);
\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
101 try { st.close(); } catch (Exception e) {}
\r
102 try { con.close(); } catch (Exception e) {}
\r
108 * ResultSetからList<Map<String, Object>>に変換
\r
111 * @throws SQLException
\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
121 result.add(record);
\r
127 * Executes the given SQL statement, which returns effective rows(INSERT/DELETE/UPDATE) or returns nothing(DDL);
\r
130 public int executeUpdate(String sql, Map<String, Object> params) throws SQLException {
\r
133 BindConnection con = null;
\r
134 BindPreparedStatement st = null;
\r
136 con = DBManager.getConnection(connectionName);
\r
137 st = con.bindPrepareStatement(sql);
\r
138 st.setParams(params);
\r
140 result = st.executeUpdate();
\r
141 }catch (SQLException e) {
\r
142 // SQLException needs rethrow.
\r
145 try { st.close(); } catch (Exception e) {}
\r
146 try { con.close(); } catch (Exception e) {}
\r
153 * You have to insert ":offset" and ":limit" like this:
\r
154 * "SELECT * FROM MEMBER OFFSET :offset LIMIT :limit".
\r
157 * @param countSql SQL string for count all.
\r
159 * @param sql SQL string for select.
\r
162 * @throws SQLException
\r
163 * @throws InvocationTargetException
\r
164 * @throws NoSuchMethodException
\r
165 * @throws IllegalAccessException
\r
166 * @throws InstantiationException
\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
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
178 // params.put(":offset", start);
\r
179 // params.put(":limit", pageInfo.getRows());
\r
182 list = executeQuery(sql, params);
\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
195 * SQLから呼び出した汎用Map<String, Object>からEntityに変換
\r
196 * try to set all columns to entity with java.lang.reflection
\r
202 * @throws InstantiationException
\r
203 * @throws IllegalAccessException
\r
204 * @throws NoSuchMethodException
\r
205 * @throws InvocationTargetException
\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
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
222 } catch (IllegalArgumentException e) {
\r
223 logger.error(setterName, e);
\r
225 } catch (InvocationTargetException e) {
\r
226 logger.error(setterName, e);
\r
239 * @throws SQLException
\r
240 * @throws InvocationTargetException
\r
241 * @throws NoSuchMethodException
\r
242 * @throws IllegalAccessException
\r
243 * @throws InstantiationException
\r
245 public <T> List<T> list(Class<T> clazz, String sql, Map<String, Object> params) throws SQLException, InstantiationException, IllegalAccessException, NoSuchMethodException, InvocationTargetException {
\r
247 List<Map<String, Object>> list = executeQuery(sql, params);
\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