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:net.mybox.mybox.ServerClientConnection.java

private void sendServerFileList() {

    System.out.println("getting local file list for: " + localDir);

    Statement statement = null;

    // refresh the database
    try {//from  w ww .  j ava 2  s  .  c o  m
        statement = connection.createStatement();
        statement.setQueryTimeout(30);

        statement.executeUpdate(
                "create table if not exists archive (name text primary key, type char(1), lastupdate integer)");
    } catch (Exception e) {
        System.out.println("SQLite error: " + e.getMessage());
    }

    try {
        List<MyFile> files = Common.getFileListing(new File(localDir));

        JSONArray jsonArray = new JSONArray();

        PreparedStatement prep = connection.prepareStatement("insert or ignore into archive values(?,?,?);");

        for (MyFile thisFile : files) {
            System.out.println(" " + thisFile.name);

            // TODO: do not insert directories that are already in the DB
            //  compare against getFilesFromDB ?

            prep.setString(1, thisFile.name);
            prep.setString(2, thisFile.getTypeChar() + "");
            prep.setLong(3, thisFile.modtime);
            prep.addBatch();

            jsonArray.add(thisFile.serialize());
        }

        prep.executeBatch();
        connection.commit();

        sendCommandToClient(Common.Signal.requestServerFileList_response);
        ByteStream.toStream(outStream, jsonArray.toJSONString());

        System.out.println("local file list: " + jsonArray.size() + " files");

    } catch (Exception e) {
        System.out.println("Error when getting local file list " + e.getMessage());
    }

}

From source file:org.jesterj.ingest.scanners.JdbcScanner.java

private Statement createStatement(Connection conn) throws SQLException {
    Statement statement = conn.createStatement(java.sql.ResultSet.TYPE_FORWARD_ONLY,
            java.sql.ResultSet.CONCUR_READ_ONLY);
    if (fetchSize != -1) {
        statement.setFetchSize(fetchSize);
    }/*from w ww .  j  av a  2 s.  co  m*/
    if (queryTimeout > 0) {
        statement.setQueryTimeout(queryTimeout);
    }
    return statement;
}

From source file:org.geoserver.wfs.response.SpatiaLiteOutputFormatDev.java

protected void write(List<SimpleFeatureCollection> collections, Charset charset, OutputStream output,
        GetFeatureType request) throws IOException, ServiceException {

    Connection conn = null;//from  w  ww. ja v  a2  s.c  o m

    /**
     * Get the necessary JDBC object.
     */
    try {
        Class.forName(this.driverClassName);
    } catch (ClassNotFoundException e) {
        System.out.println(e);
    }
    /**
     * base location to temporally store spatialite database files
     */
    File tempDir = File.createTempFile("spatialitemp", ".sqlite");

    /**
     * enables load extension
     */
    SQLiteConfig config = new SQLiteConfig();
    config.enableLoadExtension(true);

    /**
     * the Url for the temporally sqlite file
     */
    String JDBCFileUrl = tempDir.getAbsolutePath();

    try {
        //create a connection to database
        conn = DriverManager.getConnection("jdbc:sqlite:" + JDBCFileUrl, config.toProperties());
        Statement stmt = conn.createStatement();
        stmt.setQueryTimeout(30);

        /**
         * A string to store the statements to run to create the Spatialite DataBase 
         */
        String sql = null;
        conn.setAutoCommit(false);
        // FIXME: we are now working on PATH - we will define different JAR files to change this as required by platform
        sql = "SELECT load_extension('/tmp/libspatialite-2-4.dll');";
        stmt.execute(sql);
        sql = "SELECT InitSpatialMetaData();";
        stmt.execute(sql);
        conn.commit();

        /**
         * A string to store the names of the columns that will be used to populate the table 
         */
        String column_names = null;

        //We might get multiple feature collections in our response (multiple queries?) so we need to
        //write out multiple tables, one for each query response.
        for (SimpleFeatureCollection fc : collections) {

            //get the current feature
            SimpleFeatureType ft = fc.getSchema();

            //To check if the current feature has a geometry.
            String the_geom = null;
            if (ft.getGeometryDescriptor() != null) {
                the_geom = ft.getGeometryDescriptor().getLocalName();
            }

            //Get the table name for the current feature
            String tbl_name = ft.getName().getLocalPart();

            /**
             * Create the table for the current feature as follows:
             * - first get the statement for create the table
             * - execute the statement
             * - second get the statement for add the geometry (if has one) 
             * - execute the statement
             */

            //Initialize the "create table" query.
            column_names = "";
            int column_cnt = 0;
            sql = "CREATE TABLE " + tbl_name;
            sql += " ( PK_UID INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT";

            //Get the columns names for the table tbl_name
            for (int i = 0; i < ft.getAttributeCount(); i++) {
                AttributeDescriptor ad = ft.getDescriptor(i);
                if (ad.getLocalName() != the_geom) {
                    sql += ", " + prepareColumnHeader(ad);
                    column_names += ad.getLocalName();
                    column_cnt++;
                    if (i < ft.getAttributeCount() - 1) {
                        column_names += ", ";
                    }

                }
            }
            sql += ");";
            // Finish creating the table

            System.out.println(sql);
            stmt.execute(sql);
            conn.commit();

            int srid = 0;
            //If the table : "tbl_name" has a geometry, then i write the sql to add the geometry
            if (the_geom != null) {
                sql = "SELECT AddGeometryColumn('" + tbl_name + "', ";
                //get the geometry type
                sql += "'" + the_geom + "', ";
                //get the SRID.
                srid = getSpatialSRID(ft.getCoordinateReferenceSystem());
                sql += srid + ", ";
                //get the Geometry type.
                String geom_type = getSpatialGeometryType(ft);
                if (geom_type == null) {
                    throw new WFSException("Error while adding the geometry column in table " + tbl_name
                            + ", unrecognized geometry type");
                }
                sql += "'" + geom_type + "', ";
                //get Dimensions, we only works whit 2 dimensions. 
                String dimension = "XY";
                sql += "'" + dimension + "'";
                sql += " );";
            }
            //finish creating the geometry column.
            System.out.println(sql);
            stmt.execute(sql);
            conn.commit();

            /**
             * Populates the table for the current feature as follows:
             * For each row
             *      - first: configure the statement with the appropriates fields.
             *      - second: add to the statement the field the_geom if has a geometry.
             *      - third: configure the statement with the appropriates values.
             *      (if has a geometry i add that value) 
             *      - execute the statement
             * Finally commit.
             */
            //Start populating the table: tbl_name.
            SimpleFeatureIterator i = fc.features();
            try {
                while (i.hasNext()) {
                    SimpleFeature row = i.next();
                    sql = "INSERT INTO " + tbl_name + " (" + column_names;
                    //if has a geometry, i add the field the_geom.
                    if (the_geom != null)
                        if (column_cnt > 0) {
                            sql += ", " + the_geom + " ) ";
                        } else {
                            sql += the_geom + ") ";
                        }

                    else {
                        sql += ") ";
                    }

                    //I store the default geometry value, so i can omit it and add at the end.
                    Object geom_data = row.getDefaultGeometry();
                    sql += "VALUES (";
                    for (int j = 0; j < row.getAttributeCount(); j++) {
                        Object rowAtt = row.getAttribute(j);
                        if (!rowAtt.equals(geom_data)) {
                            if (rowAtt != null) {
                                //We just transform all content to String.
                                sql += "'" + rowAtt.toString() + "'";
                            }
                            if (j < row.getAttributeCount() - 1) {
                                sql += ", ";
                            }
                        }
                    }

                    //Finally if has geometry, insert the geometry data.
                    if (the_geom != null) {
                        if (column_cnt > 0) {
                            sql += ", ";
                        }
                        sql += "GeomFromText('" + prepareGeom(geom_data.toString()) + "', " + srid + ")";
                    }
                    sql += ");";
                    System.out.println(sql);
                    stmt.executeUpdate(sql);
                }
                conn.commit();
            } finally {
                fc.close(i);
            }
        }
    } catch (SQLException e) {
        System.out.println(e);
    }

    /**
     * A FileInputStream to read the tempDir in a byte array
     * so i can write this in the OutputStream output and flush it.
     */
    FileInputStream JDBCIn = new FileInputStream(tempDir);
    int longitud = JDBCIn.available();
    byte[] datos = new byte[longitud];
    JDBCIn.read(datos);
    output.write(datos);

    System.out.println(tempDir.getAbsolutePath());

}

From source file:velo.adapters.JdbcAdapter.java

public Statement factoryStatement() throws SQLException {
    Statement st = getJdbcConnection().createStatement();
    st.setQueryTimeout(getQueryTimeout());

    return st;/*from  w  ww .jav  a 2  s.  c  om*/
}

From source file:com.u2apple.rt.db.dao.DeviceDao.java

public AndroidDevice getLatestDevice() {
    AndroidDevice device = null;//  w w  w .j a  v a 2s .c  o m
    Connection connection = null;
    Statement statement = null;
    ResultSet rs = null;
    try {
        connection = Pool.getTestStatConnection();
        statement = connection.createStatement();
        statement.setQueryTimeout(Constants.TIMEOUT_LONG);
        String sql = SqlUtils.createMonthlyQuery(SQL, "log_device_init");
        rs = statement.executeQuery(sql);

        if (rs.next()) {
            device = new AndroidDevice();
            String vid = rs.getString("vid");
            String roProductModel = rs.getString("ro_product_model");
            String brand = rs.getString("ro_product_brand");
            String productId = rs.getString("product_id");
            device.setVid(vid);
            device.setRoProductBrand(brand);
            device.setRoProductModel(roProductModel);
            device.setProductId(productId);

            //Get resolution and partitios.
            if (StringUtils.isNotBlank(roProductModel)) {
                String sqlFull = SqlUtils.createMonthlyQuery(SQL_FULL, "log_device_init");
                PreparedStatement preparedStatement = connection.prepareStatement(SQL_FULL);
                preparedStatement.setString(1, roProductModel);
                rs = preparedStatement.executeQuery();
                if (rs.next()) {
                    String resolution = rs.getString("resolution");
                    String partitions = rs.getString("partitions");
                    device.setResolution(resolution);
                    device.setPartitions(partitions);
                }
            }
        }
    } catch (SQLException | ClassNotFoundException | PropertyVetoException | IOException ex) {
        logger.error("SQL fail", ex);
    } finally {
        try {
            if (rs != null) {
                rs.close();
            }
            if (statement != null) {
                statement.close();
            }
            if (connection != null) {
                connection.close();
            }
        } catch (SQLException ex) {
            logger.error("Fail when conection was closed", ex);
        }
    }
    return device;
}

From source file:org.jumpmind.db.sql.JdbcSqlTransaction.java

public <T> T queryForObject(final String sql, final Class<T> clazz, final Object... args) {
    return executeCallback(new IConnectionCallback<T>() {
        public T execute(Connection con) throws SQLException {
            T result = null;/*from  ww w . j  a v a 2  s  .  c om*/
            Statement stmt = null;
            ResultSet rs = null;
            try {
                logSql(sql, args);
                if (args != null && args.length > 0) {
                    PreparedStatement ps = con.prepareStatement(sql);
                    stmt = ps;
                    stmt.setQueryTimeout(jdbcSqlTemplate.getSettings().getQueryTimeout());
                    jdbcSqlTemplate.setValues(ps, args);
                    rs = ps.executeQuery();
                } else {
                    stmt = con.createStatement();
                    stmt.setQueryTimeout(jdbcSqlTemplate.getSettings().getQueryTimeout());
                    rs = stmt.executeQuery(sql);
                }
                if (rs.next()) {
                    result = jdbcSqlTemplate.getObjectFromResultSet(rs, clazz);
                }
            } finally {
                JdbcSqlTemplate.close(rs);
                JdbcSqlTemplate.close(stmt);
            }
            return result;
        }
    });
}

From source file:BQJDBC.QueryResultTest.BQResultSetFunctionTest.java

public void QueryLoad() {
    final String sql = "SELECT TOP(word,10) AS word, COUNT(*) as count FROM publicdata:samples.shakespeare";
    final String description = "The top 10 word from shakespeare #TOP #COUNT";
    String[][] expectation = new String[][] {
            { "you", "yet", "would", "world", "without", "with", "your", "young", "words", "word" },
            { "42", "42", "42", "42", "42", "42", "41", "41", "41", "41" } };
    /** somehow the result changed with time
    { "you", "yet", "would", "world", "without", "with", "will",
            "why", "whose", "whom" },/*from w  ww .j  a v  a 2s.co  m*/
    { "42", "42", "42", "42", "42", "42", "42", "42", "42", "42" } };
     */
    this.logger.info("Test number: 01");
    this.logger.info("Running query:" + sql);

    try {
        Statement stmt = BQResultSetFunctionTest.con.createStatement();
        stmt.setQueryTimeout(500);
        BQResultSetFunctionTest.Result = stmt.executeQuery(sql);
    } catch (SQLException e) {
        this.logger.error("SQLexception" + e.toString());
        Assert.fail("SQLException" + e.toString());
    }
    Assert.assertNotNull(BQResultSetFunctionTest.Result);

    this.logger.debug(description);
    this.printer(expectation);

    try {
        Assert.assertTrue("Comparing failed in the String[][] array",
                this.comparer(expectation, BQSupportMethods.GetQueryResult(BQResultSetFunctionTest.Result)));
    } catch (SQLException e) {
        this.logger.error("SQLexception" + e.toString());
        Assert.fail(e.toString());
    }
}

From source file:org.locationtech.udig.tools.jgrass.geopaparazzi.ImportGeopaparazziFolderWizard.java

private void notesToShapefile(Connection connection, File outputFolderFile, IProgressMonitor pm)
        throws Exception {
    File outputShapeFile = new File(outputFolderFile, GEOPAPARAZZI_NOTES_OUTPUTSHAPEFILENAME);

    SimpleFeatureTypeBuilder b = new SimpleFeatureTypeBuilder();
    b.setName("geopaparazzinotes"); //$NON-NLS-1$
    b.setCRS(mapCrs);//from w w w .  ja va  2s .co m
    b.add("the_geom", Point.class); //$NON-NLS-1$
    for (String fieldName : GEOPAPARAZZI_NOTES_DESCRIPTIONFIELDS) {
        b.add(fieldName, String.class);
    }
    SimpleFeatureType featureType = b.buildFeatureType();
    MathTransform transform = CRS.findMathTransform(DefaultGeographicCRS.WGS84, mapCrs);
    pm.beginTask("Import notes...", IProgressMonitor.UNKNOWN);
    DefaultFeatureCollection newCollection = new DefaultFeatureCollection();

    Statement statement = null;
    try {
        statement = connection.createStatement();
        statement.setQueryTimeout(30); // set timeout to 30 sec.

        ResultSet rs = statement.executeQuery("select lat, lon, altim, ts, text from notes");
        int i = 0;
        while (rs.next()) {

            double lat = rs.getDouble("lat");
            double lon = rs.getDouble("lon");
            double altim = rs.getDouble("altim");
            String dateTimeString = rs.getString("ts");
            String text = rs.getString("text");

            if (lat == 0 || lon == 0) {
                continue;
            }

            // and then create the features
            Coordinate c = new Coordinate(lon, lat);
            Point point = gF.createPoint(c);
            Geometry reprojectPoint = JTS.transform(point, transform);

            SimpleFeatureBuilder builder = new SimpleFeatureBuilder(featureType);
            Object[] values = new Object[] { reprojectPoint, text, dateTimeString, String.valueOf(altim) };
            builder.addAll(values);
            SimpleFeature feature = builder.buildFeature(featureType.getTypeName() + "." + i++);
            newCollection.add(feature);
            pm.worked(1);
        }
    } finally {
        pm.done();
        if (statement != null)
            statement.close();
    }

    ShapefileDataStoreFactory factory = new ShapefileDataStoreFactory();
    Map<String, Serializable> params = new HashMap<String, Serializable>();
    params.put("url", outputShapeFile.toURI().toURL());
    params.put("create spatial index", Boolean.TRUE);
    ShapefileDataStore dStore = (ShapefileDataStore) factory.createNewDataStore(params);
    dStore.createSchema(featureType);
    dStore.forceSchemaCRS(mapCrs);

    JGrassToolsPlugin.getDefault().writeToShapefile(dStore, newCollection);

    JGrassToolsPlugin.getDefault().addServiceToCatalogAndMap(outputShapeFile.getAbsolutePath(), true, true,
            new NullProgressMonitor());

}

From source file:eu.udig.tools.jgrass.geopaparazzi.ImportGeopaparazziFolderWizard.java

private void notesToShapefile(Connection connection, File outputFolderFile, IProgressMonitor pm)
        throws Exception {
    File outputShapeFile = new File(outputFolderFile, GEOPAPARAZZI_NOTES_OUTPUTSHAPEFILENAME);

    SimpleFeatureTypeBuilder b = new SimpleFeatureTypeBuilder();
    b.setName("geopaparazzinotes"); //$NON-NLS-1$
    b.setCRS(mapCrs);/*  w  ww  .ja  va 2  s.  c o  m*/
    b.add("the_geom", Point.class); //$NON-NLS-1$
    for (String fieldName : GEOPAPARAZZI_NOTES_DESCRIPTIONFIELDS) {
        b.add(fieldName, String.class);
    }
    SimpleFeatureType featureType = b.buildFeatureType();
    MathTransform transform = CRS.findMathTransform(DefaultGeographicCRS.WGS84, mapCrs);
    pm.beginTask("Import notes...", IProgressMonitor.UNKNOWN);
    FeatureCollection<SimpleFeatureType, SimpleFeature> newCollection = FeatureCollections.newCollection();

    Statement statement = null;
    try {
        statement = connection.createStatement();
        statement.setQueryTimeout(30); // set timeout to 30 sec.

        ResultSet rs = statement.executeQuery("select lat, lon, altim, ts, text from notes");
        int i = 0;
        while (rs.next()) {

            double lat = rs.getDouble("lat");
            double lon = rs.getDouble("lon");
            double altim = rs.getDouble("altim");
            String dateTimeString = rs.getString("ts");
            String text = rs.getString("text");

            if (lat == 0 || lon == 0) {
                continue;
            }

            // and then create the features
            Coordinate c = new Coordinate(lon, lat);
            Point point = gF.createPoint(c);
            Geometry reprojectPoint = JTS.transform(point, transform);

            SimpleFeatureBuilder builder = new SimpleFeatureBuilder(featureType);
            Object[] values = new Object[] { reprojectPoint, text, dateTimeString, String.valueOf(altim) };
            builder.addAll(values);
            SimpleFeature feature = builder.buildFeature(featureType.getTypeName() + "." + i++);
            newCollection.add(feature);
            pm.worked(1);
        }
    } finally {
        pm.done();
        if (statement != null)
            statement.close();
    }

    ShapefileDataStoreFactory factory = new ShapefileDataStoreFactory();
    Map<String, Serializable> params = new HashMap<String, Serializable>();
    params.put("url", outputShapeFile.toURI().toURL());
    params.put("create spatial index", Boolean.TRUE);
    ShapefileDataStore dStore = (ShapefileDataStore) factory.createNewDataStore(params);
    dStore.createSchema(featureType);
    dStore.forceSchemaCRS(mapCrs);

    JGrassToolsPlugin.getDefault().writeToShapefile(dStore, newCollection);

    JGrassToolsPlugin.getDefault().addServiceToCatalogAndMap(outputShapeFile.getAbsolutePath(), true, true,
            new NullProgressMonitor());

}

From source file:BQJDBC.QueryResultTest.BQScrollableResultSetFunctionTest.java

public void QueryLoad() {
    final String sql = "SELECT TOP(word,10) AS word, COUNT(*) as count FROM publicdata:samples.shakespeare";
    final String description = "The top 10 word from shakespeare #TOP #COUNT";
    String[][] expectation = new String[][] {
            { "you", "yet", "would", "world", "without", "with", "your", "young", "words", "word" },
            { "42", "42", "42", "42", "42", "42", "41", "41", "41", "41" } };
    /** somehow the result changed with time
    { "you", "yet", "would", "world", "without", "with", "will",
            "why", "whose", "whom" },// w ww . j  a va  2  s .  com
    { "42", "42", "42", "42", "42", "42", "42", "42", "42", "42" } };
     */
    this.logger.info("Test number: 01");
    this.logger.info("Running query:" + sql);

    try {
        //Statement stmt = BQResultSetFunctionTest.con.createStatement();
        Statement stmt = BQScrollableResultSetFunctionTest.con
                .createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
        stmt.setQueryTimeout(500);
        BQScrollableResultSetFunctionTest.Result = stmt.executeQuery(sql);
    } catch (SQLException e) {
        this.logger.error("SQLexception" + e.toString());
        Assert.fail("SQLException" + e.toString());
    }
    Assert.assertNotNull(BQScrollableResultSetFunctionTest.Result);

    this.logger.debug(description);
    HelperFunctions.printer(expectation);

    try {
        Assert.assertTrue("Comparing failed in the String[][] array", this.comparer(expectation,
                BQSupportMethods.GetQueryResult(BQScrollableResultSetFunctionTest.Result)));
    } catch (SQLException e) {
        this.logger.error("SQLexception" + e.toString());
        Assert.fail(e.toString());
    }
}