/**
* Apply the specified timeout - overridden by the current transaction timeout, if any - to the
* given JDBC Statement object.
*
* @param stmt the JDBC Statement object
* @param dataSource the DataSource that the Connection was obtained from
* @param timeout the timeout to apply (or 0 for no timeout outside of a transaction)
* @throws SQLException if thrown by JDBC methods
* @see java.sql.Statement#setQueryTimeout
*/
public static void applyTimeout(Statement stmt, DataSource dataSource, int timeout)
throws SQLException {
Assert.notNull(stmt, "No Statement specified");
Assert.notNull(dataSource, "No DataSource specified");
ConnectionHolder holder =
(ConnectionHolder) TransactionSynchronizationManager.getResource(dataSource);
if (holder != null && holder.hasTimeout()) {
// Remaining transaction timeout overrides specified value.
stmt.setQueryTimeout(holder.getTimeToLiveInSeconds());
} else if (timeout > 0) {
// No current transaction timeout -> apply specified value.
stmt.setQueryTimeout(timeout);
}
}
public void setStatement() throws Exception {
if (conn == null) {
setConnection();
}
statement = conn.createStatement();
statement.setQueryTimeout(iTimeout); // set timeout to 30 sec.
}
private ResultSet loadResultSet(boolean useCurrentFilter) throws SQLException {
Connection connection = connectionHandler.getStandaloneConnection();
DBDataset dataset = getDataset();
if (dataset != null) {
Project project = dataset.getProject();
DatasetFilter filter = DatasetFilterManager.EMPTY_FILTER;
if (useCurrentFilter) {
DatasetFilterManager filterManager = DatasetFilterManager.getInstance(project);
filter = filterManager.getActiveFilter(dataset);
if (filter == null) filter = DatasetFilterManager.EMPTY_FILTER;
}
String selectStatement = filter.createSelectStatement(dataset, getState().getSortingState());
Statement statement =
isReadonly()
? connection.createStatement()
: connection.createStatement(
ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
checkDisposed();
int timeout = settings.getGeneralSettings().getFetchTimeout().value();
if (timeout != -1) {
statement.setQueryTimeout(timeout);
}
return statement.executeQuery(selectStatement);
}
return null;
}
public boolean isValidConnection(DruidDataSource dataSource, Connection conn) {
Statement stmt = null;
ResultSet rs = null;
try {
stmt = conn.createStatement();
stmt.setQueryTimeout(queryTimeoutSeconds);
rs = stmt.executeQuery(getValidateSql());
if (!rs.next()) {
return false;
}
String status = rs.getString(1);
if ("on".equalsIgnoreCase(status)) {
return true;
} else {
return false;
}
} catch (Exception ex) {
LOG.error("check datasource valid errror", ex);
return false;
} finally {
JdbcUtils.close(rs);
JdbcUtils.close(stmt);
}
}
@SuppressWarnings({"unchecked"})
public static void close(Statement statement) {
log.tracef("Closing prepared statement [%s]", statement);
try {
// if we are unable to "clean" the prepared statement,
// we do not close it
try {
if (statement.getMaxRows() != 0) {
statement.setMaxRows(0);
}
if (statement.getQueryTimeout() != 0) {
statement.setQueryTimeout(0);
}
} catch (SQLException sqle) {
// there was a problem "cleaning" the prepared statement
if (log.isDebugEnabled()) {
log.debugf("Exception clearing maxRows/queryTimeout [%s]", sqle.getMessage());
}
// EARLY EXIT!!!
return;
}
statement.close();
} catch (SQLException e) {
log.debugf("Unable to release JDBC statement [%s]", e.getMessage());
} catch (Exception e) {
// try to handle general errors more elegantly
log.debugf("Unable to release JDBC statement [%s]", e.getMessage());
}
}
/** Creates tables if they do not exist. */
private void initializeTables() {
Connection conn = null;
Statement statement = null;
try {
conn = retrieveConnection();
statement = conn.createStatement();
statement.setQueryTimeout(30);
// One table holding the playername id relation
String playerQuery =
String.format(
"CREATE TABLE IF NOT EXISTS %s (id INTEGER PRIMARY KEY AUTOINCREMENT, %s STRING)",
playerTable, "name");
statement.executeUpdate(playerQuery);
// One column for every message
StringBuilder columns = new StringBuilder();
for (MessageNode node : MessageNode.getMessageNodes()) {
MsgCategory cat = messages.getCat(node);
if (node.getColumnName() != null
&& (cat == MsgCategory.TUTORIAL || cat == MsgCategory.ONE_TIME)) {
columns.append(',');
columns.append(node.getColumnName());
}
}
String msgQuery =
String.format(
"CREATE TABLE IF NOT EXISTS %s (id INTEGER PRIMARY KEY UNIQUE %s)",
msgTable, columns);
statement.executeUpdate(msgQuery);
// Check if all columns are present
DatabaseMetaData dmd = conn.getMetaData();
// Add missing columns
for (MessageNode node : MessageNode.getMessageNodes()) {
MsgCategory cat = messages.getCat(node);
if (cat == MsgCategory.TUTORIAL || cat == MsgCategory.ONE_TIME) {
ResultSet set = dmd.getColumns(null, null, msgTable, node.getColumnName());
if (!set.next()) {
String updateQuery =
String.format("ALTER TABLE %s ADD COLUMN %s", msgTable, node.getColumnName());
statement.executeUpdate(updateQuery);
}
}
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
if (conn != null) conn.close();
if (statement != null) statement.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
public void connect() {
Statement statement = null;
databaseType = foxbot.getConfig().getDatabaseType();
url =
databaseType.equalsIgnoreCase("mysql")
? String.format(
"jdbc:mysql://%s:%s/%s",
foxbot.getConfig().getDatabaseHost(),
foxbot.getConfig().getDatabasePort(),
foxbot.getConfig().getDatabaseName())
: "jdbc:sqlite:data/bot.db";
try {
if (databaseType.equalsIgnoreCase("sqlite")) {
Class.forName("org.sqlite.JDBC");
}
if (databaseType.equalsIgnoreCase("mysql")) {
String user = foxbot.getConfig().getDatabaseUser();
String password = foxbot.getConfig().getDatabasePassword();
connection = DriverManager.getConnection(url, user, password);
} else {
connection = DriverManager.getConnection(url);
}
statement = connection.createStatement();
statement.setQueryTimeout(30);
statement.executeUpdate(
"CREATE TABLE IF NOT EXISTS tells (tell_time VARCHAR(32), sender VARCHAR(32), receiver VARCHAR(32), message VARCHAR(1024), used TINYINT)");
statement.executeUpdate(
"CREATE TABLE IF NOT EXISTS bans (channel VARCHAR(64), target VARCHAR(32), hostmask VARCHAR(64), reason VARCHAR(1024), banner VARCHAR(32), ban_time BIGINT)");
statement.executeUpdate(
"CREATE TABLE IF NOT EXISTS kicks (channel VARCHAR(64), target VARCHAR(32), hostmask VARCHAR(64), reason VARCHAR(1024), kicker VARCHAR(32), kick_time BIGINT)");
statement.executeUpdate(
"CREATE TABLE IF NOT EXISTS mutes (channel VARCHAR(64), target VARCHAR(32), hostmask VARCHAR(64), reason VARCHAR(1024), muter VARCHAR(32), mute_time BIGINT)");
} catch (SQLException | ClassNotFoundException ex) {
Logger.getLogger(this.getClass().getName()).log(Level.SEVERE, null, ex);
foxbot.disconnect();
} finally {
try {
if (statement != null) {
statement.close();
}
if (connection != null) {
connection.close();
connection = null;
}
} catch (SQLException ex) {
Logger.getLogger(this.getClass().getName()).log(Level.SEVERE, null, ex);
}
}
}
private void testJdbcQueryTimeout() throws SQLException {
deleteDb("cancel");
Connection conn = getConnection("cancel");
Statement stat = conn.createStatement();
assertEquals(0, stat.getQueryTimeout());
stat.setQueryTimeout(1);
assertEquals(1, stat.getQueryTimeout());
Statement s2 = conn.createStatement();
assertEquals(1, s2.getQueryTimeout());
ResultSet rs =
s2.executeQuery(
"SELECT VALUE " + "FROM INFORMATION_SCHEMA.SETTINGS WHERE NAME = 'QUERY_TIMEOUT'");
rs.next();
assertEquals(1000, rs.getInt(1));
assertThrows(ErrorCode.STATEMENT_WAS_CANCELED, stat)
.executeQuery("SELECT MAX(RAND()) " + "FROM SYSTEM_RANGE(1, 100000000)");
stat.setQueryTimeout(0);
stat.execute("SET QUERY_TIMEOUT 1100");
// explicit changes are not detected except, as documented
assertEquals(0, stat.getQueryTimeout());
conn.close();
}
private void testQueryTimeoutInTransaction() throws SQLException {
deleteDb("cancel");
Connection conn = getConnection("cancel");
Statement stat = conn.createStatement();
stat.execute("CREATE TABLE TEST(ID INT)");
conn.setAutoCommit(false);
stat.execute("INSERT INTO TEST VALUES(1)");
Savepoint sp = conn.setSavepoint();
stat.execute("INSERT INTO TEST VALUES(2)");
stat.setQueryTimeout(1);
conn.rollback(sp);
conn.commit();
conn.close();
}
@Override
public void dump() {
try {
initConnection();
Statement statement = null;
statement = conn.createStatement();
// set timeout to 30 sec
statement.setQueryTimeout(30);
statement.execute("DELETE from Persoon");
statement.execute("DELETE FROM Gezin");
statement.execute("DELETE FROM sqlite_sequence");
statement.close();
} catch (SQLException e) {
e.printStackTrace();
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
}
private void initConnection() throws SQLException, ClassNotFoundException {
// TODO opgave 4
Class.forName(props.getProperty("driver"));
try {
// create a database connection
conn = DriverManager.getConnection(props.getProperty("url"));
Statement statement = conn.createStatement();
statement.setQueryTimeout(30); // set timeout to 30 sec.
statement.executeUpdate(
"CREATE TABLE IF NOT EXISTS `Persoon`(\n"
+ "\t`persoonsNummer`\tINTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,\n"
+ "\t`voornamen`\tTEXT NOT NULL,\n"
+ "\t`achternaam`\tTEXT NOT NULL,\n"
+ "\t`tussenvoegsel`\tTEXT NOT NULL,\n"
+ "\t`geboortedatum`\tTEXT NOT NULL,\n"
+ "\t`geboorteplaats`\tTEXT NOT NULL,\n"
+ "\t`geslacht`\tTEXT NOT NULL,\n"
+ "\t`ouders`\tTEXT,\n"
+ "\t FOREIGN KEY(ouders)\tREFERENCES gezin(gezinsnummer)\n"
+ ");");
statement.executeUpdate(
"CREATE TABLE IF NOT EXISTS `Gezin` (\n"
+ "\t`gezinsNummer`\tINTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,\n"
+ "\t`ouder1`\tINTEGER NOT NULL,\n"
+ "\t`ouder2`\tINTEGER ,\n"
+ "\t`huwelijksdatum`\t TEXT,\n"
+ "\t`scheidingsdatum`\tTEXT,\n"
+ "\t FOREIGN KEY(ouder1)\tREFERENCES persoon(persoonsnummer)\n,"
+ "\t FOREIGN KEY(ouder2)\tREFERENCES persoon(persoonsnummer)\n"
+ ");");
} catch (SQLException e) {
// if the error message is "out of memory",
// it probably means no database file is found
System.err.println(e.getMessage());
}
}
/** ajoute 1 au nombre de fois que la musique a �t� lu */
public static synchronized void addLecture(Musique musique) {
Connection connection = initConnection();
try {
Statement statement = connection.createStatement();
statement.setQueryTimeout(30);
statement.executeUpdate(
"Update songs set nblecture = nblecture + 1 where title like '"
+ musique.getTitle()
+ "' and album like '"
+ musique.getAlbum()
+ "' and artist like '"
+ musique.getArtist()
+ "'");
musique.getNbLecture().setNbLecture();
} catch (SQLException e) {
System.err.println(e.getMessage());
} finally {
try {
if (connection != null) connection.close();
} catch (SQLException e) {
System.err.println(e);
}
}
}
public void setQueryTimeout(int seconds) throws SQLException {
pst.setQueryTimeout(seconds);
};
public List<UserHistory> getFirefoxHistory() {
// TODO Auto-generated method stub
try {
Class.forName("org.sqlite.JDBC");
} catch (ClassNotFoundException e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
}
List<UserHistory> userHistoryList = new ArrayList<UserHistory>();
Connection connection = null;
try {
String profileName = HackathonUtil.getProfileName(Tracking.FILES_LOCATION);
System.out.println("Profile name:-" + profileName);
String accNum = profileName.replace(".default", "");
System.out.println("Account number:-" + accNum);
String connectionString =
"jdbc:sqlite:" + Tracking.FILES_LOCATION + profileName + "/places.sqlite";
connection = DriverManager.getConnection(connectionString);
Statement statement = connection.createStatement();
statement.setQueryTimeout(30); // set timeout to 30 sec.
ResultSet rs =
statement.executeQuery(
"select url as HOST_NAME, rev_host as FULL_URL, visit_date as VISIT_DATE from moz_places , moz_historyvisits where moz_places.id = moz_historyvisits.id and moz_places.url like '%verizon%'");
while (rs.next()) {
// read the result set
String actualText = "";
UserHistory userHistory = new UserHistory();
String dateandtime = rs.getString("VISIT_DATE");
long dt = Long.parseLong(dateandtime);
//
Calendar cal = Calendar.getInstance();
cal.setTimeInMillis(dt);
SimpleDateFormat format1 = new SimpleDateFormat("MMM-dd");
String formatted = format1.format(cal.getTime());
userHistory.setDateVisitied(formatted);
String txt = rs.getString("HOST_NAME");
System.out.println(txt);
String contents[] = txt.split("=");
if (contents.length > 2) {
actualText = contents[2].replace("+", " ");
} else {
actualText = txt;
}
userHistory.setId(accNum);
userHistory.setHostname(actualText);
userHistory.setTextSearched(HackathonUtil.reverseString(rs.getString("FULL_URL")));
userHistoryList.add(userHistory);
}
} catch (SQLException e) {
// if the error message is "out of memory",
// it probably means no database file is found
System.err.println(e.getMessage());
} catch (Exception ex) {
ex.printStackTrace();
} finally {
try {
if (connection != null) connection.close();
} catch (SQLException e) {
// connection close failed.
System.err.println(e);
}
}
return userHistoryList;
}
@Override
public void setQueryTimeout(int seconds) throws SQLException {
stat.setQueryTimeout(seconds);
}
protected ResultSet query(String query) throws SQLException {
Statement statement = m_connection.createStatement();
statement.setQueryTimeout(30); // set timeout to 30 sec
return statement.executeQuery(query);
}
protected int update(String update) throws SQLException {
Statement statementUpdate = m_connection.createStatement();
statementUpdate.setQueryTimeout(30);
return statementUpdate.executeUpdate(update);
}
/* (non-Javadoc)
* @see com.newgen.omni.jts.txn.NGOServerInterface#execute(java.sql.Connection, com.newgen.omni.jts.cmgr.XMLParser, com.newgen.omni.jts.cmgr.XMLGenerator)
* Function Name : execute
* Date Written : 03/04/2008
* Author : yogvinder
* Input Parameters : Connection, XMLPArser, XMLGenerator
* Output Parameters : None
* Return Values :
* Description :
*/
public String execute(Connection con, XMLParser xmlToParse, XMLGenerator fileProcessor)
throws JTSException {
StringBuffer outputXML = null;
String errorMsg = null;
String strColumnValue = null;
String Flag = null;
int errorCode = 0;
ResultSet rs = null;
Statement stmt = null;
int dbStatus = 0;
try {
con.setAutoCommit(false);
stmt = con.createStatement();
outputXML = new StringBuffer(100);
outputXML.append(fileProcessor.createOutputFile("NGOGetNextSynchData"));
stmt.setQueryTimeout(
ServerProperty.getReference().getQueryTimeout(xmlToParse.getValueOf("CabinetName")));
rs =
stmt.executeQuery(
"select uid, destination, commanddata, command, transactionid,actiondatetime,requestdatetime,comment,requestcounter,requestType,source,caption,sourceIP,statusFlag from pdbsynchtable where statusflag in ('01', '03', '08', '11', '12', '13', '14', '16', '17', '18', '20' , '21', '23', '24', '25', '26', '27', '28', '29', '30', '34', '36', '38', '39', '40') and requestcounter < 05 order by actiondatetime, requestcounter,requestdatetime asc Limit 1");
if (rs != null && rs.next()) {
strColumnValue = rs.getString(1).trim();
outputXML.append(fileProcessor.writeValueOf("UID", strColumnValue));
outputXML.append(fileProcessor.writeValueOf("Destination", rs.getString(2)));
outputXML.append(fileProcessor.writeValueOf("CommandData", rs.getString(3)));
outputXML.append(fileProcessor.writeValueOf("Command", rs.getString(4)));
outputXML.append(fileProcessor.writeValueOf("TransactionId", rs.getString(5)));
outputXML.append(fileProcessor.writeValueOf("ActionDateTime", rs.getString(6)));
outputXML.append(fileProcessor.writeValueOf("RequestDateTime", rs.getString(7)));
outputXML.append(fileProcessor.writeValueOf("Comment", rs.getString(8)));
String strRequestCounter = rs.getString(9);
outputXML.append(fileProcessor.writeValueOf("RequestCounter", strRequestCounter));
outputXML.append(fileProcessor.writeValueOf("RequestType", rs.getString(10)));
outputXML.append(fileProcessor.writeValueOf("Source", rs.getString(11)));
outputXML.append(fileProcessor.writeValueOf("Caption", rs.getString(12)));
outputXML.append(fileProcessor.writeValueOf("SourceIP", rs.getString(13)));
String strStatusFlag = rs.getString(14);
outputXML.append(fileProcessor.writeValueOf("StatusFlag", strStatusFlag));
int intRequestCounter = Integer.parseInt(strRequestCounter);
int x = 0;
// stmt.setQueryTimeout (ServerProperty.getReference ().getQueryTimeout
// (xmlToParse.getValueOf ("CabinetName")));
if (strStatusFlag.equalsIgnoreCase("01")) {
if (intRequestCounter == 0)
x =
stmt.executeUpdate(
"Update pdbsynchtable set statusFlag = '02' , requestcounter = requestcounter + 1, actiondatetime = now(), Comment = 'Fetched for Processing' where UID = '"
+ strColumnValue
+ "'");
else
x =
stmt.executeUpdate(
"Update pdbsynchtable set statusFlag = '02' , requestcounter = requestcounter + 1, actiondatetime = now() where UID = '"
+ strColumnValue
+ "'");
} else {
if (intRequestCounter == 0)
x =
stmt.executeUpdate(
"Update pdbsynchtable set requestcounter = requestcounter + 1, actiondatetime = now() where UID = '"
+ strColumnValue
+ "'");
else
x =
stmt.executeUpdate(
"Update pdbsynchtable set requestcounter = requestcounter + 1, actiondatetime = now() where UID = '"
+ strColumnValue
+ "'");
}
}
strColumnValue = null;
errorMsg = null;
con.commit();
rs.close();
outputXML.append(fileProcessor.writeValueOf("Status", String.valueOf(dbStatus)));
// Error Handling to be completed.
} catch (SQLException e) {
e.printStackTrace();
errorCode = JTSError.JTSE_SQL_ERR;
if (e.getErrorCode() == 0)
errorMsg =
(new JTSSQLError(e.getSQLState())).getMessage()
+ "(SQL State : "
+ e.getSQLState()
+ ")";
else errorMsg = e.getMessage();
} catch (NumberFormatException e) {
e.printStackTrace();
errorCode = JTSError.JTSE_ILLEGAL_PARAM;
} catch (NullPointerException e) {
e.printStackTrace();
errorCode = JTSError.JTSE_SYS_ERR;
} catch (Exception e) {
e.printStackTrace();
errorCode = JTSError.JTSE_UNKNOWN_ERR;
errorMsg = e.toString();
} catch (Error e) {
e.printStackTrace();
errorCode = JTSError.JTSE_UNKNOWN_ERR;
errorMsg = e.toString();
} finally {
try {
con.rollback();
if (stmt != null) stmt.close();
if (rs != null) rs.close();
} catch (Exception e) {
}
if (errorCode != 0) {
throw new JTSException(errorCode, errorMsg);
}
}
outputXML.append(fileProcessor.closeOutputFile("NGOGetNextSynchData"));
return outputXML.toString();
}
public boolean isValidConnection(
Connection conn, String validateQuery, int validationQueryTimeout) {
try {
if (conn.isClosed()) {
return false;
}
} catch (SQLException ex) {
// skip
return false;
}
if (usePingMethod) {
if (conn instanceof DruidPooledConnection) {
conn = ((DruidPooledConnection) conn).getConnection();
}
if (conn instanceof ConnectionProxy) {
conn = ((ConnectionProxy) conn).getRawObject();
}
if (clazz.isAssignableFrom(conn.getClass())) {
if (validationQueryTimeout < 0) {
validationQueryTimeout = DEFAULT_VALIDATION_QUERY_TIMEOUT;
}
try {
ping.invoke(conn, true, validationQueryTimeout);
return true;
} catch (InvocationTargetException e) {
Throwable cause = e.getCause();
if (cause instanceof SQLException) {
return false;
}
LOG.warn("Unexpected error in ping", e);
return false;
} catch (Exception e) {
LOG.warn("Unexpected error in ping", e);
return false;
}
}
}
Statement stmt = null;
ResultSet rs = null;
try {
stmt = conn.createStatement();
if (validationQueryTimeout > 0) {
stmt.setQueryTimeout(validationQueryTimeout);
}
rs = stmt.executeQuery(validateQuery);
return true;
} catch (SQLException e) {
return false;
} catch (Exception e) {
LOG.warn("Unexpected error in ping", e);
return false;
} finally {
JdbcUtils.close(rs);
JdbcUtils.close(stmt);
}
}
private Statement createStatement() throws SQLException {
Statement statement = connection.createStatement();
statement.setQueryTimeout(30); // set timeout to 30 sec.
return statement;
}
public static void main(String[] args)
throws ClassNotFoundException, FileNotFoundException, IOException {
PrintStream out =
new PrintStream(new FileOutputStream("src/adbproject/output_Spatialite_D1_SA.txt"));
System.setOut(out);
long startTime = 0;
long stopTime = 0;
// load the sqlite-JDBC driver using the current class loader
Class.forName("org.sqlite.JDBC");
Connection conn = null;
try {
// enabling dynamic extension loading
// absolutely required by SpatiaLite
SQLiteConfig config = new SQLiteConfig();
config.enableLoadExtension(true);
// create a database connection
conn =
DriverManager.getConnection(
"jdbc:sqlite:C:/Users/Madhumitha/Documents/db.sqlite", config.toProperties());
conn.setAutoCommit(false);
Statement stmt = conn.createStatement();
stmt.setQueryTimeout(30); // set timeout to 30 sec.
double total, used;
// loading SpatiaLite
stmt.execute("SELECT load_extension('mod_spatialite')");
FileReader reader = new FileReader("src/adbproject/S_Analysis-Spatialite_D1");
BufferedReader bufferedReader = new BufferedReader(reader);
int i = 0;
String line;
stmt = conn.createStatement();
ResultSet rs = null;
while ((line = bufferedReader.readLine()) != null) {
i++;
long sum = 0;
// for(int cnt=1;cnt<=5;cnt++)
// {
startTime = System.currentTimeMillis();
total =
((double) ((double) (Runtime.getRuntime().totalMemory() / 1024) / 1024))
- ((double) ((double) (Runtime.getRuntime().freeMemory() / 1024) / 1024));
rs = stmt.executeQuery(line);
used =
((double) ((double) (Runtime.getRuntime().totalMemory() / 1024) / 1024))
- ((double) ((double) (Runtime.getRuntime().freeMemory() / 1024) / 1024));
stopTime = System.currentTimeMillis();
long elapsedTime = stopTime - startTime;
System.out.println("Time taken for query " + i + ": " + elapsedTime + " ms");
// sum=sum+elapsedTime;
// }
// long avgTime= sum/5;
// System.out.println("Time taken for query " +i+ ": " +avgTime+" ms");
System.out.println();
rs.close();
}
stmt.close();
conn.close();
reader.close();
} catch (SQLException e) {
// if the error message is "out of memory",
// it probably means no database file is found
System.err.println(e.getMessage());
} finally {
try {
if (conn != null) {
conn.close();
}
} catch (SQLException e) {
// connection close failed.
System.err.println(e);
}
}
}
private void applyQueryTimeOut(Statement stmt) throws SQLException {
if (this.queryTimeout != 0) {
stmt.setQueryTimeout(queryTimeout);
}
}
/**
* @param recherche le mot a recherche dans la base de donnees
* @param artistCheck si true, recherche dans artist
* @param titleCheck si true, recherche dans title
* @return la base de donnee dans un tableau d'objet
*/
public static synchronized Object[][] recherche(
String recherche, boolean artistCheck, boolean titleCheck) {
Connection connection = initConnection();
try {
Statement statement = connection.createStatement();
statement.setQueryTimeout(30);
ResultSet rs;
ArrayList<Object[]> l = new ArrayList<Object[]>();
if (artistCheck && titleCheck)
rs =
statement.executeQuery(
"select * from songs where artist like '%"
+ recherche
+ "%' or title like '%"
+ recherche
+ "%'");
else if (artistCheck)
rs = statement.executeQuery("select * from songs where artist like '%" + recherche + "%'");
else if (titleCheck)
rs = statement.executeQuery("select * from songs where title like '%" + recherche + "%'");
else {
if (recherche.length() == 0) rs = statement.executeQuery("select * from songs");
else {
String requete = "select * from songs where ";
for (int i = 0; i < columnNames.length - 1; i++)
requete += columnNames[i] + " like '%" + recherche + "%' or ";
requete += "duration like '%" + recherche + "%'";
rs = statement.executeQuery(requete);
}
}
while (rs.next()) {
ArrayList<StructureMusique> tmp = new ArrayList<StructureMusique>();
Musique mus =
new Musique(
rs.getString("title"),
rs.getString("album"),
rs.getString("artist"),
rs.getString("genre"),
rs.getString("year"),
rs.getString("duration"),
Integer.parseInt(rs.getString("nblecture")));
tmp.add(mus.getTitle());
tmp.add(mus.getAlbum());
tmp.add(mus.getArtist());
tmp.add(mus.getGenre());
// tmp.add(mus.getYear());
// tmp.add(mus.getDuration());
l.add(tmp.toArray());
}
Object retour[][] = new Object[l.size()][];
for (int i = 0; i < l.size(); ++i) retour[i] = l.get(i);
statement.close();
connection.close();
return retour;
} catch (SQLException e) {
System.err.println(e.getMessage());
} finally {
try {
if (connection != null) connection.close();
} catch (SQLException e) {
System.err.println(e);
}
}
return null;
}
private static void setStatementTimeout(MappedStatement mappedStatement, Statement statement)
throws SQLException {
if (mappedStatement.getTimeout() != null) {
statement.setQueryTimeout(mappedStatement.getTimeout().intValue());
}
}
public static void main(String[] args) throws ClassNotFoundException {
// load the sqlite-JDBC driver using the current class loader
Class.forName("org.sqlite.JDBC");
Connection connection = null;
try {
// create a database connection
connection = DriverManager.getConnection("jdbc:sqlite:tables.db");
Statement statement = connection.createStatement();
statement.setQueryTimeout(30); // set timeout to 30 sec.
// create Station Table (The data have to be imported)
statement.executeUpdate("drop table if exists Station");
statement.executeUpdate(
"create table Station (Station_ID integer PRIMARY KEY, Station_Name string, IsTerminal boolean)");
// Initialize Station
statement.executeUpdate("insert into Station values(1, 'Gakken-Nara-Tomigaoka', 1)");
statement.executeUpdate("insert into Station values(2, 'Gakken-Kita-Ikoma', 0)");
statement.executeUpdate("insert into Station values(3, 'Shiraniwadai', 0)");
statement.executeUpdate("insert into Station values(4, 'Ikoma', 0)");
statement.executeUpdate("insert into Station values(5, 'Shin-ishikiri', 0)");
statement.executeUpdate("insert into Station values(6, 'Nagata', 0)");
statement.executeUpdate("insert into Station values(7, 'Takaida', 0)");
statement.executeUpdate("insert into Station values(8, 'Morinomiya', 0)");
statement.executeUpdate("insert into Station values(9, 'Hanimachi-4chome', 0)");
statement.executeUpdate("insert into Station values(10, 'Mahidoldaigakumae', 1)");
statement.executeUpdate("insert into Station values(11, 'Narasentandai', 0)");
statement.executeUpdate("insert into Station values(12, 'Gakuemmae', 0)");
statement.executeUpdate("insert into Station values(13, 'IPlab', 1)");
// create TimeTable table (The data have to be imported)
statement.executeUpdate("drop table if exists TimeTable");
statement.executeUpdate("create table TimeTable (Bus_Line, Bus_ID, Station_ID, Time time)");
// 138 Line
// First bus
statement.executeUpdate("insert into TimeTable values(138, 1, 1, '09:00:00')");
statement.executeUpdate("insert into TimeTable values(138, 1, 2, '09:10:00')");
statement.executeUpdate("insert into TimeTable values(138, 1, 3, '09:20:00')");
statement.executeUpdate("insert into TimeTable values(138, 1, 4, '09:30:00')");
statement.executeUpdate("insert into TimeTable values(138, 1, 5, '09:40:00')");
statement.executeUpdate("insert into TimeTable values(138, 1, 6, '09:50:00')");
statement.executeUpdate("insert into TimeTable values(138, 1, 8, '10:10:00')");
statement.executeUpdate("insert into TimeTable values(138, 1, 10, '10:30:00')");
// Second bus
statement.executeUpdate("insert into TimeTable values(138, 2, 1, '09:50:00')");
statement.executeUpdate("insert into TimeTable values(138, 2, 2, '10:00:00')");
statement.executeUpdate("insert into TimeTable values(138, 2, 3, '10:10:00')");
statement.executeUpdate("insert into TimeTable values(138, 2, 4, '10:20:00')");
statement.executeUpdate("insert into TimeTable values(138, 2, 5, '10:30:00')");
statement.executeUpdate("insert into TimeTable values(138, 2, 6, '10:40:00')");
statement.executeUpdate("insert into TimeTable values(138, 2, 8, '11:00:00')");
statement.executeUpdate("insert into TimeTable values(138, 2, 10, '11:20:00')");
// Third bus
statement.executeUpdate("insert into TimeTable values(138, 3, 1, '13:00:00')");
statement.executeUpdate("insert into TimeTable values(138, 3, 2, '13:10:00')");
statement.executeUpdate("insert into TimeTable values(138, 3, 3, '13:20:00')");
statement.executeUpdate("insert into TimeTable values(138, 3, 4, '13:30:00')");
statement.executeUpdate("insert into TimeTable values(138, 3, 5, '13:40:00')");
statement.executeUpdate("insert into TimeTable values(138, 3, 6, '13:50:00')");
statement.executeUpdate("insert into TimeTable values(138, 3, 8, '14:10:00')");
statement.executeUpdate("insert into TimeTable values(138, 3, 10, '14:30:00')");
// 82 line
// First bus
statement.executeUpdate("insert into TimeTable values(82, 1, 1, '09:00:00')");
statement.executeUpdate("insert into TimeTable values(82, 1, 2, '09:10:00')");
statement.executeUpdate("insert into TimeTable values(82, 1, 3, '09:20:00')");
statement.executeUpdate("insert into TimeTable values(82, 1, 4, '09:30:00')");
statement.executeUpdate("insert into TimeTable values(82, 1, 5, '09:40:00')");
statement.executeUpdate("insert into TimeTable values(82, 1, 6, '09:50:00')");
statement.executeUpdate("insert into TimeTable values(82, 1, 11, '10:10:00')");
statement.executeUpdate("insert into TimeTable values(82, 1, 12, '10:30:00')");
statement.executeUpdate("insert into TimeTable values(82, 1, 13, '10:50:00')");
// Second bus
statement.executeUpdate("insert into TimeTable values(82, 2, 1, '11:00:00')");
statement.executeUpdate("insert into TimeTable values(82, 2, 2, '11:10:00')");
statement.executeUpdate("insert into TimeTable values(82, 2, 3, '11:20:00')");
statement.executeUpdate("insert into TimeTable values(82, 2, 4, '11:30:00')");
statement.executeUpdate("insert into TimeTable values(82, 2, 5, '11:40:00')");
statement.executeUpdate("insert into TimeTable values(82, 2, 6, '11:50:00')");
statement.executeUpdate("insert into TimeTable values(82, 2, 11, '12:10:00')");
statement.executeUpdate("insert into TimeTable values(82, 2, 12, '12:30:00')");
statement.executeUpdate("insert into TimeTable values(82, 2, 13, '12:50:00')");
// 8 line
// First bus
statement.executeUpdate("insert into TimeTable values(8, 1, 1, '09:20:00')");
statement.executeUpdate("insert into TimeTable values(8, 1, 2, '09:50:00')");
statement.executeUpdate("insert into TimeTable values(8, 1, 5, '10:30:00')");
statement.executeUpdate("insert into TimeTable values(8, 1, 10, '10:50:00')");
// Second bus
statement.executeUpdate("insert into TimeTable values(8, 2, 1, '10:00:00')");
statement.executeUpdate("insert into TimeTable values(8, 2, 2, '10:30:00')");
statement.executeUpdate("insert into TimeTable values(8, 2, 5, '11:10:00')");
statement.executeUpdate("insert into TimeTable values(8, 2, 10, '11:30:00')");
// Third bus
statement.executeUpdate("insert into TimeTable values(8, 3, 1, '13:00:00')");
statement.executeUpdate("insert into TimeTable values(8, 3, 2, '13:30:00')");
statement.executeUpdate("insert into TimeTable values(8, 3, 5, '14:10:00')");
statement.executeUpdate("insert into TimeTable values(8, 3, 10, '14:30:00')");
// Fourth bus
statement.executeUpdate("insert into TimeTable values(8, 4, 1, '13:40:00')");
statement.executeUpdate("insert into TimeTable values(8, 4, 2, '14:10:00')");
statement.executeUpdate("insert into TimeTable values(8, 4, 5, '14:50:00')");
statement.executeUpdate("insert into TimeTable values(8, 4, 10, '15:10:00')");
// Fifith bus
statement.executeUpdate("insert into TimeTable values(8, 5, 1, '16:40:00')");
statement.executeUpdate("insert into TimeTable values(8, 5, 2, '17:10:00')");
statement.executeUpdate("insert into TimeTable values(8, 5, 5, '17:50:00')");
statement.executeUpdate("insert into TimeTable values(8, 5, 10, '18:10:00')");
// create File table (Only blank table at first)
statement.executeUpdate("drop table if exists File");
statement.executeUpdate(
"create table File (File_ID int PRIMARY KEY, File_Name string, File_Path string, File_Size float, File_Type String, DTN_Address string, MAC_Address string, Status int)");
// Checking the data in the table
/* ResultSet rs = statement.executeQuery("select * from TimeTable");
while(rs.next())
{
// read the result set
System.out.println("Station_Name = " + rs.getString("Station_Name"));
System.out.println("Station_ID = " + rs.getInt("Station_ID"));
System.out.println("Terminal = " + rs.getBoolean("IsTerminal"));
System.out.println("Time = " + rs.getInt("Hour") + ":" + rs.getInt("Minute"));
}
ResultSet rs1 = statement.executeQuery("select * from File");
while(rs1.next())
{
// read the result set
System.out.println("File_Name = " + rs1.getString("File_Name"));
} */
} catch (SQLException e) {
// if the error message is "out of memory",
// it probably means no database file is found
System.err.println(e.getMessage());
} finally {
try {
if (connection != null) connection.close();
} catch (SQLException e) {
// connection close failed.
System.err.println(e);
}
}
}
@Test(expected = SQLFeatureNotSupportedException.class)
public void assertSetQueryTimeout() throws SQLException {
actual.setQueryTimeout(0);
}
@Override
public void save(Administratie admin) throws IOException {
// todo opgave 4
try {
initConnection();
for (Persoon p : admin.getPersonen()) {
Statement statement = null;
statement = conn.createStatement();
// set timeout to 30 sec
statement.setQueryTimeout(30);
String ouderlijkGezin = "";
if (p.getOuderlijkGezin() != null) {
ouderlijkGezin = String.valueOf(p.getOuderlijkGezin().getNr());
}
String query =
String.format(
"INSERT INTO `Persoon` VALUES(%d,'%s','%s','%s','%s','%s','%s','%s');",
p.getNr(),
p.getVoornamen(),
p.getAchternaam(),
p.getTussenvoegsel(),
p.getGebDat().getTime().toString(),
p.getGebPlaats(),
p.getGeslacht().toString(),
ouderlijkGezin);
statement.executeUpdate(query);
statement.close();
}
for (Gezin g : admin.getGezinnen()) {
Statement statement = null;
statement = conn.createStatement();
// set timeout to 30 sec
statement.setQueryTimeout(30);
String huwelijksDatum = "";
String scheidingsDatum = "";
int ouder2 = -1;
if (g.getOuder2() != null) {
ouder2 = g.getOuder2().getNr();
}
if (g.getHuwelijksdatum() != null) {
huwelijksDatum = g.getHuwelijksdatum().getTime().toString();
}
if (g.getScheidingsdatum() != null) {
scheidingsDatum = g.getScheidingsdatum().getTime().toString();
}
String query =
String.format(
"INSERT INTO `Gezin` VALUES(%d,%d,%d,'%s','%s');",
g.getNr(), g.getOuder1().getNr(), ouder2, huwelijksDatum, scheidingsDatum);
statement.executeUpdate(query);
statement.close();
}
} catch (SQLException e) {
e.printStackTrace();
} catch (ClassNotFoundException e) {
e.printStackTrace();
} finally {
// closeConnection();
}
}