Examples with PGConnection org.postgresql.PGConnection used on opensource projects

Example 11 with PGConnection

use of org.postgresql.PGConnection in project ANNIS by korpling.

the class AdministrationDao method dumpTableToResource.

@Transactional(readOnly = true)
public void dumpTableToResource(String table, WritableResource resource) {
    log.debug("dumping data to '" + resource.getFilename() + "' from table '" + table + "'");
    String sql = "COPY \"" + table + "\" TO STDOUT WITH DELIMITER E'\t' NULL AS 'NULL'";
    try {
        // retrieve the currently open connection if running inside a transaction
        Connection originalCon = DataSourceUtils.getConnection(getDataSource());
        Connection con = originalCon;
        if (con instanceof DelegatingConnection) {
            DelegatingConnection<?> delCon = (DelegatingConnection<?>) con;
            con = delCon.getInnermostDelegate();
        }
        Preconditions.checkState(con instanceof PGConnection, "bulk-loading only works with a PostgreSQL JDBC connection");
        // Postgres JDBC4 8.4 driver now supports the copy API
        PGConnection pgCon = (PGConnection) con;
        pgCon.getCopyAPI().copyOut(sql, resource.getOutputStream());
        DataSourceUtils.releaseConnection(originalCon, getDataSource());
    } catch (SQLException e) {
        throw new DatabaseAccessException(e);
    } catch (IOException e) {
        throw new FileAccessException(e);
    }
}

Example 12 with PGConnection

use of org.postgresql.PGConnection in project ANNIS by korpling.

the class AdministrationDao method bulkloadTableFromResource.

// bulk-loads a table from a resource
private void bulkloadTableFromResource(String table, Resource resource) {
    log.debug("bulk-loading data from '" + resource.getFilename() + "' into table '" + table + "'");
    String sql = "COPY \"" + table + "\" FROM STDIN WITH DELIMITER E'\t' NULL AS 'NULL'";
    try {
        // retrieve the currently open connection if running inside a transaction
        Connection originalCon = DataSourceUtils.getConnection(getDataSource());
        Connection con = originalCon;
        if (con instanceof DelegatingConnection) {
            DelegatingConnection<?> delCon = (DelegatingConnection<?>) con;
            con = delCon.getInnermostDelegate();
        }
        Preconditions.checkState(con instanceof PGConnection, "bulk-loading only works with a PostgreSQL JDBC connection");
        // Postgres JDBC4 8.4 driver now supports the copy API
        PGConnection pgCon = (PGConnection) con;
        pgCon.getCopyAPI().copyIn(sql, resource.getInputStream());
        DataSourceUtils.releaseConnection(originalCon, getDataSource());
    } catch (SQLException e) {
        throw new DatabaseAccessException(e);
    } catch (IOException e) {
        throw new FileAccessException(e);
    }
}

Example 13 with PGConnection

use of org.postgresql.PGConnection in project com.revolsys.open by revolsys.

the class PostgreSQLJdbcBlobFieldDefinition method setPreparedStatementValue.

@Override
public int setPreparedStatementValue(final PreparedStatement statement, final int parameterIndex, final Object value) throws SQLException {
    if (value == null) {
        final int sqlType = getSqlType();
        statement.setNull(parameterIndex, sqlType);
    } else {
        Blob blob;
        if (value instanceof Blob) {
            blob = (Blob) value;
            statement.setBlob(parameterIndex, blob);
        } else {
            InputStream in;
            if (value instanceof InputStream) {
                in = (InputStream) value;
            } else if (value instanceof byte[]) {
                final byte[] bytes = (byte[]) value;
                in = new ByteArrayInputStream(bytes);
            } else if (value instanceof CharSequence) {
                final String string = ((CharSequence) value).toString();
                final byte[] bytes = string.getBytes(StandardCharsets.UTF_8);
                in = new ByteArrayInputStream(bytes);
            } else {
                try {
                    final Resource resource = Resource.getResource(value);
                    in = resource.newBufferedInputStream();
                } catch (final IllegalArgumentException e) {
                    throw new IllegalArgumentException(value.getClass() + " not valid for a blob column");
                }
            }
            try {
                final PGConnection pgConnection = (PGConnection) ((DelegatingConnection<?>) statement.getConnection()).getInnermostDelegate();
                final LargeObjectManager lobManager = pgConnection.getLargeObjectAPI();
                final long lobId = lobManager.createLO(LargeObjectManager.READ | LargeObjectManager.WRITE);
                final LargeObject lob = lobManager.open(lobId, LargeObjectManager.WRITE);
                try {
                    final byte[] buffer = new byte[2048];
                    int readCount = 0;
                    while ((readCount = in.read(buffer, 0, 2048)) > 0) {
                        lob.write(buffer, 0, readCount);
                    }
                } finally {
                    lob.close();
                }
                statement.setLong(parameterIndex, lobId);
            } catch (final IOException e) {
                Exceptions.throwUncheckedException(e);
            }
        }
    }
    return parameterIndex + 1;
}

Example 14 with PGConnection

use of org.postgresql.PGConnection in project hale by halestudio.

the class PostGISGeometries method configureGeometryColumnType.

@Override
public Class<? extends Geometry> configureGeometryColumnType(PGConnection connection, BaseColumn<?> column, DefaultTypeDefinition type, SimpleLog log) {
    Connection con = (Connection) connection;
    String tableName = column.getParent().getName();
    String geometryType = null;
    try {
        Statement stmt = con.createStatement();
        // Get the srid, dimension and geometry type
        /*
			 * FIXME this query should also take into account the schema and
			 * possibly the column name.
			 */
        ResultSet rs = stmt.executeQuery("SELECT srid,type,coord_dimension FROM geometry_columns WHERE f_table_name = " + "'" + tableName + "'");
        if (rs.next()) {
            geometryType = rs.getString("type");
            String dimension = rs.getString("coord_dimension");
            // Get the epsg code for the srid
            String srid = rs.getString("srid");
            ResultSet r = stmt.executeQuery("SELECT auth_srid, auth_name, srtext FROM spatial_ref_sys WHERE srid = " + srid);
            if (r.next()) {
                // Create Constraint to save the informations
                GeometryMetadata columnTypeConstraint = new GeometryMetadata(r.getString("auth_srid"), Integer.parseInt(dimension), r.getString("srtext"), r.getString("auth_name"));
                type.setConstraint(columnTypeConstraint);
                log.info("Determined geometry metadata for table {0} with SRID {1}: {2}", tableName, srid, columnTypeConstraint);
            } else {
                log.warn("Could not determine SRS information for SRID " + srid);
            }
        } else {
            // XXX what if no SRID information is present? is that a case
            // that may still be valid?
            log.warn("Could not determine SRS information for table {0}", tableName);
        }
    } catch (SQLException e) {
        log.error("Error trying to retrieve geometry information for table " + tableName, e);
    }
    // In this case we have no geometry column information
    if (geometryType == null) {
        // use geometry even if no geometry column is present describing it
        return Geometry.class;
    }
    // return the geometryType
    if (geometryType.equalsIgnoreCase("MultiPolygon")) {
        return MultiPolygon.class;
    } else if (geometryType.equalsIgnoreCase("MultiPoint")) {
        return MultiPoint.class;
    } else if (geometryType.equalsIgnoreCase("MultiLineString")) {
        return MultiLineString.class;
    } else // TODO: shouldn't this be LineString instead?
    if (geometryType.equalsIgnoreCase("LinearRing")) {
        return LinearRing.class;
    } else if (geometryType.equalsIgnoreCase("Point")) {
        return Point.class;
    } else if (geometryType.equalsIgnoreCase("Polygon")) {
        return Polygon.class;
    } else {
        return Geometry.class;
    }
}

Example 15 with PGConnection

use of org.postgresql.PGConnection in project sqlg by pietermartin.

the class PostgresDialect method notifyChange.

@Override
public int notifyChange(SqlgGraph sqlgGraph, LocalDateTime timestamp, JsonNode jsonNode) {
    Connection connection = sqlgGraph.tx().getConnection();
    try {
        PGConnection pgConnection = connection.unwrap(PGConnection.class);
        int pid = pgConnection.getBackendPID();
        if (sqlgGraph.tx().isInBatchMode()) {
            BatchManager.BatchModeType batchModeType = sqlgGraph.tx().getBatchModeType();
            sqlgGraph.tx().flush();
            sqlgGraph.tx().batchMode(BatchManager.BatchModeType.NONE);
            sqlgGraph.addVertex(T.label, SQLG_SCHEMA + "." + SQLG_SCHEMA_LOG, "timestamp", timestamp, "pid", pid, "log", jsonNode);
            sqlgGraph.tx().batchMode(batchModeType);
        } else {
            sqlgGraph.addVertex(T.label, SQLG_SCHEMA + "." + SQLG_SCHEMA_LOG, "timestamp", timestamp, "pid", pid, "log", jsonNode);
        }
        try (Statement statement = connection.createStatement()) {
            statement.execute("NOTIFY " + SQLG_NOTIFICATION_CHANNEL + ", '" + timestamp.format(DateTimeFormatter.ISO_LOCAL_DATE_TIME) + "'");
        }
        return pid;
    } catch (SQLException e) {
        throw new RuntimeException(e);
    }
}