RSS

Restful Web Services Java, MySQL and JSON

08 Nov

Today we are going to build another restful web service in eclipse using gson library. When client makes a request, the application queries the database and produces response in JSON format. This JSON can be parsed with most of the programming languages.
We will see how various clients can consume this web service in the second part of the tutorial.

Download Source Code

Download Source Code
Project Architecture

Create a new dynamic web project in Eclipse and add jersy, gson library to WEB-INF/lib.

course.java
We utilize this class when we wanna bind data after querying database.

package dto;

public class Course
{
private int id;
private String name;
private String duration;
private double fee;

public Course()
{

}

public Course(int id, String name, String duration, double fee)
{
super();
this.id = id;
this.name = name;
this.duration = duration;
this.fee = fee;
}

public int getId()
{
return id;
}

public void setId(int id)
{
this.id = id;
}

public String getName()
{
return name;
}

public void setName(String name)
{
this.name = name;
}

public String getDuration()
{
return duration;
}

public void setDuration(String duration)
{
this.duration = duration;
}

public double getFee()
{
return fee;
}

public void setFee(double fee)
{
this.fee = fee;
}

@Override
public String toString()
{
return "Course [id=" + id + ", name=" + name + ", duration=" + duration
+ ", fee=" + fee + "]";
}

}

 

Database.java

package dao;

import java.sql.Connection;
import java.sql.DriverManager;

public class Database
{
public Connection getConnection() throws Exception
{
try
{
String connectionURL = "jdbc:mysql://localhost:3306/codezone4";
Connection connection = null;
Class.forName("com.mysql.jdbc.Driver").newInstance();
connection = DriverManager.getConnection(connectionURL, "root", "");
return connection;
} catch (Exception e)
{
throw e;
}

}

}

Access.java

package dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import dto.Course;

public class Access
{
public ArrayList<Course> getCourses(Connection con) throws SQLException
{
ArrayList<Course> courseList = new ArrayList<Course>();
PreparedStatement stmt = con.prepareStatement("SELECT * FROM courses");
ResultSet rs = stmt.executeQuery();
try
{
while(rs.next())
{
Course courseObj = new Course();
courseObj.setId(rs.getInt("id"));
courseObj.setName(rs.getString("name"));
courseObj.setDuration(rs.getString("duration"));
courseObj.setFee(rs.getDouble("fee"));
courseList.add(courseObj);
}
} catch (SQLException e)
{
e.printStackTrace();
}
return courseList;

}
}

AccessManager.java

package model;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.ArrayList;

import dao.Access;
import dao.Database;
import dto.Course;

public class AccessManager
{
public ArrayList<Course> getCourses() throws Exception
{
ArrayList<Course> courseList = new ArrayList<Course>();
Database db = new Database();
Connection con = db.getConnection();
Access access = new Access();
courseList = access.getCourses(con);
return courseList;
}
}

CourseService.java

package webService;

import java.util.ArrayList;

import javax.ws.rs.GET;
import javax.ws.rs.Path;
import javax.ws.rs.Produces;

import com.google.gson.Gson;

import model.AccessManager;

import dto.Course;

@Path("/courseService")
public class CourseService
{
@GET
@Path("/courses")
@Produces("application/json")
public String courses()
{
String courses = null;
ArrayList<Course> courseList = new ArrayList<Course>();
try
{
courseList = new AccessManager().getCourses();
Gson gson = new Gson();
courses = gson.toJson(courseList);
} catch (Exception e)
{
e.printStackTrace();
}
return courses;
}
}

web.xml

<?xml version="1.0" encoding="UTF-8"?>
<web-app xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://java.sun.com/xml/ns/javaee" xmlns:web="http://java.sun.com/xml/ns/javaee/web-app_2_5.xsd" xsi:schemaLocation="http://java.sun.com/xml/ns/javaee http://java.sun.com/xml/ns/javaee/web-app_2_5.xsd" id="WebApp_ID" version="2.5">
<display-name>RestProject</display-name>
<welcome-file-list>
<welcome-file>index.html</welcome-file>
<welcome-file>index.htm</welcome-file>
<welcome-file>index.jsp</welcome-file>
<welcome-file>default.html</welcome-file>
<welcome-file>default.htm</welcome-file>
<welcome-file>default.jsp</welcome-file>
</welcome-file-list>
<servlet>
<servlet-name>Course Service</servlet-name>
<servlet-class>com.sun.jersey.server.impl.container.servlet.ServletAdaptor</servlet-class>
<load-on-startup>1</load-on-startup>
</servlet>
<servlet-mapping>
<servlet-name>Course Service</servlet-name>
<url-pattern>/Rest/*</url-pattern>
</servlet-mapping>
</web-app>

Run the application in server.
visit following URL :
http://localhost:8440/RestProject/Rest/courseService/courses
The port number in the URL depends on your server settings.
You will see the JSON response.

 

Help make a developer’s life happy and strong.

Donate Button with Credit Cards

Advertisements
 
55 Comments

Posted by on November 8, 2012 in J2EE, Java

 

Tags: ,

55 responses to “Restful Web Services Java, MySQL and JSON

  1. javaaccount

    April 29, 2013 at 6:06 am

    Hi i have tried but i am getting following exception.

    com.mysql.jdbc.CommunicationsException: Communications link failure due to underlying exception:

    ** BEGIN NESTED EXCEPTION **

    java.net.ConnectException
    MESSAGE: Connection refused

    STACKTRACE:

    java.net.ConnectException: Connection refused
    at java.net.PlainSocketImpl.socketConnect(Native Method)
    at java.net.PlainSocketImpl.doConnect(PlainSocketImpl.java:351)
    at java.net.PlainSocketImpl.connectToAddress(PlainSocketImpl.java:213)
    at java.net.PlainSocketImpl.connect(PlainSocketImpl.java:200)
    at java.net.SocksSocketImpl.connect(SocksSocketImpl.java:432)
    at java.net.Socket.connect(Socket.java:529)
    at java.net.Socket.connect(Socket.java:478)
    at java.net.Socket.(Socket.java:375)
    at java.net.Socket.(Socket.java:218)
    at com.mysql.jdbc.StandardSocketFactory.connect(StandardSocketFactory.java:256)
    at com.mysql.jdbc.MysqlIO.(MysqlIO.java:271)
    at com.mysql.jdbc.Connection.createNewIO(Connection.java:2771)
    at com.mysql.jdbc.Connection.(Connection.java:1555)
    at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:285)
    at java.sql.DriverManager.getConnection(DriverManager.java:582)
    at java.sql.DriverManager.getConnection(DriverManager.java:185)
    at dao.Database.getConnection(Database.java:15)
    at model.AccessManager.getCourses(AccessManager.java:17)
    at webService.CourseService.courses(CourseService.java:27)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
    at java.lang.reflect.Method.invoke(Method.java:597)
    at com.sun.jersey.spi.container.JavaMethodInvokerFactory$1.invoke(JavaMethodInvokerFactory.java:60)
    at com.sun.jersey.server.impl.model.method.dispatch.AbstractResourceMethodDispatchProvider$TypeOutInvoker._dispatch(AbstractResourceMethodDispatchProvider.java:185)
    at com.sun.jersey.server.impl.model.method.dispatch.ResourceJavaMethodDispatcher.dispatch(ResourceJavaMethodDispatcher.java:75)
    at com.sun.jersey.server.impl.uri.rules.HttpMethodRule.accept(HttpMethodRule.java:302)
    at com.sun.jersey.server.impl.uri.rules.RightHandPathRule.accept(RightHandPathRule.java:147)
    at com.sun.jersey.server.impl.uri.rules.ResourceClassRule.accept(ResourceClassRule.java:108)
    at com.sun.jersey.server.impl.uri.rules.RightHandPathRule.accept(RightHandPathRule.java:147)
    at com.sun.jersey.server.impl.uri.rules.RootResourceClassesRule.accept(RootResourceClassesRule.java:84)
    at com.sun.jersey.server.impl.application.WebApplicationImpl._handleRequest(WebApplicationImpl.java:1511)
    at com.sun.jersey.server.impl.application.WebApplicationImpl._handleRequest(WebApplicationImpl.java:1442)
    at com.sun.jersey.server.impl.application.WebApplicationImpl.handleRequest(WebApplicationImpl.java:1391)
    at com.sun.jersey.server.impl.application.WebApplicationImpl.handleRequest(WebApplicationImpl.java:1381)
    at com.sun.jersey.spi.container.servlet.WebComponent.service(WebComponent.java:416)
    at com.sun.jersey.spi.container.servlet.ServletContainer.service(ServletContainer.java:538)
    at com.sun.jersey.spi.container.servlet.ServletContainer.service(ServletContainer.java:716)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:728)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:305)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210)

     
    • Rajitha

      April 29, 2013 at 8:41 am

      Hi, You should have a problem with your jdbc connection. Please check port number and database settings again.

       
  2. javaaccount

    May 4, 2013 at 5:59 am

    Thanks Rajitha.
    Mai know what is prereq for DB. Do i need to create any table or database as you mentioned in code codezone4.

     
    • Rajitha

      May 4, 2013 at 10:22 am

      Of course. You need to have a database and tables with some dummy data setup before running the application. In this example database is ‘codezone4’. ‘courses’ is one of its table. You should change the jdbc URL appropriately. Please check this line.

      String connectionURL = “jdbc:mysql://localhost:3306/codezone4”;

      I think this solves your problem.

       
  3. javaaccount

    May 5, 2013 at 8:49 am

    can you please provide sample sql fro create table in mysql to run this sample.

     
    • Rajitha

      May 5, 2013 at 9:54 am

      CREATE TABLE IF NOT EXISTS `courses` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `name` varchar(50) NOT NULL,
      `duration` varchar(20) NOT NULL,
      `fee` double NOT NULL,
      PRIMARY KEY (`id`)
      )

      Sample Data
      INSERT INTO `courses` (`id`, `name`, `duration`, `fee`) VALUES
      (1, ‘OCJP’, ‘4 months’, 200),
      (2, ‘web designing’, ‘6 months’, 180),
      (3, ‘CCNA’, ‘6 months’, 250);

       
  4. Lucy

    May 28, 2013 at 12:47 pm

    Hi Rajitha,

    Your tutorial works well. Thanks a lot!

    Lucy.

     
  5. lucia

    March 7, 2014 at 9:01 pm

    Hi Rajitha,
    I liked the tutorial can you give us the code for Post to instert data into the database.
    Thanks
    Regards.

     
    • Jayd

      June 20, 2014 at 2:27 pm

      This would really help many people. Most examples explain how to GET data but not POST
      Regards

       
  6. Martin

    April 15, 2014 at 12:30 pm

    Hi, i put this line :
    String connectionURL = “jdbc:mysql://http://217.16.3.**/bowling”;

    But the applicaiton crash, have you an idea of the problem ? (Sorry for my poor English … )

    The console show :

    Exception in thread “main” java.lang.IllegalStateException: This is not a JSON Array.
    at com.google.gson.JsonElement.getAsJsonArray(JsonElement.java:100)
    at com.Main.main(Main.java:26)

    With a local database it’s OK, but when i try to connect to my server the application crash …

    Thank you.

     
    • Rajitha

      April 15, 2014 at 1:04 pm

      Hi,
      Are you sure your database connection works fine?

       
  7. koussay

    May 9, 2014 at 7:47 pm

    thx Rajitha but i have this erroe can you help me
    Grave: La servlet /ServiceWeb a généré une exception “load()”
    java.lang.ClassNotFoundException: com.sun.jersey.server.impl.container.servlet.ServletAdaptor
    at org.apache.catalina.loader.WebappClassLoader.loadClass(WebappClassLoader.java:1702)
    at org.apache.catalina.loader.WebappClassLoader.loadClass(WebappClassLoader.java:1547)
    at org.apache.catalina.core.DefaultInstanceManager.loadClass(DefaultInstanceManager.java:532)
    at org.apache.catalina.core.DefaultInstanceManager.loadClassMaybePrivileged(DefaultInstanceManager.java:514)
    at org.apache.catalina.core.DefaultInstanceManager.newInstance(DefaultInstanceManager.java:142)
    at org.apache.catalina.core.StandardWrapper.loadServlet(StandardWrapper.java:1144)
    at org.apache.catalina.core.StandardWrapper.load(StandardWrapper.java:1088)
    at org.apache.catalina.core.StandardContext.loadOnStartup(StandardContext.java:5198)
    at org.apache.catalina.core.StandardContext.startInternal(StandardContext.java:5481)
    at org.apache.catalina.util.LifecycleBase.start(LifecycleBase.java:150)
    at org.apache.catalina.core.ContainerBase$StartChild.call(ContainerBase.java:1559)
    at org.apache.catalina.core.ContainerBase$StartChild.call(ContainerBase.java:1549)
    at java.util.concurrent.FutureTask.run(Unknown Source)
    at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
    at java.lang.Thread.run(Unknown Source)

     
  8. Shubham Agarwal

    October 6, 2014 at 11:57 am

    hey can you tell me to how to store JSON array data into database through rest java api in ajax and javascript

     
  9. Sanjay Kumar

    November 12, 2014 at 5:27 pm

    hi i need example for post method using json and mysql and rest api

     
  10. Sanjay Kumar

    November 12, 2014 at 5:28 pm

    hi, i need an example code for posting method using json, mysql and rest api

     
  11. Jam Carlos Vendramini Filho

    January 13, 2015 at 11:27 pm

    How i return a file with .json on the end, and why the Name of the table doesn’t appear

     
  12. vinod

    January 24, 2015 at 5:33 pm

    java.lang.ClassNotFoundException: org.postgresql.Driver
    at org.apache.catalina.loader.WebappClassLoader.loadClass(WebappClassLoader.java:1714)
    at org.apache.catalina.loader.WebappClassLoader.loadClass(WebappClassLoader.java:1559)
    at java.lang.Class.forName0(Native Method)
    at java.lang.Class.forName(Unknown Source)
    at dao.Database.getConnection(Database.java:12)
    at model.AccessManager.getCourses(AccessManager.java:14)
    at webService.CourseService.courses(CourseService.java:32)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
    at java.lang.reflect.Method.invoke(Unknown Source)
    at com.sun.jersey.spi.container.JavaMethodInvokerFactory$1.invoke(JavaMethodInvokerFactory.java:60)
    at com.sun.jersey.server.impl.model.method.dispatch.AbstractResourceMethodDispatchProvider$TypeOutInvoker._dispatch(AbstractResourceMethodDispatchProvider.java:185)
    at com.sun.jersey.server.impl.model.method.dispatch.ResourceJavaMethodDispatcher.dispatch(ResourceJavaMethodDispatcher.java:75)

     
  13. Jhoc

    May 31, 2015 at 3:42 pm

    Hi rajtha! Please help me clarify things. Is this http://localhost:8440/RestProject/Rest/courseService/courses link will be used to put on android devices in order to access the database?

     
    • Rajitha

      May 31, 2015 at 4:01 pm

      If I understand your question correctly, yes. you can use call the web service with this url using a Android client in your local environment.

       
      • Jhoc

        June 9, 2015 at 1:37 pm

        Rajitha, thanks for your reply! 🙂 I was able to run it on my android application. But please, I really need your help. How will I be able to select records based on user input? Should I use queryparam? Thank you!

         
  14. vimal

    September 2, 2015 at 5:29 pm

    how to write post operation for a database

     
  15. Anand

    October 23, 2015 at 11:59 am

    Thanks a ton!

     
  16. Nurulhuda

    November 11, 2015 at 8:28 am

    Hi Rajitha,

    i got error 404 The requested resource is not available.

    when i run the web service

    i follow your code exactly

    thanks

     
    • MASG

      November 22, 2016 at 2:06 am

      Hi, have you found any solution?

       
  17. Panagiwtis Pap

    January 27, 2016 at 11:00 pm

    Rajitha where is the Json file?

     
  18. Attila

    February 14, 2016 at 1:41 am

    Hi Rajitha,

    Your tutorial works well.
    This presentation expand the post and put part.
    I am a novice Java developers
    Thank you.

    Attila.

     
  19. Attila

    February 14, 2016 at 1:56 am

    Hi Rajitha,
    how to write post and put and delete operation for a database.
    Sorry for my poor Englis….
    I am a novice Java developer.

    Thank you.

     
  20. gopichand

    March 3, 2016 at 2:13 pm

    Hi Rajitha,
    First of all thanks for this post it is helping me.
    but i am getting error near
    PreparedStatement stmt=con.prepareStatement(“SELECT * FROM employeeinfo”);
    employeeList=access.getEmployeeInfo(conn);
    employeeList=new AccessManager().getEmployeeInfo();
    i am new to web services can u help me out in this.

    Thank in Advance.

     
  21. Dinesh Shinkar

    April 19, 2016 at 6:25 pm

    Hi Rajitha,
    Thanks a lot for your perfect and well managed code.helped me a lot.

     
  22. Rahul

    May 24, 2016 at 6:39 pm

    could you please tell m the which version of jars and what type of JARS should i use..to build the path…i m waiting of your reply

     
  23. raj

    August 23, 2016 at 4:19 pm

    not able to download ur project

     
  24. raj

    August 23, 2016 at 4:21 pm

    not able to download the project

     
  25. jaspreet

    August 30, 2016 at 12:27 pm

    Add link to download source code.

     
  26. Sreekandan K

    November 4, 2016 at 2:54 pm

    Please anybody give the code to insert data into the database

     
  27. Mohammad R Hasan

    December 5, 2016 at 12:38 pm

    Nice tutorial..can you implement post and put method?

     
  28. Anil Yeske

    December 15, 2016 at 3:09 pm

    java.lang.ClassNotFoundException: com.sun.jersey.server.impl.container.servlet.ServletAdaptor

    what can i do

     
  29. Anil Yeske

    December 15, 2016 at 6:35 pm

    Thanks Rajith i had done my job

     
  30. Harini

    December 23, 2016 at 11:39 am

    Hi, In this project where we are using Rest Api

     
  31. Literate Aspects

    January 1, 2017 at 1:20 am

    Hi Rajitha, wonderful tutorial. I ran into an error at the very last part dealing with

    import javax.ws.rs.GET;
    import javax.ws.rs.Path;
    import javax.ws.rs.Produces;

    How can I adapt this for a Dynamic Web Project?

     
  32. srinivas

    February 24, 2017 at 4:38 pm

    java.lang.UnsupportedClassVersionError: org/glassfish/jersey/servlet/init/JerseyServletContainerInitializer : Unsupported major.minor version 52.0

    i got above error

     
    • srinivas

      February 27, 2017 at 7:13 pm

      could you please help me running the web service application

       
  33. Avudai

    March 6, 2017 at 5:14 pm

    Thank you…

     
  34. fayaz

    April 21, 2017 at 12:56 pm

    Hi Rajitha,

    can u share the jersey jar files or there version.
    having same issues as srinivas.

    Thanks

     
  35. goppalmythrigopal

    May 12, 2017 at 11:13 am

    hi rajitha when i war runnning on serrver this project i got a 404 error

     
  36. senthilkumar

    July 4, 2017 at 5:17 pm

    how to add gson libraries in java project ? and how to create the folder test as it is in project structure ? and what that folder contains ?

     
  37. Gayathri Sridharan

    August 16, 2017 at 12:53 pm

    Hi Rajitha,
    will you please help me as i am still getting the below error.

    HTTP Status [404] – [Not Found]

    Type Status Report

    Description The origin server did not find a current representation for the target resource or is not willing to disclose that one exists.

    Apache Tomcat/8.5.15

    Note: we have updated the web.xml without the below line:
    1
    if i use the above line getting below exception

    SEVERE: Servlet [Course Service] in web application [/RESTProject] threw load() exception
    java.lang.ClassNotFoundException: com.sun.jersey.server.impl.container.servlet.ServletAdaptor
    at org.apache.catalina.loader.WebappClassLoaderBase.loadClass(WebappClassLoaderBase.java:1285)
    at org.apache.catalina.loader.WebappClassLoaderBase.loadClass(WebappClassLoaderBase.java:1119)
    at org.apache.catalina.core.DefaultInstanceManager.loadClass(DefaultInstanceManager.java:512)
    at org.apache.catalina.core.DefaultInstanceManager.loadClassMaybePrivileged(DefaultInstanceManager.java:493)
    at org.apache.catalina.core.DefaultInstanceManager.newInstance(DefaultInstanceManager.java:119)
    at org.apache.catalina.core.StandardWrapper.loadServlet(StandardWrapper.java:1050)
    at org.apache.catalina.core.StandardWrapper.load(StandardWrapper.java:989)
    at org.apache.catalina.core.StandardContext.loadOnStartup(StandardContext.java:4931)
    at org.apache.catalina.core.StandardContext.startInternal(StandardContext.java:5241)
    at org.apache.catalina.util.LifecycleBase.start(LifecycleBase.java:150)
    at org.apache.catalina.core.ContainerBase$StartChild.call(ContainerBase.java:1419)
    at org.apache.catalina.core.ContainerBase$StartChild.call(ContainerBase.java:1409)
    at java.util.concurrent.FutureTask.run(Unknown Source)
    at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
    at java.lang.Thread.run(Unknown Source)

    kindly assist as i am new to development and also to java. Thanks in advance

     
  38. Ahson Junani

    October 5, 2017 at 3:40 am

    Hi Rajitha, i have to make a similar java application. i am using xampp to run mysql server. i want to connect my database to java application so that i can search for a course in my database. can you tell me what changes shall i make in the code that you have written?

     
  39. Ahson Junani

    October 15, 2017 at 3:06 am

    from where i can download gson library?

     
  40. Ahson Junani

    October 15, 2017 at 4:06 am

    The origin server did not find a current representation for the target resource or is not willing to disclose that one exists.

     
  41. Rajeev Prasad

    November 22, 2017 at 5:23 pm

    Thanks a lot.

     
  42. enterprise216pooja

    December 4, 2017 at 7:46 pm

    hello
    is it possible to transfer this data to Android studio using JSONObject or JSONArray.
    kindly guide me .
    i am trying to transfer this data to the android studio so that client node will be android app but i am not able to locate this variables on android application .
    because it is in String format, so kindly guide how to change it in JSONObject or JSONArray, and similarly fatch this inside android application

     

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: