public static void queryBookAuthor(Connection conn) {
RowSetFactory factory;
try {
// Create a new RowSetFactory
factory = RowSetProvider.newFactory();
// Create a CachedRowSet object using the factory
bookAuthors = factory.createCachedRowSet();
// Alternatively opulate the CachedRowSet connection settings
// crs.setUsername(createConn.getUsername());
// crs.setPassword(createConn.getPassword());
// crs.setUrl(createConn.getJdbcUrl());
// Populate a query that will obtain the data that will be used
bookAuthors.setCommand("SELECT ID, LASTNAME, FIRSTNAME FROM BOOK_AUTHOR");
bookAuthors.execute(conn);
// You can now work with the object contents in a disconnected state
while (bookAuthors.next()) {
System.out.println(
bookAuthors.getString(1)
+ ": "
+ bookAuthors.getString(2)
+ ", "
+ bookAuthors.getString(3));
}
} catch (SQLException ex) {
ex.printStackTrace();
}
}
public static void customLoad(ArrayList list, String db, String sqry) throws Exception {
BigliettoTreno model;
for (CachedRowSet crs = Query.select(db, sqry); crs.next(); list.add(model)) {
model = new BigliettoTreno();
populate(model, crs);
}
}
public static List<NewsPoll> selectAll() throws FtdException {
List<NewsPoll> newsPolls = new ArrayList<NewsPoll>();
DBClient dbClient = SysMgr.getInstance().getDbClient();
String sql = "select * from news_poll";
CachedRowSet rs = null;
try {
rs = dbClient.executeQuery(sql);
if (rs != null) {
while (rs.next()) {
NewsPoll np = new NewsPoll();
np.setNewsId(rs.getInt("news_id"));
np.setNewsTitle(rs.getString("news_title"));
np.setPollImgUrl(rs.getString("poll_img_url"));
np.setArticleId(rs.getInt("article_id"));
np.setActive(rs.getInt("is_active"));
newsPolls.add(np);
}
}
} catch (SQLException e) {
throw new FtdException(e, "db.sql.error");
} finally {
if (rs != null)
try {
rs.close();
} catch (SQLException e) {
// do nothing
}
}
return newsPolls;
}
public static void main(String[] args) throws Exception {
CachedRowSetPage cp = new CachedRowSetPage();
cp.initParam("mysql.ini");
CachedRowSet rs = cp.query("select * from student_table", 3, 2); // ①
// 向后滚动结果集
while (rs.next()) {
System.out.println(rs.getString(1) + "\t" + rs.getString(2) + "\t" + rs.getString(3));
}
}
public static void load(BigliettoTreno model, String db, String id) throws Exception {
ArrayList params = new ArrayList();
params.add(id);
CachedRowSet crs = Query.select(LOAD_BIGLIETTO_TRENO, params, db);
if (crs.next()) {
populate(model, crs);
}
}
public void search(Search searchBean) {
SearchDaoFacade searchDao = new SearchDaoImpl();
searchResult = searchDao.search(searchBean);
try {
searchBean.getRideList().clear();
while (searchResult.next()) {
Ride tempRide = new Ride();
Person tempPerson = new Person();
PersonAddress tempAddress = new PersonAddress();
tempAddress.setId(searchResult.getInt("ADDRESS_ID"));
tempAddress.setStreet(searchResult.getString("STREET"));
tempAddress.setCity(searchResult.getString("CITY"));
tempAddress.setState(searchResult.getString("STATE"));
tempAddress.setCountry(searchResult.getString("COUNTRY"));
tempAddress.setZip(searchResult.getString("ZIP"));
tempPerson.setId(searchResult.getInt("PERSON_ID"));
tempPerson.setFirstName(searchResult.getString("FIRST_NAME"));
tempPerson.setLastName(searchResult.getString("LAST_NAME"));
tempPerson.setSex(searchResult.getString("SEX"));
tempPerson.setAge(searchResult.getInt("AGE"));
tempPerson.setPhone(searchResult.getString("PHONE"));
tempPerson.setEmail(searchResult.getString("EMAIL"));
tempPerson.setPassword(searchResult.getString("PASSWORD"));
if (null != searchResult.getBlob("AVATAR")) {
InputStream bs = searchResult.getBlob("AVATAR").getBinaryStream();
DefaultStreamedContent dsc = new DefaultStreamedContent(bs);
tempPerson.setPhoto(dsc);
}
tempPerson.setAddress(tempAddress);
tempRide.setId(searchResult.getInt("ID"));
tempRide.setSource(searchResult.getString("SOURCE"));
tempRide.setDestination(searchResult.getString("DESTINATION"));
tempRide.setDepartDate(DateUtil.utilDate(searchResult.getDate("DEPART_DATE")));
tempRide.setDepartTime(searchResult.getString("DEPART_TIME"));
tempRide.setReturnDate(DateUtil.utilDate(searchResult.getDate("RETURN_DATE")));
tempRide.setReturnTime(searchResult.getString("RETURN_TIME"));
tempRide.setDescription(searchResult.getString("DESCRIPTION"));
tempRide.setCapacity(searchResult.getInt("CAPACITY"));
tempRide.setVehicleDescription(searchResult.getString("VEHICLE_DESCRIPTION"));
tempRide.setExpectedExpense(searchResult.getBigDecimal("EXPECTED_EXPENSE"));
tempRide.setRideType(searchResult.getString("RIDE_TYPE"));
tempRide.setPerson(tempPerson);
searchBean.getRideList().add(tempRide);
}
} catch (SQLException e) {
e.printStackTrace();
}
}
public ArrayList<Avion> recuperaPorFiltro(String filtro) {
String sql = "SELECT * FROM aviones WHERE ";
sql += filtro == null || filtro.length() == 0 ? "1" : filtro;
sql += " ORDER BY aviones.av_id";
ArrayList<Avion> lista = null;
CachedRowSet rs = consultaSQL(sql);
if (rs != null) {
try {
lista = new ArrayList<>();
while (rs.next() == true) {
lista.add(
new Avion(rs.getInt("av_id"), rs.getString("av_modelo"), rs.getInt("av_capacidad")));
}
} catch (SQLException e) {
e.printStackTrace();
}
}
return lista;
}
/**
* Review all users for the given day, and age those that have outstanding invoices over the set
* number of days for an ageing step.
*
* @param steps ageing steps
* @param today today's date
* @param executorId executor id
*/
public void reviewAllUsers(
Integer entityId, Set<AgeingEntityStepDTO> steps, Date today, Integer executorId) {
LOG.debug("Reviewing users for entity " + entityId + " ...");
// go over all the users already in the ageing system
for (UserDTO user : new UserDAS().findAgeing(entityId)) {
ageUser(steps, user, today, executorId);
}
// go over the active users with payable invoices
try {
UserDAS userDas = new UserDAS();
InvoiceDAS invoiceDas = new InvoiceDAS();
CachedRowSet users = new UserBL().findActiveWithOpenInvoices(entityId);
while (users.next()) {
Integer userId = users.getInt(1);
UserDTO user = userDas.find(userId);
int gracePeriod = getGracePeriod(entityId);
LOG.debug(
"Reviewing invoices for user "
+ user.getId()
+ " using a grace period of "
+ gracePeriod
+ " days.");
for (InvoiceDTO invoice : invoiceDas.findProccesableByUser(user)) {
if (isInvoiceOverdue(invoice, user, gracePeriod, today)) {
ageUser(steps, user, today, executorId);
break;
}
}
}
} catch (SQLException e) {
LOG.error("Failed to fetch users with payable invoices.", e);
} catch (NamingException e) {
LOG.error("Exception fetching users with payable invoices.", e);
}
}
@Test
public void testGetInfoRowSet() {
CachedRowSet rws;
try {
rws = new CachedRowSetImpl();
rws.setCommand(ALLEGATO5_FIND_BYPK);
rws.setInt(1, 6);
rws.execute(conn);
rws.next();
allegato5 = Allegato5Assembler.getInfo(rws);
rws.close();
db.closeConnection(conn);
} catch (SQLException e) {
fail(e.getMessage());
} catch (DBAccessException e) {
fail(e.getMessage());
}
}
public static void queryAuthorWork(Connection conn) {
RowSetFactory factory;
try {
// Create a new RowSetFactory
factory = RowSetProvider.newFactory();
// Create a CachedRowSet object using the factory
authorWork = factory.createCachedRowSet();
// Alternatively opulate the CachedRowSet connection settings
// crs.setUsername(createConn.getUsername());
// crs.setPassword(createConn.getPassword());
// crs.setUrl(createConn.getJdbcUrl());
// Populate a query that will obtain the data that will be used
authorWork.setCommand("SELECT ID, AUTHOR_ID, BOOK_ID FROM AUTHOR_WORK");
authorWork.execute(conn);
// You can now work with the object contents in a disconnected state
while (authorWork.next()) {
System.out.println(
authorWork.getString(1) + ": " + authorWork.getInt(2) + " - " + authorWork.getInt(3));
}
} catch (SQLException ex) {
ex.printStackTrace();
}
}
public static void customLoad(BigliettoTreno model, String db, String sqry) throws Exception {
CachedRowSet crs = Query.select(db, sqry);
if (crs.next()) {
populate(model, crs);
}
}
