SQLite PreparedStatement java.sql.SQLException: The prepared statement has been final

[Johnatan56]

Hi guys,

I am currently working on a project but can't seem to get past this error.
I am using java to create/manipulate the SQLite database.

This is the class that deals with the database code, here is the creation of the charge table:

//create the charge table.
        try{
            statement.executeUpdate("CREATE TABLE charge(username TEXT NOT NULL, date DATETIME DEFAULT CURRENT_TIMESTAMP, charge REAL, PRIMARY KEY(username, date));");
        } catch (SQLException ex) {
            System.out.println("charge table creation failed. exception" + ex);
        }

And adding a new row:

public void createCharge(String username, double amount){
        try {
            System.out.println(username + amount);
            
            ps = connection.prepareStatement("INSERT INTO charge VALUES(?, ?, ?);");
            ps.setString(1, username);
            ps.setDate(2, DateConvert.toSQLDate(Date.valueOf(LocalDate.MIN)));
            ps.setDouble(3, amount);
            ps.executeUpdate(); //Line 170
            ps.clearParameters();

            System.out.println("Complete");
            
        } catch (SQLException ex) {
            Logger.getLogger(MontsRentalDatabase.class.getName()).log(Level.SEVERE, null, ex);
        }
}

I declared PreparedStatement ps right at the beginning and Connection connection as well.

In the other class:

public void createRentals(){
        //hardcoded for on database creation.
        db.createCharge("450", 100.00);
        db.createRental("450", 1);
        db.createCharge("450", 150.00);
        db.createRental("450", 4);
    }

db is an instance of the database creation class.

I am getting the exception (EDIT: adding the printout just before, the first createCharge is succesful):

450100.0
Complete
450150.0
SEVERE: null
java.sql.SQLException: The prepared statement has been finalized
	at org.sqlite.core.NativeDB.throwex(NativeDB.java:429)
	at org.sqlite.core.NativeDB.reset(Native Method)
	at org.sqlite.core.DB.executeUpdate(DB.java:878)
	at org.sqlite.jdbc3.JDBC3PreparedStatement.executeUpdate(JDBC3PreparedStatement.java:99)
	at server.RentalDatabase.createCharge(RentalDatabase.java:170)

Any help/explanation of what I am doing wrong would be appreciated.

Kind Regards,
J

EDIT:
for the SQL date:

public class DateConvert {
    
    public static java.sql.Date toSQLDate(java.util.Date date){
        return new java.sql.Date(date.getTime());
    }
    
    public static java.util.Date toJavaDate(java.sql.Date date){
        return new java.util.Date(date.getTime());
    }
}

 

[Trib]

I am going to take a guess.

This may fix your issue:
1) You are not closing your prepared statement object (ps) after "ps.clearParameters();"
OR
2) You can "reuse" your prepared statement.
Move this line "ps = connection.prepareStatement("INSERT INTO charge VALUES(?, ?, ?);");" to where your connection object gets created and then keep reusing the ps object. I warn you that it is not "thread safe".

Hope this helps,

Paul

 

[Johnatan56]

I am going to take a guess.

This may fix your issue:
1) You are not closing your prepared statement object (ps) after "ps.clearParameters();"
OR
2) You can "reuse" your prepared statement.
Move this line "ps = connection.prepareStatement("INSERT INTO charge VALUES(?, ?, ?);");" to where your connection object gets created and then keep reusing the ps object. I warn you that it is not "thread safe".

Hope this helps,

Paul

Hi Paul,
Thanks for the reply.
by close do you mean ps.close();? I will add it to the end of every method using the PreparedStatement, will get back to you on it in a moment.

The second one: there are multiple methods using the ps statement though, doing ps = connection.preparestatement(sql);

EDIT:
every method that ps.executeUpdate() now has a clearParameters() and close() statement. Still get the error at the same spot.

java.sql.SQLException: The prepared statement has been finalized
	at org.sqlite.core.NativeDB.throwex(NativeDB.java:429)
	at org.sqlite.core.NativeDB.reset(Native Method)
	at org.sqlite.core.DB.executeUpdate(DB.java:878)
	at org.sqlite.jdbc3.JDBC3PreparedStatement.executeUpdate(JDBC3PreparedStatement.java:99)

EDIT2: http://stackoverflow.com/questions/40184674/sqlite-preparedstatement-finalized I asked, supposedly it's a bug with sqlite driver. Going to see if I can find his version and use that instead. Damn net keeps crashing currently though, can't even download 2MB, something up with the exchange.

EDIT3: Solution: downgraded the SQLite driver, seems to be an issue with it displaying correct sqllite error messages. I was violating a primary key constraint due to TIMESTAMP happening in the same second, forgot to change it to milliseconds in the create statement.

EDIT4: Seems I wasn't getting milliseconds but a static date of today. Solution:
made an abstract class with this method:

 public static Date getToday(){
        return new Date(System.currentTimeMillis());
    }

Just use that to fill in the date for the preparedStatement. You'll also need something to convert it to a sql date (and back when reading):

public static java.sql.Date toSQLDate(java.util.Date date){
        return new java.sql.Date(date.getTime());
    }
    
    public static java.util.Date toJavaDate(java.sql.Date date){
        return new java.util.Date(date.getTime());
    }

 

[Genisys]

So what I hinted at in TK last night?

 

[Johnatan56]

So what I hinted at in TK last night?

Looking for it, but you seem to have edited it away.
I got a slight extension and I have one last bug to fix.

It just hangs, I think it's due to this sequel statement not returning anything to send. (not sure how to use resultSet, first time using Sqlite tbh, hopefully that's an easy mistake to fix?)
Database Java:

public ArrayList<Rental> allRentals(){
        rental = null;
        rental = new ArrayList<>();
        try {
            PreparedStatement srps = connection.prepareStatement("SELECT * FROM rentals;");
            rs = srps.executeQuery();
            while(rs.next()){
                int rent_id = rs.getInt(1); System.out.println(rent_id);
                String username = rs.getString(2);
                int movie_id = rs.getInt(3);
                Date withdrawn = rs.getDate(4);
                Date returned = rs.getDate(5);
                rental.add(new Rental(rent_id, username, movie_id, DateConvert.toJavaDate(withdrawn), DateConvert.toJavaDate(returned)));
            }
            return rental;
            
        } catch (SQLException ex) {
            Logger.getLogger(MontsRentalDatabase.class.getName()).log(Level.SEVERE, null, ex);
            return rental;
        }
    }

Server Java

public ArrayList<Rental> allRentals(){
        //use the database search in the MontsRentalDatabase to return all rentals.
        return db.searchRentals("1=1");
    }

Client side

public void serverGetRentals(){
        try {
            output.writeObject(6);
            output.flush();
            if((Boolean)input.readObject()){
                System.out.println("rentals bool 1 passed.");
                if((Boolean) input.readObject()){
                    System.out.println("rentals bool 2 passed.");
                    rentals = ((ArrayList<Rental>) input.readObject());
                    System.out.println("rentals saved. Pos 1: " + rentals.get(1).getUsername());
                }
            }
        } catch (IOException ex) {
            Logger.getLogger(TabbedView.class.getName()).log(Level.SEVERE, null, ex);
        } catch (ClassNotFoundException ex) {
            Logger.getLogger(TabbedView.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

This is the switch statement that calls it:

try {
                //First accept the string for client saying what task has to be done:
                /*
                *   1 - login
                *   2 - create Customer
                *   3 - search Customer
                *   4 - all movies
                *   5 - search for movie
                *   6 - all rentals
                *   7 - search rental
                *   8 - make rental
                *   9 - return rental
                *   10 - make charge
                *   11 - return location
                *   12 - return category
                *   13 - update rental
                *   14 - delete rental
                *   15 - create movie
                *   16 - edit customer
                */
                scrollAppend(outputServerLog, "Waiting for input from client on information request.");
                int nextInput = (int) inputClient.readObject();
                scrollAppend(outputServerLog, "Managed to get input for type.");
                //confirm that input has been read and allow client to send next package.
                outputClient.writeObject(true);
                outputClient.flush();
                
                switch(nextInput){
                    case 1: scrollAppend(outputServerLog, "Client is attempting to log on from: " + InetAddress.getLocalHost().getHostAddress());
                            Login login = (Login) inputClient.readObject();
                            login(login, clientSocket, outputClient);
                            break;
                    case 2: scrollAppend(outputServerLog, "Client is attempting to create account from: " + InetAddress.getLocalHost().getHostAddress());
                            createCustomer(clientSocket, outputClient, inputClient);
                            break;
                    case 3: scrollAppend(outputServerLog, "Client is searching for user from: " + InetAddress.getLocalHost().getHostAddress());
                            outputClient.writeObject(searchCustomer((String) inputClient.readObject()));
                            break;
                    case 4: scrollAppend(outputServerLog, "Client is requesting all movies from: " + InetAddress.getLocalHost().getHostAddress());
                            outputClient.writeObject(allMovies());
                            break;
                    case 5: scrollAppend(outputServerLog, "Client is searching for a movie from: " + InetAddress.getLocalHost().getHostAddress());
                            outputClient.writeObject(searchMovie((String) inputClient.readObject()));
                            break;
                    case 6: scrollAppend(outputServerLog, "Client is requesting all rentals from: " + InetAddress.getLocalHost().getHostAddress());
                            returnAllRentals(clientSocket, outputClient, inputClient);
                            break;
//not pasting all of it due to limit

May I pm you a link to look at please?

EDIT: Stackoverflow: http://stackoverflow.com/questions/...-add-object-of-resultset-answers-to-arraylist

 

[[)roi(]]

Null pointer exceptions. This is your rentals tables (as you provided)


Which means; that in this line:

rental.add(new Rental(rent_id, username, movie_id, DateConvert.toJavaDate(withdrawn), DateConvert.toJavaDate(returned)));

You are effectively trying to convert a null date value; but because you are only trapping for SQLExceptions this is effectively ignored. i.e. nothing is ever returned.

Three pointers here:

• Return statements must never be included in try / catch blocks, try to make this the last statement in your function call. Initialise the default value before the try/catch block; then in the block, do just the assignment of result; and finally at the end of the function block do your return (of rental)
• Trap all exceptions e.g. in this case you needed another catch all block, see code example below.
• Try to avoid using so much hidden input; making your functions PURE, simplifies not only reading your code, but also the debugging thereof.

This is the exception block you were missing; basically without something like this, your failed conversions of null pointers were simply being ignored & not reported, and having the result inside the try / catch meant nothing was ever returned i.e. client was still waiting.

 } catch (Exception ex) {
   System.out.println("All other exceptions" + ex.getMessage());
 }

 

[Johnatan56]

Null pointer exceptions. This is your rentals tables (as you provided)
View attachment 396812

Which means; that in this line:

rental.add(new Rental(rent_id, username, movie_id, DateConvert.toJavaDate(withdrawn), DateConvert.toJavaDate(returned)));

You are effectively trying to convert a null date value; but because you are only trapping for SQLExceptions this is effectively ignored. i.e. nothing is ever returned.

Three pointers here:

• Return statements must never be included in try / catch blocks, try to make this the last statement in your function call. Initialise the default value before the try/catch block; then in the block, do just the assignment of result; and finally at the end of the function block do your return (of rental)
• Trap all exceptions e.g. in this case you needed another catch all block, see code example below.
• Try to avoid using so much hidden input; making your functions PURE, simplifies not only reading your code, but also the debugging thereof.

This is the exception block you were missing; basically without something like this, your failed conversions of null pointers were simply being ignored & not reported, and having the result inside the try / catch meant nothing was ever returned i.e. client was still waiting.

 } catch (Exception ex) {
   System.out.println("All other exceptions" + ex.getMessage());
 }

Thank you, reading up on pure functions now.

So for the date conversion should I make it return null?
So

    public static java.sql.Date toSQLDate(java.util.Date date){
        try{
            return new java.sql.Date(date.getTime());
        }catch (Exception e){
            System.out.println("Not a proper date: " + e);
            return null;
        }
    }

So it will always return something. Or should I use a dummy date that I know is wrong but it will let the program continue? Also busy adding Exception e to all catch statements with the name of the function that's throwing the exception in the System.out.println.

So change it to something like this:

    public ArrayList<Movies> searchMovie(String search){ 
        dvd = new ArrayList();
        try {          

    //(movie_id INT PRIMARY KEY ASC, name TEXT NOT NULL, date DATETIME, publisher TEXT, category_id, location_id, FOREIGN KEY(category_id) "
    //                    + "REFERENCES genre(category_id), FOREIGN KEY(location_id) REFERENCES stores(location_id));");
           
            PreparedStatement smps = connection.prepareStatement("SELECT * FROM movies WHERE movie_id LIKE '%?%' OR publisher LIKE '%?%';");
            smps.setString(1, search);
            smps.setString(2, search);
            
            rs = smps.executeQuery();
            while(rs.next()){
                int movie_id = rs.getInt(1); System.out.println(movie_id);
                String movie_name = rs.getString(2); System.out.println(movie_name);
                Date movie_date = rs.getDate(3);
                String movie_publisher = rs.getString(4);
                int cat_id = rs.getInt(5);
                int loc_id = rs.getInt(6);
                dvd.add(new Movies(movie_id, movie_name, DateConvert.toJavaDate(movie_date), movie_publisher, cat_id, loc_id));
            }
            
            rs.close();
            smps.clearParameters();
            smps.close();
        } catch (SQLException ex) {
            Logger.getLogger(MontsRentalDatabase.class.getName()).log(Level.SEVERE, null, ex);
        } catch (Exception e){
            System.out.println("Search movie(string) exception: " + e);
        }
        return dvd;
}

Dvd is initialized before the try/catch, the DateConvert now returns null if it can't convert it with an error message, there is a catch exception saying what function is failing. Busy going throughout the program and changing it, will report back. Even on failing to add it to an array an empty array is still sent back. It's already handed in, but want to fix it.

 

[Johnatan56]

Okay, so added it to everything, first exception (i have changed the main client code a little bit as well),

search Customer(String) Exception: java.lang.ArrayIndexOutOfBoundsException: 0

which means the search customer String is triggering the catch.

public ArrayList<Customer> searchCustomer(String search){
    //CREATE TABLE users(username TEXT PRIMARY KEY, f_name TEXT, l_name TEXT, tel_no INT, balance REAL DEFAULT 0.0);");
        customer = null;//reset rental arraylist.
        customer = new ArrayList();
        try{
            PreparedStatement sps = connection.prepareStatement("SELECT username, f_name, l_name, tel_no, balance FROM users WHERE username LIKE '%?%';");
            sps.setString(1, search);
            rs = sps.executeQuery();
            while(rs.next()){
                String username = rs.getString(1);
                String f_name = rs.getString(2);
                String l_name = rs.getString(3);
                int tel_no = rs.getInt(4);
                double bal = rs.getDouble(5);
                customer.add(new Customer(username, f_name, l_name, tel_no, bal));
            }
            sps.clearBatch();
            sps.close();
            
        } catch (SQLException ex) {
            Logger.getLogger(MontsRentalDatabase.class.getName()).log(Level.SEVERE, null, ex);
        } catch (Exception e){
            System.out.println("search Customer(String) Exception: " + e);
        }   
        return customer;
    }

Okay, so going to print out the values it gets on create, could be the balance is 0.

EDIT
Adding more checks.

public ArrayList<Customer> searchCustomer(String search){
    //CREATE TABLE users(username TEXT PRIMARY KEY, f_name TEXT, l_name TEXT, tel_no INT, balance REAL DEFAULT 0.0);");
        System.out.println("Starting the search");
        customer = new ArrayList();
        System.out.println("Starting the try");
        try{
            System.out.println("Starting the prepared statement");
            PreparedStatement sps = connection.prepareStatement("SELECT username, f_name, l_name, tel_no, balance FROM users WHERE username LIKE '%?%';");
            sps.setString(1, search);
            System.out.println("Executing the statement");
            rs = sps.executeQuery();
            while(rs.next()){
                System.out.println("Saving first result.");
                String username = rs.getString(1);
                System.out.println(username);
                String f_name = rs.getString(2);
                System.out.println(f_name);
                String l_name = rs.getString(3);
                System.out.println(l_name);
                int tel_no = rs.getInt(4);
                System.out.println(tel_no);
                double bal = rs.getDouble(5);
                System.out.println(username +f_name+l_name+tel_no+bal);
                customer.add(new Customer(username, f_name, l_name, tel_no, bal));
            }
            sps.clearBatch();
            sps.close();
            
        } catch (SQLException ex) {
            Logger.getLogger(MontsRentalDatabase.class.getName()).log(Level.SEVERE, null, ex);
        } catch (Exception e){
            System.out.println("search Customer(String) Exception: " + e);
        }   
        return customer;
    }

Output:

run:
-------- SQL JDBC Connection Initializing ------
SQL JDBC Driver Registered!
You made it, take control your database now!
all table execute statements have been run.
Starting search
Starting the search
Starting the try
Starting the prepared statement
search Customer(String) Exception: java.lang.ArrayIndexOutOfBoundsException: 0

So the resultset could be null? Is the SQL query invalid or not. Going to add a quick try to print out the value of rs before the while.

EDIT2:
No, it's failing at this line
rs = sps.executeQuery();
Is my query invalid or is it due to no results? Because in order to create a login which executes successfully, one has to have created a user account and those went without a hitch (as in didn't throw exception).
The query:

PreparedStatement sps = connection.prepareStatement("SELECT username, f_name, l_name, tel_no, balance FROM users WHERE username LIKE '%?%';");
            sps.setString(1, search);
            System.out.println("Executing the statement");
            rs = sps.executeQuery();

EDIT3:

System.out.println("Starting the prepared statement");
            PreparedStatement sps = connection.prepareStatement("SELECT username, f_name, l_name, tel_no, balance FROM users WHERE username LIKE '%?%';");
            sps.setString(1, search);
            System.out.println("Sps meta-data: " + sps.getMetaData() + "Sps parameter data: "+sps.getParameterMetaData());
            System.out.println("Executing the statement");
            rs = sps.executeQuery();
            System.out.println("RS was successfully assigned the sps query with value: " + rs);

-------- SQL JDBC Connection Initializing ------
SQL JDBC Driver Registered!
You made it, take control your database now!
all table execute statements have been run.
Starting search
Starting the search with: 1=1
Starting the try
Starting the prepared statement
search Customer(String) Exception: java.lang.ArrayIndexOutOfBoundsException: 0

The search is invalid, going to change it to a 1, since I made some logins that are 1234, 1011 etc.

EDIT4:
Changed the search value to 1,

-------- SQL JDBC Connection Initializing ------
SQL JDBC Driver Registered!
You made it, take control your database now!
all table execute statements have been run.
Starting search
Starting the search with: 1
Starting the try
Starting the prepared statement
search Customer(String) Exception: java.lang.ArrayIndexOutOfBoundsException: 0

Mhm. Going to change it so that it returns all instead, see what happens.

EDIT5:
And still

-------- SQL JDBC Connection Initializing ------
SQL JDBC Driver Registered!
You made it, take control your database now!
all table execute statements have been run.
Starting search
Starting the search with: 1
Starting the try
Starting the prepared statement
search Customer(String) Exception: java.lang.ArrayIndexOutOfBoundsException: 0

After changing it to:

System.out.println("Starting the prepared statement");
            PreparedStatement sps = connection.prepareStatement("SELECT username, f_name, l_name, tel_no, balance FROM users;"); // WHERE username LIKE '%?%'
            sps.setString(1, search);
            System.out.println("Sps meta-data: " + sps.getMetaData() + "Sps parameter data: "+sps.getParameterMetaData());
            System.out.println("Executing the statement");
            rs = sps.executeQuery();

EDIT6: Woops, it can't assign search, lol.

EDIT7: Working now


So there is an issue with the prepared statement phrasing.

 

[Willie Trombone]

Nice...
Reps for droid!

 

[Johnatan56]

Nice...
Reps for droid!

"You must spread some Reputation around before giving it to [)roi(] again."
I already gave him reps for his articles.

 

[Johnatan56]

Thank you, reading up on pure functions now.

So for the date conversion should I make it return null?
So

    public static java.sql.Date toSQLDate(java.util.Date date){
        try{
            return new java.sql.Date(date.getTime());
        }catch (Exception e){
            System.out.println("Not a proper date: " + e);
            return null;
        }
    }

Just realized there that I returned inside the try/catch. Would it be all right in this case or should I change it to:

public static java.sql.Date toSQLDate(java.util.Date date){
        java.sql.Date newDate;
        try{
             newDate = java.sql.Date(date.getTime());
        }catch (Exception e){
            System.out.println("Not a proper date: " + e);
        }
        return newDate; //will return null if exception?
    }

 

[[)roi(]]

Thanks guys.

@Johnatan56.
Sorry been out most of the day; but here's a response to some of your questions.

Caution
Be careful with Try/Catch blocks; these are only there to allow you to fail gracefully, by which I mean you could write code to try rectify around a fault to prevent an application from crashing. For example:

• If you try to run a query and get a response that indicates the connection is closed; you could try to reestablish the connection and resubmit the query. However that doesn't negate that you should have checked that the connection was ok, before you submitted the query. i.e. exceptions should be use only for "if everything fails" scenarios.

Personally I not a fan of like Try/catch blocks, and tend to avoid them; but there are times you won't be able to avoid them completely, e.g. with I/O (SQL == I/O).

Note: Try/catch blocks can easily hide errors that you should be dealing with; when writing new code, it's better to use "assert" if something should never be uninitialized rather than just outputting errors to the log. Principle of it being better to fail fast, early and often -- rather than catch / hide. The reason why should be obvious -- so you can write code to better manage your failures.

Null checking
Back to your usage; please don't use try/catch block for null checking -- rather check if the value is null before you try to convert it; that way you can avoid the exception completely. For example:

if (value != null) 
{
  // Convert the value
}

You can of course also have an else clause for what happens when it is null -- if for example, you wanted a date to default to the current date?, else maybe just leave it uninitialised.

Optional Types
As of Java8 there is a much better way to deal with null exceptions, called Optional Types; the use of this is optional i.e. your choice. Many languages (C#, Java, ...) have implemented these wrapper types as a solution to the null problem; however aside from Swift (Apple's new language) and Rust -- the implementation for the others is optional, and there lies most of the problem. With Swift and Rust (FYI); no value can ever be left uninitialised; basically the compiler will keep complaining until you properly handle initialisation of every variable.

With Java, C#, ... sadly the compiler offers no such help; meaning it doesn't reconcile whether a variable / instance is initialised or even not used. Which means that you have to do the work yourself i.e. you must get into the habit of checking whether something is null before you try to use it. Even though Optional Types in Java 8 are not perfect; they still IMO offer some benefits, especially inline option chaining. You can find more on this here: http://www.oracle.com/technetwork/articles/java/java8-optional-2175753.html

Class design
Some of the code that you have in the MontRentalDatabase.java should be moved to the class: the constructor, and/or the property setter. For example: checking if a date is null before you try to convert it; really belongs inside the class; performing your Date conversions in DateConvert.java is sort of ok; but then don't wrap this in a try/catch; that just hides where the problem is. Either throw the error up the call stack, or handle it more gracefully with null checking within the class.

Program design / Global instance variables / Pure functions
Variables like "connection", "rs", "rental", ...
Following Pure functions concepts most of these should be avoided, because you're dealing with unknown state unnecessarily introduced into your function. If however you need global or instance state; then look at implementing a singleton... but even then don't overdo it because it hides dependencies, violates SRP, and worse yet they make testing difficult, if not impossible.

Ps. 1 final suggestion, try to keep your questions concise; adding so much code should be avoided (link to your code if you must); and rather ask a lot of small questions, rather than 1 big lengthy one.

 

[[)roi(]]

Just realized there that I returned inside the try/catch. Would it be all right in this case or should I change it to:

public static java.sql.Date toSQLDate(java.util.Date date){
        java.sql.Date newDate;
        try{
             newDate = java.sql.Date(date.getTime());
        }catch (Exception e){
            System.out.println("Not a proper date: " + e);
        }
        return newDate; //will return null if exception?
    }

This is not ideal; why not just check for a null?

So instead of all this code, replace it with a simple ternary evaluation, for example:

public static java.sql.Date toSQLDate(java.util.Date date)
{
  return (date != null) ? java.sql.Date(date.getTime()) : null;
}

Now if that's acceptable, you might want to ask why you even need this DateConvert class; could the ternary part not just live inside a class getter?

 

[Johnatan56]

This is not ideal; why not just check for a null?

So instead of all this code, replace it with a simple ternary evaluation, for example:

public static java.sql.Date toSQLDate(java.util.Date date)
{
  return (date != null) ? java.sql.Date(date.getTime()) : null;
}

Now if that's acceptable, you might want to ask why you even need this DateConvert class; could the ternary part not just live inside a class getter?

I had the above, changed it to the try catch, was reading through the pure function thing you sent. So then: "I/O operations are taken to fail when the input sequence does not describe the operations actually taken since the program began execution." applies?

Well, the issue was that I had was that I was displaying the date and saving it in the database. SQLite refused to save the date if it was in java.util.Date, but worked fine if it was sql.Date.

 

[[)roi(]]

I had the above, changed it to the try catch, was reading through the pure function thing you sent. So then: "I/O operations are taken to fail when the input sequence does not describe the operations actually taken since the program began execution." applies?

Well, the issue was that I had was that I was displaying the date and saving it in the database. SQLite refused to save the date if it was in java.util.Date, but worked fine if it was sql.Date.

With regular I/O processes, you would encapsulate this in a reusable function or class; that included the ability to retry and/or fail gracefully. For example:

• input param: SQL statement
• Internally: All the code to set-up, tear-down and retry (which again could be encapsulation in a function that only deals with connection state, and another with the query)
• output: ResultSet (that could alternatively be wrapped in an Optional type)

Then after calling this function, you would check if the response was null.

As for the reverse: Class -> SQL table; why not just have a instance function that converts all the appropriate instance variables to a format that is ready for insert / update on the DB.

 

[[)roi(]]

Btw is this for a varsity assignment, personal growth or is this a contracted piece of work?

Did you consider approaching this differently? Using for example: a JSON web service.

 

[Willie Trombone]

Btw is this for a varsity assignment, personal growth or is this a contracted piece of work?

Did you consider approaching this differently? Using for example: a JSON web service.

+1 on the JSON web service - get to know these and you'll find a million uses for them. Secure I/O for one - don't expose the sensitive stuff.

 

[[)roi(]]

@Johnatan56
Here's a rough example of how you could alter your code to make things a bit more manageable.

For this I created:

• SQLType.java -- an enumeration type that allows me to specify the conversion type for a returned SQL value
• SQLite.java -- A singleton instance class -- which always returns an initialized DB connection. Basically it only initializes once, or if the connection closed.
• SQLiteDB.java -- A generic conversion function to more safely convert SQLTypes. A getAllRentals() function which returns an ArrayList of Rental
• Rental.java -- a reworked version of your Rental class, including a new function called newFromSQLRS, that initialise a single instance of Rental from a ResultSet.
• main.java -- an example of how we retrieve all the rentals with this approach.

PS. I've specifically kept to a more simple OOP style to make this hopefully easier for you (or anyone else) to understand. Let me know if anything is confusing.

Anyway here's the code as a single Github Gist; create a project with the filenames as show, and it should run without any modification (except the DB path of course):

• https://gist.github.com/AfricanSwift/b19325ea0df97807d6d32a166c0feb6d

In you more complete version, you'd probably also want to strip out the while loop in allRentals function and try to make that a generic reusable function for the other tables. Anyway, hopefully this gives you some ideas.

 

[Johnatan56]

Btw is this for a varsity assignment, personal growth or is this a contracted piece of work?

Did you consider approaching this differently? Using for example: a JSON web service.

Varsity assignment, handed it in but correcting it for personal growth. (EDIT: Therefore no JSON web service, client/server using sockets with server accessing/managing the database, pure java and sql, it was either sqlite or derby and I went with sqlite)

@Johnatan56
Here's a rough example of how you could alter your code to make things a bit more manageable.

For this I created:

• SQLType.java -- an enumeration type that allows me to specify the conversion type for a returned SQL value
• SQLite.java -- A singleton instance class -- which always returns an initialized DB connection. Basically it only initializes once, or if the connection closed.
• SQLiteDB.java -- A generic conversion function to more safely convert SQLTypes. A getAllRentals() function which returns an ArrayList of Rental
• Rental.java -- a reworked version of your Rental class, including a new function called newFromSQLRS, that initialise a single instance of Rental from a ResultSet.
• main.java -- an example of how we retrieve all the rentals with this approach.

PS. I've specifically kept to a more simple OOP style to make this hopefully easier for you (or anyone else) to understand. Let me know if anything is confusing.

Anyway here's the code as a single Github Gist; create a project with the filenames as show, and it should run without any modification (except the DB path of course):

• https://gist.github.com/AfricanSwift/b19325ea0df97807d6d32a166c0feb6d

In you more complete version, you'd probably also want to strip out the while loop in allRentals function and try to make that a generic reusable function for the other tables. Anyway, hopefully this gives you some ideas.

Part of the assignment was that I am not allowed to touch the Rental.java class sadly. Has to be client/server with the server renting and the client using serialized files. Downloading the version you made, will get back to you on it in 2/3 days with any questions I might have.

Thanks for doing this.

EDIT: I see what you did with optional, that's a really nice example of it's use for me.
Had a quick glance, so I would integrate this into the server's GUI class and do something similar for customer. Will try and make something similar based on what you have done. This is way above anything I could have done on my own.
There's another project I am working on to do with setting proxy's, I have currently taken a break from it until the holidays, would it be all right if I ask you to take a look at it when I resume work on it in about a month?

 

[Pho3nix]

@Johnatan56
Here's a rough example of how you could alter your code to make things a bit more manageable.

For this I created:

• SQLType.java -- an enumeration type that allows me to specify the conversion type for a returned SQL value
• SQLite.java -- A singleton instance class -- which always returns an initialized DB connection. Basically it only initializes once, or if the connection closed.
• SQLiteDB.java -- A generic conversion function to more safely convert SQLTypes. A getAllRentals() function which returns an ArrayList of Rental
• Rental.java -- a reworked version of your Rental class, including a new function called newFromSQLRS, that initialise a single instance of Rental from a ResultSet.
• main.java -- an example of how we retrieve all the rentals with this approach.

PS. I've specifically kept to a more simple OOP style to make this hopefully easier for you (or anyone else) to understand. Let me know if anything is confusing.

Anyway here's the code as a single Github Gist; create a project with the filenames as show, and it should run without any modification (except the DB path of course):

• https://gist.github.com/AfricanSwift/b19325ea0df97807d6d32a166c0feb6d

In you more complete version, you'd probably also want to strip out the while loop in allRentals function and try to make that a generic reusable function for the other tables. Anyway, hopefully this gives you some ideas.

Very nicely done.. Rep given