Java Database Connectivity (JDBC)

JDBC is the Java API that manages connections to a relational database. Using this API, you can use all types of CRUD (create, read, update, and delete) operations on an RDBMS. JDBC is one of the APIs that helps a Java program to manipulate and manage data in any relational database. Any JEE-compliant web server should support the JDBC API.

Library To Use

There are many different relational databases out there. For the most part, each one is from a different vendor and is a different system altogether, although they are all RDBMSs. If they support JDBC, then they need to provide a Java library file that can be dropped into the lib folder of our Java project to help us use JDBC to start connecting to the database.

Since each vendor is different, you need to download the specific JDBC Driver library file for each one. For the most part, every project will be connecting to one type of RDBMS, so you will see one JDBC driver library file. But some projects may be connecting to multiple different RDBMSs from different vendors, in which case, you need to have the relevant JDBC Driver library file corresponding to each RDBMS type.

Here is a list of the most popular RDBMSs and their respective driver information for quick reference:

Connecting to the database

Once the relevant JDBC driver library is in the lib folder, your Java program can start using it to make database connections. But your Java program is running on a JVM, and the database server is in a separate process. How do you connect from one process to another? First and foremost, you need to know the URL and the port number of the database where the Java program can connect. Once this is given to you, you use the below JDBC API classes to connect:

• DriverManager
• Connection

The code below shows an example of getting the Connection object from the DriverManager for DerbyDB and MySQL DB:

Connection conn1 = DriverManager.getConnection("jdbc:derby:mydb;create=true");

Connection conn2 = DriverManager.getConnection("jdbc:mysql://localhost:3306/mydb", "username", "password");

The connection variable conn1 has a connection established to DerbyDB, and in that, it is specifically connected to the 'mydb' database.

The connection variable conn2 has a connection established to MySQL, and in that, it is specifically connected to the 'mydb' database. This statement also takes the optional username and password parameters to make the connection.

CRUD Operations

Once the connection is established to the database, you are ready to start the CRUD operations. The JDBC API provides two main classes to help with that:

• Statement
• ResultSet

Using the Connection object, you create a Statement object. There are two main methods that you can use on the Statement object:

• executeQuery - for running select (read) operations on the database. This returns a ResultSet object.
• executeUpdate - for create, update, and delete operations on the database. This returns the number of rows that were affected by running this query or 0 for SQL queries that return nothing.

RDBMSs are very strict systems in which you cannot simply insert a record without giving much thought to the data integrity rules of the database. For all inconsistent data manipulations, the database throws an error. This exception is captured in the Java world as SQLException.

Below, you will see some complete examples of running SQL queries through Java:

package com.mbcc.mailer;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

public class DerbyDao implements DataSourceDao {


	private static final String DERBY_URL = "jdbc:derby:mailer;create=true";

	public boolean insertEmail(EmailDTO email) {

		try (Connection conn = DriverManager.getConnection(DERBY_URL); Statement stmt = conn.createStatement()) {

			String dbString = "insert into Email (email_text, recipient_category, subject) values ('";
			dbString += email.getEmailText();
			dbString += "', '";
			dbString += email.getRecipientCategory();
			dbString += "', '";
			dbString += email.getSubject();
			dbString += "')";

			stmt.executeUpdate(dbString);

			return true;

		}

		catch (SQLException ex) {
			// handle any errors
			System.out.println("SQLException: " + ex.getMessage());
			System.out.println("SQLState: " + ex.getSQLState());
			System.out.println("VendorError: " + ex.getErrorCode());
			ex.printStackTrace();
			return false;

		}

	}

	public ArrayList<EmailDTO> getEmails() {

		try (Connection conn = DriverManager.getConnection(DERBY_URL); Statement stmt = conn.createStatement()) {

			ResultSet rs = stmt.executeQuery("SELECT * FROM Email");

			ArrayList<EmailDTO> results = new ArrayList<EmailDTO>();

			while (rs.next()) {

				String emailText = rs.getString("email_text");
				String recipientCategory = rs.getString("recipient_category");
				String subject = rs.getString("subject");
				String testerEmail = rs.getString("tester_email");
				EmailDTO email = new EmailDTO(subject, emailText, recipientCategory, testerEmail);
				results.add(email);
			}
			return results;

		} catch (Exception ex) {
			System.out.println(ex);
		}

		return null;

	}


	public void createTable() throws SQLException {

		String emailTableSql = "CREATE TABLE email (" + "  id int NOT NULL GENERATED ALWAYS AS IDENTITY,"
				+ "  email_text blob NOT NULL," + "  recipient_category varchar(255)  NOT NULL,"
				+ "  subject varchar(255)  NOT NULL," + "  tester_email varchar(255) DEFAULT NULL,"
				+ "  email_date timestamp DEFAULT NULL," + "  PRIMARY KEY (id)" + ")";

		Connection conn = DriverManager.getConnection(DERBY_URL);
		Statement stmt = conn.createStatement();
		stmt.execute(emailTableSql);

	}

	
	public static void main(String[] args) throws SQLException {
		DerbyDao dao = new DerbyDao();
		dao.createTable();
	}
}

Note on Security: The insertEmail method above uses string concatenation to build a SQL query. This practice is highly vulnerable to SQL Injection attacks. In a real-world application, you should always use PreparedStatement to safely handle user-provided data.

EmailDTO Class

package com.mbcc.mailer;

public class EmailDTO {

	private String subject;
	private String emailText;
	private String recipientCategory;
	private String testerEmail;

	public EmailDTO(String subject, String emailText, String recipientCategory, String testerEmail) {

		if (subject == null)
			throw new IllegalArgumentException("subject cannot be null");

		this.subject = subject;

		if (emailText == null)
			throw new IllegalArgumentException("emailText cannot be null");

		this.emailText = emailText;

		if (recipientCategory == null)
			throw new IllegalArgumentException("Category cannot be null");

		this.recipientCategory = recipientCategory;

		this.testerEmail = testerEmail;
	}

	public String getSubject() {
		return subject;
	}

	public String getEmailText() {
		return emailText;
	}

	public String getRecipientCategory() {
		return recipientCategory;
	}

	public String getTesterEmail() {
		return testerEmail;
	}

}

Exercise

Add statements in the main method to:

• Insert a new record into the 'mailer' table.
• Query the inserted record in the 'mailer' table.

Reference: https://docs.oracle.com/javase/tutorial/jdbc/basics/index.html