Steps to Set Up and Access Oracle Database with Java

SQL vs. NoSQL

Let's explore the key differences between SQL and NoSQL databases in a concise yet comprehensive way.

Database Basics

• SQL (Structured Query Language) is a standard language for managing relational databases. It was developed in the 1970s and has been widely adopted for handling structured data stored in tables with rows and columns. SQL allows for complex querying, data manipulation, and relational operations, ensuring data consistency through ACID properties (Atomicity, Consistency, Isolation, Durability).
• NoSQL stands for "Not Only SQL" and provides a more flexible approach to data management by supporting unstructured and semi-structured data models. Introduced in 2011, NoSQL databases handle scalability and dynamic schema requirements, making them ideal for modern applications requiring flexibility and high performance in handling vast amounts of data.

Head-To-Head Comparison

SQL

• Structured Data Model: SQL databases organize data in well-defined tables with relationships, enabling complex queries and transactions.
• Vertically Scaling: Typically scales by upgrading hardware on a single server.
• ACID Compliant: Ensures reliable transactions with properties such as Atomicity, Consistency, Isolation, and Durability.
• Uses SQL as the Language: Provides a standardized approach to interacting with relational databases.
• Minimized Data Duplication: Rigid schemas help to reduce redundancy in data storage.
• Best for Stable, Change-Resistant Projects: Ideal for applications that require consistent data and complex relationships between data sets.

NoSQL

• Unstructured or Semi-Structured Data Model: NoSQL supports flexible schemas for dynamic and evolving data structures like JSON, XML, and more.
• Horizontally Scaling: Designed to scale horizontally by adding nodes or servers to handle increased load.
• Fast Read-Write Performance: Optimized for high-speed performance and real-time data processing.
• Uses JSON, XML, or YAML: These formats are commonly used for data storage and retrieval.
• Good for Big Data and Analytics: Best suited for applications requiring frequent changes and handling massive, semi-structured data sets.
• Examples: MongoDB, Cassandra, Redis.

Key Differences

• SQL databases are ideal for managing structured, relational data and applications that demand complex queries and precise data relationships. Examples include MySQL, PostgreSQL, and Oracle DB.
• NoSQL databases provide flexibility for unstructured or semi-structured data, excelling in scalability and handling real-time, dynamic data needs.

In summary, SQL offers robust support for structured data management, while NoSQL provides the agility needed for modern, scalable, and data-driven applications.

Setting Up Oracle DB

Create a GCP VM:

• Create a virtual machine instance on Google Cloud Platform with a Linux-based OS (e.g., Ubuntu or CentOS).
• Ensure the VM has sufficient resources for running Oracle, including CPU, RAM, and disk space.
• Download Docker: Ensure Docker is installed on your Linux VM.

$ sudo apt update
$ sudo apt install docker.io -y
$ sudo systemctl enable --now docker        

• Cloning Oracle Repo

Clone the Oracle Docker images repo:

$ git clone https://github.com/oracle/docker-images.git        

• build the Docker image.

I will be using OracleDatabase v 18.4.0:

$ cd docker-images/OracleDatabase/SingleInstance/dockerfiles

$ ./buildContainerImage.sh -v 18.4.0 -x        

• Run the Docker Image

$ docker run --name oraclexe -d -p 1521:1521 -p 5500:5500 -e ORACLE_PWD=ORACLE oracle/database:18.4.0-xe        

Connecting to the DB:

1. Download Oracle SQL Developer.

Create a new connection with the following credentials:

Username: yourusername

Password:yourpassword

Host: <VM-external-IP>

Port: 1521

SID: XE

Role: defualt

Create the BOOK Table

CREATE TABLE BOOK (
 ID NUMBER, 
NAME VARCHAR2(128), I
SBN VARCHAR2(32), 
CREATE_DATE DATE DEFAULT SYSDATE 
);        

Write a Java Application to Insert Records

Write a simple Java application to insert 100 random records into the BOOK table.

you can check my code from this Github repository

Verify

From Sql developer review the table

SELECT * FROM BOOK;        

Conclusion

Setting up an Oracle Database on a GCP VM using Docker is a modern and efficient approach to quickly deploy and manage a robust RDBMS. With Docker, you eliminate the need for complex installations, as it provides a pre-configured environment for Oracle Database.

This setup is highly portable, scalable, and ideal for development, testing, or even production workloads. By leveraging Docker and GCP, you can take advantage of Oracle’s enterprise-grade features while maintaining flexibility and cost-effectiveness.