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);
  }
示例#2
0
  /**
   * 获取对数据库的 连接
   *
   * @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;
  }
示例#3
0
文件: DBManager.java 项目: kone/kin
 /** 关闭连接 */
 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 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);
   }
 }
示例#5
0
 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;
 }
示例#6
0
文件: DBManager.java 项目: kone/kin
 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;
 }
  /**
   * 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
          }
        }
      }
    }
  }
  /**
   * @return Connection
   * @roseuid 3F3A5FFD0338
   */
  public Connection getConnection() throws EmanagerDatabaseException {
    long connectionId;
    Connection connection;
    PooledConnection pooledConnection;

    connection = null;
    pooledConnection = null;
    connectionId = InvalidConnectionId;

    try {
      synchronized (connectionPool) {
        if (!connectionPool.isEmpty()) {
          try {
            boolean connectionClosed;

            connectionClosed = false;

            pooledConnection = (PooledConnection) connectionPool.remove(0);
            connection = pooledConnection.getConnection();
            connection.clearWarnings();
            connectionId = getConnectionID(connection);
            connectionClosed = connection.isClosed();
            if (connectionId == InvalidConnectionId || connectionClosed == true) {
              logger.debug("Pooled connection closed.");
              connection = null;
            }
          } catch (SQLException sqe) {
            logger.debug("Pooled connection closed.");
            connection = null;
          }
        }
      }

      if (connection == null) {
        logger.debug("Getting a new connection.");
        pooledConnection = poolDataSource.getPooledConnection();
        pooledConnection.addConnectionEventListener(this);
        connection = pooledConnection.getConnection();
        connection.clearWarnings();
        connectionId = getConnectionID(connection);
      }
    } catch (SQLException sqe) {
      String logString;
      EmanagerDatabaseException ede;

      logString =
          EmanagerDatabaseStatusCode.UnableToGetPooledConnection.getStatusCodeDescription()
              + sqe.getMessage();

      logger.error(logString);
      ede =
          new EmanagerDatabaseException(
              EmanagerDatabaseStatusCode.UnableToGetPooledConnection, logString);
      throw ede;
    }

    if (connectionId == InvalidConnectionId) {
      EmanagerDatabaseException ede;
      ede =
          new EmanagerDatabaseException(
              EmanagerDatabaseStatusCode.UnableToGetPooledConnection,
              EmanagerDatabaseStatusCode.UnableToGetPooledConnection.getStatusCodeDescription());
      throw ede;
    }

    logger.debug(
        "\n*****************************"
            + "\nPooled Connection Init"
            + "\nCon ID:"
            + connectionId
            + "\nCon Object:"
            + pooledConnection
            + "\nPool Object:"
            + connection
            + "\n*****************************");

    return connection;
  }
示例#9
0
 public boolean isOpen() throws Exception {
   if (!settings.getDatabaseSettings().getEnabled()) return false;
   if ((connection == null) || connection.isClosed()) open();
   return (connection != null ? !connection.isClosed() : false);
 }
示例#10
0
  public void runReplicate() throws InterruptedException {
    if (verbose) log.append("Started at " + Calendar.getInstance().getTime() + "\r\n");
    Connection connectionFrom = null;
    Connection connectionTo = null;
    try {
      connectionFrom = providerI.getConnection(dbFrom);
      connectionTo = providerI.getConnection(dbTo);
      PreparedStatement preparedStatementFrom = null;
      PreparedStatement preparedStatementTo = null;
      ResultSet resultSet = null;

      for (Iterator<HashMap<String, String>> HashMapIterator = tables.iterator();
          HashMapIterator.hasNext(); ) {
        try {
          HashMap<String, String> h = HashMapIterator.next();
          String name = h.get("name");
          String name2 = h.get("name2");
          String where1 = h.get("where1");
          String where2 = h.get("where2");

          if (verbose)
            log.append("Replicating " + dbFrom + "/" + name + " to " + dbTo + "/" + name2 + "\r\n");

          preparedStatementFrom =
              connectionFrom.prepareStatement("select * from " + name + " WHERE 1=1 " + where1);
          boolean b = preparedStatementFrom.execute();
          HashMap meta = new HashMap();
          // 1. find all ids from DB1
          ArrayList db1 = new ArrayList();
          String nullMark = "____NULL";
          if (b) {
            resultSet = preparedStatementFrom.getResultSet();
            ResultSetMetaData setMetaData = resultSet.getMetaData();
            for (int i = 1; i <= setMetaData.getColumnCount(); i++) {
              meta.put(
                  setMetaData.getColumnLabel(i).toLowerCase(), setMetaData.getColumnClassName(i));
            }
            while (resultSet.next()) {
              HashMap results = new HashMap();
              for (int i = 1; i <= setMetaData.getColumnCount(); i++) {
                String columnName = setMetaData.getColumnLabel(i).toLowerCase();
                Object o = resultSet.getObject(i);
                if (o != null) {
                  results.put(columnName, o);
                } else {
                  results.put(columnName + nullMark, nullMark);
                }
              }
              db1.add(results);
            }
            resultSet.close();
          } else {
            log.append("Couldn't execute select from " + dbFrom + "/" + name + " /r/n");
            return;
          }

          StringBuffer allIds = new StringBuffer();
          HashMap records1 = new HashMap();
          HashMap toInsert = new HashMap();
          HashMap toUpdate = new HashMap();
          for (Iterator iterator = db1.iterator(); iterator.hasNext(); ) {
            HashMap record = (HashMap) iterator.next();
            if (allIds.length() != 0) {
              allIds.append(",");
            }
            allIds.append(record.get("id"));
            records1.put(record.get("id"), record);
          }

          toInsert.putAll(records1);

          // 2. find all ids to delete in DB2;
          if (allIds.length() > 0) {
            preparedStatementTo =
                connectionTo.prepareStatement(
                    "delete from "
                        + name2
                        + " where id not in ("
                        + allIds.toString()
                        + ")"
                        + where2);
            if (verbose)
              log.append(
                  "deleted from "
                      + dbTo
                      + "/"
                      + name2
                      + " "
                      + preparedStatementTo.executeUpdate()
                      + " records;\r\n");
          } else {
            if (verbose)
              log.append(
                  "No records in "
                      + dbFrom
                      + "/"
                      + name
                      + ", nothing to delete in "
                      + dbTo
                      + "/"
                      + name2
                      + " ;\r\n");
          }

          // 3. find all ids from DB2;
          preparedStatementTo =
              connectionTo.prepareStatement("select * from " + name2 + " WHERE 1=1 " + where2);
          b = preparedStatementTo.execute();
          HashMap meta2 = new HashMap();
          ArrayList db2 = new ArrayList();
          if (b) {
            resultSet = preparedStatementTo.getResultSet();
            ResultSetMetaData setMetaData = resultSet.getMetaData();
            for (int i = 1; i <= setMetaData.getColumnCount(); i++) {
              meta2.put(
                  setMetaData.getColumnLabel(i).toLowerCase(), setMetaData.getColumnClassName(i));
            }
            while (resultSet.next()) {
              HashMap results = new HashMap();
              for (int i = 1; i <= setMetaData.getColumnCount(); i++) {
                String columnName = setMetaData.getColumnLabel(i).toLowerCase();
                Object o = resultSet.getObject(i);
                if (o != null) {
                  results.put(columnName, o);
                } else {
                  results.put(columnName + nullMark, nullMark);
                }
              }
              db2.add(results);
            }
          } else {
            log.append("Couldn't execute select from " + dbTo + "/" + name2 + " /r/n");
            return;
          }

          // compare meta-data;
          {
            HashMap temp = new HashMap();
            {
              temp.putAll(meta);

              Set set = meta2.keySet();
              for (Iterator iteratorSet = set.iterator(); iteratorSet.hasNext(); ) {
                Object o = iteratorSet.next();
                if (meta.containsKey(o)) {
                  if (meta.get(o).equals(meta2.get(o))) {
                    temp.remove(o);
                  }
                } else {
                  log.append("ERROR: Meta data not equals! \r\n");
                  log.append(o + "\t" + temp.get(o) + " not present in table " + name + "\r\n");
                }
              }
            }
            if (!temp.isEmpty()) {
              log.append("ERROR: Meta data not equals! \r\n");
              Set set = temp.keySet();
              for (Iterator iteratorSet = set.iterator(); iteratorSet.hasNext(); ) {
                Object o = iteratorSet.next();
                log.append(o + "\t" + temp.get(o) + " != " + meta2.get(o) + "\r\n");
              }

              return;
            }
          }

          for (Iterator iterator = db2.iterator(); iterator.hasNext(); ) {
            HashMap db2Record = (HashMap) iterator.next();
            if (toInsert.containsKey(db2Record.get("id"))) {
              HashMap db1Record = (HashMap) toInsert.get(db2Record.get("id"));
              boolean equal = true;
              Set set = meta2.keySet();
              for (Iterator iteratorSet = set.iterator(); equal && iteratorSet.hasNext(); ) {
                String columnName2 = (String) iteratorSet.next();
                if (db2Record.containsKey(columnName2 + nullMark)
                    || db1Record.containsKey(columnName2 + nullMark)) {
                  equal =
                      db2Record.containsKey(columnName2 + nullMark)
                          && db1Record.containsKey(columnName2 + nullMark);
                } else {
                  // checking not-null;
                  equal = equalRecords(db1Record.get(columnName2), db2Record.get(columnName2));
                }
              }
              if (!equal) {
                toUpdate.put(db2Record.get("id"), toInsert.get(db2Record.get("id")));
              }
              toInsert.remove(db2Record.get("id"));
            } else {
              // this case shouldn't happen at all, since we've deleted all such records

            }
          }

          log.append(
              "Found "
                  + toUpdate.size()
                  + " to update, and "
                  + toInsert.size()
                  + " to insert.\r\n");
          int totalUpdated = 0;
          // 4. calculate all to update in DB2
          if (!toUpdate.isEmpty()) {
            Set set = toUpdate.keySet();
            for (Iterator iteratorSet = set.iterator(); iteratorSet.hasNext(); ) {
              StringBuffer sql = new StringBuffer();
              Object id = iteratorSet.next();
              HashMap r = (HashMap) toUpdate.get(id);
              sql.append("UPDATE " + name2 + " SET ");
              StringBuffer values = new StringBuffer();

              Set en = meta2.keySet();
              for (Iterator iteratorSetEn = en.iterator(); iteratorSetEn.hasNext(); ) {
                Object o = iteratorSetEn.next();
                if (!o.equals("id")) {
                  if (values.length() != 0) {
                    values.append(",");
                  }
                  Object quote = dbQuotes.get(dbToType);
                  if (quote == null) {
                    quote = "";
                  }
                  values.append(quote).append(o).append(quote);
                  values.append(" =  ? ");
                }
              }
              values.append(" WHERE id = '" + r.get("id") + "';");
              PreparedStatement statement =
                  connectionTo.prepareStatement(sql.toString() + values.toString());
              en = meta2.keySet();
              int i = 0;
              for (Iterator iteratorSetEn = en.iterator(); iteratorSetEn.hasNext(); ) {
                Object o = iteratorSetEn.next();
                if (!o.equals("id")) {
                  i++;
                  statement.setObject(i, r.get(o));
                }
              }
              try {
                totalUpdated += statement.executeUpdate();
              } catch (SQLException e) {
                e.printStackTrace();
                log.append("Error occured: " + e + "\r\n");
              }
            }
          }
          if (verbose) log.append("Updated " + totalUpdated + " records.\r\n");

          // 4. calculate all to insert to DB2
          if (!toInsert.isEmpty()) {
            StringBuffer header = new StringBuffer();
            if (header.length() == 0) {
              header.append(" INSERT INTO " + name2 + " (");
              StringBuffer columns = new StringBuffer();
              Set en = meta2.keySet();
              for (Iterator iteratorSetEn = en.iterator(); iteratorSetEn.hasNext(); ) {
                Object o = iteratorSetEn.next();
                if (columns.length() != 0) {
                  columns.append(",");
                }

                Object quote = dbQuotes.get(dbToType);
                if (quote == null) {
                  quote = "";
                }
                columns.append(quote).append(o).append(quote);
              }
              header.append(columns.toString());
              header.append(") VALUES ");
            }

            Set enumeration = toInsert.keySet();
            for (Iterator iteratorSetX = enumeration.iterator(); iteratorSetX.hasNext(); ) {
              Object id = iteratorSetX.next();
              HashMap r = (HashMap) toInsert.get(id);
              StringBuffer values = new StringBuffer();
              if (values.length() != 0) {
                values.append(",");
              }
              values.append("(");

              StringBuffer columns = new StringBuffer();
              Set en = meta2.keySet();
              for (Iterator iteratorSetEn = en.iterator(); iteratorSetEn.hasNext(); ) {
                Object o = iteratorSetEn.next();
                if (columns.length() != 0) {
                  columns.append(",");
                }
                columns.append(" ? ");
              }
              values.append(columns.toString());
              values.append(");");

              PreparedStatement statement =
                  connectionTo.prepareStatement(header.toString() + values.toString());
              en = meta2.keySet();
              int i = 0;
              for (Iterator iteratorSetEn = en.iterator(); iteratorSetEn.hasNext(); ) {
                Object o = iteratorSetEn.next();
                i++;
                statement.setObject(i, r.get(o));
              }
              statement.execute();
            }
          }

          if (verbose) log.append("Replication finished OK.\r\n");
        } catch (Exception e) {
          log.append("Some error occured: " + e + "\r\n");
          log.append(e.getMessage() + "\r\n");
          e.printStackTrace();
        }
      }
    } catch (Exception e) {
      log.append("Error with query = " + e);
      log.append(e.getMessage());
      return;
    } finally {
      try {
        if (connectionFrom != null && !connectionFrom.isClosed()) {
          connectionFrom.close();
        }
      } catch (SQLException e) {
        e.printStackTrace();
      }
      try {
        if (connectionTo != null && !connectionTo.isClosed()) {
          connectionTo.close();
        }
      } catch (SQLException e) {
        e.printStackTrace();
      }
    }
    if (verbose) log.append("Ended at " + Calendar.getInstance().getTime());
  }
示例#11
0
文件: DBManager.java 项目: nh13/IGV
 public boolean isReallyClosed() throws SQLException {
   return conn.isClosed();
 }