I am trying retrieve keys for a table on a MySQL database using DatabaseMetaData and trying to use InformationSchema to do it. MySQL Configuration Properties
import java.sql.*
class DataLoader {
static final String JDBC_DRIVER = "com.mysql.jdbc.Driver"
static final String DB_URL = "jdbc:mysql://localhost:3306/mydb?useInformationSchema=true"
static final String USER = "user"
static final String PASS = "password"
static final String DB = "mydb"
public static void main(String[] args) {
Connection conn = null
Statement stmt = null
try {
Class.forName(JDBC_DRIVER)
conn = DriverManager.getConnection(DB_URL, USER, PASS)
println "Database Product Name: " + conn.getMetaData().getDatabaseProductName()
println "Database Product Version: " + conn.getMetaData().getDatabaseProductVersion()
println "JDBC Driver: " + conn.getMetaData().getDriverName()
println "Driver Version: " + conn.getMetaData().getDriverVersion()
ResultSet importedKeysRS = conn.getMetaData().getImportedKeys(DB, null, "user")
} catch (Exception e) {
e.printStackTrace()
} finally {
try {
if (stmt != null) {
stmt.close()
conn.close()
}
} catch (SQLException se) {
}// do nothing
try {
if (conn != null)
conn.close()
} catch (SQLException se) {
se.printStackTrace()
}
}
}
}
When I execute the above groovy code, the following is the exception thrown.
Database Product Name: MySQL
Database Product Version: 5.6.15-rel63.0-log
JDBC Driver: MySQL-AB JDBC Driver
Driver Version: mysql-connector-java-5.1.6 ( Revision: ${svn.Revision} )
com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Column 'REFERENCED_TABLE_NAME' in where clause is ambiguous
at com.mysql.jdbc.DatabaseMetaDataUsingInfoSchema.executeMetadataQuery(DatabaseMetaDataUsingInfoSchema.java:50)
at com.mysql.jdbc.DatabaseMetaDataUsingInfoSchema.getImportedKeys(DatabaseMetaDataUsingInfoSchema.java:780)
When I remove useInformationSchema=true property from the JDBC URL, it works with no errors but takes too long to execute getImportedKeys()
How can I enable useInformationSchema?
If I cannot use property useInformationSchema, what can I do to make queries like getImportedKeys() and getExportedKeys() run faster?
