How to Retrieve Data From Database in Jsp Using Eclipse

In this tutorial, I will demonstrate how to retrieve data from the database in JSON format in Java. We know Java or JSP doesn't have built-in data encoding function in JSON format, so how to solve this issue.

Here is the available jQuery Ajax method that I use in this tutorial, this technique helps to retrieve data from database in JSON format and display it in HTML table format.

Let's start the tutorial, see which condition I use jQuery Ajax methodology to retrieve data from the database in JSON format.

Table Content

1. Database / Table

2. index.jsp

3. Table for Display Data

4. jQuery Ajax Codes

5. getData.jsp

6. Output

1. Database / Table

I created a person table here that contains the name, skill, and age of the three information.

          CREATE TABLE `tbl_person` (   `id` int(11) NOT NULL,   `name` varchar(20) NOT NULL,   `skill` varchar(30) NOT NULL,   `age` int(11) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1;                  

I have saved two dumping data in this table that are retrieved in JSON format, copy-paste the following data into the table.

          INSERT INTO `tbl_person` (`id`, `name`, `skill`, `age`) VALUES (1, 'Hamid Shaikh', 'Programmer', 24), (2, 'Pradeep Khodke', 'Designer', 24);        

2. index.jsp

Here are sample codes that retrieve dumping data from the table MySQL 'tbl_person' and embed it in the tag of select options.

Last see simply search for call buttons. When you select any person and click this button, the full data display in the table format will be selected using the Ajax process.

          <div class="form-group">  <label class="col-sm-2 control-label">Select Name</label>  <div class="col-sm-6">  <select id="person_list" class="form-control">   <option value=""> - select name - </option>   <%    String dburl="jdbc:mysql://localhost:3306/java_get_json_data_db";     String dbusername="root";        String dbpassword="";                                  try    {     Class.forName("com.mysql.jdbc.Driver");      Connection con=DriverManager.getConnection(dburl,dbusername,dbpassword);                                       PreparedStatement pstmt=null;                                      pstmt=con.prepareStatement("SELECT * FROM tbl_person ORDER BY name ASC");      ResultSet rs=pstmt.executeQuery();                                       while(rs.next())     {     %>      <option value="<%=rs.getInt("id")%>"><%=rs.getString("name")%></option>     <%     }    }    catch(Exception e)    {     e.printStackTrace();    }   %>  </select>  </div>    <button type="button" id="search" class="btn btn-success">Search</button>   </div>                  

3. Table for Display Data

See below simple table creating by plugin bootstrap and we set up and display person data in this table.

See the second division tag holding the value of two data attributes. id="person_details" this attribute displays the table with the data and style="display: none" this attribute visually hides the table by default.

          <div class="col-md-8"> <div id="person_details" style="display:none" class="table-responsive table-bordered" >   <table class="table">    <thead>     <tr>      <th>Name</th>      <th>Skill</th>      <th>Age</th>     </tr>    </thead>    <tbody>     <tr>      <td><span id="person_name"></span></td>      <td><span id="person_skill"></span></td>      <td><span id="person_age"></span></td>     </tr>                </tbody>   </table>  </div> </div>        

4. jQuery Ajax Codes

When you have select any person name and press on the search button then ajax( ) function has occurred, send your request to 'getData.jsp' file using the HTTP POST method and display complete data in tabular format for the specific person.

dataType:'JSON' tells jQuery that data response to expect in JSON format.

#search is the id button attribute and the click() method that uses that attribute to target your click event.

#person_list is the select option id attribute that holds the database table Id, and the val() method that uses that attribute to get Id value.

$('#person_details').css('display','block'); – This displays the table visually and #person_details is the division tag Id attribute that we've already discussed above.

See name, skill, and age are JSON objects we get from 'getData.jsp' file.

And #person_name, #person_skill, and #person_age are span tag id attributes for helping to set up unique data in table rows.

          <script type="text/javascript"> $(document).ready(function(){     $('#search').click(function(){        var id=$('#person_list').val();        if(id !==null || id !=='')        {            $.ajax({                url:'getData.jsp',                type:'POST',                data:'pid='+id,                dataType:'JSON',                success:function(data)                {                    $('#person_details').css('display','block');                    $('#person_name').text(data.name);                    $('#person_skill').text(data.skill);                    $('#person_age').text(data.age);                }            });        }        if(id==0)        {            alert('Please Select Person Name');            $('#person_details').css('display','none');        }     }); });     </script>                  

Learn More :

5. getData.jsp

This file converts MySQL rows to JSON format and gets specific requests through the function $.ajax() and fills out the details.

We assign the JSON object to retrieve able table records in else condition. The name, skill, and age are JSON objects.

Note – You will import JSON jar file packages in this file see line no 5. Unless you don't import the integrated object from JSON, it won't work.

          <%@page import="java.sql.ResultSet"%> <%@page import="java.sql.PreparedStatement"%> <%@page import="java.sql.DriverManager"%> <%@page import="java.sql.Connection"%> <%@page import="org.json.JSONObject,java.util.*"%>  <%     if(request.getParameter("pid")!=null)      {         int getID=Integer.parseInt(request.getParameter("pid"));                   String dburl="jdbc:mysql://localhost:3306/java_get_json_data_db";          String dbusername="root";             String dbpassword="";                   try         {             Class.forName("com.mysql.jdbc.Driver"); con=DriverManager.getConnection(dburl,dbusername,dbpassword);                           PreparedStatement pstmt=null;                           pstmt=con.prepareStatement("SELECT * FROM tbl_person WHERE id=?");              pstmt.setInt(1,getID);              ResultSet rs=pstmt.executeQuery();                           if(!rs.next())             {                                        out.print(" ");             }             else             {                 JSONObject obj=new JSONObject();                                     obj.put("name", rs.getString("name"));                 obj.put("skill", rs.getString("skill"));                   obj.put("age", rs.getInt("age"));                                  out.print(obj);              }                          con.close(); //close the connection         }         catch(Exception e)         {             e.printStackTrace();         }     } %>        

6. Output

Select any option name and click on the search button.

See display data in table format.

Download Codes

How to Retrieve Data From Database in Jsp Using Eclipse

Source: https://www.onlyxcodes.com/2019/02/how-to-retrieve-data-from-database.html

Share this post