In this tutorial, we will learn basic MySQL database operations (CRUD - Create, Retrieve, Update and Delete) using JDBC (Java Database Connectivity) API in Java.
These CRUD operations are equivalent to the CREATE, SELECT, UPDATE and DELETE statements in SQL language. Although the target database system is MySQL, the same technique can be applied for other database systems as well because the query syntax used is standard SQL which is supported by all relational database systems.
Video
This tutorial is explained in the below Youtube Video. Subscribe to my youtube channel to learn more about Spring boot at Java Guides - YouTube Channel.
Let's start with creating a users table and then we will perform INSERT, UPDATE, SELECT, and DELETE operations.
Create a Table Example
Let's use Statement interface to create a table - Statement object used for executing a static SQL statement and returning the results it produces.
- Table - users
- Databases - mysql_database
- DDL Script
create table users(
id int(3) primary key,
name varchar(20),
email varchar(20),
country varchar(20),
password varchar(20)
);
Here is a complete Java JDBC program to create a users table in a database:
package com.javaguides.jdbc.statement.examples;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
/**
* Create Statement JDBC Example
* @author Ramesh Fadatare
*
*/
public class CreateStatementExample {
private static final String createTableSQL = "create table users (\r\n" + " id int(3) primary key,\r\n" +
" name varchar(20),\r\n" + " email varchar(20),\r\n" + " country varchar(20),\r\n" +
" password varchar(20)\r\n" + " );";
public static void main(String[] argv) throws SQLException {
CreateStatementExample createTableExample = new CreateStatementExample();
createTableExample.createTable();
}
public void createTable() throws SQLException {
System.out.println(createTableSQL);
// Step 1: Establishing a Connection
try (Connection connection = DriverManager
.getConnection("jdbc:mysql://localhost:3306/mysql_database?useSSL=false", "root", "root");
// Step 2:Create a statement using connection object
Statement statement = connection.createStatement();) {
// Step 3: Execute the query or update query
statement.execute(createTableSQL);
} catch (SQLException e) {
// print SQL exception information
printSQLException(e);
}
// Step 4: try-with-resource statement will auto close the connection.
}
public static void printSQLException(SQLException ex) {
for (Throwable e: ex) {
if (e instanceof SQLException) {
e.printStackTrace(System.err);
System.err.println("SQLState: " + ((SQLException) e).getSQLState());
System.err.println("Error Code: " + ((SQLException) e).getErrorCode());
System.err.println("Message: " + e.getMessage());
Throwable t = ex.getCause();
while (t != null) {
System.out.println("Cause: " + t);
t = t.getCause();
}
}
}
}
}
Executing Insert Statement - Insert a Record Example
In this example, we use a PreparedStatement interface to insert users record. As we know PreparedStatement interface improves performance like SQL statement is precompiled and stored in a PreparedStatement object. This object can then be used to efficiently execute this statement multiple times.
package com.javaguides.jdbc.preparestatement.examples;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
/**
* Insert PrepareStatement JDBC Example
*
* @author Ramesh Fadatare
*
*/
public class InsertPStatementExample {
private static final String INSERT_USERS_SQL = "INSERT INTO users" +
" (id, name, email, country, password) VALUES " +
" (?, ?, ?, ?, ?);";
public static void main(String[] argv) throws SQLException {
InsertPStatementExample createTableExample = new InsertPStatementExample();
createTableExample.insertRecord();
}
public void insertRecord() throws SQLException {
System.out.println(INSERT_USERS_SQL);
// Step 1: Establishing a Connection
try (Connection connection = DriverManager
.getConnection("jdbc:mysql://localhost:3306/mysql_database?useSSL=false", "root", "root");
// Step 2:Create a statement using connection object
PreparedStatement preparedStatement = connection.prepareStatement(INSERT_USERS_SQL)) {
preparedStatement.setInt(1, 1);
preparedStatement.setString(2, "Tony");
preparedStatement.setString(3, "tony@gmail.com");
preparedStatement.setString(4, "US");
preparedStatement.setString(5, "secret");
System.out.println(preparedStatement);
// Step 3: Execute the query or update query
preparedStatement.executeUpdate();
} catch (SQLException e) {
// print SQL exception information
printSQLException(e);
}
// Step 4: try-with-resource statement will auto close the connection.
}
public static void printSQLException(SQLException ex) {
for (Throwable e: ex) {
if (e instanceof SQLException) {
e.printStackTrace(System.err);
System.err.println("SQLState: " + ((SQLException) e).getSQLState());
System.err.println("Error Code: " + ((SQLException) e).getErrorCode());
System.err.println("Message: " + e.getMessage());
Throwable t = ex.getCause();
while (t != null) {
System.out.println("Cause: " + t);
t = t.getCause();
}
}
}
}
}
Executing SELECT Statement - Select Records Example
So far we have created a users table and inserted a few records into it. So now we will query and retrieve a unique user from the database table by id.
package com.javaguides.jdbc.preparestatement.examples;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
/**
* Select PreparedStatement JDBC Example
*
* @author Ramesh Fadatare
*
*/
public class SelectPStatementExample {
private static final String QUERY = "select id,name,email,country,password from users where id =?";
public static void main(String[] args) {
// using try-with-resources to avoid closing resources (boiler plate code)
// Step 1: Establishing a Connection
try (Connection connection = DriverManager
.getConnection("jdbc:mysql://localhost:3306/mysql_database?useSSL=false", "root", "root");
// Step 2:Create a statement using connection object
PreparedStatement preparedStatement = connection.prepareStatement(QUERY);) {
preparedStatement.setInt(1, 1);
System.out.println(preparedStatement);
// Step 3: Execute the query or update query
ResultSet rs = preparedStatement.executeQuery();
// Step 4: Process the ResultSet object.
while (rs.next()) {
int id = rs.getInt("id");
String name = rs.getString("name");
String email = rs.getString("email");
String country = rs.getString("country");
String password = rs.getString("password");
System.out.println(id + "," + name + "," + email + "," + country + "," + password);
}
} catch (SQLException e) {
printSQLException(e);
}
// Step 4: try-with-resource statement will auto close the connection.
}
public static void printSQLException(SQLException ex) {
for (Throwable e: ex) {
if (e instanceof SQLException) {
e.printStackTrace(System.err);
System.err.println("SQLState: " + ((SQLException) e).getSQLState());
System.err.println("Error Code: " + ((SQLException) e).getErrorCode());
System.err.println("Message: " + e.getMessage());
Throwable t = ex.getCause();
while (t != null) {
System.out.println("Cause: " + t);
t = t.getCause();
}
}
}
}
}
Output:
1,Ram,tony@gmail.com,US,secretRead more JDBC SQL Select examples on JDBC PreparedStatement - Select Records Example.Executing UPDATE statement - Update a Record Example
We should use the PreparedStatement interface to perform SQL Update operation. In this example, we are updating name by id.
package com.javaguides.jdbc.preparestatement.examples;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
/**
* Update PreparedStatement JDBC Example
* @author Ramesh Fadatare
*
*/
public class UpdatePStatementExample {
private static final String UPDATE_USERS_SQL = "update users set name = ? where id = ?;";
public static void main(String[] argv) throws SQLException {
UpdatePStatementExample updateStatementExample = new UpdatePStatementExample();
updateStatementExample.updateRecord();
}
public void updateRecord() throws SQLException {
System.out.println(UPDATE_USERS_SQL);
// Step 1: Establishing a Connection
try (Connection connection = DriverManager
.getConnection("jdbc:mysql://localhost:3306/mysql_database?useSSL=false", "root", "root");
// Step 2:Create a statement using connection object
PreparedStatement preparedStatement = connection.prepareStatement(UPDATE_USERS_SQL)) {
preparedStatement.setString(1, "Ram");
preparedStatement.setInt(2, 1);
// Step 3: Execute the query or update query
preparedStatement.executeUpdate();
} catch (SQLException e) {
// print SQL exception information
printSQLException(e);
}
// Step 4: try-with-resource statement will auto close the connection.
}
public static void printSQLException(SQLException ex) {
for (Throwable e: ex) {
if (e instanceof SQLException) {
e.printStackTrace(System.err);
System.err.println("SQLState: " + ((SQLException) e).getSQLState());
System.err.println("Error Code: " + ((SQLException) e).getErrorCode());
System.err.println("Message: " + e.getMessage());
Throwable t = ex.getCause();
while (t != null) {
System.out.println("Cause: " + t);
t = t.getCause();
}
}
}
}
}
Read more JDBC SQL update examples on JDBC PreparedStatement - Update a Record Example.
Executing DELETE Statement - Delete a Record Example
Here we have a users table in a database and we will delete a record with following JDBC program.
package com.javaguides.jdbc.statement.examples;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
/**
* Delete Statement JDBC Example
* @author Ramesh Fadatare
*
*/
public class DeleteStatementExample {
private static final String DELETE_USERS_SQL = "delete from users where id = 3;";
public static void main(String[] argv) throws SQLException {
DeleteStatementExample deleteStatementExample = new DeleteStatementExample();
deleteStatementExample.deleteRecord();
}
public void deleteRecord() throws SQLException {
System.out.println(DELETE_USERS_SQL);
// Step 1: Establishing a Connection
try (Connection connection = DriverManager
.getConnection("jdbc:mysql://localhost:3306/mysql_database?useSSL=false", "root", "root");
// Step 2:Create a statement using connection object
Statement statement = connection.createStatement();) {
// Step 3: Execute the query or update query
int result = statement.executeUpdate(DELETE_USERS_SQL);
System.out.println("Number of records affected :: " + result);
} catch (SQLException e) {
// print SQL exception information
printSQLException(e);
}
// Step 4: try-with-resource statement will auto close the connection.
}
public static void printSQLException(SQLException ex) {
for (Throwable e: ex) {
if (e instanceof SQLException) {
e.printStackTrace(System.err);
System.err.println("SQLState: " + ((SQLException) e).getSQLState());
System.err.println("Error Code: " + ((SQLException) e).getErrorCode());
System.err.println("Message: " + e.getMessage());
Throwable t = ex.getCause();
while (t != null) {
System.out.println("Cause: " + t);
t = t.getCause();
}
}
}
}
}
Output:
delete from users where id = 3;
Number of records affected :: 1
Read more JDBC SQL Delete examples on JDBC Statement - Delete a Record Example.
Complete and Refactored Code
Here is a complete and refactored source code of this tutorial. You can get the source code of this tutorial from my GitHub repository.
JDBCUtils
Let's create a JDBCUtils class. keep common code here and use it in respective places.
package net.javaguides.jdbc.crud;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
public class JDBCUtils {
private static String jdbcURL = "jdbc:mysql://localhost:3306/java_demo?useSSL=false";
private static String jdbcUsername = "root";
private static String jdbcPassword = "root";
public static Connection getConnection() {
Connection connection = null;
try {
connection = DriverManager.getConnection(jdbcURL, jdbcUsername, jdbcPassword);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return connection;
}
public static void printSQLException(SQLException ex) {
for (Throwable e: ex) {
if (e instanceof SQLException) {
e.printStackTrace(System.err);
System.err.println("SQLState: " + ((SQLException) e).getSQLState());
System.err.println("Error Code: " + ((SQLException) e).getErrorCode());
System.err.println("Message: " + e.getMessage());
Throwable t = ex.getCause();
while (t != null) {
System.out.println("Cause: " + t);
t = t.getCause();
}
}
}
}
}
Create a Table Example - CreateStatementExample.java
package net.javaguides.jdbc.crud;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
/**
* Create Statement JDBC Example
* @author Ramesh Fadatare
*
*/
public class CreateStatementExample {
private static final String createTableSQL = "create table users (\r\n" + " id int(3) primary key,\r\n" +
" name varchar(20),\r\n" + " email varchar(20),\r\n" + " country varchar(20),\r\n" +
" password varchar(20)\r\n" + " );";
public static void main(String[] argv) throws SQLException {
CreateStatementExample createTableExample = new CreateStatementExample();
createTableExample.createTable();
}
public void createTable() throws SQLException {
System.out.println(createTableSQL);
// Step 1: Establishing a Connection
try (Connection connection = JDBCUtils.getConnection();
// Step 2:Create a statement using connection object
Statement statement = connection.createStatement();) {
// Step 3: Execute the query or update query
statement.execute(createTableSQL);
} catch (SQLException e) {
// print SQL exception information
JDBCUtils.printSQLException(e);
}
// Step 4: try-with-resource statement will auto close the connection.
}
}
Executing Insert Statement - Insert a Record Example
package net.javaguides.jdbc.crud;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
/**
* Insert PrepareStatement JDBC Example
*
* @author Ramesh Fadatare
*
*/
public class InsertPStatementExample {
private static final String INSERT_USERS_SQL = "INSERT INTO users" +
" (id, name, email, country, password) VALUES " +
" (?, ?, ?, ?, ?);";
public static void main(String[] argv) throws SQLException {
InsertPStatementExample createTableExample = new InsertPStatementExample();
createTableExample.insertRecord();
}
public void insertRecord() throws SQLException {
System.out.println(INSERT_USERS_SQL);
// Step 1: Establishing a Connection
try (Connection connection = JDBCUtils.getConnection();
// Step 2:Create a statement using connection object
PreparedStatement preparedStatement = connection.prepareStatement(INSERT_USERS_SQL)) {
preparedStatement.setInt(1, 1);
preparedStatement.setString(2, "Tony");
preparedStatement.setString(3, "tony@gmail.com");
preparedStatement.setString(4, "US");
preparedStatement.setString(5, "secret");
System.out.println(preparedStatement);
// Step 3: Execute the query or update query
preparedStatement.executeUpdate();
} catch (SQLException e) {
// print SQL exception information
JDBCUtils.printSQLException(e);
}
// Step 4: try-with-resource statement will auto close the connection.
}
}
Executing SELECT Statement - Select Records Example
package net.javaguides.jdbc.crud;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
/**
* Select PreparedStatement JDBC Example
*
* @author Ramesh Fadatare
*
*/
public class SelectPStatementExample {
private static final String QUERY = "select id,name,email,country,password from users where id =?";
public static void main(String[] args) {
// using try-with-resources to avoid closing resources (boiler plate code)
// Step 1: Establishing a Connection
try (Connection connection = JDBCUtils.getConnection();
// Step 2:Create a statement using connection object
PreparedStatement preparedStatement = connection.prepareStatement(QUERY);) {
preparedStatement.setInt(1, 1);
System.out.println(preparedStatement);
// Step 3: Execute the query or update query
ResultSet rs = preparedStatement.executeQuery();
// Step 4: Process the ResultSet object.
while (rs.next()) {
int id = rs.getInt("id");
String name = rs.getString("name");
String email = rs.getString("email");
String country = rs.getString("country");
String password = rs.getString("password");
System.out.println(id + "," + name + "," + email + "," + country + "," + password);
}
} catch (SQLException e) {
JDBCUtils.printSQLException(e);
}
// Step 4: try-with-resource statement will auto close the connection.
}
}
Executing UPDATE statement - Update a Record Example
package net.javaguides.jdbc.crud;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
/**
* Update PreparedStatement JDBC Example
* @author Ramesh Fadatare
*
*/
public class UpdatePStatementExample {
private static final String UPDATE_USERS_SQL = "update users set name = ? where id = ?;";
public static void main(String[] argv) throws SQLException {
UpdatePStatementExample updateStatementExample = new UpdatePStatementExample();
updateStatementExample.updateRecord();
}
public void updateRecord() throws SQLException {
System.out.println(UPDATE_USERS_SQL);
// Step 1: Establishing a Connection
try (Connection connection = JDBCUtils.getConnection();
// Step 2:Create a statement using connection object
PreparedStatement preparedStatement = connection.prepareStatement(UPDATE_USERS_SQL)) {
preparedStatement.setString(1, "Ram");
preparedStatement.setInt(2, 1);
// Step 3: Execute the query or update query
preparedStatement.executeUpdate();
} catch (SQLException e) {
// print SQL exception information
JDBCUtils.printSQLException(e);
}
// Step 4: try-with-resource statement will auto close the connection.
}
}
Learn complete JDBC APIs on JDBC 4.2 API Tutorial
Get source code of this tutorial on my GitHub Repository.
Free Spring Boot Tutorial | Full In-depth Course | Learn Spring Boot in 10 Hours
Watch this course on YouTube at Spring Boot Tutorial | Fee 10 Hours Full Course
Comments
Post a Comment