public void alteraJobAposExclusao(Connection conn, JobLote job) throws SQLException {
ValoresAtualizaJob valores = retornaNovosValores(conn, job);
PreparedStatement stmt = null;
String update =
"update job set qtd_transf = ?, hr_tot = ?, qtd_sucata = ?, status = ? where job = ? and operacao = ? ";
stmt = conn.prepareStatement(update);
stmt.setDouble(1, valores.getQtdTransf());
stmt.setDouble(2, valores.getHoraTotal());
stmt.setDouble(3, valores.getQtdSucata());
stmt.setString(4, "R");
stmt.setString(5, job.getJob().trim().replace(".", ""));
stmt.setInt(6, job.getOperNum());
stmt.executeUpdate();
}
public void incluiIniciado(Connection conn, JobIniciado job) throws SQLException {
String sql =
"insert into jobsiniciados(job, operacao, data, tripulacao, ativo, recurso) values(?,?,?,?,?,?) ";
PreparedStatement stmt = null;
stmt = conn.prepareStatement(sql);
stmt.setString(1, job.getJob().trim().replace(".", ""));
stmt.setInt(2, job.getOperacao());
stmt.setTimestamp(3, Validacoes.converteDataStringEmFormatoTimeStamp(job.getData().trim()));
stmt.setDouble(4, job.getTripulacao());
stmt.setBoolean(5, true);
stmt.setString(6, job.getRecurso().trim());
stmt.executeUpdate();
}
public void alteraIniciado(Connection conn, JobIniciado job) throws SQLException {
String sql =
" update jobsiniciados set data = ?, ativo = ?, tripulacao = ?, recurso = ? "
+ " where job = ? and operacao = ? ";
PreparedStatement stmt = null;
stmt = conn.prepareStatement(sql);
stmt.setTimestamp(1, Validacoes.converteDataStringEmFormatoTimeStamp(job.getData().trim()));
stmt.setBoolean(2, true);
stmt.setDouble(3, job.getTripulacao());
stmt.setString(4, job.getRecurso().trim());
stmt.setString(5, job.getJob().trim().replace(".", ""));
stmt.setInt(6, job.getOperacao());
stmt.executeUpdate();
}
public synchronized void insertData(String name, long modified, int type, DLNAMediaInfo media) {
Connection conn = null;
PreparedStatement ps = null;
try {
conn = getConnection();
ps =
conn.prepareStatement(
"INSERT INTO FILES(FILENAME, MODIFIED, TYPE, DURATION, BITRATE, WIDTH, HEIGHT, SIZE, CODECV, FRAMERATE, ASPECT, ASPECTRATIOCONTAINER, ASPECTRATIOVIDEOTRACK, REFRAMES, AVCLEVEL, BITSPERPIXEL, THUMB, CONTAINER, MODEL, EXPOSURE, ORIENTATION, ISO, MUXINGMODE, FRAMERATEMODE) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
ps.setString(1, name);
ps.setTimestamp(2, new Timestamp(modified));
ps.setInt(3, type);
if (media != null) {
if (media.getDuration() != null) {
ps.setDouble(4, media.getDurationInSeconds());
} else {
ps.setNull(4, Types.DOUBLE);
}
int databaseBitrate = 0;
if (type != Format.IMAGE) {
databaseBitrate = media.getBitrate();
if (databaseBitrate == 0) {
LOGGER.debug("Could not parse the bitrate from: " + name);
}
}
ps.setInt(5, databaseBitrate);
ps.setInt(6, media.getWidth());
ps.setInt(7, media.getHeight());
ps.setLong(8, media.getSize());
ps.setString(9, left(media.getCodecV(), SIZE_CODECV));
ps.setString(10, left(media.getFrameRate(), SIZE_FRAMERATE));
ps.setString(11, left(media.getAspect(), SIZE_ASPECT));
ps.setString(12, left(media.getAspect(), SIZE_ASPECTRATIO_CONTAINER));
ps.setString(13, left(media.getAspect(), SIZE_ASPECTRATIO_VIDEOTRACK));
ps.setByte(14, media.getReferenceFrameCount());
ps.setString(15, left(media.getAvcLevel(), SIZE_AVC_LEVEL));
ps.setInt(16, media.getBitsPerPixel());
ps.setBytes(17, media.getThumb());
ps.setString(18, left(media.getContainer(), SIZE_CONTAINER));
if (media.getExtras() != null) {
ps.setString(19, left(media.getExtrasAsString(), SIZE_MODEL));
} else {
ps.setString(19, left(media.getModel(), SIZE_MODEL));
}
ps.setInt(20, media.getExposure());
ps.setInt(21, media.getOrientation());
ps.setInt(22, media.getIso());
ps.setString(23, left(media.getMuxingModeAudio(), SIZE_MUXINGMODE));
ps.setString(24, left(media.getFrameRateMode(), SIZE_FRAMERATE_MODE));
} else {
ps.setString(4, null);
ps.setInt(5, 0);
ps.setInt(6, 0);
ps.setInt(7, 0);
ps.setLong(8, 0);
ps.setString(9, null);
ps.setString(10, null);
ps.setString(11, null);
ps.setString(12, null);
ps.setString(13, null);
ps.setByte(14, (byte) -1);
ps.setString(15, null);
ps.setInt(16, 0);
ps.setBytes(17, null);
ps.setString(18, null);
ps.setString(19, null);
ps.setInt(20, 0);
ps.setInt(21, 0);
ps.setInt(22, 0);
ps.setString(23, null);
ps.setString(24, null);
}
ps.executeUpdate();
ResultSet rs = ps.getGeneratedKeys();
int id = -1;
while (rs.next()) {
id = rs.getInt(1);
}
rs.close();
if (media != null && id > -1) {
PreparedStatement insert = null;
if (media.getAudioTracksList().size() > 0) {
insert =
conn.prepareStatement(
"INSERT INTO AUDIOTRACKS VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
}
for (DLNAMediaAudio audio : media.getAudioTracksList()) {
insert.clearParameters();
insert.setInt(1, id);
insert.setInt(2, audio.getId());
insert.setString(3, left(audio.getLang(), SIZE_LANG));
insert.setString(4, left(audio.getFlavor(), SIZE_FLAVOR));
insert.setInt(5, audio.getAudioProperties().getNumberOfChannels());
insert.setString(6, left(audio.getSampleFrequency(), SIZE_SAMPLEFREQ));
insert.setString(7, left(audio.getCodecA(), SIZE_CODECA));
insert.setInt(8, audio.getBitsperSample());
insert.setString(9, left(trimToEmpty(audio.getAlbum()), SIZE_ALBUM));
insert.setString(10, left(trimToEmpty(audio.getArtist()), SIZE_ARTIST));
insert.setString(11, left(trimToEmpty(audio.getSongname()), SIZE_SONGNAME));
insert.setString(12, left(trimToEmpty(audio.getGenre()), SIZE_GENRE));
insert.setInt(13, audio.getYear());
insert.setInt(14, audio.getTrack());
insert.setInt(15, audio.getAudioProperties().getAudioDelay());
insert.setString(16, left(trimToEmpty(audio.getMuxingModeAudio()), SIZE_MUXINGMODE));
insert.setInt(17, audio.getBitRate());
try {
insert.executeUpdate();
} catch (JdbcSQLException e) {
if (e.getErrorCode() == 23505) {
LOGGER.debug(
"A duplicate key error occurred while trying to store the following file's audio information in the database: "
+ name);
} else {
LOGGER.debug(
"An error occurred while trying to store the following file's audio information in the database: "
+ name);
}
LOGGER.debug("The error given by jdbc was: " + e);
}
}
if (media.getSubtitleTracksList().size() > 0) {
insert = conn.prepareStatement("INSERT INTO SUBTRACKS VALUES (?, ?, ?, ?, ?)");
}
for (DLNAMediaSubtitle sub : media.getSubtitleTracksList()) {
if (sub.getExternalFile() == null) { // no save of external subtitles
insert.clearParameters();
insert.setInt(1, id);
insert.setInt(2, sub.getId());
insert.setString(3, left(sub.getLang(), SIZE_LANG));
insert.setString(4, left(sub.getFlavor(), SIZE_FLAVOR));
insert.setInt(5, sub.getType().getStableIndex());
try {
insert.executeUpdate();
} catch (JdbcSQLException e) {
if (e.getErrorCode() == 23505) {
LOGGER.debug(
"A duplicate key error occurred while trying to store the following file's subtitle information in the database: "
+ name);
} else {
LOGGER.debug(
"An error occurred while trying to store the following file's subtitle information in the database: "
+ name);
}
LOGGER.debug("The error given by jdbc was: " + e);
}
}
}
close(insert);
}
} catch (SQLException se) {
if (se.getErrorCode() == 23001) {
LOGGER.debug(
"Duplicate key while inserting this entry: "
+ name
+ " into the database: "
+ se.getMessage());
} else {
LOGGER.error(null, se);
}
} finally {
close(ps);
close(conn);
}
}
void runPrepareTest() {
setProgressMinMax(0, m_nNumRuns - 1);
int nProgressStep = m_nNumRuns / 10 + 1;
try {
boolean m_bTransactionsUsed = m_bTrans && m_conn.getMetaData().supportsTransactions();
boolean bCurrentAutoCommit = m_conn.getAutoCommit();
m_conn.setAutoCommit(!m_bTransactionsUsed);
int nAccNum, nBranchNum, nTellerNum;
double dDelta, dBalance;
log(
"Starting SQL prepare/execute benchmark for "
+ m_nNumRuns
+ ((m_time > 0) ? " min.\n" : " runs\n"),
0);
java.util.Random rand = new java.util.Random(m_nMaxAccount + m_nMaxBranch + m_nMaxTeller);
for (int nRun = 0; (m_time > 0) ? true : nRun < m_nNumRuns; nRun++) {
if (m_time > 0) {
java.util.Date current = new java.util.Date();
if ((current.getTime() - m_time) > (m_nNumRuns * 60000)) break;
}
PreparedStatement updAccStmt = null,
selAccStmt = null,
updTellerStmt = null,
updBranchStmt = null,
insHistStmt = null;
try {
nAccNum = (int) (rand.nextFloat() * rand.nextFloat() * (m_nMaxAccount - 1)) + 1;
nBranchNum = (int) (rand.nextFloat() * (m_nMaxBranch - 1)) + 1;
nTellerNum = (int) (rand.nextFloat() * (m_nMaxTeller - 1)) + 1;
dDelta =
((double)
((long)
((((int) (rand.nextFloat() * (m_nMaxTeller - 1)) + 1)
* (rand.nextFloat() > 0.5 ? -1 : 1))
* 100)))
/ 100;
// prepare statements
updAccStmt =
m_conn.prepareStatement(
"UPDATE "
+ m_Driver.getAccountName()
+ " SET balance = balance + ? WHERE account = ?");
selAccStmt =
m_conn.prepareStatement(
"SELECT balance FROM " + m_Driver.getAccountName() + " WHERE account = ?");
updTellerStmt =
m_conn.prepareStatement(
"UPDATE "
+ m_Driver.getTellerName()
+ " SET balance = balance + ? WHERE teller = ?");
updBranchStmt =
m_conn.prepareStatement(
"UPDATE "
+ m_Driver.getBranchName()
+ " SET balance = balance + ? WHERE branch = ?");
insHistStmt =
m_conn.prepareStatement(
"INSERT INTO "
+ m_Driver.getHistoryName()
+ " (histid, account, teller, branch, amount, timeoftxn, filler) VALUES (? , ? , ? , ? , ? , "
+ m_nowFunction
+ " , ?)");
// bind parameters
updAccStmt.setDouble(1, dDelta);
updAccStmt.setInt(2, nAccNum);
// System.out.println(nAccNum);
selAccStmt.setInt(1, nAccNum);
updTellerStmt.setDouble(1, dDelta);
updTellerStmt.setInt(2, nTellerNum);
updBranchStmt.setDouble(1, dDelta);
updBranchStmt.setInt(2, nBranchNum);
insHistStmt.setInt(1, nRun);
insHistStmt.setInt(2, nAccNum);
insHistStmt.setInt(3, nTellerNum);
insHistStmt.setInt(4, nBranchNum);
insHistStmt.setDouble(5, dDelta);
insHistStmt.setString(6, BenchPanel.strFiller.substring(0, 21));
java.util.Date startTime = new java.util.Date();
// execute statements
log(
"UPDATE "
+ m_Driver.getAccountName()
+ " SET balance = balance + "
+ dDelta
+ " WHERE account = "
+ nAccNum
+ "\n",
2);
updAccStmt.executeUpdate();
log(
"SELECT balance FROM "
+ m_Driver.getAccountName()
+ " WHERE account = "
+ nAccNum
+ "\n",
2);
ResultSet balanceSet = selAccStmt.executeQuery();
balanceSet.next();
dBalance = balanceSet.getFloat(1);
// if (balanceSet == null)
// System.out.println("balanceSet is NULL");
// else {
// String strBalance = balanceSet.getString(1);
// System.out.println(balanceSet.wasNull() ? "SQL NULL" : strBalance);
// }
balanceSet.close();
log(
"UPDATE "
+ m_Driver.getTellerName()
+ " SET balance = balance + "
+ dDelta
+ " WHERE teller = "
+ nTellerNum
+ "\n",
2);
updTellerStmt.executeUpdate();
log(
"UPDATE "
+ m_Driver.getBranchName()
+ " SET balance = balance + "
+ dDelta
+ " WHERE branch = "
+ nBranchNum
+ "\n",
2);
updBranchStmt.executeUpdate();
log(
"INSERT INTO "
+ m_Driver.getHistoryName()
+ " (histid, account, teller, branch, amount, timeoftxn, filler) VALUES ("
+ nRun
+ " , "
+ nAccNum
+ " , "
+ nTellerNum
+ " , "
+ nBranchNum
+ " , "
+ dDelta
+ " , "
+ m_nowFunction
+ " , \'"
+ BenchPanel.strFiller.substring(0, 21)
+ "\')\n",
2);
insHistStmt.executeUpdate();
if (m_bQuery) executeQuery();
if (m_bTransactionsUsed) m_conn.commit();
java.util.Date endTime = new java.util.Date();
m_nTrans += 1;
double diff = endTime.getTime() - startTime.getTime();
if (diff < 1000) m_nTrans1Sec += 1;
else if (diff < 2000) m_nTrans2Sec += 1;
m_nTimeSum += diff / 1000;
} catch (SQLException e1) {
// System.err.println(e1.getMessage());
break;
} finally {
try {
if (updAccStmt != null) updAccStmt.close();
if (selAccStmt != null) selAccStmt.close();
if (updTellerStmt != null) updTellerStmt.close();
if (updBranchStmt != null) updBranchStmt.close();
if (insHistStmt != null) insHistStmt.close();
} catch (SQLException e) {
}
}
if (nRun % nProgressStep == 0) setProgressValue(nRun);
// yield();
}
setProgressValue(m_nNumRuns - 1);
m_conn.setAutoCommit(bCurrentAutoCommit);
} catch (SQLException e) {
// JOptionPane.showMessageDialog(null, e.getMessage(), "SQL Error in Text test",
// JOptionPane.ERROR_MESSAGE);
log("SQLError in prepare test : " + e.getMessage(), 0);
}
}