Search code examples
javadatabasepostgresqldate-formattingjdatechooser

problems using JDateChooser with a database

I have a Airport project. I have a gui in which i want to search for certain flights flights. In this gui I have a JDateChooser(because i want to find a certain flight in my database). In the database i have a column called date_dep which is a Data type. I should mention that previously I had to create flights(enter information about flights from a gui into the database) and I didn't have problems when i entered into the database the date I got from a JDateChooser. The problem now is that when i try and search for a certain flight I get this error:

org.postgresql.util.PSQLException: ERROR: syntax error at or near "Apr"

I figure that "Apr" is from April since i am searching for flights on the 24th of April. So I guess my problem is something with the formats but I tried many things without any luck. Do you have any idea what it could be? Thanks in advance. I can post more code if it helps.

dateCh is the name of my Jdatechooser.

try {

        con = DriverManager.getConnection(url, user, password);
        pst = con.prepareStatement("SELECT * FROM flight WHERE route_id="+routeid+ "AND date_dep="+dateCh.getDate());
        rs = pst.executeQuery();

        // erase everything from the list before refreshing it
        flightlist.clear();
        while (rs.next()) {
            flightlist.addElement(rs.getInt(1) + " : Flight ID" + "  |  "
                    + "Route ID: " + rs.getInt(2) + "  |  "+"Date: "+ rs.getDate(4)+ "  |  "+"Time: "+rs.getTime(5)+ "  |  "
                    + "Plane ID "+rs.getInt(3)+ "  |  "+"Economy seats: "+rs.getInt(6)+"  |  "+"Business seats: "+rs.getInt(7)+"  |  "
                    + "First class seats: "+rs.getInt(8)+"\n");
        }

    } catch (SQLException e) {
        System.out.println("Connection failed!");
        e.printStackTrace();
        return;
    }

After fixing my code

pst = con.prepareStatement("SELECT * FROM flight WHERE route_id=? AND date_dep=?");
        rs = pst.executeQuery();
        pst.setInt(1, routeid);
        pst.setDate(2, sqlDate);

I get this error now. I found online that there is some kind og bug with postgres but i don't know how to fix it.

org.postgresql.util.PSQLException: No value specified for parameter 1

My bad, I was executing the query before setting the values. I works now. Thank you very much

Solution

  • Check the PreparedStatement docs on how to use it properly.

    You should write your query as follows -

    pst = con.prepareStatement("SELECT * FROM flight WHERE route_id= ? AND date_dep= ?");

    And set the arguments as follows -

    //pst.setXXX(1, routed); just choose the appropriate type for the route_id column
pst.setDate(2, dateCh.getDate());

    Note that currently you are not even enclosing your arguments in quotes. A properly written query would look something like ... where col1 = 'val1' and col2 = 'val2' ....

    When you do something like ... "AND date_dep="+dateCh.getDate() ..., that's equivalent to doing ..."AND date_dep="+dateCh.getDate().toString().... Which eventually yields something like ... AND date_dep=Apr ....