@Override public boolean update(Bid bid) { boolean resp = false; String sql = "UPDATE bid set status=? WHERE id_bid=?"; try { PreparedStatement ps = conn.prepareStatement(sql); ps.setString(1, bid.getStatus()); ps.setInt(2, bid.getId_bid()); ps.executeUpdate(); ps.close(); } catch (SQLException ex) { ex.printStackTrace(); } return resp; }
@Override public boolean insert(Bid bid) { boolean resp = false; String sql = "INSERT INTO bid(id_user, id_post, status) VALUES(?,?,?)"; String sql2 = "INSERT INTO game_list_bid(id_bid, id_game) VALUES(?,?)"; try (PreparedStatement statement = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS); ) { statement.setInt(1, bid.getId_user()); statement.setInt(2, bid.getId_post()); statement.setString(3, bid.getStatus()); int resposta = statement.executeUpdate(); if (resposta == 0) { throw new SQLException("Creating user failed, no rows affected."); } try (ResultSet generatedKeys = statement.getGeneratedKeys()) { if (generatedKeys.next()) { bid.setId_bid(generatedKeys.getInt(1)); } else { throw new SQLException("Creating user failed, no ID obtained."); } } catch (Exception ex) { ex.printStackTrace(); } PreparedStatement statement2 = conn.prepareStatement(sql2); for (int i = 0; i < bid.getGames().size(); i++) { statement2.setInt(1, bid.getId_bid()); statement2.setInt(2, bid.getGames().get(i).getId_game()); int resposta2 = statement2.executeUpdate(); } } catch (Exception ex) { ex.printStackTrace(); } return resp; }
@Override public List<Bid> read() { List<Bid> bids = new ArrayList<>(); // 2. Criar o preparedStatement String sql = "SELECT * FROM bid"; try { PreparedStatement ps = conn.prepareStatement(sql); PreparedStatement psGame; PreparedStatement psGames; // 3. Executa a query ResultSet rs = ps.executeQuery(); ResultSet rsGame; ResultSet rsGames; // 4. Mostrar os resultados while (rs.next()) { Bid bid = new Bid(); bid.setId_bid(rs.getInt("id_bid")); String sqlGames = "SELECT game_list_bid.id_game from game_list_bid where game_list_bid.id_bid = " + (rs.getInt("id_bid")); psGames = conn.prepareStatement(sqlGames); rsGames = psGames.executeQuery(); /** LOGICA PARA ADICIONAR LISTA DE JOGOS * */ List<Game> games = new ArrayList<>(); while (rsGames.next()) { String sqlGame = "SELECT * from game where game.id_game = " + (rsGames.getInt("id_game")); psGame = conn.prepareStatement(sqlGame); rsGame = psGame.executeQuery(); while (rsGame.next()) { Game game = new Game(); game.setId_game(rsGame.getInt("id_game")); game.setDescription(rsGame.getString("description")); game.setGenre(rsGame.getString("genre")); game.setName(rsGame.getString("nome")); game.setRelreaseDate(rsGame.getDate("releasedate")); game.setType(rsGame.getString("type_game")); games.add(game); } psGame.close(); rsGame.close(); } /** FIM DA LOGICA DE ADICIONAR LISTA DE JOGOS * */ bid.setId_user(rs.getInt("id_user")); bid.setGames(games); bid.setId_post(rs.getInt("id_post")); bid.setStatus(rs.getString("status")); bids.add(bid); psGames.close(); rsGames.close(); } // 5. Fechar tudo ps.close(); rs.close(); } catch (SQLException ex) { ex.printStackTrace(); } return bids; }