private synchronized void init() throws SQLException {
if (isClosed) return;
// do tables exists?
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(TABLE_NAMES_SELECT_STMT);
ArrayList<String> missingTables = new ArrayList(TABLES.keySet());
while (rs.next()) {
String tableName = rs.getString("name");
missingTables.remove(tableName);
}
for (String missingTable : missingTables) {
try {
Statement createStmt = conn.createStatement();
// System.out.println("Adding table "+ missingTable);
createStmt.executeUpdate(TABLES.get(missingTable));
createStmt.close();
} catch (Exception e) {
System.err.println(e.getClass().getName() + ": " + e.getMessage());
}
}
}
public Connection getConnection() throws SQLException {
synchronized (pool) {
if (!pool.isEmpty()) {
int last = pool.size() - 1;
Connection pooled = (Connection) pool.remove(last);
boolean conn_ok = true;
String test_table = prop.getProperty("test_table");
if (test_table != null) {
Statement stmt = null;
try {
stmt = pooled.createStatement();
stmt.executeQuery("select * from " + prop.getProperty("test_table"));
} catch (SQLException ex) {
conn_ok = false; // 连接不可用
} finally {
if (stmt != null) {
stmt.close();
}
}
}
if (conn_ok == true) {
return pooled;
} else {
pooled.close();
}
}
}
Connection conn =
DriverManager.getConnection(
prop.getProperty("url"), prop.getProperty("username"), prop.getProperty("password"));
return conn;
}
/**
* Removes the specified listener from the list.
*
* @see #addConnectionEventListener
* @see #fireConnectionEvent
*/
public synchronized void removeConnectionEventListener(ConnectionEventListener listener) {
// Clone the list of listeners to avoid concurrent modifications. See
// bug [1113040] Small bug in net.sourceforge.jtds.jdbcx.PooledConnection
// for a description of how these can occur. The method still needs to
// be synchronized to prevent race conditions.
listeners = (ArrayList) listeners.clone();
// Now remove the listener from the new, cloned list
listeners.remove(listener);
}
public static void setupDb() {
loadDriver();
Connection conn = null;
ArrayList statements = new ArrayList();
Statement s = null;
ResultSet rs = null;
try {
// database name
String dbName = "demoDB";
conn = DriverManager.getConnection(protocol + dbName + ";create=true", props);
System.out.println("Creating database " + dbName);
boolean createTable = false;
s = conn.createStatement();
try {
s.executeQuery("SELECT count(*) FROM rssFeed");
} catch (Exception e) {
createTable = true;
}
if (createTable) {
// handle transaction
conn.setAutoCommit(false);
s = conn.createStatement();
statements.add(s);
// Create a contact table...
s.execute("create table rssFeed(id int, title varchar(255), url varchar(600))");
System.out.println("Created table rssFeed ");
conn.commit();
}
shutdown();
} catch (SQLException sqle) {
sqle.printStackTrace();
} finally {
close(rs);
// Statements and PreparedStatements
int i = 0;
while (!statements.isEmpty()) {
// PreparedStatement extend Statement
Statement st = (Statement) statements.remove(i);
close(st);
}
close(conn);
}
}
public static ArrayList getAfterDel(ArrayList al, String[] str, int num) {
int temp, temp1, temp2;
for (int i = str.length - 1; i >= 0; i--) {
temp = Integer.parseInt(str[i]);
temp1 = (temp - 1) * num;
temp2 = temp * num - 1;
for (int j = temp2; j >= temp1; j--) {
if (j < al.size()) al.remove(j);
}
}
return al;
}
public static int saveRssFeed(RssFeed rssFeed) {
int pk = rssFeed.link.hashCode();
loadDriver();
Connection conn = null;
ArrayList statements = new ArrayList();
PreparedStatement psInsert = null;
Statement s = null;
ResultSet rs = null;
try {
// database name
String dbName = "demoDB";
conn = DriverManager.getConnection(protocol + dbName + ";create=true", props);
rs =
conn.createStatement()
.executeQuery("select count(id) from rssFeed where id = " + rssFeed.link.hashCode());
rs.next();
int count = rs.getInt(1);
if (count == 0) {
// handle transaction
conn.setAutoCommit(false);
s = conn.createStatement();
statements.add(s);
psInsert = conn.prepareStatement("insert into rssFeed values (?, ?, ?)");
statements.add(psInsert);
psInsert.setInt(1, pk);
String escapeTitle = rssFeed.channelTitle.replaceAll("\'", "''");
psInsert.setString(2, escapeTitle);
psInsert.setString(3, rssFeed.link);
psInsert.executeUpdate();
conn.commit();
System.out.println("Inserted " + rssFeed.channelTitle + " " + rssFeed.link);
System.out.println("Committed the transaction");
}
shutdown();
} catch (SQLException sqle) {
sqle.printStackTrace();
} finally {
// release all open resources to avoid unnecessary memory usage
// ResultSet
close(rs);
// Statements and PreparedStatements
int i = 0;
while (!statements.isEmpty()) {
// PreparedStatement extend Statement
Statement st = (Statement) statements.remove(i);
close(st);
}
// Connection
close(conn);
}
return pk;
}
public synchronized void removeDbAccessListener(DbAccessListener l) {
if (dbAccessListeners != null && dbAccessListeners.contains(l)) dbAccessListeners.remove(l);
}
/**
* Removes the specified listener from the list.
*
* @see #addConnectionEventListener
* @see #fireConnectionEvent
*/
public synchronized void removeConnectionEventListener(ConnectionEventListener listener) {
_listeners.remove(listener);
}
