JDBC CRUD

MySQL 为例，依赖：https://central.sonatype.com/artifact/com.mysql/mysql-connector-j

<dependency>
    <groupId>com.mysql</groupId>
    <artifactId>mysql-connector-j</artifactId>
</dependency>

增删改

Class.forName("com.mysql.cj.jdbc.Driver"); // 会执行对应类的静态代码块
DriverManager.setLogWriter(new PrintWriter(System.out));
DriverManager.println("test");
try (Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/employees", "root", "root");
     Statement statement = connection.createStatement()) {
    statement.execute("use employees");
    statement.executeUpdate("INSERT INTO departments (dept_no, dept_name) VALUES ('test', 'test')");
    statement.executeUpdate("UPDATE departments SET dept_name = '123' WHERE dept_no = 'test'");
    statement.execute("DELETE FROM departments WHERE dept_no = 'test'");

    statement.execute("INSERT INTO room (name) VALUES ('test')", Statement.RETURN_GENERATED_KEYS);
    ResultSet resultSet = statement.getGeneratedKeys();
    while (resultSet.next()) { // 获取插入数据的自增主键
        System.out.println(resultSet.getLong(1));
        System.out.println(resultSet.getLong("GENERATED_KEY"));
    }
}

// Driver driver = new com.mysql.cj.jdbc.Driver();
Driver driver = (Driver) Class.forName("com.mysql.cj.jdbc.Driver").getDeclaredConstructor().newInstance();
Properties info = new Properties();
info.put("user", "root");
info.put("password", "root");
try (Connection connection = driver.connect("jdbc:mysql://localhost:3306/employees", info);
     PreparedStatement preparedStatement = connection.prepareStatement("INSERT INTO departments (dept_no, dept_name) VALUES (?, ?)");
     PreparedStatement ps = connection.prepareStatement("DELETE FROM departments WHERE dept_no = ?");
     PreparedStatement ps1 = connection.prepareStatement("INSERT INTO room (name) VALUES (?)", Statement.RETURN_GENERATED_KEYS)) {
    preparedStatement.setObject(1, "test");
    preparedStatement.setObject(2, "test");
    preparedStatement.execute();

    ps.setString(1, "test");
    ps.execute();

    ps1.setString(1, "test");
    ps1.execute();
    ResultSet resultSet = ps1.getGeneratedKeys();
    while (resultSet.next()) {
        System.out.println(resultSet.getLong(1));
        System.out.println(resultSet.getLong("GENERATED_KEY"));
    }
}

查

Class.forName("com.mysql.cj.jdbc.Driver"); // 会执行对应类的静态代码块
try (Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/employees", "root", "root");
     Statement statement = connection.createStatement();
     PreparedStatement preparedStatement = connection.prepareStatement("SELECT * FROM employees WHERE emp_no = ?")) {
    statement.execute("use employees");
    ResultSet resultSet = statement.executeQuery("SELECT * FROM employees");
    while (resultSet.next()) {
        Object id = resultSet.getObject(1);
        Date birthDate = resultSet.getDate("birth_date");
        String firstName = resultSet.getString(3);
        String lastName = resultSet.getString("last_name");
        String gender = resultSet.getString("gender");
        Date hireDate = resultSet.getDate("hire_date");
        System.out.println(id + "\t" + birthDate + "\t" + firstName + "\t" + lastName + "\t" + gender + "\t" + hireDate);
    }

    preparedStatement.setString(1, "10001");
    resultSet = preparedStatement.executeQuery();
    while (resultSet.next()) {
        Object id = resultSet.getObject(1);
        Date birthDate = resultSet.getDate("birth_date");
        String firstName = resultSet.getString(3);
        String lastName = resultSet.getString("last_name");
        String gender = resultSet.getString("gender");
        Date hireDate = resultSet.getDate("hire_date");
        System.out.println(id + "\t" + birthDate + "\t" + firstName + "\t" + lastName + "\t" + gender + "\t" + hireDate);
    }
}

 

---

https://docs.oracle.com/javase/tutorial/jdbc