Oracle Database Java JDBC Tutorial: #2 – Setup Dev Environment

In this video we will learn how to setup our development environment for JDBC work. You will learn how to connect Java to an Oracle database with JDBC.

Please SUBSCRIBE to this channel

Download Java Source Code

File: table-setup.sql

--
-- DROP TABLE IF EXISTS
--
declare
c int;
begin
select count(*) into c from user_tables where table_name = upper('employees');
if c = 1 then
execute immediate 'drop table employees';
end if;
end;
/
--
-- CREATE TABLE
--
CREATE TABLE employees (
id integer PRIMARY KEY,
last_name varchar(64) DEFAULT NULL,
first_name varchar(64) DEFAULT NULL,
email varchar(64) DEFAULT NULL,
department varchar(64) DEFAULT NULL,
salary DECIMAL(10,2) DEFAULT NULL
);
-- DROP SEQUENCE IF EXISTS
declare
c int;
begin
select count(*) into c from user_sequences where sequence_name = upper('employees_sequence');
if c = 1 then
execute immediate 'drop sequence employees_sequence';
end if;
end;
CREATE SEQUENCE employees_sequence;
CREATE OR REPLACE TRIGGER employees_trigger
BEFORE INSERT ON employees
FOR EACH ROW
BEGIN
SELECT employees_sequence.NEXTVAL
INTO :new.id
FROM dual;
END;
/
INSERT INTO employees (id,last_name,first_name,email, department, salary) VALUES (1,'Doe','John','john.doe@foo.com', 'HR', 55000.00);
INSERT INTO employees (id,last_name,first_name,email, department, salary) VALUES (2,'Public','Mary','mary.public@foo.com', 'Engineering', 75000.00);
INSERT INTO employees (id,last_name,first_name,email, department, salary) VALUES (3,'Queue','Susan','susan.queue@foo.com', 'Legal', 130000.00);
INSERT INTO employees (id,last_name,first_name,email, department, salary) VALUES (4,'Williams','David','david.williams@foo.com', 'HR', 120000.00);
INSERT INTO employees (id,last_name,first_name,email, department, salary) VALUES (5,'Johnson','Lisa','lisa.johnson@foo.com', 'Engineering', 50000.00);
INSERT INTO employees (id,last_name,first_name,email, department, salary) VALUES (6,'Smith','Paul','paul.smith@foo.com', 'Legal', 100000.00);
INSERT INTO employees (id,last_name,first_name,email, department, salary) VALUES (7,'Adams','Carl','carl.adams@foo.com', 'HR', 50000.00);
INSERT INTO employees (id,last_name,first_name,email, department, salary) VALUES (8,'Brown','Bill','bill.brown@foo.com', 'Engineering', 50000.00);
INSERT INTO employees (id,last_name,first_name,email, department, salary) VALUES (9,'Thomas','Susan','susan.thomas@foo.com', 'Legal', 80000.00);
INSERT INTO employees (id,last_name,first_name,email, department, salary) VALUES (10,'Davis','John','john.davis@foo.com', 'HR', 45000.00);
INSERT INTO employees (id,last_name,first_name,email, department, salary) VALUES (11,'Fowler','Mary','mary.fowler@foo.com', 'Engineering', 65000.00);
INSERT INTO employees (id,last_name,first_name,email, department, salary) VALUES (12,'Waters','David','david.waters@foo.com', 'Legal', 90000.00);
commit;

File: JdbcTest.java

import java.sql.*;
/**
* 
* @author www.luv2code.com
*
*/
public class JdbcTest {
public static void main(String[] args) throws SQLException {
Connection myConn = null;
Statement myStmt = null;
ResultSet myRs = null;
try {
// 1. Get a connection to database
myConn = DriverManager.getConnection(
"jdbc:oracle:thin:@localhost:1521:xe", "student", "student");
System.out.println("Database connection successful!\n");
// 2. Create a statement
myStmt = myConn.createStatement();
// 3. Execute SQL query
myRs = myStmt.executeQuery("select * from employees");
// 4. Process the result set
while (myRs.next()) {
System.out.println(myRs.getString("last_name") + ", " 
+ myRs.getString("first_name"));
}
}
catch (Exception exc) {
exc.printStackTrace();
}
finally {
if (myRs != null) {
myRs.close();
}
if (myStmt != null) {
myStmt.close();
}
if (myConn != null) {
myConn.close();
}
}
}
}

Did you like my blog post?

Signup now and receive an email when I publish new luv2code content.

I will never give away, trade or sell your email address. You can unsubscribe at any time.

Share

Leave a Reply