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);
}
}
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;
}
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);
}
}
// 使用回调机制，复用代码
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;
}
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;
}
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();
}
}
}