Java and MYSQL Syntax Issue

Question

I'm trying to insert data into my MYSQL databse. I want to insert an int into the database which I have no problem doing. However, I want to INSERT INTO (VALUES) WHERE. I get a MYSQL syntax error when I try this.

I can INSERT and SELECT WHERE as long as they are in two seperate statements. Here is my code:

    String query = ("INSERT INTO `accounts` (inventory) " + "VALUES ('"
            + Inventory.inventory + "') WHERE username='" + Frame.username
            + "' and password = '" + Frame.password + "'");

Answer 1

Basically, an INSERT statement can not have a WHERE clause. I am thinking that you want to UPDATE a certain record, eg

UPDATE accounts
SET inventory = 'valueHere'
WHERE userName = 'userHEre' AND password = 'passHere'

The only time an INSERT statement can have a WHERE clause is when you are inserting records from the result of a SELECT statement, eg

INSERT INTO tableName (col1, ..., colN)
SELECT col1, ..., colN
FROM table2
// WHERE ..your conditions here..

As a sidenote, your current coding style is vulnerable with SQL Injection. Consider using PreparedStatement.

---

Basic example of a PreparedStatement

String updateString = "UPDATE accounts SET inventory = ? WHERE userName = ? AND password = ?";
PreparedStatement updateStmt = con.prepareStatement(updateString);
updateStmt.setString(1, Inventory.inventory);
updateStmt.setString(2, Frame.username);
updateStmt.setString(3, Frame.password);
updateStmt.executeUpdate();

• JDBC PreparedStatement

Answer 2

MySQL INSERT Syntax does not support the WHERE clause so that's why you have a syntax issue. Maybe you're looking for an UPDATE :

UPDATE [LOW_PRIORITY] [IGNORE] tbl_name
    SET col_name1=expr1 [, col_name2=expr2 ...]
    [WHERE where_definition]
    [ORDER BY ...]
    [LIMIT row_count]

Answer 3

Not a direct answer but more of a best practice....

You should avoid doing this type of string concatenation for any sql. You vulnerable to sql injection and it does not scale well. Instead you should look at using JdbcTemplates or NamedJdbcTemplate using the opensource spring framework.

Answer 4

The WHERE is not applicable in INSERT INTO Syntax. You want insert a new row in the table, and you should add the username and password as well as Inventory.inventory in VALUES set.