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;

5. public class JDBCUtils {
6. private static String jdbcURL = "jdbc:mysql://127.0.0.1:6001/test";
7. private static String jdbcUsername = "dump";
8. private static String jdbcPassword = "111";

10. public static Connection getConnection() {
11. Connection connection = null;
12. try {
13. connection = DriverManager.getConnection(jdbcURL, jdbcUsername, jdbcPassword);
14. } catch (SQLException e) {
15. // TODO Auto-generated catch block
16. e.printStackTrace();
17. }
18. return connection;
19. }

21. public static void printSQLException(SQLException ex) {
22. for (Throwable e : ex) {
23. if (e instanceof SQLException) {
24. e.printStackTrace(System.err);
25. System.err.println("SQLState: " + ((SQLException) e).getSQLState());
26. System.err.println("Error Code: " + ((SQLException) e).getErrorCode());
27. System.err.println("Message: " + e.getMessage());
28. Throwable t = ex.getCause();
29. while (t != null) {
30. System.out.println("Cause: " + t);
31. t = t.getCause();
32. }
33. }
34. }
35. }
36. }

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

我们需要在 src 目录下创建相应的 java 源代码文件：Create.java、Insert.java、Update.java、Select.java，并将下面的代码对应放在这些文件中。

创建（Create.java)

1. import java.sql.Connection;
2. import java.sql.SQLException;
3. import java.sql.Statement;

5. public class Create {
6. private static final String createTableSQL = "create table student (\r\n" + "  id int primary key,\r\n" +
7. "  name varchar(20),\r\n" + "  email varchar(20),\r\n" + "  country varchar(20),\r\n" +
8. "  age int\r\n" + "  );";

10. public static void main(String[] argv) throws SQLException {
11. Create createTable = new Create();
12. createTable.createTable();
13. }

15. public void createTable() throws SQLException {

17. System.out.println(createTableSQL);
18. // Step 1: Establishing a Connection
19. try (Connection connection = JDBCUtils.getConnection();
20. // Step 2:Create a statement using connection object
21. Statement statement = connection.createStatement();) {

23. // Step 3: Execute the query or update query
24. statement.execute(createTableSQL);
25. } catch (SQLException e) {

27. // print SQL exception information
28. JDBCUtils.printSQLException(e);
29. }

31. // Step 4: try-with-resource statement will auto close the connection.
32. }
33. }

执行上述代码会在 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;

5. public class Insert {
6. private static final String INSERT_STUDENT_SQL = "INSERT INTO student" +
7. "  (id, name, email, country, age) VALUES " +
8. " (?, ?, ?, ?, ?);";

10. public static void main(String[] argv) throws SQLException {
11. Insert insertTable = new Insert();
12. insertTable.insertRecord();
13. }

15. public void insertRecord() throws SQLException {
16. System.out.println(INSERT_STUDENT_SQL);
17. // Step 1: Establishing a Connection
18. try (Connection connection = JDBCUtils.getConnection();
19. // Step 2:Create a statement using connection object
20. PreparedStatement preparedStatement = connection.prepareStatement(INSERT_STUDENT_SQL)) {
21. preparedStatement.setInt(1, 1);
22. preparedStatement.setString(2, "Tony");
23. preparedStatement.setString(3, "tony@gmail.com");
24. preparedStatement.setString(4, "US");
25. preparedStatement.setString(5, "20");

27. System.out.println(preparedStatement);
28. // Step 3: Execute the query or update query
29. preparedStatement.executeUpdate();
30. } catch (SQLException e) {

32. // print SQL exception information
33. JDBCUtils.printSQLException(e);
34. }

36. // Step 4: try-with-resource statement will auto close the connection.
37. }

39. }

执行结果：

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;

5. public class Update {
6. private static final String UPDATE_STUDENT_SQL = "update student set name = ? where id = ?;";

8. public static void main(String[] argv) throws SQLException {
9. Update updateTable = new Update();
10. updateTable.updateRecord();
11. }

13. public void updateRecord() throws SQLException {
14. System.out.println(UPDATE_STUDENT_SQL);
15. // Step 1: Establishing a Connection
16. try (Connection connection = JDBCUtils.getConnection();
17. // Step 2:Create a statement using connection object
18. PreparedStatement preparedStatement = connection.prepareStatement(UPDATE_STUDENT_SQL)) {
19. preparedStatement.setString(1, "Ram");
20. preparedStatement.setInt(2, 1);

22. // Step 3: Execute the query or update query
23. preparedStatement.executeUpdate();
24. } catch (SQLException e) {

26. // print SQL exception information
27. JDBCUtils.printSQLException(e);
28. }

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 | 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;

6. public class Select {
7. private static final String QUERY = "select id,name,email,country,age from student where id =?";

9. public static void main(String[] args) {

11. // using try-with-resources to avoid closing resources (boiler plate code)

13. // Step 1: Establishing a Connection
14. try (Connection connection = JDBCUtils.getConnection();

16. // Step 2:Create a statement using connection object
17. PreparedStatement preparedStatement = connection.prepareStatement(QUERY);) {
18. preparedStatement.setInt(1, 1);
19. System.out.println(preparedStatement);
20. // Step 3: Execute the query or update query
21. ResultSet rs = preparedStatement.executeQuery();

23. // Step 4: Process the ResultSet object.
24. while (rs.next()) {
25. int id = rs.getInt("id");
26. String name = rs.getString("name");
27. String email = rs.getString("email");
28. String country = rs.getString("country");
29. String password = rs.getString("age");
30. System.out.println(id + "," + name + "," + email + "," + country + "," + password);
31. }
32. } catch (SQLException e) {
33. JDBCUtils.printSQLException(e);
34. }
35. // Step 4: try-with-resource statement will auto close the connection.
36. }
37. }

运行结果：

image-20220927113440917