the Java Database Connectivity (JDBC) API allows developers to make their Java applications interact with any data source such as a database, spreadsheet or even a legacy file system.
In order to begin interacting with a database, you need to download a JDBC driver for your particular database (Oracle DB, MySQL, etc). This Java tutorial will be using the MySQL database. Regardless of the database you are using, however, the steps below will still apply to it.
If you enjoy learning how to program Java in an online course environment, we have a great list of the Best Online Courses to Learn Java to help you get started.
How to Connect to a Database in Java
The first step to connecting to a database with Java is to import the necessary packages, namely: java.io.* and java.sql.*. Next, you need to initialize the JDBC drivers in your application. If you are using MySQL, you can download the database connector from the official site here.
After downloading the driver, ensure that you add it to the $CLASSPATH of your application. This is a very important step. If you fail to do so, your Java application will not know where to find the DriverManager on your system.
To initialize the database driver, use the Class.forName() method, as shown in the code snippet below:
Class.forName(“com.mysql.cj.jdbc.Driver”); // to initialize mysql driver
Once this is complete, you can go ahead and create a database connection. To do so, you need to create a connection object from either the DriverManager class or the DataSource class. The DriverManager class is easier to use and is therefore the one used in this tutorial.
Connection con = DriverManager.getConnection(url, username, password);
The getConnection() method of the DriverManager enables programmers to create a database connection. It requires the following three parameters:
- url: This is the database URL. The format is: jdbc:mysql://localhost:3306/school.
mysql is the name of the database server you are using. localhost is the address of the server. 3306 is the port on which your database server is running. For MySQL, port 3306 is the default port. school is the name of the database you want to connect to.
If you are not sure of the port your database server is using, you can check this using:
$ sudo netstat -plnt
- username: This is the username of the user you would like to have connected to the database.
- password: This is the password of the above user.
Reading: Java Tools to Increase Productivity
Create a statement object for database queries
After creating a connection, you can now create a statement object to handle your database queries. There are three different methods that you can use to do this:
- createStatement(): This implements a simple SQL statement
- createPreparedStatement(): This is used to implement prepared statements
- createCallableStatement(): This is used to implement stored procedures
You can create a statement object in Java with the following example code:
Statement st = con.createStatement();
How to Execute a Database Query in Java
The next step is to execute your query. When you do this, Java returns a ResultSet object. This object contains the results of your query. You can access data in a ResultSet through a database cursor, which allows you to traverse the rows of the result set. This cursor is initially placed before the first row of your data.
You can traverse the rows using the next() method, as shown in the following example Java code:
ResultSet rs = st.executeQuery(query); rs.next();
There are three execute() methods that developers can use with their statement object:
- execute()
- executeQuery(): This returns one ResultSet object
- executeUpdate(): This returns an integer value for the number of rows affected by an execution. This method is only applicable to INSERT, UPDATE, and DELETE statements.
An example of using executeQuery() would be:
ResultSet rs = st.executeQuery(query);
Closing Database Connections in Java
After your application is done interacting with the database, you need to close the connections to release the system resources in use. The close() method enables you to do this.
It is now time to look at a fully working code example to demonstrate the above concepts. The code below gets the list of students from the school database. You must have initially created this database and seeded it with some values in order for the code to work, so be sure you do so before running it.
Code example showing how to work with databases in Java:
import java.io.*; import java.sql.*; public class ConnectDB { public static void main(String[] args) throws Exception { String url = “jdbc:mysql://localhost:3306/school”; // database URL String username = “root”; String password = “pass4567”; try{ Class.forName(“com.mysql.cj.jdbc.Driver”); Connection con = DriverManager.getConnection(url, username, password); System.out.println(“Connection successful…”); String query= “select * from student”; Statement st = con.createStatement(); ResultSet rs = st.executeQuery(query); rs.next(); String firstName = rs.getString(“fname”); System.out.println(firstName); rs.close(); st.close(); con.close(); System.out.println(“Connection closed….”); } catch (Exception e){ System.out.println(e); } } }
This results in the following output:
Connection successful… Jimmy Connection closed….
Final Thoughts on Working with Database in Java
In this programming tutorial you learned how you can use the JDBC API to connect to your database in Java. Remember, it is important for you to close all database connections after making any queries. This will ensure that your application keeps at optimal performance and also safeguards its security.
read more Java programming tutorials and software development tips.
