Information on JAVA.: 2015

Wednesday, 2 December 2015

ServerSide pagination in java using ajax

index.html

<!DOCTYPE html>
<html>
<head>
<meta charset="ISO-8859-1">
<title>Insert title here</title>
<link href="lib/jquery.dataTables.min.css" rel="stylesheet">
<script src="lib/jquery-2.1.4.min.js" type="text/javascript"></script>
<script src="lib/jquery.dataTables.min.js" type="text/javascript"></script>
<script type="text/javascript">

 $(document).ready(function(){

       $('#firmtable').dataTable({
        "processing": true,
        "serverSide": true,
        "ajax": {
            "url": "data.json",
            "type": "GET"
        },

        "columns": [
            {"mData": "id"},
            {"mData": "lineNumber"},
            {"mData": "status"},
            {"mData": "findingType"}
        ]
    });
   
});
</script>
</head>
<body>
    <table id="firmtable" class="display" border="1" >
        <thead>
            <tr>
                <th>id</th>
                <th>lineNumber</th>
                <th>status</th>
                <th>findingType</th>
            </tr>
        </thead>
        <tbody>
        </tbody>
    </table>
</body>
</html>

data.json

{ 
 "draw":0,
 "iTotalDisplayRecords":35,
 "iTotalRecords":100,
 "aaData":[  
  {"id":224,"lineNumber":"NA","status":"Assigned","findingType":"Infrastructure"},
  {"id":225,"lineNumber":"NA","status":"Assigned","findingType":"Infrastructure"},
  {"id":226,"lineNumber":"NA","status":"Assigned","findingType":"Infrastructure"},
  {"id":227,"lineNumber":"NA","status":"Assigned","findingType":"Infrastructure"},
  {"id":232,"lineNumber":"NA","status":"Assigned","findingType":"Infrastructure"}

Serverside pagination in Hibernate

var oTable =

 $('#productionIssuesTable').dataTable({
   "bRetrieve": true,"processing": true,"serverSide": true,"aaSorting": [],
   "ajax": {
 "url": 'rest/production/getBatchIssueProduction',
 "type": "GET",
 },
 "fnRowCallback": function( nRow, aData, iDisplayIndex ) {

//first column is checkbox

      $('td:eq(0)', nRow).html('

<input type="checkbox" class="issues" id="'+nRow.firstChild.textContent+'">');

//second column is anchor tag

 $('td:eq(1)', nRow).html('

<a onclick="updateIssue('+nRow.firstChild.nextSibling.textContent+')">'

+nRow.firstChild.nextSibling.textContent+ '</a>');

//last column is action button

 $('td:eq(14)', nRow).html('

<button class=" btn btn-success custom waves-effect"

onclick="assignIssues('+nRow.firstChild.nextSibling.textContent+')" >Assign</button>');
    return nRow;
   
     },
     "bSearchable": true,
     "aoColumnDefs": [
                      { 'bSortable': false, 'aTargets': [ 0,1,2,3,4,5,6,7,8,9,10,11,12] }
                      ],
     "columns" : [{
      "mData" : "check",
     },{
      "mData" : "id",
     }, {
      "mData" : "project"
     }, {
      "mData" : "activityType"
     }, {
      "mData" : "findingType"
     }, {
      "mData" : "sourceCodeFile"
     },{
      "mData" : "lineNumber"
     }, {
      "mData" : "testingInstance"
     },{
      "mData" : "dateRaised"
     }, {
      "mData" : "etdClosure"
     },{
      "mData" : "status.statusCode"
     }, {
      "mData" : "risk"
     },{
      "mData" : "batchId"
     },{
      "mData" : "batchDescription"
     },{
      "mData" : "assign"
     }],

    }); 
 <div class="container-fluid">
     <table class="display table" id="productionIssuesTable">
        <thead>
           <tr><th><input type="checkbox" id="checkall" /></th><td><b>Id</b></td>

          <td><b>Project</b></td><td><b>Activity Type</b></td><td><b>Finding Type</b></td>

          <td><b>Source Code Files</b></td><td><b>Line Number</b></td>
          <td><b>Testing Instance</b></td><td><b>Date Raised</b></td>

          <td><b>Expected Date To Close</b></td><td><b>Status</b></td>

          <td><b>Risk</b></td><td><b>Batch Id</b></td><td><b>Batch Description</b></td>

          <td><b>Assign</b></td></tr> 
        </thead>
        <tbody >
        </tbody>
     </table>
</div>

Create modal

public class DatatableView {
 private int  iTotalRecords;
 private int  iTotalDisplayRecords;
 private List aaData;
//generate setters and getters
}

JAVA SIDE

@Controller
@RequestMapping("production")
public class ProductionController { 

@RequestMapping(value = "/getBatchIssueProduction", method = RequestMethod.GET)
public @ResponseBody DatatableView getBatchIssueProduction(

  @RequestParam(value = "length") String displayReocrds,

  @RequestParam(value = "start") String pageNum,

  @RequestParam(value = "search[value]") String search) {
     try {
 DatatableView view= productionDao
            .getBatchIssueProduction(pageNum,displayReocrds,search);
 return new DatatableView(view.getiTotalRecords(), view.getiTotalDisplayRecords(), view.getAaData());
 } catch (Exception e) {
 return new DatatableView();
  }
 } 
}

@Service
@Transactional
public class ProductionDao {

@SuppressWarnings("unchecked")
public DatatableView getBatchIssueProduction(

  String pageNumber,

  String displayReocrds,

  String search){
  List<BatchIssueProductionsView> batchIssueProductionViews = null;
  List<BatchIssueProductionViewModel> issuesProductionModels=

         new ArrayList<BatchIssueProductionViewModel>();
  Integer firstValue=Integer.parseInt(pageNumber) ;
  Integer pageSize=Integer.parseInt(displayReocrds);
  Integer noOfRows;Query query;
  try {
     Session session = sessionFactory.getCurrentSession(); 
  if(search==""){
    query = session.createQuery("from BatchIssueProductionsView where status= :status ")
                   .setParameter("status", Constants.OPEN);
  }else{
    query = session.createQuery("from BatchIssueProductionsView where status= :status 
              and (batchDescription like :search or batchId like :search or risk like :search
              or etdClosure like :search  or dateRaised like :search or 
              testingInstance like :search  or lineNumber like :search  or 
              sourceCodeFile like :search or findingType like :search or 
              activityType like :search or project like :search or id like :search) ")
              .setParameter("status", Constants.OPEN).setParameter("search", "%"+search+"%");
  }
  batchIssueProductionViews = query.list();
  noOfRows=batchIssueProductionViews.size();
  query = query.setFirstResult(firstValue);
  query.setMaxResults(pageSize);
  List<BatchIssueProductionsView> batchIssueProductionViews1  = query.list();
  for (BatchIssueProductionsView batchIssueProductionView : batchIssueProductionViews1) {
    issuesProductionModels.add(entityToModelBatchIssueProducction(batchIssueProductionView));       
  }
  DatatableView datatableView = new DatatableView();
  datatableView.setAaData(issuesProductionModels);
  datatableView.setiTotalDisplayRecords(noOfRows);
  datatableView.setiTotalRecords(noOfRows);
  return datatableView;
 } catch (HibernateException e) {
  // TODO Auto-generated catch block
   e.printStackTrace();
   return new DatatableView();
 }
  }
}

Sunday, 1 November 2015

Hibernate native SQL queries

Hibernate provide a createSQLQuery method to let you call your native SQL statement directly.

Here we are joining 3 tables.

Session session = sessionFactory.getCurrentSession();
Query query = session
                 .createSQLQuery("SELECT psh.* FROM prod_sales_history psh,customer c,city cit where (psh.status=:status1 or psh.status=:status2) and cit.id=:cid and c.city=cit.id and psh.cust_id=c.id order by order_date;")
                   .setParameter("cid", cityId).setParameter("status1", Constants.ORDER_COMPLETELY_APPROVED).setParameter("status2", Constants.ORDER_PARTIALLY_APPROVED);
           List<Object[]> prodList =query.list();
for (Object[] object : prodList) {
System.out.println(object[0].toString());
             }

Fetching unique result from 3 tables placing in HashMap.

Session session = sessionFactory.getCurrentSession();
       Query query0 = session.createSQLQuery("SELECT * FROM agent where id=:agentId")
               .setParameter("agentId", Integer.parseInt(agentId));
       query0.setResultTransformer(AliasToEntityMapResultTransformer.INSTANCE);
       Query query = session.createSQLQuery("SELECT sum(pa.approved_sell_price) as amt FROM agent_order_map am,products_approved pa where pa.status=:status and pa.id=am.products_approved_id and am.agent_id=:agentId")
               .setParameter("status", Constants.PRODUCT_AMOUNT_RECEIVED_AGENT)
               .setParameter("agentId", Integer.parseInt(agentId));
       query.setResultTransformer(AliasToEntityMapResultTransformer.INSTANCE);
       Query query1 = session.createSQLQuery("SELECT sum(amount) as amount FROM agent_payment where agent_id=:agentId")
               .setParameter("agentId", Integer.parseInt(agentId));
       query1.setResultTransformer(AliasToEntityMapResultTransformer.INSTANCE);
       Map<String, Object> listResult = new HashMap();
       listResult.put("Agent",query0.uniqueResult());
       listResult.put("TotalAmountToBePaid", query.uniqueResult());
       listResult.put("TotalPaid", query1.uniqueResult());
       return listResult;

Maintain session in Web Applications.

Provides a way to identify a user across more than one page request.
The servlet container uses this interface to create a session between an HTTP client and an HTTP server. The session persists for a specified time period, across more than one connection or page request from the user.

We can save user data in modal and modal can be set to session while login.

Example:AuthenticationResponse is a modal.

AuthenticationDAO.java

   @Autowired
    private HttpSession httpSession;
    public void setHttpSession(HttpSession httpSession) {
        this.httpSession = httpSession;
    }

AuthenticationResponse authentications = new AuthenticationResponse();
                authentications.setRole(role.getUserRole().getId());
                authentications.setUserId(id.getId());
    httpSession.setAttribute("isLogin", authentications);

Header is common in all pages and it is loaded first when page loads.
In Header we can check whether user session exist.

Header.html

$.getJSON("rest/authentication/isLogin", function(res){
           if(res.status){
          // user session exit
     }else{
               location.href="index.jsp"}
});

AuthenticationDAO.java

   @RequestMapping(value="/isLogin",method=RequestMethod.GET)
    public @ResponseBody Boolean isLoginUser(){
        try {
            AuthenticationResponse loginAuth = (AuthenticationResponse) httpSession.getAttribute("isLogin");
            if(loginAuth!=null){
                return true;
            }
        } catch (Exception e) {
            e.printStackTrace();
               return false;
        }
}

Friday, 10 April 2015

Export Table To Excel Using Jquery

 HTML
 <table id="tableId">
        <tr>
            <th>Id</th>
            <th>Name</th>
            <th>Sal</th>
        </tr>
        <tr>
            <td>1</td>
            <td>Abc</td>
            <td>100000</td>
        </tr>
        <tr>
            <td>2</td>
            <td>Pqr</td>
            <td>100000</td>
        </tr>
        <tr>
            <td>3</td>
            <td>Xyz</td>
            <td>100000</td>
        </tr>
    </table>


<input type="button" id="btnExport"

value=" Export Table data into Excel " />

JAVA SCRIPT

  <script src="http://ajax.googleapis.com/ajax/libs/jquery/2.0.0/jquery.min.js">
</script>
 <script src="../src/jquery.table2excel.js"></script>

  $("#btnExport").click(function (e) {

   var dt = new Date();
 $("#tableId").table2excel({
  exclude: ".noExl",
  name: 'Table'+dt.getDate()+'_'+(dt.getMonth()+1)+'_'+dt.getFullYear()
 });

});
 jquery.table2excel.js


//table2excel.js
;(function ( $, window, document, undefined ) {
  var pluginName = "table2excel",
    defaults = {
    exclude: ".noExl",
                name: "Table2Excel"
  };

  // The actual plugin constructor
  function Plugin ( element, options ) {
    this.element = element;
    // jQuery has an extend method which merges the contents of two or
    // more objects, storing the result in the first object. The first object
    // is generally empty as we don't want to alter the default options for
    // future instances of the plugin
    this.settings = $.extend( {}, defaults, options );
    this._defaults = defaults;
    this._name = pluginName;
    this.init();
  }

  Plugin.prototype = {
   init: function () {
    var e = this;
    e.template = "<html xmlns:o=\"urn:schemas-microsoft-com:office:office\" xmlns:x=\"urn:schemas-microsoft-com:office:excel\" xmlns=\"http://www.w3.org/TR/REC-html40\"><head><!--[if gte mso 9]><xml>";
    e.template += "<x:ExcelWorkbook><x:ExcelWorksheets><x:ExcelWorksheet><x:Name>{worksheet}</x:Name><x:WorksheetOptions>";
    e.template += "<x:DisplayGridlines/></x:WorksheetOptions></x:ExcelWorksheet></x:ExcelWorksheets></x:ExcelWorkbook></xml><![endif]--></head><body><table>{table}</table></body></html>";
    e.tableRows = "";

    // get contents of table except for exclude
    $(e.element).find("tr").not(this.settings.exclude).each(function (i,o) {
     e.tableRows += "<tr>" + $(o).html() + "</tr>";
    });
    this.tableToExcel(this.tableRows, this.settings.name);
   },
   tableToExcel: function (table, name) {
    var e = this;
    e.uri = "data:application/vnd.ms-excel;base64,";
    e.base64 = function (s) {
     return window.btoa(unescape(encodeURIComponent(s)));
    };
    e.format = function (s, c) {
     return s.replace(/{(\w+)}/g, function (m, p) {
      return c[p];
     });
    };
    e.ctx = {
     worksheet: name || "Worksheet",
     table: table
    };
    window.location.href = e.uri + e.base64(e.format(e.template, e.ctx));
   }
  };

  $.fn[ pluginName ] = function ( options ) {
    this.each(function() {
      if ( !$.data( this, "plugin_" + pluginName ) ) {
        $.data( this, "plugin_" + pluginName, new Plugin( this, options ) );
      }
    });

    // chain jQuery functions
    return this;
  };

})( jQuery, window, document );

Wednesday, 11 March 2015

MySQL GROUP_CONCAT Function

MySQL GROUP_CONCAT(): Function returns a string with concatenated non-NULL value from a group.

Let’s take a look at the table vehicle_Driver.

We can find each vehicle_number having more than one driver_id.
i.e 1 vehicle contains more than one driver.

Desired Output:

id vehicle_number driver_id
1 HR55M6112 2,1,10

Want driver names to be displayed in place of driver_id look at driver_master.

Desired Output

vehicle_number driver_name

HR55M6112 Abc,Xyz,Pqr

Query

SELECT m.vehicle_number,
(select GROUP_CONCAT(driver_name) from driver_master where driver_id in (select driver_id from vehicle_driver where vehicle_number=m.vehicle_number)) drivers
FROM vehicle_driver m group by vehicle_number;