/**
* 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;
}
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();
}
});
}
public static void main(String arg[]) {
Hashtable ignoreList = new Hashtable();
Class cl = null;
Model model = null;
System.out.println("Synchronizing forms with database...");
Db.init();
try {
DatabaseMetaData meta = Db.getCon().getMetaData();
String[] types = {"TABLE"};
ResultSet rs = meta.getTables(null, null, "%", types);
// read ignore.list
ignoreList = AutogenerateUtil.readIgnoreList();
// prepare directory
File fDir = new File("../../web/WEB-INF/views/crud_form");
if (!fDir.exists()) fDir.mkdir();
while (rs.next()) {
// proper file name generationm
String className = "";
String tableName = rs.getString("TABLE_NAME");
className = StringUtil.toProperClassName(tableName); // space allowed...
// tableName = tableName.toUpperCase(); //If Oracle that need uppercase tablename. In MySQL
// in Mac OS X (and probably Linux), it mustbe case sensitive
// open table
String sql = "select * from " + tableName;
PreparedStatement pstmt =
Db.getCon()
.prepareStatement(sql, ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
ResultSet resultSet = pstmt.executeQuery();
ResultSetMetaData metaColumn = resultSet.getMetaData();
int nColoumn = metaColumn.getColumnCount();
// get foreign keys,and stored it in hashtable
ResultSet rsFk = meta.getImportedKeys(Db.getCon().getCatalog(), null, tableName);
Hashtable hashFk = new Hashtable();
System.out.println("FK Infos for table " + tableName);
while (rsFk.next()) {
String pkTableName = rsFk.getString("PKTABLE_NAME");
String pkColumnName = rsFk.getString("PKCOLUMN_NAME");
String fkColumnName = rsFk.getString("FKCOLUMN_NAME");
int fkSequence = rsFk.getInt("KEY_SEQ");
System.out.println(
tableName + "." + fkColumnName + " => " + pkTableName + "." + pkColumnName);
hashFk.put(fkColumnName, pkColumnName);
hashFk.put(fkColumnName + "_table", pkTableName);
}
rsFk.close();
// create form page
System.out.println(
"Creating form page for "
+ tableName
+ " from table + "
+ application.config.Database.DB
+ "."
+ tableName);
fDir = new File("../../web/WEB-INF/views/" + tableName);
if (!fDir.exists()) fDir.mkdir();
File f = new File("../../web/WEB-INF/views/" + tableName + "/form_" + tableName + ".jsp");
if (ignoreList.get("form_" + tableName + ".jsp") != null) {
Logger.getLogger(GenerateForm.class.getName())
.log(Level.INFO, "Ignoring creation of form_" + tableName + ".jsp");
} else {
Writer out = new FileWriter(f);
out.write(
"<%@ page contentType=\"text/html; charset=UTF-8\" language=\"java\" import=\"java.sql.*,recite18th.library.Db,application.config.Config,recite18th.library.Pagination\" %>");
out.write("<%@ taglib uri=\"http://java.sun.com/jsp/jstl/core\" prefix=\"c\" %>\n");
out.write("<%@ taglib uri=\"http://java.sun.com/jsp/jstl/functions\" prefix=\"fn\" %>\n");
// create model for this class, use in detecting its PK Field
cl = Class.forName("application.models." + className + "Model");
model = (Model) cl.newInstance();
// iterate all columns
resultSet.beforeFirst();
resultSet.next();
out.write(
"<table border=\"1\" align=\"center\" cellpadding=\"0\" cellspacing=\"0\" bordercolor=\"#E8EDFF\">\n");
out.write("<tr>\n");
out.write("<td>\n");
out.write(
"<form action=\"<%=Config.base_url%>index/"
+ className
+ "/save\" method=\"post\" enctype=\"multipart/form-data\">\n"); // I hope it's
// okay to
// default it to
// multipart data
out.write("<table id=\"hor-zebra\" summary=\"Form " + className + "\">\n");
out.write("<thead>\n");
out.write("<tr>\n");
out.write("<th colspan=\"2\" class=\"odd\" scope=\"col\">Form " + className + " </th>\n");
out.write("</tr>\n");
out.write("</thead>\n");
out.write("<tbody>\n");
for (int i = 1; i <= nColoumn; i++) {
String columnName = metaColumn.getColumnName(i);
String dataType = metaColumn.getColumnClassName(i);
out.write("<tr>\n");
// if(!columnName.equals(model.getPkFieldName())) // implementing the case of PK not
// AutoIncrement
// if(!metaColumn.isAutoIncrement(i))
// {
// varying field input for type
// foreign field, as chooser page view
if (hashFk.get(columnName)
!= null) // TODO: what if PK is chooser also?? :) CUrrently I add it manually the
// hidden_*Pk_nama* field
{
String fkTableName = hashFk.get(columnName + "_table") + "";
String fkColumnName = hashFk.get(columnName) + "";
String fkController = StringUtil.toProperClassName(fkTableName);
out.write("<td>" + columnName + "</td>\n");
out.write("<td>\n");
out.write(
"<input name=\""
+ columnName
+ "\" type=\"hidden\" id=\""
+ columnName
+ "\" value=\"${model."
+ columnName
+ "}\"/>\n");
out.write(
"<input name=\"label_"
+ columnName
+ "\" readonly=\"true\" type=\"text\" id=\"label_"
+ columnName
+ "\" value=\"${model."
+ columnName
+ "}\"/>\n"); // TODO : translate I
out.write(
"<a href=\"<%=Config.base_url%>index/"
+ fkController
+ "/chooseView?height=220&width=700\" class=\"thickbox\">Pilih</a>");
out.write("</td>\n");
} else {
// regular field input, not foreign key case
if (!columnName.equals(model.getPkFieldName())) {
out.write("<td>" + columnName + "</td>\n");
out.write("<td>\n");
// ENUM Column, displayed as HTML SELECT. May will only work for mysql only...
Logger.getLogger(GenerateForm.class.getName())
.log(Level.INFO, columnName + " type is " + metaColumn.getColumnType(i));
if (metaColumn.getColumnType(i) == 1) {
String enum_content[][] =
Db.getDataSet(
"SELECT SUBSTRING(COLUMN_TYPE,6,length(SUBSTRING(COLUMN_TYPE,6))-1) as enum_content "
+ " FROM information_schema.COLUMNS "
+ " WHERE TABLE_NAME='"
+ tableName
+ "' "
+ " AND COLUMN_NAME='"
+ columnName
+ "'");
if (enum_content.length > 0) {
// Logger.getLogger(Model.class.getName()).log(Level.INFO, "Enum Content = " +
// enum_content[0][0]);
String enum_list[] = enum_content[0][0].split(",");
out.write("<select name=\"" + columnName + "\" id=\"" + columnName + "\">\n");
for (int ienum_list = 0; ienum_list < enum_list.length; ienum_list++)
out.write(
"\t<option <c:if test=\"${model."
+ columnName
+ "=='"
+ enum_list[ienum_list].substring(
1, enum_list[ienum_list].length() - 1)
+ "'}\"> selected=\"selected\" </c:if> value=\""
+ enum_list[ienum_list].substring(
1, enum_list[ienum_list].length() - 1)
+ "\">"
+ enum_list[ienum_list].substring(
1, enum_list[ienum_list].length() - 1)
+ "</option>\n");
out.write("</select>\n\n");
} else {
// no enum content detected.. :)
out.write(
"<input name=\""
+ columnName
+ "\" type=\"text\" id=\""
+ columnName
+ "\" value=\"${model."
+ columnName
+ "}\"/>\n");
}
} else if (metaColumn.getColumnType(i) == 91) {
out.write(
"<input name=\""
+ columnName
+ "\" type=\"text\" id=\""
+ columnName
+ "\" value=\"${model."
+ columnName
+ "}\"/>\n");
out.write("<script>\n");
out.write(
" if(!isValidDate($('#"
+ columnName
+ "').val())) $('#"
+ columnName
+ "').val('1980-1-1');\n"); // TODO: default value
out.write(" (function($){\n");
out.write(" $('#" + columnName + "').click(function() {\n");
out.write(" $('#" + columnName + "').DatePickerShow();\n");
out.write(" });\n");
out.write(" $('#" + columnName + "').DatePicker({\n");
out.write(" format:'Y-m-d',\n");
out.write(" date: $('#" + columnName + "').val(),\n");
out.write(" current: $('#" + columnName + "').val(),\n");
out.write(" starts: 1,\n");
out.write(" position: 'r',\n");
out.write(" onBeforeShow: function(){\n");
out.write(
" $('#"
+ columnName
+ "').DatePickerSetDate($('#"
+ columnName
+ "').val(), true);\n");
out.write(" },\n");
out.write(" onChange: function(formated, dates){\n");
out.write(" $('#" + columnName + "').DatePickerHide();\n");
out.write(" $('#" + columnName + "').val(formated);\n");
out.write(" }\n");
out.write(" });\n");
out.write(" })(jQuery)\n");
out.write(" </script>\n");
} else {
out.write(
"<input name=\""
+ columnName
+ "\" type=\"text\" id=\""
+ columnName
+ "\" value=\"${model."
+ columnName
+ "}\"/>\n");
out.write("${" + columnName + "_error}\n"); // regular input field
}
} else { // PK case
if (metaColumn.isAutoIncrement(i)) {
out.write(
"<input name=\"hidden_"
+ columnName
+ "\" type=\"hidden\" id=\"hidden_"
+ columnName
+ "\" value=\"${model."
+ columnName
+ "}\"/>\n");
} else {
out.write("<td>" + columnName + "</td>\n");
out.write("<td>\n");
out.write(
"<input name=\""
+ columnName
+ "\" type=\"text\" id=\""
+ columnName
+ "\" value=\"${model."
+ columnName
+ "}\"/>\n");
out.write("${" + columnName + "_error}\n");
out.write(
"<input name=\"hidden_"
+ columnName
+ "\" type=\"hidden\" id=\"hidden_"
+ columnName
+ "\" value=\"${model."
+ columnName
+ "}\"/>\n");
}
}
out.write("</td>\n");
}
out.write("</tr>\n");
}
out.write("<tr class=\"odd\">\n");
out.write("<td> </td>\n");
out.write("<td><input type=\"submit\" name=\"Submit\" value=\"Simpan\">");
out.write(
"<input name=\"Button\" type=\"button\" id=\"Submit\" value=\"Batal\" onClick=\"javascript:history.back(-1);\"></td>\n");
out.write("</tr>\n");
out.write("</tbody>\n");
out.write("</table>\n");
out.write("</form></td>\n");
out.write("</tr>\n");
out.write("</table>\n");
out.flush();
out.close();
}
// create viewPage
if (ignoreList.get("view_" + tableName + ".jsp") != null) {
Logger.getLogger(GenerateForm.class.getName())
.log(Level.INFO, "Ignoring creation of view_" + tableName + ".jsp");
} else {
System.out.println("Creating view page " + tableName);
fDir = new File("../../web/WEB-INF/views/" + tableName);
if (!fDir.exists()) fDir.mkdir();
File fView =
new File("../../web/WEB-INF/views/" + tableName + "/view_" + tableName + ".jsp");
Writer outView = new FileWriter(fView);
outView.write(
"<%@ page contentType=\"text/html; charset=UTF-8\" language=\"java\" import=\"java.sql.*,recite18th.library.Db,application.config.Config,recite18th.library.Pagination\" %>");
outView.write("<%@ taglib uri=\"http://java.sun.com/jsp/jstl/core\" prefix=\"c\" %>\n");
outView.write(
"<%@ taglib uri=\"http://java.sun.com/jsp/jstl/functions\" prefix=\"fn\" %>\n");
outView.write("<% int pagenum = 0; %>\n");
// outView.write("<%@ include file=\"/WEB-INF/views/header.jsp\" %>");
outView.write(
"<a href=\"<%=Config.base_url%>index/" + className + "/input/-1\">Tambah Data</a>\n");
outView.write(
"|| <a href=\"<%=Config.base_url%>index/" + className + "/print\">Cetak</a>\n");
outView.write("<table width=\"100%\" id=\"rounded-corner\">\n");
outView.write("<thead>\n");
// iterate all columns : table header
outView.write(" <tr>\n");
outView.write(" <th scope=\"col\" class=\"rounded-company\">No.</th>\n");
resultSet.beforeFirst();
resultSet.next();
// get Primary Key Field Name : often use
String pkFieldName = "";
try {
Class params[] = null;
Method objMethod = cl.getMethod("getPkFieldName", params);
pkFieldName = "" + objMethod.invoke(model);
} catch (Exception ex) {
Logger.getLogger(Model.class.getName()).log(Level.SEVERE, null, ex);
}
// ALL Lower Case
pkFieldName = pkFieldName.toLowerCase();
// customize column view page
for (int i = 1; i <= nColoumn; i++) {
String columnName =
metaColumn.getColumnName(i).toLowerCase(); // Caution : ALL LowerCase
String dataType = metaColumn.getColumnClassName(i);
String thClass = "rounded-q1";
String thTitle = StringUtil.toProperFieldTitle(columnName);
if (TableCustomization.getTable(tableName)
!= null) // there is customization for this table
{
if (TableCustomization.getTable(tableName).get(columnName) != null) {
thTitle = TableCustomization.getTable(tableName).get(columnName) + "";
outView.write(
" <th scope=\"col\" class=\"" + thClass + "\">" + thTitle + "</th>\n");
}
} else { // standard view for this table : hide PK, because mostly is auto increment
if (!metaColumn.isAutoIncrement(i))
outView.write(
" <th scope=\"col\" class=\"" + thClass + "\">" + thTitle + "</th>\n");
}
}
outView.write(" <th scope=\"col\" class=\"rounded-q4\">Aksi</th>\n");
outView.write(" </tr>\n");
outView.write("</thead>\n");
outView.write("<tfoot>\n");
outView.write(" <tr>\n");
outView.write(
" <td colspan=\""
+ (nColoumn + 1)
+ "\" class=\"rounded-foot-left\"><%=Pagination.createLinks(pagenum)%></td>\n");
outView.write(" <td class=\"rounded-foot-right\"> </td>\n");
outView.write(" </tr>\n");
outView.write("</tfoot>\n");
outView.write("<tbody>\n");
outView.write(" <c:forEach items=\"${row}\" var=\"item\" varStatus=\"status\" >\n");
outView.write(" <tr>\n");
outView.write(" <td>${status.count}</td>\n");
// iterate all columns : table data
resultSet.beforeFirst();
resultSet.next();
for (int i = 1; i <= nColoumn; i++) {
String columnName = metaColumn.getColumnName(i);
// if(!columnName.equals(pkFieldName)) //TOFIX : currently, PK Field is not shown
if (TableCustomization.getTable(tableName) != null) {
if (TableCustomization.getTable(tableName).get(columnName) != null) {
outView.write(" <td>${item." + columnName + "}</td>\n");
}
} else {
if (!metaColumn.isAutoIncrement(i))
outView.write(" <td>${item." + columnName + "}</td>\n");
}
}
outView.write(" <td>\n");
outView.write(
" <a href=\"<%=Config.base_url%>index/"
+ className
+ "/input/${item."
+ pkFieldName
+ "}\">Ubah</a>\n");
outView.write(
" <a href=\"<%=Config.base_url%>index/"
+ className
+ "/delete/${item."
+ pkFieldName
+ "}\" onClick=\"return confirm('Apakah Anda yakin?');\">Hapus</a>\n");
outView.write(" </td>\n");
outView.write(" </tr>\n");
outView.write(" </c:forEach>\n");
outView.write("</tbody>\n");
outView.write("</table>\n");
// outView.write("<%@ include file=\"/WEB-INF/views/footer.jsp\" %>");
outView.flush();
outView.close();
}
}
} catch (Exception e) {
e.printStackTrace();
}
}
public void testMetaData() {
String ddl0 =
"DROP TABLE ADDRESSBOOK IF EXISTS; DROP TABLE ADDRESSBOOK_CATEGORY IF EXISTS; DROP TABLE USER IF EXISTS;";
String ddl1 =
"CREATE TABLE USER(USER_ID INTEGER NOT NULL PRIMARY KEY,LOGIN_ID VARCHAR(128) NOT NULL,USER_NAME VARCHAR(254) DEFAULT ' ' NOT NULL,CREATE_DATE TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,UPDATE_DATE TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,LAST_ACCESS_DATE TIMESTAMP,CONSTRAINT IXUQ_LOGIN_ID0 UNIQUE(LOGIN_ID))";
String ddl2 =
"CREATE TABLE ADDRESSBOOK_CATEGORY(USER_ID INTEGER NOT NULL,CATEGORY_ID INTEGER DEFAULT 0 NOT NULL,CATEGORY_NAME VARCHAR(60) DEFAULT '' NOT NULL,CONSTRAINT SYS_PK_ADDRESSBOOK_CATEGORY PRIMARY KEY(USER_ID,CATEGORY_ID),CONSTRAINT FK_ADRBKCAT1 FOREIGN KEY(USER_ID) REFERENCES USER(USER_ID) ON DELETE CASCADE)";
String ddl3 =
"CREATE TABLE ADDRESSBOOK(USER_ID INTEGER NOT NULL,ADDRESSBOOK_ID INTEGER NOT NULL,CATEGORY_ID INTEGER DEFAULT 0 NOT NULL,FIRST VARCHAR(64) DEFAULT '' NOT NULL,LAST VARCHAR(64) DEFAULT '' NOT NULL,NOTE VARCHAR(128) DEFAULT '' NOT NULL,CONSTRAINT SYS_PK_ADDRESSBOOK PRIMARY KEY(USER_ID,ADDRESSBOOK_ID),CONSTRAINT FK_ADRBOOK1 FOREIGN KEY(USER_ID,CATEGORY_ID) REFERENCES ADDRESSBOOK_CATEGORY(USER_ID,CATEGORY_ID) ON DELETE CASCADE)";
String result1 = "1";
String result2 = "2";
String result3 = "3";
String result4 = "4";
String result5 = "5";
try {
stmnt.execute(ddl0);
stmnt.execute(ddl1);
stmnt.execute(ddl2);
stmnt.execute(ddl3);
DatabaseMetaData md = connection.getMetaData();
{
// System.out.println(md.getDatabaseMajorVersion());
// System.out.println(md.getDatabaseMinorVersion());
System.out.println(md.getDatabaseProductName());
System.out.println(md.getDatabaseProductVersion());
System.out.println(md.getDefaultTransactionIsolation());
System.out.println(md.getDriverMajorVersion());
System.out.println(md.getDriverMinorVersion());
System.out.println(md.getDriverName());
System.out.println(md.getDriverVersion());
System.out.println(md.getExtraNameCharacters());
System.out.println(md.getIdentifierQuoteString());
// System.out.println(md.getJDBCMajorVersion());
// System.out.println(md.getJDBCMinorVersion());
System.out.println(md.getMaxBinaryLiteralLength());
System.out.println(md.getMaxCatalogNameLength());
System.out.println(md.getMaxColumnsInGroupBy());
System.out.println(md.getMaxColumnsInIndex());
System.out.println(md.getMaxColumnsInOrderBy());
System.out.println(md.getMaxColumnsInSelect());
System.out.println(md.getMaxColumnsInTable());
System.out.println(md.getMaxConnections());
System.out.println(md.getMaxCursorNameLength());
System.out.println(md.getMaxIndexLength());
System.out.println(md.getMaxProcedureNameLength());
System.out.println(md.getMaxRowSize());
System.out.println(md.getMaxSchemaNameLength());
System.out.println(md.getMaxStatementLength());
System.out.println(md.getMaxStatements());
System.out.println(md.getMaxTableNameLength());
System.out.println(md.getMaxUserNameLength());
System.out.println(md.getNumericFunctions());
System.out.println(md.getProcedureTerm());
// System.out.println(md.getResultSetHoldability());
System.out.println(md.getSchemaTerm());
System.out.println(md.getSearchStringEscape());
System.out.println(md.getSQLKeywords());
// System.out.println(md.getSQLStateType());
System.out.println(md.getStringFunctions());
System.out.println(md.getSystemFunctions());
System.out.println(md.getTimeDateFunctions());
System.out.println(md.getURL());
System.out.println(md.getUserName());
System.out.println(md.importedKeyCascade);
System.out.println(md.isCatalogAtStart());
System.out.println(md.isReadOnly());
ResultSet rs;
rs = md.getPrimaryKeys(null, null, "USER");
ResultSetMetaData rsmd = rs.getMetaData();
String result0 = "";
for (; rs.next(); ) {
for (int i = 0; i < rsmd.getColumnCount(); i++) {
result0 += rs.getString(i + 1) + ":";
}
result0 += "\n";
}
rs.close();
System.out.println(result0);
}
{
ResultSet rs;
rs = md.getBestRowIdentifier(null, null, "USER", 0, true);
ResultSetMetaData rsmd = rs.getMetaData();
String result0 = "";
for (; rs.next(); ) {
for (int i = 0; i < rsmd.getColumnCount(); i++) {
result0 += rs.getString(i + 1) + ":";
}
result0 += "\n";
}
rs.close();
System.out.println(result0);
}
{
ResultSet rs = md.getImportedKeys(null, null, "ADDRESSBOOK");
ResultSetMetaData rsmd = rs.getMetaData();
result1 = "";
for (; rs.next(); ) {
for (int i = 0; i < rsmd.getColumnCount(); i++) {
result1 += rs.getString(i + 1) + ":";
}
result1 += "\n";
}
rs.close();
System.out.println(result1);
}
{
ResultSet rs =
md.getCrossReference(null, null, "ADDRESSBOOK_CATEGORY", null, null, "ADDRESSBOOK");
ResultSetMetaData rsmd = rs.getMetaData();
result2 = "";
for (; rs.next(); ) {
for (int i = 0; i < rsmd.getColumnCount(); i++) {
result2 += rs.getString(i + 1) + ":";
}
result2 += "\n";
}
rs.close();
System.out.println(result2);
}
{
ResultSet rs = md.getExportedKeys(null, null, "USER");
ResultSetMetaData rsmd = rs.getMetaData();
result3 = "";
for (; rs.next(); ) {
for (int i = 0; i < rsmd.getColumnCount(); i++) {
result3 += rs.getString(i + 1) + ":";
}
result3 += "\n";
}
rs.close();
System.out.println(result3);
}
{
ResultSet rs = md.getCrossReference(null, null, "USER", null, null, "ADDRESSBOOK_CATEGORY");
ResultSetMetaData rsmd = rs.getMetaData();
result4 = "";
for (; rs.next(); ) {
for (int i = 0; i < rsmd.getColumnCount(); i++) {
result4 += rs.getString(i + 1) + ":";
}
result4 += "\n";
}
rs.close();
System.out.println(result4);
}
{
stmnt.execute("DROP TABLE T IF EXISTS;");
stmnt.executeQuery("CREATE TABLE T (I IDENTITY, A CHAR, B CHAR);");
stmnt.executeQuery(
"INSERT INTO T VALUES (NULL, 'get_column_name', '" + getColumnName + "');");
ResultSet rs = stmnt.executeQuery("SELECT I, A, B, A \"aliasA\", B \"aliasB\" FROM T;");
ResultSetMetaData rsmd = rs.getMetaData();
result5 = "";
for (; rs.next(); ) {
for (int i = 0; i < rsmd.getColumnCount(); i++) {
result5 += rsmd.getColumnName(i + 1) + ":" + rs.getString(i + 1) + ":";
}
result5 += "\n";
}
rs.close();
rs = stmnt.executeQuery("SELECT I, A, B, A \"aliasA\", B \"aliasB\" FROM T;");
;
rsmd = rs.getMetaData();
for (; rs.next(); ) {
for (int i = 0; i < rsmd.getColumnCount(); i++) {
result5 += rsmd.getColumnLabel(i + 1) + ":" + rs.getString(i + 1) + ":";
}
result5 += "\n";
}
System.out.println(result5);
System.out.println("first column identity: " + rsmd.isAutoIncrement(1));
rsmd.isCaseSensitive(1);
rsmd.isCurrency(1);
rsmd.isDefinitelyWritable(1);
rsmd.isNullable(1);
rsmd.isReadOnly(1);
rsmd.isSearchable(1);
rsmd.isSigned(1);
rsmd.isWritable(1);
rs.close();
// test identity with PreparedStatement
pstmnt = connection.prepareStatement("INSERT INTO T VALUES (?,?,?)");
pstmnt.setString(1, null);
pstmnt.setString(2, "test");
pstmnt.setString(3, "test2");
pstmnt.executeUpdate();
pstmnt = connection.prepareStatement("call identity()");
ResultSet rsi = pstmnt.executeQuery();
rsi.next();
int identity = rsi.getInt(1);
System.out.println("call identity(): " + identity);
rsi.close();
}
} catch (SQLException e) {
fail(e.getMessage());
}
System.out.println("testMetaData complete");
// assert equality of exported and imported with xref
assertEquals(result1, result2);
assertEquals(result3, result4);
}