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.