JDBC.md

JDBC

H2 Database

<dependency>
  <groupId>com.h2database</groupId>
  <artifactId>h2</artifactId>
  <version>1.4.200</version>
</dependency>

All H2 commands

DataSource, Connection and Transaction

DataSource

DataSource dataSource = new JdbcDataSource();
dataSource.setURL("jdbc:h2:" + path);

DataSource dataSource = new JdbcDataSource();
dataSource.setURL("jdbc:h2:mem:test");

Connection

try(Connection connection = dataSource.getConnection()) {
  ...  
}

Transaction

Connection.setAutoCommit commit rollback

try(Connection connection = dataSource.getConnection()) {
  connection.setAutoCommit(false);
  try {
    ...
    connection.commit();
  } catch(...) {
    connection.rollback();
  }
}

Transaction semantics

TRANSACTION_READ_COMMITTED see modification from other transactions

TRANSACTION_REPEATABLE_READ don't see modification from other transactions

Statement and PreparedStatement

Connection.createStatement() Connection.prepareStatement()

Connection connection = ...
String sqlQuery = """
  INSERT INTO FOO (id, name) VALUES (42, 'James Bond');
""";
try(Statement statement = connection.createStatement()) {
  statement.executeUpdate(query);
}
connection.commit();

Connection connection = ...
String sqlQuery = """
  INSERT INTO FOO (id, name) VALUES (?, ?);
""";
try(PreparedStement statement = connection.prepareStatement(query)) {
  statement.setObject(1, 42);
  statement.setObject(2, "James Bond");
  statement.executeUpdate();
}
connection.commit();

Create a Table

CREATE TABLE

Statement.executeUpdate()

Connection connection = ...
String query = """
  CREATE TABLE FOO (
    id BIGINT,
    name VARCHAR(255),
    PRIMARY KEY (id)
  );
  """;
try(Statement statement = connection.createStatement()) {
  statement.executeUpdate(query);
}
connection.commit();

Insert data

INSERT INTO

PreparedStatement.setObject()

Connection connection = ...
String sqlQuery = """
  INSERT INTO FOO (id, name) VALUES (?, ?);
""";
try(PreparedStatement statement = connection.prepareStatement(sqlQuery)) {
  statement.setObject(1, 42);
  statement.setObject(2, "James Bond");
  statement.executeUpdate();
}
connection.commit();

Merge data

MERGE INTO

Connection connection = ...
String sqlQuery = """
  MERGE INTO FOO (id, name) VALUES (?, ?);
""";
try(PreparedStatement statement = connection.prepareStatement(sqlQuery)) {
  statement.setObject(1, 42);
  statement.setObject(2, "James Bond");
  statement.executeUpdate();
}
connection.commit();

Generated Primary Key

H2 AUTO_INCREMENT

String query = """
  CREATE TABLE FOO (
    id BIGINT AUTO_INCREMENT,
    name VARCHAR(255),
    PRIMARY KEY (id)
  );
  """;

Connection.prepareStatement(query, Statement.RETURN_GENERATED_KEYS)

Statement.getGeneratedKeys()

ResultSet

ResultSet.next()

ResultSet.getObject()

Connection connection = ...
String sqlQuery = """
  INSERT INTO FOO (name) VALUES (?);
""";
try(PreparedStatement statement = connection.prepareStatement(sqlQuery, Statement.RETURN_GENERATED_KEYS)) {
  statement.setObject(1, 42);
  statement.setObject(2, "James Bond");
  statement.executeUpdate(query);
  try(ResultSet resultSet = statement.getGeneratedKeys()) {
    if (resultSet.next()) {
      Long key = (Long) resultSet.getObject(1);
      ...
    }
  }
}
connection.commit();

Query

Connection.prepareStatement()

PreparedStatement.executeQuery

ResultSet.next()

ResultSet.getObject()

Connection connection = ...
String sqlQuery = """
  SELECT (id, name) FROM FOO WHERE name = ?;
""";
try(PreparedStatement statement = connection.prepareStatement(sqlQuery)) {
  statement.setObject(1, "James Bond");
  try(ResultSet resultSet = statement.executeQuery()) {
    while(resultSet.next()) {
      Long id = (Long) resultSet.getObject(1);
      String name = (String) resultSet.getObject(2);
      ...          
    }
  }
}
connection.commit();