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);
}
}
@Override
public String getDocCode(String doName, String account) throws RemoteException {
// TODO Auto-generated method stub
try {
if (doName.equals("出库单") || doName.equals("入库单")) {
sql = "select code from " + doName + " where left(code,5)='" + account + "'";
Connection connection = DriverManager.getConnection(URL, USER, PASSWORD);
PreparedStatement preparedStatement = connection.prepareStatement(sql);
ResultSet resultSet = preparedStatement.executeQuery();
if (resultSet.last()) {
int codenum = Integer.parseInt(resultSet.getString(1));
codenum = codenum + 1;
String code = Integer.toString(codenum);
connection.close();
return code;
} else {
connection.close();
return account + "00001";
}
} else {
sql = "select code from b" + doName + " where left(code,5)='" + account + "'";
Connection connection = DriverManager.getConnection(URL, USER, PASSWORD);
PreparedStatement preparedStatement = connection.prepareStatement(sql);
ResultSet resultSet = preparedStatement.executeQuery();
if (resultSet.last()) {
int codenum = Integer.parseInt(resultSet.getString(1));
codenum = codenum + 1;
String code = Integer.toString(codenum);
connection.close();
return code;
} else {
sql = "select code from " + doName + " where left(code,5)='" + account + "'";
connection = DriverManager.getConnection(URL, USER, PASSWORD);
preparedStatement = connection.prepareStatement(sql);
resultSet = preparedStatement.executeQuery();
if (resultSet.last()) {
int codenum = Integer.parseInt(resultSet.getString(1));
codenum = codenum + 1;
String code = Integer.toString(codenum);
connection.close();
return code;
} else {
connection.close();
return account + "00001";
}
}
}
} catch (NumberFormatException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return null;
}
private void checkTokenLoad(String datadir) {
System.err.println("Checking Token table load");
SQLDB.loadFile(datadir + "loadTKTest", "Token");
try {
ResultSet t = SQLDB.queryDatabase("SELECT * FROM Token");
t.last();
assertTrue(t.getInt("TokenId") == 1);
assertTrue(t.getInt("TokenType") == 1);
assertTrue(t.getInt("SlotId") == 1);
assertTrue(t.getInt("SlotIndex") == 1);
assertTrue(t.getLong("PartialPlanId") == 1L);
assertTrue(t.getBoolean("IsFreeToken"));
assertTrue(t.getBoolean("IsValueToken"));
assertTrue(t.getInt("StartVarId") == 1);
assertTrue(t.getInt("EndVarId") == 1);
assertTrue(t.getInt("DurationVarId") == 1);
assertTrue(t.getInt("StateVarId") == 1);
assertTrue(t.getString("PredicateName").equals("1"));
assertTrue(t.getInt("ParentId") == 1);
assertTrue(t.getString("ParentName").equals("1"));
assertTrue(t.getInt("ObjectVarId") == 1);
assertTrue(t.getString("ParamVarIds").equals("1"));
assertTrue(t.getString("ExtraData").equals("1"));
} catch (SQLException sqle) {
sqle.printStackTrace();
System.exit(-1);
}
SQLDB.updateDatabase("DELETE FROM Token");
}
public int[] getAd() {
int i = 0;
int j = 0;
int[] ans = null;
String str = "SELECT * FROM ad ORDER BY id";
try {
row = stm.executeQuery(str);
row.last();
i = row.getRow();
row.first();
ans = new int[i + 1];
if (i > 0) {
ans[0] = i;
do {
j++;
ans[j] = Integer.parseInt(row.getString("priority"));
} while (row.next());
} else {
ans[0] = 0;
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return ans;
}
/**
* Function: Attempt to login the user.
*
* <p>Param: stmt - Statement object to run select.
*
* <p>Return: True if the credentials are correct.
*
* <p>schwehr 20100310
*/
private static boolean login(Statement stmt) {
String pass;
System.out.print("E-mail: ");
Main.user = Keyboard.in.readString();
System.out.print("Password: "******"select email, pwd from users where email ='%s' and pwd ='%s'", Main.user, pass);
ResultSet rset = stmt.executeQuery(query);
// There should only be one record found if the credentials are valid..
rset.last();
if (rset.getRow() == 1) {
return true;
}
} catch (SQLException ex) {
System.err.println("SQLException: " + ex.getMessage());
}
return false;
}
// 查询专家系统不为空数据
public static Object[][] getNotNullExpertData() {
Object[][] ExpertData = null;
int i = 0;
try {
Class.forName(driver);
conn = DriverManager.getConnection(url, "root", "root");
String sql =
"select customerId, totalFuzzy, neuralResult from expertSystem where totalFuzzy is not null and neuralResult is not null";
System.out.println(sql);
Statement st = conn.createStatement();
ResultSet rs = st.executeQuery(sql);
if (rs != null) {
rs.last();
ExpertData = new Object[rs.getRow()][3];
rs.beforeFirst();
while (rs != null && rs.next()) {
ExpertData[i][0] = rs.getInt(1);
ExpertData[i][1] = rs.getDouble(2);
ExpertData[i][2] = rs.getDouble(3);
i++;
}
}
rs.close();
st.close();
conn.close();
} catch (Exception e) {
e.printStackTrace();
}
return ExpertData;
}
private ArrayList<BookPO> map(ResultSet resultSet) {
ArrayList<BookPO> polist = null;
String name = null, ISBN = null, author = null, press = null, description = null;
int directoryID = 0;
Calendar publishDate = null;
double price = 0, specialPrice = 0;
try {
resultSet.last();
int len = 0;
if ((len = resultSet.getRow()) != 0) {
polist = new ArrayList<BookPO>();
resultSet.beforeFirst();
for (int i = 0; i < len; i++) {
resultSet.next();
ISBN = resultSet.getString(2);
polist.add(
new BookPO(
name,
ISBN,
author,
press,
description,
directoryID,
publishDate,
price,
specialPrice));
}
return polist;
}
} catch (SQLException e) {
e.printStackTrace();
}
return null;
}
public void updateServiceType(
CFSecurityAuthorization Authorization, CFSecurityServiceTypeBuff Buff) {
final String S_ProcName = "updateServiceType";
ResultSet resultSet = null;
try {
int ServiceTypeId = Buff.getRequiredServiceTypeId();
String Description = Buff.getRequiredDescription();
int Revision = Buff.getRequiredRevision();
Connection cnx = schema.getCnx();
String sql = "exec sp_update_svctype ?, ?, ?, ?, ?, ?" + ", " + "?" + ", " + "?" + ", " + "?";
if (stmtUpdateByPKey == null) {
stmtUpdateByPKey = cnx.prepareStatement(sql);
}
int argIdx = 1;
stmtUpdateByPKey.setLong(
argIdx++, (Authorization == null) ? 0 : Authorization.getSecClusterId());
stmtUpdateByPKey.setString(
argIdx++, (Authorization == null) ? "" : Authorization.getSecUserId().toString());
stmtUpdateByPKey.setString(
argIdx++, (Authorization == null) ? "" : Authorization.getSecSessionId().toString());
stmtUpdateByPKey.setLong(
argIdx++, (Authorization == null) ? 0 : Authorization.getSecClusterId());
stmtUpdateByPKey.setLong(
argIdx++, (Authorization == null) ? 0 : Authorization.getSecTenantId());
stmtUpdateByPKey.setString(argIdx++, "SVCT");
stmtUpdateByPKey.setInt(argIdx++, ServiceTypeId);
stmtUpdateByPKey.setString(argIdx++, Description);
stmtUpdateByPKey.setInt(argIdx++, Revision);
resultSet = stmtUpdateByPKey.executeQuery();
if (resultSet.next()) {
CFSecurityServiceTypeBuff updatedBuff = unpackServiceTypeResultSetToBuff(resultSet);
if (resultSet.next()) {
resultSet.last();
throw CFLib.getDefaultExceptionFactory()
.newRuntimeException(
getClass(),
S_ProcName,
"Did not expect multi-record response, " + resultSet.getRow() + " rows selected");
}
Buff.setRequiredDescription(updatedBuff.getRequiredDescription());
Buff.setRequiredRevision(updatedBuff.getRequiredRevision());
} else {
throw CFLib.getDefaultExceptionFactory()
.newRuntimeException(
getClass(),
S_ProcName,
"Expected a single-record response, " + resultSet.getRow() + " rows selected");
}
} catch (SQLException e) {
throw CFLib.getDefaultExceptionFactory().newDbException(getClass(), S_ProcName, e);
} finally {
if (resultSet != null) {
try {
resultSet.close();
} catch (SQLException e) {
}
resultSet = null;
}
}
}
public List<DataTransferObject> get(DataTransferObject record, List<String> fieldNames)
throws SQLException, Exception {
checkConnection();
// Connection conn = DButil.getConnection();
SQLPlaceholder placeholder = getValuesFromDTO(record, fieldNames, false);
String sql = "SELECT * FROM " + TABLE_NAME;
if (!placeholder.createWhere().equals("")) {
sql += " WHERE " + placeholder.createWhere();
}
List<DataTransferObject> records = new ArrayList<DataTransferObject>();
PreparedStatement stmt = null;
ResultSet result = null;
try {
stmt = conn.prepareStatement(sql);
placeholder.replacePlaceholders(stmt);
prglog.debug("[PRG] selection SQL: " + stmt.toString());
lastSQL = stmt.toString();
result = stmt.executeQuery();
result.last();
int rowcount = result.getRow();
prglog.debug("[PRG] number of rows: " + rowcount);
result.beforeFirst();
while (result.next()) {
records.add(result2record(result, record.getClass().newInstance()));
}
} finally {
if (result != null) result.close();
if (stmt != null) stmt.close();
}
// DButil.closeConnection(conn);
return records;
}
public int miseAJourStatutCommande() {
ConnexionBD bd = new ConnexionBD();
Statement st = bd.createStatement();
int rs = 0;
try {
// on regarde combie de colis à la commande correspondant au colis controle
ResultSet i =
st.executeQuery(
"SELECT id_commande, count(id_colis) FROM "
+ Constantes.base_colis
+ " WHERE id_colis = "
+ id
+ " GROUP BY id_commande;");
if (i.next()) {
int numCommande = i.getInt(1);
int nbColis = i.getInt(2);
// i.close();
// on récupère les colis donc le statut à été controle, cad passer par la douane et approuvé
ResultSet g =
st.executeQuery(
" SELECT id_colis FROM "
+ Constantes.base_colis
+ " WHERE id_commande = "
+ numCommande
+ " AND statut IN ('controle', 'emballe') GROUP BY id_colis;");
if (g.next()) {
g.last();
int nombreColis = g.getRow();
g.beforeFirst();
// si le nombre de colis de la commande est égal au nombre de colis controle de la
// commande, on peut changer le statut de la commande a expedie
if (nbColis == nombreColis) {
rs =
st.executeUpdate(
"UPDATE "
+ Constantes.base_commande
+ " SET statut = 'expediee' WHERE id_commande = "
+ numCommande
+ ";");
}
} else {
System.out.println("NOOOON");
}
} else {
System.out.println("RIEEEEN");
}
} catch (SQLException e) {
e.printStackTrace();
}
return rs;
}
/**
* @param id
* @return 会话id数组 TODO 查询用户所有的会话
*/
public static int[] queryAllConv(int id) {
int[] convids = null;
Connection conn = (Connection) (new DBConnection()).getConnector();
Statement statement;
ResultSet rs;
try {
statement = conn.createStatement();
rs = statement.executeQuery("SELECT convid from room where id='" + id + "'");
int i = 0;
rs.last();
int length = rs.getRow();
rs.beforeFirst();
convids = new int[length];
while (rs.next()) {
convids[i] = rs.getInt("convid");
i++;
}
rs.close();
statement.close();
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
return convids;
}
// set parameters in sql, and return integer set such as eventID set or contributorId set
public Set<Integer> getResultfromDB(String sql, int ID) {
Set<Integer> infoSet = new HashSet<Integer>();
int[] param = {ID};
// execute sql statement
ResultSet results = exePreparedStatement(sql, param);
try {
// check to see that data was returned
if (!results.last()) {
tidyup();
return null;
} else results.beforeFirst();
// build the list of contributors
while (results.next() == true) {
if (results.getInt(1) != 0) // if returned result is NULL, then getInt(1) = 0
infoSet.add(results.getInt(1));
}
} catch (java.sql.SQLException ex) {
System.out.println("Exception: " + ex.getMessage());
results = null;
}
tidyup();
return infoSet;
}
private void GenerateAdminNoActionPerformed(
java.awt.event.ActionEvent evt) { // GEN-FIRST:event_GenerateAdminNoActionPerformed
int IntAdminNo = 0;
String prodID;
try {
Class.forName("com.mysql.jdbc.Driver");
Connection con =
DriverManager.getConnection("jdbc:mysql://localhost:3306/VunaFeeds", "root", "");
Statement stmt = con.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
ResultSet rs = stmt.executeQuery("Select * from product where Status = 'Active'");
if (rs.last()) {
String adminNo = rs.getString("ProductID");
IntAdminNo = Integer.valueOf(adminNo);
System.out.println(IntAdminNo);
IntAdminNo++;
prodID = String.valueOf(IntAdminNo);
ProductID.setText(prodID);
} else {
IntAdminNo = 1;
prodID = String.valueOf(IntAdminNo);
ProductID.setText(prodID);
}
ProductID.setEditable(false);
} catch (SQLException e) {
System.out.println("2 Error : " + e);
JOptionPane.showMessageDialog(null, "Oops!! An error occured. \n " + e);
} catch (Exception ex) {
System.out.println("Error 1:" + ex);
JOptionPane.showMessageDialog(null, "Oops!! An error occured. \n" + ex);
}
} // GEN-LAST:event_GenerateAdminNoActionPerformed
/** 获得组内所有的用户 */
public User[] getUsers(String twhere) {
String strSql;
try {
strSql = "select strUserId,strMobile,strName from " + strTableName + " ".concat(twhere);
ResultSet rs = db.executeRollQuery(strSql);
if (rs.last()) {
int total = rs.getRow();
rs.beforeFirst();
User[] users = new User[total];
int i = 0;
while (rs.next()) {
users[i] =
new User(
rs.getString("strUserId"), rs.getString("strMobile"), rs.getString("strName"));
i++;
}
rs.close();
return users;
} else {
rs.close();
return null;
}
} catch (Exception e) {
e.printStackTrace();
return null;
}
}
public int searchGovernmentFileCount(SearchDataCondition condition) throws SQLException {
String sql = getquerySql(queryGovernmentFileCountSQL, condition, false);
ResultSet result = getQueryResult(sql, condition, false);
result.last();
int results = result.getInt(result.getRow());
return results;
}
public Bus[] getBuses(Line line) throws SQLException {
Statement st =
conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
ResultSet rs =
st.executeQuery(
String.format(
"SELECT bus.* FROM public.bus, public.lines_has_buses, public.line WHERE lines_has_buses.bus_id = bus.id AND line.id = lines_has_buses.line_id AND line.id = %d;",
line.getId()));
rs.last();
Bus buses[] = new Bus[rs.getRow()];
rs.beforeFirst();
while (rs.next()) {
buses[rs.getRow() - 1] =
new Bus(
rs.getString("code"),
rs.getInt("capacity"),
rs.getString("maker"),
rs.getString("model"),
rs.getString("bus_type"));
buses[rs.getRow() - 1].setId(rs.getInt("id"));
}
return buses;
}
public ArrayList<Orientacao> getBy(String SQL) {
Connection conn = new Conn().getConnection();
ArrayList<Orientacao> lo = new ArrayList<Orientacao>();
try {
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(SQL);
Orientacao o = new Orientacao();
while (rs.next()) {
o.setAluno_id(rs.getInt("aluno_id"));
o.setProfessor_id(rs.getInt("professor_id"));
o.setAno(rs.getInt("ano"));
lo.add(o);
}
rs.last();
} catch (SQLException e) {
System.out.println("Erro no SQL");
}
return lo;
}
@SuppressWarnings("deprecation")
@Override
public Future<int[]> findAll(int playerId) {
return worker.submit(
() -> {
try (Connection connection = dataSource.getConnection();
PreparedStatement statement =
connection.prepareStatement(FIND_NOTIFS_BY_PLAYER_SQL)) {
statement.setInt(1, playerId);
try (ResultSet resultSet = statement.executeQuery()) {
if (!resultSet.last()) {
return new int[0];
}
int[] ids = new int[resultSet.getRow() + 1];
resultSet.beforeFirst();
for (int i = 0; i < ids.length && resultSet.next(); i++) {
ids[i] = resultSet.getInt(1);
}
return ids;
}
}
});
}
private List<Appointment> genericFindAppointments(
Connection conn, PreparedStatement findStatement) {
try {
ResultSet rs = findStatement.executeQuery();
int nrRows = 0;
if (rs.last()) {
nrRows = rs.getRow();
}
rs.beforeFirst();
ArrayList<Appointment> apps = new ArrayList<Appointment>(nrRows);
while (rs.next()) {
apps.add((Appointment) load(rs));
}
conn.close();
return apps;
} catch (SQLException e) {
try {
conn.close();
} catch (SQLException se) {
System.out.println("An SQL exceptions occured: " + se.getMessage());
}
System.out.println("An SQL exceptions occured: " + e.getMessage());
return null;
}
}
/**
* @param convid
* @return clientid数组 TODO 获取聊天室中所有的clientid
*/
public static String[] ChatersInConv(int convid) {
String[] clientids = null;
Connection conn = (Connection) (new DBConnection()).getConnector();
Statement statement;
ResultSet rs;
try {
statement = conn.createStatement();
rs =
statement.executeQuery(
"SELECT clientid from room r,user u where r.id=u.id and convid='" + convid + "'");
int i = 0;
rs.last();
int length = rs.getRow();
rs.beforeFirst();
clientids = new String[length];
while (rs.next()) {
clientids[i] = rs.getString("clientid");
i++;
}
rs.close();
statement.close();
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
return clientids;
}
/**
* Takes a columnName, a tableName and a userID, and does "SELECT colName FROM table WHERE
* table.`uid` = userID".
*
* @param colName
* @param table
* @param uid
* @return The first double from the ResultSet of the query. -1.0 if failed.
*/
public double selectSingleDoubleByUID(String colName, String table, int uid) {
// Make the statement.
String stmt =
"SELECT `" + colName + "` FROM `" + table + "` WHERE `" + table + "`.`uid` = " + uid + ";";
// Do the query.
try {
Statement dbStmt = dbConnection.createStatement();
ResultSet dbResultSet = null;
if (dbStmt.execute(stmt)) {
dbResultSet = dbStmt.getResultSet();
dbResultSet.last();
return dbResultSet.getDouble(1);
} else {
System.err.println("CustomCommunication.selectSingleByUID failed");
}
return -1.0;
} catch (SQLException ex) {
System.out.println("SQLException: " + ex.getMessage());
System.out.println("SQLState: " + ex.getSQLState());
System.out.println("VendorError: " + ex.getErrorCode());
return -1.0;
}
}
public int[] checkTimeStamp() {
int index = 0;
int count = 0;
int[] id = null;
try {
System.out.println("Checking timestamp now");
stmt =
con.prepareStatement(
"SELECT keyword_id FROM keyword_timestamp WHERE search_time > publish_time;");
updatestmt =
con.prepareStatement(
"UPDATE keyword_timestamp SET publish_time = search_time+1 WHERE search_time > publish_time;");
stmt.executeQuery();
// updatestmt.executeUpdate();
rs = stmt.getResultSet();
rs.last();
id = new int[rs.getRow()];
rs.beforeFirst();
while (rs.next()) {
id[index] = rs.getInt("keyword_id");
System.out.println("Record found...." + id[index]);
index++;
}
rs.close();
stmt.close();
} catch (SQLException ex) {
}
return id;
}
/**
* Attempts to add a new shipment to the table
*
* @param dto - the shipment to add to the database
* @return boolean value for whether the shipment was added
*/
public boolean newShipment(ShipmentDTO dto) {
boolean success;
String sqlQ =
"INSERT INTO gunnargo_cmsc495.Shipment SET "
+ "ItemID = '"
+ dto.getItemID()
+ "', "
+ "CustID = '"
+ dto.getCustID()
+ "', "
+ "Destination = '"
+ dto.getDestination()
+ "', "
+ "Weight = '"
+ dto.getWeight()
+ "', "
+ "NumItems = '"
+ dto.getNumItems()
+ "';";
try {
con = DriverManager.getConnection(url, userid, password);
Statement stmt = con.createStatement();
stmt.execute(sqlQ);
ResultSet rs = stmt.executeQuery("SELECT * FROM gunnargo_cmsc495.Shipment");
if (rs.last()) dto.setShipID(rs.getString("ShipID"));
success = !dto.getShipID().equals("");
con.close();
} catch (SQLException ex) {
System.out.println(ex.getMessage());
success = false;
}
return success;
}
public int[] searchVideo(String key) {
int i = 0;
int j = 0;
int[] ans = null;
String str = "SELECT * FROM video WHERE name LIKE '%" + key + "%'";
try {
row = stm.executeQuery(str);
row.last();
i = row.getRow();
row.first();
if (i == 0) {
ans = new int[1];
} else {
ans = new int[i];
}
if (i > 0) {
do {
ans[j] = Integer.parseInt(row.getString("id"));
j++;
} while (row.next());
} else {
ans[0] = 0;
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return ans;
}
// 查询评级数据
public static int[] getRatingResultCompanyByLevel(String level) {
int[] RatingResult = null;
int i = 0;
try {
Class.forName(driver);
conn = DriverManager.getConnection(url, "root", "root");
String sql = "select customerId from ratingResult where ratingLevel='" + level + "'";
System.out.println(sql);
Statement st = conn.createStatement();
ResultSet rs = st.executeQuery(sql);
if (rs != null) {
rs.last();
RatingResult = new int[rs.getRow()];
rs.beforeFirst();
while (rs != null && rs.next()) {
RatingResult[i] = rs.getInt(1);
i++;
}
}
rs.close();
st.close();
conn.close();
} catch (Exception e) {
e.printStackTrace();
}
return RatingResult;
}
public String[] getVideo() {
int i = 0;
int j = 0;
String[] ans = null;
String str = "SELECT * FROM video ORDER BY id";
try {
row = stm.executeQuery(str);
row.last();
i = row.getRow();
row.first();
ans = new String[i + 1];
if (i > 0) {
ans[0] = Integer.toString(i);
do {
j++;
ans[j] = row.getString("name");
} while (row.next());
} else {
ans[0] = "0";
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return ans;
}
@Override
public int getCount(String key, int state) throws AppException {
int rowCount = 0;
Connection conn = null;
PreparedStatement psmt = null;
ResultSet rs = null;
try {
// 创建数据库连接
conn = DBUtil.getConnection();
String sql = "select * " + "from t_news where state=? and del=0 and keywords like ?";
// 设置从参数，进行查询
// 预编译sql，并设置参数
psmt = conn.prepareStatement(sql);
psmt.setInt(1, state); // 设置新闻状态
psmt.setString(2, "%" + key + "%");
// 执行查询
rs = psmt.executeQuery(); // 执行查询操作
// 提取信息，保存到newsList中
rs.last(); // 移到最后一行
rowCount = rs.getRow(); // 得到当前行号，也就是记录数
} catch (Exception e) {
e.printStackTrace();
} finally {
// 关闭数据库操作对象，释放资源
DBUtil.closeResultSet(rs);
DBUtil.closePreparedStatement(psmt);
DBUtil.closeConnection(conn);
}
return rowCount;
}
/**
* This method collects payment for the current transaction.
*
* @param amount the amount of money collected from the customer.
* @param payment no idea what this is (TODO: ask about class diagram)
* @return no idea what this is suppose to return.
* @throws SQLException if sql error.
* @throws ClassNotFoundException if sql error.
* @throws IllegalStateException if there are no items on the invoice.
*/
public double pay(String paymentMethod, int amount)
throws IllegalStateException, SQLException, ClassNotFoundException, Exception {
if (myTransaction.getNumberOfItems() == 0) {
throw new IllegalStateException("There are no items on the Invoice.");
}
JDBCConnection JDBC = new JDBCConnection();
Statement st = JDBC.createStatement();
String table = "invoice";
String column = "invoiceID";
String SQL = "SELECT " + column + " FROM " + table + " GROUP BY " + column;
ResultSet rs = st.executeQuery(SQL);
// rs.last();
int nextInvoiceID = 0;
if (rs.last()) {
int LastID = rs.getInt(column);
nextInvoiceID = LastID + 1;
}
// use these two lines if you want invoices to be numbered 1000 and up
// int starting = 999;
// int nextInvoiceID = mySQLhelper.getTotalNumberOfInvoices() + 1;
// int nextInvoiceID = mySQLhelper.getTotalNumberOfRows(SQLhelper.TRANSACTION_TABLE_NAME,
// SQLhelper.TRANSACTION_TABLE_PK) + 1;
// Payment myPayment = new Payment(amount, paymentMethod);
double change = myTransaction.markPaid(paymentMethod, amount, nextInvoiceID);
myTransaction.UpdateItem();
process();
return change;
}
public void testTemp() throws Exception {
Statement stmt =
con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
stmt.execute("CREATE TABLE #temp ( pk INT PRIMARY KEY, f_string VARCHAR(30), f_float FLOAT )");
// populate in the traditional way
for (int i = 0; i < 100; i++) {
stmt.execute(
"INSERT INTO #temp " + "VALUES( " + i + "," + "'The String " + i + "'" + ", " + i + ")");
}
dump(stmt.executeQuery("SELECT Count(*) FROM #temp"));
// Navigate around
ResultSet rs = stmt.executeQuery("SELECT * FROM #temp");
rs.first();
dumpRow(rs);
rs.last();
dumpRow(rs);
rs.first();
dumpRow(rs);
stmt.execute("DROP TABLE #temp");
stmt.close();
}
public void createTrack() {
java.sql.Connection conn = null;
try {
conn = WinConnectionUtil.getAccessDBConnection(config.getString("access.src", ""));
Statement stmt1 =
conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
Statement stmt2 = myConn.createStatement();
String cmd1 =
"SELECT [tDiscTrack.TrackID], [tDiscTrack.TrackNo], [tDiscTrack.TrackNoIndex], [tDiscTrack.DiscID], [tTrack.DurationAcronym] "
+ "FROM tDiscTrack INNER JOIN tTrack ON tDiscTrack.TrackID = tTrack.TrackID;";
if (log.isDebugEnabled()) log.debug("accessSQL1:" + cmd1);
ResultSet rs1 = stmt1.executeQuery(cmd1);
rs1.last();
int rowCount1 = rs1.getRow();
if (rowCount1 == 0) {
if (log.isWarnEnabled()) log.warn("Get no access info.");
return;
}
rs1.beforeFirst();
while (rs1.next()) {
int id = rs1.getInt("TrackID");
String cmd2 =
String.format(
"INSERT INTO TrackMap (TrackID, TrackNo, TrackNoIndex, TrackDesc, DiscID, CDID, ItemID, ItemFlow, ItemIdx, Note) VALUES (%1$d,%2$d,%3$d,'%4$s',%5$d,'',0,0,0,'');",
id,
rs1.getInt("TrackNo"),
rs1.getInt("TrackNoIndex"),
rs1.getString("DurationAcronym"),
rs1.getInt("DiscID"));
// System.out.println(cmd2);
if (stmt2.executeUpdate(cmd2) == 0) {
if (log.isWarnEnabled()) log.warn("[DataExist] TrackID:" + id);
} else {
if (log.isInfoEnabled()) log.info("[DataInsert] TrackID:" + id);
}
}
rs1.close();
stmt1.close();
stmt2.close();
} catch (SQLException s) {
if (log.isErrorEnabled()) log.error(s);
} catch (Exception e) {
if (log.isErrorEnabled()) log.error(e);
} finally {
if (conn != null) {
try {
conn.close();
} catch (SQLException ignore) {
}
}
}
}
