Database Programming With Java + JDBC: Part 2/4

Getting Started with Java JDBC

Before you start writing code that talks to a database, you need a few things in place. First, pick a database management system (DBMS). If you’re experimenting, MySQL is a popular choice because it’s free, cross‑platform, and has a well‑documented JDBC driver. You can download MySQL Community Server from the MySQL website and install it on your machine. Once the server is up, create a new database called test_db and leave the authentication fields blank for simplicity; this example will connect without a username or password.

Next, make sure the JDBC driver is in your project’s classpath. The MySQL driver, mysql-connector-java, is a type‑4 driver, which means it runs entirely in Java and communicates with the database over TCP. Download the jar from the same MySQL site, then add it to your IDE’s build path or to your project’s lib folder. In a command‑line project, you would include it with a -cp flag when compiling and running.

Here’s a short Java snippet that loads the driver class and opens a connection to test_db:

Running this program prints “Connection established!” if everything is set up correctly. The driver automatically registers itself with the DriverManager when its class is loaded, so you can skip the Class.forName line in newer Java releases, but leaving it in keeps the example explicit and compatible with older environments.

Once you have a connection, you can start issuing SQL statements. JDBC separates the process into four core concepts: Connection, Statement, ResultSet, and PreparedStatement. A Connection represents a live link to the database. From it you create Statement objects to execute static SQL, or PreparedStatement objects to run parameterized queries. The ResultSet holds the data returned from a SELECT query. All of these interfaces are defined in the java.sql package.

In practice, you’ll often see code that follows this pattern:

Using try‑with‑resources ensures that the connection and statement close automatically, even if an exception occurs. That small detail keeps your code tidy and prevents resource leaks that can deplete the database’s connection pool.

Now that you’re comfortable establishing a connection and executing a simple statement, you’re ready to move on to building a real application that demonstrates these concepts in a useful way.

Building an Address Book Application

Let’s create a small, console‑based address book that showcases the key features of JDBC: creating tables, inserting data, and querying records. The application will provide three core functions:

1. Create the contacts table if it does not already exist.
2. Insert new entries into the table.
3. Search for a contact by nickname.

   First, we’ll define a plain Java class to model a single contact. This class simply holds three properties - nickname, name, and email - and offers getters and setters. It also overrides toString() so that we can print a readable representation of the contact.

   Prompt

   public class AddressBookEntry {</p> <p> private String nickName;</p> <p> private String name;</p> <p> private String email;</p> <p> public AddressBookEntry(String nickName, String name, String email) {</p> <p> this.nickName = nickName;</p> <p> this.name = name;</p> <p> this.email = email;</p> <p> }</p> <p> public String getNickName() { return nickName; }</p> <p> public String getName() { return name; }</p> <p> public String getEmail() { return email; }</p> <p> public void setNickName(String nickName) { this.nickName = nickName; }</p> <p> public void setName(String name) { this.name = name; }</p> <p> public void setEmail(String email) { this.email = email; }</p> <p> @Override</p> <p> public String toString() {</p> <p> return "Nickname: " + nickName + ", Name: " + name + ", Email: " + email;</p> <p> }</p> <p>}</p>

   With the data model in place, we can write a utility class that manages the database schema. The createTableIfNotExists method checks whether the contacts table exists and creates it if necessary. The SQL statement uses standard MySQL syntax and defines a primary key that auto‑increments, though the application does not rely on that key for its operations.

   import java.sql.Statement;

   public class DBHelper {

   private static final String URL = "jdbc:mysql://localhost:3306/test_db";

   public static Connection getConnection() throws SQLException {

   return DriverManager.getConnection(URL);

   }

   public static void createTableIfNotExists() {

   String sql = "CREATE TABLE IF NOT EXISTS contacts (" +

   "id INT AUTO_INCREMENT PRIMARY KEY," +

   "nick VARCHAR(50) NOT NULL," +

   "name VARCHAR(100) NOT NULL," +

   "email VARCHAR(100) NOT NULL" +

   ")";

   try (Connection conn = getConnection();

   Statement stmt = conn.createStatement()) {

   stmt.execute(sql);

   } catch (SQLException e) {

   }

   }

   }

   The next step is to write methods that insert a new contact and search for a contact by nickname. The addContact method uses a PreparedStatement to avoid SQL injection and to handle string escaping automatically. The findContact method returns an AddressBookEntry if a match is found or null otherwise.

   import java.sql.PreparedStatement;

   import java.sql.ResultSet;

   public class AddressBookDAO {

   public static void addContact(AddressBookEntry entry) {

   String sql = "INSERT INTO contacts (nick, name, email) VALUES (?, ?, ?)";

   try (Connection conn = DBHelper.getConnection();

   PreparedStatement pstmt = conn.prepareStatement(sql)) {

   pstmt.setString(1, entry.getNickName());

   pstmt.setString(2, entry.getName());

   pstmt.setString(3, entry.getEmail());

   pstmt.executeUpdate();

   System.out.println("Contact added: " + entry);

   }

   }

   public static AddressBookEntry findContact(String nick) {

   String sql = "SELECT nick, name, email FROM contacts WHERE nick = ?";

   pstmt.setString(1, nick);

   try (ResultSet rs = pstmt.executeQuery()) {

   if (rs.next()) {

   String name = rs.getString("name");

   String email = rs.getString("email");

   return new AddressBookEntry(nick, name, email);

   }

   }

   }

   return null;

   }

   }

   Finally, assemble everything in a small console menu. The main method first ensures the table exists, then repeatedly prompts the user to choose an action: create the table again (for a clean start), add a new entry, or search for an existing entry. The loop continues until the user chooses to exit.

   Prompt

   import java.util.Scanner;</p> <p>public class AddressBookApp {</p> <p> DBHelper.createTableIfNotExists();</p> <p> Scanner scanner = new Scanner(System.in);</p> <p> while (true) {</p> <p> System.out.println(" Address Book Menu");</p> <p> System.out.println("1. Create table");</p> <p> System.out.println("2. Add contact");</p> <p> System.out.println("3. Find contact");</p> <p> System.out.println("4. Exit");</p> <p> System.out.print("Select an option: ");</p> <p> String choice = scanner.nextLine();</p> <p> switch (choice) {</p> <p> case "1":</p> <p> DBHelper.createTableIfNotExists();</p> <p> System.out.println("Table ensured.");</p> <p> break;</p> <p> case "2":</p> <p> System.out.print("Nickname: ");</p> <p> String nick = scanner.nextLine();</p> <p> System.out.print("Name: ");</p> <p> String name = scanner.nextLine();</p> <p> System.out.print("Email: ");</p> <p> String email = scanner.nextLine();</p> <p> AddressBookEntry entry = new AddressBookEntry(nick, name, email);</p> <p> AddressBookDAO.addContact(entry);</p> <p> case "3":</p> <p> System.out.print("Enter nickname to search: ");</p> <p> String searchNick = scanner.nextLine();</p> <p> AddressBookEntry found = AddressBookDAO.findContact(searchNick);</p> <p> if (found != null) {</p> <p> System.out.println("Found: " + found);</p> <p> } else {</p> <p> System.out.println("No contact with nickname '" + searchNick + "' found.");</p> <p> }</p> <p> case "4":</p> <p> System.out.println("Goodbye!");</p> <p> scanner.close();</p> <p> System.exit(0);</p> <p> default:</p> <p> System.out.println("Invalid option, try again.");</p> <p> }</p> <p> }</p> <p> }</p> <p>}</p>

   Running this application opens a text prompt. When you add a contact, it’s stored in the contacts table. When you search for a nickname, the application retrieves the matching row and prints the details. Because the code relies on JDBC’s PreparedStatement, the data is escaped safely, and the database interaction is efficient. The console interface keeps the focus on the JDBC logic without distractions from a graphical UI.

   Even though this example is lightweight, it demonstrates the fundamental steps of working with JDBC: setting up a connection, creating tables, inserting records, and querying data. You can extend the design by adding update and delete operations, or by wrapping the DAO in a service layer that handles business rules. The core patterns remain the same, so the knowledge gained here applies to any Java application that needs to talk to a relational database.