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 de.codecentric.multitool.db; import java.io.BufferedReader; import java.io.ByteArrayInputStream; import java.io.File; import java.io.FileInputStream; import java.io.FileReader; import java.io.InputStream; import java.io.InputStreamReader; import java.io.Reader; import java.io.StringWriter; import java.lang.reflect.Field; import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import java.util.HashMap; import java.util.Map; import java.util.NoSuchElementException; import org.apache.commons.lang.StringUtils; import org.apache.log4j.Logger; import org.dbunit.DatabaseUnitException; import org.dbunit.database.DatabaseConnection; import org.dbunit.database.QueryDataSet; import org.dbunit.dataset.IDataSet; import org.dbunit.dataset.xml.FlatXmlDataSet; import org.dbunit.dataset.xml.FlatXmlProducer; import org.dbunit.dataset.xml.XmlDataSet; import org.dbunit.operation.DatabaseOperation; import org.xml.sax.InputSource; /** * Eine Robot Library fr DBUnit Keywords * * @author markus.bonsch * */ public class DBUnitLibrary { private static final int POLL_INTERVAL_MS = 1000; private static final Logger LOGGER = Logger.getLogger(DBUnitLibrary.class); private static final String XML_FORMAT_FLAT = "flat"; private static final String XML_FORMAT_NORMAL = "normal"; private Map<String, Connection> connectionMap = new HashMap<String, Connection>(); public static final String ORACLE_DBMS_TYPE = "ORACLE"; public static final String DB2_DBMS_TYPE = "DB2"; public static final String MSSQL_DBMS_TYPE = "MSSQL"; public static final String MYSQL_DBMS_TYPE = "MYSQL"; private String curDbmsType; /** * Es wird eine Verbindung zu der EAI Datenbank aufgebaut und die * Verbindungsparameter fuer die benoetigten 'DataSources' aus der * 'OS_DEPLOY_CONFIG' Tabelle ausgelesen. * */ public DBUnitLibrary(String dbmsType) throws Exception { loadDBDriver(dbmsType); } /** * Es wird eine Verbindung zu der EAI Datenbank aufgebaut und die * Verbindungsparameter fuer die benoetigten 'DataSources' aus der * 'OS_DEPLOY_CONFIG' Tabelle ausgelesen. * */ public DBUnitLibrary(String dbmsType, String dsName, String connectString, String dbUser, String dbPassword) throws Exception { loadDBDriver(dbmsType); openConnection(dsName, connectString, dbUser, dbPassword); } public void openConnection(String dsName, String connectString, String dbUser, String dbPassword) throws Exception { if (!(connectionMap.containsKey(dsName) && connectionMap.get(dsName).isValid(0))) { Connection connection = DriverManager.getConnection(connectString, dbUser, dbPassword); if (MSSQL_DBMS_TYPE.equals(curDbmsType)) { connection.setAutoCommit(false); } else if (MYSQL_DBMS_TYPE.equals(curDbmsType)) { connection.setAutoCommit(false); } connectionMap.put(dsName, connection); } } /** * Fhrt ein DB Clean & Insert aus einer Quell-XML-Datei aus. XML * Format:Normal (DB Attribute sind XML Elemente) * * Beispiel: | Insert From File Into DB | ACSD_FULL | * ${RESOURCES}/data/trades/${templateOrdner}/data-out.xml | */ public void insertFromFileIntoDB(String dsName, String fileName) throws Exception { insertFromFileIntoDB(dsName, fileName, XML_FORMAT_FLAT); } /** * Fhrt ein DB Clean & Insert aus einer Quell-XML-Datei aus. * * Mgliche Datenformate (siehe auch * [http://www.dbunit.org/components.html]): | normal | Daten werden als * XML-Tags erwartet | | flat | Daten werden als XML-Attribute erwartet | * * Beispiel: | Insert From File Into DB | ACSD_FULL | * ${RESOURCES}/data/trades/${templateOrdner}/data-out.xml | flat | */ public void insertFromFileIntoDB(String dsName, String fileName, String xmlFormat) throws Exception { File file = new File(fileName); InputStream inputStream = new FileInputStream(file); Reader reader = new InputStreamReader(inputStream, "UTF-8"); IDataSet dataSet; if (xmlFormat.equalsIgnoreCase(XML_FORMAT_NORMAL)) { dataSet = new XmlDataSet(reader); } else { FlatXmlProducer producer = new FlatXmlProducer(new InputSource(reader)); producer.setColumnSensing(true); dataSet = new FlatXmlDataSet(producer); } insert(dsName, dataSet); } /** * Fhrt ein DB Clean & Insert aus einem XML-String aus. XML-Format:Normal * (DB Attribute sind XML Elemente) * * Beispiel: | Insert From String Into DB | ACSD_FULL | ${xml} | */ public void insertFromStringIntoDB(String dsName, String xmlContent) throws Exception { insertFromStringIntoDB(dsName, xmlContent, XML_FORMAT_FLAT); } /** * Fuehrt ein DB Clean & Insert aus einem XML-String aus. * * Mgliche Datenformate (siehe auch * [http://www.dbunit.org/components.html]): | normal | Daten werden als * XML-Tags erwartet | | flat | Daten werden als XML-Attribute erwartet | * * Beispiel: | Insert From String Into DB | ACSD_FULL | ${xml} | flat | */ public void insertFromStringIntoDB(String dsName, String xmlContent, String xmlFormat) throws Exception { InputStream is = new ByteArrayInputStream(xmlContent.getBytes("UTF-8")); IDataSet dataSet; if (xmlFormat.equalsIgnoreCase(XML_FORMAT_NORMAL)) { dataSet = new XmlDataSet(is); } else { FlatXmlProducer producer = new FlatXmlProducer(new InputSource(is)); producer.setColumnSensing(true); dataSet = new FlatXmlDataSet(producer); } insert(dsName, dataSet); } /** * Fuehrt ein DB Clean & Insert aus einem XML-String aus. * * Mgliche Datenformate (siehe auch * [http://www.dbunit.org/components.html]): | normal | Daten werden als * XML-Tags erwartet | | flat | Daten werden als XML-Attribute erwartet | * * Beispiel: | Insert From String Into DB | ACSD_FULL | ${xml} | flat | * CLEAN_INSERT | */ public void insertFromStringIntoDB(String dsName, String xmlContent, String xmlFormat, String operationName) throws Exception { InputStream is = new ByteArrayInputStream(xmlContent.getBytes("UTF-8")); IDataSet dataSet; if (xmlFormat.equalsIgnoreCase(XML_FORMAT_NORMAL)) { dataSet = new XmlDataSet(is); } else { FlatXmlProducer producer = new FlatXmlProducer(new InputSource(is)); producer.setColumnSensing(true); dataSet = new FlatXmlDataSet(producer); } execute(operationName, dsName, dataSet); } /** * Schliesst alle offenen Datenbankverbindungen: */ public void releaseAllDbConnections() { for (Connection con : connectionMap.values()) { try { if (con != null) { con.close(); } } catch (SQLException e) { LOGGER.info(e); } } connectionMap.clear(); } public String selectAsXmlResult(String dsName, String tableName, String query) throws Exception { return selectAsXmlResult(dsName, tableName, query, XML_FORMAT_FLAT, "1", "0"); } /** * Liefert ein DB Unit XML-Result feur eine SQL Abfrage in einem Timeout * (DB-Unit XML-Format: Normal) * * Beispiel: | ${xml} = | Select As Xml Result | ACSD_FULL | MYTABLE | * select * from MYTABLE where id = 1 | 30 | */ public String selectAsXmlResult(String dsName, String tableName, String query, String timeout) throws Exception { return selectAsXmlResult(dsName, tableName, query, XML_FORMAT_FLAT, "1", timeout); } /** * Liefert ein DB Unit XML-Result feur eine SQL Abfrage. * * Beispiel: | ${xml} = | Select As Xml Result | ACSD_FULL | MYTABLE | * select * from MYTABLE where id = 1 | flat | 2 | 30 | */ public String selectAsXmlResult(String dsName, String tableName, String query, String expectedRowCountString, String timeout) throws Exception { return selectAsXmlResult(dsName, tableName, query, XML_FORMAT_FLAT, expectedRowCountString, timeout); } /** * Liefert ein DB Unit XML-Result feur eine SQL Abfrage. * * Beispiel: | ${xml} = | Select As Xml Result | ACSD_FULL | MYTABLE | * select * from MYTABLE where id = 1 | flat | 2 | 30 | */ public String selectAsXmlResult(String dsName, String tableName, String query, String xmlFormat, String expectedRowCountString, String timeout) throws Exception { DatabaseConnection databaseConnection = getDatabaseConnection(dsName); StringWriter sw = new StringWriter(); int t = Integer.parseInt(timeout); int rowCount = 0; int expectedRowCount = Integer.parseInt(expectedRowCountString); long start = System.currentTimeMillis(); long now = System.currentTimeMillis(); QueryDataSet dataset = null; while (rowCount < expectedRowCount && ((now - start) / 1000) < t) { dataset = new QueryDataSet(databaseConnection); dataset.addTable(tableName, query); rowCount = dataset.getTable(tableName).getRowCount(); Thread.sleep(POLL_INTERVAL_MS); now = System.currentTimeMillis(); } if (xmlFormat.equalsIgnoreCase(XML_FORMAT_NORMAL)) XmlDataSet.write(dataset, sw, "UTF-8"); else FlatXmlDataSet.write(dataset, sw, "UTF-8"); return sw.toString(); } /** * Liest einen Einzelwert aus einer Tabellenabfrage * * Beispiel: | ${value} = | Read Single Value From Table | ACSD_FULL | * MYTABLE | STATUS | */ public String readSingleValueFromTable(String dsName, String table, String column) throws IllegalArgumentException, SQLException { return readSingleValueFromTable(dsName, table, column, null); } /** * Liest einen Einzelwert aus einer Tabellenabfrage * * Beispiel: | ${value} = | Read Single Value From Table | ACSD_FULL | * MYTABLE | STATUS | id = 5 | */ public String readSingleValueFromTable(String dsName, String table, String column, String whereStatment) throws IllegalArgumentException, SQLException { Statement statement = null; ResultSet rs = null; try { statement = getConnection(dsName).createStatement(); String sql = "SELECT " + column + " FROM " + table + (StringUtils.isNotEmpty(whereStatment) ? " WHERE " + whereStatment : ""); statement.execute(sql); rs = statement.getResultSet(); if (rs.next()) { if (rs.getObject(1) == null) return null; else return rs.getObject(1).toString(); } throw new NoSuchElementException(sql); } finally { if (rs != null) { rs.close(); } if (statement != null) { statement.close(); } } } /** * Prft nach, ob ein Wert in der Ergebnissmenge vorhanden ist * * Beispiel: | Should Contain Value From Table | ACSD_FULL | MYTABLE | * STATUS | id = 5 | Running | 20000 | */ public void shouldContainValueFromTable(String dsName, String table, String column, String whereStatment, String expectedValue, String timeout) throws IllegalArgumentException, SQLException, InterruptedException { long timeoutTimestamp = System.currentTimeMillis() + (Long.parseLong(timeout) * 1000); do { String value = null; try { value = readSingleValueFromTable(dsName, table, column, whereStatment); } catch (NoSuchElementException e) { // try again } if ((value == null && expectedValue == null) || (value != null && value.toString().contains(expectedValue))) { // correct value returned return; } Thread.sleep(POLL_INTERVAL_MS); } while (timeoutTimestamp > System.currentTimeMillis()); throw new IllegalStateException("Query timed out after " + timeout + "ms"); } /** * Liest einen Einzelwert aus einer Tabellenabfrage * * Beispiel: | Should Match Single Value From Table | ACSD_FULL | MYTABLE | * STATUS | id = 5 | Running | 20000 | */ public void shouldMatchSingleValueFromTable(String dsName, String table, String column, String whereStatment, String expectedValue, String timeout) throws IllegalArgumentException, SQLException, InterruptedException { long timeoutTimestamp = System.currentTimeMillis() + (Long.parseLong(timeout) * 1000); do { String value = readSingleValueFromTable(dsName, table, column, whereStatment); if ((value == null && expectedValue == null) || (value != null && value.toString().equals(expectedValue))) { // correct value returned return; } Thread.sleep(POLL_INTERVAL_MS); } while (timeoutTimestamp > System.currentTimeMillis()); throw new IllegalStateException("Query timed out after " + timeout + "ms"); } /** * * @param dsName * EAI Datasource Name * @param sql * SQL Statment * @throws InvalidPropertyException * @throws SQLException */ /** * Liest einen Einzelwert aus einer Tabellenabfrage * * Beispiel: | Execute SQL | ACSD_FULL | DELETE FROM table | */ public void executeSQL(String dsName, String sql) throws IllegalArgumentException, SQLException { Statement statement = null; try { Connection con = getConnection(dsName); statement = con.createStatement(); statement.execute(sql); con.commit(); } finally { if (statement != null) { statement.close(); } } } /** * @param dsName * EAI Datasource Name * @param filePath * File to Sql */ /** * Schreibt den Inhalt der Sql Datei in die Datenbank * * Beispiel: | Execute SQL | ACSD_FULL | filePath | * * @throws Exception */ public void executeSQLFromFile(String dsName, String filePath) throws Exception { String s = new String(); StringBuffer sb = new StringBuffer(); try { FileReader fr = new FileReader(new File(filePath)); BufferedReader br = new BufferedReader(fr); while ((s = br.readLine()) != null) { sb.append(s); } br.close(); String[] inst = sb.toString().split(";"); for (int i = 0; i < inst.length; i++) { if (!inst[i].trim().equals("")) { executeSQL(dsName, inst[i]); } } } catch (Exception e) { System.out.println("*** Error : " + e.toString()); System.out.println("*** "); System.out.println("*** Error : "); e.printStackTrace(); System.out.println("################################################"); System.out.println(sb.toString()); throw new Exception(e.getCause()); } } private void insert(String dsName, IDataSet ds) throws DatabaseUnitException, SQLException, IllegalArgumentException { execute("CLEAN_INSERT", dsName, ds); } private void execute(String operationName, String dsName, IDataSet ds) throws DatabaseUnitException, SQLException, IllegalArgumentException { DatabaseConnection databaseConnection = getDatabaseConnection(dsName); DatabaseOperation operation = getDatabaseOperationByName(operationName); operation.execute(databaseConnection, ds); } private DatabaseOperation getDatabaseOperationByName(String operation) { try { Field field = DatabaseOperation.class.getField(operation); return (DatabaseOperation) field.get(null); } catch (SecurityException e) { throw new IllegalArgumentException("Invalid DBUnit operation: " + operation); } catch (NoSuchFieldException e) { throw new IllegalArgumentException("Invalid DBUnit operation: " + operation); } catch (IllegalAccessException e) { throw new IllegalArgumentException("Invalid DBUnit operation: " + operation); } } private Connection getConnection(String dsName) throws SQLException, IllegalArgumentException { if (!connectionMap.containsKey(dsName)) { throw new IllegalArgumentException("Connection: " + dsName); } return connectionMap.get(dsName); } private DatabaseConnection getDatabaseConnection(String dsName) throws DatabaseUnitException, SQLException, IllegalArgumentException { DatabaseConnection databaseConnection = new DatabaseConnection(getConnection(dsName)); if (MSSQL_DBMS_TYPE.equals(curDbmsType)) { databaseConnection.getConfig().setProperty("http://www.dbunit.org/properties/datatypeFactory", new org.dbunit.ext.mssql.MsSqlDataTypeFactory()); } else if (ORACLE_DBMS_TYPE.equals(curDbmsType)) { databaseConnection.getConfig().setProperty("http://www.dbunit.org/properties/datatypeFactory", new org.dbunit.ext.oracle.Oracle10DataTypeFactory()); } else if (DB2_DBMS_TYPE.equals(curDbmsType)) { databaseConnection.getConfig().setProperty("http://www.dbunit.org/properties/datatypeFactory", new org.dbunit.ext.db2.Db2DataTypeFactory()); } else if (MYSQL_DBMS_TYPE.equals(curDbmsType)) { databaseConnection.getConfig().setProperty("http://www.dbunit.org/properties/datatypeFactory", new org.dbunit.ext.mysql.MySqlDataTypeFactory()); } return databaseConnection; } private void loadDBDriver(String dbmsType) throws ClassNotFoundException { if (dbmsType.equalsIgnoreCase(ORACLE_DBMS_TYPE)) { Class.forName("oracle.jdbc.driver.OracleDriver", true, DBUnitLibrary.class.getClassLoader()); this.curDbmsType = ORACLE_DBMS_TYPE; } else if (dbmsType.equalsIgnoreCase(MSSQL_DBMS_TYPE)) { Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver", true, DBUnitLibrary.class.getClassLoader()); this.curDbmsType = MSSQL_DBMS_TYPE; } else if (dbmsType.equalsIgnoreCase(DB2_DBMS_TYPE)) { Class.forName("com.ibm.db2.jcc.DB2Driver", true, DBUnitLibrary.class.getClassLoader()); this.curDbmsType = DB2_DBMS_TYPE; } else if (dbmsType.equalsIgnoreCase(MYSQL_DBMS_TYPE)) { Class.forName("com.mysql.jdbc.Driver", true, DBUnitLibrary.class.getClassLoader()); this.curDbmsType = MYSQL_DBMS_TYPE; } else { throw new IllegalArgumentException("Die DBMS: " + dbmsType + " wird nicht Supported!"); } } }