/**
* load the temp file maintained by the MySQLbulkLoader into the DMBS. truncates the temp file,
* and leaves it open for more insertRecord() operations. returns number of records inserted.
*
* <p>TODO: perhaps instead of having each program that uses a DAO that uses bulk loading call
* 'completeInsert', get MySQLbulkLoader created by a factory, and have the factory remember to
* load all the tables from all the temp files before the program exits.
*
* @return number of records inserted
* @throws DaoException
* @throws IOException
*/
public int loadDataFromTempFileIntoDBMS() throws DaoException, IOException {
Connection con = null;
Statement stmt = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
try {
try {
// close the file, flushing all buffers before loading the DBMS
tempFileWriter.close();
} catch (IOException e) {
throw new DaoException(e);
}
con = JdbcUtil.getDbConnection(MySQLbulkLoader.class);
stmt = con.createStatement();
// will throw error if attempts to overwrite primary keys in table
String command = "LOAD DATA LOCAL INFILE '" + tempFileName + "' INTO TABLE " + tableName;
long startTime = System.currentTimeMillis();
boolean rv = stmt.execute(command);
// TODO: throw exception if rv == true
int updateCount = stmt.getUpdateCount();
long duration = (System.currentTimeMillis() - startTime) / 1000;
// reopen empty temp file
this.tempFileWriter = new BufferedWriter(new FileWriter(this.tempFileHandle, false));
return updateCount;
} catch (SQLException e) {
throw new DaoException(e);
} finally {
JdbcUtil.closeAll(MySQLbulkLoader.class, con, pstmt, rs);
}
}
/**
* @param keywordS
* @return Map<keyword, List<cosmic>>
* @throws DaoException
*/
public static Map<String, Set<CosmicMutationFrequency>> getCosmicDataByKeyword(
Collection<String> keywordS) throws DaoException {
Connection con = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
try {
con = JdbcUtil.getDbConnection(DaoCosmicData.class);
pstmt =
con.prepareStatement(
"SELECT * FROM cosmic_mutation "
+ " WHERE KEYWORD in ('"
+ StringUtils.join(keywordS, "','")
+ "')");
rs = pstmt.executeQuery();
Map<String, Set<CosmicMutationFrequency>> ret =
new HashMap<String, Set<CosmicMutationFrequency>>();
while (rs.next()) {
CosmicMutationFrequency cmf = extractCosmic(rs);
Set<CosmicMutationFrequency> cmfs = ret.get(cmf.getKeyword());
if (cmfs == null) {
cmfs = new HashSet<CosmicMutationFrequency>();
ret.put(cmf.getKeyword(), cmfs);
}
cmfs.add(cmf);
}
return ret;
} catch (SQLException e) {
throw new DaoException(e);
} finally {
JdbcUtil.closeAll(DaoCosmicData.class, con, pstmt, rs);
}
}
public List getBooksByCost(String cost) {
List al = new ArrayList();
try {
con = JdbcUtil.getMysqlConnection();
ps = con.prepareStatement("select *from jlcbooks where cost=?");
ps.setString(1, cost);
rs = ps.executeQuery();
while (rs.next()) {
Book bo = new Book();
bo.setBid(rs.getString(1));
bo.setBname(rs.getString(2));
bo.setAuthor(rs.getString(3));
bo.setPub(rs.getString(4));
bo.setCost(rs.getString(5));
bo.setEdi(rs.getString(6));
bo.setIsbn(rs.getString(7));
al.add(bo);
}
} catch (Exception e) {
e.printStackTrace();
} finally {
JdbcUtil.cleanup(ps, con);
}
return al;
}
public JdbcStore(SessionSettings settings, SessionID sessionID, DataSource ds) throws Exception {
this.sessionID = sessionID;
if (settings.isSetting(sessionID, SETTING_JDBC_STORE_SESSIONS_TABLE_NAME)) {
sessionTableName = settings.getString(sessionID, SETTING_JDBC_STORE_SESSIONS_TABLE_NAME);
} else {
sessionTableName = DEFAULT_SESSION_TABLE_NAME;
}
if (settings.isSetting(sessionID, SETTING_JDBC_STORE_MESSAGES_TABLE_NAME)) {
messageTableName = settings.getString(sessionID, SETTING_JDBC_STORE_MESSAGES_TABLE_NAME);
} else {
messageTableName = DEFAULT_MESSAGE_TABLE_NAME;
}
if (settings.isSetting(sessionID, SETTING_JDBC_SESSION_ID_DEFAULT_PROPERTY_VALUE)) {
defaultSessionIdPropertyValue =
settings.getString(sessionID, SETTING_JDBC_SESSION_ID_DEFAULT_PROPERTY_VALUE);
} else {
defaultSessionIdPropertyValue = SessionID.NOT_SET;
}
dataSource = ds == null ? JdbcUtil.getDataSource(settings, sessionID) : ds;
// One table is sampled for the extended session ID columns. Be sure
// that all tables are extended if you extend any of them.
extendedSessionIdSupported = JdbcUtil.determineSessionIdSupport(dataSource, sessionTableName);
setSqlStrings();
loadCache();
}
public boolean set(int sequence, String message) throws IOException {
Connection connection = null;
PreparedStatement insert = null;
ResultSet rs = null;
try {
connection = dataSource.getConnection();
insert = connection.prepareStatement(SQL_INSERT_MESSAGE);
int offset = setSessionIdParameters(insert, 1);
insert.setInt(offset++, sequence);
insert.setString(offset, message);
insert.execute();
} catch (SQLException ex) {
if (connection != null) {
PreparedStatement update = null;
try {
update = connection.prepareStatement(SQL_UPDATE_MESSAGE);
update.setString(1, message);
int offset = setSessionIdParameters(update, 2);
update.setInt(offset, sequence);
boolean status = update.execute();
return !status ? update.getUpdateCount() > 0 : false;
} catch (SQLException e) {
throw (IOException) new IOException(e.getMessage()).initCause(e);
} finally {
JdbcUtil.close(sessionID, update);
}
}
} finally {
JdbcUtil.close(sessionID, rs);
JdbcUtil.close(sessionID, insert);
JdbcUtil.close(sessionID, connection);
}
return true;
}
public int updateBook(Book bo) {
int x = 0;
try {
con = JdbcUtil.getMysqlConnection();
System.out.println("update 2");
ps =
con.prepareStatement(
"update jlcbooks set bname=?,author=?,pub=?,cost=?,edition=?,isbn=? where bid=?");
System.out.println("update 3");
System.out.println(bo.getBid());
ps.setString(7, bo.getBid());
ps.setString(1, bo.getBname());
ps.setString(2, bo.getAuthor());
ps.setString(3, bo.getPub());
ps.setString(4, bo.getCost());
ps.setString(5, bo.getEdi());
ps.setString(6, bo.getIsbn());
x = ps.executeUpdate();
} catch (Exception e) {
e.printStackTrace();
} finally {
JdbcUtil.cleanup(ps, con);
}
return x;
}
public void reset() throws IOException {
cache.reset();
Connection connection = null;
PreparedStatement deleteMessages = null;
PreparedStatement updateTime = null;
try {
connection = dataSource.getConnection();
deleteMessages = connection.prepareStatement(SQL_DELETE_MESSAGES);
setSessionIdParameters(deleteMessages, 1);
deleteMessages.execute();
updateTime = connection.prepareStatement(SQL_UPDATE_SESSION);
updateTime.setTimestamp(
1, new Timestamp(Calendar.getInstance(TimeZone.getTimeZone("UTC")).getTimeInMillis()));
updateTime.setInt(2, getNextTargetMsgSeqNum());
updateTime.setInt(3, getNextSenderMsgSeqNum());
setSessionIdParameters(updateTime, 4);
updateTime.execute();
} catch (SQLException e) {
throw (IOException) new IOException(e.getMessage()).initCause(e);
} catch (IOException e) {
throw e;
} finally {
JdbcUtil.close(sessionID, deleteMessages);
JdbcUtil.close(sessionID, updateTime);
JdbcUtil.close(sessionID, connection);
}
}
private void loadCache() throws SQLException, IOException {
Connection connection = null;
PreparedStatement query = null;
PreparedStatement insert = null;
ResultSet rs = null;
try {
connection = dataSource.getConnection();
query = connection.prepareStatement(SQL_GET_SEQNUMS);
setSessionIdParameters(query, 1);
rs = query.executeQuery();
if (rs.next()) {
cache.setCreationTime(SystemTime.getUtcCalendar(rs.getTimestamp(1)));
cache.setNextTargetMsgSeqNum(rs.getInt(2));
cache.setNextSenderMsgSeqNum(rs.getInt(3));
} else {
insert = connection.prepareStatement(SQL_INSERT_SESSION);
int offset = setSessionIdParameters(insert, 1);
insert.setTimestamp(offset++, new Timestamp(cache.getCreationTime().getTime()));
insert.setInt(offset++, cache.getNextTargetMsgSeqNum());
insert.setInt(offset, cache.getNextSenderMsgSeqNum());
insert.execute();
}
} finally {
JdbcUtil.close(sessionID, rs);
JdbcUtil.close(sessionID, query);
JdbcUtil.close(sessionID, insert);
JdbcUtil.close(sessionID, connection);
}
}
static boolean existsTable(Connection connection, TableName tableName) throws Exception {
Statement st = connection.createStatement();
ResultSet rs = null;
try {
rs = st.executeQuery("select * from " + tableName);
return true;
} catch (SQLException e) {
return false;
} finally {
JdbcUtil.safeClose(rs);
JdbcUtil.safeClose(st);
}
}
public static void deleteAllRecords() throws DaoException {
Connection con = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
try {
con = JdbcUtil.getDbConnection(DaoCosmicData.class);
pstmt = con.prepareStatement("TRUNCATE TABLE cosmic_mutation");
pstmt.executeUpdate();
} catch (SQLException e) {
throw new DaoException(e);
} finally {
JdbcUtil.closeAll(DaoCosmicData.class, con, pstmt, rs);
}
}
public int deleteBook(String bid) {
int x = 0;
try {
con = JdbcUtil.getMysqlConnection();
ps = con.prepareStatement("delete from jlcbooks where bid=?");
ps.setString(1, bid);
x = ps.executeUpdate();
} catch (Exception e) {
e.printStackTrace();
} finally {
JdbcUtil.cleanup(ps, con);
}
return x;
}
public boolean tableExists(Connection connection, TableName tableName)
throws PersistenceException {
if (tableName == null) {
throw new NullPointerException("table name is mandatory");
}
ResultSet rs = null;
try {
// we need to make sure, that (even if the user has extended permissions) only the tables in
// current schema are checked
// explicit set of the schema to the current user one to make sure only tables of the current
// users are requested
DatabaseMetaData metaData = connection.getMetaData();
String schemaPattern = tableName.getSchema();
if (schemaPattern == null) {
switch (getDialect()) {
case ORACLE:
schemaPattern = metaData.getUserName();
break;
default:
}
}
rs = metaData.getTables(null, schemaPattern, tableName.getName(), new String[] {"TABLE"});
return rs.next();
} catch (SQLException e) {
if (trace) log.tracef(e, "SQLException occurs while checking the table %s", tableName);
return false;
} finally {
JdbcUtil.safeClose(rs);
}
}
private void storeSequenceNumbers() throws IOException {
Connection connection = null;
PreparedStatement update = null;
try {
connection = dataSource.getConnection();
update = connection.prepareStatement(SQL_UPDATE_SEQNUMS);
update.setInt(1, cache.getNextTargetMsgSeqNum());
update.setInt(2, cache.getNextSenderMsgSeqNum());
setSessionIdParameters(update, 3);
update.execute();
} catch (SQLException e) {
throw (IOException) new IOException(e.getMessage()).initCause(e);
} finally {
JdbcUtil.close(sessionID, update);
JdbcUtil.close(sessionID, connection);
}
}
/**
* Save the GameCharacter into the database.
*
* @param character The game character that should be saved.
* @exception LabySQLException thrown in case of database problems.
*/
public static void save(GameCharacter character) throws LabySQLException {
// Save user state into database.
try {
Log.info("CharacterState", "save", "Save state in DB...");
JdbcUtil.saveCharacter(character);
} catch (NoRecordUpdatedException e) {
throw new LabySQLException("User data not stored: " + character);
}
}
public int verifyUser(String un, String pw) {
int x = 0;
try {
con = JdbcUtil.getMysqlConnection();
ps = con.prepareStatement("select * from user_table where username=? and password=?");
ps.setString(1, un);
ps.setString(2, pw);
rs = ps.executeQuery();
if (rs.next()) {
x = 1;
}
} catch (Exception e) {
e.printStackTrace();
} finally {
JdbcUtil.cleanup(ps, con, rs);
}
return x;
}
public int addBook(Book bo) {
int x = 0;
try {
con = JdbcUtil.getMysqlConnection();
ps = con.prepareStatement("insert into jlcbooks values(?,?,?,?,?,?,?)");
ps.setString(1, bo.getBid());
ps.setString(2, bo.getBname());
ps.setString(3, bo.getAuthor());
ps.setString(4, bo.getPub());
ps.setString(5, bo.getCost());
ps.setString(6, bo.getEdi());
ps.setString(7, bo.getIsbn());
x = ps.executeUpdate();
} catch (Exception e) {
e.printStackTrace();
} finally {
JdbcUtil.cleanup(ps, con);
}
return x;
}
private void executeUpdateSql(Connection conn, String sql) throws PersistenceException {
Statement statement = null;
try {
statement = conn.createStatement();
statement.executeUpdate(sql);
} catch (SQLException e) {
log.errorCreatingTable(sql, e);
throw new PersistenceException(e);
} finally {
JdbcUtil.safeClose(statement);
}
}
/**
* @param fetchSize hint as to the number of rows that should be fetched from the database at a
* time. will be limited to 10,000
*/
@Override
public void runPreparedQuery(int fetchSize, String query, Object... data) throws JdbcException {
try {
allowReuse();
preparedStatement = connection.prepareStatement(query, resultSetType, resultSetConcurrency);
preparedStatement.setFetchSize(Math.min(fetchSize, JdbcConstants.JDBC__MAX_FETCH_SIZE));
JdbcUtil.setInputParametersForStatement(preparedStatement, data);
rSet = preparedStatement.executeQuery();
} catch (SQLException ex) {
throw newJdbcException(ex);
}
}
/**
* Load the GameCharacter from the database.
*
* @param name The (unique) name of the game character.
* @return A GameCharacter object holding all information.
* @exception UnknownUserException If no entry with the given name could be found in the database.
*/
public static GameCharacter load(String name) throws UnknownUserException {
// Load user state from database. This method throws a
// UserNotFoundException if the user is not in the database.
Log.info("CharacterState", "load", "Load data of character: " + name);
try {
GameCharacter character = JdbcUtil.loadCharacter(name);
character.setStateful(true);
Log.info("CharacterState", "load", "Character data loaded.");
return character;
} catch (NoRecordFoundException e) {
throw new UnknownUserException("Character name: " + name);
}
}
public Book getBookByBid(String bid) {
Book bo = null;
try {
con = JdbcUtil.getMysqlConnection();
ps = con.prepareStatement("select *from jlcbooks where bid=?");
ps.setString(1, bid);
rs = ps.executeQuery();
if (rs.next()) {
bo = new Book();
bo.setBid(rs.getString(1));
bo.setBname(rs.getString(2));
bo.setAuthor(rs.getString(3));
bo.setPub(rs.getString(4));
bo.setCost(rs.getString(5));
bo.setEdi(rs.getString(6));
bo.setIsbn(rs.getString(7));
}
} catch (Exception e) {
e.printStackTrace();
} finally {
JdbcUtil.cleanup(ps, con);
}
return bo;
}
public void get(int startSequence, int endSequence, Collection<String> messages)
throws IOException {
Connection connection = null;
PreparedStatement query = null;
ResultSet rs = null;
try {
connection = dataSource.getConnection();
query = connection.prepareStatement(SQL_GET_MESSAGES);
int offset = setSessionIdParameters(query, 1);
query.setInt(offset++, startSequence);
query.setInt(offset, endSequence);
rs = query.executeQuery();
while (rs.next()) {
String message = rs.getString(1);
messages.add(message);
}
} catch (SQLException e) {
throw (IOException) new IOException(e.getMessage()).initCause(e);
} finally {
JdbcUtil.close(sessionID, rs);
JdbcUtil.close(sessionID, query);
JdbcUtil.close(sessionID, connection);
}
}
@Test(dependsOnMethods = "testExists")
public void testDrop() throws Exception {
assert tableManipulation.tableExists(connection);
PreparedStatement ps = null;
try {
ps =
connection.prepareStatement(
"INSERT INTO " + tableManipulation.getTableName() + "(ID_COLUMN) values(?)");
ps.setString(1, System.currentTimeMillis() + "");
assert 1 == ps.executeUpdate();
} finally {
JdbcUtil.safeClose(ps);
}
tableManipulation.dropTable(connection);
assert !tableManipulation.tableExists(connection);
}
private final void setSqlStrings() {
String idWhereClause = JdbcUtil.getIDWhereClause(extendedSessionIdSupported);
String idColumns = JdbcUtil.getIDColumns(extendedSessionIdSupported);
String idPlaceholders = JdbcUtil.getIDPlaceholders(extendedSessionIdSupported);
SQL_UPDATE_SEQNUMS =
"UPDATE "
+ sessionTableName
+ " SET incoming_seqnum=?, "
+ "outgoing_seqnum=? WHERE "
+ idWhereClause;
SQL_INSERT_SESSION =
"INSERT INTO "
+ sessionTableName
+ " ("
+ idColumns
+ ", creation_time,incoming_seqnum, outgoing_seqnum) VALUES ("
+ idPlaceholders
+ ",?,?,?)";
SQL_GET_SEQNUMS =
"SELECT creation_time, incoming_seqnum, outgoing_seqnum FROM "
+ sessionTableName
+ " WHERE "
+ idWhereClause;
SQL_UPDATE_MESSAGE =
"UPDATE "
+ messageTableName
+ " SET message=? "
+ "WHERE "
+ idWhereClause
+ " and msgseqnum=?";
SQL_INSERT_MESSAGE =
"INSERT INTO "
+ messageTableName
+ " ("
+ idColumns
+ ", msgseqnum,message) VALUES ("
+ idPlaceholders
+ ",?,?)";
SQL_GET_MESSAGES =
"SELECT message FROM "
+ messageTableName
+ " WHERE "
+ idWhereClause
+ " and msgseqnum>=? and msgseqnum<=? "
+ "ORDER BY msgseqnum";
SQL_UPDATE_SESSION =
"UPDATE "
+ sessionTableName
+ " SET creation_time=?, "
+ "incoming_seqnum=?, outgoing_seqnum=? "
+ "WHERE "
+ idWhereClause;
SQL_DELETE_MESSAGES = "DELETE FROM " + messageTableName + " WHERE " + idWhereClause;
}
private int setSessionIdParameters(PreparedStatement query, int offset) throws SQLException {
return JdbcUtil.setSessionIdParameters(
sessionID, query, offset, extendedSessionIdSupported, defaultSessionIdPropertyValue);
}
