/** * 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); }