本文目录导读：
我来为您提供几个Java嵌入式数据库的完整案例,包括H2和SQLite两种主流选择。
H2数据库案例（最流行的Java嵌入式数据库）
Maven依赖
<dependency>
<groupId>com.h2database</groupId>
<artifactId>h2</artifactId>
<version>2.2.224</version>
</dependency>
完整示例代码
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
public class H2EmbeddedDemo {
// 数据库连接（文件模式）
private static final String DB_URL = "jdbc:h2:./data/testdb";
private static final String DB_USER = "sa";
private static final String DB_PASSWORD = "";
public static void main(String[] args) {
try {
// 1. 创建表
createTable();
// 2. 插入数据
insertUser(new User("张三", "zhangsan@email.com", 25));
insertUser(new User("李四", "lisi@email.com", 30));
insertUser(new User("王五", "wangwu@email.com", 28));
// 3. 查询数据
System.out.println("=== 所有用户 ===");
List<User> users = getAllUsers();
users.forEach(System.out::println);
// 4. 更新数据
updateUserEmail(1, "zhangsan_updated@email.com");
// 5. 条件查询
System.out.println("\n=== 年龄大于25的用户 ===");
List<User> filteredUsers = getUsersByAge(25);
filteredUsers.forEach(System.out::println);
// 6. 删除数据
deleteUser(3);
System.out.println("\n=== 删除后的所有用户 ===");
users = getAllUsers();
users.forEach(System.out::println);
} catch (SQLException e) {
e.printStackTrace();
}
}
// 创建表
private static void createTable() throws SQLException {
String sql = """
CREATE TABLE IF NOT EXISTS users (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100) NOT NULL,
email VARCHAR(100) NOT NULL UNIQUE,
age INT,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
)
""";
try (Connection conn = getConnection();
Statement stmt = conn.createStatement()) {
stmt.execute(sql);
System.out.println("表创建成功");
}
}
// 插入用户
private static void insertUser(User user) throws SQLException {
String sql = "INSERT INTO users (name, email, age) VALUES (?, ?, ?)";
try (Connection conn = getConnection();
PreparedStatement pstmt = conn.prepareStatement(sql)) {
pstmt.setString(1, user.getName());
pstmt.setString(2, user.getEmail());
pstmt.setInt(3, user.getAge());
pstmt.executeUpdate();
System.out.println("用户添加成功: " + user);
}
}
// 获取所有用户
private static List<User> getAllUsers() throws SQLException {
List<User> users = new ArrayList<>();
String sql = "SELECT * FROM users ORDER BY id";
try (Connection conn = getConnection();
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(sql)) {
while (rs.next()) {
users.add(extractUserFromResultSet(rs));
}
}
return users;
}
// 按年龄查询
private static List<User> getUsersByAge(int minAge) throws SQLException {
List<User> users = new ArrayList<>();
String sql = "SELECT * FROM users WHERE age > ? ORDER BY age";
try (Connection conn = getConnection();
PreparedStatement pstmt = conn.prepareStatement(sql)) {
pstmt.setInt(1, minAge);
ResultSet rs = pstmt.executeQuery();
while (rs.next()) {
users.add(extractUserFromResultSet(rs));
}
}
return users;
}
// 更新用户邮箱
private static void updateUserEmail(int id, String newEmail) throws SQLException {
String sql = "UPDATE users SET email = ? WHERE id = ?";
try (Connection conn = getConnection();
PreparedStatement pstmt = conn.prepareStatement(sql)) {
pstmt.setString(1, newEmail);
pstmt.setInt(2, id);
int rows = pstmt.executeUpdate();
System.out.println("更新了 " + rows + " 条记录");
}
}
// 删除用户
private static void deleteUser(int id) throws SQLException {
String sql = "DELETE FROM users WHERE id = ?";
try (Connection conn = getConnection();
PreparedStatement pstmt = conn.prepareStatement(sql)) {
pstmt.setInt(1, id);
int rows = pstmt.executeUpdate();
System.out.println("删除了 " + rows + " 条记录");
}
}
// 获取连接
private static Connection getConnection() throws SQLException {
return DriverManager.getConnection(DB_URL, DB_USER, DB_PASSWORD);
}
// 从ResultSet提取用户
private static User extractUserFromResultSet(ResultSet rs) throws SQLException {
User user = new User();
user.setId(rs.getInt("id"));
user.setName(rs.getString("name"));
user.setEmail(rs.getString("email"));
user.setAge(rs.getInt("age"));
user.setCreatedAt(rs.getTimestamp("created_at"));
return user;
}
}
// 用户实体类
class User {
private int id;
private String name;
private String email;
private int age;
private Timestamp createdAt;
public User() {}
public User(String name, String email, int age) {
this.name = name;
this.email = email;
this.age = age;
}
// getter和setter方法
public int getId() { return id; }
public void setId(int id) { this.id = id; }
public String getName() { return name; }
public void setName(String name) { this.name = name; }
public String getEmail() { return email; }
public void setEmail(String email) { this.email = email; }
public int getAge() { return age; }
public void setAge(int age) { this.age = age; }
public Timestamp getCreatedAt() { return createdAt; }
public void setCreatedAt(Timestamp createdAt) { this.createdAt = createdAt; }
@Override
public String toString() {
return String.format("User{id=%d, name='%s', email='%s', age=%d, createdAt=%s}",
id, name, email, age, createdAt);
}
}
使用Spring Boot + H2数据库
application.properties配置
# H2嵌入式数据库配置 spring.datasource.url=jdbc:h2:file:./data/mydb spring.datasource.driver-class-name=org.h2.Driver spring.datasource.username=sa spring.datasource.password= # JPA配置 spring.jpa.database-platform=org.hibernate.dialect.H2Dialect spring.jpa.hibernate.ddl-auto=update spring.jpa.show-sql=true # H2控制台（仅开发环境） spring.h2.console.enabled=true spring.h2.console.path=/h2-console
Spring Boot启动类
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
@SpringBootApplication
public class H2DemoApplication {
public static void main(String[] args) {
SpringApplication.run(H2DemoApplication.class, args);
}
}
实体类
import javax.persistence.*;
import java.time.LocalDateTime;
@Entity
@Table(name = "employees")
public class Employee {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;
@Column(nullable = false)
private String name;
@Column(unique = true)
private String email;
private String department;
private Double salary;
@Column(name = "created_at")
private LocalDateTime createdAt = LocalDateTime.now();
// getter和setter方法
// ...
}
Repository接口
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.stereotype.Repository;
import java.util.List;
@Repository
public interface EmployeeRepository extends JpaRepository<Employee, Long> {
List<Employee> findByDepartment(String department);
List<Employee> findByNameContaining(String name);
}
测试服务类
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import java.util.List;
@Service
public class EmployeeService {
@Autowired
private EmployeeRepository employeeRepository;
@Transactional
public Employee saveEmployee(Employee employee) {
return employeeRepository.save(employee);
}
public List<Employee> getAllEmployees() {
return employeeRepository.findAll();
}
public List<Employee> getByDepartment(String department) {
return employeeRepository.findByDepartment(department);
}
}
SQLite嵌入式数据库案例
Maven依赖
<dependency>
<groupId>org.xerial</groupId>
<artifactId>sqlite-jdbc</artifactId>
<version>3.43.2.2</version>
</dependency>
SQLite数据库工具类
import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
public class SQLiteHelper {
private static final String DB_PATH = "data/sqlite_demo.db";
private static SQLiteHelper instance;
private Connection connection;
private SQLiteHelper() {
try {
// 加载SQLite JDBC驱动
Class.forName("org.sqlite.JDBC");
// 创建数据库连接
String url = "jdbc:sqlite:" + DB_PATH;
connection = DriverManager.getConnection(url);
// 启用外键约束
try (Statement stmt = connection.createStatement()) {
stmt.execute("PRAGMA foreign_keys = ON");
}
} catch (ClassNotFoundException | SQLException e) {
e.printStackTrace();
}
}
public static synchronized SQLiteHelper getInstance() {
if (instance == null) {
instance = new SQLiteHelper();
}
return instance;
}
// 执行无返回结果的SQL
public boolean execute(String sql) {
try (Statement stmt = connection.createStatement()) {
stmt.execute(sql);
return true;
} catch (SQLException e) {
e.printStackTrace();
return false;
}
}
// 执行带参数的更新操作
public int executeUpdate(String sql, Object... params) {
try (PreparedStatement pstmt = connection.prepareStatement(sql)) {
for (int i = 0; i < params.length; i++) {
pstmt.setObject(i + 1, params[i]);
}
return pstmt.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
return -1;
}
}
// 查询并返回结果列表
public List<Map<String, Object>> query(String sql, Object... params) {
List<Map<String, Object>> results = new ArrayList<>();
try (PreparedStatement pstmt = connection.prepareStatement(sql)) {
for (int i = 0; i < params.length; i++) {
pstmt.setObject(i + 1, params[i]);
}
ResultSet rs = pstmt.executeQuery();
ResultSetMetaData metaData = rs.getMetaData();
int columnCount = metaData.getColumnCount();
while (rs.next()) {
Map<String, Object> row = new HashMap<>();
for (int i = 1; i <= columnCount; i++) {
String columnName = metaData.getColumnName(i);
Object value = rs.getObject(i);
row.put(columnName, value);
}
results.add(row);
}
} catch (SQLException e) {
e.printStackTrace();
}
return results;
}
// 关闭连接
public void close() {
try {
if (connection != null && !connection.isClosed()) {
connection.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
SQLite使用示例
public class SQLiteDemo {
public static void main(String[] args) {
SQLiteHelper db = SQLiteHelper.getInstance();
try {
// 1. 创建表
String createTable = """
CREATE TABLE IF NOT EXISTS products (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT NOT NULL,
category TEXT,
price REAL NOT NULL,
stock INTEGER DEFAULT 0,
created_at DATETIME DEFAULT CURRENT_TIMESTAMP
)
""";
db.execute(createTable);
System.out.println("表创建成功");
// 2. 插入数据
String insert = "INSERT INTO products (name, category, price, stock) VALUES (?, ?, ?, ?)";
db.executeUpdate(insert, "笔记本电脑", "电子产品", 5999.99, 10);
db.executeUpdate(insert, "机械键盘", "外设", 299.00, 50);
db.executeUpdate(insert, "显示器", "电子产品", 1299.00, 20);
// 3. 查询所有产品
String queryAll = "SELECT * FROM products";
List<Map<String, Object>> products = db.query(queryAll);
System.out.println("\n所有产品:");
products.forEach(System.out::println);
// 4. 带条件查询
String queryByCategory = "SELECT * FROM products WHERE category = ? AND price > ?";
List<Map<String, Object>> filtered = db.query(queryByCategory, "电子产品", 1000);
System.out.println("\n电子产品中价格大于1000的:");
filtered.forEach(System.out::println);
// 5. 更新数据
String update = "UPDATE products SET stock = stock + ? WHERE id = ?";
int rows = db.executeUpdate(update, 5, 1);
System.out.println("\n更新了 " + rows + " 条记录");
// 6. 删除数据
String delete = "DELETE FROM products WHERE stock = 0";
int deletedRows = db.executeUpdate(delete);
System.out.println("删除了 " + deletedRows + " 条记录");
} finally {
db.close();
}
}
}
使用Derby数据库（Apache Derby）
Maven依赖
<dependency>
<groupId>org.apache.derby</groupId>
<artifactId>derby</artifactId>
<version>10.17.1.0</version>
</dependency>
Derby使用示例
import java.sql.*;
public class DerbyDemo {
public static void main(String[] args) {
// 嵌入式模式 - 在本地创建数据库
String url = "jdbc:derby:data/derby_demo;create=true";
try (Connection conn = DriverManager.getConnection(url)) {
// 创建表
Statement stmt = conn.createStatement();
stmt.execute("""
CREATE TABLE IF NOT EXISTS tasks (
id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
title VARCHAR(200) NOT NULL,
description CLOB,
priority INT DEFAULT 0,
completed BOOLEAN DEFAULT FALSE,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
)
""");
// 插入数据
PreparedStatement pstmt = conn.prepareStatement(
"INSERT INTO tasks (title, description, priority) VALUES (?, ?, ?)"
);
pstmt.setString(1, "完成Java项目");
pstmt.setString(2, "需要完成用户模块和订单模块");
pstmt.setInt(3, 1);
pstmt.executeUpdate();
// 查询数据
ResultSet rs = stmt.executeQuery("SELECT * FROM tasks");
while (rs.next()) {
System.out.println("任务ID: " + rs.getInt("id"));
System.out.println("标题: " + rs.getString("title"));
System.out.println("优先级: " + rs.getInt("priority"));
System.out.println("完成状态: " + rs.getBoolean("completed"));
System.out.println("---");
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
总结与选择建议
不同数据库的特点：
| 特性 | H2 | SQLite | Derby |
|---|---|---|---|
| 性能 | 优秀 | 优秀 | 良好 |
| 功能完整性 | 非常完善 | 基本功能 | 完善 |
| 内存模式 | 支持 | 不支持 | 支持 |
| 并发支持 | 良好 | 一般 | 良好 |
| SQL标准兼容 | 高 | 较低 | 高 |
| Spring Boot支持 | 原生支持 | 需配置 | 支持 |
选择建议：
-
使用H2当：
- 开发测试环境
- 需要完整SQL功能
- Spring Boot项目
-
使用SQLite当：
- 需要轻量级方案
- 嵌入式设备或移动端
- 数据量不大
-
使用Derby当：
- 企业级应用
- 需要标准SQL支持
- 与Java EE集成
这些示例代码可以直接运行,帮您快速上手Java嵌入式数据库开发。
