Java tutorial
/******************************************************************************* * Copyright (c) 2005-2011, G. Weirich and Elexis * All rights reserved. This program and the accompanying materials * are made available under the terms of the Eclipse Public License v1.0 * which accompanies this distribution, and is available at * http://www.eclipse.org/legal/epl-v10.html * * Contributors: * G. Weirich - initial implementation * *******************************************************************************/ package ch.rgw.tools; import java.io.BufferedWriter; import java.io.IOException; import java.io.InputStream; import java.io.UnsupportedEncodingException; import java.sql.Connection; import java.sql.Driver; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.ResultSetMetaData; import java.sql.SQLException; import java.sql.Statement; import java.sql.Types; import java.util.HashMap; import java.util.Properties; import java.util.Timer; import java.util.TimerTask; import java.util.Vector; import java.util.logging.Level; import org.apache.commons.dbcp.ConnectionFactory; import org.apache.commons.dbcp.DriverConnectionFactory; import org.apache.commons.dbcp.PoolableConnectionFactory; import org.apache.commons.dbcp.PoolingDataSource; import org.apache.commons.pool.impl.GenericObjectPool; /** * Weiterer Abstraktionslayer zum einfacheren Zugriff auf eine jdbc-fhige Datenbank */ public class JdbcLink { public static final String getVersion() { return "3.2.1"; } public int lastErrorCode; public String lastErrorString; public int verMajor = 0; public int verMinor = 0; public String DBFlavor = null; private String sDrv; private String sConn; private String sUser; private String sPwd; private PoolingDataSource dataSource; private GenericObjectPool<Connection> connectionPool; // prepared statements are not released properly up until now, so keep 1 connection open private Connection preparedStatementConnection; private int keepAliveCount; private Timer keepAliveTimer = new Timer(); private class KeepAliveTask extends TimerTask { private Connection connection; private PreparedStatement keapAliveStatement; public KeepAliveTask(Connection connection) throws SQLException { this.connection = connection; this.keapAliveStatement = connection.prepareStatement(VALIDATION_QUERY); } @Override public void run() { try { keapAliveStatement.execute(); } catch (SQLException e) { lastErrorCode = CONNECTION_SQL_ERROR; lastErrorString = e.getMessage(); keepAliveTimer.cancel(); throw JdbcLinkExceptionTranslation.translateException(lastErrorString, e); } } } PreparedStatement preparedStatementKeepAlive; private static Log log; public static final int CONNECT_SUCCESS = 0; public static final int CONNECT_CLASSNOTFOUND = 1; public static final int CONNECT_FAILED = 2; public static final int CONNECT_UNKNOWN_ERROR = 10; public static final int TRANSACTION_COMMIT_FAILED = 21; public static final int TRANSACTION_ROLLBACK_FAILED = 22; public static final int TRANSACTION_COMMIT_NOT_SUPPORTED = 23; public static final int CONNECTION_CANT_CREATE_STATEMENT = 30; public static final int CONNECTION_CANT_PREPARE_STAMENT = 31; public static final int CONNECTION_SQL_ERROR = 40; public static final String DBFLAVOR_MYSQL = "mysql"; public static final String DBFLAVOR_POSTGRESQL = "postgresql"; public static final String DBFLAVOR_H2 = "h2"; public static final String VALIDATION_QUERY = "SELECT 1;"; static { log = Log.get("jdbcLink"); } @SuppressWarnings("unused") private JdbcLink() { /* intentionally blank */ } /** * Bequemlichkeitsmethode, um einen JdcbLink auf eine MySQL-Datenbank zu erhalten */ public static JdbcLink createMySqlLink(String host, String database) { log.log(Level.INFO, "Creating MySQL-Link"); String driver = "com.mysql.jdbc.Driver"; String[] hostdetail = host.split(":"); String hostname = hostdetail[0]; String hostport = hostdetail.length > 1 ? hostdetail[1] : "3306"; String connect = "jdbc:mysql://" + hostname + ":" + hostport + "/" + database; return new JdbcLink(driver, connect, DBFLAVOR_MYSQL); } /** * Bequemlichkeitsmethode, um einen JdbcLink auf eine InProcess laufende HSQL-Datenbank zu * erhalten * * @param database * ein Dateiname fr die zu erzeugende bzw. zu verwendende Datenbank */ public static JdbcLink createInProcHsqlDBLink(String database) { log.log(Level.INFO, "Creating HSQL-In-Proc-Link"); String driver = "org.hsqldb.jdbcDriver"; String connect = "jdbc:hsqldb:" + database; return new JdbcLink(driver, connect, "hsqldb"); } /** * Bequemlichkeitsmethode, um einen JdbcLink auf eine als Server laufende HSQL-Datenbank zu * erhalten * * @param host * Server, auf dem die Datenbank luft. */ public static JdbcLink createHsqlDBLink(String host) { log.log(Level.INFO, "Creating HSQL-Link"); String driver = "org.hsqldb.jdbcDriver"; String connect = "jdbc:hsqldb:hsql://" + host; return new JdbcLink(driver, connect, "hsqldb"); } /** * Bequemlichkeitsmethode, um einen Link auf eine H2-Datenbank zu bekommen. Da der * mysql-compatibility-mode fr ALTER commands nicht korrekt funktioniert, wird ein h2 DBFlavor * fr die bersetzung der Statements bergeben. * * @param database * @return */ public static JdbcLink createH2Link(String database) { log.log(Level.INFO, "Creating H2-Link"); String driver = "org.h2.Driver"; String prefix = "jdbc:h2:"; if (database.contains(".zip!")) { prefix += "zip:"; } String connect = prefix + database + ";AUTO_SERVER=TRUE"; return new JdbcLink(driver, connect, DBFLAVOR_H2); } /** * Bequemlichkeitsmethode fr einen JdbcLink auf einen 4D-Server * * @param host * de Server, auf dem die 4D-Datenbnak luft * @return */ public static JdbcLink create4DLink(String host) { log.log(Level.INFO, "Creating 4D-Link"); String driver = "com.fourd.jdbc.DriverImpl"; String connect = "jdbc:4d:" + host + ":19813"; return new JdbcLink(driver, connect, "4d"); } /** * Bequemlichkeitsmethode fr einen JdbcLink auf einen PostgreSQL- Server * * @param host * @return */ public static JdbcLink createPostgreSQLLink(String host, String database) { log.log(Level.INFO, "Creating PostgreSQL-Link"); String driver = "org.postgresql.Driver"; String[] hostdetail = host.split(":"); String hostname = hostdetail[0]; String hostport = hostdetail.length > 1 ? hostdetail[1] : "5432"; String connect = "jdbc:postgresql://" + hostname + ":" + hostport + "/" + database; return new JdbcLink(driver, connect, DBFLAVOR_POSTGRESQL); } public static JdbcLink createODBCLink(String dsn) { log.log(Level.INFO, "Creating ODBC-Link"); String driver = "sun.jdbc.odbc.JdbcOdbcDriver"; String connect = "jdbc:odbc:" + dsn; return new JdbcLink(driver, connect, "ODBC"); } /** * Erstelle einen neuen jdbcLink. Es wird kein Connect-Versuch gemacht, das heisst, der * Konstruktor wird nie scheitern. * * @param driver * Treiber-String (wie org.hsql.jdbc) * @param connect * Connect-String (wie jdbc:odbc:data) */ public JdbcLink(String driver, String connect, String flavor) { sDrv = driver; sConn = connect; DBFlavor = flavor.toLowerCase(); } /** * Verbindung zur Datenbank herstellen * * TODO return value is always true because exception is thrown on error * * @param user * Username, kann null sein * @param password * Passwort, kann null sein * @return errcode * * @throws JdbcLinkException */ public boolean connect(String user, String password) { Exception cause = null; try { sUser = user; sPwd = password; Driver driver = (Driver) Class.forName(sDrv).newInstance(); verMajor = driver.getMajorVersion(); verMinor = driver.getMinorVersion(); log.log(Level.INFO, "Loading database driver " + sDrv); log.log(Level.INFO, "Connecting with database " + sConn); // // First, we'll create a ConnectionFactory that the // pool will use to create Connections. // Properties properties = new Properties(); properties.put("user", user); properties.put("password", password); ConnectionFactory connectionFactory = new DriverConnectionFactory(driver, sConn, properties); // // Next we'll create the PoolableConnectionFactory, which wraps // the "real" Connections created by the ConnectionFactory with // the classes that implement the pooling functionality. // connectionPool = new GenericObjectPool<Connection>(null); // configure the connection pool connectionPool.setMaxActive(32); connectionPool.setMinIdle(2); connectionPool.setMaxWait(10000); connectionPool.setTestOnBorrow(true); new PoolableConnectionFactory(connectionFactory, connectionPool, null, VALIDATION_QUERY, false, true); dataSource = new PoolingDataSource(connectionPool); // test establishing a connection Connection conn = dataSource.getConnection(); conn.close(); lastErrorCode = CONNECT_SUCCESS; lastErrorString = "Connect successful"; log.log("Connect successful", Log.DEBUGMSG); return true; } catch (ClassNotFoundException ex) { lastErrorCode = CONNECT_CLASSNOTFOUND; lastErrorString = "Class not found exception: " + ex.getMessage(); cause = ex; } catch (InstantiationException e) { lastErrorCode = CONNECT_UNKNOWN_ERROR; lastErrorString = "Instantiation exception: " + e.getMessage(); cause = e; } catch (IllegalAccessException e) { lastErrorCode = CONNECT_UNKNOWN_ERROR; lastErrorString = "Illegal access exception: " + e.getMessage(); cause = e; } catch (SQLException e) { lastErrorCode = CONNECT_UNKNOWN_ERROR; lastErrorString = "SQL exception: " + e.getMessage(); cause = e; } catch (IllegalStateException e) { lastErrorCode = CONNECT_UNKNOWN_ERROR; lastErrorString = "Illegal state exception: " + e.getMessage(); cause = e; } throw JdbcLinkExceptionTranslation.translateException("Connect failed: " + lastErrorString, cause); } /** * Utility-Funktion zum Einpacken von Strings in Hochkommata und escapen illegaler Zeichen * * @param s * der String * @return Datenbankkonform eingepackte String */ public static String wrap(String s) { if (StringTool.isNothing(s)) { return "''"; } try { return wrap(s.getBytes("UTF-8"), DBFLAVOR_MYSQL); } catch (UnsupportedEncodingException e) { ExHandler.handle(e); return wrap(s.getBytes(), DBFLAVOR_MYSQL); } } public String wrapFlavored(String s) { if (StringTool.isNothing(s)) { return "''"; } try { return wrap(s.getBytes("UTF-8"), DBFlavor); } catch (UnsupportedEncodingException e) { ExHandler.handle(e); return wrap(s.getBytes(), DBFlavor); } } /** * Utility-Funktion zum Datenbankkonformen Verpacken von byte arrays zwecks Einfgen in * BLOB-Felder. * * @param flavor * TODO * @param b * das rohe byte array * @return das verpackte array in Form eines String */ public static String wrap(byte[] in, String flavor) { byte[] out = new byte[2 * in.length + 2]; int j = 0; out[j++] = '\''; for (int i = 0; i < in.length; i++) { switch (in[i]) { case 0: case 34: case '\'': if (flavor.startsWith(DBFLAVOR_POSTGRESQL) || flavor.startsWith("hsql")) { out[j++] = '\''; break; } case 92: boolean before = (i > 1 && in[i - 1] == 92); boolean after = (i < in.length - 1 && in[i + 1] == 92); if (!before && !after) { out[j++] = '\\'; } } out[j++] = in[i]; } out[j++] = '\''; try { return new String(out, 0, j, "UTF-8"); } catch (UnsupportedEncodingException e) { ExHandler.handle(e); return null; } } public Connection getKeepAliveConnection() { log.log(Level.INFO, "Creating new keep alive connection [" + keepAliveCount + "]"); Connection conncetion; try { conncetion = dataSource.getConnection(); keepAliveTimer.scheduleAtFixedRate(new KeepAliveTask(conncetion), 5000, 5000); } catch (SQLException ex) { lastErrorCode = CONNECT_FAILED; lastErrorString = "SQL exception: " + ex.getMessage(); throw JdbcLinkExceptionTranslation.translateException("Connect failed: " + lastErrorString, ex); } return conncetion; } /** * This method is deprecated. Use the methods getStatement and releaseStatement instead. * * @deprecated * @return */ public Connection getConnection() { try { return dataSource.getConnection(); } catch (SQLException ex) { lastErrorCode = CONNECT_FAILED; lastErrorString = "SQL exception: " + ex.getMessage(); throw JdbcLinkExceptionTranslation.translateException("Connect failed: " + lastErrorString, ex); } } public String getDriverName() { return sDrv; } public String getConnectString() { return sConn; } /** * Ent Statement aus dem pool beziehen. Jedes mit getStatement bezogene Statement MUSS mit * releaseStatement wieder zurckgegeben werden. * * @return ein Stm (JdbcLink-spezifische Statement-Variante) */ public Stm getStatement() { checkLink(); return createStatement(); } private Stm createStatement() { try { return new Stm(); } catch (SQLException ex) { lastErrorCode = CONNECTION_CANT_CREATE_STATEMENT; lastErrorString = ex.getMessage(); throw JdbcLinkExceptionTranslation.translateException(lastErrorString, ex); } } /** * Ein Stm - Statement in den pool zurckgeben. Die Zahl der im pool zu haltenden Statements * wird mit keepStatements definiert. * * @param s */ public void releaseStatement(Stm s) { if (s != null) { s.delete(); } } private void checkLink() { if (dataSource == null) { throw new JdbcLinkException("JdbcLink closed"); } } private HashMap<PreparedStatement, Connection> preparedConnections = new HashMap<PreparedStatement, Connection>(); /** * Create a new PreparedStatement. * * <b>IMPORTANT</b> Release the resource after using, with releasePreparedStatement mehtod. * * @param sql * @return */ public PreparedStatement getPreparedStatement(String sql) { try { Connection connection = dataSource.getConnection(); PreparedStatement statement = connection.prepareStatement(sql); preparedConnections.put(statement, connection); return statement; } catch (SQLException e) { lastErrorCode = CONNECTION_CANT_PREPARE_STAMENT; lastErrorString = e.getMessage(); throw JdbcLinkExceptionTranslation.translateException(lastErrorString, e); } } /** * Release the resources of a PreparedStatement. * * @param statement */ public void releasePreparedStatement(PreparedStatement statement) { Connection connection = preparedConnections.get(statement); if (connection != null) { try { connection.close(); } catch (SQLException e) { throw JdbcLinkExceptionTranslation.translateException(e); } } preparedConnections.remove(statement); } /** * Ein Prepared Statement anlegen * * This method is deprecated. Use the methods getPreparedStatement and releasePreparedStatement * instead. * * @deprecated * @param sql * Abfrage fr das statement (eizusetzende Parameter mssen als ? gesetzt sein * @return das vorkompilierte PreparedStatement */ public synchronized PreparedStatement prepareStatement(String sql) { checkLink(); try { if (preparedStatementConnection == null) { preparedStatementConnection = getKeepAliveConnection(); } return preparedStatementConnection.prepareStatement(sql); } catch (SQLException ex) { lastErrorCode = CONNECTION_CANT_PREPARE_STAMENT; lastErrorString = ex.getMessage(); throw JdbcLinkExceptionTranslation.translateException(lastErrorString, ex); } } public static final int INTEGRAL = 1; public static final int TEXT = 2; public static final int BINARY = 3; public static final int OTHER = 4; public static int generalType(int t) { switch (t) { case Types.BIGINT: case Types.BIT: case Types.BOOLEAN: case Types.INTEGER: case Types.SMALLINT: case Types.TINYINT: return INTEGRAL; case Types.VARCHAR: case Types.CHAR: case Types.LONGVARCHAR: return TEXT; case Types.BINARY: case Types.BLOB: case Types.CLOB: case Types.LONGVARBINARY: case Types.VARBINARY: return BINARY; default: return OTHER; } } /** * Einen String-Value aus dem aktuellen Datensatz des ResultSets holen. Es wird garantiert, dass * immer etwas zurckgeliefert wird (" " fr den leeren Sring) * * @param r * ResultSet * @param field * Feldname * @return den String * @throws Exception * Wenn das Feld nicht definiert ist. */ public static String getString(ResultSet r, String field) throws Exception { String res = r.getString(field); if (StringTool.isNothing(res)) { return " "; } return res; } /** * Verbindung zur Datenbank lsen * */ public synchronized void disconnect() { try { if (preparedStatementConnection != null) { preparedStatementConnection.close(); } connectionPool.close(); } catch (Exception e) { // ignore } finally { dataSource = null; } log.log("Disconnected", Log.INFOS); } /** * Anfrage, ob die Verbindung steht * * @todo Muss implementiert werden * @return true wenn die Verbindung steht. */ public boolean isAlive() { return true; } /** * Unscharfes Suchen im ResultSet. * * @param r * das zu durchsuchende ResultSet * @param field * Name des interessiernden Felds * @param m * (vorher konfigurierter) fuzzyMatcher mit der Suchbedingung * @see ch.rgw.tools.FuzzyMatcher * @return true wenn gefunden; das ResultSet steht auf der ersten oder einzigen Fundstelle. */ public static boolean nextMatch(ResultSet r, String field, FuzzyMatcher m) { try { while (r.next()) { if (m.match(r.getString(field))) { return true; } } return false; } catch (SQLException ex) { ExHandler.handle(ex); } return false; } public String dbDriver() { return sDrv; } /** * Einen String-Wert abfragen. Temporres Statement erzeugen * * @param sql * SQL-String, der ein VARCHAR-oder Text-Feld liefern sollte * @return den gefundenen String oder null: nicht gefunden */ public String queryString(String sql) { Stm stm = getStatement(); String res = stm.queryString(sql); releaseStatement(stm); return res; } public int queryInt(String sql) { Stm stm = getStatement(); int res = stm.queryInt(sql); releaseStatement(stm); return res; } public boolean execScript(InputStream i, boolean translate, boolean stopOnError) { Stm stm = getStatement(); boolean ret = stm.execScript(i, translate, stopOnError); releaseStatement(stm); return ret; } /** * Wrapper fr Stm#exec * * @author gerry * */ public int exec(final String sql) { Stm stm = getStatement(); int res = stm.exec(sql); releaseStatement(stm); return res; } public class Stm { private Connection conn; private Statement stm; private void checkStm() { if (stm == null || conn == null) throw new JdbcLinkException("Statement not valid!"); } private boolean reconnect() { try { if (conn != null && !conn.isClosed()) { conn.close(); } log.log(Level.WARNING, "JdbcLink.Stm - trying reconnect"); conn = getConnection(); stm = conn.createStatement(); return true; } catch (SQLException ex) { log.log(Level.SEVERE, "JdbcLink.Stm - reconnect failed " + ex.getMessage()); lastErrorCode = ex.getErrorCode(); lastErrorString = ex.getMessage(); return false; } catch (JdbcLinkException je) { log.log(Level.SEVERE, "JdbcLink.Stm - Reconnect failed " + je.getMessage()); return false; } } Stm() throws SQLException { try { conn = getConnection(); stm = conn.createStatement(); } catch (SQLException se) { log.log(Level.WARNING, "need reconnect " + se.getMessage()); if (!reconnect()) { throw se; } } } public boolean isClosed() { checkStm(); if (DBFLAVOR_POSTGRESQL.equals(DBFlavor)) { return false; } try { return stm.isClosed(); } catch (SQLException ex) { ExHandler.handle(ex); return false; } catch (UnsupportedOperationException ex) { ExHandler.handle(ex); return false; } } public void delete() { try { // stm.cancel(); if (stm != null && !stm.isClosed()) { stm.close(); } if (conn != null && !conn.isClosed()) { conn.close(); } } catch (SQLException ex) { ExHandler.handle(ex); /* egal */ } stm = null; conn = null; } /** * Eine execute auf die Datanbank ausfhren * * @param SQLText * Von der Datenbank verstandener SQL-String * @return Zahl der affected rows. */ public int exec(final String sql) { return internalExec(sql, false); } private int internalExec(final String SQLText, final boolean inError) { checkStm(); // log.log("executing " + SQLText, Log.DEBUGMSG); try { return stm.executeUpdate(SQLText); } catch (SQLException e) { if (!inError) { if (connect(sUser, sPwd)) { return internalExec(SQLText, true); } } boolean throwException = DatabaseNativeExceptionHandler.handleException(DBFlavor, e); if (throwException) { throw JdbcLinkExceptionTranslation.translateException("Fehler bei: " + SQLText, e); } return 1; } } /** * Eine SQL-Anfrage an die Datenbank senden. Versucht bei einem Fehler zuerst die Verbindung * wieder herzustellen * * @param SQLText * ein Query String in von der Datenbank verstandener Syntax * @return ein ResultSet oder null bei Fehler * @throws JdbcException */ public ResultSet query(final String SQLText) { return internalQuery(SQLText, false); } private ResultSet internalQuery(final String SQLText, final boolean inError) { checkStm(); ResultSet res = null; // log.log("querying " + SQLText, Log.DEBUGMSG); try { res = stm.executeQuery(SQLText); return res; } catch (SQLException e) { if (!inError) { // try to solve the problem with a simpel reconnect if (reconnect()) { log.log(Level.WARNING, "Reconnect"); return internalQuery(SQLText, true); } } lastErrorString = e.getMessage(); lastErrorCode = CONNECTION_SQL_ERROR; throw JdbcLinkExceptionTranslation.translateException(lastErrorString, e); } } /** * Eine Anzahl Werte als Vector zurckliefern * * @param sql * SQL-String, der die Werte liefert * @param fields * interessierende Felder * @return einen Vector aus Object[] Arrays mit den interessierenden Feldern aller * gefundenen Datenstze */ @SuppressWarnings("unchecked") public Vector queryList(String sql, String[] fields) { Vector rs = new Vector(); log.log("executing " + sql, Log.DEBUGMSG); ResultSet res = internalQuery(sql, false); try { if (res != null) { while (res.next()) { Object[] o = new Object[fields.length]; for (int i = 0; i < fields.length; i++) { o[i] = res.getObject(fields[i]); } if (fields.length == 1) { rs.add(o[0]); } else { rs.add(o); } } } } catch (SQLException ex) { ExHandler.handle(ex); } return rs; } public String queryString(String sql) { ResultSet res = internalQuery(sql, false); try { if (res != null && res.next()) { String r = res.getString(1); if ((r == null) || (r.equals("null")) || (r.equals(""))) { return ""; } return r; } return null; } catch (SQLException ex) { ExHandler.handle(ex); } return null; } /** * Einen Integer-Wert abfragen. * * @param sql * SQL-String, der ein Integer-Feld liefern sollte * @return den ersten der Suchbedingung entsprechenden Integer-Wert oder -1: Wert nicht * gefunden. */ public int queryInt(String sql) { ResultSet res = internalQuery(sql, false); try { if (res != null && res.next()) { return res.getInt(1); } } catch (SQLException ex) { ExHandler.handle(ex); } return -1; } /** * Ein SQL-Script einlesen und ausfhren. alles nach # bis zum Zeilenende wird ignoriert * * @param s * der InputStream mit dem Script * @param translate * true, wenn das Script zu den bekannten Dialekten bersetzt werden soll * @param stopOnError * true: Abbruch des Scripts, wenn ein Fehler auftritt * @return false wenn ein Fehler passiert ist. */ public boolean execScript(InputStream s, boolean translate, boolean stopOnError) { String sql = "<none>"; if (s == null) { return false; } // autoc=conn.getAutoCommit(); // setAutoCommit(false); while ((sql = readStatement(s)) != null) { log.log(sql, Log.DEBUGMSG); if (translate) { sql = translateFlavor(sql); } System.out.println(sql); try { stm.execute(sql); } catch (SQLException ex) { ExHandler.handle(ex); if (stopOnError == true) { return false; } } } // commit(); return true; } } public static String readStatement(InputStream is) { StringBuffer inp = new StringBuffer(1000); String sql = "<none>"; try { int c; boolean comment = false; while (((c = is.read()) != -1)) { if (c == ';') { break; } if (c == '#') { comment = true; } if ((c == '\r') || (c == '\n')) { comment = false; } if (comment == false) { inp.append((char) c); } } // sql=inp.toString().replace("#.+$",""); // sql=sql.replace("--[^\\r]*","").trim(); sql = inp.toString().replaceAll("[\\n\\r\\t]", " "); sql = sql.replaceAll(" {2,}", " ").trim(); if (sql.length() < 4) { return null; } return sql; } catch (IOException ex) { ExHandler.handle(ex); return null; } } public boolean dumpTable(BufferedWriter w, String name) throws Exception { Stm stm = getStatement(); ResultSet res = stm.query("SELECT * from " + name); ResultSetMetaData rm = res.getMetaData(); int cols = rm.getColumnCount(); String[] ColNames = new String[cols]; int[] colTypes = new int[cols]; w.write("CREATE TABLE " + name + "("); for (int i = 0; i < cols; i++) { ColNames[i] = rm.getColumnName(i + 1); colTypes[i] = rm.getColumnType(i + 1); w.write(ColNames[i] + " " + colTypes[i] + ",\n"); } w.write(");"); while ((res != null) && (res.next() == true)) { w.write("INSERT INTO " + name + " ("); for (int i = 0; i < cols; i++) { w.write(ColNames[i]); if (i < cols - 1) { w.write(","); } } w.write(") VALUES ("); for (int i = 0; i < cols; i++) { Object o = res.getObject(ColNames[i]); switch (JdbcLink.generalType(colTypes[i])) { case JdbcLink.INTEGRAL: if (o == null) { w.write("0"); } else { w.write(Integer.toString(((Integer) o).intValue())); } break; case JdbcLink.TEXT: if (o == null) { w.write(JdbcLink.wrap("null")); } else { w.write(JdbcLink.wrap((String) o)); } break; default: String t = o.getClass().getName(); log.log("Unknown type " + t, Log.ERRORS); throw new Exception("Cant write " + t); } if (i < cols - 1) { w.write(","); } } w.write(");"); w.newLine(); } res.close(); releaseStatement(stm); return true; } /** * Einen SQL-String in die bekannten flavors bersetzen. Basisdialekt ist mysql */ public String translateFlavor(String sql) { // sql=sql.toLowerCase(); // TODO: Konzept fr case-sensitiveness klarer definieren if (DBFlavor.equalsIgnoreCase(DBFLAVOR_POSTGRESQL)) { sql = sql.replaceAll("BLOB", "BYTEA"); sql = sql.replaceAll("DROP INDEX (.+?) ON .+?;", "DROP INDEX $1;"); sql = sql.replaceAll("MODIFY\\s+(\\w+)\\s+(.+)", "ALTER COLUMN $1 TYPE $2"); sql = sql.replaceAll("SIGNED", "INT"); } else if (DBFlavor.startsWith("hsqldb") || DBFlavor.startsWith(DBFLAVOR_H2)) { sql = sql.replaceAll("TEXT", "LONGVARCHAR"); sql = sql.replaceAll("BLOB", "LONGVARBINARY"); sql = sql.replaceAll("CREATE +TABLE", "CREATE CACHED TABLE"); sql = sql.replaceAll("DROP INDEX (.+?) ON .+?;", "DROP INDEX $1;"); sql = sql.replaceAll("MODIFY (.+)", "ALTER COLUMN $1"); } else if (DBFlavor.equalsIgnoreCase(DBFLAVOR_MYSQL)) { sql = sql.replaceAll("BLOB", "LONGBLOB"); sql = sql.replaceAll("TEXT", "LONGTEXT"); /* experimental - do not use */ } else if (DBFlavor.equalsIgnoreCase("db2")) { sql = sql.replaceAll("VARCHAR\\s*\\([0-9]+\\)", "VARCHAR"); sql = sql.replaceAll("TEXT", "CLOB"); /* /experimental */ } return sql; } }