Solved Count table rows in SQLite?

Discussion in 'Spigot Plugin Help' started by M4ST3RX, Jun 3, 2018.

  1. I have an authentication plugin that is able to create and insert data into the table 'users' but for auto increment i need to get the number of records in the database and add 1 to it so I can pass that number for the id. But as the current state it always giving me 0 back even if there is data in the database.

    Code (Java):
    public Integer countRows() {
            Connection conn = null;
            PreparedStatement ps = null;
            ResultSet rs = null;
            try {
                conn = getSQLConnection();
                ps = conn.prepareStatement("SELECT * FROM " + table + ";");
     
                rs = ps.executeQuery();
                int count = 0;
                if(rs.next()){
                    while(rs.next()){
                       count++;
                    }
                }
                return count;
            } catch (SQLException ex) {
                plugin.getLogger().log(Level.SEVERE, Errors.sqlConnectionExecute(), ex);
            } finally {
                try {
                    if (ps != null)
                        ps.close();
                    if (conn != null)
                        conn.close();
                } catch (SQLException ex) {
                    plugin.getLogger().log(Level.SEVERE, Errors.sqlConnectionClose(), ex);
                }
            }
            return 0;
        }
     
  2. Strahan

    Benefactor

    Wait, what? If the field is auto increment, it's auto increment. As in, you don't have to do that. Just insert data and the field should increment by itself, ergo "auto". To answer your question, just SELECT COUNT(*) from the table. But bear in mind, you totally do not have to do that.
     
  3. Yeah, that was my first guess but then I get error saying column users.id is empty therefore cannot execute the statement.

    Edit: This is what happens in the console when I take out the "ps.setInt(1, i + 1);"
    [​IMG] [​IMG]
     
    #3 M4ST3RX, Jun 3, 2018
    Last edited: Jun 3, 2018
  4. Strahan

    Benefactor

    Don't try to insert id at all, leave it totally out of the SQL statement. If MySQL doesn't see you trying to insert, it will auto increment.
     
  5. I got it. The problem was I created the database with id INT not is INTEGER. After changed the code where it creates the table it is auto incrementing without setting any id value.