5 Connecting to Oracle Database 12c Release 2 (12.2)
This chapter is the first in a series of five chapters, each of which describes how to create parts of a Java application that accesses Oracle Database 12c Release 2 (12.2) and displays, modifies, deletes, and updates data on it. To be able to access the database from a Java application, you must connect to the database using a java.sql.Connection object.
5.1 Creating an Employee Java Bean
The Employee Java bean creates getter and setter methods for columns to be displayed.
package com.oracle.jdbc.samples.entity;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
public class Employee {
private int Employee_Id;
private String First_Name;
private String Last_Name;
private String Email;
private String Phone_Number;
private String Job_Id;
private int Salary;
public Employee (ResultSet resultSet) throws SQL Exception{
this.Employee_Id = resultSet.getInt(1);
this.First_Name = resultSet.getString(2);
this.Last_Name = resultSet.getString(3);
this.Email = resultSet.getString(4);
this.Phone_Number = resultSet.getString(5);
his.Job_Id = resultSet.getString(6);
this.Salary = resultSet.getInt(7);
}
public int getEmployee_Id(){
return Employee_Id;
}
public void setEmployee_Id(int Employee_Id){
this.Employee_Id = Employee_Id;
}
public String getFirst_Name(){
return First_Name;
}
public void setFirst_Name(String First_Name){
this.First_Name = First_Name;
}
public String getLast_Name(){
return Last_Name;
}
public void setLast_Name(String Last_Name){
this.Last_Name = Last_Name;
}
public String getPhone_Number(){
return Phone_Number;
}
public String getJob_Id(){
return Job_Id;
}
public void setJob_Id(String Job_Id){
this.Job_Id = Job_Id;
}
public int getSalary(){
return Salary;
}
public void setSalary(int Salary){
this.Salary = Salary;
}
}5.2 Creating a Java Bean Interface for a JDBC Connection
package com.oracle.jdbc.samples.bean;
import com.oracle.jdbc.samples.entity.Employee;
public interface JdbcBean {
public List <name of employee> getEmployees();
}5.3 Creating a Java Bean Implementation for a JDBC Connection
The following code helps to create Java Bean implementation for a JDBC Connection:
package com.oracle.jdbc.samples.bean;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;
import com.oracle.jdbc.samples.entity.Employee;
import java.sql.PreparedStatement;
importoracle.jdbc.OracleStatement;
import oracle.jdbc.OracleConnection;
import oracle.jdbc.driver.OracleDriver;
public class JdbcBeanImpl implements JdbcBean {
public static Connection getConnection() throws SQLException {
DriverManager.registerDriver(new oracle.jdbc.OracleDriver());
Connection connection =
DriverManager.getConnection(“jdbc:oracle:thin:@//slc07qwu.us.oracle.com:5521/jvma.regress.rdbms.dev.us.oracle.com”, “hr”, “hr”);
return connection;
}
@Override
public List<Employee>getEmployees(){
List<Employee>returnValue = new ArrayList<>();
try (Connection connection = getConnection()) {
try (Statement statement = connection.createStatement()) {
try (ResultSet resultSet = statement.executeQuery(“SELECT Employee_Id, First_Name, Last_Name, Email, Phone_Number, Job_Id, Salary from employees”)){
while(resultSet.next()){
returnValue.add(new Employee(resultSet));
}
}
}
}catch (SQLException ex){
logger.log(Level.SEVERE, null, ex);
ex.printStackTrace();
}
return returnValue;
}5.4 Creating a Servlet to Process the Request
The following code describes the steps required to create a Servlet to process a request.
package com.oracle.jdbc.samples.web;
import com.google.gson.Gson;
import com.google.gson.reflect.TypeToken;
import com.oracle.jdbc.samples.bean.Jdbcbean;
import com.oracle.jdbc.samples.bean.jdbcBeanImpl;
import com.oracle.jdbc.samples.entity.Employee;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.*;
import javax.servlet.http.*;
import java.io.PrintWriter;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
@WebServlet(name=”WebController”,urlPatterns={”/WebController”})
public class WebController extends HttpServlet {
JdbcBean jdbcBean = new JdbcBeanImpl();
private void reportError(HttpServletResponse response, String message)
throws ServletException, IOException {
response.setContentType(“text/html;charset=UTF-8”);
try (PrintWriter out = response.getWriter() {
out.println(“<!DOCTYPE html>”);
out.println(“<html>”);
out.println(“<head>”);
out.println(“<title>Servlet WebController</title>”);
out.println(“</head>”);
out.println(“<body>)”;out.println(“<h1>”+message+”</h1>”);
out.println(“<h1>”+message+”</h1>”);
out.println(:</html>”);
}
}
protected void processRequest(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
Gson gson = new Gson();
String value = null;
List<Employee> employeeList = null;
employeeList = jdbcBean.getEmployees();
if(employeeList != null) {
response.setContentType("application/json");
gson.toJson(employeeList,
new TypeToken<ArrayList<Employee>>() { }.getType(), response.getWriter());
} else {
response.setStatus(HttpServletResponse.SC_NOT_FOUND);
}
protected void doGet(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
processRequest(request, response);
}
}5.5 Create an HTML Page to Display Results
Class Name:
src/main/webapp/listAll.html
The following code describes how to create a method processResponse() inside the Java script that processes the JSON to show it on the HTML.
Sample Code
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>List all Employees</title>
<link rel="stylesheet" type="text/css" href="css/app.css" >
</head>
<body>
<div id="id-emp"></div>
<script>
var xmlhttp = new XMLHttpRequest();
var url = "WebController";
xmlhttp.onreadystatechange=function() {
if (xmlhttp.readyState == 4 && xmlhttp.status == 200) {
processResponse(xmlhttp.responseText);
}
}
xmlhttp.open("GET", url, true);
xmlhttp.send();
function processResponse(response) {
var arr = JSON.parse(response);
var i;
var out = "<table>";
keys = Object.keys(arr[0]);
// Print headers
out += "<tr>"
for(i = 0; i < keys.length; ++i) {
out += "<th>"+keys[i]+"</th>"
}
out += "</tr>";
// Print values
for(j = 0; j < arr.length; j++) {
out += "<tr>"
for(i = 0; i < keys.length; ++i) {
out += "<td>"+arr[j][keys[i]]+"</td>"
}
out += "</tr>"
}
out += "</table>";
document.getElementById("id-emp").innerHTML = out;
}
</script>
</body>
</html>5.6 Create a CSS File
The following code creates a method processResponse() inside the Java script that processes the JSON to show it on the HTML.
Sample Code:
table {
border-collapse:collapse;
width:100%;
}
th, td{
text-align:left;
padding:8px;
}
tr:nth-child(even){background-color: #f2f2f2}
th {
background-color: #4CAF50;
color: white;
}h {
background-color: #4CAF50;
color: white;
}
body {
font-family: "Lato", sans-serif;
}
.sidenav {
height: 100%;
width: 0;
position: fixed;
z-index: 1;
top: 0;
left: 0;
background-color: #FF0000;
overflow-x: hidden;
transition: 0.5s;
padding-top: 60px;
}
.sidenav a {
padding: 8px 8px 8px 32px;
text-decoration: none;
font-size: 25px;
color: black;
display: block;
transition: 0.3s
}
.sidenav a:hover, .offcanvas a:focus{
color: #f1f1f1;
}
.closebtn {
position: absolute;
top: 0;
right: 25px;
font-size: 36px !important;
margin-left: 50px;
}
#main {
transition: margin-left .5s;
padding: 16px;
}