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:javax.arang.DB.dbcp.BasicDataSourceExample.java

public static void main(String[] args) {
    // First we set up the BasicDataSource.
    // Normally this would be handled auto-magically by
    // an external configuration, but in this example we'll
    // do it manually.
    ////from w w w. j a  va2  s  .c  o  m
    System.out.println("Setting up data source.");
    DataSource dataSource = setupDataSource("jdbc:mysql://localhost:3306/FX");
    System.out.println("Done.");

    //
    // Now, we can use JDBC DataSource as we normally would.
    //
    Connection conn = null;
    Statement stmt = null;
    ResultSet rset = null;

    try {
        System.out.println("Creating connection.");
        conn = dataSource.getConnection();
        System.out.println("Creating statement.");
        stmt = conn.createStatement();
        System.out.println("Executing statement.");
        rset = stmt.executeQuery("select * from users");
        System.out.println("Results:");
        int numcols = rset.getMetaData().getColumnCount();
        while (rset.next()) {
            for (int i = 1; i <= numcols; i++) {
                System.out.print("\t" + rset.getString(i));
            }
            System.out.println("");
        }
    } catch (SQLException e) {
        e.printStackTrace();
    } finally {
        try {
            if (rset != null)
                rset.close();
        } catch (Exception e) {
        }
        try {
            if (stmt != null)
                stmt.close();
        } catch (Exception e) {
        }
        try {
            if (conn != null)
                conn.close();
        } catch (Exception e) {
        }
    }
}

From source file:Main.java

public static void main(String[] args) throws Exception {
    Connection conn = getConnection();
    conn.setAutoCommit(false);/*from   w  ww .j a  v  a2s  .com*/
    Statement stmt = conn.createStatement();

    stmt.executeUpdate("create table survey (id int, name CHAR(5) );");

    stmt.executeUpdate("INSERT INTO survey(id, name)VALUES(111, '123456789')");

    displayError(stmt.getWarnings());
    // try to write more data for the name column.
    displayError(stmt.getWarnings());

    // since there were no errors, commit
    conn.commit();

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

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

From source file:dbcp.BasicDataSourceExample.java

public static void main(String[] args) {
    // First we set up the BasicDataSource.
    // Normally this would be handled auto-magically by
    // an external configuration, but in this example we'll
    // do it manually.
    //// w w w . j  av a2s. c o m
    System.out.println("Setting up data source.");
    DataSource dataSource = setupDataSource("jdbc:MySQL://192.168.150.11:3306/test");
    System.out.println("Done.");

    //
    // Now, we can use JDBC DataSource as we normally would.
    //
    Connection conn = null;
    Statement stmt = null;
    ResultSet rset = null;

    try {
        System.out.println("Creating connection.");
        conn = dataSource.getConnection();
        System.out.println("Creating statement.");
        stmt = conn.createStatement();
        System.out.println("Executing statement.");
        rset = stmt.executeQuery("select * from Person");
        System.out.println("Results:");
        int numcols = rset.getMetaData().getColumnCount();
        while (rset.next()) {
            for (int i = 1; i <= numcols; i++) {
                System.out.print("\t" + rset.getString(i));
            }
            System.out.println("");
        }
        printDataSourceStats(dataSource);
    } catch (SQLException e) {
        e.printStackTrace();
    } finally {
        try {
            if (rset != null)
                rset.close();
        } catch (Exception e) {
        }
        try {
            if (stmt != null)
                stmt.close();
        } catch (Exception e) {
        }
        try {
            if (conn != null)
                conn.close();
        } catch (Exception e) {
        }
    }
}

From source file:Main.java

public static void main(String[] args) {
    Connection conn = null;//www .  java  2s .c o  m
    Statement stmt = null;
    ResultSet rs = null;
    try {
        String driver = "oracle.jdbc.driver.OracleDriver";
        Class.forName(driver).newInstance();
        System.out.println("Connecting to database...");
        String jdbcUrl = "jdbc:oracle:thin:@localhost:1521:ORCL";
        conn = DriverManager.getConnection(jdbcUrl, "yourName", "mypwd");
        stmt = conn.createStatement();
        try {
            rs = stmt.executeQuery("Select * from no_table_exisits");
        } catch (SQLException seRs) {
            String exMsg = "Message from MySQL Database";
            String exSqlState = "Exception";
            SQLException mySqlEx = new SQLException(exMsg, exSqlState);
            seRs.setNextException(mySqlEx);
            throw seRs;
        }
    } catch (SQLException se) {
        int count = 1;
        while (se != null) {
            System.out.println("SQLException " + count);
            System.out.println("Code: " + se.getErrorCode());
            System.out.println("SqlState: " + se.getSQLState());
            System.out.println("Error Message: " + se.getMessage());
            se = se.getNextException();
            count++;
        }
    } catch (Exception e) {
        e.printStackTrace();
    }
}

From source file:com.ingby.socbox.bischeck.test.JDBCtest.java

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

    CommandLineParser parser = new GnuParser();
    CommandLine line = null;//from w w  w  . jav a  2 s .  co  m

    // create the Options
    Options options = new Options();
    options.addOption("u", "usage", false, "show usage.");
    options.addOption("c", "connection", true, "the connection url");
    options.addOption("s", "sql", true, "the sql statement to run");
    options.addOption("m", "meta", true, "get the table meta data");
    options.addOption("C", "columns", true, "the number of columns to display, default 1");
    options.addOption("d", "driver", true, "the driver class");
    options.addOption("v", "verbose", false, "verbose outbout");

    try {
        // parse the command line arguments
        line = parser.parse(options, args);
    } catch (org.apache.commons.cli.ParseException e) {
        System.out.println("Command parse error:" + e.getMessage());
        HelpFormatter formatter = new HelpFormatter();
        formatter.printHelp("JDBCtest", options);
        Util.ShellExit(1);
    }

    if (line.hasOption("verbose")) {
        verbose = true;
    }

    if (line.hasOption("usage")) {
        HelpFormatter formatter = new HelpFormatter();
        formatter.printHelp("Bischeck", options);
        Util.ShellExit(0);
    }

    String driverclassname = null;
    if (!line.hasOption("driver")) {
        System.out.println("Driver class must be set");
        Util.ShellExit(1);
    } else {
        driverclassname = line.getOptionValue("driver");
        outputln("DriverClass: " + driverclassname);
    }

    String connectionname = null;
    if (!line.hasOption("connection")) {
        System.out.println("Connection url must be set");
        Util.ShellExit(1);
    } else {
        connectionname = line.getOptionValue("connection");
        outputln("Connection: " + connectionname);
    }

    String sql = null;
    String tablename = null;

    if (line.hasOption("sql")) {
        sql = line.getOptionValue("sql");
        outputln("SQL: " + sql);

    }

    if (line.hasOption("meta")) {
        tablename = line.getOptionValue("meta");
        outputln("Table: " + tablename);
    }

    int nrColumns = 1;
    if (line.hasOption("columns")) {
        nrColumns = new Integer(line.getOptionValue("columns"));
    }

    long execStart = 0l;
    long execEnd = 0l;
    long openStart = 0l;
    long openEnd = 0l;
    long metaStart = 0l;
    long metaEnd = 0l;

    Class.forName(driverclassname).newInstance();
    openStart = System.currentTimeMillis();
    Connection conn = DriverManager.getConnection(connectionname);
    openEnd = System.currentTimeMillis();

    if (tablename != null) {
        ResultSet rsCol = null;
        metaStart = System.currentTimeMillis();
        DatabaseMetaData md = conn.getMetaData();
        metaEnd = System.currentTimeMillis();

        rsCol = md.getColumns(null, null, tablename, null);
        if (verbose) {
            tabular("COLUMN_NAME");
            tabular("TYPE_NAME");
            tabular("COLUMN_SIZE");
            tabularlast("DATA_TYPE");
            outputln("");
        }

        while (rsCol.next()) {
            tabular(rsCol.getString("COLUMN_NAME"));
            tabular(rsCol.getString("TYPE_NAME"));
            tabular(rsCol.getString("COLUMN_SIZE"));
            tabularlast(rsCol.getString("DATA_TYPE"));
            outputln("", true);
        }
    }

    if (sql != null) {
        Statement stat = conn.createStatement();
        stat.setQueryTimeout(10);

        execStart = System.currentTimeMillis();
        ResultSet res = stat.executeQuery(sql);
        ResultSetMetaData rsmd = res.getMetaData();
        execEnd = System.currentTimeMillis();

        if (verbose) {
            for (int i = 1; i < nrColumns + 1; i++) {
                if (i != nrColumns)
                    tabular(rsmd.getColumnName(i));
                else
                    tabularlast(rsmd.getColumnName(i));
            }
            outputln("");
        }
        while (res.next()) {
            for (int i = 1; i < nrColumns + 1; i++) {
                if (i != nrColumns)
                    tabular(res.getString(i));
                else
                    tabularlast(res.getString(i));
            }
            outputln("", true);
        }

        stat.close();
        res.close();
    }

    conn.close();

    // Print the execution times
    outputln("Open time: " + (openEnd - openStart) + " ms");

    if (line.hasOption("meta")) {
        outputln("Meta time: " + (metaEnd - metaStart) + " ms");
    }

    if (line.hasOption("sql")) {
        outputln("Exec time: " + (execEnd - execStart) + " ms");
    }
}

From source file:SQLStatement.java

public static void main(String args[]) {

    String url = "jdbc:mySubprotocol:myDataSource";
    Connection con;/*  w ww.  ja  v a 2s  .c  o  m*/
    String query = "select SUPPLIERS.SUP_NAME, COFFEES.COF_NAME " + "from COFFEES, SUPPLIERS "
            + "where SUPPLIERS.SUP_NAME like 'Acme, Inc.' and " + "SUPPLIERS.SUP_ID = COFFEES.SUP_ID";
    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 rs = stmt.executeQuery(query);
        ResultSetMetaData rsmd = rs.getMetaData();
        int numberOfColumns = rsmd.getColumnCount();
        int rowCount = 1;
        while (rs.next()) {
            System.out.println("Row " + rowCount + ":  ");
            for (int i = 1; i <= numberOfColumns; i++) {
                System.out.print("   Column " + i + ":  ");
                System.out.println(rs.getString(i));
            }
            System.out.println("");
            rowCount++;
        }
        stmt.close();
        con.close();

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

From source file:Main.java

public static void main(String[] args) throws Exception {
    Connection conn = getConnection();
    Statement st = conn.createStatement();

    st.executeUpdate("create table survey (id int,myDate DATE);");
    String INSERT_RECORD = "insert into survey(id, myDate) values(?, ?)";

    PreparedStatement pstmt = conn.prepareStatement(INSERT_RECORD);
    pstmt.setString(1, "1");
    java.sql.Date sqlDate = new java.sql.Date(new java.util.Date().getTime());
    pstmt.setDate(2, sqlDate);// ww w.  ja  v a 2 s  .  c  o  m

    pstmt.executeUpdate();

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

    outputResultSet(rs);

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

From source file:Main.java

public static void main(String[] args) throws Exception {
    Connection conn = getConnection();
    Statement st = conn.createStatement();

    st.executeUpdate("create table survey (id int,myDate TIME);");
    String INSERT_RECORD = "insert into survey(id, myDate) values(?, ?)";

    PreparedStatement pstmt = conn.prepareStatement(INSERT_RECORD);
    pstmt.setString(1, "1");
    java.sql.Time sqlDate = new java.sql.Time(new java.util.Date().getTime());
    pstmt.setTime(2, sqlDate);//  ww  w .j ava  2 s  . co m

    pstmt.executeUpdate();

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

    outputResultSet(rs);

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

From source file:SqlException.java

public static void main(String[] args) {

    Connection conn = null;//from   w  ww.  j  a  v a  2 s.  c om
    Statement stmt = null;
    ResultSet rs = null;

    try {
        String driver = "oracle.jdbc.driver.OracleDriver";
        Class.forName(driver).newInstance();
        System.out.println("Connecting to database...");
        String jdbcUrl = "jdbc:oracle:thin:@localhost:1521:ORCL";
        conn = DriverManager.getConnection(jdbcUrl, "yourName", "mypwd");

        stmt = conn.createStatement();

        try {
            rs = stmt.executeQuery("Select * from no_table_exisits");
        } catch (SQLException seRs) {
            String exMsg = "Message from MySQL Database";
            String exSqlState = "Exception";
            SQLException mySqlEx = new SQLException(exMsg, exSqlState);
            seRs.setNextException(mySqlEx);
            throw seRs;
        }
    } catch (SQLException se) {
        int count = 1;
        while (se != null) {
            System.out.println("SQLException " + count);
            System.out.println("Code: " + se.getErrorCode());
            System.out.println("SqlState: " + se.getSQLState());
            System.out.println("Error Message: " + se.getMessage());
            se = se.getNextException();
            count++;
        }
    } catch (Exception e) {
        e.printStackTrace();
    }
}

From source file:Main.java

public static void main(String[] args) throws Exception {
    Connection conn = getConnection();
    Statement st = conn.createStatement();

    st.executeUpdate("create table survey (id int,myDate DATE );");
    String INSERT_RECORD = "insert into survey(id, myDate) values(?, ?)";

    PreparedStatement pstmt = conn.prepareStatement(INSERT_RECORD);
    pstmt.setString(1, "1");
    java.sql.Date sqlDate = new java.sql.Date(new java.util.Date().getTime());
    pstmt.setDate(2, sqlDate);//w w  w .j av a  2  s.  co  m

    pstmt.executeUpdate();

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

    Calendar cal = Calendar.getInstance();

    // get the TimeZone for "America/Los_Angeles"
    TimeZone tz = TimeZone.getTimeZone("America/Los_Angeles");
    cal.setTimeZone(tz);

    while (rs.next()) {
        // the JDBC driver will use the time zone information in
        // Calendar to calculate the date, with the result that
        // the variable dateCreated contains a java.sql.Date object
        // that is accurate for "America/Los_Angeles".
        java.sql.Date dateCreated = rs.getDate(2, cal);
        System.out.println(dateCreated);
    }

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