1

I want to update data if it already exists or insert data if it doesn't. Do I have to put these actions into separate methods or is there an option to do it in one database call?

public boolean insert_tvShow(TvShow tvShow) {

    java.util.Date myDate = new java.util.Date(tvShow.getReleaseDate());
    boolean success = false;

    try {
        String sqlQuery = "UPDATE tvshows " +
                          "SET title = ?, plot = ?, poster = ?, imdb_rating = ?, imdb_url = ?, imdb_id = ?, release_date = ? WHERE title='?' " +
                          "IF @@ROWCOUNT=0 " +
                          "INSERT INTO tvshows VALUES (title, plot, poster, imdb_rating, imdb_url, imdb_id, release_date) " +
                          "VALUES(?, ?, ?, ?, ?, ?, ?)";
        PreparedStatement ps = connection.prepareStatement(sqlQuery);
        ps.setString(1, tvShow.getTitle()); 
        ps.setString(2, tvShow.getPlot());
        ps.setString(3, tvShow.getPoster());
        ps.setDouble(4, tvShow.getImdb_Rating()); 
        ps.setString(5, tvShow.getImdb_url()); 
        ps.setString(6, tvShow.getImdb_id());
        ps.setDate(7, new java.sql.Date(myDate.getTime()));
        ps.setString(8, tvShow.getTitle());
        ps.setString(9, tvShow.getTitle()); 
        ps.setString(10, tvShow.getPlot());
        ps.setString(11, tvShow.getPoster());
        ps.setDouble(12, tvShow.getImdb_Rating()); 
        ps.setString(13, tvShow.getImdb_url()); 
        ps.setString(14, tvShow.getImdb_id());
        ps.setDate(15, new java.sql.Date(myDate.getTime()));
        ps.executeUpdate();
        success = true;
    } catch(Exception e) {
        //TODO logging
    } finally {
        try {
            connection.close();
        } catch (SQLException e) {}
    }
    return success;
}
Jake Shaffer
  • 35
  • 1
  • 8
user1335163
  • 131
  • 1
  • 2
  • 10
  • You might find the following question useful: [Does SQL Server Offer Anything Like MySQL's ON DUPLICATE KEY UPDATE](http://stackoverflow.com/q/1197733/851811). – Xavi López Mar 25 '13 at 15:30
  • What platform are you on? Some of them have what's called a `MERGE` statement, which basically does what you describe. Otherwise, look into JPA, which has a merge call (which abstracts the Java code from needing to know about the DB-level calls needed for the desired behavior) – Clockwork-Muse Mar 25 '13 at 16:08

0 Answers0