Examples with OracleStatement oracle.jdbc.OracleStatement used on opensource projects

Example 1 with OracleStatement

use of oracle.jdbc.OracleStatement in project druid by alibaba.

the class TestMigrate method test_migrate.

public void test_migrate() throws Exception {
    Connection conn = dataSource.getConnection();
    int count = 0;
    {
        String sql = "SELECT COUNT(*) FROM db_day_sqltext";
        Statement stmt = conn.createStatement();
        ResultSet rs = stmt.executeQuery(sql);
        rs.next();
        count = rs.getInt(1);
        rs.close();
        stmt.close();
    }
    System.out.println("COUNT : " + count);
    clearResult();
    String sql = //
    "SELECT SNAP_DATE, DBNAME, SQL_ID, PIECE, SQL_TEXT" + //
    "      , COMMAND_TYPE, LAST_SNAP_DATE, DB_PK, SQL_PARSE_RESULT " + //
    "  FROM db_day_sqltext " + //
    "  WHERE snap_date = trunc(sysdate) " + "  ORDER BY db_pk, sql_id, piece";
    Statement stmt = conn.createStatement();
    OracleStatement oracleStmt = stmt.unwrap(OracleStatement.class);
    oracleStmt.setRowPrefetch(1000);
    ResultSet rs = stmt.executeQuery(sql);
    List<Record> list = new ArrayList<Record>();
    Record r = null;
    String lastSqlId = null;
    int i = 0;
    int j = 0;
    while (rs.next()) {
        j++;
        Date d1 = rs.getDate(1);
        String s2 = rs.getString(2);
        String sqlId = rs.getString(3);
        if (lastSqlId == null || !lastSqlId.equals(sqlId)) {
            if (r != null) {
                System.out.println((i++) + "/" + j + " : " + r.getSqlId());
                System.out.println();
                try {
                    setInfo(r);
                } catch (Throwable e) {
                    e.printStackTrace();
                }
                list.add(r);
                if (list.size() > 100) {
                    insert(list);
                    list.clear();
                }
            }
            r = new Record();
            r.setSnapshotDate(d1);
            r.setDbName(s2);
            r.setSqlId(sqlId);
            r.setPiece(rs.getInt(4));
            r.setSqlText(rs.getString(5));
            r.setCommandType(rs.getInt(6));
            r.setLastSnapshotDate(rs.getDate(7));
            r.setDbPk(rs.getLong(8));
        } else {
            String part = rs.getString(5);
            if (part == null) {
                continue;
            }
            if (part != null) {
                r.appendSqlText(part);
            }
        }
        lastSqlId = sqlId;
    }
    insert(list);
    rs.close();
    stmt.close();
    conn.close();
}

Example 2 with OracleStatement

use of oracle.jdbc.OracleStatement in project jabref by JabRef.

the class OracleProcessor method startNotificationListener.

@Override
public void startNotificationListener(DBMSSynchronizer dbmsSynchronizer) {
    this.listener = new OracleNotificationListener(dbmsSynchronizer);
    try {
        oracleConnection = (OracleConnection) connection;
        Properties properties = new Properties();
        properties.setProperty(OracleConnection.DCN_NOTIFY_ROWIDS, "true");
        properties.setProperty(OracleConnection.DCN_QUERY_CHANGE_NOTIFICATION, "true");
        databaseChangeRegistration = oracleConnection.registerDatabaseChangeNotification(properties);
        databaseChangeRegistration.addListener(listener);
        try (Statement statement = oracleConnection.createStatement()) {
            ((OracleStatement) statement).setDatabaseChangeRegistration(databaseChangeRegistration);
            StringBuilder selectQuery = new StringBuilder().append("SELECT 1 FROM ").append(escape("ENTRY")).append(", ").append(escape("METADATA"));
            // this execution registers all tables mentioned in selectQuery
            statement.executeQuery(selectQuery.toString());
        }
    } catch (SQLException e) {
        LOGGER.error("SQL Error: ", e);
    }
}

Example 3 with OracleStatement

use of oracle.jdbc.OracleStatement in project druid by alibaba.

the class TestTransform method f_test_migrate.

public void f_test_migrate() throws Exception {
    Connection conn = dataSource.getConnection();
    int count = 0;
    {
        String sql = "SELECT COUNT(*) FROM db_day_sqltext";
        Statement stmt = conn.createStatement();
        ResultSet rs = stmt.executeQuery(sql);
        rs.next();
        count = rs.getInt(1);
        rs.close();
        stmt.close();
    }
    System.out.println("COUNT : " + count);
    clearResult();
    String sql = //
    "SELECT SNAP_DATE, DBNAME, SQL_ID, PIECE, SQL_TEXT" + //
    "      , COMMAND_TYPE, LAST_SNAP_DATE, DB_PK, SQL_PARSE_RESULT " + //
    "  FROM db_day_sqltext " + // "  WHERE db_pk = 40 and snap_date = trunc(sysdate) " + //
    "  ORDER BY db_pk, sql_id, piece";
    Statement stmt = conn.createStatement();
    OracleStatement oracleStmt = stmt.unwrap(OracleStatement.class);
    oracleStmt.setRowPrefetch(1000);
    ResultSet rs = stmt.executeQuery(sql);
    List<Record> list = new ArrayList<Record>();
    Record r = null;
    String lastSqlId = null;
    int i = 0;
    int j = 0;
    while (rs.next()) {
        j++;
        Date d1 = rs.getDate(1);
        String s2 = rs.getString(2);
        String sqlId = rs.getString(3);
        if (lastSqlId == null || !lastSqlId.equals(sqlId)) {
            if (r != null) {
                System.out.println((i++) + "/" + j + " : " + r.getSqlId());
                System.out.println();
                try {
                    setInfo(r);
                } catch (Throwable e) {
                    e.printStackTrace();
                }
                list.add(r);
                if (list.size() > 100) {
                    insert(list);
                    list.clear();
                }
            }
            r = new Record();
            r.setSnapshotDate(d1);
            r.setDbName(s2);
            r.setSqlId(sqlId);
            r.setPiece(rs.getInt(4));
            r.setSqlText(rs.getString(5));
            r.setCommandType(rs.getInt(6));
            r.setLastSnapshotDate(rs.getDate(7));
            r.setDbPk(rs.getLong(8));
        } else {
            String part = rs.getString(5);
            if (part == null) {
                continue;
            }
            int commentIndex = part.indexOf("--");
            if (commentIndex != -1) {
                part = part.substring(0, commentIndex);
            }
            if (part != null) {
                r.appendSqlText(part);
            }
        }
        lastSqlId = sqlId;
    }
    insert(list);
    rs.close();
    stmt.close();
    conn.close();
}

Example 4 with OracleStatement

use of oracle.jdbc.OracleStatement in project druid by alibaba.

the class TestTransform method test_transform.

public void test_transform() throws Exception {
    Connection conn = dataSource.getConnection();
    int count = 0;
    {
        String sql = "SELECT COUNT(*) FROM db_day_sql_fulltext WHERE SQL_PARSE_RESULT IS NULL";
        Statement stmt = conn.createStatement();
        ResultSet rs = stmt.executeQuery(sql);
        rs.next();
        count = rs.getInt(1);
        rs.close();
        stmt.close();
    }
    System.out.println("COUNT : " + count);
    String sql = //
    "SELECT SNAP_DATE, DBNAME, SQL_ID, PIECE, SQL_TEXT" + //
    "      , COMMAND_TYPE, LAST_SNAP_DATE, DB_PK, SQL_PARSE_RESULT " + //
    "  FROM db_day_sql_fulltext " + //
    " WHERE SQL_PARSE_RESULT IS NULL" + "  ORDER BY sql_id, piece";
    Statement stmt = conn.createStatement();
    OracleStatement oracleStmt = stmt.unwrap(OracleStatement.class);
    oracleStmt.setRowPrefetch(100);
    ResultSet rs = stmt.executeQuery(sql);
    Record r = null;
    int i = 0;
    while (rs.next()) {
        r = new Record();
        i++;
        Date d1 = rs.getDate(1);
        String s2 = rs.getString(2);
        String sqlId = rs.getString(3);
        r.setSnapshotDate(d1);
        r.setDbName(s2);
        r.setSqlId(sqlId);
        r.setPiece(rs.getInt(4));
        r.setSqlText(rs.getString(5));
        r.setCommandType(rs.getInt(6));
        r.setLastSnapshotDate(rs.getDate(7));
        r.setDbPk(rs.getLong(8));
        System.out.println(i + "(" + r.getDbName() + "/" + r.getSqlId() + "/" + r.getCommandType() + ") : " + r.getSqlText());
        try {
            schemaStatInternal(r);
        } catch (Throwable e) {
        //                e.printStackTrace();
        }
    }
    rs.close();
    stmt.close();
    conn.close();
}