A Beginner's guide to Java Data Base Connectivity

Java Database connectivity (JDBC)

To understand the JDBC, you should have the basic knowledge of core java and SQL. If we are developing java project, we may have the need to connect it with database.

Connecting Java program to Database

If we take Banking app for example, they store the transaction details and account information. In java they usually store in Array but it is only on memory and lost after the execution and they need to update fresh data again. In order to store permanently and retrieve the data whenever we want, we need database which can be relational databases like db2 oracle, NoSQL etc.

Sample query of employee table in SQL

We are trying to insert details from a Java program, if we use string which will not be understood by SQL because it knows only varchar which will not be understood by Java. So we have to write something that should be understood by both Java and SQL. This is where JDBC comes in use.

For example : this "select * from employee" in SQL, Java cant understand the table so to interact, we should change it accordingly. we need databases like MySQL, pre-sql, oracle for the Java program to interact with database

We need interface which is an API that helps to connect between two software or hardware. JDBC is the interface between java program and data base.

JDBC API (Application Programming Interface)

JDBC software plays an important role in connecting the two system software and acts like a bridge between them. Java --JDBC --> SQL, Jdbc not only supports SQL but also multiple relational Databases like Oracle, which has separate driver and logic. If we give URL, Username and password it connects the database according to the URL.

Types Of Drivers

Eliminating all drivers and making most modern databases implement or provide a type 4 driver which is Thin Driver making it a common for Java applications.

This has its own several advantages of

• Not installing drivers in client machine.

• Better performance

• Complete Java implementation

• Direct Database communication

Prerequisite

Download the MySQL JDBC Driver

We have to download the MySQL JDBC (JAR file) to enable your Java application to connect to a MySQL database.

Follow the steps given below:

• Visit the JDBC MySQL jar link

• Download the JAR file (always choose the latest version)

Select Platform Independent

Select ZIP Archive

Select No thanks, just start my download

After Downloading

• Go to explorer and right click the file and extract it.

• You will see the MySQL jar which we are adding to the project.

• Do a right click on the project and show in explorer and copy paste the jar file.

• Refresh it.

• Add it to build bath.

Include the JAR File in Your Project

• If you're using an IDE (e.g., IntelliJ IDEA, Eclipse, VS Code):

• Add the downloaded JAR file to your project's classpath.

• For Maven-based projects add the following dependency in your pom.xml

Verify the Classpath

Ensure the JAR file is correctly added to your classpath. Without this, your application cannot establish a connection to MySQL.

Creating a JDBC connection using Java Class

To create a connection, we need to define the connection properties and these are the connection for MySQL which includes JDBC URL Username and password.

Establishing a connection

When you are establishing a connection, we use DriverManager for connection to the data base. This step ensures the communication link between your java application and MySQL Database is established.

Once the connection is established, you can execute SQL queries using the Statement

Connection con = DriverManager.getConnection(url,userName,passWord);

Example:

Execute SQL queries like SELECT * FROM employees

Let's create data base employee with id name and salary in MySQL

First we will define the query

Execute the Query

Use st.execute(query) to execute the SQL query, where query is a variable containing your SQL statement. This automatically executes the query and returns a ResultSet.

Process the ResultSet

The ResultSet is created automatically upon execution of a query. It contains the results of the query. Use rs.next() to move to the next record (row) in the result set. For each record, you can access the data in its columns using appropriate getter methods.

Access Data

• Here’s an example of how to access different column data:

• rs.getInt(columnIndex) gets an integer from the specified column.

• rs.getString(columnIndex) gets a string from the specified column.

While loop

If we have more rows, we have to give it in a while loop to access more than 1 row.

Closing the Connection

Once all records are processed, close the ResultSet and database connection to release resources. Always close resources like ResultSet and Connection to avoid memory leaks.

Compile the program

Compile and run the project to execute the SQL query and display the results. Structured approach by putting all steps together looks like below example code

Execute the program for an expected output below

Example using Insert

To insert a record into the database, you can create a separate method in the same class, for example, insertRecord. This method will use the same Connection and Statement objects to interact with the database. The query should be updated to an INSERT statement where values are passed directly in the parentheses, which ensures that double quotes around string values like name are removed. Since INSERT would modify the database, you should use the executeUpdate method instead of executeQuery.

The executeUpdate method returns an integer which indicates the number of rows affected by the query. In the main method, you can call the insertRecord method to perform the insertion. After running the program, it will update the database with the specified values, and you can print a message which will confirm the number of rows successfully inserted.

To insert records without hardcoding values, create a method like insertRecord that takes parameters for the values to insert. In the main method, define variables for the data (e.g., id, name, and salary) and pass them to the insertRecord method. Inside the method, an INSERT SQL query is created using these variables. The executeUpdate method runs the query, updates the database, and returns the number of rows affected. After running the program, the database is updated, and a confirmation message shows the number of rows inserted.

To insert records using a PreparedStatement, you can use placeholders (?) in the SQL query instead of hardcoding values. For example, the query can be written as INSERT INTO employees (id, name, salary) VALUES (?, ?, ?). This approach reduces manual entry and protects against SQL injection. You assign values to the placeholders using specific methods based on the data type, such as setInt for integers, setString for strings, and setDouble for decimal values.

To execute the query, use the executeUpdate method, which inserts the record and returns the number of rows affected. In the main method, you define the variables for the record you want to insert, pass them to the insertion method, and run the program to update the database. This approach is efficient and easy to maintain.

To perform all DML (Data Manipulation Language) operations in Java using JDBC, you can create separate methods for operations such as UPDATE, DELETE, and SELECT just like how we created for INSERT above. These operations are responsible for modifying and retrieving data from the database. Each method will use a PreparedStatement or Statement object depending on the need, and the operations can be executed with either executeUpdate (for INSERT, UPDATE, DELETE) or executeQuery (for SELECT).

May the queries be ever in your favor - thanks to JDBC!