Varri Varri - 17 days ago 6
Java Question

Update a single value inside a table with java using MySQL

Currently I have this code:




PreparedStatement prepCust = connection.prepareStatement("insert into Customer values (?, ?, ?, ?, ?, ?);");

ResultSet results = connection.createStatement()
.executeQuery("SELECT phonenumber FROM customer WHERE phonenumber = " + cust.getPhoneNumber());
results.next();
try {
if (results.getString(1).equals(cust.getPhoneNumber())) {
System.out.println("User already exist in database, adding one to order count");
results = connection.createStatement().executeQuery(
"SELECT numberoforders FROM customer WHERE phonenumber = " + cust.getPhoneNumber());
results.next();
int updated = results.getInt(1) + 1;
System.out.println(updated);
results = connection.createStatement()
.executeQuery("SELECT * FROM customer WHERE phonenumber = " + cust.getPhoneNumber());
results.next();
prepCust.setString(1, results.getString(1));
prepCust.setString(2, results.getString(2));
prepCust.setString(3, results.getString(3));
prepCust.setString(4, results.getString(4));
prepCust.setInt(5, updated);
prepCust.setInt(6, results.getInt(6));
prepCust.executeUpdate();
}
} catch (SQLException sql) {
sql.printStackTrace();
prepCust.setString(1, cust.getfName());
prepCust.setString(2, cust.getlName());
prepCust.setString(3, cust.getAddress());
prepCust.setString(4, cust.getPhoneNumber());
prepCust.setInt(5, 1);
prepCust.setInt(6, 0);
prepCust.executeUpdate();
}


And the table it is accessing is:

create table customer(
firstname varchar(50) NOT NULL,
lastname varchar(50) NOT NULL,
streetaddress varchar(150) NOT NULL,
phonenumber varchar(10) NOT NULL,
numberoforders int(11) default 1;
customer_id int(11) NOT NULL AUTO_INCREMENT,
PRIMARY KEY(customer_id,phonenumber)
UNIQUE KEY customer_id (customer_id)

);


My issue is, when I tried to just update a single value using prepCust.setInt(5, someInt);, and then attempt to execute the update, I would get an exception saying that I never input anything for prepCust.setString(1,whatever); and so on. My thoughts for surrounding the code with a trycatch was if the code throws an exception, then that must mean that field does not exist inside the table, and to create a new field that contains all the entered data. If the code doesn't throw an exception, that means that the phone number does exist inside the table, and to find what row that phonenumber is on, and add one to the amount of orders that customer has. My issue now with this code is that it will add an entire new column of information to the table, even if it has the same phonenumber and customer id, throw an exception telling me that, and then I'm stuck with values of the exact same where the value doesn't increment that I need to. How can I just update a single value in a column? Is there anyway to do that with a prepared statement?

Answer

The problem is because you are using an INSERT statement when you are looking to do an UPDATE statement. Since the entry already exists, you want to update that one value that exists with the same phone number. When it doesn't exist you put your prepCust insert statement in your catch block to make the new entry.

String phonenumber = cust.getPhoneNumber();
Statement s = connection.createStatement();
ResultSet results = s.executeQuery("SELECT phonenumber, numberoforders FROM customer WHERE phonenumber = " + phonenumber);
results.next();
try {
    if (results.getString(1).equals(phonenumber)) {
        System.out.println("User already exist in database, adding one to order count");
        int updated = results.getInt(2) + 1;
        System.out.println(updated);

        PreparedStatement updateCust = connection.prepareStatement("UPDATE customer SET numberoforders = ? WHERE phonenumber = ?");
        updateCust.setInt(1, updated);
        updateCust.setString(2, phonenumber);
        updateCust.executeUpdate();
    }
} catch (SQLException sql) {
    sql.printStackTrace();
    try {
        PreparedStatement prepCust = connection.prepareStatement("insert into Customer values (?, ?, ?, ?, ?, ?);");
        prepCust.setString(1, cust.getfName());
        prepCust.setString(2, cust.getlName());
        prepCust.setString(3, cust.getAddress());
        prepCust.setString(4, cust.getPhoneNumber());
        prepCust.setInt(5, 1);
        prepCust.setInt(6, 0);
        prepCust.executeUpdate();
    } catch (SQLException sql2) {
        sql2.printStackTrace();
    }
}
Comments