In my Database created_date colum of data type is DATE and I want to select by DATE type in jdbc using ORACLE database, but when I run this method like this

Info infos = ExcellWriteToDatabase.readFromDataBase("2021-10-14 09:36:58");

, I have getting this error. How to solve this problem? I have tried many things, but coludn't solved.

java.sql.SQLDataException: ORA-01861: literal does not match format string

    at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:509)
    at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:461)
    at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:1104)
    at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:550)
    at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:268)
    at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:655)
    at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:270)
    at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:91)
    at oracle.jdbc.driver.T4CPreparedStatement.executeForRows(T4CPreparedStatement.java:970)
    at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1012)
    at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1168)
    at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3666)
    at oracle.jdbc.driver.T4CPreparedStatement.executeInternal(T4CPreparedStatement.java:1426)
    at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3713)
    at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeQuery(OraclePreparedStatementWrapper.java:1167)
    at az.expressbank.insertdatabasetheexcellfile.util.ExcellWriteToDatabase.readFromDataBase(ExcellWriteToDatabase.java:106)
    at az.expressbank.insertdatabasetheexcellfile.test.Test.main(Test.java:25)
Caused by: Error : 1861, Position : 65, Sql = SELECT PHONE_NUMBER,TEXT_MESSAGE FROM TEST WHERE CREATED_DATE = :1 , OriginalSql = SELECT PHONE_NUMBER,TEXT_MESSAGE FROM TEST WHERE CREATED_DATE = ?, Error Msg = ORA-01861: literal does not match format string

    at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:513)
    ... 16 more
null



public static Info readFromDataBase(String createdDate) throws SQLException {
         try {
                    Properties properties = new Properties();
                    properties.setProperty("user", "root");
                    properties.setProperty("password", "password");
                    properties.setProperty("useSSL", "false");
                    properties.setProperty("autoReconnect", "true");
        
                    Class.forName("oracle.jdbc.driver.OracleDriver");
                    Connection connect = DriverManager.getConnection("url", properties);
                    PreparedStatement prepareStatement = connect.prepareStatement("SELECT PHONE_NUMBER,TEXT_MESSAGE FROM TEST WHERE CREATED_DATE = ?");
                    prepareStatement.setString(1, createdDate);
                    ResultSet resultSet = prepareStatement.executeQuery();
                    if (resultSet.next()) {
                        Info info = new Info();
                        info.setPhoneNumber(resultSet.getString(1));
                        info.setCreatedDate(LocalDateTime.parse(resultSet.getString(2), DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss")));
                        return info;
                    }
                    connect.close();
                } catch (ClassNotFoundException | SQLException e) {
                    e.printStackTrace();
                }
        
                return null;
                }

CodePudding user response：

The main problem here is that you are binding a Java String to your Oracle query, when instead you should be binding a date/time type which the JDBC driver can convert to the proper type in the query. Consider this version:

public static Info readFromDataBase(String createdDate) throws SQLException {
    // ...

    DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss");
    LocalDateTime dateTime = LocalDateTime.parse(createdDate, formatter);
    String sql = "SELECT PHONE_NUMBER, TEXT_MESSAGE FROM TEST WHERE CREATED_DATE = ?";
    PreparedStatement ps = connect.prepareStatement(sql);
    ps.setObject(1, dateTime);
    ResultSet resultSet = ps.executeQuery();
    if (resultSet.next()) {
        // process result set
    }

    // ...
}