Example usage for java.sql PreparedStatement executeQuery

List of usage examples for java.sql PreparedStatement executeQuery

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

Introduction

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

Prototype

ResultSet executeQuery() throws SQLException;

Source Link

Document

Executes the SQL query in this PreparedStatement object and returns the ResultSet object generated by the query.

Usage

From source file:DynaBeansExampleV3.java

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

    Connection conn = getConnection();
    PreparedStatement ps = conn
            .prepareStatement("SELECT * from movie, person " + "WHERE movie.director = person.Id");
    ResultSet rs = ps.executeQuery();

    RowSetDynaClass rsdc = new RowSetDynaClass(rs);

    conn.close();// w  w  w . j a v  a 2s .c o m

    Iterator itr = rsdc.getRows().iterator();
    while (itr.hasNext()) {
        DynaBean bean = (DynaBean) itr.next();
        System.err.println(bean.get("title"));
    }

}

From source file:Main.java

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

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

    String INSERT_RECORD = "select * from survey where id < ?";

    PreparedStatement pstmt = conn.prepareStatement(INSERT_RECORD);

    pstmt.setInt(1, 1);

    pstmt.setFetchSize(200);

    ResultSet rs = pstmt.executeQuery();

    outputResultSet(rs);

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

From source file:Main.java

public static void main(String[] args) throws Exception {
    Connection con = DriverManager.getConnection("jdbc:mysql://192.168.1.3/zzzTest?" + //
            "useUnicode=yes&characterEncoding=UTF-8" + //
            "&user=root&password=whatever");
    String newName = "G";
    String newEmail = "g@example.com";
    String newMobile = "444-555-2222";
    String sql = "SELECT " + //
            "id, " + //
            "name, " + //
            "email, " + //
            "mobile " + //
            "FROM registerSmsUsers " + //
            "WHERE mobile = ? " + //
            "FOR UPDATE";
    PreparedStatement pst = con.prepareStatement(sql, ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
    pst.setString(1, newMobile);//  ww  w  .j  a va 2  s .c om
    ResultSet rs = pst.executeQuery();
    if (rs.next()) {
        rs.moveToCurrentRow();
        rs.updateString("name", newName);
        rs.updateString("email", newEmail);
        rs.updateRow();
        System.out.println("Existing row updated.");
    } else {
        rs.moveToInsertRow();
        rs.updateString("name", newName);
        rs.updateString("email", newEmail);
        rs.updateString("mobile", newMobile);
        rs.insertRow();
        System.out.println("New row inserted.");
    }
}

From source file:Main.java

public static void main(String[] args) throws Exception {
    Connection conn = getOracleConnection();
    String[] columnNames = { "id", "name", "content", "date_created" };
    Object[] inputValues = new Object[columnNames.length];
    inputValues[0] = new java.math.BigDecimal(100);
    inputValues[1] = new String("String Value");
    inputValues[2] = new String("This is my resume.");
    inputValues[3] = new Timestamp((new java.util.Date()).getTime());

    String insert = "insert into resume (id, name, content, date_created ) values(?, ?, ?, ?)";
    PreparedStatement pstmt = conn.prepareStatement(insert);

    pstmt.setObject(1, inputValues[0]);/* w ww .j a  va 2s  . c  om*/
    pstmt.setObject(2, inputValues[1]);
    pstmt.setObject(3, inputValues[2]);
    pstmt.setObject(4, inputValues[3]);

    pstmt.executeUpdate();
    String query = "select id, name, content, date_created from resume where id=?";

    PreparedStatement pstmt2 = conn.prepareStatement(query);
    pstmt2.setObject(1, inputValues[0]);
    ResultSet rs = pstmt2.executeQuery();
    Object[] outputValues = new Object[columnNames.length];
    if (rs.next()) {
        for (int i = 0; i < columnNames.length; i++) {
            outputValues[i] = rs.getObject(i + 1);
        }
    }
    System.out.println("id=" + ((java.math.BigDecimal) outputValues[0]).toString());
    System.out.println("name=" + ((String) outputValues[1]));
    System.out.println("content=" + ((Clob) outputValues[2]));
    System.out.println("date_created=" + ((java.sql.Date) outputValues[3]).toString());

    rs.close();
    pstmt.close();
    pstmt2.close();
    conn.close();

}

From source file:Main.java

public static final void main(String[] argv) throws Exception {
    Class.forName("oracle.jdbc.OracleDriver");
    Connection conn = DriverManager.getConnection("your_connection_string", "your_user_name", "your_password");
    Date nowDate = new Date();
    Timestamp nowTimestamp = new Timestamp(nowDate.getTime());
    PreparedStatement insertStmt = conn.prepareStatement(
            "INSERT INTO MyTable" + " (os_name, ts, ts_with_tz, ts_with_local_tz)" + " VALUES (?, ?, ?, ?)");
    insertStmt.setString(1, System.getProperty("os.name"));
    insertStmt.setTimestamp(2, nowTimestamp);
    insertStmt.setTimestamp(3, nowTimestamp);
    insertStmt.setTimestamp(4, nowTimestamp);
    insertStmt.executeUpdate();/*from w  w w . j  ava 2 s .  co m*/
    insertStmt.close();
    System.out.println("os_name, ts, ts_with_tz, ts_with_local_tz");
    PreparedStatement selectStmt = conn
            .prepareStatement("SELECT os_name, ts, ts_with_tz, ts_with_local_tz" + " FROM MyTable");
    ResultSet result = null;
    result = selectStmt.executeQuery();
    while (result.next()) {
        System.out.println(String.format("%s,%s,%s,%s", result.getString(1), result.getTimestamp(2).toString(),
                result.getTimestamp(3).toString(), result.getTimestamp(4).toString()));
    }
    result.close();
    selectStmt.close();
    conn.close();
}

From source file:GetDateFromOracle.java

public static void main(String args[]) {
    String GET_RECORD = "select date_column, time_column, " + "timestamp_column from TestDates where id = ?";
    ResultSet rs = null;/*from   w w  w  . j a v  a 2  s .co  m*/
    Connection conn = null;
    PreparedStatement pstmt = null;
    try {
        conn = getConnection();
        pstmt = conn.prepareStatement(GET_RECORD);
        pstmt.setString(1, "0001");
        rs = pstmt.executeQuery();
        while (rs.next()) {
            java.sql.Date dbSqlDate = rs.getDate(1);
            java.sql.Time dbSqlTime = rs.getTime(2);
            java.sql.Timestamp dbSqlTimestamp = rs.getTimestamp(3);
            System.out.println("dbSqlDate=" + dbSqlDate);
            System.out.println("dbSqlTime=" + dbSqlTime);
            System.out.println("dbSqlTimestamp=" + dbSqlTimestamp);
        }
    } catch (Exception e) {
        e.printStackTrace();
    } finally {
        try {
            rs.close();
            pstmt.close();
            conn.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

From source file:ImageStringToBlob.java

public static void main(String[] args) {
    Connection conn = null;// w  w  w  .  j ava  2 s  .c o  m

    if (args.length != 1) {
        System.out.println("Missing argument: full path to <oscar.properties>");
        return;
    }

    try {

        FileInputStream fin = new FileInputStream(args[0]);
        Properties prop = new Properties();
        prop.load(fin);

        String driver = prop.getProperty("db_driver");
        String uri = prop.getProperty("db_uri");
        String db = prop.getProperty("db_name");
        String username = prop.getProperty("db_username");
        String password = prop.getProperty("db_password");

        Class.forName(driver);
        conn = DriverManager.getConnection(uri + db, username, password);
        conn.setAutoCommit(true); // no transactions

        /*
         * select all records ids with image_data not null and contents is null
         * for each id fetch record
         * migrate data from image_data to contents
         */
        String sql = "select image_id from client_image where image_data is not null and contents is null";
        PreparedStatement pst = conn.prepareStatement(sql);
        ResultSet rs = pst.executeQuery();
        List<Long> ids = new ArrayList<Long>();

        while (rs.next()) {
            ids.add(rs.getLong("image_id"));
        }

        rs.close();

        sql = "select image_data from client_image where image_id = ?";
        pst = conn.prepareStatement(sql);

        System.out.println("Migrating image data for " + ids.size() + " images...");
        for (Long id : ids) {
            pst.setLong(1, id);
            ResultSet imagesRS = pst.executeQuery();
            while (imagesRS.next()) {
                String dataString = imagesRS.getString("image_data");
                Blob dataBlob = fromStringToBlob(dataString);
                if (writeBlobToDb(conn, id, dataBlob) == 1) {
                    System.out.println("Image data migrated for image_id: " + id);
                }
            }
            imagesRS.close();
        }
        System.out.println("Migration completed.");

    } catch (Exception e) {
        e.printStackTrace();
    } finally {
        if (conn != null) {
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

From source file:CountRecordsUsingPreparedStatement.java

public static void main(String[] args) {
    ResultSet rs = null;//from  w w  w .j a  v a2s.c o  m
    Connection conn = null;
    PreparedStatement pstmt = null;
    try {
        conn = getConnection();
        String query = "select count(*) from tableName";
        pstmt = conn.prepareStatement(query);
        rs = pstmt.executeQuery();
        if (rs.next()) {
            int numberOfRows = rs.getInt(1);
            System.out.println("numberOfRows= " + numberOfRows);
        } else {
            System.out.println("error: could not get the record counts");
        }
    } catch (Exception e) {
        e.printStackTrace();
    } finally {
        try {
            rs.close();
            pstmt.close();
            conn.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

From source file:Main.java

public static void main(String[] args) throws Exception {
    String WRITE_OBJECT_SQL = "BEGIN " + "  INSERT INTO java_objects(object_id, object_name, object_value) "
            + "  VALUES (?, ?, empty_blob()) " + "  RETURN object_value INTO ?; " + "END;";
    String READ_OBJECT_SQL = "SELECT object_value FROM java_objects WHERE object_id = ?";

    Connection conn = getOracleConnection();
    conn.setAutoCommit(false);//from  w  w  w. j av a  2 s. c  o  m
    List<Object> list = new ArrayList<Object>();
    list.add("This is a short string.");
    list.add(new Integer(1234));
    list.add(new java.util.Date());

    // write object to Oracle
    long id = 0001;
    String className = list.getClass().getName();
    CallableStatement cstmt = conn.prepareCall(WRITE_OBJECT_SQL);

    cstmt.setLong(1, id);
    cstmt.setString(2, className);

    cstmt.registerOutParameter(3, java.sql.Types.BLOB);

    cstmt.executeUpdate();
    BLOB blob = (BLOB) cstmt.getBlob(3);
    OutputStream os = blob.getBinaryOutputStream();
    ObjectOutputStream oop = new ObjectOutputStream(os);
    oop.writeObject(list);
    oop.flush();
    oop.close();
    os.close();

    // Read object from oracle
    PreparedStatement pstmt = conn.prepareStatement(READ_OBJECT_SQL);
    pstmt.setLong(1, id);
    ResultSet rs = pstmt.executeQuery();
    rs.next();
    InputStream is = rs.getBlob(1).getBinaryStream();
    ObjectInputStream oip = new ObjectInputStream(is);
    Object object = oip.readObject();
    className = object.getClass().getName();
    oip.close();
    is.close();
    rs.close();
    pstmt.close();
    conn.commit();

    // de-serialize list a java object from a given objectID
    List listFromDatabase = (List) object;
    System.out.println("[After De-Serialization] list=" + listFromDatabase);
    conn.close();
}

From source file:DemoDisplayBlobFromDatabase.java

public static void main(String args[]) throws Exception {
    Connection conn = null;/* w w w .ja  v  a  2 s. c om*/
    ResultSet rs = null;
    PreparedStatement pstmt = null;
    String query = "SELECT blob_column FROM blob_table WHERE id = ?";

    try {
        conn = getConnection();
        pstmt = conn.prepareStatement(query);
        pstmt.setString(1, "0001");
        rs = pstmt.executeQuery();
        rs.next();
        // materialize binary data onto client
        java.sql.Blob blob = rs.getBlob(1);
    } finally {
        rs.close();
        pstmt.close();
        conn.close();
    }
}