Java tutorial
package org.jumpmind.db.platform.db2; /* * 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. */ import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Types; import java.util.HashSet; import java.util.Map; import java.util.regex.Matcher; import java.util.regex.Pattern; import org.apache.commons.lang.StringUtils; import org.jumpmind.db.model.Column; import org.jumpmind.db.model.ForeignKey; import org.jumpmind.db.model.IIndex; import org.jumpmind.db.model.Table; import org.jumpmind.db.model.TypeMap; import org.jumpmind.db.platform.AbstractJdbcDdlReader; import org.jumpmind.db.platform.DatabaseMetaDataWrapper; import org.jumpmind.db.platform.IDatabasePlatform; import org.jumpmind.db.sql.JdbcSqlTemplate; /* * Reads a database model from a Db2 UDB database. */ public class Db2DdlReader extends AbstractJdbcDdlReader { /* Known system tables that Db2 creates (e.g. automatic maintenance). */ private static final String[] KNOWN_SYSTEM_TABLES = { "STMG_DBSIZE_INFO", "HMON_ATM_INFO", "HMON_COLLECTION", "POLICY" }; /* The regular expression pattern for the time values that Db2 returns. */ private Pattern db2TimePattern = Pattern.compile("'(\\d{2}).(\\d{2}).(\\d{2})'"); /* The regular expression pattern for the timestamp values that Db2 returns. */ private Pattern db2TimestampPattern = Pattern .compile("'(\\d{4}\\-\\d{2}\\-\\d{2})\\-(\\d{2}).(\\d{2}).(\\d{2})(\\.\\d{1,8})?'"); public Db2DdlReader(IDatabasePlatform platform) { super(platform); setDefaultCatalogPattern(null); setDefaultSchemaPattern(null); } @Override protected Table readTable(Connection connection, DatabaseMetaDataWrapper metaData, Map<String, Object> values) throws SQLException { String tableName = (String) values.get("TABLE_NAME"); for (int idx = 0; idx < KNOWN_SYSTEM_TABLES.length; idx++) { if (KNOWN_SYSTEM_TABLES[idx].equals(tableName)) { return null; } } Table table = super.readTable(connection, metaData, values); if (table != null) { enhanceTableMetaData(connection, metaData, table); } return table; } protected void enhanceTableMetaData(Connection connection, DatabaseMetaDataWrapper metaData, Table table) throws SQLException { log.debug("about to read additional column data"); /* DB2 does not return the auto-increment status via the database metadata */ String sql = "SELECT NAME, IDENTITY FROM SYSIBM.SYSCOLUMNS WHERE TBNAME=?"; if (StringUtils.isNotBlank(metaData.getSchemaPattern())) { sql = sql + " AND TBCREATOR=?"; } PreparedStatement pstmt = null; ResultSet rs = null; try { pstmt = connection.prepareStatement(sql); pstmt.setString(1, table.getName()); if (StringUtils.isNotBlank(metaData.getSchemaPattern())) { pstmt.setString(2, metaData.getSchemaPattern()); } rs = pstmt.executeQuery(); while (rs.next()) { String columnName = rs.getString(1); Column column = table.getColumnWithName(columnName); if (column != null) { String isIdentity = rs.getString(2); if (isIdentity != null && isIdentity.startsWith("Y")) { column.setAutoIncrement(true); if (log.isDebugEnabled()) { log.debug("Found identity column {} on {}", columnName, table.getName()); } } } } } finally { JdbcSqlTemplate.close(rs); JdbcSqlTemplate.close(pstmt); } log.debug("done reading additional column data"); } @Override protected Column readColumn(DatabaseMetaDataWrapper metaData, Map<String, Object> values) throws SQLException { Column column = super.readColumn(metaData, values); if (column.getDefaultValue() != null) { if (column.getMappedTypeCode() == Types.TIME) { Matcher matcher = db2TimePattern.matcher(column.getDefaultValue()); // Db2 returns "HH24.MI.SS" if (matcher.matches()) { StringBuffer newDefault = new StringBuffer(); newDefault.append("'"); // the hour newDefault.append(matcher.group(1)); newDefault.append(":"); // the minute newDefault.append(matcher.group(2)); newDefault.append(":"); // the second newDefault.append(matcher.group(3)); newDefault.append("'"); column.setDefaultValue(newDefault.toString()); } } else if (column.getMappedTypeCode() == Types.TIMESTAMP) { Matcher matcher = db2TimestampPattern.matcher(column.getDefaultValue()); // Db2 returns "YYYY-MM-DD-HH24.MI.SS.FF" if (matcher.matches()) { StringBuffer newDefault = new StringBuffer(); newDefault.append("'"); // group 1 is the date which has the correct format newDefault.append(matcher.group(1)); newDefault.append(" "); // the hour newDefault.append(matcher.group(2)); newDefault.append(":"); // the minute newDefault.append(matcher.group(3)); newDefault.append(":"); // the second newDefault.append(matcher.group(4)); // optionally, the fraction if ((matcher.groupCount() > 4) && (matcher.group(4) != null)) { newDefault.append(matcher.group(5)); } newDefault.append("'"); column.setDefaultValue(newDefault.toString()); } } else if (TypeMap.isTextType(column.getMappedTypeCode())) { String defaultValue = column.getDefaultValue(); // DB2 stores default text values quoted if ((defaultValue.length() >= 2) && defaultValue.startsWith("'") && defaultValue.endsWith("'")) { defaultValue = defaultValue.substring(1, defaultValue.length() - 1); } column.setDefaultValue(unescape(defaultValue, "'", "''")); } } return column; } @Override protected boolean isInternalPrimaryKeyIndex(Connection connection, DatabaseMetaDataWrapper metaData, Table table, IIndex index) throws SQLException { // Db2 uses the form "SQL060205225246220" if the primary key was defined // during table creation // When the ALTER TABLE way was used however, the index has the name of // the primary key if (index.getName().startsWith("SQL")) { try { Long.parseLong(index.getName().substring(3)); return true; } catch (NumberFormatException ex) { // we ignore it } return false; } else { // we'll compare the index name to the names of all primary keys // TODO: Once primary key names are supported, this can be done // easier via the table object ResultSet pkData = null; HashSet<String> pkNames = new HashSet<String>(); try { log.debug("getting pk info"); pkData = metaData.getPrimaryKeys(table.getName()); log.debug("done getting pk info"); while (pkData.next()) { Map<String, Object> values = readMetaData(pkData, getColumnsForPK()); pkNames.add((String) values.get("PK_NAME")); } } finally { if (pkData != null) { pkData.close(); } } return pkNames.contains(index.getName()); } } @Override protected boolean isInternalForeignKeyIndex(Connection connection, DatabaseMetaDataWrapper metaData, Table table, ForeignKey fk, IIndex index) throws SQLException { return fk.getName().equalsIgnoreCase(index.getName()); } }