Rushikesh Rushikesh - 1 month ago 7
Java Question

Error while passing startDate and endDate as parameters in SQL query through java

I am getting startDate and endDate as start date of previous month and end date of previous month from current date. And I am trying to use these fields in my SQL query in java program.

My code looks like this:

public class DocumentUploads extends BaseSqlQuery<DocumentUploadDetails> {


static Date currentDate = new Date();
static Calendar c = Calendar.getInstance();

static String startDate ;
static String endDate;

static{
c.set(currentDate.getYear()+1900,currentDate.getMonth() -1, c.getActualMinimum(Calendar.DAY_OF_MONTH));
startDate = new SimpleDateFormat("dd/MM/yyy").format(c.getTime());

c.set(currentDate.getYear()+1900,currentDate.getMonth() -1, c.getActualMaximum(Calendar.DAY_OF_MONTH));
endDate = new SimpleDateFormat("dd/MM/yyy").format(c.getTime());

}

@Override
public String getQuery() {

return QUERY;
}

@Override
public void declareParameters() {
}

@Override
public DocumentUploadDetails mapRow(ResultSet resultSet, int rowNum)
throws SQLException {
DocumentUploadDetails appointment = new DocumentUploadDetails();
appointment.setId(resultSet.getLong("ID"));
appointment.setAppNum(resultSet.getLong("APP_NUM"));
appointment.setCaseNum(resultSet.getString("CASE_NUM"));
appointment.setDocType(resultSet.getString("DOC_TYPE"));
appointment.setSource(resultSet.getString("SOURCE"));
appointment.setUploadDate(resultSet.getDate("UPLOAD_DT"));
return appointment;
}

private static final String QUERY ="SELECT * FROM CASE_UPLOADS WHERE UPLOAD_DT >=" + startDate + "AND UPLOAD_DT <=" + endDate;

}


After executing my code I am getting following error:

org.springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback; bad SQL grammar [SELECT * FROM CASE_UPLOADS WHERE UPLOAD_DT >=01/05/15AND UPLOAD_DT <=31/05/15]; nested exception is java.sql.SQLException: ORA-00932: inconsistent datatypes: expected DATE got NUMBER

at org.springframework.jdbc.support.SQLStateSQLExceptionTranslator.doTranslate(SQLStateSQLExceptionTranslator.java:99)


at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:73)
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81)
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81)
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:660)
at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:695)
at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:722)
at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:772)
at org.springframework.jdbc.object.SqlQuery.execute(SqlQuery.java:111)
at org.springframework.jdbc.object.SqlQuery.execute(SqlQuery.java:121)
at org.springframework.jdbc.object.SqlQuery.execute(SqlQuery.java:136)
at com.leader.ladpss.document.data.controller.QueryController.getCaseUploadsDetails(QueryController.java:15)
at com.leader.ladpss.document.Application.main(Application.java:20)


I tried to convert data types of startDate and endDate to Date and removing use of SimpleDateFormat function. But still its not working.

Please tell me what I am doing wrong here?

Answer

You need the Oracle TO_DATE function to format the date correctly.

private static final String QUERY = "SELECT * 
            FROM CASE_UPLOADS 
            WHERE UPLOAD_DT >= to_date('" + startDate + "', 'dd/MM/yyy')
            AND UPLOAD_DT <= to_date('" + endDate + "', 'dd/MM/yyy')";