public static void main(String[] args) throws Exception { try (Connection conn = DBUtil.getConnection(DBType.HSQLDB); Statement stmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY); ResultSet rs = stmt.executeQuery("SELECT stateId, stateName FROM states"); ) { States.displayData(rs); rs.last(); System.out.println("Number of rows: " + rs.getRow()); rs.first(); System.out.println("The first state is " + rs.getString("stateName")); rs.last(); System.out.println("The last state is " + rs.getString("stateName")); rs.absolute(10); System.out.println("The 10th state is " + rs.getString("stateName")); } catch (SQLException e) { System.err.println(e); } }
private void DeletaCliente() { // if (!TFcodCli.getText().isEmpty()) { String id_cliente = TFcodCli.getText().isEmpty() ? "0" : TFcodCli.getText(); ResultSet rscliente = FMaster.con.executeSQL("select * from dlcliente where id_cliente = '" + id_cliente + "'"); try { rscliente.first(); if (rscliente.isFirst()) { rscliente.absolute(rscliente.getRow()); rscliente.deleteRow(); LimpaCampos(); PreencherJtableConsulta(); } else { JOptionPane.showMessageDialog( null, "Um cliente deve ser informada!", "Alerta", JOptionPane.ERROR_MESSAGE); } rscliente.close(); } catch (SQLException ex) { JOptionPane.showMessageDialog( null, "Não foi possível excluir o cliente!", "Alerta", JOptionPane.ERROR_MESSAGE); } } // }
private void AlterarCliente() { ResultSet rsCliente = FMaster.con.executeSQL( "select * from dlcliente where id_cliente = '" + TFcodCli.getText() + "'"); try { rsCliente.first(); if (rsCliente.isFirst()) { int iresp = JOptionPane.showOptionDialog( null, "Deseja alterar?", "", JOptionPane.YES_NO_OPTION, JOptionPane.QUESTION_MESSAGE, null, // do not use a custom Icon options, // the titles of buttons options[0]); // default button title if (iresp == 0) { rsCliente.absolute(rsCliente.getRow()); rsCliente.updateString("nome", TFNome.getText()); rsCliente.updateString("rua", TFRua.getText()); rsCliente.updateString("cidade", TFCidade.getText()); rsCliente.updateString("uf", CBEstado.getSelectedItem().toString()); rsCliente.updateInt( "numero", Integer.parseInt(TFNumero.getText().isEmpty() ? "0" : TFNumero.getText())); rsCliente.updateString("bairro", TFBairro.getText()); rsCliente.updateString("cep", TFCep1.getText().replace(".", "").replace("-", "")); rsCliente.updateString("cpf", TFCPF.getText().replace(".", "").replace("-", "")); rsCliente.updateString("obs", TAObs.getText()); rsCliente.updateFloat( "taxa_entrega", Float.parseFloat( TFVlrTaxaEntrega.getText().isEmpty() ? "0" : TFVlrTaxaEntrega.getText().replace(",", "."))); rsCliente.updateInt( "id_regiao", Integer.parseInt( CBRegiao.getSelectedItem() .toString() .substring( CBRegiao.getSelectedItem().toString().length() - 1, CBRegiao.getSelectedItem().toString().length()))); rsCliente.updateRow(); prencherHeder(rsCliente.getInt("id_cliente")); PreencherJtableConsulta(); } } rsCliente.close(); } catch (SQLException ex) { Logger.getLogger(IFProduto.class.getName()).log(Level.SEVERE, null, ex); } }
public static void deleteThought(int index) { try { java.sql.Connection con = Database.getConnection(); java.sql.Statement s = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE); int indext = index + 1; ResultSet thoughts; thoughts = s.executeQuery("select * from thoughts"); thoughts.absolute(indext); thoughts.deleteRow(); int size = aantalID("thoughts"); ResultSet thoughts2; thoughts2 = s.executeQuery("select * from thoughts"); if (thoughts2.next()) { while (indext < size) { thoughts2.absolute(indext); thoughts2.updateInt("id", indext); thoughts2.updateRow(); indext++; thoughts2.next(); } } } catch (SQLException ex) { Logger.getLogger(DataLayer.class.getName()).log(Level.SEVERE, null, ex); } }
public static void deleteProject(int id) { try { java.sql.Connection con = Database.getConnection(); java.sql.Statement s = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE); ResultSet projects; projects = s.executeQuery("select * from projects"); projects.absolute(id); projects.deleteRow(); int size = aantalID("projects"); projects = s.executeQuery("select * from projects"); if (projects.next()) { while (id < size) { projects.absolute(id); projects.updateInt("id", id); projects.updateRow(); id++; projects.next(); } } } catch (SQLException ex) { Logger.getLogger(DataLayer.class.getName()).log(Level.SEVERE, null, ex); } }
public void testQuery() { try { Connection c = newConnection(); Statement st = c.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE); String s = "CREATE TABLE T (I INTEGER, C CHARACTER(10), B BIT(4) DEFAULT B'')"; st.execute(s); s = "INSERT INTO T VALUES(?,?, DEFAULT)"; PreparedStatement ps = c.prepareStatement(s); for (int i = 1; i <= 20; i++) { ps.setInt(1, i); ps.setString(2, "TEST " + i); ps.execute(); } c.setAutoCommit(false); s = "SELECT * FROM T"; ResultSet rs = st.executeQuery(s); rs.absolute(10); rs.updateString(2, "UPDATE10"); rs.updateRow(); rs.absolute(11); rs.deleteRow(); rs.moveToInsertRow(); rs.updateInt(1, 1011); rs.updateString(2, "INSERT1011"); rs.updateString(3, "0101"); rs.insertRow(); rs.close(); rs = st.executeQuery(s); while (rs.next()) { System.out.println( "" + rs.getInt(1) + " " + rs.getString(2) + " " + rs.getString(3)); } } catch (Exception e) { System.out.print(e); } }
public PageTO page(int curPage) { PageTO to = new PageTO(); int totalCount = totalCount(); ArrayList<BoardVO> list = new ArrayList<BoardVO>(); Connection conn = null; PreparedStatement pstmt = null; ResultSet rs = null; try { conn = getConnection(); // String query = "select * from qna"; String query = "select q.num num, q.mnum mnum, q.subject subject, q.reg_Date reg_date," + " q.content content, q.anwser anwser, m.nick nick from MEMBER m, QNA q where " + "m.mnum=q.mnum"; pstmt = conn.prepareStatement( query, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY); rs = pstmt.executeQuery(); int perpage = to.getPerPage(); // 5 int skip = (curPage - 1) * perpage; if (skip > 0) { rs.absolute(skip); } for (int i = 0; i < perpage && rs.next(); i++) { int num = rs.getInt("num"); int mnum = rs.getInt("mnum"); String Subject = rs.getString("subject"); Date Reg_date = rs.getDate("Reg_date"); String content = rs.getString("content"); String answer = rs.getString("anwser"); String nick = rs.getString("nick"); BoardVO data = new BoardVO(); data.setNum(num); data.setMnum(mnum); data.setSubject(Subject); data.setReg_date(Reg_date); data.setContent(content); data.setAnwser(answer); data.setNick(nick); list.add(data); } to.setList(list); // ArrayList 저장 to.setTotalCount(totalCount); // 전체 레코드 개수 to.setCurPage(curPage); // 현재 페이지 } catch (Exception e) { e.printStackTrace(); } finally { CloseUtil.close(rs); CloseUtil.close(pstmt); CloseUtil.close(conn); } // end finally return to; } // end page
public Object getValueAt(int row, int col) { try { if (cache.containsKey(row) == false) { mResultSet.absolute(row + 1); final JDBMaterialUom prow = new JDBMaterialUom(Common.selectedHostID, Common.sessionID); prow.getPropertiesfromResultSet(mResultSet); cache.put(row, prow); } switch (col) { case Material_Col: return cache.get(row).getMaterial(); case Material_Uom_Col: return cache.get(row).getUom(); case Material_Ean_Col: return cache.get(row).getEan(); case Material_Variant_Col: return cache.get(row).getVariant(); case Material_Numerator_Col: return cache.get(row).getNumerator(); case Material_Denominator_Col: return cache.get(row).getDenominator(); } } catch (Exception ex) { return "Error"; } return new String(); }
// 列表记录集 public Vector<User> list(String where, int startRow, int rowCount) { Vector<User> beans = new Vector<User>(); try { String sql = "SELECT * FROM " + strTableName + " "; if (where.length() > 0) sql = String.valueOf(sql) + String.valueOf(where); Statement s = db.getConnection() .createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY); if (startRow != 0 && rowCount != 0) s.setMaxRows((startRow + rowCount) - 1); ResultSet rs = s.executeQuery(sql); if (rs != null && rs.next()) { if (startRow != 0 && rowCount != 0) rs.absolute(startRow); do { User theBean = new User(); theBean = load(rs, false); beans.addElement(theBean); } while (rs.next()); } rs.close(); s.close(); } catch (Exception ee) { ee.printStackTrace(); } return beans; }
public void updateDb(Connection con) { try (Statement statement = con.createStatement(); ResultSet resultSet = statement.executeQuery("SELECT * FROM contact where firstname = \"Michael\"")) { resultSet.absolute(1); // 1 zmienic na dokładny wiersz w ktorym jest michael - metadata? resultSet.updateString("phoneNumber", "+009392032302"); resultSet.updateRow(); System.out.println(" After the update"); System.out.println("ID \tfName \tsurname \temail \t\tphoneNo"); resultSet.beforeFirst(); while (resultSet.next()) { System.out.println( resultSet.getInt("id") + "\t" + resultSet.getString("name") + "t\"" + resultSet.getString("surname") + "t\"" + resultSet.getString("email") + "t\"" + resultSet.getString("phoneNumber")); } } catch (SQLException e) { e.printStackTrace(); } }
/** * Return List<Game> according to page number and page size * * @param pageNumber cursor of data record * @param pageSize size of one page * @return List<Game> */ public List<Game> getEntryList(int pageNumber, int pageSize) { List<Game> gameList = new ArrayList<Game>(); try { rs = crud.fetchAll("game"); if (rs == null) { return null; } if (pageNumber < 1) { pageNumber = 1; } if (pageNumber > 1) { rs.absolute(pageNumber); } for (int i = 0; i < pageSize; i++) { if (rs.next()) { Game game = new Game(); game.setId(rs.getInt("id")); game.setCompany(rs.getString("company")); game.setName(rs.getString("name")); game.setSummary(rs.getString("summary")); game.setWebsite(rs.getString("website")); game.setImage(rs.getString("image")); game.setReleaseDate(rs.getDate("releaseDate")); game.setUser_id(rs.getInt("user_id")); gameList.add(game); } } } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } return gameList; }
public ArrayList getFollowedLinkList(int theBegin, int theEnd, int templateId, int currentNodeId) throws SQLException { ArrayList list = new ArrayList(); String sql = "select * from wf_link where template_id = ? and current_node_id = ?"; PreparedStatement st = null; ResultSet rs = null; Connection conn = null; try { conn = ConnectionFactory.getConnection(); st = conn.prepareStatement(sql); if (theEnd > 0) st.setFetchSize(theEnd); st.setInt(1, templateId); st.setInt(2, currentNodeId); rs = st.executeQuery(); if (theBegin > 1) rs.absolute(theBegin - 1); while (rs.next()) { list.add(parseResultSet(rs)); if (rs.getRow() == theEnd) break; } } catch (SQLException e) { e.printStackTrace(); throw new SQLException(e.getMessage()); } finally { DBHelper.closeConnection(conn, st, rs); } return list; }
public ArrayList find(int theBegin, int theEnd) throws SQLException { ArrayList list = new ArrayList(); String sql = "select NODE_LINK_ID, NAME, DESCRIPTION, LINK_TYPE, TEMPLATE_ID, CURRENT_NODE_ID, NEXT_NODE_ID, EXECUTOR_RELATION, EXECUTORS_METHOD, NUMBER_OR_PERCENT, PASS_VALUE, EXPRESSION, DEFAULT_PATH, ACTION_NAME from WF_LINK"; PreparedStatement st = null; ResultSet rs = null; Connection conn = null; try { conn = ConnectionFactory.getConnection(); st = conn.prepareStatement(sql); if (theEnd > 0) st.setFetchSize(theEnd); rs = st.executeQuery(); if (theBegin > 1) rs.absolute(theBegin - 1); while (rs.next()) { list.add(parseResultSet(rs)); if (rs.getRow() == theEnd) break; } } catch (SQLException e) { e.printStackTrace(); throw new SQLException(e.getMessage()); } finally { DBHelper.closeConnection(conn, st, rs); } return list; }
public void absolute(ResultSet rs) throws SQLException { if (paginesql.getSql().equals(getSql()) && rs != null) { if (getOffset() > 0L) { rs.absolute((int) getOffset()); } } }
private void listaFacturasMouseClicked( java.awt.event.MouseEvent evt) { // GEN-FIRST:event_listaFacturasMouseClicked try { if (rsVenta == null || listaFacturas.getSelectedIndex() == -1) { return; } rsVenta.absolute(listaFacturas.getSelectedIndex() + 1); lbNumeroVenta.setText(rsVenta.getString("id_venta")); lbNombre.setText( rsVenta.getString("nombre_cliente") + " " + rsVenta.getString("apellido_cliente")); lbCedulaRif.setText(rsVenta.getString("cedula_rif")); lbfecha.setText(rsVenta.getDate("fecha_venta").toString()); lbDireccion.setText(rsVenta.getString("domicilio_cliente")); String sql = "SELECT venta.id_venta, detalle_venta.cantidad,detalle_venta.importe, " + "producto.nombre_producto,producto.descripcion_producto, producto.id_producto, producto.precio_producto " + "FROM public.venta " + "INNER JOIN public.detalle_venta ON venta.id_venta=detalle_venta.id_venta " + "INNER JOIN public.producto ON detalle_venta.id_producto=producto.id_producto " + "WHERE venta.id_venta =" + rsVenta.getInt("id_venta") + "ORDER BY id_venta "; Statement st = Conexion.getConnection() .createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY); ResultSet rs = st.executeQuery(sql); int cant = modeloTabla.getRowCount(); for (int i = 0; i < cant; i++) { modeloTabla.removeRow(0); } float subTotal = 0f; while (rs.next()) { subTotal += rs.getFloat("precio_producto") * rs.getInt("cantidad"); modeloTabla.addRow( new Object[] { rs.getInt("id_producto"), rs.getString("nombre_producto"), rs.getString("descripcion_producto"), rs.getInt("cantidad"), rs.getFloat("precio_producto"), subTotal }); } tfSubTotal.setText(subTotal + ""); tfIva.setText((subTotal * 0.12) + ""); tfTotal.setText(rsVenta.getFloat("total_venta") + ""); } catch (SQLException ex) { Logger.getLogger(cliente.class.getName()).log(Level.SEVERE, null, ex); } } // GEN-LAST:event_listaFacturasMouseClicked
public static int rowCount(ResultSet rs) throws Exception { int oldRow = rs.getRow(); rs.last(); int count = rs.getRow(); if (oldRow == 0) rs.beforeFirst(); else rs.absolute(oldRow); return count; }
/* * Execute query against a list of sensors * * */ public static String executeQuery( String envelope, String query, String matchingSensors, String format) throws ParseException { // String matchingSensors = getListOfSensorsAsString(envelope); String reformattedQuery = reformatQuery(query, matchingSensors); StringBuilder sb = new StringBuilder(); Connection connection = null; try { connection = Main.getDefaultStorage().getConnection(); Statement statement = connection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY); ResultSet results = statement.executeQuery(reformattedQuery); ResultSetMetaData metaData; // Additional information about the results int numCols, numRows; // How many rows and columns in the table metaData = results.getMetaData(); // Get metadata on them numCols = metaData.getColumnCount(); // How many columns? results.last(); // Move to last row numRows = results.getRow(); // How many rows? String s; // System.out.println("* Executing query *\n" + reformattedQuery + "\n***"); // headers // sb.append("# Query: " + query + NEWLINE); sb.append("# Query: " + reformattedQuery.replaceAll("\n", "\n# ") + NEWLINE); sb.append("# "); // System.out.println("ncols: " + numCols); // System.out.println("nrows: " + numRows); for (int col = 0; col < numCols; col++) { sb.append(metaData.getColumnLabel(col + 1)); if (col < numCols - 1) sb.append(SEPARATOR); } sb.append(NEWLINE); for (int row = 0; row < numRows; row++) { results.absolute(row + 1); // Go to the specified row for (int col = 0; col < numCols; col++) { Object o = results.getObject(col + 1); // Get value of the column // logger.warn(row + " , "+col+" : "+ o.toString()); if (o == null) s = "null"; else s = o.toString(); if (col < numCols - 1) sb.append(s).append(SEPARATOR); else sb.append(s); } sb.append(NEWLINE); } } catch (SQLException e) { sb.append("ERROR in execution of query: " + e.getMessage()); } finally { Main.getDefaultStorage().close(connection); } return sb.toString(); }
@Override public boolean absolute(int row) throws SQLException { try { return _res.absolute(row); } catch (SQLException e) { handleException(e); return false; } }
private void skipRows(ResultSet rs, RowBounds rowBounds) throws SQLException { if (rs.getType() != ResultSet.TYPE_FORWARD_ONLY) { if (rowBounds.getOffset() != RowBounds.NO_ROW_OFFSET) { rs.absolute(rowBounds.getOffset()); } } else { for (int i = 0; i < rowBounds.getOffset(); i++) { rs.next(); } } }
@Override public void traceMarker() throws Exception { Statement statement = connection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY); try { statement.execute("select * from employee"); ResultSet rs = statement.getResultSet(); rs.absolute(2); } finally { statement.close(); } }
// Delete row, return true if successful, false otherwise public static boolean deleteRow(int row) { try { resultSet.absolute(row + 1); resultSet.deleteRow(); // Tell table to redraw itself // fireTableDataChanged(); return true; } catch (SQLException se) { System.out.println("Delete row error " + se); return false; } }
private void initResultSet(ResultSet sql_set) throws SQLException { // Set the cursor on the right position if (sql_set.getType() == ResultSet.TYPE_FORWARD_ONLY) { for (int i = 1; i < mCurRow; i++) { sql_set.next(); } } else { sql_set.absolute(mCurRow); } mResult = new SoftReference<ResultSet>(sql_set); }
/** * Get the orderid's of those with status = 4 * * @return the orderid's */ static ArrayList<Integer> getOrderID() throws Exception { ArrayList<Integer> orders = new ArrayList<Integer>(); Connection conn = Database.staticGetConnection(); String query = "SELECT orderid FROM orders WHERE status = 4"; PreparedStatement st = conn.prepareStatement(query); ResultSet rs = st.executeQuery(); int x = 1; while (rs.absolute(x)) { orders.add(rs.getInt(1)); x++; } return orders; }
public synchronized Object get(int row) throws PageException { Object sv = getStoredValue(row); if (sv != SimpleQuery.DEFAULT_VALUE) return sv; try { if (row != res.getRow()) { res.absolute(row); } return _get(row); } catch (Throwable t) { throw Caster.toPageException(t); } }
public Object getValueAt(int r, int c) { if (rs == null) return -1; Object o = null; try { rs.beforeFirst(); rs.absolute(r + 1); o = rs.getObject(c + 1); } catch (SQLException e) { e.printStackTrace(); } return o; }
public synchronized Object get(int row, Object defaultValue) { Object sv = getStoredValue(row); if (sv != SimpleQuery.DEFAULT_VALUE) return sv; try { if (row != res.getRow()) { res.absolute(row); } return _get(row); } catch (Throwable t) { return defaultValue; } }
protected ResultSet send(String request) { ResultSet result; try { Statement state = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY); result = state.executeQuery(request); result.absolute(1); } catch (SQLException e) { if (e.getErrorCode() != 0) System.err.println("Unable set the connection to the database!/n"); result = null; } return result; }
private int skip(boolean cache, final ResultSet rs, int skipSize) throws SQLException { int skipped = 0; if (!cache) { for (int i = 0; i < skipSize; i++) { skipped++; if (!rs.next()) { break; } } } else { rs.absolute(skipSize); } return skipped; }
private int getRowCount(ResultSet rs) { int count = 0; try { rs.absolute(-1); count = rs.getRow(); rs.beforeFirst(); } catch (SQLException e) { printSQLException(e); } return count; }
private void testAbsolute() throws SQLException { // stat.execute("SET MAX_MEMORY_ROWS 90"); stat.execute("CREATE TABLE TEST(ID INT PRIMARY KEY)"); // there was a problem when more than MAX_MEMORY_ROWS where in the // result set stat.execute("INSERT INTO TEST SELECT X FROM SYSTEM_RANGE(1, 200)"); Statement s2 = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY); ResultSet rs = s2.executeQuery("SELECT * FROM TEST ORDER BY ID"); for (int i = 100; i > 0; i--) { rs.absolute(i); assertEquals(i, rs.getInt(1)); } stat.execute("DROP TABLE TEST"); }