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:org.ut.biolab.medsavant.server.db.variants.VariantManagerUtils.java

public static int uploadTSVFileToVariantTable(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 = null;//from   ww w .j  a  v a2  s.c  om
    int lineNumber = 0;

    try {
        c = ConnectionController.connectPooled(sid);

        c.setAutoCommit(false);

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

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

        BufferedWriter bw = null;
        //BufferedWriter sw = null;
        String subsetFileName = parentDirectory + "/" + MiscUtils.extractFileName(file.getAbsolutePath())
                + "_subset";
        /*if (step > 0) { //assert
         sw = new BufferedWriter(new FileWriter(subsetFileName));
         } else {
         throw new IllegalArgumentException("Can't upload TSV file " + file + " to variant table, invalid step size=" + step);
         }*/

        String currentOutputPath = null;

        boolean stateOpen = false;

        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;
            }

            /*if (sw != null && ((lineNumber - 1) % step == 0)) {
             sw.write(line + "\r\n");
             }*/
            // 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 final 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 = null;
                try {
                    s = c.createStatement();
                    s.setQueryTimeout(30 * 60); // 30 minutes
                    s.execute(query);
                } finally {
                    s.close();
                }

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

        // 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 = null;
            try {
                s = c.createStatement();
                s.setQueryTimeout(60 * 60); // 1 hour
                s.execute(query);
            } finally {
                s.close();
            }

            (new File(currentOutputPath)).delete();
            /*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");
        /*
         if (sw != null) {
         sw.close();
                
         String query = "LOAD DATA LOCAL INFILE '" + subsetFileName.replaceAll("\\\\", "/") + "' "
         + "INTO TABLE " + subTableName + " "
         + "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 subset file " + subsetFileName);
         LOG.info(query);
         Statement s = c.createStatement();
         s.setQueryTimeout(60 * 60); // 1 hour
         s.execute(query);
         LOG.info("Subset table import done");
         (new File(subsetFileName)).delete();
         }
         */
        c.commit();
        c.setAutoCommit(true);
    } finally {
        c.close();
    }

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

From source file:hu.bme.mit.trainbenchmark.benchmark.sqlite.driver.SQLiteDriver.java

@Override
public void read(final String modelPath) throws IOException, InterruptedException, SQLException {
    final File modelFile = new File(modelPath);
    if (!modelFile.exists()) {
        throw new IOException("Model does not exist: " + modelPath);
    }/*  w w  w. j av a 2s.  co  m*/

    connection = DriverManager.getConnection("jdbc:sqlite::memory:");
    final Statement statement = connection.createStatement();
    statement.setQueryTimeout(3600);

    final String sql = FileUtils.readFileToString(new File(modelPath));
    statement.executeUpdate(sql);

    // create temporary table (used by the transformations)
    final PreparedStatement createStatement = connection
            .prepareStatement("CREATE TEMP TABLE IF NOT EXISTS Variables (Name TEXT PRIMARY KEY, Value LONG);");
    createStatement.execute();
}

From source file:com.thinkbiganalytics.ingest.GetTableDataSupport.java

/**
 * Performs a full extract of the data for the specified table
 *///from  w w w  . j  av  a2s.  co m
public ResultSet selectFullLoad(String tableName, String[] selectFields) throws SQLException {
    final Statement st = conn.createStatement();
    st.setQueryTimeout(timeout);
    String query = getSelectQuery(tableName, selectFields);

    logger.info("Executing full GetTableData query {}", query);
    st.setQueryTimeout(timeout);

    return st.executeQuery(query);
}

From source file:edu.uci.mhlee.BasicCrawler.java

public boolean isAlreadyVisited(WebURL url) {
    boolean isAlreadyVisited = false;
    try {//from   w w w.j  av a 2 s  . c o  m
        // create a database connection

        Statement statement = connection.createStatement();
        statement.setQueryTimeout(30); // set timeout to 30 sec.
        String urlString = url.getURL().toLowerCase();
        //url.get

        //System.out.println("select count(*) as cnt from webContents where url = '"+urlString+"'");
        ResultSet rs = statement
                .executeQuery("select count(*) as cnt from webContents where url = '" + urlString + "'");
        while (rs.next()) {
            // read the result set
            //System.out.println("cnt = " + rs.getString("name"));
            //System.out.println("id = " + rs.getInt("id"));
            if (Integer.parseInt(rs.getString("cnt")) > 0) {
                isAlreadyVisited = true;
            }
        }
        //System.out.println("Is already visited?"+isAlreadyVisited);
    } catch (SQLException e) {
        // if the error message is "out of memory", 
        // it probably means no database file is found
        System.err.println(e.getMessage());
    } finally {
        //         try
        //         {
        //            if(connection != null)
        //               connection.close();
        //         }
        //         catch(SQLException e)
        //         {
        //            // connection close failed.
        //            System.err.println(e);
        //         }
    }

    return isAlreadyVisited;
}

From source file:edu.uci.mhlee.BasicCrawler.java

public int write2DB(WebContent content) {
    logger.info("docid:" + content.getDocid() + " added.");

    String insertSQL = null;//from w w  w. j a v a 2  s  .co  m
    try {
        Statement statement = connection.createStatement();
        statement.setQueryTimeout(30); // set timeout to 30 sec.

        insertSQL = "insert into webContents values(" + "null," + //auto increment
                content.getDocid() + "," + "'" + content.getUrl() + "'," + "'" + content.getSubDomain() + "',"
                + "'" + content.getPath() + "'," + "'" + content.getParentUrl() + "'," + "'"
                + content.getAnchor() + "'," + "" + content.getTextLength() + "," + "" + content.getHtmlLength()
                + "," + "" + content.getWordcount() + "," + "'" + content.getText() + "'," + "'"
                + content.getHtml() + "'," + "" + content.getOutgoingLink() + "," + "'" + content.getTitle()
                + "'" + ")";

        statement.executeUpdate(insertSQL);

        for (WebURL url : content.getLinks()) {
            insertSQL = "insert into webLinks values(" + "null," + content.getDocid() + "," + "'" + url.getURL()
                    + "'" + ")";
            statement.executeUpdate(insertSQL);
        }

    } catch (SQLException e) {
        // if the error message is "out of memory", 
        // it probably means no database file is found
        System.err.println(e.getMessage());
        System.err.println("========================");
        System.err.println(insertSQL);
        System.err.println("========================");
    } finally {
        //         try
        //         {
        //            if(connection != null)
        //               connection.close();
        //         }
        //         catch(SQLException e)
        //         {
        //            // connection close failed.
        //            System.err.println(e);
        //         }
    }

    return 0;
}

From source file:com.taobao.diamond.server.service.DefaultPersistService.java

public void initDataSource(DataSource dataSource) {
    System.out.println("#initDataSource");
    if (dataSource == null) {
        System.out.println("dataSource is null.");
    }/*from ww w. ja  v  a2 s . c o m*/
    String mode = System.getProperty("diamond.server.mode");
    System.out.println("-Ddiamond.server.mode:" + mode);
    String[] attrNames = new String[] { "offline", "flying", "lostDB", "xx" };
    for (String key : attrNames) {
        if (key.equals(mode)) {
            SystemConfig.setOffline();
        }
    }
    int timeout = 3;// seconds
    boolean dsValid = false;
    Connection conn = null;
    if (dataSource != null)
        try {
            BasicDataSource bds = (BasicDataSource) dataSource;
            bds.isPoolPreparedStatements();
            conn = dataSource.getConnection();

            Statement stmt = conn.createStatement();
            stmt.setQueryTimeout(timeout);
            dsValid = true;
            try {
                ResultSet rs = stmt.executeQuery("SELECT COUNT(*) FROM config_info");
                if (rs.next()) {
                    rs.getInt(1);
                } else {
                    dsValid = false;
                }
                rs = stmt.executeQuery("select count(*) from group_info");
                if (rs.next()) {
                    rs.getInt(1);
                } else {
                    dsValid = false;
                }
            } catch (Exception e) {
                dsValid = false;
            }

        } catch (Throwable t) {
            log.error(t.getMessage(), t.getCause());
        }

    if (dsValid == false) {
        // 
        if (SystemConfig.isOnlineMode()) {
            System.out.println("#########################################################");
            System.out.println("DataSource .");
            System.out.println("error occured in DataSource initilizing,connection timeout or refuse conn.");
            System.out.println("#########################################################");
            SystemConfig.system_pause();
            System.exit(0);
        }
        // 
        if (SystemConfig.isOfflineMode()) {
            String msg = "#########################################################";
            System.out.println(msg);
            log.info(msg);
            OfflinePersistService ps = new OfflinePersistService();
            persistService = ps;

        }
    } else {
        DBPersistService ps = new DBPersistService();
        ps.setDataSource(dataSource);
        persistService = ps;
    }
    System.out.println("#########################################################");
    System.out.println("Current Persist Service");
    System.out.println("persistService:" + persistService);
    System.out.println("DBPersistService:" + (persistService instanceof DBPersistService));
    System.out.println("OfflinePersistService:" + (persistService instanceof OfflinePersistService));
    System.out.println("#########################################################");
}

From source file:BQJDBC.QueryResultTest.BQForwardOnlyResultSetFunctionTest.java

public void QueryLoad() {
    final String sql = "SELECT TOP(word,10) AS word, COUNT(*) as count FROM publicdata:samples.shakespeare";
    this.logger.info("Test number: 01");
    this.logger.info("Running query:" + sql);

    try {//ww w.ja  va  2s.  c  o  m
        //Statement stmt = BQResultSetFunctionTest.con.createStatement();
        Statement stmt = BQForwardOnlyResultSetFunctionTest.con.createStatement(ResultSet.TYPE_FORWARD_ONLY,
                ResultSet.CONCUR_READ_ONLY);
        stmt.setQueryTimeout(500);
        BQForwardOnlyResultSetFunctionTest.Result = stmt.executeQuery(sql);
    } catch (SQLException e) {
        this.logger.error("SQLexception" + e.toString());
        Assert.fail("SQLException" + e.toString());
    }
    Assert.assertNotNull(BQForwardOnlyResultSetFunctionTest.Result);
}

From source file:com.extrahardmode.module.MsgPersistModule.java

/** Creates tables if they do not exist. */
private void initializeTables() {
    Connection conn = null;/*  w  w w  .  java 2s .com*/
    Statement statement = null;
    try {
        conn = retrieveConnection();
        statement = conn.createStatement();
        statement.setQueryTimeout(30);

        //One table holding the playername id relation
        String playerQuery = String.format(
                "CREATE TABLE IF NOT EXISTS %s (id INTEGER PRIMARY KEY AUTOINCREMENT, %s STRING)", playerTable,
                "name");
        statement.executeUpdate(playerQuery);

        //One column for every message
        StringBuilder columns = new StringBuilder();
        for (MessageNode node : MessageNode.getMessageNodes()) {
            MsgCategory cat = messages.getCat(node);
            if (node.getColumnName() != null && (cat == MsgCategory.TUTORIAL || cat == MsgCategory.ONE_TIME)) {
                columns.append(',');
                columns.append(node.getColumnName());
            }
        }

        String msgQuery = String.format("CREATE TABLE IF NOT EXISTS %s (id INTEGER PRIMARY KEY UNIQUE %s)",
                msgTable, columns);
        statement.executeUpdate(msgQuery);

        //Check if all columns are present
        DatabaseMetaData dmd = conn.getMetaData();
        //Add missing columns
        for (MessageNode node : MessageNode.getMessageNodes()) {
            MsgCategory cat = messages.getCat(node);
            if (cat == MsgCategory.TUTORIAL || cat == MsgCategory.ONE_TIME) {
                ResultSet set = dmd.getColumns(null, null, msgTable, node.getColumnName());
                if (!set.next()) {
                    String updateQuery = String.format("ALTER TABLE %s ADD COLUMN %s", msgTable,
                            node.getColumnName());
                    statement.executeUpdate(updateQuery);
                }
            }
        }
    } catch (SQLException e) {
        e.printStackTrace();
    } finally {
        try {
            if (conn != null)
                conn.close();
            if (statement != null)
                statement.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

From source file:BQJDBC.QueryResultTest.QueryResultTest.java

@Test
public void QueryResultTest10() {
    final String sql = "SELECT corpus_date,SUM(word_count) FROM publicdata:samples.shakespeare GROUP BY corpus_date ORDER BY corpus_date DESC LIMIT 5;";
    // final String description =
    // "A query which gets how many words Shapespeare wrote in a year (5 years displayed descending)";
    /*//from w ww. ja va 2  s  .com
     * String[][] expectation = new String[][] {
     * {"1612","1611","1610","1609","1608"},
     * {"26265","17593","26181","57073","19846"} };
     */

    this.logger.info("Test number: 10");
    this.logger.info("Running query:" + sql);

    try {
        Statement stmt = QueryResultTest.con.createStatement();
        stmt.setQueryTimeout(1);
        stmt.executeQuery(sql);
    } catch (SQLException e) {
        this.logger.info("SQLexception" + e.toString());
        Assert.assertTrue(true);
    }

}

From source file:net.mybox.mybox.ServerClientConnection.java

HashMap<String, MyFile> getFilesFromDB() {

    HashMap<String, MyFile> files = new HashMap<String, MyFile>();

    try {/*from www  . j a  va  2 s  .  co m*/
        Statement statement = connection.createStatement();
        statement.setQueryTimeout(30);
        ResultSet rs = statement.executeQuery("select * from archive");

        while (rs.next()) {
            MyFile myFile = new MyFile(rs.getString("name"));
            myFile.modtime = rs.getLong("lastupdate");
            myFile.setType(rs.getString("type").charAt(0));

            files.put(myFile.name, myFile);
        }
    } catch (SQLException ex) {
        Logger.getLogger(ServerClientConnection.class.getName()).log(Level.SEVERE, null, ex);
    }

    return files;
}