issue related to JTable and SQLite database

[zishan khan]

hello ranchers,
facing problem, I had completed a contact book which is used to store addresses, database I am using is sqlite, every thing is working smooth when I am running and testing the app in my pc but when I run the same app after creating jar/webstart(clean build netbeans) jtable not populating the data from sqlite database, however when I am storing the contacts its storing it in the databsae, i also hva a search feature which do sqlite database calls and it is searching fine when I am running in jar, the only problem is
through jar my jtable is not showing data, other wise full software is working perfect when I am running it without jarring. to sum up.

when i am running the app through netbeans run its working perfect.
1 add/insert/ working.
2 jtable populating
3 every thing is fine

when i am running the app after creating jar netbeans clean build
1 insert/add working
2 app working perfectly except jtable is not populating the data

Its a confusing situating, when insert is working perfect that means there is no problem in database, software is getting through it but at the same time jtable is not populating the data which I am doing in main programs constructor call.

my code for connecting with sqlite: sqliteToJtable class

 public Connection dbConnection() throws Exception {
        try {
            databasePath = loader.getResource("JTableCall/database/contactBook.db");
            String database = databasePath.getPath();
            int sg = database.indexOf("/");
            String st = database.substring(sg + 1, database.length());
            String databaseFullPath = "jdbc:sqlite:" + st;
            Class.forName("org.sqlite.JDBC");
            connection = DriverManager.getConnection(databaseFullPath);     
        } catch (Exception e) {
            e.printStackTrace();
        }
        return connection;
    }

     public Vector getPersonsData() throws Exception {
        pdata = new Vector<Vector<String>>();
        Connection con = dbConnection();
        PreparedStatement pre = con.prepareStatement("Select * from personsdata");
        rs = pre.executeQuery();
        while (rs.next()) {
            persons = new Vector<String>();
            persons.add(rs.getString(1));
            persons.add(rs.getString(2));
            persons.add(rs.getString(3));
            persons.add(rs.getString(4));
            persons.add(rs.getString(5));
            persons.add(rs.getString(6));
            persons.add(rs.getString(7));
            persons.add(rs.getString(8));
            persons.add(rs.getString(9));
//           persons.add(rs.getString(10));
            pdata.add(persons);
        }
        if (con != null) {
            con.close();
        }
        return pdata;
    }

2. My main programs constructor call where I am populating jtable and its not working in jar

 public enhanceContactBook() {
        sqliteToJTable stj = new sqliteToJTable();
        try {
            data = stj.getPersonsData();     //// here jtable data is getting
            System.out.println(data);
        } catch (Exception e) {
            System.out.println("stj error");
            e.printStackTrace();
        }
        header = new Vector<String>();
//        header.add("Person Number");
        header.add("First Name");
        header.add("Last Name");
        header.add("Telephone");
        header.add("addressline1");
        header.add("addressline2");
        header.add("county");
        header.add("postcode");
        header.add("Notes");
        initComponents();
        progressbar.setVisible(false);
        progressLabel.setVisible(false);
        printBusyLabel.setVisible(false);
        if(!isImagesDirectoryExist()){
            createImagesDirectory();
        }else{
        File[] roots = File.listRoots();
        String driveLetter = roots[0].getAbsolutePath();
        pathToSaveImages = driveLetter+"enhanceContactBook/images";
        }
        if(!isMailSettingsDirectoryExist()){
            createMailSettingsDirectory();
        }
    }

any help appreciated.

thank you

zishan

[Martin Vashko]

From your code I understood that you're trying to distribute your database file as part of the JAR file. If I'm not mistaken in this assumption, then this cannot work: resources inside JAR files cannot be modified, at least not without some very special handling.

You should also verify what records are you fetching from the database in the getPersonsData() function, possibly again by printing them out to the console. I suspect you actually don't fetch any records there. I guess that by calling dbConnection() a fresh copy of the database file is extracted from the JAR, and therefore does not contain any changes that were made to any other database obtained by previous call to dbConnection() - not even to the copy that contains changes from your INSERT statements.

A final unrelated note: use list of columns in your SELECT clause, not SELECT *. It will help with the readability and maintenance of your code.

[zishan khan]

thank you for the reply,
martin, i am having this assumption regarding embedded databases as I googled. earlier I was using mysql but the fact is i dont want to use mysql as it will needs installation nor i want place database on the server. I require a database which will sit inside the app, i researched google and found three embedded databases will do the job derby,sqlite, third one i forgot sorry. so I used sqlite for this, i had gone through sqlite docs and found that this can be done.

lets forget about the story, according to you how should i distribute my app with the database then!!!

i am doing debugging with it..as to why jtable is not getting records..

but let me know please as I dont want to go with the wrong end of the stuff as you said that jarring databases is not possible then whats the alternate!

thank you

zishan

[Martin Vashko]

I would do it like this:

Implement a functionality in your app that will allow the user to choose a location for the database file (probably at the first use), remember that location and use it in subsequent work. You might even allow the user to create several such databases and switch between them (something similar to what MS Access does). That way your users will be aware where the data are stored and will be able to properly backup them if they want.

When creating a new database file, you might just copy the resource from JAR to the file at the location the user indicated.

I don't consider myself to be an expert in this area, though. Others might have better suggestions.

[zishan khan]

you are right, this might be the solution.

if(!isDatabaseExist){
createDatabaseOnce()//create it
}

But i am not convinced to implement it when you actually do the job with the use of an embedded database.
would you please consult with collegues and shed light as to when and where we should actually use embedded databases?
basiclly the query is , is it must to create the embeddded database at runtime or we can make it and put some data and use it in our application??

thanks again fr help

zishan

[Martin Vashko]

My opinion is that you should let users of your app control their data. That the database engine is embedded is not important in this context. I personally would not seriously use an application that would not let me backup the data I have entered into it. Your application seems to handle contacts. Contacts are one of the most important (and most sensitive) data of all, just behind accounting perhaps. If you really embedded the data into your application somehow, how would your users update to a newer version of the application?

Of course, your initial database might be preloaded with some sample data. This would probably be much better than creating the initial database completely empty. I'd do it that way.

Unfortunately, I cannot consult this problem with my colleagues. By "others" in "Others might have better suggestions" I was referring to other Java ranchers out there.

However, this is probably not an appropriate forum for such discussion, as it has nothing to do with Swing. You might try to reformulate the question and post it as a new topic in more appropriate forum (JDBC or Java in General forums would seem better suited to me, as there is no forum dedicated to design issues specifically).

[zishan khan]

well thank you martin for the discussion,
I would better take the approach of creating the database by the user instead of jarring it.
backup is not a prob as my app has the reporting and exporting feature.
i though no user will mess with the database if accidently they delete it, so I though to pack up in my jar. the reason behind all this.

all I know with embedded databases is users can do all operations insert/edit/delete without any problem. so what if my database resides in jar or on harddisk, if it is in jar users can able to insert data and fetch it, if its in harddisk the same is true. however if it is in jar it is much safe as you know your app will work 100%.

but the question is still unclear to me as to is it necessary to create the database at runtime? or we can simply create it before hand,jar it and distribute!!

thanks again

zishan

[Martin Vashko]

Regarding backup: for me the export feature is not a backup tool. I might have several dozens of applications. To trigger some sort of export operation in every of them, even if script-based, would be too inconvenient for me. I'd want to be able to backup my user directory and be done. Bad enough I have to backup parts of Windows registry for some applications.

The problem with JAR is that, as far as I know, Java does not support modification of resources in JAR files. There might be some libraries to do it, but another problem is there: you'd have to pack the database file into JAR after every modification of that file, otherwise the JAR would contain outdated data and if your application crashed or power failure occurred before it got updated, users might lose changes that didn't make it into JAR file. Lots of hassle in my opinion.

Moreover, I understand you want to keep the db file close to your application's JAR file. Some years ago we were deploying application which had a default database stored next to a program (it was not a Java application and was Windows based, the db file was in Program Files subdirectory). Every upgrade was a nightmare: user had to manually back up the default database file, upgrade the application and then restore the database file, as a new one was deployed as part of the install procedure. A few databases were lost as users didn't do it this way and they didn't have backups, these incidents were very unpleasant. When we moved the database to be independent of the executables, it all became much simpler.

Even worse, in some environments (Windows with strict access controls) the OS might prevent you from modifying files in directories reserved for executables (ie. Program Files directory) as a protection measure. So if you manage to tightly couple the database with the JAR, you might run into problems of this kind. Also, if you have to digitally sign the JAR for any reason (Webstart, applet), you obviously cannot modify it at all.

I don't say that the solution I suggest is the best, I only want to point out problems I've personally run into and that I think you'll also encounter with your approach.

[zishan khan]

thank you martin for pointing out the drawbacks of packing database in jar, i will obviously not going to do that now andI wil certainly modify my code.
its time create the database and hand it to the user....

thanks for sharing your exp. i appreciate your time and help.

have a great day!!

pack() now.

zishan