Example usage for java.sql Statement 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:example.propertysource.MySqlPropertySourceApplicationTests.java

@Test
public void shouldProvideConfigurationThroughBean() throws SQLException {

    Connection connection = DriverManager.getConnection("jdbc:mysql://localhost?useSSL=false",
            databaseConfiguration.getUsername(), databaseConfiguration.getPassword());
    Statement statement = connection.createStatement();

    ResultSet resultSet = statement.executeQuery("SELECT CURRENT_USER();");

    assertThat(resultSet.next()).isTrue();
    log.info("Database user: Config({}), Reported by MySQL({})", databaseConfiguration.getUsername(),
            resultSet.getString(1));//w  w w  .j  ava 2s .  co  m

    resultSet.close();
    statement.close();
    connection.close();
}

---

From source file:me.redstarstar.rdfx.duty.dao.jdbc.ScheduleJdbcDao.java

@Override
public List<Schedule> listSchedulesByWeek(int week) {
    return jdbcTemplate.execute((Statement statement) -> {
        ResultSet rs = statement
                .executeQuery("SELECT * FROM schedule WHERE week = " + week + " ORDER BY activity_date");
        List<Schedule> schedules = new ArrayList<>();
        while (rs.next()) {
            Schedule schedule = new Schedule();
            schedule.setActivityDate(rs.getDate("activity_date").toLocalDate());
            schedule.setParentId(rs.getLong("parent_id"));
            schedule.setWeek(rs.getInt("week"));
            schedules.add(schedule);/*  w  w w . ja v a2s .c  o m*/
        }
        return schedules;
    });
}

---

From source file:edu.ku.brc.specify.dbsupport.cleanuptools.LocalityCleanup.java

public static void fixOld() {
    String connectStr = "jdbc:mysql://localhost/";

    String dbName = "kevin";

    DBConnection dbc = new DBConnection("root", "root", connectStr + dbName, "com.mysql.jdbc.Driver",
            "org.hibernate.dialect.MySQLDialect", dbName);
    Connection conn = dbc.createConnection();
    BasicSQLUtils.setDBConnection(conn);

    try {/*  ww w . j ava2s . com*/
        String sql = "SELECT LocalityName, cnt FROM (SELECT LocalityName, COUNT(LocalityName) as cnt FROM locality GROUP BY LocalityName) T1 WHERE cnt > 1 ORDER BY cnt desc ";

        Statement stmt = conn.createStatement();
        Statement stmt2 = conn.createStatement();
        PreparedStatement pStmt = conn
                .prepareStatement("UPDATE collectingevent SET LocalityID=? WHERE CollectingEventID = ?");

        int fixedCnt = 0;
        ResultSet rs = stmt.executeQuery(sql);
        while (rs.next()) {
            String locName = rs.getString(1);
            int cnt = rs.getInt(2);

            sql = String.format(
                    "SELECT LocalityID FROM locality WHERE LocalityName = '%s' ORDER BY LocalityID ASC",
                    locName);
            System.out.println(
                    "------------------------------------" + locName + "-----------------------------------");

            int c = 0;
            Integer firstID = null;

            ResultSet rs2 = stmt2.executeQuery(sql);
            while (rs2.next()) {
                int id = rs2.getInt(1);
                if (c == 0) {
                    firstID = id;
                    c = 1;
                    continue;
                }

                System.out.println("Fixing LocalityID: " + id);
                sql = String.format("SELECT CollectingEventId FROM collectingevent WHERE LocalityID = %d", id);
                Vector<Integer> ids = BasicSQLUtils.queryForInts(conn, sql);
                for (Integer ceId : ids) {
                    pStmt.setInt(1, firstID);
                    pStmt.setInt(2, ceId);
                    if (pStmt.executeUpdate() != 1) {
                        System.out.println("Error updating CE Id: " + ceId);
                    } else {
                        System.out
                                .println("Fixed CollectingEventID: " + ceId + "  with LocalityID: " + firstID);
                        fixedCnt++;
                    }
                }
                c++;
            }
            rs2.close();

            if (c != cnt) {
                System.out.println("Error updating all Localities for " + locName);
            }
        }
        rs.close();

        stmt.close();
        stmt2.close();
        pStmt.close();

        System.out.println("Fixed CE Ids: " + fixedCnt);

    } catch (SQLException ex) {
        ex.printStackTrace();
    }
}

---

From source file:net.ontopia.persistence.rdbms.CSVExport.java

public void exportCSV(Writer writer, String table, String[] columns) throws SQLException, IOException {
    Statement stm = conn.createStatement();
    ResultSet rs = stm.executeQuery("select " + StringUtils.join(columns, ", ") + " from " + table);
    try {/*  w  ww.  ja  va2 s.c  om*/
        while (rs.next()) {
            for (int i = 1; i <= columns.length; i++) {
                if (i > 1)
                    writer.write(separator);
                writer.write('"');
                String value = rs.getString(i);
                if (value != null) {
                    writer.write(StringUtils.replace(value, "\"", "\\\""));
                }
                writer.write('"');
            }
            writer.write('\n');
        }
    } finally {
        rs.close();
        stm.close();
    }
    writer.flush();
}

---

From source file:org.copperengine.core.test.persistent.BaseSpringTxnPersistentWorkflowTest.java

public void testSpringTxnUnitTestWorkflow(String dsContext) throws Exception {
    assumeFalse(skipTests());//from  ww  w .j  a va2s.  c om
    final ConfigurableApplicationContext context = createContext(dsContext);
    cleanDB(context.getBean(DataSource.class));
    final PersistentScottyEngine engine = context.getBean(PersistentScottyEngine.class);
    final BackChannelQueue backChannelQueue = context.getBean(BackChannelQueue.class);
    try {
        engine.startup();
        engine.run("org.copperengine.core.test.persistent.springtxn.SpringTxnUnitTestWorkflow", "TestData");
        WorkflowResult x = backChannelQueue.dequeue(60, TimeUnit.SECONDS);
        assertNotNull(x);
        assertNotNull(x.getResult());
        assertNull(x.getException());

        // check
        new RetryingTransaction<Void>(context.getBean(DataSource.class)) {
            @Override
            protected Void execute() throws Exception {
                Statement stmt = getConnection().createStatement();
                ResultSet rs = stmt.executeQuery("select count(*) from COP_AUDIT_TRAIL_EVENT");
                assertTrue(rs.next());
                int c = rs.getInt(1);
                assertEquals(7, c);
                rs.close();
                stmt.close();
                return null;
            }
        }.run();
    } finally {
        closeContext(context);
    }
    assertEquals(EngineState.STOPPED, engine.getEngineState());
    assertEquals(0, engine.getNumberOfWorkflowInstances());
}

---

From source file:edu.stanford.junction.sample.sql.QueryHandler.java

@Override
public void onMessageReceived(MessageHeader header, JSONObject message) {

    //String query = q.getQueryText();
    String query = message.optString("query");

    query = query.toLowerCase();//from   w w w  . jav  a2 s. c  o  m

    if (!query.contains("select"))
        return;
    if (query.contains("drop") || query.contains("delete"))
        return;
    System.out.println("Got query: " + query);

    Connection connection = null;
    try {
        // Load the JDBC driver
        String driverName = "com.mysql.jdbc.Driver"; // MySQL MM JDBC driver
        Class.forName(driverName);

        // Create a connection to the database
        //String serverName = "192.168.1.122";
        String serverName = "127.0.0.1";
        String mydatabase = "jinzora3";
        String url = "jdbc:mysql://" + serverName + "/" + mydatabase; // a JDBC url
        String username = "jinzora";
        String password = "jinzora";
        connection = DriverManager.getConnection(url, username, password);
    } catch (ClassNotFoundException e) {
        // Could not find the database driver
        e.printStackTrace();
    } catch (SQLException e) {
        // Could not connect to the database
        e.printStackTrace();
    }

    try {
        Statement stmt = connection.createStatement();
        ResultSet rs = stmt.executeQuery(query);

        ResultSetMetaData rsMetaData = rs.getMetaData();
        int cols = rsMetaData.getColumnCount();

        while (rs.next()) {

            JSONObject row = new JSONObject();
            try {
                for (int i = 1; i <= cols; i++) { // stupid indexing
                    row.put(rsMetaData.getColumnName(i), rs.getObject(i));
                }
            } catch (JSONException e) {
                e.printStackTrace();
            }
            System.out.println("sending " + row);
            if (mActor != null) {
                //mActor.getJunction().sendMessageToTarget(header.getReplyTarget(),row);
                header.getReplyTarget().sendMessage(row);
            }
        }
    } catch (SQLException e) {
        e.printStackTrace();
    }

    System.out.println("closing stream.");
    //results.close();
}

---

From source file:cc.osint.graphd.db.SQLDB.java

public JSONObject query(String expression) throws Exception {
    log.info("query(" + expression + ")");
    Statement st = null;
    ResultSet rs = null;//from  www . j a va  2 s.co m
    st = conn.createStatement();
    rs = st.executeQuery(expression);
    JSONObject result = jsonizeResultSet(rs);
    st.close();
    return result;
}

---

From source file:com.linuxrouter.netcool.session.QueryUtils.java

public ArrayList<HashMap<String, Object>> executeQuery(String dbName, String sql) {
    Long start = System.currentTimeMillis();
    ArrayList<HashMap<String, Object>> result = new ArrayList<>();
    HashMap<Integer, String> colTypes = new HashMap<Integer, String>();
    HashMap<Integer, String> colNames = new HashMap<Integer, String>();
    try {/*from  w w  w.  j av  a  2  s .c  o  m*/
        //connection caching...
        Connection con = null;
        if (connectionMap.get(dbName) == null) {
            BasicDataSource ds = DbUtils.getSimpleDataSourceByName(dbName);
            con = ds.getConnection();
            connectionMap.put(dbName, con);
        } else {
            con = connectionMap.get(dbName);

        }

        Statement st = con.createStatement();

        ResultSet rs = st.executeQuery(sql);
        ResultSetMetaData metaData = rs.getMetaData();
        int colCount = metaData.getColumnCount();
        for (int i = 1; i <= colCount; i++) {
            colTypes.put(i, metaData.getColumnTypeName(i));
            colNames.put(i, metaData.getColumnLabel(i));
        }
        while (rs.next()) {
            HashMap<String, Object> dado = new HashMap<>();
            for (int i = 1; i <= colCount; i++) {
                dado.put(colNames.get(i), rs.getObject(i));

            }
            result.add(dado);
        }
        rs.close();
        st.close();
        //con.close();
        Long end = System.currentTimeMillis();
        //logger.debug("Query on external DB took: " + (end - start) + "ms");
    } catch (SQLException ex) {
        logger.error("Erro ao executar query:", ex);
    }
    return result;
}

---

From source file:controladores.PeliculasController.java

public ModelAndView inicio(HttpServletRequest request, HttpServletResponse response) throws Exception {
    this.getConnection(request.getServletContext());
    ArrayList al = new ArrayList();
    try {/*from w w  w  .jav  a2  s  .c  o m*/
        Statement stmt = this.conexion.createStatement();
        ResultSet rs = stmt
                .executeQuery("SELECT ids, nombre, observaciones, tipopeli, precio, foto FROM peliculas");
        while (rs.next()) {
            Pelicula p = new Pelicula(rs.getInt("ids"), rs.getString("nombre"), rs.getString("tipopeli"),
                    rs.getString("observaciones"), rs.getInt("precio"), rs.getString("foto").toUpperCase());
            al.add(p);
        }
        rs.close();
    } catch (SQLException ex) {
        ex.printStackTrace();
    }
    ModelAndView mv = new ModelAndView("index");
    mv.addObject("peliculas", al);
    mv.addObject("metodo", "inicio");
    return mv;
}

---

From source file:com.glaf.report.jxls.ReportManagerImpl.java

public List<?> exec(String sql) throws SQLException {
    try {//from w ww .j  av  a 2s  .  c  o  m
        sql = evaluate(sql, beans);
    } catch (Exception ex) {
        log.error(sql);
        ex.printStackTrace();
    }
    sql = sql.replaceAll("&apos;", "'");
    Statement stmt = connection.createStatement();
    ResultSet rs = stmt.executeQuery(sql);
    RowSetDynaClass rsdc = new RowSetDynaClass(rs, false, true);
    stmt.close();
    rs.close();
    return rsdc.getRows();
}

---

• «
• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8
• 9
• 10
• 11
• 12
• 13
• 14
• 15
• 16
• 17
• 18
• 19
• 20
• 21
• 22
• 23
• 24
• 25
• 26
• 27
• 28
• 29
• 30
• 31
• 32
• 33
• 34
• 35
• 36
• 37
• 38
• 39
• 40
• 41
• 42
• 43
• 44
• 45
• 46
• 47
• 48
• 49
• 50
• »