In-memory database testing in JUnit - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to create an in-memory H2 database connection.

JUnit

Connection conn = DriverManager.getConnection("jdbc:h2:[1]", "sa", "");

Drag options to blanks, or click blank then click option'

Azip:testdb

Bfile:testdb

Ctcp://localhost/testdb

Dmem:testdb

Attempts:

3 left

💡 Hint

Common Mistakes

Using 'file:' instead of 'mem:' causes the database to be file-based, not in-memory.

Forgetting the 'mem:' prefix leads to connection errors.

The correct JDBC URL for an in-memory H2 database starts with 'jdbc:h2:mem:'.

2fill in blank

medium

Complete the code to create a JUnit 5 test method that initializes the in-memory database.

JUnit

@Test
void testDatabaseConnection() throws SQLException {
    Connection conn = DriverManager.getConnection("jdbc:h2:mem:testdb", "sa", "");
    Statement stmt = conn.createStatement();
    stmt.execute([1]);
    conn.close();
}

Drag options to blanks, or click blank then click option'

A"SELECT * FROM users"

B"DROP TABLE users"

C"CREATE TABLE users(id INT PRIMARY KEY, name VARCHAR(255))"

D"INSERT INTO users VALUES(1, 'Alice')"

Attempts:

3 left

💡 Hint

Common Mistakes

Using SELECT or DROP statements before creating the table causes errors.

Trying to insert data before creating the table leads to failures.

To initialize the database, you create the table first using a CREATE TABLE statement.

3fill in blank

hard

Fix the error in the assertion that checks if the user count is 1.

JUnit

ResultSet rs = stmt.executeQuery("SELECT COUNT(*) FROM users");
rs.next();
assertEquals([1], rs.getInt(1));

Drag options to blanks, or click blank then click option'

A0

B1

C"1"

Drs.getString(1)

Attempts:

3 left

💡 Hint

Common Mistakes

Using a string '1' instead of integer 1 causes assertion failure.

Using 0 as expected count is incorrect if one row was inserted.

The count should be compared as an integer 1, not as a string or zero.

4fill in blank

hard

Fill both blanks to insert a user and verify the insertion.

JUnit

stmt.executeUpdate("INSERT INTO users (id, name) VALUES ([1], '[2]')");
ResultSet rs = stmt.executeQuery("SELECT name FROM users WHERE id = 1");
rs.next();
assertEquals("Alice", rs.getString(1));

Drag options to blanks, or click blank then click option'

A1

BAlice

C2

DBob

Attempts:

3 left

💡 Hint

Common Mistakes

Using id 2 or name 'Bob' causes the assertion to fail.

Mismatching inserted values and assertion expected values.

The inserted user has id 1 and name 'Alice' to match the assertion.

5fill in blank

hard

Fill all three blanks to create a test that inserts multiple users and checks their count.

JUnit

stmt.executeUpdate("INSERT INTO users (id, name) VALUES ([1], '[2]')");
stmt.executeUpdate("INSERT INTO users (id, name) VALUES ([3], 'Bob')");
ResultSet rs = stmt.executeQuery("SELECT COUNT(*) FROM users");
rs.next();
assertEquals([4], rs.getInt(1));

Drag options to blanks, or click blank then click option'

A1

BAlice

C2

Attempts:

3 left

💡 Hint

Common Mistakes

Using the same id for both inserts causes primary key errors.

Expecting count 1 after inserting two users causes assertion failure.

Insert user with id 1 named Alice, user with id 2 named Bob, and expect count 2.

Practice

1. What is the main advantage of using an in-memory database for JUnit testing?

easy

A. It stores data permanently for production use

B. It requires complex setup and external servers

C. Tests run faster without needing a real database connection

D. It only works with web applications

Solution

1. Step 1: Understand in-memory database purpose

   In-memory databases keep data in RAM, so no disk access is needed, making tests faster.

2. Step 2: Compare options

   The incorrect options describe permanent storage, complex setup, and limitation to web apps, which are not advantages of in-memory testing databases.

3. Final Answer:

   Tests run faster without needing a real database connection -> Option C

4. Quick Check:

   In-memory = fast tests [OK]

Hint: In-memory means fast, no real DB needed [OK]

Common Mistakes:

• Thinking in-memory DB stores data permanently
• Assuming it needs external servers
• Confusing test DB with production DB

2. Which annotation is commonly used in JUnit to configure a test with an in-memory database and JdbcTemplate?

easy

A. @DataJpaTest

B. @JdbcTest

C. @Entity

D. @SpringBootApplication

Solution

1. Step 1: Identify annotation for JDBC testing

   @JdbcTest sets up an in-memory database and JdbcTemplate for simple JDBC tests.

2. Step 2: Review other annotations

   @SpringBootApplication is for main app config, @Entity marks entities, @DataJpaTest is for JPA tests, not simple JDBC.

3. Final Answer:

   @JdbcTest -> Option B

4. Quick Check:

   @JdbcTest = in-memory JDBC test setup [OK]

Hint: Use @JdbcTest for fast in-memory JDBC tests [OK]

Common Mistakes:

• Confusing @JdbcTest with @DataJpaTest
• Using @Entity as a test annotation
• Using @SpringBootApplication in tests

3. Given this JUnit test snippet using JdbcTemplate with an in-memory database:

jdbcTemplate.execute("CREATE TABLE users(id INT PRIMARY KEY, name VARCHAR(50))");
jdbcTemplate.update("INSERT INTO users VALUES (1, 'Alice')");
int count = jdbcTemplate.queryForObject("SELECT COUNT(*) FROM users", Integer.class);

What is the value of count after execution?

medium

A. 1

B. 0

C. Syntax error

D. NullPointerException

Solution

1. Step 1: Analyze table creation and insertion

   The table 'users' is created and one row with id=1 and name='Alice' is inserted.

2. Step 2: Query the count of rows

   The query "SELECT COUNT(*) FROM users" returns the number of rows, which is 1 after insertion.

3. Final Answer:

   1 -> Option A

4. Quick Check:

   Inserted 1 row, count = 1 [OK]

Hint: Count rows after insert to get 1 [OK]

Common Mistakes:

• Assuming count is 0 before insertion
• Expecting syntax error from correct SQL
• Confusing queryForObject return type

4. In this JUnit test using JdbcTemplate, what is the error?

@JdbcTest
public class UserTest {
  @Autowired
  private JdbcTemplate jdbcTemplate;

  @Test
  public void testInsert() {
    jdbcTemplate.execute("CREATE TABLE users(id INT PRIMARY KEY, name VARCHAR(50))");
    jdbcTemplate.update("INSERT INTO users VALUES (1, 'Bob')");
    int count = jdbcTemplate.queryForObject("SELECT COUNT(*) FROM user", Integer.class);
    assertEquals(1, count);
  }
}

medium

A. Table name typo in SELECT query

B. Missing @Test annotation

C. JdbcTemplate not autowired

D. Incorrect assertion syntax

Solution

1. Step 1: Check table creation and query

   The table created is named 'users' but the SELECT query uses 'user', causing an error.

2. Step 2: Verify annotations and syntax

   @Test is present, JdbcTemplate is autowired, and assertion syntax is correct.

3. Final Answer:

   Table name typo in SELECT query -> Option A

4. Quick Check:

   Table 'users' vs query 'user' mismatch [OK]

Hint: Match table names exactly in queries [OK]

Common Mistakes:

• Ignoring table name mismatch
• Thinking @Test is missing
• Assuming autowiring fails silently

5. You want to write a JUnit test using an in-memory database that creates a products table, inserts multiple rows, and verifies the total price sum. Which approach is best to keep tests independent and reliable?

hard

A. Create table once in a static block and reuse data across tests

B. Skip table creation and assume it exists

C. Use a real database server for accurate results

D. Create table and insert data inside each test method

Solution

1. Step 1: Understand test independence

   Creating tables and inserting data inside each test ensures no leftover data affects other tests.

2. Step 2: Evaluate other options

   Static blocks or real DBs reduce isolation and speed; skipping creation risks failures if table missing.

3. Final Answer:

   Create table and insert data inside each test method -> Option D

4. Quick Check:

   Setup per test = independent, reliable [OK]

Hint: Setup fresh data inside each test method [OK]

Common Mistakes:

• Sharing data across tests causing flaky results
• Using real DB slowing tests
• Assuming tables exist without creation