com.hfg.sql.jdbc.postgresql.PostgreSQL Maven / Gradle / Ivy
Go to download
Show more of this group Show more artifacts with this name
Show all versions of com_hfg Show documentation
Show all versions of com_hfg Show documentation
com.hfg xml, html, svg, and bioinformatics utility library
package com.hfg.sql.jdbc.postgresql;
import com.hfg.datetime.ThreadSafeDateFormat;
import com.hfg.sql.SQLQuery;
import com.hfg.sql.SQLUtil;
import com.hfg.sql.jdbc.*;
import com.hfg.security.LoginCredentials;
import com.hfg.sql.table.DatabaseCol;
import com.hfg.sql.table.DatabaseSequence;
import com.hfg.sql.table.ForeignKeyConstraint;
import com.hfg.util.StringUtil;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import java.text.DateFormat;
import java.time.format.DateTimeFormatter;
import java.util.ArrayList;
import java.util.List;
//------------------------------------------------------------------------------
/**
Home for PostgreSQL-specific RDBMS info.
@author J. Alex Taylor, hairyfatguy.com
*/
//------------------------------------------------------------------------------
// com.hfg XML/HTML Coding Library
//
// This library is free software; you can redistribute it and/or
// modify it under the terms of the GNU Lesser General Public
// License as published by the Free Software Foundation; either
// version 2.1 of the License, or (at your option) any later version.
//
// This library is distributed in the hope that it will be useful,
// but WITHOUT ANY WARRANTY; without even the implied warranty of
// MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
// Lesser General Public License for more details.
//
// You should have received a copy of the GNU Lesser General Public
// License along with this library; if not, write to the Free Software
// Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
//
// J. Alex Taylor, President, Founder, CEO, COO, CFO, OOPS hairyfatguy.com
// [email protected]
//------------------------------------------------------------------------------
public class PostgreSQL extends RDBMS
{
// These need to be final or else their values will still be unset when the
// RDBMS static declaration of PostgresSQL is instantiated.
private static final String sName = "PostgreSQL";
private static final String sDriverClassName = "org.postgresql.Driver";
private static final int sDefaultPort = 5432;
private static final int sMaxIdentifierLength = 63;
private static final int sMaxInClauseArgs = 2500; // This is a practical but not absolute value
private static PostgreSQL sInstance;
private DateFormat mSQLDateFormat;
private SQLQuery mConnTestQuery = new SQLQuery().addSelect("1");
private static final DateTimeFormatter TIMESTAMP_FORMATTER = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss");
private static final DateTimeFormatter TIMESTAMP_WITH_TIMEZONE_FORMATTER = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss.SSSxxx");
//###########################################################################
// CONSTRUCTORS
//###########################################################################
//---------------------------------------------------------------------------
private PostgreSQL()
{
super(sName);
setDefaultPort(sDefaultPort);
setMaxIdentifierLength(sMaxIdentifierLength);
setMaxInClauseArgs(sMaxInClauseArgs);
setDriverClassName(sDriverClassName);
}
//###########################################################################
// PUBLIC METHODS
//###########################################################################
//--------------------------------------------------------------------------
/**
Since PostgreSQL doesn't have a hard limit for the number of in-clause
arguments, the user is allowed to set a desired limit.
* @param inValue the limit to use when generating SQL.
* @return this PostgreSQL object
*/
@Override
public PostgreSQL setMaxInClauseArgs(int inValue)
{
return (PostgreSQL) super.setMaxInClauseArgs(inValue);
}
//--------------------------------------------------------------------------
public static synchronized PostgreSQL getInstance()
{
if (null == sInstance)
{
sInstance = new PostgreSQL();
}
return sInstance;
}
//---------------------------------------------------------------------------
public String getConnectString(JDBCServer inServer, String inDatabaseName)
{
return getConnectString(inServer, inDatabaseName, null);
}
//---------------------------------------------------------------------------
public String getConnectString(JDBCServer inServer, String inDatabaseName, JDBCConnectionSettings inSettings)
{
StringBuilder buffer = new StringBuilder();
buffer.append("jdbc:postgresql://");
buffer.append(inServer.getHost());
buffer.append(":");
buffer.append(inServer.getPort());
buffer.append("/");
buffer.append(inDatabaseName);
return buffer.toString();
}
//---------------------------------------------------------------------------
public PostgreSQLConnectionPool establishConnectionPool(JDBCServer inServer, String inDatabaseName, LoginCredentials inCredentials, JDBCConnectionPoolSettings inPoolSettings)
{
PostgreSQLConnectionPool pool = new PostgreSQLConnectionPool(inServer, inDatabaseName, inCredentials);
pool.setSettings(inPoolSettings);
return pool;
}
//---------------------------------------------------------------------------
public PostgreSQLConnection getConnection(JDBCServer inServer, String inDatabaseName, LoginCredentials inCredentials)
throws JDBCException
{
return new PostgreSQLConnection(inServer, inDatabaseName, inCredentials);
}
//---------------------------------------------------------------------------
@Deprecated
public DateFormat getSQLDateFormat(int inSQLType)
{
if (null == mSQLDateFormat)
{
mSQLDateFormat = new ThreadSafeDateFormat("yyyy-MM-dd HH:mm:ss XXX");
}
return mSQLDateFormat;
}
//---------------------------------------------------------------------------
public DateTimeFormatter getSQLDateFormatter(int inSQLType)
{
DateTimeFormatter formatter;
switch (inSQLType)
{
case Types.DATE:
case Types.TIMESTAMP:
// Ex: '1999-01-08 04:05:06'
formatter = TIMESTAMP_FORMATTER;
break;
case Types.TIMESTAMP_WITH_TIMEZONE:
// Ex: '1999-01-08 04:05:06 -8:00'
formatter = TIMESTAMP_WITH_TIMEZONE_FORMATTER;
break;
default:
throw new JDBCException("Currently unsupported SQL Type for date: " + inSQLType + "!");
}
return formatter;
}
//---------------------------------------------------------------------------
public DatabaseSequence allocateSequence(String inName)
{
return new PostgreSQLSequence(inName);
}
//---------------------------------------------------------------------------
public Long getLastGeneratedId(Connection inConn, DatabaseCol inIdCol)
throws SQLException
{
// SELECT currval('persons_id_seq');
DatabaseSequence sequence = inIdCol.getSequence();
if (null == sequence)
{
// SERIAL column
sequence = new DatabaseSequence(inIdCol.getTable().getQualifiedName() + "_" + inIdCol.name() + "_seq");
}
String sql = "SELECT currval(" + StringUtil.singleQuote(sequence) + ")";
Long id = null;
ResultSet rs = null;
try
{
rs = SQLUtil.executeQuery(inConn, sql);
if (rs.next())
{
id = rs.getLong(1);
}
}
finally
{
SQLUtil.closeResultSetAndStatement(rs);
}
return id;
}
//---------------------------------------------------------------------------
public SQLQuery getConnTestQuery()
{
return mConnTestQuery;
}
//---------------------------------------------------------------------------
public boolean tableExists(Connection inConn, Schema inSchema, String inTablename)
throws SQLException
{
SQLQuery query = new SQLQuery()
.addSelect("1")
.addFrom("information_schema.tables")
.addWhereClause("table_schema = " + SQLUtil.sqlString(inSchema))
.addWhereClause("table_name = " + SQLUtil.sqlString(inTablename));
boolean result = false;
ResultSet rs = null;
try
{
rs = query.execute(inConn);
result = rs.next();
}
finally
{
SQLUtil.close(rs);
}
return result;
}
//---------------------------------------------------------------------------
public List getForeignKeyConstraints(Connection inConn, Schema inSchema, String inTablename)
throws SQLException
{
/*
SELECT
tc.table_schema,
tc.constraint_name,
tc.table_name,
kcu.column_name,
ccu.table_schema AS foreign_table_schema,
ccu.table_name AS foreign_table_name,
ccu.column_name AS foreign_column_name
FROM information_schema.table_constraints AS tc
JOIN information_schema.key_column_usage AS kcu
ON tc.constraint_name = kcu.constraint_name
AND tc.table_schema = kcu.table_schema
JOIN information_schema.constraint_column_usage AS ccu
ON ccu.constraint_name = tc.constraint_name
WHERE tc.constraint_type = 'FOREIGN KEY'
AND tc.table_schema='myschema'
AND tc.table_name='mytable';
*/
SQLQuery query = new SQLQuery()
.addSelect("tc.table_schema")
.addSelect("tc.constraint_name")
.addSelect("tc.table_name")
.addSelect("kcu.column_name")
.addSelect("ccu.table_schema AS foreign_table_schema")
.addSelect("ccu.table_name AS foreign_table_name")
.addSelect("ccu.column_name AS foreign_column_name")
.addFrom("information_schema.table_constraints", "tc")
.addJoin("information_schema.key_column_usage AS kcu ON tc.constraint_name = kcu.constraint_name AND tc.table_schema = kcu.table_schema")
.addJoin("information_schema.constraint_column_usage AS ccu ON ccu.constraint_name = tc.constraint_name")
.addWhereClause("tc.constraint_type = 'FOREIGN KEY'")
.addWhereClause("tc.table_name = " + SQLUtil.sqlString(inTablename));
if (inSchema != null)
{
query.addWhereClause("tc.table_schema = " + SQLUtil.sqlString(inSchema));
}
List foreignKeyConstraints = null;
ResultSet rs = null;
try
{
rs = query.execute(inConn);
while (rs.next())
{
if (null == foreignKeyConstraints)
{
foreignKeyConstraints = new ArrayList<>(5);
}
ForeignKeyConstraint foreignKeyConstraint = new ForeignKeyConstraint()
.setConstraintName(rs.getString("constraint_name"))
.setSchemaName(rs.getString("table_schema"))
.setTableName(rs.getString("table_name"))
.setColumnName(rs.getString("column_name"))
.setForeignSchemaName(rs.getString("foreign_table_schema"))
.setForeignTableName(rs.getString("foreign_table_name"))
.setForeignColumnName(rs.getString("foreign_column_name"));
foreignKeyConstraints.add(foreignKeyConstraint);
}
}
finally
{
SQLUtil.close(rs);
}
return foreignKeyConstraints;
}
//---------------------------------------------------------------------------
public String getDatabaseVersion(Connection inConn)
throws SQLException
{
String version = null;
ResultSet rs = null;
try
{
rs = SQLUtil.executeQuery(inConn, "SHOW server_version");
if (rs.next())
{
version = rs.getString(1).split("\\s+")[0];
}
}
finally
{
SQLUtil.close(rs);
}
return version;
}
}