/* * Relational_5.java * * Copyright 2004-2006 by SAP AG. All Rights Reserved. * SAP, R/3, mySAP, mySAP.com, xApps, xApp, SAP NetWeaver, and other SAP * products and services mentioned herein as well as their respective logos * are trademarks or registered trademarks of SAP AG in Germany and in several * other countries all over the world. All other product and service names * mentioned are the trademarks of their respective companies. Data contained * in this document serves informational purposes only. National product * specifications may vary. * * These materials are subject to change without notice. These materials are * provided by SAP AG and its affiliated companies ("SAP Group") for * informational purposes only, without representation or warranty of any kind, * and SAP Group shall not be liable for errors or omissions with respect to * the materials. The only warranties for SAP Group products and services are * those that are set forth in the express warranty statements accompanying * such products and services, if any. Nothing herein should be construed as * constituting an additional warranty. */ package com.sap.ip.bi.sdk.samples; import java.io.IOException; import java.io.PrintWriter; import java.sql.ResultSet; import java.util.Iterator; import java.util.List; import javax.servlet.ServletConfig; import javax.servlet.ServletException; import javax.servlet.http.HttpServlet; import javax.servlet.http.HttpServletRequest; import javax.servlet.http.HttpServletResponse; import org.omg.cwm.resource.relational.Column; import org.omg.cwm.resource.relational.Table; import com.sap.exception.IBaseException; import com.sap.ip.bi.sdk.dac.connector.IBIConnection; import com.sap.ip.bi.sdk.dac.connector.IBIRelational; import com.sap.ip.bi.sdk.dac.relational.query.IBICommandProcessor; import com.sap.ip.bi.sdk.dac.relational.query.IBIQuery; import com.sap.ip.bi.sdk.dac.relational.query.sql.IBISQLComparisonOperator; import com.sap.ip.bi.sdk.dac.relational.query.sql.IBISQLLogicalOperator; import com.sap.ip.bi.sdk.samples.servlet.MinimalServletContainer; /** * More complex relational query - * * Demonstrates how to retrieve a result set by creating a more * complex query with the following features: * * 1. field selections * 2. joins * 3. sorting * * View the HTML rendered by this servlet in the following file: * [SDK archive]/docs/examples/relational_5.result.html * * @author SAP * @version 3.50 * @since 3.50 */ public class Relational_5 extends HttpServlet { private final static String CONTENT_TYPE = "text/html"; public void init(ServletConfig config) throws ServletException { super.init(config); } public void doGet( HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { response.setContentType(CONTENT_TYPE); PrintWriter out = response.getWriter(); out.println(Helpers.getDocTypeDefinition()); out.println("<html>"); out.println("<head><title>Relational_5</title>"); out.println(Helpers.getStyleSheetDefinition()); out.println("</head><body>"); try { // ******************************************************** // Connect to a data source. // ******************************************************** IBIConnection connection = Helpers.connectToJDBCDatasource(out); IBIRelational rel = connection.getRelational(); // create JDBC sample data where needed Helpers.createJDBCSampleData(); // ******************************************************** // Retrieve the metadata. // ******************************************************** // find all tables starting with prefix // (could cause issues if large number of tables and prefix // is empty or not restrictive enough) String prefix = Helpers.getJDBCTablePrefix(); List tables = rel.getObjectFinder().findTable( (String) null, null, prefix + "%"); // should contain at least the sample tables if (tables == null || tables.size() < 6) { throw new ServletException("metadata access failed"); } Table carriers = null; Table planfli = null; Table actfli = null; for (Iterator i = tables.iterator(); i.hasNext();) { Table table = (Table) i.next(); if (table != null && table.getName() != null && table.getName().equalsIgnoreCase( prefix + "CARRIERS")) { carriers = table; } if (table != null && table.getName() != null && table.getName().equalsIgnoreCase( prefix + "PLANFLI")) { planfli = table; } if (table != null && table.getName() != null && table.getName().equalsIgnoreCase( prefix + "ACTFLI")) { actfli = table; } } if (carriers == null || planfli == null || actfli == null) { throw new ServletException("sample data missing!"); } // columns for CARRIERS List ccol = carriers.getFeature(); Column cmandt = (Column) ccol.get(0); Column ccarrid = (Column) ccol.get(1); // columns for PLANFLI List pcol = planfli.getFeature(); Column pmandt = (Column) pcol.get(0); Column pcarrid = (Column) pcol.get(1); Column pconnid = (Column) pcol.get(2); // columns for ACTFLI List acol = actfli.getFeature(); Column amandt = (Column) acol.get(0); Column acarrid = (Column) acol.get(1); Column aconnid = (Column) acol.get(2); // ******************************************************** // Create the query. // ******************************************************** // create query instance IBIQuery query = rel.createQuery(); // create command processor for this query instance IBICommandProcessor cp = query.getCommandProcessor(); // Tables: // add CARRIER table String c = cp.addTable(carriers, "C"); // add PLANFLI table String p = cp.addTable(planfli, "P"); // add ACTFLI table String a = cp.addTable(actfli, "A"); //Columns: cp.addColumn((Column) ccol.get(2), c, "Carrier Name"); //CARRIERS.CARRNAME cp.addColumn((Column) pcol.get(2), p, "Flight"); //PLANLFI.CONNID cp.addColumn((Column) pcol.get(3), p, "From"); //PLANLFI.CITYFROM cp.addColumn((Column) pcol.get(5), p, "To"); //PLANLFI.CITYTO cp.addColumn((Column) acol.get(3), a, "Date"); //ACTLFI.FLDATE cp.addColumn((Column) pcol.get(8), p, "Departs"); //PLANLFI.DEPTIME cp.addColumn((Column) acol.get(4), a, "Price"); //ACTLFI.PRICE cp.addColumn((Column) acol.get(5), a, "Curr"); //ACTLFI.CURRENCY // Restrictions: // <1>:= C.MANDT=P.MANDT AND C.CARRID=P.CARRID cp.pushWhereComparison( c, cmandt, IBISQLComparisonOperator.EQUALS, p, pmandt); cp.pushWhereComparison( c, ccarrid, IBISQLComparisonOperator.EQUALS, p, pcarrid); cp.pushWhereLogicalOperator(IBISQLLogicalOperator.AND); // <2>:= P.MANDT=A.MANDT AND P.CARRID=A.CARRID cp.pushWhereComparison( p, pmandt, IBISQLComparisonOperator.EQUALS, a, amandt); cp.pushWhereComparison( p, pcarrid, IBISQLComparisonOperator.EQUALS, a, acarrid); cp.pushWhereLogicalOperator(IBISQLLogicalOperator.AND); // <3>:= <1> AND <2> cp.pushWhereLogicalOperator(IBISQLLogicalOperator.AND); // <4>:= P.CONNID=A.CONNID cp.pushWhereComparison( p, pconnid, IBISQLComparisonOperator.EQUALS, a, aconnid); // <5>:= <3> AND <4> cp.pushWhereLogicalOperator(IBISQLLogicalOperator.AND); // Ordering: cp.addOrderBy((Column) ccol.get(2), c); //CARRIERS.CARRNAME cp.addOrderBy((Column) pcol.get(3), p); //PLANLFI.CITYFROM cp.addOrderBy((Column) pcol.get(5), p); //PLANLFI.CITYTO cp.addOrderBy((Column) acol.get(3), a); //ACTLFI.FLDATE cp.addOrderBy((Column) pcol.get(8), p); //PLANLFI.DEPTIME // ******************************************************** // Execute the query. // ******************************************************** ResultSet res = query.execute(); // show the executed SQL statement out.println("<p><b>SQL Statement that was executed:</b><br>"); out.println( "<code>" + rel.getNativeRepresentation(query) + "</code>"); // ******************************************************** // Render the result set. // ******************************************************** Helpers.renderResultset(out, res); } // Catch errors. catch (Exception e) { // $JL-EXC$ e.printStackTrace(); if (e instanceof IBaseException) out.println("Error: " + ((IBaseException)e).getNestedLocalizedMessage()); else out.println("Error: " + e.getMessage()); } out.println("</body>"); out.println("</html>"); } public void destroy() { } public static void main(String[] args) { if (args.length == 1) { MinimalServletContainer.executeServlet(new Relational_5(), args[0]); } else { MinimalServletContainer.executeServlet(new Relational_5(), System.out); } } }