Java tutorial
/* * BabyFish, Object Model Framework for Java and JPA. * https://github.com/babyfish-ct/babyfish * * Copyright (c) 2008-2015, Tao Chen * * This copyrighted material is made available to anyone wishing to use, modify, * copy, or redistribute it subject to the terms and conditions of the GNU * Lesser General Public License, as published by the Free Software Foundation. * * Please visit "http://opensource.org/licenses/LGPL-3.0" to know more. * * This program is distributed in the hope that it will be useful, * but WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY * or FITNESS FOR A PARTICULAR PURPOSE. See the GNU Lesser General Public License * for more details. */ package org.babyfish.hibernate.dialect; import java.io.BufferedReader; import java.io.IOException; import java.io.InputStreamReader; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import java.util.List; import java.util.regex.Pattern; import org.babyfish.collection.ArrayList; import org.babyfish.hibernate.cfg.SettingsFactory; import org.babyfish.util.Joins; import org.babyfish.util.Resources; import org.hibernate.HibernateException; import org.hibernate.QueryException; import org.hibernate.StatelessSession; import org.hibernate.engine.spi.SessionFactoryImplementor; import org.hibernate.engine.spi.SessionImplementor; import org.slf4j.Logger; import org.slf4j.LoggerFactory; /** * @author Tao Chen */ class OracleDistinctLimits { private static final String DISTINCT_ROWID_COUNTER_INTERNAL_NAME = "org/babyfish/hibernate/dialect/oracle/DistinctRankContext"; private static final String DISTINCT_RANK = "DISTINCT_RANK"; private static final Logger LOGGER = LoggerFactory.getLogger(OracleDistinctLimits.class); private static LoggerResource LOGGER_RESOURCE = Resources.of(LoggerResource.class); protected OracleDistinctLimits() { throw new UnsupportedOperationException(); } public static String getOracleDistinctLimitString(String sql, boolean hasOffset) { boolean isForUpdate = false; if (sql.endsWith(" for update")) { sql = sql.substring(0, sql.length() - 11); isForUpdate = true; } int fromIndex = indexOfToppestStatement(sql, "from", true); String rootTableAlias = getRootTableAlias(sql, fromIndex); int orderByIndex = indexOfToppestStatement(sql, "order by", true); String orderBy; boolean useDistinctRank = false; String rankAlias = "dense_rank____"; if (orderByIndex == -1) { orderBy = " order by " + rootTableAlias + ".rowid asc"; } else { orderBy = sql.substring(orderByIndex); sql = sql.substring(0, orderByIndex); String rootTableAliasPrefix = rootTableAlias + '.'; for (String orderByColumn : splitOrderByCluase(orderBy)) { if (!orderByColumn.startsWith(rootTableAliasPrefix)) { useDistinctRank = true; break; } } if (!useDistinctRank) { orderBy += ", " + rootTableAlias + ".rowid asc"; } else { rankAlias = "distinct_rank____"; } } StringBuilder pagingSelect = new StringBuilder(sql.length() + 100); pagingSelect.append("select * from (").append(sql.substring(0, fromIndex)).append(", "); if (useDistinctRank) { pagingSelect.append("distinct_rank(").append(rootTableAlias).append(".rowid)"); } else { pagingSelect.append("dense_rank()"); } pagingSelect.append(" over(").append(orderBy).append(") ").append(rankAlias).append(' ') .append(sql.substring(fromIndex)).append(") where ").append(rankAlias).append(" <= ?"); if (hasOffset) { pagingSelect.append(" and ").append(rankAlias).append(" > ?"); } if (isForUpdate) { pagingSelect.append(" for update"); } return pagingSelect.toString(); } private static String getRootTableAlias(String sql, int fromIndex) { int index = fromIndex + 4; int whitespaceCount = 0; boolean isPreviousWhitespace = false; StringBuilder builder = new StringBuilder(); while (true) { char c = sql.charAt(index++); if (Character.isWhitespace(c)) { if (!isPreviousWhitespace) { if (builder.length() != 0) { String value = builder.toString(); if (!value.equals("as")) { return value; } builder = new StringBuilder(); } isPreviousWhitespace = true; whitespaceCount++; } } else { isPreviousWhitespace = false; if (whitespaceCount > 1) { builder.append(c); } } } } private static int indexOfToppestStatement(String sql, String search, boolean mustBeWord) { char searchFirst = search.charAt(0); int searchLen = search.length(); int statementDepth = 0; int len = sql.length(); boolean inComment = false; boolean inQName = false; for (int i = 0; i < len; i++) { char c = sql.charAt(i); if (inComment) { if (c == '*' && i + 1 < len && sql.charAt(i + 1) == '/') { inComment = false; i++; } continue; } else if (c == '/' && i + 1 < len && sql.charAt(i + 1) == '*') { inComment = true; i++; continue; } if (inQName) { if (c == '"') { inQName = false; } continue; } else if (c == '"') { inQName = true; continue; } if (c == '(') { statementDepth++; continue; } if (c == ')') { statementDepth--; continue; } if (statementDepth == 0 && c == searchFirst && i + searchLen <= len) { int ii = 0; while (ii < searchLen) { if (sql.charAt(i + ii) != search.charAt(ii)) { break; } ii++; } if (ii == searchLen) { if (mustBeWord && (i != 0 && isValidIdentifierChar(sql.charAt(i - 1))) || (i + searchLen >= len && isValidIdentifierChar(sql.charAt(i + searchLen)))) { i += searchLen - 1; continue; } return i; } } } return -1; } private static List<String> splitOrderByCluase(String orderBy) { List<String> list = new ArrayList<>(); StringBuilder builder = new StringBuilder(); orderBy = orderBy.substring(9); int statementDepth = 0; int len = orderBy.length(); boolean inComment = false; boolean inQName = false; for (int i = 0; i < len; i++) { char c = orderBy.charAt(i); if (inComment) { if (c == '*' && i + 1 < len && orderBy.charAt(i + 1) == '/') { inComment = false; i++; } continue; } else if (c == '/' && i + 1 < len && orderBy.charAt(i + 1) == '*') { inComment = true; i++; continue; } if (inQName) { if (c == '"') { inQName = false; } builder.append(c); continue; } else if (c == '"') { inQName = true; builder.append(c); continue; } if (c == '(') { statementDepth++; builder.append(c); continue; } if (c == ')') { statementDepth--; builder.append(c); continue; } if (statementDepth == 0 && c == ',') { list.add(builder.toString().trim()); builder = new StringBuilder(); } else { builder.append(c); } } list.add(builder.toString().trim()); return list; } private static boolean isValidIdentifierChar(char c) { if (c >= 'A' && c <= 'Z') { return true; } if (c >= 'a' && c <= 'z') { return true; } if (c >= '0' && c <= '9') { return true; } if (c == '_') { return true; } if (c == '$') { return true; } return false; } public static void install(SessionFactoryImplementor sfi) { if (!SettingsFactory.isDistinctRankCreateable(sfi.getProperties())) { return; } LOGGER.info(LOGGER_RESOURCE.tryToCreateAnalyticFunction(SettingsFactory.CREATE_ORACLE_DISTINCT_RANK, "true", DISTINCT_RANK)); StatelessSession sls = sfi.openStatelessSession(); try { Connection con = ((SessionImplementor) sls).connection(); installPLSQLWrapper(con); } catch (SQLException ex) { throw new QueryException(ex); } catch (IOException ex) { throw new HibernateException("Can not install the installable dialect", ex); } finally { sls.close(); } } private static void installPLSQLWrapper(Connection con) throws SQLException, IOException { boolean existing; //Don't use try(...) because lower version Oracle driver may not implement java7 String queryFunctionSql = "select object_name " + "from user_objects " + "where object_name = ? and object_type = ? " + "union " + "select synonym_name " + "from all_synonyms sy " + "inner join all_objects o " + "on sy.table_name = o.object_name " + "and sy.table_owner = o.owner " + "where sy.synonym_name = ? and o.object_type = ?"; PreparedStatement pstmt = con.prepareStatement(queryFunctionSql); try { pstmt.setString(1, DISTINCT_RANK); pstmt.setString(2, "FUNCTION"); pstmt.setString(3, DISTINCT_RANK); pstmt.setString(4, "FUNCTION"); if (LOGGER.isDebugEnabled()) { LOGGER.debug(queryFunctionSql + " with parameters: " + Joins.join(new String[] { DISTINCT_RANK, "FUNCTION", DISTINCT_RANK, "FUNCTION" })); } ResultSet rs = pstmt.executeQuery(); try { existing = rs.next(); } finally { rs.close(); } } finally { pstmt.close(); } if (existing) { LOGGER.info(LOGGER_RESOURCE.analyticFunctionDoesExists(DISTINCT_RANK)); return; } installJavaImpl(con); LOGGER.info(LOGGER_RESOURCE.analyticFunctionDoesNotExists(DISTINCT_RANK)); List<String> sqls = new ArrayList<>(); StringBuilder builder = new StringBuilder(); try (BufferedReader reader = new BufferedReader( new InputStreamReader(Oracle10gDialect.class.getResourceAsStream("oracle_distinct_rank.sql")))) { while (true) { String line = reader.readLine(); String trimedLine = line; if (line != null) { trimedLine = line.trim(); } if (line == null || trimedLine.equals("/")) { if (builder.length() != 0) { sqls.add(builder.toString()); builder = new StringBuilder(); } } if (line == null) { break; } if (trimedLine.isEmpty() || trimedLine.equals("/")) { continue; } builder.append(line).append('\n'); } } for (String sql : sqls) { Statement stmt = con.createStatement(); try { LOGGER.info(sql); stmt.execute(sql); } finally { stmt.close(); } } } private static void installJavaImpl(Connection con) throws SQLException, IOException { boolean existing; //Don't use try(...) because lower version Oracle driver may not implement java7 String queryJavaClassSql = "select name " + "from all_java_classes " + "where name = ? " + "union " + "select synonym_name " + "from all_synonyms s " + "inner join all_java_classes jc " + "on s.table_name = jc.name " + "and s.table_owner = jc.owner " + "where synonym_name = ?"; PreparedStatement pstmt = con.prepareStatement(queryJavaClassSql); try { pstmt.setString(1, DISTINCT_ROWID_COUNTER_INTERNAL_NAME); pstmt.setString(2, DISTINCT_ROWID_COUNTER_INTERNAL_NAME); if (LOGGER.isDebugEnabled()) { LOGGER.debug(queryJavaClassSql + " with parameters: " + Joins.join(new String[] { DISTINCT_ROWID_COUNTER_INTERNAL_NAME, DISTINCT_ROWID_COUNTER_INTERNAL_NAME })); } ResultSet rs = pstmt.executeQuery(); try { existing = rs.next(); } finally { rs.close(); } } finally { pstmt.close(); } if (existing) { LOGGER.info(LOGGER_RESOURCE.javaClassDoesExists(DISTINCT_ROWID_COUNTER_INTERNAL_NAME)); return; } LOGGER.info(LOGGER_RESOURCE.javaClassDoesNotExists(DISTINCT_ROWID_COUNTER_INTERNAL_NAME)); // Oracle JDBC driver has a bug, it can not execute the sql like this // CREATE OR REPLACE AND COMPILE JAVA SOURCE NAMED ${name} AS // ${java source code}; // // Fortunately, Oracle supports dynamic SQL, so the embedded sql resource // "oracle_distinct_rank_java.sql" can be execute as dyanmic sql in pl/sql block StringBuilder builder = new StringBuilder(); builder.append("BEGIN\n"); builder.append("\tEXECUTE IMMEDIATE\n\t\t"); try (BufferedReader reader = new BufferedReader(new InputStreamReader( Oracle10gDialect.class.getResourceAsStream("oracle_distinct_rank_java.sql")))) { boolean addConcat = false; Pattern singleQuotePattern = Pattern.compile("'", Pattern.LITERAL); while (true) { String line = reader.readLine(); if (line == null) { break; } if (line.trim().isEmpty()) { continue; } line = singleQuotePattern.matcher(line).replaceAll("''"); if (addConcat) { builder.append("\n\t\t|| chr(10) || "); } builder.append('\'').append(line).append('\''); addConcat = true; } builder.append(";\nEND;\n"); } //Don't use try(...) because lower version Oracle driver may not implement java7 Statement stmt = con.createStatement(); try { String sql = builder.toString(); LOGGER.info(sql); stmt.execute(sql); } finally { stmt.close(); } } private interface LoggerResource { String tryToCreateAnalyticFunction(String babyfishHiberantePropertyName, String babyfishHiberantePropertyValue, String analyticFunctionName); String javaClassDoesNotExists(String javaClassName); String javaClassDoesExists(String javaClassName); String analyticFunctionDoesNotExists(String analyticFunctionName); String analyticFunctionDoesExists(String analyticFunctionName); } }