Java 基础示例

Note

本篇文档所介绍的演示程序的源代码下载地址为:Java CRUD 示例

配置环境

在开始之前,请确保已经下载并安装了以下软件。

  1. mysql> create database test;

image-20220927102516885

Note

我们使用 IDEA 作为一个 IDE 示例来演示这个过程,你可以自由地选择 Eclipse 或其他 IDE 工具实践。

初始化一个新的 Java 项目

启动 IDEA,并创建一个新的 Java 项目,如下所示:

image-20220927104740221

进入菜单 Project Setting > Libraries,导入 mysql-connector-java-8.0.30.jar 文件。

image-20220927104904770

编写 Java 代码连接 MatrixOne

首先,创建一个名为 JDBCUtils 的 Java 类作为连接实用程序。这个类将作为连接 MatrixOne 和执行 SQL 查询的工具。

src 目录下,创建一个名为 JDBCUtils.java 的文件,并使用以下代码编辑该文件:

  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. }

其次,我们用 MatrixOne 编写创建、插入、更新和删除操作的示例代码。

我们需要在 src 目录下创建相应的 java 源代码文件:Create.javaInsert.javaUpdate.javaSelect.java,并将下面的代码对应放在这些文件中。

创建(Create.java)

  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. }

执行上述代码会在 test 数据库中创建一个表,然后你可以在 MySQL 客户端中使用如下代码验证是否创建了表。

  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.java

  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. }

执行结果:

  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.java

  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. }

运行结果:

  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.java

  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. }

运行结果:

image-20220927113440917