Contents
Java JDBC CRUD operations
We will demo the basic Java JDBC CRUD operations (Create, Read, Update and Delete). The package java.sql has an interface called Statement. We use this interface to execute the SQL query and get the result that we need. We create the Statement object from the method createStatement() of the Connection object.
The Statement object has some basic methods, we will study them in each case of CRUDE operations. (One note, almost all JDBC operations throw an SQLException, which is a checked exception)
Read
We can query data with executeQuery() method. The executeQuery() method will return a ResultSet object (which is basically a table), we use the next() method of the ResultSet to iterate the rows of the table. We use these method to do the read operation, that is to query data from Customers table in the test database:
import java.sql.*;
class Test {
static public void main(String[] args)
throws SQLException {
var db = "jdbc:mysql://localhost/test";
var user = "testuser";
var password = "Test@12345";
Connection conn = DriverManager
.getConnection(db, user, password);
//Create a statement
Statement stm = conn.createStatement();
//Create a string sql
String query = "select * from Customers";
//get the ResultSet
ResultSet c = stm.executeQuery(query);
//iterate the ResultSet
while (c.next()){
System.out.print("ID: ");
System.out.print(c.getInt("CustomerId"));
System.out.print("\t- FirstName: ");
System.out.print(c.getString("FirstName"));
System.out.print("\t- LastName: ");
System.out.println(c.getString("LastName"));
}
}
}
Run the code and we have the table in the Customers table of the test database:
ID: 1 - FirstName: Tom - LastName: Rex ID: 2 - FirstName: Luke - LastName: Skywalker ID: 3 - FirstName: Ken - LastName: Star
That’s for retrieving operations, the next is the creation operation. We will create a table and add data to that table.
Create
We use the execute() method to create a table, the name of the table is Test. The Test table has only two columns, one is the Id column, the other is Name column:
import java.sql.*;
class Test {
static public void main(String[] args)
throws SQLException {
var dbUrl = "jdbc:mysql://localhost/test?"
+ "user=testuser&password=Test@12345";
var conn = DriverManager.getConnection(dbUrl);
var stm = conn.createStatement();
var query = "create table Test " +
"(Id int not null auto_increment " +
"primary key, " +
"Name varchar(50));";
stm.execute(query);
}
}
The table Test has been created, we will update it right now.
Insert
We insert rows into the table with executeUpdate() method:
import java.sql.*;
class Test {
static public void main(String[] args)
throws SQLException {
var dbUrl = "jdbc:mysql://localhost/test?"
+ "user=testuser&password=Test@12345";
var conn = DriverManager.getConnection(dbUrl);
var stm = conn.createStatement();
var query = "insert into Test" +
"(Name) values" +
"('Krisha'), ('Prisha'), ('Pavatil');";
stm.executeUpdate(query);
}
}
We can check the content of the table like in the read operation example:
import java.sql.*;
class Test {
static public void main(String[] args)
throws SQLException {
var dbUrl = "jdbc:mysql://localhost/test?"
+ "user=testuser&password=Test@12345";
var conn = DriverManager.getConnection(dbUrl);
var stm = conn.createStatement();
var query = "select * from Test";
ResultSet set = stm.executeQuery(query);
while (set.next()){
System.out.println(
"Id: " + set.getInt("Id") + " - " +
"Name: " + set.getString("Name")
);
}
}
}
Run the code and we have:
Id: 1 - Name: Krisha Id: 2 - Name: Prisha Id: 3 - Name: Pavatil
Update
We use the queryUpdate() again to update a row, say, we want to change the name Krisha to Krina. Let’s do it:
import java.sql.*;
class Test {
static public void main(String[] args)
throws SQLException {
var dbUrl = "jdbc:mysql://localhost/test?"
+ "user=testuser&password=Test@12345";
var conn = DriverManager.getConnection(dbUrl);
var stm = conn.createStatement();
var query = "update Test" +
" set Name='Krina'" +
" where Name = 'Krisha';";
stm.executeUpdate(query);
}
}
Run the code and we can check again the content of the table Test with this code:
import java.sql.*;
class Test {
static public void main(String[] args)
throws SQLException {
var dbUrl = "jdbc:mysql://localhost/test?"
+ "user=testuser&password=Test@12345";
var conn = DriverManager.getConnection(dbUrl);
var stm = conn.createStatement();
var query = "select * from Test";
ResultSet set = stm.executeQuery(query);
while (set.next()){
System.out.println(
"Id: " + set.getInt("Id") + " - " +
"Name: " + set.getString("Name")
);
}
}
}
The output will be:
Id: 1 - Name: Krina Id: 2 - Name: Prisha Id: 3 - Name: Pavatil
The Name column of row 1 has been changed to Krina, just as what we expected.
Delete
We use the executeUpdate() method to delete a row. In the next example, we will delete the first row (which we just updated to Krina). Try to remember the delete sql command, delete from where huh? 😀
import java.sql.*;
class Test {
static public void main(String[] args)
throws SQLException {
var dbUrl = "jdbc:mysql://localhost/test?"
+ "user=testuser&password=Test@12345";
var conn = DriverManager.getConnection(dbUrl);
var stm = conn.createStatement();
var query = "delete from Test" +
" where Id = 1" +
" and Name = 'Krina';";
stm.executeUpdate(query);
}
}
Run the code and we will check the table Test again with the code below:
import java.sql.*;
class Test {
static public void main(String[] args)
throws SQLException {
var dbUrl = "jdbc:mysql://localhost/test?"
+ "user=testuser&password=Test@12345";
var conn = DriverManager.getConnection(dbUrl);
var stm = conn.createStatement();
var query = "select * from Test";
ResultSet set = stm.executeQuery(query);
while (set.next()){
System.out.println(
"Id: " + set.getInt("Id") + " - " +
"Name: " + set.getString("Name")
);
}
}
}
The output will be:
Id: 2 - Name: Prisha Id: 3 - Name: Pavatil
Delete a table
We will use update() method again to delete the Test table with the sql drop table command:
import java.sql.*;
class Test {
static public void main(String[] args)
throws SQLException {
var dbUrl = "jdbc:mysql://localhost/test?"
+ "user=testuser&password=Test@12345";
var conn = DriverManager.getConnection(dbUrl);
var stm = conn.createStatement();
var query = "drop table Test";
stm.execute(query);
}
}
Now, just run the code and open the terminal, login mysql promt and check the test database to see if the Test table has been deleted.