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.

Be the first to leave a comment

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

Leave a Reply