在Java中使用Oracle数据库插入数据

要在Java中使用Oracle数据库插入数据，你可以使用JDBC（Java Database Connectivity）,以下是详细的步骤和代码示例：

准备工作

确保你已经：

• 安装了Oracle数据库
• 下载了Oracle JDBC驱动（ojdbc.jar）
• 将驱动添加到你的Java项目的类路径中

基本插入操作示例

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
public class OracleInsertExample {
    // 数据库连接信息
    private static final String DB_URL = "jdbc:oracle:thin:@localhost:1521:ORCL";
    private static final String USER = "username";
    private static final String PASS = "password";
    public static void main(String[] args) {
        // 要插入的数据
        String name = "John Doe";
        int age = 30;
        String email = "john.doe@example.com";
        // SQL插入语句
        String sql = "INSERT INTO employees (name, age, email) VALUES (?, ?, ?)";
        try (Connection conn = DriverManager.getConnection(DB_URL, USER, PASS);
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            // 设置参数
            pstmt.setString(1, name);
            pstmt.setInt(2, age);
            pstmt.setString(3, email);
            // 执行插入
            int rowsAffected = pstmt.executeUpdate();
            System.out.println(rowsAffected + " 行数据已插入");
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

批量插入示例

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
public class OracleBatchInsertExample {
    private static final String DB_URL = "jdbc:oracle:thin:@localhost:1521:ORCL";
    private static final String USER = "username";
    private static final String PASS = "password";
    public static void main(String[] args) {
        // 准备批量数据
        List<Employee> employees = new ArrayList<>();
        employees.add(new Employee("Alice", 28, "alice@example.com"));
        employees.add(new Employee("Bob", 35, "bob@example.com"));
        employees.add(new Employee("Charlie", 42, "charlie@example.com"));
        String sql = "INSERT INTO employees (name, age, email) VALUES (?, ?, ?)";
        try (Connection conn = DriverManager.getConnection(DB_URL, USER, PASS);
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            // 禁用自动提交
            conn.setAutoCommit(false);
            for (Employee emp : employees) {
                pstmt.setString(1, emp.getName());
                pstmt.setInt(2, emp.getAge());
                pstmt.setString(3, emp.getEmail());
                pstmt.addBatch();
            }
            // 执行批量插入
            int[] results = pstmt.executeBatch();
            // 提交事务
            conn.commit();
            System.out.println("批量插入完成，共插入 " + results.length + " 行数据");
        } catch (SQLException e) {
            e.printStackTrace();
            // 发生异常时回滚
            try {
                if (conn != null) conn.rollback();
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
        }
    }
    static class Employee {
        private String name;
        private int age;
        private String email;
        public Employee(String name, int age, String email) {
            this.name = name;
            this.age = age;
            this.email = email;
        }
        // getters
        public String getName() { return name; }
        public int getAge() { return age; }
        public String getEmail() { return email; }
    }
}

使用连接池（推荐）

在生产环境中，建议使用连接池（如HikariCP）来管理数据库连接：

import com.zaxxer.hikari.HikariConfig;
import com.zaxxer.hikari.HikariDataSource;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
public class OracleInsertWithPool {
    private static HikariDataSource dataSource;
    static {
        HikariConfig config = new HikariConfig();
        config.setJdbcUrl("jdbc:oracle:thin:@localhost:1521:ORCL");
        config.setUsername("username");
        config.setPassword("password");
        config.setDriverClassName("oracle.jdbc.OracleDriver");
        // 连接池配置
        config.setMaximumPoolSize(10);
        config.setMinimumIdle(5);
        config.setConnectionTimeout(30000);
        config.setIdleTimeout(600000);
        config.setMaxLifetime(1800000);
        dataSource = new HikariDataSource(config);
    }
    public static void insertEmployee(String name, int age, String email) {
        String sql = "INSERT INTO employees (name, age, email) VALUES (?, ?, ?)";
        try (Connection conn = dataSource.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setString(1, name);
            pstmt.setInt(2, age);
            pstmt.setString(3, email);
            pstmt.executeUpdate();
            System.out.println("数据插入成功");
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
    public static void main(String[] args) {
        insertEmployee("David", 45, "david@example.com");
    }
}

注意事项

1. SQL注入防护：始终使用PreparedStatement而不是字符串拼接SQL语句
2. 资源管理：使用try-with-resources确保数据库连接、语句和结果集被正确关闭
3. 事务管理：对于批量操作，考虑使用事务来确保数据一致性
4. 异常处理：妥善处理SQL异常，考虑记录日志
5. 性能优化：批量插入时使用addBatch()和executeBatch()方法
6. 连接池：生产环境中使用连接池而不是每次创建新连接

Maven依赖

如果你使用Maven，可以在pom.xml中添加Oracle JDBC驱动依赖：

<dependency>
    <groupId>com.oracle.database.jdbc</groupId>
    <artifactId>ojdbc8</artifactId>
    <version>19.8.0.0</version>
</dependency>

或者对于较新的版本：

<dependency>
    <groupId>com.oracle.database.jdbc</groupId>
    <artifactId>ojdbc11</artifactId>
    <version>21.5.0.0</version>
</dependency>

示例涵盖了从基本插入到批量操作的各种场景,你可以根据实际需求选择合适的方法。