Java tutorial
/** * Licensed to Cloudera, Inc. under one * or more contributor license agreements. See the NOTICE file * distributed with this work for additional information * regarding copyright ownership. Cloudera, Inc. 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 com.cloudera.sqoop.testutil; import java.util.Arrays; import java.sql.Connection; import java.sql.DriverManager; import java.sql.SQLException; import java.sql.Statement; import org.apache.commons.logging.Log; import org.apache.commons.logging.LogFactory; import org.hsqldb.Server; import com.cloudera.sqoop.SqoopOptions; import com.cloudera.sqoop.manager.ConnManager; import com.cloudera.sqoop.manager.HsqldbManager; /** * Create a simple hsqldb server and schema to use for testing. */ public class HsqldbTestServer { public static final Log LOG = LogFactory.getLog(HsqldbTestServer.class.getName()); // singleton server instance. private static Server server; // if -Dhsql.server.host hasn't been set to something like // hsql://localhost.localdomain/ a defaul in-mem DB will be used private static final String IN_MEM = "mem:"; public static String getServerHost() { String host = System.getProperty("hsql.server.host", IN_MEM); if (!host.endsWith("/")) { host += "/"; } return host; } private static boolean inMemoryDB = IN_MEM.equals(getServerHost()); // Database name can be altered too private static final String DATABASE_NAME = System.getProperty("hsql.database.name", "db1"); // hsqldb always capitalizes table and column names private static final String DUMMY_TABLE_NAME = "TWOINTTABLE"; private static final String[] TWO_INT_TABLE_FIELDS = { "INTFIELD1", "INTFIELD2", }; private static final String EMPLOYEE_TABLE_NAME = "EMPLOYEES"; private static final String DB_URL = "jdbc:hsqldb:" + getServerHost() + DATABASE_NAME; private static final String DRIVER_CLASS = "org.hsqldb.jdbcDriver"; // all user-created HSQLDB tables are in the "PUBLIC" schema when connected // to a database. private static final String HSQLDB_SCHEMA_NAME = "PUBLIC"; public static String getSchemaName() { return HSQLDB_SCHEMA_NAME; } public static String[] getFieldNames() { return Arrays.copyOf(TWO_INT_TABLE_FIELDS, TWO_INT_TABLE_FIELDS.length); } public static String getUrl() { return DB_URL; } public static String getTableName() { return DUMMY_TABLE_NAME; } public static String getDatabaseName() { return DATABASE_NAME; } /** * start the server. */ public void start() { if (null == server) { LOG.info("Starting new hsqldb server; database=" + DATABASE_NAME); String tmpDir = System.getProperty("test.build.data", "/tmp/"); String dbLocation = tmpDir + "/sqoop/testdb.file"; if (inMemoryDB) { dbLocation = IN_MEM; } server = new Server(); server.setDatabaseName(0, DATABASE_NAME); server.putPropertiesFromString("database.0=" + dbLocation + ";no_system_exit=true"); server.start(); } } public Connection getConnection() throws SQLException { try { Class.forName(DRIVER_CLASS); } catch (ClassNotFoundException cnfe) { LOG.error("Could not get connection; driver class not found: " + DRIVER_CLASS); return null; } Connection connection = DriverManager.getConnection(DB_URL); connection.setAutoCommit(false); return connection; } /** * Returns database URL for the server instance. * @return String representation of DB_URL */ public static String getDbUrl() { return DB_URL; } /** * Create a table. */ public void createSchema() throws SQLException { Connection connection = null; Statement st = null; try { connection = getConnection(); st = connection.createStatement(); st.executeUpdate("DROP TABLE " + DUMMY_TABLE_NAME + " IF EXISTS"); st.executeUpdate("CREATE TABLE " + DUMMY_TABLE_NAME + "(intField1 INT, intField2 INT)"); connection.commit(); } finally { if (null != st) { st.close(); } if (null != connection) { connection.close(); } } } /** * @return the sum of the integers in the first column of TWOINTTABLE. */ public static int getFirstColSum() { return 1 + 3 + 5 + 7; } /** * Fill the table with some data. */ public void populateData() throws SQLException { Connection connection = null; Statement st = null; try { connection = getConnection(); st = connection.createStatement(); st.executeUpdate("INSERT INTO " + DUMMY_TABLE_NAME + " VALUES(1, 8)"); st.executeUpdate("INSERT INTO " + DUMMY_TABLE_NAME + " VALUES(3, 6)"); st.executeUpdate("INSERT INTO " + DUMMY_TABLE_NAME + " VALUES(5, 4)"); st.executeUpdate("INSERT INTO " + DUMMY_TABLE_NAME + " VALUES(7, 2)"); connection.commit(); } finally { if (null != st) { st.close(); } if (null != connection) { connection.close(); } } } public void createEmployeeDemo() throws SQLException, ClassNotFoundException { Class.forName(DRIVER_CLASS); Connection connection = null; Statement st = null; try { connection = getConnection(); st = connection.createStatement(); st.executeUpdate("DROP TABLE " + EMPLOYEE_TABLE_NAME + " IF EXISTS"); st.executeUpdate( "CREATE TABLE " + EMPLOYEE_TABLE_NAME + "(emp_id INT NOT NULL PRIMARY KEY, name VARCHAR(64))"); st.executeUpdate("INSERT INTO " + EMPLOYEE_TABLE_NAME + " VALUES(1, 'Aaron')"); st.executeUpdate("INSERT INTO " + EMPLOYEE_TABLE_NAME + " VALUES(2, 'Joe')"); st.executeUpdate("INSERT INTO " + EMPLOYEE_TABLE_NAME + " VALUES(3, 'Jim')"); st.executeUpdate("INSERT INTO " + EMPLOYEE_TABLE_NAME + " VALUES(4, 'Lisa')"); connection.commit(); } finally { if (null != st) { st.close(); } if (null != connection) { connection.close(); } } } /** * Delete any existing tables. */ public void dropExistingSchema() throws SQLException { ConnManager mgr = getManager(); String[] tables = mgr.listTables(); if (null != tables) { Connection conn = mgr.getConnection(); for (String table : tables) { Statement s = conn.createStatement(); try { s.executeUpdate("DROP TABLE " + table); conn.commit(); } finally { s.close(); } } } } /** * Creates an hsqldb server, fills it with tables and data. */ public void resetServer() throws ClassNotFoundException, SQLException { start(); dropExistingSchema(); createSchema(); populateData(); } public SqoopOptions getSqoopOptions() { return new SqoopOptions(HsqldbTestServer.getUrl(), HsqldbTestServer.getTableName()); } public ConnManager getManager() { return new HsqldbManager(getSqoopOptions()); } }