mysql query in java not working in database

Question

I have a methode here which should get data from my preset database. The table has set info in it already. But I can not retrieve the data from the table in my java program. The rest of the code works fine like al the setters and getters. I have an other methode which writes to a specific row in the database. This works fine so I know that there is a connection between eclipse and my database.

What I have :

public void readCard(int id){
 try{ 
 java.sql.Statement statement=con.connection.createStatement();
 this.id=id;
 String gget;
 gget= "SELECT DISTINCT * FROM addresscard WHERE (id = \""+ 
 this.id +"\");"; 
 ResultSet rs= statement.executeQuery(gget);
 rs.next();
 this.id= rs.getInt(1);
 this.name= rs.getString(2);
 this.adress= rs.getString(3);
 this.postcode= rs.getString(4);
 this.place= rs.getString(5);
 this.telephone= rs.getString(6);
 System.out.println(this.id);
 }catch(Exception e){
 System.out.println(" did not read anything");
 }

I put String gget= "SELECT DISTINCT * FROM adreskaart WHERE (id = \""+ this.id +"\");";
in mysql as: SELECT DISTINCT * FROM adreskaart WHERE id =2;

The row which corresponds with id 2 get selected. So my database works fine. My guess is that my sql code in eclipse is wrong. Can anyone see why I can't read from the database.

Many Thanks

edit In my changeCard() I should be able to adjust data in my tester class like : Connectie con= new Connectie(); AddressCard ac=new AddressCard();

 ak.readCard(2);
ak.setName("prof dr ir P chimp"); 
ak.changeCard();
Vector tabel;
 tabel=ak.readAllCards();

and of course all the gui good stuff here.

public void changeCard(){
 try{
 java.sql.Statement statement= con.connection.createStatement(); 
 String sset; 
 sset= "UPDATE adresKaart " + " SET naam = '" + this.naam + "', " + "adres='" + this.adres + "'," +
 " postcode='" + this.postcode + "'," + "plaats='" + this.plaats + "'," + "telefoon='" + this.telefoon
 + "' " + " WHERE adreskaart.id = " + this.id; 
 statement.executeUpdate(sset); 
 }catch(Exception e){
 System.out.println(" did not change anything");
 e.printStackTrace();
 } 
 }

Seems that I have the sql wrong but cant seem to figure it out. Thanks in advance .

Answer 1

First of all, make sure about your table name. Is it addresscard or adreskaart?

As you wrote, your query should be

SELECT DISTINCT * FROM addresscard WHERE id = 2

but, now it is (extra quotes)

SELECT DISTINCT * FROM addresscard WHERE id = "2"

If you store id as an integer, you should not put those quotes. If id is stored as varchar then you should. Moreover, no need to put semicolon at the end of the query. Try following.

gget= "SELECT DISTINCT * FROM addresscard WHERE id = " + this.id;

• XY-bane: Thanks for the reply,
• XY-bane: Thanks for the reply, But it seems to say that the string literal is not closed properly and that is have to add a double quote. I Thought you have to close with a semicolon to close the string and to end the query? Can you please explain what you mean by : I am comparing int id as a string? Thank you . I am still a JAVA noobie
• XY-bane: Indeed adreskaart is addresscard. it's the dutch translation. I stored id as an int. I left the quotes out. Good tip. I still get no errors and the name still doesn't change in my database.

Answer 2

I think your slq get a result of arry.So,you should viste the result:

while(rs.next()){
 this.id= rs.getInt(1);
 this.name= rs.getString(2);
 this.adress= rs.getString(3);
 this.postcode= rs.getString(4);
 this.place= rs.getString(5);
 this.telephone= rs.getString(6);
}

• XY-bane: This seems to give the same result as before. I think my method to change data is wrong.
• XY-bane: rs.next() will force the compiler to go to the next query to execute and retrieve the given rs.getInt(1) , r.getString(2) and so on. Right? So why add the while ()?