public static List<PriceBar> getPriceDatas(
String sym,
java.util.Date beginDT,
java.util.Date endDT,
int priceMagnifier,
int multiplier) {
List<PriceBar> priceDatas = new ArrayList<PriceBar>();
try {
PreparedStatement datedRangeBySymbol =
DBopsMySql.datedRangeBySymbol(
sym, new Timestamp(beginDT.getTime()), new Timestamp(endDT.getTime()));
ResultSet res = datedRangeBySymbol.executeQuery();
while (res.next()) {
PriceBar priceBar =
new PriceBar(
res.getTimestamp("datetime").getTime(),
res.getDouble("open") / priceMagnifier * multiplier,
res.getDouble("high") / priceMagnifier * multiplier,
res.getDouble("low") / priceMagnifier * multiplier,
res.getDouble("close") / priceMagnifier * multiplier,
res.getLong("volume"));
priceDatas.add(priceBar);
}
// int i = 1;
} catch (SQLException ex) {
MsgBox.err2(ex);
} catch (Exception ex) {
MsgBox.err2(ex);
} finally {
return priceDatas;
}
}
/**
* 获取游戏接口参数
* @param rs ResultSet
* @return
*/
protected static wh.game.model.GameInterfaceParams getModel(ResultSet rs) throws SQLException{
wh.game.model.GameInterfaceParams m = null;
if (rs != null)
{
m = new wh.game.model.GameInterfaceParams();
try{
m.setGameInterfaceParamsId(rs.getInt("GameInterfaceParamsId"));
wh.game.model.GameInterface gameInterface = new wh.game.model.GameInterface();
gameInterface.setGameInterfaceId(rs.getInt("GameInterface_GameInterfaceId"));
gameInterface.setGame(new wh.game.model.Game());
gameInterface.getGame().setGameId(rs.getInt("GameInterface_GameId"));
gameInterface.setName(rs.getString("GameInterface_Name"));
gameInterface.setUrl(rs.getString("GameInterface_Url"));
gameInterface.setInterfaceType(rs.getByte("GameInterface_InterfaceType"));
gameInterface.setPayUnitType(rs.getByte("GameInterface_PayUnitType"));
gameInterface.setCreateDate(rs.getTimestamp("GameInterface_CreateDate"));
gameInterface.setStatus(rs.getByte("GameInterface_Status"));
gameInterface.setSignJoinSymbol(rs.getString("GameInterface_SignJoinSymbol"));
m.setGameInterface(gameInterface);
wh.game.model.Game game = new wh.game.model.Game();
game.setGameId(rs.getInt("Game_GameId"));
game.setProvider(new wh.game.model.Provider());
game.getProvider().setProviderId(rs.getInt("Game_ProviderId"));
game.setManager(new wh.member.model.Manager());
game.getManager().setManagerId(rs.getInt("Game_ManagerId"));
game.setGameCategory(new wh.game.model.GameCategory());
game.getGameCategory().setGameCategoryId(rs.getInt("Game_GameCategoryId"));
game.setGameName(rs.getString("Game_GameName"));
game.setPosterPath(rs.getString("Game_PosterPath"));
game.setCreateDate(rs.getTimestamp("Game_CreateDate"));
game.setGameType(rs.getByte("Game_GameType"));
game.setEnableType(rs.getByte("Game_EnableType"));
game.setStatus(rs.getByte("Game_Status"));
game.setGameMoneyType(rs.getByte("Game_GameMoneyType"));
game.setGameMoneyRate(rs.getInt("Game_GameMoneyRate"));
game.setContent(rs.getString("Game_Content"));
game.setRecommendType(rs.getByte("Game_RecommendType"));
game.setHomeUrl(rs.getString("Game_HomeUrl"));
game.setCPosterPath(rs.getString("Game_CPosterPath"));
game.setSPosterPath(rs.getString("Game_SPosterPath"));
game.setLPosterPath(rs.getString("Game_LPosterPath"));
m.setGame(game);
m.setParamName(rs.getString("ParamName"));
m.setParamValue(rs.getString("ParamValue"));
m.setParamExplain(rs.getString("ParamExplain"));
m.setSignType(rs.getByte("SignType"));
m.setSignIndex(rs.getByte("SignIndex"));
m.setParamType(rs.getByte("ParamType"));
m.setParamInType(rs.getByte("ParamInType"));
m.setParamOutType(rs.getByte("ParamOutType"));
m.setSignFormatValue(rs.getString("SignFormatValue"));
}catch (SQLException e) {
throw new SQLException(e.getMessage(),e);
}
}
return m;
}
/**
* Populates this object from a resultset
*
* @param rs Description of the Parameter
* @throws SQLException Description of the Exception
*/
public void buildRecord(ResultSet rs) throws SQLException {
id = rs.getInt("queue_id");
reportId = rs.getInt("report_id");
entered = rs.getTimestamp("entered");
enteredBy = rs.getInt("enteredby");
processed = rs.getTimestamp("processed");
status = rs.getInt("status");
filename = rs.getString("filename");
size = DatabaseUtils.getLong(rs, "filesize");
enabled = rs.getBoolean("enabled");
outputType = rs.getInt("output_type");
email = rs.getBoolean("email");
outputTypeDescription = rs.getString("type_description");
outputTypeConstant = rs.getInt("type_constant");
}
@Test
public void testResultFromResultSet() throws Exception {
SavedReviewerSearchDAO dao = new SavedReviewerSearchDAO();
ResultSet resultSet = EasyMock.createStrictMock(ResultSet.class);
Date createdDate = new Date();
int customerId = 56;
int userId = 32422;
int msgTypeId = 2342;
String name = "myName";
String queryType = "myQueryType";
String jsonData = "this is my json data but I don't check that it is json.";
EasyMock.expect(resultSet.getString(1)).andReturn(name);
EasyMock.expect(resultSet.getInt(2)).andReturn(customerId);
EasyMock.expect(resultSet.getInt(3)).andReturn(userId);
EasyMock.expect(resultSet.getInt(4)).andReturn(msgTypeId);
EasyMock.expect(resultSet.getString(5)).andReturn(queryType);
EasyMock.expect(resultSet.getString(6)).andReturn(jsonData);
EasyMock.expect(resultSet.getTimestamp(7)).andReturn(new Timestamp(createdDate.getTime()));
EasyMock.replay(resultSet);
SavedReviewerSearch search = dao.resultFromResultSet(resultSet);
EasyMock.verify(resultSet);
assertEquals("Wrong created date.", createdDate, search.getCreatedDate());
assertEquals("Wrong customer id.", customerId, search.getCustomerId());
assertEquals("Wrong user id.", userId, search.getSearchUserId());
assertEquals("Wrong msg type id.", msgTypeId, search.getMessageTypeId());
assertEquals("Wrong name.", name, search.getSearchName());
assertEquals("Wrong query type.", queryType, search.getQueryType());
assertEquals("Wrong json data.", jsonData, search.getJsonData());
// now with no date
EasyMock.reset(resultSet);
EasyMock.expect(resultSet.getString(1)).andReturn(name);
EasyMock.expect(resultSet.getInt(2)).andReturn(customerId);
EasyMock.expect(resultSet.getInt(3)).andReturn(userId);
EasyMock.expect(resultSet.getInt(4)).andReturn(msgTypeId);
EasyMock.expect(resultSet.getString(5)).andReturn(queryType);
EasyMock.expect(resultSet.getString(6)).andReturn(jsonData);
EasyMock.expect(resultSet.getTimestamp(7)).andReturn(null);
EasyMock.replay(resultSet);
search = dao.resultFromResultSet(resultSet);
EasyMock.verify(resultSet);
assertNull("Created date should not be set.", search.getCreatedDate());
}
@Test
public void timestamp() throws SQLException {
connectWithJulianDayModeActivated();
long now = System.currentTimeMillis();
Timestamp d1 = new Timestamp(now);
Date d2 = new Date(now);
Time d3 = new Time(now);
stat.execute("create table t (c1);");
PreparedStatement prep = conn.prepareStatement("insert into t values (?);");
prep.setTimestamp(1, d1);
prep.executeUpdate();
ResultSet rs = stat.executeQuery("select c1 from t;");
assertTrue(rs.next());
assertEquals(d1, rs.getTimestamp(1));
rs = stat.executeQuery("select date(c1, 'localtime') from t;");
assertTrue(rs.next());
assertEquals(d2.toString(), rs.getString(1));
rs = stat.executeQuery("select time(c1, 'localtime') from t;");
assertTrue(rs.next());
assertEquals(d3.toString(), rs.getString(1));
rs = stat.executeQuery("select strftime('%Y-%m-%d %H:%M:%f', c1, 'localtime') from t;");
assertTrue(rs.next());
// assertEquals(d1.toString(), rs.getString(1)); // ms are not occurate...
}
private void loadCache() throws SQLException, IOException {
Connection connection = null;
PreparedStatement query = null;
PreparedStatement insert = null;
ResultSet rs = null;
try {
connection = dataSource.getConnection();
query = connection.prepareStatement(SQL_GET_SEQNUMS);
setSessionIdParameters(query, 1);
rs = query.executeQuery();
if (rs.next()) {
cache.setCreationTime(SystemTime.getUtcCalendar(rs.getTimestamp(1)));
cache.setNextTargetMsgSeqNum(rs.getInt(2));
cache.setNextSenderMsgSeqNum(rs.getInt(3));
} else {
insert = connection.prepareStatement(SQL_INSERT_SESSION);
int offset = setSessionIdParameters(insert, 1);
insert.setTimestamp(offset++, new Timestamp(cache.getCreationTime().getTime()));
insert.setInt(offset++, cache.getNextTargetMsgSeqNum());
insert.setInt(offset, cache.getNextSenderMsgSeqNum());
insert.execute();
}
} finally {
JdbcUtil.close(sessionID, rs);
JdbcUtil.close(sessionID, query);
JdbcUtil.close(sessionID, insert);
JdbcUtil.close(sessionID, connection);
}
}
/**
* Transforma os dados obtidos atraves de uma consulta a tabela de glosas do prestador em um
* objeto do tipo GlosaPrestador
*
* @param rset - um ResultSet contendo o resultado da consulta a tabela de glosas do prestador
* @return um objeto do tipo GlosaPrestador
*/
private static final GlosaPrestador montaGlosaPrestador(ResultSet rset) throws SQLException {
String numAtendimento = null;
String numDocOrigem = null;
String strLoteNota = "";
String strCodBeneficiario = "";
String strNomeBeneficiario = "";
Timestamp dtGlosa = null;
String strMotivo = "";
String strDescricao = "";
String strObservacoes = "";
numAtendimento = rset.getString("rej_ate_num_atendimento");
dtGlosa = rset.getTimestamp("rej_data_hora");
numDocOrigem = rset.getString("nat_numero_documento_origem");
strLoteNota = rset.getString("Lote_Nota");
strCodBeneficiario = rset.getString("Beneficiario");
strNomeBeneficiario = rset.getString("Nome");
strDescricao = rset.getString("Descricao");
strMotivo = (rset.getString("rej_motivo") == null) ? "" : rset.getString("rej_motivo");
strObservacoes =
(rset.getString("ate_observacoes") == null) ? "" : rset.getString("ate_observacoes");
return (new GlosaPrestador(
numAtendimento,
numDocOrigem,
strLoteNota,
strCodBeneficiario,
strNomeBeneficiario,
dtGlosa,
strMotivo,
strDescricao,
strObservacoes));
} // montaGlosaPrestador()
public JobIniciado retornaUltimoLote(Connection conn, String job, int operacao)
throws SQLException {
String sql =
" select job, operacao, tripulacao,recurso, max(data_fim) \n"
+ "from joblote where job = ? and operacao = ? \n"
+ "group by job, operacao, tripulacao, recurso ";
PreparedStatement stmt = null;
ResultSet rs = null;
JobIniciado iniciado = null;
stmt = conn.prepareStatement(sql);
stmt.setString(1, job.trim().replace(".", ""));
stmt.setInt(2, operacao);
rs = stmt.executeQuery();
if (rs.next()) {
iniciado = new JobIniciado();
iniciado.setJob(rs.getString(1));
iniciado.setOperacao(rs.getInt(2));
iniciado.setTripulacao(rs.getDouble(3));
iniciado.setRecurso(rs.getString(4));
Timestamp data = rs.getTimestamp(5);
iniciado.setData(Validacoes.getDataHoraString(data));
}
return iniciado;
}
@Override
public News getNewsById(int id) {
News news = new News();
SqlUtilities.jbdcUtil();
Connection connection = null;
PreparedStatement preparedStatement = null;
ResultSet resultSet = null;
try {
connection =
DriverManager.getConnection(
"jdbc:mysql://localhost:3306/fourscorepicks", "fourscorepicks", "fourscorepicks");
preparedStatement = connection.prepareStatement("SELECT * FROM news WHERE id=?");
preparedStatement.setInt(1, id);
resultSet = preparedStatement.executeQuery();
resultSet.next();
news.setDatePosted(resultSet.getTimestamp("date_posted"));
news.setNewsText(resultSet.getString("news"));
news.setId(resultSet.getInt("id"));
} catch (SQLException e) {
throw new RuntimeException(e);
} finally {
SqlUtilities.closePreparedStatement(preparedStatement);
SqlUtilities.closeResultSet(resultSet);
SqlUtilities.closeConnection(connection);
}
return news;
}
private void buildRecord(ResultSet rs) throws SQLException {
userId = rs.getInt("user_id");
linkModuleId = DatabaseUtils.getInt(rs, "link_module_id");
linkItemId = DatabaseUtils.getInt(rs, "link_item_id");
tag = rs.getString("tag");
tagDate = rs.getTimestamp("tag_date");
}
public static Show[] getShows() {
Logger.log("Database.getShows", CALL_FLAG);
Show[] shows = new Show[0];
try {
Hall[] halls = getHalls();
Movie[] movies = getMovies();
Timestamp time;
Hall hall;
Movie movie;
int ID;
ResultSet rs = query("SELECT COUNT(*) FROM forestillinger");
rs.next();
shows = new Show[rs.getInt("COUNT(*)")];
rs = query("SELECT * FROM forestillinger ORDER BY tid ASC");
while (rs.next()) {
hall = halls[rs.getInt("sal_id") - 1];
movie = movies[rs.getInt("film_id") - 1];
time = rs.getTimestamp("tid");
ID = rs.getInt("forestilling_id");
shows[rs.getRow() - 1] = new Show(hall, movie, time, ID);
}
} catch (Exception exception) {
Logger.log(exception, "Database:getShows");
}
Logger.log("Database.getShows", FINISHED_FLAG);
return shows;
}
public ArrayList<Customer> getCustomers(String searchType, String searchBy) {
ArrayList<Customer> customerList = new ArrayList<Customer>();
String query = "select * from customer where &searchtype like '%&searchBy%'";
Constants constants = new Constants();
HashMap<String, String> map = constants.searchTypeToColumn;
if (searchType.equals("All")) query = "select * from customer";
else {
query = query.replace("&searchtype", map.get(searchType));
query = query.replace("&searchBy", searchBy);
}
try {
ResultSet rs = statement.executeQuery(query);
while (rs.next()) {
Customer cust = new Customer();
cust.setId(rs.getInt(1));
cust.setFirst_name(rs.getString(2));
cust.setLast_name(rs.getString(3));
cust.setAddress(rs.getString(4));
cust.setTel(rs.getString(5));
cust.setMobile(rs.getString(6));
cust.setDob(rs.getTimestamp(7));
cust.setDoj(rs.getTimestamp(8));
customerList.add(cust);
}
} catch (SQLException ex) {
Logger.getLogger(CustomerDBHelper.class.getName()).log(Level.SEVERE, null, ex);
}
return customerList;
}
public void printAppsForModeration() {
try {
Statement statement = this.conn.createStatement();
String query = "SELECT * FROM apps_for_moderation()";
ResultSet resultSet = statement.executeQuery(query);
System.out.println("***************** PUBLICATION REQUESTS *****************\n");
System.out.println(
String.format("%-10s %-20s %-10s %-20s\n", "Request", "Date", "App id", "App name"));
SimpleDateFormat df = new SimpleDateFormat("MM/dd/yyyy HH:mm:ss");
while (resultSet.next()) {
int request_id = resultSet.getInt("request_id");
java.util.Date request_date = resultSet.getTimestamp("request_date");
int app_id = resultSet.getInt("app_id");
String app_name = resultSet.getString("app_name");
System.out.println(
String.format(
"%-10d %-20s %-10d %-20s", request_id, df.format(request_date), app_id, app_name));
}
System.out.print("\n");
} catch (SQLException exception) {
printErrorForSQLException(exception);
}
}
public List<ClientEventEntry> findLogEvents(
List<SearchConstraint> constraints, List<String> orderBy, int offset, int limit, Connection c)
throws SQLException {
StringBuffer sql =
new StringBuffer(
"select e.event_id, e.customer_id, e.user_id, e.event_time, e.description, e.has_log_file from dat_client_log_events e ");
appendWhere(sql, constraints, s_propToColumnMap);
appendOrderBy(sql, orderBy, "e.event_id desc", s_propToColumnMap);
appendLimits(sql, offset, limit);
Statement stmt = null;
ResultSet rs = null;
try {
stmt = c.createStatement();
rs = stmt.executeQuery(sql.toString());
List<ClientEventEntry> results = new ArrayList<ClientEventEntry>();
while (rs.next()) {
ClientEventEntry entry =
new ClientEventEntry(
rs.getInt(1),
rs.getInt(2),
rs.getInt(3),
resolveDate(rs.getTimestamp(4)),
rs.getString(5),
rs.getInt(6) != 0);
results.add(entry);
}
return results;
} finally {
DbUtils.safeClose(rs);
DbUtils.safeClose(stmt);
}
}
public ArrayList<File> getFiles(String sql) {
ArrayList<File> list = new ArrayList<>();
Connection conn = null;
ResultSet rs = null;
PreparedStatement ps = null;
try {
conn = getConnection();
ps =
conn.prepareStatement(
sql.toLowerCase().startsWith("select")
? sql
: ("SELECT FILENAME, MODIFIED FROM FILES WHERE " + sql));
rs = ps.executeQuery();
while (rs.next()) {
String filename = rs.getString("FILENAME");
long modified = rs.getTimestamp("MODIFIED").getTime();
File file = new File(filename);
if (file.exists() && file.lastModified() == modified) {
list.add(file);
}
}
} catch (SQLException se) {
LOGGER.error(null, se);
return null;
} finally {
close(rs);
close(ps);
close(conn);
}
return list;
}
public Vector<Message> getInboxMessages(String username, int type) {
if (type == 4) username = "******";
ResultSet rs =
con.queryDB(
"SELECT * FROM "
+ MESSAGES
+ " WHERE toName = \""
+ username
+ "\" AND messageType = "
+ type
+ " ORDER BY timeStamp DESC");
Vector<Message> inbox = new Vector<Message>();
try {
while (rs.next()) {
inbox.add(
new Message(
rs.getString("fromName"),
username,
rs.getString("message"),
rs.getInt("messageType"),
rs.getTimestamp("timeStamp")));
}
} catch (SQLException e) {
throw new RuntimeException("SQLException in MessageDatabase::getInboxMessages");
}
return inbox;
}
public Visitor mapRow(ResultSet rs, int rowNum) throws SQLException {
Visitor visitor = new Visitor();
visitor.setId(rs.getInt("id"));
visitor.setIp(rs.getString("ip"));
visitor.setCreate_time(rs.getTimestamp("create_time"));
visitor.setUa(rs.getString("ua"));
return visitor;
}
@Override
public Object retrieveValue(ResultSet results, int columnIndex, Class<?> expectedType)
throws SQLException {
if (expectedType.equals(Timestamp.class)) {
// Calendar based getTimestamp not supported by Hive
return results.getTimestamp(columnIndex);
}
return super.retrieveValue(results, columnIndex, expectedType);
}
public static void main(String[] args) {
port(Integer.valueOf(System.getenv("PORT")));
staticFileLocation("/public");
// get("/hello", (req, res) -> "Hello World");
// get("/hello", (req, res) -> {
// RelativisticModel.select();
// String energy = System.getenv().get("ENERGY");
// Amount<Mass> m = Amount.valueOf(energy).to(KILOGRAM);
// return "E=mc^2: " + energy + " = " + m.toString();
// });
// get("/", (request, response) -> {
// Map<String, Object> attributes = new HashMap<>();
// attributes.put("message", "Hello World!");
// return new ModelAndView(attributes, "index.ftl");
// }, new FreeMarkerEngine());
get(
"/db",
(req, res) -> {
Connection connection = null;
Map<String, Object> attributes = new HashMap<>();
try {
connection = DatabaseUrl.extract().getConnection();
Statement stmt = connection.createStatement();
stmt.executeUpdate("CREATE TABLE IF NOT EXISTS ticks (tick timestamp)");
stmt.executeUpdate("INSERT INTO ticks VALUES (now())");
ResultSet rs = stmt.executeQuery("SELECT tick FROM ticks");
ArrayList<String> output = new ArrayList<String>();
while (rs.next()) {
output.add("Read from DB: " + rs.getTimestamp("tick"));
}
attributes.put("results", output);
return new ModelAndView(attributes, "db.ftl");
} catch (Exception e) {
attributes.put("message", "There was an error: " + e);
return new ModelAndView(attributes, "error.ftl");
} finally {
if (connection != null)
try {
connection.close();
} catch (SQLException e) {
}
}
},
new FreeMarkerEngine());
}
protected Map<String, Object> getARow(
ResultSet resultSet,
boolean convertType,
List<String> colNames,
Map<String, Integer> fieldNameVsType) {
if (resultSet == null) return null;
Map<String, Object> result = new HashMap<>();
for (String colName : colNames) {
try {
if (!convertType) {
// Use underlying database's type information except for BigDecimal and BigInteger
// which cannot be serialized by JavaBin/XML. See SOLR-6165
Object value = resultSet.getObject(colName);
if (value instanceof BigDecimal || value instanceof BigInteger) {
result.put(colName, value.toString());
} else {
result.put(colName, value);
}
continue;
}
Integer type = fieldNameVsType.get(colName);
if (type == null) type = Types.VARCHAR;
switch (type) {
case Types.INTEGER:
result.put(colName, resultSet.getInt(colName));
break;
case Types.FLOAT:
result.put(colName, resultSet.getFloat(colName));
break;
case Types.BIGINT:
result.put(colName, resultSet.getLong(colName));
break;
case Types.DOUBLE:
result.put(colName, resultSet.getDouble(colName));
break;
case Types.DATE:
result.put(colName, resultSet.getTimestamp(colName));
break;
case Types.BOOLEAN:
result.put(colName, resultSet.getBoolean(colName));
break;
case Types.BLOB:
result.put(colName, resultSet.getBytes(colName));
break;
default:
result.put(colName, resultSet.getString(colName));
break;
}
} catch (SQLException e) {
logError("Error reading data ", e);
wrapAndThrow(SEVERE, e, "Error reading data from database");
}
}
return result;
}
@Override
public GrupoCollection obtener_coleccion(long timestamp, boolean before) throws SQLException {
Grupo grup = null;
GrupoCollection grupocollection = new GrupoCollection();
Connection connection = null;
PreparedStatement stmt = null;
try {
connection = Database.getConnection();
if (before)
stmt =
connection.prepareStatement(
GrupoDAOQuery.OBTENER_COLECCION_GRUPOS_APARTIR_ID_PAGINADA_A_5);
else
stmt =
connection.prepareStatement(
GrupoDAOQuery.OBTENER_COLECCION_GRUPOS_APARTIR_ID_PAGINADA_A_5_after);
stmt.setTimestamp(1, new Timestamp(timestamp));
ResultSet rs = stmt.executeQuery();
boolean first = true;
while (rs.next()) {
grup = new Grupo();
grup.setId(rs.getString("id"));
grup.setNombre(rs.getString("nombre"));
grup.setCreationTimestamp(rs.getTimestamp("creation_timestamp").getTime());
grup.setLastModified(rs.getTimestamp("last_modified").getTime());
if (first) {
grupocollection.setNewestTimestamp(grup.getLastModified());
first = false;
}
grupocollection.setOldestTimestamp(grup.getLastModified());
grupocollection.getGrupos().add(grup);
}
} catch (SQLException e) {
throw e;
} finally {
if (stmt != null) stmt.close();
if (connection != null) {
connection.setAutoCommit(true);
connection.close();
}
}
return grupocollection;
}
@Override
protected Vote loadObject(ResultSet resultSet) throws SQLException, MapperException {
Vote vote =
new Vote(
Mappers.getForClass(User.class).loadById(resultSet.getLong("user_id")),
Mappers.getForClass(Answer.class).loadById(resultSet.getLong("answer_id")),
resultSet.getTimestamp("creation_datetime"));
vote.setId(resultSet.getLong("id"));
return vote;
}
private void printOutLogs(Connection connection, PrintWriter out) throws SQLException {
Statement select = connection.createStatement();
ResultSet result = select.executeQuery("SELECT * FROM LOGGING ORDER BY DATE ASC");
while (result.next()) {
Timestamp date = result.getTimestamp("DATE");
String ip = result.getString("IP");
String url = result.getString("URL");
out.println(date + "\t\t" + ip + "\t\t" + url);
}
}
public static void format(Connection con, String kq_date, String branch_id) throws Exception {
PreparedStatement pstmt = null;
ResultSet rs = null;
String sql = null;
StuKqFormatNew stuKqFormatNewObj = null;
JLog.getLogger().debug("branch_id=" + branch_id);
java.sql.Timestamp dKqDate = new java.sql.Timestamp(JUtil.str2SQLDate(kq_date).getTime());
java.sql.Timestamp nextDate = new java.sql.Timestamp(JUtil.relativeDate(dKqDate, 1).getTime());
sql =
" select a.* from t_fl_class_schedule a, shinyway.t_fl_class b "
+ " where a.class_id=b.class_id and b.branch_id=?"
+ " and a.begin_time >=? and a.begin_time <?";
pstmt = con.prepareStatement(sql);
pstmt.setString(1, branch_id);
pstmt.setTimestamp(2, dKqDate);
pstmt.setTimestamp(3, nextDate);
JLog.getLogger()
.debug(
"sql="
+ sql
+ " branch_id="
+ branch_id
+ " dKqDate="
+ dKqDate
+ " nextDate="
+ nextDate);
rs = pstmt.executeQuery();
while (rs.next()) {
ScheduleDate scheduleObj = new ScheduleDate();
scheduleObj.setLine_id(rs.getString("line_id"));
scheduleObj.setClass_id(rs.getString("class_id"));
scheduleObj.setBegin_time(rs.getTimestamp("begin_time"));
scheduleObj.setEnd_time(rs.getTimestamp("end_time"));
JLog.getLogger().debug("class_id=" + scheduleObj.getClass_id());
formatClassSchedule(con, scheduleObj);
}
rs.close();
pstmt.close();
}
// ---------------------------------------------------------------//
public ArrayList<Project> selectAllProjects() throws SQLException {
Connection conn = null;
PreparedStatement prepStmt = null;
ArrayList<Project> rtrn = null;
try {
conn = select();
String sql =
"SELECT pr.*,cst.CUST_NAME FROM PROJECTS pr,CUSTOMERS cst WHERE cst.CUST_ID=pr.CUST_ID";
prepStmt = conn.prepareStatement(sql);
ResultSet rs = prepStmt.executeQuery();
rtrn = new ArrayList<Project>();
while (rs.next()) {
Project proj =
new Project(
rs.getInt("PROJ_ID"),
rs.getString("PROJ_NAME"),
rs.getInt("CUST_ID"),
rs.getString("PROJ_TYPE"),
rs.getDate("PROJ_FROM"),
rs.getDate("PROJ_TO"),
rs.getDate("PROJ_DEADLINE"),
(rs.getString("PROJ_ACTIVE")).equals("Y"),
rs.getFloat("PROJ_BUDGET"),
rs.getString("PROJ_DESCRIPTION"),
rs.getTimestamp("INSERTED_AT"),
rs.getString("INSERTED_BY"),
rs.getTimestamp("MODIFIED_AT"),
rs.getString("MODIFIED_BY"),
rs.getInt("ROWVERSION"),
rs.getInt("PAR_PROJ_ID"),
rs.getString("CUST_NAME"));
rtrn.add(proj);
}
rs.close();
} finally {
if (prepStmt != null) conn.close();
if (conn != null) conn.close();
}
return rtrn;
}
/**
* Retrieves the value of the designated column in the current row of this ResultSet object as a
* java.sql.Timestamp object.
*
* @param s the SQL name of the column
* @return the column value; if the value is SQL NULL, the value returned is null
*/
protected Timestamp getTimestamp(String s) {
Timestamp ret = null;
try {
ret = rs.getTimestamp(s);
} catch (SQLException ex) {
logger.severe("Unable to get timestamp from resultset");
logger.fine("Current statement: " + stmt);
logger.finer("Returned error: " + ex);
}
return ret;
}
public void testHalfHourTimezone() throws Exception {
Statement stmt = con.createStatement();
stmt.execute("SET TimeZone = 'GMT+3:30'");
for (int i = 0; i < PREPARE_THRESHOLD; i++) {
PreparedStatement ps = con.prepareStatement("SELECT '1969-12-31 20:30:00'::timestamptz");
ResultSet rs = ps.executeQuery();
assertTrue(rs.next());
assertEquals(0L, rs.getTimestamp(1).getTime());
ps.close();
}
}
public long getTimestamp(int n) {
try {
ResultSet rs = getResultSet();
if (rs == null) return 0;
Timestamp ts = rs.getTimestamp(n);
return ts.getTime();
} catch (Exception e) {
Debug.warning(e);
}
return 0;
}
@Override
public Collection<Long> extractData(ResultSet rs) throws SQLException, DataAccessException {
List<Long> goodIds = new ArrayList<>(rs.getFetchSize());
while (rs.next()) {
incGoodCount(/* article id */ rs.getLong(2), /* timestamp */ rs.getTimestamp(3).getTime());
goodIds.add(/* good id*/ rs.getLong(1));
}
return goodIds;
}
public void cleanup() {
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
try {
conn = getConnection();
ps = conn.prepareStatement("SELECT COUNT(*) FROM FILES");
rs = ps.executeQuery();
dbCount = 0;
if (rs.next()) {
dbCount = rs.getInt(1);
}
rs.close();
ps.close();
PMS.get().getFrame().setStatusLine(Messages.getString("DLNAMediaDatabase.2") + " 0%");
int i = 0;
int oldpercent = 0;
if (dbCount > 0) {
ps =
conn.prepareStatement(
"SELECT FILENAME, MODIFIED, ID FROM FILES",
ResultSet.TYPE_FORWARD_ONLY,
ResultSet.CONCUR_UPDATABLE);
rs = ps.executeQuery();
while (rs.next()) {
String filename = rs.getString("FILENAME");
long modified = rs.getTimestamp("MODIFIED").getTime();
File file = new File(filename);
if (!file.exists() || file.lastModified() != modified) {
rs.deleteRow();
}
i++;
int newpercent = i * 100 / dbCount;
if (newpercent > oldpercent) {
PMS.get()
.getFrame()
.setStatusLine(Messages.getString("DLNAMediaDatabase.2") + newpercent + "%");
oldpercent = newpercent;
}
}
}
} catch (SQLException se) {
LOGGER.error(null, se);
} finally {
close(rs);
close(ps);
close(conn);
}
}
