Java tutorial
/** * Copyright (c) 2000-2012 Liferay, Inc. All rights reserved. * * This library is free software; you can redistribute it and/or modify it under * the terms of the GNU Lesser General Public License as published by the Free * Software Foundation; either version 2.1 of the License, or (at your option) * any later version. * * This library 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 com.liferay.util.dao.orm; import com.liferay.portal.kernel.dao.jdbc.DataAccess; import com.liferay.portal.kernel.io.unsync.UnsyncBufferedReader; import com.liferay.portal.kernel.io.unsync.UnsyncStringReader; import com.liferay.portal.kernel.log.Log; import com.liferay.portal.kernel.log.LogFactoryUtil; import com.liferay.portal.kernel.util.GetterUtil; import com.liferay.portal.kernel.util.OrderByComparator; import com.liferay.portal.kernel.util.PortalClassLoaderUtil; import com.liferay.portal.kernel.util.StringBundler; import com.liferay.portal.kernel.util.StringPool; import com.liferay.portal.kernel.util.StringUtil; import com.liferay.portal.kernel.util.Validator; import com.liferay.portal.kernel.xml.Document; import com.liferay.portal.kernel.xml.Element; import com.liferay.portal.kernel.xml.SAXReaderUtil; import com.liferay.portal.util.PortalUtil; import java.io.IOException; import java.io.InputStream; import java.sql.Connection; import java.sql.DatabaseMetaData; import java.sql.SQLException; import java.util.HashMap; import java.util.Map; import java.util.Properties; /** * @author Brian Wing Shun Chan * @author Bruno Farache * @author Raymond Aug */ public class CustomSQL { public static final String DB2_FUNCTION_IS_NOT_NULL = "CAST(? AS VARCHAR(32672)) IS NOT NULL"; public static final String DB2_FUNCTION_IS_NULL = "CAST(? AS VARCHAR(32672)) IS NULL"; public static final String INFORMIX_FUNCTION_IS_NOT_NULL = "NOT lportal.isnull(?)"; public static final String INFORMIX_FUNCTION_IS_NULL = "lportal.isnull(?)"; public static final String MYSQL_FUNCTION_IS_NOT_NULL = "IFNULL(?, '1') = '0'"; public static final String MYSQL_FUNCTION_IS_NULL = "IFNULL(?, '1') = '1'"; public static final String SYBASE_FUNCTION_IS_NOT_NULL = "CONVERT(VARCHAR,?) IS NOT NULL"; public static final String SYBASE_FUNCTION_IS_NULL = "CONVERT(VARCHAR,?) IS NULL"; public CustomSQL() throws SQLException { reloadCustomSQL(); } public String appendCriteria(String sql, String criteria) { if (Validator.isNull(criteria)) { return sql; } if (!criteria.startsWith(StringPool.SPACE)) { criteria = StringPool.SPACE.concat(criteria); } if (!criteria.endsWith(StringPool.SPACE)) { criteria = criteria.concat(StringPool.SPACE); } int pos = sql.indexOf(_GROUP_BY_CLAUSE); if (pos != -1) { return sql.substring(0, pos + 1).concat(criteria).concat(sql.substring(pos + 1)); } pos = sql.indexOf(_ORDER_BY_CLAUSE); if (pos != -1) { return sql.substring(0, pos + 1).concat(criteria).concat(sql.substring(pos + 1)); } return sql.concat(criteria); } public String get(String id) { return _sqlPool.get(id); } /** * Returns <code>true</code> if Hibernate is connecting to a DB2 database. * * @return <code>true</code> if Hibernate is connecting to a DB2 database */ public boolean isVendorDB2() { return _vendorDB2; } /** * Returns <code>true</code> if Hibernate is connecting to an Informix * database. * * @return <code>true</code> if Hibernate is connecting to an Informix * database */ public boolean isVendorInformix() { return _vendorInformix; } /** * Returns <code>true</code> if Hibernate is connecting to a MySQL database. * * @return <code>true</code> if Hibernate is connecting to a MySQL database */ public boolean isVendorMySQL() { return _vendorMySQL; } /** * Returns <code>true</code> if Hibernate is connecting to an Oracle * database. Oracle has a nasty bug where it treats '' as a * <code>NULL</code> value. See * http://thedailywtf.com/forums/thread/26879.aspx for more information on * this nasty bug. * * @return <code>true</code> if Hibernate is connecting to an Oracle * database */ public boolean isVendorOracle() { return _vendorOracle; } /** * Returns <code>true</code> if Hibernate is connecting to a PostgreSQL * database. * * @return <code>true</code> if Hibernate is connecting to a PostgreSQL * database */ public boolean isVendorPostgreSQL() { return _vendorPostgreSQL; } /** * Returns <code>true</code> if Hibernate is connecting to a Sybase * database. * * @return <code>true</code> if Hibernate is connecting to a Sybase database */ public boolean isVendorSybase() { return _vendorSybase; } public String[] keywords(String keywords) { return keywords(keywords, true); } public String[] keywords(String keywords, boolean lowerCase) { if (Validator.isNull(keywords)) { return new String[] { null }; } if (lowerCase) { keywords = keywords.toLowerCase(); } keywords = keywords.trim(); String[] keywordsArray = keywords.split("\\s+"); for (int i = 0; i < keywordsArray.length; i++) { String keyword = keywordsArray[i]; keywordsArray[i] = StringPool.PERCENT + keyword + StringPool.PERCENT; } return keywordsArray; } public String[] keywords(String[] keywordsArray) { return keywords(keywordsArray, true); } public String[] keywords(String[] keywordsArray, boolean lowerCase) { if ((keywordsArray == null) || (keywordsArray.length == 0)) { return new String[] { null }; } if (lowerCase) { for (int i = 0; i < keywordsArray.length; i++) { keywordsArray[i] = StringUtil.lowerCase(keywordsArray[i]); } } return keywordsArray; } public void reloadCustomSQL() throws SQLException { PortalUtil.initCustomSQL(); Connection con = DataAccess.getConnection(); String functionIsNull = PortalUtil.getCustomSQLFunctionIsNull(); String functionIsNotNull = PortalUtil.getCustomSQLFunctionIsNotNull(); try { if (Validator.isNotNull(functionIsNull) && Validator.isNotNull(functionIsNotNull)) { _functionIsNull = functionIsNull; _functionIsNotNull = functionIsNotNull; if (_log.isDebugEnabled()) { _log.info("functionIsNull is manually set to " + functionIsNull); _log.info("functionIsNotNull is manually set to " + functionIsNotNull); } } else if (con != null) { DatabaseMetaData metaData = con.getMetaData(); String dbName = GetterUtil.getString(metaData.getDatabaseProductName()); if (_log.isInfoEnabled()) { _log.info("Database name " + dbName); } if (dbName.startsWith("DB2")) { _vendorDB2 = true; _functionIsNull = DB2_FUNCTION_IS_NULL; _functionIsNotNull = DB2_FUNCTION_IS_NOT_NULL; if (_log.isInfoEnabled()) { _log.info("Detected DB2 with database name " + dbName); } } else if (dbName.startsWith("Informix")) { _vendorInformix = true; _functionIsNull = INFORMIX_FUNCTION_IS_NULL; _functionIsNotNull = INFORMIX_FUNCTION_IS_NOT_NULL; if (_log.isInfoEnabled()) { _log.info("Detected Informix with database name " + dbName); } } else if (dbName.startsWith("MySQL")) { _vendorMySQL = true; //_functionIsNull = MYSQL_FUNCTION_IS_NULL; //_functionIsNotNull = MYSQL_FUNCTION_IS_NOT_NULL; if (_log.isInfoEnabled()) { _log.info("Detected MySQL with database name " + dbName); } } else if (dbName.startsWith("Sybase") || dbName.equals("ASE")) { _vendorSybase = true; _functionIsNull = SYBASE_FUNCTION_IS_NULL; _functionIsNotNull = SYBASE_FUNCTION_IS_NOT_NULL; if (_log.isInfoEnabled()) { _log.info("Detected Sybase with database name " + dbName); } } else if (dbName.startsWith("Oracle")) { _vendorOracle = true; if (_log.isInfoEnabled()) { _log.info("Detected Oracle with database name " + dbName); } } else if (dbName.startsWith("PostgreSQL")) { _vendorPostgreSQL = true; if (_log.isInfoEnabled()) { _log.info("Detected PostgreSQL with database name " + dbName); } } else { if (_log.isDebugEnabled()) { _log.debug("Unable to detect database with name " + dbName); } } } } catch (Exception e) { _log.error(e, e); } finally { DataAccess.cleanUp(con); } if (_sqlPool == null) { _sqlPool = new HashMap<String, String>(); } else { _sqlPool.clear(); } try { Class<?> clazz = getClass(); ClassLoader classLoader = clazz.getClassLoader(); String[] configs = getConfigs(); for (String _config : configs) { read(classLoader, _config); } } catch (Exception e) { _log.error(e, e); } } public String removeGroupBy(String sql) { int x = sql.indexOf(_GROUP_BY_CLAUSE); if (x != -1) { int y = sql.indexOf(_ORDER_BY_CLAUSE); if (y == -1) { sql = sql.substring(0, x); } else { sql = sql.substring(0, x) + sql.substring(y); } } return sql; } public String removeOrderBy(String sql) { int pos = sql.indexOf(_ORDER_BY_CLAUSE); if (pos != -1) { sql = sql.substring(0, pos); } return sql; } public String replaceAndOperator(String sql, boolean andOperator) { String andOrConnector = "OR"; String andOrNullCheck = "AND ? IS NOT NULL"; if (andOperator) { andOrConnector = "AND"; andOrNullCheck = "OR ? IS NULL"; } sql = StringUtil.replace(sql, new String[] { "[$AND_OR_CONNECTOR$]", "[$AND_OR_NULL_CHECK$]" }, new String[] { andOrConnector, andOrNullCheck }); if (_vendorPostgreSQL) { sql = StringUtil.replace(sql, new String[] { "Date >= ? AND ? IS NOT NULL", "Date <= ? AND ? IS NOT NULL", "Date >= ? OR ? IS NULL", "Date <= ? OR ? IS NULL" }, new String[] { "Date >= ? AND CAST(? AS TIMESTAMP) IS NOT NULL", "Date <= ? AND CAST(? AS TIMESTAMP) IS NOT NULL", "Date >= ? OR CAST(? AS TIMESTAMP) IS NULL", "Date <= ? OR CAST(? AS TIMESTAMP) IS NULL" }); } sql = replaceIsNull(sql); return sql; } public String replaceIsNull(String sql) { if (Validator.isNotNull(_functionIsNull)) { sql = StringUtil.replace(sql, new String[] { "? IS NULL", "? IS NOT NULL" }, new String[] { _functionIsNull, _functionIsNotNull }); } return sql; } public String replaceKeywords(String sql, String field, boolean last, int[] values) { if ((values != null) && (values.length == 1)) { return sql; } StringBundler oldSql = new StringBundler(4); oldSql.append("("); oldSql.append(field); oldSql.append(" = ?)"); if (!last) { oldSql.append(" [$AND_OR_CONNECTOR$]"); } if ((values == null) || (values.length == 0)) { return StringUtil.replace(sql, oldSql.toString(), StringPool.BLANK); } StringBundler newSql = new StringBundler(values.length * 4 + 3); newSql.append("("); for (int i = 0; i < values.length; i++) { if (i > 0) { newSql.append(" OR "); } newSql.append("("); newSql.append(field); newSql.append(" = ?)"); } newSql.append(")"); if (!last) { newSql.append(" [$AND_OR_CONNECTOR$]"); } return StringUtil.replace(sql, oldSql.toString(), newSql.toString()); } public String replaceKeywords(String sql, String field, boolean last, long[] values) { if ((values != null) && (values.length == 1)) { return sql; } StringBundler oldSql = new StringBundler(4); oldSql.append("("); oldSql.append(field); oldSql.append(" = ?)"); if (!last) { oldSql.append(" [$AND_OR_CONNECTOR$]"); } if ((values == null) || (values.length == 0)) { return StringUtil.replace(sql, oldSql.toString(), StringPool.BLANK); } StringBundler newSql = new StringBundler(values.length * 4 + 3); newSql.append("("); for (int i = 0; i < values.length; i++) { if (i > 0) { newSql.append(" OR "); } newSql.append("("); newSql.append(field); newSql.append(" = ?)"); } newSql.append(")"); if (!last) { newSql.append(" [$AND_OR_CONNECTOR$]"); } return StringUtil.replace(sql, oldSql.toString(), newSql.toString()); } public String replaceKeywords(String sql, String field, String operator, boolean last, String[] values) { if ((values != null) && (values.length <= 1)) { return sql; } StringBundler oldSql = new StringBundler(6); oldSql.append("("); oldSql.append(field); oldSql.append(" "); oldSql.append(operator); oldSql.append(" ? [$AND_OR_NULL_CHECK$])"); if (!last) { oldSql.append(" [$AND_OR_CONNECTOR$]"); } StringBundler newSql = new StringBundler(values.length * 6 + 3); newSql.append("("); for (int i = 0; i < values.length; i++) { if (i > 0) { newSql.append(" OR "); } newSql.append("("); newSql.append(field); newSql.append(" "); newSql.append(operator); newSql.append(" ? [$AND_OR_NULL_CHECK$])"); } newSql.append(")"); if (!last) { newSql.append(" [$AND_OR_CONNECTOR$]"); } return StringUtil.replace(sql, oldSql.toString(), newSql.toString()); } public String replaceGroupBy(String sql, String groupBy) { if (groupBy == null) { return sql; } int x = sql.indexOf(_GROUP_BY_CLAUSE); if (x != -1) { int y = sql.indexOf(_ORDER_BY_CLAUSE); if (y == -1) { sql = sql.substring(0, x + _GROUP_BY_CLAUSE.length()).concat(groupBy); } else { sql = sql.substring(0, x + _GROUP_BY_CLAUSE.length()).concat(groupBy).concat(sql.substring(y)); } } else { int y = sql.indexOf(_ORDER_BY_CLAUSE); if (y == -1) { sql = sql.concat(_GROUP_BY_CLAUSE).concat(groupBy); } else { StringBundler sb = new StringBundler(); sb.append(sql.substring(0, y)); sb.append(_GROUP_BY_CLAUSE); sb.append(groupBy); sb.append(sql.substring(y)); sql = sb.toString(); } } return sql; } public String replaceOrderBy(String sql, OrderByComparator obc) { if (obc == null) { return sql; } String orderBy = obc.getOrderBy(); int pos = sql.indexOf(_ORDER_BY_CLAUSE); if ((pos != -1) && (pos < sql.length())) { sql = sql.substring(0, pos + _ORDER_BY_CLAUSE.length()).concat(orderBy); } else { sql = sql.concat(_ORDER_BY_CLAUSE).concat(orderBy); } return sql; } protected String[] getConfigs() { if (PortalClassLoaderUtil.getClassLoader() == CustomSQL.class.getClassLoader()) { Properties propsUtil = PortalUtil.getPortalProperties(); return StringUtil.split(propsUtil.getProperty("custom.sql.configs")); } else { return new String[] { "custom-sql/default.xml" }; } } protected void read(ClassLoader classLoader, String source) throws Exception { InputStream is = classLoader.getResourceAsStream(source); if (is == null) { return; } if (_log.isDebugEnabled()) { _log.debug("Loading " + source); } Document document = SAXReaderUtil.read(is); Element rootElement = document.getRootElement(); for (Element sqlElement : rootElement.elements("sql")) { String file = sqlElement.attributeValue("file"); if (Validator.isNotNull(file)) { read(classLoader, file); } else { String id = sqlElement.attributeValue("id"); String content = transform(sqlElement.getText()); content = replaceIsNull(content); _sqlPool.put(id, content); } } } protected String transform(String sql) { sql = PortalUtil.transformCustomSQL(sql); StringBundler sb = new StringBundler(); try { UnsyncBufferedReader unsyncBufferedReader = new UnsyncBufferedReader(new UnsyncStringReader(sql)); String line = null; while ((line = unsyncBufferedReader.readLine()) != null) { sb.append(line.trim()); sb.append(StringPool.SPACE); } unsyncBufferedReader.close(); } catch (IOException ioe) { return sql; } return sb.toString(); } private static final String _GROUP_BY_CLAUSE = " GROUP BY "; private static final String _ORDER_BY_CLAUSE = " ORDER BY "; private static Log _log = LogFactoryUtil.getLog(CustomSQL.class); private String _functionIsNotNull; private String _functionIsNull; private Map<String, String> _sqlPool; private boolean _vendorDB2; private boolean _vendorInformix; private boolean _vendorMySQL; private boolean _vendorOracle; private boolean _vendorPostgreSQL; private boolean _vendorSybase; }