JDBC - Loading data from a text file

Question

I'm trying to load data to my MySQL database from a text file through Java. So far I have the following code. When I run this it successfully reads the data from the file and prints it out but then it gives this error and no data is added to the database:

Exception in thread "main" java.lang.IllegalArgumentException
at java.sql.Date.valueOf(Date.java:143)
at jdbc.Jdbc.main(Jdbc.java:81)

package jdbc;

import java.sql.*; 
import java.io.*;
import java.util.*; 
import java.io.File;
import java.io.FileReader;
import java.io.BufferedReader;

public class Jdbc { 
public static void main (String args []) 
  throws SQLException, IOException { 

  // the following statement loads the MySQL jdbc driver
  // make sure it is in the CLASSPATH

try {
  Class.forName ("com.mysql.jdbc.Driver");
} catch (ClassNotFoundException e) {
    System.out.println ("Could not load the driver"); 
  }

String user, password, host;

Scanner sc = new Scanner(System.in);
System.out.println("Type userid, password, hostname or ipaddress: ");
user = sc.next();
password = sc.next();
host = sc.next();
System.out.println(user+" "+password+" "+host);

//  userid, password and hostname are obtained from the console

Connection conn = DriverManager.getConnection
              ("jdbc:mysql://localhost/PoemsAss", user, password);

/* JDBC default is to commit each SQL statement as it is sent to the database.  Setting autocommmit=false changes the default
   behaviour so that transactions have to be committed explicity.
 */
conn.setAutoCommit(false);

// Creating a statement lets us issue commands against the connection.

Statement s = conn.createStatement();



//Create Tables
s.executeUpdate("create table IF NOT EXISTS Poet(Poet CHAR(20)  PRIMARY KEY,     DOB DATE, PlaceOfBirth CHAR(20), Nationally CHAR(20),  Language CHAR(15))");
System.out.println("Table created");
s.executeUpdate("create table IF NOT EXISTS Translator(Translator_Name CHAR(30)  PRIMARY KEY,  Poem_Title VARCHAR(50), Translation_Date DATE)");
System.out.println("Table created");
s.executeUpdate("create table IF NOT EXISTS Poem(Poem_Title VARCHAR(50)  PRIMARY KEY,  Date_Published DATE, Language CHAR(20), Num_Words SMALLINT, Translator_Name CHAR(30))");
System.out.println("Table created");
s.executeUpdate("create table IF NOT EXISTS Recording(RecordingID VARCHAR(4)  PRIMARY KEY, Date DATE, Place CHAR(20), Duration TINYINT, Format VARCHAR(10), Poem_Title VARCHAR(50))" );
System.out.println("Table created");


String data_file = "src/jdbc/poet_data.txt";
File inputFile = new File(data_file);
FileReader inf = new FileReader(inputFile);
BufferedReader inb = new BufferedReader(inf);
System.out.println("Ready to read line");
String line = inb.readLine(); // read a line



String psq2 = "insert into Poet values (?,?,?,?,?)";
PreparedStatement ps2=conn.prepareStatement(psq2);


java.sql.Date when;

while ((line != null)) {
String[] tokens = line.split(","); // split line into ‘,’
System.out.println(tokens[0] + " " + tokens[1] +
" " + tokens[2] + " " + tokens[3] + " " +
tokens[4] + " ");
//Should trim leading/trailing spaces from tokens.
ps2.setString(1, tokens[0]);
when = java.sql.Date.valueOf(tokens[1]);
ps2.setDate(2, when);
ps2.setString(3, tokens[2]);
ps2.setString(4, tokens[3]);
//convert yyyy-mm-dd string to date
ps2.setString(5, tokens[4]);
ps2.execute();
ps2.clearParameters();
line = inb.readLine(); //read next line
}


conn.commit();


 conn.close();
  }
}

Answer 1

It is obvious that your problem lies in the format of the tokens[1] string.

You are trying to create a java.sql.Date Object from a String not matching to the acceptable format yyyy-mm-dd

If for example your String is like this: 21/12/2002 that would throw a run-time exception as the one you already have. For this to work correctly, you have to parse your String, in order to make it readable to the java.sql.Date.valueOf method.

Example:

SimpleDateFormat textFormat = new SimpleDateFormat("dd/MM/yyyy");
java.util.Date myDate = textFormat.parse(tokens[1]);
when = new java.sql.Date(myDate.getTime());

As a side note, the parse() method throws ParseException, so make sure you enclose your code in the relevant try/catch block or make your method throw this exception.

Hope this helps