Java tutorial
/** * Licensed to the Apache Software Foundation (ASF) under one * or more contributor license agreements. See the NOTICE file * distributed with this work for additional information * regarding copyright ownership. The ASF licenses this file * to you 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 org.apache.hadoop.raid; import java.io.IOException; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import java.util.ArrayList; import java.util.Collections; import java.util.HashMap; import java.util.List; import java.util.Map; import java.util.Random; import org.apache.commons.logging.Log; import org.apache.commons.logging.LogFactory; import org.apache.hadoop.conf.Configuration; import org.apache.hadoop.raid.DBConnectionFactory; import org.apache.hadoop.util.ReflectionUtils; /** * Utilities for writing hooks. */ public class DBUtils { static final private Log LOG = LogFactory.getLog(DBUtils.class); // The default value is to retry 20 times with maximum retry interval // 60 seconds. The expectation is about 22 minutes. After 7 retries, it // reaches 60 seconds. static final int DEFAULT_SQL_NUM_RETRIES = 20; static final int DEFAULT_RETRY_MAX_INTERVAL_SEC = 60; public static final String DB_MAX_RETRY = "hdfs.raid.db.max_retries"; public static final int DEFAULT_DB_MAX_RETRY = 30; public static final String RAID_DB_CONNECTION_FACTORY_CLASS_KEY = "hdfs.raid.db.connection.factory.class"; public static final int RETRY_MAX_INTERVAL_SEC = 60; public static final List<Object> EMPTY_SQL_PARAMS = new ArrayList<Object>(); public static Random rand = new Random(); public static int numDBOpenObjects = 0; public static Map<Boolean, String> defaultUrls = Collections.synchronizedMap(new HashMap<Boolean, String>()); public static Long selectCount(DBConnectionFactory connectionFactory, String sql, List<Object> sqlParams, int sqlNumRetries, String tblName) throws IOException { List<List<Object>> results = DBUtils.runInsertSelect(connectionFactory, sql, sqlParams, true, sqlNumRetries, DBUtils.RETRY_MAX_INTERVAL_SEC, false, false); Long count = null; // should be the first field in the first element if (results != null && !results.isEmpty() && results.get(0).get(0) != null) { count = (Long) results.get(0).get(0); } if (count == null) { throw new IOException("You cannot select from " + tblName); } return count; } public static DBConnectionFactory getDBConnectionFactory(Configuration conf) throws IOException { Class<?> DBFactoryClass = null; DBFactoryClass = conf.getClass(DBUtils.RAID_DB_CONNECTION_FACTORY_CLASS_KEY, SimpleDBConnectionFactory.class); if (DBFactoryClass == null) { throw new IOException( "Connection factory key: " + DBUtils.RAID_DB_CONNECTION_FACTORY_CLASS_KEY + " is not set!"); } DBConnectionFactory connectionFactory = (DBConnectionFactory) ReflectionUtils.newInstance(DBFactoryClass, conf); connectionFactory.initialize(conf); return connectionFactory; } public static int getSqlNumRetry(Configuration conf) { return conf.getInt(DB_MAX_RETRY, DEFAULT_DB_MAX_RETRY); } public static void runInsert(DBConnectionFactory connectionFactory, String sql, List<Object> sqlParams, int numRetries) throws IOException { runInsertSelect(connectionFactory, sql, sqlParams, true, numRetries, DEFAULT_RETRY_MAX_INTERVAL_SEC, true, false); } public static List<List<Object>> getResults(ResultSet result) throws SQLException { List<List<Object>> results = new ArrayList<List<Object>>(); int numColumns = result.getMetaData().getColumnCount(); while (result.next()) { List<Object> row = new ArrayList<Object>(); results.add(row); for (int index = 1; index <= numColumns; index++) { row.add(result.getObject(index)); } } return results; } public static void close(ResultSet generatedKeys, PreparedStatement[] pstmts, Connection conn) { if (generatedKeys != null) { try { generatedKeys.close(); } catch (Exception e) { LOG.warn("Error to close ResultSet", e); } finally { try { if (!generatedKeys.isClosed()) { LOG.warn("ResultSet is not closed"); DBUtils.numDBOpenObjects++; } } catch (Exception ignore) { DBUtils.numDBOpenObjects++; } } } if (pstmts != null && pstmts.length > 0) { for (PreparedStatement pstmt : pstmts) { if (pstmt == null) { continue; } try { pstmt.close(); } catch (Exception e) { LOG.warn("Error to close PreparedStatement", e); } finally { try { if (!pstmt.isClosed()) { LOG.warn("PreparedStatement is not closed"); DBUtils.numDBOpenObjects++; } } catch (Exception ignore) { DBUtils.numDBOpenObjects++; } } } } if (conn != null) { try { conn.close(); } catch (Exception e) { LOG.warn("Error to close Connection", e); } finally { try { if (!conn.isClosed()) { LOG.warn("Connection is not closed"); DBUtils.numDBOpenObjects++; } } catch (Exception ignore) { DBUtils.numDBOpenObjects++; } } } } // In the case of a select returns a list of lists, where each inner list represents a row // returned by the query. In the case of an insert, returns null. public static List<List<Object>> runInsertSelect(DBConnectionFactory connectionFactory, String sql, List<Object> sqlParams, boolean isWrite, int numRetries, int retryMaxInternalSec, boolean insert, boolean getGeneratedKeys) throws IOException { int waitMS = 3000; // wait for at least 3s before next retry. for (int i = 0; i < numRetries; ++i) { Connection conn = null; ResultSet generatedKeys = null; PreparedStatement pstmt = null; String url = null; try { try { url = connectionFactory.getUrl(isWrite); } catch (IOException ioe) { LOG.warn("Cannot get DB URL, fall back to the default one", ioe); url = defaultUrls.get(isWrite); if (url == null) { throw ioe; } } LOG.info("Attepting connection with URL " + url); conn = connectionFactory.getConnection(url); defaultUrls.put(isWrite, url); pstmt = getPreparedStatement(conn, sql, sqlParams, getGeneratedKeys); if (insert) { int recordsUpdated = pstmt.executeUpdate(); LOG.info("rows inserted: " + recordsUpdated + " sql: " + sql); List<List<Object>> results = null; if (getGeneratedKeys) { generatedKeys = pstmt.getGeneratedKeys(); results = getResults(generatedKeys); } Thread.sleep(connectionFactory.getDBOpsSleepTime() + rand.nextInt(1000)); return results; } else { generatedKeys = pstmt.executeQuery(); List<List<Object>> results = getResults(generatedKeys); pstmt.clearBatch(); LOG.info("rows selected: " + results.size() + " sql: " + sql); Thread.sleep(connectionFactory.getDBOpsSleepTime() + rand.nextInt(1000)); return results; } } catch (Exception e) { // We should catch a better exception than Exception, but since // DBConnectionUrlFactory.getUrl() defines throws Exception, it's hard // for us to figure out the complete set it can throw. We follow // DBConnectionUrlFactory.getUrl()'s definition to catch Exception. // It shouldn't be a big problem as after numRetries, we anyway exit. LOG.info("Exception " + e + ". Will retry " + (numRetries - i) + " times."); // Introducing a random factor to the wait time before another retry. // The wait time is dependent on # of failures and a random factor. // At the first time of getting a SQLException, the wait time // is a random number between [0,300] msec. If the first retry // still fails, we will wait 300 msec grace period before the 2nd retry. // Also at the second retry, the waiting window is expanded to 600 msec // alleviating the request rate from the server. Similarly the 3rd retry // will wait 600 msec grace period before retry and the waiting window // is // expanded to 1200 msec. waitMS += waitMS; if (waitMS > retryMaxInternalSec * 1000) { waitMS = retryMaxInternalSec * 1000; } double waitTime = waitMS + waitMS * rand.nextDouble(); if (i + 1 == numRetries) { LOG.error("Still got Exception after " + numRetries + " retries.", e); throw new IOException(e); } try { Thread.sleep((long) waitTime); } catch (InterruptedException ie) { throw new IOException(ie); } } finally { DBUtils.close(generatedKeys, new PreparedStatement[] { pstmt }, conn); } } return null; } public static PreparedStatement getPreparedStatement(Connection conn, String sql, List<Object> sqlParams, boolean getGeneratedKeys) throws IOException, SQLException { PreparedStatement pstmt = getGeneratedKeys ? conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS) : conn.prepareStatement(sql); int pos = 1; for (Object param : sqlParams) { if (param instanceof Integer) { pstmt.setInt(pos++, ((Integer) param).intValue()); } else { pstmt.setString(pos++, (String) param); } } return pstmt; } }