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

Question

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.

can you post your database connection class? So we can see how you get the details from dbConfig.txt — Jonathan Laliberte
– Jonathan Laliberte, Commented Oct 31, 2018 at 13:11

Fredy Fischer · Accepted Answer · 2018-11-04 08:53:03Z

1

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.

answered Nov 4, 2018 at 8:53

Fredy Fischer

4484 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Prasanth Nair · Accepted Answer · 2018-10-31 14:32:31Z

0

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.

answered Oct 31, 2018 at 14:32

Prasanth Nair

5093 silver badges12 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related