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