SAP UI5 – CALLING EXTERNAL DATABASE MYSQL THROUGH “HIBERNATE”
In this demo I would like to show – how to connect to External Database such as MYSQL through Hibernate using JQUERY,AJAX and JSON.
Before proceeding to this demo tutorial I would suggest to go through the below links where in I explained how to Call a Servlet using JQUERY,AJAX and JSON.
http://scn.sap.com/docs/DOC-73103
http://scn.sap.com/docs/DOC-73104
Pre-Requisites for this demo :
a) Eclipse with SAP UI5 Installed
b) Tomcat server 7.0
c) javax.servlet.jar
d) google’s GSON library (gson-2.3.1.jar)
e) MYSQL database
f) mysql-connector-java-5.0.8-bin.jar
g) antlr-2.7.7.jar
h) classmate-1.3.0.jar
i) dom4j-1.6.1.jar
j) geronimo-jta_1.1_spec-1.1.1.jar
k) hibernate-commons-annotations-5.0.1.Final.jar
l) hibernate-core-5.1.0.Final.jar
m) hibernate-jpa-2.1-api-1.0.0.Final.jar
n) jandex-2.0.0.Final.jar
o) javassist-3.20.0-GA.jar
p) jboss-logging-3.3.0.Final.jar
Step 1 : Create table in mysql as shown below
Step 1 : Create table in mysql as shown below
Step 2 :
I have already shown how to create a Dynamic Web Project in the above links.
To the existing project “SAPUI5_JQUERY_AJAX_DEMO” , Add a new view as below:
WebContent-> sapui5_jquery_ajax_demo->New->SAPUI5 Application Development->View->Enter name “call_DB_JDBC_JqueryAjax” -> Select Javascript option -> Click Finish
Step 3:
Create new html file as below :
WebContent->Right Click->New->HTML file-> Enter name “index3.html” ->Finish
Step 4:
Enter below code in index3.html
<!DOCTYPE HTML>
<html>
<head>
<meta http-equiv=“X-UA-Compatible” content=“IE=edge”>
<meta http-equiv=‘Content-Type’ content=‘text/html;charset=UTF-8’/>
<script src=“resources/sap-ui-core.js”
id=“sap-ui-bootstrap”
data-sap-ui-libs=“sap.ui.commons”
data-sap-ui-theme=“sap_bluecrystal”>
</script>
<!– add sap.ui.table,sap.ui.ux3 and/or other libraries to ‘data-sap-ui–libs‘ if required –>
<script>
sap.ui.localResources(“sapui5_jquery_ajax_demo”);
var view = sap.ui.view({id:“idcall_DB_JDBC_JqueryAjax1”, viewName:“sapui5_jquery_ajax_demo.call_DB_JDBC_JqueryAjax”, type:sap.ui.core.mvc.ViewType.JS});
</script>
</head>
<body class=“sapUiBody” role=“application”>
<h1>SAP UI5 Demo</h1>
<input type=“button” value=“Show Table” id=“showTable”/>
<div id=“tablediv”>
<table id=“countrytable”>
<tr>
<th scope=“col”>Code</th>
<th scope=“Name”>Name</th>
<th scope=“Continent”>Continent</th>
<th scope=“Region”>Region</th>
<th scope=“Population”>Population</th>
<th scope=“Capital”>Capital</th>
</tr>
</table>
</div>
</body>
</html>
Step 5:
Enter below code in call_DB_JDBC_JqueryAjax.view.js
- sap.ui.jsview(“sapui5_jquery_ajax_demo.call_DB_JDBC_JqueryAjax”, {
/** Specifies the Controller belonging to this View.
* In the case that it is not implemented, or that “null” is returned, this View does not have a Controller.
* @memberOf sapui5_jquery_ajax_demo.call_DB_JDBC_JqueryAjax
*/
getControllerName : function() {
return “sapui5_jquery_ajax_demo.call_DB_JDBC_JqueryAjax”;
},
/** Is initially called once after the Controller has been instantiated. It is the place where the UI is constructed.
* Since the Controller is given to this method, its event handlers can be attached right away.
* @memberOf sapui5_jquery_ajax_demo.call_DB_JDBC_JqueryAjax
*/
createContent : function(oController) {
$(document).ready(function() {
var temp=‘tempvalue’;
$(“#tablediv”).hide();
$(“#showTable”).click(function(event) {
$.get(‘ActionServlet2’,{temp:temp},function(responseJson){
if (responseJson!=null) {
//$(“#countrytable“).find(“tr:gt(0)”).remove();
//var table1=$(“#countrytable“);
var obj = jQuery.parseJSON(responseJson);
$.each(obj,function(key,value){
var tr=$(‘<tr></tr>’);
$(‘<td>’+value.code+‘</td><td>’+value.name+‘</td><td>’+value.continent+‘</td><td>’+value.region+‘</td><td>’+value.population+‘</td><td>’+value.capital+‘</td>’).appendTo(tr);
tr.appendTo(‘#countrytable’);
});
}
});
$(“#tablediv”).show();
});
});
}
});
Step 6:
Copy all the jar files mentioned in pre-requisite to WebContent->WEB-INF->lib.
Simple way is to drag and drop the files,select copy option and press ok.
Step 7:
Create below class files :
- 1) ActionServlet2.java
- 2) FetchData.Java
- 3) Countrydetail.java
ActionServlet2.java – copy the below code
package ajaxdemo;
import java.io.IOException;
import java.util.ArrayList;
import model.Countrydetail;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import com.google.gson.Gson;
import com.google.gson.reflect.TypeToken;
import com.google.gson.*;
@WebServlet(“/ActionServlet2”)
public class ActionServlet2 extends HttpServlet {
private static final long serialVersionUID = 1L;
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
System.out.println(“inside servlet”);
ArrayList<Countrydetail> countryDetailArrayList=new ArrayList<Countrydetail>();
countryDetailArrayList=FetchData.getAllRows();
Gson gsonObj=new Gson();
JsonElement jsonElm = gsonObj.toJsonTree(countryDetailArrayList,new TypeToken<java.util.List<Countrydetail>>(){}.getType());
JsonArray jsonArray=jsonElm.getAsJsonArray();
System.out.println(“jsonArray size is”+jsonArray.size());
response.setCharacterEncoding(“UTF-8”);
response.getWriter().print(jsonArray);
}
}
- FetchData.java – copy the code below
package ajaxdemo;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
import org.hibernate.HibernateException;
import org.hibernate.SQLQuery;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.Transaction;
import org.hibernate.boot.registry.StandardServiceRegistryBuilder;
import org.hibernate.cfg.Configuration;
import org.hibernate.service.ServiceRegistry;
import model.Countrydetail;
public class FetchData {
private static SessionFactory factory;
public static void getFactory() {
try
{
Configuration configuration=new Configuration();
configuration.addAnnotatedClass(model.Countrydetail.class);
configuration.configure();
//ServiceRegistry sr= new ServiceRegistryBuilder().applySettings(configuration.getProperties()).buildServiceRegistry();
ServiceRegistry sr = new StandardServiceRegistryBuilder().applySettings(configuration.getProperties()).build();
factory=configuration.buildSessionFactory(sr);
}catch(Exception e){
e.printStackTrace();
}
}
/* Method to READ all the rows using Entity Query */
public static ArrayList<Countrydetail> getAllRows( ){
getFactory();
Session session = factory.openSession();
Transaction tx = null;
ArrayList<Countrydetail> countryDetailArrayList=new ArrayList<Countrydetail>();
try{
tx = session.beginTransaction();
String sql = “SELECT * FROM countrydetails;”;
SQLQuery query = session.createSQLQuery(sql);
query.addEntity(Countrydetail.class);
List<Countrydetail> countryDetailsList = query.list();
for (Iterator iterator =
countryDetailsList.iterator(); iterator.hasNext();){
Countrydetail countryDetailsObject = (Countrydetail) iterator.next();
countryDetailArrayList.add(countryDetailsObject);
}
tx.commit();
}catch (HibernateException e) {
if (tx!=null) tx.rollback();
e.printStackTrace();
}finally {
session.close();
}
return countryDetailArrayList;
}
}
- Countrydetail.java – Enter the below code
package model;
import java.io.Serializable;
import javax.persistence.*;
/**
* The persistent class for the countrydetails database table.
*
*/
@Entity
@Table(name=”countrydetails”)
@NamedQuery(name=”Countrydetail.findAll”, query=”SELECT c FROM Countrydetail c”)
public class Countrydetail implements Serializable {
private static final long serialVersionUID = 1L;
@Id
private String code;
private int capital;
private String continent;
private String name;
private int population;
private String region;
public Countrydetail() {
}
public String getCode() {
return this.code;
}
public void setCode(String code) {
this.code = code;
}
public int getCapital() {
return this.capital;
}
public void setCapital(int capital) {
this.capital = capital;
}
public String getContinent() {
return this.continent;
}
public void setContinent(String continent) {
this.continent = continent;
}
public String getName() {
return this.name;
}
public void setName(String name) {
this.name = name;
}
public int getPopulation() {
return this.population;
}
public void setPopulation(int population) {
this.population = population;
}
public String getRegion() {
return this.region;
}
public void setRegion(String region) {
this.region = region;
}
}
Step 8 :
Create “hibernate.cfg.xml” in src folder.
Right Click on “Src”->New-> file ->enter the name “hibernate.cfg.xml>
Enter the below code in “hibernate.cfg.cml”.
<?xml version=“1.0” encoding=“UTF-8”?>
<!DOCTYPE hibernate-configuration PUBLIC
“-//Hibernate/Hibernate Configuration DTD//EN”
“http://www.hibernate.org/dtd/hibernate-configuration-3.0.dtd“>
<hibernate-configuration>
<session-factory>
<property name=“connection.driver_class”>com.mysql.jdbc.Driver</property>
<property name=“connection.url”>jdbc:mysql://localhost:3306/world</property>
<property name=“connection.username”>root</property>
<property name=“connection.password”>varun123</property>
<property name=“connection.pool_size”>1</property>
<property name=“dialect”>org.hibernate.dialect.MySQLDialect</property>
<property name=“current_session_context_class”>thread</property>
<property name=“show_sql”>true</property>
<property name=“hbm2ddl.auto”>update</property>
<mapping class=“model.Countrydetail”/>
</session-factory>
</hibernate-configuration>
Step 9 :
Run the application by right clicking on “index3.html” ->Run On Server->Select Tomcat server 7.0
Click on the button “Show Table” and below page will be displayed:
hi,
very good blog. I was trying to follow this. I have completed the steps mentioned in blog. When I ran the project, I got below error
this is how my config file looks like
Are there are specific steps to follow in mySQL DB. My SQL workbench version is 6.3
Error occurs at line
Session session = factory.openSession();
thank you !
Mohammed
Hi Mohammed,
Sorry for the late reply as I did not login to sdn for few months now because of other work commitments.
Thanks for appreciating the blog.
Have you resolved your issues by now?
Regards,
Varun