Example #1
0
  private static boolean connectToDatabase() {
    String databaseConnectionInfo =
        "jdbc:mysql://localhost/authorities?user=authorities&password=authorities&useUnicode=yes&characterEncoding=UTF-8";
    if (authoritiesConn == null) {
      try {
        authoritiesConn = DriverManager.getConnection(databaseConnectionInfo);
        getPreferredAuthorByOriginalNameStmt =
            authoritiesConn.prepareStatement(
                "SELECT viafId, normalizedName from preferred_authors where originalName = ?",
                ResultSet.TYPE_FORWARD_ONLY,
                ResultSet.CONCUR_READ_ONLY);
        getPreferredAuthorByNormalizedNameStmt =
            authoritiesConn.prepareStatement(
                "SELECT viafId, normalizedName from preferred_authors where originalName = ?",
                ResultSet.TYPE_FORWARD_ONLY,
                ResultSet.CONCUR_READ_ONLY);
        getPreferredAuthorByAlternateNameStmt =
            authoritiesConn.prepareStatement(
                "SELECT preferred_authors.viafId, normalizedName FROM `preferred_authors` inner join alternate_authors on preferred_authors.viafId = alternate_authors.viafId where alternateName = ?",
                ResultSet.TYPE_FORWARD_ONLY,
                ResultSet.CONCUR_READ_ONLY);

        return true;
      } catch (Exception e) {
        logger.error("Error connecting to authorities database");
        return false;
      }
    }
    return true;
  }
Example #2
0
  /** Return the user's secure_hash_key. If one wasn't found create one on the fly. */
  private SecureHashKeyResult getSecureHashKey(UserAccount user, Connection c) throws SQLException {

    PreparedStatement select = null;

    // Create lazily.
    PreparedStatement update = null;

    int userId = user.getUserID();

    boolean justCreated = false;

    byte[] key = null;
    try {
      // TODO:  consider having UserManager returning secure_hash_key.
      // TODO:  We have similar logic in several places for creating secure_hash_key just-in-time.
      // D.R.Y. this out.  Sorry I couldn't resist using this cliche :)

      select = c.prepareStatement("SELECT secure_hash_key FROM dat_user_account WHERE object_id=?");

      select.setInt(1, userId);
      ResultSet rs = select.executeQuery();
      if (!rs.next()) {
        LogMessageGen lmg = new LogMessageGen();
        lmg.setSubject("dat_user_account row not found");
        lmg.param(LoggingConsts.USER_ID, userId);
        // possible that the user simply disappeared by the time we got here.
        m_logCategory.warn(lmg.toString());
      } else {
        key = rs.getBytes(1);
        if (key == null || key.length == 0) {
          // hash key not found; create one on the fly.
          update =
              c.prepareStatement("UPDATE dat_user_account SET secure_hash_key=? WHERE object_id=?");
          key = createNewRandomKey();
          update.setBytes(1, key);
          update.setInt(2, userId);
          int ct = update.executeUpdate();
          if (ct != 1) {
            LogMessageGen lmg = new LogMessageGen();
            lmg.setSubject("Unable to update dat_user_account.secure_hash_key");
            lmg.param(LoggingConsts.USER_ID, userId);
            m_logCategory.error(lmg.toString());
          } else {
            justCreated = true;
          }
        } // needed to set key.
      } // user found
    } finally {
      DbUtils.safeClose(select);
      DbUtils.safeClose(update);
    }
    return new SecureHashKeyResult(key, justCreated);
  }
 static {
   try {
     Class.forName("com.mysql.jdbc.Driver");
     con = DriverManager.getConnection("jdbc:mysql:///hospital", "root", "root");
     pa = con.prepareStatement("insert into patient values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?) ");
     pd = con.prepareStatement("delete from patient where pid=? ");
     pl = con.prepareStatement("select * from patient order by pid");
     pps = con.prepareStatement("select * from patient where pid=? ");
     pps1 = con.prepareStatement("select * from patient where plnm= ?");
   } catch (Exception e) {
     System.out.println(e);
   }
 }
  public void doSQL() throws Exception {
    Class.forName("org.sqlite.JDBC");
    Connection conn = DriverManager.getConnection("jdbc:sqlite:test.db");
    Statement stat = conn.createStatement();
    stat.executeUpdate("drop table if exists people;");
    stat.executeUpdate("create table people (name, occupation);");
    PreparedStatement prep = conn.prepareStatement("insert into people values (?, ?);");

    prep.setString(1, "Gandhi");
    prep.setString(2, "politics");
    prep.addBatch();
    prep.setString(1, "Turing");
    prep.setString(2, "computers");
    prep.addBatch();
    prep.setString(1, "Wittgenstein");
    prep.setString(2, "smartypants");
    prep.addBatch();

    conn.setAutoCommit(false);
    prep.executeBatch();
    conn.setAutoCommit(true);

    ResultSet rs = stat.executeQuery("select * from people;");
    while (rs.next()) {
      System.out.println("name = " + rs.getString("name"));
      System.out.println("job = " + rs.getString("occupation"));
    }
    rs.close();
    conn.close();
  }
  public int update(ValueObject obj) throws SQLException {

    if ((obj instanceof PT_KICA_ERR_LOGEntity) == false) {
      throw new SQLException("DAO 에러(1): PT_KICA_ERR_LOG : update() ");
    }
    PT_KICA_ERR_LOGEntity entity = (PT_KICA_ERR_LOGEntity) obj;

    Connection conn = null;
    PreparedStatement ps = null;
    int result = 0;

    StringBuffer sb = new StringBuffer();
    sb.append("update PT_KICA_ERR_LOG  set ")
        .append("U_D_FLAG = ")
        .append(toDB(entity.getU_D_FLAG()))
        .append(",")
        .append("YYYYMMDD = ")
        .append(toDB(entity.getYYYYMMDD()))
        .append(",")
        .append("TRANSHOUR = ")
        .append(toDB(entity.getTRANSHOUR()))
        .append(",")
        .append("FILENAME = ")
        .append(toDB(entity.getFILENAME()))
        .append(",")
        .append("ERRLOG = ")
        .append(toDB(entity.getERRLOG()))
        .append(",")
        .append("RESULT_FLAG = ")
        .append(toDB(entity.getRESULT_FLAG()))
        .append(",")
        .append("UPD_DT = ")
        .append(toDB(entity.getUPD_DT()))
        .append(",")
        .append("INS_DT = ")
        .append(toDB(entity.getINS_DT()))
        .append(" where  1=1 ");

    sb.append(" and SEQ = ").append(toDB(entity.getSEQ()));

    KJFLog.sql(sb.toString());

    try {

      conn = this.getConnection();
      ps = conn.prepareStatement(sb.toString());

      int i = 1;

      result = ps.executeUpdate();

    } catch (SQLException e) {
      throw e;
    } finally {
      if (ps != null) ps.close();
      this.release(conn);
    }

    return result;
  }
Example #6
0
  /** Returns event_id of newly created logging entry. */
  private Integer logEvent(IClient client, String eventText, InputStream logFileSteam, Connection c)
      throws SQLException {
    PreparedStatement stmt = null;
    int id = 0;
    try {
      long longId = getSequenceId("seq_client_log_event_ids", c);
      if (longId > Integer.MAX_VALUE) {
        // dat_client_log_events.event_id is a 32 bit integer numeric type.

        // this is a bad problem; ensure it's logged; don't depend on whoever's catching
        // the exception.
        m_logger.log("seq_client_log_event_ids overflowed").error();
        throw new IllegalStateException("seq_client_log_event_ids overflowed.");
      }
      id = (int) longId;
      stmt =
          c.prepareStatement(
              "insert into dat_client_log_events "
                  + "(event_id, customer_id, user_id, event_time, description, has_log_file) "
                  + "values "
                  + "(?, ?, ?, current_timestamp, ?, ?)");

      stmt.setInt(1, id);
      stmt.setInt(2, client.getCustomerId());
      stmt.setInt(3, client.getUserId());
      stmt.setString(4, eventText);
      stmt.setInt(5, logFileSteam == null ? 0 : 1);
      if (stmt.executeUpdate() != 1) {
        throw new SQLException("Can't insert client event for " + client);
      }
    } finally {
      DbUtils.safeClose(stmt);
    }
    return new Integer(id);
  }
  public int delete(ValueObject obj) throws SQLException {

    if ((obj instanceof PT_KICA_ERR_LOGEntity) == false) {
      throw new SQLException("DAO 에러(1): PT_KICA_ERR_LOG : delete() ");
    }
    PT_KICA_ERR_LOGEntity entity = (PT_KICA_ERR_LOGEntity) obj;

    Connection conn = null;
    PreparedStatement ps = null;
    int result = 0;

    StringBuffer sb = new StringBuffer();
    sb.append("delete from PT_KICA_ERR_LOG  where  1=1")
        .append(" and SEQ = ")
        .append(toDB(entity.getSEQ()));

    KJFLog.sql(sb.toString());

    try {

      conn = this.getConnection();
      ps = conn.prepareStatement(sb.toString());

      result = ps.executeUpdate();

    } catch (SQLException e) {
      throw e;
    } finally {
      if (ps != null) ps.close();
      this.release(conn);
    }

    return result;
  }
  static void task1() throws FileNotFoundException, IOException, SQLException {
    // Read Input
    System.out.println("Task1 Started...");
    BufferedReader br = new BufferedReader(new FileReader(inputFile));
    br.readLine();
    String task1Input = br.readLine();
    br.close();
    double supportPercent =
        Double.parseDouble(task1Input.split(":")[1].split("=")[1].split("%")[0].trim());
    if (supportPercent >= 0) {
      System.out.println("Task1 Support Percent :" + supportPercent);
      // Prepare query
      String task1Sql =
          "select  temp.iname,(temp.counttrans/temp2.uniquetrans)*100 as percent"
              + " from (select i.itemname iname,count(t.transid) counttrans from trans t, items i"
              + " where i.itemid = t.itemid group by i.itemname having count(t.transid)>=(select count(distinct transid)*"
              + supportPercent / 100
              + " from trans)"
              + ") temp , (select count(distinct transid) uniquetrans from trans) temp2 order by percent";

      PreparedStatement selTask1 = con.prepareStatement(task1Sql);
      ResultSet rsTask1 = selTask1.executeQuery();

      BufferedWriter bw = new BufferedWriter(new FileWriter("system.out.1"));
      while (rsTask1.next()) {
        bw.write("{" + rsTask1.getString(1) + "}, s=" + rsTask1.getDouble(2) + "%");
        bw.newLine();
      }
      rsTask1.close();
      bw.close();
      System.out.println("Task1 Completed...\n");
    } else System.out.println("Support percent should be a positive number");
  }
  public void executeBatch(String sql, Object args[][]) throws SQLException, FileNotFoundException {
    PreparedStatement prep = connection.prepareStatement(sql);
    int i;
    for (Object[] a : args) {
      i = 1;
      for (Object b : a) {
        if (b instanceof String) {
          prep.setString(i, (String) b);
        }
        if (b instanceof Integer) {
          prep.setInt(i, (Integer) b);
        }
        if (b instanceof File) {
          FileInputStream fis = new FileInputStream((File) b);
          prep.setBinaryStream(i, fis);
        }
        if (b instanceof FileInputStream) {
          prep.setBinaryStream(i, (InputStream) b);
        }
        if (b instanceof BufferedImage) {
          byte[] buffer =
              ((DataBufferByte) ((BufferedImage) b).getRaster().getDataBuffer()).getData();
          InputStream ist = new ByteArrayInputStream(buffer);
          prep.setBinaryStream(i, ist);
        }
        i++;
      }
      prep.addBatch();
    }

    connection.setAutoCommit(false);
    prep.executeBatch();
    connection.setAutoCommit(true);
  }
  protected void processRequest(HttpServletRequest request, HttpServletResponse response)
      throws ServletException, IOException {
    response.setContentType("text/html;charset=UTF-8");
    PrintWriter out = response.getWriter();
    try {
      Class.forName("com.mysql.jdbc.Driver").newInstance();
      Connection con =
          DriverManager.getConnection(Utility.connection, Utility.username, Utility.password);

      int user_id = Integer.parseInt(request.getParameter("user_id"));
      int question_id = Integer.parseInt(request.getParameter("question_id"));
      int option = Integer.parseInt(request.getParameter("option"));

      System.out.println("uid: " + user_id + "\nquestion: " + question_id + "\noption: " + option);
      String str1 = "INSERT INTO VOTES(USER_ID, QUESTION_ID,OPTION_VOTED) VALUES (?,?,?)";
      PreparedStatement prep1 = con.prepareStatement(str1);
      prep1.setInt(1, user_id);
      prep1.setInt(3, option);
      prep1.setInt(2, question_id);
      prep1.execute();

      String str2 = "SELECT OPTION_" + option + " FROM ARCHIVE_VOTES WHERE QUESTION_ID=?";
      PreparedStatement prep2 = con.prepareStatement(str2);
      prep2.setInt(1, question_id);
      int count = 0;
      ResultSet rs2 = prep2.executeQuery();
      if (rs2.next()) {
        count = rs2.getInt("OPTION_" + option);
      }
      count++;
      String str3 = "UPDATE ARCHIVE_VOTES SET OPTION_" + option + "=? WHERE QUESTION_ID=?";
      PreparedStatement prep3 = con.prepareStatement(str3);
      prep3.setInt(1, count);
      prep3.setInt(2, question_id);
      prep3.executeUpdate();

      out.print("You Vote has been recorded! Thank you!");
      System.out.println(
          "Voted for question " + question_id + ", by user " + user_id + ", for option " + option);

    } catch (Exception e) {
      e.printStackTrace();
    } finally {
      out.close();
    }
  }
 /**
  * This internal method creates a new table in the database for storing XML responses. You can
  * override this method in a subclass if this generic method does not work with the database
  * server you are using, given that the following table columns are present:
  *
  * <ul>
  *   <li><code>id</code> - The primary key, which is used to identify cache entries (see {@link
  *       Cache#createCacheEntryName}
  *   <li><code>expiration_date</code> - A timestamp field for this cache entry's expiration date
  *   <li><code>response</code> - The actual response XML
  * </ul>
  *
  * If you choose to use a different schema in your table you'll most likely have to override the
  * other methods in this class, too.
  *
  * @throws SQLException When the generic SQL code in this method is not compatible with the
  *     database
  */
 protected void createTable() throws SQLException {
   PreparedStatement stmt =
       connection.prepareStatement(
           "CREATE TABLE "
               + tableName
               + " (id VARCHAR(200) PRIMARY KEY, expiration_date TIMESTAMP, response TEXT)");
   stmt.execute();
   stmt.close();
 }
 public void clear() {
   try {
     PreparedStatement stmt = connection.prepareStatement("DELETE FROM " + tableName);
     stmt.execute();
     stmt.close();
   } catch (SQLException e) {
     // ignore
   }
 }
 private void insertLog(HttpServletRequest req, Connection connection) throws SQLException {
   try (PreparedStatement stmt =
       connection.prepareStatement("INSERT INTO LOGGING (date,ip,url) VALUES (?,?,?)")) {
     stmt.setTimestamp(1, new Timestamp((new java.util.Date()).getTime()));
     stmt.setString(2, req.getRemoteAddr());
     stmt.setString(3, req.getRequestURI());
     stmt.executeUpdate();
   }
 }
 public void remove(String cacheEntryName) {
   try {
     PreparedStatement stmt =
         connection.prepareStatement("DELETE FROM " + tableName + " WHERE id = ?");
     stmt.setString(1, cacheEntryName);
     stmt.execute();
     stmt.close();
   } catch (SQLException e) {
     // ignore
   }
 }
  public int insert(ValueObject obj) throws SQLException {

    if ((obj instanceof PT_KICA_ERR_LOGEntity) == false) {
      throw new SQLException("DAO 에러(1): PT_KICA_ERR_LOG : insert() ");
    }
    PT_KICA_ERR_LOGEntity entity = (PT_KICA_ERR_LOGEntity) obj;

    Connection conn = null;
    PreparedStatement ps = null;
    int result = 0;

    StringBuffer sb = new StringBuffer();
    sb.append("insert into PT_KICA_ERR_LOG ")
        .append(" ( SEQ,U_D_FLAG,YYYYMMDD,TRANSHOUR,FILENAME,ERRLOG,RESULT_FLAG,UPD_DT,INS_DT ) ")
        .append(" values ( ")
        .append(toDB(entity.getSEQ()))
        .append(",")
        .append(toDB(entity.getU_D_FLAG()))
        .append(",")
        .append(toDB(entity.getYYYYMMDD()))
        .append(",")
        .append(toDB(entity.getTRANSHOUR()))
        .append(",")
        .append(toDB(entity.getFILENAME()))
        .append(",")
        .append(toDB(entity.getERRLOG()))
        .append(",")
        .append(toDB(entity.getRESULT_FLAG()))
        .append(",")
        .append(toDB(entity.getUPD_DT()))
        .append(",")
        .append(toDB(entity.getINS_DT()))
        .append(" ) ");

    KJFLog.sql(sb.toString());

    try {

      conn = this.getConnection();
      ps = conn.prepareStatement(sb.toString());

      int i = 1;

      result = ps.executeUpdate();

    } catch (SQLException e) {
      throw e;

    } finally {
      if (ps != null) ps.close();
      this.release(conn);
    }
    return result;
  }
Example #16
0
  public TableXRef(Connection c, String t, String match, String ret) throws SQLException {

    cxn = c;
    tableName = t;
    matchField = match;
    returnField = ret;

    ps =
        cxn.prepareStatement(
            "select " + returnField + " from " + tableName + " " + "where " + matchField + "=?");
  }
  public int update(ValueObject obj) throws SQLException {

    if ((obj instanceof PT_BBS_COM_FILESEntity) == false) {
      throw new SQLException("DAO 에러(1): PT_BBS_COM_FILES : update() ");
    }
    PT_BBS_COM_FILESEntity entity = (PT_BBS_COM_FILESEntity) obj;

    Connection conn = null;
    PreparedStatement ps = null;
    int result = 0;

    StringBuffer sb = new StringBuffer();
    sb.append("update PT_BBS_COM_FILES  set ")
        .append("CT_ID = ")
        .append(toDB(entity.getCT_ID()))
        .append(",")
        .append("BOARD_SEQ = ")
        .append(toDB(entity.getBOARD_SEQ()))
        .append(",")
        .append("FILE_REAL_NAME = ")
        .append(toDB(entity.getFILE_REAL_NAME()))
        .append(",")
        .append("FILE_SIZE = ")
        .append(toDB(entity.getFILE_SIZE()))
        .append(",")
        .append("UPD_DT = ")
        .append(toDB(entity.getUPD_DT()))
        .append(",")
        .append("DOWN_HIT = ")
        .append(toDB(entity.getDOWN_HIT()))
        .append(" where  1=1 ");

    sb.append(" and SYS_FILE_NAME = ").append(toDB(entity.getSYS_FILE_NAME()));

    KJFLog.sql(sb.toString());

    try {

      conn = this.getConnection();
      ps = conn.prepareStatement(sb.toString());

      int i = 1;

      result = ps.executeUpdate();

    } catch (SQLException e) {
      throw e;
    } finally {
      if (ps != null) ps.close();
      this.release(conn);
    }

    return result;
  }
Example #18
0
  Competition getDataFromDatabase(String arg, Properties properties) {
    Competition competition = new Competition();

    String getAthleteDataQuery =
        "SELECT athletes.name, athletes.dob, athletes.country_code, "
            + "results.race_100m, results.long_jump, results.shot_put, results.high_jump, results.race_400m, "
            + "results.hurdles_110m, results.discus_throw, results.pole_vault, results.javelin_throw, results.race_1500m "
            + "FROM athletes, results, competitions "
            + "WHERE athletes.id = results.athlete_id "
            + "AND results.competition_id = competitions.id "
            + "AND (competitions.id = ? OR competitions.name = ?)";

    Connection conn = null;

    try {
      String url = properties.getProperty("db.url");
      String username = properties.getProperty("db.username");
      String password = properties.getProperty("db.password");

      conn = DriverManager.getConnection(url, username, password);
      PreparedStatement athleteDataQuery = conn.prepareStatement(getAthleteDataQuery);
      athleteDataQuery.setString(1, arg);
      athleteDataQuery.setString(2, arg);
      ResultSet resultSet = athleteDataQuery.executeQuery();

      while (resultSet.next()) {
        Athlete athlete = parseAthlete(resultSet);
        if (athlete == null) {
          System.out.println(Error.ERROR_DB_ATHLETE_PARSING_FAILED.getErrorText());
          continue;
        }

        DecathlonEvents decathlonEvents = parseDecathlonEvents(resultSet);
        if (decathlonEvents == null) {
          System.out.println(Error.ERROR_DB_ATHLETE_RESULTS_PARSING_FAILED.getErrorText());
          continue;
        }
        athlete.setDecathlonEvent(decathlonEvents);

        competition.addAthlete(athlete);
      }
    } catch (SQLException e) {
      System.out.println(Error.ERROR_DB_CONNECTION.getErrorText());
    } finally {
      if (conn != null)
        try {
          conn.close();
        } catch (SQLException e) {
          System.out.println(Error.ERROR_DB_CLOSE.getErrorText());
        }
    }
    return competition;
  }
Example #19
0
  /**
   * Queries persons older than provided age.
   *
   * @param conn JDBC connection.
   * @param minAge Minimum age.
   * @throws SQLException In case of SQL error.
   */
  private static void queryPersons(Connection conn, int minAge) throws SQLException {
    PreparedStatement stmt = conn.prepareStatement("select name, age from Person where age >= ?");

    stmt.setInt(1, minAge);

    ResultSet rs = stmt.executeQuery();

    X.println(">>> Persons older than " + minAge + ":");

    while (rs.next())
      X.println(">>>     " + rs.getString("NAME") + " (" + rs.getInt("AGE") + " years old)");
  }
  public void doGet(HttpServletRequest request, HttpServletResponse response)
      throws ServletException, IOException {
    response.setContentType("text/html");

    PrintWriter out = response.getWriter();
    Connection conn = null;
    PreparedStatement pstmt = null;
    try {
      System.out.println("Enrollno: 130050131049");
      // STEP 2: Register JDBC driver
      Class.forName(JDBC_DRIVER);

      // STEP 3: Open a connection
      System.out.println("Connecting to a selected database...");
      conn = DriverManager.getConnection(DB_URL, USER, PASS);
      System.out.println("Connected database successfully...");

      // STEP 2: Executing query
      String sql = "SELECT * FROM logindetails WHERE name = ?";
      pstmt = conn.prepareStatement(sql);
      pstmt.setString(1, "Krut");

      ResultSet rs = pstmt.executeQuery();
      out.print("| <b>Name</b>| ");
      out.print("<b>Password</b>| ");
      out.println("</br>\n-------------------------------</br>");
      while (rs.next()) {
        out.println();
        out.print("| " + rs.getString(1));
        out.print("| " + rs.getString(2) + "|");
        out.println("</br>");
      }

    } catch (SQLException se) {
      // Handle errors for JDBC
      se.printStackTrace();
    } catch (Exception e) {
      // Handle errors for Class.forName
      e.printStackTrace();
    } finally {
      // finally block used to close resources
      try {
        if (pstmt != null) conn.close();
      } catch (SQLException se) {
      } // do nothing
      try {
        if (conn != null) conn.close();
      } catch (SQLException se) {
        se.printStackTrace();
      } // end finally try
    } // end try
  }
 public boolean contains(String cacheEntryName) {
   try {
     PreparedStatement stmt =
         connection.prepareStatement("SELECT id FROM " + tableName + " WHERE id = ?");
     stmt.setString(1, cacheEntryName);
     ResultSet result = stmt.executeQuery();
     boolean b = result.next();
     stmt.close();
     return b;
   } catch (SQLException e) {
     return false;
   }
 }
Example #22
0
  /**
   * Method called by the Form panel to delete existing data.
   *
   * @param persistentObject value object to delete
   * @return an ErrorResponse value object in case of errors, VOResponse if the operation is
   *     successfully completed
   */
  public Response deleteRecord(ValueObject persistentObject) throws Exception {
    PreparedStatement stmt = null;
    try {
      stmt = conn.prepareStatement("delete from DEMO4 where TEXT=?");
      DetailTestVO vo = (DetailTestVO) persistentObject;
      stmt.setString(1, vo.getStringValue());
      stmt.execute();

      // this instruction is no more needed: the grid has been linked to the Form (see Form.linkGrid
      // method...)
      //      gridFrame.reloadData();
      return new VOResponse(vo);
    } catch (SQLException ex) {
      ex.printStackTrace();
      return new ErrorResponse(ex.getMessage());
    } finally {
      try {
        stmt.close();
        conn.commit();
      } catch (SQLException ex1) {
      }
    }
  }
Example #23
0
  /**
   * Queries persons working in provided organization.
   *
   * @param conn JDBC
   * @param orgName Organization name.
   * @throws SQLException In case of SQL error.
   */
  private static void queryPersonsInOrganization(Connection conn, String orgName)
      throws SQLException {
    PreparedStatement stmt =
        conn.prepareStatement(
            "select p.name from Person p, Organization o where p.orgId = o.id and o.name = ?");

    stmt.setString(1, orgName);

    ResultSet rs = stmt.executeQuery();

    X.println(">>> Persons working in " + orgName + ":");

    while (rs.next()) X.println(">>>     " + rs.getString(1));
  }
 public static void main(String[] args) throws SQLException {
   // declare a connection by using Connection interface
   Connection connection = null;
   /* Create string of connection url within specified format with machine
   name, port number and database name. Here machine name id localhost
   and database name is mahendra. */
   String connectionURL = "jdbc:mysql://localhost:3306/mahendra";
   /*declare a resultSet that works as a table resulted by execute a specified
   sql query. */
   ResultSet rs = null;
   // Declare prepare statement.
   PreparedStatement psmnt = null;
   // declare FileInputStream object to store binary stream of given image.
   FileInputStream fis;
   try {
     // Load JDBC driver "com.mysql.jdbc.Driver"
     Class.forName("com.mysql.jdbc.Driver").newInstance();
     /* Create a connection by using getConnection() method that takes
     parameters of string type connection url, user name and password to
     connect to database. */
     connection = DriverManager.getConnection(connectionURL, "root", "root");
     // create a file object for image by specifying full path of image as parameter.
     File image = new File("C:/image.jpg");
     /* prepareStatement() is used for create statement object that is
     used for sending sql statements to the specified database. */
     psmnt =
         connection.prepareStatement(
             "insert into save_image(name, city, image, Phone) " + "values(?,?,?,?)");
     psmnt.setString(1, "mahendra");
     psmnt.setString(2, "Delhi");
     psmnt.setString(4, "123456");
     fis = new FileInputStream(image);
     psmnt.setBinaryStream(3, (InputStream) fis, (int) (image.length()));
     /* executeUpdate() method execute specified sql query. Here this query
     insert data and image from specified address. */
     int s = psmnt.executeUpdate();
     if (s > 0) {
       System.out.println("Uploaded successfully !");
     } else {
       System.out.println("unsucessfull to upload image.");
     }
   } // catch if found any exception during rum time.
   catch (Exception ex) {
     System.out.println("Found some error : " + ex);
   } finally {
     // close all the connections.
     connection.close();
     psmnt.close();
   }
 }
  protected void processRequest(HttpServletRequest request, HttpServletResponse response)
      throws ServletException, IOException {
    response.setContentType("text/html;charset=UTF-8");
    PrintWriter out = response.getWriter();
    try {
      Class.forName("com.mysql.jdbc.Driver").newInstance();
      Connection con =
          DriverManager.getConnection(Utility.connection, Utility.username, Utility.password);

      String email = request.getParameter("email_id");

      String number = "";
      boolean exists = false;
      String user_name = "";
      int user_id = -1;
      String str1 = "SELECT USER_ID,NAME,PHONE_NUMBER FROM USERS WHERE EMAIL_ID=?";
      PreparedStatement prep1 = con.prepareStatement(str1);
      prep1.setString(1, email);
      ResultSet rs1 = prep1.executeQuery();
      if (rs1.next()) {
        exists = true;
        user_id = rs1.getInt("USER_ID");
        user_name = rs1.getString("NAME");
        number = rs1.getString("PHONE_NUMBER");
      }
      int verification = 0;
      JSONObject data = new JSONObject();
      if (exists) {
        verification = (int) (Math.random() * 9535641 % 999999);
        System.out.println("Number " + number + "\nVerification: " + verification);
        SMSProvider.sendSMS(
            number, "Your One Time Verification Code for PeopleConnect Is " + verification);
      }

      data.put("user_name", user_name);
      data.put("user_id", user_id);
      data.put("verification_code", "" + verification);
      data.put("phone_number", number);

      String toSend = data.toJSONString();
      out.print(toSend);
      System.out.println(toSend);

    } catch (Exception e) {
      e.printStackTrace();
    } finally {
      out.close();
    }
  }
Example #26
0
  public static void main(String argv[]) {
    Connection con = null;
    PreparedStatement pstmt = null;
    InputStream fin = null;
    String url = "jdbc:oracle:thin:@localhost:1521:XE";
    String userid = "hr1";
    String passwd = "123456";
    String picName[] = {"iconPet29.jpg", "iconPet30.png"};
    int[] picPetId = {29, 30};
    int rowsUpdated = 0;
    try {
      con = DriverManager.getConnection(url, userid, passwd);

      for (int i = 0; i < picName.length; i++) {
        File pic = new File("WebContent/images", picName[i]); // �۹���|- picFrom
        // ������|- Ĵ�p:
        // File pic = new File("x:\\aa\\bb\\picFrom", picName);
        long flen = pic.length();
        fin = new FileInputStream(pic);
        pstmt = con.prepareStatement("UPDATE PET SET icon = ? WHERE ID = ?");
        pstmt.setBinaryStream(1, fin, (int) flen); // void
        // pstmt.setBinaryStream(int
        // parameterIndex,
        // InputStream x,
        // int length)
        // throws
        // SQLException
        pstmt.setInt(2, picPetId[i]);
        rowsUpdated += pstmt.executeUpdate();
      }

      System.out.print("Changed " + rowsUpdated);

      if (1 == rowsUpdated) System.out.println(" row.");
      else System.out.println(" rows.");

      fin.close();
      pstmt.close();

    } catch (Exception e) {
      e.printStackTrace();
    } finally {
      try {
        con.close();
      } catch (SQLException e) {
      }
    }
  }
Example #27
0
 @Override
 public String getUsername(byte[] encodedKey) {
   String b64key = Base64.getEncoder().encodeToString(encodedKey);
   try {
     try (PreparedStatement preparedStatement =
         conn.prepareStatement("select name from users where publickey = ? limit 1")) {
       preparedStatement.setString(1, b64key);
       ResultSet resultSet = preparedStatement.executeQuery();
       boolean next = resultSet.next();
       if (!next) return "";
       return resultSet.getString(1);
     }
   } catch (SQLException sqle) {
     throw new IllegalStateException(sqle);
   }
 }
 @Override
 public void deleteOperation(int operationId) throws OperationManagementDAOException {
   PreparedStatement stmt = null;
   try {
     super.deleteOperation(operationId);
     Connection connection = OperationManagementDAOFactory.getConnection();
     stmt = connection.prepareStatement("DELETE DM_POLICY_OPERATION WHERE OPERATION_ID=?");
     stmt.setInt(1, operationId);
     stmt.executeUpdate();
   } catch (SQLException e) {
     throw new OperationManagementDAOException(
         "Error occurred while deleting operation metadata", e);
   } finally {
     OperationManagementDAOUtil.cleanupResources(stmt);
   }
 }
Example #29
0
  public static void main(String[] args) {
    BD bd = new BD();
    String lsTable = "pays";
    String lsContenu = "";
    lsContenu += "<?xml version='1.0' encoding='utf-8'?>\n";
    lsContenu += "<!-- " + lsTable + "Document.xml -->\n";
    lsContenu += "<" + lsTable + ">\n";

    String lsEnfant = lsTable.substring(0, lsTable.length() - 1);

    try {
      bd.seConnecter("cours", "root", "alonso");
      Connection lcn = bd.getConnexion();
      PreparedStatement lpst =
          lcn.prepareStatement("SELECT * FROM pays"); // paysSelect() ou villesSelect()
      ResultSet lrs = lpst.executeQuery();

      ResultSetMetaData lrsmd = lrs.getMetaData();

      int liCols = lrsmd.getColumnCount();

      while (lrs.next()) {
        lsContenu += "\t<" + lsEnfant + " ";

        for (int i = 1; i <= liCols; i++) {
          lsContenu += lrsmd.getColumnName(i) + "='" + lrs.getString(i) + "' ";
        }

        lsContenu += "/>\n";
      }

      lsContenu += "</" + lsTable + ">";

      System.out.println(lsContenu);

      FileWriter lfwFichier = new FileWriter(lsTable + ".xml"); // pays.csv ou villes.csv
      BufferedWriter lbwBuffer = new BufferedWriter(lfwFichier);
      lbwBuffer.write(lsContenu);
      lbwBuffer.close();
      lfwFichier.close();

      bd.seDeconnecter();

    } catch (Exception e) {
      System.out.println("Erreor : ");
    }
  }
 public boolean isExpired(String cacheEntryName) {
   try {
     PreparedStatement stmt =
         connection.prepareStatement("SELECT expiration_date FROM " + tableName + " WHERE id = ?");
     stmt.setString(1, cacheEntryName);
     ResultSet result = stmt.executeQuery();
     if (result.next()) {
       Timestamp timestamp = result.getTimestamp("expiration_date");
       long expirationDate = timestamp.getTime();
       stmt.close();
       return expirationDate < System.currentTimeMillis();
     }
   } catch (SQLException e) {
     // ignore
   }
   return false;
 }