In this post, we're going to see Java Database Connectivity (JDBC) with MySQL. Connecting to database to have CRUD operations like INSERT, SELECT, UPDATE and DELETE is the "center of gravity" of almost every backend technology.
To connect java application with the MySQL database, mysqlconnector.jar file is required to be loaded. You can download this file from here or check here.
To load the jar file, you can do this by setting the classpath permanently. To do this, go to environment variable then click on new tab. In variable name write classpath and in variable value paste the path to the mysqlconnector.jar file by appending mysqlconnector.jar as C:\yourfolder\mysql-connector-java-8.0.25;
Then create a new project in your java text editor say Eclipse, Netbeans.
Next, load mysqlconnector.jar file in your project by right clicking on it. I'm using Eclipse as my text editor, do the following to load the jar file in your project.
1.Right-click on your project.
2.Select Build Path.
3.Click on Configure Build Path.
4.Click on Libraries, select Add External JARs.
5.Select the jar file from the required folder.
6.Click and Apply and Ok.
For Ubuntu users, you might find this helpful.
NOTE: For whatever text editor you are using, You should be able to load that file.
Next after loading the jar file in your project, create a java class say Users to illustrate how to connect java to MySql database using com.mysql.jdbc.Driver driver class.
Code Example
import java.sql.*;
public class Users {
public static void main(String[] args) {
try{
Class.forName("com.mysql.cj.jdbc.Driver");
//Class.forName("com.mysql.jdbc.Driver"); // this has been deprecated
String connectionString = "jdbc:mysql://localhost:3306/db_name";
String db_username = "yourdatabase_username";
String db_password = "yourdatabase_password";
Connection con = DriverManager.getConnection(connectionString,
db_username,
db_password);
Statement stmt=con.createStatement();
ResultSet rs=stmt.executeQuery("select * from users");
while(rs.next()) {
int id = rs.getInt("id");
String firstName = rs.getString("first_name");
String lastName = rs.getString("last_name");
String name = rs.getString("name");
String username = rs.getString("username");
String telephone_no = rs.getString("tel_no");
String address = rs.getString("address");
Date dateCreated = rs.getDate("created_at");
boolean isActive = rs.getBoolean("isActive");
int user_role = rs.getInt("user_role");
System.out.format("%s, %s, %s,
%s, %s, %s, %s, %s, %s\n",
id,firstName, lastName, dateCreated,
isActive, user_role, name, username,
telephone_no, address);
}
con.close();
}
catch(Exception e){
System.out.println(e);
}
}
}
Code Explanation:
- Class.forName() is the method loads the driver class for the mysql database (com.mysql.jdbc.Driver).
- Next, we establish a database connection using getConnection() method on DriverManager class which takes in the url for the database host, database username and password.
- con.createStatement() is used to create a statement instance on the database connection object (con). Statement is used for accessing database. Statement interface cannot accept parameters and is useful when you are making static SQL statements at runtime like SELECT.
- executeQuery() method on the statement object(stmt) returns result set by fetching data from database by selecting data from users table.
- In the next line, we use while loop and rs.next to iterate through the rows of data from users table in database and then capture cell data using getString(), getInt(), getDate() and getBoolean() to capture string, integer, date and boolean column data from the schema which are later printed on the screen using System.out.println().
UPDATE QUERY CODE SNIPPET
Class.forName("com.mysql.cj.jdbc.Driver");
String connectionString = "jdbc:mysql://localhost:3306/db_name";
String db_username = "yourdatabase_username";
String db_password = "yourdatabase_password";
Connection con = DriverManager.getConnection(connectionString,
db_username,
db_password);
String query = "UPDATE users SET first_name= ?,
last_name=? where id = ?";
PreparedStatement preparedStmt=con.prepareStatement(query);
preparedStmt.setString(1, "Dallington");
preparedStmt.setInt (2, 1);
preparedStmt.executeUpdate();
con.close();
NOTE: We use PreparedStatement interface not Statement when executing dynamic sql updates in java. PreparedStatement is suitable when you want to use SQL statements many times and it accepts input parameters at runtime which is not the same case with Statement interface. Statement interface is useful when you are running static SQL statements at runtime.
Benefits of PreparedStatement over Statement Interface
- PreparedStatement can execute parameterized queries whereas Statement can only run static queries.
- The PreparedStatement performs faster as compared to Statement because the Statement needs to be compiled everytime we run the code whereas the PreparedStatement compiled once and then execute only on runtime.
DELETE QUERY CODE SNIPPET
Class.forName("com.mysql.cj.jdbc.Driver");
String connectionString = "jdbc:mysql://localhost:3306/db_name";
String db_username = "yourdatabase_username";
String db_password = "yourdatabase_password";
Connection con = DriverManager.getConnection(connectionString,
db_username,
db_password);
String query = "DELETE FROM users where id = ?";
PreparedStatement preparedStmt =
con.prepareStatement(query);
preparedStmt.setInt(1, 3);
preparedStmt.execute();
con.close();
Because we pass parameters (e.g id in our code example) during the delete query at runtime, that's why we use PreparedStatement interface.
INSERT QUERY CODE SNIPPET
Class.forName("com.mysql.cj.jdbc.Driver");
String connectionString = "jdbc:mysql://localhost:3306/db_name";
String db_username = "yourdatabase_username";
String db_password = "yourdatabase_password";
Connection con = DriverManager.getConnection(connectionString,
db_username,
db_password);
String query = " insert into users (first_name, last_name,
username, telephone_no, age, address, isActive)"
+ " values (?, ?, ?, ?, ?, ?, ?)";
PreparedStatement preparedStmt=con.prepareStatement(query);
preparedStmt.setString(1, "Dallington");
preparedStmt.setString(2, "Asingwire");
preparedStmt.setString(3, "Dalton");
preparedStmt.setString(4, "25670000000");
preparedStmt.setInt(5, 33);
preparedStmt.setString(5, "Kampala");
preparedStmt.setBoolean(6, true);
preparedStmt.execute();
con.close();
Conclusion: That's how you can have CRUD operations in java using driver class; com.mysql.jdbc.Driver. Hope you can use this as the building block for more interesting projects in java. Thank you for taking time to read through this post, see you in the next one!
Top comments (0)