0

Earlier I was discussing with some colleagues the most suitable way to create a DAO in Java. Some argue that a DAO should only create, prepare bind and return the statements, which in turn should be executed by other classes, whereas others argue that the statements should be executed by DAOs. I was wondering if there is a best practice regarding this matter.

More specifically,

Option 1, DAO only constructs the statement to be executed later:

public class MyDAO {
           public static final String SELECT_ALL = "SELECT * FROM my_table WHERE column= ?";

           public static Statement getAll(String param) {
              BoundStatement boundStatement = new BoundStatement(MySession.getInstance().prepareStmt(SELECT_ALL);
              boundStatement.bind(param);
              return boundStatement;
           }
    }

Option 2, DAO also executes the statement and returns the result set:

public class MyDAO {
               public static final String SELECT_ALL = "SELECT * FROM my_table WHERE column= ?";

               public static ResultSet getAll(String param) {
                  BoundStatement boundStatement = new BoundStatement(MySession.getInstance().prepareStmt(SELECT_ALL);
                  boundStatement.bind(param);
                  return MySession.getInstance().execute(boundStatement);
               }
        }

i) Is Option 1 more correct than option 2?; ii) the other way around?; iii) Is there an Option 3 more correct than 1 and 2?; iv) all the same?

Thank you for your time.

João Matos
  • 6,102
  • 5
  • 41
  • 76
  • More of an opinion based question so probably not a great fit for Stackoverflow. That said, the DAO's should definitely execute the queries. The DAO is meant to abstract away the database from the application, and if the DAO only prepares queries, then another part of your application will still be tightly coupled to your database. Generally, avoid strong coupling if you can, even when there isn't an obvious objective reason to. Hope that helps your argument. – Andy Aug 22 '17 at 15:46
  • 1
    Adding to what @AndyPryor says, option 3 is not returning a result set or a statement but returning a domain object (or list of them) – David Aug 22 '17 at 16:00
  • This is an opinion and my opinion is the same as those above. Return a domain object or list. Also, I suggest declaring an interface and implementing it rather than using static methods. In your examples, what happens when you need to switch from SQL to a REST API? A no-sql database? Or flat files? Those don't use `ResultSet` or `Statement` at all and you'd rewrite a lot of code. You should look into Spring's `JDBCTemplate` class. It handles most of the JDBC details for you and lets you focus on the domain objects instead. – mymarkers Aug 22 '17 at 16:12
  • There are no opinions when considering a time tested design pattern such as `DAO`. They're very well defined, so there's no need to guess or discuss them. – Kayaman Aug 22 '17 at 16:19
  • Personally I would return a model object instead of a result set – vikarjramun Aug 22 '17 at 16:44

0 Answers0