Learn JDBC By Project — Part II

Sera Ng.
Sera Ng.
Oct 20, 2020 · 5 min read

Start initializing the project and write some first code for our Device Management program

Milestone II: Project Initialization

Task 1: Explore Project

Netbeans Project Structure

If you are using NetBeans IDE, you can download this project structure here to follow along:

https://github.com/stackera/LearnJDBC/blob/main/project-template.zip

Since the application needs to connect to MySQL Server, we need a driver class.

First, download the driver-jar file (mysql-connector-java-8.0.11.jar) for MySQL 8

https://github.com/stackera/LearnJDBC/blob/main/lib.zip

Then, add the jar file to the project by right click on the Libraries section in the project structure, select Add JAR/Folder

Then, navigate to the folder containing your downloaded jar file (mysql-connector-java-8.0.11.jar) and add to the project.

Once added, the jar file should be appeared in the Libraries section

Task 2: Explore User Interface

In this project, I have already created the main user interface, necessary UI components, and related event handlers.

Since the purpose of this project is to demonstrate the application of JDBC, I used the drag-n-drop feature in Netbeans to create the UI. I know this can be annoying, but the UI here is just for demonstration purposes.

If you have already installed Netbeans 8 or above, you can download the project template and open it in Netbeans. No more work needs to be done.

Depending on your OS, but the UI should look like this at development time:

I’m sure you know that every UI component you see in the Design mode is just an instance created from the corresponding UI class generated by Netbeans. For instance, a text field is an instance created from the JTextField class.

Therefore, it is not wrong to say every UI component has a variable name and I have already changed all the necessary variable names instead of using the default created names. For instance:

  • the Clear button has a variable name as btnClearDeviceForm
  • the table to display a list of devices has the variable name as tblDevice

..and so on.

As you can observe while navigating the UI, since we need to manage devices and suppliers, I have used a JTabbedPane with 2 tabs: one for Devices, and one for Suppliers.

Inside each tab is a panel which contains a JTable on the right side, and other UI components for adding/updating/removing data

Take some time to go around the UI.

Once you get familiar with the UI, go to the next step

Task 3: Explore Data Transfer Object (DTO)

From Wikipedia:

“Data transfer object (DTO), formerly known as value objects or VO, is a design pattern used to transfer data between software application subsystems. DTOs are often used in conjunction with data access objects to retrieve data from a database”

DTO can be used in plenty of cases and later on, in your software development career, I’m sure that at some point, you will encounter DTO.

In our case, we need 2 classes to store data retrieved from the database:

As you can see, since the Device class is used to store data retrieved from the device table in our database, it has attributes corresponding to the fields in the device table.

Also notice that, since in the device table, we have a foreign key referenced to the primary in the supplier table, we need to create an instance of the Supplier class in the Device class. This is very convenient for later data manipulation.

This is our Supplier class:

Task 4: Explore Table model classes

As mentioned from the beginning, in this project I’m using JTable component and custom table model classes to display data to users.

I have created the 2 custom table model classes with necessary implementation:

DeviceTableModel: responsible for displaying and updating device information on device JTable

SupplierTableModel: responsible for displaying and updating supplier information on supplier JTable

If you are not familiar with JTable and the custom table model, give yourself some time to study these topics before moving to the next task.

Task 5: Explore JDBC Programming Model

Since we are using JDBC, let’s first discuss the implementation model in JDBC.

From Wikipedia:

“Java Database Connectivity (JDBC) is an application programming interface (API) for the programming language Java, which defines how a client may access a database. It is a Java-based data access technology used for Java database connectivity. It is part of the Java Standard Edition platform, from Oracle Corporation. It provides methods to query and update data in a database, and is oriented towards relational databases.”

In a simple term, JDBC provides API so that we can use to manipulate data in a relational database system from our Java application. In short, API is just a collection of build-in classes, methods, functions, etc that we can use for certain purposes.

With the correct driver, JDBC supports quite a lot of kinds of relational database systems such as MS SQL Server, My SQL, Oracle, MS Excel, and MS Access.

JDBC programming model can be viewed as the following diagram:

In order to have a complete query execution, the following steps needed to be conducted:

Step 1: Open connection

Before performing any SQL queries, we first need to open a connection to the specific database system.

Depending on the selected database system, we will need an appropriate driver.

There are 4 types of drivers in JDBC that can be used for different kinds of databases and purposes.

You can read more at https://en.wikipedia.org/wiki/JDBC_driver

The most commonly used driver in normal applications is Type 4, which is the one that we are using in our application.

Driver Type 4 is also called Pure Java Driver because it is written in Java language. Java Type 4 uses a technique called Socket to establish a connection to a database system and then convert JDBC statements directly into vendor-specific database ones.

Step 2: Init a statement

Once a connection has been established, we need to initialize a query statement, which can be Select, Insert, Update, or Delete.

If you prefer to call a store procedure, you can specify at this step

Step 3: Execute the query

For Select statements: a ResultSet object is returned after execution. This is an interface referencing to the returned records from the database

For Insert, Update, Delete statements: an integer value is returned after the execution. The returned integer indicates the number of affected rows in the database, which is the number of rows that have been performed successfully. For instance, if 5 rows have been inserted successfully, the number 5 is returned.

Medium is an open platform where 170 million readers come to find insightful and dynamic thinking. Here, expert and undiscovered voices alike dive into the heart of any topic and bring new ideas to the surface. Learn more

Follow the writers, publications, and topics that matter to you, and you’ll see them on your homepage and in your inbox. Explore

If you have a story to tell, knowledge to share, or a perspective to offer — welcome home. It’s easy and free to post your thinking on any topic. Write on Medium

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store