Json Encode In JSP - MySQL Data Encode In Json Example - onlyxcodes

Tuesday, 12 March 2019

Json Encode In JSP - MySQL Data Encode In Json Example

Today I get a simple, helpful and tiny bit of code in this tutorial that is JSON encode in JSP and MySQL data encode in JSON format.  

The JSON is a format for data transfer between a website or mobile apps, which can easily translate data in a human-readable format into simple text. JSON is a language-independent and present time most uses compare to XML.

This tutorial is very small before we start the tutorial let's explore JSON and its syntax quickly.

JSON Encode In JSP - MySQL Data Encode In Json Example | Onlyxcodes

Table Content

1. About JSON

    1.1 JSON Simple Syntax

    1.2 JSON Syntax Of Multiple Key And Value

    1.3 JSON Array Syntax

2. Tools And Jar Files

3. Database And Table

4. Fetch Data From Table And Encoding in JSON

5. Output


1. About JSON [ JavaScript Object Notation ]

JSON, or JavaScript Object Notation, is an open standard format that transmits data objects consisting of attribute-value pairs that use human-readable text. This is mostly used to transfer information between a server and a web application, as an alternative to XML 

1.1 JSON Simple Syntax :


Below you can see a simplistic syntax of JSON, each key and value in the start { bracket and end } bracket.

{ key : value } 

Example :

{
 "mobile" : "iphone"
}  

1.2 JSON Syntax Of Multiple Key and Value :


In this syntax, there is one object collected from multiple key and value/pair that contains separate comma symbols. 

{
 "key" : "value", "key" : "value", "key" : "value"
} 

Example :

Here is the object called mobile which collects multiple keys and values separated by the symbol of a comma.

{
 "mobile" : "iphone", "price" : "10000", "color" : "black"
} 

1.3 JSON Array Syntax :


Below is the syntax and illustration of JSON with Array, where the array defines the ordered list values, beginning with [ bracket and ending with ] bracket, each value separated by comma symbol. 

{
 "key" : [
  "value",
  "value",
  "value",
  "value"   
 ]
} 

Example :

The colors are array objects composed of various values which are red, yellow, blue, black and all values divided by a symbol of a comma.

{
 "colors" : [
  "red",
  "yellow",
  "blue",
  "black"   
 ]
} 

Let's see JSON work with JSP and encoding MySQL data

2. Tools and Jar Files

This tutorial compulsory required jar files from mysql-connector-java-5.1.15-bin.jar and org.json-chargebee-1.0.jar.

See the structure of the project directory below for this tutorial source code which I have established in NetBeans IDE.

NetBeans IDE directory structure of JSON encode in JSP project

3. Database And Table 

I've used a database called 'json_encode_jsp_db' but you can use whatever name that depends on you. To store product fields in the table, execute the SQL codes in your PhpMyAdmin below. 

CREATE TABLE `tbl_product` (
  `id` int(11) NOT NULL,
  `product_name` varchar(15) NOT NULL,
  `product_price` int(11) NOT NULL,
  `product_size` varchar(4) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1; 

create a product table in the database

Look below for storing two dumping data from the product table and JSP encoding those data in JSON format.

INSERT INTO `tbl_product` (`id`, `product_name`, `product_price`, `product_size`) VALUES
(1, 'blue shirt', 300, 'M'),
(2, 'sandal', 150, 'S'); 

storing two dumping data from the product table

4. Fetch Data From Table And Encoding In JSON

This is the index page with complete codes that we have to get all data from the table and displayed in JSON format inside this page. 

<%@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.*"%>
<%@page contentType="text/html" pageEncoding="UTF-8"%>

<!DOCTYPE html>
<html>
    <head>
        <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
        <title>json encode in jsp example - mysql data encode in json : onlyxcodes </title>
        <link href="bootstrap/css/bootstrap.min.css" rel="stylesheet" type="text/css">
        <script src="bootstrap/js/bootstrap.min.js" type="text/javascript"></script>
        <script src="js/jquery-1.12.4-jquery.min.js" type="text/javascript"></script>
    </head>
    <body>
            <h3 align="center"><a href="http://www.onlyxcodes.com/2019/03/json-encode-in-jsp-mysql-data-encode-in.html" target="_blank">json encode in jsp example - mysql data encode in json </a></h3></br>
            <%
                String dburl="jdbc:mysql://localhost:3306/json_encode_jsp_db"; 
                String dbusername="root"; 
                String dbpassword=""; 
                
                try
                {
                    Class.forName("com.mysql.jdbc.Driver"); 
                    Connection con=DriverManager.getConnection(dburl,dbusername,dbpassword); 
                    
                    PreparedStatement pstmt=null; //create statement
        
                    pstmt=con.prepareStatement("SELECT * FROM tbl_product"); 
                    ResultSet rs=pstmt.executeQuery(); 
                    
                    while(rs.next())
                    {
                        JSONObject obj=new JSONObject();  
                
                        obj.put("name", rs.getString("product_name"));
                        obj.put("price", rs.getInt("product_price"));   
                        obj.put("size", rs.getString("product_size"));
                       
                    %>
                        <h4 align="center"> <%=obj%> </h4>
                    <%
                        
                    }
                }
                catch(Exception e)
                {
                    e.printStackTrace();
                }
            %>
    </body>
</html>

Explanation :

The PHP provides a built-in json_encode () function to encode MySQL data in JSON format.

JSP does not provide an integrated function to encode MySQL data in JSON format that you need to jar file. This tutorial uses org.json-chargebee-1.0 jar file this file help to incorporate JSON object in MySQL records.

Name, price, and size are JSON objects which integrate into table collection of data. 

Note – Go to top of this page and see row no 5 I import external jar files package org.json.JSONObject and java.util.*, if you no import the JSON object doesn't work.

5. Output :

{"size" : "M","price" : "300","name" : "blue shirt"}
{"size" : "S","price" : "150","name" : "sandal"}

Random Tutorials :-





Download Codes

No comments:

Post a Comment