Java JDBC CRUD Example with MySQL

Below are the Java code samples that uses Java Database Connectivity API (JDBC) to Create, Retrieve, Update and Delete records using MySQL databases. Before running below examples, you must add Connector/J i.e. MySQL Database driver in your classpath, Click Here to Download Connector/J. I have created a database named cuonline that contains a 'students' table. The SQL statement to create the table is given below:

CREATE TABLE `students` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) DEFAULT NULL,
  `email` varchar(45) DEFAULT NULL,
  `gender` varchar(45) DEFAULT NULL,
  `status` tinyint(4) DEFAULT NULL,
  `city` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=latin1;

You noticed, id column is set as 'auto-increment', so it would be automatically initialized if we do not pass its value while inserting new records. For each record, database would increment the value by one while inserting new record in table.

Insert Statement JDBC Example

import java.sql.*;

public class JDBCTest {
    public static void main(String[] args) {
        User user = new User(5, "Oracle", "oracle@example.com", true, "M", "LHR");
        Connection connection = null;
        Statement statement = null;

        try {
            Class.forName("com.mysql.jdbc.Driver");
            connection = DriverManager.getConnection("jdbc:mysql://localhost/cuonline", "root", "WriteYourPasswordHere");
            statement = connection.createStatement();

            String SQL = "INSERT INTO students(id, name, email, gender, status, city) " +
                    "VALUES( " + user.getId() + ", '" + user.getName() + "', '" + user.getEmail() + "', '"
                    + user.getGender() + "', " + user.isStatus() + ",'" + user.getCity() + "'')";

            int num = statement.executeUpdate(SQL);

            System.out.println(num + " Rows updated. ");

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if (statement != null) statement.close();
                if (connection != null) connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
}

Select Statement Example using JDBC

import java.sql.*;

public class JDBCTest {
    public static void main(String[] args) {
        Connection connection = null;
        Statement statement = null;
        ResultSet resultSet = null;

        try {
            Class.forName("com.mysql.jdbc.Driver");
            connection = DriverManager.getConnection("jdbc:mysql://localhost/cuonline", "root", "WriteYourPasswordHere");
            statement = connection.createStatement();
            resultSet = statement.executeQuery("SELECT * FROM students");

            while (resultSet.next()) {
                System.out.print(resultSet.getInt("id"));
                System.out.print(resultSet.getString("name"));
                System.out.print(resultSet.getString("email"));
                System.out.print(resultSet.getString("gender"));
                System.out.print(resultSet.getBoolean("status"));
            }
            
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if (resultSet != null) resultSet.close();
                if (statement != null) statement.close();
                if (connection != null) connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
}

Update Statement JDBC Example

import java.sql.*;

public class JDBCTest {
    public static void main(String[] args) {
        Connection connection = null;
        Statement statement = null;

        try {
            Class.forName("com.mysql.jdbc.Driver");
            connection = DriverManager.getConnection("jdbc:mysql://localhost/cuonline", "root", "WriteYourPasswordHere");
            statement = connection.createStatement();
            int num = statement.executeUpdate("UPDATE students SET name='Bob', email='bob@gmail.com' WHERE id=1");
            System.out.println(num + " Rows updated. ");
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                statement.close();
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
}

Delete Statement JDBC Example

import java.sql.*;

public class JDBCTest {
    public static void main(String[] args) {
        Connection connection = null;
        Statement statement = null;
        try {
            Class.forName("com.mysql.jdbc.Driver");
            connection = DriverManager.getConnection("jdbc:mysql://localhost/cuonline", "root", "WriteYourPasswordHere");
            statement = connection.createStatement();
            int num = statement.executeUpdate("DELETE FROM students WHERE id=1");
            System.out.println(num + " Rows updated. ");

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if (statement != null) statement.close();
                if (connection != null) connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
}

Insert Statement JDBC Example using PreparedStatement

Its tedius to draft insert SQL statement when the number of columns are large and when we want to use null and datatime objects. For these cases we need to be very carefull about data format and using single quotes properly. If our value also contains single or double quotes, we need to escape them using \ character. So the solution is to use the PreparedStatement which also gives performance increase. Below code demonstrate how you can insert records in database using PreparedStatement.

import java.sql.*;

public class JDBCTest {
    public static void main(String[] args) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;

        try {
            Class.forName("com.mysql.jdbc.Driver");
            connection = DriverManager.getConnection("jdbc:mysql://localhost/cuonline", "root", "WriteYourPasswordHere");
            String insertQuery = "INSERT INTO students(name, email, gender, status, city) VALUES(?,?,?,?,?)";
            preparedStatement = connection.prepareStatement(insertQuery);
            preparedStatement.setString(1, "Alice");
            preparedStatement.setString(2, "alice@example.com");
            preparedStatement.setString(3, "F");
            preparedStatement.setBoolean(4, true);
            preparedStatement.setString(5, "Berlin");

            int num = preparedStatement.executeUpdate();
            System.out.println(num + " Rows effected. ");

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if (preparedStatement != null) preparedStatement.close();
                if (connection != null) connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
}

Prepared statement can be used to update the record, you need use UPDATE query rather INSERT. Use ? where values are inserted in UPDATE, then add values using different set methods as we did in above code sample.