001: /*
002: * JFox - The most lightweight Java EE Application Server!
003: * more details please visit http://www.huihoo.org/jfox or http://www.jfox.org.cn.
004: *
005: * JFox is licenced and re-distributable under GNU LGPL.
006: */
007: package org.jfox.entity;
008:
009: import java.io.ByteArrayOutputStream;
010: import java.io.IOException;
011: import java.io.InputStream;
012: import java.io.Serializable;
013: import java.math.BigDecimal;
014: import java.sql.Clob;
015: import java.sql.Connection;
016: import java.sql.PreparedStatement;
017: import java.sql.ResultSet;
018: import java.sql.ResultSetMetaData;
019: import java.sql.SQLException;
020: import java.sql.Timestamp;
021: import java.sql.Types;
022: import java.util.ArrayList;
023: import java.util.Collection;
024: import java.util.Date;
025: import java.util.HashMap;
026: import java.util.List;
027: import java.util.Map;
028: import javax.persistence.Entity;
029: import javax.persistence.PersistenceException;
030: import javax.persistence.Query;
031:
032: import org.apache.log4j.Logger;
033: import org.apache.velocity.app.event.EventHandler;
034: import org.apache.velocity.app.event.ReferenceInsertionEventHandler;
035: import org.jfox.entity.annotation.ParameterMap;
036: import org.jfox.entity.cache.Cache;
037: import org.jfox.entity.cache.CacheConfig;
038: import org.jfox.util.ClassUtils;
039: import org.jfox.util.VelocityUtils;
040:
041: /**
042: * è´Ÿè´£æ ¹æ?®æ ¹æ?® SQLTemplate æž„é€ PreparedStatement,并执行,返回 ResultClass
043: *
044: * @author <a href="mailto:jfox.young@gmail.com">Yang Yong</a>
045: */
046: public class SQLQuery extends QueryExt {
047:
048: protected static Logger logger = Logger.getLogger(SQLQuery.class);
049:
050: private EntityManagerImpl em;
051: private SQLTemplate sqlTemplate = null;
052:
053: //ä¿?å˜ä½¿ç”¨ setParameter 设置å?‚数,用于 VelocityContext çš„æ•°æ?®
054: private Map<String, Object> parameterMap = new HashMap<String, Object>();
055:
056: private String nativeSQL;
057:
058: private boolean isNamedQuery = false;
059:
060: private QueryCacheKey cacheKey = null;
061:
062: public SQLQuery(EntityManagerImpl em, SQLTemplate sqlTemplate) {
063: this .em = em;
064: this .sqlTemplate = sqlTemplate;
065: if (sqlTemplate instanceof NamedSQLTemplate) {
066: isNamedQuery = true;
067: }
068: }
069:
070: public boolean isNamedQuery() {
071: return isNamedQuery;
072: }
073:
074: public String getName() {
075: if (sqlTemplate instanceof NamedSQLTemplate) {
076: return ((NamedSQLTemplate) sqlTemplate).getName();
077: } else {
078: return "";
079: }
080: }
081:
082: private SQLTemplate getSQLTemplate() {
083: return sqlTemplate;
084: }
085:
086: public Query setParameter(String name, Object value) {
087: parameterMap.put(name, value);
088: return this ;
089: }
090:
091: private synchronized QueryCacheKey getCacheKey() {
092: if (cacheKey == null) {
093: cacheKey = new QueryCacheKey(getName(), parameterMap,
094: getFirstResult(), getMaxResult());
095: }
096: return cacheKey;
097: }
098:
099: public int executeUpdate() {
100: try {
101: PreparedStatement pst = buildPreparedStatement();
102: int rows = pst.executeUpdate();
103: // close PreparedStatement
104: pst.close();
105: tryFlushCache(); // update successfully, clear cache
106: return rows;
107: } catch (SQLException e) {
108: throw new PersistenceException(
109: "SQLQuery.executeUpdate exception.", e);
110: }
111:
112: }
113:
114: public List<?> getResultList() {
115:
116: Object cachedResult = tryRetrieveCache();
117: if (cachedResult != null) {
118: return (List<?>) cachedResult;
119: }
120:
121: PreparedStatement pst = null;
122: ResultSet rset = null;
123: final List<Object> results = new ArrayList<Object>();
124: try {
125: pst = buildPreparedStatement();
126: rset = pst.executeQuery();
127: // Skip Results
128: if (getFirstResult() > 0) {
129: try {
130: rset.absolute(getFirstResult() + 1); // absolute start from 1, but firstResult start 0
131: } catch (SQLException e) { // not support
132: for (int i = 0; i < getFirstResult(); i++) {
133: if (!rset.next()) {
134: return results;
135: }
136: }
137: }
138: }
139:
140: int countResult = 0;
141:
142: while (rset.next() && (countResult++ < getMaxResult())) {
143: results.add(buildResultObject(rset));
144: }
145:
146: tryStoreCache(results);
147:
148: return results;
149: } catch (SQLException e) {
150: throw new PersistenceException(
151: "SQLQuery.getResultList exception.", e);
152: } finally {
153: //close PreparedStatement
154: try {
155: if (rset != null) {
156: rset.close();
157: }
158: } catch (SQLException e) {
159: e.printStackTrace();
160: }
161: try {
162: if (pst != null) {
163: pst.close();
164: }
165: } catch (SQLException e) {
166: e.printStackTrace();
167: }
168: }
169: }
170:
171: public Object getSingleResult() {
172: Object cachedObject = tryRetrieveCache();
173: if (cachedObject != null) {
174: return cachedObject;
175: }
176:
177: PreparedStatement pst = null;
178: ResultSet rset = null;
179: try {
180: pst = buildPreparedStatement();
181: rset = pst.executeQuery();
182:
183: if (rset.next()) {
184: Object result = buildResultObject(rset);
185: tryStoreCache(result);
186: return result;
187: } else {
188: return null;
189: }
190: } catch (SQLException e) {
191: throw new PersistenceException(
192: "SQLQuery.getSingleResult exception.", e);
193: } finally {
194: //close PreparedStatement
195: try {
196: if (rset != null) {
197: rset.close();
198: }
199: } catch (SQLException e) {
200: e.printStackTrace();
201: }
202: try {
203: if (pst != null) {
204: pst.close();
205: }
206: } catch (SQLException e) {
207: e.printStackTrace();
208: }
209: }
210: }
211:
212: /**
213: * 使用 velocity æž„é€ PreparedStatement,将 args 设置到 PreparedStatement çš„ Parameter ä¸
214: * Parameter 使用 p1 p2 p3 编�
215: *
216: * @throws SQLException sql exception
217: */
218: protected PreparedStatement buildPreparedStatement()
219: throws SQLException {
220: // velocity expressions
221: final List<String> expressions = new ArrayList<String>();
222: // expression's result
223: final List<Object> expressionResults = new ArrayList<Object>();
224:
225: // nativeSQL ä¸?一定是固定的,比如有 if 判æ–的时候,所以æ¯?次都è¦?生æˆ?
226: nativeSQL = VelocityUtils.evaluate(
227: sqlTemplate.getTemplateSQL(), parameterMap,
228: new ReferenceInsertionEventHandler() {
229: public Object referenceInsert(String reference,
230: Object value) {
231: expressions.add(reference);
232: expressionResults.add(value);
233: return "?";
234: }
235: });
236:
237: logger
238: .info("Building PreparedStatemenet use SQL: "
239: + nativeSQL);
240:
241: Connection connection = em.getConnection();
242: PreparedStatement pst = connection.prepareStatement(nativeSQL);
243:
244: for (int i = 0; i < expressions.size(); i++) {
245:
246: // ä¸?能都是用 setString/setobject, 需è¦?使用æ£ç¡®ç±»åž‹,ä½†æ˜¯è¯¥ç±»åž‹æ— æ³•åˆ¤æ–
247: Object parameterResult = expressionResults.get(i);
248: if (parameterResult == null) {
249: String msg = "Get null while evalute "
250: + expressions.get(i);
251: if (isNamedQuery()) {
252: msg += " in named qeury: "
253: + ((NamedSQLTemplate) sqlTemplate)
254: .getName();
255: }
256: logger.warn(msg);
257: }
258: setPreparedStatementParameter(pst, i + 1, expressionResults
259: .get(i));
260: }
261:
262: return pst;
263:
264: }
265:
266: /**
267: * å› ä¸ºç»™å‡º result 的类型,调用æ£ç¡®çš„ PreparedStatement.setXXX 方法
268: *
269: * @param pst pst
270: * @param index index
271: * @param value value
272: * @throws SQLException sql exception
273: */
274: protected void setPreparedStatementParameter(PreparedStatement pst,
275: int index, Object value) throws SQLException {
276: if (value == null) {
277: pst.setObject(index, null);
278: return;
279: }
280: Class<?> clazz = value.getClass();
281:
282: if (Boolean.class.equals(clazz) || boolean.class.equals(clazz)) {
283: pst.setBoolean(index, (Boolean) value);
284: } else if (Byte.class.equals(clazz) || byte.class.equals(clazz)) {
285: pst.setByte(index, (Byte) value);
286: } else if (Short.class.equals(clazz)
287: || short.class.equals(clazz)) {
288: pst.setShort(index, (Short) value);
289: } else if (Integer.class.equals(clazz)
290: || int.class.equals(clazz)) {
291: pst.setInt(index, (Integer) value);
292: } else if (Long.class.equals(clazz) || long.class.equals(clazz)) {
293: pst.setLong(index, (Long) value);
294: } else if (Float.class.equals(clazz)
295: || float.class.equals(clazz)) {
296: pst.setFloat(index, (Float) value);
297: } else if (Double.class.equals(clazz)
298: || double.class.equals(clazz)) {
299: pst.setDouble(index, (Double) value);
300: } else if (java.math.BigDecimal.class.equals(clazz)) {
301: pst.setBigDecimal(index, ((java.math.BigDecimal) value));
302: } else if (String.class.equals(clazz)) {
303: pst.setString(index, ((String) value));
304: } else if (Date.class.equals(clazz)) {
305: pst.setDate(index, new java.sql.Date(((Date) value)
306: .getTime()));
307: } else if (java.sql.Date.class.equals(clazz)) {
308: pst.setDate(index, (java.sql.Date) value);
309: } else if (Timestamp.class.equals(clazz)) {
310: pst.setTimestamp(index, (Timestamp) value);
311: } else {
312: pst.setObject(index, value);
313: }
314: }
315:
316: protected Object buildResultObject(ResultSet rset)
317: throws SQLException {
318: //需è¦?åˆ¤æ– ResultClass 类型
319: Class<?> resultClass = sqlTemplate.getResultClass();
320:
321: if ((!resultClass.isInterface() && resultClass
322: .isAnnotationPresent(Entity.class))
323: || MappedEntity.class.isAssignableFrom(resultClass)) {
324: return buildEntityObject(rset);
325: } else if (resultClass.equals(String.class)
326: || ClassUtils.isPrimitiveClass(resultClass)
327: || ClassUtils.isPrimitiveWrapperClass(resultClass)) {
328: return getCorrectResult(rset, resultClass, 1);
329: } else {
330: throw new PersistenceException(
331: "Not supported result class: " + resultClass);
332: }
333: }
334:
335: /**
336: * å°† ResultSet æž„é€ æˆ? EntityObject
337: *
338: * @param rset result set
339: * @throws SQLException if failed
340: */
341: protected Object buildEntityObject(ResultSet rset)
342: throws SQLException {
343: final Map<String, Object> resultMap = new HashMap<String, Object>();
344: Class<?> resultClass = sqlTemplate.getResultClass();
345: ResultSetMetaData rsetMeta = rset.getMetaData();
346:
347: //注æ„?: 有å?¯èƒ½å?ªæŸ¥éƒ¨åˆ†å—段,ä¸?能使用 sqlTemplate.getColumnsByResultClass()
348: for (int i = 1; i <= rsetMeta.getColumnCount(); i++) {
349: String columnName = rsetMeta.getColumnName(i);
350:
351: if (resultClass.equals(MappedEntity.class)) {
352: // 是 MappedEntityï¼Œæ— æ³•èŽ·å¾—Column Classä¿¡æ?¯ï¼Œç»Ÿä¸€å?– String
353: resultMap.put(columnName, rset.getString(columnName));
354: } else {
355: Class columnClass = sqlTemplate
356: .getColumnClass(columnName);
357: // 如果 columnClass == null,说明 Entityä¸æ²¡æœ‰è¯¥ @Columnï¼Œé‚£ä¹ˆæ— éœ€è®¾ç½®åˆ° resultMap
358: if (columnClass == null) {
359: logger.warn("No column named \"" + columnName
360: + "\" in result class "
361: + resultClass.getName()
362: + " when execute sql query: " + nativeSQL);
363: } else {
364: //设置为 DataObject @Column 的类型
365: resultMap.put(columnName, getCorrectResult(rset,
366: columnClass, i));
367: }
368:
369: }
370: }
371:
372: Object dataObject = EntityFactory.newEntityObject(resultClass,
373: resultMap);
374:
375: // deal with MappedColumn
376: final Map<String, Object> mappedColumnResultMap = new HashMap<String, Object>();
377: boolean isMappedColumnSet = false;
378: for (EntityFactory.MappedColumnEntry mappedColEntry : sqlTemplate
379: .getMappedColumnEntries()) {
380: ParameterMap[] params = mappedColEntry.params;
381: final List<Object> parameterResult = new ArrayList<Object>();
382:
383: final Map<String, Object> velocityMap = new HashMap<String, Object>();
384: // $this 表示本 data object
385: velocityMap.put("this", dataObject);
386:
387: // 测试�数是�有值,�有在有值的情况�设置 MappedColumn
388: final Map<String, Boolean> mappedColumnSetFlag = new HashMap<String, Boolean>(
389: 1);
390: final String EVALUATE_KEY = "evaluated";
391: mappedColumnSetFlag.put(EVALUATE_KEY, true);
392: EventHandler eventHandler = new ReferenceInsertionEventHandler() {
393: public Object referenceInsert(String reference,
394: Object value) {
395: if (value == null || value.equals("")
396: || value.equals(reference)) {
397: mappedColumnSetFlag.put(EVALUATE_KEY, false);
398: }
399: parameterResult.add(value);
400: return value;
401: }
402: };
403:
404: for (ParameterMap parameterMap : params) {
405: // 有�能抛出异常
406: VelocityUtils.evaluate(parameterMap.value(),
407: velocityMap, eventHandler);
408: }
409:
410: // MappedColumn 需�的�数都已�赋值,没有赋值的�说明该次查询也�需� MappedColumn 的值
411: if (mappedColumnSetFlag.get(EVALUATE_KEY)) {
412: isMappedColumnSet = true;
413: QueryExt mappedColumnQuery = em
414: .createNamedQuery(mappedColEntry.namedQuery);
415: for (int i = 0; i < params.length; i++) {
416: mappedColumnQuery.setParameter(params[i].name(),
417: parameterResult.get(i));
418: }
419:
420: if (mappedColEntry.field.getType().isArray()) { // array
421: mappedColumnResultMap
422: .put(mappedColEntry.name, mappedColumnQuery
423: .getResultList().toArray());
424: } else if (Collection.class
425: .isAssignableFrom(mappedColEntry.field
426: .getType())) { // Collection
427: mappedColumnResultMap.put(mappedColEntry.name,
428: mappedColumnQuery.getResultList());
429: } else { // single
430: mappedColumnResultMap.put(mappedColEntry.name,
431: mappedColumnQuery.getSingleResult());
432: }
433: }
434: }
435: if (isMappedColumnSet) {
436: EntityFactory.appendMappedColumn(dataObject,
437: mappedColumnResultMap);
438: }
439: return dataObject;
440: }
441:
442: protected Object getCorrectResult(ResultSet rset,
443: Class columnClass, int columnIndex) throws SQLException {
444: Object value = null;
445: if (Boolean.class == columnClass
446: || boolean.class == columnClass) {
447: value = rset.getBoolean(columnIndex);
448: } else if (Byte.class == columnClass
449: || byte.class == columnClass) {
450: value = rset.getByte(columnIndex);
451: } else if (Short.class == columnClass
452: || short.class == columnClass) {
453: value = rset.getShort(columnIndex);
454: } else if (Integer.class == columnClass
455: || int.class == columnClass) {
456: value = rset.getInt(columnIndex);
457: } else if (Long.class == columnClass
458: || long.class == columnClass) {
459: value = rset.getLong(columnIndex);
460: } else if (Float.class == columnClass
461: || float.class == columnClass) {
462: value = rset.getFloat(columnIndex);
463: } else if (Double.class == columnClass
464: || double.class == columnClass) {
465: value = rset.getDouble(columnIndex);
466: } else if (BigDecimal.class == columnClass) {
467: value = rset.getBigDecimal(columnIndex);
468: } else if (byte[].class == columnClass) {
469: try {
470: InputStream in = rset.getBinaryStream(columnIndex);
471: ByteArrayOutputStream baos = new ByteArrayOutputStream();
472: int n;
473: byte[] buffer = new byte[1024];
474: while ((n = in.read(buffer)) != -1) {
475: baos.write(buffer, 0, n);
476: }
477: value = baos.toByteArray();
478: in.close();
479: } catch (IOException e) {
480: value = rset.getBytes(columnIndex);
481: }
482: } else if (java.lang.String.class == columnClass) {
483: //deal with CLOB, 使用 rset.getCharactorStream 统一处�
484: /*
485: try {
486: Reader reader = rset.getCharacterStream(columnIndex);
487: StringWriter sw = new StringWriter();
488:
489: int n;
490: char[] buffer = new char[1024];
491: while (-1 != (n = reader.read(buffer))) {
492: sw.write(buffer, 0, n);
493: }
494: value = sw.toString();
495: }
496: catch(IOException e) {
497: value = rset.getString(columnIndex);
498: }
499: */
500:
501: if (rset.getMetaData().getColumnType(columnIndex) == Types.CLOB) {
502: Clob clob = rset.getClob(columnIndex);
503: if (clob != null) {
504: value = clob.getSubString(1, (int) clob.length());
505: }
506: } else {
507: value = rset.getString(columnIndex);
508: }
509:
510: } else if (Date.class == columnClass) {
511: java.sql.Date sqldate = rset.getDate(columnIndex);
512: if (sqldate != null) {
513: value = new Date(sqldate.getTime());
514: }
515: } else if (java.sql.Date.class == columnClass) {
516: value = rset.getDate(columnIndex);
517: } else if (Timestamp.class == columnClass) {
518: value = rset.getTimestamp(columnIndex);
519: } else if (java.lang.Object.class == columnClass) {
520: value = rset.getObject(columnIndex);
521: }
522: return value;
523: }
524:
525: Object tryRetrieveCache() {
526: if (isNamedQuery()) {
527: String cachePartition = ((NamedSQLTemplate) getSQLTemplate())
528: .getCachePartition();
529: CacheConfig cacheConfig = EntityManagerFactoryBuilderImpl
530: .getCacheConfig(em.getUnitName());
531: if (cacheConfig != null) {
532: Cache cache = cacheConfig.buildCache(cachePartition);
533: QueryCacheKey key = getCacheKey();
534: return cache.get(key);
535: }
536: }
537: return null;
538: }
539:
540: void tryStoreCache(Object result) {
541: if (result == null) {
542: return;
543: }
544: if (!(result instanceof Serializable)) {
545: logger
546: .warn("Store cache failed, result is not Serializable! "
547: + result);
548: return;
549: }
550: if (isNamedQuery()) {
551: String cachePartition = ((NamedSQLTemplate) getSQLTemplate())
552: .getCachePartition();
553: CacheConfig cacheConfig = EntityManagerFactoryBuilderImpl
554: .getCacheConfig(em.getUnitName());
555: if (cacheConfig != null) {
556: Cache cache = cacheConfig.buildCache(cachePartition);
557: QueryCacheKey key = getCacheKey();
558: cache.put(key, (Serializable) result);
559: }
560: }
561: }
562:
563: void tryFlushCache() {
564: if (isNamedQuery()) {
565: String cachePartition = ((NamedSQLTemplate) getSQLTemplate())
566: .getCachePartition();
567: CacheConfig cacheConfig = EntityManagerFactoryBuilderImpl
568: .getCacheConfig(em.getUnitName());
569: if (cacheConfig != null) {
570: Cache cache = cacheConfig.buildCache(cachePartition);
571: cache.clear();
572: }
573: }
574: }
575: }
576:
577: //实现完整的 equals and hashCode
578: class QueryCacheKey implements Serializable {
579: private String templateName;
580: private Map<String, Object> parameterMap = new HashMap<String, Object>();
581: private int startPosition = 0;
582: private int maxResult = Integer.MAX_VALUE;
583:
584: public QueryCacheKey(String templateName,
585: Map<String, Object> parameterMap, int startPosition,
586: int maxResult) {
587: this .templateName = templateName;
588: this .parameterMap.putAll(parameterMap);
589: this .startPosition = startPosition;
590: this .maxResult = maxResult;
591: }
592:
593: public String toString() {
594: return templateName + ", ParameterMap"
595: + parameterMap.toString();
596: }
597:
598: public boolean equals(Object o) {
599: if (this == o)
600: return true;
601: if (o == null || getClass() != o.getClass())
602: return false;
603:
604: QueryCacheKey cacheKey = (QueryCacheKey) o;
605: return templateName.equals(cacheKey.templateName)
606: && (startPosition == cacheKey.startPosition)
607: && (maxResult == cacheKey.maxResult)
608: && parameterMap.equals(cacheKey.parameterMap);
609: }
610:
611: public int hashCode() {
612: int result;
613: result = templateName.hashCode();
614: result = 31 * result + parameterMap.hashCode();
615: return result;
616: }
617: }
|