示例#1
1
  public static void main(String[] args) throws Exception {

    try (Connection conn = DBUtil.getConnection(DBType.HSQLDB);
        Statement stmt =
            conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
        ResultSet rs = stmt.executeQuery("SELECT stateId, stateName FROM states"); ) {

      States.displayData(rs);

      rs.last();
      System.out.println("Number of rows: " + rs.getRow());

      rs.first();
      System.out.println("The first state is " + rs.getString("stateName"));

      rs.last();
      System.out.println("The last state is " + rs.getString("stateName"));

      rs.absolute(10);
      System.out.println("The 10th state is " + rs.getString("stateName"));

    } catch (SQLException e) {
      System.err.println(e);
    }
  }
示例#2
0
 public SiteInternal find(URL siteURL) {
   SiteInternal site = null;
   Statement st = null;
   ResultSet rs = null;
   try {
     Connection c = dbUtil.getConnection();
     st = c.createStatement();
     rs =
         st.executeQuery(
             "select * from jspider_site where host = '"
                 + siteURL.getHost()
                 + "' and port = "
                 + siteURL.getPort());
     if (rs.next()) {
       site = createSiteFromRecord(rs);
     } else {
       return null;
     }
   } catch (SQLException e) {
     log.error("SQLException", e);
   } finally {
     dbUtil.safeClose(rs, log);
     dbUtil.safeClose(st, log);
   }
   return site;
 }
示例#3
0
 public void save(int id, SiteInternal site) {
   StringBuffer sb = new StringBuffer();
   Connection connection = dbUtil.getConnection();
   Statement st = null;
   sb.append("update jspider_site set ");
   sb.append("handle=");
   sb.append(DBUtil.format(site.mustHandle()));
   sb.append(",robotstxthandled=");
   sb.append(DBUtil.format(site.isRobotsTXTHandled()));
   sb.append(",usecookies=");
   sb.append(DBUtil.format(site.getUseCookies()));
   sb.append(",useproxy=");
   sb.append(DBUtil.format(site.getUseProxy()));
   sb.append(",state=");
   sb.append(DBUtil.format(site.getState()));
   sb.append(",obeyrobotstxt=");
   sb.append(DBUtil.format(site.getObeyRobotsTXT()));
   sb.append(",basesite=");
   sb.append(DBUtil.format(site.isBaseSite()));
   sb.append(",useragent=");
   sb.append(DBUtil.format(site.getUserAgent()));
   sb.append(" where id = ");
   sb.append(DBUtil.format(id));
   try {
     st = connection.createStatement();
     st.executeUpdate(sb.toString());
   } catch (SQLException e) {
     log.error("SQLException", e);
   } finally {
     dbUtil.safeClose(st, log);
   }
 }
示例#4
0
  // 使用回调机制,复用代码
  public ResultSet execute(StatementCallback callback) {
    Connection connection = DBUtil.getConnection();
    Statement statement = null;
    ResultSet resultSet = null;

    try {

      statement = connection.createStatement();
      connection.setAutoCommit(false);
      // 调用回调函数
      resultSet = callback.doInStatement(statement);
      connection.commit();

    } catch (SQLException e) {
      log.error(e);
      try {
        connection.rollback();
      } catch (SQLException e1) {
        log.error(e);
      }
    } finally {
      try {
        statement.close();
        connection.close();
      } catch (SQLException e) {
        log.error(e);
      }
    }

    return resultSet;
  }
示例#5
0
 public void create(int id, SiteInternal site) {
   Connection connection = dbUtil.getConnection();
   Statement st = null;
   StringBuffer sb = new StringBuffer();
   sb.append("insert into jspider_site (");
   sb.append("id,");
   sb.append("host,");
   sb.append("port,");
   sb.append("handle,");
   sb.append("robotstxthandled,");
   sb.append("usecookies,");
   sb.append("useproxy,");
   sb.append("state,");
   sb.append("obeyrobotstxt,");
   sb.append("basesite,");
   sb.append("useragent");
   sb.append(") values (");
   sb.append(DBUtil.format(id));
   sb.append(",");
   sb.append(DBUtil.format(site.getHost()));
   sb.append(",");
   sb.append(DBUtil.format(site.getPort()));
   sb.append(",");
   sb.append(DBUtil.format(site.mustHandle()));
   sb.append(",");
   sb.append(DBUtil.format(site.isRobotsTXTHandled()));
   sb.append(",");
   sb.append(DBUtil.format(site.getUseCookies()));
   sb.append(",");
   sb.append(DBUtil.format(site.getUseProxy()));
   sb.append(",");
   sb.append(DBUtil.format(site.getState()));
   sb.append(",");
   sb.append(DBUtil.format(site.getObeyRobotsTXT()));
   sb.append(",");
   sb.append(DBUtil.format(site.isBaseSite()));
   sb.append(",");
   sb.append(DBUtil.format(site.getUserAgent()));
   sb.append(")");
   try {
     st = connection.createStatement();
     st.executeUpdate(sb.toString());
   } catch (SQLException e) {
     log.error("SQLException", e);
   } finally {
     dbUtil.safeClose(st, log);
   }
 }
  private String getMessageFromDB() {
    String msg = null;
    try {
      DBUtil dbUtil = DBUtil.getInstance();
      Connection conn = dbUtil.getConnection();
      PreparedStatement stmt = conn.prepareStatement("select msg from hello");
      ResultSet rs = stmt.executeQuery();

      while (rs.next()) {
        msg = rs.getString("msg");
      }
    } catch (Exception ex) {
      StringWriter sw = new StringWriter();
      PrintWriter pw = new PrintWriter(sw);
      ex.printStackTrace(pw);
      msg = sw.toString();
    }
    return msg;
  }
示例#7
0
 public SiteInternal[] findAll() {
   ArrayList al = new ArrayList();
   Statement st = null;
   ResultSet rs = null;
   try {
     Connection c = dbUtil.getConnection();
     st = c.createStatement();
     rs = st.executeQuery("select * from jspider_site");
     while (rs.next()) {
       al.add(createSiteFromRecord(rs));
     }
   } catch (SQLException e) {
     log.error("SQLException", e);
   } finally {
     dbUtil.safeClose(rs, log);
     dbUtil.safeClose(st, log);
   }
   return (SiteInternal[]) al.toArray(new SiteInternal[al.size()]);
 }
 public TransactionService() {
   connection = DBUtil.getConnection();
 }
public class TransactionService {

  private static Connection connection = DBUtil.getConnection();

  public TransactionService() {
    connection = DBUtil.getConnection();
  }

  public static float getAccountBalance(int businessID) {
    float balance = 0;
    try {
      String query = ("select type, amount from transaction_table where business_id=?");
      PreparedStatement preparedStatement = connection.prepareStatement(query);
      preparedStatement.setInt(1, businessID);
      ResultSet rs = preparedStatement.executeQuery();
      while (rs.next()) {
        if (rs.getBoolean("type") == false) {
          balance -= rs.getFloat("amount");
        } else {
          balance += rs.getFloat("amount");
        }
      }
    } catch (SQLException e) {
      e.printStackTrace();
    }
    return balance;
  }

  public static float getTotalBalance(int businessID) {
    float balance = 0;
    try {
      String query =
          "select account_balance from transaction_table where business_id=? order by id desc limit 1";
      PreparedStatement preparedStatement = connection.prepareStatement(query);
      preparedStatement.setInt(1, businessID);
      ResultSet rs = preparedStatement.executeQuery();
      balance = rs.getFloat("account_balance");
    } catch (SQLException e) {
      e.printStackTrace();
    }
    return balance;
  }

  public static List<Transaction> getAllTransactions(int businessID) {
    List<Transaction> transactions = new ArrayList<Transaction>();
    try {
      String query = "select * from transaction_table where business_id=?";
      PreparedStatement preparedStatement = connection.prepareStatement(query);
      preparedStatement.setInt(1, businessID);
      ResultSet rs = preparedStatement.executeQuery();
      while (rs.next()) {
        Transaction transaction = new Transaction();
        transaction.setId(rs.getInt("transaction_id"));
        transaction.setType(rs.getBoolean("type"));
        transaction.setCategory(rs.getString("category"));
        transaction.setDescription(rs.getString("description"));
        transaction.setAmount(rs.getFloat("amount"));
        transaction.setTransactionDate(rs.getString("transaction_date"));
        transaction.setAccountBalance(rs.getFloat("account_balance"));
        transactions.add(transaction);
      }
    } catch (SQLException e) {
      e.printStackTrace();
    }
    return transactions;
  }

  public static void createTransaction(Transaction transaction) throws ParseException {
    try {
      String query =
          "insert into transaction_table(business_id, transaction_id, type, category, description, amount, transaction_date, account_balance) values (?,?,?,?,?,?,?,?)";
      PreparedStatement preparedStatement = connection.prepareStatement(query);
      preparedStatement.setInt(1, 1);
      preparedStatement.setInt(2, transaction.getId());
      preparedStatement.setBoolean(3, transaction.getType());
      preparedStatement.setString(4, transaction.getCategory());
      preparedStatement.setString(5, transaction.getDescription());
      preparedStatement.setFloat(6, transaction.getAmount());
      preparedStatement.setString(7, transaction.getTransactionDate());
      preparedStatement.setFloat(8, transaction.getAccountBalance());
      preparedStatement.executeUpdate();
    } catch (SQLException e) {
      e.printStackTrace();
    }
  }

  public static Transaction getTransaction(int transactionID) {
    Transaction transaction = new Transaction();
    try {
      String query = "select * from transaction_table where transaction_id=?";
      PreparedStatement preparedStatement = connection.prepareStatement(query);
      preparedStatement.setInt(1, transactionID);
      ResultSet rs = preparedStatement.executeQuery();
      if (rs.next()) {
        transaction.setId(rs.getInt("transaction_id"));
        transaction.setType(rs.getBoolean("type"));
        transaction.setCategory(rs.getString("category"));
        transaction.setDescription(rs.getString("description"));
        transaction.setAmount(rs.getFloat("amount"));
        transaction.setTransactionDate(rs.getString("transaction_date"));
        transaction.setAccountBalance(rs.getFloat("account_balance"));
      }
    } catch (SQLException e) {
      e.printStackTrace();
    }
    return transaction;
  }

  public static void updateTransaction(Transaction transaction) throws ParseException {
    try {
      String query =
          "update transaction_table set type=?, category=?, description=?, amount=?, transaction_date=?, account_balance=? where transaction_id=?";
      PreparedStatement preparedStatement = connection.prepareStatement(query);
      preparedStatement.setInt(1, 1);
      preparedStatement.setInt(2, transaction.getId());
      preparedStatement.setBoolean(3, transaction.getType());
      preparedStatement.setString(4, transaction.getCategory());
      preparedStatement.setString(5, transaction.getDescription());
      preparedStatement.setFloat(6, transaction.getAmount());
      preparedStatement.setString(7, transaction.getTransactionDate());
      preparedStatement.setFloat(8, transaction.getAccountBalance());
      preparedStatement.executeUpdate();
    } catch (SQLException e) {
      e.printStackTrace();
    }
  }

  public static void deleteTransaction(int transactionID) {
    try {
      String query = "delete from transaction_table where transaction_id=?";
      PreparedStatement preparedStatement = connection.prepareStatement(query);
      preparedStatement.setInt(1, transactionID);
      preparedStatement.executeUpdate();

    } catch (SQLException e) {
      e.printStackTrace();
    }
  }
}