@Override
public LiveDetailSale findSaleById(int id) {
// 根据id获取购买信息
dbConn = JdbcUtil.connSqlServer();
try {
sta = dbConn.createStatement();
res = sta.executeQuery("SELECT * FROM JCP_LiveDetailSale WHERE Id=" + id);
while (res.next()) {
int userId = res.getInt(2); // FK_UserId
int teacherId = res.getInt(3); // FK_TearchId
String orderCode = res.getString(4); // OrderCode
int liveDetailId = res.getInt(5); // FK_LiveDetailId
String insertDate = res.getString(6); // InsertDate
LiveDetailSale sale = new LiveDetailSale();
sale.setUserId(userId);
sale.setTeacherId(teacherId);
sale.setOrderCode(orderCode);
sale.setLiveDetailId(liveDetailId);
sale.setInsertDate(insertDate);
return sale;
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
JdbcUtil.closeConn(sta, dbConn, res);
} catch (SQLException e) {
e.printStackTrace();
}
}
return null;
}
@Override
public List<LiveDetailSale> findSaleByUid(int uId, int page) {
// 根据用户id获取购买信息
sales.clear();
int totlePage = getTotlePage("WHERE FK_UserId=" + uId);
dbConn = JdbcUtil.connSqlServer();
try {
sta = dbConn.createStatement();
res =
sta.executeQuery(
"SELECT TOP 15 * FROM "
+ "(SELECT ROW_NUMBER() OVER (ORDER BY InsertDate desc) AS RowNumber,* FROM JCP_LiveDetailSale WHERE FK_UserId="
+ uId
+ ") A "
+ "WHERE RowNumber > "
+ 15 * (page - 1));
while (res.next()) {
int id = res.getInt("Id");
int teacherId = res.getInt("FK_TearchId"); // FK_TearchId
String orderCode = res.getString("OrderCode"); // OrderCode
int liveDetailId = res.getInt("FK_LiveDetailId"); // FK_LiveDetailId
String insertDate = res.getString("InsertDate"); // InsertDate
LiveDetailSale sale = new LiveDetailSale();
sale.setUserId(uId);
sale.setId(id);
sale.setTotlePage(totlePage);
sale.setPage(page);
sale.setTeacherId(teacherId);
sale.setOrderCode(orderCode);
sale.setLiveDetailId(liveDetailId);
sale.setInsertDate(insertDate);
sales.add(sale);
}
return sales;
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
JdbcUtil.closeConn(sta, dbConn, res);
} catch (SQLException e) {
e.printStackTrace();
}
}
return null;
}