Ejemplo n.º 1
1
  public boolean checkChartUpdateRequired(String doi, Connection con) throws SQLException {

    PreparedStatement getUpdateStmt = con.prepareStatement(DEF_GET_UPDATE_QUERY);
    getUpdateStmt.setString(1, doi);
    ResultSet rs1 = getUpdateStmt.executeQuery();
    int lastNcites = 0;
    if (rs1.first()) {
      lastNcites = rs1.getInt("lastNcites");
    } else {
      rs1.close();
      getUpdateStmt.close();
      return true;
    }
    rs1.close();
    getUpdateStmt.close();

    PreparedStatement getNcitesStmt = con.prepareStatement(DEF_GET_NCITES_QUERY);
    getNcitesStmt.setString(1, doi);
    int ncites = 0;
    ResultSet rs2 = getNcitesStmt.executeQuery();
    if (rs2.first()) {
      ncites = rs2.getInt("ncites");
    }
    rs2.close();
    getNcitesStmt.close();
    if (ncites != lastNcites) {
      return true;
    } else {
      return false;
    }
  } // - checkChartUpdateRequired
Ejemplo n.º 2
0
  public static String getRate(String voter, String ans) {

    DBConnector dBConnector = new DBConnector();
    Connection dbConnection = dBConnector.getConnection();
    String selectTableSQL = "select * from RATING where idvoter= ? and idanswer=?";
    String selectTableSQL2 = "select * from user where username= ? ";
    PreparedStatement statement;
    try {
      statement = dbConnection.prepareStatement(selectTableSQL2);
      statement.setString(1, voter);
      ResultSet rs = statement.executeQuery();
      while (rs.next()) {
        voter = rs.getObject(1) + "";
      }
      statement.close();

      statement = dbConnection.prepareStatement(selectTableSQL);
      statement.setString(1, voter);
      statement.setString(2, ans);
      rs = statement.executeQuery();
      int count = 0;
      while (rs.next()) {
        count++;
        return rs.getObject(4) + "";
      }
      statement.close();
    } catch (SQLException ex) {
      Logger.getLogger(Rating.class.getName()).log(Level.SEVERE, null, ex);
    }
    return "gagal";
  }
Ejemplo n.º 3
0
 public long getAverAmount5(String paramString) throws SQLException {
   long l = 0L;
   PreparedStatement localPreparedStatement = null;
   ResultSet localResultSet = null;
   try {
     this.conn = getConn();
     String str =
         "SELECT MIN(TradeDate) FROM (SELECT TradeDate FROM HistoryDayData WHERE CommodityID = ? ORDER BY TradeDate DESC) WHERE rownum < 6";
     localPreparedStatement = this.conn.prepareStatement(str);
     localPreparedStatement.setString(1, paramString);
     localResultSet = localPreparedStatement.executeQuery();
     Timestamp localTimestamp = null;
     if (localResultSet.next()) localTimestamp = localResultSet.getTimestamp(1);
     localResultSet.close();
     localPreparedStatement.close();
     str = "SELECT AVG(TotalAmount) FROM HistoryDayData WHERE CommodityID = ? AND TradeDate >= ?";
     localPreparedStatement = this.conn.prepareStatement(str);
     localPreparedStatement.setString(1, paramString);
     localPreparedStatement.setTimestamp(2, localTimestamp);
     localResultSet = localPreparedStatement.executeQuery();
     if (localResultSet.next()) l = localResultSet.getLong(1);
   } catch (SQLException localSQLException) {
     localSQLException.printStackTrace();
   } finally {
     closeStatement(localResultSet, localPreparedStatement, null);
   }
   return l;
 }
Ejemplo n.º 4
0
 /**
  * Returns the normalized Authority value for an author based on the name passed in. If no
  * authority exists, null will be returned.
  *
  * @param author the author to get the authority information for
  * @return the normalized authority information or null if no authority exists.
  */
 public static String getNormalizedAuthorAuthorityFromDatabase(String author) {
   if (!connectToDatabase()) {
     return null;
   } else {
     try {
       getPreferredAuthorByOriginalNameStmt.setString(1, author);
       // First check without normalization
       ResultSet originalNameResults = getPreferredAuthorByOriginalNameStmt.executeQuery();
       if (originalNameResults.next()) {
         String authority = originalNameResults.getString("normalizedName");
         // Found a match
         originalNameResults.close();
         return authority;
       } else {
         // No match, check alternate names for the author
         String normalizedAuthor = AuthorNormalizer.getNormalizedName(author);
         getPreferredAuthorByAlternateNameStmt.setString(1, normalizedAuthor);
         ResultSet alternateNameResults = getPreferredAuthorByAlternateNameStmt.executeQuery();
         if (alternateNameResults.next()) {
           String authority = alternateNameResults.getString("normalizedName");
           alternateNameResults.close();
           return authority;
         }
       }
     } catch (Exception e) {
       logger.error("Error loading authority information from database", e);
     }
   }
   return null;
 }
  @Override
  public UserInfo getUserByAuthToken(String token) throws SQLException {
    UserInfo userInfo = null;
    Connection connection = null;
    PreparedStatement stmt = null;

    try {
      connection = Database.getConnection();

      stmt = connection.prepareStatement(AuthTokenDAOQuery.GET_USER_BY_TOKEN);
      stmt.setString(1, token);
      ResultSet rs = stmt.executeQuery();

      if (rs.next()) {
        userInfo = new UserInfo();
        userInfo.setName(rs.getString("id"));
        stmt.close();

        stmt = connection.prepareStatement(AuthTokenDAOQuery.GET_ROLES_OF_USER);
        stmt.setString(1, userInfo.getName());
        rs = stmt.executeQuery();
        while (rs.next()) {
          String role = rs.getString("role");
          userInfo.getRoles().add(Role.valueOf(role));
        }
      }
    } catch (SQLException e) {
      throw e;
    } finally {
      if (stmt != null) stmt.close();
      if (connection != null) connection.close();
    }

    return userInfo;
  }
 /**
  * Load user's info from database.
  *
  * @param userName
  */
 @Override
 protected UserIdentity loadUser(String userName) {
   try {
     try (Connection connection = getConnection();
         PreparedStatement statement1 = connection.prepareStatement(_userSql)) {
       statement1.setObject(1, userName);
       try (ResultSet rs1 = statement1.executeQuery()) {
         if (rs1.next()) {
           int key = rs1.getInt(_userTableKey);
           String credentials = rs1.getString(_userTablePasswordField);
           List<String> roles = new ArrayList<String>();
           try (PreparedStatement statement2 = connection.prepareStatement(_roleSql)) {
             statement2.setInt(1, key);
             try (ResultSet rs2 = statement2.executeQuery()) {
               while (rs2.next()) {
                 roles.add(rs2.getString(_roleTableRoleField));
               }
             }
           }
           return putUser(
               userName,
               Credential.getCredential(credentials),
               roles.toArray(new String[roles.size()]));
         }
       }
     }
   } catch (NamingException e) {
     LOG.warn("No datasource for " + _jndiName, e);
   } catch (SQLException e) {
     LOG.warn("Problem loading user info for " + userName, e);
   }
   return null;
 }
Ejemplo n.º 7
0
  /** Web service operation */
  @WebMethod(operationName = "getUserByToken")
  @WebResult(name = "User")
  public User getUserByToken(@WebParam(name = "token") String token) {
    User user = new User();
    try {
      Statement stmt = conn.createStatement();
      String sql;
      sql = "SELECT user_id FROM token WHERE token_id = ?";
      PreparedStatement dbStatement = conn.prepareStatement(sql);
      dbStatement.setString(1, token);
      ResultSet rs = dbStatement.executeQuery();

      while (rs.next()) {
        user.setUserID(rs.getInt("user_id"));
      }
      rs.close();

      String sql2 = "SELECT * FROM user WHERE user_id = ?";
      PreparedStatement dbStatement2 = conn.prepareStatement(sql);
      dbStatement2.setInt(1, user.getUserID());
      ResultSet rs2 = dbStatement2.executeQuery();

      while (rs2.next()) {
        user.setName(rs.getString("name"));
        user.setEmail(rs.getString("email"));
        user.setPassword(rs.getString("password"));
      }
      rs2.close();
      stmt.close();
    } catch (SQLException ex) {
      Logger.getLogger(UserWS.class.getName()).log(Level.SEVERE, null, ex);
    }

    return user;
  }
Ejemplo n.º 8
0
 @Test
 public void moveTasksToBacklog() throws Exception {
   given()
       .header(PermanentUserData.tokenHeader)
       .post("/m/backlog/2016/04/26")
       .then()
       .statusCode(204);
   try (Connection conn = ds.getConnection();
       PreparedStatement ps =
           conn.prepareStatement(
               "select count (*) from task where appuser_id = ? and date is null")) {
     ps.setLong(1, PermanentUserData.user.getId());
     try (ResultSet rs = ps.executeQuery(); ) {
       rs.next();
       assertThat(rs.getInt(1), equalTo(2));
     }
   }
   given().filter(sessionFilter).post("/web/backlog/2016/05/16").then().statusCode(204);
   try (Connection conn = ds.getConnection();
       PreparedStatement ps =
           conn.prepareStatement(
               "select count (*) from task where appuser_id = ? and date is null")) {
     ps.setLong(1, PermanentUserData.user.getId());
     try (ResultSet rs = ps.executeQuery(); ) {
       rs.next();
       assertThat(rs.getInt(1), equalTo(3));
     }
   }
 }
Ejemplo n.º 9
0
  public static void fivejp(
      ResultSet[] data1, ResultSet[] data2, ResultSet[] data3, ResultSet[] data4, ResultSet[] data5)
      throws SQLException {

    Connection conn = DriverManager.getConnection("jdbc:default:connection");

    PreparedStatement ps1 = conn.prepareStatement("select * from MRS.FIVERS where i > ?");
    ps1.setInt(1, 1);
    data1[0] = ps1.executeQuery();

    PreparedStatement ps2 = conn.prepareStatement("select * from MRS.FIVERS  where i > ?");
    ps2.setInt(1, 2);
    data2[0] = ps2.executeQuery();

    PreparedStatement ps3 = conn.prepareStatement("select * from MRS.FIVERS  where i > ?");
    ps3.setInt(1, 3);
    data3[0] = ps3.executeQuery();

    PreparedStatement ps4 = conn.prepareStatement("select * from MRS.FIVERS  where i > ?");
    ps4.setInt(1, 4);
    data4[0] = ps4.executeQuery();

    PreparedStatement ps5 = conn.prepareStatement("select * from MRS.FIVERS  where i > ?");
    ps5.setInt(1, 5);
    data5[0] = ps5.executeQuery();

    conn.close();
  }
Ejemplo n.º 10
0
  public static void selectRows(int p1, int p2, ResultSet[] data1, ResultSet[] data2)
      throws SQLException {

    System.out.println("selectRows - 2 arg - 2 rs");

    Connection conn = DriverManager.getConnection("jdbc:default:connection");
    PreparedStatement ps = conn.prepareStatement("select * from t1 where i = ?");
    ps.setInt(1, p1);
    data1[0] = ps.executeQuery();

    ps = conn.prepareStatement("select * from t1 where i >= ?");
    ps.setInt(1, p2);
    data2[0] = ps.executeQuery();

    if (p2 == 99) data2[0].close();

    // return no results
    if (p2 == 199) {
      data1[0].close();
      data1[0] = null;
      data2[0].close();
      data2[0] = null;
    }

    // swap results
    if (p2 == 299) {
      ResultSet rs = data1[0];
      data1[0] = data2[0];
      data2[0] = rs;
    }

    conn.close();
  }
Ejemplo n.º 11
0
  public static int randomPage() throws SQLException {
    String query = "select id from pages where finding=0 order by Rand() limit 1";

    // String query="select min(id) from pages";

    // String query="SELECT * FROM `pages` WHERE id >= (SELECT FLOOR( MAX(id) * RAND()) FROM `pages`
    // ) ORDER BY id LIMIT 1;";
    Connection j = textdisplay.DatabaseWrapper.getConnection();
    PreparedStatement p = j.prepareStatement(query);

    ResultSet rs;
    rs = p.executeQuery(query);
    rs.next();
    int min = rs.getInt(1);
    if (true) {
      j.close();
      return min;
    }
    rs = p.executeQuery("select max(id) from pages");
    rs.next();
    int max = rs.getInt(1);
    int count = max - min;
    Random generator = new Random();
    int rec = generator.nextInt(count);
    rec += min;

    j.close();

    return rec;
  }
Ejemplo n.º 12
0
  public static int userCount(String user) throws SQLException {

    String query = "select count(id) from pages where finder=?";
    System.out.print(query);
    Connection j = textdisplay.DatabaseWrapper.getConnection();
    PreparedStatement p = j.prepareStatement(query);
    p.setString(1, user);
    ResultSet rs = p.executeQuery();
    rs.next();
    int amount = rs.getInt(1);

    query = "select * from leaders where uname=?";
    p = j.prepareStatement(query);

    p.setString(1, user);
    rs = p.executeQuery();
    if (!rs.next()) {
      query = "insert into leaders (uname,count) values(?,0)";
      p = j.prepareStatement(query);
      p.setString(1, user);
      p.execute();
    }

    query = "update leaders set count=? where uname=?";
    p = j.prepareStatement(query);
    p.setInt(1, amount);
    p.setString(2, user);
    p.execute();
    j.close();
    return amount;
  }
Ejemplo n.º 13
0
 public Topic displayques(int alid) throws Exception {
   Topic topic = new Topic();
   Connection conn = null;
   PreparedStatement stmt = null;
   PreparedStatement stmt1 = null;
   ResultSet rs = null;
   try {
     conn = Dbtopic.getConn();
     stmt = conn.prepareStatement("select * from IMQUES WHERE IMQUES.ALID =?");
     stmt1 =
         conn.prepareStatement(
             "UPDATE imques  SET  scannumber=scannumber+1  WHERE imques.alid = ? ");
     stmt.setInt(1, alid);
     stmt1.setInt(1, alid);
     rs = stmt.executeQuery();
     stmt1.executeQuery();
     while (rs != null && rs.next()) {
       topic.setAlid(rs.getInt("alid"));
       topic.setPubren(rs.getString("pubren"));
       topic.setPubtime(rs.getString("pubtime"));
       topic.setQues(rs.getString("ques"));
       topic.setQuesinfo(rs.getString("quesinfo"));
     }
   } finally {
     Dbtopic.closeconn(conn);
   }
   return topic;
 }
Ejemplo n.º 14
0
  /**
   * Consulta as fatos do prestador passado em um determinado periodo
   *
   * @param strCdContrato - o codigo do contrato do prestado do qual se deseja obter as fatos
   * @param strNumPeriodo - o periodo de referencia do qual se deseja obter os fatos
   * @return um array de fatos do prestador fornecido como paramentro
   */
  public static final ResumoFato[] buscaResumoFato(String strCdContrato, String strNumPeriodo)
      throws Exception {

    Connection con = ConnectionPool.getConnection();
    ResumoFato[] fatos = null;
    PreparedStatement pst;
    ResultSet rset;
    int qtdeFatos = 0;

    try {

      pst = con.prepareStatement(CONSULTA_RESUMO_FATO);
      pst.setString(1, strCdContrato);
      pst.setString(2, strNumPeriodo);
      pst.setString(3, strCdContrato);
      pst.setString(4, strNumPeriodo);
      pst.setString(5, strCdContrato);
      pst.setString(6, strNumPeriodo);
      pst.setString(7, strCdContrato);
      pst.setString(8, strNumPeriodo);
      pst.setString(9, strCdContrato);
      pst.setString(10, strNumPeriodo);

      rset = pst.executeQuery();

      if (!rset.next()) {
        return null;
      } // if ( ! rset.next() )

      do {
        qtdeFatos += 1;
      } while (rset.next());

      System.out.println("qtdeFatos -> " + qtdeFatos);

      fatos = new ResumoFato[qtdeFatos];

      rset = pst.executeQuery();

      qtdeFatos = 0;

      while (rset.next()) {
        fatos[qtdeFatos] = montaResumoFato(rset);
        qtdeFatos++;
      } // while

    } catch (SQLException sqle) {
      sqle.printStackTrace();
      throw new Exception(
          "Não foi possivel estabelecer conexão com a base de "
              + "dados.Erro:\n"
              + sqle.getMessage());
    } finally { // catch()
      if (con != null) {
        con.close();
        System.out.println("Fechou a conexao");
      } // if
    }
    return fatos;
  } // buscaResumoFato()
  private void testLikeIndex(Connection conn) throws SQLException {
    Statement stat = conn.createStatement();
    stat.execute("CREATE TABLE TEST(ID INT PRIMARY KEY, NAME VARCHAR(255))");
    stat.execute("INSERT INTO TEST VALUES(1, 'Hello')");
    stat.execute("INSERT INTO TEST VALUES(2, 'World')");
    stat.execute("create index idxname on test(name);");
    PreparedStatement prep, prepExe;

    prep = conn.prepareStatement("EXPLAIN SELECT * FROM TEST WHERE NAME LIKE ?");
    assertEquals(1, prep.getParameterMetaData().getParameterCount());
    prepExe = conn.prepareStatement("SELECT * FROM TEST WHERE NAME LIKE ?");
    prep.setString(1, "%orld");
    prepExe.setString(1, "%orld");
    ResultSet rs = prep.executeQuery();
    rs.next();
    String plan = rs.getString(1);
    assertTrue(plan.indexOf(".tableScan") >= 0);
    rs = prepExe.executeQuery();
    rs.next();
    assertEquals("World", rs.getString(2));
    assertFalse(rs.next());

    prep.setString(1, "H%");
    prepExe.setString(1, "H%");
    rs = prep.executeQuery();
    rs.next();
    String plan1 = rs.getString(1);
    assertTrue(plan1.indexOf("IDXNAME") >= 0);
    rs = prepExe.executeQuery();
    rs.next();
    assertEquals("Hello", rs.getString(2));
    assertFalse(rs.next());

    stat.execute("DROP TABLE IF EXISTS TEST");
  }
 public ResultSet tranzakcioLekerdezesDatumEsOsszegEsSzamlaszamSzerint(
     int datumTol,
     int datumIg,
     long osszegTol,
     long osszegIg,
     long szamlaszam,
     String rendezesSzempont)
     throws SQLException {
   if (rendezesSzempont.equals("RendezésDátumSzerint")) {
     if (pstmtForTLDEOESZSZRDSZ == null)
       pstmtForTLDEOESZSZRDSZ =
           conn.prepareStatement(
               "select * from tranzakcio where ((datum >= ? and datum <= ?) and (osszeg >= ? and osszeg <= ?) and (szamlaszam = ? or kuldo = ?)) order by datum");
     pstmtForTLDEOESZSZRDSZ.setInt(1, datumTol);
     pstmtForTLDEOESZSZRDSZ.setInt(2, datumIg);
     pstmtForTLDEOESZSZRDSZ.setLong(3, osszegTol);
     pstmtForTLDEOESZSZRDSZ.setLong(4, osszegIg);
     pstmtForTLDEOESZSZRDSZ.setLong(5, szamlaszam);
     pstmtForTLDEOESZSZRDSZ.setLong(6, szamlaszam);
     return pstmtForTLDEOESZSZRDSZ.executeQuery();
   }
   if (pstmtForTLDEOESZSZROSZ == null)
     pstmtForTLDEOESZSZROSZ =
         conn.prepareStatement(
             "select * from tranzakcio where ((datum >= ? and datum <= ?) and (osszeg >= ? and osszeg <= ?) and (szamlaszam = ? or kuldo = ?)) order by osszeg");
   pstmtForTLDEOESZSZROSZ.setInt(1, datumTol);
   pstmtForTLDEOESZSZROSZ.setInt(2, datumIg);
   pstmtForTLDEOESZSZROSZ.setLong(3, osszegTol);
   pstmtForTLDEOESZSZROSZ.setLong(4, osszegIg);
   pstmtForTLDEOESZSZROSZ.setLong(5, szamlaszam);
   pstmtForTLDEOESZSZROSZ.setLong(6, szamlaszam);
   return pstmtForTLDEOESZSZROSZ.executeQuery();
 }
Ejemplo n.º 17
0
 @Override
 public User read(int id) {
   try (Connection connection = getConnection()) {
     List<Role> roles = new ArrayList<>();
     try (PreparedStatement statement = connection.prepareStatement(READ_USER_ROLES_BY_USER_ID)) {
       statement.setInt(1, id);
       try (ResultSet resultSet = statement.executeQuery()) {
         while (resultSet.next()) {
           roles.add($.roleFrom(resultSet));
         }
       }
     }
     try (PreparedStatement statement = connection.prepareStatement(READ_BY_ID)) {
       statement.setInt(1, id);
       try (ResultSet resultSet = statement.executeQuery()) {
         if (resultSet.next()) {
           return $.userFrom(resultSet, roles);
         }
       }
     }
   } catch (SQLException e) {
     processException(e);
   }
   return null;
 }
Ejemplo n.º 18
0
  public void listProducts() {
    products = new ArrayList<Product>();

    try {
      ResultSet results;

      if (listAllProducts) {
        results = listAllProductsStatement.executeQuery();
      } else {
        listByCustomerStatement.setString(1, customerLogin);
        results = listByCustomerStatement.executeQuery();
      }
      while (results.next()) {
        int auctionId = results.getInt("auction_id");
        Product product = new Product(session.getDb(), auctionId);
        products.add(product);
      }

    } catch (SQLException e) {
      while (e != null) {
        debug.println(e.toString());
        debug.flush();
        e = e.getNextException();
      }
    }

    if (products.size() <= 0) {
      productsBox.setLine(0, "");
      productsBox.setLine(1, " No products to show.");
      for (int i = 2; i < 13; i++) {
        productsBox.setLine(i, "");
      }
    } else {
      productsBox.setLine(0, "      Name | Status | Highest Bid Amount | Buyer or Highest Bidder");
      productsBox.setLine(1, "");
      ArrayList<Product> productsOnPage = paginator.paginate(products, curPage, 5);
      int lineOffset = 0;
      for (int i = 0; i < productsOnPage.size(); i++) {
        Product product = productsOnPage.get(i);
        // lineOffset works now
        lineOffset = i * 2;
        productsBox.setLine(
            lineOffset + 3,
            " "
                + product.name
                + " | "
                + product.status
                + " | $"
                + product.amount
                + " | "
                + product.getHighestBidder());
      }
      // clear the lines of productBox if there are not as many products as before
      int lastLine = productsOnPage.size() * 2 + 2;
      for (int i = lastLine; i < 12; i++) {
        productsBox.setLine(i, "");
      }
      productsBox.setLine(12, paginator.getPageMenu(products, curPage, 5));
    }
  }
Ejemplo n.º 19
0
  // public static void main(String[] args)
  // {
  void query() {
    try {
      // System.out.println("hello ");
      // Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
      Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
      // System.out.println("heloo2");
      System.out.println("Driver Loading Succesful");
      Connection conn =
          DriverManager.getConnection(
              "jdbc:ucanaccess://C:\\Users\\naman\\Desktop\\Database1.accdb");
      System.out.println("connecttion with Database Succesful");
      Statement stmt = conn.createStatement();
      // access obj()
      PreparedStatement ps =
          conn.prepareStatement(
              "select customer_id,customer_name,phone,address,email from customer where customer_id = ?");
      v = Integer.parseInt(st);
      ps.setInt(1, v);

      PreparedStatement ps1 =
          conn.prepareStatement(
              "select m.medicine_name,o.quantity from medicine m,order o,new n where n.customer_id=? and n.order_id=o.order_id and o.medicine_id=m.medicine_id");
      ps1.setInt(1, v);
      // ps.setString(2, "*");

      rs1 = ps1.executeQuery();
      rs = ps.executeQuery();
      // ResultSetMetaData rsmd = rs.getMetaData();
      // System.out.println("Result set Created");

      // int id;
      // int salary;
      // System.out.println("id  ");
      //  String s;
      // int col=rsmd.getColumnCount();
      // System.out.println(col);
      // id=rsmd.getColumnCount();
      // int i=0;
      /* while(rs.next())
      {

          for ( i = 1; i <= col; i++)
          {

            s=rs.getString(i);
              System.out.println(s);

          }
          //salary=rs.getInt("salary");
         // System.out.println(s+"  " );
         // s=rs.getString("Medicine_Name");
          //System.out.println(s);
          //i++;
      }
      */
    } catch (Exception e) {

      System.out.println(e.getMessage());
    }
  }
Ejemplo n.º 20
0
  private ResultSet executeQuery() throws SQLException {
    ResultSet result = null;

    if (!mInitialized) {
      prepareStatement();
    }

    // Get the SQL connection
    SqlConnector sql_connector = mDataset.getSqlConnector();
    if (sql_connector != null) {
      // Count number of result
      if (mNbRows < 0) {
        setParams(mStatCount);
        ResultSet tmp = mStatCount.executeQuery();
        if (tmp.next()) {
          mNbRows = tmp.getInt(1);
        }
      }

      // Execute the query
      setParams(mStatQuery);
      result = mStatQuery.executeQuery();
    }

    return result;
  }
Ejemplo n.º 21
0
  /**
   * Add a new user into GrafixPlane with specified UID
   *
   * @param UID The specified UID
   * @param name The new user's name
   * @param passwd The new user's password
   * @param level The access level that would be assigned to the user
   * @return The newly created user, never null
   * @throws SQLException If there are some thing wrong with the database
   * @throws NameExistsException if this name already belongs to another user
   * @throws UIDExistsException if this UID already belongs to another user
   */
  public static User newUser(int UID, String name, String passwd, AccessLevel level)
      throws SQLException, NameExistsException, UIDExistsException {
    if (userPool.containsValue(UID)) throw new UIDExistsException();
    synchronized (userByName) {
      userByName.setString(1, name);
      if (userByName.executeQuery().first())
        throw new NameExistsException(); // A user with same name already exists
      synchronized (userByID) {
        userByID.setInt(1, UID);
        if (userByID.executeQuery().first())
          throw new UIDExistsException(); // A user with same UID already exists
      }

      synchronized (newUser) {
        newUser.setInt(1, UID);
        newUser.setString(2, name);
        newUser.setString(3, passwd);
        newUser.setInt(4, level.value);
        newUser.setInt(5, 0);

        newUser.execute();
      }
    }

    User result = new User(UID, name, level, 0);
    synchronized (userPool) {
      userPool.put(UID, result);
    }
    return result;
  }
 private int checkSituationRecordInDb(MobisTrafficSituationRecord sitRec, Connection con) {
   try {
     String selectString =
         "SELECT * FROM public.mobissituationrecord WHERE providerid='"
             + sitRec.getProviderId()
             + "' AND provider='"
             + sitRec.getProvider()
             + "' AND version="
             + sitRec.getSituationRecordVersion()
             + ";";
     PreparedStatement stmt = con.prepareStatement(selectString);
     ResultSet rs = stmt.executeQuery();
     if (rs.next()) {
       return 0;
     } else {
       selectString =
           "SELECT * FROM public.mobissituationrecord WHERE providerid='"
               + sitRec.getProviderId()
               + "' AND provider='"
               + sitRec.getProvider()
               + "';";
       stmt = con.prepareStatement(selectString);
       rs = stmt.executeQuery();
       if (rs.next()) {
         return 1;
       } else return 2;
     }
   } catch (SQLException e) {
     // TODO Auto-generated catch block
     e.printStackTrace();
     return -1;
   }
 }
Ejemplo n.º 23
0
  /* ------------------------------------------------------------ */
  @Override
  protected UserIdentity loadUser(String username) {
    try {
      if (null == _con) connectDatabase();

      if (null == _con) throw new SQLException("Can't connect to database");

      PreparedStatement stat = _con.prepareStatement(_userSql);
      stat.setObject(1, username);
      ResultSet rs = stat.executeQuery();

      if (rs.next()) {
        int key = rs.getInt(_userTableKey);
        String credentials = rs.getString(_userTablePasswordField);
        stat.close();

        stat = _con.prepareStatement(_roleSql);
        stat.setInt(1, key);
        rs = stat.executeQuery();
        List<String> roles = new ArrayList<String>();
        while (rs.next()) roles.add(rs.getString(_roleTableRoleField));

        stat.close();
        return putUser(
            username,
            Credential.getCredential(credentials),
            roles.toArray(new String[roles.size()]));
      }
    } catch (SQLException e) {
      LOG.warn("UserRealm " + getName() + " could not load user information from database", e);
      closeConnection();
    }
    return null;
  }
  private void testValues(Connection conn) throws SQLException {
    PreparedStatement prep = conn.prepareStatement("values(?, ?)");
    prep.setInt(1, 1);
    prep.setString(2, "Hello");
    ResultSet rs = prep.executeQuery();
    rs.next();
    assertEquals(1, rs.getInt(1));
    assertEquals("Hello", rs.getString(2));

    prep = conn.prepareStatement("select * from values(?, ?), (2, 'World!')");
    prep.setInt(1, 1);
    prep.setString(2, "Hello");
    rs = prep.executeQuery();
    rs.next();
    assertEquals(1, rs.getInt(1));
    assertEquals("Hello", rs.getString(2));
    rs.next();
    assertEquals(2, rs.getInt(1));
    assertEquals("World!", rs.getString(2));

    prep = conn.prepareStatement("values 1, 2");
    rs = prep.executeQuery();
    rs.next();
    assertEquals(1, rs.getInt(1));
    rs.next();
    assertEquals(2, rs.getInt(1));
  }
  public static ArrayList<StatisticalReports> retrieveStatistics(String username, String password) {

    ArrayList<StatisticalReports> allStatistics = new ArrayList<StatisticalReports>();
    DBCreation sql = DBCreation.getInstance();
    Connection conn;
    ResultSet res = null;
    PreparedStatement st;
    conn = sql.connect();
    ArrayList<String> nodes = new ArrayList<String>();
    int i;

    if (!checkMobileExistance(username, password)) {
      System.out.println("Invalid un or pw of android client");
      return null;
    }
    try {

      st = conn.prepareStatement("select clientID from clients");
      res = st.executeQuery();
      while (res.next()) {
        nodes.add(res.getString("clientID"));
      }
      st.clearParameters();

      for (i = 0; i < nodes.size(); i++) {
        StatisticalReports nodeStatistics = new StatisticalReports();

        st = conn.prepareStatement("SELECT * FROM statistics WHERE nodeID = ? ");
        st.setString(1, nodes.get(i));
        res = st.executeQuery();

        ArrayList<StatisticsEntry> statistic = new ArrayList<StatisticsEntry>();

        while (res.next()) {
          StatisticsEntry stat = new StatisticsEntry();
          stat.setNodeID(res.getString("nodeID"));
          stat.setInterfaceName(res.getString("interfaceName"));
          //	            	stat.setMaliciousPatternID(res.getInt("maliciousPatternID"));
          stat.setMaliciousPattern(getMaliciousByID(res.getInt("maliciousPatternID")));
          stat.setInterfaceIP(res.getString("interfaceIP"));
          stat.setFrequency(res.getInt("frequency"));

          statistic.add(stat);
        }
        nodeStatistics.setStatisticalReportEntries(statistic);
        allStatistics.add(nodeStatistics);
        st.clearParameters();
      }
    } catch (SQLException e) {
      e.printStackTrace();
      // System.out.println("Can't get malicious patterns from db");
    } finally {
      try {
        conn.close();
      } catch (SQLException e) {
        e.printStackTrace();
      }
    }
    return allStatistics;
  }
 private void testTempView(Connection conn) throws SQLException {
   Statement stat = conn.createStatement();
   PreparedStatement prep;
   stat.execute("CREATE TABLE TEST(FIELD INT PRIMARY KEY)");
   stat.execute("INSERT INTO TEST VALUES(1)");
   stat.execute("INSERT INTO TEST VALUES(2)");
   prep =
       conn.prepareStatement(
           "select FIELD FROM "
               + "(select FIELD FROM (SELECT FIELD  FROM TEST WHERE FIELD = ?) AS T2 "
               + "WHERE T2.FIELD = ?) AS T3 WHERE T3.FIELD = ?");
   prep.setInt(1, 1);
   prep.setInt(2, 1);
   prep.setInt(3, 1);
   ResultSet rs = prep.executeQuery();
   rs.next();
   assertEquals(1, rs.getInt(1));
   prep.setInt(1, 2);
   prep.setInt(2, 2);
   prep.setInt(3, 2);
   rs = prep.executeQuery();
   rs.next();
   assertEquals(2, rs.getInt(1));
   stat.execute("DROP TABLE TEST");
 }
Ejemplo n.º 27
0
 /** Get the Manuscript that contains this particular Folio */
 public Manuscript(int folio) throws SQLException {
   String query = "select msID from folios where pageNumber=?";
   Connection j = null;
   PreparedStatement ps = null;
   try {
     j = DatabaseWrapper.getConnection();
     ps = j.prepareStatement(query);
     ps.setInt(1, folio);
     ResultSet rs = ps.executeQuery();
     if (rs.next()) {
       this.id = rs.getInt(1);
       query = "select city,repository, msIdentifier,archive from manuscript where id=?";
       ps = j.prepareStatement(query);
       ps.setInt(1, id);
       rs = ps.executeQuery();
       if (rs.next()) {
         this.city = rs.getString(1);
         this.repository = rs.getString(2);
         this.collection = rs.getString(3);
         this.archive = rs.getString(4);
       }
     }
   } finally {
     if (j != null) {
       DatabaseWrapper.closeDBConnection(j);
       DatabaseWrapper.closePreparedStatement(ps);
     }
   }
 }
  private void testPreparedSubquery(Connection conn) throws SQLException {
    Statement s = conn.createStatement();
    s.executeUpdate("CREATE TABLE TEST(ID IDENTITY, FLAG BIT)");
    s.executeUpdate("INSERT INTO TEST(ID, FLAG) VALUES(0, FALSE)");
    s.executeUpdate("INSERT INTO TEST(ID, FLAG) VALUES(1, FALSE)");
    PreparedStatement u = conn.prepareStatement("SELECT ID, FLAG FROM TEST ORDER BY ID");
    PreparedStatement p = conn.prepareStatement("UPDATE TEST SET FLAG=true WHERE ID=(SELECT ?)");
    p.clearParameters();
    p.setLong(1, 0);
    assertEquals(1, p.executeUpdate());
    p.clearParameters();
    p.setLong(1, 1);
    assertEquals(1, p.executeUpdate());
    ResultSet rs = u.executeQuery();
    assertTrue(rs.next());
    assertEquals(0, rs.getInt(1));
    assertTrue(rs.getBoolean(2));
    assertTrue(rs.next());
    assertEquals(1, rs.getInt(1));
    assertTrue(rs.getBoolean(2));

    p = conn.prepareStatement("SELECT * FROM TEST WHERE EXISTS(SELECT * FROM TEST WHERE ID=?)");
    p.setInt(1, -1);
    rs = p.executeQuery();
    assertFalse(rs.next());
    p.setInt(1, 1);
    rs = p.executeQuery();
    assertTrue(rs.next());

    s.executeUpdate("DROP TABLE IF EXISTS TEST");
  }
Ejemplo n.º 29
0
  @Override
  public RegionDatabase load(UUID uuid) {
    HashMap<UUID, ProtectedChunkRegion> protectedChunks = new HashMap<>();
    PreparedStatement preparedStatement =
        baseSqliteConnection.createPreparedStatement(
            "SELECT COUNT(REGION_DATA) AS _SIZE_ FROM UPRO_%w_REGIONS"
                .replace("%w", uuid.toString().replace("-", "")));

    try {
      int size = preparedStatement.executeQuery().getInt("_SIZE_");
      preparedStatement =
          baseSqliteConnection.createPreparedStatement(
              "SELECT REGION_DATA FROM UPRO_%w_REGIONS"
                  .replace("%w", uuid.toString().replace("-", "")));
      ResultSet regions = preparedStatement.executeQuery();

      int i = 0;
      while (i < size) {
        ProtectedChunkRegion protectedChunkRegion =
            ProtectedChunkRegion.deserialize(regions.getString(1));
        protectedChunks.put(protectedChunkRegion.getUUID(), protectedChunkRegion);
        i++;
        regions.next();
      }
      preparedStatement.close();

    } catch (SQLException e) {
      e.printStackTrace();
    }

    return new RegionDatabase(protectedChunks);
  }
  @Override
  public List<List<String>> getListFilter() {
    List<List<String>> listFilter = new ArrayList<List<String>>();
    String result;
    Connection connection = SingletonConnection.getConnection();

    try {
      // for field constructor
      List<String> listField = new ArrayList<String>();
      PreparedStatement prepStat =
          connection.prepareStatement("SELECT DISTINCT Constructor FROM equipment;");
      ResultSet resSet = prepStat.executeQuery(); // recovery results
      // for each line store result in list of constructor
      while (resSet.next()) {
        result = resSet.getString("Constructor");
        listField.add(result); // add constructor in list
      }
      listFilter.add(listField); // add list of constructor in list of lists

      // for field type
      listField = new ArrayList<String>();
      prepStat = connection.prepareStatement("SELECT DISTINCT Type FROM equipment;");
      resSet = prepStat.executeQuery(); // recovery results
      // for each line store result in list of type
      while (resSet.next()) {
        result = resSet.getString("Type");
        listField.add(result); // add type in list
      }
      listFilter.add(listField); // add list of type in list of lists

      // for field statut
      listField = new ArrayList<String>();
      prepStat = connection.prepareStatement("SELECT DISTINCT Status FROM equipment;");
      resSet = prepStat.executeQuery(); // recovery results
      // for each line store result in list of statut
      while (resSet.next()) {
        result = resSet.getString("Status");
        listField.add(result); // add statut in list
      }
      listFilter.add(listField); // add list of statut in list of lists

      // for field localisation
      listField = new ArrayList<String>();
      prepStat = connection.prepareStatement("SELECT DISTINCT Localisation FROM equipment;");
      resSet = prepStat.executeQuery(); // recovery results
      // for each line store result in list of localisation
      while (resSet.next()) {
        result = resSet.getString("Localisation");
        listField.add(result); // add localisation in list
      }
      listFilter.add(listField); // add list of localisation in list of lists

    } catch (SQLException e) {
      // TODO Auto-generated catch block
      e.printStackTrace();
    }

    return listFilter; // return list of lists
  }