Now that we have verified that our drivers are present and restructured our project into a common design protocol, it is time to make the connection to our database.
The default setup of a SQLite database requires no username and password, therefore to connect to the database all we need to do is point the Java program in the right direction. To get our program to connect to a database we need to start using classes from the JDBC API, specifically the DriverManager
and Connection
classes.
The DriverManager
class is the basic service used to load and manage specific JDBC drivers and to reach out to the database to obtain an initial connection.
The Connection
interface manages a session with a specific database. All SQL statements and results are returned and handled within the context of a single connection. Along with managing SQL execution, a Connection
can also provide database schema information such as overall design and specific table layouts.
The DriverManager
has a static method that attempts to establish the initial connection and return the connection back to the user in the form of a Connection
object. The .getConnection()
method can either accept simply a database location such as our SQLite database, or a location, username, and password to access more secure databases. Database connections are costly to both local memory and network bandwidth and it is very important to close this resource as soon as you are finished with it.
Database locations, typically in the form of a URL, follow a generic pattern, especially when using a JDBC connection. For example, the address for our SQLite database is: jdbc:sqlite:mysteryBusiness.db
.
- The first part refers to the type of service used to connect to the database, in our case
jdbc
. - The second part is the type or vendor of the database. We are using
sqlite
but if you are using a MySQL database it would bemysql
or on a Microsoft SQL Server it would besqlserver
. - The last part is the path to the database and any extra parameters such as usernames and passwords. Once again, our SQLite database is fairly barebones and requires only the location in the file system of the database.
Connections are also subject to failure, which can lead to other errors in our program. For this reason, a failed connection will throw a SQLException
and needs to be wrapped in a try-catch-finally
block or a try-with-resources
block, the latter being preferred because it automatically closes any open resources.
try (put resources you want to open here) { // Do something with the resources here } catch (exceptions here) { // Do something when the try block fails, typically alert the user }
Note: SQLite will create a database with the database name you give it if you request to open a database that doesn’t exist. This can make it appear like you are connected to the proper database even though you are not. Using a username/password will help eliminate this problem, as well as double-checking your code.
Instructions
The first thing we are going to do is set up the URL for our database inside CustomerDaoService.java.
Under the comment, declare a new private class variable,
url
of typeString
. Make itfinal
as well, that way we can’t accidentally change the database in our logic.Set our
url
variable equal to"jdbc:sqlite:resources/MYSTERY_BUSINESS.db"
. This will connect to our local database.
Next, navigate to the empty .testDatabaseConnection()
method and add an empty try-with-resources
block.
Let’s work on the try
part of the try-with-resources
block:
- Inside the parenthesis of the
try
block declare a newConnection
calledconnection
and set it equal to the result of making a call toDriverManager
‘s.getConnection()
method, passing in theurl
variable to the.getConnection()
method. - In the
try
block, print out"The connection to the SQLite database was successful!"
using.println()
.
Now to finish the catch
portion:
- Catch a
SQLException
ase
. - Print
"The connection to the database was unsuccessful!"
using.println()
. - Print the variable,
e
, also using.println()
.
Now go back to the top of CustomerDaoService.java and import the DriverManager
, Connection
, and SQLException
classes from the java.sql
library.
Open BusinessLogic.java, inside the main method and below the .loadDriver()
call, make a call to the new .testDatabaseConnection()
.
Compile and run your program:
- Navigate to the
projects
folder in the terminal (use ‘cd’ to change directories). - Use the command
javac $(find . -name '*.java')
to compile all.java
files in all subdirectories of theproject
folder. - Run your program with the classpath variables like before:
java -classpath .:../sqlite-jdbc-3.36.0.3.jar viewmodels.BusinessLogic
. - You will see the database,
MYSTERY_BUSINESS.db
, appears in theresources
folder.