Learn Programming Step By Step

The part 2 will show you how to setup spring oracle database connection by using Maven pom.xml

First create table in Oracle database, and insert 2 records.

CREATE TABLE TBL_STUDENT  (
    "ID"        NUMBER(38,0),
    "FIRSTNAME" VARCHAR2(50 BYTE),
    "LASTNAME"  VARCHAR2(50 BYTE)
  )

insert into  TBL_STUDENT (ID, Firstname, Lastname) values (1, 'Adam', 'Smith');
insert into  TBL_STUDENT (ID, Firstname, Lastname) values (2, 'Scott', 'Miller');
commit;

You need to manually install the Oracle jdbc jar in Maven local repository first (Maven central does not have this.)

For Oracle 11g, download ojdbc14.jar, and for Oracle 10g, download classes12.jar.

Install jar in Maven local repository:

$ mvn install:install-file -Dpackaging=jar -DgroupId=com.oracle -DartifactId=ojdbc14 -Dversion=10.2.0.4.0 -Dfile="c:\download\ojdbc14.jar"

Or manually put the downloaded file into the repository: .m2\repository\com\oracle\ojdbc14\10.2.0.4.0\, and rename it to ojdbc14-10.2.0.4.0.jar

For Oracle 10g

$ mvn install:install-file -Dpackaging=jar -DgroupId=com.oracle -DartifactId=classes12 -Dversion=10.2.0.2.0 -Dfile="c:\download\classes12.jar"

Or manually put the downloaded file into the repository: .m2\repository\com\oracle\class12\10.2.0.2.0\ and rename it to classes12-10.2.0.2.0.jar

Add dependency in pom.xml

<groupId>org.springframework</groupId>

<artifactId>spring-jdbc</artifactId>

<version>4.0.5.RELEASE</version>

</dependency>

For Oracle 11g

<groupId>com.oracle</groupId>

<artifactId>ojdbc14</artifactId>

</dependency>

For Oracle 10g

<groupId>com.oracle</groupId>

<artifactId>classes12</artifactId>

</dependency>

Next, add dependency for JSON and Hibernate

<groupId>org.codehaus.jackson</groupId>

<artifactId>jackson-mapper-asl</artifactId>

</dependency>

<groupId>org.hibernate</groupId>

<artifactId>hibernate-entitymanager</artifactId>

<version>4.3.5.Final</version>

</dependency>

Update Maven dependencies, right click MyMavenWS project à Maven à Update Project

Next, add spring-servlet.xml under WEB-INF folder

spring-servlet.xml

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
 xmlns:context="http://www.springframework.org/schema/context"
 xmlns:util="http://www.springframework.org/schema/util" 
 xmlns:mvc="http://www.springframework.org/schema/mvc"
 xmlns:tx="http://www.springframework.org/schema/tx"
 xsi:schemaLocation="http://www.springframework.org/schema/mvc 
 http://www.springframework.org/schema/mvc/spring-mvc-4.0.xsd
  http://www.springframework.org/schema/beans 
  http://www.springframework.org/schema/beans/spring-beans.xsd
  http://www.springframework.org/schema/util 
  http://www.springframework.org/schema/util/spring-util-4.0.xsd
  http://www.springframework.org/schema/context 
  http://www.springframework.org/schema/context/spring-context-4.0.xsd
  http://www.springframework.org/schema/tx 
  http://www.springframework.org/schema/tx/spring-tx-4.0.xsd
  ">

 <mvc:annotation-driven/>
 <context:component-scan base-package="spring.code.com.controller" />
 
  <bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
      <property name="driverClassName" value="oracle.jdbc.pool.OracleDataSource"/>
      <property name="url" value="jdbc:oracle:thin:@//Localhost:1521/XE"/>
      <property name="username" value="oracle_usr"/>
      <property name="password" value="oracle_usr"/>
   </bean>


 <bean id="HibernateSessionFactory" class="org.springframework.orm.hibernate4.LocalSessionFactoryBean">
  <property name="dataSource" ref="dataSource" />
  <property name="annotatedClasses">
   <list>
    <value>spring.code.com.model.Student</value>
   </list>
  </property>
  <property name="hibernateProperties">
   <props>
    <prop key="hibernate.dialect">org.hibernate.dialect.Oracle10gDialect</prop>
    <prop key="hibernate.show_sql">true</prop>
   </props>
  </property>
 </bean>

 <tx:annotation-driven transaction-manager="transactionManager"/>
 <bean id="transactionManager"
  class="org.springframework.orm.hibernate4.HibernateTransactionManager">
  <property name="sessionFactory" ref="HibernateSessionFactory" />
 </bean>

 <bean id="persistenceExceptionTranslationPostProcessor"
  class="org.springframework.dao.annotation.PersistenceExceptionTranslationPostProcessor" />

 <bean id="dataDao" class="spring.code.com.dao.DataDaoImpl"></bean>
 
</beans>

Next,modify web.xml

web.xml

<?xml version="1.0" encoding="UTF-8"?>
<web-app version="2.4" xmlns="http://java.sun.com/xml/ns/j2ee"
 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
 xsi:schemaLocation="http://java.sun.com/xml/ns/j2ee http://java.sun.com/xml/ns/j2ee/web-app_2_4.xsd">
 <display-name>MyMavenWS</display-name>
 
 <servlet>
  <servlet-name>spring</servlet-name>
  <servlet-class>org.springframework.web.servlet.DispatcherServlet</servlet-class>
  
  <load-on-startup>1</load-on-startup>
 </servlet>

 <servlet-mapping>
  <servlet-name>spring</servlet-name>
  <url-pattern>/</url-pattern>
 </servlet-mapping>

    <context-param>
  <param-name>contextConfigLocation</param-name>
  <param-value>/WEB-INF/spring-servlet.xml</param-value>
 </context-param>

<!-- Creates the Spring Container shared by all Servlets and Filters -->
 <listener>
  <listener-class>org.springframework.web.context.ContextLoaderListener</listener-class>
 </listener>
 
 <session-config>
  <session-timeout>30</session-timeout>
 </session-config>
 
</web-app>

Next, create folder model under src\main\java\spring\code\com , and add Student.java

Student.java

package spring.code.com.model;
package spring.code.com.model;

import java.io.Serializable;

import javax.persistence.*;

import org.codehaus.jackson.annotate.JsonIgnoreProperties;

import com.fasterxml.jackson.annotation.JsonAutoDetect;

@Entity
@Table(name = "tbl_student")

 public class Student{
 
 @Id
 //@GeneratedValue
 @Column(name = "id")
 private long id;

 @Column(name = "firstname")
 private String firstName;

 @Column(name = "lastname")
 private String lastName;

 
 public long getId() {
  return id;
 }

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

 public String getFirstName() {
  return firstName;
 }

 public void setFirstName(String firstName) {
  this.firstName = firstName;
 }

 public String getLastName() {
  return lastName;
 }

 public void setLastName(String lastName) {
  this.lastName = lastName;
 }
 
}

Next, create dao folder , and add DataDao.java, DataDaoImpl.java

DataDao.java

package spring.code.com.dao;

import java.util.List;

import spring.code.com.model.Student;

public interface DataDao {

 public void addStudent(Student student) throws Exception;
 
 public Student getStudentById(long id) throws Exception;
 
 public List<student> getStudentList() throws Exception;
 
 public void deleteStudent(long id) throws Exception;
 
}

DataDaoImpl.java
package spring.code.com.dao; import java.util.List; import org.hibernate.*; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.transaction.annotation.Transactional; import spring.code.com.model.Student; @Transactional public class DataDaoImpl implements DataDao { @Autowired SessionFactory sessionFactory; Session session = null; @Override public void addStudent(Student student) throws Exception { session = sessionFactory.getCurrentSession(); session.save(student); } @Override public Student getStudentById(long id) throws Exception { session = sessionFactory.getCurrentSession(); Student student = (Student) session.load(Student.class,new Long(id)); return student; } @SuppressWarnings("unchecked") @Override public List<student> getStudentList() throws Exception { session = sessionFactory.getCurrentSession(); List<student> studentList = session.createCriteria(Student.class).list(); return studentList; } @Override public void deleteStudent(long id) throws Exception { session = sessionFactory.getCurrentSession(); Student student = (Student)session.load(Student.class, id); session.delete(student); } }

Next , add RestController.java under controller folder

RestController.java

package spring.code.com.controller;

import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.http.MediaType;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.ResponseBody;

import  spring.code.com.model.Student;
import  spring.code.com.dao.DataDao;

@Controller
@RequestMapping("/student")
public class RestController {

 @Autowired
 DataDao dataDao;

 @RequestMapping(value = "/create", method = RequestMethod.POST, consumes = MediaType.APPLICATION_JSON_VALUE)
 public @ResponseBody
 String addStudent(@RequestBody Student student) {
  try {
   dataDao.addStudent(student);
   return "Student added Successfully !";
  } catch (Exception e) {
   return e.toString();
  }

 }

 @RequestMapping(value = "/{id}", method = RequestMethod.GET)
 public @ResponseBody
 Student getStudent(@PathVariable("id") long id) {
  Student student = null;
  try {
   student = dataDao.getStudentById(id);

  } catch (Exception e) {
   e.printStackTrace();
  }
  return student;
 }

 @RequestMapping(value = "/list", method = RequestMethod.GET)
 public @ResponseBody
 List<student> getStudent() {

  List<student> studentList = null;
  try {
   studentList = dataDao.getStudentList();

  } catch (Exception e) {
   e.printStackTrace();
  }

  return studentList;
 }

 @RequestMapping(value = "delete/{id}", method = {RequestMethod.DELETE, RequestMethod.GET})
 String deleteStudent(@PathVariable("id") long id) {

  try {
   dataDao.deleteStudent(id);
   return "Student " + id + " Deleted Successfully !";
  } catch (Exception e) {
   return e.toString();
  }

 }
}

At final, the project structure is looks like this:

Run it on tomcat, in case port 8080 is used by Oracle XE, run Tomcat on 8081 port. Enter "tomcat:run -Dmaven.tomcat.port=8081" in goals at Run Configuration window.

Enter http://localhost:8081/MyMavenWS/student/list on browser. You can see the JSON data type of the student list.

Below is for deleting.

Next, create a client file to call web service,

Add test folder under src/main/jave/spring/code/com, and add TestClient.java

TestClient.java

package spring.code.com.test;
import spring.code.com.model.*;
import java.util.LinkedHashMap;
import java.util.List;
 
import org.springframework.web.client.RestTemplate;


public class TestClient {

  public static String strURI = "http://localhost:8081/MyMavenWS/student";
     
     public static void main(String args[]){
          
         
         System.out.println("************************");
         testCreateStudent();
         
         System.out.println("************************");
         testGetStudentById(2);
         
         System.out.println("************************");
         testGetStudentList();
         
         System.out.println("************************");
         testDeleteStudentById(2);
     }
  
     private static void testGetStudentList() {
      
         RestTemplate restTemplate = new RestTemplate();
        
         List<linkedhashmap> students = restTemplate.getForObject(strURI+"/list", List.class);
         System.out.println(students.size());
         for(LinkedHashMap student : students){
             System.out.println("ID="+student.get("id")+",Name="+student.get("firstName"));;
         }
     }
  
     private static void testGetStudentById(long Id) {
         RestTemplate restTemplate = new RestTemplate();
         Student student = restTemplate.getForObject(strURI+"/" + Id, Student.class);
         System.out.println("ID="+student.getId()+",Name="+student.getFirstName());
     }
     
     private static void testCreateStudent() {
      
         RestTemplate restTemplate = new RestTemplate();
         Student student = new Student();
         student.setId(3);
         student.setFirstName("Nelson");
         student.setLastName("Davis");
         
         String str = restTemplate.postForObject(strURI+"/create", student, java.lang.String.class);
         System.out.println(str);
         
     }
  
     
     
     private static void testDeleteStudentById(long Id) {
      
         RestTemplate restTemplate = new RestTemplate();
         restTemplate.delete(strURI+"/delete/" + Id);
         System.out.println("Student with ID: " + Id + " was deleted.");  
     }
  


}

Source Code is here: