1

I am trying to create a method that will count the number of records I have in a certain table when the method is called. But for some reason, I keep getting an error saying I have a problem in my SQL code. Looking over everything, I couldn't find the problem. I even went as far to as to copy parts of another person's solution online, but this failed too. To better show where the error seems to be ocurring, I inserted two print statements in the code. I think Java and SQL must hate me! Any help would be most appreciated.

 int tableCounter(String tableName) {
                int num = 0;
                String sql = "SELECT COUNT(*) AS total FROM ?";
                try(Connection conn = letConnect(); PreparedStatement ps = conn.prepareStatement(sql)){
                    ps.setString(1, tableName);
                    System.out.println("test");
                    ResultSet rs = ps.executeQuery();//error ocurrs here
                                                     //when the SQL code is executed
                    System.out.println("test");
                    num = rs.getInt("total");
                } catch (SQLException e) {
                    e.printStackTrace();
                }
                return num;
            }

Output:

test
java.sql.SQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''Country'' at line 1
    at mysql.connector.java@8.0.30/com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:120)
    at mysql.connector.java@8.0.30/com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)
    at mysql.connector.java@8.0.30/com.mysql.cj.jdbc.ClientPreparedStatement.executeInternal(ClientPreparedStatement.java:916)
    at mysql.connector.java@8.0.30/com.mysql.cj.jdbc.ClientPreparedStatement.executeQuery(ClientPreparedStatement.java:972)
    at Database_Interactor.tableCounter(Database_Interactor.java:25)
    at MainClass.main(MainClass.java:10)
Brandon Thies
  • 69
  • 6
  • 4
    You can't use a parameter in a PreparedStatement to substitute for a table name. You're just going to have to append the table name into the query. – Dawood ibn Kareem Aug 23 '22 at 00:40
  • Okay, there is no other way another this? I need to be able to use the same method for like 10 differant tables. I really don't want to write a method for each table. – Brandon Thies Aug 23 '22 at 00:43
  • 1
    You don't have to write a method for each table. Just append the table name to the rest of the SQL. Something like `String sql = "SELECT COUNT(*) AS total FROM " + tableName;` would work nicely. – Dawood ibn Kareem Aug 23 '22 at 00:45
  • Okay thanks, I don't really work with java very much, I got a little confused. – Brandon Thies Aug 23 '22 at 00:47
  • 2
    Just make sure that the name of the table is not provided by the user, otherwise this is subject to SQL injection attacks. If it has to be provided by the user, then validate it first. – Dawood ibn Kareem Aug 23 '22 at 00:48

1 Answers1

2

Based on the answer given above, the code should look something like this:

int tableCounter(String tableName) {
    int num = 0;
    String sql = "SELECT COUNT(*) AS total FROM "+ tableName;
    Connection conn = letConnect();
    try{Statement stmt = conn.createStatement();
        ResultSet rs = stmt.executeQuery(sql);
        while (rs.next()){
            num = rs.getInt("total");}
    }catch (SQLException e) {
        e.printStackTrace();}
    return num;}
Brandon Thies
  • 69
  • 6