CODEWITHSUNDEEP
javapostgresql
Jesper
Jesper

Reputation: 2814

No error, but data doesn't get inserted into table

In my database I have two tables called car and ad. In car i have a foreign key referencing the adId value in ad. When i run my code, i don't get any errors, but data is only inserted into ad, not car. Any suggestions? Here's my code:

public void createAd(Ad ad, Car car) {
    try {
        Class.forName("org.postgresql.Driver");
        if (con != null) {
            ps = con.prepareStatement("INSERT INTO \"ad\"(\"title\", \"description\", \"author\", \"image\") VALUES (?, ?, ?, ?)");
            ps.setString(1, ad.getTitle());
            ps.setString(2, ad.getDescription());
            ps.setString(3, ad.getAuthor());
            ps.setBytes(4, ad.getImage());
            ps.executeQuery();

            ps = con.prepareStatement("SELECT currval('\"ad_adId_seq\"');");
            rs = ps.executeQuery();
            rs.next();
            int adId = rs.getInt("currval");
            ps = con.prepareStatement("INSERT INTO \"ad\"(\"adId\") VALUES (?);");
            ps.setInt(1, adId);
            ps.executeUpdate();
            ad.setAdId(adId);

            ps = con.prepareStatement("INSERT INTO \"car\"(\"distanceTraveled\", \"age\", \"condition\", \"vin\", \"brand\", \"price\", \"ad_adId\") VALUES (?, ?, ?, ?, ?, ?, ?)");
            ps.setInt(1, car.getDistanceTraveled());
            ps.setInt(2, car.getAge());
            ps.setString(3, car.getCondition());
            ps.setString(4, car.getVIN());
            ps.setString(5, car.getBrand());
            ps.setInt(6, car.getPrice());
            ps.setInt(6, adId);
            ps.executeQuery();
            car.setAdId(adId);
        }
    } catch (Exception ex) {
        System.out.println(ex);
    }
}

Upvotes: 1

Views: 295

Answers (2)

dan
dan

Reputation: 13272

I suggest that you should take a look at spring-database. Using JdbcTemplate will reduce a lot of the JDBC boiler plate code and more important will take care for a lot of jdbc connection leaks worries for you.

If you sill need to use plain JDBC, you need to properly initialize the connection object and use update instead of query, when using update statements.

Also you need to properly close the statement objects that were created. You should do something similar to the below code:

Connection con = null;
PrepareStatement ps1 = null;
PrepareStatement ps2 = null;
PrepareStatement ps3 = null;
PrepareStatement ps4 = null;
ResultSet rs = null;

String url = "jdbc:postgresql://localhost/dbName";
String user = "userName";
String password = "userPass";

try {
  con = DriverManager.getConnection(url, user, password);
  ps1 = con.prepareStatement("INSERT INTO \"ad\"(\"title\", \"description\", \"author\", \"image\") VALUES (?, ?, ?, ?)");
  ps1.setString(1, ad.getTitle());
  ps1.setString(2, ad.getDescription());
  ps1.setString(3, ad.getAuthor());
  ps1.setBytes(4, ad.getImage());
  ps1.executeUpdate();

  ps2 = con.prepareStatement("SELECT currval('\"ad_adId_seq\"');");
  rs = ps2.executeQuery();
  if(rs.next())
   int adId = rs.getInt("currval");
  }
  ps3 = con.prepareStatement("INSERT INTO \"ad\"(\"adId\") VALUES (?);");
  ps3.setInt(1, adId);
  ps3.executeUpdate();
  ad.setAdId(adId);

  ps4 = con.prepareStatement("INSERT INTO \"car\"(\"distanceTraveled\", \"age\", \"condition\", \"vin\", \"brand\", \"price\", \"ad_adId\") VALUES (?, ?, ?, ?, ?, ?, ?)");
  ps4.setInt(1, car.getDistanceTraveled());
  ps4.setInt(2, car.getAge());
  ps4.setString(3, car.getCondition());
  ps4.setString(4, car.getVIN());
  ps4.setString(5, car.getBrand());
  ps4.setInt(6, car.getPrice());
  ps4.setInt(7, adId);
  ps4.executeUpdate();
  car.setAdId(adId);
} catch (SQLException ex) {
   Logger lgr = Logger.getLogger(getClass().getName());
   lgr.log(Level.SEVERE, "Unable to execute updates", ex);
} finally {
   try {
     if (rs != null)
         rs.close();
      if (ps1 != null)
         ps1.close();
      if (ps2 != null) 
         ps2.close();
      if (ps3 != null) 
         ps3.close();
      if (ps4 != null)
         ps4.close();
      if (con != null)
         con.close();
   } catch (SQLException ex) {
       Logger lgr = Logger.getLogger(getClass().getName());
       lgr.log(Level.WARNING, "Unable to close the connection", ex);
   }
}

Upvotes: 1

rhitz
rhitz

Reputation: 1892

There is an error in your last query : 

ps.setInt(7, adId);

instead of 

ps.setInt(6, adId);

Upvotes: 1

Related Questions