Esempio n. 1
1
  public void eliminarConfiguracion(int usuario) throws DAOException {
    log.info("eliminarConfiguracion(int usuario)");
    Connection cons = null;
    PreparedStatement stmt = null;
    try {
      String query = "DELETE FROM cv_servicio_usuario WHERE IDUSUARIO=?";
      cons = dataSource.getConnection();
      stmt = cons.prepareStatement(query);
      stmt.setInt(1, usuario);
      stmt.executeUpdate();

      query =
          "insert into cv_servicio_usuario(idusuario,idservicio,columna,posicion,visible,estado) "
              + "select ?,idservicio,columna,posicion,usuario_visible,usuario_estado "
              + "from cv_servicio_maestro";
      stmt = cons.prepareStatement(query);
      stmt.setInt(1, usuario);
      stmt.executeUpdate();
    } catch (SQLException e) {
      log.info(e.toString());
      throw new DAOException(e.toString());
    } catch (Exception e) {
      log.info(e.toString());
      throw new DAOException(e.toString());
    } finally {
      closeStatement(stmt);
      closeConnection(cons);
    }
  }
Esempio n. 2
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);
     }
   }
 }
Esempio n. 3
0
  public int deleteDevice(int dID) {
    int rowsDeleted = 0;
    try {
      Connection connect = conn.use();

      String SQLcheck = "SELECT * FROM device WHERE deviceID = " + dID + ";";

      PreparedStatement check = connect.prepareStatement(SQLcheck);

      if (check.execute()) {
        String SQL = "DELETE FROM device WHERE deviceID = " + dID + ";";

        PreparedStatement psDelete = connect.prepareStatement(SQL);

        rowsDeleted = psDelete.executeUpdate();
        psDelete.close();
        conn.release(connect);
      }
    } catch (SQLException ex) {
      System.out.println("Error in delete device: ");
      Logger.getLogger(DeviceBroker.class.getName()).log(Level.SEVERE, null, ex);
    }

    return rowsDeleted;
  }
Esempio n. 4
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 {
      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) {
      }
    }
  }
Esempio n. 5
0
  /**
   * @param conn
   * @param fingerprint
   * @param sampleID
   * @return
   * @throws SQLException
   */
  private static void insertSampleSet(Connection conn, Fingerprint fingerprint, Integer sampleID)
      throws SQLException {
    if (fingerprint.getSampleSetID() == null) {
      /*
       * Insert whole new SampleSetID.
       */
      String query = "INSERT INTO `SampleSets`(`SampleID`) VALUES(?);";
      PreparedStatement insertSampleSet =
          conn.prepareStatement(query, Statement.RETURN_GENERATED_KEYS);

      insertSampleSet.setInt(1, sampleID);
      insertSampleSet.execute();

      ResultSet rs = insertSampleSet.getGeneratedKeys();
      if (rs.next()) {
        fingerprint.setSampleSetID(rs.getInt(1));
      }
      rs.close();
      insertSampleSet.close();
    } else {
      /*
       * Insert new SampleID for existing SampleSetID.
       */
      String query = "INSERT INTO `SampleSets`(`SampleSetID`,`SampleID`) VALUES(?, ?);";
      PreparedStatement insertSampleSet = conn.prepareStatement(query);

      insertSampleSet.setInt(1, fingerprint.getSampleSetID());
      insertSampleSet.setInt(2, sampleID);
      insertSampleSet.execute();

      insertSampleSet.close();
    }
  }
Esempio n. 6
0
  private void btnSuperUserSubmitActionPerformed(
      java.awt.event.ActionEvent evt) { // GEN-FIRST:event_btnSuperUserSubmitActionPerformed
    String IDNO = txtIDNO.getText();
    String OldPassword = txtOldPassword.getText();
    String NewPassword = txtNewPassword.getText();
    String ConfirmPassword = txtConfirmPassword.getText();
    String sqlQueryPwd = "select Password from super_user where IDNO=? and Password=?";
    String sqlQueryP =
        "update super_user set Password='******' where IDNO='" + IDNO + "'";
    try {
      PreparedStatement ps = x.prepareStatement(sqlQueryPwd);
      PreparedStatement ps1 = x.prepareStatement(sqlQueryP);
      ps.setString(1, IDNO);
      ps.setString(2, OldPassword);
      ResultSet rs = ps.executeQuery();

      if (rs.next() && NewPassword.equals(ConfirmPassword)) {
        ps1.execute(sqlQueryP);
        JOptionPane.showMessageDialog(null, "Password successfully changed!");
        dispose();
        Login L = new Login();
        L.setVisible(true);
      } else {
        JOptionPane.showMessageDialog(null, "Invalid! Cross check!");
      }
      rs.close();
    } catch (Exception e) {
      JOptionPane.showMessageDialog(null, e);
    }
  } // GEN-LAST:event_btnSuperUserSubmitActionPerformed
Esempio n. 7
0
  public void addAgent(Agent agent) {

    try {
      conn = JDBC_Conn.getConnection();
      String sql = "insert into agents values(?,?,?,?,?,?,?,?)";
      stmt = conn.prepareStatement(sql);
      stmt.setString(1, agent.getAgent_id());
      stmt.setString(2, agent.getPhone_number());
      stmt.setString(3, agent.getEmail());
      stmt.setString(4, agent.getFirst_nm());
      stmt.setString(5, agent.getLast_nm());
      stmt.setDate(6, new java.sql.Date(agent.getDob().getTime()));
      stmt.setString(7, agent.getZip());
      stmt.setString(8, agent.getGender());
      stmt.executeUpdate();

      sql = "insert into position values(?,?)";
      stmt = conn.prepareStatement(sql);
      stmt.setString(1, agent.getAgent_id());
      stmt.setString(2, agent.getPosition());
      stmt.executeUpdate();

    } catch (Exception e) {
      e.printStackTrace();
      throw new DAOException();
    } finally {
      JDBC_Conn.releaseConnection(conn, stmt, rs);
    }
  }
Esempio n. 8
0
  public void checkPOStatus(String poNumber) throws SQLException {

    /**
     * For retrieving the details of purchase order using poNumber corresponding to particular
     * vendorNumber
     *
     * @param poNumber
     * @return
     * @throws SQLException
     */
    DBConnection dbConnection = new DBConnection();
    Connection connection = dbConnection.getConnection();

    PreparedStatement preparedStatement =
        connection.prepareStatement(Selection.selectPoProductFromPo);
    logger.debug("retrieving the details of purchase order using poNumber" + poNumber);
    preparedStatement.setString(1, poNumber);

    ResultSet resultSet = preparedStatement.executeQuery();
    if (resultSet.next()) {

    } else {

      PreparedStatement preparedStatement2 = connection.prepareStatement(Selection.updatePoStatus);
      logger.debug("updating status CLOSED of PO for particular PO_NO" + poNumber);
      preparedStatement2.setString(1, "CLOSED");
      preparedStatement2.setString(2, poNumber);
      preparedStatement2.execute();
    }
  }
  public void save() throws SQLException {
    try (Connection connection = DbHelper.getConnection()) {
      if (id == -1) {
        Contact.LOGGER.debug("Adding new contact: {}", this);
        final String sql = "INSERT INTO contacts(name, contacts) VALUES(?, ?)";
        try (PreparedStatement pstmt =
            connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS)) {
          pstmt.setString(1, name);
          pstmt.setString(2, contacts);
          pstmt.execute();

          try (final ResultSet rs = pstmt.getGeneratedKeys()) {
            rs.next();
            id = rs.getLong(1);
          }
        }
      } else {
        Contact.LOGGER.debug("Updating existing contact: {}", this);
        final String sql = "UPDATE contacts SET name = ?, contacts = ? WHERE id = ?";
        try (PreparedStatement pstmt = connection.prepareStatement(sql)) {
          pstmt.setString(1, name);
          pstmt.setString(2, contacts);
          pstmt.setLong(3, id);
          pstmt.execute();
        }
      }
    }
  }
Esempio n. 10
0
  public static Post addMessage(Post post, Connection con) {
    try {

      DateFormat dateFormat = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
      Date date = new Date();
      java.sql.Date sqlDate = new java.sql.Date(date.getTime());
      //            Connection con = Database.getConnection();
      PreparedStatement p =
          con.prepareStatement("INSERT INTO messages (message,created,author) VALUES(?,?,?)");

      p.setString(1, post.getContent());
      p.setDate(2, sqlDate);
      p.setString(3, post.getContent());
      boolean ok = p.execute();

      p =
          con.prepareStatement(
              "select id,created from  messages where message = ? and created = ? and author = ?");
      p.setString(1, post.getContent());
      p.setDate(2, sqlDate);
      p.setString(3, post.getContent());
      ResultSet resultSet = p.executeQuery();
      resultSet.next();
      post.setId(resultSet.getInt("id"));
      post.setCreated(resultSet.getDate("created"));
      resultSet.close();
      p.close();
      return post;
    } catch (Exception e) {
      e.printStackTrace();
      throw new RuntimeException();
    }
  }
  /**
   * 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 String removeItem(String[] idList) throws SQLException {

    String sql1 = "delete from sellitem where itemid = ?;";
    PreparedStatement stmnt1 = connection.prepareStatement(sql1);
    String sql2 = "delete from solditem where itemid = ?;";
    PreparedStatement stmnt2 = connection.prepareStatement(sql2);
    String sql3 = "delete from userhistory where itemid = ?;";
    PreparedStatement stmnt3 = connection.prepareStatement(sql3);
    String sql4 = "delete from item where id = ?;";
    PreparedStatement stmnt4 = connection.prepareStatement(sql4);
    for (int i = 0; i < idList.length; i++) {

      stmnt1.setInt(1, Integer.parseInt(idList[i]));
      stmnt1.executeUpdate();

      stmnt2.setInt(1, Integer.parseInt(idList[i]));
      stmnt2.executeUpdate();

      stmnt3.setInt(1, Integer.parseInt(idList[i]));
      stmnt3.executeUpdate();

      stmnt4.setInt(1, Integer.parseInt(idList[i]));
      stmnt4.executeUpdate();
    }
    connection.close();
    return "Remove succeed!";
  }
Esempio n. 13
0
  @Override
  protected void doPost(HttpServletRequest req, HttpServletResponse resp)
      throws ServletException, IOException {
    // This resets the vote counts of the options of a poll
    int pollIdToReset = Integer.parseInt(req.getParameter("id"));

    try {
      Connection c =
          DriverManager.getConnection(
              Constants.DATABASE_URL, Constants.MYSQL_USERNAME, Constants.MYSQL_PASSWORD);

      String setNewPollCount = "update poll set total_count=0 where poll_id =?";
      PreparedStatement pstmt4 = c.prepareStatement(setNewPollCount);
      pstmt4.setInt(1, pollIdToReset);
      pstmt4.executeUpdate();

      String setNewPageCount = "update poll_option set vote_count=0 where poll_id =?";
      PreparedStatement pstmt = c.prepareStatement(setNewPageCount);
      pstmt.setInt(1, pollIdToReset);
      pstmt.executeUpdate();

      String deleteIps = "delete from poll_ip where poll_id =?";
      PreparedStatement pstmt2 = c.prepareStatement(deleteIps);
      pstmt2.setInt(1, pollIdToReset);
      pstmt2.executeUpdate();

      resp.sendRedirect("./Admin");

      c.close();
    } catch (SQLException e) {
      throw new ServletException(e);
    }
  }
Esempio n. 14
0
  @Override
  protected void do_addOrUpdatePlayer(BBPlayerInfo pi) {
    Connection conn = null;
    PreparedStatement ps = null;
    try {
      conn = ConnectionManager.getConnection();
      if (conn == null) return;
      if (pi.getNew() && getUserFromDB(pi.getName()) == null) {
        /*String statement = "IF EXISTS (SELECT \"name\" FROM \""+getTableName()+"\" WHERE \"name\" = ?) THEN"
        	+ " UPDATE \""+getTableName()+"\" SET \"flags\" = ? WHERE \"name\" = ?;"
        	+ " ELSE "
        	+ " INSERT INTO \""+getTableName()+"\" (name,flags) VALUES(?,?)"
        	+ " END IF;";
        ps = conn.prepareStatement(statement);*/
        ps = conn.prepareStatement("INSERT INTO " + getTableName() + " (name,flags) VALUES (?,?)");

        ps.setString(1, pi.getName());
        ps.setInt(2, pi.getFlags());

      } else {
        ps = conn.prepareStatement("UPDATE " + getTableName() + " SET flags = ? WHERE id=?");
        ps.setInt(1, pi.getFlags());
        ps.setInt(2, pi.getID());
      }
      BBLogging.debug(ps.toString());
      ps.executeUpdate();
      conn.commit();
    } catch (SQLException e) {
      BBLogging.severe("Can't update the user `" + pi.getName() + "`.", e);
    } finally {
      ConnectionManager.cleanup("BBUsersMySQL.do_addOrUpdatePlayer", conn, ps, null);
    }
  }
Esempio n. 15
0
 public static boolean deleteFromInvite(int meetingId, int id) {
   // 通过meeting id和User id撤销对某人的邀请
   Connection connection = ConnectionFactory.getConnection();
   if (connection == null) return false;
   try {
     String sql = "select account from user where userid=" + id + "";
     PreparedStatement pStatement = connection.prepareStatement(sql);
     ResultSet set = pStatement.executeQuery(sql);
     while (set.next() == true) {
       String sql1 =
           "delete from invite where meetingid="
               + meetingId
               + " and account='"
               + set.getString(1)
               + "'";
       PreparedStatement ps = connection.prepareStatement(sql1);
       int rs = ps.executeUpdate(sql1);
       if (rs == 1) return true;
     }
   } catch (SQLException e) {
     // TODO Auto-generated catch block
     e.printStackTrace();
   }
   return false;
 }
Esempio n. 16
0
 public int updateAssignToNode(String node, int i, int numNodes, long maxtime)
     throws DatabaseException {
   if (__log.isDebugEnabled())
     __log.debug(
         "updateAsssignToNode node=" + node + " " + i + "/" + numNodes + " maxtime=" + maxtime);
   Connection con = null;
   PreparedStatement ps = null;
   try {
     con = getConnection();
     if (_dialect == Dialect.SQLSERVER) {
       ps = con.prepareStatement(UPGRADE_JOB_SQLSERVER);
     } else if (_dialect == Dialect.DB2) {
       ps = con.prepareStatement(UPGRADE_JOB_DB2);
     } else if (_dialect == Dialect.SYBASE) {
       ps = con.prepareStatement(UPGRADE_JOB_SYBASE);
     } else if (_dialect == Dialect.SYBASE12) {
       ps = con.prepareStatement(UPGRADE_JOB_SYBASE12);
     } else {
       ps = con.prepareStatement(UPGRADE_JOB_DEFAULT);
     }
     ps.setString(1, node);
     ps.setInt(2, numNodes);
     ps.setInt(3, i);
     ps.setLong(4, maxtime);
     return ps.executeUpdate();
   } catch (SQLException se) {
     throw new DatabaseException(se);
   } finally {
     close(ps);
     close(con);
   }
 }
Esempio n. 17
0
  @Override
  protected void createSkipColumnTable() throws Exception {
    Connection con = getDataSource().getConnection();

    String stmt =
        "create table "
            + DB2TestUtil.SCHEMA_QUOTED
            + ".\"skipcolumn\" (\"fid\" int generated always as identity (start with 0, increment by 1),"
            + "\"id\" int ,"
            + " \"geom\" DB2GSE.ST_GEOMETRY, "
            + "\"weirdproperty\" XML, "
            + "\"name\" varchar(255), "
            + "primary key (\"fid\"))";
    con.prepareStatement(stmt).execute();
    DB2Util.executeRegister(DB2TestUtil.SCHEMA, "auto", "geom", DB2TestUtil.SRSNAME, con);

    con.prepareStatement(
            "INSERT INTO "
                + DB2TestUtil.SCHEMA_QUOTED
                + ".\"skipcolumn\" "
                + "(\"id\",\"geom\",\"weirdproperty\",\"name\")  "
                + "VALUES (0, db2gse.st_GeomFromText('POINT(0 0)', "
                + DB2TestUtil.SRID
                + "), null, 'GeoTools')")
        .execute();
    con.close();
  }
Esempio n. 18
0
  private void saveBuildings(int building_id, String customers, Connection conn) throws Exception {
    PreparedStatement psDelete = null;
    PreparedStatement psAdd = null;
    try {
      psDelete =
          conn.prepareStatement("delete from maps.building_to_customers where building_id=?");
      psDelete.setInt(1, building_id);
      psDelete.executeUpdate();
      if (customers.length() > 0) {
        String cusIds[] = customers.split(",");
        psAdd =
            conn.prepareStatement(
                "insert into maps.building_to_customers (building_id,cusid) values (?,?)");

        for (String cusId : cusIds) {
          psAdd.setInt(1, building_id);
          psAdd.setInt(2, new Integer(cusId));
          psAdd.addBatch();
        }
        psAdd.executeBatch();
      }

    } finally {
      try {
        psDelete.close();
      } catch (Exception e2) {
        // TODO: handle exception
      }
      try {
        psAdd.close();
      } catch (Exception e2) {
        // TODO: handle exception
      }
    }
  }
Esempio n. 19
0
  public String addBillDetails(String poNumber, double totalAmount, double totalQuantity)
      throws SQLException {
    /**
     * For retrieving the details of purchase order using poNumber corresponding to particular
     * vendorNumber
     *
     * @param poNumber
     * @return
     * @throws SQLException
     */
    DBConnection dbConnection = new DBConnection();
    Connection connection = dbConnection.getConnection();
    String billNo = "";

    PreparedStatement ps = connection.prepareStatement("select 'BILL'||BILL_SEQ.nextval FROM dual");

    ResultSet rs = ps.executeQuery();
    if (rs.next()) {

      billNo = rs.getString(1);
      System.out.println(billNo);
      logger.debug("generating bill no" + billNo);
    }

    PreparedStatement preparedStatement =
        connection.prepareStatement("INSERT INTO BILL_DETAILS VALUES(?,?,?,?)");
    logger.debug("inserting values into Bill_Details" + billNo);
    preparedStatement.setString(1, billNo);
    preparedStatement.setString(2, poNumber);
    preparedStatement.setDouble(3, totalAmount);
    preparedStatement.setDouble(4, totalQuantity);
    preparedStatement.execute();
    return billNo;
  }
Esempio n. 20
0
  public static String saveSearchResult(String uuid, String building_ids, int srid, Connection conn)
      throws Exception {
    PreparedStatement psDelete = null;
    PreparedStatement psAdd = null;
    PreparedStatement psGet = null;
    ResultSet rs = null;
    try {
      psDelete =
          conn.prepareStatement(
              "delete from cust_search_result where uu_id=? or create_time<now() - cast('1 day' as interval)");
      psDelete.setString(1, uuid);
      psDelete.executeUpdate();
      if (building_ids.length() > 0) {

        psAdd =
            conn.prepareStatement(
                "insert into cust_search_result (buid,uu_id) select distinct building_id,? from maps.building_to_customers "
                    + "where building_id = ANY(string_to_array(?,',')::int[])");

        psAdd.setString(1, uuid);
        psAdd.setString(2, building_ids);
        psAdd.executeUpdate();
        psGet =
            conn.prepareStatement(
                "select ST_AsText(ST_transform(ST_Buffer(ST_Envelope (ST_Collect(the_geom)),100),?)) from buildings b"
                    + " where buid= ANY(string_to_array(?,',')::int[])");

        psGet.setInt(1, srid);
        psGet.setString(2, building_ids);
        rs = psGet.executeQuery();
        if (rs.next()) {
          String res = rs.getString(1);
          return res;
        }
      }
      return "";

    } finally {
      try {
        psDelete.close();
      } catch (Exception e2) {
        // TODO: handle exception
      }
      try {
        psAdd.close();
      } catch (Exception e2) {
        // TODO: handle exception
      }
      try {
        psGet.close();
      } catch (Exception e2) {
        // TODO: handle exception
      }
      try {
        rs.close();
      } catch (Exception e2) {
        // TODO: handle exception
      }
    }
  }
Esempio n. 21
0
  public void updateAgent(Agent agent) {
    try {
      conn = JDBC_Conn.getConnection();
      String sql =
          "update agents set phone_number=?, email=?, first_nm=?, last_nm=?, dob=?, zip=?, gender=? where agent_id=?";
      stmt = conn.prepareStatement(sql);
      stmt.setString(1, agent.getPhone_number());
      stmt.setString(2, agent.getEmail());
      stmt.setString(3, agent.getFirst_nm());
      stmt.setString(4, agent.getLast_nm());
      stmt.setDate(5, new java.sql.Date(agent.getDob().getTime()));
      stmt.setString(6, agent.getZip());
      stmt.setString(7, agent.getGender());
      stmt.setString(8, agent.getAgent_id());
      stmt.executeUpdate();

      sql = "update position set position = ? where agent_id=?";
      stmt = conn.prepareStatement(sql);
      stmt.setString(1, agent.getPosition());
      stmt.setString(2, agent.getAgent_id());
      stmt.executeUpdate();

    } catch (Exception e) {
      e.printStackTrace();
      throw new DAOException();
    } finally {
      JDBC_Conn.releaseConnection(conn, stmt, rs);
    }
  }
Esempio n. 22
0
 public void copyData(Connection conn) throws SQLException {
   ResultSet rs = null;
   PreparedStatement stmt = null;
   PreparedStatement stmt2 = null;
   String q2 = "insert into modmodlinks2 (pModID,cModID,courseID) values (?,?,?)";
   try {
     String q =
         "select parentModID,childModID,courseID from modmodulelinks order by parentModId,childModID";
     stmt = conn.prepareStatement(q);
     rs = stmt.executeQuery();
     while (rs.next()) {
       int p = rs.getInt(1);
       int c = rs.getInt(2);
       int crs = rs.getInt(3);
       stmt2 = conn.prepareStatement(q2);
       stmt2.setInt(1, p);
       stmt2.setInt(2, c);
       stmt2.setInt(3, crs);
       System.out.println("Insert p=" + p + " c=" + c + " crs=" + crs);
       stmt2.executeUpdate();
       stmt2.close();
     }
   } finally {
     if (stmt != null) stmt.close();
     if (rs != null) rs.close();
   }
 }
Esempio n. 23
0
 /**
  * Method updateOwnerInDB.
  *
  * @param clan Clan
  */
 private void updateOwnerInDB(Clan clan) {
   _owner = clan;
   Connection con = null;
   PreparedStatement statement = null;
   try {
     con = DatabaseFactory.getInstance().getConnection();
     statement =
         con.prepareStatement("UPDATE clan_data SET hasFortress=0 WHERE hasFortress=? LIMIT 1");
     statement.setInt(1, getId());
     statement.execute();
     DbUtils.close(statement);
     if (clan != null) {
       statement =
           con.prepareStatement("UPDATE clan_data SET hasFortress=? WHERE clan_id=? LIMIT 1");
       statement.setInt(1, getId());
       statement.setInt(2, getOwnerId());
       statement.execute();
       clan.broadcastClanStatus(true, false, false);
     }
   } catch (Exception e) {
     _log.error("", e);
   } finally {
     DbUtils.closeQuietly(con, statement);
   }
 }
Esempio n. 24
0
 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);
   }
 }
Esempio n. 25
0
  /**
   * Create a CharacteristicBean.
   *
   * @param conn A connection to the database.
   * @param num_samples The number of samples in the database.
   * @param value The value of this sample.
   * @return
   * @throws SQLException
   */
  private static CharacteristicBean getCharacteristicBean(
      Connection conn, int num_samples, String dbname, Long value) throws SQLException {
    CharacteristicBean chrbean = new CharacteristicBean();

    PreparedStatement getCount;
    String querystr = "SELECT COUNT(*) FROM `Samples` WHERE `" + dbname + "`";
    if (value != null) {
      chrbean.setValue(value.toString());
      querystr += " = ?;";

      getCount = conn.prepareStatement(querystr);
      getCount.setLong(1, value);
    } else {
      chrbean.setValue(NO_JAVASCRIPT);

      querystr += " IS NULL;";
      getCount = conn.prepareStatement(querystr);
    }

    ResultSet rs = getCount.executeQuery();
    rs.next();
    int count = rs.getInt(1);
    rs.close();
    chrbean.setInX(((double) num_samples) / ((double) count));
    chrbean.setBits(Math.abs(Math.log(chrbean.getInX()) / Math.log(2)));

    return chrbean;
  }
  /**
   * @param date
   * @param vipType 黄钻为0,红钻为1
   * @param type 活跃用户为0,新增用户为1
   * @param list
   * @throws SQLException
   */
  private void saveRecord(Date date, int vipType, int type, int[][] list) throws SQLException {
    PreparedStatement pstmt;
    for (int isYearVip = 0; isYearVip < 2; isYearVip++) {
      for (int vipLevel = 0; vipLevel < 8; vipLevel++) {

        pstmt =
            con.prepareStatement(
                "delete from vip_level where date=? and type=? and is_year_vip=? and vip_level=? and vip_type=?");
        pstmt.setDate(1, new java.sql.Date(date.getTime()));
        pstmt.setInt(2, type);
        pstmt.setInt(3, isYearVip);
        pstmt.setInt(4, vipLevel);
        pstmt.setInt(5, vipType);
        pstmt.execute();
        pstmt.close();

        pstmt =
            con.prepareStatement(
                "insert into vip_level(date,type,is_year_vip,vip_level,vip_type,count) values(?,?,?,?,?,?)");
        pstmt.setDate(1, new java.sql.Date(date.getTime()));
        pstmt.setInt(2, type);
        pstmt.setInt(3, isYearVip);
        pstmt.setInt(4, vipLevel);
        pstmt.setInt(5, vipType);
        pstmt.setInt(6, list[isYearVip][vipLevel]);
        pstmt.execute();
        pstmt.close();
      }
    }
  }
  public PostgresClientManager() {
    try {
      connection = DriverManager.getConnection(url, "postgres", "kuba22");
      statement = connection.createStatement();

      ResultSet rs = connection.getMetaData().getTables(null, null, null, null);
      boolean tableExists = false;

      while (rs.next()) {
        if (rs.getString("TABLE_NAME").equalsIgnoreCase("Client")) {
          tableExists = true;
          break;
        }
      }
      if (!tableExists) {
        statement.executeUpdate(createTable);
      }

      getAllClient = connection.prepareStatement("" + "Select * From Klient");
      deleteClient = connection.prepareStatement("" + "DELETE From Klient where name=?");
      getClient = connection.prepareStatement("" + "SELECT * From Klient where id=?");

    } catch (SQLException e) {

      e.printStackTrace();
    }
  }
Esempio n. 28
0
  @Override
  public int update(Object obj) {
    int result = 0;

    try {
      conn.setAutoCommit(false);

      pstmt =
          conn.prepareStatement(
              "update THEME_MESSAGE set NAME=?,EMAIL=?,IMAGE=?,TITLE=? "
                  + "where THEME_MESSAGE_ID=?");
      pstmt =
          conn.prepareStatement("update THEME_CONTENT set CONTENT=? " + "where THEME_MESSAGE_ID=?");

      pstmt.setString(1, bean.getName());
      pstmt.setString(2, bean.getEmail());
      pstmt.setString(3, bean.getImage());
      pstmt.setString(4, bean.getTitle());
      pstmt.setInt(5, bean.getId());
      pstmt.executeUpdate();

      pstmt.setCharacterStream(1, null);
      pstmt.setInt(2, bean.getId());
      pstmt.executeUpdate();

      conn.commit();
    } catch (SQLException ex) {
      ex.printStackTrace();
      System.out.println("업데이트 에러");
    } finally {

    }

    return result;
  }
Esempio n. 29
0
  private void jButton3ActionPerformed(
      java.awt.event.ActionEvent evt) { // GEN-FIRST:event_jButton3ActionPerformed
    // TODO add your handling code here:
    if (itemcode.getText().length() == 0) {
      JOptionPane.showMessageDialog(
          this, "Please Input  Item Code", "Error", JOptionPane.ERROR_MESSAGE);
      itemcode.requestFocus();
      return;
    }

    String sql = "delete from item where item_code=?";
    try {
      ps = con.prepareStatement(sql);
      ps.setString(1, itemcode.getText());
      ps.executeUpdate();
      JOptionPane.showMessageDialog(null, "Data Delete  Successfully");
    } catch (Exception e) {
      JOptionPane.showMessageDialog(null, "Data not  Delete");
    }

    String sqll = "delete from stock where item_code=?";
    try {
      ps = con.prepareStatement(sqll);
      ps.setString(1, itemcode.getText());
      ps.executeUpdate();
      // JOptionPane.showMessageDialog(null, "Data Delete  Successfully");
    } catch (Exception e) {
      JOptionPane.showMessageDialog(null, "Data not  Delete");
    }
  } // GEN-LAST:event_jButton3ActionPerformed
Esempio n. 30
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;
 }