Java tutorial
/************************************************************************************** * Copyright (C) 2008 EsperTech, Inc. All rights reserved. * * http://esper.codehaus.org * * http://www.espertech.com * * ---------------------------------------------------------------------------------- * * The software in this package is published under the terms of the GPL license * * a copy of which has been included with this distribution in the license.txt file. * **************************************************************************************/ package com.espertech.esperio.db; import com.espertech.esper.client.ConfigurationDBRef; import java.util.Properties; import java.util.List; import java.util.ArrayList; import java.sql.*; import org.apache.commons.logging.Log; import org.apache.commons.logging.LogFactory; public class SupportDatabaseService { private static final Log log = LogFactory.getLog(SupportDatabaseService.class); public final static String DBNAME_FULLURL = "mydb"; public final static String DBNAME_PARTURL = "mydb_part"; public final static String DBUSER = "root"; public final static String DBPWD = "password"; public final static String DRIVER = "com.mysql.jdbc.Driver"; public final static String FULLURL = "jdbc:mysql://localhost/test?user=root&password=password"; public final static String PARTURL = "jdbc:mysql://localhost/test"; public static ConfigurationDBRef makeDBConfig() { ConfigurationDBRef configDB = new ConfigurationDBRef(); configDB.setDriverManagerConnection(SupportDatabaseService.DRIVER, SupportDatabaseService.FULLURL, new Properties()); return configDB; } public static void truncateTable(String tableName) throws SQLException { Connection connection = getConnection(PARTURL, DBUSER, DBPWD); connection.setAutoCommit(true); Statement stmt = connection.createStatement(); String sql = "delete from " + tableName; log.info("Executing sql : " + sql); stmt.executeUpdate(sql); stmt.close(); connection.close(); } public static Object[][] readAll(String tableName) throws SQLException { Connection connection = getConnection(PARTURL, DBUSER, DBPWD); connection.setAutoCommit(true); Statement stmt = connection.createStatement(); String sql = "select * from " + tableName; log.info("Executing sql : " + sql); ResultSet resultSet = stmt.executeQuery(sql); List<Object[]> rows = new ArrayList<Object[]>(); while (resultSet.next()) { List<Object> row = new ArrayList<Object>(); for (int i = 0; i < resultSet.getMetaData().getColumnCount(); i++) { row.add(resultSet.getObject(i + 1)); } rows.add(row.toArray()); } Object[][] arr = new Object[rows.size()][]; for (int i = 0; i < rows.size(); i++) { arr[i] = rows.get(i); } stmt.close(); connection.close(); return arr; } /** * Strictly for use in regression testing, this method provides a connection via driver manager. * @param url url * @param username user * @param password password * @return connection */ public static Connection getConnection(String url, String username, String password) { log.info("Creating new connection instance for pool for url " + url); Driver d; try { d = (Driver) Class.forName(DRIVER).newInstance(); DriverManager.registerDriver(d); } catch (Exception e) { String message = "Failed to load and register driver class:" + e.getMessage(); log.error(message, e); throw new RuntimeException(message, e); } Connection connection; try { connection = DriverManager.getConnection(url, username, password); } catch (SQLException e) { String message = "Failed to obtain a database connection using url '" + url + "' and user '" + username + "' :" + e.getMessage(); log.error(message, e); throw new RuntimeException(message, e); } try { connection.setAutoCommit(false); } catch (SQLException e) { String message = "Failed to set auto-commit on connection '" + url + "' and user '" + username + "' :" + e.getMessage(); log.error(message, e); throw new RuntimeException(message, e); } return connection; } }