CODEWITHSUNDEEP
javamysqljdbc
user5974397
user5974397

Reputation:

Insert optional field values to mysql from jdbc

I have written a code to insert data from jdbc textfield to mysql DB .

String res ="INSERT INTO reservation(check_in_date,check_out_date,cus_id,room_no,Username,nights,adults,kids) VALUES ('"+Startdate+"','"+Lastdate+"','"+cusNo+"','"+Roomno+"','"+Username+"','"+Nights +"','"+Adults +"','"+Kids +"')";

stm=link.prepareStatement(res);
stm.execute();

In this code Kids variable can be null and I have set its' default value to null in database.

But when I enter data from GUI I should always input values for Kids otherwise I can't insert data to the table. So I must always enter zero to the Kids if I don't need to use that field. Is there any way to insert data without input Kids when there is no need to use that field?

Upvotes: 1

Views: 1659

Answers (2)

Mark Rotteveel
Mark Rotteveel

Reputation: 109079

You are using PreparedStatement incorrectly. You should not concatenate values into a query, but use parameter placeholders instead. This prevents SQL injection and it is usually cleaner.

You can set values to null this way.

String res = "INSERT INTO reservation(check_in_date,check_out_date,cus_id,room_no,Username,nights,adults,kids)" 
        + " VALUES (?,?,?,?,?,?,?,?)";
try (PreparedStatement stm=link.prepareStatement(res)) {
    stmt.setDate(1, startDate);
    // ... other variables

    // Option 1, assuming kids is an object (eg Integer)
    stm.setObject(7, kids);

    // Option 2, kids is int and other condition used to determine if null,
    // explicitly set null or set value:
    if (kidsIsNull) {
        stm.setNull(7, Types.INTEGER);
    } else {
        stm.setInt(7, kids);
    }
    stm.executeUpdate();
}

Upvotes: 1

Vijay
Vijay

Reputation: 1034

As you are using prepare statement you can add logic to use setNull method for preparedStatement something like this :

if( kids == null){ stm.setNull(#ParamerterNo ,java.sql.Types.INTEGER); }else{ stm.setInt(#ParamerterNo ,kids); }

Upvotes: 1

Related Questions