Database Connection and Query methods

Question

I have my class DBFuntcions containing this 2 methods:

The method for the connection to the database:

Connection connection_db(String username, String password, String dbname, Connection c) { try { Class.forName("org.postgresql.Driver"); c = DriverManager.getConnection("jdbc:postgresql://localhost:5432/" + dbname, username, password); System.out.println("Opened database successfully"); } catch (ClassNotFoundException | SQLException e) { System.err.println(e.getClass().getName() + ": " + e.getMessage() + "\n\n\n"); } return c; } 

And the method for the query.

void viewAll(String tableName, long startDate, long endDate, Connection c, String path) { String query = "SELECT * FROM " + tableName + " WHERE CAST(receivedtime AS integer) BETWEEN ? AND ? ORDER BY source, receivedtime;"; try (PreparedStatement stmt = c.prepareStatement(query)) { //try with resources stmt.setLong(1, startDate); //prepare query stmt.setLong(2, endDate); ResultSet rs = stmt.executeQuery(); try { while (rs.next()) { ... } 

My question it is the best to open and close the connection?

Just before calling the query and closing right after? Opening and closing the connection inside the query method? Or it is better to open it in the main and close it at the end?

I think it should depends of the number of time I try to access to it. Let's say for the moment I only have this query and run it only once.

Answer 1

Inconsistent method naming

Connection connection_db(String username, String password, String dbname, Connection c) { void viewAll(String tableName, long startDate, long endDate, Connection c, String path) { 

These methods have both different naming schemes, the top one uses all_lowercase_with_underscores, while the button user pascalCase. The latter is recommend in Java.

URL Injection using dbname

 c = DriverManager.getConnection("jdbc:postgresql://localhost:5432/" + dbname, username, password); 

You never check for special characters in the dbname. This can cause databases containing special characters to mess up your function, especially when the database can be edited though config. Escape it using URLEncoder.encode

Answer 2

Usually the best way to do this is with a DataSource.

void viewAll(DataSource source, String tableName, long startDate, long endDate, String path) { String query = "..."; try (Connection connection = source.getConnection()) { try (PreparedStatement statement = connection.prepareStatement(query)) { ... 

This has the advantage of using Java's native resource management and being able to pool the prepared statement. Also, the DataSource should keep track of the username, password and other database information.

For PostgreSQL I believe the DataSource implementation is a PGConnectionPoolDataSource.