Java连接到多个数据库 [英] Java connecting to multiple databases

问题描述

我正在创建一个连接到多个数据库的Java应用程序.用户将能够从下拉框中选择要连接的数据库.

I am creating a java application that connects to multiple databases. A user will be able to select the database they want to connect to from a drop down box.

然后，该程序通过将名称传递给创建初始上下文的方法来连接到数据库，以便它可以与oracle Web逻辑数据源进行通讯.

The program then connects to the database by passing the name to a method that creates an initial context so it can talk with an oracle web logic data source.

public class dbMainConnection {

    private static dbMainConnection conn = null;
    private static java.sql.Connection dbConn = null;
    private static javax.sql.DataSource ds = null;
    private static Logger log = LoggerUtil.getLogger();

    private dbMainConnection(String database) {

         try {

            Context ctx = new InitialContext();

            if (ctx == null) {
                log.info("JDNI Problem, cannot get InitialContext");
            }

                database = "jdbc/" + database;
                log.info("This is the database string in DBMainConnection" + database);
                ds = (javax.sql.DataSource) ctx.lookup (database);


        } catch (Exception ex) {
            log.error("eMTSLogin: Error in dbMainConnection while connecting to the database : " + database, ex);
        }

    }

    public Connection getConnection() {

        try {

            return ds.getConnection();

        } catch (Exception ex) {
            log.error("Error in main getConnection while connecting to the database : ", ex);
            return null;
        }

    }

    public static dbMainConnection getInstance(String database) {

        if (dbConn == null) {
            conn = new dbMainConnection(database);
        }

        return conn;

    }

    public void freeConnection(Connection c) {
        try {
            c.close();
            log.info(c + "  is now closed");
        } catch (SQLException sqle) {
            log.error("Error in main freeConnection : ", sqle);
        }
    }

}

我的问题是，如果有人忘记为数据库创建数据源，但他们仍将其添加到下拉框中，该怎么办?现在发生的是，如果我尝试连接到没有数据源的数据库，则会出错，提示无法建立连接.这就是我想要的，但是如果我先连接到具有数据源的数据库(该数据库可以正常工作)，然后尝试连接到不具有数据源的数据库，则再次出现

My problem is what happens if say someone forgets to create the data source for the database but they still add it to the drop down box? Right now what happens is if I try and connect to a database that doesn't have a data source it errors saying it cannot get a connection. Which is what I want but if I connect to a database that does have a data source first, which works, then try and connect to the database that doesn't have a data source, again it errors with

javax.naming.NameNotFoundException:无法解析"jdbc.peterson".已解决的"jdbc"；剩下的名字是彼得森".

javax.naming.NameNotFoundException: Unable to resolve 'jdbc.peterson'. Resolved 'jdbc'; remaining name 'peterson'.

再次期望，但是让我感到困惑的是，它抓住了另一个数据库的最后一个良好连接，并像处理任何事情一样处理了一切.

Which again I would expect but what is confusing me is it then grabs the last good connection which is for a different database and process everything as if nothing happened.

有人知道为什么吗?是weblogic缓存连接或作为故障安全的东西吗?以这种方式创建连接是一个坏主意吗?

Anyone know why that is? Is weblogic caching the connection or something as a fail safe? Is it a bad idea to create connections this way?

推荐答案

您正在将唯一的数据源(以及连接和dbMainConnection)存储在类的 static 变量中.每次有人请求数据源时，都用新数据源替换以前的数据源.如果从JNDI获取数据源时发生异常，则静态数据源将保持原样.您不应该在静态变量中存储任何内容.由于您的dbMainConnection类是用数据库的名称构造的，并且有多个数据库名称，因此将其设为单例是没有意义的.

You're storing a unique datasource (and connection, and dbMainConnection) in a static variable of your class. Each time someone asks for a datasource, you replace the previous one by the new one. If an exception occurs while getting a datasource from JNDI, the static datasource stays as it is. You should not store anything in a static variable. Since your dbMainConnection class is constructed with the name of a database, and there are several database names, it makes no sense to make it a singleton.

只需使用以下代码即可访问数据源:

Just use the following code to access the datasource:

public final class DataSourceUtil {
    /**
     * Private constructor to prevent unnecessary instantiations
     */
    private DataSourceUtil() {
    }

    public static DataSource getDataSource(String name) {
        try {
            Context ctx = new InitialContext();
            String database = "jdbc/" + name;
            return (javax.sql.DataSource) ctx.lookup (database);
        }
        catch (NamingException e) {
            throw new IllegalStateException("Error accessing JNDI and getting the database named " + name);
        }
    }
}

让调用者从数据源获得连接，并在使用完毕后将其关闭.

And let the callers get a connection from the datasource and close it when they have finished using it.

这篇关于Java连接到多个数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！