I'm writing this blog post to share my experience on a specific development task on SAP NetWeaver Portal 7.3.I developed SAPUI5 application is connected to Non SAP systems (SQL) by using EJB and REST web application.
Pre-Requirements:-
SAP Enterprise Portal 7.3
Development Tool: NWDS 7.3 SP07
SAPUI5 Development Skills: HTML5, JavaScript, CSS3, AJAX/JSON and JQuery.
Connect Back-end Systems: EJB & REST Service (3rd Party Systems).
STEP 1:- I have installed SAPUI5 Plug-Ins in NWDS 7.3 SP07 using the block post
Developing SAP UI5 applications in SAP NetWeaver Developer Studio - Part 1/2
Developing SAP UI5 applications in SAP NetWeaver Developer Studio - Part 2/2
STEP 2:- I created “JDBC Custom Data Source” connection between Portal and SQL Database in NWA.
- Downloaded the required (ojdbc6.jar) jars from the google.
- In NWA, Configuration Management ---> Infrastructure --->Application Resources Select Create New Resource -> Deploy new JDBC driver
3. Again, Select Create new resource -> New JDBC Custom DataSource.
4. Created Custom data source alias in Dependent DataSource Aliases tab:
5. Finally we have custom DataSource and a DataSource Aliases tab.
STEP 3:-
I created EJB application called “rest/ejb”, web application called “REST/Web” and ear application (for deploy ejb/web applications) under J2EE perspective.
I created Session Bean with @Stateless “Manager.java”
Note: - Mainly my application is follows CURD methods, in this blog I include only search the data from the SQL System using REST Service. Same way will follow any methods like Insert/Delete/Update etc…
1. Connected SQL System by using below code in “Manager.JAVA”. PFB code for your Reference.
2. Code for Search the data from SQL Data Base. PFB code for your Reference
@WebMethod(operationName="Manager", exclude=false)
public List<PtnerGroup> querySearchManager(
@WebParam(name="groupName") String groupName,
@WebParam(name="countryName") String countryName
List<PtnerGroup> pgList = new LinkedList<PtnerGroup>();
Connection connection = null;
Statement st = null;
ResultSet rs = null;
try{
connection = this.getConnection(null);
String sql = "select * from TABLENAME_GROUPS where";
if (!"*".equalsIgnoreCase(groupName) && groupName != null
&& groupName.trim().length() > 0) {
sql = sql + " (LOWER(name) like LOWER('%" + groupName
+ "%') OR group_id like '%" + groupName + "%') AND";
}
if (!"*".equalsIgnoreCase(countryName) && countryName != null
&& countryName.trim().length() > 0) {
sql = sql + " (LOWER(country_name) like LOWER('%" + countryName
+ "%') OR COUNTRY_ID LIKE '%" + countryName + "%') ";
}
if (sql.endsWith("where")) {
sql = sql.replace("where", "");
}
if (sql.endsWith("AND")) {
sql = sql.replace("AND", " ");
}
if (!sql.contains("order by")) {
sql = sql + "order by GROUP_ID";
}
//System.err.println("SQL Query:"+sql);
st = connection.createStatement();
rs = st.executeQuery(sql);
while (rs.next()) {
PtnerGroup pg = new PtnerGroup(rs.getString("GROUP_ID"), rs.getString("NAME"),
rs.getString("COMMENTS"), rs.getString("COUNTRY_ID"), rs.getString("COUNTRY_NAME"),
rs.getString("BRAND_ID"), rs.getString("BRAND_NAME"));
pgList.add(pg);
}
return pgList;
} catch (SQLException e) {
logger.traceThrowableT(Severity.ERROR, null, e);
} finally {
try {
if (st != null)
st.close();
if (connection != null)
connection.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
return null;
}
3. Now go the REST/Web application creates “ManagerGroupRest.Java”. PFB code for your Reference.
@Path("/manager")
public class ManagerGroupRest {
final String JNDI_NAME = "demo.sap.com/b2b~rest~ear/LOCAL/Manager/com.manager.ejb.ManagerLocal";
public ManagerGroupRest(){
super();
}
private static final Location _location = Location
.getLocation(ManagerGroupRest.class);
@Path("search")
@POST
@Consumes({ MediaType.APPLICATION_XML, MediaType.APPLICATION_JSON,MediaType.TEXT_PLAIN })
@Produces( { MediaType.APPLICATION_XML, MediaType.APPLICATION_JSON })
public Response queryPrtGroup(ManagerGroupSearch search){
_location.infoT("in method queryPrtGroup");
String groupName = search.getGroupName();
String countryName = search.getCountryName();
//System.err.println("Group Name:"+groupName);
//System.err.println("grpManagerLocal:"+grpManagerLocal);
try
{
InitialContext jndiContext = new javax.naming.InitialContext();
GroupManagerLocal inventoryMgr = (GroupManagerLocal) jndiContext.lookup(JNDI_NAME);
List<PtnerGroup> list = inventoryMgr.querySearchGroupManager(groupName, countryName);
System.err.println("list:"+list);
if (list == null) {
System.err.println("null");
return Response.status(Status.INTERNAL_SERVER_ERROR).build();
} else {
GenericEntity<List<PtnerGroup>> entity = new GenericEntity<List<PtnerGroup>>(list) {};
System.err.println("entity:"+entity);
return Response.ok(entity).build();
}
}
catch(NamingException e)
{
e.printStackTrace();
}
return null;
}
}
By using REST application I will generate REST URL like(http://hostname:50000/demo.sap.com~b2b~rest~web/rest/manager/search)
4. Now test your rest URL is working or not in Mozilla Firefox. Install REST Plug-ins in Mozilla Firefox.
5. After test the REST application Firefox the data will display in “Response Body(Preview)” and if will have any error/issues will get the status in “Response Header”
STEP 4:-
Created SAP UI5 Application or Create Web application (under “WebContent” we have to create all required (HTML5, JavaScript, CSS3, AJAX/JSON and JQuery) and deploy the application in Server) in NWDS
When we click on Submit Query. we will read the data from INPUT FIELDS and send to the REST Service by using below code
Once we received the data from SQL System, same data we are going to bind to the table by using below code
Code for Model.Js file
Output of the SAPUI5 Application
STEP 5:- Same scenario we will develop the Update/Create/Delete etc… functionalities.
Hope this is help full
Regards
Vijay Kalluri
- SAP Managed Tags:
8 Comments
