Amalegeni

Dynamic sql example

The regular way of writing a SQL service is like this:

AirportBean getAirport(String iata, String icao, String cityServed) 
<<
    select iata,icao,city_served,region,airport_name 
    from   t_airport 
    where  iata=? 
    and    icao=? 
    and    city_served=?
>>

There are instances where you would like to create the SQL at runtime, which is not recommended, but it seems to be unavoidable. How to handle that? Just leave the sql body empty, and pass the dynamically built sql in the first parameter. So the equivalent to the above service becomes:

AirportBean getAirportDynSql(String sql, String iata, String icao, String cityServed) 
<<
>>

You can then call the above service like this:

String sql=  " select iata,icao,city_served,region,airport_name "
            +" from   t_airport "
            +" where  iata=? "
            +" and    icao=? "
            +" and    city_served=? ";
String iata="TNM";
String icao="SCRM";
String cityServed="Antarctica";

AirportBean bean=service.getAirportDynSql(sql, iata, icao, cityServed);

Generated code for the above services

public AirportBean getAirport(String iata, String icao, String cityServed)
throws SQLException
{
    Connection connection = getConnection();
    
    PreparedStatement pstmt= connection.prepareCall(
        "select iata,icao,city_served,region,airport_name "+ 
        "from   t_airport "+ 
        "where  iata=? "+ 
        "and    icao=? "+ 
        "and    city_served=? ");

    pstmt.setString(1,iata); 
    pstmt.setString(2,icao); 
    pstmt.setString(3,cityServed); 

    ResultSet resultSet=pstmt.executeQuery();
    
    AirportBean o= null;
    if ( resultSet.next() )
    {
        o=resultSetToAirportBean( resultSet);
    }
    resultSet.close();
    pstmt.close();
    returnConnection(connection);
    return o;
}

public AirportBean getAirportDynSql(String sql, String iata, String icao, String cityServed)
throws SQLException
{
    Connection connection = getConnection();
    
    PreparedStatement pstmt= connection.prepareCall(sql);

    pstmt.setString(1,iata); 
    pstmt.setString(2,icao); 
    pstmt.setString(3,cityServed); 

    ResultSet resultSet=pstmt.executeQuery();
    
    AirportBean o= null;
    if ( resultSet.next() )
    {
        o=resultSetToAirportBean( resultSet);
    }
    resultSet.close();
    pstmt.close();
    returnConnection(connection);
    return o;
}