0

How can I execute this statement and process it java. Is it possible to get a return value and then the next resultset which is the select to employee in the way I'm doing it?

I cannot find the accurate way on Google to perform what I want because all of the examples are results with single SELECTs and cannot find a query with RETURN from DB. But according to this question, it is possible to manage multiple result sets from DB (java) as .NET can do.

I'm using postgresql 9.4 and I don't want to use a stored proc (function) to do what I'm trying to do.

This is the code that I've been trying to test, but I get an exception that there is a syntax error in 'IF' line 1

public Employee getEmployee(Connection con, String code) {
    Employee employee = new Employee();
    try {
        String query = 
                "IF EXISTS(SELECT * FROM employee WHERE code = ?) THEN "
                + "RETURN 1; "
                + "ELSE "
                + "RETURN 2; "
                + "END IF; "
                + "SELECT EmployeeID, FirstName FROM employee where code = ?; ";
        PreparedStatement stmt = con.prepareStatement(query);
        stmt.setString(1, code);
        stmt.setString(2, code);
        boolean hasResults = stmt.execute();
        int returnValue = 0;
        while(hasResults){
            ResultSet rs = stmt.getResultSet();
            returnValue = rs.getInt(1);
            if(returnValue == 1){
                hasResults = stmt.getMoreResults();
                while(hasResults){
                    employee.setId(rs.getInt("EmployeeID"));
                    employee.setFirstName(rs.getString("FirstName"));
                }
            }
        }
        return employee;
    } catch (Exception e) {
        System.out.println(e.getMessage());
        return null;
    }
Community
  • 1
  • 1
Maximus Decimus
  • 4,901
  • 22
  • 67
  • 95

1 Answers1

0

You are overcomplicating things (apart from the fact that there is no if in SQL).

Just run the select, if there is no such employee you'll get an empty result set:

public Employee getEmployee(Connection con, String code) {
    Employee employee = new Employee();
    try {
        String query = "SELECT EmployeeID, FirstName FROM employee where code = ?";
        PreparedStatement stmt = con.prepareStatement(query);
        stmt.setString(1, code);
        ResultSet rs = stmt.executeQuery();
        if (rs.next()){
            employee.setId(rs.getInt("EmployeeID"));
            employee.setFirstName(rs.getString("FirstName"));
        }
        return employee;
    } catch (Exception e) {
        System.out.println(e.getMessage());
        return null;
    }
}

You also might want to move the creation of the Employee instance inside the if (rs.next()) so that if no employee exists your method returns null. The above code would always return an Employee even if there is none

You also should remove the ; from the query string. Although the Postgres driver will happily run statements terminated with ; this is not JDBC compliant and other drivers (or DBMS) might refuse to run it.

  • I appreciate your answer. My question was an example. In any way how can I get the return value followed by a query. I want to execute mutiple queries and get the multiple result sets in just one transaction/execution. But at the same time I wanna be able to perform function but not specifically pre-compiled in the DBMS, but as query string. – Maximus Decimus Apr 18 '16 at 13:01