@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 int getReservationCnt(int product_id) { connect(); ArrayList<reservation> list = new ArrayList<reservation>(); int cnt = 0; try { pstmt = con.prepareStatement("select * from reservation where product_id = " + product_id); ResultSet rs = pstmt.executeQuery(); while (rs.next()) { reservation r = new reservation(); r.setReserve_id(rs.getInt("reserve_id")); r.setReserve_date(rs.getString("reserve_date")); r.setPeople_num(rs.getInt("people_num")); r.setPeople_id(rs.getString("people_id")); r.setProduct_id(rs.getInt("product_id")); r.setSeat(rs.getInt("seat")); r.setPassport_num(rs.getString("passport_num")); cnt++; list.add(r); result++; } this.setResult(result); rs.close(); } catch (SQLException e) { e.printStackTrace(); } finally { disconnect(); } return cnt; }
public static int retornaQtdTotal(Connection conn, JobProtheus job) throws SQLException { PreparedStatement stmt = null; ResultSet rs = null; int qtdTotal = 0; int qtdSucata = 0; String sqlVerQtd = "select job, sum(qtd_transf), sum(hr_tot), sum(qtd_sucata) from jobLote where job = ? and operacao = ? group by job"; stmt = conn.prepareStatement(sqlVerQtd); stmt.setString(1, job.getJob().trim()); stmt.setInt(2, job.getOperacao()); rs = stmt.executeQuery(); String qtdJob = ""; double totHora = 0; while (rs.next()) { qtdJob = rs.getString(1); // numero job qtdTotal = rs.getInt(2); // quantidade total totHora = rs.getInt(3); // hora total em minutos qtdSucata = rs.getInt(4); } return qtdTotal + qtdSucata; }
public void printDadoTable() { out("----------------------Dado----------------------"); try { stmt = c.createStatement(); rs = stmt.executeQuery("SELECT * FROM " + tableDadoName + ";"); while (rs.next()) { int id = rs.getInt("id"); int id_codi = rs.getInt("id_cod"); String data = rs.getString("data"); String local = rs.getString("local"); String acao = rs.getString("acao"); String detalhes = rs.getString("detalhes"); out("ID = " + id); out("id_cod = " + id_codi); out("data = " + data); out("local = " + local); out("acao = " + acao); out("detalhes = " + detalhes); System.out.println(); } } catch (Exception e) { System.err.println(e.getClass().getName() + ": " + e.getMessage()); } }
public String query7() { String name, coach, output; int budget; String sqlText; try { sql = connection.createStatement(); sqlText = "CREATE VIEW topScorer AS SELECT cid, MAX(goals) AS scores FROM player GROUP BY cid"; sql.executeUpdate(sqlText); sqlText = "CREATE VIEW maxScore AS SELECT MAX(scores) AS maxscore FROM topScorer"; sql.executeUpdate(sqlText); sqlText = "CREATE VIEW topScorerTeam AS SELECT cid FROM topScorer JOIN maxScore ON (topScorer.scores = maxScore.maxscore)"; sql.executeUpdate(sqlText); sqlText = "CREATE VIEW budgetTeam AS SELECT cid, SUM(value) AS budget FROM player GROUP BY cid"; sql.executeUpdate(sqlText); sqlText = "CREATE VIEW lowestBudget AS SELECT MIN(budget) AS minbudget FROM budgetTeam"; sql.executeUpdate(sqlText); sqlText = "CREATE VIEW lowestBudgetTeam AS SELECT cid, minbudget AS budget FROM lowestBudget JOIN budgetTeam ON (lowestBudget.minbudget = budgetTeam.budget)"; sql.executeUpdate(sqlText); sqlText = "CREATE VIEW countryLowestBudgetTopScorer AS SELECT topScorerTeam.cid AS cid, budget FROM topScorerTeam JOIN lowestBudgetTeam ON topScorerTeam.cid = lowestBudgetTeam.cid"; sql.executeUpdate(sqlText); // We project Query7 over here. sqlText = "SELECT name, coach, budget FROM countryLowestBudgetTopScorer JOIN country ON (countryLowestBudgetTopScorer.cid = country.cid)"; rs = sql.executeQuery(sqlText); if (!rs.isBeforeFirst()) { return ""; } else { rs.next(); // Query7 (String name, String coach, integer budget) name = rs.getString("name"); coach = rs.getString("coach"); budget = rs.getInt("budget"); output = name + ":" + coach + ":" + budget; while (rs.next()) { name = rs.getString("name"); coach = rs.getString("coach"); budget = rs.getInt("budget"); output += "#" + name + ":" + coach + ":" + budget; } return output; } } catch (SQLException e) { return ""; } }
@BeforeClass public static void setUpBeforeClass() throws Exception { con.addCustomer( "Buddy", "Bear", "1520 Garnet Ave", "", "San Diego", "CA", "92109", "4766666656"); con.addPublication("Runner Magazine", "Sports", 9.80, "Monthly", 5); ResultSet r = con.searchCustomer(0, "Buddy", ""); try { while (r.next()) { testCustID = r.getInt("CustomerID"); } r.close(); } catch (Exception e) { e.printStackTrace(); } ResultSet rs = con.searchPublication(0, "", "Runner Magazine"); try { while (rs.next()) { testPubID = rs.getInt("PublicationID"); } } catch (Exception e) { e.printStackTrace(); } subscriptions newSub = new subscriptions(testCustID, testPubID); }
@Override public Project get(int id) { Project result = null; try (Connection connection = dbDataSourceProvider.getConnection()) { try (PreparedStatement statement = connection.prepareStatement(sqlProvider.get4Load())) { statement.setInt(1, id); ResultSet resultSet = statement.executeQuery(); if (resultSet.next()) { result = new Project( resultSet.getString("name"), resultSet.getInt("goal"), resultSet.getDate("deadline_date")); result.setShortDescription(resultSet.getString("description")); result.setBalance(resultSet.getInt("balance")); result.setDemoLink(resultSet.getString("demo_link")); result.setCreateDate(resultSet.getDate("create_date")); result.setId(id); } } connection.commit(); } catch (SQLException e) { throw new RuntimeException(e); } return result; }
public void updateOptionPrice( String modelName, String optionSetName, String optionName, float newPrice) { try { int autoid = 0; String sql = "select id from automobile where name ='" + modelName + "';"; ResultSet rs = statement.executeQuery(sql); while (rs.next()) { autoid = rs.getInt("id"); // get auto_id } int opsid = 0; sql = "select id from optionset where name ='" + optionSetName + "' and auto_id= " + autoid + ";"; rs = statement.executeQuery(sql); while (rs.next()) { opsid = rs.getInt("id"); // get option_id } sql = "update options set price = " + newPrice + " where name= '" + optionName + "' and option_id= " + opsid + ";"; statement.executeUpdate(sql); // update it with name and option_id } catch (SQLException e) { e.printStackTrace(); } }
@Override public Trago GetById(int id) throws Exception { try { Connection con = Conexion.getConexion(); Statement sql = con.createStatement(); ResultSet cmd = sql.executeQuery("Select * from tragos where id=" + id + ";"); Trago tr = null; while (cmd.next()) { tr = new Trago(); tr.setDescripcion(cmd.getString("descripcion")); tr.setId(cmd.getInt("id")); tr.setEstado((cmd.getInt("estado") == 1) ? true : false); tr.setGraduacion(cmd.getString("graduacion")); tr.setModoPreparacion(cmd.getString("modopreparacion")); tr.setNombre(cmd.getString("nombre")); tr.setPositivo(cmd.getInt("positivo")); tr.setNegativo(cmd.getInt("negativo")); } cmd.close(); sql.close(); con.close(); if (tr != null) { tr.setReceta(ADReceta.getInstancia().GetAllByIdTrago(tr.getId())); } return tr; } catch (Exception ex) { throw ex; } }
// Needs a connection so it can fetch more stuff lazily Copy(ResultSet rs) throws SQLException { super(); copyId = rs.getInt("copy#"); bibId = rs.getInt("bib#"); note = rs.getString("pac_note"); location = rs.getString("location"); locationName = rs.getString("location_name"); collectionDescr = rs.getString("collection_descr"); collection = rs.getString("collection"); callNumber = new CallNumber( rs.getString("call_number"), rs.getString("call_type"), rs.getString("copy_number"), rs.getString("call_type_hint")); callType = rs.getString("call_type"); callTypeHint = rs.getString("call_type_hint"); callTypeName = rs.getString("call_type_name"); mediaType = rs.getString("media_type"); mediaTypeDescr = rs.getString("media_descr"); summaryOfHoldings = rs.getBoolean("summary_of_holdings"); itemType = rs.getString("itype"); itemTypeDescr = rs.getString("idescr"); }
// metode getRestrictions nodrošina ierobežojumu saraksta izgūšanu no datubāzes public static List getRestrictions() { List<restrictionsList> restrList = new ArrayList<>(); try { Statement stmt = connect.createStatement(); ResultSet rs = stmt.executeQuery( "SELECT R.ID, PT.Type_Name, R.Time , PT.Type_count_day, R.Used FROM RESTRICTIONS R INNER JOIN PROCESS_TYPE PT WHERE R.Type_ID = PT.PT_ID;"); while (rs.next()) { int restr_id = rs.getInt("ID"); String type_name = rs.getString("Type_Name"); int max_time = rs.getInt("Time"); int used_time = rs.getInt("Type_count_day"); Boolean is_used = rs.getBoolean("Used"); restrictionsList list = new restrictionsList(restr_id, type_name, max_time, used_time, is_used); restrList.add(list); // System.out.println( "TYPE NAME = " + name + " Type Count = " +count); } rs.close(); stmt.close(); // System.out.println("Getting type list completed"); } catch (Exception e) { JOptionPane.showMessageDialog( null, e.getClass().getName() + ": " + e.getMessage(), "Error", JOptionPane.ERROR_MESSAGE); // System.exit(0); } return restrList; }
// metode getTypeList atgriež sarakstu ar informāciju par visiem tipiem public static List getTypeList() { List<typeList> typeList = new ArrayList<>(); try { Statement stmt = connect.createStatement(); ResultSet rs = stmt.executeQuery("SELECT * FROM PROCESS_TYPE ORDER BY Type_Count_Day DESC;"); while (rs.next()) { int type_id = rs.getInt("PT_ID"); String name = rs.getString("TYPE_NAME"); int count_day = rs.getInt("TYPE_COUNT_DAY"); int count_all = rs.getInt("TYPE_COUNT_ALL"); String currDate = rs.getString("Type_Sys_date"); typeList type = new typeList(type_id, name, count_day, currDate, count_all); typeList.add(type); // System.out.println( "TYPE NAME = " + name + " Type Count = " +count); } rs.close(); stmt.close(); // System.out.println("Getting type list completed"); } catch (Exception e) { JOptionPane.showMessageDialog( null, e.getClass().getName() + ": " + e.getMessage(), "Error", JOptionPane.ERROR_MESSAGE); // System.exit(0); } return typeList; }
// metode getProcessList atgriež sarakstu, ar visiem procesiem un to datiem, kas reģistrēti // datubāzē public static List getProcessList() { List<processList> proctypeList = new ArrayList<>(); try { Statement stmt = connect.createStatement(); ResultSet rs = stmt.executeQuery( "SELECT * FROM PROCESS INNER JOIN PROCESS_TYPE WHERE PROCESS.TYPE_ID = PROCESS_TYPE.PT_ID ORDER BY PROCESS.Count_Day DESC;"); while (rs.next()) { String name = rs.getString("NAME"); int count_day = rs.getInt("Count_Day"); String currDate = rs.getString("Sys_date"); String type = rs.getString("Type_Name"); int count_all = rs.getInt("Count_All"); processList proctype = new processList(name, count_day, currDate, type, count_all); proctypeList.add(proctype); // System.out.println( "NAME = " + name + " Count = " +count+ " Type:" + type); } stmt.close(); rs.close(); // System.out.println("Getting process type completed"); } catch (Exception e) { JOptionPane.showMessageDialog( null, e.getClass().getName() + ": " + e.getMessage(), "Error", JOptionPane.ERROR_MESSAGE); // System.exit(0); } return proctypeList; }
public ArrayList<reservationInfo> getInfo() { connect(); ArrayList<reservationInfo> list = new ArrayList<reservationInfo>(); try { pstmt = con.prepareStatement(SQL); ResultSet rs = pstmt.executeQuery(); while (rs.next()) { reservationInfo r = new reservationInfo(); r.setPassport_num(rs.getString("passport_num")); r.setPeople_num(rs.getInt("people_num")); r.setProduct_id(rs.getInt("product_id")); r.setReserve_date(rs.getString("reserve_date")); r.setReserve_id(rs.getInt("reserve_id")); r.setSeat(rs.getInt("seat")); r.setAirline_name(rs.getString("airline_name")); r.setName(rs.getString("name")); r.setArrival_time(rs.getString("arrival_time")); r.setArrival_date(rs.getString("arrival_date")); r.setDeparture_time(rs.getString("departure_time")); r.setDeparture_date(rs.getString("departure_date")); r.setSeat(rs.getInt("seat")); r.setSchedule_id(rs.getInt("schedule_id")); r.setPeople_id(rs.getString("people_id")); result++; list.add(r); } this.setResult(result); rs.close(); } catch (SQLException e) { e.printStackTrace(); } finally { disconnect(); } return list; }
public ArrayList<rev> getRevId() { connect(); ArrayList<rev> list = new ArrayList<rev>(); try { pstmt = con.prepareStatement(SQL); ResultSet rs = pstmt.executeQuery(); while (rs.next()) { rev r = new rev(); r.setCost(rs.getInt("cost")); r.setPassport_num(rs.getString("passport_num")); r.setPeople_num(rs.getInt("people_num")); r.setProduct_id(rs.getInt("product_id")); r.setProduct_name(rs.getString("product_name")); r.setReserve_date(rs.getString("reserve_date")); r.setReserve_id(rs.getInt("reserve_id")); r.setSeat(rs.getInt("seat")); list.add(r); result++; } this.setResult(result); rs.close(); } catch (SQLException e) { e.printStackTrace(); } finally { disconnect(); } return list; }
/** * metoda pobierajaca wszystkie rekordy z BD1. * * @return */ public ArrayList<StudentFirst> getAllStudentFromDBfirst() { logger.info("getAllContactFromDB"); ResultSet result = null; ArrayList<StudentFirst> students = new ArrayList<>(); try { prepStmt = conn.prepareStatement("SELECT * FROM student1"); result = prepStmt.executeQuery(); int id, index; String name, surname, university, faculty, field; for (int i = 0; result.next(); i++) { id = result.getInt("stud1_id"); index = result.getInt("stud1_index"); name = result.getString("stud1_name"); surname = result.getString("stud1_lastname"); university = result.getString("stud1_university"); faculty = result.getString("stud1_faculty"); field = result.getString("stud1_field"); students.add(new StudentFirst(id, index, name, surname, university, faculty, field)); logger.info(students.get(i).toString()); } if (students.size() == 0) { logger.info("Pusta BD?"); } return students; } catch (SQLException e1) { JOptionPane.showMessageDialog(null, e1); e1.printStackTrace(); return null; } }
/** * get a project by projectID * * @return a project */ public Project getProject(int ID) { PreparedStatement stmt = null; ResultSet rs = null; Project project = null; try { String sql = "select ProjectID, Title, RecordSperImage, " + "FirstYCoord, RecordHeight from Projects where ProjectID = ?"; stmt = db.getConnection().prepareStatement(sql); stmt.setInt(1, ID); rs = stmt.executeQuery(); while (rs.next()) { Project newProject = new Project(rs.getInt(1), rs.getString(2), rs.getInt(3), rs.getInt(4), rs.getInt(5)); project = newProject; } } catch (SQLException e) { System.out.println("Can't execute query"); e.printStackTrace(); } finally { try { if (rs != null) rs.close(); if (stmt != null) stmt.close(); } catch (SQLException e) { System.out.println("Can't execute connect"); e.printStackTrace(); } } return project; }
@Override public ArrayList<Trago> GetAll() throws Exception { ArrayList<Trago> colTragos = new ArrayList<Trago>(); try { Connection con = Conexion.getConexion(); Statement sql = con.createStatement(); ResultSet cmd = sql.executeQuery("Select * from tragos;"); while (cmd.next()) { Trago tr = new Trago(); tr.setDescripcion(cmd.getString("descripcion")); tr.setId(cmd.getInt("id")); tr.setEstado((cmd.getInt("estado") == 1) ? true : false); tr.setGraduacion(cmd.getString("graduacion")); tr.setModoPreparacion(cmd.getString("modopreparacion")); tr.setNombre(cmd.getString("nombre")); tr.setPositivo(cmd.getInt("positivo")); tr.setNegativo(cmd.getInt("negativo")); colTragos.add(tr); } cmd.close(); sql.close(); con.close(); for (Trago tr1 : colTragos) { tr1.setReceta(ADReceta.getInstancia().GetAllByIdTrago(tr1.getId())); } return colTragos; } catch (Exception ex) { throw ex; } }
public void deleteOption(String modelName, String optionSetName, String option) { try { int autoid = 0; String sql = "select id from automobile where name ='" + modelName + "';"; ResultSet rs; rs = statement.executeQuery(sql); while (rs.next()) { autoid = rs.getInt("id"); // get auto_id } int opsid = 0; sql = "select id from optionset where name ='" + optionSetName + "' and auto_id= " + autoid + ";"; rs = statement.executeQuery(sql); while (rs.next()) { opsid = rs.getInt("id"); // get option_id } sql = "delete from options where name= '" + option + "' and option_id= " + opsid; statement.executeUpdate(sql); // delete it using name and option_id } catch (SQLException e) { e.printStackTrace(); } }
private Operator fromResultSet(ResultSet rs) throws SQLException { long id = rs.getLong(1); if (rs.wasNull()) id = -1; String name = rs.getString(2); if (rs.wasNull()) name = ""; int binding = rs.getInt(3); if (rs.wasNull()) binding = -1; String notation = rs.getString(4); if (rs.wasNull()) notation = ""; String symbol = rs.getString(5); if (rs.wasNull()) symbol = ""; String symbol_intern = rs.getString(6); if (rs.wasNull()) symbol_intern = ""; int arity = rs.getInt(7); if (rs.wasNull()) arity = -1; String description = rs.getString(8); if (rs.wasNull()) description = ""; String comment = rs.getString(9); if (rs.wasNull()) comment = ""; Operator operator = new Operator( id, name, binding, notation, symbol, symbol_intern, arity, description, comment); return operator; }
/** * ******************************************************** Displays a table listing the team * names and season records. Since teams have not yet played, all numbers are zero. * * @param conn Connection to the database ******************************************************** */ public static void viewTeams(Connection conn) { try { // Create a Statement object. Statement stmt = conn.createStatement(); // Create a string with a SELECT statement. String sqlStatement = "SELECT * FROM Teams"; // Send the statement to the DBMS. ResultSet result = stmt.executeQuery(sqlStatement); System.out.printf("%-15s %10s %10s %10s\n", "Team Name", "Win", "Lose", "Tie"); // Display the contents of the result set. // The result set will have 5 columns. while (result.next()) { System.out.printf( "%-15s %10d %10d %10d\n", result.getString("TeamName"), result.getInt("Wins"), result.getInt("Losses"), result.getInt("Ties")); } } catch (Exception ex) { System.out.println("ERROR: " + ex.getMessage()); } }
stab(ResultSet k) { super("PATIENT FOUND"); String[] chd = { "Date", "Patient ID", "Name", "Gender", "Age", "Weight", "Address", "Contact No.", "Doctor Name", "Symptoms", "Dignosis", "Fee: Rs.", "Blood Group" }; // DefaultTableModel dtm=new DefaultTableModel(); // jt.setModel(new DefaultTableModel(arr,chd)); jt = new JTable(); jsp = new JScrollPane(jt); btn = new JButton("CLOSE"); btn.addActionListener(this); int i = 0; try { while (k.next()) { arr[i][0] = k.getString(1); // System.out.println("ddddddddddddd"+arr[i][0]); arr[i][1] = "" + k.getInt(2); arr[i][2] = k.getString(3) + " " + k.getString(4) + " " + k.getString(5); arr[i][3] = k.getString(6); arr[i][4] = "" + k.getInt(7); arr[i][5] = k.getString(8); arr[i][6] = k.getString(9); arr[i][7] = k.getString(10); arr[i][8] = k.getString(11); arr[i][9] = k.getString(12); arr[i][10] = k.getString(13); arr[i][11] = "" + k.getInt(14); arr[i][12] = k.getString(15); arr[i][13] = k.getString(16); // dtm.insertRow(i,new Object[]{patient.rs.getString(1), // patient.rs.getInt(2),patient.rs.getString(3)+patient.rs.getString(4)+patient.rs.getString(5),patient.rs.getString(6),patient.rs.getInt(7),patient.rs.getString(8),patient.rs.getString(9),patient.rs.getString(10),patient.rs.getString(11),patient.rs.getString(12),patient.rs.getString(13),patient.rs.getInt(14)}); i++; } } catch (Exception e12) { System.out.println("" + e12); } jt.setModel(new DefaultTableModel(arr, chd)); Container con = getContentPane(); con.setLayout(null); jsp.setBounds(20, 20, 1230, 600); btn.setBounds(685, 630, 100, 30); add(jsp); add(btn); setSize(1330, 800); setVisible(true); }
public ArrayList<String> getEquipos() throws SQLException, ClassNotFoundException { ArrayList<String> foo = new ArrayList(); Class.forName("org.sqlite.JDBC"); c = DriverManager.getConnection("jdbc:sqlite:Equipos.db"); c.setAutoCommit(false); System.out.println("Tabla Equipos abierta"); stmt = c.createStatement(); try (ResultSet rs = stmt.executeQuery("SELECT Nombre FROM Equipos;")) { while (rs.next()) { int id = rs.getInt("id"); String name = rs.getString("Nombre"); String form = rs.getString("Formacion"); int h1 = rs.getInt("Arquero"); int h2 = rs.getInt("Defensor"); int h3 = rs.getInt("Mediocampo"); int h4 = rs.getInt("Ataque"); foo.add(Integer.toString(id)); foo.add(name); foo.add(form); foo.add(Integer.toString(h1)); foo.add(Integer.toString(h2)); foo.add(Integer.toString(h3)); foo.add(Integer.toString(h4)); } } stmt.close(); c.close(); System.out.println("Operation done successfully"); return foo; }
@Test public void testUpsertValuesWithExpression() throws Exception { long ts = nextTimestamp(); ensureTableCreated(getUrl(), "IntKeyTest", null, ts - 2); Properties props = new Properties(); props.setProperty( PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 1)); // Execute at timestamp 1 Connection conn = DriverManager.getConnection(PHOENIX_JDBC_URL, props); String upsert = "UPSERT INTO IntKeyTest VALUES(-1)"; PreparedStatement upsertStmt = conn.prepareStatement(upsert); int rowsInserted = upsertStmt.executeUpdate(); assertEquals(1, rowsInserted); upsert = "UPSERT INTO IntKeyTest VALUES(1+2)"; upsertStmt = conn.prepareStatement(upsert); rowsInserted = upsertStmt.executeUpdate(); assertEquals(1, rowsInserted); conn.commit(); conn.close(); props.setProperty( PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 2)); // Execute at timestamp 1 conn = DriverManager.getConnection(PHOENIX_JDBC_URL, props); String select = "SELECT i FROM IntKeyTest"; ResultSet rs = conn.createStatement().executeQuery(select); assertTrue(rs.next()); assertEquals(-1, rs.getInt(1)); assertTrue(rs.next()); assertEquals(3, rs.getInt(1)); assertFalse(rs.next()); }
/** @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response) */ protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { PrintWriter pw = null; try { pw = response.getWriter(); String name = request.getParameter("myval"); Class.forName("oracle.jdbc.driver.OracleDriver"); Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:orcl", "scott", "tiger"); Statement st = conn.createStatement(); String str = "select * from student where name like %" + name + "%"; ResultSet rs = st.executeQuery(str); while (rs.next()) { pw.println( rs.getString(1) + " " + rs.getInt(2) + " " + rs.getString(3) + " " + rs.getInt(4)); } conn.close(); } catch (Exception e) { pw.print(e + ""); } }
@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; }
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); } }
/** * {@inheritDoc} This method will get the distance data from a MariaDB database. This method * assumes the connection member is not null and open. Therefore, should be called through * MariaDaoManager. */ @Override public Optional<Distance> getDistanceBetween(Classroom classroom1, Classroom classroom2) throws DataAccessException { Distance distance = null; try { if (selectTwoRooms == null || selectTwoRooms.isClosed()) { SqlBuilder builder = new SqlBuilder("distance", StatementType.SELECT) .addColumns("id,distance") .addWhereClause( "(startRoomId=? AND endRoomId=?) OR (startRoomId=? AND endRoomId=?"); selectTwoRooms = connection.prepareStatement(builder.build()); } selectTwoRooms.setInt(1, classroom1.id); selectTwoRooms.setInt(4, classroom1.id); selectTwoRooms.setInt(2, classroom2.id); selectTwoRooms.setInt(3, classroom2.id); ResultSet set = selectTwoRooms.executeQuery(); if (set.next()) { distance = new Distance(set.getInt(1), classroom1, classroom2, set.getInt(2)); } set.close(); } catch (SQLException e) { Log.debug("Caught [" + e + "] so throwing a DataAccessException!"); throw new DataAccessException(e); } return Optional.ofNullable(distance); }
public void updateScore(List<Player> players) { try { if (connection == null || players.size() <= 0) { return; } List<String> playersName = new ArrayList<String>(); for (Player player : players) { playersName.add("'" + player.getName() + "'"); } String arrs = join(playersName, ","); String strSql = "SELECT * FROM player WHERE name IN (" + arrs + ")"; cacheScores.clear(); Map<String, Object> map; Statement sql = connection.createStatement(); ResultSet result = sql.executeQuery(strSql); while (result.next()) { map = new HashMap<String, Object>(); map.put("kills", result.getInt("kills")); map.put("deaths", result.getInt("deaths")); map.put("mobs", result.getInt("mobs")); map.put("prefix", result.getString("prefix")); cacheScores.put(result.getString("name"), map); } result.close(); sql.close(); } catch (Exception e) { e.printStackTrace(); } }
public ArrayList<staffEntity> getStaffList() { connect(); ArrayList<staffEntity> list = new ArrayList<staffEntity>(); try { pstmt = con.prepareStatement(SQL); ResultSet rs = pstmt.executeQuery(); while (rs.next()) { staffEntity s = new staffEntity(); s.setName(rs.getString("name")); s.setCareer(rs.getInt("career")); s.setGender(rs.getString("gender")); s.setPhone(rs.getString("phone")); s.setAge(rs.getInt("age")); result++; list.add(s); } this.setResult(result); rs.close(); } catch (SQLException e) { e.printStackTrace(); } finally { disconnect(); } return list; }