displaying the group by id with respect driverid and name

Question

@Controller

    @RequestMapping(value ="/provider" ,method=RequestMethod.POST)
        public String welcome(Model model){
            Transaction trans=new Transaction();
            ArrayList<Transaction> asd=new ArrayList<Transaction>();
            List<Transaction> list=driverService.groupby();
            model.addAttribute("list1",list);
            return "NewFile";
        }


    public interface DriverService {
         public  List<Transaction> groupby();

         }


    @Service("driverService")
    public class DriverServiceImpl implements DriverService{

        @Autowired
        private DriverDAO driverDAO;
    @Override
        public List<Transaction> groupby() {

            return driverDAO.groupby();
        }
    }

    public interface DriverDAO {
     public  List<Transaction> groupby();

    }


    @Repository("driverDAO")
    public class DriverDAOImpl implements DriverDAO{
        @Autowired
        private HibernateTemplate hibernateTemplate;
        @SuppressWarnings("unchecked")
        @Override
        public List<Transaction> groupby() {

            String hql="select count(tx.driver.driverid),tx.driver.drivername from Transaction tx group by driver"; 
            return   (List<Transaction>)hibernateTemplate.find(hql);
    }
    }

pojo class

@Entity
@Table(name="driver_registration")
public class DriverDetails {

    @Id
    @GeneratedValue(strategy=GenerationType.AUTO)
    @Column(name="driver_id")
    private String driverid;

    @Column(name="driver_name")
    private String drivername;



    @OneToMany(mappedBy="driver", cascade = CascadeType.ALL)
     private List<Transaction> items;

    //setter and getters

    }


@Entity
@Table(name="transaction")
public class Transaction {
    @Id
    @GeneratedValue(strategy=GenerationType.AUTO)
    @Column(name="transaction_id")
    private String transactionid;
@ManyToOne()
    @JoinColumn(name="driver_id")
    private DriverDetails driver;
//setter and getters
}

jsp page

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
    <%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>



<head>

</body>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<link href="resources/css/bootstrap.min.css" rel="stylesheet" type="text/css">  
    <link href="resources/css/style.css" rel="stylesheet" type="text/css"> 

    <link href="resources/css/core.css" rel="stylesheet" type="text/css">
      <link href="resources/css/themify.css" rel="stylesheet" type="text/css">
      <link href="resources/css/dashboard.css" rel="stylesheet" type="text/css">


<title>RajaRatha Provider List</title>
<link href="resources/css/jquery.dataTables.min.css" rel="stylesheet" type="text/css"> 
 <link href="resources/css/buttons.dataTables.min.css" rel="stylesheet" type="text/css"> 

<!--  pdf and excel plugins -->

   <script  type="text/javascript"  src="resources/js/jquery-1.12.4.js"></script>
   <script  type="text/javascript"  src="resources/js/jquery.dataTables.min.js"></script>
   <script  type="text/javascript"  src="resources/js/dataTables.buttons.min.js"></script>
  <script  type="text/javascript"  src="resources/js/buttons.flash.min.js"></script>
  <script  type="text/javascript"  src="resources/js/pdfmake.min.js"></script>
  <script  type="text/javascript"  src="resources/js/jszip.min.js"></script>
  <script  type="text/javascript"  src="resources/js/buttons.html5.min.js"></script>
  <script  type="text/javascript"  src="resources/js/buttons.print.min.js"></script>
  <script  type="text/javascript"  src="resources/js/vfs_fonts.js"></script>


  <script>


  $(document).ready(function(){
      $('#myTable').DataTable({

          'ordering':false,
            dom: 'Bfrtip',

            buttons:[
                 {
                     extend: 'excelHtml5',
                     title: 'Data export'
                 },
                 {
                     extend: 'pdfHtml5',
                     title: 'Data export'
                 },
                  {
                     extend: 'csvHtml5',
                     title: 'Data export'
                 },
                  {
                     extend: 'print',
                     title: 'Data export'
                 } 
           ]
        });
    });

  </script>

</head>

<body >
<div class="wrapper">
        <div class="preloader" style="display: none;"></div>
        <div class="site-overlay"></div>

       <!--Navigation panel starts here  -->
         <%@include file="navigation.jsp" %>    
         <!--Navigation panel ends here  -->
  <div class="site-content">
 <div class="content-area py-1">
    <div class="container-fluid">
        <div class="box box-block bg-white">
            <h5 class="mb-1">
                Providers
                                <!-- <span class="pull-right">(*personal information hidden in demo)</span> -->
                            </h5>


            <a href="/RajaRathaDashBoardApp/RajarathaProviderCreate" style="margin-left: 1em;" class="btn btn-primary pull-right"><i class="fa fa-plus"></i> Add New Provider</a>
         <!--    <table class="table table-striped table-bordered dataTable" id="table-2" id="myTable"> -->

               <table class="table table-striped table-bordered dataTable" id="myTable">
         <!--    <table id="myTable"  class="table table-striped table-bordered dataTable"  > -->
             <h4 style="color:green">${message}</h4> 

                <thead>
                    <tr>
                        <th>ID</th>
                        <th>Full Name</th>
                        <!-- <th>Email</th>
                        <th>Mobile</th>
                        <th>Total Requests</th>
                        <th>Accepted Requests</th>
                        <th>Cancelled Requests</th> -->
                        <!-- <th>Status</th>
                        <th>Ratings</th>
                        <th>Online</th>
                        <th>Action</th> -->
                    </tr>
                </thead>
                <tbody  >

                 <c:forEach var="list" items="${list1}">  
                     <tr>
                        <td>${list.driver.driverid}</td> 

                        <td>${trans.driver.drivename}</td> 

                       <%--  <td>${driver.location1.driveremail}</td>
                        <td>${driver.location1.driverphone}</td>
                        <td>${noRides}</td>
                        <td></td>
                        <td>0</td>
                         <td>${driver.driverStatus}</td>  --%>
                        <td></td>


                       <%--  <td><span class="tag tag-success">${driver.location1.driverenabledstatus}</span></td>      
                       <td><a class="btn btn-danger btn-block" href="/RajaRathaDashBoardApp/RajarathaDocumentList">Disable</a>

                       <td> 
                       <center><a class="btn btn-block label-right" href="/RajaRathaDashBoardApp/RajarathaDocumentList?driver=${driver.location1.driverid}&owner=${driverlocation1.ownerid}">View Details</a>
                 </center>
                  <center>     <a  href="/RajaRathaDashBoardApp/RajarathaProviderEdit?email=${driver.location1.driveremail}"> <span style='font-size:30px;'>&#x270D;</span> </a>
                                </center>                              
                                  </td>     --%>                       


                    </tr>
                    </c:forEach>
                                </tbody>
                <tfoot>
                    <tr>
                        <th>ID</th>
                        <th>Full Name</th>
                        <th>Email</th>
                        <th>Mobile</th>
                        <th>Total Requests</th>
                        <th>Accepted Requests</th>
                        <th>Cancelled Requests</th>
                        <th>Status</th>
                        <th>Ratings</th>
                        <th>Online</th>
                        <th>Action</th>
                    </tr>
                </tfoot>
            </table>
        </div>
    </div>
</div>

                    <footer class="footer">
    <div class="container-fluid">
       <!--  <div class="row text-xs-center" > -->
            <div class="col-sm-4 text-sm-left mb-0-5 mb-sm-0">
                <p>2017-2018. RajaRatha</p>
            </div>
        <!-- </div> -->
    </div>
</footer>
        </div>
        </div>

    </body>

</html>

output:

113:                 
114:                  <c:forEach var="list" items="${list1}">  
115:                      <tr>
116:                         <td>${list.driver.driverid}</td> 
117:                      
118:                         <<td>${trans.driver.drivename}</td> 
119:                          


Stacktrace:] with root cause
java.lang.NumberFormatException: For input string: "driver"
    at java.lang.NumberFormatException.forInputString(NumberFormatException.java:65)
    at java.lang.Integer.parseInt(Integer.java:580)
    at java.lang.Integer.parseInt(Integer.java:615)
    at javax.el.ArrayELResolver.coerce(ArrayELResolver.java:144)
    at javax.el.ArrayELResolver.getValue(ArrayELResolver.java:61)
    at org.apache.jasper.el.JasperELResolver.getValue(JasperELResolver.java:110)
    at org.apache.el.parser.AstValue.getValue(AstValue.java:169)
    at org.apache.el.ValueExpressionImpl.getValue(ValueExpressionImpl.java:184)
    at org.apache.jasper.runtime.PageContextImpl.proprietaryEvaluate(PageContextImpl.java:702)
    at org.apache.jsp.view.NewFile_jsp._jspx_meth_c_005fforEach_005f0(NewFile_jsp.java:468)
    at org.apache.jsp.view.NewFile_jsp._jspService(NewFile_jsp.java:361)
    at org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:70)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:741)
    at org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:444)
    at org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:385)

I am trying to get individual data from database using group by method in hql but query executed successfully and also it does not display on the respective web page what is the problem in the above code and also how to use group by in Hibernate and Spring Mvc application and also inform what is wrong with the mapping.

score 0 · Answer 1 · answered Jan 13 '18 at 09:17

1) You query should have the same column ins group by as in the select (excluding the count):

select count(tx.driver.driverid),tx.driver.drivername 
from Transaction tx
group by tx.driver.drivername

2) You are trying to perform a projection but you are expecting a List of Transaction. By default the persistence provider will return a List<Object[]> and you will need to extract manually the count column and the drivername column.

3) Alternatively you could use a ResultClass feature -> example

displaying the group by id with respect driverid and name

1 Answers1