Skip to Content

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


Capture_0.JPG



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


Capture_1.JPG


Capture_2.JPG



Step 3:

Create new html file as below :

WebContent->Right Click->New->HTML file-> Enter name “index3.html” ->Finish


Capture_3.JPG

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-uilibs‘ 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

  1. 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.

Capture_4.JPG

Step 7:

Create below class files :

  1. 1) ActionServlet2.java
  2. 2) FetchData.Java
  3. 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);

                   

    }

}

  1. 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;

                   }

                } 

  1. 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


Capture_5.JPG


Click on the button “Show Table” and below page will be displayed:




Capture_6.JPG

To report this post you need to login first.

1 Comment

You must be Logged on to comment or reply to a post.

  1. Silent Monk

    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

     

     

    (0) 

Leave a Reply