Example usage for java.sql Statement executeQuery

List of usage examples for java.sql Statement executeQuery

  1. HOME
  2. Java
  3. java
  4. java.sql.*
  5. Statement
  6. executeQuery

Introduction

In this page you can find the example usage for java.sql Statement executeQuery.

Prototype

ResultSet executeQuery(String sql) throws SQLException;

Source Link

Document

Executes the given SQL statement, which returns a single ResultSet object.

Usage

From source file:generadorqr.jifrGestionArticulos.java

public static DefaultTableModel LlenarTablaArticulos() {
    try {//from   w w w .ja  v  a 2 s.  c  o m
        String titulos[] = { "ID", "CATEGORIA", "NOMBRE", "CANTIDAD", "DESCRIPCION", "IMG 1", "IMG 2", "IMG 3",
                "SONIDO", "VIDEO", "IMAGEN QR" };
        String SQLTA = "SELECT a.IDARTICULO, c.NOMBRECATEGORIA, a.NOMBREARTICULO,a.CANTIDADARTICULO, a.DESCRIPCIONARTICULO, a.IMAGENUNOARTICULO, a.IMAGENDOSARTICULO, a.IMAGENTRESARTICULO, a.SONIDOARTICULO, a.VIDEOARTICULO, a.IMAGENQRARTICULO FROM articulos AS a INNER JOIN categorias AS c USING(IDCATEGORIA) ORDER BY a.IDARTICULO, c.NOMBRECATEGORIA, a.NOMBREARTICULO ASC";
        DefaultTableModel model = new DefaultTableModel(null, titulos);
        Statement sent = conn.createStatement();
        ResultSet rs = sent.executeQuery(SQLTA);
        String[] fila = new String[11];
        while (rs.next()) {
            fila[0] = rs.getString("IDARTICULO");
            fila[1] = rs.getString("NOMBRECATEGORIA");
            fila[2] = rs.getString("NOMBREARTICULO");
            fila[3] = rs.getString("CANTIDADARTICULO");
            fila[4] = rs.getString("DESCRIPCIONARTICULO");
            fila[5] = rs.getString("IMAGENUNOARTICULO");
            fila[6] = rs.getString("IMAGENDOSARTICULO");
            fila[7] = rs.getString("IMAGENTRESARTICULO");
            fila[8] = rs.getString("SONIDOARTICULO");
            fila[9] = rs.getString("VIDEOARTICULO");
            fila[10] = rs.getString("IMAGENQRARTICULO");
            model.addRow(fila);
        }
        return model;
    } catch (Exception e) {
        return null;
    }
}

From source file:generadorqr.jifrGestionArticulos.java

public static DefaultTableModel LlenarTablaArticulosporCategora() {
    try {/*from w  ww. j a v a 2  s.c o  m*/
        String titulos[] = { "ID", "CATEGORIA", "NOMBRE", "CANTIDAD", "DESCRIPCION", "IMG 1", "IMG 2", "IMG 3",
                "SONIDO", "VIDEO", "IMAGEN QR" };
        String SQLTA = "SELECT a.IDARTICULO, c.NOMBRECATEGORIA, a.NOMBREARTICULO,a.CANTIDADARTICULO, a.DESCRIPCIONARTICULO, a.IMAGENUNOARTICULO, a.IMAGENDOSARTICULO, a.IMAGENTRESARTICULO, a.SONIDOARTICULO, a.VIDEOARTICULO, a.IMAGENQRARTICULO FROM articulos AS a INNER JOIN categorias AS c USING(IDCATEGORIA) WHERE c.IDCATEGORIA = "
                + ItemSeleccionado.idCategoria + " ORDER BY a.IDARTICULO ASC";
        DefaultTableModel model = new DefaultTableModel(null, titulos);
        Statement sent = conn.createStatement();
        ResultSet rs = sent.executeQuery(SQLTA);
        String[] fila = new String[11];
        while (rs.next()) {
            fila[0] = rs.getString("IDARTICULO");
            fila[1] = rs.getString("NOMBRECATEGORIA");
            fila[2] = rs.getString("NOMBREARTICULO");
            fila[3] = rs.getString("CANTIDADARTICULO");
            fila[4] = rs.getString("DESCRIPCIONARTICULO");
            fila[5] = rs.getString("IMAGENUNOARTICULO");
            fila[6] = rs.getString("IMAGENDOSARTICULO");
            fila[7] = rs.getString("IMAGENTRESARTICULO");
            fila[8] = rs.getString("SONIDOARTICULO");
            fila[9] = rs.getString("VIDEOARTICULO");
            fila[10] = rs.getString("IMAGENQRARTICULO");
            model.addRow(fila);
        }
        return model;
    } catch (Exception e) {
        return null;
    }
}

From source file:MainClass.java

public MainClass() {
    try {//from  ww w .  j a  v a  2s.com
        Class.forName("COM.cloudscape.core.RmiJdbcDriver");
        Connection connection = DriverManager.getConnection("jdbc:cloudscape:rmi:books");
        Statement statement = connection.createStatement();
        ResultSet resultSet = statement.executeQuery("SELECT * FROM authors");
        ResultSetMetaData metaData = resultSet.getMetaData();
        int numberOfColumns = metaData.getColumnCount();

        for (int i = 1; i <= numberOfColumns; i++) {
            System.out.println(metaData.getColumnName(i) + "\t");
        }

        while (resultSet.next()) {

            for (int i = 1; i <= numberOfColumns; i++) {
                System.out.println(resultSet.getObject(i) + "\t");
            }

            System.out.println("\n");
        }

        statement.close();
        connection.close();
    } catch (SQLException sqlException) {
        System.out.println(sqlException.getMessage());
    } catch (ClassNotFoundException classNotFound) {
        System.out.println("Driver Not Found");
        System.exit(1);
    }
}

From source file:HelloMySQLJDBC.java

public void executeSQL() {
    try {//from ww  w  .  j  ava2  s  .c  o m
        Statement statement = connection.createStatement();

        ResultSet rs = statement.executeQuery("SELECT * FROM bool");

        while (rs.next()) {
            System.out.println(rs.getString("a") + " " + rs.getBoolean("a"));
            System.out.println(rs.getString("b") + " " + rs.getBoolean("b"));
            System.out.println(rs.getString("c") + " " + rs.getBoolean("c"));
            System.out.println(rs.getString("d") + " " + rs.getBoolean("d"));
        }

        rs.close();
        statement.close();
        connection.close();
    } catch (SQLException e) {
        displaySQLErrors(e);
    }
}

From source file:HSqlManager.java

public static void getClusterSizes(Connection connection) throws ClassNotFoundException, SQLException,
        InstantiationException, IllegalAccessException, IOException {

    Connection db = connection;/*ww  w. ja va 2s .  com*/
    db.setAutoCommit(false);
    Statement stat = db.createStatement();
    PrintWriter unique = new PrintWriter(new File("clustercount.log"));
    ResultSet call = stat.executeQuery("Select DISTINCT Cluster From Primerdb.Primers;");
    Set<String> clust = new HashSet<>();
    while (call.next()) {
        clust.add(call.getString("Cluster"));
    }
    unique.print("Cluster\t");
    unique.print("Phage Count\t");
    unique.print("18bp Unique Count\t");
    unique.print("22bp Unique Count\t");
    unique.print("25bp Unique Count\n");
    unique.flush();
    for (String c : clust) {
        call = stat.executeQuery("Select * From Primerdb.Primers where Cluster = '" + c + "'" + "and Bp = "
                + Integer.toString(18) + "and CommonP = true and UniqueP = true;");
        int count = 0;
        while (call.next()) {
            count++;
        }
        call = stat.executeQuery("Select * From Primerdb.Primers where Cluster = '" + c + "'" + "and Bp = "
                + Integer.toString(22) + "and CommonP = true and UniqueP = true;");
        int count2 = 0;
        while (call.next()) {
            count2++;
        }
        call = stat.executeQuery("Select * From Primerdb.Primers where Cluster = '" + c + "'" + "and Bp = "
                + Integer.toString(25) + "and CommonP = true and UniqueP = true;");
        int count3 = 0;
        while (call.next()) {
            count3++;
        }
        call = stat.executeQuery("Select * From Primerdb.Phages where Cluster = '" + c + "';");
        int count4 = 0;
        while (call.next()) {
            count4++;
        }
        unique.print(c + "\t");
        unique.print(count4 + "\t");
        unique.print(count + "\t");
        unique.print(count2 + "\t");
        unique.print(count3 + "\n");
        unique.flush();
    }
    System.out.println("Meta recorded");
}

From source file:edu.ku.brc.specify.conversion.ConvertMiscData.java

/**
 * @param oldDBConn/*  w  w w .j  a  v  a  2 s . c  o m*/
 * @param newDBConn
 * @param disciplineID
 */
public static void convertMethodFromStratGTP(final Connection oldDBConn, final Connection newDBConn) {
    String sql = null;
    Session localSession = null;
    try {
        localSession = HibernateUtil.getCurrentSession();
        HibernateUtil.beginTransaction();

        // Query to Create PickList
        sql = "SELECT gtp.Name, CONCAT(gtp.Name,' - ', gtp.Standard) as Method FROM collectingevent AS ce "
                + "Inner Join stratigraphy AS s ON ce.CollectingEventID = s.StratigraphyID "
                + "Inner Join geologictimeperiod AS gtp ON s.GeologicTimePeriodID = gtp.GeologicTimePeriodID "
                + "GROUP BY gtp.Name";

        PickList pl = (PickList) localSession.createQuery("FROM PickList WHERE Name = 'CollectingMethod'")
                .list().get(0);
        if (pl == null) {
            log.error("Couldn't find CollectingMethod.");
        }

        for (PickListItem pli : new Vector<PickListItem>(pl.getPickListItems())) {
            log.debug("Removing[" + pli.getTitle() + "]");
            localSession.delete(pli);
            pl.getPickListItems().remove(pli);
        }
        localSession.saveOrUpdate(pl);

        HibernateUtil.commitTransaction();

        HibernateUtil.beginTransaction();
        Vector<Object[]> list = BasicSQLUtils.query(oldDBConn, sql);
        for (Object[] cols : list) {
            PickListItem pli = new PickListItem();
            pli.initialize();

            pli.setTitle(cols[1].toString());
            pli.setValue(cols[0].toString());

            pl.getPickListItems().add(pli);
            pli.setPickList(pl);
            localSession.saveOrUpdate(pli);
        }

        localSession.saveOrUpdate(pl);

        HibernateUtil.commitTransaction();

        // Query for processing data
        sql = "SELECT ce.CollectingEventID, gtp.Name FROM collectingevent AS ce "
                + "Inner Join stratigraphy AS s ON ce.CollectingEventID = s.StratigraphyID "
                + "Inner Join geologictimeperiod AS gtp ON s.GeologicTimePeriodID = gtp.GeologicTimePeriodID "
                + "ORDER BY ce.CollectingEventID ASC";

        IdMapperMgr.getInstance().setDBs(oldDBConn, newDBConn);
        IdMapperIFace mapper = IdMapperMgr.getInstance().addTableMapper("collectingevent", "CollectingEventID",
                false);

        PreparedStatement pStmt = newDBConn
                .prepareStatement("UPDATE collectingevent SET Method=? WHERE CollectingEventID=?");
        Statement stmt = oldDBConn.createStatement();
        ResultSet rs = stmt.executeQuery(sql);
        while (rs.next()) {
            Integer newId = mapper.get(rs.getInt(1));
            pStmt.setString(1, rs.getString(2));
            pStmt.setInt(2, newId);
            pStmt.executeUpdate();
        }
        rs.close();
        stmt.close();

    } catch (Exception ex) {
        ex.printStackTrace();
        HibernateUtil.rollbackTransaction();
    }
}

From source file:Main.java

public Main() throws Exception {
    ArrayList columnNames = new ArrayList();
    ArrayList data = new ArrayList();
    String url = "jdbc:mysql://localhost:3306/yourdb";
    String userid = "root";
    String password = "sesame";
    String sql = "SELECT * FROM animals";

    Connection connection = DriverManager.getConnection(url, userid, password);
    Statement stmt = connection.createStatement();
    ResultSet rs = stmt.executeQuery(sql);
    ResultSetMetaData md = rs.getMetaData();
    int columns = md.getColumnCount();
    for (int i = 1; i <= columns; i++) {
        columnNames.add(md.getColumnName(i));
    }/*from   w ww  . java  2s . c  om*/
    while (rs.next()) {
        ArrayList row = new ArrayList(columns);
        for (int i = 1; i <= columns; i++) {
            row.add(rs.getObject(i));
        }
        data.add(row);
    }
    Vector columnNamesVector = new Vector();
    Vector dataVector = new Vector();
    for (int i = 0; i < data.size(); i++) {
        ArrayList subArray = (ArrayList) data.get(i);
        Vector subVector = new Vector();
        for (int j = 0; j < subArray.size(); j++) {
            subVector.add(subArray.get(j));
        }
        dataVector.add(subVector);
    }
    for (int i = 0; i < columnNames.size(); i++)
        columnNamesVector.add(columnNames.get(i));
    JTable table = new JTable(dataVector, columnNamesVector) {
        public Class getColumnClass(int column) {
            for (int row = 0; row < getRowCount(); row++) {
                Object o = getValueAt(row, column);
                if (o != null) {
                    return o.getClass();
                }
            }
            return Object.class;
        }
    };
    JScrollPane scrollPane = new JScrollPane(table);
    getContentPane().add(scrollPane);
    JPanel buttonPanel = new JPanel();
    getContentPane().add(buttonPanel, BorderLayout.SOUTH);
}

From source file:jp.yustam.batch.SampleBatchApplication.java

@Bean
protected Tasklet tasklet() {
    return new Tasklet() {
        @Override//w  w  w  .  j a  va2  s .  c  o  m
        public RepeatStatus execute(StepContribution contribution, ChunkContext context) throws SQLException {
            System.out.println("Hello, world!");
            System.out.println(config.getHost());
            System.out.println(config.getPort());

            String url = String.format("jdbc:postgresql://%s:%d/", config.getHost(), config.getPort());
            Properties props = new Properties();
            props.setProperty("user", "postgres");
            // props.setProperty("password", "secret");
            // props.setProperty("ssl", "true");
            Connection conn = DriverManager.getConnection(url, props);
            Statement statement = conn.createStatement();
            ResultSet rs = statement.executeQuery("SELECT * FROM company;");
            while (rs.next()) {
                System.out.printf("%d %d %s\n", rs.getInt("id"), rs.getInt("code"), rs.getString("name"));
            }

            return RepeatStatus.FINISHED;
        }
    };
}

From source file:metadata.etl.lineage.AzDbCommunicator.java

public String getExecLog(long execId, String jobName) throws SQLException, IOException {
    System.out.println("start");
    String cmd = "select log from execution_logs where exec_id = " + execId + " and name = '" + jobName
            + "'and attempt = 0 order by start_byte;";
    System.out.println(cmd);//from   www  . jav a 2s .c o  m
    Statement statement = conn.createStatement();
    ResultSet rs = statement.executeQuery(cmd);
    StringBuilder sb = new StringBuilder();
    while (rs.next()) {
        Blob logBlob = rs.getBlob("log");
        GZIPInputStream gzip = new GZIPInputStream(logBlob.getBinaryStream());
        sb.append(IOUtils.toString(gzip, "UTF-8"));
    }
    statement.close();
    System.out.println("stop");
    return sb.toString();
}

From source file:HSqlPrimerDesign.java

public static void checker(Connection connection, int bps) throws ClassNotFoundException, SQLException,
        InstantiationException, IllegalAccessException, IOException {
    String base = new File("").getAbsolutePath();
    Connection db = connection;/*from  ww w  .j  a  v  a2  s.  co  m*/
    db.setAutoCommit(false);
    Statement stat = db.createStatement();
    PrintWriter log = new PrintWriter(new File("checkertest.log"));
    ImportPhagelist.getInstance().parseAllPhagePrimers(bps);
    stat.execute("SET FILES LOG FALSE;\n");
    ResultSet call = stat.executeQuery("Select * From Primerdb.Phages;");
    List<String[]> phages = new ArrayList<>();
    String strain = "";
    while (call.next()) {
        String[] r = new String[3];
        r[0] = call.getString("Strain");
        r[1] = call.getString("Cluster");
        r[2] = call.getString("Name");
        phages.add(r);
        if (r[2].equals("xkcd")) {
            strain = r[0];
        }
    }
    String x = strain;
    phages.stream().filter(y -> y[0].equals(x)).map(y -> y[1]).collect(Collectors.toSet()).forEach(z -> {
        System.out.println("Starting:" + z);
        try {
            List<String> primers = new ArrayList<String>();
            Set<String> clustphages = phages.stream().filter(a -> a[0].equals(x) && a[1].equals(z))
                    .map(a -> a[2]).collect(Collectors.toSet());

            ResultSet resultSet = stat.executeQuery("Select * from primerdb.primers" + " where Strain ='" + x
                    + "' and Cluster ='" + z + "' and UniqueP = true" + " and Bp = " + Integer.valueOf(bps)
                    + " and Hairpin = false");
            while (resultSet.next()) {
                primers.add(resultSet.getString("Sequence"));
            }
            if (primers.size() > 0) {
                for (int i = 0; i < 4; i++) {
                    String primer = primers.get(i);
                    for (String clustphage : clustphages) {
                        if (!CSV.readCSV(base + "/PhageData/" + Integer.toString(bps) + clustphage + ".csv")
                                .contains(primer))
                            log.println("Problem " + z);
                    }
                }
                Set<String> nonclustphages = phages.stream().filter(a -> a[0].equals(x) && !a[1].equals(z))
                        .map(a -> a[2]).collect(Collectors.toSet());
                log.println("Cluster phages done");
                for (int i = 0; i < 4; i++) {
                    String primer = primers.get(i);
                    for (String nonclustphage : nonclustphages) {
                        if (CSV.readCSV(base + "/PhageData/" + Integer.toString(bps) + nonclustphage + ".csv")
                                .contains(primer))
                            log.println("Problem " + z);
                    }
                }
                log.println("NonCluster phages done");
            }
        } catch (SQLException e) {
            e.printStackTrace();
            System.out.println("Error occurred at " + x + " " + z);
        }
        log.println(z);
        log.flush();
        System.gc();
    });
    stat.execute("SET FILES LOG TRUE\n");
    stat.close();
    System.out.println("Primers Matched");
}