com.viaoa.datasource.jdbc.delegate.DBMetaDataDelegate Maven / Gradle / Ivy

Go to download
Show more of this group Show more artifacts with this name
Show all versions of oa-core Show documentation
Object Automation library
There is a newer version: 3.7.10
/*  Copyright 1999 Vince Via [email protected]
    Licensed under the Apache License, Version 2.0 (the "License");
    you may not use this file except in compliance with the License.
    You may obtain a copy of the License at http://www.apache.org/licenses/LICENSE-2.0
    Unless required by applicable law or agreed to in writing, software
    distributed under the License is distributed on an "AS IS" BASIS,
    WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
    See the License for the specific language governing permissions and
    limitations under the License.
*/
package com.viaoa.datasource.jdbc.delegate;

import com.viaoa.datasource.jdbc.db.DBMetaData;
import com.viaoa.util.OAString;

/**
 * Used to get information about the specific type of database.
 *
 * @author vvia
 */
public class DBMetaDataDelegate {

	/**
	 * Set the type of Database SQLSERVER, ACCESS, DERBY, ORACLE, MYSQL default: SQLSERVER
	 */
	public static void updateAfterTypeChange(DBMetaData dbmd) {
		if (dbmd == null) {
			return;
		}
		dbmd.setDistinctKeyword("DISTINCT");

		dbmd.setUseBracket(true);
		dbmd.setDatesIncludeTime(false);
		dbmd.setUseExists(true);
		dbmd.setLowerCaseFunction("LOWER");
		dbmd.setCaseSensitive(false);
		String distinctKeyword = "DISTINCT";
		dbmd.setAutoAssignValue("");
		dbmd.setMaxVarcharLength(255);

		String s;
		switch (dbmd.databaseType) {
		case DBMetaData.OTHER:
			dbmd.setUseExists(false);
			dbmd.setName("Other");
			dbmd.setUseBracket(false);
			dbmd.setCaseSensitive(false);
			break;
		case DBMetaData.DERBY:
			// connect 'jdbc:derby:database;create=true;collation=TERRITORY_BASED';
			dbmd.setName("Derby Database");
			dbmd.setUseExists(false); // Derby allows EXISTS, but is (much!much!) faster to do a join
			dbmd.setUseBracket(false);
			dbmd.setCaseSensitive(false); // was true, before 10.4
			dbmd.setLowerCaseFunction("LOWER");
			dbmd.setAutoAssignValue("GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1)");
			dbmd.setSupportsAutoAssign(true);
			dbmd.setFkeysAutoCreateIndex(true);
			dbmd.setMaxVarcharLength(32672);
			s = dbmd.getDriverJDBC();
			if (OAString.isEmpty(s)) {
				s = "org.apache.derby.jdbc.EmbeddedDriver";
				dbmd.setDriverJDBC(s);
			}
			s = dbmd.getUrlJDBC();
			if (OAString.isEmpty(s)) {
				s = "jdbc:derby:database";
				dbmd.setUrlJDBC(s);
			}
			dbmd.supportsFetchFirst = true;
			// 20210109
			dbmd.objectTrue = new Integer(1);
			dbmd.objectFalse = new Integer(0);
			break;
		case DBMetaData.SQLSERVER:
			dbmd.setName("SQL Server Database");
			// dbmd.setMaxString("TOP ?");
			dbmd.setDatesIncludeTime(true);
			dbmd.setAutoAssignValue("IDENTITY(1,1)");
			dbmd.setSupportsAutoAssign(true);
			dbmd.setMaxVarcharLength(8000);

			s = dbmd.getDriverJDBC();
			if (OAString.isEmpty(s)) {
				s = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
				dbmd.setDriverJDBC(s);
			}
			s = dbmd.getUrlJDBC();
			if (OAString.isEmpty(s)) {
				s = "jdbc:sqlserver://localhost;port=1433;database=dbname;sendStringParametersAsUnicode=false;SelectMethod=cursor;ConnectionRetryCount=2;ConnectionRetryDelay=2";
				dbmd.setUrlJDBC(s);
			}
			dbmd.supportsFetchFirst = true; // before 12, need to use TOP
			break;
		case DBMetaData.ORACLE:
			dbmd.setUseBracket(false);
			dbmd.setName("ORACLE Database");
			// dbmd.setMaxString("LIMIT ?");
			dbmd.setMaxVarcharLength(4000);
			dbmd.setSupportsAutoAssign(true);
			dbmd.supportsFetchFirst = true;
			break;
		case DBMetaData.ACCESS:
			dbmd.setName("Access Database");
			// distinctKeyword = "DISTINCT ROW";
			dbmd.setDatesIncludeTime(true);
			distinctKeyword = "DISTINCTROW";
			dbmd.setBlanksAsNulls(true);
			break;
		case DBMetaData.MYSQL:
			dbmd.setName("MySql Database");
			dbmd.setUseBracket(false);
			dbmd.setDatesIncludeTime(false);
			dbmd.setFkeysAutoCreateIndex(true);
			dbmd.setSupportsAutoAssign(true);
			// dbmd.setMaxString("LIMIT ?");
			// ??? dbmd.setMaxVarcharLength();
			// bUseExists = false; // prior to MySQL 4, now supports subqueries and EXISTS
			dbmd.supportsLimit = true;
			break;
		case DBMetaData.BRIDGE:
			dbmd.setName("ODBC-JDBC Bridge");
			break;
		case DBMetaData.POSTGRES:
			dbmd.setName("Postgres Database");
			dbmd.setUseBracket(false);
			dbmd.setDatesIncludeTime(false);
			dbmd.setFkeysAutoCreateIndex(false);
			// dbmd.setMaxString("LIMIT ?");
			dbmd.jsonCast = "::jsonb";
			dbmd.setSupportsAutoAssign(true);
			dbmd.setAutoAssignType("SERIAL");

			dbmd.setMaxVarcharLength((int) Math.pow(1024, 3));// 1GB

			// dbmd.setCaseSensitive(true);
			dbmd.setCaseSensitive(false); // ILIKE
			dbmd.setLowerCaseFunction("LOWER");

			dbmd.setBlanksAsNulls(false);
			dbmd.setUseExists(true);
			dbmd.setLikeKeyword("ILIKE");
			dbmd.setObjectTrue("'true'");
			dbmd.setObjectFalse("'false'");
			dbmd.supportsFetchFirst = true;

			s = dbmd.getDriverJDBC();
			if (OAString.isEmpty(s)) {
				s = "org.postgresql.ds.PGSimpleDataSource";
				dbmd.setDriverJDBC(s);
			}
			s = dbmd.getUrlJDBC();
			if (OAString.isEmpty(s)) {
				s = "jdbc:postgresql://$Host:5432/$DatabaseName";
				dbmd.setUrlJDBC(s);
			}

			break;
		case DBMetaData.DB2:
			dbmd.setName("IBM DB2");
			dbmd.setUseBracket(false);
			dbmd.setDatesIncludeTime(false);
			dbmd.setFkeysAutoCreateIndex(false);

			dbmd.setSupportsAutoAssign(true);
			// dbmd.setAutoAssignValue("GENERATED BY DEFAULT AS IDENTITY (start with 1, increment by 1, cycle)");
			dbmd.setAutoAssignValue("GENERATED BY DEFAULT AS IDENTITY");

			dbmd.setMaxVarcharLength(32 * 1024);

			dbmd.supportsFetchFirst = true;

			dbmd.setLowerCaseFunction("LOWER");

			s = dbmd.getDriverJDBC();
			if (OAString.isEmpty(s)) {
				s = "com.ibm.as400.access.AS400JDBCDriver";
				dbmd.setDriverJDBC(s);
			}
			s = dbmd.getUrlJDBC();
			if (OAString.isEmpty(s)) {
				s = "jdbc:as400://as400/;libraries=??,??;errors=full;naming=system;driver=native;";
				dbmd.setUrlJDBC(s);
			}
			break;
		}
		dbmd.setDistinctKeyword(distinctKeyword);
	}

	public static void close(DBMetaData dbmd) {
		if (dbmd.databaseType == dbmd.DERBY) {
			try {
				java.sql.DriverManager.getConnection("jdbc:derby:;shutdown=true"); // shuts down Derby, not just a single database
			} catch (Exception e) {
				// System.out.println("DataSource.shutdown() " + e);
			}
		}
	}

	private static String getValidName(DBMetaData dbmd, String name) {
		if (name == null) {
			return "";
		}
		// note: the "XvXvX" will be converted afterwards
		if (name.equalsIgnoreCase("max")) {
			name = "MaxXvXvX";
		} else if (name.equalsIgnoreCase("min")) {
			name = "MinXvXvX";
		} else if (name.equalsIgnoreCase("text")) {
			name = "TextXvXvX";
		} else if (name.equalsIgnoreCase("memo")) {
			name = "MemoXvXvX";
		} else if (name.equalsIgnoreCase("user")) {
			name = "UserXvXvX";
		} else if (name.equalsIgnoreCase("date")) {
			name = "DateXvXvX";
		} else if (name.equalsIgnoreCase("datetime")) {
			name = "DateTimeXvXvX";
		} else if (name.equalsIgnoreCase("time")) {
			name = "TimeXvXvX";
		} else if (name.equalsIgnoreCase("timestamp")) {
			name = "TimeStampXvXvX";
		} else if (name.equalsIgnoreCase("count")) {
			name = "CountXvXvX";
		} else if (name.equalsIgnoreCase("group")) {
			name = "GroupXvXvX";
		}
		return name;
	}

	public static String getValidTableName(DBMetaData dbmd, String name) {
		name = getValidName(dbmd, name);
		return OAString.convert(name, "XvXvX", "Table");
	}

	public static String getValidColumnName(DBMetaData dbmd, String name) {
		name = getValidName(dbmd, name);
		return OAString.convert(name, "XvXvX", "Value");
	}

	public static String getValidIndexName(DBMetaData dbmd, String name) {
		name = getValidName(dbmd, name);
		return OAString.convert(name, "XvXvX", "Index");
	}

}