Reusing PreparedStatments
Hello,
Yet another question about PreparedStatments.
I have a application that processes events and (depending on conditions) save them into a database.
I have a Event class which represents the event.
class Event {
public int id;
public String name;
}
And a DataStore which handles the SQL part. Since it is alot of events ca 500/sec I thought that I should use PreparedStatments.
class DataStore {
private PreparedStatement pStmnt;
public DataStore(Connection conn) {
try {
pStmnt = conn.prepareStatement("INSERT INTO EVENTS VALUES(?, ?)");
} catch (SQLException e) {
e.printStackTrace();
}
}
public void storeEvent(Event e) throws SQLException {
pStmnt.clearParameters();
pStmnt.setInt(1, e.id);
pStmnt.setString(2, e.name);
pStmnt.executeUpdate();
}
public void closeDataStore() {
try {
pStmnt.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
Then in the application I do something like this:
Event e;
while(appIsRunning) {
// Check the event and if all is good
dataStore.storeEvent(e);
}
dataStore.close();
The problem is when I start using this in multiple instances (processes/threads).
The INSERT creates a lock which isn't released until I close the PreparedStatment.
But closing and recreating the PreparedStatment. in DataStore#storeEvent(Event) seems to me to defeat the whole purpose of using a PreparedStatment ?
What have I missed ?
And in the main:
