private ImmutableMap<String, JdbcTable> computeTables() {
Connection connection = null;
ResultSet resultSet = null;
try {
connection = dataSource.getConnection();
DatabaseMetaData metaData = connection.getMetaData();
resultSet = metaData.getTables(catalog, schema, null, null);
final ImmutableMap.Builder<String, JdbcTable> builder = ImmutableMap.builder();
while (resultSet.next()) {
final String tableName = resultSet.getString(3);
final String catalogName = resultSet.getString(1);
final String schemaName = resultSet.getString(2);
final String tableTypeName = resultSet.getString(4);
// Clean up table type. In particular, this ensures that 'SYSTEM TABLE',
// returned by Phoenix among others, maps to TableType.SYSTEM_TABLE.
// We know enum constants are upper-case without spaces, so we can't
// make things worse.
final String tableTypeName2 = tableTypeName.toUpperCase().replace(' ', '_');
final TableType tableType = Util.enumVal(TableType.class, tableTypeName2);
final JdbcTable table = new JdbcTable(this, catalogName, schemaName, tableName, tableType);
builder.put(tableName, table);
}
return builder.build();
} catch (SQLException e) {
throw new RuntimeException("Exception while reading tables", e);
} finally {
close(connection, null, resultSet);
}
}
public void checkTables(consumer<Boolean> consumer) {
Connection connection = null;
ResultSet result = null;
try {
connection = HikariManager.getInstance().getConnection();
DatabaseMetaData dbm = connection.getMetaData();
boolean playersExists = false;
boolean periodicExists = false;
boolean allExist = false;
result = dbm.getTables(null, null, HikariManager.getInstance().prefix + "players", null);
if (result.next()) playersExists = true;
result = dbm.getTables(null, null, HikariManager.getInstance().prefix + "periodic", null);
if (result.next()) periodicExists = true;
if (playersExists && periodicExists) allExist = true;
if (consumer != null) consumer.accept(allExist);
} catch (SQLException e) {
e.printStackTrace();
} finally {
HikariManager.getInstance().close(connection, null, result);
}
}
SqlDialect get(DataSource dataSource) {
Connection connection = null;
try {
connection = dataSource.getConnection();
DatabaseMetaData metaData = connection.getMetaData();
String productName = metaData.getDatabaseProductName();
String productVersion = metaData.getDatabaseProductVersion();
List key = Arrays.asList(productName, productVersion);
SqlDialect dialect = map.get(key);
if (dialect == null) {
final SqlDialect.DatabaseProduct product =
SqlDialect.getProduct(productName, productVersion);
dialect = new SqlDialect(product, productName, metaData.getIdentifierQuoteString());
map.put(key, dialect);
}
connection.close();
connection = null;
return dialect;
} catch (SQLException e) {
throw new RuntimeException(e);
} finally {
if (connection != null) {
try {
connection.close();
} catch (SQLException e) {
// ignore
}
}
}
}
/** Creates tables if they do not exist. */
private void initializeTables() {
Connection conn = null;
Statement statement = null;
try {
conn = retrieveConnection();
statement = conn.createStatement();
statement.setQueryTimeout(30);
// One table holding the playername id relation
String playerQuery =
String.format(
"CREATE TABLE IF NOT EXISTS %s (id INTEGER PRIMARY KEY AUTOINCREMENT, %s STRING)",
playerTable, "name");
statement.executeUpdate(playerQuery);
// One column for every message
StringBuilder columns = new StringBuilder();
for (MessageNode node : MessageNode.getMessageNodes()) {
MsgCategory cat = messages.getCat(node);
if (node.getColumnName() != null
&& (cat == MsgCategory.TUTORIAL || cat == MsgCategory.ONE_TIME)) {
columns.append(',');
columns.append(node.getColumnName());
}
}
String msgQuery =
String.format(
"CREATE TABLE IF NOT EXISTS %s (id INTEGER PRIMARY KEY UNIQUE %s)",
msgTable, columns);
statement.executeUpdate(msgQuery);
// Check if all columns are present
DatabaseMetaData dmd = conn.getMetaData();
// Add missing columns
for (MessageNode node : MessageNode.getMessageNodes()) {
MsgCategory cat = messages.getCat(node);
if (cat == MsgCategory.TUTORIAL || cat == MsgCategory.ONE_TIME) {
ResultSet set = dmd.getColumns(null, null, msgTable, node.getColumnName());
if (!set.next()) {
String updateQuery =
String.format("ALTER TABLE %s ADD COLUMN %s", msgTable, node.getColumnName());
statement.executeUpdate(updateQuery);
}
}
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
if (conn != null) conn.close();
if (statement != null) statement.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
private List getAllTables(Connection conn) throws SQLException {
DatabaseMetaData dbMetaData = conn.getMetaData();
ResultSet rs = dbMetaData.getTables(getCatalog(), getSchema(), null, null);
List tables = new ArrayList();
while (rs.next()) {
tables.add(createTable(conn, rs));
}
return tables;
}
public synchronized void serialEvent(SerialPortEvent oEvent) {
try {
switch (oEvent.getEventType()) {
case SerialPortEvent.DATA_AVAILABLE:
if (input == null) {
System.out.println("here11");
input = new BufferedReader(new InputStreamReader(serialPort.getInputStream()));
}
String inputLine = input.readLine();
// System.out.println(input.readLine().trim());
if (inputLine.equals("")) {
} else {
String url = "jdbc:mysql://localhost/secureplanet";
Properties prop = new Properties();
prop.setProperty("user", "root");
prop.setProperty("password", "toor");
Driver d = new com.mysql.jdbc.Driver();
Connection conn = d.connect(url, prop);
if (conn == null) {
System.out.println("connection failed");
return;
}
DatabaseMetaData dm = conn.getMetaData();
String dbversion = dm.getDatabaseProductVersion();
String dbname = dm.getDatabaseProductName();
System.out.println("name:" + dbname);
System.out.println("version:" + dbversion);
String rfidtoken = inputLine.trim();
Statement stmt = conn.createStatement();
Double lat = 17.4416;
Double lng = 78.3826;
String sql =
"INSERT INTO smarttracking "
+ "VALUES ('"
+ rfidtoken
+ "','"
+ lat
+ "','"
+ lng
+ "')";
stmt.executeUpdate(sql);
}
break;
default:
break;
}
} catch (Exception e) {
e.printStackTrace();
}
}
/** Returns a table's primary key columns as a Set of strings. */
public static Set<String> getPrimaryKey(DatabaseMetaData metadata, String tableName)
throws Exception {
Set<String> columns = new HashSet<String>();
ResultSet keys =
metadata.getPrimaryKeys(
metadata.getConnection().getCatalog(), metadata.getUserName(), tableName);
while (keys.next()) {
columns.add(keys.getString(PRIMARY_PK_COL_NAME));
}
keys.close();
return columns;
}
public static String[] getSchemas(Connection connection) throws Exception {
if (connection != null) {
List<String> schemaList = new ArrayList<String>();
DatabaseMetaData mObject = connection.getMetaData();
ResultSet schemas = mObject.getSchemas();
while (schemas.next()) {
schemaList.add(schemas.getString(DBConstants.DataServiceGenerator.TABLE_SCHEM));
}
String str[] = schemaList.toArray(new String[schemaList.size()]);
return str;
} else {
return null;
}
}
private Table _getTable(String catalog, String schema, String tableName) throws SQLException {
if (tableName == null || tableName.trim().length() == 0)
throw new IllegalArgumentException("tableName must be not empty");
catalog = StringHelper.defaultIfEmpty(catalog, null);
schema = StringHelper.defaultIfEmpty(schema, null);
Connection conn = getConnection();
DatabaseMetaData dbMetaData = conn.getMetaData();
ResultSet rs = dbMetaData.getTables(catalog, schema, tableName, null);
while (rs.next()) {
Table table = createTable(conn, rs);
return table;
}
return null;
}
boolean tiene_tablas() {
DatabaseMetaData dbmd = null;
Integer contador = 0;
try {
dbmd = conexion.getMetaData();
String[] tipo = {"TABLE"};
ResultSet rs = dbmd.getTables(null, null, "%", tipo);
while (rs.next()) {
contador++;
}
} catch (SQLException e) {
System.out.println("Error al obtener información de la bd...");
}
return contador > 0;
}
private ResultSet getColumnNames(
DatabaseMetaData metaData,
String schema,
String dbName,
String tableName,
String columnNamePattern)
throws SQLException {
ResultSet columnNames = metaData.getColumns(dbName, schema, tableName, columnNamePattern);
if (columnNames.next()) {
columnNames = metaData.getColumns(dbName, schema, tableName, columnNamePattern);
} else {
columnNames = metaData.getColumns(null, schema, tableName, columnNamePattern);
}
return columnNames;
}
private String getPrimaryKey(
DatabaseMetaData meta, String dbName, String schema, String tableName) throws SQLException {
String pKey = null;
ResultSet resultSet = meta.getPrimaryKeys(dbName, schema, tableName);
if (resultSet.next()) {
resultSet = meta.getPrimaryKeys(dbName, schema, tableName);
} else {
resultSet = meta.getPrimaryKeys(null, schema, tableName);
}
while (resultSet.next()) {
pKey = resultSet.getString(DBConstants.DataServiceGenerator.COLUMN_NAME);
return pKey;
}
return pKey;
}
// UTILITY
public boolean hasTable(String tableName) {
try {
DatabaseMetaData dbm = connection.getMetaData();
ResultSet tables = dbm.getTables(null, null, tableName, null);
while (tables.next()) {
String foundName = tables.getString("TABLE_NAME");
if (tableName.equals(foundName)) {
return true;
}
}
} catch (SQLException e) {
e.printStackTrace();
}
return false;
}
@Override
public ArrayList<String> getListOfTables(Connection conn)
throws SQLException, ClassNotFoundException {
ArrayList<String> tableNames = new ArrayList<String>();
DatabaseMetaData dmd = conn.getMetaData();
// Pedro: 2012/12/03 comment out to enable loading Views in Karma (on behalf of Maria)
// ResultSet rs = dmd.getTables(null, null, null, new String[] {"TABLE"});
ResultSet rs = dmd.getTables(null, null, null, new String[] {"TABLE", "VIEW"});
while (rs.next()) {
String tablename = rs.getString(3);
String schema = rs.getString(2);
if (schema != null && schema.length() > 0) tableNames.add(schema + "." + tablename);
else tableNames.add(tablename);
}
Collections.sort(tableNames);
return tableNames;
}
private Connection createDatasource() throws ClassNotFoundException, SQLException {
Connection connection = getConnection();
boolean exist = false;
int tableNameColumn = 3;
DatabaseMetaData dbm = connection.getMetaData();
for (ResultSet rs = dbm.getTables(null, null, null, null); rs.next(); ) {
if (rs.getString(tableNameColumn).equals(TABLE_NAME)) {
exist = true;
rs.close();
break;
}
}
if (!exist) {
Statement statement = connection.createStatement();
statement.executeUpdate(
"CREATE TABLE " + TABLE_NAME + " (name VARCHAR(32) PRIMARY KEY, balance FLOAT)");
}
return connection;
}
public static List<String> getTableNamesList(
DatabaseMetaData mObject, String dbName, String schema) throws SQLException {
List<String> tableList = new ArrayList<String>();
ResultSet tableNamesList = mObject.getTables(dbName, schema, null, null);
while (tableNamesList.next()) {
tableList.add(tableNamesList.getString(DBConstants.DataServiceGenerator.TABLE_NAME));
}
return tableList;
}
@Signature
public Memory getSchemas(Environment env) throws SQLException {
ResultSet schemas = metaData.getSchemas();
ArrayMemory r = new ArrayMemory();
while (schemas.next()) {
r.add(new PSqlResult(env, schemas).toArray(env));
}
return r.toConstant();
}
public static void main(String args[]) {
try {
Class.forName("org.gjt.mm.mysql.Driver");
Connection con =
DriverManager.getConnection("jdbc:mysql://localhost:3333/world", "root", "root");
DatabaseMetaData dbmd = con.getMetaData();
String table[] = {"TABLE"};
ResultSet rs = dbmd.getTables(null, null, null, table);
while (rs.next()) {
System.out.println(rs.getString(3));
}
con.close();
} catch (Exception e) {
System.out.println(e);
}
}
private static Map<PwmAboutProperty, String> getConnectionDebugProperties(
final Connection connection) {
if (connection != null) {
try {
final Map<PwmAboutProperty, String> returnObj = new LinkedHashMap<>();
final DatabaseMetaData databaseMetaData = connection.getMetaData();
returnObj.put(PwmAboutProperty.database_driverName, databaseMetaData.getDriverName());
returnObj.put(PwmAboutProperty.database_driverVersion, databaseMetaData.getDriverVersion());
returnObj.put(
PwmAboutProperty.database_databaseProductName,
databaseMetaData.getDatabaseProductName());
returnObj.put(
PwmAboutProperty.database_databaseProductVersion,
databaseMetaData.getDatabaseProductVersion());
return Collections.unmodifiableMap(returnObj);
} catch (SQLException e) {
LOGGER.error("error rading jdbc meta data: " + e.getMessage());
}
}
return Collections.emptyMap();
}
/**
* Returns a table's foreign keys and their columns as a Map from the key name to the ForeignKey
* object.
*
* <p>A foreign key may not have a name. On such a database, 2 foreign keys must reference 2
* different tables. Otherwise there's no way to tell them apart and the foreign key information
* reported by DatabaseMetaData becomes ill-formed.
*/
public static Map<String, ForeignKey> getForeignKeys(DatabaseMetaData metadata, String tableName)
throws Exception {
ResultSet keys =
metadata.getImportedKeys(
metadata.getConnection().getCatalog(), metadata.getUserName(), tableName);
Map<String, ForeignKey> map = new HashMap<String, ForeignKey>();
while (keys.next()) {
String table = keys.getString(IMPORTED_PK_TAB_NAME);
String name = keys.getString(IMPORTED_FK_KEY_NAME);
if (name == null || name.length() == 0) name = "UNNAMED_FK_" + table;
ForeignKey key = map.get(name);
if (key == null) {
map.put(name, key = new ForeignKey(table));
}
key.add(keys.getString(IMPORTED_FK_COL_NAME));
}
keys.close();
return map;
}
static void truncateTable(String strTable) {
String DBMS = "";
try {
DatabaseMetaData metaData = conn.getMetaData();
DBMS = metaData.getDatabaseProductName().toLowerCase();
} catch (SQLException e) {
System.out.println("Problem determining database product name: " + e);
}
System.out.println("Truncating '" + strTable + "' ...");
try {
if (DBMS.startsWith("db2")) {
stmt.execute("TRUNCATE TABLE " + strTable + " IMMEDIATE");
} else {
stmt.execute("TRUNCATE TABLE " + strTable);
}
transCommit();
} catch (SQLException se) {
System.out.println(se.getMessage());
transRollback();
}
}
/** Returns a table's columns */
public static List<Column> getColumns(DatabaseMetaData metadata, String tableName)
throws Exception {
List<Column> columns = new ArrayList<Column>();
PreparedStatement stmt =
metadata.getConnection().prepareStatement("SELECT * FROM " + tableName);
ResultSetMetaData rsmeta = stmt.getMetaData();
for (int i = 1, ii = rsmeta.getColumnCount(); i <= ii; ++i) {
columns.add(new Column(rsmeta, i));
}
stmt.close();
return columns;
}
/**
* 初始化连接池
*
* @param props
* @param show_sql
*/
private static final void initDataSource(Properties dbProperties) {
try {
if (dbProperties == null) {
dbProperties = new Properties();
dbProperties.load(DBManager.class.getResourceAsStream(CONFIG_PATH));
}
// Class.forName(dbProperties.getProperty("jdbc.driverClass"));
for (Object key : dbProperties.keySet()) {
String skey = (String) key;
if (skey.startsWith("jdbc.")) {
String name = skey.substring(5);
cp_props.put(name, dbProperties.getProperty(skey));
if ("show_sql".equalsIgnoreCase(name)) {
show_sql = "true".equalsIgnoreCase(dbProperties.getProperty(skey));
}
}
}
dataSource = (DataSource) Class.forName(cp_props.getProperty("datasource")).newInstance();
if (dataSource.getClass().getName().indexOf("c3p0") > 0) {
// Disable JMX in C3P0
System.setProperty(
"com.mchange.v2.c3p0.management.ManagementCoordinator",
"com.mchange.v2.c3p0.management.NullManagementCoordinator");
}
log.info("Using DataSource : " + dataSource.getClass().getName());
BeanUtils.populate(dataSource, cp_props);
Connection conn = getConnection();
DatabaseMetaData mdm = conn.getMetaData();
log.info(
"Connected to " + mdm.getDatabaseProductName() + " " + mdm.getDatabaseProductVersion());
closeConnection();
} catch (Exception e) {
e.printStackTrace();
throw new DBException(e);
}
}
public Map<String, List<String>> querySchema(DBConfig config) {
Map<String, List<String>> map = new HashMap<String, List<String>>();
List<String> schemaList = config.getSchemaList();
Connection conn = null;
DatabaseMetaData databaseMetaData = null;
try {
conn = ConnectionFactory.getInstance().getConnection(config);
databaseMetaData = conn.getMetaData();
} catch (Exception e) {
e.printStackTrace();
return map;
}
for (String schema : schemaList) {
List<String> tableList = new ArrayList<String>();
try {
String localSchema = schema;
if (databaseMetaData.storesLowerCaseIdentifiers()) {
localSchema = localSchema == null ? null : localSchema.toLowerCase();
} else if (databaseMetaData.storesUpperCaseIdentifiers()) {
localSchema = localSchema == null ? null : localSchema.toUpperCase();
}
ResultSet rs = databaseMetaData.getTables(null, localSchema, null, null);
while (rs.next()) {
String tableName = rs.getString("TABLE_NAME");
tableList.add(tableName);
// System.out.println(tableName);
}
map.put(schema, tableList);
} catch (Exception e) {
e.printStackTrace();
}
}
return map;
}
private void loadDatabaseMetadata() {
if (!databaseMetadataLoaded) {
String componentName = "UNKNOWN";
Connection con = null;
try {
componentName = getMetaComponent().getName();
con = DataSourceConnectionProvider.getByComponent(componentName).getConnection();
DatabaseMetaData metaData = con.getMetaData();
supportsSchemasInDataManipulation = metaData.supportsSchemasInDataManipulation();
Collection timeDateFunctions =
Strings.toCollection(metaData.getTimeDateFunctions().toUpperCase());
//
// another solution instead of the use of 'if' would be to use a xml with
// the information of the functions from each BBDD
if ("DB2 UDB for AS/400".equals(metaData.getDatabaseProductName())
|| "Oracle".equals(metaData.getDatabaseProductName())
|| "PostgresSQL".equals(metaData.getDatabaseProductName())) {
supportsTranslateFunction = true;
}
if ("Oracle".equals(metaData.getDatabaseProductName())
|| "PostgreSQL".equals(metaData.getDatabaseProductName())) {
supportsYearFunction = supportsMonthFunction = false;
} else {
supportsYearFunction = timeDateFunctions.contains("YEAR");
supportsMonthFunction = timeDateFunctions.contains("MONTH");
}
databaseMetadataLoaded = true;
} catch (Exception ex) {
log.warn(XavaResources.getString("load_database_metadata_warning"));
} finally {
try {
if (con != null) {
con.close();
}
} catch (SQLException e) {
log.warn(XavaResources.getString("close_connection_warning"));
}
}
}
}
RelProtoDataType getRelDataType(
DatabaseMetaData metaData, String catalogName, String schemaName, String tableName)
throws SQLException {
final ResultSet resultSet = metaData.getColumns(catalogName, schemaName, tableName, null);
// Temporary type factory, just for the duration of this method. Allowable
// because we're creating a proto-type, not a type; before being used, the
// proto-type will be copied into a real type factory.
final RelDataTypeFactory typeFactory = new SqlTypeFactoryImpl();
final RelDataTypeFactory.FieldInfoBuilder fieldInfo = typeFactory.builder();
while (resultSet.next()) {
final String columnName = resultSet.getString(4);
final int dataType = resultSet.getInt(5);
final String typeString = resultSet.getString(6);
final int size = resultSet.getInt(7);
final int scale = resultSet.getInt(9);
RelDataType sqlType = sqlType(typeFactory, dataType, size, scale, typeString);
boolean nullable = resultSet.getBoolean(11);
fieldInfo.add(columnName, sqlType).nullable(nullable);
}
resultSet.close();
return RelDataTypeImpl.proto(fieldInfo.build());
}
/** Method declaration */
private void refreshTree() {
tTree.removeAll();
try {
int color_table = Color.yellow.getRGB();
int color_column = Color.orange.getRGB();
int color_index = Color.red.getRGB();
tTree.addRow("", dMeta.getURL(), "-", 0);
String usertables[] = {"TABLE"};
ResultSet result = dMeta.getTables(null, null, null, usertables);
Vector tables = new Vector();
// sqlbob@users Added remarks.
Vector remarks = new Vector();
while (result.next()) {
tables.addElement(result.getString(3));
remarks.addElement(result.getString(5));
}
result.close();
for (int i = 0; i < tables.size(); i++) {
String name = (String) tables.elementAt(i);
String key = "tab-" + name + "-";
tTree.addRow(key, name, "+", color_table);
// sqlbob@users Added remarks.
String remark = (String) remarks.elementAt(i);
if ((remark != null) && !remark.trim().equals("")) {
tTree.addRow(key + "r", " " + remark);
}
ResultSet col = dMeta.getColumns(null, null, name, null);
while (col.next()) {
String c = col.getString(4);
String k1 = key + "col-" + c + "-";
tTree.addRow(k1, c, "+", color_column);
String type = col.getString(6);
tTree.addRow(k1 + "t", "Type: " + type);
boolean nullable = col.getInt(11) != DatabaseMetaData.columnNoNulls;
tTree.addRow(k1 + "n", "Nullable: " + nullable);
}
col.close();
tTree.addRow(key + "ind", "Indices", "+", 0);
ResultSet ind = dMeta.getIndexInfo(null, null, name, false, false);
String oldiname = null;
while (ind.next()) {
boolean nonunique = ind.getBoolean(4);
String iname = ind.getString(6);
String k2 = key + "ind-" + iname + "-";
if ((oldiname == null || !oldiname.equals(iname))) {
tTree.addRow(k2, iname, "+", color_index);
tTree.addRow(k2 + "u", "Unique: " + !nonunique);
oldiname = iname;
}
String c = ind.getString(9);
tTree.addRow(k2 + "c-" + c + "-", c);
}
ind.close();
}
tTree.addRow("p", "Properties", "+", 0);
tTree.addRow("pu", "User: "******"pr", "ReadOnly: " + cConn.isReadOnly());
tTree.addRow("pa", "AutoCommit: " + cConn.getAutoCommit());
tTree.addRow("pd", "Driver: " + dMeta.getDriverName());
tTree.addRow("pp", "Product: " + dMeta.getDatabaseProductName());
tTree.addRow("pv", "Version: " + dMeta.getDatabaseProductVersion());
} catch (SQLException e) {
tTree.addRow("", "Error getting metadata:", "-", 0);
tTree.addRow("-", e.getMessage());
tTree.addRow("-", e.getSQLState());
}
tTree.update();
}
public void copyDbData(String dbin, String dbout) throws SQLException {
// Do some connections and collect statements
Connection inConn = makeConnection(dbin);
Statement inSt = inConn.createStatement();
DatabaseMetaData dbmeta = inConn.getMetaData();
Connection outConn = makeConnection(dbout);
Statement outSt = outConn.createStatement();
ResultSet tables = getTables(inConn);
forEach(
tables,
rs -> {
try {
// Okay, we'v got our table name
String tableName = rs.getString("TABLE_NAME");
// PreparedStatement st = DriverManager.getConnection(addr, user,
// passwd).prepareStatement(format("CREATE TABLE %s.%s (LIKE %s.%s)", dbout, tableName,
// dbin, tableName));
// st.execute();
// st.close();
// Lets fetch all data from it
ResultSet data = inSt.executeQuery(format("SELECT * FROM \"%s\"", tableName));
// Lets get table schema from metadata and generate creation operator
ResultSetMetaData datamd = data.getMetaData();
int columnCount = datamd.getColumnCount();
StringBuilder sb = new StringBuilder();
sb.append("CREATE TABLE ").append(tableName).append(" (");
for (int i = 1; i <= columnCount; i++) {
if (i > 1) sb.append(", ");
sb.append(datamd.getColumnName(i)).append(" ").append(datamd.getColumnTypeName(i));
if (datamd.isNullable(i) == ResultSetMetaData.columnNoNulls) sb.append(" NOT NULL");
// Tons of parameters could be parsed here except of references and keys
// if (precision != 0) {
// sb.append("(").append(precision).append(")");
// }
switch (datamd.getColumnTypeName(i)) {
case "character":
case "time":
case "":
sb.append("(").append(datamd.getPrecision(i)).append(")");
}
}
sb.append(");");
// Looks good, lets create this in our outer database
LOGGER.info("Execute: '{}'", sb.toString());
outSt.execute(sb.toString());
// Setting up primary keys
Map<String, StringBuilder> primaryKeys = new HashMap<>();
ResultSet primaryKeysRs = dbmeta.getPrimaryKeys(null, null, tableName);
forEach(
primaryKeysRs,
pkRs -> {
try {
String columName = pkRs.getString("COLUMN_NAME");
String pkName = pkRs.getString("PK_NAME");
if (!primaryKeys.containsKey(pkName)) {
primaryKeys.put(pkName, new StringBuilder(columName));
} else {
primaryKeys.get(pkName).append(", ").append(columName);
}
} catch (SQLException e) {
// Should never be reached
e.printStackTrace();
}
});
primaryKeys
.entrySet()
.forEach(
entry -> {
String pkName = entry.getKey();
String pk = entry.getValue().toString();
String query = format("ALTER TABLE %s ADD PRIMARY KEY (%s)", tableName, pk);
try {
LOGGER.info("Execute: '{}'", query);
outSt.executeUpdate(query);
} catch (SQLException e) {
LOGGER.error("Setting '{}' as primary key in '{}' failed", pk, tableName);
e.printStackTrace();
}
});
// Generate insertion statement
StringBuilder prepareQuery = new StringBuilder("INSERT INTO ");
prepareQuery.append(tableName).append(" VALUES (");
for (int i = 0; i < columnCount; i++) {
if (i > 0) prepareQuery.append(", ");
prepareQuery.append("?");
}
prepareQuery.append(");");
LOGGER.info("Prepare statement with: {}", prepareQuery);
PreparedStatement pst = outConn.prepareStatement(prepareQuery.toString());
// And put all our's data in it
forEach(
data,
dataRs -> {
try {
for (int i = 1; i <= columnCount; i++) {
pst.setObject(i, dataRs.getObject(i));
}
LOGGER.info("Execute: '{}'", pst.toString());
pst.execute();
} catch (SQLException e) {
e.printStackTrace();
}
/*
try {
StringBuilder insert = new StringBuilder();
insert.append("INSERT INTO \"").append(tableName).append("\" VALUES (");
for (int i = 1; i <= columnCount; i++) {
if (i > 1) insert.append(", ");
insert.append(dataRs.getString(i));
}
insert.append(");");
// Finally put insert it in table
// Timestamps would be inserted incorrect
LOGGER.info("Execute: '{}'", insert.toString());
outSt.executeUpdate(insert.toString());
} catch (SQLException e) {
LOGGER.error("Failed to insert into '{}'", tableName);
}
*/
});
} catch (SQLException e) {
LOGGER.error("Failed to create table");
e.printStackTrace();
}
});
// Lets deal up with foreign keys
tables.beforeFirst();
forEach(
tables,
rs -> {
try {
String tableName = rs.getString("TABLE_NAME");
ResultSet foreignKeysRs = dbmeta.getImportedKeys(null, null, tableName);
Map<String, String> pkTables = new HashMap<>();
Map<String, StringBuilder> pkKeys = new HashMap<>();
Map<String, StringBuilder> fkKeys = new HashMap<>();
forEach(
foreignKeysRs,
keyRs -> {
try {
String pkTable = keyRs.getString("PKTABLE_NAME");
String pkColumn = keyRs.getString("PKCOLUMN_NAME");
String fkColumn = keyRs.getString("FKCOLUMN_NAME");
String fkName = keyRs.getString("FK_NAME");
if (!pkTables.containsKey(fkName)) {
pkTables.put(fkName, pkTable);
pkKeys.put(fkName, new StringBuilder(pkColumn));
fkKeys.put(fkName, new StringBuilder(fkColumn));
} else {
pkKeys.get(fkName).append(", ").append(pkColumn);
fkKeys.get(fkName).append(", ").append(fkColumn);
}
} catch (SQLException e) {
e.printStackTrace();
// Should newer be reached
}
});
pkTables
.entrySet()
.forEach(
entry -> {
String keyName = entry.getKey();
String extTableName = entry.getValue();
String alterFK =
format(
"ALTER TABLE %s ADD FOREIGN KEY (%s) REFERENCES %s(%s);",
tableName,
// keyName,
fkKeys.get(keyName),
extTableName,
pkKeys.get(keyName));
LOGGER.info("Execute: '{}'", alterFK);
try {
outSt.executeUpdate(alterFK);
} catch (SQLException e) {
e.printStackTrace();
}
});
} catch (SQLException e) {
e.printStackTrace();
}
});
}
/**
* Each table description has the following columns:
*
* <p>TABLE_CAT String => table catalog (may be null) TABLE_SCHEM String => table schema (may be
* null) TABLE_NAME String => table name TABLE_TYPE String => table type. Typical types are
* "TABLE", "VIEW", "SYSTEM TABLE", "GLOBAL TEMPORARY", "LOCAL TEMPORARY", "ALIAS", "SYNONYM".
* REMARKS String => explanatory comment on the table TYPE_CAT String => the types catalog (may be
* null) TYPE_SCHEM String => the types schema (may be null) TYPE_NAME String => type name (may be
* null) SELF_REFERENCING_COL_NAME String => name of the designated "identifier" column of a typed
* table (may be null) REF_GENERATION String => specifies how values in SELF_REFERENCING_COL_NAME
* are created. Values are "SYSTEM", "USER", "DERIVED". (may be null)
*/
public ResultSet getTables(Connection conn) throws SQLException {
DatabaseMetaData md = conn.getMetaData();
ResultSet rs = md.getTables(null, null, "%", new String[] {"TABLE"});
return rs;
}
public static void main(String args[]) {
Connection connection = null;
SQLMXJdbcRowSet jrs = null;
Statement stmt = null;
ResultSet rs = null;
DatabaseMetaData dbMeta = null;
ResultSetMetaData rsMD = null;
int rowNo;
try {
Class.forName("org.trafodion.jdbc.t2.T2Driver");
} catch (Exception e) {
e.printStackTrace();
System.out.println(e.getMessage());
return;
}
try {
String url = "jdbc:t2jdbc:";
connection = DriverManager.getConnection(url);
stmt = connection.createStatement();
try {
stmt.executeUpdate("drop table jdbcrsdemo");
} catch (SQLException e) {
}
stmt.executeUpdate(
"create table jdbcrsdemo (c1 char(20), c2 smallint, c3 integer, c4 largeint, c5 varchar(120), c6 numeric(10,2), c7 decimal(10,2),c8 date, c9 time, c10 timestamp, c11 float, c12 double precision)");
stmt.executeUpdate(
"insert into jdbcrsdemo values('Doberman Pinscher', 100, 12345678, 123456789012, 'Canine', 100.12, 100.12, {d '2005-12-23'}, {t '10:11:12'}, {ts '2000-12-23 10:11:12'}, 100.12, 100.12)");
stmt.executeUpdate(
"insert into jdbcrsdemo values('Rottweiler', 150, -12345678, -123456789012, 'Canine', -100.12, -100.12, {d '2005-12-24'}, {t '10:11:12'}, {ts '2005-12-24 10:11:12'}, -100.12, -100.12)");
stmt.executeUpdate(
"insert into jdbcrsdemo values('Tiger', 101, -12345678, 123456789012, 'Feline', -100.12, 100.12, {d '2005-12-25'}, {t '10:11:12'}, {ts '2005-12-25 10:11:12.0'}, -100.12, 100.12)");
System.out.println("Creating JdbcRowSet");
jrs = new SQLMXJdbcRowSet();
jrs.setUrl(url);
for (int i = 0; i < 5; i++) {
switch (i) {
case 0:
System.out.println("");
System.out.println("Simple Select ");
jrs.setCommand("select * from jdbcrsdemo");
jrs.execute();
break;
case 1:
System.out.println("");
System.out.println("Parameterized Select - CHAR");
jrs.setCommand("select c1, c2 from jdbcrsdemo where c1 = ?");
jrs.setString(1, "Rottweiler");
jrs.execute();
break;
case 2:
System.out.println("");
System.out.println("Parameterized Select - INT");
jrs.setCommand("select c1, c2, c3 from jdbcrsdemo where c2 = ? or c2 = ?");
jrs.setInt(1, 100);
jrs.setInt(2, 150);
jrs.execute();
break;
case 3:
System.out.println("");
System.out.println("Parameterized Select - TIMESTAMP");
jrs.setCommand("select c1, c2, c3, c10 from jdbcrsdemo where c10 = ?");
jrs.setTimestamp(1, Timestamp.valueOf("2005-12-25 10:11:12.0"));
jrs.execute();
break;
case 4:
System.out.println("");
System.out.println("Parameterized Select - DECIMAL");
jrs.setCommand("select c1, c2, c3, c7 from jdbcrsdemo where c7 = ? or c7 = ?");
jrs.setBigDecimal(1, new BigDecimal("100.12"));
jrs.setBigDecimal(2, new BigDecimal("-100.12"));
jrs.execute();
break;
}
rsMD = jrs.getMetaData();
System.out.println("");
System.out.println("Printing JdbcRowSet ResultSetMetaData ...");
System.out.println("No. of Columns " + rsMD.getColumnCount());
for (int j = 1; j <= rsMD.getColumnCount(); j++) {
System.out.println(
"Column "
+ j
+ " Data Type: "
+ rsMD.getColumnTypeName(j)
+ " Name: "
+ rsMD.getColumnName(j));
}
System.out.println("");
System.out.println("Fetching JdbcRowSet rows...");
rowNo = 0;
while (jrs.next()) {
rowNo++;
System.out.println("");
System.out.println("Printing Row " + rowNo + " using getString(), getObject()");
for (int j = 1; j <= rsMD.getColumnCount(); j++) {
System.out.println("Column " + j + " - " + jrs.getString(j) + "," + jrs.getObject(j));
}
}
System.out.println("");
}
System.out.println("Calling JdbcRowSet.getDatabaseMetaData()");
dbMeta = jrs.getDatabaseMetaData();
for (int k = 0; k < 4; k++) {
switch (k) {
case 0:
System.out.println("");
System.out.println("getTypeInfo() ");
rs = dbMeta.getTypeInfo();
break;
case 1:
System.out.println("");
System.out.println("getCatalogs()");
rs = dbMeta.getCatalogs();
break;
case 2:
System.out.println("");
System.out.println("getTables() ");
rs = dbMeta.getTables(null, null, "JDB%", null);
break;
case 3:
System.out.println("");
System.out.println("getColumns()");
rs = dbMeta.getColumns(null, null, "JDBCRSDEMO", "%");
break;
default:
rs = null;
continue;
}
if (rs != null) {
rsMD = rs.getMetaData();
System.out.println("");
System.out.println("Printing ResultSetMetaData ...");
System.out.println("No. of Columns " + rsMD.getColumnCount());
for (int j = 1; j <= rsMD.getColumnCount(); j++) {
System.out.println(
"Column "
+ j
+ " Data Type: "
+ rsMD.getColumnTypeName(j)
+ " Name: "
+ rsMD.getColumnName(j));
}
System.out.println("");
System.out.println("Fetching resultSet rows...");
rowNo = 0;
while (rs.next()) {
rowNo++;
System.out.println("");
System.out.println("Printing Row " + rowNo + " using getString(), getObject()");
for (int j = 1; j <= rsMD.getColumnCount(); j++) {
System.out.println("Column " + j + " - " + rs.getString(j) + "," + rs.getObject(j));
}
}
}
System.out.println("");
System.out.println("End of Data");
if (rs != null) rs.close();
}
System.out.println("Demo Completed");
} catch (SQLException e) {
SQLException nextException;
nextException = e;
do {
System.out.println(nextException.getMessage());
System.out.println("SQLState " + nextException.getSQLState());
System.out.println("Error Code " + nextException.getErrorCode());
} while ((nextException = nextException.getNextException()) != null);
} finally {
try {
if (connection != null) connection.close();
if (jrs != null) jrs.close();
} catch (SQLException sqle) {
sqle.printStackTrace();
System.out.println(sqle.getMessage());
}
}
}