CODEWITHSUNDEEP
jakarta-eejdbcdatasource
Eduardo
Eduardo

Reputation: 1199

Dynamic JDBC Credentials

I'm developing a Java EE web application. I would like that the web application uses a JDBC connection pool.

Defining a DataSource in an application server is very easy. My problem is that all the database connections are created with the same credential (user/password), and what I need is, that depending on the user who is accessing the web application, the application get a database connection with the credentials of the user that has accesed to the web application.

I'm working with the JBoss application server and an Oracle database.

Upvotes: 2

Views: 983

Answers (1)

Beryllium
Beryllium

Reputation: 13008

I am not aware of an out-of-the-box solution (configuration-only) together with data sources.

But you can implement it, have a look at these approaches:

  • Open a database connection using plain JDBC on a per-request basis. You could open and close the connection in a javax.servlet.Filter to make sure that you are not leaking connections. Put the connection into request-scope by using HttpServletRequest.setAttribute(), for example. This is fast enough for some databases, but might be too slow for others.

  • You could handle it as well on a per-session basis, if you are not running out of resources (number of connections etc.). Put the connection into session-scope by using HttpSession.setAttribute(). In this case do an additional check in a filter as well, to make sure that the connection is still valid (this handles timeouts, closed sockets etc.). If the session is invalidated, close the connection (you can do this in a HttpSessionListener).

These are simple approaches. You could refine the first one: Keep the connection open between requests, close after some time of inactivity.

As for the first option, some code:

The filter:

@WebFilter("/pages/public/web/filter/dbconn/*")
public class DbConnectionFilter implements Filter {
    private final static Db db = new Db();

    public void init(FilterConfig fc) throws ServletException {
    }

    public void doFilter(ServletRequest req, ServletResponse resp,
            FilterChain chain) throws IOException, ServletException {

        final HttpServletRequest request = (HttpServletRequest) req;

        try {
            final Connection conn = db.getConnection(
                    request.getParameter("user"),
                    request.getParameter("password"));
            try {
                request.setAttribute("dbConnection", conn);
                chain.doFilter(req, resp);
            } finally {
                request.removeAttribute("dbConnection");
                conn.close();
            }
        } catch (SQLException e) {
            throw new ServletException(e);
        }
    }

    public void destroy() {
    }
}

using a small utility class:

class Db {
    private final String jdbcDriver = "org.postgresql.Driver";
    private final String jdbcUrl = "jdbc:postgresql://localhost/sandbox";

    public Db() {
        try {
            final Class<?> cls = Class.forName(this.jdbcDriver);
            final Driver driver = (Driver) cls.newInstance();
            DriverManager.registerDriver(driver);
        } catch (Exception e) {
            throw new IllegalStateException(e);
        }
    }

    public Connection getConnection(final String jdbcUser,
            final String jdbcPassword) throws SQLException {
        return DriverManager
                .getConnection(this.jdbcUrl, jdbcUser, jdbcPassword);
    }
}

and in your servlet, for example:

final Connection conn = (Connection) request.getAttribute("dbConnection");

This code is for illustration only (you should use request.getRemoteUser(), and the password should be stored somewhere else, ...)

I have tested it using PostgreSQL: In that case it's fast enough to do it on a request-basis.

Upvotes: 1

Related Questions