Example usage for java.sql Statement execute

List of usage examples for java.sql Statement execute

  1. HOME
  2. Java
  3. java
  4. java.sql.*
  5. Statement
  6. execute

Introduction

In this page you can find the example usage for java.sql Statement execute.

Prototype

boolean execute(String sql) throws SQLException;

Source Link

Document

Executes the given SQL statement, which may return multiple results.

Usage

From source file:edu.duke.cabig.c3pr.webservice.integration.C3PREmbeddedTomcatTestBase.java

/**
 * See http://sourceforge.net/tracker/index.php?func=detail&aid=1459205&
 * group_id=47439&atid=449491. We will execute PURGE RECYCLEBIN here.
 * /*from   w  ww.  j  av  a  2  s  . co m*/
 * @throws Exception
 * @throws SQLException
 */
private void purgeRecycleBin() throws SQLException, Exception {
    Connection conn = null;
    Statement st = null;
    try {
        conn = getConnection().getConnection();
        st = conn.createStatement();
        st.execute("PURGE RECYCLEBIN");
    } catch (Exception e) {
        logger.warning(
                "Unable to execute PURGE RECYCLEBIN either because of an error or because not running on Oracle: "
                        + e.getMessage());
    } finally {
        try {
            st.close();
        } catch (Exception e) {
            logger.warning("Coult not close the Statement. Probably, safe to ignore.");
        }
        try {
            conn.commit();
        } catch (Exception e) {
            logger.warning("Coult not commit the Connection. Probably, safe to ignore.");
        }
        try {
            conn.close();
        } catch (Exception e) {
            logger.warning("Coult not close the Connection. Probably, safe to ignore.");
        }
    }
}

From source file:com.alibaba.wasp.jdbc.TestPreparedStatement.java

public void testInsertFunction() throws SQLException {
    Statement stat = conn.createStatement();
    PreparedStatement prep;//from  w w w .  j ava 2 s.  com
    ResultSet rs;

    stat.execute("CREATE TABLE TEST(ID INT, H BINARY)");
    prep = conn.prepareStatement("INSERT INTO TEST VALUES(?, HASH('SHA256', STRINGTOUTF8(?), 5))");
    prep.setInt(1, 1);
    prep.setString(2, "One");
    prep.execute();
    prep.setInt(1, 2);
    prep.setString(2, "Two");
    prep.execute();
    rs = stat.executeQuery("SELECT COUNT(DISTINCT H) FROM TEST");
    rs.next();
    assertEquals(2, rs.getInt(1));

    stat.execute("DROP TABLE TEST");
}

From source file:com.alibaba.wasp.jdbc.TestPreparedStatement.java

public void testObject() throws SQLException {
    Statement stat = conn.createStatement();
    ResultSet rs;/*from w ww  .  ja  v  a2  s .  co  m*/
    stat.execute("CREATE TABLE TEST(ID INT PRIMARY KEY, NAME VARCHAR(255))");
    stat.execute("INSERT INTO TEST VALUES(1, 'Hello')");
    PreparedStatement prep = conn
            .prepareStatement("SELECT ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? FROM TEST");
    prep.setObject(1, Boolean.TRUE);
    prep.setObject(2, "Abc");
    prep.setObject(3, new BigDecimal("10.2"));
    prep.setObject(4, new Byte((byte) 0xff));
    prep.setObject(5, new Short(Short.MAX_VALUE));
    prep.setObject(6, new Integer(Integer.MIN_VALUE));
    prep.setObject(7, new Long(Long.MAX_VALUE));
    prep.setObject(8, new Float(Float.MAX_VALUE));
    prep.setObject(9, new Double(Double.MAX_VALUE));
    prep.setObject(10, Date.valueOf("2001-02-03"));
    prep.setObject(11, Time.valueOf("04:05:06"));
    prep.setObject(12, Timestamp.valueOf("2001-02-03 04:05:06.123456789"));
    prep.setObject(13, new java.util.Date(Date.valueOf("2001-02-03").getTime()));
    prep.setObject(14, new byte[] { 10, 20, 30 });
    prep.setObject(15, new Character('a'), Types.OTHER);
    prep.setObject(16, "2001-01-02", Types.DATE);
    // converting to null seems strange...
    prep.setObject(17, "2001-01-02", Types.NULL);
    prep.setObject(18, "3.725", Types.DOUBLE);
    prep.setObject(19, "23:22:21", Types.TIME);
    prep.setObject(20, new java.math.BigInteger("12345"), Types.OTHER);
    rs = prep.executeQuery();
    rs.next();
    assertTrue(rs.getObject(1).equals(Boolean.TRUE));
    assertTrue(rs.getObject(2).equals("Abc"));
    assertTrue(rs.getObject(3).equals(new BigDecimal("10.2")));
    assertTrue(rs.getObject(4).equals((byte) 0xff));
    assertTrue(rs.getObject(5).equals(new Short(Short.MAX_VALUE)));
    assertTrue(rs.getObject(6).equals(new Integer(Integer.MIN_VALUE)));
    assertTrue(rs.getObject(7).equals(new Long(Long.MAX_VALUE)));
    assertTrue(rs.getObject(8).equals(new Float(Float.MAX_VALUE)));
    assertTrue(rs.getObject(9).equals(new Double(Double.MAX_VALUE)));
    assertTrue(rs.getObject(10).equals(Date.valueOf("2001-02-03")));
    assertEquals("04:05:06", rs.getObject(11).toString());
    assertTrue(rs.getObject(11).equals(Time.valueOf("04:05:06")));
    assertTrue(rs.getObject(12).equals(Timestamp.valueOf("2001-02-03 04:05:06.123456789")));
    assertTrue(rs.getObject(13).equals(Timestamp.valueOf("2001-02-03 00:00:00")));
    assertEquals(new byte[] { 10, 20, 30 }, (byte[]) rs.getObject(14));
    assertTrue(rs.getObject(15).equals('a'));
    assertTrue(rs.getObject(16).equals(Date.valueOf("2001-01-02")));
    assertTrue(rs.getObject(17) == null && rs.wasNull());
    assertTrue(rs.getObject(18).equals(new Double(3.725)));
    assertTrue(rs.getObject(19).equals(Time.valueOf("23:22:21")));
    assertTrue(rs.getObject(20).equals(new java.math.BigInteger("12345")));

    // } else if(x instanceof java.io.Reader) {
    // return session.createLob(Value.CLOB,
    // TypeConverter.getInputStream((java.io.Reader)x), 0);
    // } else if(x instanceof java.io.InputStream) {
    // return session.createLob(Value.BLOB, (java.io.InputStream)x, 0);
    // } else {
    // return ValueBytes.get(TypeConverter.serialize(x));

    stat.execute("DROP TABLE TEST");

}

From source file:com.alibaba.wasp.jdbc.TestPreparedStatement.java

public void testGetMoreResults() throws SQLException {
    Statement stat = conn.createStatement();
    PreparedStatement prep;/*w  ww.ja  v  a2  s.c o m*/
    ResultSet rs;
    stat.execute("CREATE TABLE TEST(ID INT)");
    stat.execute("INSERT INTO TEST VALUES(1)");

    prep = conn.prepareStatement("SELECT * FROM TEST");
    // just to check if it doesn't throw an exception - it may be null
    prep.getMetaData();
    assertTrue(prep.execute());
    rs = prep.getResultSet();
    assertFalse(prep.getMoreResults());
    assertEquals(-1, prep.getUpdateCount());
    // supposed to be closed now
    assertThrows(SQLErrorCode.OBJECT_CLOSED, rs).next();
    assertEquals(-1, prep.getUpdateCount());

    prep = conn.prepareStatement("UPDATE TEST SET ID = 2");
    assertFalse(prep.execute());
    assertEquals(1, prep.getUpdateCount());
    assertFalse(prep.getMoreResults(Statement.CLOSE_CURRENT_RESULT));
    assertEquals(-1, prep.getUpdateCount());
    // supposed to be closed now
    assertThrows(SQLErrorCode.OBJECT_CLOSED, rs).next();
    assertEquals(-1, prep.getUpdateCount());

    prep = conn.prepareStatement("DELETE FROM TEST");
    prep.executeUpdate();
    assertFalse(prep.getMoreResults());
    assertEquals(-1, prep.getUpdateCount());
}

From source file:com.sludev.mssqlapplylog.MSSQLApplyLog.java

@Override
public Integer call() throws Exception {
    Integer res = 0;//from   w w w.j a va 2s .  c  o m

    String backupDirStr = config.getBackupDirStr();
    String fullBackupPathStr = config.getFullBackupPathStr();
    String fullBackupDatePatternStr = config.getFullBackupDatePatternStr();
    String laterThanStr = config.getLaterThanStr();
    String fullBackupPatternStr = config.getFullBackupPatternStr();
    String logBackupPatternStr = config.getLogBackupPatternStr();
    String logBackupDatePatternStr = config.getLogBackupDatePatternStr();

    String sqlHost = config.getSqlHost();
    String sqlDb = config.getSqlDb();
    String sqlUser = config.getSqlUser();
    String sqlPass = config.getSqlPass();
    String sqlURL = config.getSqlUrl();
    String sqlProcessUser = config.getSqlProcessUser();

    boolean useLogFileLastMode = BooleanUtils.isTrue(config.getUseLogFileLastMode());
    boolean doFullRestore = BooleanUtils.isTrue(config.getDoFullRestore());
    boolean monitorLogBackupDir = BooleanUtils.isTrue(config.getMonitorLogBackupDir());

    Path backupsDir = null;
    Instant laterThan = null;

    Path fullBackupPath = null;

    // Validate the Log Backup Directory
    if (StringUtils.isBlank(backupDirStr)) {
        LOGGER.error("Invalid blank/empty backup directory");

        return 1;
    }

    try {
        backupsDir = Paths.get(backupDirStr);
    } catch (Exception ex) {
        LOGGER.error(String.format("Error parsing Backup Directory '%s'", backupDirStr), ex);

        return 1;
    }

    if (Files.notExists(backupsDir)) {
        LOGGER.error(String.format("Invalid non-existant backup directory '%s'", backupsDir));

        return 1;
    }

    if (StringUtils.isBlank(logBackupPatternStr)) {
        LOGGER.warn(String.format(
                "\"Log Backup Pattern\" cannot be empty.  Defaulting to " + "%s regex in backup directory",
                DEFAULT_LOG_FILE_PATTERN_STR));

        logBackupPatternStr = DEFAULT_LOG_FILE_PATTERN_STR;
    }

    if (StringUtils.isNoneBlank(fullBackupPathStr)) {
        fullBackupPath = Paths.get(fullBackupPathStr);
        if (Files.notExists(fullBackupPath) || Files.isRegularFile(fullBackupPath) == false) {
            LOGGER.error(String.format("Invalid Full Backup file '%s'", backupDirStr));

            return 1;
        }
    }

    if (StringUtils.isNoneBlank(fullBackupDatePatternStr) && StringUtils.isBlank(laterThanStr)
            && fullBackupPath != null) {
        // Retrieve the "Later Than" date from the full backup file name
        laterThan = FSHelper.getTimestampFromFilename(fullBackupPatternStr, fullBackupDatePatternStr, 1,
                fullBackupPath);
        if (laterThan == null) {
            LOGGER.error("'Later Than' cannot be null");

            return 1;
        }
    } else {
        // Use the "Later Than" timestamp from the command line or properties
        // file.

        try {
            laterThan = Instant.from(DateTimeFormatter.ISO_INSTANT.parse(laterThanStr));
        } catch (Exception ex) {
            LOGGER.error(String.format("Error parsing 'Later Than' time '%s'", laterThanStr), ex);
        }
    }

    try {
        Class.forName("net.sourceforge.jtds.jdbc.Driver");
    } catch (ClassNotFoundException ex) {
        LOGGER.error("Error loading SQL Server driver [ net.sourceforge.jtds.jdbc.Driver ]", ex);

        return 1;
    }

    if (StringUtils.isBlank(sqlURL)) {
        // Build the SQL URL
        sqlURL = String.format("jdbc:jtds:sqlserver://%s;DatabaseName=master", sqlHost);
    }

    Properties props = new Properties();

    props.setProperty("user", sqlUser);
    props.setProperty("password", sqlPass);

    try (Connection conn = MSSQLHelper.getConn(sqlURL, props)) {
        if (conn == null) {
            LOGGER.error("Connection to MSSQL failed.");

            return 1;
        }

        if (doFullRestore) {
            LOGGER.info(String.format("\nStarting full restore of '%s'...", fullBackupPathStr));

            StopWatch sw = new StopWatch();

            sw.start();

            String strDevice = fullBackupPathStr;

            String query = String.format("RESTORE DATABASE %s FROM DISK='%s' WITH NORECOVERY, REPLACE", sqlDb,
                    strDevice);

            Statement stmt = null;

            try {
                stmt = conn.createStatement();
            } catch (SQLException ex) {
                LOGGER.debug("Error creating statement", ex);

                return 1;
            }

            try {
                boolean sqlRes = stmt.execute(query);
            } catch (SQLException ex) {
                LOGGER.error(String.format("Error executing...\n'%s'", query), ex);

                return 1;
            }

            sw.stop();

            LOGGER.debug(String.format("Query...\n'%s'\nTook %s", query, sw.toString()));
        }
    } catch (SQLException ex) {
        LOGGER.error("SQL Exception restoring the full backup", ex);
    }

    // Filter the log files.

    // Loop multiple times to catch new logs that have been transferred
    // while we process.
    List<Path> files = null;
    do {
        try {
            files = FSHelper.listLogFiles(backupsDir, laterThan, useLogFileLastMode, logBackupPatternStr,
                    logBackupDatePatternStr, files);
        } catch (IOException ex) {
            LOGGER.error("Log Backup file filter/sort failed", ex);

            return 1;
        }

        if (files == null || files.isEmpty()) {
            LOGGER.debug("No Log Backup files found this iteration.");

            continue;
        }

        StringBuilder msg = new StringBuilder();

        for (Path file : files) {
            msg.append(String.format("file : '%s'\n", file));
        }

        LOGGER.debug(msg.toString());

        // Restore all log files
        try (Connection conn = MSSQLHelper.getConn(sqlURL, props)) {
            for (Path p : files) {
                try {
                    MSSQLHelper.restoreLog(p, sqlProcessUser, sqlDb, conn);
                } catch (SQLException ex) {
                    LOGGER.error(String.format("SQL Exception restoring the log backup '%s'", p), ex);
                }
            }
        } catch (SQLException ex) {
            LOGGER.error("SQL Exception restoring the log backup", ex);
        }
    } while (files != null && files.isEmpty() == false);

    if (monitorLogBackupDir) {
        // Watch for new log files
        List<Path> paths = new ArrayList();
        paths.add(backupsDir);

        final Watch watch;
        final String currSQLDb = sqlDb;
        final String currSQLProcUser = sqlProcessUser;
        final String currSQLURL = sqlURL;
        final String currLogBackupPatternStr = logBackupPatternStr;

        try {
            watch = Watch.from(paths);
            watch.processEvents((WatchEvent<Path> event, Path path) -> {
                int watchRes = 0;

                if (event.kind() != StandardWatchEventKinds.ENTRY_CREATE) {
                    return watchRes;
                }

                Pattern fileMatcher = Pattern.compile(currLogBackupPatternStr);

                if (fileMatcher.matcher(path.getFileName().toString()).matches()) {
                    try (Connection conn = MSSQLHelper.getConn(currSQLURL, props)) {
                        MSSQLHelper.restoreLog(path, currSQLProcUser, currSQLDb, conn);
                    } catch (SQLException ex) {
                        // There's really no recovering from a failed log backup

                        LOGGER.error("SQL Exception restoring the log backup", ex);

                        System.exit(1);
                    }
                }

                return watchRes;
            });
        } catch (IOException | FileCheckException ex) {
            LOGGER.error(String.format("Error watching backup directory...\n'%s'", backupsDir), ex);

            return 1;
        } catch (InterruptedException ex) {
            LOGGER.info(String.format("Interrupted watching backup directory...\n'%s'", backupsDir), ex);
        }
    }

    return res;
}

From source file:com.alibaba.wasp.jdbc.TestPreparedStatement.java

public void testDateTimeTimestampWithCalendar() throws SQLException {
    Statement stat = conn.createStatement();
    stat.execute("create table ts(x timestamp primary key)");
    stat.execute("create table t(x time primary key)");
    stat.execute("create table d(x date)");
    Calendar utcCalendar = new GregorianCalendar(new SimpleTimeZone(0, "Z"));
    TimeZone old = TimeZone.getDefault();
    DateTimeUtils.resetCalendar();/*from   w  w  w.j a va2  s  .  co m*/
    TimeZone.setDefault(TimeZone.getTimeZone("PST"));
    try {
        Timestamp ts1 = Timestamp.valueOf("2010-03-13 18:15:00");
        Time t1 = new Time(ts1.getTime());
        Date d1 = new Date(ts1.getTime());
        // when converted to UTC, this is 03:15, which doesn't actually exist
        // because of summer time change at that day
        Timestamp ts2 = Timestamp.valueOf("2010-03-13 19:15:00");
        Time t2 = new Time(ts2.getTime());
        Date d2 = new Date(ts2.getTime());
        PreparedStatement prep;
        ResultSet rs;
        prep = conn.prepareStatement("insert into ts values(?)");
        prep.setTimestamp(1, ts1, utcCalendar);
        prep.execute();
        prep.setTimestamp(1, ts2, utcCalendar);
        prep.execute();
        prep = conn.prepareStatement("insert into t values(?)");
        prep.setTime(1, t1, utcCalendar);
        prep.execute();
        prep.setTime(1, t2, utcCalendar);
        prep.execute();
        prep = conn.prepareStatement("insert into d values(?)");
        prep.setDate(1, d1, utcCalendar);
        prep.execute();
        prep.setDate(1, d2, utcCalendar);
        prep.execute();
        rs = stat.executeQuery("select * from ts order by x");
        rs.next();
        assertEquals("2010-03-14 02:15:00.0", rs.getString(1));
        assertEquals("2010-03-13 18:15:00.0", rs.getTimestamp(1, utcCalendar).toString());
        assertEquals("2010-03-14 03:15:00.0", rs.getTimestamp(1).toString());
        assertEquals("2010-03-14 02:15:00.0", rs.getString("x"));
        assertEquals("2010-03-13 18:15:00.0", rs.getTimestamp("x", utcCalendar).toString());
        assertEquals("2010-03-14 03:15:00.0", rs.getTimestamp("x").toString());
        rs.next();
        assertEquals("2010-03-14 03:15:00.0", rs.getString(1));
        assertEquals("2010-03-13 19:15:00.0", rs.getTimestamp(1, utcCalendar).toString());
        assertEquals("2010-03-14 03:15:00.0", rs.getTimestamp(1).toString());
        assertEquals("2010-03-14 03:15:00.0", rs.getString("x"));
        assertEquals("2010-03-13 19:15:00.0", rs.getTimestamp("x", utcCalendar).toString());
        assertEquals("2010-03-14 03:15:00.0", rs.getTimestamp("x").toString());
        rs = stat.executeQuery("select * from t order by x");
        rs.next();
        assertEquals("02:15:00", rs.getString(1));
        assertEquals("18:15:00", rs.getTime(1, utcCalendar).toString());
        assertEquals("02:15:00", rs.getTime(1).toString());
        assertEquals("02:15:00", rs.getString("x"));
        assertEquals("18:15:00", rs.getTime("x", utcCalendar).toString());
        assertEquals("02:15:00", rs.getTime("x").toString());
        rs.next();
        assertEquals("03:15:00", rs.getString(1));
        assertEquals("19:15:00", rs.getTime(1, utcCalendar).toString());
        assertEquals("03:15:00", rs.getTime(1).toString());
        assertEquals("03:15:00", rs.getString("x"));
        assertEquals("19:15:00", rs.getTime("x", utcCalendar).toString());
        assertEquals("03:15:00", rs.getTime("x").toString());
        rs = stat.executeQuery("select * from d order by x");
        rs.next();
        assertEquals("2010-03-14", rs.getString(1));
        assertEquals("2010-03-13", rs.getDate(1, utcCalendar).toString());
        assertEquals("2010-03-14", rs.getDate(1).toString());
        assertEquals("2010-03-14", rs.getString("x"));
        assertEquals("2010-03-13", rs.getDate("x", utcCalendar).toString());
        assertEquals("2010-03-14", rs.getDate("x").toString());
        rs.next();
        assertEquals("2010-03-14", rs.getString(1));
        assertEquals("2010-03-13", rs.getDate(1, utcCalendar).toString());
        assertEquals("2010-03-14", rs.getDate(1).toString());
        assertEquals("2010-03-14", rs.getString("x"));
        assertEquals("2010-03-13", rs.getDate("x", utcCalendar).toString());
        assertEquals("2010-03-14", rs.getDate("x").toString());
    } finally {
        TimeZone.setDefault(old);
        DateTimeUtils.resetCalendar();
    }
    stat.execute("drop table ts");
    stat.execute("drop table t");
    stat.execute("drop table d");
}

From source file:com.oracle.tutorial.jdbc.CoffeesTable.java

public void modifyPricesByPercentage(String coffeeName, float priceModifier, float maximumPrice)
        throws SQLException {
    con.setAutoCommit(false);//from   w  w w . j  ava2  s  . c  o m

    Statement getPrice = null;
    Statement updatePrice = null;
    ResultSet rs = null;
    String query = "SELECT COF_NAME, PRICE FROM COFFEES " + "WHERE COF_NAME = '" + coffeeName + "'";

    try {
        Savepoint save1 = con.setSavepoint();
        getPrice = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
        updatePrice = con.createStatement();

        if (!getPrice.execute(query)) {
            System.out.println("Could not find entry for coffee named " + coffeeName);
        } else {
            rs = getPrice.getResultSet();
            rs.first();
            float oldPrice = rs.getFloat("PRICE");
            float newPrice = oldPrice + (oldPrice * priceModifier);
            System.out.println("Old price of " + coffeeName + " is " + oldPrice);
            System.out.println("New price of " + coffeeName + " is " + newPrice);
            System.out.println("Performing update...");
            updatePrice.executeUpdate(
                    "UPDATE COFFEES SET PRICE = " + newPrice + " WHERE COF_NAME = '" + coffeeName + "'");
            System.out.println("\nCOFFEES table after update:");
            CoffeesTable.viewTable(con);
            if (newPrice > maximumPrice) {
                System.out.println("\nThe new price, " + newPrice + ", is greater than the maximum " + "price, "
                        + maximumPrice + ". Rolling back the transaction...");
                con.rollback(save1);
                System.out.println("\nCOFFEES table after rollback:");
                CoffeesTable.viewTable(con);
            }
            con.commit();
        }
    } catch (SQLException e) {
        JDBCTutorialUtilities.printSQLException(e);
    } finally {
        if (getPrice != null) {
            getPrice.close();
        }
        if (updatePrice != null) {
            updatePrice.close();
        }
        con.setAutoCommit(true);
    }
}

From source file:com.micromux.cassandra.jdbc.JdbcRegressionTest.java

/**
 * Create a column group and confirm that the {@code ResultSetMetaData} works.
 *//*  w  w  w . j  a v  a  2 s  .c o  m*/
@Test
public void testResultSetMetaData() throws Exception {

    Statement stmt = con.createStatement();

    // Create the target Column family
    String createCF = "CREATE COLUMNFAMILY t33 (k int PRIMARY KEY," + "c text " + ") ;";

    stmt.execute(createCF);
    stmt.close();
    con.close();

    // open it up again to see the new CF
    con = DriverManager
            .getConnection(String.format("jdbc:cassandra://%s:%d/%s?%s", HOST, PORT, KEYSPACE, OPTIONS));

    // paraphrase of the snippet from the ISSUE #33 provided test
    PreparedStatement statement = con.prepareStatement("update t33 set c=? where k=123");
    statement.setString(1, "mark");
    statement.executeUpdate();

    ResultSet result = statement.executeQuery("SELECT k, c FROM t33;");

    ResultSetMetaData metadata = result.getMetaData();

    int colCount = metadata.getColumnCount();

    System.out.println("Test Issue #33");
    DatabaseMetaData md = con.getMetaData();
    System.out.println();
    System.out.println("--------------");
    System.out.println("Driver Version :   " + md.getDriverVersion());
    System.out.println("DB Version     :   " + md.getDatabaseProductVersion());
    System.out.println("Catalog term   :   " + md.getCatalogTerm());
    System.out.println("Catalog        :   " + con.getCatalog());
    System.out.println("Schema term    :   " + md.getSchemaTerm());

    System.out.println("--------------");
    while (result.next()) {
        metadata = result.getMetaData();
        colCount = metadata.getColumnCount();

        assertEquals("Total column count should match schema for t33", 2, metadata.getColumnCount());

        System.out.printf("(%d) ", result.getRow());
        for (int i = 1; i <= colCount; i++) {
            System.out.print(showColumn(i, result) + " ");

            switch (i) {
            case 1:
                assertEquals("First Column: k", "k", metadata.getColumnName(1));
                assertEquals("First Column Type: int", Types.INTEGER, metadata.getColumnType(1));
                break;
            case 2:
                assertEquals("Second Column: c", "c", metadata.getColumnName(2));
                assertEquals("Second Column Type: text", Types.NVARCHAR, metadata.getColumnType(2));
                break;
            }
        }
        System.out.println();
    }
}

From source file:com.threecrickets.prudence.cache.SqlCache.java

/**
 * Makes sure that the required tables exist.
 * //from ww  w  . ja  v  a 2 s . c o  m
 * @param fresh
 *        Whether to drop the table first
 */
public void validateTables(boolean fresh) {
    try {
        Connection connection = connect();
        if (connection == null)
            return;

        try {
            Statement statement = connection.createStatement();
            try {
                if (fresh) {
                    statement.execute("DROP TABLE IF EXISTS " + cacheTableName);
                    statement.execute("DROP TABLE IF EXISTS " + cacheTagsTableName);
                }

                statement.execute("CREATE TABLE IF NOT EXISTS " + cacheTableName
                        + " (key VARCHAR(255) PRIMARY KEY, data BLOB, media_type VARCHAR(255), language VARCHAR(255), character_set VARCHAR(255), encoding VARCHAR(255), modification_date TIMESTAMP, tag VARCHAR(255), headers TEXT, expiration_date TIMESTAMP, document_modification_date TIMESTAMP)");
                statement.execute("CREATE TABLE IF NOT EXISTS " + cacheTagsTableName
                        + " (key VARCHAR(255), tag VARCHAR(255), FOREIGN KEY(key) REFERENCES " + cacheTableName
                        + "(key) ON DELETE CASCADE)");
                statement.execute("CREATE INDEX IF NOT EXISTS " + cacheTagsTableName + "_tag_idx ON "
                        + cacheTagsTableName + " (tag)");
            } finally {
                statement.close();
            }
        } finally {
            connection.close();
        }
    } catch (SQLException x) {
        logger.log(Level.WARNING, "Could not validate that tables exist", x);
    }
}

From source file:ca.sqlpower.wabit.report.ResultSetRendererTest.java

/**
 * This is a test to confirm that subtotalling columns for breaks works.
 *///from ww w .  j ava2 s.c o  m
public void testSubtotals() throws Exception {
    Connection con = null;
    Statement stmt = null;
    try {
        con = getContext().createConnection(
                (JDBCDataSource) getSession().getDataSources().getDataSource("regression_test"));
        stmt = con.createStatement();
        stmt.execute("Create table subtotal_table (break_col varchar(50), subtotal_values integer)");
        stmt.execute("insert into subtotal_table (break_col, subtotal_values) values ('a', 10)");
        stmt.execute("insert into subtotal_table (break_col, subtotal_values) values ('a', 20)");
        stmt.execute("insert into subtotal_table (break_col, subtotal_values) values ('a', 30)");
        stmt.execute("insert into subtotal_table (break_col, subtotal_values) values ('b', 12)");
        stmt.execute("insert into subtotal_table (break_col, subtotal_values) values ('b', 24)");
        stmt.execute("insert into subtotal_table (break_col, subtotal_values) values ('fib', 1)");
        stmt.execute("insert into subtotal_table (break_col, subtotal_values) values ('fib', 1)");
        stmt.execute("insert into subtotal_table (break_col, subtotal_values) values ('fib', 2)");
        stmt.execute("insert into subtotal_table (break_col, subtotal_values) values ('fib', 3)");
        stmt.execute("insert into subtotal_table (break_col, subtotal_values) values ('fib', 5)");
        stmt.execute("insert into subtotal_table (break_col, subtotal_values) values ('fib', 8)");
        stmt.execute("insert into subtotal_table (break_col, subtotal_values) values ('fib', 13)");
    } finally {
        if (stmt != null)
            stmt.close();
        if (con != null)
            con.close();
    }

    query.setUserModifiedQuery("select * from subtotal_table");

    Report report = new Report("report");
    getWorkspace().addReport(report);

    ContentBox cb = new ContentBox();
    report.getPage().addContentBox(cb);
    cb.setWidth(100);
    cb.setHeight(200);
    ResultSetRenderer renderer = new ResultSetRenderer(query);
    renderer.setParent(cb);
    renderer.refresh();

    Graphics2D contentGraphics = (Graphics2D) graphics.create((int) cb.getX(), (int) cb.getY(),
            (int) cb.getWidth(), (int) cb.getHeight());

    renderer.renderReportContent(contentGraphics, (int) cb.getWidth(), (int) cb.getHeight(), 1, 0, true,
            new SPVariableHelper(renderer));

    assertEquals(2, renderer.getColumnInfoList().size());
    renderer.getColumnInfoList().get(0).setWillGroupOrBreak(GroupAndBreak.GROUP);
    renderer.getColumnInfoList().get(1).setWillSubtotal(true);

    Font font = GraphicsEnvironment.getLocalGraphicsEnvironment().getAllFonts()[0];
    renderer.setHeaderFont(font);
    renderer.setBodyFont(font);

    renderer.renderReportContent(contentGraphics, (int) cb.getWidth(), (int) cb.getHeight(), 1, 0, false,
            new SPVariableHelper(renderer));

    List<List<ResultSetCell>> layoutCells = renderer.findCells();

    boolean foundATotal = false;
    boolean foundBTotal = false;
    boolean foundFibTotal = false;
    for (List<ResultSetCell> cells : layoutCells) {
        for (ResultSetCell cell : cells) {
            if (cell.getText().equals("60")) {
                foundATotal = true;
            } else if (cell.getText().equals("36")) {
                foundBTotal = true;
            } else if (cell.getText().equals("33")) {
                foundFibTotal = true;
            }
        }
    }
    if (!foundATotal) {
        fail("Could not find the correct subtotal cell for the A column. The cell should contain the value 60");
    }
    if (!foundBTotal) {
        fail("Could not find the correct subtotal cell for the B column. The cell should contain the value 36");
    }
    if (!foundFibTotal) {
        fail("Could not find the correct subtotal cell for the Fib column. The cell should contain the value 33");
    }

    con = null;
    stmt = null;
    try {
        con = getContext().createConnection(
                (JDBCDataSource) getSession().getDataSources().getDataSource("regression_test"));
        stmt = con.createStatement();
        stmt.execute("drop table subtotal_table");
    } finally {
        if (stmt != null)
            stmt.close();
        if (con != null)
            con.close();
    }

}