Counting the number of tables in a SQLite DB

Question

I am creating a backup system that records the SQL queries required to create a pre-existing database using Java. I have it to the point that it has the queries however I cannot for the life of me get it to list all of the table creation queries at the start and I had an idea to get the number of tables in the database, then place the create table part in a loop that runs the amount of times that there are tables in the database, however I am at a roadblock. What would be the best way to achieve this using JDBC? Below is the section I want to loop.

while (/*loop contents here */)
            {
                ResultSet columns = databaseMetaData.getColumns(null, null, currentTable, null);

                System.out.print("CREATE TABLE " + currentTable + " (");

                while(columns.next())
                {
                    String columnName = columns.getString("COLUMN_NAME");
                    String columnType = columns.getString("TYPE_NAME");
                    /*String selectCol = "SELECT * FROM " + currentTable;
                    ResultSet allColumns = statement.executeQuery(selectCol);*/
                    System.out.print(columnName + " " + columnType + ", ");
                }

                ResultSet PrimKey = databaseMetaData.getPrimaryKeys(null, null, currentTable);
                System.out.print("PRIMARY KEY (");

                while(PrimKey.next())
                    {
                        if(!PrimKey.isFirst())
                        {
                            System.out.print(", ");
                        }
                        System.out.print(PrimKey.getString("COLUMN_NAME"));
                    }

                    System.out.print(")");

                ResultSet ForKey = databaseMetaData.getImportedKeys(null, null, currentTable);

                while(ForKey.next())
                {
                    if(!ForKey.isFirst())
                    {
                        System.out.print("FOREIGN KEY (" + ForKey.getString("PKCOLUMN_NAME") + ")");
                        System.out.print(" REFERENCES " + ForKey.getString("FKTABLE_NAME") + "(" + ForKey.getString("FKCOLUMN_NAME") + ")");                
                    }
                }
                System.out.print("); \n");//Ends the sql query.
            }

by the way, sqlite will return you all the create commands like the ones you are manually writing, if you use the ".dump tablename" command, if I am not mistaken. — vefthym, Feb 28 '20 at 00:07
@vefthym Not particularly, as I want the number of tables not the names? Unless you're hinting at something and I'm too stupid/tired to notice? — Jack Holden, Feb 28 '20 at 00:14
If I understand your question right, you are looking for the number of tables so that you can then iterate over each one of them, get their names and use it in the create statement. What my suggested link provides is a way to iterate through each table name, without requiring to know how many tables there are. I don't see any part of your code that is using the number of tables per se. — vefthym, Feb 28 '20 at 00:19
@vefthym My code currently outputs one CREATE TABLE then all the INSERT INTO for that relevant table, then moves onto the next CREATE TABLE, etc etc. I want it to create every table first and then input everything after they are all created. — Jack Holden, Feb 28 '20 at 00:22

vefthym · Answer 1 · 2020-02-28T00:41:12.140

I thought that the link I provided in comments would solve the problem, but let me elaborate more on how to use it, in case I was wrong, or I didn't get your question right:

ResultSet rs = databaseMetaData.getTables(null, null, "%", null);
while (rs.next()) {
    String currentTable = rs.getString(3));
    ResultSet columns = ...// the rest of your code goes here as is
}

That's if, quoting your question, your problem is that:

I cannot for the life of me get it to list all of the table creation queries at the start

Suggestion: you may want to consider easier, and less error-prone ways to back up a sqlite database, like using the .backup or the .dump commands that the sqlite3 command line tool offers. See this post for more details.

Counting the number of tables in a SQLite DB

1 Answers1