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 , I have getting this error. How to solve this problem? I have tried many things, but coludn't solved. Answer The main problem here is that you are binding

How to select by DATE type in jdbc using oracle DB?

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;
                }

Answer

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
    }

    // ...
}

Advertisement

Answer