Java中使用Lambda表达式高效插入MySQL数据的实践教程

// 假设 con 和 pst 已正确初始化
// ...
map.entrySet().stream().sorted((k1, k2) -> -k1.getValue().compareTo(k2.getValue()))             
    .forEach(k -> out.println(k.getKey() + ": " + k.getValue())); // k 在此处有效

// 错误示范：k 在此处已超出作用域
// pst.setInt(1, k.getValue()); 
// pst.setString(2, k.getKey());
// pst.executeUpdate();

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.Map;
import java.util.LinkedHashMap; // 假设 map 是 LinkedHashMap 或其他 Map 实现
import java.util.logging.Level;
import java.util.logging.Logger;

public class LambdaDbInsert {

    private static final Logger LGR = Logger.getLogger(LambdaDbInsert.class.getName());

    public static void main(String[] args) {
        // 模拟一个已排序的Map数据
        Map<String, Integer> map = new LinkedHashMap<>();
        map.put("apple", 10);
        map.put("banana", 5);
        map.put("orange", 15);
        map.put("grape", 8);

        // 数据库连接信息
        String url = "jdbc:mysql://localhost:3306/eagle_scoutleader";
        String user = "root";
        String password = "your_password"; // 请替换为实际密码
        String sql = "INSERT INTO cats(wordCount, wordName) VALUES(?,?)";

        // 确保JDBC驱动已加载，对于现代JDBC驱动，通常不需要显式调用Class.forName
        // try {
        //     Class.forName("com.mysql.cj.jdbc.Driver");
        // } catch (ClassNotFoundException e) {
        //     LGR.log(Level.SEVERE, "MySQL JDBC Driver not found!", e);
        //     return;
        // }

        try (Connection con = DriverManager.getConnection(url, user, password);
             PreparedStatement pst = con.prepareStatement(sql)) {

            map.entrySet().stream()
                .sorted((k1, k2) -> -k1.getValue().compareTo(k2.getValue())) // 按值降序排序
                .forEach(k -> {
                    try {
                        pst.setInt(1, k.getValue());
                        pst.setString(2, k.getKey());
                        pst.executeUpdate();
                        // System.out.println("Inserted: " + k.getKey() + " -> " + k.getValue());
                    } catch (SQLException e) {
                        // 在lambda内部处理SQL异常，将其封装为运行时异常抛出
                        // 或者根据业务需求进行更细致的异常处理
                        LGR.log(Level.SEVERE, "Error inserting record for key: " + k.getKey(), e);
                        // 可以选择抛出 RuntimeException 中断流，或继续处理其他元素
                        // throw new RuntimeException("Database insert failed", e); 
                    }
                });
            System.out.println("Data insertion complete using Lambda Stream.");

            // 示例：查询并打印插入的数据 (可选)
            // try (Statement statement = con.createStatement();
            //      ResultSet rs = statement.executeQuery("SELECT wordCount, wordName FROM cats ORDER BY wordCount DESC")) {
            //     System.out.println("\nInserted data in database:");
            //     while (rs.next()) {
            //         System.out.println(rs.getInt("wordCount") + ": " + rs.getString("wordName"));
            //     }
            // }

        } catch (SQLException ex) {
            LGR.log(Level.SEVERE, "Database connection or preparation failed: " + ex.getMessage(), ex);
        } catch (Exception ex) {
            LGR.log(Level.SEVERE, "An unexpected error occurred: " + ex.getMessage(), ex);
        }
    }
}

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.Comparator;
import java.util.Map;
import java.util.LinkedHashMap;
import java.util.logging.Level;
import java.util.logging.Logger;

public class TraditionalDbInsert {

    private static final Logger LGR = Logger.getLogger(TraditionalDbInsert.class.getName());

    public static void main(String[] args) {
        Map<String, Integer> map = new LinkedHashMap<>();
        map.put("apple", 10);
        map.put("banana", 5);
        map.put("orange", 15);
        map.put("grape", 8);

        String url = "jdbc:mysql://localhost:3306/eagle_scoutleader";
        String user = "root";
        String password = "your_password"; // 请替换为实际密码
        String sql = "INSERT INTO cats(wordCount, wordName) VALUES(?,?)";

        try (Connection con = DriverManager.getConnection(url, user, password);
             PreparedStatement pst = con.prepareStatement(sql)) {

            // 对entrySet进行排序，并转换为List以便进行传统循环
            map.entrySet().stream()
                .sorted(Comparator.comparing(Map.Entry::getValue).reversed()) // 按值降序排序
                .forEach(entry -> { // 使用forEach将排序结果收集到List中，或者直接迭代
                    try {
                        pst.setInt(1, entry.getValue());
                        pst.setString(2, entry.getKey());
                        pst.executeUpdate();
                        // System.out.println("Inserted: " + entry.getKey() + " -> " + entry.getValue());
                    } catch (SQLException e) {
                        LGR.log(Level.SEVERE, "Error inserting record for key: " + entry.getKey(), e);
                    }
                });
            System.out.println("Data insertion complete using Traditional Loop (via Stream.forEach for sorting).");

            // 如果不使用Stream进行排序，则需要手动排序或接受Map的默认迭代顺序
            // 或者：
            // List<Map.Entry<String, Integer>> sortedEntries = map.entrySet().stream()
            //     .sorted(Comparator.comparing(Map.Entry::getValue).reversed())
            //     .collect(Collectors.toList());
            //
            // for (Map.Entry<String, Integer> entry : sortedEntries) {
            //     pst.setInt(1, entry.getValue());
            //     pst.setString(2, entry.getKey());
            //     pst.executeUpdate();
            // }

        } catch (SQLException ex) {
            LGR.log(Level.SEVERE, "Database connection or preparation failed: " + ex.getMessage(), ex);
        } catch (Exception ex) {
            LGR.log(Level.SEVERE, "An unexpected error occurred: " + ex.getMessage(), ex);
        }
    }
}