Using CSV Files as Databases and Interacting with Them Using Java

Now it is time for us to interact with the new data source.  Take a look at the code below. 

 
import java.sql.Connection; 
import java.sql.DriverManager; 
import java.sql.ResultSet; 
import java.sql.Statement; 

 
public class CSVInteract {
 
 public static void main(String[] args) {
 
  final String DBURL = "jdbc:odbc:StateBirdDS";
 
  System.out.println( 
   "Interacting with CSV file as if it were a true 
    database."); 
  try {
 
   Class.forName( 
    "sun.jdbc.odbc.JdbcOdbcDriver"); 
   Connection conn = 
   DriverManager.getConnection( 
     DBURL, 
     "", 
     "");
 
   Statement st = conn.createStatement();
 
   // Example of Select Query 
   ResultSet rec = st.executeQuery( 
     "SELECT StateBird FROM statebirds.txt 
      where STATE='Florida'"); 
   while (rec.next()) { 
    System.out.println( 
     "Florida's State Bird is: " 
      + rec.getString( 
       "STATEBIRD")); 
   } 
   // Example of Insert 
   System.out.println( 
    "Inserting Record"); 
   String insertStatement = 
    "INSERT INTO statebirds.txt 
     values('Wyoming','Western Meadowlark')"; 
   st.executeUpdate(insertStatement);
   // Confirm Insertion 
   rec = st.executeQuery( 
     "SELECT StateBird FROM statebirds.txt 
      where STATE='Wyoming'"); 
   while (rec.next()) { 
    System.out.println( 
     "Wyoming's State Bird is: " 
      + rec.getString("STATEBIRD")); 
   } 
  } catch (Exception e) {
   e.printStackTrace(); 
  } 
 } 
} 



The code first displays the state bird of Florida to the console.  Next, we insert a record for the state of Wyoming.  Then we confirm the insertion by displaying the added record.  The file can also be opened to view the update.
 
Readers familiar with the JDBC™ Application Programming Interface (API) should find the code straightforward. To learn about JDBC, take the “JDBC Short Course” here.

The driver we use is sun.jdbc.odbc.JdbcOdbcDriver
This driver is the JDBC-ODBC bridge driver.  The good thing is that you don’t have to download anything to use it.  The driver comes built into the Java Development Kit™ (JDK). 

Also, notice that the table we refer to in our code is the name of our file name: statebirds.txt

Note that at the time of this article’s writing, the Microsoft ODBC Text Driver does not support the deletion of records.

Conclusion

This article showed how to setup a CSV file as a no-frills database that can be accessed with JDBC.  Such a technique is particularly useful when access to a database is not available, yet need to test your JDBC code. 

The reader is cautioned not to use a CSV file in true production level code.  One should consider a more scalable solution designed to truly provide database functionality (e.g., IBM® DB2™ Universal Database).  However, the technique described in this article is useful for individuals who are testing their JDBC code, providing a database (albeit quite limited in functionality) to work against.