Java MySQL Data Convert In JSON Format Using JSP - onlyxcodes

Sunday, 2 December 2018

Java MySQL Data Convert In JSON Format Using JSP

Hi friends, in this tutorial we will see codes of Java MySQL Data Convert in JSON Format Using JSP. The most important significance of this tutorial is that I don't use any jar files that I configure explicitly and add JSP codes according to the JSON format data display.

Before we start the tutorial let's discuss JSON a little bit.

Java MySQL Data Convert In JSON Format Using JSP - onlyxcodes

Table Content

1. What is meant by JSON?

2. Pros of JSON

3. Cons of JSON

4. Where Use of JSON

    4.1 JSON simple syntax

    4.2 JSON multiple key and value syntax

    4.3 JSON Array syntax

5. Database and Table Creating

6. index.jsp

7. Output


What is meant by JSON? 

JSON (Javascript Object Notation) is a minimally readable data structuring format. This is used to preprocess and transmit data over a network. An alternative to XML (Extensible Markup Language) is used. JSON usually transmits data between a server request and a web application.

The JSON format which Douglas Crockford originally defined in the early 2000s. JSON syntax refers to JavaScript. The lightweight format and it supports modern programming languages.

Pros of JSON

It is a lightweight format for data-interchange. It is easy for human beings to quickly read and write.

For AJAX Request JSON is faster and better.

JSON is much smaller when zipped so it takes less time to zip everything out.

For modern programming languages, this can be incorporated.

Cons of JSON

It's small in terms of the datatype supported.

It is not fully secure.

JSON is that use with untrusted services or untrusted browsers can be very risky.

Where Use of JSON

It is mainly used for the transmission of data between a server and web applications.

JSON data format is commonly used in the serialization and transmission process for the structured data over the network.

Web services and APIs use JSON format to communicate information to the public.

JSON data format is widely used for browser attachments and websites in the programming language written by JavaScript language.

JSON simple syntax : -


Here is a basic syntax, within { } bracket, a name/value pair contains a field name (in double-quotes).

{ key : value } 

example of the simple syntax : -


{ "name" : "hamid" } 

JSON multiple key and value syntax : -


See below our JSON syntax with various keys and values. In this syntax, each key and value are separated by a comma symbol.

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

Example of multiple key and value : -


{ "name" : "hamid", "country" : "india", "city" : "surat", "skill" : "programmer" } 

JSON Array syntax : -


It is shown below syntax of the JSON array, starting with [ bracket and ending with ] bracket, the value of the array indicated by the ordered list. Each key-value is divided by the comma symbol.

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

Example : -


Below is the person array object which contains 4 values for each value separated by a comma symbol.

{
 "person":[
   "hamid",
   "india",
   "city",
   "programmer"
  ]
}

Database and Table Creating

The sample database and table called 'user' with dumping data contains username and owner, so simply copy this SQL code into your phpMyAdmin.

--
-- Database: `json_db`
--

-- --------------------------------------------------------

--
-- Table structure for table `user`
--

CREATE TABLE `user` (
  `user_id` int(11) NOT NULL,
  `username` varchar(15) NOT NULL,
  `owner` varchar(12) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `user`
--

INSERT INTO `user` (`user_id`, `username`, `owner`) VALUES
(1, 'Bill Gates', 'Microsoft'),
(2, 'Steve Jobs', 'Apple'),
(3, 'Markzuckerberg', 'Facebook');

index.jsp

Convert MySQL database records to JSON array format on this page.

<%@page import="java.sql.*" %>
<%
    String dburl="jdbc:mysql://localhost:3306/json_db"; //database url string, "json_db" is databasename
    String dbusername="root"; //database username
    String dbpassword=""; //database password
            
 try
 {
        Class.forName("com.mysql.jdbc.Driver"); //load driver
        Connection con=DriverManager.getConnection(dburl,dbusername,dbpassword); //create connection
                
        PreparedStatement pstmt=null; //create statement
                
        pstmt=con.prepareStatement("select * from user"); //sql select query
        ResultSet rs=pstmt.executeQuery(); //execute query and set in ResultSet object "rs"
                
        out.print("[");
        rs.next();
        while(true)               //fetch record JSON format type
        {
            %>
               "user_id" : "<%=rs.getInt("user_id")%>" , "username" : "<%=rs.getString("username")%>" , "owner" : "<%=rs.getString("owner")%>"              
            <%
            if(rs.next())
            {
                %>
                    ,
                <%
            }
            else break;
        }
        out.print("]");
                
    }    
    catch(Exception e)
    {
       e.printStackTrace();
    }
%>
 

Explanation :


First, we set up the configuration for MySQL database connection and fire SQL select query for selecting all the data.

The object (rs) in the ResultSet has a pointer pointing to the current row. This pointer is initially placed before the first row.

The next() method moves the ResultSet object (rs) pointer continuously and retrieves all data from the database table.

JSP code customizes and embedded data within the while() condition according to JSON array syntax format display.

Output :

[ "user_id" : "1" , "username" : "Bill Gates" , "owner" : "Microsoft" , "user_id" : "2" , "username" : "Steve Jobs" , "owner" : "Apple" , "user_id" : "3" , "username" : "Markzuckerberg" , "owner" : "Facebook" ]




Download Codes

No comments:

Post a Comment