How to Fetch Data from Database in Modal - Onlyxcodes - onlyxcodes

Monday, 10 December 2018

How to Fetch Data from Database in Modal - Onlyxcodes

In this post, you are going to learn 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

Table Content

1. What is modal in bootstrap?

2. Requirement

3. Create Table

4. Display The Data

5. Bootstrap Modal Codes

6. jQuery and Ajax Codes

7. getData.jsp

8. 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. Requirement

Ojdbc14.jar – Download Here I use the oracle database to open the oracle database connection using this jar file.

Bootstrap – Download Bootstrap package required to be mandatory for this tutorial, because this package contains CSS and JS library to properly build bootstrap modal.

jQuery – Download Compulsory jquery library also needed to build modal.

NetBeans – Download I have this project developed in this editor. See below for the structure of the project directory.

NetBeans project directory of How to Fetch Data from Database in Modal

3. Create Table

In Oracle SQL command we create a table called bootstrap_modal, execute the SQL command below to quickly build a table.

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

create a table called bootstrap_modal

In this table we insert 3 dumping data, all of them dynamically displayed in bootstrap modal. Wrapping the SQL insert query command below.

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');

we insert 3 dumping data in table

By following SQL select query command to verify the completely inserted dumping data above 3. Okay, see added perfectly.

select * from bootstrap_modal;

Display three dumping data in table by SQL select query command


4. 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 we create a view button that containing 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 --> 

6. jQuery and Ajax Codes

The jQuery and ajax codes are specified below. The ajax({ codes that are responsible for creating 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: 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> 

7. getData.jsp

Now, this file is called secretly in the back-end through ajax to dynamically load content in bootstrap modal whatever Id was requested and the table 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

Thank you for coming to learn how to fetch data from database in modal.

Download Codes

No comments:

Post a comment