Uploading and Downloading CSV Files from a Website Database using CSV Manager

There's one other little detail. CSV is a text file format. We need to convert all the data from the database table into text strings. To do this we'll use a special converter object that takes in a ResultSet and returns a string array.

And don't forget we have the same problem in reverse when someone uploads a CSV file. In that case we have to convert from text to a Java object.

If this all sounds like too much hard work, don't worry! CSV Manager comes with a utility API that does the job for us. All the grisly details are covered in the documentation of the StringConverter interface.

So how do we know which Java objects to convert to and from strings? We'll use the JDBC API to find out the database table column types. Then we just match them up with the right StringConverter object. We'll look at the details later on.

First we need a database connection. In the example code the connection is obtained in classic JDBC style using a DriverManager. In your own application you'll probably use the more modern DataSource approach. We pass the database connection to our CsvDownloader object so that it can load the data from the database.

Next, we set up the HttpServletResponse object with some HTTP headers that will cause the user's browser to save our data as a CSV file. These are really just magic incantations — there are plenty of web development websites that explain them in excruciating detail. For our purposes, we just plug and play.

Finally, we call the process method of our CsvDownloader. This executes the exporting code we saw already. We pass in the output stream of the HTTP response, and a properties object to control the CSV Manager settings. Using the HTTP response output stream allows us to stream the CSV file down to the user. This is much faster than creating the entire CSV file in memory first.

Here's the code:

Connection con = getConnection();
csvDownloader.setDatabaseContext( con, mTableName );

response.setContentType("text/csv");
response.setHeader( "Content-disposition", "attachment; filename="+mTableName+".csv");

csvDownloader.process( pResponse.getOutputStream(), properties);

And that's pretty much it for downloading. We'll tie it all together and look at the whole application in a moment. But first: uploading!

This is a little bit more involved. The process we've imagined so far is that Laura, our customer, has downloaded the CSV file, imported it into Excel, made some changes, and then saved it again. Now she wants to upload it to her website.

It's very important to remember at this point that the order of data fields in the CSV file must match the columns that we exported. This ensures that the right data goes into the right database table column. Of course, you could extend the code to allow for a different field order, or missing fields, but we'll assume for the moment that we need all the fields.

To make our lives easier, let's use a third-party component to handle the file uploading. We'll use the venerable FileUpload from the Apache Commons project. We'll plug this into our servlet to handle upload requests. And there are more magic incantations — we need to use a special syntax for the HTML form element:

<form name="uploadform" action="updown" enctype="multipart/form-data" method="post">

The encoding type enctype attribute causes the browser to send the uploaded file to our servlet in MIME format (also used for email attachments). The FileUpload component know how to handle this.

Ok. Let's look at the upload code:

Connection con = getConnection();
csvUploader.setDatabaseContext(con, mTableName, mIdentityColumn);

csvUploader.process(pIn, pProperties);

pResponse.sendRedirect("upload.htm");

It looks pretty much the same as the download code, except that we use a CsvUploader object (we'll look at that in the next section)

We also use another variable, mIdentityColumn. This is the name of the database table primary key column. This is how we will identity which row of data we are updating. Again, for security reasons, this variable is hard-coded in our example.

The CsvUploader works in the same was as the CsvDownloader. It has a process method that does all the hard work for us. Once the file has been uploaded and processed, the only thing left to do is to redirect the user back to the upload HTML page, and display some statistics about the upload.

You can see from the upload processing code that we use three PreparedStatements; one to see if a record already exists; one to update the record if it does; and one to insert a record if it is new. The SQL for these statements is constructed by three utility methods. We'll look at one of them quickly, just to give you a feel for the code.

public PreparedStatement makeUpdate( String[] pColNames, String pTableName, 
  String pIdentityColumn, Connection pConnection ) 
throws Exception 
{
  StringBuffer sql = new StringBuffer( "UPDATE "+pTableName+" SET ");
  for( int colI = 0; colI < pColNames.length; colI++ ) {
    sql.append( 0 == colI ? "":", " );
    sql.append( pColNames[colI]+" = ?" );
  }
  sql.append(" WHERE "+pIdentityColumn+" = ?;");
  return pConnection.prepareStatement( sql.toString() );
}

We use the question mark syntax to mark the location of the data field values. The PreparedStatement will insert the data correctly for us, so that we don't have to worry about escaping apostrophes.

For the UPDATE statement we loop through each column and add it to the SET clause. We use the identity column to identify the row that we want to update.

Let's go back to the main upload loop. Here it is again:

while( csvldr.hasNext() ) {
  String[] data = csvldr.next();

  conv.setData( 1, idcol, data[idcol], selectsql );
  rs = selectsql.executeQuery();
  
  boolean exists = rs.next();
  rs.close();
  
  if( exists ) {
    conv.setData( data, updatesql );
    conv.setData( data.length+1, idcol, data[idcol], updatesql );
    updatesql.executeUpdate();
  }
  else {
    conv.setData( data, insertsql );
    insertsql.executeUpdate();
  }
}

csvldr.end();
mNumLines = csvman.getLineCount();

We keep asking the CsvLoader if there are any more lines in the CSV file. For each line, we get the data fields from the line as the data string array.

Then we check to see if this record already exists in the database. We use the Converter to set the value of the identity field for this row in the SELECT query. If there is at least one row, then we know that the record already exists. In that case, we use an UPDATE query to update the database row with the new data from the CSV file. If it does not exist, we INSERT the data into a new database table row.

Finally, when all the lines of the CSV file have been read, we let the CsvLoader know that we're done by calling its end method. We also record the final total of lines in the CSV file so that we can report it to the user.

Converting the string values in a CSV file into proper data, and converting back proper data from the database into string values for CSV data fields is the trickiest part of this example. Luckily, CSV Manager makes this easy. CSV Manager includes a set of utility classes for exactly this purpose. These StringConverter utility classes can convert all the common Java data types into and out of strings, so you don't have to worry about doing it yourself.

Here's part of the Converter object code, so you can see how StringConverters are used:

if( Types.INTEGER == pColType 
    || Types.TINYINT == pColType 
    || Types.SMALLINT == pColType 
    || Types.BIGINT == pColType
    || Types.BIT == pColType ) {
  mSetMethod[pColIndex] 
    = psc.getMethod("setInt", new Class[]{Integer.TYPE,Integer.TYPE});
  mGetMethod[pColIndex] 
    = rs.getMethod("getInt", new Class[]{Integer.TYPE});
  mStringConverter[pColIndex] = new IntegerStringConverter();
}

This code analyses the database table column types before loading begins, and sets up the correct get and set methods to call on the ResultSet and PreparedStatement objects. It also creates the correct StringConverter utility object for each field.

In the code above, if the database table column data type, as provided by the ResultSetMetaData object, is an integer of some sort, then we use the IntegerStringConverter class to do the conversion to and from ints and Strings. You can also see how we set up references to the appropriate set and get methods for ints.

What if you want to convert custom data? Here's how to write a custom StringConverter that converts between java.sql.Date objects and strings

public class SqlDateStringConverter extends DefaultStringConverter {
  private DateFormat mFormat;

  public SqlDateStringConverter( DateFormat pFormat ) {
    mFormat = pFormat;
  } 
  protected Object makeObjectImpl( String pString ) throws Exception { 
    return new java.sql.Date( mFormat.parse( pString ).getTime() );
  }
  protected String makeStringImpl( Object pObject ) {
    return mFormat.format( new Date( ((java.sql.Date)pObject).getTime() ) );
  }
}

As you can see, we just have to provide the methods that turn a String into an Object, and an Object into a String. We use Java's DateFormat class to do the actual conversion work. You can apply this model to any sort of string representation of data.