SQL-Scripte über JDBC auf in-memory Derby-DB ausführen

Frauenkirche Dresden, by Christoph Burmeister (own photo)

Frauenkirche Dresden, by Christoph Burmeister (own photo)


Die DerbyDB (oder JavaDB in der Oracle-Variante) ist ganz interessant, wenn man kein gewaltiges DBMS benötigt… zum Beispiel beim Unit-Testing o.ä. Per Default lassen sich über JDBC nur einzelne Statements ausführen. Ein ScriptRunner könnte das ändern.

Zuerst die Implementierung des Database-Providers:

import java.io.BufferedReader;
import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileReader;
import java.io.IOException;
import java.net.URL;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;

import org.apache.log4j.Logger;

public class DerbyDatabaseProvider {

	private static final Logger logger = Logger.getLogger(DerbyDatabaseProvider.class);

	private String driver = "org.apache.derby.jdbc.EmbeddedDriver";
	private String protocol = "jdbc:derby:memory:"; // in-memory-database!

	Properties connectionProperties;
	Connection connection;
	Statement statement;

	public void startup(String dbName) {

		loadDriver(); // just to make sure, the appropr. driver is in classpath,
						// system-properties must be set before!

		connectionProperties = new Properties();
		connectionProperties.setProperty("user", "guest");
		connectionProperties.setProperty("password", "spongebob");

		String connectionString = protocol + dbName + ";create=true";
		logger.info("connection-string >>" + connectionString + "<<");

		try {
			connection = DriverManager.getConnection(connectionString, connectionProperties);
			statement = connection.createStatement();
		} catch (SQLException e) {
			logger.error(e.getMessage());
		}
	}

	/*
	 * The JDBC driver is loaded by loading its class. If you are using JDBC 4.0
	 * (Java SE 6) or newer, JDBC drivers may be automatically loaded, making
	 * this code optional.
	 * 
	 * In an embedded environment, this will also start up the Derby engine
	 * (though not any databases), since it is not already running. In a client
	 * environment, the Derby engine is being run by the network server
	 * framework.
	 * 
	 * In an embedded environment, any static Derby system properties must be
	 * set before loading the driver to take effect.
	 */
	private void loadDriver() {

		try {
			Class.forName(driver).newInstance();
			logger.info("Loaded the appropriate driver: " + driver);
		} catch (ClassNotFoundException e) {
			logger.error(e.getMessage());
		} catch (InstantiationException e) {
			logger.error(e.getMessage());
		} catch (IllegalAccessException e) {
			logger.error(e.getMessage());
		}
	}

	public void deploy(URL&#91;&#93; urls) {
		for (int i = 0; i < urls.length; i++) {
			File sqlFile = new File(urls&#91;i&#93;.getPath());
			try {
				executeSqlFile(sqlFile);
			} catch (SQLException e) {
				logger.error(e.getMessage());
			} catch (FileNotFoundException e) {
				logger.error(e.getMessage());
			} catch (IOException e) {
				logger.error(e.getMessage());
			}
		}
	}

	/**
	 * executes a sql-script by using external scriptRunner
	 * 
	 * @param sqlFile
	 *            the file to execute
	 * @throws SQLException
	 * @throws IOException
	 * @throws FileNotFoundException
	 */
	private void executeSqlFile(File sqlFile) throws FileNotFoundException, IOException, SQLException {
		JdbcScriptRunner scriptRunner = new JdbcScriptRunner(connection, false, true);
		scriptRunner.setDelimiter("/", false);
		scriptRunner.runScript(new BufferedReader(new FileReader(sqlFile)));
	}

	public ResultSet query(String query) {
		ResultSet result = null;
		try {
			result = statement.executeQuery(query);
		} catch (SQLException e) {
			logger.error(e.getMessage());
		}
		return result;
	}

	public void clean(String dbName) {

		// just for in-memory-databases!!!
		try {
			DriverManager.getConnection("jdbc:derby:memory:" + dbName + ";drop=true", connectionProperties);
		} catch (SQLException e) {
			if (((e.getErrorCode() == 45000) && ("08006".equals(e.getSQLState())))) {
				logger.info(dbName + " dropped successfully");
			} else {
				logger.error(e.getMessage());
			}
		}
	}

	/**
	 * shutdown the whole system!
	 */
	public void shutdown() {
		try {
			DriverManager.getConnection("jdbc:derby:;shutdown=true", connectionProperties);
		} catch (SQLException e) {
			if (((e.getErrorCode() == 50000) && ("XJ015".equals(e.getSQLState())))) {
				logger.info("derby shutdown normally");
			} else {
				logger.error(e.getMessage());
			}
		}
	}
}
&#91;/sourcecode&#93;

Ein ScriptRunner für die Ausführung von SQLScripts (dankenswerter Weise von <a href="http://pastebin.com/f10584951" target="_blank">Clinton Begin unter der Apache2-Lizenz</a> freigegeben)


/*
 * Slightly modified version of the com.ibatis.common.jdbc.ScriptRunner class
 * from the iBATIS Apache project. Only removed dependency on Resource class
 * and a constructor 
 */
/*
 *  Copyright 2004 Clinton Begin
 *
 *  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.
 */

import java.io.IOException;
import java.io.LineNumberReader;
import java.io.PrintWriter;
import java.io.Reader;
import java.sql.*;

/**
 * Tool to run database scripts
 */
public class JdbcScriptRunner {

	private static final String DEFAULT_DELIMITER = ";";

	private Connection connection;

	private boolean stopOnError;
	private boolean autoCommit;

	private PrintWriter logWriter = new PrintWriter(System.out);
	private PrintWriter errorLogWriter = new PrintWriter(System.err);

	private String delimiter = DEFAULT_DELIMITER;
	private boolean fullLineDelimiter = false;

	/**
	 * Default constructor
	 */
	public JdbcScriptRunner(Connection connection, boolean autoCommit,
			boolean stopOnError) {
		this.connection = connection;
		this.autoCommit = autoCommit;
		this.stopOnError = stopOnError;
	}

	public void setDelimiter(String delimiter, boolean fullLineDelimiter) {
		this.delimiter = delimiter;
		this.fullLineDelimiter = fullLineDelimiter;
	}

	/**
	 * Setter for logWriter property
	 * 
	 * @param logWriter
	 *            - the new value of the logWriter property
	 */
	public void setLogWriter(PrintWriter logWriter) {
		this.logWriter = logWriter;
	}

	/**
	 * Setter for errorLogWriter property
	 * 
	 * @param errorLogWriter
	 *            - the new value of the errorLogWriter property
	 */
	public void setErrorLogWriter(PrintWriter errorLogWriter) {
		this.errorLogWriter = errorLogWriter;
	}

	/**
	 * Runs an SQL script (read in using the Reader parameter)
	 * 
	 * @param reader
	 *            - the source of the script
	 */
	public void runScript(Reader reader) throws IOException, SQLException {
		try {
			boolean originalAutoCommit = connection.getAutoCommit();
			try {
				if (originalAutoCommit != this.autoCommit) {
					connection.setAutoCommit(this.autoCommit);
				}
				runScript(connection, reader);
			} finally {
				connection.setAutoCommit(originalAutoCommit);
			}
		} catch (IOException e) {
			throw e;
		} catch (SQLException e) {
			throw e;
		} catch (Exception e) {
			throw new RuntimeException("Error running script.  Cause: " + e, e);
		}
	}

	/**
	 * Runs an SQL script (read in using the Reader parameter) using the
	 * connection passed in
	 * 
	 * @param conn
	 *            - the connection to use for the script
	 * @param reader
	 *            - the source of the script
	 * @throws SQLException
	 *             if any SQL errors occur
	 * @throws IOException
	 *             if there is an error reading from the Reader
	 */
	private void runScript(Connection conn, Reader reader) throws IOException,
			SQLException {
		StringBuffer command = null;
		try {
			LineNumberReader lineReader = new LineNumberReader(reader);
			String line = null;
			while ((line = lineReader.readLine()) != null) {
				if (command == null) {
					command = new StringBuffer();
				}
				String trimmedLine = line.trim();
				if (trimmedLine.startsWith("--")) {
					println(trimmedLine);
				} else if (trimmedLine.length() < 1
						|| trimmedLine.startsWith("//")) {
					// Do nothing
				} else if (trimmedLine.length() < 1
						|| trimmedLine.startsWith("--")) {
					// Do nothing
				} else if (!fullLineDelimiter
						&& trimmedLine.endsWith(getDelimiter())
						|| fullLineDelimiter
						&& trimmedLine.equals(getDelimiter())) {
					command.append(line.substring(0, line
							.lastIndexOf(getDelimiter())));
					command.append(" ");
					Statement statement = conn.createStatement();

					println(command);

					boolean hasResults = false;
					if (stopOnError) {
						hasResults = statement.execute(command.toString());
					} else {
						try {
							statement.execute(command.toString());
						} catch (SQLException e) {
							e.fillInStackTrace();
							printlnError("Error executing: " + command);
							printlnError(e);
						}
					}

					if (autoCommit && !conn.getAutoCommit()) {
						conn.commit();
					}

					ResultSet rs = statement.getResultSet();
					if (hasResults && rs != null) {
						ResultSetMetaData md = rs.getMetaData();
						int cols = md.getColumnCount();
						for (int i = 0; i < cols; i++) {
							String name = md.getColumnLabel(i);
							print(name + "\t");
						}
						println("");
						while (rs.next()) {
							for (int i = 0; i < cols; i++) {
								String value = rs.getString(i);
								print(value + "\t");
							}
							println("");
						}
					}

					command = null;
					try {
						statement.close();
					} catch (Exception e) {
						// Ignore to workaround a bug in Jakarta DBCP
					}
					Thread.yield();
				} else {
					command.append(line);
					command.append(" ");
				}
			}
			if (!autoCommit) {
				conn.commit();
			}
		} catch (SQLException e) {
			e.fillInStackTrace();
			printlnError("Error executing: " + command);
			printlnError(e);
			throw e;
		} catch (IOException e) {
			e.fillInStackTrace();
			printlnError("Error executing: " + command);
			printlnError(e);
			throw e;
		} finally {
			conn.rollback();
			flush();
		}
	}

	private String getDelimiter() {
		return delimiter;
	}

	private void print(Object o) {
		if (logWriter != null) {
			System.out.print(o);
		}
	}

	private void println(Object o) {
		if (logWriter != null) {
			logWriter.println(o);
		}
	}

	private void printlnError(Object o) {
		if (errorLogWriter != null) {
			errorLogWriter.println(o);
		}
	}

	private void flush() {
		if (logWriter != null) {
			logWriter.flush();
		}
		if (errorLogWriter != null) {
			errorLogWriter.flush();
		}
	}
}
&#91;/sourcecode&#93;

die Maven-Dependencies halten sich auch in Grenzen:
&#91;sourcecode language="xml"&#93;
         <dependencies>
		<dependency>
			<groupId>org.apache.derby</groupId>
			<artifactId>derby</artifactId>
			<version>10.8.2.2</version>
		</dependency>
		<dependency>
			<groupId>junit</groupId>
			<artifactId>junit</artifactId>
			<version>4.8.2</version>
			<scope>test</scope>
		</dependency>
		<dependency>
			<groupId>log4j</groupId>
			<artifactId>log4j</artifactId>
			<version>1.2.14</version>
		</dependency>
	</dependencies>

Jetzt noch ein Test, der einfach zwei Scripts ausführt und anschließend einen Query durchführt und die Resultate vergleicht.

import java.net.MalformedURLException;
import java.net.URL;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import junit.framework.Assert;

import org.apache.log4j.Logger;
import org.junit.Before;
import org.junit.Test;

public class DerbyDatabaseProviderTest {

	private static final Logger logger = Logger.getLogger(DerbyDatabaseProviderTest.class);

	private DerbyDatabaseProvider provider;
	private static final String dbName = "derbyTest";
	private URL[] urls = new URL[2];
	List<String> expectedJediList = new ArrayList<String>();
	List<String> expectedSithList = new ArrayList<String>();
	List<String> expectedShipList = new ArrayList<String>();

	@Before
	public void setUp() throws MalformedURLException {
		provider = new DerbyDatabaseProvider();
		urls[0] = new URL("file:src/test/resources/sql/createTable.sql");
		urls[1] = new URL("file:src/test/resources/sql/insertRows.sql");
		
		expectedJediList.add("Skywalker");
		expectedJediList.add("Organa");
		expectedJediList.add("Kenobi");	
		
		expectedSithList.add("Vader");
		expectedSithList.add("Maul");
		expectedSithList.add("Palpatine");
		
		expectedShipList.add("X-Wing");
		expectedShipList.add("Y-Wing");
		expectedShipList.add("Tie-Fighter");
		
		// setting some properties for the derby-dbms
		System.setProperty("derby.system.home", "target/derby");
		System.setProperty("derby.stream.error.file", "derby.log"); // default
		System.setProperty("derby.infolog.append", "true");
		System.setProperty("derby.authentication.provider", "BUILTIN");
		System.setProperty("derby.connection.requireAuthentication", "true");
		System.setProperty("derby.user.guest", "spongebob");
	}

	@Test
	public void derbyTest() throws SQLException {
		List<String> actualJediList = new ArrayList<String>();
		List<String> actualSithList = new ArrayList<String>();
		List<String> actualShipList = new ArrayList<String>();
		
		logger.info("startup()");
		provider.startup(dbName);

		logger.info("deploy()");
		provider.deploy(urls);

		logger.info("query()");
		ResultSet resultJedis = provider.query("SELECT * FROM jedis ORDER BY ID");
		while (resultJedis.next()) {
			String name = resultJedis.getString(2);// hint: coloum starts with 1
			actualJediList.add(name);
			logger.info("query-result >>" + name + "<<");
		}
		Assert.assertEquals(expectedJediList, actualJediList);
		
		ResultSet resultSiths = provider.query("SELECT * FROM siths ORDER BY ID");
		while (resultSiths.next()) {
			String name = resultSiths.getString(2);// hint: coloum starts with 1
			actualSithList.add(name);
			logger.info("query-result >>" + name + "<<");
		}
		Assert.assertEquals(expectedSithList, actualSithList);
		
		ResultSet resultShips = provider.query("SELECT * FROM ships ORDER BY ID");
		while (resultShips.next()) {
			String name = resultShips.getString(2);// hint: coloum starts with 1
			actualShipList.add(name);
			logger.info("query-result >>" + name + "<<");
		}
		Assert.assertEquals(expectedShipList, actualShipList);

		logger.info("clean()");
		provider.clean(dbName);

		logger.info("shutdown()");
		provider.shutdown();

	}
}
&#91;/sourcecode&#93;>

die beiden SQL-Scripts sehen so aus:
createTables.sql

-- create table for the jedi-knights
CREATE TABLE jedis
(
	id INT, 
	name VARCHAR(50)
)
/

-- create table for the sith-lords
CREATE TABLE siths
(
	id INT, 
	name VARCHAR(50)
)
/

-- create table for the ships
CREATE TABLE ships 
(
	id INT, 
	name VARCHAR(50)
)
/

und insertRows.sql

-- inserting the good ones
INSERT INTO jedis (ID, NAME) 
	VALUES (1, 'Skywalker')
/
INSERT INTO jedis (ID, NAME) 
	VALUES (2, 'Organa')
/
INSERT INTO jedis (ID, NAME) 
	VALUES (3, 'Kenobi')
/

-- inserting the bad ones
INSERT INTO siths (ID, NAME) 
	VALUES (1, 'Vader')
/
INSERT INTO siths (ID, NAME) 
	VALUES (2, 'Maul')
/
INSERT INTO siths (ID, NAME) 
	VALUES (3, 'Palpatine')
/

-- inserting the ships 
INSERT INTO ships (ID, NAME) 
	VALUES (1, 'X-Wing')
/
INSERT INTO ships (ID, NAME) 
	VALUES (2, 'Y-Wing')
/
INSERT INTO ships (ID, NAME) 
	VALUES (3, 'Tie-Fighter')
/

Die Delimeter sind erstmal auf Slash „/“ gesetzt, per Default wird aber ein Semikolon „;“ verwendet.

Nice 🙂