Java嵌入式数据库案例

本文目录导读：

1. H2数据库案例（最流行的Java嵌入式数据库）
2. 使用Spring Boot + H2数据库
3. SQLite嵌入式数据库案例
4. 使用Derby数据库（Apache Derby）
5. 总结与选择建议

我来为您提供几个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();
        }
    }
}

总结与选择建议

不同数据库的特点：

选择建议：

1. 使用H2当：

   • 开发测试环境
   • 需要完整SQL功能
   • Spring Boot项目

2. 使用SQLite当：

   • 需要轻量级方案
   • 嵌入式设备或移动端
   • 数据量不大

3. 使用Derby当：

   • 企业级应用
   • 需要标准SQL支持
   • 与Java EE集成

这些示例代码可以直接运行,帮您快速上手Java嵌入式数据库开发。