Java tutorial
/** * Copyright (c) 2004-2011 Wang Jinbao(Julian Wong), http://www.ralasafe.com * Licensed under the MIT license: http://www.opensource.org/licenses/mit-license.php */ package org.ralasafe.db.sql; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.text.SimpleDateFormat; import java.util.ArrayList; import java.util.Collection; import java.util.HashMap; import java.util.Iterator; import java.util.LinkedList; import java.util.List; import java.util.Map; import org.apache.commons.logging.Log; import org.apache.commons.logging.LogFactory; import org.ralasafe.SystemConstant; import org.ralasafe.db.ColumnAdapter; import org.ralasafe.db.DBLevelException; import org.ralasafe.db.DBPower; import org.ralasafe.entitle.QueryResult; import org.ralasafe.entitle.QueryTestResult; import org.ralasafe.user.User; import org.ralasafe.util.DBUtil; public class Query implements Operand, LeftOfIn, RightOfIn, SQLElement { private static Log log = LogFactory.getLog(Query.class); public static final String SQL_TYPE = "sql"; public static final String STORED_PROCEDURE_TYPE = "storedProcedure"; private String name; /** * datasource name */ private String ds; private boolean isRawSQL; private String type = SQL_TYPE; private Select select = new Select(); private From from = new From(); private Where where = new Where(); private GroupBy groupBy = new GroupBy(); private OrderBy orderBy = new OrderBy(); private RawSQL rawSQL = new RawSQL(); private StoredProcedure storedProcedure = new StoredProcedure(); private String SQL; /** * The values would be set to PreparedStatement */ private ArrayList values = new ArrayList(); public boolean isRawSQL() { return isRawSQL; } public String getType() { return type; } public void setType(String type) { this.type = type; } public StoredProcedure getStoredProcedure() { return storedProcedure; } public void setStoredProcedure(StoredProcedure storedProcedure) { this.storedProcedure = storedProcedure; } public void setIsRawSQL(boolean isRawSQL) { this.isRawSQL = isRawSQL; } public RawSQL getRawSQL() { return rawSQL; } public void setRawSQL(RawSQL rawSQL) { this.rawSQL = rawSQL; } public String reloadSQL() { this.SQL = null; return toSQL(); } public String toSQL() { if (this.SQL == null) { StringBuffer buf = new StringBuffer(); if (isRawSQL) { buf.append(rawSQL.getContent()); buf.append(where.toSQL()); } else { buf.append(select.toSQL()).append(from.toSQL()); buf.append(where.toSQL()); buf.append(groupBy.toSQL()); buf.append(orderBy.toSQL()); } this.SQL = format(buf.toString()); } if (log.isDebugEnabled()) { log.debug("\n" + this.SQL); } return this.SQL; } /** * Add line breaker at propriety positions, to format for web view * @param script * @return */ private String format(String script) { int maxInLine = 80; int lengthOfLine = 0; int indexOfTheLatestSpaceOrComma = 0; char[] dst = new char[script.length()]; script.getChars(0, script.length(), dst, 0); StringBuffer buf = new StringBuffer(); char replacedChar = ' '; for (int i = 0; i < dst.length; i++, lengthOfLine++) { char curChar = dst[i]; buf.append(curChar); if (curChar == '\n') { // reset counter lengthOfLine = 0; continue; } if (curChar == ' ' || curChar == '\t' || curChar == ',') { replacedChar = curChar; indexOfTheLatestSpaceOrComma = buf.length() - 1; } if (lengthOfLine == maxInLine) { // break line if (replacedChar == ',') { buf.replace(indexOfTheLatestSpaceOrComma, indexOfTheLatestSpaceOrComma + 1, ",\n "); } else { buf.replace(indexOfTheLatestSpaceOrComma, indexOfTheLatestSpaceOrComma + 1, "\n "); } lengthOfLine = (buf.length() - indexOfTheLatestSpaceOrComma + 1); } } return buf.toString(); } public void setValues(ArrayList values) { this.values = values; } public ArrayList getValues() { return values; } public Select getSelect() { return select; } public void setSelect(Select select) { this.select = select; } public From getFrom() { return from; } public void setFrom(From from) { this.from = from; } public Where getWhere() { return where; } public void setWhere(Where where) { this.where = where; } public GroupBy getGroupBy() { return groupBy; } public void setGroupBy(GroupBy groupBy) { this.groupBy = groupBy; } public OrderBy getOrderBy() { return orderBy; } public void setOrderBy(OrderBy orderBy) { this.orderBy = orderBy; } public String getName() { return name; } public void setName(String name) { this.name = name; } /** * * Execute this query. It' often be called by UserCategory and BusinessDate script. * * @param returnCollection return a collection? * @return if returnCollection, return the collection< first column's value of query records> * else, return the first record's first column value */ public Object executeQueryRef(User user, Map context, boolean returnCollection) throws DBLevelException { Connection conn = null; try { conn = DBPower.getConnection(ds); return executeQueryRef(conn, user, context, returnCollection); } finally { DBUtil.close(conn); } } private Object executeQueryRef(Connection conn, User user, Map context, boolean returnCollection) { QueryResult queryResult = execute(conn, user, context); Collection dataColl = queryResult.getData(); int size = dataColl.size(); if (returnCollection) { ArrayList list = new ArrayList(size); for (Iterator iter = dataColl.iterator(); iter.hasNext();) { Object record = iter.next(); list.add(extractValue(record)); } return list; } else { Object record = null; // return default value if no record be queried if (size == 0) { record = this.select.getObjectNewer().newObject(); } else { record = dataColl.iterator().next(); } return extractValue(record); } } private Object extractValue(Object record) { ArrayList columns = this.select.getColumns(); Column column = (Column) columns.get(0); ColumnAdapter adapter = column.getAdapter(); return adapter.extractFieldValue(record); } // private Object getValue( ResultSet rs ) throws SQLException { // ArrayList columns=this.select.getColumns(); // if( columns.size()>0 ) { // Column column=(Column) columns.get( 0 ); // column.getAdapter().readResultSet( rs, 1, o ) // } // return rs.getObject(1); // } public QueryResult execute(User user, Map context) throws DBLevelException { Connection conn = null; try { conn = DBPower.getConnection(ds); QueryResult result = null; int queryLimit = SystemConstant.getQueryLimit(); if (queryLimit > 0) { result = execute(conn, user, context, 0, queryLimit); if (result.getData().size() >= queryLimit) { result.setReachQueryLimit(true); } } else { result = execute(conn, user, context); } result.setTotalCount(executeCount(conn, user, context)); return result; } finally { DBUtil.close(conn); } } /** * Return query result pagination. * * @param first first index, count from 0,1,2,... * @param max max records */ public QueryResult execute(User user, Map context, int first, int max) { first = (first < 0 ? 0 : first); max = (max < 0 ? 0 : max); Connection conn = null; try { conn = DBPower.getConnection(ds); int queryLimit = SystemConstant.getQueryLimit(); if (queryLimit > 0 && queryLimit < max) { max = queryLimit; } QueryResult result = execute(conn, user, context, first, max); if (result.getData().size() >= queryLimit) { result.setReachQueryLimit(true); } result.setTotalCount(executeCount(conn, user, context)); return result; } finally { DBUtil.close(conn); } } private QueryResult execute(Connection conn, User user, Map context) { if (context == null) { context = new HashMap(); } List data = new LinkedList(); PreparedStatement pstmt = null; ResultSet rs = null; try { if (type.equals(SQL_TYPE)) { pstmt = conn.prepareStatement(toSQL()); } else if (type.equals(STORED_PROCEDURE_TYPE)) { pstmt = conn.prepareCall(storedProcedure.getContent()); } rs = getResultSet(user, context, pstmt); // mapping Select selectObj = null; if (type.equals(SQL_TYPE)) { if (isRawSQL) { selectObj = rawSQL.getSelect(); } else { selectObj = select; } } else if (type.equals(STORED_PROCEDURE_TYPE)) { selectObj = storedProcedure.getSelect(); } while (rs.next()) { Object obj = selectObj.getObjectNewer().newObject(); ArrayList columns = selectObj.getColumns(); int columnCount = columns.size(); for (int i = 0; i < columnCount; i++) { Column column = (Column) columns.get(i); column.getAdapter().readResultSet(rs, column.getAlias(), obj); } data.add(obj); } } catch (SQLException e) { throw new DBLevelException(e); } finally { DBUtil.close(rs); DBUtil.close(pstmt); } return getQueryResult(data); } private QueryResult getQueryResult(Collection data) { QueryResult result = new QueryResult(); result.setData(data); if (type.equals(SQL_TYPE)) { if (isRawSQL) { result.setFields(rawSQL.getSelect().getFields()); result.setReadOnlyFields(rawSQL.getSelect().getReadOnlyFields()); } else { result.setFields(select.getFields()); result.setReadOnlyFields(select.getReadOnlyFields()); } } else if (type.equals(STORED_PROCEDURE_TYPE)) { result.setFields(storedProcedure.getSelect().getFields()); result.setReadOnlyFields(storedProcedure.getSelect().getReadOnlyFields()); } return result; } private QueryResult execute(Connection conn, User user, Map context, int first, int max) { if (context == null) { context = new HashMap(); } List data = new LinkedList(); PreparedStatement pstmt = null; ResultSet rs = null; try { boolean supportsLimit = false; if (type.equals(SQL_TYPE)) { supportsLimit = DBUtil.supportsLimit(conn); String sql = toSQL(); if (supportsLimit) { sql = DBUtil.getLimitString(conn, sql, first, max); } pstmt = conn.prepareStatement(sql); } else if (type.equals(STORED_PROCEDURE_TYPE)) { supportsLimit = false; pstmt = conn.prepareCall(storedProcedure.getContent()); } rs = getResultSet(user, context, pstmt); // mapping Select selectObj = null; if (type.equals(SQL_TYPE)) { if (isRawSQL) { selectObj = rawSQL.getSelect(); } else { selectObj = select; } } else if (type.equals(STORED_PROCEDURE_TYPE)) { selectObj = storedProcedure.getSelect(); } if (supportsLimit) { while (rs.next()) { Object obj = selectObj.getObjectNewer().newObject(); ArrayList columns = selectObj.getColumns(); int columnCount = columns.size(); for (int i = 0; i < columnCount; i++) { Column column = (Column) columns.get(i); column.getAdapter().readResultSet(rs, column.getAlias(), obj); } data.add(obj); } } else { // skip to index(first) for (int n = 0; n < first && rs.next(); n++) ; // read max records for (int n = 0; n < max && rs.next(); n++) { Object obj = selectObj.getObjectNewer().newObject(); ArrayList columns = selectObj.getColumns(); int columnCount = columns.size(); for (int i = 0; i < columnCount; i++) { Column column = (Column) columns.get(i); column.getAdapter().readResultSet(rs, column.getAlias(), obj); } data.add(obj); } } } catch (SQLException e) { throw new DBLevelException(e); } finally { DBUtil.close(rs); DBUtil.close(pstmt); } return getQueryResult(data); } public String getDs() { return ds; } public void setDs(String ds) { this.ds = ds; } public int executeCount(User user, Map context) { Connection conn = null; try { conn = DBPower.getConnection(ds); return executeCount(conn, user, context); } finally { DBUtil.close(conn); } } private int executeCount(Connection conn, User user, Map context) { PreparedStatement pstmt = null; ResultSet rs = null; int count = 0; try { if (type.equals(SQL_TYPE)) { pstmt = conn.prepareStatement(toCountSQL()); rs = getResultSet(user, context, pstmt); rs.next(); count = rs.getInt(1); } else if (type.equals(STORED_PROCEDURE_TYPE)) { pstmt = conn.prepareCall(storedProcedure.getContent()); rs = getResultSet(user, context, pstmt); while (rs.next()) { count++; } } return count; } catch (SQLException e) { throw new DBLevelException(e); } finally { DBUtil.close(rs); DBUtil.close(pstmt); } } /** * SQL: SELECT COUNT(*)... * @return */ private String toCountSQL() { StringBuffer buf = new StringBuffer(); if (isRawSQL) { buf.append("SELECT COUNT(*) FROM ( "); buf.append(rawSQL.getContent()); buf.append(where.toSQL()); buf.append(") ralasafe_"); } else { buf.append("SELECT COUNT(*) FROM ( "); buf.append(select.toSQL()).append(from.toSQL()); buf.append(where.toSQL()); buf.append(groupBy.toSQL()); buf.append(") ralasafe_"); //buf.append(orderBy.toSQL()); } String countSql = buf.toString(); if (log.isDebugEnabled()) { log.debug("\n" + countSql); } return countSql; } private ResultSet getResultSet(User user, Map context, PreparedStatement pstmt) throws SQLException { int valueCount = values.size(); for (int i = 0; i < valueCount; i++) { Value value = (Value) values.get(i); Object setValue = value.getValue(user, context); if (value.isBehindLike()) { if (setValue instanceof java.util.Date) { SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm"); setValue = format.format((java.util.Date) setValue); } pstmt.setObject(i + 1, "%" + setValue + "%"); } else { if (setValue instanceof java.util.Date) { java.util.Date utilDate = (java.util.Date) setValue; java.sql.Date sqlDate = new java.sql.Date(utilDate.getTime()); pstmt.setDate(i + 1, sqlDate); } else { pstmt.setObject(i + 1, setValue); } } } // long start=System.currentTimeMillis(); ResultSet executeQuery = pstmt.executeQuery(); // long end=System.currentTimeMillis(); // System.out.println( "Oracle Query Cost Time(ms):" + (end-start) ); return executeQuery; } public QueryTestResult test(User user, Map context, int first, int max) { QueryTestResult result = new QueryTestResult(); try { QueryResult queryResult = execute(user, context, first, max); List data = new ArrayList(queryResult.getData()); int count = queryResult.getTotalCount(); result.setSQL(toSQL()); result.setProperties(getProperties()); result.setData(getData(data)); result.setTotalRecords(count); } catch (Exception e) { result.setFailed(true); result.setErrorMessage(e.getMessage()); result.setSQL(toSQL()); } return result; } private String[][] getData(List result) { ArrayList columns = null; if (type.equals(SQL_TYPE)) { if (isRawSQL) { columns = rawSQL.getSelect().getColumns(); } else { columns = select.getColumns(); } } else if (type.equals(STORED_PROCEDURE_TYPE)) { columns = storedProcedure.getSelect().getColumns(); } String[][] data = new String[result.size()][columns.size()]; for (int i = 0; i < data.length; i++) { Object mappingObj = result.get(i); for (int j = 0; j < data[i].length; j++) { Column column = (Column) columns.get(j); Object extractFieldValue = column.getAdapter().extractFieldValue(mappingObj); if (extractFieldValue == null) { data[i][j] = null; } else { data[i][j] = extractFieldValue.toString(); } } } return data; } private String[] getProperties() { ArrayList columns = null; if (type.equals(SQL_TYPE)) { if (isRawSQL) { columns = rawSQL.getSelect().getColumns(); } else { columns = select.getColumns(); } } else if (type.equals(STORED_PROCEDURE_TYPE)) { columns = storedProcedure.getSelect().getColumns(); } String[] properties = new String[columns.size()]; for (int i = 0; i < properties.length; i++) { Column column = (Column) columns.get(i); properties[i] = column.getProperty(); } return properties; } public Query lightCopy() { Query q = new Query(); q.setDs(ds); q.setFrom(from); q.setGroupBy(groupBy); q.setIsRawSQL(isRawSQL); q.setName(name); q.setOrderBy(orderBy); q.setRawSQL(rawSQL); q.setSelect(select); q.setStoredProcedure(storedProcedure); q.setType(type); ArrayList newValues = new ArrayList(values.size()); newValues.addAll(values); q.setValues(newValues); Where w = where.lightCopy(); q.setWhere(w); return q; } }