Sql update query in eclipse

Question

My question is about the Update query shown in eclipse using MySQL, received from the code below

This is my database: I'm trying to do on eclipse the next update query but I'm having trouble

 public static void update_Payment(String month,int number) {
    String sqlupdate = "UPDATE tenant SET month=?  WHERE appartmentnum =? ";

    try {
        PreparedStatement pst = connect.prepareStatement(sqlupdate);
        pst.setInt(1, 4);
        pst.setInt(2, 1);
        pst.executeUpdate();
    } catch (SQLException var2) {
        var2.printStackTrace();
    }

}

I know that I don't have column name month in my database, I just want to receive from the user the name of the month for example

this is working fine when I change [the column name from ] month to april or jan

thx for help

@JacobBarnes theres no way to get string from user ? and using it as column? — Hai Vaknin, Jan 17 '18 at 21:18
I think what you are asking is to do something like this: String sqlupdate = "UPDATE tenant SET " + month + " =? WHERE appartmentnum =?"; — SenSok, Jan 17 '18 at 21:18
are you trying to do this? "UPDATE tenant SET " + month + " = ? WHERE appartmentnum =? — isaace, Jan 17 '18 at 21:18

Jacob Barnes · Answer 1 · 2018-01-18T15:10:56.433

1

Since you are building this SQL statement as a string, please try the following:

 public static void update_Payment(String month,int number) {
    String sqlupdate = "UPDATE tenant SET " + month + "=? WHERE appartmentnum =? ";

    try {
        PreparedStatement pst = connect.prepareStatement(sqlupdate);
        pst.setInt(1, 4);
        pst.setInt(2, 1);
        pst.executeUpdate();
    } catch (SQLException var2) {
        var2.printStackTrace();
    }

}

You still have a problem:

You need a way to pass in the appropriate value for ?. There isn't a default way of converting month to month number. You can write a solution to that in code or db, e.g. Get month number from month name

Or of course just add another parameter and pass in the appropriate integer representing the month similar to what you're doing for apt#

edited Jan 18 '18 at 15:10

answered Jan 17 '18 at 21:22

Jacob Barnes

1,480
1
12
29

@HaiVaknin Awesome, glad I could help! Will you please accept my response as an answer? – Jacob Barnes Jan 17 '18 at 21:31
jacob i have another question that iam stuck on – Hai Vaknin Jan 17 '18 at 22:52
@HaiVaknin, sure, what's up? – Jacob Barnes Jan 17 '18 at 22:52
on the same database i need to sum the monthly payment for each month For example, in January everyone paid But in May not everyone paid because there the value 0 i need to return the amount of each moth could you help me? – Hai Vaknin Jan 17 '18 at 22:54
I'll add an example solution to my answer, but this database schema could be much improved to make queries like that easier. – Jacob Barnes Jan 17 '18 at 23:23
that's giving me only January sum – Hai Vaknin Jan 17 '18 at 23:45
Ya, sorry, just rethought that and obviously that would only give Jan. – Jacob Barnes Jan 18 '18 at 15:12

Sql update query in eclipse

1 Answers1