This is the fourth blog in this blog series which I have called “Java & JDBC for Oracle DBAs”. Now we are finally ready to start to develop towards the Oracle database we created in part 1, and schemas we created in part 3.
- Part 1: Install/setup Oracle database (in docker)
- Part 2: Installing Java (JDK), Eclipse and Maven
- Part 3: Git, Oracle schemas and your first Java application
- Part 4: Your first JDBC Application (This)
- Part 5: Spring-boot, JdbcTemplate & DB migration (Using FlywayDB)
- Part 6: Spring-boot, JPA and Hiberate
Part 4: Your first JDBC application
Creating a new project
Today we start by creating a new project. Hopefully you remember how to do this from the last blog post – part 3. If not – take a step back and review part 3. You can keep your existing project open.
In Eclipse go to the top menu and click “File-> New -> Project …”. Then choose “Maven-> Maven project”. Or you might be able to chose “Maven project” directly under the “New” menu. Choose the same workspace as last time, and the same archetype (maven-archetype-quickstart). Use the same groupId as last time, and use “simpleJdbcDemo” as artifactId, and click “Finish”.
Adding the JDBC dependencies
Note! Before continuing you need to have installed the Oracle JDBC drivers into the local maven repository, or configured maven to authenticated with your Oracle support account. These steps were described in part 2 (Installing Java (JDK), Eclipse and Maven).
When we write Java code towards an Oracle we need access to the Oracle JDBC drivers. We now need to add a dependency towards the needed classes in our pom.xml file. Add the “ojdbc8″dependency to the pom-xml file (see picture below).
<dependencies>
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>3.8.1</version>
<scope>test</scope>
</dependency>
<dependency>
<groupId>com.oracle.jdbc</groupId>
<artifactId>ojdbc8</artifactId>
<version>12.2.0.1</version>
</dependency>
</dependencies>
We now have access to the classes we need to create the connection towards the database. If you look in the Eclipse “Project Explorer” under “Maven dependencies” you will see the following:
Now you see why we also downloaded the “ucp.jar” file. The “ojdbc8” depends on the “ucp.jar”, and maven have automatically added this. (Note! If you have manually installed the Oracle drivers into the your local repository, this dependency might not show. But the code in this blog will still run ok).
We are now ready to write some code towards our database. In a real application we would most probably use a connection pool. In this simple test we’ll use a dedicated connection towards the database instead.
Create a test table in the DEMODATA schema
Log into the database as DEMODATA using either SQL Developer or SqlPlus, and create the following table:
CREATE TABLE person (id number, first_name varchar2(50), salary number); INSERT INTO person VALUES (1, 'SCOTT', 1000); INSERT INTO person VALUES (2, 'VALERIE', 2000); INSERT INTO person VALUES (3, 'JIM', 1500); COMMIT;
In the example below I’ll be using a docker console to run the SQLs above:
$ docker exec -it ora12c-db01 /bin/bash
[oracle@1408a090d766 ~]$ sqlplus demodata/demo@orcl
SQL*Plus: Release 12.2.0.1.0 Production on Wed Jul 18 23:46:19 2018
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Last Successful login time: Wed Jul 18 2018 23:46:03 +00:00
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
SQL> CREATE TABLE person (id number, first_name varchar2(50), salary number);
Table created.
SQL> INSERT INTO person VALUES (1, 'SCOTT', 1000);
1 row created.
SQL> INSERT INTO person VALUES (2, 'VALERIE', 2000);
1 row created.
SQL> INSERT INTO person VALUES (3, 'JIM', 1500);
1 row created.
SQL> COMMIT;
Commit complete.
SQL> exec demo_grant.granttoroles;
PL/SQL procedure successfully completed.
SQL> conn demo/demo@orcl
Connected.
SQL> select * from person;
ID FIRST_NAME SALARY
---------- -------------------------------------------------- ----------
1 SCOTT 1000
2 VALERIE 2000
3 JIM 1500
SQL>
Now we have a PERSON table with 3 rows, which can be accessed through the DEMO application user.
Writing Java code to access the database
First we add a variable “url” with the connection information, and try-catch block where we try connect to the database. If the connections succeeds we’ll print “OK”, else we’ll print an “ERROR” message:
package no.eritec.demo.simpleJdbcDemo;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
public class App {
private static String url = "jdbc:oracle:thin:demodata/demo@localhost:1521/orcl";
public static void main( String[] args ) {
System.out.print( "Testing connection to database ..." );
try {
Connection conn = DriverManager.getConnection(url);
System.out.println("OK");
System.exit(0); // Prevent hang after completion when ran by mvn
} catch (SQLException ignore) {
System.out.println("ERROR: " + ignore);
System.exit(1); // Prevent hang after completion when ran by mvn
}
}
}
If we run this we get:
$ mvn exec:java -Dexec.workingdir="target" -Dexec.mainClass="no.eritec.demo.simpleJdbcDemo.App" [INFO] Scanning for projects... [INFO] [INFO] -------------------< no.eritec.demo:simpleJdbcDemo >-------------------- [INFO] Building simpleJdbcDemo 0.0.1-SNAPSHOT [INFO] --------------------------------[ jar ]--------------------------------- [INFO] [INFO] --- exec-maven-plugin:1.6.0:java (default-cli) @ simpleJdbcDemo --- Testing connection to database ...OK
If we change the url-string to point to a wrong port number (url = “jdbc:oracle:thin:demodata/demo@localhost:1531/orcl”), we get:
$ mvn exec:java -Dexec.workingdir="target" -Dexec.mainClass="no.eritec.demo.simpleJdbcDemo.App" [INFO] Scanning for projects... [INFO] [INFO] -------------------< no.eritec.demo:simpleJdbcDemo >-------------------- [INFO] Building simpleJdbcDemo 0.0.1-SNAPSHOT [INFO] --------------------------------[ jar ]--------------------------------- [INFO] [INFO] --- exec-maven-plugin:1.6.0:java (default-cli) @ simpleJdbcDemo --- Testing connection to database ...ERROR: java.sql.SQLRecoverableException: IO Error: The Network Adapter could not establish the connection
Adding code to do SELECT & DML (INSERT, UPDATE and DELETE)
Now I’ll add code to SELECT and manipulate the data in the PERSON table. To make the code a little more readable, I start to introduce new procedures.
In the code below I have added procedures for both listing all persons, searching for person id, adding a person, dropping a person, and finally updating the salary for a person:
package no.eritec.demo.simpleJdbcDemo;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class App
{
private static String url = "jdbc:oracle:thin:demodata/demo@localhost:151/orcl";
private static Connection conn;
public static void main( String[] args ) {
System.out.print( "Connecting to the database ..." );
getConnection();
System.out.println("Listing all persons ...");
listPersons();
System.out.println("Getting all info for person id=3:");
getPerson(3);
System.out.println("Adding a new person:");
addPerson(4,"JULIE", 1400);
System.out.println("Updating JIMs salary to 3000 ...");
updatePersonSalary(3,3000);
System.out.println("Listing all persons again ...");
listPersons();
System.out.println("Deleting Julie again ...");
deletePerson(4);
System.exit(0);
}
private static void updatePersonSalary(int id, int salary) {
PreparedStatement ps = null;
try {
ps = conn.prepareStatement("UPDATE person SET salary = ? WHERE id= ?");
ps.setInt(1, salary);
ps.setInt(2, id);
int rows = ps.executeUpdate();
System.out.println("Updated " + rows + " row(s)");
} catch (SQLException e) {
System.out.println("ERROR: " + e);
System.exit(1); // Just exit if error
} finally {
try {
if (ps!=null) ps.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
private static void getConnection() {
try {
conn = DriverManager.getConnection(url);
System.out.println("OK");
} catch (SQLException ignore) {
System.out.println("ERROR");
System.exit(1); // Just exit if error
}
}
private static void listPersons() {
Statement s = null;
try {
s = conn.createStatement();
ResultSet rs = s.executeQuery("SELECT * FROM person");
System.out.println("ID FIRST_NAME SALARY");
System.out.println("-------- ------------------------ ----------");
while(rs.next()){
int id = rs.getInt("id");
String name = rs.getString("first_name");
int sal = rs.getInt("salary");
int length = 24 - name.length();
System.out.println(String.format("%1$8s", id) + " " + name
+ String.format("%1$"+ length + "s","" )
+ " " + sal);
}
} catch (SQLException e) {
System.out.println("ERROR");
System.exit(1); // Just exit if error
} finally {
try {
if (s!=null) s.close();
} catch (SQLException e){
e.printStackTrace();
}
}
}
private static void getPerson(int id) {
PreparedStatement ps = null;
try {
ps = conn.prepareStatement("SELECT * FROM person WHERE id=?");
ps.setInt(1, id);
ResultSet rs = ps.executeQuery();
System.out.println("ID FIRST_NAME YEAR");
System.out.println("-------- ------------------ ---------");
if (rs.next()) {
String name = rs.getString("first_name");
int sal = rs.getInt("salary");
System.out.println(String.format("%1$8s", id) + " " + String.format("%1$18s", name) + " " + sal );
}
} catch (SQLException e) {
System.out.println("ERROR");
System.exit(1); // Just exit if error
} finally {
try {
if (ps!=null) ps.close();
} catch (SQLException e){
e.printStackTrace();
}
}
}
private static void addPerson(int id, String first_name, int salary) {
PreparedStatement ps = null;
try {
ps = conn.prepareStatement("INSERT INTO person VALUES (?,?,?)");
ps.setInt(1, id);
ps.setString(2, first_name);
ps.setInt(3, salary);
int rows = ps.executeUpdate();
System.out.println("Inserted " + rows + " row(s)");
} catch (SQLException e) {
System.out.println("ERROR: " + e);
System.exit(1); //Just exit if error
} finally {
try {
if (ps!=null) ps.close();
} catch (SQLException e){
e.printStackTrace();
}
}
}
private static void deletePerson(int id) {
PreparedStatement ps = null;
try {
ps = conn.prepareStatement("DELETE FROM person WHERE id=?");
ps.setInt(1, id);
int rows = ps.executeUpdate();
System.out.println("Deleted " + rows + " row(s)");
} catch (SQLException e) {
System.out.println("ERROR: " + e);
System.exit(1); // Just exit if error
} finally {
try {
if (ps!=null) ps.close();
} catch (SQLException e){
e.printStackTrace();
}
}
}
}
When we run the code, we get the following output:
$ mvn exec:java -Dexec.workingdir="target" -Dexec.mainClass="no.eritec.demo.simpleJdbcDemo.App"
[INFO] Scanning for projects...
[INFO]
[INFO] -------------------< no.eritec.demo:simpleJdbcDemo >--------------------
[INFO] Building simpleJdbcDemo 0.0.1-SNAPSHOT
[INFO] --------------------------------[ jar ]---------------------------------
[INFO]
[INFO] --- exec-maven-plugin:1.6.0:java (default-cli) @ simpleJdbcDemo ---
Connecting to the database ...OK
Listing all persons ...
ID FIRST_NAME
-------- ------------------------
1 SCOTT 1000
2 VALERIE 2000
3 JIM 1500
Getting all info for person id=3:
ID FIRST_NAME YEAR
-------- ------------------ ---------
3 JIM 1500
Adding a new person:
Inserted 1 row(s)
Updating JIMs salary to 3000 ...
Updated 1 row(s)
Listing all persons again ...
ID FIRST_NAME
-------- ------------------------
1 SCOTT 1000
2 VALERIE 2000
3 JIM 3000
4 JULIE 1400
Deleting Julie again ...
Deleted 1 row(s)
Introducing a entity class (for Person)
In Java we work with classes, so we need a Person class. In Java we call this an Entity, and a entity class. Then we go back to Eclipse, and the main menu. Click “File -> New -> Others”, and choose “Class”, and click “Next”.
In the next window, add “entity” in the package path (see below), and name the class “Person”, and click “Finish”.
Then we add some appropriate variables, a constructor, and getters and setters:
package no.eritec.demo.simpleJdbcDemo.entity;
public class Person {
private int id;
private String first_name;
private int salary;
public Person(int id, String first_name, int salary) {
this.id = id;
this.first_name = first_name;
this.salary = salary;
}
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getFirst_name() {
return first_name;
}
public void setFirst_name(String first_name) {
this.first_name = first_name;
}
public int getSalary() {
return salary;
}
public void setSalary(int salary) {
this.salary = salary;
}
}
To keep the main method small, let’s move some of the code accessing the database into a separate class – a dao class (data access object). So let’s create a another class in package “no.eritec.demo.simpleJdbcDemo.dao” and class name “PersonDAO”.
We add some methods:
- getPersonById
- getPersons
- addPerson
- deletePerson
- updateSalary
- updateName
- countPersons
The code:
package no.eritec.demo.simpleJdbcDemo.dao;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import no.eritec.demo.simpleJdbcDemo.entity.Person;
public class PersonDAO {
private String url = "jdbc:oracle:thin:demodata/demo@localhost:151/orcl";
private Connection conn = null;
public PersonDAO() {
getConnection();
}
private void getConnection() {
try {
conn = DriverManager.getConnection(url);
System.out.println("Connection established: " + url);
} catch (SQLException ignore) {
System.out.println("ERROR");
System.exit(0); // Prevent hang after completion when ran by mvn
}
}
public Person getPersonById(int id) {
PreparedStatement ps = null;
Person p = null;
try {
ps = conn.prepareStatement("SELECT * FROM person WHERE id=?");
ps.setInt(1, id);
ResultSet rs = ps.executeQuery();
if (rs.next()) {
p = new Person(id, rs.getString("first_name"),rs.getInt("salary"));
}
} catch (SQLException e) {
System.out.println("ERROR");
System.exit(0); // Prevent hang after completion when ran by mvn
} finally {
try {
if (ps!=null) ps.close();
} catch (SQLException e){
e.printStackTrace();
}
}
return p;
}
public List getPersons() {
List persons = new ArrayList();
Statement s = null;
try {
s = conn.createStatement();
ResultSet rs = s.executeQuery("SELECT * FROM person");
while (rs.next()){
Person p = new Person(rs.getInt("id"),rs.getString("first_name"),rs.getInt("salary"));
persons.add(p);
}
} catch (SQLException e) {
System.out.println("ERROR");
System.exit(0); // Prevent hang after completion when ran by mvn
} finally {
try {
if (s!=null) s.close();
} catch (SQLException e){
e.printStackTrace();
}
}
return persons;
}
public int addPerson(Person p) {
PreparedStatement ps = null;
int rows_inserted = 0;
try {
ps = conn.prepareStatement("INSERT INTO person VALUES (?,?,?)");
ps.setInt(1, p.getId());
ps.setString(2, p.getFirstname());
ps.setInt(3, p.getSalary());
rows_inserted = ps.executeUpdate();
} catch (SQLException e) {
System.out.println("ERROR: " + e);
System.exit(0); // Prevent hang after completion when ran by mvn
} finally {
try {
if (ps!=null) ps.close();
} catch (SQLException e){
e.printStackTrace();
}
}
return rows_inserted;
}
public int deletePersonById(int id) {
PreparedStatement ps = null;
int rows_deleted = 0;
try {
ps = conn.prepareStatement("DELETE FROM person WHERE id=?");
ps.setInt(1, id);
rows_deleted = ps.executeUpdate();
} catch (SQLException e) {
System.out.println("ERROR: " + e);
System.exit(0); // Prevent hang after completion when ran by mvn
} finally {
try {
if (ps!=null) ps.close();
} catch (SQLException e){
e.printStackTrace();
}
}
return rows_deleted;
}
public int updateSalary(int id, int salary) {
PreparedStatement ps = null;
int rows_updated = 0;
try {
ps = conn.prepareStatement("UPDATE person SET salary = ? WHERE id= ?");
ps.setInt(1, salary);
ps.setInt(2, id);
rows_updated = ps.executeUpdate();
} catch (SQLException e) {
System.out.println("ERROR: " + e);
System.exit(0); // Prevent hang after completion when ran by mvn
} finally {
try {
if (ps!=null) ps.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
return rows_updated;
}
public int updateName (int id, String name) {
PreparedStatement ps = null;
int rows_updated = 0;
try {
ps = conn.prepareStatement("UPDATE person SET first_name = ? WHERE id= ?");
ps.setString(1, name);
ps.setInt(2, id);
rows_updated = ps.executeUpdate();
} catch (SQLException e) {
System.out.println("ERROR: " + e);
System.exit(0); // Prevent hang after completion when ran by mvn
} finally {
try {
if (ps!=null) ps.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
return rows_updated;
}
public int countPersons() {
Statement s = null;
int cnt = 0;
try {
s = conn.createStatement();
ResultSet rs = s.executeQuery("SELECT count(*) FROM person");
rs.next();
cnt = rs.getInt(1);
} catch (SQLException e) {
System.out.println("ERROR");
System.exit(0); // Prevent hang after completion when ran by mvn
} finally {
try {
if (s!=null) s.close();
} catch (SQLException e){
e.printStackTrace();
}
}
return cnt;
}
}
We also add a class to handle the output to the console:
package no.eritec.demo.simpleJdbcDemo.gui;
import java.util.List;
import no.eritec.demo.simpleJdbcDemo.entity.Person;
public class Console {
public static void showPersonHeader() {
System.out.println("ID FIRST_NAME SALARY");
System.out.println("-------- ------------------------ ----------");
}
public static void listPerson(Person p) {
String fname = p.getFirstname();
int length = 24 - fname.length();
System.out.println(String.format("%1$8s", p.getId()) + " " + p.getFirstname()
+ String.format("%1$"+ length + "s","" )
+ " " + p.getSalary());
}
public static void listPersons(List list) {
for (Person p : list) {
listPerson(p);
}
}
}
Then we have the edited App class with the main method:
package no.eritec.demo.simpleJdbcDemo;
import java.util.List;
import no.eritec.demo.simpleJdbcDemo.dao.PersonDAO;
import no.eritec.demo.simpleJdbcDemo.entity.Person;
import no.eritec.demo.simpleJdbcDemo.gui.Console;
public class App {
public static void main( String[] args ) {
PersonDAO dao = new PersonDAO();
System.out.println("Listing all persons ...");
List list = dao.getPersons();
Console.showPersonHeader();
Console.listPersons(list);
System.out.println("Getting all info for person id=3:");
Person p = dao.getPersonById(3);
Console.showPersonHeader();
Console.listPerson(p);
System.out.println("Adding a new person:");
dao.addPerson(new Person(4,"JULIE", 1400));
System.out.println("Updating JIMs salary to 3000 ...");
dao.updateSalary(3,3000);
System.out.println("Listing all persons again ...");
list = dao.getPersons();
Console.showPersonHeader();
Console.listPersons(list);
System.out.println("Deleting Julie again ...");
dao.deletePersonById(4);
System.out.println("Counting number of persons ...");
System.out.println("Number of persons: " + dao.countPersons());
System.exit(0);
}
}
Adding instrumentation (Oracle End-to-End Metrics)
Finally we’ll instrument our code with Oracle End-to-end Metrics. I believe very much in the quote from Cary Millsap:
“Performance is a feature”, Cary Millsap
From JDBC 4.1 the specification includes the method “setClientInfo” in the Connection class. This method let you set different tags inside of Oracle – for instance – the module, action and client_info tag. Before JDBC 4.1 you had to use methods in the Oracle JDBC driver and the OracleConnection class. In this blog we’ll only be using the new “setClientInfo” method.
Adding instrumentation to the PersonDAO class:
package no.eritec.demo.simpleJdbcDemo.dao;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLClientInfoException;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;
import java.util.UUID;
import no.eritec.demo.simpleJdbcDemo.entity.Person;
public class PersonDAO {
private String url = "jdbc:oracle:thin:demodata/demo@localhost:151/orcl";
private Connection conn = null;
public PersonDAO() {
getConnection();
}
private void getConnection() {
try {
conn = DriverManager.getConnection(url);
System.out.println("Connection established: " + url);
} catch (SQLException ignore) {
System.out.println("ERROR");
System.exit(1); // Exit if error
}
}
public Person getPersonById(int id) {
setMetrics("SimpleJdbcDemo","getPersonById", UUID.randomUUID().toString());
PreparedStatement ps = null;
Person p = null;
try {
ps = conn.prepareStatement("SELECT * FROM person WHERE id=?");
ps.setInt(1, id);
ResultSet rs = ps.executeQuery();
if (rs.next()) {
p = new Person(id, rs.getString("first_name"),rs.getInt("salary"));
}
} catch (SQLException e) {
System.out.println("ERROR");
System.exit(1); // Exit if error
} finally {
try {
if (ps!=null) ps.close();
} catch (SQLException e){
e.printStackTrace();
}
}
return p;
}
public List getPersons() {
setMetrics("SimpleJdbcDemo","getPersons", UUID.randomUUID().toString());
List persons = new ArrayList();
Statement s = null;
try {
s = conn.createStatement();
ResultSet rs = s.executeQuery("SELECT * FROM person");
while (rs.next()){
Person p = new Person(rs.getInt("id"),rs.getString("first_name"),rs.getInt("salary"));
persons.add(p);
}
} catch (SQLException e) {
System.out.println("ERROR");
System.exit(1); // Exit if error
} finally {
try {
if (s!=null) s.close();
} catch (SQLException e){
e.printStackTrace();
}
}
return persons;
}
public int addPerson(Person p) {
setMetrics("SimpleJdbcDemo","addPerson", UUID.randomUUID().toString());
PreparedStatement ps = null;
int rows_inserted = 0;
try {
ps = conn.prepareStatement("INSERT INTO person VALUES (?,?,?)");
ps.setInt(1, p.getId());
ps.setString(2, p.getFirstname());
ps.setInt(3, p.getSalary());
rows_inserted = ps.executeUpdate();
} catch (SQLException e) {
System.out.println("ERROR: " + e);
System.exit(1); // Exit if error
} finally {
try {
if (ps!=null) ps.close();
} catch (SQLException e){
e.printStackTrace();
}
}
return rows_inserted;
}
public int deletePersonById(int id) {
setMetrics("SimpleJdbcDemo","deletePersonById", UUID.randomUUID().toString());
PreparedStatement ps = null;
int rows_deleted = 0;
try {
ps = conn.prepareStatement("DELETE FROM person WHERE id=?");
ps.setInt(1, id);
rows_deleted = ps.executeUpdate();
} catch (SQLException e) {
System.out.println("ERROR: " + e);
System.exit(1); // Exit if error
} finally {
try {
if (ps!=null) ps.close();
} catch (SQLException e){
e.printStackTrace();
}
}
return rows_deleted;
}
public int updateSalary(int id, int salary) {
setMetrics("SimpleJdbcDemo","updateSalary", UUID.randomUUID().toString());
PreparedStatement ps = null;
int rows_updated = 0;
try {
ps = conn.prepareStatement("UPDATE person SET salary = ? WHERE id= ?");
ps.setInt(1, salary);
ps.setInt(2, id);
rows_updated = ps.executeUpdate();
} catch (SQLException e) {
System.out.println("ERROR: " + e);
System.exit(1); // Exit if error
} finally {
try {
if (ps!=null) ps.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
return rows_updated;
}
public int updateName (int id, String name) {
setMetrics("SimpleJdbcDemo","updateName", UUID.randomUUID().toString());
PreparedStatement ps = null;
int rows_updated = 0;
try {
ps = conn.prepareStatement("UPDATE person SET first_name = ? WHERE id= ?");
ps.setString(1, name);
ps.setInt(2, id);
rows_updated = ps.executeUpdate();
} catch (SQLException e) {
System.out.println("ERROR: " + e);
System.exit(1); // Exit if error
} finally {
try {
if (ps!=null) ps.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
return rows_updated;
}
public int countPersons() {
setMetrics("SimpleJdbcDemo","countPersons", UUID.randomUUID().toString());
Statement s = null;
int cnt = 0;
try {
s = conn.createStatement();
ResultSet rs = s.executeQuery("SELECT count(*) FROM person");
rs.next();
cnt = rs.getInt(1);
} catch (SQLException e) {
System.out.println("ERROR");
System.exit(1); // Exit if error
} finally {
try {
if (s!=null) s.close();
} catch (SQLException e){
e.printStackTrace();
}
}
return cnt;
}
public void setMetrics(String mod, String act, String client_info) {
try {
Properties p = new Properties();
p.put("OCSID.MODULE" , mod);
p.put("OCSID.ACTION" , act);
p.put("OCSID.CLIENTID", client_info);
conn.setClientInfo(p);
} catch (SQLClientInfoException e) {
//Ignore any errors
}
}
public void resetMetrics() {
try {
Properties p = new Properties();
p.put("OCSID.MODULE" , "");
p.put("OCSID.ACTION" , "");
p.put("OCSID.CLIENTID", "");
conn.setClientInfo(p);
} catch (SQLClientInfoException e) {
//Ignore any errors
}
}
}
Before you run the application again, login to the database as SYSTEM and run “alter system flush shared_pool” (so the SQL are parsed again).
Run the application again, and then go back to the database and query your v$session and v$sql (Note! The output below is from SQL Developer):
SQL> select module, action , s.buffer_gets, s.disk_reads, s.executions, s.sql_text 2 > from v$sql s where module='SimpleJdbcDemo';
You could also run queries towards v$session filtering on module and action, but you will probably not be able to catch them before the connection towards the database is closed. When the connection closes the metrics are also reset to empty strings. In a production environment this could be very convenient. Often when DBAs are querying v$session they see hundreds of sessions where some might be active, without knowing what kind of code is running in these sessions. When you set the Oracle end-to-end metrics, the DBA could very easy see what kind of code which is running at the moment.
The real strength is when you can combine the Oracle End-to-end Metrics with Oracle Trace. Using Oracle extended trace we can turn on trace using the Oracle end-to-end metrics (read: the module and action tags).
As a DBA user you can run the following to turn on trace for a certain module and action:
SQL> exec dbms_monitor.serv_mod_act_trace_enable(service_name=> 'orcl',module_name=>'SimpleJdbcDemo',action_name=>'updateSalary',waits=>TRUE, binds=>TRUE, plan_stat=>'ALL_EXECUTIONS'); PL/SQL procedure successfully completed. SQL> SELECT * FROM dba_enabled_traces; TRACE_TYPE PRIMARY_ID QUALIFIER_ID1 QUALIFIER_ID2 WAITS BINDS PLAN_STATS INSTANCE_NAME --------------------- -------------------- ------------------------- ------------------------- ----- ----- ---------- ---------------- SERVICE_MODULE_ACTION orcl SimpleJdbcDemo updateSalary TRUE TRUE ALL_EXEC
If we now go and run the application again, this should trigger trace to be written to one of the files in the trace file location. If we go to the docker console, and do a ls -l in /opt/oracle/diag/rdbms/cdborcl/CDBORCL/trace we see:
$ ls -ltr
...
-rw-r----- 1 oracle dba 3959952 Jul 20 22:58 alert_CDBORCL.log
-rw-r----- 1 oracle dba 40477 Jul 20 22:58 CDBORCL_vktm_27.trm
-rw-r----- 1 oracle dba 333809 Jul 20 22:58 CDBORCL_vktm_27.trc
-rw-r----- 1 oracle dba 925 Jul 20 23:00 CDBORCL_m000_13891.trm
-rw-r----- 1 oracle dba 1141 Jul 20 23:00 CDBORCL_m000_13891.trc
-rw-r----- 1 oracle dba 2277 Jul 20 23:54 CDBORCL_ora_14383.trm
-rw-r----- 1 oracle dba 6435 Jul 20 23:54 CDBORCL_ora_14383.trc
-rw-r----- 1 oracle dba 200033 Jul 20 23:56 CDBORCL_dbrm_47.trm
-rw-r----- 1 oracle dba 2282473 Jul 20 23:56 CDBORCL_dbrm_47.trc
-rw-r----- 1 oracle dba 2925554 Jul 20 23:56 CDBORCL_gen0_31.trm
-rw-r----- 1 oracle dba 21594795 Jul 20 23:56 CDBORCL_gen0_31.trc
$ cat CDBORCL_ora_14383.trc
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
Build label: RDBMS_12.2.0.1.0_LINUX.X64_170125
ORACLE_HOME: /opt/oracle/product/12.2.0.1/dbhome_1
System name: Linux
Node name: 1408a090d766
Release: 4.9.87-linuxkit-aufs
Version: #1 SMP Wed Mar 14 15:12:16 UTC 2018
Machine: x86_64
Instance name: CDBORCL
Redo thread mounted by this instance: 1
Oracle process number: 8
Unix process pid: 14383, image: oracle@1408a090d766
*** 2018-07-20T23:54:21.013860+00:00 (ORCL(3))
*** SESSION ID:(16.62711) 2018-07-20T23:54:21.013928+00:00
*** CLIENT ID:(1970416f-cb45-4e2f-bdf4-2176b177771e) 2018-07-20T23:54:21.013945+00:00
*** SERVICE NAME:(orcl) 2018-07-20T23:54:21.013958+00:00
*** MODULE NAME:(SimpleJdbcDemo) 2018-07-20T23:54:21.013970+00:00
*** ACTION NAME:(updateSalary) 2018-07-20T23:54:21.013982+00:00
*** CLIENT DRIVER:(jdbcthin : 12.2.0.1.0) 2018-07-20T23:54:21.013994+00:00
*** CONTAINER ID:(3) 2018-07-20T23:54:21.014004+00:00
=====================
PARSING IN CURSOR #140255886572368 len=44 dep=0 uid=132 oct=6 lid=132 tim=297159830358 hv=584616541 ad='68c9d588' sqlid='86pda78jdj2kx'
UPDATE person SET salary = :1 WHERE id= :2
END OF STMT
PARSE #140255886572368:c=0,e=648,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,plh=0,tim=297159830330
=====================
PARSING IN CURSOR #140255886557144 len=97 dep=2 uid=0 oct=3 lid=0 tim=297159834834 hv=791757000 ad='68b385c8' sqlid='87gaftwrm2h68'
select o.owner#,o.name,o.namespace,o.remoteowner,o.linkname,o.subname from obj$ o where o.obj#=:1
END OF STMT
PARSE #140255886557144:c=0,e=450,p=0,cr=0,cu=0,mis=1,r=0,dep=2,og=4,plh=0,tim=297159834830
WAIT #140255886557144: nam='PGA memory operation' ela= 16 p1=65536 p2=1 p3=0 obj#=-1 tim=297159835280
WAIT #140255886557144: nam='PGA memory operation' ela= 16 p1=65536 p2=2 p3=0 obj#=-1 tim=297159836244
WAIT #140255886557144: nam='PGA memory operation' ela= 10 p1=65536 p2=1 p3=0 obj#=-1 tim=297159836529
BINDS #140255886557144:
Bind#0
oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
oacflg=00 fl2=1000001 frm=00 csi=00 siz=24 off=0
kxsbbbfp=7f8fde4bb138 bln=22 avl=01 flg=05
value=0
EXEC #140255886557144:c=10000,e=5761,p=0,cr=0,cu=0,mis=1,r=0,dep=2,og=4,plh=1072382624,tim=297159840690
FETCH #140255886557144:c=0,e=49,p=0,cr=2,cu=0,mis=0,r=0,dep=2,og=4,plh=1072382624,tim=297159840765
STAT #140255886557144 id=1 cnt=0 pid=0 pos=1 obj=18 op='TABLE ACCESS BY INDEX ROWID BATCHED OBJ$ (cr=2 pr=0 pw=0 str=1 time=56 us cost=3 size=112 card=1)'
STAT #140255886557144 id=2 cnt=0 pid=1 pos=1 obj=36 op='INDEX RANGE SCAN I_OBJ1 (cr=2 pr=0 pw=0 str=1 time=40 us cost=2 size=0 card=1)'
CLOSE #140255886557144:c=0,e=6,dep=2,type=3,tim=297159841370
=====================
PARSING IN CURSOR #140255886559576 len=444 dep=1 uid=132 oct=3 lid=132 tim=297159841439 hv=1768462559 ad='635042e0' sqlid='c5dy4mtnqj66z'
SELECT /* OPT_DYN_SAMP */ /*+ ALL_ROWS IGNORE_WHERE_CLAUSE RESULT_CACHE(SNAPSHOT=3600) opt_param('parallel_execution_enabled', 'false') NO_PARALLEL(SAMPLESUB) NO_PARALLEL_INDEX(SAMPLESUB) NO_SQL_TUNE */ NVL(SUM(C1),0), NVL(SUM(C2),0) FROM (SELECT /*+ IGNORE_WHERE_CLAUSE NO_PARALLEL("PERSON") FULL("PERSON") NO_PARALLEL_INDEX("PERSON") */ 1 AS C1, CASE WHEN "PERSON"."ID"=:B1 THEN 1 ELSE 0 END AS C2 FROM "DEMODATA"."PERSON" "PERSON") SAMPLESUB
END OF STMT
PARSE #140255886559576:c=10000,e=7706,p=0,cr=2,cu=0,mis=1,r=0,dep=1,og=1,plh=0,tim=297159841435
BINDS #140255886557144:
Bind#0
oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
oacflg=00 fl2=1000001 frm=00 csi=00 siz=24 off=0
kxsbbbfp=7f8fde4bb138 bln=22 avl=01 flg=05
value=0
EXEC #140255886557144:c=0,e=99,p=0,cr=0,cu=0,mis=0,r=0,dep=2,og=4,plh=1072382624,tim=297159843045
FETCH #140255886557144:c=0,e=28,p=0,cr=2,cu=0,mis=0,r=0,dep=2,og=4,plh=1072382624,tim=297159843093
STAT #140255886557144 id=1 cnt=0 pid=0 pos=1 obj=18 op='TABLE ACCESS BY INDEX ROWID BATCHED OBJ$ (cr=2 pr=0 pw=0 str=1 time=35 us cost=3 size=112 card=1)'
STAT #140255886557144 id=2 cnt=0 pid=1 pos=1 obj=36 op='INDEX RANGE SCAN I_OBJ1 (cr=2 pr=0 pw=0 str=1 time=20 us cost=2 size=0 card=1)'
CLOSE #140255886557144:c=0,e=6,dep=2,type=3,tim=297159843198
WAIT #140255886559576: nam='PGA memory operation' ela= 16 p1=65536 p2=1 p3=0 obj#=-1 tim=297159843288
WAIT #140255886559576: nam='PGA memory operation' ela= 8 p1=65536 p2=1 p3=0 obj#=-1 tim=297159843357
BINDS #140255886559576:
Bind#0
oacdty=02 mxl=22(02) mxlc=00 mal=00 scl=00 pre=00
oacflg=10 fl2=0001 frm=00 csi=00 siz=24 off=0
kxsbbbfp=7f8fde3aaf78 bln=22 avl=02 flg=05
value=3
EXEC #140255886559576:c=0,e=1478,p=0,cr=2,cu=0,mis=1,r=0,dep=1,og=1,plh=1154882994,tim=297159843440
FETCH #140255886559576:c=0,e=36,p=0,cr=0,cu=0,mis=0,r=1,dep=1,og=1,plh=1154882994,tim=297159843494
STAT #140255886559576 id=1 cnt=1 pid=0 pos=1 obj=0 op='RESULT CACHE 9z4qk6ak7d9dydktdj11cjryds (cr=0 pr=0 pw=0 str=1 time=28 us)'
STAT #140255886559576 id=2 cnt=0 pid=1 pos=1 obj=0 op='SORT AGGREGATE (cr=0 pr=0 pw=0 str=0 time=0 us)'
STAT #140255886559576 id=3 cnt=0 pid=2 pos=1 obj=76108 op='TABLE ACCESS FULL PERSON (cr=0 pr=0 pw=0 str=0 time=0 us cost=3 size=5317 card=409)'
CLOSE #140255886559576:c=0,e=7,dep=1,type=0,tim=297159843605
BINDS #140255886572368:
Bind#0
oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
oacflg=03 fl2=1000000 frm=01 csi=873 siz=48 off=0
kxsbbbfp=7f8fde4be350 bln=22 avl=02 flg=05
value=3000
Bind#1
oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
oacflg=03 fl2=1000000 frm=01 csi=873 siz=0 off=24
kxsbbbfp=7f8fde4be368 bln=22 avl=02 flg=01
value=3
EXEC #140255886572368:c=10000,e=11261,p=0,cr=12,cu=3,mis=1,r=1,dep=0,og=1,plh=2144681027,tim=297159844221
STAT #140255886572368 id=1 cnt=0 pid=0 pos=1 obj=0 op='UPDATE PERSON (cr=7 pr=0 pw=0 str=1 time=163 us)'
STAT #140255886572368 id=2 cnt=1 pid=1 pos=1 obj=76108 op='TABLE ACCESS FULL PERSON (cr=7 pr=0 pw=0 str=1 time=55 us cost=3 size=26 card=1)'
XCTEND rlbk=0, rd_only=0, tim=297159844328
WAIT #140255886572368: nam='log file sync' ela= 733 buffer#=349 sync scn=6151572 p3=0 obj#=-1 tim=297159845154
WAIT #140255886572368: nam='SQL*Net message to client' ela= 5 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=297159845237
WAIT #140255886572368: nam='SQL*Net message from client' ela= 1058 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=297159846325
CLOSE #140255886572368:c=0,e=7,dep=0,type=0,tim=297159846412
Here we can actually see what has been going on towards the database from the application. Since I did a “flush shared_pool” before I ran the application again, we see all the recursive calls which happened because the UPDATE went through a hard parse. But that is enough about Oracle trace today.
To turn of trace again, run the following as a DBA user:
SQL> exec dbms_monitor.serv_mod_act_trace_disable(service_name=> 'orcl',module_name=>'SimpleJdbcDemo',action_name=>'updateSalary'); PL/SQL procedure successfully completed.
That was all for today! I’ll soon be back with the next blog post in this series.
Post a Comment