Muhammad Ali Muhammad Ali - 1 month ago 6
Java Question

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

Answer

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();
Comments