How to Fetch Data from Database in Modal - onlyxcodes

Monday 10 December 2018

How to Fetch Data from Database in Modal

In this post, I will explain how to fetch data from database in modal of Bootstrap.


Modals are becoming a highly successful and valuable part of websites such as UI and UX, we can play video, message forms and many other things with modals. The Modals are nowadays regularly used for inserting, updating, deleting operations as well as displaying dynamic content.


I have covered up one functionality of modal in this tutorial to dynamically display oracle data in bootstrap modal through Ajax call with JSP. Let's dive into the tutorial and see how that happens.


how to fetch data from database in modal of bootstrap

Table Content

1. What is modal in bootstrap?

2. Create Table

3. Display The Data

4. Bootstrap Modal Codes

5. jQuery and Ajax Codes

6. getData.jsp

7. Output


The modal is a dialog box/popup window and we can say modal is a child window of the parent window. The intention is to display data from a separate cause that may communicate with others without leaving the parent page.


Modal is used for lightboxes, user alerts, UI improvements, modules for e-commerce as well as many other instances.


2. Create Table

In the Oracle SQL command, I have created a table called bootstrap_modal.


You can use below SQL command below to build your table name.


create table bootstrap_modal(
id number,
username varchar2(13),
owner varchar2(12),
country varchar2(12)
);

In this table, I have inserted 3 dumping data that dynamically display in the bootstrap modal.


insert into bootstrap_modal (id,username,owner,country) values (1,'steve jobs','Apple','U.S.A');
insert into bootstrap_modal (id,username,owner,country) values (2,'Bill Gates','Microsoft','U.S.A');
insert into bootstrap_modal (id,username,owner,country) values (3,'Lary Page','Google','U.S.A');

By following the SQL select query command you can verify the completely inserted dumping data.


select * from bootstrap_modal;

3. Display The Data

I created index.jsp page. Before closing the </head > tag, I included bootstrap CSS, JS and jQuery library inside this page.


<link href="bootstrap/css/bootstrap.min.css" rel="stylesheet" type="text/css">
<script src="js/jquery-1.12.4-jquery.min.js" type="text/javascript"></script>
<script src="bootstrap/js/bootstrap.min.js" type="text/javascript"></script> 

Here is an easy code that will select and display all data from the bootstrap_modal table in an HTML table.


Here I created the view button that contains three data attributes.


data-target="#view-modal" – This attribute toggles modal to click event.


data-target="#view-modal" – That attribute will launch a targeted modal containing view-modal Id.


id="<%=rs.getInt("id")%>" – Last of this attribute that has table Id and will assist send Id to another page through ajax.


<td><button data-toggle="modal" data-target="#view-modal" id="<%=rs.getInt("id")%>" class="btn btn-sm btn-success"><i class="glyphicon glyphicon-eye-open"></i> View</button></td>  

See I've full codes pasted here.


<table class="table">
        <thead>
            <tr>
                <th>Username</th>
                <th>View</th>
            </tr>
        </thead>
        <%
            String dburl="jdbc:oracle:thin:@localhost:1521:XE"; //database url string
            String dbusername="system"; //database username
            String dbpassword="tiger"; //database password
                                    
            try
            {
                Class.forName("oracle.jdbc.driver.OracleDriver"); //load driver
                Connection con=DriverManager.getConnection(dburl, dbusername, dbpassword); //create connection
                                        
                PreparedStatement pstmt=null; //create statement
                                        
                pstmt=con.prepareStatement("SELECT * FROM bootstrap_modal"); //sql select query
                ResultSet rs=pstmt.executeQuery(); //execute query and set in ResultSet object "rs"
        %>          
                <tbody>
                    <%
                        while(rs.next())
                        {
                     %>
                        <tr>
                            <td><%=rs.getString("username")%></td>
                            <td><button data-toggle="modal" data-target="#view-modal" id="<%=rs.getInt("id")%>" class="btn btn-sm btn-success"><i class="glyphicon glyphicon-eye-open"></i> View</button></td>
                        </tr>
       <%
                        }
                     %>
                </tbody>
                     <%
   }
   catch(Exception e)
   {
     e.printStackTrace();
   }
        %>
</table>

See the data display visually below this type.


fetch data from database and display visually in table format

now as we can see there is a view button in each row. If any of the buttons are clicked a popup modal will become dynamically displayed in bootstrap modal with a specific clicked row with complete row specific data.


That's modal code of bootstrap, this modal id="view-modal" attribute target Id for the attribute data-target="#view-modal". When the button clicks along with the following modal popup in tabular format with the target row with full data.


The server response will still be loaded within modal body class in id="show-data", and the response will be in HTML format.


<div id="view-modal" class="modal fade" tabindex="-1" role="dialog" aria-labelledby="myModalLabel" aria-hidden="true" style="display: none;">
    <div class="modal-dialog"> 
        <div class="modal-content"> 
                  
            <div class="modal-header"> 
                <button type="button" class="close" data-dismiss="modal" aria-hidden="true">×</button> 
                    <h4 class="modal-title">
                        <i class="glyphicon glyphicon-user"></i> All User Records 
                    </h4> 
            </div> 
                      
            <div class="modal-body">   
            <!-- all data will be show here -->                          
                <div id="show-data"></div>
            </div> 
                      
            <div class="modal-footer"> 
                <button type="button" class="btn btn-default" data-dismiss="modal">Close</button>  
            </div> 
                        
        </div> 
    </div>
</div><!-- /.modal --> 

5. jQuery and Ajax Codes

The jQuery and Ajax codes I have specified below. In this code, the Ajax () codes are responsible for creating the dynamic bootstrap modal.


.btn_success is a class attribute of view button, $(this).attr('id') keeps is id variable and sends it via HTTP POST request to the URL property getData.jsp and returns the response in HTML format.


When the request has been made, the data will be returned and displayed in the modal body of the bootstrap. $('#showdata').'html(data)';


Note:  The attr() method returns the current id value of the table. 


<script type="text/javascript">
    $(document).ready(function(){
       $('.btn-success').click(function(){
           var id=$(this).attr("id");
           $.ajax({
              url:"getData.jsp",
              type:"post",
              data:"uid="+id,
              success:function(data){
                $("#show-data").html(data);
                 
              }
           });
       });
    });
</script> 

6. getData.jsp

This file I created separately.


This file we call silently in the back-end through Ajax to dynamically load content in bootstrap modal whatever ID will request, that ID data will be displayed in $("# show-data") div bootstrap modals.


<%@page import="java.sql.ResultSet"%>
<%@page import="java.sql.PreparedStatement"%>
<%@page import="java.sql.DriverManager"%>
<%@page import="java.sql.Connection"%>
<%
    if(request.getParameter("uid")!=null) //get "uid" from index.jsp file jQuery ajax part this line "uid="+id".
    {
        int id=Integer.parseInt(request.getParameter("uid")); //get "uid" and store in new "id" variable
        
        String dburl="jdbc:oracle:thin:@localhost:1521:XE"; //database url string
        String dbusername="system"; //database username
        String dbpassword="tiger"; //database password

        try
        {
            Class.forName("oracle.jdbc.driver.OracleDriver"); //load driver
            Connection con=DriverManager.getConnection(dburl,dbusername,dbpassword); //create connection

            PreparedStatement pstmt=null; //create statement

            pstmt=con.prepareStatement("SELECT * FROM bootstrap_modal WHERE id=?"); //sql select query
            pstmt.setInt(1,id); //variable "id" set this
            ResultSet rs=pstmt.executeQuery(); //execute query and set in ResultSet object "rs"

            while(rs.next())
            {
                %>
                <div class="table-responsive">
  
                <table class="table table-striped table-bordered">
                    <tr>
                    <th>ID</th>
                    <td><%=rs.getInt("id")%></td>
                    </tr>
                        
                    <tr>
                    <th>Username</th>
                    <td><%=rs.getString("username")%></td>
                    </tr>
                        
                    <tr>
                    <th>Owner</th>
                    <td><%=rs.getString("owner")%></td>
                    </tr>
                    
                    <tr>
                    <th>Country</th>
                    <td><%=rs.getString("country")%></td>
                    </tr>
                        
                </table>
   
                </div>
                <%
                    con.close();
            }
        }
        catch(Exception e)
        {
            e.printStackTrace();
        }
    }
%>

8. Output

All codes are perfectly executed when you click any button to display personal data such as username, company owner and country name.


See Modal with table format will look good.


See output How to Fetch Data from Database in Modal

Download Codes

No comments:

Post a Comment

Post Bottom Ad