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
  /**
   * Inserts a new non-existing row into the DailyPrices table
   *
   * @param symbolId ID of entry in symbol table.
   * @param dt Date of the price information
   * @param open Opening price on the day
   * @param close Closing price on the day
   * @param high high price of the day
   * @param low low price of the day
   * @throws SQLException
   */
  public void insertDailyPrice(
      int symbolId, Date dt, double open, double close, double high, double low)
      throws SQLException {
    // ensure price doesn't exist already.
    PreparedStatement psCheck =
        MyConnection.prepareStatement(
            "select count(*) rowcount from DailyPrices where Date=? and SymbolId=?;");
    psCheck.setString(1, dt.toString());
    psCheck.setInt(2, symbolId);
    ResultSet rsCheck = psCheck.executeQuery();
    if (rsCheck.getInt("rowcount") > 0) {
      return;
    }

    PreparedStatement ps =
        MyConnection.prepareStatement(
            "insert into DailyPrices(SymbolId, Date, Open, Close, High, Low) values(?,?,?,?,?,?);");
    ps.setInt(1, symbolId);
    ps.setString(2, dt.toString());
    ps.setDouble(3, open);
    ps.setDouble(4, close);
    ps.setDouble(5, high);
    ps.setDouble(6, low);
    ps.executeUpdate();
  }
Exemple #3
0
 public boolean set(int sequence, String message) throws IOException {
   Connection connection = null;
   PreparedStatement insert = null;
   ResultSet rs = null;
   try {
     connection = dataSource.getConnection();
     insert = connection.prepareStatement(SQL_INSERT_MESSAGE);
     int offset = setSessionIdParameters(insert, 1);
     insert.setInt(offset++, sequence);
     insert.setString(offset, message);
     insert.execute();
   } catch (SQLException ex) {
     if (connection != null) {
       PreparedStatement update = null;
       try {
         update = connection.prepareStatement(SQL_UPDATE_MESSAGE);
         update.setString(1, message);
         int offset = setSessionIdParameters(update, 2);
         update.setInt(offset, sequence);
         boolean status = update.execute();
         return !status ? update.getUpdateCount() > 0 : false;
       } catch (SQLException e) {
         throw (IOException) new IOException(e.getMessage()).initCause(e);
       } finally {
         JdbcUtil.close(sessionID, update);
       }
     }
   } finally {
     JdbcUtil.close(sessionID, rs);
     JdbcUtil.close(sessionID, insert);
     JdbcUtil.close(sessionID, connection);
   }
   return true;
 }
Exemple #4
0
  public void reset() throws IOException {
    cache.reset();
    Connection connection = null;
    PreparedStatement deleteMessages = null;
    PreparedStatement updateTime = null;
    try {
      connection = dataSource.getConnection();
      deleteMessages = connection.prepareStatement(SQL_DELETE_MESSAGES);
      setSessionIdParameters(deleteMessages, 1);
      deleteMessages.execute();

      updateTime = connection.prepareStatement(SQL_UPDATE_SESSION);
      updateTime.setTimestamp(
          1, new Timestamp(Calendar.getInstance(TimeZone.getTimeZone("UTC")).getTimeInMillis()));
      updateTime.setInt(2, getNextTargetMsgSeqNum());
      updateTime.setInt(3, getNextSenderMsgSeqNum());
      setSessionIdParameters(updateTime, 4);
      updateTime.execute();
    } catch (SQLException e) {
      throw (IOException) new IOException(e.getMessage()).initCause(e);
    } catch (IOException e) {
      throw e;
    } finally {
      JdbcUtil.close(sessionID, deleteMessages);
      JdbcUtil.close(sessionID, updateTime);
      JdbcUtil.close(sessionID, connection);
    }
  }
  public void insertChartUpdate(String doi, int lastNcites, Connection con) throws SQLException {

    PreparedStatement getUpdateStmt = con.prepareStatement(DEF_GET_UPDATE_QUERY);
    getUpdateStmt.setString(1, doi);
    ResultSet rs = getUpdateStmt.executeQuery();
    boolean isNew = true;
    if (rs.first()) {
      isNew = false;
    }
    rs.close();
    getUpdateStmt.close();

    PreparedStatement stmt = null;
    if (isNew) {
      stmt = con.prepareStatement(DEF_INS_UPDATE_STMT);
      stmt.setString(1, doi);
      stmt.setInt(2, lastNcites);
    } else {
      stmt = con.prepareStatement(DEF_UPDATE_UPDATE_STMT);
      stmt.setInt(1, lastNcites);
      stmt.setString(2, doi);
    }
    stmt.executeUpdate();
    stmt.close();
  } // - insertChartUpdate
Exemple #6
0
  public void insert(Connection con, JUser user) throws Exception {
    PreparedStatement pstmt = null;
    ResultSet rs = null;
    String sql = null;
    sql = "update t_card set valid=1 where member_id=?";
    pstmt = con.prepareStatement(sql);
    pstmt.setString(1, this.member_id);
    pstmt.executeUpdate();
    pstmt.close();

    this.card_id = com.gemway.util.ID.getIDObject("default").create();

    sql =
        "insert into t_card "
            + "  (card_id,   regdate, card_no,    member_id, card_class, op_type, moneyPaid, "
            + "   paidOfPro, con_no,  class_name, markup,    created,    createrName,valid)"
            + " values(?,?,?,?,?,?,?,  ?,?,?,?,?,?,0)";
    pstmt = con.prepareStatement(sql);
    int colIndex = 1;
    pstmt.setString(colIndex++, this.card_id);
    pstmt.setDate(colIndex++, this.regdate);
    pstmt.setString(colIndex++, this.card_no);
    pstmt.setString(colIndex++, this.member_id);
    pstmt.setString(colIndex++, this.card_class);
    pstmt.setInt(colIndex++, this.op_type);
    pstmt.setString(colIndex++, this.moneypaid);
    pstmt.setString(colIndex++, this.paidofpro);
    pstmt.setString(colIndex++, this.con_no);
    pstmt.setString(colIndex++, this.class_name);
    pstmt.setString(colIndex++, this.markup);
    pstmt.setTimestamp(colIndex++, new java.sql.Timestamp(System.currentTimeMillis()));
    pstmt.setString(colIndex++, user.getUserName());
    pstmt.executeUpdate();
    pstmt.close();
  }
Exemple #7
0
 public static int delTeam(Connection con, String team_code) throws java.sql.SQLException {
   PreparedStatement pstmt = null;
   java.sql.ResultSet rs = null;
   String sql = null;
   int delCounts = 0;
   int teamMember = 0;
   if (team_code == null) return delCounts;
   sql = " select count(*) as teamMembers  from t_teammember " + " where tm_team_id = ?";
   pstmt = con.prepareStatement(sql);
   pstmt.setString(1, Team.getTeamIdbyCode(con, team_code));
   rs = pstmt.executeQuery();
   if (rs.next()) {
     teamMember = rs.getInt("teamMembers");
   }
   rs.close();
   if (teamMember == 0) {
     sql = " delete from t_team " + " where team_code = ?";
     pstmt = con.prepareStatement(sql);
     pstmt.setString(1, team_code);
     pstmt.executeUpdate();
     delCounts++;
   }
   pstmt.close();
   return delCounts;
 }
Exemple #8
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;
  }
  /**
   * fügt einen neuen Kontakt zur Kontaktliste hinzu
   *
   * @param besitzer Der Besitzer der Kontaktliste
   * @param kontakt Der Kontakt, der hinzugefügt werden soll
   */
  public void kontaktZurKontaktListeHinzufuegen(String besitzer, String kontakt) {
    int kNr = bestimmeBNrBenutzer(kontakt);
    int bNr = bestimmeBNrBenutzer(besitzer);

    try {
      // überprüft, ob der Konktakt noch nicht in der Kontaktliste ist
      boolean gefunden = false;
      ResultSet rueckgabewert = null;
      String query1 = "select count(*) from kontaktliste where besitzer = ? and kontakt = ?";
      PreparedStatement anweisung1 = con.prepareStatement(query1);

      anweisung1.setString(1, besitzer);
      anweisung1.setString(1, kontakt);
      rueckgabewert = anweisung1.executeQuery();

      // werdet den Rückgabewert aus
      while (rueckgabewert.next()) {
        gefunden = rueckgabewert.getBoolean(1);
      }

      if (!gefunden) {
        // fügt den Kontakt zur Kontakliste hinzu
        String query = "insert into kontaktliste (besitzer, kontakt) values(?, ?);";
        PreparedStatement anweisung = con.prepareStatement(query);

        anweisung.setInt(1, bNr);
        anweisung.setInt(2, kNr);
        anweisung.executeUpdate();
      }
    } catch (SQLException e) {
      System.err.println(e.getClass().getName() + ": " + e.getMessage());
      System.exit(1);
    }
  }
 public void deleteUser(String username) {
   if (isReadOnly()) {
     // Reject the operation since the provider is read-only
     throw new UnsupportedOperationException();
   }
   Connection con = null;
   PreparedStatement pstmt = null;
   boolean abortTransaction = false;
   try {
     // Delete all of the users's extended properties
     con = DbConnectionManager.getTransactionConnection();
     pstmt = con.prepareStatement(DELETE_USER_PROPS);
     pstmt.setString(1, username);
     pstmt.execute();
     pstmt.close();
     // Delete the actual user entry
     pstmt = con.prepareStatement(DELETE_USER);
     pstmt.setString(1, username);
     pstmt.execute();
   } catch (Exception e) {
     Log.error(e);
     abortTransaction = true;
   } finally {
     DbConnectionManager.closeTransactionConnection(pstmt, con, abortTransaction);
   }
 }
  @Test
  public void testUpsertValuesWithExpression() throws Exception {
    long ts = nextTimestamp();
    ensureTableCreated(getUrl(), "IntKeyTest", null, ts - 2);
    Properties props = new Properties();
    props.setProperty(
        PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 1)); // Execute at timestamp 1
    Connection conn = DriverManager.getConnection(PHOENIX_JDBC_URL, props);
    String upsert = "UPSERT INTO IntKeyTest VALUES(-1)";
    PreparedStatement upsertStmt = conn.prepareStatement(upsert);
    int rowsInserted = upsertStmt.executeUpdate();
    assertEquals(1, rowsInserted);
    upsert = "UPSERT INTO IntKeyTest VALUES(1+2)";
    upsertStmt = conn.prepareStatement(upsert);
    rowsInserted = upsertStmt.executeUpdate();
    assertEquals(1, rowsInserted);
    conn.commit();
    conn.close();

    props.setProperty(
        PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 2)); // Execute at timestamp 1
    conn = DriverManager.getConnection(PHOENIX_JDBC_URL, props);
    String select = "SELECT i FROM IntKeyTest";
    ResultSet rs = conn.createStatement().executeQuery(select);
    assertTrue(rs.next());
    assertEquals(-1, rs.getInt(1));
    assertTrue(rs.next());
    assertEquals(3, rs.getInt(1));
    assertFalse(rs.next());
  }
Exemple #12
0
  public ArrayList<passport> getPassport() {
    connect();
    ArrayList<passport> list = new ArrayList<passport>();

    try {

      if (this.getOrd().equals("select")) {
        pstmt = con.prepareStatement(SQL);
        ResultSet rs = pstmt.executeQuery();

        while (rs.next()) {
          passport p = new passport();
          p.setName(rs.getString("name"));
          p.setEng_name(rs.getString("eng_name"));
          p.setBirth(rs.getString("birth"));
          p.setExdate(rs.getString("exdate"));
          list.add(p);
          result++;
        }
        this.setResult(result);
        rs.close();
      } else {

        pstmt = con.prepareStatement(SQL);
        result = pstmt.executeUpdate();
        this.setResult(result);
      }
    } catch (SQLException e) {
      e.printStackTrace();
    } finally {
      disconnect();
    }
    return list;
  }
Exemple #13
0
  @Override
  public void delete(Integer deptno) {
    int updateCount_EMPs = 0;

    Connection con = null;
    PreparedStatement pstmt = null;

    try {

      Class.forName(driver);
      con = DriverManager.getConnection(url, userid, passwd);

      // 1●設定於 pstm.executeUpdate()之前
      con.setAutoCommit(false);

      // 先刪除員工
      pstmt = con.prepareStatement(DELETE_EMPs);
      pstmt.setInt(1, deptno);
      updateCount_EMPs = pstmt.executeUpdate();
      // 再刪除部門
      pstmt = con.prepareStatement(DELETE_DEPT);
      pstmt.setInt(1, deptno);
      pstmt.executeUpdate();

      // 2●設定於 pstm.executeUpdate()之後
      con.commit();
      con.setAutoCommit(true);
      System.out.println("刪除部門編號" + deptno + "時,共有員工" + updateCount_EMPs + "人同時被刪除");

      // Handle any driver errors
    } catch (ClassNotFoundException e) {
      throw new RuntimeException("Couldn't load database driver. " + e.getMessage());
      // Handle any SQL errors
    } catch (SQLException se) {
      if (con != null) {
        try {
          // 3●設定於當有exception發生時之catch區塊內
          con.rollback();
        } catch (SQLException excep) {
          throw new RuntimeException("rollback error occured. " + excep.getMessage());
        }
      }
      throw new RuntimeException("A database error occured. " + se.getMessage());
    } finally {
      if (pstmt != null) {
        try {
          pstmt.close();
        } catch (SQLException se) {
          se.printStackTrace(System.err);
        }
      }
      if (con != null) {
        try {
          con.close();
        } catch (Exception e) {
          e.printStackTrace(System.err);
        }
      }
    }
  }
  public void insertVisitant(VisitantBean visitant) {

    String insertSql = query.insertVisitant_InsertQuery;

    String selectSql = query.insertVisitant_SelectSql;

    try {
      connection = DBManager.getConnection();

      preparedStatement = connection.prepareStatement(selectSql);

      preparedStatement.setInt(1, visitant.getPassNum());

      resultSet = preparedStatement.executeQuery();

      if (!resultSet.next()) {

        preparedStatement = connection.prepareStatement(insertSql);

        preparedStatement.setTimestamp(1, visitant.getInTime());
        preparedStatement.setInt(2, visitant.getPassNum());
        preparedStatement.setString(3, visitant.getVisitantName());
        preparedStatement.setString(4, visitant.getCompany());
        preparedStatement.setString(5, visitant.getPhone());
        preparedStatement.setInt(6, 1);

        preparedStatement.executeUpdate();
      }
    } catch (SQLException e) {
      System.out.println(query.insertVisitant_Exception);
      e.printStackTrace();
    } finally {
      DBManager.close(resultSet, connection, preparedStatement);
    }
  } // end insertVisitant
Exemple #15
0
 private void loadCache() throws SQLException, IOException {
   Connection connection = null;
   PreparedStatement query = null;
   PreparedStatement insert = null;
   ResultSet rs = null;
   try {
     connection = dataSource.getConnection();
     query = connection.prepareStatement(SQL_GET_SEQNUMS);
     setSessionIdParameters(query, 1);
     rs = query.executeQuery();
     if (rs.next()) {
       cache.setCreationTime(SystemTime.getUtcCalendar(rs.getTimestamp(1)));
       cache.setNextTargetMsgSeqNum(rs.getInt(2));
       cache.setNextSenderMsgSeqNum(rs.getInt(3));
     } else {
       insert = connection.prepareStatement(SQL_INSERT_SESSION);
       int offset = setSessionIdParameters(insert, 1);
       insert.setTimestamp(offset++, new Timestamp(cache.getCreationTime().getTime()));
       insert.setInt(offset++, cache.getNextTargetMsgSeqNum());
       insert.setInt(offset, cache.getNextSenderMsgSeqNum());
       insert.execute();
     }
   } finally {
     JdbcUtil.close(sessionID, rs);
     JdbcUtil.close(sessionID, query);
     JdbcUtil.close(sessionID, insert);
     JdbcUtil.close(sessionID, connection);
   }
 }
 @Override
 public Grupo crear_grupo(String nombre) throws SQLException, GrupoAlreadyExistException {
   Connection connection = null;
   PreparedStatement stmt = null;
   String id = null;
   try {
     Grupo grupo = obtener_ID_grupo_por_NOMBRE(nombre);
     if (grupo != null) throw new GrupoAlreadyExistException();
     connection = Database.getConnection();
     stmt = connection.prepareStatement(GrupoDAOQuery.UUID);
     ResultSet rs = stmt.executeQuery();
     if (rs.next()) id = rs.getString(1);
     else throw new SQLException();
     connection.setAutoCommit(false);
     stmt.close();
     stmt = connection.prepareStatement(GrupoDAOQuery.CREAR_GRUPO);
     stmt.setString(1, id);
     stmt.setString(2, nombre);
     stmt.executeUpdate();
   } catch (SQLException e) {
     throw e;
   } finally {
     if (stmt != null) stmt.close();
     if (connection != null) {
       connection.setAutoCommit(true);
       connection.close();
     }
   }
   return obtener_NOMBRE_por_ID(id);
 }
Exemple #17
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());
    }
  }
Exemple #18
0
  public static UserBean isRegister(UserBean bean) {
    con = Connector.getConnection();
    String createTable =
        "create table if not exists users("
            + "id int not null auto_increment primary key,"
            + "firstName varchar(100),"
            + "lastName varchar(100),"
            + "email varchar(100),"
            + "password varchar(50),"
            + "gender varchar(7)"
            + ");";

    String sql = "insert into users(firstName,lastName,email,password,gender) values(?,?,?,?,?);";
    String sqlId = "select * from users where email = ? and password = ?;";
    String sql3 = "insert into isRead(id) values(?);";

    try {
      PreparedStatement st = con.prepareStatement(createTable);
      st.executeUpdate();
      st.close();
    } catch (Exception e) {
      e.printStackTrace();
    }
    try {
      PreparedStatement st = con.prepareStatement(sql);
      st.setString(1, bean.getFirstName());
      st.setString(2, bean.getLastName());
      st.setString(3, bean.getEmail());
      st.setString(4, bean.getPassword());
      st.setString(5, bean.getGender());

      int i = st.executeUpdate();

      PreparedStatement st2 = con.prepareStatement(sqlId);
      st2.setString(1, bean.getEmail());
      st2.setString(2, bean.getPassword());
      if (i != 0) {
        ResultSet rs = st2.executeQuery();
        boolean more = rs.next();

        if (more) {
          bean.setFirstName(rs.getString("firstName"));
          bean.setLastName(rs.getString("lastName"));
          bean.setId(rs.getString("id"));
          bean.setIsValid(true);

          PreparedStatement st3 = con.prepareStatement(sql3);
          st3.setString(1, bean.getId());
          st3.executeUpdate();
        }
        bean.setIsValid(true);
      }
      st.close();
      st2.close();
      con.close();
    } catch (Exception e) {
      e.printStackTrace();
    }
    return bean;
  }
Exemple #19
0
  protected void setUp() throws Exception {
    Class.forName("org.hsqldb.jdbcDriver");
    conn = DriverManager.getConnection("jdbc:hsqldb:mem:jxls", "sa", "");
    Statement stmt = conn.createStatement();
    stmt.executeUpdate(CREATE_DEPARTMENT_TABLE);
    stmt.executeUpdate(CREATE_EMPLOYEE_TABLE);
    PreparedStatement insertDep = conn.prepareStatement(INSERT_DEPARTMENT);
    PreparedStatement insertStmt = conn.prepareStatement(INSERT_EMPLOYEE);
    int k = 1;
    int n = 1;
    for (int i = 0; i < depNames.length; i++) {
      String depName = depNames[i];
      insertDep.setString(1, depName);
      insertDep.setInt(2, n++);
      insertDep.executeUpdate();
      for (int j = 0; j < employeeNames[i].length; j++) {
        insertStmt.setString(1, employeeNames[i][j]);
        insertStmt.setInt(2, employeeAges[i][j]);
        insertStmt.setDouble(3, employeePayments[i][j]);
        insertStmt.setDouble(4, employeeBonuses[i][j]);
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-mm-dd");
        insertStmt.setDate(5, new Date(sdf.parse(employeeBirthDates[i][j]).getTime()));
        SimpleDateFormat tdf = new SimpleDateFormat("yyyy-mm-dd hh:mm:ss.SSS");
        insertStmt.setTimestamp(6, new Timestamp(tdf.parse(birthTimestamps[i][j]).getTime()));
        insertStmt.setInt(7, n - 1);
        insertStmt.setInt(8, k++);
        insertStmt.executeUpdate();
      }
    }

    stmt.close();
    insertStmt.close();
  }
  /**
   * 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 {
      EmpVO vo = (EmpVO) persistentObject;

      // delete from WORKING_DAYS...
      stmt = conn.prepareStatement("delete from WORKING_DAYS where EMP_CODE=?");
      stmt.setString(1, vo.getEmpCode());
      stmt.execute();
      stmt.close();

      // delete from EMP...
      stmt = conn.prepareStatement("delete from EMP where EMP_CODE=?");
      stmt.setString(1, vo.getEmpCode());
      stmt.execute();
      gridFrame.reloadData();

      frame.getGrid().clearData();

      return new VOResponse(vo);
    } catch (SQLException ex) {
      ex.printStackTrace();
      return new ErrorResponse(ex.getMessage());
    } finally {
      try {
        stmt.close();
        conn.commit();
      } catch (SQLException ex1) {
      }
    }
  }
Exemple #21
0
 public static boolean saveDetalle(Detalle a) {
   try {
     conn = ds.getConnection();
     stmt = conn.prepareStatement("call savedetalle(?,?,?,?,?)");
     Date fech = new Date(a.getFecha().getTime());
     stmt.setInt(1, a.getFormaDePago().getId());
     stmt.setInt(2, a.getUsuario().getIdUsuario());
     stmt.setDate(3, fech);
     stmt.setDouble(4, a.getTotal());
     stmt.setTime(5, new java.sql.Time(a.getHora().toDateTimeToday().getMillis()));
     rs = stmt.executeQuery();
     while (rs.next()) {
       a.setId(rs.getInt(1));
     }
     stmt = conn.prepareStatement("call saveproductosventa(?,?)");
     for (Producto x : a.getProductos()) {
       stmt.setInt(1, a.getId());
       stmt.setInt(2, x.getIdProducto());
       stmt.execute();
     }
     rs.close();
     stmt.close();
     conn.close();
   } catch (SQLException e) {
     e.printStackTrace();
   }
   return false;
 }
  @Test
  public void testUpsertDateValues() throws Exception {
    long ts = nextTimestamp();
    Date now = new Date(System.currentTimeMillis());
    ensureTableCreated(getUrl(), TestUtil.PTSDB_NAME, null, ts - 2);
    Properties props = new Properties();
    props.setProperty(
        PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 1)); // Execute at timestamp 1
    Connection conn = DriverManager.getConnection(PHOENIX_JDBC_URL, props);
    String dateString = "1999-01-01 02:00:00";
    PreparedStatement upsertStmt =
        conn.prepareStatement(
            "upsert into ptsdb(inst,host,date) values('aaa','bbb',to_date('" + dateString + "'))");
    int rowsInserted = upsertStmt.executeUpdate();
    assertEquals(1, rowsInserted);
    upsertStmt =
        conn.prepareStatement(
            "upsert into ptsdb(inst,host,date) values('ccc','ddd',current_date())");
    rowsInserted = upsertStmt.executeUpdate();
    assertEquals(1, rowsInserted);
    conn.commit();

    props.setProperty(
        PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 2)); // Execute at timestamp 1
    conn = DriverManager.getConnection(PHOENIX_JDBC_URL, props);
    String select = "SELECT date,current_date() FROM ptsdb";
    ResultSet rs = conn.createStatement().executeQuery(select);
    Date then = new Date(System.currentTimeMillis());
    assertTrue(rs.next());
    Date date = DateUtil.parseDate(dateString);
    assertEquals(date, rs.getDate(1));
    assertTrue(rs.next());
    assertTrue(rs.getDate(1).after(now) && rs.getDate(1).before(then));
    assertFalse(rs.next());
  }
Exemple #23
0
 public void save(Connection con, JUser juser) throws Exception {
   PreparedStatement pstmt = null;
   java.sql.Timestamp now = new java.sql.Timestamp(System.currentTimeMillis());
   String sql = null;
   int idx = 1;
   if (JUtil.convertNull(this.getGroup_id()).equals("")) {
     sql =
         " insert into t_ym_book(group_id, group_name, inquire_date, group_no, contact,"
             + "	 contact_phone, contact_handset, contact_fax, countries, countries_name, group_type, "
             + "	 memo, company_id, zxgw_id, zxgw_name, take_out, creater, createrName, created) "
             + " values(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) ";
     pstmt = con.prepareStatement(sql);
     this.setGroup_id(JUtil.createUNID());
     pstmt.setString(idx++, this.getGroup_id());
     pstmt.setString(idx++, this.getGroup_name());
     pstmt.setDate(idx++, this.getInquire_date());
     pstmt.setString(idx++, this.getGroup_no());
     pstmt.setString(idx++, this.getContact());
     pstmt.setString(idx++, this.getContact_phone());
     pstmt.setString(idx++, this.getContact_handset());
     pstmt.setString(idx++, this.getContact_fax());
     pstmt.setString(idx++, this.getCountries());
     pstmt.setString(idx++, this.getCountries_name());
     pstmt.setInt(idx++, this.getGroup_type());
     pstmt.setString(idx++, this.getMemo());
     pstmt.setString(idx++, this.getCompany_id());
     pstmt.setString(idx++, this.getZxgw_id());
     pstmt.setString(idx++, this.getZxgw_name());
     pstmt.setString(idx++, this.getTake_out());
     pstmt.setString(idx++, juser.getUserId());
     pstmt.setString(idx++, juser.getUserName());
     pstmt.setTimestamp(idx++, now);
     pstmt.executeUpdate();
     pstmt.close();
   } else {
     sql =
         " update t_ym_book set group_name = ?, inquire_date = ?, memo = ?, "
             + " group_no = ?, contact = ?, contact_phone = ?, contact_handset = ?, contact_fax = ?,"
             + " countries = ?, countries_name = ?, zxgw_id = ?, zxgw_name = ?, take_out = ? "
             + " where group_id = ? ";
     pstmt = con.prepareStatement(sql);
     pstmt.setString(idx++, this.getGroup_name());
     pstmt.setDate(idx++, this.getInquire_date());
     pstmt.setString(idx++, this.getMemo());
     pstmt.setString(idx++, this.getGroup_no());
     pstmt.setString(idx++, this.getContact());
     pstmt.setString(idx++, this.getContact_phone());
     pstmt.setString(idx++, this.getContact_handset());
     pstmt.setString(idx++, this.getContact_fax());
     pstmt.setString(idx++, this.getCountries());
     pstmt.setString(idx++, this.getCountries_name());
     pstmt.setString(idx++, this.getZxgw_id());
     pstmt.setString(idx++, this.getZxgw_name());
     pstmt.setString(idx++, this.getTake_out());
     pstmt.setString(idx++, this.getGroup_id());
     pstmt.executeUpdate();
     pstmt.close();
   }
 }
  public void init(int startIndex, int endIndex, String updateDate) throws SQLException {

    if (connection == null || connection.isClosed()) {
      connection = getConnection();
    }
    if (startIndex != 0 && endIndex != 0) {
      bibQuery =
          "SELECT * FROM OLE_DS_BIB_T WHERE BIB_ID BETWEEN "
              + startIndex
              + " AND "
              + endIndex
              + " ORDER BY BIB_ID";
    } else if (StringUtils.isNotEmpty(updateDate)) {
      updateDate = getDateStringForOracle(updateDate);
      bibQuery = "SELECT * FROM OLE_DS_BIB_T where DATE_UPDATED > '" + updateDate + "'";
    } else {
      bibQuery = "SELECT * FROM OLE_DS_BIB_T ORDER BY BIB_ID";
    }
    if (!isStaffOnly) {
      bibQuery = bibStaffOnly;
      holdingsQuery = holdingsQuery + staffOnlyHoldings;
      itemQuery = itemQuery + staffOnlyItem;
    }

    fetchCallNumberType();
    fetchReceiptStatus();
    fetchAuthenticationType();
    fetchItemType();
    fetchItemStatus();
    fetchStatisticalSearchCode();
    fetchExtentOfOwnershipType();

    bibConnection = getConnection();
    holdingsConnection = getConnection();
    itemConnection = getConnection();
    bibConnection.setAutoCommit(false);

    bibStatement =
        bibConnection.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
    if (dbVendor.equalsIgnoreCase("oracle")) {
      bibStatement.setFetchSize(1);
    } else if (dbVendor.equalsIgnoreCase("mysql")) {
      bibStatement.setFetchSize(Integer.MIN_VALUE);
    }

    bibResultSet = bibStatement.executeQuery(bibQuery);

    holdingsPreparedStatement = holdingsConnection.prepareStatement(holdingsQuery);

    itemPreparedStatement = itemConnection.prepareStatement(itemQuery);

    String insertQuery =
        "INSERT INTO OLE_DS_BIB_INFO_T(BIB_ID, BIB_ID_STR, TITLE, AUTHOR, PUBLISHER, ISXN) VALUES (?,?,?,?,?,?)";
    bibInsertPreparedStatement = connection.prepareStatement(insertQuery);

    String updateQuery =
        "UPDATE OLE_DS_BIB_INFO_T SET TITLE=?, AUTHOR=?, PUBLISHER=?, ISXN=?, BIB_ID_STR=? WHERE BIB_ID=?";
    bibUpdatePreparedStatement = connection.prepareStatement(updateQuery);
  }
Exemple #25
0
 @Test
 public void dbclose() throws SQLException {
   conn.prepareStatement("select ?;").setString(1, "Hello World");
   conn.prepareStatement("select null;").close();
   conn.prepareStatement("select null;").executeQuery().close();
   conn.prepareStatement("create table t (c);").executeUpdate();
   conn.prepareStatement("select null;");
 }
 private void prepareStatements(Connection connection) throws SQLException {
   createAccountStatement =
       connection.prepareStatement("INSERT INTO " + TABLE_NAME + " VALUES (?, 0)");
   findAccountStatement =
       connection.prepareStatement("SELECT * from " + TABLE_NAME + " WHERE NAME = ?");
   deleteAccountStatement =
       connection.prepareStatement("DELETE FROM " + TABLE_NAME + " WHERE name = ?");
 }
  @WebMethod(operationName = "update")
  public int update(
      @WebParam(name = "id") int id,
      @WebParam(name = "token") String token,
      @WebParam(name = "user-agent") String ua,
      @WebParam(name = "ip") String ip,
      @WebParam(name = "topic") String topic,
      @WebParam(name = "content") String content) {

    Connection conn = null;
    PreparedStatement ps = null;
    int res = -1;
    InformationToken it = new InformationToken();
    String email = it.getEmail(token, ua, ip);
    if (it.getStatus() != 200) {
      return -1 * it.getStatus();
    } else {
      try {
        // new com.mysql.jdbc.Driver();
        Class.forName("com.mysql.jdbc.Driver").newInstance();
        // conn =
        // DriverManager.getConnection("jdbc:mysql://localhost:3306/databasename?user=username&password=password");
        String connectionUrl = "jdbc:mysql://localhost:3306/stackexchange";
        String connectionUser = "******";
        String connectionPassword = "";
        conn = DriverManager.getConnection(connectionUrl, connectionUser, connectionPassword);
        ps = conn.prepareStatement("select name from user where email = ?");
        ps.setString(1, email);
        ResultSet rs = ps.executeQuery();
        rs.next();
        String name = rs.getString("name");
        ps =
            conn.prepareStatement(
                "update question set name = ?, email = ?, topic = ?, content = ? where id = ?;");
        ps.setString(1, name);
        ps.setString(2, email);
        ps.setString(3, topic);
        ps.setString(4, content);
        ps.setInt(5, id);
        res = ps.executeUpdate();
      } catch (ClassNotFoundException
          | InstantiationException
          | IllegalAccessException
          | SQLException e) {

      } finally {
        try {
          if (ps != null) ps.close();
        } catch (SQLException e) {
        }
        try {
          if (conn != null) conn.close();
        } catch (SQLException e) {
        }
      }
      return res;
    }
  }
  /**
   * Description of the Method
   *
   * @param db Description of the Parameter
   * @return Description of the Return Value
   * @throws SQLException Description of the Exception
   */
  public DependencyList processDependencies(Connection db) throws SQLException {
    DependencyList dependencyList = new DependencyList();
    // Check for orders associated with this customer product
    try {
      PreparedStatement pst =
          db.prepareStatement(
              "SELECT COUNT(DISTINCT(order_id)) as orderscount "
                  + "FROM customer_product_history "
                  + "WHERE customer_product_id = ? ");
      pst.setInt(1, this.getId());
      ResultSet rs = pst.executeQuery();
      if (rs.next()) {
        int orderscount = rs.getInt("orderscount");
        if (orderscount != 0) {
          Dependency thisDependency = new Dependency();
          thisDependency.setName("orders");
          thisDependency.setCount(orderscount);
          thisDependency.setCanDelete(true);
          dependencyList.add(thisDependency);
        }
      }
      rs.close();
      pst.close();
    } catch (SQLException e) {
      throw new SQLException(e.getMessage());
    }

    // check for quotes associated with this customer product
    try {
      PreparedStatement pst =
          db.prepareStatement(
              "SELECT COUNT(*) as quotescount FROM order_entry "
                  + "WHERE quote_id > -1 AND order_id IN "
                  + "(SELECT DISTINCT(order_id) "
                  + " FROM customer_product_history "
                  + " WHERE customer_product_id = ? )");
      pst.setInt(1, this.getId());
      ResultSet rs = pst.executeQuery();
      if (rs.next()) {
        int quotescount = rs.getInt("quotescount");
        if (quotescount != 0) {
          Dependency thisDependency = new Dependency();
          thisDependency.setName("quotes");
          thisDependency.setCount(quotescount);
          thisDependency.setCanDelete(true);
          dependencyList.add(thisDependency);
        }
      }
      rs.close();
      pst.close();
    } catch (SQLException e) {
      throw new SQLException(e.getMessage());
    }
    return dependencyList;
  }
  /**
   * Consulta as glosas do prestador passado em um determinado periodo
   *
   * @param strCdContrato - o codigo do contrato do prestado do qual se deseja obter as glosas
   * @param strNumPeriodo - o periodo de referencia do qual se deseja obter os glosas
   * @return um array de glosas do prestador fornecido como paramentro
   */
  public static final GlosaPrestador[] buscaGlosaPrest(String strCdContrato, String strNumPeriodo)
      throws Exception {

    Connection con = ConnectionPool.getConnection();
    GlosaPrestador[] glosas = null;
    PreparedStatement pst;
    ResultSet rset;
    int qtdeGlosas = 0;

    try {

      pst = con.prepareStatement(CONSULTA_GLOSA);
      pst.setString(1, strCdContrato);
      pst.setString(2, strNumPeriodo);
      rset = pst.executeQuery();

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

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

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

      glosas = new GlosaPrestador[qtdeGlosas];

      pst = con.prepareStatement(CONSULTA_GLOSA);
      pst.setString(1, strCdContrato);
      pst.setString(2, strNumPeriodo);
      rset = pst.executeQuery();

      qtdeGlosas = 0;

      while (rset.next()) {
        glosas[qtdeGlosas] = montaGlosaPrestador(rset);
        qtdeGlosas++;
      } // 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 glosas;
  } // consultaGlosaPrest()
  /** 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);
  }