Java tutorial
/* * This file is part of AceQL. * AceQL: Remote JDBC access over HTTP. * Copyright (C) 2015, KawanSoft SAS * (http://www.kawansoft.com). All rights reserved. * * AceQL is free software; you can redistribute it and/or * modify it under the terms of the GNU Lesser General Public * License as published by the Free Software Foundation; either * version 2.1 of the License, or (at your option) any later version. * * AceQL is distributed in the hope that it will be useful, * but WITHOUT ANY WARRANTY; without even the implied warranty of * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU * Lesser General Public License for more details. * * You should have received a copy of the GNU Lesser General Public * License along with this library; if not, write to the Free Software * Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA * 02110-1301 USA * * Any modifications to this file must keep this entire header * intact. */ package org.kawanfw.test.api.client; import java.io.BufferedOutputStream; import java.io.BufferedReader; import java.io.File; import java.io.FileOutputStream; import java.io.FileReader; import java.io.IOException; import java.io.PrintStream; import java.sql.Connection; import java.sql.DatabaseMetaData; import java.sql.ResultSet; import java.sql.SQLException; import java.util.HashSet; import java.util.Set; import org.apache.commons.io.IOUtils; import org.junit.Assert; import org.junit.Test; import org.kawanfw.commons.util.FrameworkFileUtil; import org.kawanfw.commons.util.FrameworkSystemUtil; import org.kawanfw.sql.api.client.RemoteConnection; import org.kawanfw.sql.api.util.ResultSetPrinter; import org.kawanfw.sql.api.util.SqlUtil; import org.kawanfw.test.parms.ConnectionLoader; import org.kawanfw.test.parms.SqlTestParms; import org.kawanfw.test.util.MessageDisplayer; public class DatabaseMetaDataTest { public static void main(String[] args) throws Exception { SqlTestParms.ACEQL_URL = SqlTestParms.ACEQL_URL_TOMCAT_EMBEDED_LOCALHOST_SSL_2; new DatabaseMetaDataTest().test(); } @Test public void test() throws Exception { Connection connection = null; try { connection = ConnectionLoader.getAceqlConnection(); test(connection); } finally { if (connection != null) { connection.close(); } } } public void test(Connection connection) throws Exception { MessageDisplayer.initClassDisplay(this.getClass().getSimpleName()); DatabaseMetaData databaseMetaData = connection.getMetaData(); // Test that getMetaData() will return value from cache databaseMetaData = connection.getMetaData(); if (connection instanceof RemoteConnection) { MessageDisplayer.display("Java Version : " + System.getProperty("java.version")); MessageDisplayer.display("AceQL Version: " + ((RemoteConnection) connection).getVersion()); MessageDisplayer.display("AceQL Url : " + ((RemoteConnection) connection).getUrl()); MessageDisplayer.display(""); } if (connection instanceof RemoteConnection) { MessageDisplayer.display("((RemoteConnection)connection).clone();"); Connection connection2 = ((RemoteConnection) connection).clone(); @SuppressWarnings("unused") DatabaseMetaData databaseMetaData2 = connection2.getMetaData(); connection2.close(); } MessageDisplayer.display("General info (no Assert done):"); MessageDisplayer.display("connection.getCatalog() : " + connection.getCatalog()); MessageDisplayer.display( "databaseMetaData.getDatabaseProductName() : " + databaseMetaData.getDatabaseProductName()); MessageDisplayer.display( "databaseMetaData.getDatabaseProductVersion(): " + databaseMetaData.getDatabaseProductVersion()); MessageDisplayer.display( "databaseMetaData.getDatabaseMajorVersion() : " + databaseMetaData.getDatabaseMajorVersion()); MessageDisplayer.display( "databaseMetaData.getDatabaseMinorVersion() : " + databaseMetaData.getDatabaseMinorVersion()); MessageDisplayer.display( "databaseMetaData.allProceduresAreCallable() : " + databaseMetaData.allProceduresAreCallable()); // SystemOutHandle.display(DatabaseMetaData.bestRowSession); MessageDisplayer.display(""); // SystemOutHandle.display(databaseMetaData.autoCommitFailureClosesAllResultSets()); MessageDisplayer.display("databaseMetaData.getCatalogTerm(): " + databaseMetaData.getCatalogTerm()); try { MessageDisplayer.display( "databaseMetaData.supportsStoredProcedures(): " + databaseMetaData.supportsStoredProcedures()); MessageDisplayer.display("databaseMetaData.supportsStoredFunctionsUsingCallSyntax(): " + databaseMetaData.supportsStoredFunctionsUsingCallSyntax()); } catch (Throwable e) { MessageDisplayer.display(e.toString()); } MessageDisplayer.display("connection.getAutoCommit(): " + connection.getAutoCommit()); MessageDisplayer.display("databaseMetaData.getDefaultTransactionIsolation() : " + databaseMetaData.getDefaultTransactionIsolation()); MessageDisplayer .display("databaseMetaData.supportsTransactionIsolationLevel(TRANSACTION_READ_UNCOMMITTED): " + databaseMetaData .supportsTransactionIsolationLevel(Connection.TRANSACTION_READ_UNCOMMITTED)); MessageDisplayer.display("databaseMetaData.supportsTransactionIsolationLevel(TRANSACTION_READ_COMMITTED): " + databaseMetaData.supportsTransactionIsolationLevel(Connection.TRANSACTION_READ_COMMITTED)); MessageDisplayer.display("databaseMetaData.supportsTransactionIsolationLevel(TRANSACTION_REPEATABLE_READ): " + databaseMetaData.supportsTransactionIsolationLevel(Connection.TRANSACTION_REPEATABLE_READ)); MessageDisplayer.display("databaseMetaData.supportsTransactionIsolationLevel(TRANSACTION_SERIALIZABLE): " + databaseMetaData.supportsTransactionIsolationLevel(Connection.TRANSACTION_SERIALIZABLE)); MessageDisplayer .display("databaseMetaData.supportsBatchUpdates() : " + databaseMetaData.supportsBatchUpdates()); MessageDisplayer .display("databaseMetaData.supportsSavepoints() : " + databaseMetaData.supportsSavepoints()); MessageDisplayer.display( "databaseMetaData.supportsGetGeneratedKeys(): " + databaseMetaData.supportsGetGeneratedKeys()); if (!new SqlUtil(connection).isTeradata() && !new SqlUtil(connection).isInformix()) { Assert.assertEquals(true, databaseMetaData.supportsTransactionIsolationLevel(Connection.TRANSACTION_READ_COMMITTED)); } Assert.assertEquals("databaseMetaData.supportsBatchUpdates()", true, databaseMetaData.supportsBatchUpdates()); if (!new SqlUtil(connection).isSQLAnywhere() && !new SqlUtil(connection).isAccess()) { Assert.assertEquals("databaseMetaData.supportsGetGeneratedKeys()", true, databaseMetaData.supportsGetGeneratedKeys()); } // Informix does not support savepoints SqlUtil sqlUtil = new SqlUtil(connection); if (!sqlUtil.isInformix() && !sqlUtil.isTeradata() && !new SqlUtil(connection).isAccess()) { Assert.assertEquals(true, databaseMetaData.supportsSavepoints()); } MessageDisplayer.display(""); String catalog = null; String schema = null; String table = "customer"; // Table name must be uppercase for Oracle & DB2, lowercase for MySQL // and PostgreSQL if (new SqlUtil(connection).isOracle() || new SqlUtil(connection).isHSQLDB() || new SqlUtil(connection).isDB2()) { table = table.toUpperCase(); } ResultSet rs = null; if (!new SqlUtil(connection).isAccess()) { rs = databaseMetaData.getPrimaryKeys(catalog, schema, table); printResultSet(rs); boolean rsNext = false; while (rs.next()) { rsNext = true; String keyColumnName = rs.getString("COLUMN_NAME"); MessageDisplayer.display("Primary Key is: " + keyColumnName + " for Table: " + table); Assert.assertEquals("customer_id", keyColumnName.toLowerCase()); } if (!new SqlUtil(connection).isH2()) { Assert.assertEquals(true, rsNext); } rs.close(); } // boolean returnNow = true; // if (returnNow) return; String[] types = { "TABLE", "VIEW" }; rs = databaseMetaData.getTables(null, null, null, types); Set<String> tablesSet = new HashSet<String>(); Set<String> ourTables = new HashSet<String>(); ourTables.add("banned_usernames"); ourTables.add("customer"); ourTables.add("customer_auto"); ourTables.add("orderlog"); ourTables.add("user_login"); MessageDisplayer.display(""); while (rs.next()) { table = rs.getString("TABLE_NAME"); if (ourTables.contains(table.toLowerCase())) { MessageDisplayer.display("Table: " + table); } tablesSet.add(table.toLowerCase()); } // printResultSet(rs); testTable("banned_usernames", tablesSet); testTable("customer", tablesSet); testTable("orderlog", tablesSet); testTable("user_login", tablesSet); rs.close(); } /** * Create our own temp file * * @return the tempfile to create */ private synchronized File createAceqlTempFile() { String unique = FrameworkFileUtil.getUniqueId(); String tempDir = FrameworkFileUtil.getKawansoftTempDir(); String tempFile = tempDir + File.separator + "print-stream-" + unique + ".kawanfw.txt"; return new File(tempFile); } /** * Print a ResultSet and reinit it * * @param rs * @throws SQLException */ private void printResultSet(ResultSet rs) throws SQLException { MessageDisplayer.display(); if (rs.getType() == ResultSet.TYPE_FORWARD_ONLY) { System.out.println("Impossible to print the ResultSet: TYPE_FORWARD_ONLY"); MessageDisplayer.display(); return; } ResultSetPrinter resultSetPrinter; rs.beforeFirst(); // Special treatment for Androd if (FrameworkSystemUtil.isAndroid()) { File file = createAceqlTempFile(); // MessageDisplayer.display(file.toString()); PrintStream ps = null; BufferedReader reader = null; try { ps = new PrintStream(new BufferedOutputStream(new FileOutputStream(file))); resultSetPrinter = new ResultSetPrinter(rs, ps, false); resultSetPrinter.print(); ps.close(); // Or, read back the file and print it with reader = new BufferedReader(new FileReader(file)); String line = null; while ((line = reader.readLine()) != null) { MessageDisplayer.display(line); } } catch (IOException ioe) { throw new SQLException(ioe); } finally { IOUtils.closeQuietly(reader); IOUtils.closeQuietly(ps); file.delete(); } } else { resultSetPrinter = new ResultSetPrinter(rs, System.out, false); resultSetPrinter.print(); } rs.beforeFirst(); MessageDisplayer.display(); } /** * Test that a table is in the set * * @param table * the table * @param tablesSet * the tables set */ private void testTable(String table, Set<String> tablesSet) { Assert.assertTrue("DatabaseMedata does not contain table " + table, tablesSet.contains(table)); } }