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:net.sf.webphotos.tools.Thumbnail.java

/**
 * Abre uma conexo com o banco de dados atravs da classe BancoImagem,
 * busca um lote de imagens e faz thumbs para todas as fotos. No possui
 * utilizaes.//from ww w . j av  a2 s  .  c  o  m
 */
public static void executaLote() {
    net.sf.webphotos.BancoImagem db = net.sf.webphotos.BancoImagem.getBancoImagem();

    try {
        db.configure("jdbc:mysql://localhost/test", "com.mysql.jdbc.Driver");
        BancoImagem.login();
        java.sql.Connection conn = BancoImagem.getConnection();
        java.sql.Statement st = conn.createStatement();
        java.sql.ResultSet rs = st.executeQuery("select * from fotos");

        int albumID, fotoID;
        String caminho;

        while (rs.next()) {
            albumID = rs.getInt("albumID");
            fotoID = rs.getInt("fotoID");
            caminho = "d:/bancoImagem/" + albumID + "/" + fotoID + ".jpg";
            makeThumbs(caminho);
            Util.out.println(caminho);
        }

        rs.close();
        st.close();
        conn.close();

    } catch (Exception e) {
        e.printStackTrace(Util.err);
    }
}

From source file:dbcount.DBCountPageView.java

private static boolean verify(final DBMapReduceJobConf jobConf, final long totalPageview) throws SQLException {
    //check total num pageview
    String dbUrl = jobConf.getReduceOutputDbUrl();
    final Connection conn;
    try {/*ww w. ja v a 2s .  co m*/
        conn = jobConf.getConnection(dbUrl, true);
    } catch (ClassNotFoundException e) {
        throw new IllegalStateException(e);
    }
    String sumPageviewQuery = "SELECT SUM(pageview) FROM Pageview";
    Statement st = null;
    ResultSet rs = null;
    try {
        st = conn.createStatement();
        rs = st.executeQuery(sumPageviewQuery);
        rs.next();
        long sumPageview = rs.getLong(1);

        LOG.info("totalPageview=" + totalPageview);
        LOG.info("sumPageview=" + sumPageview);

        return totalPageview == sumPageview && totalPageview != 0;
    } finally {
        if (st != null) {
            st.close();
        }
        if (rs != null) {
            rs.close();
        }
        conn.close();
    }
}

From source file:io.agi.framework.persistence.jdbc.JdbcUtil.java

/**
 * Execute a SQL query that returns data.
 *
 * @param dbUrl//from  w ww.  j av  a 2 s.  co m
 * @param user
 * @param password
 * @param sql
 * @param cb
 */
public static void ExecuteQuery(String dbUrl, String user, String password, String sql, ResultSetCallback cb) {
    Connection c = null;
    Statement s = null;
    try {

        //            c = DriverManager.getConnection(dbUrl, user, password);
        c = GetConnection(dbUrl, user, password);

        //STEP 4: Execute a query
        s = c.createStatement();
        ResultSet rs = s.executeQuery(sql);

        if (cb != null) {
            cb.onResultSet(rs);
        }

        //STEP 6: Clean-up environment
        rs.close();
        s.close();
        c.close();
    } catch (SQLException se) {
        logger.error(se.toString(), se);
    } catch (Exception e) {
        logger.error(s.toString(), s);
    } finally {
        try {
            if (s != null)
                s.close();
        } catch (SQLException se2) {
            logger.error(se2.toString(), se2);
        }

        try {
            if (c != null)
                c.close();
        } catch (SQLException se) {
            logger.error(se.toString(), se);
        }

    }
}

From source file:bizlogic.Records.java

public static void list(Connection DBcon) throws IOException, ParseException, SQLException {

    Statement st;
    ResultSet rs = null;//from  w  ww .j a  v a2 s  . com

    try {
        st = DBcon.createStatement();
        rs = st.executeQuery("SELECT userconf.log_list.sensor_id, " + "userconf.log_list.smpl_interval, "
                + "userconf.log_list.running, " + "userconf.log_list.name AS log_name, "
                + "userconf.log_list.log_id, " + "userconf.sensorlist.name AS sensor_name "
                + "FROM USERCONF.LOG_LIST " + "JOIN userconf.sensorlist "
                + "ON userconf.log_list.sensor_id=userconf.sensorlist.sensor_id");

    } catch (SQLException ex) {
        Logger lgr = Logger.getLogger(Records.class.getName());
        lgr.log(Level.SEVERE, ex.getMessage(), ex);
    }

    try {
        FileWriter recordsFile = new FileWriter("/var/lib/tomcat8/webapps/ROOT/Records/records.json");
        //BufferedWriter recordsFile = new BufferedWriter(_file);
        //recordsFile.write("");
        //recordsFile.flush(); 

        FileReader fr = new FileReader("/var/lib/tomcat8/webapps/ROOT/Records/records.json");
        BufferedReader br = new BufferedReader(fr);

        JSONObject Records = new JSONObject();

        int _total = 0;

        JSONArray recordList = new JSONArray();

        while (rs.next()) {

            String isRunningStr;

            JSONObject sensor_Obj = new JSONObject();

            int sensor_id = rs.getInt("sensor_id");
            sensor_Obj.put("sensor_id", sensor_id);

            String smpl_interval = rs.getString("smpl_interval");
            sensor_Obj.put("smpl_interval", smpl_interval);

            Boolean running = rs.getBoolean("running");
            if (running) {
                //System.out.print("1");
                isRunningStr = "ON";
            } else {
                //System.out.print("0");
                isRunningStr = "OFF";
            }
            sensor_Obj.put("running", isRunningStr);

            String log_name = rs.getString("log_name");
            sensor_Obj.put("log_name", log_name);

            String sensor_name = rs.getString("sensor_name");
            sensor_Obj.put("sensor_name", sensor_name);

            int log_id = rs.getInt("log_id");
            sensor_Obj.put("recid", log_id);

            recordList.add(sensor_Obj);
            _total++;

        }

        rs.close();

        Records.put("total", _total);
        Records.put("records", recordList);

        recordsFile.write(Records.toJSONString());
        recordsFile.flush();

        recordsFile.close();

        System.out.print(Records.toJSONString());
        System.out.print(br.readLine());

    }

    catch (IOException ex) {
        Logger.getLogger(Records.class.getName()).log(Level.SEVERE, null, ex);
    }
}

From source file:Student.java

public static void checkData() throws Exception {
        Class.forName("org.hsqldb.jdbcDriver");
        Connection conn = DriverManager.getConnection("jdbc:hsqldb:data/tutorial", "sa", "");
        Statement st = conn.createStatement();

        ResultSet mrs = conn.getMetaData().getTables(null, null, null, new String[] { "TABLE" });
        while (mrs.next()) {
            String tableName = mrs.getString(3);
            System.out.println("\n\n\n\nTable Name: " + tableName);

            ResultSet rs = st.executeQuery("select * from " + tableName);
            ResultSetMetaData metadata = rs.getMetaData();
            while (rs.next()) {
                System.out.println(" Row:");
                for (int i = 0; i < metadata.getColumnCount(); i++) {
                    System.out.println("    Column Name: " + metadata.getColumnLabel(i + 1) + ",  ");
                    System.out.println("    Column Type: " + metadata.getColumnTypeName(i + 1) + ":  ");
                    Object value = rs.getObject(i + 1);
                    System.out.println("    Column Value: " + value + "\n");
                }/* w w  w .  j a v  a  2s.c  o  m*/
            }
        }
    }

From source file:com.adaptris.core.util.JdbcUtil.java

public static Connection testConnection(Connection sqlConnection, String testStatement, boolean debugMode)
        throws SQLException {
    Statement stmt = sqlConnection.createStatement();
    ResultSet rs = null;//from  w  w  w.j  a  v a  2 s .c  o  m
    try {
        if (isEmpty(testStatement)) {
            return sqlConnection;
        }
        if (debugMode) {
            rs = stmt.executeQuery(testStatement);
            if (rs.next()) {
                StringBuffer sb = new StringBuffer("TestStatement Results - ");
                ResultSetMetaData rsm = rs.getMetaData();
                for (int i = 1; i <= rsm.getColumnCount(); i++) {
                    sb.append("[");
                    sb.append(rsm.getColumnName(i));
                    sb.append("=");
                    sb.append(rs.getObject(i));
                    sb.append("] ");
                }
                log.trace(sb.toString());
            }
        } else {
            stmt.execute(testStatement);
        }
    } finally {
        JdbcUtil.closeQuietly(rs);
        JdbcUtil.closeQuietly(stmt);
    }
    return sqlConnection;
}

From source file:br.ufmt.ic.pawii.util.Estados.java

public static String getEstados(String estado, String nome, String email) {
    try {//  www  .  ja  v a2  s  . com

        String strCidades;

        Statement stm;

        Connection con = ConnBD.getConnection();

        if (con == null) {
            throw new SQLException("Erro conectando");
        }

        stm = con.createStatement();

        String sql = "SELECT codigo,municipio FROM municipios" + " WHERE uf='" + estado
                + "' ORDER BY municipio ASC";

        ResultSet rs = stm.executeQuery(sql);

        JSONArray cidades = new JSONArray();

        while (rs.next()) {

            String codigo = rs.getString(1);
            String cidade = rs.getString(2);

            JSONObject jsonCidade = new JSONObject();
            jsonCidade.put("codigo", codigo);
            jsonCidade.put("nome", cidade);
            cidades.put(jsonCidade);
        }

        JSONObject jsonRetorno = new JSONObject();
        jsonRetorno.put("cidades", cidades);
        jsonRetorno.put("seuNome", nome);
        jsonRetorno.put("seuEmail", email);

        strCidades = jsonRetorno.toString();

        return strCidades;

    } catch (SQLException ex) {
        System.out.println("Error: " + ex.getMessage());
    } catch (Exception e) {
        System.out.println("Error: " + e.getMessage());
    }

    return null;
}

From source file:bizlogic.Records.java

public static void writeCSV(Connection DBcon, String record_id) throws SQLException {

    Statement st;
    ResultSet rs = null;//from   ww  w  .j av a 2  s  .com

    System.out.println("WriteCSV started");

    try {
        st = DBcon.createStatement();
        rs = st.executeQuery("SELECT * FROM PUBLIC.t" + record_id);
        System.out.println("Result set read finished");
    } catch (SQLException ex) {
        Logger lgr = Logger.getLogger(Records.class.getName());
        lgr.log(Level.SEVERE, ex.getMessage(), ex);
    }

    try {

        String DELIMITER = ",";
        String NEW_LINE = "\n";
        String FILE_HEADER = "Time,Series";

        System.out.println("Delete old file");
        FileWriter csvFile = new FileWriter("/var/lib/tomcat8/webapps/ROOT/Records/Data/" + record_id + ".csv");

        //BufferedWriter csvFile = new BufferedWriter(
        //        new OutputStreamWriter(new FileOutputStream(new File(
        //                "/var/lib/tomcat8/webapps/ROOT/Records/Data/" + 
        //                        record_id + ".csv"))));

        csvFile.write("");

        csvFile.append(FILE_HEADER);
        csvFile.append(NEW_LINE);

        Calendar calendar = new GregorianCalendar();

        System.out.println("Writing file...");
        while (rs.next()) {

            long time_stamp = rs.getLong("time");
            double value = rs.getDouble("value");
            String _year;
            String _month;
            String _day;
            String _hour;
            String _min;
            String _sec;

            calendar.setTimeInMillis(time_stamp);

            _year = Integer.toString(calendar.get(Calendar.YEAR));
            _month = Integer.toString(calendar.get(Calendar.MONTH) + 1);
            _day = Integer.toString(calendar.get(Calendar.DAY_OF_MONTH));
            _hour = Integer.toString(calendar.get(Calendar.HOUR_OF_DAY));
            _min = Integer.toString(calendar.get(Calendar.MINUTE));
            _sec = Integer.toString(calendar.get(Calendar.SECOND));

            csvFile.append(_year + "/" + _month + "/" + _day + " " + _hour + ":" + _min + ":" + _sec + DELIMITER
                    + Double.toString(value) + NEW_LINE); //new Date("2009/07/19 12:34:56")

        }
        System.out.print("File written");
        rs.close();
        //csvFile.flush();
        csvFile.close();
    }

    catch (IOException ex) {
        Logger.getLogger(Records.class.getName()).log(Level.WARNING, null, ex);
    }
}

From source file:com.cloudera.sqoop.manager.PostgresqlExportTest.java

public static void assertRowCount(long expected, String tableName, Connection connection) {
    Statement stmt = null;
    ResultSet rs = null;/*  w w w .java  2  s .  c  o  m*/
    try {
        stmt = connection.createStatement();
        rs = stmt.executeQuery("SELECT count(*) FROM " + tableName);

        rs.next();

        assertEquals(expected, rs.getLong(1));
    } catch (SQLException e) {
        LOG.error("Can't verify number of rows", e);
        fail();
    } finally {
        try {
            connection.commit();

            if (stmt != null) {
                stmt.close();
            }
            if (rs != null) {
                rs.close();
            }
        } catch (SQLException ex) {
            LOG.info("Ignored exception in finally block.");
        }
    }
}

From source file:jp.co.acroquest.endosnipe.data.dao.AbstractDao.java

/**
 * ????<br />// ww  w .  j av a 2 s.  c  om
 * 
 * @param database ??
 * @param table ??
 * @param notNullKey NULL ???
 * @return ?
 * @throws SQLException SQL ?????
 */
protected static int count(final String database, final String table, final String notNullKey)
        throws SQLException {
    int count = 0;
    Connection conn = null;
    Statement stmt = null;
    ResultSet rs = null;
    try {
        conn = getConnection(database, true);
        String sql = "select count(" + notNullKey + ") from " + table;
        stmt = conn.createStatement();
        rs = stmt.executeQuery(sql);
        if (rs.next() == true) {
            count = rs.getInt(1);
        }
    } finally {
        SQLUtil.closeResultSet(rs);
        SQLUtil.closeStatement(stmt);
        SQLUtil.closeConnection(conn);
    }
    return count;
}