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:HSqlPrimerDesign.java

public static void main(String[] args) throws NoSuchFieldException, IllegalAccessException,
        ClassNotFoundException, InstantiationException, SQLException, FileNotFoundException {
    Class.forName(JDBC_DRIVER_HSQL).newInstance();
    conn = DriverManager.getConnection(DB_SERVER_URL, USER, PASS);
    PrintWriter log = new PrintWriter(new File("javalog.log"));
    DpalLoad.main(args);/*from w w  w. j a va2s. c  o m*/
    Dpal_Inst = DpalLoad.INSTANCE_WIN64;
    //        int[][] arr =calcHairpin("GGGGGGCCCCCCCCCCCCGGGGGGG",4);
    //        if(arr.length<=1){
    //            System.out.println("No Hairpin's found");
    //        }else{
    //            System.out.println("Hairpin(s) found");
    //        }
    Statement stat = conn.createStatement();
    ResultSet call = stat.executeQuery(
            "Select * From " + "Primerdb.primers where Cluster ='A1' and UniqueP =True and Bp = 20");
    Set<CharSequence> primers = new HashSet<>();
    while (call.next()) {
        primers.add(call.getString("Sequence"));
    }
    //        primers.stream().forEach(x->{
    //            log.println(x);
    //            log.println(complementarity(x, x, Dpal_Inst));
    //            int[][] arr =calcHairpin((String)x,4);
    //            if(arr.length<=1){
    //                log.println("No Hairpin's found");
    //            }else{
    //                log.println("Hairpin(s) found");
    //            }
    //            log.println();
    //            log.flush();
    //        });
}

From source file:jfutbol.com.jfutbol.GcmSender.java

public static void main(String[] args) {
    log.info("GCM - Sender running");
    do {/*  w  ww. j  a v a 2s. c o  m*/

        Connection conn = null;
        Connection conn2 = null;
        Statement stmt = null;
        Statement stmt2 = null;

        try {
            // STEP 2: Register JDBC driver
            Class.forName(JDBC_DRIVER);
            // STEP 3: Open a connection
            // System.out.println("Connecting to database...");
            conn = DriverManager.getConnection(DB_URL, USER, PASS);
            conn2 = DriverManager.getConnection(DB_URL, USER, PASS);
            // STEP 4: Execute a query
            // System.out.println("Creating statement...");
            stmt = conn.createStatement();
            String sql;
            sql = "SELECT userId FROM notifications WHERE sentByGCM=0 GROUP BY userId";
            ResultSet rs = stmt.executeQuery(sql);
            // STEP 5: Extract data from result set
            while (rs.next()) {
                log.info("Notification found");
                int userId = rs.getInt("userId");

                stmt2 = conn2.createStatement();
                String sql2;
                sql2 = "SELECT COUNT(id) notificationCounter FROM notifications WHERE status=0 AND userId="
                        + userId;
                ResultSet rs2 = stmt2.executeQuery(sql2);
                int notificationCounter = rs2.getInt("notificationCounter");
                rs2.close();
                stmt2.close();
                // Retrieve by column name

                // Display values
                // System.out.print("userId: " + userId);
                // System.out.print(", notificationCounter: " +
                // notificationCounter);
                SendNotification(userId, notificationCounter);
            }
            // STEP 6: Clean-up environment
            rs.close();
            stmt.close();
            conn.close();
            conn2.close();
        } catch (SQLException se) {
            // Handle errors for JDBC
            log.error(se.getMessage());
            se.printStackTrace();
        } catch (Exception e) {
            // Handle errors for Class.forName
            log.error(e.getMessage());
            e.printStackTrace();
        } finally {
            // finally block used to close resources
            try {
                if (stmt != null)
                    stmt.close();
            } catch (SQLException se2) {
                log.error(se2.getMessage());
            } // nothing we can do
            try {
                if (conn != null)
                    conn.close();
            } catch (SQLException se) {
                log.error(se.getMessage());
                se.printStackTrace();
            } // end finally try
        } // end try
        try {
            Thread.sleep(1000);
        } catch (InterruptedException e) {
            log.error(e.getMessage());
            e.printStackTrace();
        }
    } while (1 != 0);
}

From source file:BatchUpdate.java

public static void main(String args[]) {
    String url = "jdbc:mySubprotocol:myDataSource";
    Connection con;//ww  w . j  a v a  2 s. c  o m
    Statement stmt;

    try {
        Class.forName("myDriver.ClassName");
    } catch (java.lang.ClassNotFoundException e) {
        System.err.print("ClassNotFoundException: ");
        System.err.println(e.getMessage());
    }

    try {
        con = DriverManager.getConnection(url, "myLogin", "myPassword");
        stmt = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
        con.setAutoCommit(false);
        stmt.addBatch("INSERT INTO COFFEES " + "VALUES('Amaretto', 49, 9.99, 0, 0)");
        stmt.addBatch("INSERT INTO COFFEES " + "VALUES('Hazelnut', 49, 9.99, 0, 0)");
        stmt.addBatch("INSERT INTO COFFEES " + "VALUES('Amaretto_decaf', 49, 10.99, 0, 0)");
        stmt.addBatch("INSERT INTO COFFEES " + "VALUES('Hazelnut_decaf', 49, 10.99, 0, 0)");
        int[] updateCounts = stmt.executeBatch();
        ResultSet uprs = stmt.executeQuery("SELECT * FROM COFFEES");
        System.out.println("Table COFFEES after insertion:");

        while (uprs.next()) {
            String name = uprs.getString("COF_NAME");
            int id = uprs.getInt("SUP_ID");
            float price = uprs.getFloat("PRICE");
            int sales = uprs.getInt("SALES");
            int total = uprs.getInt("TOTAL");
            System.out.print(name + " " + id + " " + price);
            System.out.println(" " + sales + " " + total);
        }
        uprs.close();
        stmt.close();
        con.close();

    } catch (BatchUpdateException b) {
        System.err.println("SQLException: " + b.getMessage());
        System.err.println("SQLState: " + b.getSQLState());
        System.err.println("Message: " + b.getMessage());
        System.err.println("Vendor: " + b.getErrorCode());
        System.err.print("Update counts: ");
        int[] updateCounts = b.getUpdateCounts();
        for (int i = 0; i < updateCounts.length; i++) {
            System.err.print(updateCounts[i] + " ");
        }

    } catch (SQLException ex) {
        System.err.println("SQLException: " + ex.getMessage());
        System.err.println("SQLState: " + ex.getSQLState());
        System.err.println("Message: " + ex.getMessage());
        System.err.println("Vendor: " + ex.getErrorCode());
    }
}

From source file:Main.java

public static void main(String[] args) throws Exception {
    Connection conn = getConnection();
    conn.setAutoCommit(false);/*from  w  ww . j av a  2 s  . c  o m*/
    Statement st = conn.createStatement();

    st.executeUpdate("create table survey (id int, name VARCHAR(30) );");

    String INSERT_RECORD = "insert into survey(id, name) values(?,?)";

    PreparedStatement pstmt = conn.prepareStatement(INSERT_RECORD);
    pstmt.setString(1, "1");
    pstmt.setString(2, "name1");
    pstmt.addBatch();

    pstmt.setString(1, "2");
    pstmt.setString(2, "name2");
    pstmt.addBatch();

    try {
        // execute the batch
        int[] updateCounts = pstmt.executeBatch();
    } catch (BatchUpdateException e) {
        int[] updateCounts = e.getUpdateCounts();
        checkUpdateCounts(updateCounts);
        try {
            conn.rollback();
        } catch (Exception e2) {
            e.printStackTrace();
            System.exit(1);
        }
    }
    // since there were no errors, commit
    conn.commit();

    ResultSet rs = st.executeQuery("SELECT * FROM survey");
    outputResultSet(rs);

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

From source file:InsertSuppliers.java

public static void main(String args[]) {

    String url = "jdbc:mySubprotocol:myDataSource";
    Connection con;//from w ww .java2s  .c om
    Statement stmt;
    String query = "select SUP_NAME, SUP_ID from SUPPLIERS";

    try {
        Class.forName("myDriver.ClassName");

    } catch (java.lang.ClassNotFoundException e) {
        System.err.print("ClassNotFoundException: ");
        System.err.println(e.getMessage());
    }

    try {
        con = DriverManager.getConnection(url, "myLogin", "myPassword");

        stmt = con.createStatement();

        stmt.executeUpdate("insert into SUPPLIERS " + "values(49, 'Superior Coffee', '1 Party Place', "
                + "'Mendocino', 'CA', '95460')");

        stmt.executeUpdate("insert into SUPPLIERS " + "values(101, 'Acme, Inc.', '99 Market Street', "
                + "'Groundsville', 'CA', '95199')");

        stmt.executeUpdate("insert into SUPPLIERS " + "values(150, 'The High Ground', '100 Coffee Lane', "
                + "'Meadows', 'CA', '93966')");

        ResultSet rs = stmt.executeQuery(query);

        System.out.println("Suppliers and their ID Numbers:");
        while (rs.next()) {
            String s = rs.getString("SUP_NAME");
            int n = rs.getInt("SUP_ID");
            System.out.println(s + "   " + n);
        }

        stmt.close();
        con.close();

    } catch (SQLException ex) {
        System.err.println("SQLException: " + ex.getMessage());
    }
}

From source file:com.quest.orahive.HiveJdbcClient.java

public static void main(String[] args) {

    long mainStartTime = System.nanoTime();

    Configuration conf = new Configuration();
    Options options = new Options();

    parseGeneralOptions(options, conf, args); //<- log4j will now be configured.

    showWelcomeMessage();/*  w  ww.j  a  va2s. co m*/

    if (args.length == 0 || userWantsToSeeHelp(args)) {
        printCommandLineHelp(options);
        System.exit(0);
    }

    checkConfiguration(conf);

    OraHiveOptions opts = getOraHiveOptions(conf);
    OraHiveCounters counters = new OraHiveCounters();

    try {
        Connection hiveConnection = createHiveJdbcConnection(opts.hiveJdbcUrl, opts.hiveUserName,
                opts.hivePassword);
        try {
            Connection oracleConnection = createOracleJdbcConnection(opts.oracleJdbcUrl, opts.oracleUserName,
                    opts.oraclePassword);
            try {
                initializeOracleSession(oracleConnection, opts);

                Statement statement = hiveConnection.createStatement();

                LOG.info("Running: " + opts.hql);

                // Execute Hive Query...
                long start = System.nanoTime();
                ResultSet hiveResultSet = statement.executeQuery(opts.hql);
                counters.hiveQueryTimeNanoSec = System.nanoTime() - start;

                // Get column definitions from the Hive resultset...
                List<OracleTableColumn> oracleColumns = getOracleTableColumnsForHiveResults(hiveResultSet);

                if (opts.exportMode.equals(Constants.ExportMode.CREATE)) {
                    // Create an Oracle table based on the columns in the Hive resultset...
                    createOracleTableWithRetry(opts, oracleColumns, oracleConnection); //<- Lets the user retry this if it fails.
                }

                // Generate the Oracle insert statement...
                String insertSql = generateOracleInsertStatement(opts, oracleColumns);

                // Insert the Hive data into Oracle...
                insertHiveResultsIntoOracleTable(opts, insertSql, oracleColumns, oracleConnection,
                        hiveResultSet, counters);

                //hiveResultSet.close();   //<- Not required/supported
                statement.close();
            } finally {
                oracleConnection.close();
            }
        } finally {
            hiveConnection.close();
        }

    } catch (SQLException ex) {
        LOG.error(String.format("An error occurred in %s.", Constants.ORAHIVE_PRODUCT_NAME), ex);
    }

    LOG.info(String.format("\n\n********************************************************************\n"
            + "\tTotal time                        : %s sec.\n" + "\tNumber of records processed       : %s\n"
            + "\tTime spent executing HQL statement: %s sec.\n"
            + "\tTime spent fetching Hive data     : %s sec.\n"
            + "\tTime spent inserting into Oracle  : %s sec.",
            (System.nanoTime() - mainStartTime) / Math.pow(10, 9), counters.rowsProcessed,
            counters.hiveQueryTimeNanoSec / Math.pow(10, 9), counters.hiveFetchTimeNanoSec / Math.pow(10, 9),
            counters.oracleInsertTimeNanoSec / Math.pow(10, 9)));

}

From source file:BatchUpdate.java

public static void main(String args[]) throws SQLException {

    ResultSet rs = null;// ww  w . ja  v  a  2  s .com
    PreparedStatement ps = null;

    String url = "jdbc:mySubprotocol:myDataSource";

    Connection con;
    Statement stmt;
    try {

        Class.forName("myDriver.ClassName");

    } catch (java.lang.ClassNotFoundException e) {
        System.err.print("ClassNotFoundException: ");
        System.err.println(e.getMessage());
    }

    try {

        con = DriverManager.getConnection(url, "myLogin", "myPassword");
        con.setAutoCommit(false);

        stmt = con.createStatement();

        stmt.addBatch("INSERT INTO COFFEES " + "VALUES('Amaretto', 49, 9.99, 0, 0)");
        stmt.addBatch("INSERT INTO COFFEES " + "VALUES('Hazelnut', 49, 9.99, 0, 0)");
        stmt.addBatch("INSERT INTO COFFEES " + "VALUES('Amaretto_decaf', 49, 10.99, 0, 0)");
        stmt.addBatch("INSERT INTO COFFEES " + "VALUES('Hazelnut_decaf', 49, 10.99, 0, 0)");

        int[] updateCounts = stmt.executeBatch();
        con.commit();
        con.setAutoCommit(true);

        ResultSet uprs = stmt.executeQuery("SELECT * FROM COFFEES");

        System.out.println("Table COFFEES after insertion:");
        while (uprs.next()) {
            String name = uprs.getString("COF_NAME");
            int id = uprs.getInt("SUP_ID");
            float price = uprs.getFloat("PRICE");
            int sales = uprs.getInt("SALES");
            int total = uprs.getInt("TOTAL");
            System.out.print(name + "   " + id + "   " + price);
            System.out.println("   " + sales + "   " + total);
        }

        uprs.close();
        stmt.close();
        con.close();

    } catch (BatchUpdateException b) {
        System.err.println("-----BatchUpdateException-----");
        System.err.println("SQLState:  " + b.getSQLState());
        System.err.println("Message:  " + b.getMessage());
        System.err.println("Vendor:  " + b.getErrorCode());
        System.err.print("Update counts:  ");
        int[] updateCounts = b.getUpdateCounts();
        for (int i = 0; i < updateCounts.length; i++) {
            System.err.print(updateCounts[i] + "   ");
        }
        System.err.println("");

    } catch (SQLException ex) {
        System.err.println("-----SQLException-----");
        System.err.println("SQLState:  " + ex.getSQLState());
        System.err.println("Message:  " + ex.getMessage());
        System.err.println("Vendor:  " + ex.getErrorCode());
    }
}

From source file:InsertCoffees.java

public static void main(String args[]) {

    String url = "jdbc:mySubprotocol:myDataSource";
    Connection con;//from  w w  w  .j  a v  a 2 s  .c o  m
    Statement stmt;
    String query = "select COF_NAME, PRICE from COFFEES";

    try {
        Class.forName("myDriver.ClassName");

    } catch (java.lang.ClassNotFoundException e) {
        System.err.print("ClassNotFoundException: ");
        System.err.println(e.getMessage());
    }

    try {

        con = DriverManager.getConnection(url, "myLogin", "myPassword");

        stmt = con.createStatement();

        stmt.executeUpdate("insert into COFFEES " + "values('Colombian', 00101, 7.99, 0, 0)");

        stmt.executeUpdate("insert into COFFEES " + "values('French_Roast', 00049, 8.99, 0, 0)");

        stmt.executeUpdate("insert into COFFEES " + "values('Espresso', 00150, 9.99, 0, 0)");

        stmt.executeUpdate("insert into COFFEES " + "values('Colombian_Decaf', 00101, 8.99, 0, 0)");

        stmt.executeUpdate("insert into COFFEES " + "values('French_Roast_Decaf', 00049, 9.99, 0, 0)");

        ResultSet rs = stmt.executeQuery(query);

        System.out.println("Coffee Break Coffees and Prices:");
        while (rs.next()) {
            String s = rs.getString("COF_NAME");
            float f = rs.getFloat("PRICE");
            System.out.println(s + "   " + f);
        }

        stmt.close();
        con.close();

    } catch (SQLException ex) {
        System.err.println("SQLException: " + ex.getMessage());
    }
}

From source file:SimpleProgramToAccessOracleDatabase.java

public static void main(String[] args) throws SQLException {
    Connection conn = null; // connection object
    Statement stmt = null; // statement object
    ResultSet rs = null; // result set object
    try {//  w  ww . jav a  2  s .  c om
        conn = getConnection(); // without Connection, can not do much
        // create a statement: This object will be used for executing
        // a static SQL statement and returning the results it produces.
        stmt = conn.createStatement();
        // start a transaction
        conn.setAutoCommit(false);

        // create a table called cats_tricks
        stmt.executeUpdate("CREATE TABLE cats_tricks " + "(name VARCHAR2(30), trick VARCHAR2(30))");
        // insert two new records to the cats_tricks table
        stmt.executeUpdate("INSERT INTO cats_tricks VALUES('mono', 'r')");
        stmt.executeUpdate("INSERT INTO cats_tricks VALUES('mono', 'j')");

        // commit the transaction
        conn.commit();

        // set auto commit to true (from now on every single
        // statement will be treated as a single transaction
        conn.setAutoCommit(true);

        // get all of the the records from the cats_tricks table
        rs = stmt.executeQuery("SELECT name, trick FROM cats_tricks");

        // iterate the result set and get one row at a time
        while (rs.next()) {
            String name = rs.getString(1); // 1st column in query
            String trick = rs.getString(2); // 2nd column in query
            System.out.println("name=" + name);
            System.out.println("trick=" + trick);
            System.out.println("==========");
        }
    } catch (ClassNotFoundException ce) {
        // if the driver class not found, then we will be here
        System.out.println(ce.getMessage());
    } catch (SQLException e) {
        // something went wrong, we are handling the exception here
        if (conn != null) {
            conn.rollback();
            conn.setAutoCommit(true);
        }

        System.out.println("--- SQLException caught ---");
        // iterate and get all of the errors as much as possible.
        while (e != null) {
            System.out.println("Message   : " + e.getMessage());
            System.out.println("SQLState  : " + e.getSQLState());
            System.out.println("ErrorCode : " + e.getErrorCode());
            System.out.println("---");
            e = e.getNextException();
        }
    } finally { // close db resources
        try {
            rs.close();
            stmt.close();
            conn.close();
        } catch (Exception e) {
        }

    }
}

From source file:TypeMapDemo.java

public static void main(String[] args) throws IOException, ClassNotFoundException, SQLException {

    Properties p = new Properties();
    p.load(new FileInputStream("db.properties"));
    Class c = Class.forName(p.getProperty("db.driver"));
    System.out.println("Loaded driverClass " + c.getName());

    Connection con = DriverManager.getConnection(p.getProperty("db.url"), "student", "student");
    System.out.println("Got Connection " + con);

    Statement s = con.createStatement();
    int ret;/*  w  ww . ja va2  s. c o  m*/
    try {
        s.executeUpdate("drop table MR");
        s.executeUpdate("drop type MUSICRECORDING");
    } catch (SQLException andDoNothingWithIt) {
        // Should use "if defined" but not sure it works for UDTs...
    }
    ret = s.executeUpdate("create type MUSICRECORDING as object (" + "   id integer," + "   title varchar(20), "
            + "   artist varchar(20) " + ")");
    System.out.println("Created TYPE! Ret=" + ret);

    ret = s.executeUpdate("create table MR of MUSICRECORDING");
    System.out.println("Created TABLE! Ret=" + ret);

    int nRows = s.executeUpdate("insert into MR values(123, 'Greatest Hits', 'Ian')");
    System.out.println("inserted " + nRows + " rows");

    // Put the data class into the connection's Type Map
    // If the data class were not an inner class,
    // this would likely be done with Class.forName(...);
    Map map = con.getTypeMap();
    map.put("MUSICRECORDING", MusicRecording.class);
    con.setTypeMap(map);

    ResultSet rs = s.executeQuery("select * from MR where id = 123");
    //"select musicrecording(id,artist,title) from mr");
    rs.next();
    for (int i = 1; i <= rs.getMetaData().getColumnCount(); i++) {
        Object o = rs.getObject(i);
        System.out.print(o + "(Type " + o.getClass().getName() + ")\t");
    }
    System.out.println();
}