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 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));
}
}
protected static void populate(BigliettoTreno model, CachedRowSet crs) throws Exception {
model.setCodice(crs.getString("vtb_codice"));
model.setTrasferimento(crs.getString("vtb_viaggio"));
model.getPasseggero().setCodicePartecipante(crs.getString("vtb_passeggero"));
model.getClasse().setCodice(crs.getString("vtb_classe"));
model.setTariffa(new Currency(crs.getString("vtb_tariffa")));
PartecipanteDto.populate(model.getPasseggero(), crs);
TipoPostoTrenoDto.populate(model.getClasse(), crs);
}
/**
* @throws SQLException
* @tests java.sql.rowset.joinRowSet#getRowSets()
*/
public void testGetRowSets_SingleJdbcRowSet() throws Exception {
// Creates jdbc rowset.
JdbcRowSet jdbcRs;
jdbcRs = newJdbcRowSet();
jdbcRs.setCommand("SELECT * FROM USER_INFO");
jdbcRs.setUrl(DERBY_URL);
jdbcRs.execute();
jdbcRs.setMatchColumn(1);
jrs.addRowSet(jdbcRs);
CachedRowSet returnRowset = (CachedRowSet) jrs.getRowSets().iterator().next();
assertNotSame(returnRowset, jrs);
jrs.absolute(4);
jrs.updateString(2, "Updated 4");
jrs.updateRow();
jrs.absolute(4);
assertEquals("Updated 4", jrs.getString(2));
// TODO It is Strange. According to spec, the returned rowset should
// maintain
// the update occured in the joinrowset.
returnRowset.absolute(4);
assertEquals("test4", returnRowset.getString(2));
jdbcRs.absolute(3);
jdbcRs.updateString(2, "Updated 3");
jdbcRs.updateRow();
returnRowset.absolute(3);
assertEquals("test3", returnRowset.getString(2));
jdbcRs.close();
}
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;
}
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 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();
}
}
