/**
  * 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
  */
 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);
   }
 }
Example #2
0
 public void setStatement() throws Exception {
   if (conn == null) {
     setConnection();
   }
   statement = conn.createStatement();
   statement.setQueryTimeout(iTimeout); // set timeout to 30 sec.
 }
  private ResultSet loadResultSet(boolean useCurrentFilter) throws SQLException {
    Connection connection = connectionHandler.getStandaloneConnection();
    DBDataset dataset = getDataset();
    if (dataset != null) {
      Project project = dataset.getProject();
      DatasetFilter filter = DatasetFilterManager.EMPTY_FILTER;
      if (useCurrentFilter) {
        DatasetFilterManager filterManager = DatasetFilterManager.getInstance(project);
        filter = filterManager.getActiveFilter(dataset);
        if (filter == null) filter = DatasetFilterManager.EMPTY_FILTER;
      }

      String selectStatement = filter.createSelectStatement(dataset, getState().getSortingState());
      Statement statement =
          isReadonly()
              ? connection.createStatement()
              : connection.createStatement(
                  ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
      checkDisposed();
      int timeout = settings.getGeneralSettings().getFetchTimeout().value();
      if (timeout != -1) {
        statement.setQueryTimeout(timeout);
      }
      return statement.executeQuery(selectStatement);
    }
    return null;
  }
Example #4
0
  public boolean isValidConnection(DruidDataSource dataSource, Connection conn) {
    Statement stmt = null;
    ResultSet rs = null;
    try {
      stmt = conn.createStatement();
      stmt.setQueryTimeout(queryTimeoutSeconds);
      rs = stmt.executeQuery(getValidateSql());
      if (!rs.next()) {
        return false;
      }

      String status = rs.getString(1);

      if ("on".equalsIgnoreCase(status)) {
        return true;
      } else {
        return false;
      }
    } catch (Exception ex) {
      LOG.error("check datasource valid errror", ex);
      return false;
    } finally {
      JdbcUtils.close(rs);
      JdbcUtils.close(stmt);
    }
  }
  @SuppressWarnings({"unchecked"})
  public static void close(Statement statement) {
    log.tracef("Closing prepared statement [%s]", statement);

    try {
      // if we are unable to "clean" the prepared statement,
      // we do not close it
      try {
        if (statement.getMaxRows() != 0) {
          statement.setMaxRows(0);
        }
        if (statement.getQueryTimeout() != 0) {
          statement.setQueryTimeout(0);
        }
      } catch (SQLException sqle) {
        // there was a problem "cleaning" the prepared statement
        if (log.isDebugEnabled()) {
          log.debugf("Exception clearing maxRows/queryTimeout [%s]", sqle.getMessage());
        }
        // EARLY EXIT!!!
        return;
      }
      statement.close();
    } catch (SQLException e) {
      log.debugf("Unable to release JDBC statement [%s]", e.getMessage());
    } catch (Exception e) {
      // try to handle general errors more elegantly
      log.debugf("Unable to release JDBC statement [%s]", e.getMessage());
    }
  }
  /** Creates tables if they do not exist. */
  private void initializeTables() {
    Connection conn = null;
    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();
      }
    }
  }
Example #7
0
  public void connect() {
    Statement statement = null;

    databaseType = foxbot.getConfig().getDatabaseType();
    url =
        databaseType.equalsIgnoreCase("mysql")
            ? String.format(
                "jdbc:mysql://%s:%s/%s",
                foxbot.getConfig().getDatabaseHost(),
                foxbot.getConfig().getDatabasePort(),
                foxbot.getConfig().getDatabaseName())
            : "jdbc:sqlite:data/bot.db";

    try {
      if (databaseType.equalsIgnoreCase("sqlite")) {
        Class.forName("org.sqlite.JDBC");
      }

      if (databaseType.equalsIgnoreCase("mysql")) {
        String user = foxbot.getConfig().getDatabaseUser();
        String password = foxbot.getConfig().getDatabasePassword();
        connection = DriverManager.getConnection(url, user, password);
      } else {
        connection = DriverManager.getConnection(url);
      }

      statement = connection.createStatement();

      statement.setQueryTimeout(30);
      statement.executeUpdate(
          "CREATE TABLE IF NOT EXISTS tells (tell_time VARCHAR(32), sender VARCHAR(32), receiver VARCHAR(32), message VARCHAR(1024), used TINYINT)");
      statement.executeUpdate(
          "CREATE TABLE IF NOT EXISTS bans (channel VARCHAR(64), target VARCHAR(32), hostmask VARCHAR(64), reason VARCHAR(1024), banner VARCHAR(32), ban_time BIGINT)");
      statement.executeUpdate(
          "CREATE TABLE IF NOT EXISTS kicks (channel VARCHAR(64), target VARCHAR(32), hostmask VARCHAR(64), reason VARCHAR(1024), kicker VARCHAR(32), kick_time BIGINT)");
      statement.executeUpdate(
          "CREATE TABLE IF NOT EXISTS mutes (channel VARCHAR(64), target VARCHAR(32), hostmask VARCHAR(64), reason VARCHAR(1024), muter VARCHAR(32), mute_time BIGINT)");
    } catch (SQLException | ClassNotFoundException ex) {
      Logger.getLogger(this.getClass().getName()).log(Level.SEVERE, null, ex);
      foxbot.disconnect();
    } finally {
      try {
        if (statement != null) {
          statement.close();
        }
        if (connection != null) {
          connection.close();
          connection = null;
        }
      } catch (SQLException ex) {
        Logger.getLogger(this.getClass().getName()).log(Level.SEVERE, null, ex);
      }
    }
  }
Example #8
0
 private void testJdbcQueryTimeout() throws SQLException {
   deleteDb("cancel");
   Connection conn = getConnection("cancel");
   Statement stat = conn.createStatement();
   assertEquals(0, stat.getQueryTimeout());
   stat.setQueryTimeout(1);
   assertEquals(1, stat.getQueryTimeout());
   Statement s2 = conn.createStatement();
   assertEquals(1, s2.getQueryTimeout());
   ResultSet rs =
       s2.executeQuery(
           "SELECT VALUE " + "FROM INFORMATION_SCHEMA.SETTINGS WHERE NAME = 'QUERY_TIMEOUT'");
   rs.next();
   assertEquals(1000, rs.getInt(1));
   assertThrows(ErrorCode.STATEMENT_WAS_CANCELED, stat)
       .executeQuery("SELECT MAX(RAND()) " + "FROM SYSTEM_RANGE(1, 100000000)");
   stat.setQueryTimeout(0);
   stat.execute("SET QUERY_TIMEOUT 1100");
   // explicit changes are not detected except, as documented
   assertEquals(0, stat.getQueryTimeout());
   conn.close();
 }
Example #9
0
 private void testQueryTimeoutInTransaction() throws SQLException {
   deleteDb("cancel");
   Connection conn = getConnection("cancel");
   Statement stat = conn.createStatement();
   stat.execute("CREATE TABLE TEST(ID INT)");
   conn.setAutoCommit(false);
   stat.execute("INSERT INTO TEST VALUES(1)");
   Savepoint sp = conn.setSavepoint();
   stat.execute("INSERT INTO TEST VALUES(2)");
   stat.setQueryTimeout(1);
   conn.rollback(sp);
   conn.commit();
   conn.close();
 }
Example #10
0
  @Override
  public void dump() {
    try {
      initConnection();

      Statement statement = null;

      statement = conn.createStatement();
      // set timeout to 30 sec
      statement.setQueryTimeout(30);

      statement.execute("DELETE from Persoon");
      statement.execute("DELETE FROM Gezin");
      statement.execute("DELETE FROM sqlite_sequence");

      statement.close();
    } catch (SQLException e) {
      e.printStackTrace();
    } catch (ClassNotFoundException e) {
      e.printStackTrace();
    }
  }
Example #11
0
  private void initConnection() throws SQLException, ClassNotFoundException {
    // TODO opgave 4
    Class.forName(props.getProperty("driver"));

    try {
      // create a database connection
      conn = DriverManager.getConnection(props.getProperty("url"));
      Statement statement = conn.createStatement();
      statement.setQueryTimeout(30); // set timeout to 30 sec.

      statement.executeUpdate(
          "CREATE TABLE IF NOT EXISTS `Persoon`(\n"
              + "\t`persoonsNummer`\tINTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,\n"
              + "\t`voornamen`\tTEXT NOT NULL,\n"
              + "\t`achternaam`\tTEXT NOT NULL,\n"
              + "\t`tussenvoegsel`\tTEXT NOT NULL,\n"
              + "\t`geboortedatum`\tTEXT NOT NULL,\n"
              + "\t`geboorteplaats`\tTEXT NOT NULL,\n"
              + "\t`geslacht`\tTEXT NOT NULL,\n"
              + "\t`ouders`\tTEXT,\n"
              + "\t FOREIGN KEY(ouders)\tREFERENCES gezin(gezinsnummer)\n"
              + ");");
      statement.executeUpdate(
          "CREATE TABLE IF NOT EXISTS  `Gezin` (\n"
              + "\t`gezinsNummer`\tINTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,\n"
              + "\t`ouder1`\tINTEGER NOT NULL,\n"
              + "\t`ouder2`\tINTEGER ,\n"
              + "\t`huwelijksdatum`\t TEXT,\n"
              + "\t`scheidingsdatum`\tTEXT,\n"
              + "\t FOREIGN KEY(ouder1)\tREFERENCES persoon(persoonsnummer)\n,"
              + "\t FOREIGN KEY(ouder2)\tREFERENCES persoon(persoonsnummer)\n"
              + ");");
    } catch (SQLException e) {
      // if the error message is "out of memory",
      // it probably means no database file is found
      System.err.println(e.getMessage());
    }
  }
Example #12
0
 /** ajoute 1 au nombre de fois que la musique a �t� lu */
 public static synchronized void addLecture(Musique musique) {
   Connection connection = initConnection();
   try {
     Statement statement = connection.createStatement();
     statement.setQueryTimeout(30);
     statement.executeUpdate(
         "Update songs set nblecture = nblecture + 1 where title like '"
             + musique.getTitle()
             + "' and album like '"
             + musique.getAlbum()
             + "' and artist like '"
             + musique.getArtist()
             + "'");
     musique.getNbLecture().setNbLecture();
   } catch (SQLException e) {
     System.err.println(e.getMessage());
   } finally {
     try {
       if (connection != null) connection.close();
     } catch (SQLException e) {
       System.err.println(e);
     }
   }
 }
Example #13
0
 public void setQueryTimeout(int seconds) throws SQLException {
   pst.setQueryTimeout(seconds);
 };
  public List<UserHistory> getFirefoxHistory() {
    // TODO Auto-generated method stub
    try {
      Class.forName("org.sqlite.JDBC");
    } catch (ClassNotFoundException e1) {
      // TODO Auto-generated catch block
      e1.printStackTrace();
    }
    List<UserHistory> userHistoryList = new ArrayList<UserHistory>();
    Connection connection = null;
    try {
      String profileName = HackathonUtil.getProfileName(Tracking.FILES_LOCATION);
      System.out.println("Profile name:-" + profileName);
      String accNum = profileName.replace(".default", "");
      System.out.println("Account number:-" + accNum);
      String connectionString =
          "jdbc:sqlite:" + Tracking.FILES_LOCATION + profileName + "/places.sqlite";
      connection = DriverManager.getConnection(connectionString);
      Statement statement = connection.createStatement();
      statement.setQueryTimeout(30); // set timeout to 30 sec.

      ResultSet rs =
          statement.executeQuery(
              "select url as HOST_NAME, rev_host as FULL_URL, visit_date as VISIT_DATE from moz_places , moz_historyvisits  where moz_places.id = moz_historyvisits.id and moz_places.url like '%verizon%'");
      while (rs.next()) {
        // read the result set

        String actualText = "";
        UserHistory userHistory = new UserHistory();
        String dateandtime = rs.getString("VISIT_DATE");
        long dt = Long.parseLong(dateandtime);
        //
        Calendar cal = Calendar.getInstance();
        cal.setTimeInMillis(dt);
        SimpleDateFormat format1 = new SimpleDateFormat("MMM-dd");
        String formatted = format1.format(cal.getTime());
        userHistory.setDateVisitied(formatted);
        String txt = rs.getString("HOST_NAME");
        System.out.println(txt);
        String contents[] = txt.split("=");
        if (contents.length > 2) {
          actualText = contents[2].replace("+", " ");
        } else {
          actualText = txt;
        }
        userHistory.setId(accNum);
        userHistory.setHostname(actualText);
        userHistory.setTextSearched(HackathonUtil.reverseString(rs.getString("FULL_URL")));
        userHistoryList.add(userHistory);
      }
    } catch (SQLException e) {
      // if the error message is "out of memory",
      // it probably means no database file is found
      System.err.println(e.getMessage());
    } catch (Exception ex) {
      ex.printStackTrace();
    } finally {
      try {
        if (connection != null) connection.close();
      } catch (SQLException e) {
        // connection close failed.
        System.err.println(e);
      }
    }
    return userHistoryList;
  }
Example #15
0
 @Override
 public void setQueryTimeout(int seconds) throws SQLException {
   stat.setQueryTimeout(seconds);
 }
 protected ResultSet query(String query) throws SQLException {
   Statement statement = m_connection.createStatement();
   statement.setQueryTimeout(30); // set timeout to 30 sec
   return statement.executeQuery(query);
 }
 protected int update(String update) throws SQLException {
   Statement statementUpdate = m_connection.createStatement();
   statementUpdate.setQueryTimeout(30);
   return statementUpdate.executeUpdate(update);
 }
  /* (non-Javadoc)
   * @see com.newgen.omni.jts.txn.NGOServerInterface#execute(java.sql.Connection, com.newgen.omni.jts.cmgr.XMLParser, com.newgen.omni.jts.cmgr.XMLGenerator)
   * Function Name : execute
   * Date Written : 03/04/2008
   * Author : yogvinder
   * Input Parameters : Connection, XMLPArser, XMLGenerator
   * Output Parameters : None
   * Return Values :
   * Description :
   */
  public String execute(Connection con, XMLParser xmlToParse, XMLGenerator fileProcessor)
      throws JTSException {
    StringBuffer outputXML = null;
    String errorMsg = null;
    String strColumnValue = null;
    String Flag = null;
    int errorCode = 0;
    ResultSet rs = null;

    Statement stmt = null;

    int dbStatus = 0;

    try {
      con.setAutoCommit(false);

      stmt = con.createStatement();

      outputXML = new StringBuffer(100);

      outputXML.append(fileProcessor.createOutputFile("NGOGetNextSynchData"));

      stmt.setQueryTimeout(
          ServerProperty.getReference().getQueryTimeout(xmlToParse.getValueOf("CabinetName")));

      rs =
          stmt.executeQuery(
              "select uid, destination, commanddata, command, transactionid,actiondatetime,requestdatetime,comment,requestcounter,requestType,source,caption,sourceIP,statusFlag from pdbsynchtable where statusflag in ('01', '03', '08', '11', '12', '13', '14', '16', '17', '18', '20' , '21', '23', '24', '25', '26', '27', '28', '29', '30', '34', '36', '38', '39', '40') and requestcounter < 05 order by actiondatetime, requestcounter,requestdatetime asc Limit 1");
      if (rs != null && rs.next()) {
        strColumnValue = rs.getString(1).trim();
        outputXML.append(fileProcessor.writeValueOf("UID", strColumnValue));
        outputXML.append(fileProcessor.writeValueOf("Destination", rs.getString(2)));
        outputXML.append(fileProcessor.writeValueOf("CommandData", rs.getString(3)));
        outputXML.append(fileProcessor.writeValueOf("Command", rs.getString(4)));
        outputXML.append(fileProcessor.writeValueOf("TransactionId", rs.getString(5)));
        outputXML.append(fileProcessor.writeValueOf("ActionDateTime", rs.getString(6)));
        outputXML.append(fileProcessor.writeValueOf("RequestDateTime", rs.getString(7)));
        outputXML.append(fileProcessor.writeValueOf("Comment", rs.getString(8)));
        String strRequestCounter = rs.getString(9);
        outputXML.append(fileProcessor.writeValueOf("RequestCounter", strRequestCounter));
        outputXML.append(fileProcessor.writeValueOf("RequestType", rs.getString(10)));
        outputXML.append(fileProcessor.writeValueOf("Source", rs.getString(11)));
        outputXML.append(fileProcessor.writeValueOf("Caption", rs.getString(12)));
        outputXML.append(fileProcessor.writeValueOf("SourceIP", rs.getString(13)));
        String strStatusFlag = rs.getString(14);
        outputXML.append(fileProcessor.writeValueOf("StatusFlag", strStatusFlag));
        int intRequestCounter = Integer.parseInt(strRequestCounter);
        int x = 0;

        // stmt.setQueryTimeout (ServerProperty.getReference ().getQueryTimeout
        // (xmlToParse.getValueOf ("CabinetName")));
        if (strStatusFlag.equalsIgnoreCase("01")) {
          if (intRequestCounter == 0)
            x =
                stmt.executeUpdate(
                    "Update pdbsynchtable set statusFlag = '02' , requestcounter = requestcounter +  1, actiondatetime = now(), Comment = 'Fetched for Processing' where UID = '"
                        + strColumnValue
                        + "'");
          else
            x =
                stmt.executeUpdate(
                    "Update pdbsynchtable set statusFlag = '02' , requestcounter = requestcounter +  1, actiondatetime = now() where UID = '"
                        + strColumnValue
                        + "'");
        } else {
          if (intRequestCounter == 0)
            x =
                stmt.executeUpdate(
                    "Update pdbsynchtable set requestcounter = requestcounter +  1, actiondatetime = now() where UID = '"
                        + strColumnValue
                        + "'");
          else
            x =
                stmt.executeUpdate(
                    "Update pdbsynchtable set requestcounter = requestcounter +  1, actiondatetime = now() where UID = '"
                        + strColumnValue
                        + "'");
        }
      }

      strColumnValue = null;
      errorMsg = null;
      con.commit();
      rs.close();

      outputXML.append(fileProcessor.writeValueOf("Status", String.valueOf(dbStatus)));

      // Error Handling to be completed.
    } catch (SQLException e) {
      e.printStackTrace();
      errorCode = JTSError.JTSE_SQL_ERR;
      if (e.getErrorCode() == 0)
        errorMsg =
            (new JTSSQLError(e.getSQLState())).getMessage()
                + "(SQL State : "
                + e.getSQLState()
                + ")";
      else errorMsg = e.getMessage();
    } catch (NumberFormatException e) {
      e.printStackTrace();
      errorCode = JTSError.JTSE_ILLEGAL_PARAM;
    } catch (NullPointerException e) {
      e.printStackTrace();
      errorCode = JTSError.JTSE_SYS_ERR;
    } catch (Exception e) {
      e.printStackTrace();
      errorCode = JTSError.JTSE_UNKNOWN_ERR;
      errorMsg = e.toString();
    } catch (Error e) {
      e.printStackTrace();
      errorCode = JTSError.JTSE_UNKNOWN_ERR;
      errorMsg = e.toString();
    } finally {
      try {
        con.rollback();
        if (stmt != null) stmt.close();
        if (rs != null) rs.close();

      } catch (Exception e) {
      }
      if (errorCode != 0) {
        throw new JTSException(errorCode, errorMsg);
      }
    }

    outputXML.append(fileProcessor.closeOutputFile("NGOGetNextSynchData"));

    return outputXML.toString();
  }
  public boolean isValidConnection(
      Connection conn, String validateQuery, int validationQueryTimeout) {
    try {
      if (conn.isClosed()) {
        return false;
      }
    } catch (SQLException ex) {
      // skip
      return false;
    }

    if (usePingMethod) {
      if (conn instanceof DruidPooledConnection) {
        conn = ((DruidPooledConnection) conn).getConnection();
      }

      if (conn instanceof ConnectionProxy) {
        conn = ((ConnectionProxy) conn).getRawObject();
      }

      if (clazz.isAssignableFrom(conn.getClass())) {
        if (validationQueryTimeout < 0) {
          validationQueryTimeout = DEFAULT_VALIDATION_QUERY_TIMEOUT;
        }

        try {
          ping.invoke(conn, true, validationQueryTimeout);
          return true;
        } catch (InvocationTargetException e) {
          Throwable cause = e.getCause();
          if (cause instanceof SQLException) {
            return false;
          }

          LOG.warn("Unexpected error in ping", e);
          return false;
        } catch (Exception e) {
          LOG.warn("Unexpected error in ping", e);
          return false;
        }
      }
    }

    Statement stmt = null;
    ResultSet rs = null;
    try {
      stmt = conn.createStatement();
      if (validationQueryTimeout > 0) {
        stmt.setQueryTimeout(validationQueryTimeout);
      }
      rs = stmt.executeQuery(validateQuery);
      return true;
    } catch (SQLException e) {
      return false;
    } catch (Exception e) {
      LOG.warn("Unexpected error in ping", e);
      return false;
    } finally {
      JdbcUtils.close(rs);
      JdbcUtils.close(stmt);
    }
  }
Example #20
0
 private Statement createStatement() throws SQLException {
   Statement statement = connection.createStatement();
   statement.setQueryTimeout(30); // set timeout to 30 sec.
   return statement;
 }
  public static void main(String[] args)
      throws ClassNotFoundException, FileNotFoundException, IOException {
    PrintStream out =
        new PrintStream(new FileOutputStream("src/adbproject/output_Spatialite_D1_SA.txt"));
    System.setOut(out);
    long startTime = 0;
    long stopTime = 0;
    // load the sqlite-JDBC driver using the current class loader
    Class.forName("org.sqlite.JDBC");

    Connection conn = null;
    try {
      // enabling dynamic extension loading
      // absolutely required by SpatiaLite
      SQLiteConfig config = new SQLiteConfig();
      config.enableLoadExtension(true);

      // create a database connection
      conn =
          DriverManager.getConnection(
              "jdbc:sqlite:C:/Users/Madhumitha/Documents/db.sqlite", config.toProperties());
      conn.setAutoCommit(false);
      Statement stmt = conn.createStatement();
      stmt.setQueryTimeout(30); // set timeout to 30 sec.
      double total, used;
      // loading SpatiaLite
      stmt.execute("SELECT load_extension('mod_spatialite')");

      FileReader reader = new FileReader("src/adbproject/S_Analysis-Spatialite_D1");
      BufferedReader bufferedReader = new BufferedReader(reader);
      int i = 0;
      String line;
      stmt = conn.createStatement();
      ResultSet rs = null;
      while ((line = bufferedReader.readLine()) != null) {
        i++;
        long sum = 0;
        // for(int cnt=1;cnt<=5;cnt++)
        // {
        startTime = System.currentTimeMillis();
        total =
            ((double) ((double) (Runtime.getRuntime().totalMemory() / 1024) / 1024))
                - ((double) ((double) (Runtime.getRuntime().freeMemory() / 1024) / 1024));
        rs = stmt.executeQuery(line);
        used =
            ((double) ((double) (Runtime.getRuntime().totalMemory() / 1024) / 1024))
                - ((double) ((double) (Runtime.getRuntime().freeMemory() / 1024) / 1024));
        stopTime = System.currentTimeMillis();
        long elapsedTime = stopTime - startTime;
        System.out.println("Time taken for query " + i + ": " + elapsedTime + " ms");
        // sum=sum+elapsedTime;
        // }
        // long avgTime= sum/5;
        // System.out.println("Time taken for query " +i+ ": " +avgTime+" ms");

        System.out.println();
        rs.close();
      }

      stmt.close();
      conn.close();
      reader.close();

    } 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 (conn != null) {
          conn.close();
        }
      } catch (SQLException e) {
        // connection close failed.
        System.err.println(e);
      }
    }
  }
Example #22
0
 private void applyQueryTimeOut(Statement stmt) throws SQLException {
   if (this.queryTimeout != 0) {
     stmt.setQueryTimeout(queryTimeout);
   }
 }
Example #23
0
  /**
   * @param recherche le mot a recherche dans la base de donnees
   * @param artistCheck si true, recherche dans artist
   * @param titleCheck si true, recherche dans title
   * @return la base de donnee dans un tableau d'objet
   */
  public static synchronized Object[][] recherche(
      String recherche, boolean artistCheck, boolean titleCheck) {
    Connection connection = initConnection();
    try {
      Statement statement = connection.createStatement();
      statement.setQueryTimeout(30);
      ResultSet rs;
      ArrayList<Object[]> l = new ArrayList<Object[]>();
      if (artistCheck && titleCheck)
        rs =
            statement.executeQuery(
                "select * from songs where artist like '%"
                    + recherche
                    + "%' or title like '%"
                    + recherche
                    + "%'");
      else if (artistCheck)
        rs = statement.executeQuery("select * from songs where artist like '%" + recherche + "%'");
      else if (titleCheck)
        rs = statement.executeQuery("select * from songs where title like '%" + recherche + "%'");
      else {
        if (recherche.length() == 0) rs = statement.executeQuery("select * from songs");
        else {
          String requete = "select * from songs where ";
          for (int i = 0; i < columnNames.length - 1; i++)
            requete += columnNames[i] + " like '%" + recherche + "%' or ";
          requete += "duration like '%" + recherche + "%'";
          rs = statement.executeQuery(requete);
        }
      }

      while (rs.next()) {
        ArrayList<StructureMusique> tmp = new ArrayList<StructureMusique>();
        Musique mus =
            new Musique(
                rs.getString("title"),
                rs.getString("album"),
                rs.getString("artist"),
                rs.getString("genre"),
                rs.getString("year"),
                rs.getString("duration"),
                Integer.parseInt(rs.getString("nblecture")));
        tmp.add(mus.getTitle());
        tmp.add(mus.getAlbum());
        tmp.add(mus.getArtist());
        tmp.add(mus.getGenre());
        // tmp.add(mus.getYear());
        // tmp.add(mus.getDuration());
        l.add(tmp.toArray());
      }
      Object retour[][] = new Object[l.size()][];
      for (int i = 0; i < l.size(); ++i) retour[i] = l.get(i);

      statement.close();
      connection.close();
      return retour;
    } catch (SQLException e) {
      System.err.println(e.getMessage());
    } finally {
      try {
        if (connection != null) connection.close();
      } catch (SQLException e) {
        System.err.println(e);
      }
    }
    return null;
  }
Example #24
0
 private static void setStatementTimeout(MappedStatement mappedStatement, Statement statement)
     throws SQLException {
   if (mappedStatement.getTimeout() != null) {
     statement.setQueryTimeout(mappedStatement.getTimeout().intValue());
   }
 }
  public static void main(String[] args) throws ClassNotFoundException {
    // load the sqlite-JDBC driver using the current class loader
    Class.forName("org.sqlite.JDBC");

    Connection connection = null;
    try {
      // create a database connection
      connection = DriverManager.getConnection("jdbc:sqlite:tables.db");
      Statement statement = connection.createStatement();
      statement.setQueryTimeout(30); // set timeout to 30 sec.

      // create Station Table (The data have to be imported)
      statement.executeUpdate("drop table if exists Station");
      statement.executeUpdate(
          "create table Station (Station_ID integer PRIMARY KEY, Station_Name string, IsTerminal boolean)");

      // Initialize Station
      statement.executeUpdate("insert into Station values(1, 'Gakken-Nara-Tomigaoka', 1)");
      statement.executeUpdate("insert into Station values(2, 'Gakken-Kita-Ikoma', 0)");
      statement.executeUpdate("insert into Station values(3, 'Shiraniwadai', 0)");
      statement.executeUpdate("insert into Station values(4, 'Ikoma', 0)");
      statement.executeUpdate("insert into Station values(5, 'Shin-ishikiri', 0)");
      statement.executeUpdate("insert into Station values(6, 'Nagata', 0)");
      statement.executeUpdate("insert into Station values(7, 'Takaida', 0)");
      statement.executeUpdate("insert into Station values(8, 'Morinomiya', 0)");
      statement.executeUpdate("insert into Station values(9, 'Hanimachi-4chome', 0)");
      statement.executeUpdate("insert into Station values(10, 'Mahidoldaigakumae', 1)");
      statement.executeUpdate("insert into Station values(11, 'Narasentandai', 0)");
      statement.executeUpdate("insert into Station values(12, 'Gakuemmae', 0)");
      statement.executeUpdate("insert into Station values(13, 'IPlab', 1)");

      // create TimeTable table (The data have to be imported)
      statement.executeUpdate("drop table if exists TimeTable");
      statement.executeUpdate("create table TimeTable (Bus_Line, Bus_ID, Station_ID, Time time)");

      // 138 Line
      // First bus
      statement.executeUpdate("insert into TimeTable values(138, 1, 1, '09:00:00')");
      statement.executeUpdate("insert into TimeTable values(138, 1, 2, '09:10:00')");
      statement.executeUpdate("insert into TimeTable values(138, 1, 3, '09:20:00')");
      statement.executeUpdate("insert into TimeTable values(138, 1, 4, '09:30:00')");
      statement.executeUpdate("insert into TimeTable values(138, 1, 5, '09:40:00')");
      statement.executeUpdate("insert into TimeTable values(138, 1, 6, '09:50:00')");
      statement.executeUpdate("insert into TimeTable values(138, 1, 8, '10:10:00')");
      statement.executeUpdate("insert into TimeTable values(138, 1, 10, '10:30:00')");

      // Second bus
      statement.executeUpdate("insert into TimeTable values(138, 2, 1, '09:50:00')");
      statement.executeUpdate("insert into TimeTable values(138, 2, 2, '10:00:00')");
      statement.executeUpdate("insert into TimeTable values(138, 2, 3, '10:10:00')");
      statement.executeUpdate("insert into TimeTable values(138, 2, 4, '10:20:00')");
      statement.executeUpdate("insert into TimeTable values(138, 2, 5, '10:30:00')");
      statement.executeUpdate("insert into TimeTable values(138, 2, 6, '10:40:00')");
      statement.executeUpdate("insert into TimeTable values(138, 2, 8, '11:00:00')");
      statement.executeUpdate("insert into TimeTable values(138, 2, 10, '11:20:00')");

      // Third bus
      statement.executeUpdate("insert into TimeTable values(138, 3, 1, '13:00:00')");
      statement.executeUpdate("insert into TimeTable values(138, 3, 2, '13:10:00')");
      statement.executeUpdate("insert into TimeTable values(138, 3, 3, '13:20:00')");
      statement.executeUpdate("insert into TimeTable values(138, 3, 4, '13:30:00')");
      statement.executeUpdate("insert into TimeTable values(138, 3, 5, '13:40:00')");
      statement.executeUpdate("insert into TimeTable values(138, 3, 6, '13:50:00')");
      statement.executeUpdate("insert into TimeTable values(138, 3, 8, '14:10:00')");
      statement.executeUpdate("insert into TimeTable values(138, 3, 10, '14:30:00')");

      // 82 line
      // First bus
      statement.executeUpdate("insert into TimeTable values(82, 1, 1, '09:00:00')");
      statement.executeUpdate("insert into TimeTable values(82, 1, 2, '09:10:00')");
      statement.executeUpdate("insert into TimeTable values(82, 1, 3, '09:20:00')");
      statement.executeUpdate("insert into TimeTable values(82, 1, 4, '09:30:00')");
      statement.executeUpdate("insert into TimeTable values(82, 1, 5, '09:40:00')");
      statement.executeUpdate("insert into TimeTable values(82, 1, 6, '09:50:00')");
      statement.executeUpdate("insert into TimeTable values(82, 1, 11, '10:10:00')");
      statement.executeUpdate("insert into TimeTable values(82, 1, 12, '10:30:00')");
      statement.executeUpdate("insert into TimeTable values(82, 1, 13, '10:50:00')");

      // Second bus
      statement.executeUpdate("insert into TimeTable values(82, 2, 1, '11:00:00')");
      statement.executeUpdate("insert into TimeTable values(82, 2, 2, '11:10:00')");
      statement.executeUpdate("insert into TimeTable values(82, 2, 3, '11:20:00')");
      statement.executeUpdate("insert into TimeTable values(82, 2, 4, '11:30:00')");
      statement.executeUpdate("insert into TimeTable values(82, 2, 5, '11:40:00')");
      statement.executeUpdate("insert into TimeTable values(82, 2, 6, '11:50:00')");
      statement.executeUpdate("insert into TimeTable values(82, 2, 11, '12:10:00')");
      statement.executeUpdate("insert into TimeTable values(82, 2, 12, '12:30:00')");
      statement.executeUpdate("insert into TimeTable values(82, 2, 13, '12:50:00')");

      // 8 line
      // First bus
      statement.executeUpdate("insert into TimeTable values(8, 1, 1, '09:20:00')");
      statement.executeUpdate("insert into TimeTable values(8, 1, 2, '09:50:00')");
      statement.executeUpdate("insert into TimeTable values(8, 1, 5, '10:30:00')");
      statement.executeUpdate("insert into TimeTable values(8, 1, 10, '10:50:00')");

      // Second bus
      statement.executeUpdate("insert into TimeTable values(8, 2, 1, '10:00:00')");
      statement.executeUpdate("insert into TimeTable values(8, 2, 2, '10:30:00')");
      statement.executeUpdate("insert into TimeTable values(8, 2, 5, '11:10:00')");
      statement.executeUpdate("insert into TimeTable values(8, 2, 10, '11:30:00')");

      // Third bus
      statement.executeUpdate("insert into TimeTable values(8, 3, 1, '13:00:00')");
      statement.executeUpdate("insert into TimeTable values(8, 3, 2, '13:30:00')");
      statement.executeUpdate("insert into TimeTable values(8, 3, 5, '14:10:00')");
      statement.executeUpdate("insert into TimeTable values(8, 3, 10, '14:30:00')");

      // Fourth bus
      statement.executeUpdate("insert into TimeTable values(8, 4, 1, '13:40:00')");
      statement.executeUpdate("insert into TimeTable values(8, 4, 2, '14:10:00')");
      statement.executeUpdate("insert into TimeTable values(8, 4, 5, '14:50:00')");
      statement.executeUpdate("insert into TimeTable values(8, 4, 10, '15:10:00')");

      // Fifith bus
      statement.executeUpdate("insert into TimeTable values(8, 5, 1, '16:40:00')");
      statement.executeUpdate("insert into TimeTable values(8, 5, 2, '17:10:00')");
      statement.executeUpdate("insert into TimeTable values(8, 5, 5, '17:50:00')");
      statement.executeUpdate("insert into TimeTable values(8, 5, 10, '18:10:00')");

      // create File table (Only blank table at first)
      statement.executeUpdate("drop table if exists File");
      statement.executeUpdate(
          "create table File (File_ID int PRIMARY KEY, File_Name string, File_Path string, File_Size float, File_Type String, DTN_Address string, MAC_Address string, Status int)");

      // Checking the data in the table
      /* ResultSet rs = statement.executeQuery("select * from TimeTable");
      while(rs.next())
      {
        // read the result set
        System.out.println("Station_Name = " + rs.getString("Station_Name"));
        System.out.println("Station_ID = " + rs.getInt("Station_ID"));
        System.out.println("Terminal = " + rs.getBoolean("IsTerminal"));
        System.out.println("Time = " + rs.getInt("Hour") + ":" + rs.getInt("Minute"));
      }

      ResultSet rs1 = statement.executeQuery("select * from File");
      while(rs1.next())
      {
        // read the result set
        System.out.println("File_Name = " + rs1.getString("File_Name"));
      } */

    } 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);
      }
    }
  }
 @Test(expected = SQLFeatureNotSupportedException.class)
 public void assertSetQueryTimeout() throws SQLException {
   actual.setQueryTimeout(0);
 }
Example #27
0
  @Override
  public void save(Administratie admin) throws IOException {
    // todo opgave 4

    try {
      initConnection();

      for (Persoon p : admin.getPersonen()) {
        Statement statement = null;

        statement = conn.createStatement();
        // set timeout to 30 sec
        statement.setQueryTimeout(30);

        String ouderlijkGezin = "";

        if (p.getOuderlijkGezin() != null) {
          ouderlijkGezin = String.valueOf(p.getOuderlijkGezin().getNr());
        }

        String query =
            String.format(
                "INSERT INTO `Persoon` VALUES(%d,'%s','%s','%s','%s','%s','%s','%s');",
                p.getNr(),
                p.getVoornamen(),
                p.getAchternaam(),
                p.getTussenvoegsel(),
                p.getGebDat().getTime().toString(),
                p.getGebPlaats(),
                p.getGeslacht().toString(),
                ouderlijkGezin);
        statement.executeUpdate(query);

        statement.close();
      }

      for (Gezin g : admin.getGezinnen()) {
        Statement statement = null;

        statement = conn.createStatement();
        // set timeout to 30 sec
        statement.setQueryTimeout(30);

        String huwelijksDatum = "";
        String scheidingsDatum = "";
        int ouder2 = -1;

        if (g.getOuder2() != null) {
          ouder2 = g.getOuder2().getNr();
        }
        if (g.getHuwelijksdatum() != null) {
          huwelijksDatum = g.getHuwelijksdatum().getTime().toString();
        }

        if (g.getScheidingsdatum() != null) {
          scheidingsDatum = g.getScheidingsdatum().getTime().toString();
        }

        String query =
            String.format(
                "INSERT INTO `Gezin` VALUES(%d,%d,%d,'%s','%s');",
                g.getNr(), g.getOuder1().getNr(), ouder2, huwelijksDatum, scheidingsDatum);
        statement.executeUpdate(query);

        statement.close();
      }

    } catch (SQLException e) {

      e.printStackTrace();
    } catch (ClassNotFoundException e) {
      e.printStackTrace();
    } finally {
      // closeConnection();

    }
  }