JAVA CODE : RESTAPI TO ACCESS POSTGRESQL USING ECLIPSE

JAVA CODE : RSETAPI TO ACCESS POSTGRESQL USING ECLIPSE

package 1:

package com.user.inventory;

import java.sql.*;

import javax.ws.rs.GET;

import javax.ws.rs.Path;

//import javax.ws.rs.PathParam;

import javax.ws.rs.Produces;

//import javax.ws.rs.core.MediaType;

//import javax.ws.rs.core.Response;

//import javax.ws.rs.QueryParam;

import org.codehaus.jettison.json.JSONArray;

import com.user.util.ToJSON;

@Path("/v/inventory")

public class V_inventory {

@GET

@Produces("application/json")

public String returnAllcontacts() throws Exception{

PreparedStatement query = null;

Connection conn = null;

String returnString = null;

try{

Class.forName("org.postgresql.Driver");

   conn = DriverManager.getConnection("jdbc:postgresql://localhost:5432/db","Username","password");

   query = conn.prepareStatement("select * from tablename");

ResultSet rs = query.executeQuery();

ToJSON converter = new ToJSON();

JSONArray json = new JSONArray();

json = converter.toJSONArray(rs);

query.close();

returnString = json.toString();

}

catch (Exception e){

e.printStackTrace();

}

finally{

if (conn != null) conn.close();

}

return returnString;

//return "<p>java web service</p>";

}

}

########################################################################

package 2:

package com.user.util;

import org.codehaus.jettison.json.JSONArray;

import org.codehaus.jettison.json.JSONObject;

import java.sql.ResultSet;

public class ToJSON {

public JSONArray toJSONArray(ResultSet rs) throws Exception {

JSONArray json = new JSONArray();

try{

java.sql.ResultSetMetaData rsmd = rs.getMetaData();

while( rs.next() ) {

//figure out how many columns there are

               int numColumns = rsmd.getColumnCount();

               //each row in the ResultSet will be converted to a JSON Object

               JSONObject obj = new JSONObject();

               //loop through all the columns and place them into the JSON Object

               for (int i=1; i<numColumns+1; i++) {

                   String column_name = rsmd.getColumnName(i);

                   if(rsmd.getColumnType(i)==java.sql.Types.ARRAY){

                   obj.put(column_name, rs.getArray(column_name));

                   /*Debug*/ System.out.println("ToJson: ARRAY");

                   }

                   else if(rsmd.getColumnType(i)==java.sql.Types.INTEGER){

                   obj.put(column_name, rs.getInt(column_name));

                   /*Debug*/ System.out.println("ToJson: INTEGER");

                   }

                   else if(rsmd.getColumnType(i)==java.sql.Types.VARCHAR){                                                     

                   obj.put(column_name, rs.getString(column_name));

                   /*Debug*/ System.out.println("ToJson: VARCHAR");

                   }

                   else if(rsmd.getColumnType(i)==java.sql.Types.BOOLEAN){

                   obj.put(column_name, rs.getBoolean(column_name));

                   /*Debug*/ System.out.println("ToJson: BOOLEAN");

                   }

                   else if(rsmd.getColumnType(i)==java.sql.Types.DATE){

                   obj.put(column_name, rs.getDate(column_name));

                   /*Debug*/ System.out.println("ToJson: DATE");

                   }

                   else if(rsmd.getColumnType(i)==java.sql.Types.TIMESTAMP){

                   obj.put(column_name, rs.getTimestamp(column_name));

                   /*Debug*/ System.out.println("ToJson: TIMESTAMP");

                   }

                   else if(rsmd.getColumnType(i)==java.sql.Types.NUMERIC){

                   obj.put(column_name, rs.getBigDecimal(column_name));

                   // /*Debug*/ System.out.println("ToJson: NUMERIC");

                    }

                   else {

                   obj.put(column_name, rs.getObject(column_name));

                   /*Debug*/ System.out.println("ToJson: Object "+column_name);

                   }

}

json.put(obj);

}

}catch(Exception e) {

e.printStackTrace();

}

return json;

}

}

#############################################################################

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" 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>User_Management</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>Jersey REST Service</servlet-name>
    <servlet-class>com.sun.jersey.spi.container.servlet.ServletContainer</servlet-class>
    <init-param>
        <param-name>com.sun.jersey.config.provider.packages</param-name>
        <param-value>User_Management</param-value>
    </init-param>
    <load-on-startup>1</load-on-startup>
  </servlet>
  <servlet-mapping>
    <servlet-name>Jersey REST Service</servlet-name>
    <url-pattern>/rest/*</url-pattern>
  </servlet-mapping>
</web-app>

Add the above Servlet in your in default web.xml


#################################################

JAR files:

postgresql JAR:  PostgreSQL JDBC 4.2 Driver, 42.1.1
LINK : https://jdbc.postgresql.org/download.html