Introduction to JDBC

JDBC is a Java database connectivity technology. Here is a brief tutorial and a simple sample of programming with JDBC (This sample just show you how Jdbc works. The below code can be simplified a lot in a real product development):

public void test_jdbc() throws Exception {
    // Download the specific jdbc driver from web site. Here we use H2 as a sample. 

    final String driver = "org.h2.Driver";
    final String url = "jdbc:h2:tcp://localhost/~/codes";
    final String user = "SA";
    final String password = "";

    DriverManager.registerDriver((Driver) N.forClass(driver).newInstance());

    // insert a record into database
    final String sql_insert = "INSERT INTO account (gui, first_name, last_name, last_update_time, create_time) VALUES (?, ?, ?, ?, ?)";
    long id = 0;
    Connection conn = null;
    PreparedStatement stmt = null;
    ResultSet rs = null;

    try {
        // get a connection
        conn = DriverManager.getConnection(url, user, password);

        // prepare the statement by sql.
        stmt = conn.prepareStatement(sql_insert, Statement.RETURN_GENERATED_KEYS);

        // set parameters
        int parameterIndex = 1;
        stmt.setString(parameterIndex++, N.uuid());
        stmt.setString(parameterIndex++, "Jack");
        stmt.setString(parameterIndex++, "Ye");
        stmt.setTimestamp(parameterIndex++, N.currentTimestamp());
        stmt.setTimestamp(parameterIndex++, N.currentTimestamp());

        stmt.execute();

        rs = stmt.getGeneratedKeys();

        if (rs.next()) {
            id = rs.getLong(1);
        }
    } finally {
        //  close ResultSet, PreparedStatement and Connection in finally block.
        closeQuietly(rs, stmt, conn);
    }

    // Read the inserted account from database
    final String sql_getById = "SELECT id AS \"id\", gui AS \"gui\", first_name AS \"firstName\", last_name AS \"lastName\" FROM account WHERE id = ?";
    Account dbAccount = null;
    conn = null;
    stmt = null;
    rs = null;

    try {
        conn = DriverManager.getConnection(url, user, password);
        stmt = conn.prepareStatement(sql_getById);
        stmt.setLong(1, id);

        rs = stmt.executeQuery();

        if (rs.next()) {
            dbAccount = new Account();
            dbAccount.setId(rs.getLong("id"));
            dbAccount.setGUI(rs.getString("firstName"));
            dbAccount.setFirstName(rs.getString("firstName"));
            dbAccount.setLastName(rs.getString("lastName"));
        }
    } finally {
        closeQuietly(rs, stmt, conn);
    }

    // Delete the inserted account from database
    final String delete_getById = "DELETE FROM account WHERE id = ?";
    conn = null;
    stmt = null;
    rs = null;

    try {
        conn = DriverManager.getConnection(url, user, password);
        stmt = conn.prepareStatement(delete_getById);
        stmt.setLong(1, id);

        stmt.executeUpdate();
    } finally {
        closeQuietly(null, stmt, conn);
    }
}

private void closeQuietly(ResultSet rs, PreparedStatement stmt, Connection conn) {
    if (rs != null) {
        try {
            rs.close();
        } catch (Throwable e) {
            // ignore.
        }
    }

    if (stmt != null) {
        try {
            stmt.close();
        } catch (Throwable e) {
            // ignore.
        }
    }

    if (conn != null) {
        try {
            conn.close();
        } catch (Throwable e) {
            // ignore.
        }
    }
}