Java: mySQL VALUES

Der er ikke nogen superløsning.

Jeg kan se to rimelige løsninger:

A)  konkataner som du gør, men brug StringBuilder append fremfor String + og brug prepared statement

B)  indsæt en række ad gangen men bruge transaction/batch til at bundte flere inserts med og brug prepared statement

Kan du prøve at forklare løsning b lidt mere detaljeret - evt. med noget kodeeksempel?

Vil du smide et svar?

gerne

Men et eksempel som viser lidt forskelligt:

package march;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class MySQL {
    private final static int NREC = 10000;
    public static void normal() throws SQLException {
        Connection con = DriverManager.getConnection("jdbc:mysql://localhost/Test", "", "");
        Statement stmt = con.createStatement();
        stmt.executeUpdate("CREATE TABLE t (f1 INTEGER NOT NULL, f2 VARCHAR(50), PRIMARY KEY(f1))");
        long t1 = System.currentTimeMillis();
        PreparedStatement pstmt = con.prepareStatement("INSERT INTO t VALUES(?,?)");
        for (int i = 0; i < NREC; i++) {
            pstmt.setInt(1, i + 1);
            pstmt.setString(2, "Dette er en test !");
            pstmt.executeUpdate();
        }
        pstmt.close();
        long t2 = System.currentTimeMillis();
        ResultSet rs = stmt.executeQuery("SELECT COUNT(*) FROM t");
        rs.next();
        System.out.println("Normal: " + rs.getInt(1) + " records inserted in " + (t2 - t1) + " milliseconds");
        stmt.executeUpdate("DROP TABLE t");
        stmt.close();
        con.close();
    }
    public static void transaction() throws SQLException {
        Connection con = DriverManager.getConnection("jdbc:mysql://localhost/Test", "", "");
        Statement stmt = con.createStatement();
        stmt.executeUpdate("CREATE TABLE t (f1 INTEGER NOT NULL, f2 VARCHAR(50), PRIMARY KEY(f1))");
        long t1 = System.currentTimeMillis();
        con.setAutoCommit(false);
        PreparedStatement pstmt = con.prepareStatement("INSERT INTO t VALUES(?,?)");
        for (int i = 0; i < NREC; i++) {
            pstmt.setInt(1, i + 1);
            pstmt.setString(2, "Dette er en test !");
            pstmt.executeUpdate();
        }
        con.commit();
        pstmt.close();
        long t2 = System.currentTimeMillis();
        ResultSet rs = stmt.executeQuery("SELECT COUNT(*) FROM t");
        rs.next();
        System.out.println("Transaction: " + rs.getInt(1) + " records inserted in " + (t2 - t1) + " milliseconds");
        stmt.executeUpdate("DROP TABLE t");
        stmt.close();
        con.close();
    }
    public static void batch() throws SQLException {
        Connection con = DriverManager.getConnection("jdbc:mysql://localhost/Test", "", "");
        Statement stmt = con.createStatement();
        stmt.executeUpdate("CREATE TABLE t (f1 INTEGER NOT NULL, f2 VARCHAR(50), PRIMARY KEY(f1))");
        long t1 = System.currentTimeMillis();
        con.setAutoCommit(false);
        PreparedStatement pstmt = con.prepareStatement("INSERT INTO t VALUES(?,?)");
        for (int i = 0; i < NREC; i++) {
            pstmt.setInt(1, i + 1);
            pstmt.setString(2, "Dette er en test !");
            pstmt.addBatch();
        }
        pstmt.executeBatch();
        con.commit();
        pstmt.close();
        long t2 = System.currentTimeMillis();
        ResultSet rs = stmt.executeQuery("SELECT COUNT(*) FROM t");
        rs.next();
        System.out.println("Batch: " + rs.getInt(1) + " records inserted in " + (t2 - t1) + " milliseconds");
        stmt.executeUpdate("DROP TABLE t");
        stmt.close();
        con.close();
    }
    public static void dirtyMyISAMHack() throws SQLException {
        Connection con = DriverManager.getConnection("jdbc:mysql://localhost/Test", "", "");
        Statement stmt = con.createStatement();
        stmt.executeUpdate("CREATE TABLE t (f1 INTEGER NOT NULL, f2 VARCHAR(50), PRIMARY KEY(f1)) DELAY_KEY_WRITE=1");
        long t1 = System.currentTimeMillis();
        PreparedStatement pstmt = con.prepareStatement("INSERT INTO t VALUES(?,?)");
        for (int i = 0; i < NREC; i++) {
            pstmt.setInt(1, i + 1);
            pstmt.setString(2, "Dette er en test !");
            pstmt.executeUpdate();
        }
        pstmt.close();
        long t2 = System.currentTimeMillis();
        ResultSet rs = stmt.executeQuery("SELECT COUNT(*) FROM t");
        rs.next();
        System.out.println("Dirty MyISAM hack: " + rs.getInt(1) + " records inserted in " + (t2 - t1) + " milliseconds");
        stmt.executeUpdate("DROP TABLE t");
        stmt.close();
        con.close();
    }
    public static void main(String[] args) throws Exception {
        Class.forName("com.mysql.jdbc.Driver");
        for(int i = 0; i < 3; i++) {
            normal();
            transaction();
            batch();
            dirtyMyISAMHack();
        }
    }
}