Xjyuk

I'm going through some code and trying to figure out how to get rid of duplicates from the following two methods. Both methods execute a simple login/register query to a local MySQL DB. I couldnt figure out a good design pattern to solve my problem, especially to get rid of the duplicate try-catch.

Any advice?

public class DatabaseHandler 

 [...]

 public static boolean checkLogin(String username, String password) 
 Connection connection = createConenction();
 PreparedStatement statement = null;

 String query = "select * from users where username = ? and password = ? ";

 try 
 statement = connection.prepareStatement(query);
 statement.setString(1, username);
 statement.setString(2, password);
 ResultSet result = statement.executeQuery();
 return result.next(); //True if User exists
 catch(SQLException e) 
 return false;
 finally 
 try 
 statement.close();
 catch (SQLException e) 
 e.printStackTrace();
 
 
 

 public static boolean registerUser(String username, String password) 
 Connection connection = createConenction();
 PreparedStatement statement = null;

 String query = "insert into users (username, password) values (?, ?)";

 try 
 statement = connection.prepareStatement(query);
 statement.setString(1, username);
 statement.setString(2, password);
 statement.executeUpdate();
 return true;
 catch(SQLException e) 
 return false;
 finally 
 try 
 statement.close();
 catch (SQLException e) 
 e.printStackTrace();
 
 
 

```

Funny coincidence, I just created the solution for your problem (but in PHP). The common thing here is the one-time use of a prepared statement, the difference is in the query string and arguments, so what you need is a function that accepts a query string, an argument determining what execution function to use (executeQuery() or executeUpdate()) and the parameters for the query:

// note: QueryType is an enum you should define
public static ResultSet query(String sql, QueryType queryType, Object... queryArgs)
 // ...

Include the try-catch blocks in this function too of course, and return null when according to queryType there should be no results.

Note: I have no experience with database interaction with Java, so if there are only 2 different statement execution functions then you can use a boolean argument instead of the enum QueryType.

Then your code could look like this:

public static boolean checkLogin(String username, String password) 
 String sql = "select * from users where username = ? and password = ? ";
 ResultSet result = query(sql, QueryType.RETURN_RESULT, username, password);
 return result.next(); // true if User exists

A note about your query string: I find it more convenient to use CAPS for keywords and lowercase text for names, it highlights the structure of the query.