添加，修改，删除

package test.jdbc;import org.junit.jupiter.api.AfterEach;import org.junit.jupiter.api.BeforeEach;import org.junit.jupiter.api.Test;import java.io.InputStream;import java.sql.*;import java.util.Properties;public class CURDTest {    private Connection conn;    private Statement statement;    private ResultSet result;    @BeforeEach    public void start() throws Exception {        Properties properties = new Properties();        InputStream in = this.getClass().getClassLoader().getResourceAsStream("jdbc.properties");        properties.load(in);        String driver = properties.getProperty("driver");        String jdbcUrl = properties.getProperty("jdbcUrl");        String user = properties.getProperty("user");        String password = properties.getProperty("password");        Class.forName(driver);        conn = DriverManager.getConnection(jdbcUrl, user, password);    }    @AfterEach    public void end() throws Exception {        if (result != null) {            result.close();        }        if (statement != null) {            statement.close();        }        if (conn != null) {            conn.close();        }    }    /**     * 通过 JDBC 向指定的数据表中插入一条记录     *      * Statement: 用于执行 SQL 语句的对象     * SQL 可以是 INSERT、UPDATE、DELETE. 不能为 SELECT     * Connection、Statement 都是应用程序和数据库服务器的连接资源. 使用后要关闭     */    @Test    public void testStatement() throws Exception {        try {            statement = conn.createStatement();            // SQL 语句            statement.executeUpdate("INSERT INTO user (name, age) VALUES('SWT', 22)");            // SQL 占位符            PreparedStatement preparedStatement = conn.prepareStatement("INSERT INTO user (name, age) VALUES(?, ?)");            preparedStatement.setObject(1,"WC");            preparedStatement.setObject(2,23);            preparedStatement.executeUpdate();        } catch (Exception e) {            e.printStackTrace();        }    }}

添加数据时返回主键

@Testpublic void testGetKeyValue() {    try {        String sql = "INSERT INTO user (name, age) VALUES(?, ?)";        // 使用重载的 prepareStatement(sql, flag)         PreparedStatement preparedStatement = conn.prepareStatement(sql,Statement.RETURN_GENERATED_KEYS);        preparedStatement.setObject(1, "ABCDE");        preparedStatement.setObject(2, 22);        preparedStatement.execute();        // 通过 getGeneratedKeys() 获取包含了新生成的主键的 ResultSet 对象        // 在 ResultSet 中只有一列 GENERATED_KEY, 用于存放新生成的主键值        ResultSet rs = preparedStatement.getGeneratedKeys();        if(rs.next()){            System.out.println(rs.getInt("GENERATED_KEY"));        }    } catch (Exception e) {        e.printStackTrace();    }}

查询

/** * ResultSet: 结果集. 封装了使用 JDBC 进行查询的结果 * 调用 Statement 对象的 executeQuery(sql) 可以得到 ResultSet 结果集 * 调用 ResultSet 的 next() 方法检测下一行是否有效. 若有效该方法返回 true, 且指针下移 * 当指针对位到一行时, 可以通过调用 getXxx(index) 或 getXxx(columnName) 得到数据 */@Testpublic void testResultSet() {    try {        // 获取 Statement        statement = conn.createStatement();        // SQL        String sql = "SELECT * FROM user";        // 执行查询, 得到 ResultSet        result = statement.executeQuery(sql);        // 处理 ResultSet        while (result.next()) {            int id = result.getInt(1);            String name = result.getString("name");            int age = result.getInt(3);            System.out.println(id + "\t" + name + "\t" + age);        }        PreparedStatement preparedStatement = conn.prepareStatement("SELECT * FROM user where id = ?");        preparedStatement.setObject(1,1);        result = preparedStatement.executeQuery();        while (result.next()) {            int id = result.getInt(1);            String name = result.getString("name");            int age = result.getInt(3);            System.out.println(id + "\t" + name + "\t" + age);        }    } catch (Exception e) {        e.printStackTrace();    }}

 

---