/** * For table containing catalog, schema and table name, this function create a prepared statement with a filter on * this combination. Use "f_table_catalog","f_table_schema","f_table_name" as field names. * @param connection Active connection * @param catalog Table catalog, may be empty * @param schema Table schema, may be empty * @param table Table name * @param informationSchemaTable Information table location * @param endQuery Additional where statement * @return Prepared statement * @throws SQLException */ public static PreparedStatement prepareInformationSchemaStatement(Connection connection,String catalog, String schema, String table, String informationSchemaTable, String endQuery) throws SQLException { return prepareInformationSchemaStatement(connection,catalog, schema, table, informationSchemaTable, endQuery,"f_table_catalog","f_table_schema","f_table_name"); }
/** * For table containing catalog, schema and table name, this function create a prepared statement with a filter on * this combination. Use "f_table_catalog","f_table_schema","f_table_name" as field names. * @param connection Active connection * @param catalog Table catalog, may be empty * @param schema Table schema, may be empty * @param table Table name * @param informationSchemaTable Information table location * @param endQuery Additional where statement * @return Prepared statement * @throws SQLException */ public static PreparedStatement prepareInformationSchemaStatement(Connection connection,String catalog, String schema, String table, String informationSchemaTable, String endQuery) throws SQLException { return prepareInformationSchemaStatement(connection,catalog, schema, table, informationSchemaTable, endQuery,"f_table_catalog","f_table_schema","f_table_name"); }
private static ResultSet getGeometryColumnsView(Connection connection,String catalog, String schema, String table) throws SQLException { PreparedStatement geomStatement = prepareInformationSchemaStatement(connection,catalog, schema, table, "geometry_columns", ""); return geomStatement.executeQuery(); } /**
/** * For table containing catalog, schema and table name, this function create a prepared statement with a filter on * this combination. Use "f_table_catalog","f_table_schema","f_table_name" as field names. * * @param connection Active connection * @param catalog Table catalog, may be empty * @param schema Table schema, may be empty * @param table Table name * @param informationSchemaTable Information table location * @param endQuery Additional where statement * * @return Prepared statement * * @throws SQLException */ public static PreparedStatement prepareInformationSchemaStatement(Connection connection,String catalog, String schema, String table, String informationSchemaTable, String endQuery) throws SQLException { return prepareInformationSchemaStatement(connection,catalog, schema, table, informationSchemaTable, endQuery, "f_table_catalog","f_table_schema","f_table_name"); }
private static ResultSet getGeometryColumnsView(Connection connection,String catalog, String schema, String table) throws SQLException { PreparedStatement geomStatement = prepareInformationSchemaStatement(connection,catalog, schema, table, "geometry_columns", ""); return geomStatement.executeQuery(); } /**
private static ResultSet getGeometryColumnsView(Connection connection,String catalog, String schema, String table) throws SQLException { PreparedStatement geomStatement = prepareInformationSchemaStatement(connection,catalog, schema, table, "geometry_columns", ""); return geomStatement.executeQuery(); } /**
/** * Read table constraints from database metadata. * @param connection Active connection * @param catalogName Catalog name or empty string * @param schemaName Schema name or empty string * @param tableName table name * @return Found table constraints * @throws SQLException */ public static String fetchConstraint(Connection connection, String catalogName, String schemaName, String tableName) throws SQLException { // Merge column constraint and table constraint PreparedStatement pst = SFSUtilities.prepareInformationSchemaStatement(connection, catalogName, schemaName, tableName, "INFORMATION_SCHEMA.CONSTRAINTS", "", "TABLE_CATALOG", "TABLE_SCHEMA","TABLE_NAME"); ResultSet rsConstraint = pst.executeQuery(); try { StringBuilder constraint = new StringBuilder(); while (rsConstraint.next()) { String tableConstr = rsConstraint.getString("CHECK_EXPRESSION"); if(tableConstr != null) { constraint.append(tableConstr); } } return constraint.toString(); } finally { rsConstraint.close(); pst.close(); } }
/** * Read table constraints from database metadata. * @param connection Active connection * @param catalogName Catalog name or empty string * @param schemaName Schema name or empty string * @param tableName table name * @return Found table constraints * @throws SQLException */ public static String fetchConstraint(Connection connection, String catalogName, String schemaName, String tableName) throws SQLException { // Merge column constraint and table constraint PreparedStatement pst = SFSUtilities.prepareInformationSchemaStatement(connection, catalogName, schemaName, tableName, "INFORMATION_SCHEMA.CONSTRAINTS", "", "TABLE_CATALOG", "TABLE_SCHEMA","TABLE_NAME"); ResultSet rsConstraint = pst.executeQuery(); try { StringBuilder constraint = new StringBuilder(); while (rsConstraint.next()) { String tableConstr = rsConstraint.getString("CHECK_EXPRESSION"); if(tableConstr != null) { constraint.append(tableConstr); } } return constraint.toString(); } finally { rsConstraint.close(); pst.close(); } }
/** * Read table constraints from database metadata. * @param connection Active connection * @param catalogName Catalog name or empty string * @param schemaName Schema name or empty string * @param tableName table name * @return Found table constraints * @throws SQLException */ public static String fetchConstraint(Connection connection, String catalogName, String schemaName, String tableName) throws SQLException { // Merge column constraint and table constraint PreparedStatement pst = SFSUtilities.prepareInformationSchemaStatement(connection, catalogName, schemaName, tableName, "INFORMATION_SCHEMA.CONSTRAINTS", "", "TABLE_CATALOG", "TABLE_SCHEMA","TABLE_NAME"); ResultSet rsConstraint = pst.executeQuery(); try { StringBuilder constraint = new StringBuilder(); while (rsConstraint.next()) { String tableConstr = rsConstraint.getString("CHECK_EXPRESSION"); if(tableConstr != null) { constraint.append(tableConstr); } } return constraint.toString(); } finally { rsConstraint.close(); pst.close(); } }
/** * Read table constraints from database metadata. * @param connection Active connection * @param catalogName Catalog name or empty string * @param schemaName Schema name or empty string * @param tableName table name * @return Found table constraints * @throws SQLException */ public static String fetchConstraint(Connection connection, String catalogName, String schemaName, String tableName) throws SQLException { // Merge column constraint and table constraint PreparedStatement pst = SFSUtilities.prepareInformationSchemaStatement(connection, catalogName, schemaName, tableName, "INFORMATION_SCHEMA.CONSTRAINTS", "", "TABLE_CATALOG", "TABLE_SCHEMA","TABLE_NAME"); try (ResultSet rsConstraint = pst.executeQuery()) { StringBuilder constraint = new StringBuilder(); while (rsConstraint.next()) { String tableConstr = rsConstraint.getString("CHECK_EXPRESSION"); if(tableConstr != null) { constraint.append(tableConstr); } } return constraint.toString(); } finally { pst.close(); } }
public static long getRowCount(Connection connection, String tableReference) throws SQLException { TableLocation tableLocation = TableLocation.parse(tableReference); if(JDBCUtilities.isH2DataBase(connection.getMetaData())) { try(PreparedStatement st = SFSUtilities.prepareInformationSchemaStatement(connection,tableLocation.getCatalog(), tableLocation.getSchema(), tableLocation.getTable(), "INFORMATION_SCHEMA.TABLES", "", "TABLE_CATALOG","TABLE_SCHEMA","TABLE_NAME"); ResultSet rs = st.executeQuery()) { if(rs.next()) { long estimatedRowCount = rs.getLong("ROW_COUNT_ESTIMATE"); // 100 because H2 views est if(estimatedRowCount > 0 && !"VIEW".equalsIgnoreCase(rs.getString("TABLE_TYPE"))) { return estimatedRowCount; } } } catch (Exception ex) { // This method failed, will use standard one LOGGER.debug(ex.getLocalizedMessage(), ex); } } // Use precise row count try(Statement st = connection.createStatement(); ResultSet rs = st.executeQuery("SELECT COUNT(*) cpt FROM "+tableReference)) { rs.next(); return rs.getLong(1); } }
public static long getRowCount(Connection connection, String tableReference) throws SQLException { TableLocation tableLocation = TableLocation.parse(tableReference); if(JDBCUtilities.isH2DataBase(connection.getMetaData())) { try(PreparedStatement st = SFSUtilities.prepareInformationSchemaStatement(connection,tableLocation.getCatalog(), tableLocation.getSchema(), tableLocation.getTable(), "INFORMATION_SCHEMA.TABLES", "", "TABLE_CATALOG","TABLE_SCHEMA","TABLE_NAME"); ResultSet rs = st.executeQuery()) { if(rs.next()) { long estimatedRowCount = rs.getLong("ROW_COUNT_ESTIMATE"); // 100 because H2 views est if(estimatedRowCount > 0 && !"VIEW".equalsIgnoreCase(rs.getString("TABLE_TYPE"))) { return estimatedRowCount; } } } catch (Exception ex) { // This method failed, will use standard one LOGGER.debug(ex.getLocalizedMessage(), ex); } } // Use precise row count try(Statement st = connection.createStatement(); ResultSet rs = st.executeQuery("SELECT COUNT(*) cpt FROM "+tableReference)) { rs.next(); return rs.getLong(1); } }
public static double getActiveLayerInitialZ(Connection connection ,MapContext mapContext) { String table = mapContext.getActiveLayer().getTableReference(); if(!table.isEmpty()) { TableLocation tableLocation = TableLocation.parse(table); try(PreparedStatement st = SFSUtilities.prepareInformationSchemaStatement(connection, tableLocation.getCatalog(),tableLocation.getSchema(),tableLocation.getTable(), "GEOMETRY_COLUMNS",""); ResultSet rs = st.executeQuery()) { if(rs.next()) { switch (rs.getInt("coord_dimension")) { case 3: //XYZ case 4: //XYZM return 0; default: //2 and 5 XYM return Double.NaN; } } } catch (SQLException ex) { LOGGER.debug(ex.getLocalizedMessage(), ex); return Double.NaN; } } return Double.NaN; }
try(Connection connection = layer.getDataManager().getDataSource().getConnection()) { try(PreparedStatement pst = SFSUtilities.prepareInformationSchemaStatement(connection, tableLocation.getCatalog(), tableLocation.getSchema(), tableLocation.getTable(), "PUBLIC.GEOMETRY_COLUMNS", "");