| |
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;
}
|