Example usage for java.sql Statement setQueryTimeout

List of usage examples for java.sql Statement setQueryTimeout

  1. HOME
  2. Java
  3. java
  4. java.sql.*
  5. Statement
  6. setQueryTimeout

Introduction

In this page you can find the example usage for java.sql Statement setQueryTimeout.

Prototype

void setQueryTimeout(int seconds) throws SQLException;

Source Link

Document

Sets the number of seconds the driver will wait for a Statement object to execute to the given number of seconds.

Usage

From source file:com.ingby.socbox.bischeck.test.JDBCtest.java

static public void main(String[] args)
        throws InstantiationException, IllegalAccessException, ClassNotFoundException, SQLException {

    CommandLineParser parser = new GnuParser();
    CommandLine line = null;/*from   www . j ava2  s  . c  om*/

    // create the Options
    Options options = new Options();
    options.addOption("u", "usage", false, "show usage.");
    options.addOption("c", "connection", true, "the connection url");
    options.addOption("s", "sql", true, "the sql statement to run");
    options.addOption("m", "meta", true, "get the table meta data");
    options.addOption("C", "columns", true, "the number of columns to display, default 1");
    options.addOption("d", "driver", true, "the driver class");
    options.addOption("v", "verbose", false, "verbose outbout");

    try {
        // parse the command line arguments
        line = parser.parse(options, args);
    } catch (org.apache.commons.cli.ParseException e) {
        System.out.println("Command parse error:" + e.getMessage());
        HelpFormatter formatter = new HelpFormatter();
        formatter.printHelp("JDBCtest", options);
        Util.ShellExit(1);
    }

    if (line.hasOption("verbose")) {
        verbose = true;
    }

    if (line.hasOption("usage")) {
        HelpFormatter formatter = new HelpFormatter();
        formatter.printHelp("Bischeck", options);
        Util.ShellExit(0);
    }

    String driverclassname = null;
    if (!line.hasOption("driver")) {
        System.out.println("Driver class must be set");
        Util.ShellExit(1);
    } else {
        driverclassname = line.getOptionValue("driver");
        outputln("DriverClass: " + driverclassname);
    }

    String connectionname = null;
    if (!line.hasOption("connection")) {
        System.out.println("Connection url must be set");
        Util.ShellExit(1);
    } else {
        connectionname = line.getOptionValue("connection");
        outputln("Connection: " + connectionname);
    }

    String sql = null;
    String tablename = null;

    if (line.hasOption("sql")) {
        sql = line.getOptionValue("sql");
        outputln("SQL: " + sql);

    }

    if (line.hasOption("meta")) {
        tablename = line.getOptionValue("meta");
        outputln("Table: " + tablename);
    }

    int nrColumns = 1;
    if (line.hasOption("columns")) {
        nrColumns = new Integer(line.getOptionValue("columns"));
    }

    long execStart = 0l;
    long execEnd = 0l;
    long openStart = 0l;
    long openEnd = 0l;
    long metaStart = 0l;
    long metaEnd = 0l;

    Class.forName(driverclassname).newInstance();
    openStart = System.currentTimeMillis();
    Connection conn = DriverManager.getConnection(connectionname);
    openEnd = System.currentTimeMillis();

    if (tablename != null) {
        ResultSet rsCol = null;
        metaStart = System.currentTimeMillis();
        DatabaseMetaData md = conn.getMetaData();
        metaEnd = System.currentTimeMillis();

        rsCol = md.getColumns(null, null, tablename, null);
        if (verbose) {
            tabular("COLUMN_NAME");
            tabular("TYPE_NAME");
            tabular("COLUMN_SIZE");
            tabularlast("DATA_TYPE");
            outputln("");
        }

        while (rsCol.next()) {
            tabular(rsCol.getString("COLUMN_NAME"));
            tabular(rsCol.getString("TYPE_NAME"));
            tabular(rsCol.getString("COLUMN_SIZE"));
            tabularlast(rsCol.getString("DATA_TYPE"));
            outputln("", true);
        }
    }

    if (sql != null) {
        Statement stat = conn.createStatement();
        stat.setQueryTimeout(10);

        execStart = System.currentTimeMillis();
        ResultSet res = stat.executeQuery(sql);
        ResultSetMetaData rsmd = res.getMetaData();
        execEnd = System.currentTimeMillis();

        if (verbose) {
            for (int i = 1; i < nrColumns + 1; i++) {
                if (i != nrColumns)
                    tabular(rsmd.getColumnName(i));
                else
                    tabularlast(rsmd.getColumnName(i));
            }
            outputln("");
        }
        while (res.next()) {
            for (int i = 1; i < nrColumns + 1; i++) {
                if (i != nrColumns)
                    tabular(res.getString(i));
                else
                    tabularlast(res.getString(i));
            }
            outputln("", true);
        }

        stat.close();
        res.close();
    }

    conn.close();

    // Print the execution times
    outputln("Open time: " + (openEnd - openStart) + " ms");

    if (line.hasOption("meta")) {
        outputln("Meta time: " + (metaEnd - metaStart) + " ms");
    }

    if (line.hasOption("sql")) {
        outputln("Exec time: " + (execEnd - execStart) + " ms");
    }
}

From source file:io.cloudslang.content.database.services.SQLQueryService.java

public static void executeSqlQuery(@NotNull final SQLInputs sqlInputs) throws Exception {
    if (StringUtils.isEmpty(sqlInputs.getSqlCommand())) {
        throw new Exception("command input is empty.");
    }// w  w  w  .j  a v  a  2s  .  c om
    ConnectionService connectionService = new ConnectionService();
    try (final Connection connection = connectionService.setUpConnection(sqlInputs)) {

        connection.setReadOnly(true);
        Statement statement = connection.createStatement(sqlInputs.getResultSetType(),
                sqlInputs.getResultSetConcurrency());
        statement.setQueryTimeout(sqlInputs.getTimeout());
        final ResultSet results = statement.executeQuery(sqlInputs.getSqlCommand());

        final ResultSetMetaData mtd = results.getMetaData();

        int iNumCols = mtd.getColumnCount();

        final StringBuilder strColumns = new StringBuilder(sqlInputs.getStrColumns());

        for (int i = 1; i <= iNumCols; i++) {
            if (i > 1) {
                strColumns.append(sqlInputs.getStrDelim());
            }
            strColumns.append(mtd.getColumnLabel(i));
        }
        sqlInputs.setStrColumns(strColumns.toString());

        while (results.next()) {
            final StringBuilder strRowHolder = new StringBuilder();
            for (int i = 1; i <= iNumCols; i++) {
                if (i > 1)
                    strRowHolder.append(sqlInputs.getStrDelim());
                if (results.getString(i) != null) {
                    String value = results.getString(i).trim();
                    if (sqlInputs.isNetcool())
                        value = SQLUtils.processNullTerminatedString(value);

                    strRowHolder.append(value);
                }
            }
            sqlInputs.getLRows().add(strRowHolder.toString());
        }
    }
}

From source file:io.cloudslang.content.database.services.SQLQueryLobService.java

public static boolean executeSqlQueryLob(SQLInputs sqlInputs) throws Exception {
    if (StringUtils.isEmpty(sqlInputs.getSqlCommand())) {
        throw new Exception("command input is empty.");
    }/*from w  w w . j  av a  2s. co m*/
    boolean isLOB = false;
    ConnectionService connectionService = new ConnectionService();
    try (final Connection connection = connectionService.setUpConnection(sqlInputs)) {

        StringBuilder strColumns = new StringBuilder(sqlInputs.getStrColumns());

        connection.setReadOnly(true);
        Statement statement = connection.createStatement(sqlInputs.getResultSetType(),
                sqlInputs.getResultSetConcurrency());
        statement.setQueryTimeout(sqlInputs.getTimeout());

        ResultSet results = statement.executeQuery(sqlInputs.getSqlCommand());
        ResultSetMetaData mtd = results.getMetaData();
        int iNumCols = mtd.getColumnCount();
        for (int i = 1; i <= iNumCols; i++) {
            if (i > 1)
                strColumns.append(sqlInputs.getStrDelim());
            strColumns.append(mtd.getColumnLabel(i));
        }
        sqlInputs.setStrColumns(strColumns.toString());
        int nr = -1;
        while (results.next()) {
            nr++;
            final StringBuilder strRowHolder = new StringBuilder();
            for (int i = 1; i <= iNumCols; i++) {
                if (i > 1)
                    strRowHolder.append(sqlInputs.getStrDelim());
                Object columnObject = results.getObject(i);
                if (columnObject != null) {
                    String value;
                    if (columnObject instanceof java.sql.Clob) {
                        isLOB = true;
                        final File tmpFile = File.createTempFile("CLOB_" + mtd.getColumnLabel(i), ".txt");

                        copyInputStreamToFile(
                                new ReaderInputStream(results.getCharacterStream(i), StandardCharsets.UTF_8),
                                tmpFile);

                        if (sqlInputs.getLRowsFiles().size() == nr) {
                            sqlInputs.getLRowsFiles().add(nr, new ArrayList<String>());
                            sqlInputs.getLRowsNames().add(nr, new ArrayList<String>());
                        }
                        sqlInputs.getLRowsFiles().get(nr).add(tmpFile.getAbsolutePath());
                        sqlInputs.getLRowsNames().get(nr).add(mtd.getColumnLabel(i));
                        value = "(CLOB)...";

                    } else {
                        value = results.getString(i);
                        if (sqlInputs.isNetcool())
                            value = SQLUtils.processNullTerminatedString(value);
                    }
                    strRowHolder.append(value);
                } else
                    strRowHolder.append("null");
            }
            sqlInputs.getLRows().add(strRowHolder.toString());
        }
    }

    return isLOB;
}

From source file:io.cloudslang.content.database.services.SQLCommandService.java

public static String executeSqlCommand(final SQLInputs sqlInputs) throws Exception {
    final ConnectionService connectionService = new ConnectionService();
    try (final Connection connection = connectionService.setUpConnection(sqlInputs)) {

        connection.setReadOnly(false);//from   w  w  w .  j a  v  a 2  s.c  o  m

        final String dbType = sqlInputs.getDbType();
        if (ORACLE_DB_TYPE.equalsIgnoreCase(dbType)
                && sqlInputs.getSqlCommand().toLowerCase().contains(DBMS_OUTPUT)) {

            final PreparedStatement preparedStatement = connection.prepareStatement(sqlInputs.getSqlCommand());
            preparedStatement.setQueryTimeout(sqlInputs.getTimeout());
            OracleDbmsOutput oracleDbmsOutput = new OracleDbmsOutput(connection);
            preparedStatement.executeQuery();
            sqlInputs.setIUpdateCount(preparedStatement.getUpdateCount());
            preparedStatement.close();
            final String output = oracleDbmsOutput.getOutput();
            oracleDbmsOutput.close();
            return output;
        } else {
            final Statement statement = connection.createStatement(sqlInputs.getResultSetType(),
                    sqlInputs.getResultSetConcurrency());
            statement.setQueryTimeout(sqlInputs.getTimeout());
            try {
                statement.execute(sqlInputs.getSqlCommand());
            } catch (SQLException e) {
                if (SYBASE_DB_TYPE.equalsIgnoreCase(dbType)) {
                    //during a dump sybase sends back status as exceptions.
                    if (sqlInputs.getSqlCommand().trim().toLowerCase().startsWith("dump")) {
                        return SQLUtils.processDumpException(e);
                    } else if (sqlInputs.getSqlCommand().trim().toLowerCase().startsWith("load")) {
                        return SQLUtils.processLoadException(e);
                    }
                } else {
                    throw e;
                }
            }

            ResultSet rs = statement.getResultSet();
            if (rs != null) {
                ResultSetMetaData rsMtd = rs.getMetaData();
                if (rsMtd != null) {
                    sqlInputs.getLRows().clear();
                    int colCount = rsMtd.getColumnCount();

                    if (sqlInputs.getSqlCommand().trim().toLowerCase().startsWith("dbcc")) {
                        while (rs.next()) {
                            if (colCount >= 4) {
                                sqlInputs.getLRows().add(rs.getString(4));
                            }
                        }
                    } else {
                        String delimiter = (StringUtils.isNoneEmpty(sqlInputs.getStrDelim()))
                                ? sqlInputs.getStrDelim()
                                : ",";
                        String strRowHolder;
                        while (rs.next()) {
                            strRowHolder = "";
                            for (int i = 1; i <= colCount; i++) {
                                if (i > 1) {
                                    strRowHolder += delimiter;
                                }
                                strRowHolder += rs.getString(i);
                            }
                            sqlInputs.getLRows().add(strRowHolder);
                        }
                    }
                    rs.close();
                }

            }
            //For sybase, when dbcc command is executed, the result is shown in warning message
            else if (dbType.equalsIgnoreCase(SYBASE_DB_TYPE)
                    && sqlInputs.getSqlCommand().trim().toLowerCase().startsWith("dbcc")) {
                SQLWarning warning = statement.getWarnings();
                while (warning != null) {
                    sqlInputs.getLRows().add(warning.getMessage());
                    warning = warning.getNextWarning();
                }
            }

            sqlInputs.setIUpdateCount(statement.getUpdateCount());
        }
    }
    return "Command completed successfully";
}

From source file:org.bml.util.geo.util.geolite.GeoLiteCityBlock.java

public static Map<Integer, GeoLiteCityBlock> readFromDB(ComboPooledDataSource dataSource) {
    Map<Integer, GeoLiteCityBlock> mapOut = new TreeMap<Integer, GeoLiteCityBlock>();
    Connection con = null;// w w w  .j av  a  2 s .c  o m
    Statement st = null;
    ResultSet rs = null;
    GeoLiteCityBlock tmp = null;
    int c = 0;
    try {
        con = dataSource.getConnection();

        st = con.createStatement();
        st.setMaxRows(Integer.MAX_VALUE);
        st.setQueryTimeout(600000);
        st.setFetchSize(100000);

        rs = st.executeQuery(GeoLiteCityBlock.PREPARED_SELECT_SQL);
        while (rs.next()) {
            c++;
            mapOut.put(rs.getInt(FIELD.STARTIP.fieldName),
                    new GeoLiteCityBlock(rs.getInt(FIELD.STARTIP.fieldName), rs.getInt(FIELD.ENDIP.fieldName),
                            rs.getInt(FIELD.LOCID.fieldName)));
            if ((c % 100000) == 0) {
                if (LOG.isInfoEnabled()) {
                    LOG.info("Loaded " + c + " IP Block to Location mappings");
                }
            }
        }
    } catch (SQLException ex) {
        if (LOG.isWarnEnabled()) {
            LOG.warn("SQLException caught while loading GeoLiteCityBlock objects ", ex);
        }
    } finally {
        DbUtils.closeQuietly(con, st, rs);
    }
    return mapOut;
}

From source file:org.bml.util.geo.util.geolite.GeoLiteCityLocation.java

public static Map<Integer, GeoLiteCityLocation> readFromDB(ComboPooledDataSource dataSource) {
    Map<Integer, GeoLiteCityLocation> mapOut = new HashMap<Integer, GeoLiteCityLocation>();
    Connection con = null;//from   w  w  w.  j  a va 2  s .  co m
    Statement st = null;
    ResultSet rs = null;
    GeoLiteCityBlock tmp = null;
    int c = 0;
    try {
        con = dataSource.getConnection();

        st = con.createStatement();
        st.setMaxRows(Integer.MAX_VALUE);
        st.setQueryTimeout(600000);
        st.setFetchSize(100000);

        rs = st.executeQuery(PREPARED_SELECT_SQL);
        while (rs.next()) {
            c++;
            mapOut.put(rs.getInt(FIELD.LOCID.fieldName),
                    new GeoLiteCityLocation(rs.getInt(FIELD.LOCID.fieldName),
                            rs.getString(FIELD.COUNTRY.fieldName), rs.getString(FIELD.REGION.fieldName),
                            rs.getString(FIELD.CITY.fieldName), rs.getString(FIELD.POSTALCODE.fieldName),
                            rs.getDouble(FIELD.LATITUDE.fieldName), rs.getDouble(FIELD.LONGITUDE.fieldName),
                            rs.getString(FIELD.METROCODE.fieldName), rs.getLong(FIELD.AREACODE.fieldName)));
            if ((c % 100000) == 0) {
                if (LOG.isInfoEnabled()) {
                    LOG.info("Loaded " + c + " Location mappings");
                }
            }
        }
    } catch (SQLException ex) {
        if (LOG.isWarnEnabled()) {
            LOG.warn("SQLException caught while loading GeoLiteCityBlock objects ", ex);
        }
    } finally {
        DbUtils.closeQuietly(con, st, rs);
    }
    return mapOut;
}

From source file:org.ut.biolab.medsavant.server.db.variants.VariantManagerUtils.java

@Deprecated
public static int uploadTSVFileToVariantTableOld(String sid, File file, String tableName)
        throws SQLException, IOException, SessionExpiredException {

    file = cleanVariantFile(file, tableName, sid);

    BufferedReader br = new BufferedReader(new FileReader(file));

    // TODO: for some reason the connection is closed going into this function
    Connection c = ConnectionController.connectPooled(sid);

    c.setAutoCommit(false);//from w  w w  . ja v  a  2 s  .com

    int chunkSize = 100000; // number of lines per chunk (100K lines = ~50MB for a standard VCF file)
    int lineNumber = 0;

    BufferedWriter bw = null;
    String currentOutputPath = null;

    boolean stateOpen = false;

    String parentDirectory = file.getParentFile().getAbsolutePath();

    String line;
    while ((line = br.readLine()) != null) {
        lineNumber++;

        // start a new output file
        if (lineNumber % chunkSize == 1) {
            currentOutputPath = parentDirectory + "/" + MiscUtils.extractFileName(file.getAbsolutePath()) + "_"
                    + (lineNumber / chunkSize);
            LOG.info("Opening new partial file " + currentOutputPath);
            bw = new BufferedWriter(new FileWriter(currentOutputPath));
            stateOpen = true;
        }

        // write line to chunk file
        bw.write(line + "\r\n");

        // close and upload this output file
        if (lineNumber % chunkSize == 0) {
            bw.close();

            LOG.info("Closing and uploading partial file " + currentOutputPath);

            String query = "LOAD DATA LOCAL INFILE '" + currentOutputPath.replaceAll("\\\\", "/") + "' "
                    + "INTO TABLE " + tableName + " " + "FIELDS TERMINATED BY '"
                    + VariantManagerUtils.FIELD_DELIMITER + "' ENCLOSED BY '" + VariantManagerUtils.ENCLOSED_BY
                    + "' " + "ESCAPED BY '" + StringEscapeUtils.escapeJava(VariantManagerUtils.ESCAPE_CHAR)
                    + "' " + " LINES TERMINATED BY '\\r\\n'" + ";";

            //  LOG.info(query);
            Statement s = c.createStatement();
            s.setQueryTimeout(30 * 60); // 30 minutes
            s.execute(query);

            /*if (VariantManager.REMOVE_TMP_FILES) {
             boolean deleted = new File(currentOutputPath).delete();
             LOG.info("Deleting " + currentOutputPath + " - " + (deleted ? "successful" : "failed"));
             }*/
            stateOpen = false;
        }
    }

    // write the remaining open file
    if (bw != null && stateOpen) {
        bw.close();
        String query = "LOAD DATA LOCAL INFILE '" + currentOutputPath.replaceAll("\\\\", "/") + "' "
                + "INTO TABLE " + tableName + " " + "FIELDS TERMINATED BY '"
                + StringEscapeUtils.escapeJava(VariantManagerUtils.FIELD_DELIMITER) + "' ENCLOSED BY '"
                + VariantManagerUtils.ENCLOSED_BY + "' " + "ESCAPED BY '"
                + StringEscapeUtils.escapeJava(VariantManagerUtils.ESCAPE_CHAR) + "'"
                + " LINES TERMINATED BY '\\r\\n'" + ";";

        LOG.info("Closing and uploading last partial file " + currentOutputPath);

        LOG.info(query);
        Statement s = c.createStatement();
        s.setQueryTimeout(60 * 60); // 1 hour
        s.execute(query);

        /*if (VariantManager.REMOVE_TMP_FILES) {
         boolean deleted = new File(currentOutputPath).delete();
         LOG.info("Deleting " + currentOutputPath + " - " + (deleted ? "successful" : "failed"));
         }*/
    }

    LOG.info("Imported " + lineNumber + " lines of variants in total");

    c.commit();
    c.setAutoCommit(true);

    c.close();

    /*if (VariantManager.REMOVE_TMP_FILES) {
     boolean deleted = file.delete();
     LOG.info("Deleting " + file.getAbsolutePath() + " - " + (deleted ? "successful" : "failed"));
     }*/
    return lineNumber;
}

From source file:org.cfr.capsicum.datasource.DataSourceUtils.java

/**
 * Apply the specified timeout - overridden by the current transaction timeout,
 * if any - to the given JDBC Statement object.
 * @param stmt the JDBC Statement object
 * @param dataSource the DataSource that the Connection was obtained from
 * @param timeout the timeout to apply (or 0 for no timeout outside of a transaction)
 * @throws SQLException if thrown by JDBC methods
 * @see java.sql.Statement#setQueryTimeout
 *//*from   w ww  .j a va  2 s  .  c o  m*/
public static void applyTimeout(Statement stmt, DataSource dataSource, int timeout) throws SQLException {
    Assert.notNull(stmt, "No Statement specified");
    Assert.notNull(dataSource, "No DataSource specified");
    ConnectionHolder holder = (ConnectionHolder) TransactionSynchronizationManager.getResource(dataSource);
    if (holder != null && holder.hasTimeout()) {
        // Remaining transaction timeout overrides specified value.
        stmt.setQueryTimeout(holder.getTimeToLiveInSeconds());
    } else if (timeout > 0) {
        // No current transaction timeout -> apply specified value.
        stmt.setQueryTimeout(timeout);
    }
}

From source file:com.toxind.benchmark.thrid.ibatis.sqlmap.engine.execution.SqlExecutor.java

private static void setStatementTimeout(MappedStatement mappedStatement, Statement statement)
        throws SQLException {
    if (mappedStatement.getTimeout() != null) {
        statement.setQueryTimeout(mappedStatement.getTimeout().intValue());
    }/*from   w  w w.jav  a 2  s. c  o  m*/
}

From source file:org.springframework.jdbc.datasource.DataSourceUtils.java

/**
 * Apply the specified timeout - overridden by the current transaction timeout,
 * if any - to the given JDBC Statement object.
 * @param stmt the JDBC Statement object
 * @param dataSource the DataSource that the Connection was obtained from
 * @param timeout the timeout to apply (or 0 for no timeout outside of a transaction)
 * @throws SQLException if thrown by JDBC methods
 * @see java.sql.Statement#setQueryTimeout
 *///  www .  ja  v  a 2  s  .co m
public static void applyTimeout(Statement stmt, @Nullable DataSource dataSource, int timeout)
        throws SQLException {
    Assert.notNull(stmt, "No Statement specified");
    ConnectionHolder holder = null;
    if (dataSource != null) {
        holder = (ConnectionHolder) TransactionSynchronizationManager.getResource(dataSource);
    }
    if (holder != null && holder.hasTimeout()) {
        // Remaining transaction timeout overrides specified value.
        stmt.setQueryTimeout(holder.getTimeToLiveInSeconds());
    } else if (timeout >= 0) {
        // No current transaction timeout -> apply specified value.
        stmt.setQueryTimeout(timeout);
    }
}