Search code examples
javasqldatems-accessucanaccess

Inserting date as Date object in a database using Java 8?

I'm working on a project that would require calculations on dates. I'm looking for a way to store date as a Date object inside a access database but every method gives an error. I'm using UcanAccess drivers with Java 8.

My stripped Code:

//========================== SetupDB =====================================//
public void setupDB() {
    try {
        Class.forName("net.ucanaccess.jdbc.UcanaccessDriver");
        c1 = DriverManager.getConnection(ABDebatePro.DBURL);
        stmt = c1.createStatement();
    }
catch (Exception e) {
        System.out.println(e);
}
}
//========================== InsertDB ====================================//
public void insertDB(int m, String ft, String st, int s1, int s2, Date d) {
    MatchNumber = m;
    FirstTeam = ft;
    SecondTeam = st;
    FirstTeamScore = s1;
    SecondTeamScore = s2;
    MatchDate = d;
    setupDB();
    try {
        String sql = "insert into Schedule (MatchNumber, FirstTeam, SecondTeam, FirstTeamScore, SecondTeamScore, MatchDate)  values " + "(" + m + ",'" + ft + "','" + st + "'," + s1 + "," + s2+ ",'" + d  + "')";
        int z = stmt.executeUpdate(sql);
        c1.close(); 
    } catch (Exception fe) {
        System.out.println(fe);
    }
}
 //========================== Main ========================================//
public static void main(String[] args) {
Schedule sch = new Schedule();
try {
     java.sql.Date date1 = java.sql.Date.valueOf("2016-10-25");
     sch.insertDB(3, "Turtles", "Aligators", 4, 3, date1);
} catch (Exception ex) {
    System.out.println(ex);
}

My Log:

insert into Schedule (MatchNumber, FirstTeam, SecondTeam, FirstTeamScore, SecondTeamScore, MatchDate)  values (3,'Turtles','Aligators',4,3,'2016-10-25')
net.ucanaccess.jdbc.UcanaccessSQLException: data exception: invalid datetime format

My database:

Database snapshot

Solution

  • Your problem is that you are using dynamic SQL to construct the SQL statement in insertDB, and that statement contains an invalid date literal. You should be using a PreparedStatement and a parameterized query like so:

    String sql =
        "INSERT INTO Schedule " +
        "(MatchNumber, FirstTeam, SecondTeam, FirstTeamScore, SecondTeamScore, MatchDate) " +
        "VALUES " +
        "(?,?,?,?,?,?)"
PreparedStatement ps = c1.prepareStatement(sql)
ps.setInt(1, m);
ps.setString(2, ft);
ps.setString(3, st);
ps.setInt(4, s1);
ps.setInt(5, s2);
ps.setDate(6, d);
int z = ps.executeUpdate();