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:com.alibaba.wasp.jdbc.TestPreparedStatement.java

public void testDataTypes() throws SQLException {
    conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
    conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
    Statement stat = conn.createStatement();
    PreparedStatement prep;/*from   w  w  w  .  java 2  s .co m*/
    ResultSet rs;
    trace("Create tables");
    stat.execute("CREATE TABLE T_INT(ID INT PRIMARY KEY,VALUE INT)");
    stat.execute("CREATE TABLE T_VARCHAR(ID INT PRIMARY KEY,VALUE VARCHAR(255))");
    stat.execute("CREATE TABLE T_DECIMAL_0(ID INT PRIMARY KEY,VALUE DECIMAL(30,0))");
    stat.execute("CREATE TABLE T_DECIMAL_10(ID INT PRIMARY KEY,VALUE DECIMAL(20,10))");
    stat.execute("CREATE TABLE T_DATETIME(ID INT PRIMARY KEY,VALUE DATETIME)");
    prep = conn.prepareStatement("INSERT INTO T_INT VALUES(?,?)", ResultSet.TYPE_FORWARD_ONLY,
            ResultSet.CONCUR_READ_ONLY);
    prep.setInt(1, 1);
    prep.setInt(2, 0);
    prep.executeUpdate();
    prep.setInt(1, 2);
    prep.setInt(2, -1);
    prep.executeUpdate();
    prep.setInt(1, 3);
    prep.setInt(2, 3);
    prep.executeUpdate();
    prep.setInt(1, 4);
    prep.setNull(2, Types.INTEGER);
    prep.executeUpdate();
    prep.setInt(1, 5);
    prep.setBigDecimal(2, new BigDecimal("0"));
    prep.executeUpdate();
    prep.setInt(1, 6);
    prep.setString(2, "-1");
    prep.executeUpdate();
    prep.setInt(1, 7);
    prep.setObject(2, new Integer(3));
    prep.executeUpdate();
    prep.setObject(1, "8");
    // should throw an exception
    prep.setObject(2, null);
    // some databases don't allow calling setObject with null (no data type)
    prep.executeUpdate();
    prep.setInt(1, 9);
    prep.setObject(2, -4, Types.VARCHAR);
    prep.executeUpdate();
    prep.setInt(1, 10);
    prep.setObject(2, "5", Types.INTEGER);
    prep.executeUpdate();
    prep.setInt(1, 11);
    prep.setObject(2, null, Types.INTEGER);
    prep.executeUpdate();
    prep.setInt(1, 12);
    prep.setBoolean(2, true);
    prep.executeUpdate();
    prep.setInt(1, 13);
    prep.setBoolean(2, false);
    prep.executeUpdate();
    prep.setInt(1, 14);
    prep.setByte(2, (byte) -20);
    prep.executeUpdate();
    prep.setInt(1, 15);
    prep.setByte(2, (byte) 100);
    prep.executeUpdate();
    prep.setInt(1, 16);
    prep.setShort(2, (short) 30000);
    prep.executeUpdate();
    prep.setInt(1, 17);
    prep.setShort(2, (short) (-30000));
    prep.executeUpdate();
    prep.setInt(1, 18);
    prep.setLong(2, Integer.MAX_VALUE);
    prep.executeUpdate();
    prep.setInt(1, 19);
    prep.setLong(2, Integer.MIN_VALUE);
    prep.executeUpdate();

    assertTrue(stat.execute("SELECT * FROM T_INT ORDER BY ID"));
    rs = stat.getResultSet();
    assertResultSetOrdered(rs,
            new String[][] { { "1", "0" }, { "2", "-1" }, { "3", "3" }, { "4", null }, { "5", "0" },
                    { "6", "-1" }, { "7", "3" }, { "8", null }, { "9", "-4" }, { "10", "5" }, { "11", null },
                    { "12", "1" }, { "13", "0" }, { "14", "-20" }, { "15", "100" }, { "16", "30000" },
                    { "17", "-30000" }, { "18", "" + Integer.MAX_VALUE }, { "19", "" + Integer.MIN_VALUE }, });

    prep = conn.prepareStatement("INSERT INTO T_DECIMAL_0 VALUES(?,?)");
    prep.setInt(1, 1);
    prep.setLong(2, Long.MAX_VALUE);
    prep.executeUpdate();
    prep.setInt(1, 2);
    prep.setLong(2, Long.MIN_VALUE);
    prep.executeUpdate();
    prep.setInt(1, 3);
    prep.setFloat(2, 10);
    prep.executeUpdate();
    prep.setInt(1, 4);
    prep.setFloat(2, -20);
    prep.executeUpdate();
    prep.setInt(1, 5);
    prep.setFloat(2, 30);
    prep.executeUpdate();
    prep.setInt(1, 6);
    prep.setFloat(2, -40);
    prep.executeUpdate();

    rs = stat.executeQuery("SELECT VALUE FROM T_DECIMAL_0 ORDER BY ID");
    checkBigDecimal(rs, new String[] { "" + Long.MAX_VALUE, "" + Long.MIN_VALUE, "10", "-20", "30", "-40" });
}

From source file:kr.co.bitnine.octopus.frame.SessionServerTest.java

@Test
public void testSelectPrivilege() throws Exception {
    Connection conn = getConnection("octopus", "bitnine");
    Statement stmt = conn.createStatement();
    stmt.execute("CREATE USER \"jsyang\" IDENTIFIED BY '0009'");
    stmt.close();/*from   w  ww.  ja va 2 s . c o m*/
    conn.close();

    conn = getConnection("jsyang", "0009");
    stmt = conn.createStatement();
    try {
        stmt.executeQuery("SELECT \"id\", \"name\" FROM \"employee\";");
    } catch (SQLException e) {
        System.out.println("expected exception - " + e.getMessage());
    }
    conn.close();

    conn = getConnection("octopus", "bitnine");
    stmt = conn.createStatement();
    stmt.execute("GRANT SELECT ON \"" + dataMemDb.name + "\".\"__DEFAULT\" TO \"jsyang\"");
    stmt.close();
    conn.close();

    conn = getConnection("jsyang", "0009");
    stmt = conn.createStatement();
    stmt.executeQuery("SELECT \"id\", \"name\" FROM \"employee\"").close();
    stmt.close();
    conn.close();

    conn = getConnection("octopus", "bitnine");
    stmt = conn.createStatement();
    stmt.execute("DROP USER \"jsyang\"");
    stmt.close();
    conn.close();
}

From source file:net.sf.jabb.util.db.StartAndStopSQL.java

/**
 * Execute one SQL statement. RuntimeException will be thrown if SQLException was caught.
 * @param sql the statement to be executed
 *//*from  w  w w . j a v  a  2  s  .com*/
protected void executeSQL(String sql) {
    Connection conn = null;
    Statement stmt = null;

    if (useAnt) {
        try {
            AntSqlExec sqlExec = new AntSqlExec(dataSource, sql, delimiter, delimiterType);
            sqlExec.execute();
            log.info("SQL executed with Ant: " + sql);
        } catch (BuildException be) {
            throw new RuntimeException("Failed to execute SQL with Ant (" + be.getMessage() + "): " + sql, be);
        }
    } else {
        try {
            conn = dataSource.getConnection();
            stmt = conn.createStatement();
            stmt.execute(sql);
            log.info("SQL executed: " + sql);
        } catch (SQLException sqle) {
            throw new RuntimeException("Failed to execute SQL (" + sqle.getMessage() + "): " + sql, sqle);
        } finally {
            ConnectionUtility.closeConnection(conn, stmt);
        }
    }
}

From source file:morphy.service.SocketConnectionService.java

protected void handlePasswordPromptText(SocketChannelUserSession userSession, String message) {
    String userPasswordEntered = message;
    String name = userSession.getUser().getUserName();
    boolean isGuest = !userSession.getUser().isRegistered();

    UserService instance = UserService.getInstance();
    /*try { userSession.getChannel().configureBlocking(true); } catch(Exception e) { e.printStackTrace(System.err); }*/

    if (!isGuest) {

        try {/*from   w  w  w .j  a v  a2s  . co m*/
            DatabaseConnection conn = DatabaseConnectionService.getInstance().getDBConnection();
            java.sql.Statement s = conn.getStatement();
            s.execute("SELECT `password` FROM `users` WHERE `username` = '" + name + "'");
            java.sql.ResultSet r = s.getResultSet();
            if (r.next()) {
                String actualpass = r.getString(1);

                if (!actualpass.equals(userPasswordEntered)) {
                    sendWithoutPrompt("**** Invalid password! ****\n\n"
                            + "If you cannot remember your password, please log in with \"g\" and ask for help\n"
                            + "in channel 4. Type \"tell 4 I've forgotten my password\". If that is not\n"
                            + "possible, please email: support@freechess.org\n\n"
                            + "\tIf you are not a registered player, enter guest or a unique ID.\n"
                            + "\t\t(If your return key does not work, use cntrl-J)\n\nlogin: ", userSession);
                    userSession.setCurrentState(SocketChannelUserSession.UserSessionState.LOGIN_NEED_USERNAME);
                    //userSession.disconnect();
                    return;
                }
            }
        } catch (java.sql.SQLException e) {
            e.printStackTrace(System.err);
        }
    }

    boolean isLoggedIn = instance.isLoggedIn(name);
    if (isLoggedIn) {
        /* this code in this logic block should be commented out to support multiple-login. */
        userSession.send(name + " is already logged in - kicking them out.");

        UserSession sess = instance.getUserSession(name);
        sess.send("**** " + name + " has arrived - you can't both be logged in. ****");
        sess.disconnect();
        isLoggedIn = !isLoggedIn;
    }

    userSession.getUser().setUserName(name);
    userSession.getUser().setPlayerType(PlayerType.Human);
    userSession.getUser().setUserLevel(isGuest ? UserLevel.Guest : UserLevel.Player);
    userSession.getUser().setRegistered(!isGuest);
    userSession.getUser().setUserVars(new morphy.user.UserVars(userSession.getUser()));
    if (isGuest) {
        userSession.getUser().getUserVars().getVariables().put("rated", "0");
    }
    userSession.setCurrentState(SocketChannelUserSession.UserSessionState.LOGGED_IN);
    if (!isLoggedIn) {
        instance.addLoggedInUser(userSession);
    } else {
        // This code is used for multiple-login.
        /*SocketChannelUserSession sess = (SocketChannelUserSession) instance.getUserSession(name);
        sess.addUserOnMultipleLogins(userSession);
        userSession.addParentOnMultipleLogins(sess);*/
    }
    userSession.getUser().setDBID(instance.getDBID(name));

    boolean isHeadAdmin = false;

    if (!isGuest) {
        DatabaseConnection conn = DatabaseConnectionService.getInstance().getDBConnection();

        String query = "SELECT pl.`name`,pe.`value` FROM personallist pl INNER JOIN personallist_entry pe ON (pe.personallist_id = pl.id) WHERE pl.user_id = '"
                + userSession.getUser().getDBID() + "'";
        java.sql.ResultSet rs = conn.executeQueryWithRS(query);
        try {
            while (rs.next()) {
                PersonalList pl = PersonalList.valueOf(rs.getString(1));
                String val = rs.getString(2);
                userSession.getUser().getLists().get(pl).add(val);
                if (pl == PersonalList.channel) {
                    int channelNum = Integer.parseInt(val);
                    Channel c = ChannelService.getInstance().getChannel(channelNum);
                    if (c != null) {
                        c.addListener(userSession);
                    }
                }
            }
        } catch (SQLException e) {
            Morphy.getInstance().onError(e);
        }

        Map<PersonalList, Integer> map = new HashMap<PersonalList, Integer>();
        query = "SELECT `name`,`id` FROM `personallist` WHERE `user_id` = '" + userSession.getUser().getDBID()
                + "'";
        rs = conn.executeQueryWithRS(query);
        try {
            while (rs.next()) {
                map.put(PersonalList.valueOf(rs.getString(1)), rs.getInt(2));
            }
        } catch (SQLException e) {
            Morphy.getInstance().onError(e);
        }
        userSession.getUser().setPersonalListDBIDs(map);

        conn.executeQuery("UPDATE `users` SET `lastlogin` = CURRENT_TIMESTAMP, `ipaddress` = '"
                + SocketUtils.getIpAddress(userSession.getChannel().socket()) + "' WHERE `username` = '" + name
                + "'");
        ResultSet r = conn
                .executeQueryWithRS("SELECT `adminLevel` FROM `users` WHERE `username` = '" + name + "'");
        try {
            if (r.next()) {
                String level = r.getString(1);
                UserLevel val = UserLevel.valueOf(level);
                userSession.getUser().setUserLevel(val);
                if (val == UserLevel.Admin || val == UserLevel.SuperAdmin || val == UserLevel.HeadAdmin) {
                    ServerListManagerService s = ServerListManagerService.getInstance();
                    s.getElements().get(s.getList("admin")).add(name);
                }

                if (val == UserLevel.HeadAdmin) {
                    isHeadAdmin = true;
                }
            }
        } catch (SQLException e) {
            if (LOG.isErrorEnabled()) {
                LOG.error("Unable to set user level from database for name \"" + name + "\"");
                LOG.error(e);
            }
        }
    }

    StringBuilder loginMessage = new StringBuilder(200);
    loginMessage.append(
            formatMessage(userSession, "**** Starting FICS session as " + instance.getTags(name) + " ****\n"));
    if (isHeadAdmin)
        loginMessage.append("\n  ** LOGGED IN AS HEAD ADMIN **\n");
    loginMessage.append(ScreenService.getInstance().getScreen(Screen.SuccessfulLogin));
    userSession.send(loginMessage.toString());

    //         query = "SELECT DISTINCT u.username FROM `morphyics`.`personallist` pl INNER JOIN users u ON (pl.user_id = u.id) WHERE pl.`name` = 'notify'";
    //         rs = dbcs.getDBConnection().executeQueryWithRS(query);
    //         try {
    //            UserService us = UserService.getInstance();
    //            while(rs.next()) {
    //               String username = rs.getString(1);
    //               UserSession sess = us.getUserSession(username);
    //               sess.send("Notification: " + name + " has arrived.");
    //            }
    //         } catch(SQLException e) { Morphy.getInstance().onError(e); }

    UserSession[] sessions = UserService.getInstance().fetchAllUsersWithVariable("pin", "1");
    for (UserSession s : sessions) {
        UserLevel adminLevel = s.getUser().getUserLevel();

        if (adminLevel == UserLevel.Admin || adminLevel == UserLevel.SuperAdmin
                || adminLevel == UserLevel.HeadAdmin) {
            s.send(String.format("[%s (%s: %s) has connected.]", userSession.getUser().getUserName(),
                    !isGuest ? "R" : "U", SocketUtils.getIpAddress(userSession.getChannel().socket())));
        } else {
            s.send(String.format("[%s has connected.]", userSession.getUser().getUserName()));
        }
    }

    DatabaseConnectionService dbcs = DatabaseConnectionService.getInstance();

    java.util.List<String> arrivalNotedBy = new java.util.ArrayList<String>(10);
    // this query gets all usernames with this player on their notify list.
    String query = "SELECT u.username FROM personallist pl INNER JOIN personallist_entry ple ON (pl.id = ple.personallist_id) INNER JOIN users u ON (u.id = pl.user_id) WHERE pl.`name` = 'notify' && ple.`value` LIKE '"
            + userSession.getUser().getUserName() + "';";
    ResultSet rs = dbcs.getDBConnection().executeQueryWithRS(query);
    try {
        UserService us = UserService.getInstance();
        while (rs.next()) {
            String username = rs.getString(1);
            UserSession sess = us.getUserSession(username);
            if (sess != null) {
                UserVars uv = sess.getUser().getUserVars();
                boolean highlight = uv.getVariables().get("highlight").equals("1");
                if (sess != null && sess.isConnected()) {
                    sess.send("Notification: " + (highlight ? ((char) 27) + "[7m" : "") + name
                            + (highlight ? ((char) 27) + "[0m" : "") + " has arrived.");
                    arrivalNotedBy.add(sess.getUser().getUserName());
                }
            }
        }
    } catch (SQLException e) {
        Morphy.getInstance().onError(e);
    }
    if (arrivalNotedBy.size() > 0) {
        userSession.send("Your arrival was noted by: " + MorphyStringUtils
                .toDelimitedString(arrivalNotedBy.toArray(new String[arrivalNotedBy.size()]), " "));
    }

    query = "SELECT ple.`value` FROM personallist pl INNER JOIN personallist_entry ple ON (pl.id = ple.personallist_id) WHERE pl.user_id = "
            + userSession.getUser().getDBID() + " && pl.`name` = 'notify'"; // get this player's notify list
    rs = dbcs.getDBConnection().executeQueryWithRS(query);
    try {
        UserService us = UserService.getInstance();
        while (rs.next()) {
            String username = rs.getString(1);
            if (arrivalNotedBy.contains(username))
                continue;
            UserSession sess = us.getUserSession(username);
            if (sess == null)
                continue;
            UserVars uv = sess.getUser().getUserVars();
            boolean highlight = uv.getVariables().get("highlight").equals("1");
            if (sess != null && sess.isConnected())
                sess.send("Notification: " + (highlight ? ((char) 27) + "[7m" : "") + name
                        + (highlight ? ((char) 27) + "[0m" : "")
                        + " has arrived and isn't on your notify list.");
        }
    } catch (SQLException e) {
        Morphy.getInstance().onError(e);
    }
    // Notification: ChannelBot has arrived and isn't on your notify list.
}

From source file:kr.co.bitnine.octopus.frame.SessionServerTest.java

@Test
public void testDropDataSource2() throws Exception {
    MemoryDatabase newMemDb = new MemoryDatabase("DATA2");
    newMemDb.start();//  w  w w.j  a  v  a2 s.  co m

    newMemDb.runExecuteUpdate("CREATE TABLE \"TMP\" (\"ID\" INTEGER, \"NAME\" STRING)");

    Connection conn = getConnection("octopus", "bitnine");
    Statement stmt = conn.createStatement();
    stmt.execute("ALTER SYSTEM ADD DATASOURCE \"" + newMemDb.name + "\" CONNECT TO '"
            + newMemDb.connectionString + "' USING '" + MemoryDatabase.DRIVER_NAME + "'");

    stmt.execute("CREATE USER \"yjchoi\" IDENTIFIED BY 'piggy'");
    stmt.execute("GRANT SELECT ON \"" + newMemDb.name + "\".\"__DEFAULT\" TO \"yjchoi\"");

    ResultSet rs = stmt.executeQuery("SHOW OBJECT PRIVILEGES FOR \"yjchoi\"");
    int numRows = 0;
    while (rs.next()) {
        System.out.println("  " + rs.getString("TABLE_CAT") + ", " + rs.getString("TABLE_SCHEM") + ", "
                + rs.getString("PRIVILEGE"));
        ++numRows;
    }
    rs.close();
    assertEquals(numRows, 1);

    stmt.execute("ALTER SYSTEM DROP DATASOURCE \"" + newMemDb.name + '"');

    rs = stmt.executeQuery("SHOW OBJECT PRIVILEGES FOR \"yjchoi\"");
    numRows = 0;
    while (rs.next()) {
        System.out.println("  " + rs.getString("TABLE_CAT") + ", " + rs.getString("TABLE_SCHEM") + ", "
                + rs.getString("PRIVILEGE"));
        ++numRows;
    }
    rs.close();
    assertEquals(numRows, 0);

    stmt.execute("DROP USER \"yjchoi\"");

    stmt.close();
    conn.close();
    newMemDb.stop();
}

From source file:kr.co.bitnine.octopus.frame.SessionServerTest.java

@Before
public void setUp() throws Exception {
    metaMemDb = new MemoryDatabase("meta");
    metaMemDb.start();/*from w  ww. j  a  va  2  s .c  o  m*/

    dataMemDb = new MemoryDatabase("data");
    dataMemDb.start();
    dataMemDb.importJSON(SessionServerTest.class.getClass(), "/sample.json");

    Configuration conf = new OctopusConfiguration();
    conf.set("metastore.jdo.connection.drivername", MemoryDatabase.DRIVER_NAME);
    conf.set("metastore.jdo.connection.URL", metaMemDb.connectionString);
    conf.set("metastore.jdo.connection.username", "");
    conf.set("metastore.jdo.connection.password", "");

    MetaStore metaStore = MetaStores.newInstance(conf.get("metastore.class"));
    metaStoreService = new MetaStoreService(metaStore, new StdoutUpdateLoggerFactory());
    metaStoreService.init(conf);
    metaStoreService.start();

    MetaContext metaContext = metaStore.getMetaContext();
    MetaUser user = metaContext.createUser("octopus", "bitnine");
    metaContext.addSystemPrivileges(Arrays.asList(SystemPrivilege.values()), Arrays.asList(user.getName()));

    connectionManager = new ConnectionManager(metaStore);
    connectionManager.init(conf);
    connectionManager.start();

    schemaManager = SchemaManager.getSingletonInstance(metaStore);
    schemaManager.init(conf);
    schemaManager.start();

    SessionFactory sessFactory = new SessionFactoryImpl(metaStore, connectionManager, schemaManager);
    sessionServer = new SessionServer(sessFactory);
    sessionServer.init(conf);
    sessionServer.start();

    Connection conn = getConnection("octopus", "bitnine");
    Statement stmt = conn.createStatement();
    stmt.execute("ALTER SYSTEM ADD DATASOURCE \"" + dataMemDb.name + "\" CONNECT TO '"
            + dataMemDb.connectionString + "' USING '" + MemoryDatabase.DRIVER_NAME + "'");
    stmt.close();
    conn.close();
}

From source file:kr.co.bitnine.octopus.frame.SessionServerTest.java

@Test
public void testUpdateDataSource2() throws Exception {
    Connection conn = getConnection("octopus", "bitnine");
    Statement stmt = conn.createStatement();

    stmt.execute("CREATE USER \"yjchoi\" IDENTIFIED BY 'piggy'");
    stmt.execute("GRANT SELECT ON \"" + dataMemDb.name + "\".\"__DEFAULT\" TO \"yjchoi\"");

    Connection conn2 = getConnection("yjchoi", "piggy");
    Statement stmt2 = conn.createStatement();

    int rows = checkNumRows(stmt2, "employee");
    assertEquals(rows, 10);//from ww w .  j a v a 2s  .c om

    ResultSet rs;
    DatabaseMetaData metaData = conn.getMetaData();
    System.out.println("* Columns");
    rs = metaData.getColumns(dataMemDb.name, "%DEFAULT", "employee", "%");
    while (rs.next()) {
        System.out.println("  " + rs.getString("TABLE_CAT") + ", " + rs.getString("TABLE_SCHEM") + ", "
                + rs.getString("TABLE_NAME") + ", " + rs.getString("COLUMN_NAME") + ", "
                + rs.getString("REMARKS"));
    }
    rs.close();

    stmt.execute("ALTER SYSTEM UPDATE DATASOURCE \"" + dataMemDb.name + '"');

    metaData = conn.getMetaData();
    System.out.println("* Columns");
    rs = metaData.getColumns(dataMemDb.name, "%DEFAULT", "employee", "%");
    while (rs.next()) {
        System.out.println("  " + rs.getString("TABLE_CAT") + ", " + rs.getString("TABLE_SCHEM") + ", "
                + rs.getString("TABLE_NAME") + ", " + rs.getString("COLUMN_NAME") + ", "
                + rs.getString("REMARKS"));
    }
    rs.close();

    /* privileges should be preserved after update dataSource */
    rows = checkNumRows(stmt2, "employee");
    assertEquals(rows, 10);

    stmt2.close();
    conn2.close();
    stmt.close();
    conn.close();
}

From source file:fr.dyade.aaa.util.MySqlDBRepository.java

/**
 * Initializes the repository./* w  w w  .  j  ava  2s  .  com*/
 * Opens the connection, evntually creates the database and tables.
 */
public void init(Transaction transaction, File dir) throws IOException {
    this.dir = dir;

    try {
        Class.forName(driver).newInstance();
        //       conn = DriverManager.getConnection(connurl + new File(dir, "JoramDB").getPath() + ";shutdown=true;server.no_system_exit=true", "sa", "");
        Properties props = new Properties();
        /*
        props.put("user", "user1");
        props.put("password", "user1");
         */
        props.put("user", user);
        props.put("password", pass);

        /*
        conn = DriverManager.getConnection(connurl + new File(dir, "JoramDB").getPath() + ";create=true", props);
         */
        // conn = DriverManager.getConnection(connurl, props); // MySQL (the database must exist and start seperately)
        conn = getConnection();
        conn.setAutoCommit(false);
    } catch (IllegalAccessException exc) {
        throw new IOException(exc.getMessage());
    } catch (ClassNotFoundException exc) {
        throw new IOException(exc.getMessage());
    } catch (InstantiationException exc) {
        throw new IOException(exc.getMessage());
    } catch (SQLException sqle) {
        throw new IOException(sqle.getMessage());
    }

    try {
        // Creating a statement lets us issue commands against the connection.
        Statement s = conn.createStatement();
        // We create the table.
        //         s.execute("create cached table JoramDB(name VARCHAR PRIMARY KEY, content VARBINARY(256))");
        /*
        s.execute("CREATE TABLE JoramDB (name VARCHAR(256), content LONG VARCHAR FOR BIT DATA, PRIMARY KEY(name))");
         */
        s.execute("CREATE TABLE JoramDB (name VARCHAR(256), content longblob, primary key(name))"); // MySQL
        s.close();
        conn.commit();
    } catch (SQLException sqle) {
        String exceptionString = sqle.toString();
        if (exceptionString.indexOf("CREATE command denied") == -1) {
            sqle.printStackTrace();
        }
    } catch (Exception e) {
        e.printStackTrace();
    }

    try {
        insertStmt = conn.prepareStatement("INSERT INTO JoramDB VALUES (?, ?)");
        updateStmt = conn.prepareStatement("UPDATE JoramDB SET content=? WHERE name=?");
        deleteStmt = conn.prepareStatement("DELETE FROM JoramDB WHERE name=?");
    } catch (SQLException sqle) {
        sqle.printStackTrace();
        throw new IOException(sqle.getMessage());
    } catch (Exception e) {
        e.printStackTrace();
        // throw e;
    }
}

From source file:kr.co.bitnine.octopus.frame.SessionServerTest.java

@Test
public void testAddDataSourceExists() throws Exception {
    Connection conn = getConnection("octopus", "bitnine");
    Statement stmt = conn.createStatement();

    exception.expect(SQLException.class);
    stmt.execute("ALTER SYSTEM ADD DATASOURCE \"" + dataMemDb.name + "\" CONNECT TO '"
            + dataMemDb.connectionString + "' USING '" + MemoryDatabase.DRIVER_NAME + "'");

    stmt.close();/*from   w w w. j a v  a  2 s  .  co  m*/
    conn.close();
}

From source file:kr.co.bitnine.octopus.frame.SessionServerTest.java

@Test
public void testComment() throws Exception {
    Connection conn = getConnection("octopus", "bitnine");
    Statement stmt = conn.createStatement();

    stmt.execute("COMMENT ON DATASOURCE \"" + dataMemDb.name + "\" IS 'dataSource'");
    stmt.execute("COMMENT ON SCHEMA \"" + dataMemDb.name + "\".\"__DEFAULT\" IS 'schema'");
    stmt.execute("COMMENT ON TABLE \"" + dataMemDb.name + "\".\"__DEFAULT\".\"employee\" IS 'table'");
    stmt.execute(//from ww  w.j  ava2  s  .  c  o m
            "COMMENT ON COLUMN \"" + dataMemDb.name + "\".\"__DEFAULT\".\"employee\".\"name\" IS 'column'");
    stmt.execute("COMMENT ON USER \"octopus\" IS 'superuser'");

    stmt.execute("CREATE USER \"jsyang\" IDENTIFIED BY '0009';");

    stmt.close();
    conn.close();

    conn = getConnection("jsyang", "0009");
    stmt = conn.createStatement();

    try {
        stmt.execute("COMMENT ON DATASOURCE \"" + dataMemDb.name + "\" IS 'dataSource'");
    } catch (SQLException e) {
        System.out.println("expected exception - " + e.getMessage());
    }

    try {
        stmt.execute("COMMENT ON USER \"octopus\" IS 'superuser'");
    } catch (SQLException e) {
        System.out.println("expected exception - " + e.getMessage());
    }

    try {
        stmt.execute("COMMENT ON SCHEMA \"" + dataMemDb.name + "\".\"__DEFAULT\" IS 'schema'");
    } catch (SQLException e) {
        System.out.println("expected exception - " + e.getMessage());
    }

    stmt.close();
    conn.close();

    conn = getConnection("octopus", "bitnine");
    stmt = conn.createStatement();
    stmt.execute("GRANT COMMENT ON \"" + dataMemDb.name + "\".\"__DEFAULT\" TO \"jsyang\"");
    stmt.close();
    conn.close();

    conn = getConnection("jsyang", "0009");
    stmt = conn.createStatement();
    stmt.execute("COMMENT ON SCHEMA \"" + dataMemDb.name + "\".\"__DEFAULT\" IS 'schema'");
    stmt.execute("COMMENT ON TABLE \"" + dataMemDb.name + "\".\"__DEFAULT\".\"employee\" IS 'table'");
    stmt.execute(
            "COMMENT ON COLUMN \"" + dataMemDb.name + "\".\"__DEFAULT\".\"employee\".\"name\" IS 'column'");
    stmt.close();
    conn.close();

    conn = getConnection("octopus", "bitnine");
    stmt = conn.createStatement();
    stmt.execute("DROP USER \"jsyang\"");
    stmt.close();
    conn.close();
}