Lesson 4 - Programming a Database Wrapper in Java - Preparation

So far, we've been writing code for working with the database over and over, creating a lot of redundancy. In today's lesson, we're going to create our own database wrapper, which will save us work and we'll be able to focus on the logic of the application and won't have to deal with the SQL language much. In this introductory lesson, we're not going to create the wrapper yet, but prepare test data, create a database, tables, and configure our project. So you can take this introductory lesson as a preparation for the next ones, where we'll be programming the wrapper itself.

Visual Database Tools

You can use some of graphical environments to understand and work better with the MySQL database. We have many programs to choose from. I can recommend phpMyAdmin or DatAdmin for example. Certainly there are many other development environments. If you know any other graphical environment and you prefer it more than these tools, feel free to use it.

Designing the Database

To give our wrapper a try, we'll create a persons database with a simple programmers table that has id, name, age, and language columns. You can either use your graphical environment and simply click the database and table in it, or use the following SQL query:

CREATE DATABASE persons;

CREATE TABLE `persons`.`programmers` (
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
`name` VARCHAR( 30 ) NOT NULL,
`age` INT NOT NULL,
`language` VARCHAR( 20 ) NOT NULL
) ENGINE = INNODB;

Test data

This is an important part of any project. Only a properly tested application (or class, library) is a properly working application. Therefore, we'll insert 14 test subjects in the programmers table:

INSERT INTO  `persons`.`programmers` (
`id` ,
`name` ,
`age` ,
`language`
)
VALUES (
'null',  'James',  '23',  'java'
),
(
'null',  'Danna',  '32',  'java'
),
(
'null',  'Kaitlin',  '41',  'Pythor'
),
(
'null',  'Daniel',  '18',  'php'
),
(
'null',  'Jack',  '51',  'Delphi'
),
(
'null',  'Kaitlin',  '26',  'c#'
),
(
'null',  'Kate',  '52',  'Modula-3'
),
(
'null',  'Amy',  '38',  'java'
),
(
'null',  'Josh',  '66',  'php'
),
(
'null',  'Joe',  '39',  'Pythor'
),
(
'null',  'Matthew',  '37',  'f#'
),
(
'null',  'Gabriel',  '21',  'php'
),
(
'null',  'Lara',  '20',  'c++'
),
(
'null',  'Samantha',  '16',  'VB.net'
);

Sample

Now we'll show how to solve 5 simple tasks without using a wrapper and what it'll look like when using it.

Common solution

Printing table entries:

// 1)
try (Connection connection = DriverManager.getConnection("jdbc:mysql://localhost/persons?user=root&password=");

        PreparedStatement statement = connection.prepareStatement("SELECT * FROM programmers");
        ResultSet results = statement.executeQuery();) {


        while (results.next()) {
                System.out.println(vysledek.getString("name") + " - " + results.getString("language"));
        }
} catch (SQLException ex) {
        System.out.println("Error while communicating with the database");
}

Then we'd need to delete one programmer from the database table somewhere in the application:

try (Connection connection = DriverManager.getConnection("jdbc:mysql://localhost/dictionary_db?user=root&password=");
        PreparedStatement statement = connection.prepareStatement("DELETE FROM programmers WHERE name = ?");) {
        statement.setString(1, "Jack");
        int rows = statement.executeUpdate();
        System.out.println(rows);
} catch (SQLException ex) {
        System.out.println("Error while communicating with the database");
}

This solution (of only 2 tasks) is very long and moreover, if multiple classes work with the database, the code will be duplicate (see above).

Wrapper solution

The following code performs a total of 5 tasks with the database:

try {
     System.out.println("Welcome");
     Database database = new Database("persons", "root", "");
     int success1 = database.delete("persons", "name = ?", "gali");
     int success2 = database.delete("persons", "id = ?", 12);
     int success3 = database.save("persons", null, "Galí", 43, "PHP");
     String[] columns = {"name","age"};
     int success = database.update("persons", columns, "where id = ?", "Galileo", 40, 30);
     System.out.println(success);
     System.out.println(success1);
     System.out.println(success2);
     System.out.println(success3);
     ResultSet result = database.select("SELECT * FROM `persons`", null);
     while (result.next()) {
         System.out.println(result.getString("name") + " - " + result.getString("language"));
     }
} catch (SQLException ex) {
     System.out.println("Error - " + ex.getMessage());
}

The code is much shorter and clearer than the original code. So far, it probably won't tell you much, but don't worry, we'll describe and explain all 5 tasks when creating the wrapper.

Creating and preparing the project

In NetBeans, we'll create a new project from the Java category - Java Application named Wrapper. So far we have only one package named Wrapper in the app. Therefore, we'll create a new DB package and create the Database class in it.

Steps

We'll right-click the Source Packages folder and choose new -> Java Package. We'll name the package DB.

Then we'll right-click this package and choose new -> Java Class. We'll name the class Database.

Finally, we don't forget to add the MySQL JDBC Driver to our project. The procedure was described in one of the previous tutorials. You can find it here.

That will be all for today's lesson. I'm looking forward to seeing you next time when we'll finally get to programming our wrapper.