@Override
public Set<EmpVO> getEmpsByDeptno(Integer deptno) {
Set<EmpVO> set = new LinkedHashSet<EmpVO>();
EmpVO empVO = null;
Connection con = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
try {
Class.forName(driver);
con = DriverManager.getConnection(url, userid, passwd);
pstmt = con.prepareStatement(GET_Emps_ByDeptno_STMT);
pstmt.setInt(1, deptno);
rs = pstmt.executeQuery();
while (rs.next()) {
empVO = new EmpVO();
empVO.setEmpno(rs.getInt("empno"));
empVO.setEname(rs.getString("ename"));
empVO.setJob(rs.getString("job"));
empVO.setHiredate(rs.getDate("hiredate"));
empVO.setSal(rs.getDouble("sal"));
empVO.setComm(rs.getDouble("comm"));
empVO.setDeptno(rs.getInt("deptno"));
set.add(empVO); // Store the row in the vector
}
// Handle any driver errors
} catch (ClassNotFoundException e) {
throw new RuntimeException("Couldn't load database driver. " + e.getMessage());
// Handle any SQL errors
} catch (SQLException se) {
throw new RuntimeException("A database error occured. " + se.getMessage());
} finally {
if (rs != null) {
try {
rs.close();
} catch (SQLException se) {
se.printStackTrace(System.err);
}
}
if (pstmt != null) {
try {
pstmt.close();
} catch (SQLException se) {
se.printStackTrace(System.err);
}
}
if (con != null) {
try {
con.close();
} catch (Exception e) {
e.printStackTrace(System.err);
}
}
}
return set;
}
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;
}
}
public static void refreshLumberLog(LumberLog lumberLog) {
Logger logger = Logger.getLogger("dao");
StringBuilder lumberLogData =
new StringBuilder("select diameter, metric from lumberlog_diameter where lumberlog_id = ")
.append(lumberLog.getId());
Connection con = null;
Statement stm = null;
ResultSet rs = null;
try {
con = DataAccess.getInstance().getDatabaseConnection();
con.setAutoCommit(true);
stm = con.createStatement();
logger.info(lumberLogData.toString());
rs = stm.executeQuery(lumberLogData.toString());
lumberLog.setMediumRadius(new ArrayList<Double>());
while (rs.next()) {
lumberLog.getMediumRadius().add(rs.getDouble(1));
}
} catch (Exception e) {
logger.warning(e.getMessage());
logger.log(Level.INFO, "Error", e);
} finally {
if (rs != null)
try {
rs.close();
} catch (Exception e) {
}
if (stm != null)
try {
stm.close();
} catch (Exception e) {
}
}
}
public ValoresAtualizaJob retornaNovosValores(Connection conn, JobLote job) throws SQLException {
PreparedStatement stmt = null;
ResultSet rs = null;
ValoresAtualizaJob obj = null;
String sql =
" select sum(qtd_transf) qtd_transf, sum(qtd_sucata) qtd_sucata, sum(hr_tot) hr_tot from joblote\n"
+ " where job = ? and operacao = ? ";
stmt = conn.prepareStatement(sql);
stmt.setString(1, job.getJob().trim().replace(".", ""));
stmt.setInt(2, job.getOperNum());
rs = stmt.executeQuery();
if (rs.next()) {
obj = new ValoresAtualizaJob();
obj.setQtdTransf(rs.getDouble("qtd_transf"));
obj.setQtdSucata(rs.getDouble("qtd_sucata"));
obj.setHoraTotal(rs.getDouble("hr_tot"));
}
return obj;
}
// metoda za pretragu po broju stanovnika
public ArrayList<Country> SearchCountryPopulation(long Population) {
ArrayList<Country> countries = new ArrayList<Country>();
try {
Connection connection = getConnected("world");
PreparedStatement statement =
connection.prepareStatement(
"SELECT * FROM country WHERE Population <= " + Population + ";");
ResultSet result = statement.executeQuery();
while (result.next()) {
countries.add(
new Country(
result.getString("Code"),
result.getString("Name"),
result.getString("Continent"),
result.getString("Region"),
result.getDouble("SurfaceArea"),
result.getInt("IndepYear"),
result.getLong("Population"),
result.getDouble("LifeExpectancy"),
result.getDouble("GNP"),
result.getDouble("GNPOld"),
result.getString("LocalName"),
result.getString("GovernmentForm"),
result.getString("HeadOfState"),
result.getInt("Capital"),
result.getString("Code2")));
}
connection.close();
} catch (Exception e) {
System.out.println(e.toString());
return null;
}
return countries;
}
public static City getCity(int cityIndex) {
City city = new City();
city.setMonthsIrradiance(new double[12]);
Connection connection = null;
String select_sql = "select * from cities natural join states where CityID =?";
PreparedStatement select = null;
try {
connection =
DriverManager.getConnection(
"jdbc:google:rdbms://solarcalculator372:solarcalculator/solar");
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
try {
select = connection.prepareStatement(select_sql);
select.setInt(1, cityIndex);
ResultSet rs = select.executeQuery();
while (rs.next()) {
city.setCityName(rs.getString("CityName"));
city.setZoneRating(rs.getDouble("ZoneRating"));
city.setAvgProduePerkw(rs.getDouble("AvgProducePerkw"));
city.setFeedInTariff(rs.getDouble("FeedInTariff"));
city.setElectricityCost(rs.getDouble("AvgElectricityCost"));
city.setPostcode(rs.getInt("Postcode"));
city.setOptimalYearDegree(rs.getInt("OptimalYearRound"));
city.setBestWinterDegree(rs.getInt("BestWinter"));
city.setBestSummerDegree(rs.getInt("BestSummer"));
for (int i = 0; i < 12; i++) {
city.getMonthsIrradiance()[i] = rs.getDouble(10 + i);
}
return city;
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
try {
if (select != null) {
select.close();
}
if (connection != null) {
connection.close();
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return null;
}
@Test
public void twoRowRS() throws SQLException {
PreparedStatement prep = conn.prepareStatement("select ? union all select ?;");
prep.setDouble(1, Double.MAX_VALUE);
prep.setDouble(2, Double.MIN_VALUE);
ResultSet rs = prep.executeQuery();
assertTrue(rs.next());
assertEquals(Double.MAX_VALUE, rs.getDouble(1));
assertTrue(rs.next());
assertEquals(Double.MIN_VALUE, rs.getDouble(1));
assertFalse(rs.next());
rs.close();
}
public void executeQuery() throws SQLException {
log(
"select account, branch, balance, filler from "
+ m_Driver.getAccountName()
+ " where account < 101\n",
2);
Statement stmt = null;
try {
stmt = m_conn.createStatement();
ResultSet set =
stmt.executeQuery(
"select account, branch, balance, filler from "
+ m_Driver.getAccountName()
+ " where account < 101");
while (set.next()) {
set.getInt(1);
set.getInt(2);
set.getDouble(3);
set.getString(4);
}
set.close();
} finally {
if (stmt != null) stmt.close();
stmt = null;
}
}
@Test
public void batch() throws SQLException {
ResultSet rs;
stat.executeUpdate("create table test (c1, c2, c3, c4);");
PreparedStatement prep = conn.prepareStatement("insert into test values (?,?,?,?);");
for (int i = 0; i < 10; i++) {
prep.setInt(1, Integer.MIN_VALUE + i);
prep.setFloat(2, Float.MIN_VALUE + i);
prep.setString(3, "Hello " + i);
prep.setDouble(4, Double.MAX_VALUE + i);
prep.addBatch();
}
assertArrayEq(prep.executeBatch(), new int[] {1, 1, 1, 1, 1, 1, 1, 1, 1, 1});
assertEquals(0, prep.executeBatch().length);
prep.close();
rs = stat.executeQuery("select * from test;");
for (int i = 0; i < 10; i++) {
assertTrue(rs.next());
assertEquals(Integer.MIN_VALUE + i, rs.getInt(1));
assertEquals(Float.MIN_VALUE + i, rs.getFloat(2));
assertEquals("Hello " + i, rs.getString(3));
assertEquals(Double.MAX_VALUE + i, rs.getDouble(4));
}
rs.close();
stat.executeUpdate("drop table test;");
}
/**
* Transforma os dados obtidos atraves de uma consulta a tabela de fatos do prestador em um objeto
* do tipo ResumoFato
*
* @param rset - um ResultSet contendo o resultado da consulta a tabela de fatos do prestador
* @return um objeto do tipo ResumoFato
*/
private static final ResumoFato montaResumoFato(ResultSet rset) throws SQLException {
/* double qtdeNotas;
String strCodServico;
String strDsServico;
double qtdeServico;
double valorTotal;
strCodServico = rset.getString("pef_codigo");
strDsServico = rset.getString("Descricao");
qtdeNotas = rset.getDouble("QtdeNotas");
qtdeServico = rset.getDouble("QtdeServicos");
valorTotal = rset.getDouble("ValorTotal");
return ( new ResumoFato(qtdeNotas, strCodServico, strDsServico, qtdeServico,
valorTotal) );*/
int tipoNota = 0;
int qtdeNotas = 0;
int tipoRegistro = 0;
double valorTotal = 0;
tipoNota = rset.getInt("tipo_nota");
tipoRegistro = rset.getInt("tipo_registro");
qtdeNotas = rset.getInt("qtde_notas");
valorTotal = rset.getDouble("valor_total");
return (new ResumoFato(tipoNota, tipoRegistro, qtdeNotas, valorTotal));
} // montaResumoFato()
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 ManagerPanel getById(int userId) throws DbException {
PreparedStatement ps = null;
ResultSet rs = null;
ManagerPanel managerPanel = null;
try {
managerPanel = new ManagerPanel();
prepareConnection();
ps = connection.prepareStatement(MANAGER_SELECT_BY_ID);
ps.setInt(1, userId);
rs = ps.executeQuery();
rs.next();
Money finresult = Money.dollars(rs.getDouble("summaryfinresult"));
managerPanel.setSummaryFinRes(finresult);
} catch (SQLException e) {
throw new DbException("Can't execute SQL = '" + MANAGER_SELECT_BY_ID + "'", e);
} finally {
daoHelper.closeDataBaseEntities(ps, rs, connection);
}
return managerPanel;
}
public static void main(String[] args) {
Connection conn = null;
try {
String connUrl = "jdbc:sqlserver://localhost:1433;databaseName=jdbc";
conn = DriverManager.getConnection(connUrl, "sa", "passw0rd");
String qryStmt = "SELECT ename, salary FROM employee";
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(qryStmt);
while (rs.next()) {
System.out.print("name = " + rs.getString("ename") + ", ");
System.out.println("salary = " + rs.getDouble("salary"));
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
if (conn != null)
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
} // end of main()
@Override
public List<MstGasto> read() {
Connection cn;
PreparedStatement pst;
ResultSet rs;
String sql;
List<MstGasto> lst = new ArrayList();
try {
Class.forName(bd.getDriver());
cn = DriverManager.getConnection(bd.getUrl(), bd.getUser(), bd.getPasswd());
sql = "select * from mst_tipo_gastos order by corr_gasto";
pst = cn.prepareStatement(sql);
rs = pst.executeQuery();
while (rs.next()) {
lst.add(
new MstGasto(
rs.getInt("cod_residencial"),
rs.getInt("corr_gasto"),
rs.getString("desc_gasto"),
rs.getString("cod_cta_conta"),
rs.getDouble("valor_gasto"),
rs.getDate("fecha_creacion"),
rs.getString("cod_usuario"),
rs.getString("activo")));
}
rs.close();
pst.close();
cn.close();
} catch (SQLException e) {
log.severe(e.toString());
} catch (Exception e) {
log.severe(e.toString());
}
return lst;
}
static void task1() throws FileNotFoundException, IOException, SQLException {
// Read Input
System.out.println("Task1 Started...");
BufferedReader br = new BufferedReader(new FileReader(inputFile));
br.readLine();
String task1Input = br.readLine();
br.close();
double supportPercent =
Double.parseDouble(task1Input.split(":")[1].split("=")[1].split("%")[0].trim());
if (supportPercent >= 0) {
System.out.println("Task1 Support Percent :" + supportPercent);
// Prepare query
String task1Sql =
"select temp.iname,(temp.counttrans/temp2.uniquetrans)*100 as percent"
+ " from (select i.itemname iname,count(t.transid) counttrans from trans t, items i"
+ " where i.itemid = t.itemid group by i.itemname having count(t.transid)>=(select count(distinct transid)*"
+ supportPercent / 100
+ " from trans)"
+ ") temp , (select count(distinct transid) uniquetrans from trans) temp2 order by percent";
PreparedStatement selTask1 = con.prepareStatement(task1Sql);
ResultSet rsTask1 = selTask1.executeQuery();
BufferedWriter bw = new BufferedWriter(new FileWriter("system.out.1"));
while (rsTask1.next()) {
bw.write("{" + rsTask1.getString(1) + "}, s=" + rsTask1.getDouble(2) + "%");
bw.newLine();
}
rsTask1.close();
bw.close();
System.out.println("Task1 Completed...\n");
} else System.out.println("Support percent should be a positive number");
}
public static void getProfile() {
String url = "jdbc:mysql://" + var.db_host + "/" + var.db_name;
String login = var.db_username;
String passwd = var.db_psswd;
Connection cn = null;
Statement stmt = null;
String query = "SELECT * FROM profiles WHERE Name='" + getCurrentProfile() + "'";
try {
Class.forName("com.mysql.jdbc.Driver");
cn = DriverManager.getConnection(url, login, passwd);
stmt = cn.createStatement();
ResultSet rs = stmt.executeQuery(query);
while (rs.next()) {
Status.Name = rs.getString("Name");
Status.Sunrise = rs.getTime("Sunrise");
Status.Sunset = rs.getTime("Sunset");
Status.Interval = rs.getTime("Interval");
Status.Working_Time = rs.getTime("Working_Time");
Status.Tank_Capacity = rs.getDouble("Tank_Capacity");
Status.Pump_Flow = rs.getDouble("Pump_Flow");
Status.Watering_Hour = rs.getTime("Watering_Hour");
Status.Water_Amount = rs.getDouble("Water_Amount");
Status.Temperature = rs.getDouble("Temperature");
Status.Humidity = rs.getDouble("Humidity");
Status.Water_Days[0] = rs.getInt("Monday") == 1;
Status.Water_Days[1] = rs.getInt("Tuesday") == 1;
Status.Water_Days[2] = rs.getInt("Wednesday") == 1;
Status.Water_Days[3] = rs.getInt("Thursday") == 1;
Status.Water_Days[4] = rs.getInt("Friday") == 1;
Status.Water_Days[5] = rs.getInt("Saturday") == 1;
Status.Water_Days[6] = rs.getInt("Sunday") == 1;
}
} catch (SQLException e) {
e.printStackTrace();
} catch (ClassNotFoundException e) {
e.printStackTrace();
} finally {
if (stmt != null) {
try {
stmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
public Collection<domain.BridgeInfo> findBlock(double height, double length, double width) {
connection = getConnection();
Collection<domain.BridgeInfo> brigeInf = new ArrayList<>();
String query =
"SELECT OBJECTID, COLLOQUIAL_NAME_1, COLLOQUIAL_NAME_2, COLLOQUIAL_NAME_3, "
+ "CAST(MIN_CLEARANCE AS FLOAT) AS MIN_CLEARANCE, "
+ "CAST(OVERALL_LENGTH AS FLOAT) AS OVERALL_LENGTH, "
+ "CAST(OVERALL_WIDTH AS FLOAT) AS OVERALL_WIDTH, LAT, LONGIT FROM guest.Tbl_bridge_structure_vic "
+ "WHERE CAST(MIN_CLEARANCE AS FLOAT) > ? "
+ "AND CAST(OVERALL_LENGTH AS FLOAT) > ? AND CAST(OVERALL_WIDTH AS FLOAT) > ?;";
try {
preps = connection.prepareStatement(query);
preps.setDouble(1, height);
preps.setDouble(2, length);
preps.setDouble(3, width);
ResultSet rset = preps.executeQuery();
while (rset.next()) {
domain.BridgeInfo bridge = new BridgeInfo();
bridge.setObjectId(rset.getString(1));
bridge.setCollName1(rset.getString(2));
bridge.setCollName2(rset.getString(3));
bridge.setCollName3(rset.getString(4));
bridge.setMinClearance(rset.getDouble(5));
bridge.setLength(rset.getDouble(6));
bridge.setWidth(rset.getDouble(7));
bridge.setLat(rset.getDouble(8));
bridge.setLongit(rset.getDouble(9));
brigeInf.add(bridge);
}
connection.close();
rset.close();
} catch (SQLException ex) {
Logger.getLogger(jdbc.class.getName()).log(Level.SEVERE, null, ex);
} catch (NullPointerException ex) {
System.out.println(ex.getMessage());
}
System.out.println(brigeInf.size());
return brigeInf;
}
public Racquet getRacquet(int racquetId) throws SQLException {
start();
rs = stmt.executeQuery("SELECT * FROM racquetInfo WHERE racquetID = '" + racquetId + "'");
Racquet rtrn = null;
if (rs.next()) {
rtrn = new Racquet();
rtrn.setModelName(rs.getString(2));
rtrn.setBrand(rs.getString(3));
rtrn.setMass(rs.getDouble(4));
rtrn.setLength(rs.getDouble(5));
// rtrn.setSwingWeight(rs.getDouble(6));
rtrn.setBalancePoint(rs.getDouble(7));
rtrn.setQualityIndex(rs.getDouble(8));
}
close();
return rtrn;
}
/**
* Method to build a vector from a ResultSet
*
* @param <code>ResultSet rsResult</code> the result from a query
* @return Vector of objects from the ResultSet
* @exception Throws Exception on error
*/
protected Vector buildObjectVector(ResultSet rsResult) throws Exception {
// vector for return data
Vector vReturn = null;
try {
// init the vector
vReturn = new Vector(super.VECT_INIT_SIZE, super.VECT_GROW_SIZE);
// loop through the entire result set
while (rsResult.next()) {
// create a new one
StageUsacForm objTmp = new StageUsacForm();
// set the attributes
// ADDING A NEW FIELD "ROWID"
objTmp.ROWID = rsResult.getString("ROWID");
objTmp.HDR_SPIN = rsResult.getLong("HDR_SPIN");
objTmp.SPIN_NM = rsResult.getString("SPIN_NM");
objTmp.RCPNT_EMAIL = rsResult.getString("RCPNT_EMAIL");
objTmp.USAC_EMAIL = rsResult.getString("USAC_EMAIL");
objTmp.RFRNC_NMBR = rsResult.getString("RFRNC_NMBR");
objTmp.RCRD_CNT = rsResult.getLong("RCRD_CNT");
objTmp.TOT_PAYMENT = rsResult.getDouble("TOT_PAYMENT");
objTmp.USAC_PRCS_DAT = rsResult.getDate("USAC_PRCS_DAT");
objTmp.RTRCT_FLAG = rsResult.getString("RTRCT_FLAG");
objTmp.DTL_SPIN = rsResult.getLong("DTL_SPIN");
objTmp.FRN = rsResult.getLong("FRN");
objTmp.SDC_INV_NO = rsResult.getString("SDC_INV_NO");
objTmp.AMT_PAID = rsResult.getDouble("AMT_PAID");
objTmp.DSBRSMNT_TXT = rsResult.getString("DSBRSMNT_TXT");
objTmp.EMAIL_DATE = rsResult.getDate("EMAIL_DATE");
objTmp.FILENAME = rsResult.getString("FILENAME");
objTmp.PROCESS_DATE = rsResult.getDate("PROCESS_DATE");
objTmp.STATUS = rsResult.getLong("STATUS");
// add it to the vector
vReturn.addElement(objTmp);
}
return vReturn;
} catch (Exception e) {
throw new Exception("BuildObjectVector()\n" + e.getMessage());
}
}
protected LumberLog fromResultSet(ResultSet rs) throws SQLException {
LumberLog lumberLog = new LumberLog();
lumberLog.setId(rs.getLong("id"));
lumberLog.setLength(rs.getDouble("length"));
lumberLog.setRealLength(rs.getLong("reallength"));
lumberLog.setVolume(rs.getDouble("volume"));
lumberLog.setRealVolume(rs.getDouble("realvolume"));
lumberLog.setSmallRadius(rs.getDouble("small_diameter"));
lumberLog.setBigRadius(rs.getDouble("big_diameter"));
lumberLog.setLumberType(rs.getLong("lumbertype"));
lumberLog.setLumberClass(rs.getLong("lumberclass"));
lumberLog.setCutPlanId(rs.getLong("planId"));
LumberStack stack = new LumberStack();
stack.setName(rs.getString("stackName"));
stack.setId(rs.getLong("stack"));
lumberLog.setStack(stack);
IDPlate plate = new IDPlate();
plate.setId(rs.getLong("idplate"));
plate.setLabel(rs.getString("plateName"));
lumberLog.setPlate(plate);
lumberLog.setSupplierId(rs.getLong("SupplierId"));
lumberLog.setTransportCertifiateId(rs.getLong("TransportCertificateId"));
if (rs.wasNull()) {
lumberLog.setTransportCertifiateId(null);
}
lumberLog.setMarginPercent(rs.getInt("Margin"));
lumberLog.setMarginVolume(rs.getDouble("MarginVolume"));
lumberLog.setMarginRealVolume(rs.getDouble("RealMarginVolume"));
return lumberLog;
}
private Producto rsToBean(ResultSet rs) throws SQLException {
Producto o = new Producto();
o.setIdprod(rs.getInt("idprod"));
o.setIdcat(rs.getInt("idcat"));
o.setNombre(rs.getString("nombre"));
o.setPrecio(rs.getDouble("precio"));
o.setStock(rs.getInt("stock"));
return o;
}
@SuppressWarnings("unchecked")
public static <T> T getValueFromResultSet(
int index, final ResultSet resultSet, final Class<T> type) {
try {
if (java.sql.Date.class.isAssignableFrom(type)) {
final long time = resultSet.getLong(index);
return (T) new java.sql.Date(time);
} else if (Time.class.isAssignableFrom(type)) {
final long time = resultSet.getLong(index);
return (T) new java.sql.Time(time);
} else if (Timestamp.class.isAssignableFrom(type)) {
final long time = resultSet.getLong(index);
return (T) new java.sql.Timestamp(time);
} else if (Date.class.isAssignableFrom(type)) {
final long time = resultSet.getLong(index);
return (T) new Date(time);
} else if (Long.class.isAssignableFrom(type)) {
return (T) Long.valueOf(resultSet.getLong(index));
} else if (Integer.class.isAssignableFrom(type)) {
return (T) Integer.valueOf(resultSet.getInt(index));
} else if (Short.class.isAssignableFrom(type)) {
return (T) Short.valueOf(resultSet.getShort(index));
} else if (Float.class.isAssignableFrom(type)) {
return (T) Float.valueOf(resultSet.getFloat(index));
} else if (Double.class.isAssignableFrom(type)) {
return (T) Double.valueOf(resultSet.getDouble(index));
} else if (Boolean.class.isAssignableFrom(type)) {
return (T) Boolean.valueOf(resultSet.getBoolean(index));
} else if (BigDecimal.class.isAssignableFrom(type)) {
return (T) resultSet.getBigDecimal(index);
} else if (CharSequence.class.isAssignableFrom(type)) {
return (T) resultSet.getString(index);
} else if (byte[].class.isAssignableFrom(type)) {
return (T) resultSet.getBytes(index);
} else {
throw new IllegalStateException("Type " + type + " not supported.");
}
} catch (Exception e) {
throw new IllegalStateException(
"Unable to read the value from the resultSet. Index:" + index + ", type: " + type, e);
}
}
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;
}
public void atestHSQLDBConnect() throws SQLException {
Statement stmt = conn.createStatement();
String query = "SELECT name, age, payment, bonus, birthDate FROM employee order by age desc";
ResultSet rs = stmt.executeQuery(query);
while (rs.next()) {
String name = rs.getString("name");
int age = rs.getInt("age");
double payment = rs.getDouble("payment");
double bonus = rs.getDouble("bonus");
Date date = rs.getDate("birthDate");
System.out.println("Name: " + name);
System.out.println("Age: " + age);
System.out.println("Payment: " + payment);
System.out.println("Bonus: " + bonus);
System.out.println("BirthDate: " + date);
}
rs.close();
stmt.close();
}
@Test
public void singleRowRS() throws SQLException {
PreparedStatement prep = conn.prepareStatement("select ?;");
prep.setInt(1, Integer.MAX_VALUE);
ResultSet rs = prep.executeQuery();
assertTrue(rs.next());
assertEquals(Integer.MAX_VALUE, rs.getInt(1));
assertEquals(Integer.toString(Integer.MAX_VALUE), rs.getString(1));
assertEquals(new Integer(Integer.MAX_VALUE).doubleValue(), rs.getDouble(1));
assertFalse(rs.next());
rs.close();
prep.close();
}
@Override
public Void call() throws Exception {
db = new Database();
tweetDAO = db.getTweetDAO();
connect();
try {
resultSet = statement.executeQuery("select * from elektro7");
while (resultSet.next()) {
Tweet tweet = new Tweet();
tweet.setId(String.valueOf(resultSet.getLong("tweetId")));
tweet.setText(resultSet.getString("text"));
tweet.setTimestamp(resultSet.getDate("createdAt"));
tweet.setRetweetCount(resultSet.getInt("retweetCount"));
tweet.setFavoriteCount(resultSet.getInt("favoriteCount"));
tweet.setLanguage(resultSet.getString("tweetLanguage"));
tweet.setLatitude(resultSet.getDouble("tweetLatitude"));
tweet.setLongitude(resultSet.getDouble("tweetLongitude"));
User user = new User();
user.setId(resultSet.getLong("userId"));
user.setUserName(resultSet.getString("userName"));
user.setScreenName(resultSet.getString("screenName"));
user.setLocation(resultSet.getString("userLocation"));
user.setLanguage(resultSet.getString("userLanguage"));
user.setFollowers(resultSet.getLong("followersCount"));
user.setFriends(resultSet.getLong("friendsCount"));
tweet.setUser(user);
tweetDAO.save(tweet);
}
} finally {
close();
db.close();
}
return null;
}
public JobProtheus retornaJob(Connection conn, JobLote lote) throws SQLException {
ResultSet rs = null;
PreparedStatement stmt = null;
JobProtheus job = null;
String sql = "select job , B1_DESC, operacao , produto, ";
sql += " dt_release, job_start_date , qtd_release, setor, qtd_transf ";
sql += " from job left join " + DB_PROTHEUS.trim() + ".dbo.SB1000 SB1 ";
sql += " on(produto collate SQL_Latin1_General_CP1_CI_AS = B1_COD) ";
sql += " where job = ? and operacao = ? and SB1.D_E_L_E_T_ = '' ";
sql += " order by dt_release, produto ";
stmt = conn.prepareStatement(sql);
stmt.setString(1, lote.getJob().trim());
stmt.setInt(2, lote.getOperNum());
rs = stmt.executeQuery();
while (rs.next()) {
String nJob = rs.getString("job");
int operacao = rs.getInt("operacao");
String produto = rs.getString("produto");
Date dataEmissao = rs.getDate("dt_release");
Date dataPrevisaoInicio = rs.getDate("job_start_date");
String descricaoProduto = rs.getString("B1_DESC");
double quantidade = rs.getDouble("qtd_release");
String wc = rs.getString("setor");
String emissao = dataEmissao != null ? getDateToString(dataEmissao) : "";
String previsaoInicio = dataPrevisaoInicio != null ? getDateToString(dataPrevisaoInicio) : "";
job = new JobProtheus();
job.setStatus("");
job.setJob(nJob);
job.setOperacao(operacao);
job.setProduto(produto.trim());
job.setDataEmissao(emissao);
job.setQuantidadeLiberada(quantidade);
job.setDescricaoProduto(descricaoProduto);
job.setCentroTrabalho(wc);
double qtdTotal = retornaQtdTotal(conn, job); // calcula o total transferido para o job
job.setQuantidadeCompleta(qtdTotal);
job.setDataPrivisaoInicio(previsaoInicio);
job.setQuantidadeFaltando(quantidade - qtdTotal); // seta o valor qtd faltando
}
return job; // retorna lista de jobs
}
/** static method executes a standard jdbc query */
public static void testStandardJDBC() {
Connection con = null;
try {
Class.forName(driverName).newInstance();
con = DriverManager.getConnection(connURL, username, password);
PreparedStatement ps = con.prepareStatement(sqlSelect);
ps.setInt(1, 8);
ps.setDate(2, Date.valueOf("2003-05-10"));
ResultSet rs = ps.executeQuery();
String out = "SQL RESULTS:\n";
while (rs.next()) // still have records...
out +=
rs.getLong("TEST_ID")
+ " "
+ rs.getString("NOTES")
+ " "
+ rs.getDate("TEST_DT")
+ " "
+ rs.getDouble("AMOUNT")
+ " "
+ rs.getString("CODE")
+ "\n";
System.out.println(out);
} catch (SQLException sqle) {
sqle.printStackTrace();
// was it a data integrity violation?
switch (sqle.getErrorCode()) // ORACLE SPECIFIC CODES
{
case 1:
case 1407:
case 1722:
applyDataIntegrityViolationRecovery();
}
} catch (ClassNotFoundException cnfe) {
cnfe.printStackTrace();
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
if (con != null) con.close();
} catch (SQLException sqle) {
sqle.printStackTrace();
}
}
}
public ArrayList<Aulas> importarDatos() throws ClassNotFoundException, SQLException {
ArrayList<Aulas> listAulas = new ArrayList<>();
ResultSet rs = consultarDatos();
while (rs.next()) {
Aulas aula = new Aulas();
aula.setId(rs.getInt(1));
aula.setEdificio(rs.getString(2));
aula.setPiso(rs.getString(3));
aula.setCoordenadaX(rs.getDouble(4));
aula.setCoordenadaY(rs.getDouble(5));
aula.setContenido(rs.getString(6));
listAulas.add(aula);
}
return listAulas;
}
public Object get(ResultSet resultSet, int column) throws SQLException {
switch (this) {
case OBJECT:
return resultSet.getObject(column + 1);
case STRING:
return resultSet.getString(column + 1);
case INT:
return resultSet.getInt(column + 1);
case LONG:
return resultSet.getLong(column + 1);
case DOUBLE:
return resultSet.getDouble(column + 1);
default:
throw Util.unexpected(this);
}
}
