Java.sql.SQLException date format error: invalid column type

Question

I display the date in JSF using pattern="dd-MMM-yyyy" .

When I try to insert / update date values ​​in my oracle DB, I get

 java.sql.SQLException: Invalid column type 

because my date format before inserting or updating is in this format

 Wed Feb 09 00:00:00 AST 2011 

How can I correctly insert or update my date values ​​to Oracle Db and what is the best way to do this?

Update 1

My db insert code.

 private void editSchedule(Schedule schedule) Object[] values = { schedule.getStartDate(), schedule.getVacationId() }; Connection connection = null; PreparedStatement preparedStatement = null; try { connection = datacon.getConnection(); preparedStatement = prepareStatement(connection, SQL_EDIT, values); preparedStatement.executeUpdate(); } catch (Exception e) { logger.info("errro "+e.getMessage()); e.printStackTrace(); } finally { // TODO: handle exception close(connection, preparedStatement); } } 

The prepared part of the code code

 public static PreparedStatement prepareStatement (Connection connection, String sql, Object... values) throws SQLException { PreparedStatement preparedStatement = connection.prepareStatement(sql ); setValues(preparedStatement, values); return preparedStatement; } public static void setValues(PreparedStatement preparedStatement, Object... values) throws SQLException { for (int i = 0; i < values.length; i++) { preparedStatement.setObject(i + 1, values[i]); logger.info("sql "+Arrays.asList(values)); } } 

Answer 1

It looks like you are trying to include data in text when pasting / updating. Do not do this - use java.sql.Date in PreparedStatement . Introducing unnecessary string conversions is a very bad idea - it makes your code very fragile and makes your code more confusijng: save your data in the appropriate data type as much as possible.

Answer 2

JDBC only understands java.sql.Date , java.sql.Time and java.sql.Timestamp as types of SQL columns, not java.util.Date .

You need to change

  Object[] values = { schedule.getStartDate(), schedule.getVacationId() }; 

by

  Object[] values = { new java.sql.Date(schedule.getStartDate().getTime()), schedule.getVacationId() }; 

Then it will work. Just keep using java.util.Date in your model. JSF, in turn, does not understand java.sql.Date .

Answer 3

 public static String dateToSQLFormat(Date date){ Format formatter = new SimpleDateFormat("yyyy-MM-dd"); String ret=formatter.format(date); return ret; }