<Context>

<Resource name="jdbc/orcljdbc_ds" auth="Container"

type="javax.sql.DataSource"

driverClassName="oracle.jdbc.OracleDriver"

username="hr"

password="hr"

url="jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(HOST=myhost)(PORT=1521)(PROTOCOL=tcp))(CONNECT_DATA=(SERVICE_NAME=myorcldbservicename)))"

/>

</Context>

The Oracle JDBC drivers allow Java applications to connect and process data in the Oracle Database. **Apache Tomcat** is a Java application server for building and deploying Java Web applications. This repository has code samples for a Java Servlet that connects to the Oracle Database using the Oracle JDBC driver. We have furnished `build.xml` to compile the servlet and the `Readme.md` that has instructions to compile and deploy this servlet on Tomcat. If you have subscribed to any Oracle Database Service on Cloud such as DBCS, EECS, BMCS etc., follow these instructions to verify the database connectivity with Tomcat.

* **Apache Tomcat**: Download and install [Apache Tomcat](https://tomcat.apache.org/)

* **Apache Ant**: Make sure you have [Apache ANT](http://ant.apache.org/) to compile the source code

* **JDBC driver**: Download the latest JDBC driver [ojdbc8.jar from OTN](http://www.oracle.com/technetwork/database/features/jdbc/jdbc-ucp-122-3110062.html)

* **Oracle Database**: You need to have a working Oracle Database with the credentials to verify the successful connection. Make sure either you have subscribed to Oracle Database Service on Cloud (DBCS, EECS, BMCS, ExaCS) or installed an Oracle Database on premise.

* **Update build.xml**: Download the `build.xml` present in this repository. Update TOMCAT_HOME to point to the location where Tomcat is installed.

* **Create a Resource in context.xml**: Download the `context.xml` present in `META-INF` folder. Update the database URL, username, and password to point to your Oracle Database. Let us name this datasource as `orcljdbc_ds`

* **Update JDBCSample_Servlet**: Download the `JDBCSample_Servlet` from this repository. Update the method `getDataSource()` to use the correct Oracle datasource name. E.g.,`orcljdbc_ds`

* **JDBC driver**: Place the downloaded JDBC driver ojdbc8.jar in `WEB-INF/lib` folder.

* **Create the war file**: Go to the location where the `build.xml` is located. Execute the command `ant` that will compile and also create the `JDBCSample.war` file in the `dist` folder.

* **Deploy the WAR file**: Copy the `JDBCSample.war` file to TOMCAT_HOME/webapps/ and Start the Tomcat Server

* **Invoke the Servlet**: Invoke the servlet at `https://localhost:8080/JDBCSample/JDBCSample_Servlet`

* **Check the Results**: Check the results on the page and make sure that it prints driver information retrieved from the Oracle database.

* [Connecting Java Applications to Database Cloud Services](https://blogs.oracle.com/dev2dev/connecting-java-applications-to-database-cloud-services)

* [Using Java Containers with Exadata Express Cloud Service (EECS)](http://www.oracle.com/technetwork/database/application-development/jdbc/jdbc-eecontainers-cloud.html#tomcat)

<project name="JDBCSampleDemo" default="all">

<property name="classes" value="WEB-INF/classes" />

<property name="src" value="src" />

<property name="dist" value="dist" />

<property name="TOMCAT_HOME" value="/Users/test/apache-tomcat-9.0.0.M17" />

<path id="JDBCSampleDemo_classpath">

<pathelement location="${classes}"/>

<fileset dir="${TOMCAT_HOME}/lib" includes="*.jar" />

<fileset dir="WEB-INF/lib" includes="*.jar" />

</path>

<target name="displayMessage">

<echo message="-------------------------------------------------"/>

<echo message=" BUILDING JDBC Sample Demo "/>

<echo message="-------------------------------------------------"/>

</target>

<target name="clean">

<delete dir="${classes}"/>

<delete dir="${dist}" />

</target>

<target name="init">

<mkdir dir="${classes}"/>

</target>

<target name="compile" depends="init">

<javac srcdir="${src}"

includeantruntime="false"

destdir="${classes}"

debug="on">

<classpath refid="JDBCSampleDemo_classpath"/>

</javac>

</target>

<target name="displayMessage2">

<echo message="--------------------------------------------------"/>

<echo message=" CREATING a WAR FILE "/>

<echo message="--------------------------------------------------"/>

</target>

<target name="prepare_war" depends="compile">

<mkdir dir="${dist}" />

<jar destfile="${dist}/JDBCSample.war" basedir="."/>

</target>

<target name="all" depends="displayMessage, clean, init,compile,displayMessage2 ,prepare_war"></target>

</project>

import java.io.IOException;

import java.io.PrintWriter;

import java.sql.Connection;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.sql.Statement;

import javax.sql.DataSource;

import java.sql.DatabaseMetaData;

import javax.naming.Context;

import javax.naming.InitialContext;

import javax.naming.NamingException;

import javax.servlet.ServletException;

import javax.servlet.annotation.WebServlet;

import javax.servlet.http.HttpServlet;

import javax.servlet.http.HttpServletRequest;

import javax.servlet.http.HttpServletResponse;

@WebServlet("/JDBCSample_Servlet")

public class JDBCSample_Servlet extends HttpServlet {

private static final long serialVersionUID = 1L;

/**

public JDBCSample_Servlet() {

super();

}

/**

protected void doGet(HttpServletRequest request, HttpServletResponse response)

throws ServletException, IOException {

PrintWriter out = response.getWriter();

out.print("Sample JDBC Servlet");

try {

// Get a context for the JNDI look up

DataSource ds = getDataSource();

// With AutoCloseable, the connection is closed automatically.

try (Connection connection = ds.getConnection()) {

for (int i=0; i<10; i++) {

out.println("The database user is"

+ executeBusinessLogicOnDatabase(connection, out));

}

out.print("\n Sample JDBC Servlet Request was successful");

response.setStatus(200);

}

} catch (Exception e) {

response.setStatus(500);

response.setHeader("Exception", e.toString());

out.print("\n Web Request failed");

out.print("\n "+e.toString());

e.printStackTrace();

}

}

/*

private DataSource getDataSource() throws NamingException {

Context ctx;

ctx = new InitialContext();

Context envContext = (Context) ctx.lookup("java:/comp/env");

// Look up a data source

javax.sql.DataSource ds

= (javax.sql.DataSource) envContext.lookup ("jdbc/orcljdbc_ds");

return ds;

}

/*

private String executeBusinessLogicOnDatabase(Connection conn,

PrintWriter out) throws SQLException {

// Get the JDBC driver name and version

DatabaseMetaData dbmd = conn.getMetaData();

out.println("Driver Name: " + dbmd.getDriverName());

out.println("Driver Version: " + dbmd.getDriverVersion());

String user = null;

String query = "select user from dual";

Statement stmt = conn.createStatement();

ResultSet rs = stmt.executeQuery(query);

while (rs.next()) {

user = rs.getString(1);

}

stmt.close();

rs.close();

return user;

}

public void destroy() { }

}

The Oracle JDBC drivers allow Java applications to connect and process data in the Oracle Database. Oracle WebLogic Server is the application server for building and deploying enterprise Java EE applications. This repository has code samples for a Java Servlet that connects to the Oracle Database using the Oracle JDBC driver. We have furnished `build.xml` to compile the servlet and the `Readme.md` that has instructions to compile and deploy this servlet on WebLogic Server. If you have subscribed to any Oracle Database Service on Cloud such as DBCS, EECS, BMCS etc., follow these instructions to verify the connectivity with WebLogic Server.

* **Web Logic Server v12.2.1.2**: Download and install the [WebLogic Server v12.2.1.2](http://www.oracle.com/technetwork/middleware/weblogic/downloads/index.html)

* **Apache Ant**: Make sure you have [Apache ANT](http://ant.apache.org/) to compile the source code

* **JDBC driver**: You can choose to use the JDBC driver (ojdbc7.jar) that is shipped with WebLogicServer v 12.2.1.2 or you can download and use the latest [ojdbc8.jar from OTN](http://www.oracle.com/technetwork/database/features/jdbc/jdbc-ucp-122-3110062.html)

* **Oracle Database**: You need to have a working Oracle Database with the credentials to verify the successful connection. Make sure either you have subscribed to Oracle Database Service on Cloud (DBCS, EECS, BMCS, ExaCS) or installed an Oracle Database on premise.

* **Update build.xml**: Download the `build.xml` present in this repository. Update WLS_HOME to point to the location where WebLogic Server is installed.

* **Create a Datasource in WebLogicServer**: Create an Oracle Datasource through the admin console. Refer to the blog for more details ["Create and Deploy a Java Servlet using WebLogic Server"](https://blogs.oracle.com/dev2dev/create-and-deploy-a-java-servlet-using-weblogic-server-wls). Let us name this datasource as `orcljdbc_ds`

* **Update JDBCSample_Servlet**: Download the `JDBCSample_Servlet` from this repository and Update the method `getDataSource()` to use the correct Oracle datasource name. E.g.,`orcljdbc_ds` created through admin console.

* **Create the war file**: Go to the location where the `build.xml` is located. Execute the command `ant` that will compile and also create the `JDBCSample.war` file in the `dist` folder.

* **Deploy the WAR file**: Start the WebLogic Server and open the admin console. Follow the steps in this [blog](https://blogs.oracle.com/dev2dev/create-and-deploy-a-java-servlet-using-weblogic-server-wls#step8) to deploy `JDBCSample.war`

* **Invoke the Servlet**: Invoke the servlet at `https://localhost:7001/JDBCSample/JDBCSample_Servlet`

* **Check the Results**: Check the results on the page and make sure that it prints driver information retrieved from the Oracle database.

* [Create and Deploy a Java Servlet using WebLogic Server](https://blogs.oracle.com/dev2dev/create-and-deploy-a-java-servlet-using-weblogic-server-wls)

* [How to use the latest ojdbc8.jar in WebLogic Server?](http://www.oracle.com/technetwork/database/application-development/jdbc/jdbc-eecontainers-cloud.html#wls)

* [Using Java Containers with Exadata Express Cloud Service (EECS)](http://www.oracle.com/technetwork/database/application-development/jdbc/jdbc-eecontainers-cloud.html#wls)

<project name="JDBCSampleDemo" default="all">

<property name="classes" value="WEB-INF/classes" />

<property name="src" value="src" />

<property name="dist" value="dist" />

<property name="WLS_HOME" value="/Users/test/wls12212/wlserver" />

<path id="JDBCSampleDemo_classpath">

<pathelement location="${classes}"/>

<fileset dir="${WLS_HOME}/server/lib" includes="*.jar" />

<fileset dir="WEB-INF/lib" includes="*.jar" />

</path>

<target name="displayMessage">

<echo message="-------------------------------------------------"/>

<echo message=" BUILDING JDBC Sample Demo "/>

<echo message="-------------------------------------------------"/>

</target>

<target name="clean">

<delete dir="${classes}"/>

<delete dir="${dist}" />

</target>

<target name="init">

<mkdir dir="${classes}"/>

</target>

<target name="compile" depends="init">

<javac srcdir="${src}"

includeantruntime="false"

destdir="${classes}"

debug="on">

<classpath refid="JDBCSampleDemo_classpath"/>

</javac>

</target>

<target name="displayMessage2">

<echo message="--------------------------------------------------"/>

<echo message=" CREATING a WAR FILE "/>

<echo message="--------------------------------------------------"/>

</target>

<target name="prepare_war" depends="compile">

<mkdir dir="${dist}" />

<jar destfile="${dist}/JDBCSample.war" basedir="."/>

</target>

<target name="all" depends="displayMessage, clean, init,compile,displayMessage2 ,prepare_war"></target>

</project>

import java.io.IOException;

import java.io.PrintWriter;

import java.sql.Connection;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.sql.Statement;

import java.sql.DatabaseMetaData;

import javax.sql.DataSource;

import javax.naming.Context;

import javax.naming.InitialContext;

import javax.naming.NamingException;

import javax.servlet.ServletException;

import javax.servlet.annotation.WebServlet;

import javax.servlet.http.HttpServlet;

import javax.servlet.http.HttpServletRequest;

import javax.servlet.http.HttpServletResponse;

@WebServlet("/JDBCSample_Servlet")

public class JDBCSample_Servlet extends HttpServlet {

private static final long serialVersionUID = 1L;

public JDBCSample_Servlet() {

super();

}

/*

protected void doGet(HttpServletRequest request,

HttpServletResponse response) throws ServletException,IOException {

PrintWriter out = response.getWriter();

out.println("Sample JDBC Servlet");

try {

// Get a context for the JNDI look up

DataSource ds = getDataSource();

out.println("Data source is " + ds );

// With AutoCloseable, the connection is closed automatically.

try ( Connection connection = ds.getConnection()) {

out.println("Connection obtained " + connection);

for (int i=0; i<10; i++) {

out.println("The database user is" +

executeBusinessLogicOnDatabase(connection, out));

}

out.print("\n Sample JDBC Servlet Request was successful");

response.setStatus(200);

}

} catch (Exception e) {

response.setStatus(500);

response.setHeader("Exception", e.toString());

out.print("\n Web Request failed");

out.print("\n "+e.toString());

e.printStackTrace();

}

}

/*

private DataSource getDataSource() throws NamingException {

Context ctx = new InitialContext();

// Look up for the JNDI datasource

javax.sql.DataSource ds

= (javax.sql.DataSource) ctx.lookup ("orcljdbc_ds");

return ds;

}

/*

private String executeBusinessLogicOnDatabase(Connection conn, PrintWriter out) throws SQLException {

// Get the JDBC driver name and version

DatabaseMetaData dbmd = conn.getMetaData();

out.println("Driver Name: " + dbmd.getDriverName());

out.println("Driver Version: " + dbmd.getDriverVersion());

String user = null;

String query = "select user from dual";

Statement stmt = conn.createStatement();

ResultSet rs = stmt.executeQuery(query);

while (rs.next()) {

user = rs.getString(1);

}

stmt.close();

rs.close();

return user;

}

public void destroy() {}

}

The furnished testSODA.sql performs the steps (iv) and (v).

final static String DB_URL= "jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(HOST=myhost)(PORT=1521)(PROTOCOL=tcp))(CONNECT_DATA=(SERVICE_NAME=myorcldbservicename)))";

// Use TNS alias when using tnsnames.ora. Use it while connecting to the database service on cloud.

// final static String DB_URL= "jdbc:oracle:thin:@orcldbaccess";