|
JavaXT
|
|
Database Class
package javaxt.sql;
import java.sql.ResultSet;
import java.sql.DatabaseMetaData;
import java.sql.SQLException;
import javax.sql.ConnectionPoolDataSource;
//******************************************************************************
//** Database
//******************************************************************************
/**
* Used to encapsulate database connection information, open connections to
* the database, and execute queries.
*
******************************************************************************/
public class Database implements Cloneable {
private String name; //name of the catalog used to store tables, views, etc.
private String host;
private Integer port;
private String username;
private String password;
private Driver driver;
private java.util.Properties properties;
private String querystring;
private ConnectionPoolDataSource ConnectionPoolDataSource;
private static final Class<?>[] stringType = { String.class };
private static final Class<?>[] integerType = { Integer.TYPE };
private ConnectionPool connectionPool;
private int maxConnections = 15;
private Table[] tables = null;
private String[] catalogs = null;
private boolean cacheMetadata = false;
//**************************************************************************
//** Constructor
//**************************************************************************
/** Creates a new instance of this class. Note that you will need to set the
* name, host, port, username, password, and driver in order to create a
* connection to the database.
*/
public Database(){
}
//**************************************************************************
//** Constructor
//**************************************************************************
/** Creates a new instance of this class.
* @param name Name of the catalog used to store tables, views, etc.
* @param host Server name or IP address.
* @param port Port number used to establish connections to the database.
* @param username Username used to log into the database
* @param password Password used to log into the database
*/
public Database(String name, String host, int port, String username, String password, Driver driver) {
this.name = name;
this.host = host;
this.port = port>0 ? port : null;
this.username = username;
this.password = password;
this.driver = driver;
}
//**************************************************************************
//** Constructor
//**************************************************************************
/** Creates a new instance of this class using a java.sql.Connection.
*/
public Database(java.sql.Connection conn){
try{
DatabaseMetaData dbmd = conn.getMetaData();
this.name = conn.getCatalog();
this.username = dbmd.getUserName();
parseURL(dbmd.getURL());
//dbmd.getDriverName();
}
catch(Exception e){
//e.printStackTrace();
}
}
//**************************************************************************
//** Constructor
//**************************************************************************
/** Creates a new instance of this class using a jdbc connection string.
* Username and password may be appended to the end of the connection string
* in the property list.
* @param connStr A jdbc connection string/url. All connection URLs
* have the following form:
* <pre> jdbc:[dbVendor]://[dbName][propertyList] </pre>
*
* Examples:
* <p>Derby:</p>
* <pre> jdbc:derby://temp/my.db;user=admin;password=mypassword </pre>
* <p>SQL Server:</p>
* <pre> jdbc:sqlserver://192.168.0.80;databaseName=master;user=admin;password=mypassword </pre>
*/
public Database(String connStr){
parseURL(connStr);
}
//**************************************************************************
//** parseURL
//**************************************************************************
/** Used to parse a JDBC connection string (url)
*/
private void parseURL(String connStr){
String[] arrConnStr = connStr.split(";");
String jdbcURL = arrConnStr[0];
//Update jdbc url for URL parser
if (!jdbcURL.contains("//")){
String protocol = jdbcURL.substring(jdbcURL.indexOf(":")+1);
protocol = "jdbc:" + protocol.substring(0, protocol.indexOf(":")) + ":";
String path = jdbcURL.substring(protocol.length());
jdbcURL = protocol + "//" + path;
}
//Parse url and extract connection parameters
javaxt.utils.URL url = new javaxt.utils.URL(jdbcURL);
host = url.getHost();
port = url.getPort();
driver = Driver.findDriver(url.getProtocol());
if (driver==null){
driver = new Driver(null, null, url.getProtocol());
}
if (name==null){
name = url.getPath();
if (this.name!=null && this.name.startsWith("/")){
this.name = this.name.substring(1);
}
}
querystring = url.getQueryString();
if (querystring.length()==0) querystring = null;
//Extract additional connection parameters
for (int i=1; i<arrConnStr.length; i++) {
String[] arrParams = arrConnStr[i].split("=");
String paramName = arrParams[0].toLowerCase();
String paramValue = arrParams[1];
if (paramName.equals("database")){
this.name = paramValue;
}
else if (paramName.equals("user")){
this.username = paramValue;
}
else if (paramName.equals("password")){
this.password = paramValue;
}
else if (paramName.equalsIgnoreCase("derby.system.home")){
//if (System.getProperty("derby.system.home")==null)
System.setProperty("derby.system.home", paramValue);
}
else{
//Extract additional properties
if (properties==null) properties = new java.util.Properties();
properties.put(arrParams[0], arrParams[1]);
}
}
}
//**************************************************************************
//** setName
//**************************************************************************
/** Sets the name of the catalog used to store tables, views, etc.
*/
public void setName(String name){
this.name = name;
}
//**************************************************************************
//** getName
//**************************************************************************
/** Gets the name of the catalog used to store tables, views, etc.
*/
public String getName(){
return name;
}
//**************************************************************************
//** setHost
//**************************************************************************
/** Used to set the path to the database (server name and port).
*/
public void setHost(String host, int port){
this.host = host;
this.port = port;
}
//**************************************************************************
//** setHost
//**************************************************************************
/** Used to set the path to the database.
* @param host Server name/port (e.g. localhost:9080) or a path to a file
* (e.g. /temp/firebird.db)
*/
public void setHost(String host){
if (host==null){
this.host = null;
}
else{
host = host.trim();
if (host.contains(":")){
try{
this.host = host.substring(0, host.indexOf(":"));
this.port = Integer.valueOf(host.substring(host.indexOf(":")+1));
}
catch(Exception e){
this.host = host; //eg file paths
}
}
else{
this.host = host;
}
}
}
//**************************************************************************
//** getHost
//**************************************************************************
/** Returns the name or IP address of the server or a physical path to the
* database file.
*/
public String getHost(){
return host;
}
//**************************************************************************
//** setPort
//**************************************************************************
public void setPort(int port){
this.port = port;
}
public Integer getPort(){
return port;
}
//**************************************************************************
//** setDriver
//**************************************************************************
public void setDriver(Driver driver){
this.driver = driver;
}
//**************************************************************************
//** setDriver
//**************************************************************************
/** Used to find a driver that corresponds to a given vendor name, class
* name, or protocol.
*/
public void setDriver(String driver){ //throw exception?
this.driver = Driver.findDriver(driver);
}
public void setDriver(java.sql.Driver driver){
this.driver = new Driver(driver);
}
public void setDriver(Class driver){
this.driver = Driver.findDriver(driver.getCanonicalName());
}
//**************************************************************************
//** getDriver
//**************************************************************************
public Driver getDriver(){
return driver;
}
//**************************************************************************
//** setUserName
//**************************************************************************
public void setUserName(String username){
this.username = username;
}
public String getUserName(){
return username;
}
//**************************************************************************
//** setPassword
//**************************************************************************
public void setPassword(String password){
this.password = password;
}
public String getPassword(){
return password;
}
public void setProperties(java.util.Properties properties){
this.properties = properties;
}
public java.util.Properties getProperties(){
return properties;
}
//**************************************************************************
//** getConnectionString
//**************************************************************************
/** Returns a JDBC connection string used to connect to the database.
* Username and password are appended to the end of the url.
*/
public String getConnectionString(){
String path = getURL(false);
if (username!=null) path += ";user=" + username;
if (password!=null) path += ";password=" + password;
return path;
}
//**************************************************************************
//** getURL
//**************************************************************************
/** Used to construct a JDBC connection string
*/
protected String getURL(boolean appendProperties){
//Update Server Name
String server = host;
if (port!=null && port>0) server += ":" + port;
String vendor = driver.getVendor();
if (vendor==null) vendor = "";
if (vendor.equals("Derby") || vendor.equals("SQLite")){
server = ":" + server;
}
//Update Initial Catalog
String database = "";
if (name!=null) {
if (name.trim().length()>0){
if (vendor.equals("SQLServer")){
database = ";databaseName=" + name;
}
else if (vendor.equals("Oracle")){
database = ":" + name; //only tested with thin driver
}
else if (vendor.equals("Derby")){
database = ";databaseName=" + name;
}
else{
database = "/" + name;
}
}
}
//Append querystring as needed
if (querystring!=null) database += "?" + querystring;
//Set Path
String path = driver.getProtocol() + "://";
//Update path as needed
if (vendor.equals("Sybase")){
if (path.toLowerCase().contains((CharSequence) "tds:")==false){
path = driver.getProtocol() + "Tds:";
}
}
else if (vendor.equals("Oracle")){
path = driver.getProtocol() + ":thin:@"; //only tested with thin driver
}
else if (vendor.equals("Derby") || vendor.equals("SQLite")){
path = driver.getProtocol();
}
else if (vendor.equals("H2")){
//Special case for newer versions of H2. In the 2.x releases of H2,
//the protocol changed for embedded file databases. The following
//logic will update the path to set the correct protocol depending
//on which version of the driver we have.
if (driver.getProtocol().equals("jdbc:h2")){
java.sql.Driver d = driver.getDriver();
try{
if (d==null) d = driver.load();
if (d.getMajorVersion()>1){
path = driver.getProtocol() + ":file:";
}
}
catch(Exception e){
}
}
}
//Assemble Connection String
String url = path + server + database;
if (appendProperties){
StringBuilder props = new StringBuilder();
if (properties!=null){
java.util.Iterator it = properties.keySet().iterator();
while (it.hasNext()){
Object key = it.next();
Object val = properties.get(key);
props.append(";" + key + "=" + val);
}
}
url+= props.toString();
}
return url;
}
//**************************************************************************
//** getConnection
//**************************************************************************
/** Returns a connection to the database. If a connection pool has been
* initialized, a connection is returned from the pool. Otherwise, a new
* connection is created. In either case, the connection must be closed
* immediately after use. See Connection.close() for details.
*/
public Connection getConnection() throws SQLException {
Connection connection = new Connection();
connection.open(this);
return connection;
}
//**************************************************************************
//** initConnectionPool
//**************************************************************************
/** Used to initialize a connection pool. Subsequent called to the
* getConnection() method will return connections from the pool.
*/
public void initConnectionPool() throws SQLException {
if (connectionPool!=null) return;
initConnectionPool(new ConnectionPool(this, maxConnections));
}
//**************************************************************************
//** initConnectionPool
//**************************************************************************
/** Used to configure the Database class to use a specific instance of a
* javaxt.sql.ConnectionPool. Subsequent called to the getConnection()
* method will return connections from the pool.
* @param cp An instance of a javaxt.sql.ConnectionPool.
*/
public void initConnectionPool(ConnectionPool cp) throws SQLException {
if (connectionPool!=null) return;
connectionPool = cp;
//Create Shutdown Hook to clean up the connection pool on exit
Runtime.getRuntime().addShutdownHook(new Thread() {
public void run() {
if (connectionPool!=null){
//System.out.println("\r\nShutting down connection pool...");
try{
connectionPool.close();
}
catch(Exception e){
//e.printStackTrace();
}
}
}
});
}
//**************************************************************************
//** terminateConnectionPool
//**************************************************************************
/** Used to terminate the connection pool, closing all active connections.
*/
public void terminateConnectionPool() throws SQLException {
if (connectionPool!=null){
connectionPool.close();
connectionPool = null;
}
}
//**************************************************************************
//** setConnectionPoolSize
//**************************************************************************
/** Used to specify the size of the connection pool. The pool size must be
* set before initializing the connection pool. If the pool size is not
* defined, the connection pool will default to 15.
*/
public void setConnectionPoolSize(int maxConnections){
if (connectionPool!=null) return;
this.maxConnections = maxConnections;
}
//**************************************************************************
//** getConnectionPoolSize
//**************************************************************************
/** Returns the size of the connection pool.
*/
public int getConnectionPoolSize(){
return maxConnections;
}
//**************************************************************************
//** getConnectionPool
//**************************************************************************
/** Returns the connection pool that was created via the initConnectionPool
* method. Returns null if the connection pool has not been not initialized
* or if the connection pool has been terminated.
*/
public ConnectionPool getConnectionPool(){
return connectionPool;
}
//**************************************************************************
//** setConnectionPoolDataSource
//**************************************************************************
/** Used to set the ConnectionPoolDataSource for the database. Typically,
* the getConnectionPoolDataSource() method is used to create a
* ConnectionPoolDataSource. This method allows you to specify a different
* ConnectionPoolDataSource.
*/
public void setConnectionPoolDataSource(ConnectionPoolDataSource dataSource){
this.ConnectionPoolDataSource = dataSource;
}
//**************************************************************************
//** getConnectionPoolDataSource
//**************************************************************************
/** Used to instantiate a ConnectionPoolDataSource for the database. The
* ConnectionPoolDataSource is typically used to create a JDBC Connection
* Pool.
*/
public ConnectionPoolDataSource getConnectionPoolDataSource() throws SQLException {
if (ConnectionPoolDataSource!=null) return ConnectionPoolDataSource;
if (driver==null) throw new SQLException(
"Failed to create a ConnectionPoolDataSource. Please specify a driver.");
String className = null;
java.util.HashMap<String, Object> methods = new java.util.HashMap<>();
if (driver.equals("sqlite")){
className = "org.sqlite.SQLiteConnectionPoolDataSource";
methods.put("setUrl", "jdbc:sqlite:" + host);
/*
javax.sql.DataSource sqliteDS = new DataSource();
sqliteDS.setURL ("jdbc:sqlite://" + name);
dataSource = sqliteDS;
*/
}
else if (driver.equals("derby")){
className = ("org.apache.derby.jdbc.EmbeddedConnectionPoolDataSource");
methods.put("setDatabaseName", host);
methods.put("setCreateDatabase", "create");
}
else if (driver.equals("h2")){
className = ("org.h2.jdbcx.JdbcDataSource");
//URL requirements changed from 1.x to 2.x. Starting with 2.x, we
//now have to add properties to the end of the url
String url = null;
java.sql.Driver d = driver.getDriver();
try{
if (d==null) d = driver.load();
if (d.getMajorVersion()>1){
url = getURL(true);
}
}
catch(Exception e){}
methods.put("setURL", url==null ? getURL(false) : url);
methods.put("setUser", username);
methods.put("setPassword", password);
}
else if (driver.equals("sqlserver")){ //mssql
className = ("com.microsoft.sqlserver.jdbc.SQLServerXADataSource");
methods.put("setDatabaseName", name);
methods.put("setServerName", host);
methods.put("setUser", username);
methods.put("setPassword", password);
}
else if (driver.equals("postgresql")){ //pgsql
className = ("org.postgresql.ds.PGConnectionPoolDataSource");
methods.put("setDatabaseName", name);
methods.put("setServerName", host);
methods.put("setPortNumber", port);
methods.put("setUser", username);
methods.put("setPassword", password);
}
else if (driver.equals("mysql")){
className = ("com.mysql.jdbc.jdbc2.optional.MysqlConnectionPoolDataSource");
methods.put("setDatabaseName", name);
methods.put("setServerName", host);
methods.put("setPortNumber", port); //setPort?
methods.put("setUser", username);
methods.put("setPassword", password);
}
else if (driver.equals("oracle")){
String connDriver = "thin";
String connService = "";
className = ("oracle.jdbc.pool.OracleConnectionPoolDataSource");
methods.put("setDriverType", connDriver);
methods.put("setServerName", host);
methods.put("setPortNumber", port);
methods.put("setServiceName", connService);
methods.put("setUser", username);
methods.put("setPassword", password);
}
else if (driver.equals("jtds")){
className = ("net.sourceforge.jtds.jdbcx.JtdsDataSource");
methods.put("setDatabaseName", name);
methods.put("setServerName", host);
methods.put("setUser", username);
methods.put("setPassword", password);
}
//Instantiate the ConnectionPoolDataSource
if (className!=null){
try{
Class classToLoad = Class.forName(className);
Object instance = classToLoad.newInstance();
java.util.Iterator<String> it = methods.keySet().iterator();
while (it.hasNext()){
String methodName = it.next();
Object parameter = methods.get(methodName);
if (parameter!=null){
java.lang.reflect.Method method = null;
if (parameter instanceof String)
method = classToLoad.getMethod(methodName, stringType);
else if (parameter instanceof Integer)
method = classToLoad.getMethod(methodName, integerType);
if (method!=null) method.invoke(instance, new Object[] { parameter });
}
}
ConnectionPoolDataSource = (ConnectionPoolDataSource) instance;
return ConnectionPoolDataSource;
}
catch(Exception e){
throw new SQLException("Failed to instantiate the ConnectionPoolDataSource.", e);
}
}
throw new SQLException("Failed to find a suitable ConnectionPoolDataSource.");
}
//**************************************************************************
//** getRecord
//**************************************************************************
/** Used to retrieve a single record from this database.
*/
public javaxt.sql.Record getRecord(String sql) throws SQLException {
try (Connection conn = getConnection()){
return conn.getRecord(sql);
}
}
//**************************************************************************
//** getRecords
//**************************************************************************
/** Used to retrieve records from this database. Note that this method
* relies on a Generator to yield records. This is fine for relatively
* small record sets. However, for large record sets, we recommend opening
* a database connection first and calling Connection.getRecords() like
* this:
<pre>
try (Connection conn = database.getConnection()){
return conn.getRecords(sql);
}
</pre>
*/
public Iterable<javaxt.sql.Record> getRecords(String sql) throws SQLException {
return new javaxt.utils.Generator<javaxt.sql.Record>(){
public void run() throws InterruptedException {
try (Connection conn = getConnection()){
for (javaxt.sql.Record record : conn.getRecords(sql)){
try{
this.yield(record);
}
catch(InterruptedException e){
return;
}
}
}
catch(Exception e){
RuntimeException ex = new RuntimeException(e.getMessage());
ex.setStackTrace(e.getStackTrace());
throw ex;
}
}
};
}
//**************************************************************************
//** getTables
//**************************************************************************
/** Used to retrieve an array of tables and columns found in this database.
*/
public Table[] getTables() throws SQLException {
if (tables!=null) return tables;
try (Connection conn = getConnection()){
return getTables(conn);
}
}
//**************************************************************************
//** getTables
//**************************************************************************
/** Used to retrieve an array of tables and columns found in a database.
*/
public static Table[] getTables(Connection conn){
Database database = conn.getDatabase();
if (database!=null){
if (database.tables!=null) return database.tables;
}
java.util.ArrayList<Table> tables = new java.util.ArrayList<>();
try{
DatabaseMetaData dbmd = conn.getConnection().getMetaData();
try(ResultSet rs = dbmd.getTables(null,null,null,new String[]{"TABLE"})){
while (rs.next()) {
tables.add(new Table(rs, dbmd));
}
}
}
catch(Exception e){
}
Table[] arr = tables.toArray(new Table[tables.size()]);
if (database!=null){
if (database.cacheMetadata) database.tables = arr;
}
return arr;
}
//**************************************************************************
//** getTableNames
//**************************************************************************
/** Used to retrieve an array of table names found in the database. If a
* table is part of a schema, the schema name is prepended to the table
* name. This method is significantly faster than the getTables() method
* which returns the full metadata for each table.
*/
public String[] getTableNames() throws SQLException {
java.util.ArrayList<javaxt.utils.Record> tableNames = new java.util.ArrayList<>();
if (tables!=null){
for (Table table : tables){
javaxt.utils.Record record = new javaxt.utils.Record();
record.set("schema", table.getSchema());
record.set("table", table.getName());
tableNames.add(record);
}
}
else{
try (Connection conn = getConnection()){
DatabaseMetaData dbmd = conn.getConnection().getMetaData();
try (ResultSet rs = dbmd.getTables(null,null,null,new String[]{"TABLE"})){
while (rs.next()) {
javaxt.utils.Record record = new javaxt.utils.Record();
record.set("schema", rs.getString("TABLE_SCHEM"));
record.set("table", rs.getString("TABLE_NAME"));
tableNames.add(record);
}
}
}
}
String[] arr = new String[tableNames.size()];
for (int i=0; i<arr.length; i++){
javaxt.utils.Record record = tableNames.get(i);
String tableName = record.get("table").toString();
String schemaName = record.get("schema").toString();
if (schemaName!=null && !schemaName.isEmpty()){
tableName = schemaName + "." + tableName;
}
arr[i] = tableName;
}
java.util.Arrays.sort(arr);
return arr;
}
//**************************************************************************
//** getCatalogs
//**************************************************************************
/** Used to retrieve a list of available catalogs (aka databases) found on
* this server.
*/
public String[] getCatalogs() throws SQLException{
try (Connection conn = getConnection()){
return getCatalogs(conn);
}
}
//**************************************************************************
//** getCatalogs
//**************************************************************************
/** Used to retrieve a list of available catalogs (aka databases) found on
* a server.
*/
public static String[] getCatalogs(Connection conn){
Database database = conn.getDatabase();
if (database!=null){
if (database.catalogs!=null) return database.catalogs;
}
java.util.TreeSet<String> catalogs = new java.util.TreeSet<String>();
try{
DatabaseMetaData dbmd = conn.getConnection().getMetaData();
try (ResultSet rs = dbmd.getCatalogs()){
while (rs.next()) {
catalogs.add(rs.getString(1));
}
}
}
catch(Exception e){
return null;
}
String[] arr = catalogs.toArray(new String[catalogs.size()]);
if (database!=null){
if (database.cacheMetadata) database.catalogs = arr;
}
return arr;
}
//**************************************************************************
//** getReservedKeywords
//**************************************************************************
/** Returns a list of reserved keywords for the current database.
*/
public String[] getReservedKeywords() throws Exception {
try(Connection conn = this.getConnection()){
return getReservedKeywords(conn);
}
}
//**************************************************************************
//** getReservedKeywords
//**************************************************************************
/** Returns a list of reserved keywords for a given database.
*/
public static String[] getReservedKeywords(Connection conn){
Database database = conn.getDatabase();
javaxt.sql.Driver driver = database.getDriver();
if (driver==null) driver = new Driver("","","");
if (driver.equals("Firebird")){
return fbKeywords;
}
else if (driver.equals("SQLServer")){
return msKeywords;
}
else if (driver.equals("H2")){
//Check if a "MODE" is set
String mode = "";
java.util.Properties properties = database.getProperties();
if (properties!=null){
Object o = properties.get("MODE");
if (o!=null) mode = o.toString();
}
if (mode.equalsIgnoreCase("PostgreSQL")){
return java.util.stream.Stream.concat(
java.util.Arrays.stream(h2Keywords),
java.util.Arrays.stream(pgKeywords)
).toArray(String[]::new);
}
else{
return h2Keywords;
}
}
else if (driver.equals("PostgreSQL")){
//Try to get reserved keywords from the database. Note that in PostgreSQL
//"non-reserved" keywords are key words that are explicitly known to
//the parser but are allowed as column or table names. Therefore, we
//will ignore "non-reserved" keywords from our query.
if (pgKeywords==null){
java.util.HashSet<String> arr = new java.util.HashSet<>();
try (java.sql.Statement stmt = conn.getConnection().createStatement(
java.sql.ResultSet.TYPE_FORWARD_ONLY,
java.sql.ResultSet.CONCUR_READ_ONLY,
java.sql.ResultSet.FETCH_FORWARD)){
try (java.sql.ResultSet rs = stmt.executeQuery(
"select word from pg_get_keywords() where catcode='R'")){
while(rs.next()){
arr.add(rs.getString(1));
}
}
}
catch(java.sql.SQLException e){
e.printStackTrace();
}
String[] keywords = new String[arr.size()];
int i=0;
java.util.Iterator<String> it = arr.iterator();
while (it.hasNext()){
keywords[i] = it.next();
i++;
}
pgKeywords = keywords;
}
return pgKeywords;
}
else{
return ansiKeywords;
}
}
//**************************************************************************
//** enableMetadataCache
//**************************************************************************
/** Used to enable/disable metadata caching. If caching is enabled, calls
* to getTables() and getCatalogs() will return cached results. This is
* appropriate if the database schema doesn't change often and may increase
* performance when inserting and updating records via the Recordset class.
* @param b If true, will cache database metadata. If false, will disable
* metadata caching and delete any information than was previously cached.
*/
public void enableMetadataCache(boolean b){
cacheMetadata = b;
if (b==false){
tables = null;
catalogs = null;
}
}
//**************************************************************************
//** addModel
//**************************************************************************
/** Used to register a javaxt.sql.Model with the database. The model is
* initialized and associated with the database connection pool. Once the
* model is initialized, it can be used to find records in the database
* and execute CRUD operations.
* @param c A Java class that extends the javaxt.sql.Model abstract class.
*/
public void addModel(Class c) throws SQLException {
//Check if class is a model
if (!Model.class.isAssignableFrom(c)){
throw new IllegalArgumentException();
}
//Initialize connectionPool as needed
if (connectionPool==null) initConnectionPool();
if (connectionPool==null){
throw new SQLException("Connection pool has not been initialized");
}
Model.init(c, connectionPool);
}
//**************************************************************************
//** displayDbProperties
//**************************************************************************
public static void displayDbProperties(Connection conn){
if (conn==null){
System.out.println("Error: Connection is null");
return;
}
try{
java.sql.DatabaseMetaData dm = conn.getConnection().getMetaData();
System.out.println("Driver Information");
System.out.println("\tDriver Name: "+ dm.getDriverName());
System.out.println("\tDriver Version: "+ dm.getDriverVersion ());
System.out.println("\nDatabase Information ");
System.out.println("\tDatabase Name: "+ dm.getDatabaseProductName());
System.out.println("\tDatabase Version: "+ dm.getDatabaseProductVersion());
System.out.println("Avalilable Catalogs ");
try (java.sql.ResultSet rs = dm.getCatalogs()){
while(rs.next()){
System.out.println("\tcatalog: "+ rs.getString(1));
}
}
}
catch(Exception e){
e.printStackTrace();
}
}
//**************************************************************************
//** toString
//**************************************************************************
/** Returns database connection information encapsulated by this class.
*/
public String toString(){
StringBuffer str = new StringBuffer();
str.append("Name: " + name + "\r\n");
str.append("Host: " + host + "\r\n");
str.append("Port: " + port + "\r\n");
str.append("UserName: " + username + "\r\n");
str.append("Driver: " + driver + "\r\n");
str.append("URL: " + getURL(false) + "\r\n");
str.append("ConnStr: " + this.getConnectionString());
return str.toString();
}
//**************************************************************************
//** clone
//**************************************************************************
public Database clone(){
Database db = new Database(name, host, port==null ? -1 : port, username, password, driver);
if (properties!=null) db.properties = (java.util.Properties) properties.clone();
db.querystring = querystring;
return db;
}
/** Firebird reserved keywords. */
private static final String[] fbKeywords = new String[]{
"ADD","ADMIN","ALL","ALTER","AND","ANY","AS","AT","AVG","BEGIN","BETWEEN",
"BIGINT","BIT_LENGTH","BLOB","BOTH","BY","CASE","CAST","CHAR","CHAR_LENGTH",
"CHARACTER","CHARACTER_LENGTH","CHECK","CLOSE","COLLATE","COLUMN","COMMIT",
"CONNECT","CONSTRAINT","COUNT","CREATE","CROSS","CURRENT","CURRENT_CONNECTION",
"CURRENT_DATE","CURRENT_ROLE","CURRENT_TIME","CURRENT_TIMESTAMP",
"CURRENT_TRANSACTION","CURRENT_USER","CURSOR","DATE","DAY","DEC","DECIMAL",
"DECLARE","DEFAULT","DELETE","DISCONNECT","DISTINCT","DOUBLE","DROP","ELSE",
"END","ESCAPE","EXECUTE","EXISTS","EXTERNAL","EXTRACT","FETCH","FILTER",
"FLOAT","FOR","FOREIGN","FROM","FULL","FUNCTION","GDSCODE","GLOBAL","GRANT",
"GROUP","HAVING","HOUR","IN","INDEX","INNER","INSENSITIVE","INSERT","INT",
"INTEGER","INTO","IS","JOIN","LEADING","LEFT","LIKE","LONG","LOWER","MAX",
"MAXIMUM_SEGMENT","MERGE","MIN","MINUTE","MONTH","NATIONAL","NATURAL","NCHAR",
"NO","NOT","NULL","NUMERIC","OCTET_LENGTH","OF","ON","ONLY","OPEN","OR",
"ORDER","OUTER","PARAMETER","PLAN","POSITION","POST_EVENT","PRECISION",
"PRIMARY","PROCEDURE","RDB$DB_KEY","REAL","RECORD_VERSION","RECREATE",
"RECURSIVE","REFERENCES","RELEASE","RETURNING_VALUES","RETURNS","REVOKE",
"RIGHT","ROLLBACK","ROW_COUNT","ROWS","SAVEPOINT","SECOND","SELECT","SENSITIVE",
"SET","SIMILAR","SMALLINT","SOME","SQLCODE","SQLSTATE","START","SUM","TABLE",
"THEN","TIME","TIMESTAMP","TO","TRAILING","TRIGGER","TRIM","UNION","UNIQUE",
"UPDATE","UPPER","USER","USING","VALUE","VALUES","VARCHAR","VARIABLE","VARYING",
"VIEW","WHEN","WHERE","WHILE","WITH","YEAR"
};
/** SQLServer reserved keywords. Source:
* https://msdn.microsoft.com/en-us/library/ms189822.aspx
*/
private static final String[] msKeywords = new String[]{
"ADD","ALL","ALTER","AND","ANY","AS","ASC","AUTHORIZATION","BACKUP","BEGIN",
"BETWEEN","BREAK","BROWSE","BULK","BY","CASCADE","CASE","CHECK","CHECKPOINT",
"CLOSE","CLUSTERED","COALESCE","COLLATE","COLUMN","COMMIT","COMPUTE",
"CONSTRAINT","CONTAINS","CONTAINSTABLE","CONTINUE","CONVERT","CREATE","CROSS",
"CURRENT","CURRENT_DATE","CURRENT_TIME","CURRENT_TIMESTAMP","CURRENT_USER",
"CURSOR","DATABASE","DBCC","DEALLOCATE","DECLARE","DEFAULT","DELETE","DENY",
"DESC","DISK","DISTINCT","DISTRIBUTED","DOUBLE","DROP","DUMP","ELSE","END",
"ERRLVL","ESCAPE","EXCEPT","EXEC","EXECUTE","EXISTS","EXIT","EXTERNAL",
"FETCH","FILE","FILLFACTOR","FOR","FOREIGN","FREETEXT","FREETEXTTABLE",
"FROM","FULL","FUNCTION","GOTO","GRANT","GROUP","HAVING","HOLDLOCK","IDENTITY",
"IDENTITY_INSERT","IDENTITYCOL","IF","IN","INDEX","INNER","INSERT","INTERSECT",
"INTO","IS","JOIN","KEY","KILL","LEFT","LIKE","LINENO","LOAD","MERGE","NATIONAL",
"NOCHECK","NONCLUSTERED","NOT","NULL","NULLIF","OF","OFF","OFFSETS","ON",
"OPEN","OPENDATASOURCE","OPENQUERY","OPENROWSET","OPENXML","OPTION","OR",
"ORDER","OUTER","OVER","PERCENT","PIVOT","PLAN","PRECISION","PRIMARY","PRINT",
"PROC","PROCEDURE","PUBLIC","RAISERROR","READ","READTEXT","RECONFIGURE",
"REFERENCES","REPLICATION","RESTORE","RESTRICT","RETURN","REVERT","REVOKE",
"RIGHT","ROLLBACK","ROWCOUNT","ROWGUIDCOL","RULE","SAVE","SCHEMA","SECURITYAUDIT",
"SELECT","SEMANTICKEYPHRASETABLE","SEMANTICSIMILARITYDETAILSTABLE",
"SEMANTICSIMILARITYTABLE","SESSION_USER","SET","SETUSER","SHUTDOWN","SOME",
"STATISTICS","SYSTEM_USER","TABLE","TABLESAMPLE","TEXTSIZE","THEN","TO",
"TOP","TRAN","TRANSACTION","TRIGGER","TRUNCATE","TRY_CONVERT","TSEQUAL",
"UNION","UNIQUE","UNPIVOT","UPDATE","UPDATETEXT","USE","USER","VALUES",
"VARYING","VIEW","WAITFOR","WHEN","WHERE","WHILE","WITH","WITHIN GROUP",
"WRITETEXT"
};
/** H2 reserved keywords. Source:
* http://www.h2database.com/html/advanced.html#keywords
*/
private static final String[] h2Keywords = new String[]{
"ALL","AND","ARRAY","AS","BETWEEN","BOTH","CASE","CHECK","CONSTRAINT",
"CROSS","CURRENT_DATE","CURRENT_TIME","CURRENT_TIMESTAMP","CURRENT_USER",
"DISTINCT","EXCEPT","EXISTS","FALSE","FETCH","FILTER","FOR","FOREIGN",
"FROM","FULL","GROUP","GROUPS","HAVING","IF","ILIKE","IN","INNER",
"INTERSECT","INTERSECTS","INTERVAL","IS","JOIN","KEY","LEADING","LEFT","LIKE",
"LIMIT","LOCALTIME","LOCALTIMESTAMP","MINUS","NATURAL","NOT","NULL","OFFSET",
"ON","OR","ORDER","OVER","PARTITION","PRIMARY","QUALIFY","RANGE","REGEXP",
"RIGHT","ROW","_ROWID_","ROWNUM","ROWS","SELECT","SYSDATE","SYSTIME",
"SYSTIMESTAMP","TABLE","TODAY","TOP","TRAILING","TRUE","UNION","UNIQUE",
"VALUE","VALUES","WHERE","WINDOW","WITH","YEAR"
};
/** PostgreSQL reserved keywords generated via the following query using PG15:
<pre>
select string_agg('"' || UPPER(word) || '"', ',') from pg_get_keywords() where catcode='R';
</pre>
*/
private static String[] pgKeywords = new String[]{
"ALL","ANALYSE","ANALYZE","AND","ANY","ARRAY","AS","ASC","ASYMMETRIC",
"BOTH","CASE","CAST","CHECK","COLLATE","COLUMN","CONSTRAINT","CREATE",
"CURRENT_CATALOG","CURRENT_DATE","CURRENT_ROLE","CURRENT_TIME",
"CURRENT_TIMESTAMP","CURRENT_USER","DEFAULT","DEFERRABLE","DESC",
"DISTINCT","DO","ELSE","END","EXCEPT","FALSE","FETCH","FOR","FOREIGN",
"FROM","GRANT","GROUP","HAVING","IN","INITIALLY","INTERSECT","INTO",
"LATERAL","LEADING","LIMIT","LOCALTIME","LOCALTIMESTAMP","NOT","NULL",
"OFFSET","ON","ONLY","OR","ORDER","PLACING","PRIMARY","REFERENCES",
"RETURNING","SELECT","SESSION_USER","SOME","SYMMETRIC","TABLE","THEN",
"TO","TRAILING","TRUE","UNION","UNIQUE","USER","USING","VARIADIC",
"WHEN","WHERE","WINDOW","WITH"
};
/** Superset of SQL-92, SQL-99, SQL-2003 reserved keywords. */
private static String[] ansiKeywords = new String[]{
"ABSOLUTE","ACTION","ADD","AFTER","ALL","ALLOCATE","ALTER","AND","ANY","ARE",
"ARRAY","AS","ASC","ASENSITIVE","ASSERTION","ASYMMETRIC","AT","ATOMIC",
"AUTHORIZATION","AVG","BEFORE","BEGIN","BETWEEN","BIGINT","BINARY","BIT",
"BIT_LENGTH","BLOB","BOOLEAN","BOTH","BREADTH","BY","CALL","CALLED","CASCADE",
"CASCADED","CASE","CAST","CATALOG","CHAR","CHAR_LENGTH","CHARACTER","CHARACTER_LENGTH",
"CHECK","CLOB","CLOSE","COALESCE","COLLATE","COLLATION","COLUMN","COMMIT",
"CONDITION","CONNECT","CONNECTION","CONSTRAINT","CONSTRAINTS","CONSTRUCTOR",
"CONTAINS","CONTINUE","CONVERT","CORRESPONDING","COUNT","CREATE","CROSS",
"CUBE","CURRENT","CURRENT_DATE","CURRENT_DEFAULT_TRANSFORM_GROUP","CURRENT_PATH",
"CURRENT_ROLE","CURRENT_TIME","CURRENT_TIMESTAMP","CURRENT_TRANSFORM_GROUP_FOR_TYPE",
"CURRENT_USER","CURSOR","CYCLE","DATA","DATE","DAY","DEALLOCATE","DEC","DECIMAL",
"DECLARE","DEFAULT","DEFERRABLE","DEFERRED","DELETE","DEPTH","DEREF","DESC",
"DESCRIBE","DESCRIPTOR","DETERMINISTIC","DIAGNOSTICS","DISCONNECT","DISTINCT",
"DO","DOMAIN","DOUBLE","DROP","DYNAMIC","EACH","ELEMENT","ELSE","ELSEIF",
"END","EQUALS","ESCAPE","EXCEPT","EXCEPTION","EXEC","EXECUTE","EXISTS","EXIT",
"EXTERNAL","EXTRACT","FALSE","FETCH","FILTER","FIRST","FLOAT","FOR","FOREIGN",
"FOUND","FREE","FROM","FULL","FUNCTION","GENERAL","GET","GLOBAL","GO","GOTO",
"GRANT","GROUP","GROUPING","HANDLER","HAVING","HOLD","HOUR","IDENTITY","IF",
"IMMEDIATE","IN","INDICATOR","INITIALLY","INNER","INOUT","INPUT","INSENSITIVE",
"INSERT","INT","INTEGER","INTERSECT","INTERVAL","INTO","IS","ISOLATION",
"ITERATE","JOIN","KEY","LANGUAGE","LARGE","LAST","LATERAL","LEADING","LEAVE",
"LEFT","LEVEL","LIKE","LOCAL","LOCALTIME","LOCALTIMESTAMP","LOCATOR","LOOP",
"LOWER","MAP","MATCH","MAX","MEMBER","MERGE","METHOD","MIN","MINUTE","MODIFIES",
"MODULE","MONTH","MULTISET","NAMES","NATIONAL","NATURAL","NCHAR","NCLOB",
"NEW","NEXT","NO","NONE","NOT","NULL","NULLIF","NUMERIC","OBJECT","OCTET_LENGTH",
"OF","OLD","ON","ONLY","OPEN","OPTION","OR","ORDER","ORDINALITY","OUT","OUTER",
"OUTPUT","OVER","OVERLAPS","PAD","PARAMETER","PARTIAL","PARTITION","PATH",
"POSITION","PRECISION","PREPARE","PRESERVE","PRIMARY","PRIOR","PRIVILEGES",
"PROCEDURE","PUBLIC","RANGE","READ","READS","REAL","RECURSIVE","REF","REFERENCES",
"REFERENCING","RELATIVE","RELEASE","REPEAT","RESIGNAL","RESTRICT","RESULT",
"RETURN","RETURNS","REVOKE","RIGHT","ROLE","ROLLBACK","ROLLUP","ROUTINE",
"ROW","ROWS","SAVEPOINT","SCHEMA","SCOPE","SCROLL","SEARCH","SECOND","SECTION",
"SELECT","SENSITIVE","SESSION","SESSION_USER","SET","SETS","SIGNAL","SIMILAR",
"SIZE","SMALLINT","SOME","SPACE","SPECIFIC","SPECIFICTYPE","SQL","SQLCODE",
"SQLERROR","SQLEXCEPTION","SQLSTATE","SQLWARNING","START","STATE","STATIC",
"SUBMULTISET","SUBSTRING","SUM","SYMMETRIC","SYSTEM","SYSTEM_USER","TABLE",
"TABLESAMPLE","TEMPORARY","THEN","TIME","TIMESTAMP","TIMEZONE_HOUR","TIMEZONE_MINUTE",
"TO","TRAILING","TRANSACTION","TRANSLATE","TRANSLATION","TREAT","TRIGGER",
"TRIM","TRUE","UNDER","UNDO","UNION","UNIQUE","UNKNOWN","UNNEST","UNTIL",
"UPDATE","UPPER","USAGE","USER","USING","VALUE","VALUES","VARCHAR","VARYING",
"VIEW","WHEN","WHENEVER","WHERE","WHILE","WINDOW","WITH","WITHIN","WITHOUT",
"WORK","WRITE","YEAR","ZONE"
};
}
|
|