java sqlite3查询慢_java sqlite autocommit慢

背景：我正在使用Java在Netbeans中。java sqlite autocommit慢

我正在讀取CSV文件中的行並將它們插入到SQLite數據庫中。非常基本的東西，我會想。從本網站和其他地方找到的例子中，我構建了以下代碼。它的工作原理，但我很驚訝，看它有多慢。花費3.95秒將46行加載到數據庫中！使用Netbeans Profiler，我可以看到它佔用了大部分時間3.7秒的autoCommit()。

我在做什麼錯？或者，這僅僅是使用SQLite代替MySQL的代價？

public static void LoadJCNAClassesTable(Connection aConn, String strPath) throws SQLException{

String [] nextLine;

String strDivision;

String strClass;

String strNotes;

String strDescription;

Statement stat = aConn.createStatement();

stat.executeUpdate("drop table if exists JCNAClasses;");

String q = "create table JCNAClasses ('ID' INTEGER PRIMARY KEY AUTOINCREMENT, 'division' TEXT NOT NULL, 'class' TEXT NOT NULL UNIQUE, 'description' TEXT NOT NULL, 'note' TEXT NOT NULL, 'node' INTEGER NOT NULL);";

stat.executeUpdate(q);

CSVReader reader;

int iLine;

String JCNAClassesCSV = strPath + "\\JCNAClassesCsv.txt" ;

try {

reader = new CSVReader(new FileReader(JCNAClassesCSV));

iLine = 0;

while ((nextLine = reader.readNext()) != null) {

// nextLine[] is an array of values from the line

// System.out.println(nextLine[0] + nextLine[1]);

if (iLine > 0){

strDivision = nextLine[0];

strClass = nextLine[1];

strDescription= nextLine[2];

strNotes= nextLine[3];

PreparedStatement prep = aConn.prepareStatement("insert into JCNAClasses ('division', 'class', 'description', 'note', 'node') values (?, ?, ?, ?, ?);");

prep.setString(1, strDivision); // note that the comma seems not to be a problem

prep.setString(2,strClass);

prep.setString(3,strDescription);

prep.setString(4,strNotes);

prep.setInt(5,iLine);

prep.addBatch();

aConn.setAutoCommit(false);

prep.executeBatch();

aConn.setAutoCommit(true);

}

iLine++;

}

} catch (FileNotFoundException ex) {

Logger.getLogger(Entries.class.getName()).log(Level.SEVERE, null, ex);

}

catch (IOException ex) {

Logger.getLogger(Entries.class.getName()).log(Level.SEVERE, null, ex);

} catch (SQLException ex) {

Logger.getLogger(LoadSQLiteConcoursDatabase.class.getName()).log(Level.SEVERE, null, ex);

}

}

public static void LoadConcoursEntriesTable(Connection aConn, String strPath) throws SQLException{

String [] nextLine;

String strEntryName;

String strClass;

Integer intYear;

String strDescription;

String strOwnerFirst;

String strOwnerLast;

Integer intJCNA;

String strColor;

String strPlate;

Integer intNode;

Long intPersonnel_id;

Long intJaguar_id;

ResultSet generatedKeys;

String strStatus;

int intPersonnelNode;

ResultSet r;

CSVReader reader;

String q;

int iLine;

PreparedStatement prep;

ResultSet rs;

Statement stat = aConn.createStatement();

//

// Concourse Personnel Table: Owners, Judges, & Interested parties

//

stat.executeUpdate("drop table if exists ConcoursPersonnel");

// status: Owner = "O"; "J" = Judge; "OJ" = Owner & Judge; "IP" = Interested party, e.g., spouse, restorer

q = "create table ConcoursPersonnel ('personnel_id' INTEGER PRIMARY KEY AUTOINCREMENT , 'ownerfirst' TEXT NOT NULL, 'ownerlast' TEXT NOT NULL, 'jcna' INTEGER NOT NULL UNIQUE ON CONFLICT IGNORE, 'status' TEXT NOT NULL, 'node' INTEGER NOT NULL UNIQUE)";

stat.executeUpdate(q);

//

// Concours Jaguar Table

//

stat.executeUpdate("drop table if exists ConcoursJaguars");

q = "create table ConcoursJaguars ('jaguar_id' INTEGER PRIMARY KEY AUTOINCREMENT , 'class' TEXT NOT NULL, 'year' TEXT NOT NULL, 'description' TEXT NOT NULL, 'Color' TEXT, 'plate' TEXT, 'node' INTEGER NOT NULL UNIQUE)";

stat.executeUpdate(q);

//

// Entry Table (a Relationship or "link" between Personnel & Jaguars

//

stat.executeUpdate("drop table if exists ConcoursEntries");

q = "create table ConcoursEntries (entry_name TEXT NOT NULL, personnel_id INTEGER NOT NULL, jaguar_id INTEGER NOT NULL, UNIQUE (personnel_id, jaguar_id), FOREIGN KEY (personnel_id) REFERENCES ConcoursPersonnel (Personnel_ID), FOREIGN KEY (jaguar_id) REFERENCES ConcoursPersonnel (jaguar_id))";

stat.executeUpdate(q);

String EntriesCSV = strPath + "\\EntriesCsv.txt" ;

strStatus = "O"; // not in the CSV data so set to Owner

try {

reader = new CSVReader(new FileReader(EntriesCSV));

iLine = 0;

while ((nextLine = reader.readNext()) != null) {

// nextLine[] is an array of values from the line

// System.out.println(nextLine[0] + nextLine[1]);

if (iLine > 0){

strEntryName = nextLine[0];

strClass = nextLine[1];

intYear= Integer.parseInt(nextLine[2]);

strDescription= nextLine[3];

strOwnerFirst= nextLine[4];

strOwnerLast= nextLine[5];

intJCNA = Integer.parseInt(nextLine[6]) ;

strColor= nextLine[7];

strPlate= nextLine[8];

intNode= Integer.parseInt(nextLine[9]); // Since Jaguars are 1-to-1 with Entries this is used as Node number for both. However, it can't be used for Personnel Node

//

// Load Owners into Personnel Table

//

Statement s = aConn.createStatement();

r = s.executeQuery("SELECT COUNT(*) AS rowcount FROM ConcoursPersonnel");

r.next();

intPersonnelNode = r.getInt("rowcount") +1 ; // Assignes Personnel node numbers as a continuous sequence

prep = aConn.prepareStatement("insert into ConcoursPersonnel ('ownerfirst', 'ownerlast', 'jcna', 'status', 'node') values (?, ?, ?, ?, ?);");

//prep.setString(1, strEntryName); // note that the comma seems not to be a problem

prep.setString(1,strOwnerFirst);

prep.setString(2,strOwnerLast);

prep.setInt(3,intJCNA);

prep.setString(4,strStatus);

prep.setInt(5,intPersonnelNode);

prep.addBatch();

aConn.setAutoCommit(false); // starts transaction

prep.executeBatch();

aConn.setAutoCommit(true); // ends transaction

aConn.setAutoCommit(false); // starts transaction

stat = aConn.createStatement();

generatedKeys = stat.executeQuery("SELECT last_insert_rowid()");

intPersonnel_id = 0L; // won't be used

if (generatedKeys.next()) {

intPersonnel_id = generatedKeys.getLong(1);

}

else{

System.out.println("No Personnel ID found in LoadConcoursEntriesTable");

System.exit(-1);

}

aConn.setAutoCommit(true); // Commits transaction.

//

// Load Entry cars into the ConcoursJaguars table

//

prep = aConn.prepareStatement("insert into ConcoursJaguars ('class', 'year', 'description', 'color', 'plate', 'node') values (?, ?, ?, ?, ?, ?);");

prep.setString(1,strClass);

prep.setInt(2,intYear);

prep.setString(3,strDescription);

prep.setString(4,strColor);

prep.setString(5,strPlate);

prep.setInt(6,intNode); //

prep.addBatch();

aConn.setAutoCommit(false);

prep.executeBatch();

aConn.setAutoCommit(true);

q = "select jaguar_id from ConcoursJaguars where node == " + intNode + ";";

rs = stat.executeQuery(q);

intJaguar_id = rs.getLong("jaguar_id");

prep = aConn.prepareStatement("insert into ConcoursEntries (entry_name, personnel_id, jaguar_id) values (?, ?, ?);");

//prep.setString(1, strEntryName); // note that the comma seems not to be a problem

prep.setString(1,strEntryName);

prep.setLong(2,intPersonnel_id);

prep.setLong(3,intJaguar_id);

prep.addBatch();

aConn.setAutoCommit(false);

prep.executeBatch();

aConn.setAutoCommit(true);

}

iLine++;

}

} catch (FileNotFoundException ex) {

Logger.getLogger(Entries.class.getName()).log(Level.SEVERE, null, ex);

}

catch (IOException ex) {

Logger.getLogger(Entries.class.getName()).log(Level.SEVERE, null, ex);

} catch (SQLException ex) {

Logger.getLogger(LoadSQLiteConcoursDatabase.class.getName()).log(Level.SEVERE, null, ex);

}

}

2014-10-06

Ed S