How can I create a database from a file using Java?-CodePudding

I am having issues creating a SQLite-database using a text file. I am supposed to create a members list with the following: (member) number, first name, last name, address, and telephone number (if any). I am using methods to create the db, but I can't seem to get it to work properly.

The text file is populated like this (the '...' is supposed to mean that there are others in the list):

1;John;Doe;Downing, Virginia;123456
2;Jane;Doe;Obispo 2, California;342532
...
...
14;Rich;Damons;Av.5, NY
...
...

As you can probably see, some of the members do not have a telephone number, which is what I think is causing the db to not be populated. The reason I believe this, is that I tried making my own list with all the info and that seems to work, but removing the telephone numbers gives me the following error Index 4 out of bounds for length 4

This is the method:

    private static void createNewTable() throws Exception {
        String url = "jdbc:sqlite:members.db";
        Connection con = DriverManager.getConnection(url);
        Statement stmt = con.createStatement();
        Scanner reader = new Scanner(new File("member_list.txt"));

        String sql = "create table Member(No integer primary key, "  
                "First varchar(50), "  
                "Last varchar(50), "  
                "Address varchar(50), "  
                "Telephone integer);";
        stmt.executeUpdate(sql);

        try (reader) {
            while (reader.hasNextLine()) {
                String[] db = reader.nextLine().split("[;]");
                String fName = db[1], lName = db[2], address = db[3];
                int no = parseInt(db[0]), tel = parseInt(db[4]);
                if (db.length == 5) {
                    sql = "insert into Member values("   no   ",'"   fName   "','"   lName   "','"   address   "',"   tel   ");";
                    stmt.executeUpdate(sql);
                } else {
                    sql = "insert into Member values("   no   ",'"   fName   "','"   lName   "','"   address   "',"   null   ");";
                    stmt.executeUpdate(sql);
                }
            }
        }

        con.close();
        out.println("Start: Creates table");
        showMessageDialog(null, "Start: Creates table");
    }

Any help is greatly appreciated!

I tried creating my own member list with all the information, which worked; I tried creating a member list without telephone numbers, which did not work.

CodePudding user response：

For entries that do not have a telephone number, the length of the string is 4. Hence, when you try to access db[4] you get ArrayIndexOutofBound exception.

Initialise the tel variable inside the if condition.

CodePudding user response：

The result of your split (db) will only have a length of 4 for members without a phone number, meaning its highest index will be 3.

int no = parseInt(db[0]), tel = parseInt(db[4]);

tries to access an index that is out of bounds for members without a phone number. Consider moving the declaration tel = parseInt(db[4]) inside the if statement that deals with members who do have a phone number, since it will only relevant for those cases anyways.