Introduction to JDBC

305.2 - Introduction to Java DataBase Connectivity

Learning Objectives:

In this presentation, we will explore how to connect MySql Database through a Java Class. By the end of this session, learners will be able to:

• Make a MySqL Database connection through a Java Class.

• Write an SQL query from within a Java class.

• Access records from the Database Table through Java Class with user input values.

Introduction to Java DataBase Connectivity

Table of Contents

• Java Database Connectivity Driver/API.

• JDBC Basic Architecture.

• Download/Install MySQL JDBC Driver.

• Java Database Connectivity Steps:

  • Load database driver.

  • Open database connection.

  • Send statements to the database.

  • Common methods of Statement Interface

  • Response from database.

  • Close database connection.

• Overview of Prepared Statements.

• Adding Placeholders to the Statement.

• Prepared Statement With DML.

• Separate class for Queries.

Java Database Connectivity Driver/API

A Java Database Connectivity (JDBC) Driver/Application Programming Interface (API) manages connections to a database issuing queries and commands and handles result sets obtained from the database.

An API is Java’s solution to the problems; it allows for programmatic interaction with a database:

• Initial connection.

• Querying (SQL).

• Inserting, updating, and deleting of data (DML).

• Creating, updating, and deleting of tables (DDL).

JDBC Driver classes are available in the java.sql package. JDBC/API:

• A software component enabling a Java application to interact with a database.

• Implements the protocol for transferring the query and the result between client and database.

• Allows for development of an application that can send SQL statements to different data sources.

JDBC Basic Architecture

!https://dotnettutorials.net/wp-content/uploads/2019/07/JDBC-Architecture.png

Download / Install MySQL JDBC Driver

We can download and install MySQL JDBC driver two ways:

1. Using Maven Dependency.

2. Using Jar File (need installation steps).

Download MySQL JDBC Driver Using Maven

The MySQL Driver is available on Maven Central repository. Copy the MySQL JDBC driver maven dependency below and paste it in your project pom.xml file:

<!-- <https://mvnrepository.com/artifact/mysql/mysql-connector-java> -->
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>8.0.18</version>
</dependency>

Download MySQL JDBC Driver Using Jar File

1. Click here to download MySql JDBC driver.

2. Click on the Download button next to Platform Independent (Architecture Independent), ZIP Archive to download a zip archive.

Prerequisites

We will use the classicmodels database as an SQL sample database for the JDBC demonstration to help work with JDBC - SQL effectively.

The classicmodels database is a retailer of scale models of classic cars. It contains typical business data such as customers, products, sales orders, and sales order line items, etc. See the schema diagram to the left.

If you do not have the classicmodels database, you can find GLab - 304.1.2 - Download Classicmodels” Database” on Canvas under the Guided Lab section.

Java Database Connectivity Steps

1. Load database driver.

2. Open the database connection.

   1. Database URL.

   2. Username.

   3. Password.

3. Send the Statement to the database.

   1. Use Statement Class or Preparedstatement class.

4. Get the results from the database.

5. Close the database connection.

Load Database Driver

Step 1 - explicitly by this statement:

Class.forName("com.mysql.jdbc.Driver");

But before Java 6, we had to load the driver:

Class.forName("com.mysql.jdbc.Driver");

The easiest way to load the database driver is to use the class that implements the java.sql.Driver interface. That statement is no longer needed, thanks to the new update in JDBC 4.0, which is included in Java 6. As long as you put the MySQL JDBC driver JAR file into your program’s classpath, the driver manager can find and load the database driver.

Class.forName ("com.mysql.jdbc.Driver")

Open Database Connection

Step 2 -

JDBC driver classes include the DriverManager class, this class provides getConnection() method for obtaining a connection instance.

There are three different signatures of the method getConnection() that we can use:

• static Connection getConnection(String url).

• static Connection getConnection(String url, Properties info).

• static Connection getConnection(String url, String dbuser, String dbpassword).

Note that all three versions have a parameter called url, which is the database URL.

If a connection was made successfully with the database, the getConnection() method returns an instance of Connection class, which will be used to make queries and perform other database operations.

Connection connection = DriverManager.getConnection(dburl, user, password);

JDBC includes a Connection Class: A Java object that represents a unique connection to a database; often opened by using the methods of the DriverManager class before you can read/write data from/to a database using JDBC.

Allows for storing Database Connection as a variable.

Connection created via DriverManager.

Example:

public static void main(String[] args) throws SQLException {
        String dburl = "jdbc:mysql://localhost:3306/classicmodels";
        String user = "root";
        String password = "password";
        try {
            Class.forName("com.mysql.cj.jdbc.Driver"); // optional
            Connection connection = DriverManager.getConnection(dburl, user, password);
        }
        catch(SQLException e) {
            e.printStackTrace();
        }
    }

Send Statements to the Database

Step 3 -

JDBC Statement:

• The JDBC Statement is an interface used to execute an SQL query within the database.

• A Java object that can be used to execute either database queries or database updates when using JDBC.

• DriverManager -> Connection -> Statement

Example - Creating a Statement and executing a Query:

String SelectSQL = "Select * FROM employees";
Statement stmt = conn.createStatement();
ResultSet result = stmt.executeQuery(SelectSQL);

Note: You can use DML (INSERT, UPDATE, DELETE) queries as well.

Common Methods of Statement Interface

Commonly Used Methods:

• boolean execute (String sql): executes a general SQL statement. It returns true if the query returns a ResultSet, and returns false if the query returns an update count or nothing. This method can be used with a Statement only.

• int executeUpdate (String sql): executes an INSERT, UPDATE, or DELETE statement and returns an update account indicating the number of rows affected (e.g., 1 row inserted, 2 rows updated, or 0 rows affected).

• ResultSet executeQuery (String sql): executes a SELECT statement and returns a ResultSet object, which contains results returned by the query.

Response From Database

Step 4 -

A JDBC ResultSet is a Java object that can be created by executing a database query using JDBC that contains records returned by the execution of that query.

Use this object to iterate over rows in the result set using the next() method and get the value of a column in the current row using getXXX() methods (e.g. getString(), getInt(), getFloat(), and so on). The column value can be retrieved either by index number (1-based) or by column name.

Example: executing a Query against a database via JDBC:

ResultSet result = stmt.executeQuery(SelectSQL);
while(result.next()) {
    String name = result.getString("firstName");
    String email = result.getString("email");
    System.out.println(name +" | " + email);
}

JDBC ResultSet Class object.

• Returned from Statement.executeQuery().

• List of output data.

• Separated into columns.

Looping through data.

• ResultSet.next().

• ResultSet.getType.

• Use column number (starts at 1).

• Use column header (i.e.,: “student_id”).

• Replace [Type] with actual type.

  • getString(), getInt(), etc.

Close Database Connection

Step 5-

When you have completed the JDBC database connection, close the connection again. A JDBC connection can use a lot of sources inside your application and inside the database server. Therefore, it is important to close the database connection after use. Close the JDBC connection via its close() method:

connection.close();

Why close a JDBC connection?

• If not closed, it would lead to memory leaks.

• The connection may remain active even after the user logs out.

• The database server would not be able to provide connections for new requests after it reaches its maximum number of simultaneous connections.

• It would lead to slow performance.

• Eventually, the database server will crash.

Example 1: Java Connection With MySQL

Create a class named DemoJDBC, and write the code in the class:

import java.sql.*;

public class DemoJDBC {
    public static void main(String[] args) throws ClassNotFoundException {
        String dburl = "jdbc:mysql://localhost:3306/classicmodels";
        String user = "root";
        String password = "password";
        System.out.println("-------- MySQL JDBC Connection Demo ------------");
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            Connection connection = DriverManager.getConnection(dburl, user, password);
            String SelectSQL = "Select * FROM employees";
            Statement stmt = connection.createStatement();
            ResultSet result = stmt.executeQuery(SelectSQL);
            while(result.next()) {
                String EmployeeID = result.getString("employeeNumber");
                String fname = result.getString("firstName");
                String lname = result.getString("lastName");
                System.out.println(EmployeeID +" | " + fname + "|"+ lname );
            }
            connection.close();
        }
        catch(SQLException e) {
            e.printStackTrace();
        }
    }
}

Overview of Prepared Statements

A JDBC PreparedStatement is a special kind of Java JDBC Statement object with some useful added features. Remember, you need a statement in order to execute either a query or an update. You can use a Java JDBC PreparedStatement instead of a statement, and benefit from its added features:

• Makes it easier to set SQL parameter values.

• Accepts a query in constructor.

• Prevents SQL dependency injection attacks (can safely insert variables into SQL).

• Improves application performance. (When used for multiple executions of the same query, the performance of the PreparedStatement interface is better than the Statement interface.)

• Includes precompiled SQL statements (you can pass parameters to your SQL query at run-time).

Using Prepared Statements

Adding Placeholders to the Statement

PreparedStatement set variables:

• Use PreparedStatement.set[Type].

• Replaces ? with data.

• Accepts position and data.

Creating PreparedStatement

Methods of PreparedStatement Interface

The Important Methods of PreparedStatement Interfaces:

Method	Description
public void setInt(int paramIndex, int value)	Sets the integer value to the given parameter index.
public void setString(int paramIndex, String value)	Sets the String value to the given parameter index.
public void setFloat(int paramIndex, float value)	Sets the float value to the given parameter index.
public void setDouble(int paramIndex, double value)	Sets the double value to the given parameter index.
public int executeUpdate()	The executeUpdate(String SQL) method is most often used to execute DML statements (INSERT, UPDATE, and DELETE), and it returns an representing the count of the rows affected by its execution.
public ResultSet executeQuery()	The executeQuery(String SQL) method is used to retrieve data from the database by executing a DQL(SELECT ) statement and it returns a ResultSet Object containing data requested by executed SQL statement.

Example 1: Prepared Statements

import java.sql.*;
import java.sql.PreparedStatement;

public class ExamplePrepareStat{
    public static void main (String[] args) throws Exception {
        Class.forName("com.mysql.cj.jdbc.Driver");
        String url = "jdbc:mysql://localhost:3306/classicmodels";
        final String USER = "root";
        final String PASS = "password";
        Connection conn = DriverManager.getConnection(url, USER , PASS );
        String SelectSQL = "select * from employees where employeeNumber = ?";
        PreparedStatement mystmt = conn.prepareStatement(SelectSQL);
        mystmt.setInt(1, 1002);
        ResultSet result = mystmt.executeQuery();
        while(result.next()) {
            String name = result.getString("firstName");
            String email = result.getString("email");
            System.out.println(name +" | " + email);
        }
    }
}

In this example, we will demonstrate how to use Prepared Statements to access records and get employee information by using employee id.

Prepared Statement With DML

We can use Prepared Statement for:

• Insert statements.

• Update statements.

• Delete statements.

Example 1: Prepared Statement with Insert Statement

String sqlQuery = "INSERT INTO EMPLOYEES (officeCode,firstName,lastName,email,extension,reportsTo,VacationHours,employeeNumber,jobTitle) VALUES (?,?,?,?,?,?,?,?,?)";
prepStmt = con.prepareStatement(sqlQuery);
prepStmt.setInt(1, 6);
prepStmt.setString(2, "Jamil");
prepStmt.setString(3, "fink");
prepStmt.setString(4, "[e-mail]");
prepStmt.setString(5, "2759");
prepStmt.setInt(6, 1143);
prepStmt.setInt(7, 9);
prepStmt.setInt(8, 0003);
prepStmt.setString(9, "Manager");
int affectedRows = prepStmt.executeUpdate();
System.out.println(affectedRows + " row(s) affected !!");

Example 2: Prepared Statement with Update Statement

String SelectSQL = "update employees set firstName=? , lastName=? where employeeNumber=?";
PreparedStatement mystmt = conn.prepareStatement(SelectSQL);
mystmt.setString(1, "Gary");
mystmt.setString(2, "Larson");
mystmt.setLong(3, 1002);
mystmt.executeUpdate();

Hands-On Lab

Find the GLAB - 305.2.1 - JDBC - PreparedStatement with DML and DDL on Canvas under the Assignment section.

If you have any technical questions while performing the lab activity, ask your instructors for assistance.

Separate Class for Queries

It is a traditional practice to store all of the SQL queries in a separate class. That class usually contains public, static final string fields that store the queries in a variable. That variable is called later in the prepared statement in the class that performs the functionality.

Examples:

public class SqlQuries {
    public final static String GetEmployeByID="select * from employees where employeeNumber=? ";
    public final static String GetEmployeBySalalry= "select * from employees where salary = ?";
}

Separate Class for Queries (continued)

WITHOUT the Separate Class

myStmt = myConn.prepareStatement("select * from employees where salary > ? and department = ?");
myStmt.setDouble(1, 80000);
myStmt.setString(2, "Legal");

WITH the Separate Class

myStmt = myConn.prepareStatement(SqlQuries.GetEmployeBySalalry);
myStmt.setDouble(1, 80000);

Hands-On Activity

Create SQL PreparedStatements that would perform the following functionalities (you can use the “classicmodels” database):

• Select all employees whose officecode is 1 and 4.

• Select all orderdetails whose orderNumber is 10100 and 10102.

• Update the extension number of employees whose officecode is 2, and the new extension number will be “5698.”

• Select all employees whose last name is less than five characters in length.

Knowledge Check

• What is JDBC in Java?

• What is a ResultSet?

• What is a JDBC driver?

• What are the types of JDBC statements?

• What is the difference between Statement and PreparedStatement?

Summary

JDBC (Java Database Connectivity) is the Java API that manages connecting to a database, issuing queries and commands, and handling Resultsets obtained from the database. The steps for connecting to a database with JDBC include:

1. Installing or locating the database you want to access, including the JDBC library.

2. Ensuring that the JDBC driver you need is on your classpath.

3. Using the JDBC library to obtain a connection to the database.

4. Using the connection to issue SQL commands.

Both Statement and PreparedStatement can be used to execute SQL queries. These interfaces look very similar; however, they differ significantly from one another in features and performance:

• Statement – Used to execute string-based SQL queries.

• PreparedStatement – Used to execute parameterized SQL queries.

References

• https://docs.oracle.com/javase/6/docs/api/java/sql/Driver.html

• https://docs.oracle.com/javase/8/docs/api/java/sql/package-summary.html

End of Section