public static ArrayList getContexts() {
    try {
      ArrayList<String> arraylist = new ArrayList<String>();
      java.sql.Connection con = Database.getConnection();
      java.sql.Statement s =
          con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);

      ResultSet contexts;
      contexts = s.executeQuery("select name from contexts");

      if (contexts.next()) {
        contexts.first();
        arraylist.add(contexts.getString("name"));

        while (contexts.next()) {
          arraylist.add(contexts.getString("name"));
        }
      }
      return arraylist;

    } catch (SQLException ex) {
      Logger.getLogger(DataLayer.class.getName()).log(Level.SEVERE, null, ex);
    }
    return null;
  }
  public static ArrayList search(String notes) {
    try {
      java.sql.Connection con = Database.getConnection();
      java.sql.Statement s =
          con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);

      ArrayList list = new ArrayList();

      ResultSet search;
      search = s.executeQuery("select * from thoughts");

      while (search.next()) {
        String str = search.getString("notes");

        if (str.equals(notes)) {
          list.add(str);
        }
      }

      return list;
    } catch (SQLException ex) {
      ArrayList exception = new ArrayList();
      exception.add(notes + "kon niet gevonden worden/ bestaat niet!");

      return exception;
    }
  }
  protected int fetchNumRows(String clause, boolean forceQuotes) throws SQLException {
    PreparedStatement stmt = null;
    ResultSet rs = null;
    StringBuilder sql = new StringBuilder("select ");
    sql.append(clause);
    sql.append(" from ");
    if (getSchema() != null) {
      sql.append(getSchema());
      sql.append('.');
    }

    if (forceQuotes) {
      String quote = db.getMetaData().getIdentifierQuoteString().trim();
      sql.append(quote + getName() + quote);
    } else sql.append(db.getQuotedIdentifier(getName()));

    try {
      stmt = db.getConnection().prepareStatement(sql.toString());
      rs = stmt.executeQuery();
      while (rs.next()) {
        return rs.getInt(1);
      }
      return -1;
    } catch (SQLException exc) {
      if (forceQuotes) // we tried with and w/o quotes...fail this attempt
      throw exc;

      return fetchNumRows(clause, true);
    } finally {
      if (rs != null) rs.close();
      if (stmt != null) stmt.close();
    }
  }
  public static ArrayList getAllThoughts() {
    try {
      ArrayList arrayList = new ArrayList<Gedachte>();

      java.sql.Connection con = Database.getConnection();
      java.sql.Statement s =
          con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);

      ResultSet thoughts;
      thoughts = s.executeQuery("select * from thoughts");
      if (thoughts.next()) {
        thoughts.first();

        int id1 = thoughts.getInt("id");
        String notes1 = thoughts.getString("notes");
        Gedachte gedachte1 = new Gedachte(id1, notes1);
        arrayList.add(gedachte1);

        while (thoughts.next()) {
          int id = thoughts.getInt("id");
          String notes = thoughts.getString("notes");

          Gedachte gedachte = new Gedachte(id, notes);
          arrayList.add(gedachte);
        }
      }
      return arrayList;
    } catch (SQLException ex) {
      ex.printStackTrace();
    }
    return null;
  }
  public static void deleteThought(int index) {
    try {
      java.sql.Connection con = Database.getConnection();
      java.sql.Statement s =
          con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
      int indext = index + 1;
      ResultSet thoughts;
      thoughts = s.executeQuery("select * from thoughts");
      thoughts.absolute(indext);
      thoughts.deleteRow();

      int size = aantalID("thoughts");
      ResultSet thoughts2;
      thoughts2 = s.executeQuery("select * from thoughts");
      if (thoughts2.next()) {
        while (indext < size) {
          thoughts2.absolute(indext);
          thoughts2.updateInt("id", indext);
          thoughts2.updateRow();
          indext++;
          thoughts2.next();
        }
      }

    } catch (SQLException ex) {
      Logger.getLogger(DataLayer.class.getName()).log(Level.SEVERE, null, ex);
    }
  }
  public static void deleteProject(int id) {
    try {
      java.sql.Connection con = Database.getConnection();
      java.sql.Statement s =
          con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);

      ResultSet projects;
      projects = s.executeQuery("select * from projects");
      projects.absolute(id);
      projects.deleteRow();

      int size = aantalID("projects");
      projects = s.executeQuery("select * from projects");
      if (projects.next()) {
        while (id < size) {
          projects.absolute(id);
          projects.updateInt("id", id);
          projects.updateRow();
          id++;
          projects.next();
        }
      }
    } catch (SQLException ex) {
      Logger.getLogger(DataLayer.class.getName()).log(Level.SEVERE, null, ex);
    }
  }
 @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);
 }
/** Simple Factory 방식으로 변경해보았음. */
public class BusinessA {
  // DatabaseFactory 타입의 클래스는 Database 타입의 객체를 생성하도록 구현해야 함
  DatabaseFactory databaseFactory = new DatabaseFactoryImpl();
  // Database 객체 생성을 위임 받은 DatabaseFactory를 통해서 필요로 하는 객체를 취득
  Database db = databaseFactory.getDatabase("mysql");

  // Factory에 의해 구현된 Database 객체는 getConnection() 메서도를 구현하여 Connection 객체를 반환하도록 함
  Connection con = db.getConnection();

  public void insert(String id, String code, int quality) {

    String query = "insert into product values ( " + id + "," + code + "," + quality + ")";
    try {
      Statement stmt = con.createStatement();
      stmt.executeUpdate(query);
    } catch (SQLException e) {
      e.printStackTrace();
    }
  }

  public ResultSet selectAll() {
    String query = "select * from product";
    ResultSet resultSet = null;
    try {
      Statement stmt = con.createStatement();
      resultSet = stmt.executeQuery(query);
    } catch (SQLException e) {
      e.printStackTrace();
    }
    return resultSet;
  }

  private void writeResultSet(ResultSet resultSet) throws SQLException {
    // ResultSet is initially before the first data set
    while (resultSet.next()) {
      // It is possible to get the columns via name
      // also possible to get the columns via the column number
      // which starts at 1
      // e.g. resultSet.getSTring(2);
      String id = resultSet.getString("id");
      String code = resultSet.getString("code");
      int quality = resultSet.getInt("quality");

      System.out.println("id: " + id + " code: " + code + " quality: " + quality);
    }
  }

  public static void main(String[] argv) {
    BusinessA business = new BusinessA();
    //        business.insert("1", "111", 4);
    //        business.insert("2", "222", 2);
    //        business.insert("3", "333", 3);
    ResultSet resultSet = business.selectAll();
    try {
      business.writeResultSet(resultSet);
    } catch (SQLException e) {
      e.printStackTrace();
    }
  }
}
  @Override
  public boolean eliminar_grupo(String nombregrupo) throws GrupoNoExisteException, SQLException {
    Connection connection = null;
    PreparedStatement stmt = null;

    try {
      Grupo grupo = obtener_ID_grupo_por_NOMBRE(nombregrupo);

      if (grupo == null) throw new GrupoNoExisteException();

      connection = Database.getConnection();
      stmt = connection.prepareStatement(GrupoDAOQuery.ELIMINAR_GRUPO);
      stmt.setString(1, nombregrupo);
      stmt.executeUpdate();
      return true;
    } catch (SQLException e) {
      throw e;
    } finally {
      if (stmt != null) stmt.close();
      if (connection != null) {
        connection.setAutoCommit(true);
        connection.close();
      }
    }
  }
 @Override
 public boolean abandonar_grupo(String nombregrupo, String nombreusuario)
     throws SQLException, GrupoNoExisteException, UserNoExisteException,
         RelacionNoExisteException {
   Connection connection = null;
   PreparedStatement stmt = null;
   UserDAOImpl comprobarUser = new UserDAOImpl();
   User user = null;
   try {
     Grupo grupo = obtener_ID_grupo_por_NOMBRE(nombregrupo);
     user = comprobarUser.obtener_UserByLoginid(nombreusuario);
     if (grupo == null) throw new GrupoNoExisteException();
     if (user == null) throw new UserNoExisteException();
     grupo = comprobarUsuarioengrupo(grupo.getId(), user.getId());
     if (grupo == null) throw new RelacionNoExisteException();
     connection = Database.getConnection();
     stmt = connection.prepareStatement(GrupoDAOQuery.ABANDONAR_GRUPO);
     stmt.setString(1, grupo.getId());
     stmt.setString(2, user.getId());
     stmt.executeUpdate();
     return true;
   } catch (SQLException e) {
     throw e;
   } finally {
     if (stmt != null) stmt.close();
     if (connection != null) {
       connection.setAutoCommit(true);
       connection.close();
     }
   }
 }
  @Override
  public Grupo comprobarUsuarioengrupo(String grupoid, String userid)
      throws SQLException, RelacionNoExisteException {

    Connection connection = null;
    PreparedStatement stmt = null;
    Grupo grupo = null;
    try {
      connection = Database.getConnection();
      stmt = connection.prepareStatement(GrupoDAOQuery.COMPROBAR_USER_ASIGNADO_A_GRUPO);
      stmt.setString(1, grupoid);
      stmt.setString(2, userid);
      ResultSet rs = stmt.executeQuery();
      while (rs.next()) {
        grupo = new Grupo();
        grupo.setId(rs.getString("grupoid"));
      }
    } catch (SQLException e) {
      throw e;
    } finally {
      if (stmt != null) stmt.close();
      if (connection != null) connection.close();
    }
    return grupo;
  }
Example #12
0
  /** update a project */
  public void update(Project project) {

    PreparedStatement stmt = null;
    try {
      String sql =
          "update Projects "
              + "set Title = ?, RecordSperImage = ?, FirstYCoord = ?, RecordHeight = ? "
              + "where ProjectID = ?";
      stmt = db.getConnection().prepareStatement(sql);
      stmt.setString(1, project.getTitle());
      stmt.setInt(2, project.getRecordsPerImage());
      stmt.setInt(3, project.getFirstYCoord());
      stmt.setInt(4, project.getRecordHeight());
      stmt.setInt(5, project.getProjectID());

      if (stmt.executeUpdate() == 1) {
        System.out.println("Update success");
      } else {
        System.out.println("Update fail");
      }
    } catch (SQLException e) {
      System.out.println("Can't execute update");
      e.printStackTrace();
    } finally {
      try {
        if (stmt != null) stmt.close();
      } catch (SQLException e) {
        System.out.println("Can't execute connect");
        e.printStackTrace();
      }
    }
  }
Example #13
0
  /**
   * get a project by projectID
   *
   * @return a project
   */
  public Project getProject(int ID) {
    PreparedStatement stmt = null;
    ResultSet rs = null;
    Project project = null;
    try {
      String sql =
          "select ProjectID, Title, RecordSperImage, "
              + "FirstYCoord, RecordHeight from Projects where ProjectID = ?";
      stmt = db.getConnection().prepareStatement(sql);
      stmt.setInt(1, ID);

      rs = stmt.executeQuery();
      while (rs.next()) {
        Project newProject =
            new Project(rs.getInt(1), rs.getString(2), rs.getInt(3), rs.getInt(4), rs.getInt(5));
        project = newProject;
      }
    } catch (SQLException e) {
      System.out.println("Can't execute query");
      e.printStackTrace();
    } finally {
      try {
        if (rs != null) rs.close();
        if (stmt != null) stmt.close();
      } catch (SQLException e) {
        System.out.println("Can't execute connect");
        e.printStackTrace();
      }
    }
    return project;
  }
Example #14
0
  /** update a field */
  public void update(Field field) {
    PreparedStatement stmt = null;
    try {
      String sql =
          "update Fields "
              + "set XCoord = ?, Width = ?, HelpHTML = ?, "
              + "KnownData = ?, ProjectID = ?, Title = ? "
              + "where FieldID = ?";
      stmt = db.getConnection().prepareStatement(sql);
      stmt.setInt(1, field.getxCoord());
      stmt.setInt(2, field.getWidth());
      stmt.setString(3, field.getHelpHTML());
      stmt.setString(4, field.getKnownData());
      stmt.setInt(5, field.getProjectID());
      stmt.setInt(7, field.getFieldID());
      stmt.setString(6, field.getTitle());

      if (stmt.executeUpdate() == 1) {
        System.out.println("Update success");
      } else {
        System.out.println("Update fail");
      }
    } catch (SQLException e) {
      System.out.println("Can't execute update");
      e.printStackTrace();
    } finally {
      try {
        if (stmt != null) stmt.close();
      } catch (SQLException e) {
        System.out.println("Can't execute connect");
        e.printStackTrace();
      }
    }
  }
  public static ArrayList getActions(int id) {
    try {
      ArrayList<String> arraylist = new ArrayList<String>();
      java.sql.Connection con = Database.getConnection();
      java.sql.Statement s =
          con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);

      ResultSet actions;

      actions = s.executeQuery("select * from actions where project_id = " + id);

      if (actions.next()) {
        actions.first();
        arraylist.add(
            actions.getInt("id")
                + ", "
                + actions.getString("description")
                + ", "
                + actions.getString("notes")
                + ", "
                + actions.getInt("status_id")
                + ", "
                + actions.getInt("contexts_id")
                + ", "
                + actions.getInt("project_id")
                + ", "
                + actions.getString("action_date")
                + ", "
                + actions.getString("statuschange_date")
                + ", "
                + actions.getBoolean("done"));

        while (actions.next()) {
          arraylist.add(
              actions.getInt("id")
                  + ", "
                  + actions.getString("description")
                  + ", "
                  + actions.getString("notes")
                  + ", "
                  + actions.getInt("status_id")
                  + ", "
                  + actions.getInt("contexts_id")
                  + ", "
                  + actions.getInt("project_id")
                  + ", "
                  + actions.getString("action_date")
                  + ", "
                  + actions.getString("statuschange_date")
                  + ", "
                  + actions.getBoolean("done"));
        }
      }
      return arraylist;
    } catch (SQLException ex) {
      Logger.getLogger(DataLayer.class.getName()).log(Level.SEVERE, null, ex);
    }
    return null;
  }
Example #16
0
  /** add a field */
  public void add(Field field) {
    PreparedStatement stmt = null;
    Statement keyStmt = null;
    ResultSet keyRS = null;
    try {
      String sql =
          "insert into Fields (XCoord, Title, Width, HelpHTML, "
              + "KnownData, ProjectID) values (?, ?, ?, ?, ?, ?)";
      stmt = db.getConnection().prepareStatement(sql);
      stmt.setInt(1, field.getxCoord());
      stmt.setInt(3, field.getWidth());
      stmt.setString(4, field.getHelpHTML());
      stmt.setString(5, field.getKnownData());
      stmt.setInt(6, field.getProjectID());
      stmt.setString(2, field.getTitle());

      if (stmt.executeUpdate() == 1) {
        keyStmt = db.getConnection().createStatement();
        keyRS = keyStmt.executeQuery("select last_insert_rowid()");
        keyRS.next();
        int id = keyRS.getInt(1);
        field.setFieldID(id);
      } else {
        System.out.println("Add fail");
      }
    } catch (SQLException e) {
      System.out.println("Can't execute add");
      e.printStackTrace();
    } finally {
      try {
        if (keyRS != null) keyRS.close();
        if (stmt != null) stmt.close();
        if (keyStmt != null) keyStmt.close();
      } catch (SQLException e) {
        System.out.println("Can't execute connect");
        e.printStackTrace();
      }
    }
  }
Example #17
0
  /** add a project */
  public void add(Project project) {
    PreparedStatement stmt = null;
    Statement keyStmt = null;
    ResultSet keyRS = null;
    try {
      String sql =
          "insert into Projects "
              + "(Title, RecordSperImage, "
              + "FirstYCoord, RecordHeight) "
              + "values (?, ?, ?, ?)";
      stmt = db.getConnection().prepareStatement(sql);
      stmt.setString(1, project.getTitle());
      stmt.setInt(2, project.getRecordsPerImage());
      stmt.setInt(3, project.getFirstYCoord());
      stmt.setInt(4, project.getRecordHeight());

      if (stmt.executeUpdate() == 1) {
        keyStmt = db.getConnection().createStatement();
        keyRS = keyStmt.executeQuery("select last_insert_rowid()");
        keyRS.next();
        int id = keyRS.getInt(1);
        project.setProjectID(id);
      } else {
        System.out.println("Add fail");
      }
    } catch (SQLException e) {
      System.out.println("Can't execute add");
      e.printStackTrace();
    } finally {
      try {
        if (keyRS != null) keyRS.close();
        if (stmt != null) stmt.close();
        if (keyStmt != null) keyStmt.close();
      } catch (SQLException e) {
        System.out.println("Can't execute connect");
        e.printStackTrace();
      }
    }
  }
  public static void changeProject(int id, String change) {
    try {
      java.sql.Connection con = Database.getConnection();
      java.sql.Statement s =
          con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);

      ResultSet thought;
      thought = s.executeQuery("select * from projects");
      thought.absolute(id);
      thought.updateString("name", change);
      thought.updateRow();
    } catch (SQLException ex) {
      Logger.getLogger(DataLayer.class.getName()).log(Level.SEVERE, null, ex);
    }
  }
  public static int getContextID(String name) {
    try {
      java.sql.Connection con = Database.getConnection();
      java.sql.Statement s =
          con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
      ResultSet contexts;

      String query = "select id from contexts where name = \"" + name + "\";";
      contexts = s.executeQuery(query);
      contexts.first();
      return contexts.getInt("id");
    } catch (SQLException ex) {
      Logger.getLogger(DataLayer.class.getName()).log(Level.SEVERE, null, ex);
    }
    return 0;
  }
  public static void addAction(
      String description,
      String notes,
      String context,
      String status,
      int projectid,
      String datum) {
    try {
      java.sql.Connection con = Database.getConnection();
      java.sql.Statement s =
          con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);

      ResultSet statuses;
      statuses = s.executeQuery("select * from statuses");
      statuses.first();
      boolean klaar = false;
      int statusid = 0;
      while (!klaar) {
        if (statuses.getString("name").equals(status)) {
          statusid = statuses.getInt("id");
          klaar = true;
        } else {
          statuses.next();
        }
      }

      int actionid = aantalID("actions");
      int context_id = getContextID(context);

      ResultSet actions;
      actions = s.executeQuery("select * from actions");
      actions.moveToInsertRow();
      actions.updateInt("id", actionid);
      actions.updateString("description", description);
      actions.updateString("notes", notes);
      actions.updateInt("contexts_id", context_id);
      actions.updateInt("status_id", statusid);
      actions.updateInt("project_id", projectid);
      actions.updateDate("action_date", Date.valueOf(datum));
      actions.updateDate("statuschange_date", null);
      actions.updateBoolean("done", false);
      actions.insertRow();

    } catch (SQLException ex) {
      Logger.getLogger(DataLayer.class.getName()).log(Level.SEVERE, null, ex);
    }
  }
  public static void putGedachte(String notes) {
    try {
      java.sql.Connection con = Database.getConnection();
      java.sql.Statement s =
          con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);

      int id = aantalID("thoughts");

      ResultSet thoughtInvoegen;
      thoughtInvoegen = s.executeQuery("select * from thoughts");
      thoughtInvoegen.moveToInsertRow();
      thoughtInvoegen.updateInt("id", id);
      thoughtInvoegen.updateString("notes", notes);
      thoughtInvoegen.insertRow();
    } catch (SQLException ex) {
      ex.printStackTrace();
    }
  }
 @Override
 public GrupoCollection obtener_coleccion(long timestamp, boolean before) throws SQLException {
   Grupo grup = null;
   GrupoCollection grupocollection = new GrupoCollection();
   Connection connection = null;
   PreparedStatement stmt = null;
   try {
     connection = Database.getConnection();
     if (before)
       stmt =
           connection.prepareStatement(
               GrupoDAOQuery.OBTENER_COLECCION_GRUPOS_APARTIR_ID_PAGINADA_A_5);
     else
       stmt =
           connection.prepareStatement(
               GrupoDAOQuery.OBTENER_COLECCION_GRUPOS_APARTIR_ID_PAGINADA_A_5_after);
     stmt.setTimestamp(1, new Timestamp(timestamp));
     ResultSet rs = stmt.executeQuery();
     boolean first = true;
     while (rs.next()) {
       grup = new Grupo();
       grup.setId(rs.getString("id"));
       grup.setNombre(rs.getString("nombre"));
       grup.setCreationTimestamp(rs.getTimestamp("creation_timestamp").getTime());
       grup.setLastModified(rs.getTimestamp("last_modified").getTime());
       if (first) {
         grupocollection.setNewestTimestamp(grup.getLastModified());
         first = false;
       }
       grupocollection.setOldestTimestamp(grup.getLastModified());
       grupocollection.getGrupos().add(grup);
     }
   } catch (SQLException e) {
     throw e;
   } finally {
     if (stmt != null) stmt.close();
     if (connection != null) {
       connection.setAutoCommit(true);
       connection.close();
     }
   }
   return grupocollection;
 }
Example #23
0
  public boolean delete(int id) {

    try {
      connection = Database.getConnection();

      String query = "DELETE FROM address WHERE id=?";
      statement = connection.prepareStatement(query);
      statement.setInt(1, id);

      if (statement.executeUpdate() > 0) {
        return true;
      }
    } catch (SQLException exception) {
      Logger.getLogger(this.getClass().getName()).log(Level.SEVERE, null, exception);
    } finally {
      Database.close(connection, statement, resultSet);
    }

    return false;
  }
  public static void addContext(String context) {
    try {
      java.sql.Connection con = Database.getConnection();
      java.sql.Statement s =
          con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);

      int aantal = aantalID("contexts");

      ResultSet contexts;
      contexts = s.executeQuery("select * from contexts");

      contexts.moveToInsertRow();
      contexts.updateInt("id", aantal);
      contexts.updateString("name", context);
      contexts.insertRow();

    } catch (SQLException ex) {
      Logger.getLogger(DataLayer.class.getName()).log(Level.SEVERE, null, ex);
    }
  }
Example #25
0
  public boolean add(Address address) {

    try {
      connection = Database.getConnection();

      String query = "INSERT INTO address (customer_id, address) VALUES (?, ?)";
      statement = connection.prepareStatement(query);
      statement.setInt(1, address.getCustomer().getId());
      statement.setString(2, address.getAddress());

      if (statement.executeUpdate() > 0) {
        return true;
      }
    } catch (SQLException exception) {
      Logger.getLogger(this.getClass().getName()).log(Level.SEVERE, null, exception);
    } finally {
      Database.close(connection, statement, resultSet);
    }

    return false;
  }
Example #26
0
  public boolean edit(Address address) {

    try {
      connection = Database.getConnection();

      String query = "UPDATE address SET address=? WHERE id=?";
      statement = connection.prepareStatement(query);
      statement.setString(1, address.getAddress());
      statement.setInt(2, address.getId());

      if (statement.executeUpdate() > 0) {
        return true;
      }
    } catch (SQLException exception) {
      Logger.getLogger(this.getClass().getName()).log(Level.SEVERE, null, exception);
    } finally {
      Database.close(connection, statement, resultSet);
    }

    return false;
  }
  public static int aantalID(String table) {
    try {
      java.sql.Connection con = Database.getConnection();
      java.sql.Statement s =
          con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);

      ResultSet idTest;
      idTest = s.executeQuery("select count(id) as aantalID from " + table);
      idTest.first();
      int id = idTest.getInt("aantalID");
      if (id == 0) {
        id = 1;
      } else {
        id = id + 1;
      }

      return id;
    } catch (SQLException ex) {
      Logger.getLogger(DataLayer.class.getName()).log(Level.SEVERE, null, ex);
    }
    return 0;
  }
Example #28
0
  /**
   * get a field by FieldID
   *
   * @return a field
   */
  public Field getField(int fieldID) {
    PreparedStatement stmt = null;
    ResultSet rs = null;
    Field field = null;
    try {
      String sql =
          "select FieldID, Title,  XCoord, Width, HelpHTML, "
              + "KnownData, ProjectID from Fields where FieldID = ?";
      stmt = db.getConnection().prepareStatement(sql);
      stmt.setInt(1, fieldID);

      rs = stmt.executeQuery();
      while (rs.next()) {
        Field newField =
            new Field(
                rs.getInt(1),
                rs.getString(2),
                rs.getInt(3),
                rs.getInt(4),
                rs.getString(5),
                rs.getString(6),
                rs.getInt(7));
        field = newField;
      }
    } catch (SQLException e) {
      System.out.println("Can't execute query");
      e.printStackTrace();
    } finally {
      try {
        if (rs != null) rs.close();
        if (stmt != null) stmt.close();
      } catch (SQLException e) {
        System.out.println("Can't execute connect");
        e.printStackTrace();
      }
    }
    return field;
  }
Example #29
0
  public Address read(int id) {
    Address address = null;

    try {
      connection = Database.getConnection();

      String query = "SELECT address FROM address WHERE id=?";
      statement = connection.prepareStatement(query);
      statement.setInt(1, id);

      resultSet = statement.executeQuery();

      if (resultSet.next()) {
        address = new Address(id, null, resultSet.getString("address"));
      }
    } catch (SQLException exception) {
      Logger.getLogger(this.getClass().getName()).log(Level.SEVERE, null, exception);
    } finally {
      Database.close(connection, statement, resultSet);
    }

    return address;
  }
  public static String addProject(String naam) {
    try {
      java.sql.Connection con = Database.getConnection();
      java.sql.Statement s =
          con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);

      int id = aantalID("projects");

      ResultSet projects;
      projects = s.executeQuery("select * from projects");
      projects.moveToInsertRow();
      projects.updateInt("id", id);
      projects.updateString("name", naam);
      projects.updateString("notes", "test");
      projects.insertRow();

      return id + " " + naam + " test";

    } catch (SQLException ex) {
      Logger.getLogger(DataLayer.class.getName()).log(Level.SEVERE, null, ex);
    }
    return null;
  }