CODEWITHSUNDEEP
javadatabaseservletsjdbcdesign-patterns
Aditya
Aditya

Reputation: 3158

How to allow user to configure database connection parameters in Servlets/JSP project?

I'm creating a simple Servlets/JSP based web app which connects to a MySQL database. Previously I had created a ConnectionParams class and a DBConnection class. ConnectionParams class contains hard-coded DB host, username, and password. DBConnection creates a JDBC connection based on it which is then used for database operations.

public class DBConnector extends HttpServlet {
    Connection conn;
    static DBConnectionProperties dbProps;

    public DBConnector() {
        try {
            BufferedReader br = new BufferedReader(new FileReader(System.getProperty("java.io.tmpdir")+"\\zlmondbcon.txt"));
            String line = br.readLine();
            dbProps = new DBConnectionProperties(line);
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }

    public Connection getConnection() {
        return conn;
    }

    public Connection connect() throws SQLException, ClassNotFoundException {
        Class.forName("com.mysql.cj.jdbc.Driver");
        conn = DriverManager.getConnection(dbProps.getConnectionString(), dbProps.getUser(), dbProps.getPass());
        return conn;
    }

    ...
    ...

I realized the issue while deploying it to a test environment which has a separate DB of its own, and the DBA may change username/password etc. So I created a separate text file with these details as WEB-INF\dbConfig.txt. This contains the info as:

dbName=mydbschema
dbUser=myuser
dbPass=mypass
dbType=mysql

The user has to edit the file in <TOMCAT HOME>\Webapps\myproject\WEB-INF everytime the server restarts. Everytime the main servlet starts, it reads the file and stores a global ConnectionParams object. I can see that this is definitely not the best practice.

I'm looking for a more robust solution for the same.

Please edit the question if you think it is not properly worded. Add comments if I should add more details.

Upvotes: 0

Views: 66

Answers (2)

Fredy Fischer
Fredy Fischer

Reputation: 458

I recommend to use connection pooling and prepare a connection for each DB you are using. Then let the user select the desired connection. With that you still need to do some manual entries when a username or a password changes or an additional DB is created, but the connections are handled by the container. When handling the connections by the webapplication, you must put a lot of attention into closing the connections not to have idle DB connections.

Upvotes: 1

Prasanth Nair
Prasanth Nair

Reputation: 509

One option would be to pull the config file out of war file and specify the path and name of the file through a system properly. This would make the task of editing and maintaining the config easy.

Upvotes: 0

Related Questions