测试数据库交互
为数据访问层编写集成测试，确保与关系型数据库的交互正确。
测试数据库交互 是 CoddyKit 上的免费 Testing Mastery: JUnit, Mockito & Integration Tests 课时。 这是第 3 节课，共 4 节。 你可以在下方免费阅读本课时的完整内容 — 然后在浏览器中使用内置代码编辑器和全天候 AI 导师进行实践。 这是 Testing Mastery: JUnit, Mockito & Integration Tests 学习路径的一部分，你的进度在网页和 CoddyKit 应用中同步。 Testing Mastery: JUnit, Mockito & Integration Tests 课程共包含 4 节课。
本课时的部分内容尚未翻译，以英文显示。
Why Test Database Interactions?
Database interactions are a critical part of most applications. Testing these interactions ensures your app reliably stores, retrieves, and updates data.
- Data Integrity: Guarantees data is stored correctly.
- Business Logic: Verifies data-related business rules.
- Error Prevention: Catches issues before they reach production.
The Challenges of Real Databases
Testing directly with a real production database can be tricky and lead to unreliable tests:
- Slow: Real DBs add significant time to test suites.
- Stateful: Tests can leave leftover data, affecting subsequent tests.
- Complex Setup: Requires a running external service and specific configurations.
- Isolation: Difficult to ensure each test runs in an isolated environment.
In-Memory Databases to the Rescue
To overcome these challenges, we often use in-memory databases for integration tests. These are lightweight databases that run entirely within your application's memory.
- Fast: No disk I/O, quick startup/shutdown.
- Isolated: Each test run can start with a fresh, empty database.
- Easy Setup: Often just a dependency and a connection URL.
Popular choices include H2, HSQLDB, and Apache Derby.
Setting Up Your H2 Database
Let's see how easy it is to connect to an H2 in-memory database using standard JDBC. It works just like a regular database, but lives in your application's memory.
You'll need the H2 dependency (e.g., Maven: com.h2database:h2).
import java.sql.*;
public class H2Demo {
public static void main(String[] args) throws SQLException {
String jdbcUrl = "jdbc:h2:mem:testdb";
String username = "sa";
String password = "";
try (Connection conn = DriverManager.getConnection(jdbcUrl, username, password)) {
Statement stmt = conn.createStatement();
stmt.execute("CREATE TABLE products (id INT PRIMARY KEY, name VARCHAR(255))");
stmt.executeUpdate("INSERT INTO products (id, name) VALUES (1, 'Laptop')");
ResultSet rs = stmt.executeQuery("SELECT * FROM products");
if (rs.next()) {
System.out.println("Product: " + rs.getString("name"));
}
}
}
}Introducing the Data Access Layer
Your application typically uses a Data Access Object (DAO) or Repository pattern to interact with the database. This layer abstracts away the low-level JDBC or ORM details.
We'll use a simple ProductRepository to manage Product objects.
public class Product {
private int id;
private String name;
public Product(int id, String name) {
this.id = id;
this.name = name;
}
public int getId() { return id; }
public String getName() { return name; }
}
import java.util.List;
public interface ProductRepository {
void save(Product product);
Product findById(int id);
List<Product> findAll();
}Implementing a Simple Repository
Here's a basic implementation of our ProductRepository using raw JDBC. In a real application, you might use Spring's JdbcTemplate or an ORM like Hibernate.
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
// Product class and ProductRepository interface as defined previously
public class JdbcProductRepository implements ProductRepository {
private Connection conn;
public JdbcProductRepository(Connection conn) {
this.conn = conn;
try (Statement stmt = conn.createStatement()) {
stmt.execute("CREATE TABLE IF NOT EXISTS products (id INT PRIMARY KEY, name VARCHAR(255))");
} catch (SQLException e) { throw new RuntimeException(e); }
}
@Override
public void save(Product product) {
String sql = "INSERT INTO products (id, name) VALUES (?, ?)";
try (PreparedStatement ps = conn.prepareStatement(sql)) {
ps.setInt(1, product.getId());
ps.setString(2, product.getName());
ps.executeUpdate();
} catch (SQLException e) { throw new RuntimeException(e); }
}
@Override
public Product findById(int id) {
String sql = "SELECT id, name FROM products WHERE id = ?";
try (PreparedStatement ps = conn.prepareStatement(sql)) {
ps.setInt(1, id); ResultSet rs = ps.executeQuery();
if (rs.next()) { return new Product(rs.getInt("id"), rs.getString("name")); }
} catch (SQLException e) { throw new RuntimeException(e); }
return null;
}
@Override
public List<Product> findAll() {
List<Product> products = new ArrayList<>();
String sql = "SELECT id, name FROM products";
try (Statement stmt = conn.createStatement()) {
ResultSet rs = stmt.executeQuery(sql);
while (rs.next()) {
products.add(new Product(rs.getInt("id"), rs.getString("name")));
}
} catch (SQLException e) { throw new RuntimeException(e); }
return products;
}
}Testing the Repository - Setup
Now, let's write a simple program to test our JdbcProductRepository. We'll set up an H2 in-memory database specifically for this test run.
Notice how we create a fresh database connection for our repository, ensuring isolation.
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
// Product class (simplified for mobile)
class Product {
private int id; private String name;
public Product(int id, String name) { this.id = id; this.name = name; }
public int getId() { return id; }
public String getName() { return name; }
}
// ProductRepository interface
interface ProductRepository {
void save(Product product);
Product findById(int id);
List<Product> findAll();
}
// JdbcProductRepository (simplified for mobile)
class JdbcProductRepository implements ProductRepository {
private Connection conn;
public JdbcProductRepository(Connection conn) {
this.conn = conn;
try (Statement stmt = conn.createStatement()) {
stmt.execute("CREATE TABLE IF NOT EXISTS products (id INT PRIMARY KEY, name VARCHAR(255))");
} catch (SQLException e) { throw new RuntimeException(e); }
}
@Override public void save(Product product) {
String sql = "INSERT INTO products (id, name) VALUES (?, ?)";
try (PreparedStatement ps = conn.prepareStatement(sql)) {
ps.setInt(1, product.getId()); ps.setString(2, product.getName());
ps.executeUpdate();
} catch (SQLException e) { throw new RuntimeException(e); }
}
@Override public Product findById(int id) { /* ... omitted ... */ return null; }
@Override public List<Product> findAll() { /* ... omitted ... */ return new ArrayList<>(); }
}
public class ProductRepositoryTestRunner {
public static void main(String[] args) throws SQLException {
String jdbcUrl = "jdbc:h2:mem:testdb;DB_CLOSE_DELAY=-1";
try (Connection conn = DriverManager.getConnection(jdbcUrl, "sa", "")) {
System.out.println("DB connection established.");
ProductRepository repository = new JdbcProductRepository(conn);
Product laptop = new Product(1, "Laptop");
repository.save(laptop);
System.out.println("Saved product: " + laptop.getName());
Product foundProduct = repository.findById(1);
System.out.println("Found product: " + (foundProduct != null ? foundProduct.getName() : "None"));
List<Product> allProducts = repository.findAll();
System.out.println("Total products: " + allProducts.size());
}
}
}Asserting Database State
In a real test, simply printing isn't enough. You need to assert that the database state matches your expectations after an operation. This confirms your DAO methods work correctly.
Let's add some basic checks to verify the outcomes.
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
// Product class (simplified for mobile)
class Product {
private int id; private String name;
public Product(int id, String name) { this.id = id; this.name = name; }
public int getId() { return id; }
public String getName() { return name; }
}
// ProductRepository interface
interface ProductRepository {
void save(Product product); Product findById(int id); List<Product> findAll();
}
// JdbcProductRepository (simplified for mobile)
class JdbcProductRepository implements ProductRepository {
private Connection conn;
public JdbcProductRepository(Connection conn) { /* ... omitted ... */ this.conn = conn; }
@Override public void save(Product product) { /* ... omitted ... */ }
@Override public Product findById(int id) {
String sql = "SELECT id, name FROM products WHERE id = ?";
try (PreparedStatement ps = conn.prepareStatement(sql)) {
ps.setInt(1, id); ResultSet rs = ps.executeQuery();
if (rs.next()) { return new Product(rs.getInt("id"), rs.getString("name")); }
} catch (SQLException e) { throw new RuntimeException(e); }
return null;
}
@Override public List<Product> findAll() {
List<Product> products = new ArrayList<>();
String sql = "SELECT id, name FROM products";
try (Statement stmt = conn.createStatement()) {
ResultSet rs = stmt.executeQuery(sql);
while (rs.next()) { products.add(new Product(rs.getInt("id"), rs.getString("name"))); }
} catch (SQLException e) { throw new RuntimeException(e); }
return products;
}
}
public class ProductRepositoryAssertions {
public static void main(String[] args) throws SQLException {
String jdbcUrl = "jdbc:h2:mem:testdb;DB_CLOSE_DELAY=-1";
try (Connection conn = DriverManager.getConnection(jdbcUrl, "sa", "")) {
ProductRepository repository = new JdbcProductRepository(conn);
Product laptop = new Product(1, "Laptop");
repository.save(laptop);
// Assertion 1: Check if product was saved
Product found = repository.findById(1);
if (found != null && found.getName().equals("Laptop")) {
System.out.println("Test Passed: Product saved & found.");
} else { System.out.println("Test Failed: Product not found or name mismatch."); }
// Assertion 2: Check total count
List<Product> all = repository.findAll();
if (all.size() == 1) {
System.out.println("Test Passed: Correct product count.");
} else { System.out.println("Test Failed: Incorrect product count."); }
}
}
}Ensuring Clean Tests with Transactions
For more robust test isolation, especially when using frameworks like Spring, you can leverage transactions.
By annotating your test methods with @Transactional, any changes made to the database within that test will automatically be rolled back after the test completes. This leaves the database in a clean state for the next test.
- Automatic Cleanup: No need for manual
DELETEstatements. - Isolation: Each test runs as if it's the only one.
- Faster: Rolling back is often quicker than deleting and re-inserting.
Database Testing Best Practices
To make your database integration tests effective and maintainable, consider these tips:
- Use In-Memory DBs: For speed and isolation.
- Minimal Setup: Only create tables/data necessary for the specific test.
- Transactional Tests: Automatically roll back changes.
- Clear Assertions: Verify expected data and state.
- Focus on DAO: Test the data access layer, not business logic here.
Quick Check: DB Testing
Which of the following is a primary benefit of using an in-memory database like H2 for integration tests?
Recap: Testing Database Interactions
In this lesson, we learned how to effectively test database interactions.
- We understood the challenges of testing with real databases.
- We discovered in-memory databases like H2 as a fast and isolated solution.
- We saw how to set up and interact with an H2 database programmatically.
- We practiced writing tests for a Data Access Object (DAO), ensuring data integrity and correct behavior.
- We touched upon transactional tests for automatic cleanup.
Keep practicing to ensure your application's data layer is rock solid!
常见问题解答
「测试数据库交互」课时是免费的吗？
是的 — 「测试数据库交互」的完整文本可在网页上免费阅读。要进行交互式练习（内置代码编辑器和全天候 AI 导师）并解锁 Testing Mastery: JUnit, Mockito & Integration Tests 课程的其余内容，请升级到 CoddyKit PRO。 Testing Mastery: JUnit, Mockito & Integration Tests 课程共包含 4 节课。
「测试数据库交互」这节课中我会学到什么？
为数据访问层编写集成测试，确保与关系型数据库的交互正确。 你通过在浏览器中直接运行的动手代码来练习 Testing Mastery: JUnit, Mockito & Integration Tests，全天候 AI 导师会在你学习这节课的过程中回答你的问题。
学习 Testing Mastery: JUnit, Mockito & Integration Tests 需要有经验吗？
无需任何先前经验。CoddyKit 上的 Testing Mastery: JUnit, Mockito & Integration Tests 课程适合初学者到高级学习者，你可以从这里开始或从头开始，按照自己的节奏学习。 这是第 3 节课，共 4 节。
「测试数据库交互」课时需要多长时间？
大多数 CoddyKit 课程大约需要 5–10 分钟。每节课都很精短且互动，所以你能稳步进步，并在网页和应用中从离开的地方继续。
我能在这节 Testing Mastery: JUnit, Mockito & Integration Tests 课中编写并运行代码吗？
能。每节 Testing Mastery: JUnit, Mockito & Integration Tests 课都包含内置代码编辑器，你可以在浏览器中直接编写并运行真实代码，并获得即时 AI 反馈 — 无需本地设置。