How to store user input date and pass them in sql query

Question

In order to make our code more usable. here I try to take many pattern but didn't get my results

I am however facing a problem with the date variable , If I pass static variable then it's right output. but I want to dynamic(user input date). I don't to pass predefine date.

Pls give me proper solution.

Here is the code:

        Connection con=DriverManager.getConnection( "jdbc:oracle:thin:@localhost:1521:VASVMT","scott","tiger");


        Statement stmt=con.createStatement();
        
        Scanner sc=new Scanner(System.in);
        System.out.println("Enter DOB (dd-mm-yyyy) format");
        String td=sc.next();
        SimpleDateFormat date1=new SimpleDateFormat("dd-MM-yyyy");
        System.out.println(date1);
        java.util.Date tdd=date1.parse(td);
        System.out.println(tdd);
        long ms=tdd.getTime();
        System.out.println(ms);
        java.sql.Date dttt=new java.sql.Date(ms);
        System.out.println(dttt);
         
        
        String sql="select * from tbl_transaction_new where to_char(date_time,'dd-mm-yyyy')='dttt'";
        ResultSet rs = stmt.executeQuery(sql);
        while(rs.next()) {
              
               System.out.print(" NAME: "+rs.getString("messgae"));
            
        
               System.out.print(" Date: "+rs.getDate("date_time"));
             
               System.out.println();
    
        
    con.close();
    }

My sql table -- Like

message(column) and date_time(column, datatype- Date).. In date_time column values are like 05-JUL-2021 , 08-JUL-2021 , 01-JAN-2021.

I recommend you don’t use `SimpleDateFormat` and `Date`. Those classes are poorly designed and long outdated, the former in particular notoriously troublesome. Instead use `LocalDate` and `DateTimeFormatter`, both from [java.time, the modern Java date and time API](https://docs.oracle.com/javase/tutorial/datetime/). See [Insert & fetch java.time.LocalDate objects to/from an SQL database such as H2](https://stackoverflow.com/questions/43039614/insert-fetch-java-time-localdate-objects-to-from-an-sql-database-such-as-h2). — Ole V.V., Jul 08 '21 at 05:30
Your code seems to process the user input, `td`. What are you missing? — Ole V.V., Jul 08 '21 at 05:32
Friend, it’s a good idea to keep an eye on your question the first few hours after you’ve posted and edit it immediately in response to any requests for clarification. The interest is greatest at this time, so it increases your chances of a good reception and good answers. — Ole V.V., Jul 08 '21 at 09:50

Ole V.V. · Answer 1 · 2021-07-08T12:33:50.883

Use java.time, the modern Java date and time API, for your date work.
Don’t convert back and forth between date objects, strings and longs. Parse your user input string once into a LocalDate and stick to LocalDate for the rest. Also you don’t need to_char in SQL.
Use a ? parameter placeholder in your SQL and substitute the actual parameter from Java.
Move con.close(); outside the loop, or you may only be able to read the first row.

In code:

    String userInput = "06-09-1987"; // = sc.next();
    LocalDate dob = LocalDate.parse(userInput, DTF);
    
    String sql = "select * from tbl_transaction_new where date_time = ?;";
    PreparedStatement stmt = con.prepareStatement(sql);
    stmt.setObject(1, dob);
    ResultSet rs = stmt.executeQuery();
    while(rs.next()) {
        System.out.print(" NAME: " + rs.getString("messgae"));
        System.out.print(" Date: " + rs.getObject("date_time", LocalDate.class));
        System.out.println();
    }
    con.close();

The code uses the following date formatter:

private static final DateTimeFormatter DTF = DateTimeFormatter.ofPattern("dd-MM-uuuu");

I haven’t got Oracle installed so have not had an opportunity to test myself. I have compiled the code. Please forgive if there’s a typo that wasn’t caught in compilation.

Note how I am also retrieving a LocalDate rather than an old-fashioned java.sql.Date from the result set. Passing LocalDate or other java.time types to and from SQL requires JDBC 4.2. About all of us have that nowadays.

Edit: You further asked:

I have an issue with table data .. If column (date_time) value is like 05-JUL-201 (Type - Date) . in that case we need to use to_char variable or not(?)

05-JUL-2021 is Oracle’s way of displaying the date in response to a query. It has nothing to do with how the date is stored internally in the database. It’s easier both for you and for the database to compare two dates without converting them to char first. So no, don’t use to_char (again said without having tested).

Link

Oracle tutorial: Date Time explaining how to use java.time.

First of all thanks , I have an issue with table data .. If column (date_time) value is like 05-JUL-201 (Type - Date) . in that case we need to use to_char variable or not . — AMIT PATEL, Jul 08 '21 at 11:03
@AMITPATEL Thanks for the follow-up question. Please see my edit. — Ole V.V., Jul 08 '21 at 12:35
@AMITPATEL I’m curious to know whether the answer turned out to be helpful and whether there’s anything I can still contribute? — Ole V.V., Jul 13 '21 at 06:25

How to store user input date and pass them in sql query

1 Answers1

Link