Example usage for java.sql Connection setCatalog

List of usage examples for java.sql Connection setCatalog

Introduction

In this page you can find the example usage for java.sql Connection setCatalog.

Prototype

void setCatalog(String catalog) throws SQLException;

Source Link

Document

Sets the given catalog name in order to select a subspace of this Connection object's database in which to work.

Usage

From source file:net.hydromatic.foodbench.Main.java

/** Does the work. */
private void run(String jdbcUrl, String catalog, String driverClassName)
        throws IOException, SQLException, ClassNotFoundException {
    URL url = FoodMartQuery.class.getResource("/queries.json");
    InputStream inputStream = url.openStream();
    ObjectMapper mapper = new ObjectMapper();
    Map values = mapper.readValue(inputStream, Map.class);
    //noinspection unchecked
    List<Map<String, Object>> tests = (List) values.get("queries");
    if (driverClassName != null) {
        Class.forName(driverClassName);
    }/*w  ww  .  ja  v a2s.  co m*/
    Connection connection = DriverManager.getConnection(jdbcUrl);
    if (catalog != null) {
        connection.setCatalog(catalog);
    }
    Statement statement = connection.createStatement();
    for (Map<String, Object> test : tests) {
        int id = (Integer) test.get("id");
        if (!idSet.contains(id)) {
            continue;
        }
        String sql = (String) test.get("sql");
        if (jdbcUrl.startsWith("jdbc:mysql:")) {
            sql = sql.replace("\"", "`");
            sql = sql.replace(" NULLS FIRST", "");
            sql = sql.replace(" NULLS LAST", "");
            if (sql.contains("VALUES ")) {
                System.out.println("query id: " + id + " sql: " + sql + " skipped");
                continue;
            }
        }
        if (jdbcUrl.startsWith("jdbc:optiq:")) {
            sql = sql.replace("RTRIM(", "TRIM(TRAILING ' ' FROM ");
        }
        final AtomicLong tPrepare = new AtomicLong(0);
        Hook.Closeable hook = Hook.JAVA_PLAN.add(new Function1<Object, Object>() {
            public Object apply(Object a0) {
                tPrepare.set(System.nanoTime());
                return null;
            }
        });
        try {
            final long t0 = System.nanoTime();
            ResultSet resultSet = statement.executeQuery(sql);
            int n = 0;
            while (resultSet.next()) {
                ++n;
            }
            resultSet.close();
            final long tEnd = System.nanoTime();
            final long nanos = tEnd - t0;
            final long prepare = tPrepare.longValue() - t0;
            final long execute = tEnd - tPrepare.longValue();
            System.out.println("query id: " + id + " rows: " + n + " nanos: " + NF.format(nanos) + " prepare: "
                    + NF.format(prepare) + " execute: " + NF.format(execute) + " prepare%: "
                    + ((float) prepare / (float) nanos * 100f));
        } catch (SQLException e) {
            System.out.println("query id: " + id + " sql: " + sql + " error: " + e.getMessage());
            if (verbose) {
                e.printStackTrace();
            }
        } finally {
            hook.close();
        }
    }
    statement.close();
    connection.close();
}

From source file:io.seldon.db.jdbc.JDBCConnectionFactory.java

public Connection getConnection(String client, boolean readonly) throws SQLException {
    DataSource ds = dataSources.get(client);
    Connection c = null;
    if (ds != null) {
        c = ds.getConnection();//from www  .  j av  a 2  s.  c o  m
        if (readonly)
            c.setReadOnly(true);
        else
            c.setReadOnly(false);
        c.setCatalog(clientToCatalog.get(client));
    } else {
        //         logger.error("Can't get connection for client "+client);
        final String message = "Can't get connection for client " + client;
        logger.error(message, new Exception(message));
    }
    return c;
}

From source file:net.ageto.gyrex.persistence.jdbc.pool.internal.PoolDataSource.java

private Connection configure(final Connection connection) throws SQLException {
    // set the catalog to operate it
    if (null != catalog) {
        if (PoolDebug.debug) {
            LOG.debug("Setting catalog {} for connection {} from pool {}",
                    new Object[] { catalog, connection, poolId });
        }//from w  w w . j  a v  a2 s . c om
        connection.setCatalog(catalog);
    }
    return connection;
}

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

public Connection getConnection(String userName, String password) throws SQLException {
    Connection conn = null;
    Properties connectionProps = new Properties();
    connectionProps.put("user", userName);
    connectionProps.put("password", password);
    if (this.dbms.equals("mysql")) {
        conn = DriverManager.getConnection(
                "jdbc:" + this.dbms + "://" + this.serverName + ":" + this.portNumber + "/", connectionProps);
        conn.setCatalog(this.dbName);
    } else if (this.dbms.equals("derby")) {
        conn = DriverManager.getConnection("jdbc:" + this.dbms + ":" + this.dbName + ";create=true",
                connectionProps);/*from   w w w  . j a v a2  s. co m*/
    }
    return conn;
}

From source file:net.fender.sql.DriverConnectionFactory.java

public Connection getConnection() throws SQLException {
    // don't log properties as it contains plain text user name and password
    log.debug("creating connection to " + driver + " " + url);
    Connection connection = driver.connect(url, properties);
    if (autoCommit != null) {
        connection.setAutoCommit(autoCommit);
    }//  w  ww.  j  a v  a 2  s.  co  m
    if (holdability != null) {
        connection.setHoldability(holdability.getHoldability());
    }
    if (readOnly != null) {
        connection.setReadOnly(readOnly);
    }
    if (transactionIsolation != null) {
        connection.setTransactionIsolation(transactionIsolation.getLevel());
    }
    if (catalog != null) {
        connection.setCatalog(catalog);
    }
    return connection;
}

From source file:com.adaptris.jdbc.connection.FailoverDatasourceTest.java

@Test
public void testInfo() throws Exception {
    Connection conn = new MyProxy();

    try {/*  ww  w  . j a  v a2  s  .c  om*/
        try {
            conn.getMetaData();
        } catch (SQLException e) {

        }
        try {
            conn.setCatalog(conn.getCatalog());
        } catch (SQLException e) {

        }
        try {
            conn.setReadOnly(conn.isReadOnly());
        } catch (SQLException e) {

        }
        try {
            conn.setTransactionIsolation(conn.getTransactionIsolation());
        } catch (SQLException e) {

        }
        try {
            conn.setTransactionIsolation(conn.getTransactionIsolation());
        } catch (SQLException e) {

        }
        try {
            conn.getWarnings();
        } catch (SQLException e) {

        }
        try {
            conn.clearWarnings();
        } catch (SQLException e) {

        }
        try {
            conn.setHoldability(conn.getHoldability());
        } catch (SQLException e) {

        }

        try {
            conn.setSchema(conn.getSchema());
        } catch (SQLException e) {

        }

    } finally {
        JdbcUtil.closeQuietly(conn);
    }

}

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

public Connection getConnection() throws SQLException {
    Connection conn = null;
    Properties connectionProps = new Properties();
    connectionProps.put("user", this.userName);
    connectionProps.put("password", this.password);

    String currentUrlString = null;

    if (this.dbms.equals("mysql")) {
        currentUrlString = "jdbc:" + this.dbms + "://" + this.serverName + ":" + this.portNumber + "/";
        conn = DriverManager.getConnection(currentUrlString, connectionProps);

        this.urlString = currentUrlString + this.dbName;
        conn.setCatalog(this.dbName);
    } else if (this.dbms.equals("derby")) {
        this.urlString = "jdbc:" + this.dbms + ":" + this.dbName;

        conn = DriverManager.getConnection(this.urlString + ";create=true", connectionProps);

    }/*  w  ww  . jav  a 2  s  . c o  m*/
    System.out.println("Connected to database");
    return conn;
}

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

public Connection getConnectionToDatabase() throws SQLException {
    {//from   w ww  . j  ava2 s. co m
        Connection conn = null;
        Properties connectionProps = new Properties();
        connectionProps.put("user", this.userName);
        connectionProps.put("password", this.password);

        // Using a driver manager:

        if (this.dbms.equals("mysql")) {
            //        DriverManager.registerDriver(new com.mysql.jdbc.Driver());
            conn = DriverManager.getConnection(
                    "jdbc:" + dbms + "://" + serverName + ":" + portNumber + "/" + dbName, connectionProps);
            conn.setCatalog(this.dbName);
        } else if (this.dbms.equals("derby")) {
            //        DriverManager.registerDriver(new org.apache.derby.jdbc.EmbeddedDriver());
            conn = DriverManager.getConnection("jdbc:" + dbms + ":" + dbName, connectionProps);
        }
        System.out.println("Connected to database");
        return conn;
    }
}

From source file:edu.ku.brc.specify.dbsupport.BuildFromGeonames.java

/**
 * Needed when a new geonames file is gotten
 *//*w  ww  .j  a  v  a  2s. c o m*/
@SuppressWarnings("unused")
private void buildISOCodes() {
    try {
        DBConnection currDBConn = DBConnection.getInstance();
        String dbName = currDBConn.getDatabaseName();
        DBMSUserMgr dbMgr = DBMSUserMgr.getInstance();
        if (dbMgr != null) {
            boolean isConnected = dbMgr.connectToDBMS(itUsername, itPassword, currDBConn.getServerName(),
                    dbName, currDBConn.isEmbedded());
            if (isConnected) {
                Connection conn = dbMgr.getConnection();
                Statement stmt = null;
                ResultSet rs = null;
                PreparedStatement pStmt = null;
                try {
                    conn.setCatalog(dbName);

                    boolean isFieldOK = true;
                    if (!dbMgr.doesFieldExistInTable("geoname", "ISOCode")) {
                        if (BasicSQLUtils.update(conn,
                                "ALTER TABLE geoname ADD COLUMN ISOCode VARCHAR(24) DEFAULT NULL") == 0) {
                            isFieldOK = false;
                        }
                    } else {
                        int numMissingISOCodes = BasicSQLUtils.getCountAsInt(currDBConn.getConnection(),
                                "SELECT COUNT(*) FROM geoname WHERE ISOCode IS NULL");
                        if (numMissingISOCodes == 0) {
                            dbMgr.close();
                            return;
                        }
                    }

                    if (isFieldOK) {
                        Vector<Object[]> rowData = BasicSQLUtils
                                .query("SELECT code, name, geonameId FROM continentcodes");
                        // Do Continents and Oceans
                        for (Object[] cols : rowData) {
                            String iso = cols[0].toString();
                            String name = cols[1].toString();
                            StringBuilder str = new StringBuilder(
                                    String.format("UPDATE geoname SET ISOCode='%s' WHERE ", iso));
                            String sql = "";
                            if (name.contains("Ocean") && !name.contains("Oceania")) {
                                str.append(String.format("asciiname LIKE '|%s' AND fcode = 'OCN'", name));
                                sql = str.toString().replace("|", "%");
                            } else {
                                str.append("geonameId = " + cols[2]);
                                sql = str.toString();
                            }
                            System.out.println(sql);
                            int rv = BasicSQLUtils.update(sql);
                            if (rv < 1) {
                                log.error("Can't update geoname: " + sql);
                            }
                        }

                        int cnt = 0;
                        String countStr = "SELECT COUNT(*) ";
                        String post = " FROM geoname WHERE fcode = 'GULF' AND country IS NOT NULL AND LENGTH(country) > 0";
                        String pre = "SELECT geonameId, country";
                        String sql = pre + post;

                        double totalCnt = BasicSQLUtils.getCountAsInt(conn, countStr + post);
                        doProgress(0, 100, "Initializing...");

                        for (Object[] cols : BasicSQLUtils.query(sql)) {
                            String id = cols[0].toString();
                            String iso = cols[1].toString();
                            sql = String.format("UPDATE geoname SET ISOCode='%s' WHERE geonameId = %s", iso,
                                    id);
                            System.out.println(sql);
                            int rv = BasicSQLUtils.update(sql);
                            if (rv < 1) {
                                log.error("Can't update geoname: " + sql);
                            }
                            cnt++;
                            doProgress((int) ((double) cnt / totalCnt * 100.0));
                        }

                        post = " FROM geoname g WHERE ISOCode IS NULL ORDER BY g.country ASC, g.fcode DESC, g.admin1 ASC, g.admin2 ASC";
                        pre = "SELECT g.geonameId, g.fcode, g.country, g.admin1, g.admin2";
                        sql = pre + post;

                        totalCnt = BasicSQLUtils.getCountAsInt(conn, "SELECT COUNT(*) " + post);
                        doProgress(0, 100, "Preparing data...");

                        pStmt = conn.prepareStatement("UPDATE geoname SET ISOCode=? WHERE geonameId = ?");
                        stmt = conn.createStatement();
                        rs = stmt.executeQuery(sql);

                        cnt = 0;
                        boolean hasCountry = false;
                        boolean isOK = true;
                        StringBuilder sb = new StringBuilder();
                        while (rs.next() && isOK) {
                            String fcode = rs.getString(2);
                            if (!hasCountry) {
                                hasCountry = fcode.equals("PCLI");
                            }

                            if (hasCountry) {
                                String country = rs.getString(3);
                                if (StringUtils.isEmpty(country)) {
                                    continue;
                                }

                                String state = rs.getString(4);
                                String county = rs.getString(5);

                                sb.setLength(0);
                                if (StringUtils.isNotEmpty(country)) {
                                    sb.append(country);
                                    if (!fcode.equals("PCLI") && StringUtils.isNotEmpty(state)) {
                                        sb.append(state);
                                        if (StringUtils.isNotEmpty(county)) {
                                            sb.append(county);
                                        }
                                    }
                                    pStmt.setString(1, sb.length() > 24 ? sb.substring(0, 24) : sb.toString());
                                    pStmt.setInt(2, rs.getInt(1));
                                    isOK = pStmt.executeUpdate() == 1;
                                }
                            }
                            cnt++;
                            if (cnt % 2000 == 0) {
                                doProgress((int) ((double) cnt / totalCnt * 100.0));
                                //doProgress(cnt);
                            }
                        }
                    } else {
                        UIRegistry.showLocalizedError("ERROR_ERR_GEODB", dbName);
                    }
                } catch (Exception ex) {
                    ex.printStackTrace();
                } finally {
                    try {
                        if (rs != null)
                            rs.close();
                        if (stmt != null)
                            stmt.close();
                        if (pStmt != null)
                            pStmt.close();

                    } catch (Exception ex) {
                    }
                }
            } else {
                UIRegistry.showLocalizedError("ERROR_LOGIN_GEODB", dbName);
            }
            dbMgr.close();

        } else {
            edu.ku.brc.af.core.UsageTracker.incrHandledUsageCount();
            edu.ku.brc.exceptions.ExceptionTracker.getInstance().capture(BuildFromGeonames.class,
                    new Exception("Couldn't create DBMSMgr"));
        }
    } catch (Exception ex) {
        ex.printStackTrace();
        edu.ku.brc.af.core.UsageTracker.incrHandledUsageCount();
        edu.ku.brc.exceptions.ExceptionTracker.getInstance().capture(BuildFromGeonames.class, ex);
    }

}

From source file:edu.ku.brc.af.core.db.MySQLBackupService.java

public boolean doRestoreBulkDataInBackground(final String databaseName, final String options,
        final String restoreZipFilePath, final SimpleGlassPane glassPane, final String completionMsgKey,
        final PropertyChangeListener pcl, final boolean doSynchronously, final boolean doDropDatabase) {
    getNumberofTables();//from   www .j a va  2s .  c  o m

    SynchronousWorker backupWorker = new SynchronousWorker() {
        long dspMegs = 0;

        @Override
        protected Integer doInBackground() throws Exception {
            boolean skipTrackExceptions = BasicSQLUtils.isSkipTrackExceptions();
            BasicSQLUtils.setSkipTrackExceptions(false);
            try {
                String userName = itUsername != null ? itUsername : DBConnection.getInstance().getUserName();
                String password = itPassword != null ? itPassword : DBConnection.getInstance().getPassword();

                DBConnection currDBConn = DBConnection.getInstance();
                String dbName = currDBConn.getDatabaseName();
                DBMSUserMgr dbMgr = DBMSUserMgr.getInstance();
                if (dbMgr != null) {
                    boolean isConnected = dbMgr.connectToDBMS(userName, password, currDBConn.getServerName(),
                            dbName, currDBConn.isEmbedded());
                    if (isConnected) {
                        if (doDropDatabase) {
                            if (dbMgr.doesDBExists(databaseName) && !dbMgr.dropDatabase(databaseName)) {
                                log.error("Database[" + databaseName + "] could not be dropped before load.");
                                UIRegistry.showLocalizedError("MySQLBackupService.ERR_DRP_DB", databaseName);
                                return null;
                            }

                            if (!dbMgr.createDatabase(databaseName)) {
                                log.error("Database[" + databaseName + "] could not be created before load.");
                                UIRegistry.showLocalizedError("MySQLBackupService.CRE_DRP_DB", databaseName);
                                return null;
                            }
                        }

                        DatabaseDriverInfo driverInfo = DatabaseDriverInfo
                                .getDriver(DBConnection.getInstance().getDriverName());
                        String connStr = DBConnection.getInstance().getConnectionStr();

                        DBConnection itDBConn = DBConnection.createInstance(driverInfo.getDriverClassName(),
                                driverInfo.getDialectClassName(), databaseName, connStr, userName, password);
                        Connection connection = itDBConn.createConnection();
                        connection.setCatalog(databaseName);

                        List<File> unzippedFiles = ZipFileHelper.getInstance()
                                .unzipToFiles(new File(restoreZipFilePath));

                        boolean dbCreated = false;
                        for (File file : unzippedFiles) {
                            //System.out.println(file.getName());
                            if (file.getName().equals("createdb.sql")) {
                                long size = restoreFile(connection, file);
                                log.debug("size: " + size);
                                dbCreated = true;
                            }
                        }

                        if (dbCreated) {
                            for (File file : unzippedFiles) {
                                if (file.getName().endsWith("infile")) {
                                    String fPath = file.getCanonicalPath();
                                    if (UIHelper.isWindows()) {
                                        fPath = StringUtils.replace(fPath, "\\", "\\\\");
                                    }
                                    String sql = "LOAD DATA LOCAL INFILE '" + fPath + "' INTO TABLE "
                                            + FilenameUtils.getBaseName(file.getName());
                                    log.debug(sql);
                                    //System.err.println(sql);
                                    int rv = BasicSQLUtils.update(connection, sql);
                                    log.debug("done fPath[" + fPath + "] rv= " + rv);
                                    //System.err.println("done fPath["+fPath+"] rv= "+rv);
                                }
                            }
                        }

                        ZipFileHelper.getInstance().cleanUp();

                        /*if (!dbMgr.dropDatabase(databaseName))
                        {
                        log.error("Database["+databaseName+"] could not be dropped after load.");
                        UIRegistry.showLocalizedError("MySQLBackupService.ERR_DRP_DBAF", databaseName);
                        }*/

                        setProgress(100);

                        //errorMsg = sb.toString();

                        itDBConn.close();
                    } else {
                        // error can't connect
                    }
                } else {
                    // error
                }
            } catch (Exception ex) {
                ex.printStackTrace();
                errorMsg = ex.toString();
                if (pcl != null) {
                    pcl.propertyChange(new PropertyChangeEvent(MySQLBackupService.this, ERROR, 0, 1));
                }

            } finally {
                BasicSQLUtils.setSkipTrackExceptions(skipTrackExceptions);
            }
            return null;
        }

        @Override
        protected void done() {
            super.done();

            JStatusBar statusBar = UIRegistry.getStatusBar();
            if (statusBar != null) {
                statusBar.setProgressDone(STATUSBAR_NAME);
            }

            if (glassPane != null) {
                UIRegistry.clearSimpleGlassPaneMsg();
            }

            if (StringUtils.isNotEmpty(errorMsg)) {
                UIRegistry.showError(errorMsg);
            }

            if (statusBar != null) {
                statusBar.setText(UIRegistry.getLocalizedMessage(completionMsgKey, dspMegs));
            }

            if (pcl != null) {
                pcl.propertyChange(new PropertyChangeEvent(MySQLBackupService.this, DONE, 0, 1));
            }
        }
    };

    if (glassPane != null) {
        glassPane.setProgress(0);
    }

    backupWorker.addPropertyChangeListener(new PropertyChangeListener() {
        public void propertyChange(final PropertyChangeEvent evt) {
            if (MEGS.equals(evt.getPropertyName()) && glassPane != null) {
                int value = (Integer) evt.getNewValue();

                if (value < 100) {
                    glassPane.setProgress((Integer) evt.getNewValue());
                } else {
                    glassPane.setProgress(100);
                }
            }
        }
    });

    if (doSynchronously) {
        return backupWorker.doWork();
    }

    backupWorker.execute();
    return true;
}