Examples of java.sql.PreparedStatement

java.sql.PreparedStatement
An interface for a Precompiled SQL Statement.
An SQL Statement is put into a PreparedStatement and is precompiled so that it can be executed multiple times efficiently.
Setter methods are supplied in the PreparedStatement interface for the setting of IN parameters for the Statement. The setter method used for each IN parameter must match the type of the IN parameter being set.

        Blob b = conn.createBlob();
        OutputStream out = b.setBinaryStream(1);
        out.write(data, 0, data.length);
        out.close();
        stat.execute("delete from test");
        PreparedStatement prep = conn.prepareStatement("insert into test values(?, ?)");
        prep.setInt(1, 1);
        prep.setBlob(2, b);
        prep.execute();
        prep.setInt(1, 2);
        b = conn.createBlob();
        b.setBytes(1, data);
        prep.setBlob(2, b);
        prep.execute();
        ResultSet rs;
        rs = stat.executeQuery("select * from test");
        rs.next();
        Blob b2 = rs.getBlob(2);
        assertEquals(length, b2.length());

View Full Code Here

        Clob c = conn.createClob();
        Writer out = c.setCharacterStream(1);
        out.write(data, 0, data.length);
        out.close();
        stat.execute("delete from test");
        PreparedStatement prep = conn.prepareStatement("insert into test values(?, ?)");
        prep.setInt(1, 1);
        prep.setClob(2, c);
        prep.execute();
        c = conn.createClob();
        c.setString(1, new String(data));
        prep.setInt(1, 2);
        prep.setClob(2, c);
        prep.execute();
        ResultSet rs;
        rs = stat.executeQuery("select * from test");
        rs.next();
        Clob c2 = rs.getClob(2);
        assertEquals(length, c2.length());

View Full Code Here

    private void testLobReconnect() throws Exception {
        deleteDb("lob");
        Connection conn = reconnect(null);
        Statement stat = conn.createStatement();
        stat.execute("CREATE TABLE TEST(ID INT PRIMARY KEY, TEXT CLOB)");
        PreparedStatement prep;
        prep = conn.prepareStatement("INSERT INTO TEST VALUES(1, ?)");
        String s = new String(getRandomChars(10000, 1));
        byte[] data = s.getBytes("UTF-8");
        prep.setBinaryStream(1, new ByteArrayInputStream(data), 0);
        prep.execute();


        conn = reconnect(conn);
        stat = conn.createStatement();
        ResultSet rs = stat.executeQuery("SELECT * FROM TEST WHERE ID=1");
        rs.next();
        assertEqualStreams(rs.getBinaryStream("TEXT"), new ByteArrayInputStream(data), -1);


        prep = conn.prepareStatement("UPDATE TEST SET TEXT = ?");
        prep.setBinaryStream(1, new ByteArrayInputStream(data), 0);
        prep.execute();


        conn = reconnect(conn);
        stat = conn.createStatement();
        rs = stat.executeQuery("SELECT * FROM TEST WHERE ID=1");
        rs.next();

View Full Code Here

        deleteDb("lob");
        Connection conn = reconnect(null);
        conn = reconnect(conn);
        Statement stat = conn.createStatement();
        stat.execute("DROP TABLE IF EXISTS TEST");
        PreparedStatement prep;
        ResultSet rs;
        long time;
        stat.execute("CREATE TABLE TEST(ID INT PRIMARY KEY, VALUE " + (clob ? "CLOB" : "BLOB") + ")");


        int len = getSize(1, 1000);
        if (config.networked && config.big) {
            len = 100;
        }


        time = System.currentTimeMillis();
        prep = conn.prepareStatement("INSERT INTO TEST VALUES(?, ?)");
        for (int i = 0; i < len; i += i + i + 1) {
            prep.setInt(1, i);
            int size = i * i;
            if (clob) {
                prep.setCharacterStream(2, getRandomReader(size, i), 0);
            } else {
                prep.setBinaryStream(2, getRandomStream(size, i), 0);
            }
            prep.execute();
        }
        trace("insert=" + (System.currentTimeMillis() - time));
        traceMemory();
        conn = reconnect(conn);


        time = System.currentTimeMillis();
        prep = conn.prepareStatement("SELECT ID, VALUE FROM TEST");
        rs = prep.executeQuery();
        while (rs.next()) {
            int id = rs.getInt("ID");
            int size = id * id;
            if (clob) {
                Reader rt = rs.getCharacterStream(2);
                assertEqualReaders(getRandomReader(size, id), rt, -1);
                Object obj = rs.getObject(2);
                if (obj instanceof Clob) {
                    obj = ((Clob) obj).getCharacterStream();
                }
                assertEqualReaders(getRandomReader(size, id), (Reader) obj, -1);
            } else {
                InputStream in = rs.getBinaryStream(2);
                assertEqualStreams(getRandomStream(size, id), in, -1);
                Object obj = rs.getObject(2);
                if (obj instanceof Blob) {
                    obj = ((Blob) obj).getBinaryStream();
                }
                assertEqualStreams(getRandomStream(size, id), (InputStream) obj, -1);
            }
        }
        trace("select=" + (System.currentTimeMillis() - time));
        traceMemory();


        conn = reconnect(conn);


        time = System.currentTimeMillis();
        prep = conn.prepareStatement("DELETE FROM TEST WHERE ID=?");
        for (int i = 0; i < len; i++) {
            prep.setInt(1, i);
            prep.executeUpdate();
        }
        trace("delete=" + (System.currentTimeMillis() - time));
        traceMemory();
        conn = reconnect(conn);


        conn.setAutoCommit(false);
        prep = conn.prepareStatement("INSERT INTO TEST VALUES(1, ?)");
        if (clob) {
            prep.setCharacterStream(1, getRandomReader(0, 0), 0);
        } else {
            prep.setBinaryStream(1, getRandomStream(0, 0), 0);
        }
        prep.execute();
        conn.rollback();
        prep.execute();
        conn.commit();


        conn.createStatement().execute("DELETE FROM TEST WHERE ID=1");
        conn.rollback();
        conn.createStatement().execute("DELETE FROM TEST WHERE ID=1");

View Full Code Here

    private void testJavaObject() throws SQLException {
        deleteDb("lob");
        Connection conn = getConnection("lob");
        Statement stat = conn.createStatement();
        stat.execute("CREATE TABLE TEST(ID INT PRIMARY KEY, DATA OTHER)");
        PreparedStatement prep = conn.prepareStatement("INSERT INTO TEST VALUES(1, ?)");
        prep.setObject(1, new TestLobObject("abc"));
        prep.execute();
        ResultSet rs = conn.createStatement().executeQuery("SELECT * FROM TEST");
        rs.next();
        Object oa = rs.getObject(2);
        TestLobObject a = (TestLobObject) oa;
        Object ob = rs.getObject("DATA");
        TestLobObject b = (TestLobObject) ob;
        assertEquals("abc", a.data);
        assertEquals("abc", b.data);
        assertFalse(rs.next());


        conn.createStatement().execute("drop table test");
        stat.execute("create table test(value other)");
        prep = conn.prepareStatement("insert into test values(?)");
        prep.setObject(1, Utils.serialize(""));
        prep.execute();
        rs = stat.executeQuery("select value from test");
        while (rs.next()) {
            assertEquals("", (String) rs.getObject("value"));
        }
        conn.close();

View Full Code Here

    private void testDatetimeWithCalendar() throws SQLException {
        trace("Test DATETIME with Calendar");
        ResultSet rs;


        stat.execute("CREATE TABLE TEST(ID INT PRIMARY KEY, D DATE, T TIME, TS TIMESTAMP)");
        PreparedStatement prep = conn.prepareStatement("INSERT INTO TEST VALUES(?, ?, ?, ?)");
        Calendar regular = Calendar.getInstance();
        Calendar other = null;
        // search a locale that has a _different_ raw offset
        long testTime = java.sql.Date.valueOf("2001-02-03").getTime();
        for (String s : TimeZone.getAvailableIDs()) {
            TimeZone zone = TimeZone.getTimeZone(s);
            long rawOffsetDiff = regular.getTimeZone().getRawOffset() - zone.getRawOffset();
            // must not be the same timezone (not 0 h and not 24 h difference
            // as for Pacific/Auckland and Etc/GMT+12)
            if (rawOffsetDiff != 0 && rawOffsetDiff != 1000 * 60 * 60 * 24) {
                if (regular.getTimeZone().getOffset(testTime) != zone.getOffset(testTime)) {
                    other = Calendar.getInstance(zone);
                    break;
                }
            }
        }


        trace("regular offset = " + regular.getTimeZone().getRawOffset() + " other = "
                + other.getTimeZone().getRawOffset());


        prep.setInt(1, 0);
        prep.setDate(2, null, regular);
        prep.setTime(3, null, regular);
        prep.setTimestamp(4, null, regular);
        prep.execute();


        prep.setInt(1, 1);
        prep.setDate(2, null, other);
        prep.setTime(3, null, other);
        prep.setTimestamp(4, null, other);
        prep.execute();


        prep.setInt(1, 2);
        prep.setDate(2, java.sql.Date.valueOf("2001-02-03"), regular);
        prep.setTime(3, java.sql.Time.valueOf("04:05:06"), regular);
        prep.setTimestamp(4, java.sql.Timestamp.valueOf("2007-08-09 10:11:12.131415"), regular);
        prep.execute();


        prep.setInt(1, 3);
        prep.setDate(2, java.sql.Date.valueOf("2101-02-03"), other);
        prep.setTime(3, java.sql.Time.valueOf("14:05:06"), other);
        prep.setTimestamp(4, java.sql.Timestamp.valueOf("2107-08-09 10:11:12.131415"), other);
        prep.execute();


        prep.setInt(1, 4);
        prep.setDate(2, java.sql.Date.valueOf("2101-02-03"));
        prep.setTime(3, java.sql.Time.valueOf("14:05:06"));
        prep.setTimestamp(4, java.sql.Timestamp.valueOf("2107-08-09 10:11:12.131415"));
        prep.execute();


        rs = stat.executeQuery("SELECT * FROM TEST ORDER BY ID");
        assertResultSetMeta(rs, 4,
                new String[] { "ID", "D", "T", "TS" },
                new int[] { Types.INTEGER, Types.DATE, Types.TIME, Types.TIMESTAMP },

View Full Code Here

            stat.execute("CREATE TABLE " + tableName + "(ID INT PRIMARY KEY, DATA VARCHAR)");
            stat.execute("CALL " + prefix + "_CREATE_INDEX('PUBLIC', '" + tableName + "', NULL)");
            task[i] = new Task() {
                public void call() throws SQLException {
                    trace("starting thread " + Thread.currentThread());
                    PreparedStatement prep = conn.prepareStatement("INSERT INTO " + tableName + " VALUES(?, ?)");
                    Statement stat = conn.createStatement();
                    Random random = new Random();
                    int x = 0;
                    while (!stop) {
                        trace("stop = " + stop + " for " + Thread.currentThread());
                        StringBuilder buff = new StringBuilder();
                        for (int j = 0; j < 1000; j++) {
                            buff.append(" ").append(random.nextInt(10000));
                            buff.append(" x").append(j);
                            buff.append(" ").append(KNOWN_WORDS[j % KNOWN_WORDS.length]);
                        }
                        prep.setInt(1, x);
                        prep.setString(2, buff.toString());
                        prep.execute();
                        x++;
                        for (String knownWord : KNOWN_WORDS) {
                            trace("searching for " + knownWord + " with " + Thread.currentThread());
                            ResultSet rs = stat.executeQuery("SELECT * FROM " + prefix + "_SEARCH('" + knownWord + "', 0, 0)");
                            assertTrue(rs.next());

View Full Code Here

        conn.setAutoCommit(false);
        stat.execute("insert into test values(1, 'Hello Moon!')");
        conn.rollback();
        conn.setAutoCommit(true);
        stat.execute("insert into test values(0, 'Hello World!')");
        PreparedStatement prep = conn.prepareStatement("insert into test values(1, ?)");
        final int length = 1024 * 1024;
        prep.setCharacterStream(1, new Reader() {
            int remaining = length;
            public void close() {
                // ignore
            }
            public int read(char[] buff, int off, int len) {
                if (remaining >= len) {
                    remaining -= len;
                    return len;
                }
                remaining = -1;
                return -1;
            }
        }, length);
        prep.execute();
        ResultSet rs = stat.executeQuery("SELECT * FROM FT_SEARCH('World', 0, 0)");
        assertTrue(rs.next());
        rs = stat.executeQuery("SELECT * FROM FT_SEARCH('Moon', 0, 0)");
        assertFalse(rs.next());
        FullText.dropAll(conn);

View Full Code Here

        stat.execute("ALTER TABLE TEST ALTER COLUMN ID INT NOT NULL");
        stat.execute("CREATE PRIMARY KEY ON TEST(ID)");
        long time = System.currentTimeMillis();
        stat.execute("CALL " + prefix + "_CREATE_INDEX('PUBLIC', 'TEST', NULL)");
        println("create " + prefix + ": " + (System.currentTimeMillis() - time));
        PreparedStatement prep = conn.prepareStatement("SELECT * FROM " + prefix + "_SEARCH(?, 0, 0)");
        time = System.currentTimeMillis();
        ResultSet rs = stat.executeQuery("SELECT TEXT FROM TEST");
        int count = 0;
        while (rs.next()) {
            String text = rs.getString(1);
            StringTokenizer tokenizer = new StringTokenizer(text, " ()[].,;:-+*/!?=<>{}#@'\"~$_%&|");
            while (tokenizer.hasMoreTokens()) {
                String word = tokenizer.nextToken();
                if (word.length() < 10) {
                    continue;
                }
                prep.setString(1, word);
                ResultSet rs2 = prep.executeQuery();
                while (rs2.next()) {
                    rs2.getString(1);
                    count++;
                }
            }

View Full Code Here

        return rs.next();
    }


    private boolean checkPasswordSecure(String pwd) throws SQLException {
        String sql = "SELECT * FROM USERS WHERE PASSWORD=?";
        PreparedStatement prep = conn.prepareStatement(sql);
        prep.setString(1, pwd);
        ResultSet rs = prep.executeQuery();
        return rs.next();
    }

View Full Code Here

0 1 2 3 4 5 6 7 8 9

TOP

Related Classes of java.sql.PreparedStatement

ch.qos.logback.classic.db.DBAppender

co.paralleluniverse.fibers.jdbc.FiberConnection

com.alibaba.wasp.jdbc.TestPreparedStatement

com.alimama.mdrill.adhoc.MysqlCallback

com.emc.plants.service.impl.ReportGeneratorBean

com.github.zathrus_writer.commandsex.SQLManager

com.groupon.odo.proxylib.ConfigurationService

com.groupon.odo.proxylib.EditService

com.groupon.odo.proxylib.HistoryService

com.groupon.odo.proxylib.OverrideService

All source code are property of their respective owners. Java is a trademark of Sun Microsystems, Inc and owned by ORACLE Inc. Contact coftware#gmail.com.