Build a simple Java CRUD demo with MatrixOne

Note

The source code of this demo can be downloaded at github.

Setup your environment

Before you start, make sure you have downloaded and installed the following software.

  • MatrixOne 0.5.1: Follow the installation instruction and launch MatrixOne 0.5.1. Create a database by MySQL client.
  1. mysql> CREATE DATABASE TEST;

!Note:We take IDEA as an IDE example to demonstrate the process, you are free to choose Eclipse or other IDE tools for the same purpose.

image-20220927102516885

Initialize a new Java project

Launch IDEA, and create a new Java project as below:

image-20220927104740221

In your Project Setting > Libraries, import the mysql-connector-java-8.0.xx.jar file.

image-20220927104904770

Write Java code to connect with MatrixOne

Firstly we create a Java class named as JDBCUtils as a connection utility. This class will serve as a tool to connect with MatrixOne and execute SQL queries.

  1. import java.sql.Connection;
  2. import java.sql.DriverManager;
  3. import java.sql.SQLException;
  4. public class JDBCUtils {
  5. private static String jdbcURL = "jdbc:mysql://127.0.0.1:6001/TEST";
  6. private static String jdbcUsername = "dump";
  7. private static String jdbcPassword = "111";
  8. public static Connection getConnection() {
  9. Connection connection = null;
  10. try {
  11. connection = DriverManager.getConnection(jdbcURL, jdbcUsername, jdbcPassword);
  12. } catch (SQLException e) {
  13. // TODO Auto-generated catch block
  14. e.printStackTrace();
  15. }
  16. return connection;
  17. }
  18. public static void printSQLException(SQLException ex) {
  19. for (Throwable e : ex) {
  20. if (e instanceof SQLException) {
  21. e.printStackTrace(System.err);
  22. System.err.println("SQLState: " + ((SQLException) e).getSQLState());
  23. System.err.println("Error Code: " + ((SQLException) e).getErrorCode());
  24. System.err.println("Message: " + e.getMessage());
  25. Throwable t = ex.getCause();
  26. while (t != null) {
  27. System.out.println("Cause: " + t);
  28. t = t.getCause();
  29. }
  30. }
  31. }
  32. }
  33. }

Secondly we write example code for Create/Insert/Update/Delete operations with MatrixOne.

Create

  1. import java.sql.Connection;
  2. import java.sql.SQLException;
  3. import java.sql.Statement;
  4. public class Create {
  5. private static final String createTableSQL = "create table student (\r\n" + " id int primary key,\r\n" +
  6. " name varchar(20),\r\n" + " email varchar(20),\r\n" + " country varchar(20),\r\n" +
  7. " age int\r\n" + " );";
  8. public static void main(String[] argv) throws SQLException {
  9. Create createTable = new Create();
  10. createTable.createTable();
  11. }
  12. public void createTable() throws SQLException {
  13. System.out.println(createTableSQL);
  14. // Step 1: Establishing a Connection
  15. try (Connection connection = JDBCUtils.getConnection();
  16. // Step 2:Create a statement using connection object
  17. Statement statement = connection.createStatement();) {
  18. // Step 3: Execute the query or update query
  19. statement.execute(createTableSQL);
  20. } catch (SQLException e) {
  21. // print SQL exception information
  22. JDBCUtils.printSQLException(e);
  23. }
  24. // Step 4: try-with-resource statement will auto close the connection.
  25. }
  26. }

Executing this code will create a table in the TEST database.

  1. mysql> show create table student;
  2. +---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  3. | Table | Create Table |
  4. +---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  5. | student | CREATE TABLE `student` (
  6. `id` INT DEFAULT NULL,
  7. `name` VARCHAR(20) DEFAULT NULL,
  8. `email` VARCHAR(20) DEFAULT NULL,
  9. `country` VARCHAR(20) DEFAULT NULL,
  10. `age` INT DEFAULT NULL,
  11. PRIMARY KEY (`id`)
  12. ) |
  13. +---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  14. 1 row in set (0.01 sec)

Insert

  1. import java.sql.Connection;
  2. import java.sql.PreparedStatement;
  3. import java.sql.SQLException;
  4. public class Insert {
  5. private static final String INSERT_STUDENT_SQL = "INSERT INTO student" +
  6. " (id, name, email, country, age) VALUES " +
  7. " (?, ?, ?, ?, ?);";
  8. public static void main(String[] argv) throws SQLException {
  9. Insert insertTable = new Insert();
  10. insertTable.insertRecord();
  11. }
  12. public void insertRecord() throws SQLException {
  13. System.out.println(INSERT_STUDENT_SQL);
  14. // Step 1: Establishing a Connection
  15. try (Connection connection = JDBCUtils.getConnection();
  16. // Step 2:Create a statement using connection object
  17. PreparedStatement preparedStatement = connection.prepareStatement(INSERT_STUDENT_SQL)) {
  18. preparedStatement.setInt(1, 1);
  19. preparedStatement.setString(2, "Tony");
  20. preparedStatement.setString(3, "tony@gmail.com");
  21. preparedStatement.setString(4, "US");
  22. preparedStatement.setString(5, "20");
  23. System.out.println(preparedStatement);
  24. // Step 3: Execute the query or update query
  25. preparedStatement.executeUpdate();
  26. } catch (SQLException e) {
  27. // print SQL exception information
  28. JDBCUtils.printSQLException(e);
  29. }
  30. // Step 4: try-with-resource statement will auto close the connection.
  31. }
  32. }

Execution result:

  1. mysql> select * from student;
  2. +------+------+----------------+---------+------+
  3. | id | name | email | country | age |
  4. +------+------+----------------+---------+------+
  5. | 1 | Tony | tony@gmail.com | US | 20 |
  6. +------+------+----------------+---------+------+
  7. 1 row in set (0.01 sec)

Update

  1. import java.sql.Connection;
  2. import java.sql.PreparedStatement;
  3. import java.sql.SQLException;
  4. public class Update {
  5. private static final String UPDATE_STUDENT_SQL = "update student set name = ? where id = ?;";
  6. public static void main(String[] argv) throws SQLException {
  7. Update updateTable = new Update();
  8. updateTable.updateRecord();
  9. }
  10. public void updateRecord() throws SQLException {
  11. System.out.println(UPDATE_STUDENT_SQL);
  12. // Step 1: Establishing a Connection
  13. try (Connection connection = JDBCUtils.getConnection();
  14. // Step 2:Create a statement using connection object
  15. PreparedStatement preparedStatement = connection.prepareStatement(UPDATE_STUDENT_SQL)) {
  16. preparedStatement.setString(1, "Ram");
  17. preparedStatement.setInt(2, 1);
  18. // Step 3: Execute the query or update query
  19. preparedStatement.executeUpdate();
  20. } catch (SQLException e) {
  21. // print SQL exception information
  22. JDBCUtils.printSQLException(e);
  23. }
  24. // Step 4: try-with-resource statement will auto close the connection.
  25. }
  26. }

Execution result:

  1. mysql> select * from student;
  2. +------+------+----------------+---------+------+
  3. | id | name | email | country | age |
  4. +------+------+----------------+---------+------+
  5. | 1 | Ram | tony@gmail.com | US | 20 |
  6. +------+------+----------------+---------+------+
  7. 1 row in set (0.00 sec)

SELECT

  1. import java.sql.Connection;
  2. import java.sql.PreparedStatement;
  3. import java.sql.ResultSet;
  4. import java.sql.SQLException;
  5. public class Select {
  6. private static final String QUERY = "select id,name,email,country,age from student where id =?";
  7. public static void main(String[] args) {
  8. // using try-with-resources to avoid closing resources (boiler plate code)
  9. // Step 1: Establishing a Connection
  10. try (Connection connection = JDBCUtils.getConnection();
  11. // Step 2:Create a statement using connection object
  12. PreparedStatement preparedStatement = connection.prepareStatement(QUERY);) {
  13. preparedStatement.setInt(1, 1);
  14. System.out.println(preparedStatement);
  15. // Step 3: Execute the query or update query
  16. ResultSet rs = preparedStatement.executeQuery();
  17. // Step 4: Process the ResultSet object.
  18. while (rs.next()) {
  19. int id = rs.getInt("id");
  20. String name = rs.getString("name");
  21. String email = rs.getString("email");
  22. String country = rs.getString("country");
  23. String password = rs.getString("age");
  24. System.out.println(id + "," + name + "," + email + "," + country + "," + password);
  25. }
  26. } catch (SQLException e) {
  27. JDBCUtils.printSQLException(e);
  28. }
  29. // Step 4: try-with-resource statement will auto close the connection.
  30. }
  31. }

Execution result:

image-20220927113440917