Reading a File Into a Derby Database

This is my latest attempt to read a text file into a Netbeans Derby database. The file contains 5 rows with each row containing 7 items delineated by commas. The program runs without any errors but the database is not updated at all. I would appreciate any help in fixing this code.

<% Connection connection = null;
PreparedStatement ps = null;

String urlanddatabasename = "jdbc:derby://localhost:1527/ProgramName

String userName = "root";
String password = "root";

Class.forName("org.apache.derby.jdbc.ClientDriver");
connection = DriverManager.getConnection(urlanddatabasename,userName,password);

try{
     String fileName = saveFile;
     File file = new File(fileName);
     Scanner inputStream = new Scanner(file);
     String[] array = new String[7];

     Statement statement = connection.createStatement();
     while(inputStream.hasNext()){//reads from the file until there are no items left
     String data = inputStream.next();
     array = data.split(",");
     String reportidString = array[0];

     String coursenameString = array[1];

     String tardiesString = array[2];

     String absencesString = array[3];

     String totalgradeString = array[4];

     String teachernameString = array[5];

     String schoolnameString = array[6];

     statement.executeUpdate("INSERT INTO report(reportid, coursename, tardies, absences, totalgrade, teachername, schoolname) values(reportidString, coursenameString,tardiesString, absencesString, totalgradeString, teachernameString, teachernameString, schoolnameString)");

                }
                inputStream.close();
            }catch(FileNotFoundException e){e.printStackTrace();}

        %>

Solution

It seems that you are using this code inside a JSP scriptlet. This is very bad style. Especially for code without any relation to the view representation.

So the first thing you should do, is to create an ordinary Java class and put that code inside. Then you will remark that Class.forName() throws a checked exception (ClassNotFoundException) and some other parts like DriverManager.getConnection(), connection.createStatement() and statement.executeUpdate() throw SQLException. You shouldn't let the servlet container catch these exceptions.

Then write a simple main() method to check if your code is working or even better a JUnit test.

You declared a PreparedStatement variable but never used it. Instead later you used a simple Statement. The first one fits better here. So use something like that:

ps = connection.prepareStatement("INSERT INTO "
     + "report(reportid, coursename, tardies, absences, totalgrade, teachername, schoolname) "
     + "values(?, ?, ?, ?, ?, ?, ?)");

Use the ? as a placeholder for the actual values.

Then later you set the values. Beware that the prepared statement index begins at 1. After the execution of all SQL inserts, you should commit the transaction. In case of an exception use abort().

while(inputStream.hasNext()){//reads from the file until there are no items left
  // ...
  ps.setString(1, reportidString);
  ps.setString(2, coursenameString);
  // ...
  ps.executeUpdate();
}
connection.commit();

Afterwards you should clean up and free the ressources. The best place is in a finally of a try block:

} finally {
  try {
    ps.close();
  } catch(SQLException e) {
    e.printStackTrace();
  }
  try {
    connection.close();
  } catch(SQLException e) {
    e.printStackTrace();
  }
}

Some further considerations:

If you really want to use plain JSP, then make use of the Front Controller pattern. So every request goes to the front controller servlet. There you decide what kind of action to execute and collect the data for the view. In the end forward to a JSP to create the view. Inside the JSP you should not use any scriptlets.

Consider to use a MVC framework like Struts or Spring MVC. But the current standard is Java Server Faces which is more component based.

Use a connection pool for the database connection.

Use logging instead of System.out.println() or e.printStackTrace()

To efficiently insert larger files into the database use batch inserts.