How to retrieve data from database using webservices (JAX - RS) in eclipse using Java

java mysql eclipse jersey jax-rs

33,651

Solution 1

Finally I have done it, Now I am able to perform both insertion and retriving of data from database using jersey(JAX - RS) webservices and able to display it in a html page.

Account.java:

package com.fetch;

import java.io.Serializable;
import javax.persistence.Column;
import javax.persistence.Id;

public class Account implements Serializable 
{
    @Id
    private long id;

    @Column(name = "NAME")
    private String name;

    public Account(int id, String name)
    {
        this.id = id;
        this.name = name;
    }
    public long getId() {
        return id;
    }
    public void setId(long id) {
        this.id = id;
    }
    public String getName() {
        return name;
    }
    public void setName(String name) {
        this.name = name;
    }
}

MyAccount.java:

package com.fetch;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import javax.jws.WebMethod;
import javax.jws.WebService;
import javax.persistence.Entity;
import javax.ws.rs.FormParam;
import javax.ws.rs.GET;
import javax.ws.rs.POST;
import javax.ws.rs.Path;
import javax.ws.rs.Produces;

@WebService()
@Entity
@Path("/user")
public class MyAccount
{
    @POST
    @Path("/fetch")
    @WebMethod(operationName = "insert")
    public String insert(@FormParam("name") String name) 
    {
        try 
        {
            Class.forName("com.mysql.jdbc.Driver");
            Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/studentinfo", "root", "root");
            String query = "insert into CUSTOMER"+"(NAME) VALUES"+"(?)";

            PreparedStatement st = con.prepareStatement(query);
            st.setString(1,name);
            st.executeUpdate();     
        } 
        catch (Exception e) 
        {
            System.out.println(e.getMessage());
        }
        return"Record inserted successfully";
    }

    @GET
    @Path("/retrive")
    @Produces("text/html")
    @WebMethod(operationName = "retrive")
    public String retrive() 
    {
        ResultSet rs = null;
        String details = ""; 
        try 
        {
            Class.forName("com.mysql.jdbc.Driver");
            Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/studentinfo", "root", "root");

            String query = "select ID,NAME from CUSTOMER";

            PreparedStatement st = con.prepareStatement(query);
            rs = st.executeQuery();

            details = "<html><body>"; 
            details = details + "<table border=1>";
            details = details + "<tr><td><Strong>Id </Strong></td>" +
                                    "<td><Strong>Name </Strong></td>" + "</tr>";
            while (rs.next()) 
            {
                details = details + "<tr><td>" + rs.getInt("ID") + "</td>" +
                                        "<td>" + rs.getString("NAME") + "</td></tr>";
            }
            details += "</table></body></html>"; 
        } 
        catch (Exception e) 
        {
            System.out.println(e.getMessage());
        }   
        return details;
    }
}

insert.html:

 <!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>Insert</title>
</head>
<body>
    <form action="rest/user/fetch" method="POST">
        <p>
            Name : <input id="name" name="name" />
        </p>
        <input type="submit" value="Add" />
    </form>
    <form action="rest/user/retrive" method="GET">
        <input type="submit" value="Retrive" />
    </form>
</body>
</html>

my web.xml is:

 <?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_3_0.xsd"
    id="WebApp_ID" version="3.0">
    <display-name>FetchAndInsert</display-name>
    <welcome-file-list>
        <welcome-file>index.jsp</welcome-file>
    </welcome-file-list>
    <servlet>
        <servlet-name>REST Servlet</servlet-name>
        <servlet-class>com.sun.jersey.spi.container.servlet.ServletContainer</servlet-class>
        <init-param>
            <param-name>com.sun.jersey.config.property.packages</param-name>
            <param-value>com.fetch</param-value>
        </init-param>
        <load-on-startup>1</load-on-startup>
    </servlet>
    <servlet-mapping>
        <servlet-name>REST Servlet</servlet-name>
        <url-pattern>/rest/*</url-pattern>
    </servlet-mapping>
</web-app>

Solution 2

Jersey doesn't provide any sort of HTML templating. You need to forward your response to a jsp page or use Ajax to fetch the data and lay it out on the page.

33,651

Author by

spt

Updated on November 29, 2020

Comments

spt over 3 years

I have done inserting a record into database but I don't know how to retrieve it. My code is: Account.java:

package com.fetch;

import java.io.Serializable;
import javax.persistence.Column;
import javax.persistence.Id;

public class Account implements Serializable 
{   
  @Id
  private long id;

  @Column(name="NAME")  
  private String name;

  public Account()  
  {

  }

  public Account(int id, String name)   
  {     
     this.id = id;      
     this.name = name;  
  }

  public long getId() 
  {     
     return id; 
  }

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

  public String getName() 
  {     
     return name;   
  }

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

MyAccount.java:

package com.fetch;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.Collection;
import javax.jws.WebMethod;
import javax.jws.WebService;
import javax.persistence.Entity;
import javax.persistence.NamedQuery;
import javax.persistence.Table;
import javax.ws.rs.FormParam;
import javax.ws.rs.GET;
import javax.ws.rs.POST;
import javax.ws.rs.Path;

@WebService()
@Entity
@Table(name = "CUSTOMER")
@Path("/user")
@NamedQuery(name="loginquery", query="select ID,NAME from CUSTOMER")
public class MyAccount 
{   
   private Account ac;  
   public MyAccount()   
   {    
     // TODO Auto-generated constructor stub      
     ac = new Account();    
   }    

   @POST    
   @Path("/fetch")  
   @WebMethod(operationName = "insert") 
   public String insert(@FormParam("name") String name) 
   {     
      try   
      {     
         Class.forName("com.mysql.jdbc.Driver");        
         Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/studentinfo", "root", "root");

         String query = "insert into CUSTOMER"+"(NAME) VALUES"+"(?)";
     PreparedStatement st = con.prepareStatement(query);    
         st.setString(1,name);  
     st.executeUpdate();
      }     
      catch (Exception e)   
      {     
         System.out.println(e.getMessage());    
      } 
      return"Record inserted successfully";
   }    
  public Account getAc() 
  {     
     return ac; 
  } 
  public void setAc(Account ac) 
  {     
     this.ac = ac;  
  }
}

insert.html:

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>Insert</title>
</head>
<body>
    <form action="rest/user/fetch" method="POST">
        <p>
            Name : <input id="name" name="name" />
        </p>
        <input type="submit" value="Add" />
            <input type="submit" value="Retrive" />
    </form>
</body>
</html>

web.xml:

<?xml version="1.0" encoding="UTF-8"?>
<web-app xmlns="http://java.sun.com/xml/ns/javaee" xmlns:web="http://java.sun.com/xml/ns/javaee/web-app_2_5.xsd" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://java.sun.com/xml/ns/javaee http://java.sun.com/xml/ns/javaee/web-app_3_0.xsd" id="WebApp_ID" version="3.0">
   <display-name>FetchAndInsert</display-name>
   <servlet>
      <servlet-name>REST Servlet</servlet-name>
      <servlet-class>com.sun.jersey.spi.container.servlet.ServletContainer</servlet-class>
      <init-param>
         <param-name>com.sun.jersey.config.property.packages</param-name>
         <param-value>com.fetch</param-value>
      </init-param>
      <load-on-startup>1</load-on-startup>
   </servlet>
   <servlet-mapping>
      <servlet-name>REST Servlet</servlet-name>
      <url-pattern>/rest/*</url-pattern>
   </servlet-mapping>
</web-app>

How to retrieve the data from the database when the user clicks on the retrieve button, and display all the records in another HTML form? Please give suggestions on how to do it.

In my application, when the user clicks the retrieve button it is performing the insert operation. But I want is, when the user clicks it should go to the another page and display the data in table from database.

Can anybody tell the mistake what I have done, and give suggestions for finding solution for it and how to display the dynamic table in other page when the user clicks the retrieve button?