private List<VersionManifest> findFromMapQuery(Map<String, String> map) throws SQLException {
if (conn.isClosed()) throw new SQLException("Connection is closed, cannot find record");
String where = "";
String col;
String[] values = new String[map.size()];
Iterator<String> colIter = map.keySet().iterator();
for (int i = 0; i < map.size(); i++) {
col = colIter.next();
values[i] = map.get(col);
where += col + " = ?";
if (i < map.size() - 1) where += " AND ";
}
String query = "SELECT * FROM " + config.get(CONFIG_TABLENAME) + " WHERE " + where;
PreparedStatement stmt = this.conn.prepareStatement(query);
for (int i = 0; i < values.length; i++) {
stmt.setString(i + 1, values[i]);
}
ResultSet rs = stmt.executeQuery();
List<VersionManifest> mpvList = VersionManifest.fromResultSet(rs);
rs.close();
return mpvList;
}
public boolean isClosed() throws SQLException {
if (bNotInUse) {
return true;
} else {
return con.isClosed();
}
}
public void init(int startIndex, int endIndex, String updateDate) throws SQLException {
if (connection == null || connection.isClosed()) {
connection = getConnection();
}
if (startIndex != 0 && endIndex != 0) {
bibQuery =
"SELECT * FROM OLE_DS_BIB_T WHERE BIB_ID BETWEEN "
+ startIndex
+ " AND "
+ endIndex
+ " ORDER BY BIB_ID";
} else if (StringUtils.isNotEmpty(updateDate)) {
updateDate = getDateStringForOracle(updateDate);
bibQuery = "SELECT * FROM OLE_DS_BIB_T where DATE_UPDATED > '" + updateDate + "'";
} else {
bibQuery = "SELECT * FROM OLE_DS_BIB_T ORDER BY BIB_ID";
}
if (!isStaffOnly) {
bibQuery = bibStaffOnly;
holdingsQuery = holdingsQuery + staffOnlyHoldings;
itemQuery = itemQuery + staffOnlyItem;
}
fetchCallNumberType();
fetchReceiptStatus();
fetchAuthenticationType();
fetchItemType();
fetchItemStatus();
fetchStatisticalSearchCode();
fetchExtentOfOwnershipType();
bibConnection = getConnection();
holdingsConnection = getConnection();
itemConnection = getConnection();
bibConnection.setAutoCommit(false);
bibStatement =
bibConnection.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
if (dbVendor.equalsIgnoreCase("oracle")) {
bibStatement.setFetchSize(1);
} else if (dbVendor.equalsIgnoreCase("mysql")) {
bibStatement.setFetchSize(Integer.MIN_VALUE);
}
bibResultSet = bibStatement.executeQuery(bibQuery);
holdingsPreparedStatement = holdingsConnection.prepareStatement(holdingsQuery);
itemPreparedStatement = itemConnection.prepareStatement(itemQuery);
String insertQuery =
"INSERT INTO OLE_DS_BIB_INFO_T(BIB_ID, BIB_ID_STR, TITLE, AUTHOR, PUBLISHER, ISXN) VALUES (?,?,?,?,?,?)";
bibInsertPreparedStatement = connection.prepareStatement(insertQuery);
String updateQuery =
"UPDATE OLE_DS_BIB_INFO_T SET TITLE=?, AUTHOR=?, PUBLISHER=?, ISXN=?, BIB_ID_STR=? WHERE BIB_ID=?";
bibUpdatePreparedStatement = connection.prepareStatement(updateQuery);
}
public boolean isDbConnected() {
try {
return !connector.isClosed();
} catch (SQLException ex) {
ex.printStackTrace();
return false;
}
}
/** Destroy a connection */
private void destroyConnection() throws ConnectionException, SQLException {
if (connection == null || connection.isClosed()) {
throw new ConnectionException("no connection");
}
connection.close();
connection = null;
}
public void close() throws DBIException {
try {
if (!conn.isClosed()) {
conn.close();
}
} catch (SQLException e) {
throw new DBIException(e);
}
}
/** @param dbConnection Connection */
public static void closeConnection(Connection dbConnection) {
try {
if (dbConnection != null && (!dbConnection.isClosed())) {
dbConnection.close();
}
} catch (SQLException sqlEx) {
sqlEx.printStackTrace();
}
}
public void cierraConexion(Connection cn) {
try {
if (cn != null && !cn.isClosed()) {
cn.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
public void close() {
if (dbConn != null)
try {
if (!dbConn.isClosed()) dbConn.close();
} catch (Exception ex) {
System.out.println(
(new StringBuilder("close sdb connection false:")).append(ex.getMessage()).toString());
}
}
public static void closeConn() {
try {
if (_conn != null & _conn.isClosed()) {
_conn.close();
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
/**
* Creates a connection to the database at the given host.
*
* @param host The host to connect to
* @param dbName The name of the database to connect to
*/
private void createConnection(String host, String dbName, String userName, String password)
throws SQLException, ConnectionException {
if (connection != null) {
if (!connection.isClosed()) {
throw new ConnectionException("connection already exists");
}
}
connection =
DriverManager.getConnection("jdbc:mysql://" + host + "/" + dbName, userName, password);
}
/** @return connection */
public Connection getConnection() {
try {
if (connection == null || connection.isClosed()) {
initialize();
}
} catch (SQLException e) {
initialize();
}
return connection;
}
/**
* 获取对数据库的 连接
*
* @return
* @throws Exception
*/
public Connection getConn() throws Exception {
String driver = "com.mysql.jdbc.Driver";
String url = "jdbc:mysql://localhost:3306/fetchResults";
String user = "******";
String password = "******";
Class.forName(driver);
Connection conn = DriverManager.getConnection(url, user, password);
if (conn.isClosed()) System.out.println("False connecting to the Database!");
return conn;
}
protected boolean isInnerClose() {
try {
if (con == null) {
return true;
}
return con.isClosed();
} catch (SQLException ex) {
return true;
}
}
public void close() {
try {
if (connection != null && !connection.isClosed()) {
connection.close();
}
} catch (SQLException ignored) {
}
connection = null;
}
/** 关闭连接 */
public static final void closeConnection() {
Connection conn = conns.get();
try {
if (conn != null && !conn.isClosed()) {
conn.setAutoCommit(true);
conn.close();
connectionContext.remove(Thread.currentThread().getId());
}
} catch (SQLException e) {
log.error("Unabled to close connection!!! ", e);
}
conns.set(null);
}
public boolean validateObject(Object arg0) {
boolean validated = true;
if (arg0 instanceof Connection) {
Connection connection = (Connection) arg0;
if (connection instanceof AuthingableConnection) {
AuthingableConnection authConn = (AuthingableConnection) connection;
validated = validated && authConn.isAuthenticated();
}
validated = validated && !connection.isClosed();
} else {
validated = false;
}
return validated;
}
/**
* Updates all records (should only ever be one match however) matching the values found in
* matchMap with the values found in updateMap.
*
* @param matchMap Map of the key-value pairs to match in the database.
* @param updateMap Map of the key-value pairs to update in the matching record.
* @throws SQLException
*/
private void updateRecordFromMapQuery(Map<String, String> matchMap, Map<String, String> updateMap)
throws SQLException {
if (conn.isClosed()) throw new SQLException("Connection is closed, cannot add record");
String matchString = "";
String updateString = "";
Iterator<String> matchIter = matchMap.keySet().iterator();
Iterator<String> updateIter = updateMap.keySet().iterator();
// Dynamically create the column string for updating and
// add the proper amount of IN variables for the query
while (updateIter.hasNext()) {
updateString += updateIter.next() + "=?";
if (updateIter.hasNext()) {
updateString += ", ";
}
}
// System.out.println("update string: " + updateString);
// Dynamically create the column string for matching and
// add the proper amount of IN variables for the query
while (matchIter.hasNext()) {
matchString += matchIter.next() + "=?";
if (matchIter.hasNext()) {
matchString += " AND ";
}
}
// System.out.println("match string: " + matchString);
String query =
"UPDATE "
+ config.get(CONFIG_TABLENAME)
+ " SET "
+ updateString
+ " WHERE "
+ matchString
+ ";";
PreparedStatement stmt = this.conn.prepareStatement(query);
Iterator<String> valueIter1 = updateMap.values().iterator();
Iterator<String> valueIter2 = matchMap.values().iterator();
// add the values to the prepared statement
for (int i = 1; i < updateMap.size() + 1; i++) {
stmt.setString(i, valueIter1.next());
}
for (int i = 1; i < matchMap.size() + 1; i++) {
stmt.setString(i + updateMap.size(), valueIter2.next());
}
System.out.println("Rows updated: " + stmt.executeUpdate());
}
private List<VersionManifest> findAllQuery() throws SQLException {
if (conn.isClosed()) throw new SQLException("Connection is closed, cannot find records");
String query = "SELECT * FROM " + config.get(CONFIG_TABLENAME);
PreparedStatement stmt = this.conn.prepareStatement(query);
ResultSet rs = stmt.executeQuery();
List<VersionManifest> mpvList = VersionManifest.fromResultSet(rs);
rs.close();
return mpvList;
}
/** 現在のトランザクションをコミットする */
public void commit() {
Log.info("--- start commit transactin ---");
if (con != null) {
try {
if (!con.isClosed()) {
con.commit();
con.close();
}
} catch (Exception e) {
Log.warning("fail commit transaction : " + e.getLocalizedMessage());
}
con = null;
}
Log.info("--- end commit transactin ---");
}
/**
* Returns true if the connection is open
*
* @return true if the connection is open. false if not.
*/
public boolean getIsConnected() {
if (connection == null) {
return false;
} else {
try {
if (connection.isClosed()) {
return false;
}
} catch (SQLException e) {
return false;
}
}
return true;
}
public Connection getConnection() {
try {
if (connection == null || connection.isClosed()) {
loadJdbcDriver();
connection =
DriverManager.getConnection(
GeneratorProperties.getRequiredProperty("jdbc.url"),
GeneratorProperties.getRequiredProperty("jdbc.username"),
GeneratorProperties.getProperty("jdbc.password"));
}
return connection;
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
public static Connection getConnection(Connection c) throws Exception {
Connection conn = c;
if (c != null && !c.isClosed()) {
return c;
} else {
Class clazz = Class.forName("org.luciddb.jdbc.LucidDbLocalDriver");
LucidDbLocalDriver driver = (LucidDbLocalDriver) clazz.newInstance();
String urlPrefix = driver.getUrlPrefix();
Properties props = new Properties();
props.setProperty("user", "sa");
props.setProperty("password", "");
props.setProperty("requireExistingEngine", "true");
c = DriverManager.getConnection(urlPrefix, props);
}
return c;
}
public static final Connection getConnection() throws SQLException {
Connection conn = conns.get();
if (conn == null || conn.isClosed()) {
conn = _getConnection();
if (conn == null) throw new SQLException("Unabled to get connection.");
conns.set(conn);
// RequestContext ctx = RequestContext.get();
// connectionContext.put(
// Thread.currentThread().getId(),
// new ConnectionContext(new Exception(), (ctx != null) ? ctx
// .ip() : null, (ctx != null) ? ctx.uri() : null,
// (ctx != null) ? ctx.request().getParameterMap()
// : null));
}
return (show_sql && !Proxy.isProxyClass(conn.getClass()))
? new _DebugConnection(conn).getConnection()
: conn;
}
public ProfileServlet() {
String driver = "com.mysql.jdbc.Driver";
String url = "jdbc:mysql://127.0.0.1:3306/project34?useUnicode=true&characterEncoding=utf-8";
String user = "******";
String password = "******";
try {
Class.forName(driver);
conn = DriverManager.getConnection(url, user, password);
if (!conn.isClosed()) System.out.println("Succeeded connecting to the Database!");
} catch (ClassNotFoundException e) {
System.out.println("Sorry,can`t find the Driver!");
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
} catch (Exception e) {
e.printStackTrace();
}
}
private void connect() throws SQLException {
if (connection != null) {
try {
connection.createStatement().execute("SELECT 1;");
} catch (SQLException sqlException) {
if (sqlException.getSQLState().equals("08S01")) {
try {
connection.close();
} catch (SQLException ignored) {
}
}
}
}
if (connection == null || connection.isClosed()) {
connection = DriverManager.getConnection(connectionUri, username, password);
}
}
/**
* Fires a new connection event on all listeners.
*
* @param closed <code>true</code> if <code>close</code> has been called on the connection; <code>
* false</code> if the <code>sqlException</code> represents an error where the connection may
* not longer be used.
* @param sqlException the SQLException to pass to the listeners
*/
public synchronized void fireConnectionEvent(boolean closed, SQLException sqlException) {
if (listeners.size() > 0) {
ConnectionEvent connectionEvent = new ConnectionEvent(this, sqlException);
Iterator iterator = listeners.iterator();
while (iterator.hasNext()) {
ConnectionEventListener listener = (ConnectionEventListener) iterator.next();
if (closed) {
listener.connectionClosed(connectionEvent);
} else {
try {
if (connection == null || connection.isClosed()) {
listener.connectionErrorOccurred(connectionEvent);
}
} catch (SQLException ex) {
// Will never occur
}
}
}
}
}
private void addRecordFromMapQuery(Map<String, String> colMap) throws SQLException {
if (conn.isClosed()) throw new SQLException("Connection is closed, cannot add record");
String colString = "";
String valString = "";
Iterator<String> colIter = colMap.keySet().iterator();
// Dynamically create the column string for inserting and
// add the proper amount of IN variables for the query
while (colIter.hasNext()) {
colString += colIter.next();
valString += "?"; // IN variable for statement
if (colIter.hasNext()) {
colString += ", ";
valString += ", ";
}
}
String query =
"INSERT INTO "
+ config.get(CONFIG_TABLENAME)
+ "("
+ colString
+ ")"
+ "VALUES ("
+ valString
+ ");";
PreparedStatement stmt = this.conn.prepareStatement(query);
Iterator<String> valueIter = colMap.values().iterator();
// add the values to the prepared statement
for (int i = 1; i < colMap.size() + 1; i++) {
stmt.setString(i, valueIter.next());
}
stmt.executeUpdate();
}
public Connection getConn() {
try {
if (conn == null || conn.isClosed()) {
conn =
DriverManager.getConnection(
"jdbc:mysql://"
+ host
+ ":"
+ port
+ "/"
+ db
+ "?user="******"&password="
+ pass);
cantCon++;
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return conn;
}
public List<Tax> getTaxTableData() {
List<Tax> taxList = new ArrayList<Tax>();
Connection conn = getConnection.connection();
Statement stmt = null;
ResultSet rs = null;
try {
stmt = conn.createStatement();
rs = stmt.executeQuery("SELECT * FROM tax_table");
while (rs.next()) {
Tax tax = new Tax();
tax.setId(conUtil.convertStringToInteger(rs.getString("id")));
tax.setStatus(rs.getString("status"));
tax.setTaxRate1(conUtil.convertStringToDouble(rs.getString("taxRate1")));
tax.setTaxRate2(conUtil.convertStringToDouble(rs.getString("taxRate2")));
tax.setTaxRate3(conUtil.convertStringToDouble(rs.getString("taxRate3")));
tax.setTaxRate4(conUtil.convertStringToDouble(rs.getString("taxRate4")));
tax.setTaxRate5(conUtil.convertStringToDouble(rs.getString("taxRate5")));
tax.setTaxRate6(conUtil.convertStringToDouble(rs.getString("taxRate6")));
tax.setTaxRate7(conUtil.convertStringToDouble(rs.getString("taxRate7")));
taxList.add(tax);
}
} catch (SQLException ex) {
Logger.getLogger(TaxDAO.class.getName()).log(Level.SEVERE, null, ex);
} finally {
try {
if (conn != null || !conn.isClosed()) {
stmt.close();
rs.close();
conn.close();
}
} catch (SQLException ex) {
Logger.getLogger(TaxDAO.class.getName()).log(Level.SEVERE, null, ex);
}
}
return taxList;
}