When it comes to querying data from a MySQL database using Java, there are several different ways that you can do it. However, in this article, we’re going to focus on using the JDBC API to query data from a MySQL database.
Introduction
Databases are used in several computer programs or online applications such as management systems, online shopping websites, game development, etc. When creating java applications or programs, we often need to have a database through which we can store or fetch the data.
MySQL is a Relational Database management System(RDBMS) and is widely used as it is open-source, scalable, and can support large databases. Therefore, we’ll learn how to connect the MySQL database to a java application and fetch or upload some data over the database.
For this tutorial, we must have a basic understanding of Java programming language and MySQL relational database management system(RDBMS).
For a follow-along with this tutorial, you must have the below programs installed on your desktop. If you do not have these installed yet, you can click on their corresponding links and download-install them:
- Java(https://www.java.com/en/download/) is a programming language used in a variety of areas such as web development and software development.
- MySQL(https://www.mysql.com/downloads/) is a widely used relational database management system(RDBMS).
- JDBC driver(https://dev.mysql.com/downloads/connector/j/) is a database connectivity driver for the Java programming language.
- IDE stands for the integrated development environment, you can use any IDE of your choice, some of them are sublime,vscode, eclipse, etc. In this tutorial, we’ll use Eclipse IDE.
Steps to connect MySQL database to Java Application using JDBC
Before writing the java, we are still left with a few steps. Therefore, let’s set up our java project and add the JDBC driver to our project.
Step 1: Create a new project
On your IDE (preferably Eclipse IDE) create a java project, you can do that by going to Files -> New -> Java Project. Set the project’s name (make sure to set the name in lowercase as this is the naming convention).
Step 2: Create a driver class
After creating the project, let’s create a driver class by right-clicking on the src folder -> class and name the class driver, and enabling the main function within the class.
Step 3: Add JDBC jar file
Now, let’s add the JDBC driver, if you have downloaded the JDBC driver then unzip the folder and look for the JDBC jar file (with .jar extension), and copy it. After copying it go to your project and create a new folder by right-clicking on the project -> Folder and name the folder as lib. Paste the jar file into the lib folder.
When this is done, right-click on the project and select properties. Now, select Java Build Path -> Libraries. Click on module-path and you’ll see Add JARs option will be available now, click on Add JARs button and add the jar file which is present in the lib folder, after that click on apply and close.
Note: If you are using new versions of the eclipse, you will get the module folder. You must delete it, as it will definitely hamper the JDBC connectivity.
Step 4: Write driver code
Now, that we have done all the preliminary setup, let’s just dive into the java code to connect it with the MySQL database.
There are basically four steps to follow whenever you want to connect to a database using JDBC:
Create Connection
We need to provide three things to create a connection to a database. Database name, MySQL User name, and MySQL password. If you have all these three things then you are good to go. Below is the code for creating a connection:
String url = "jdbc:MySQL://localhost:3306/databaseName";
String user ="root";
String password = "root";
//Creating connection using url, MySQL username and password
Connection myConn = DriverManager.getConnection("url","user","password"); Code language: Java (java)Create a statement
After connecting to the MySQL database, We need to write an SQL query and for that, we’ll create a statement using the createStatement function. It will allow us to execute queries on the MySQL database using Java. Let’s see one simple example of how to do that:
Statement myStmt = myConn.createStatement();Code language: Java (java)Process ResultSet
We must write a query and process the result set, we can perform any of the SQL queries using java, for example, update the table, Insert records, etc and then we can simply process those queries.
//Execute SQL Query
ResultSet myRs= myStmt.executeQuery("select * from employee");
//Process the result set
while(myRs.next()) {
System.out.print(myRs.getString("name")+"\n");
}Code language: Java (java)Close connection
In the end, just close the connection.
conn.close();Code language: Java (java)A few examples of Querying Data from MySQL using JDBC
In this section we’ll see full-fledged examples of querying MySQL databases using JDBC, for example, inserting data into tables, updating the database, and fetching data from Databases using Java.
View MySQL data using JDBC
The below code is an example of querying the database to fetch data using JDBC. We’ll be fetching the First name of each employee existing in the employee table which is present in the DB database.
package jdbcproject;
import java.sql.*;
public class Driver {
public static void main(String[] args) {
try {
//get a connection to database
Connection myConn = DriverManager.getConnection("jdbc:mysql://localhost:3306/db","root","root");
//create a statement
Statement myStmt = myConn.createStatement();
//Execute SQL Query
ResultSet myRs= myStmt.executeQuery("select * from employee");
//Process the result set
while(myRs.next()) {
System.out.print(myRs.getString("fname")+"\n");
}
}
catch(Exception e) {
e.printStackTrace();
}
}
}Code language: Java (java)Insert data into MySQL database using JDBC
The below code is an example of inserting a new record into the employee table which is present in the DB database.
package jdbcproject;
import java.sql.*;
import java.sql.Connection;
import java.sql.DriverManager;
public class InsertData {
public static void main(String[] args) {
String url = "jdbc:MySQL://localhost:3306/db";
String user ="root";
String password = "root";
try {
//1. Create a connection
Connection myConn = DriverManager.getConnection(url,user,password);
//2. Create a statement
Statement myStmt = myConn.createStatement();
//3. Execute Query
String sql = "insert into employee "
+"(fname, lname, age, phone, dept, des)"
+ " values ('Matt', "LeBlanc", 54, 34235452, "Management", "COO")";
myStmt.executeUpdate(sql);
System.out.println("Insert complete.");
//4. Close connection
myConn.close();
}
catch(Exception e){
e.printStackTrace();
}
}
}Code language: Java (java)Update data in MySQL database using JDBC
The below code is an example of updating a table using JDBC. In this example, we will update the department name of the employee whose empid is 5 to ‘Telecom’.
package jdbcproject;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
public class UpdateSQL {
public static void main(String[] args) {
String url = "jdbc:MySQL://localhost:3306/db";
String user ="root";
String password = "root";
try {
//1. Create a connection
Connection myConn = DriverManager.getConnection(url,user,password);
//2. Create a statement
Statement myStmt = myConn.createStatement();
//3. Execute Query
String sql = "update employee "
+"set dept = 'Telecom' "
+ " where empid = 5 ";
myStmt.executeUpdate(sql);
System.out.println("Update complete.");
//4. Execute Query
myConn.close();
}
catch(Exception e){
e.printStackTrace();
}
}
}Code language: Java (java)Conclusion
In this tutorial, we learned about querying a MySQL database with several examples, we also learned how to set our work environment for JDBC connectivity and simple steps to query a database.