Java tutorial
// Copyright 2011 Google Inc. // // Licensed under the Apache License, Version 2.0 (the "License"); // you may not use this file except in compliance with the License. // You may obtain a copy of the License at // // http://www.apache.org/licenses/LICENSE-2.0 // // Unless required by applicable law or agreed to in writing, software // distributed under the License is distributed on an "AS IS" BASIS, // WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. // See the License for the specific language governing permissions and // limitations under the License. package com.google.enterprise.connector.db; import com.google.common.annotations.VisibleForTesting; import com.google.common.base.Function; import com.google.common.base.Strings; import com.google.common.collect.ImmutableMap; import com.google.enterprise.connector.spi.SpiConstants.DatabaseType; import com.google.enterprise.connector.spi.XmlUtils; import com.google.enterprise.connector.util.diffing.SnapshotRepositoryRuntimeException; import org.apache.ibatis.session.RowBounds; import org.apache.ibatis.session.SqlSession; import org.apache.ibatis.session.SqlSessionFactory; import org.apache.ibatis.session.SqlSessionFactoryBuilder; import java.io.BufferedWriter; import java.io.File; import java.io.FileWriter; import java.io.IOException; import java.io.StringReader; import java.io.Writer; import java.sql.Connection; import java.sql.DatabaseMetaData; import java.sql.SQLException; import java.text.Collator; import java.text.MessageFormat; import java.util.ArrayList; import java.util.HashMap; import java.util.List; import java.util.Map; import java.util.logging.Level; import java.util.logging.Logger; /** * A client which gets rows from a database corresponding to a given SQL query. * <p> * It uses MyBatis to talk to the database. */ public class DBClient { private static final Logger LOG = Logger.getLogger(DBClient.class.getName()); private boolean hasCustomCollationQuery = false; protected DBContext dbContext; protected SqlSessionFactory sqlSessionFactory; protected DatabaseType databaseType; static { org.apache.ibatis.logging.LogFactory.useJdkLogging(); } public DBClient() { } public void setDBContext(DBContext dbContext) throws DBException { this.dbContext = dbContext; generateSqlMap(); this.sqlSessionFactory = getSqlSessionFactory(generateMyBatisConfig()); LOG.info("DBClient for database " + getDatabaseInfo() + " is instantiated"); this.databaseType = getDatabaseType(); } /** * Constructor used for testing purpose. DBCLient initialized with sqlMap * having crawl query without CDATA section. */ @VisibleForTesting DBClient(DBContext dbContext) throws DBException { this.dbContext = dbContext; this.sqlSessionFactory = getSqlSessionFactory(generateMyBatisConfig()); this.databaseType = getDatabaseType(); } private SqlSessionFactory getSqlSessionFactory(String config) { try { SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder(); return builder.build(new StringReader(config)); } catch (RuntimeException e) { throw new RuntimeException("XML is not well formed", e); } } /** * @return a SqlSession */ @VisibleForTesting SqlSession getSqlSession() throws SnapshotRepositoryRuntimeException { try { return sqlSessionFactory.openSession(); } catch (RuntimeException e) { Throwable cause = (e.getCause() != null && e.getCause() instanceof SQLException) ? e.getCause() : e; LOG.log(Level.WARNING, "Unable to connect to the database.", cause); throw new SnapshotRepositoryRuntimeException("Unable to connect to the database.", cause); } } /** * @param skipRows number of rows to skip in the database. * @param maxRows max number of rows to return. * @return rows - subset of the result of executing the SQL query. E.g., * result table with columns id and lastName and two rows will be * returned as * * <pre> * [{id=1, lastName=last_01}, {id=2, lastName=last_02}] * </pre> * @throws DBException */ public List<Map<String, Object>> executePartialQuery(int skipRows, int maxRows) throws SnapshotRepositoryRuntimeException { // TODO(meghna): Think about a better way to scroll through the result set. List<Map<String, Object>> rows; LOG.info("Executing partial query with skipRows = " + skipRows + " and " + "maxRows = " + maxRows); SqlSession session = getSqlSession(); try { rows = session.selectList("IbatisDBClient.getAll", null, new RowBounds(skipRows, maxRows)); LOG.info( "Sucessfully executed partial query with skipRows = " + skipRows + " and maxRows = " + maxRows); } catch (RuntimeException e) { checkDBConnection(session, e); rows = new ArrayList<Map<String, Object>>(); } finally { session.close(); } LOG.info("Number of rows returned " + rows.size()); return rows; } /** * Executes the partial parameterized query for given keyValue and * returns the list of records having their key value greater than keyValue * parameter. * * @param keyValue * @return list of documents */ public List<Map<String, Object>> executeParameterizePartialQuery(Integer keyValue) throws SnapshotRepositoryRuntimeException { List<Map<String, Object>> rows; int skipRows = 0; int maxRows = dbContext.getNumberOfRows(); // Create a hashmap as to provide input parameters minvalue and maxvalue to // the query. Map<String, Object> paramMap = new HashMap<String, Object>(); paramMap.put("value", keyValue); LOG.info("Executing partial parametrized query with keyValue = " + keyValue); SqlSession session = getSqlSession(); try { rows = session.selectList("IbatisDBClient.getAll", paramMap, new RowBounds(skipRows, maxRows)); LOG.info("Sucessfully executed partial parametrized query with keyValue = " + keyValue); } catch (RuntimeException e) { checkDBConnection(session, e); rows = new ArrayList<Map<String, Object>>(); } finally { session.close(); } LOG.info("Number of rows returned " + rows.size()); return rows; } private void checkDBConnection(SqlSession session, Exception e) throws SnapshotRepositoryRuntimeException { /* * Below code is added to handle scenarios when table is deleted or * connectivity with database is lost. In this scenario first check * the SQLState of the supplied Exception. If it has no SQLState, * check the connectivity with database and if there is no connectivity, * throw a SnapshotRepositoryRuntimeException, otherwise if the SQLState * indicates a syntax error (which a dropped table or changed primary * key will show up as) allow the connector to continue as if there was * no data available. If the SQLState is anything other than a syntax * error, throw a SnapshotRepositoryRuntimeException. */ SQLException sqlException; if (e instanceof SQLException) { sqlException = (SQLException) e; } else if (e.getCause() != null && e.getCause() instanceof SQLException) { sqlException = (SQLException) e.getCause(); } else { // It is not even a SQLException. Something else is wrong, so propagate // the error. throw new SnapshotRepositoryRuntimeException(e.getMessage(), e); } String sqlState = sqlException.getSQLState(); if (sqlState != null) { // Look for SQL syntax errors, both ISO style and XOpen style. if (sqlState.startsWith("42") || sqlState.startsWith("S0") || sqlState.startsWith("37")) { LOG.log(Level.WARNING, "Could not execute SQL query on the database.", sqlException); // Swallow the exception. } else if (sqlState.startsWith("08")) { // Connection errors. LOG.log(Level.WARNING, "Unable to connect to the database.", sqlException); throw new SnapshotRepositoryRuntimeException("Unable to connect to the database.", sqlException); } else { throw new SnapshotRepositoryRuntimeException(sqlException.getMessage(), sqlException); } } else { // No SQLState to consider. Check connectivity with DB. Connection conn = null; try { conn = session.getConnection(); LOG.log(Level.WARNING, "Could not execute SQL query on the database.", e); // Swallow the exception. } catch (RuntimeException e1) { Throwable cause = (e1.getCause() != null && e1.getCause() instanceof SQLException) ? e1.getCause() : e1; LOG.log(Level.WARNING, "Unable to connect to the database", cause); throw new SnapshotRepositoryRuntimeException("Unable to connect to the database.", cause); } finally { if (conn != null) { try { conn.close(); } catch (SQLException e1) { LOG.fine("Could not close database connection: " + e1.toString()); } } } } } /** * Generates the SqlMapConfig for mysql database. It contains a reference to * the SqlMap which should be a url or a file. It assumes that the SqlMap is * in IbatisSqlMap.xml in the googleConnectorWorkDir. * * @return MyBatis Configuration XML string. */ private String generateMyBatisConfig() { /* * TODO(meghna): Look into <properties resource=" * examples/sqlmap/maps/SqlMapConfigExample.properties " /> Also look into * making DTD retrieving local with * "jar:file:<path_to_jar>/dtd.jar!<path_to_dtd>/mybatis-3-config.dtd" */ String passwordFormat = "<property name=\"password\" value=\"%s\"/>"; String passwordElem = String.format(passwordFormat, toAttrValue(dbContext.getPassword())); String config = "<?xml version=\"1.0\" encoding=\"UTF-8\" ?>\n" + "<!DOCTYPE configuration " + "PUBLIC \"-//mybatis.org//DTD Config 3.0//EN\" " + "\"http://mybatis.org/dtd/mybatis-3-config.dtd\">\n" + "<configuration>\n" + " <environments default=\"connector\">\n" + " <environment id=\"connector\">\n" + " <transactionManager type=\"JDBC\"/>\n" + " <dataSource type=\"POOLED\">\n" + " <property name=\"driver\" value=\"" + toAttrValue(dbContext.getDriverClassName()) + "\"/>\n" + " <property name=\"url\" value=\"" + toAttrValue(dbContext.getConnectionUrl()) + "\"/>\n" + " <property name=\"username\" value=\"" + toAttrValue(dbContext.getLogin()) + "\"/>\n" + " " + passwordElem + "\n" + " </dataSource>\n" + " </environment>\n" + " </environments>\n" + " <mappers>\n" + " <mapper url=\"file:///" + toAttrValue(dbContext.getGoogleConnectorWorkDir() + "/IbatisSqlMap.xml") + "\"/>\n" + " </mappers>\n" + "</configuration>\n"; LOG.config("Generated MyBatis Configuration:\n" + config.replace(passwordElem, String.format(passwordFormat, "*****"))); return config; } /** Escapes special characters in value for use in an XML attribute value. */ private String toAttrValue(String value) { StringBuilder builder = new StringBuilder(); try { XmlUtils.xmlAppendAttrValue(value, builder); } catch (IOException e) { // Can't happen with StringBuilder. throw new AssertionError(e); } return builder.toString(); } /** * Generates the SqlMap which contains the SQL query. It writes the SqlMap in * IbatisSqlMap.xml under googleConnectorWorkDir. * * @throws DBException */ private void generateSqlMap() throws DBException { /* * TODO(meghna): Look into making DTD retrieving local with * "jar:file:<path_to_jar>/dtd.jar!<path_to_dtd>/mybatis-3-mapper.dtd" */ /* * TODO(bmj): Look into making this resource available as * an in-memory resource or Mapper class, to avoid dumping * this into the file system. */ /* * Use CDATA section for escaping XML reserved symbols as documented on * iBatis data mapper developer Guide */ String sqlMap = "<?xml version=\"1.0\" encoding=\"UTF-8\" ?>\n" + "<!DOCTYPE mapper " + "PUBLIC \"-//mybatis.org//DTD Mapper 3.0//EN\" " + "\"http://mybatis.org/dtd/mybatis-3-mapper.dtd\">\n" + "<mapper namespace=\"IbatisDBClient\">\n"; // Add a type handler for the lob field, if there is one. String lobField = dbContext.getLobField(); String resultAttribute; if (lobField != null && lobField.trim().length() > 0) { sqlMap += " <resultMap id=\"lobResultMap\" type=\"hashmap\">\n" + " <result property=\"" + lobField + "\" column=\"" + lobField + "\" typeHandler=\"" + "com.google.enterprise.connector.db.LobTypeHandler\"/>\n" + " </resultMap>\n"; resultAttribute = "resultMap=\"lobResultMap\""; } else { // TODO(jlacey): Could we just use an empty resultMap? resultAttribute = "resultType=\"hashmap\""; } sqlMap += " <select id=\"getAll\" " + resultAttribute + ">\n" + " <![CDATA[ " + dbContext.getSqlQuery() + " ]]>\n" + " </select>\n"; /* * check if authZ query is provided. If authZ query is there , add 'select' * element for getting authorized documents. */ if (dbContext.getAuthZQuery() != null && dbContext.getAuthZQuery().trim().length() > 0) { sqlMap += " <select id=\"getAuthorizedDocs\" parameterType=" + "\"java.util.HashMap\" resultType=\"java.lang.String\">\n " + " <![CDATA[ " + dbContext.getAuthZQuery() + " ]]>\n" + " </select>\n"; dbContext.setPublicFeed(false); } else { dbContext.setPublicFeed(true); } // Add in the SqlCollator Query strings, if any. sqlMap += generateCollationQueries(dbContext.getCollator()); sqlMap += "</mapper>\n"; LOG.config("Generated sqlMap : \n" + sqlMap); File file = new File(dbContext.getGoogleConnectorWorkDir(), "IbatisSqlMap.xml"); Writer output; try { output = new BufferedWriter(new FileWriter(file)); output.write(sqlMap); output.close(); } catch (IOException e) { throw new DBException("Could not write to/close Sql Map " + dbContext.getGoogleConnectorWorkDir() + "/IbatisSqlMap.xml", e); } } /** * Returns the collation queries mappings for the supported databases. */ @VisibleForTesting String generateCollationQueries(Collator collator) { String collationQuery; String collationId; if (collator != null && collator instanceof SqlCollator) { SqlCollator dbCollator = (SqlCollator) collator; collationQuery = dbCollator.getCollationQuery(); collationId = dbCollator.getCollationId(); } else { return ""; } if (!Strings.isNullOrEmpty(collationQuery)) { hasCustomCollationQuery = true; return generateCollationQuery("compareStrings", collationQuery); } else if (!Strings.isNullOrEmpty(collationId)) { String queries = generateCollationQuery("compareStrings_oracle", MessageFormat.format( "SELECT Name FROM (SELECT ''$'{'source'}''' " + "AS Name, NLSSORT(''$'{'source'}''', ''NLS_SORT = {0}'') " + "AS SortKey FROM dual UNION ALL SELECT ''$'{'target'}'''," + "NLSSORT(''$'{'target'}''', ''NLS_SORT = {0}'') FROM dual) " + "temp WHERE NLSSORT(''$'{'source'}''', ''NLS_SORT = {0}'') <> " + "NLSSORT(''$'{'target'}''', ''NLS_SORT = {0}'') " + "ORDER BY SortKey", collationId)); queries += generateCollationQuery("compareStrings", MessageFormat.format( "SELECT Name FROM (SELECT ''$'{'source'}''' COLLATE {0} AS Name " + "UNION SELECT ''$'{'target'}''' COLLATE {0}) AS temp ORDER BY " + "Name", collationId)); return queries; } else { return generateCollationQuery("compareStrings_oracle", "SELECT Name FROM (SELECT '${source}' AS Name FROM dual " + "UNION SELECT '${target}' FROM dual) temp ORDER BY Name") + generateCollationQuery("compareStrings", "SELECT Name FROM (SELECT '${source}' AS Name " + "UNION SELECT '${target}') AS temp ORDER BY Name"); } } private String generateCollationQuery(String name, String query) { return " <select id=\"" + name + "\" parameterType=\"java.util.HashMap\" " + "resultType=\"java.lang.String\">\n" + " <![CDATA[ " + query + " ]]>\n </select>\n"; } /** * Like google.common.base.Function, but apply() may * throw SQLExceptions. */ public interface SqlFunction<F, T> { public T apply(F input) throws SQLException; } /** * Returns the database name and version details. * * @author Suresh_Ghuge * @return database name and version details */ public String getDatabaseInfo() { return Strings.nullToEmpty(getDatabaseMetaData(new SqlFunction<DatabaseMetaData, String>() { public String apply(DatabaseMetaData metaData) throws SQLException { return metaData.getDatabaseProductName() + " " + metaData.getDatabaseProductVersion(); } })); } /** * Returns the {@link DatabaseType} for this client. */ public DatabaseType getDatabaseType() { return getDatabaseMetaData(new SqlFunction<DatabaseMetaData, DatabaseType>() { public DatabaseType apply(DatabaseMetaData metaData) throws SQLException { String productName = metaData.getDatabaseProductName(); if (productName.equalsIgnoreCase("Oracle")) { return DatabaseType.ORACLE; } else if (productName.equalsIgnoreCase("Microsoft SQL Server")) { return DatabaseType.SQLSERVER; } else if (productName.equalsIgnoreCase("H2")) { return DatabaseType.H2; } else { return DatabaseType.OTHER; } } }); } /** * Returns information derived from the DatabaseMetaData. * * @param metaDataHandler a Function that takes a DatabaseMetaData as input * and returns a value * @return the value returned by the metaDataHandler Function, or null if * there was an error */ public <T> T getDatabaseMetaData(SqlFunction<DatabaseMetaData, T> metaDataHandler) { try { SqlSession session = sqlSessionFactory.openSession(); try { Connection conn = session.getConnection(); try { DatabaseMetaData meta = conn.getMetaData(); if (meta != null) { return metaDataHandler.apply(meta); } } finally { conn.close(); } } finally { session.close(); } } catch (SQLException e) { LOG.warning("Caught SQLException while fetching database details: " + e); } catch (Exception e) { LOG.warning("Caught Exception while fetching database details: " + e); } return null; } /** * Executes the AuthZ query for given user-name and list of * documents and returns the list of authorized documents. * * @param userName user-name * @param docIds List of documents to be authorized * @return list of authorized documents */ @SuppressWarnings("unchecked") public List<String> executeAuthZQuery(String userName, String docIds) { List<String> authorizedDocs = new ArrayList<String>(); // Create a hashmap as to provide input parameters userName and list of // documents to AuthZ query. Map<String, Object> paramMap = new HashMap<String, Object>(); paramMap.put("username", userName); paramMap.put("docIds", docIds); // Execute the AuthZ query. SqlSession session = getSqlSession(); try { authorizedDocs = session.selectList("IbatisDBClient.getAuthorizedDocs", paramMap); } catch (Exception e) { LOG.log(Level.WARNING, "Could not execute AuthZ query on the database.", e); } finally { session.close(); } return authorizedDocs; } /** * Executes the Collation SQL query, to determine the sort order of the two * string values. * * @param source the source String * @param target the target String * @return an integer less than, equal to, or greater than zero depending * on whether the source string is less than, equal to, or greater than the * target string. */ public int executeCollationQuery(String source, String target) { // Determine which query to use based on DatabaseType or custom query. String collationQueryId = "IbatisDBClient.compareStrings"; if (!hasCustomCollationQuery) { if (databaseType == DatabaseType.ORACLE) { collationQueryId += "_" + databaseType.toString(); } } // Create a hashmap to provide input parameters to the query. Map<String, Object> paramMap = ImmutableMap.<String, Object>of("source", source, "target", target); // Execute the Collation query. SqlSession session = getSqlSession(); List<String> result; try { result = session.selectList(collationQueryId, paramMap); } catch (Exception e) { LOG.log(Level.WARNING, "Could not execute SQL Collation query.", e); // Fall back to local Java Collation. return Collator.getInstance().compare(source, target); } finally { session.close(); } // If the query returns two rows, the lesser value will be the first one. if (result.size() == 2) { return source.equals(result.get(0)) ? -1 : 1; } else { // If the query returns fewer than two rows, the strings were considered // equivalent; either through the UNION or the WHERE clause of the query. return 0; } } /** * Returns true if nulls sort low in this database implementation; or * false if nulls sort high. */ public Boolean nullsAreSortedLow() { return getDatabaseMetaData(new SqlFunction<DatabaseMetaData, Boolean>() { public Boolean apply(DatabaseMetaData meta) throws SQLException { return Boolean.valueOf(meta.nullsAreSortedLow() || meta.nullsAreSortedAtStart()); } }); } }