0

I just started to work with MySQL in Java, I am trying to update existing data in my database. The main purpose is to create a counter, there will update an int in the database when an action has been done.

In this case, I am trying to update the daily_search_count by increasing the integer when the code is compiling. Below you can see a picture of my DB data: data within the database The code I have written is intended to increase the "daily_search_count" by 1 each time the code is running. But unfortunately I get the following error:

Exception in thread "main" java.sql.SQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ''accounts' set 'daily_search_count' = '4' where 'id' = '1'' at line 1
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:118)
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:95)
at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)
at com.mysql.cj.jdbc.ClientPreparedStatement.executeInternal(ClientPreparedStatement.java:960)
at com.mysql.cj.jdbc.ClientPreparedStatement.executeUpdateInternal(ClientPreparedStatement.java:1116)
at com.mysql.cj.jdbc.ClientPreparedStatement.executeUpdateInternal(ClientPreparedStatement.java:1066)
at com.mysql.cj.jdbc.ClientPreparedStatement.executeLargeUpdate(ClientPreparedStatement.java:1396)
at com.mysql.cj.jdbc.ClientPreparedStatement.executeUpdate(ClientPreparedStatement.java:1051)
at Database.main(Database.java:29)

I can't see what is wrong with my code as you can see below:

import java.sql.*;

public class Database {

public static void main(String[] args) throws Exception {
    String host = "jdbc:mysql://localhost:3306/presearch";
    String username = "root";
    String password = "";
    String query = "select * from accounts";

    Connection con = DriverManager.getConnection(host, username, password);

    Statement st = con.createStatement();
    ResultSet rs = st.executeQuery(query);

    String userData = "";

    while (rs.next()) {
        if (rs.getInt(4) < 33) {
            userData = rs.getInt(1) + " : " + rs.getString(2) + " daily search count : " + rs.getInt(4);
            System.out.println(userData);

            int counter = rs.getInt(4) + 1;
            PreparedStatement updatexdd = con.prepareStatement("update 'accounts' set 'daily_search_count' = '" + counter + "' where 'id' = '" + rs.getInt(1) + "'");
            int updatexdd_done = updatexdd.executeUpdate();
        }
    }

    st.close();
    con.close();

}

}

I hope someone can see what I am doing wrong.

Thanks in advance!

Youcef LAIDANI
  • 55,661
  • 15
  • 90
  • 140
kristoffer
  • 65
  • 1
  • 7

1 Answers1

0

You have some problems in your code you have to avoid :

  • name of table and columns should not be between two quotes 'accounts'
  • make sure to use placeholder(?) to specify your attribute in the query with PreparedStatement
  • make sure to close the connection and statement in finally block instead
  • I note also that all you need is just one query

Your code should look like this :

public static void main(String[] args) throws Exception {
    String host = "jdbc:mysql://localhost:3306/presearch";
    String username = "root";
    String password = "";
    String query = "update accounts set daily_search_count = daily_search_count + 1 where daily_search_count < 33";
    try (Connection con = DriverManager.getConnection(host, username, password);
            PreparedStatement updatexdd = con.prepareStatement(query)) {

        int updatexdd_done = updatexdd.executeUpdate();
    }
}

In my code I use The try-with-resources Statement which support AutoCloseable and Closeable Interface which mean you don't need to close the connection or the statement.

Youcef LAIDANI
  • 55,661
  • 15
  • 90
  • 140