Layers of a simple Java program to persist data into MySQL using JDBC - ConnectionFactory, DAO, Service

Question

For educational purposes, I'm developing a simple desktop/console Java program to persist data into MySQL database using JDBC. I'd like to know if the following architecure/layers are ok (despite the computational/maintenance cost, interpretability etc):

Class Product

public class Product {
    private int id;
    private String name;
    
   //getters and setters and constructors
}

ConnectionFactory:

public abstract class ConnectionFactory {
     private static Connection conn  = null;


    public  static Connection getConnection() throws SQLException{
        if (conn == null)
               conn = DriverManager.getConnection("jdbc:mysql://localhost/database_name","user","password");

        return conn;
    }
}

Interface DAO

public interface DAO {
     void  save(T type);
     List findAll();

}

Interface ProductDAO

public class ProductDAO implements DAO{
    @Override
    public void save(Product p) {
        String sql = "INSERT INTO product values (?,?)";

        try(Connection con = ConnectionFactory.getConnection();
            PreparedStatement stmt = con.prepareStatement(sql)) {
            stmt.setInt(1, p.getId());
            stmt.setString(2, p.getName());
            stmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }


    @Override
    public List buscarTodos() {
        String sql = "SELECT * FROM product";
        List listProd =new ArrayList<>();
        try(Connection con = ConnectionFactory.criaConexao();
            PreparedStatement stmt = con.prepareStatement(sql))  {
            ResultSet rs = stmt.executeQuery();
            while (rs.next()) {
                Product p = new Product(rs.getInt("id"), rs.getString("name"));
                listProd.add(p);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return listProd;
    }
}

Interface ProductService:

public interface Service {
    void  save(T type);
    List findAll();
}

Class ProductService:

public class ProductService implements Service{
    ProductDAO productDAO ;

    public ProductService() {
        productDAO= new ProductDAO();
    }

    @Override
    public void save(Product p) {
        productDAO.save(p);
    }

    @Override
    public List findAll() {
        return productDAO.findAll();
    }
}

Class Main

public class Main {
    public static void main(String[] args) {
        Product p1 = new Product(1,"Product A");
        ProductService productService = new ProductService();
        productService.save(p1);
    }
}

I'd be grateful for comments and suggestions.

Rob Conklin · Accepted Answer

Going to go ahead and try and provide some counterpoint to rzwitserloot's answer (which is very good, and has a lot of really salient points).

I've absolutely written large-scale applications that look 90% like the suggested pattern. And they have scaled remarkably well. They have incredibly boring, verbose code, and likely can be updated 20 years later with very little learning-curve. The big advantage in building an application like that is that you get absolute control over your low-level execution of your database commands, which can provide a huge amount of performance and control. Furthermore, without dependencies on third-party libraries, they never have out-of-date or incompatible versions, and generally will keep running for decades.

The downside is, of course, writing a VERY large amount of code. That code is boring, and in many cases irrelevant, and something like JOOQ can be a huge accelerator. It's a tradeoff between control and efficiency (as ALL abstractions are). If it is important to you to exactly control how you are talking to the database, how you are managing your transactions, and exactly optimizing performance, the above patterns will give you that control, while still isolating your data-access to a separate layer and keeping business-logic separate / testable.

I do not necessarily agree with the option to embed SQL / connection logic directly into business logic, it makes unit-testing a nightmare.

What you need to ask yourself is, is it important that I control these interactions? Or is it ok if a framework takes over this because I don't need that level of control (or if you are not qualified to deal with it). It's the same question we ask any time we are building software, what do I need control over, and what can I pass over to an abstraction to handle? You ARE going to program to some abstraction, SQL itself is an abstraction layer over the database operations, the question is, what's the right abstraction layer for your particular needs? Figuring this out regularly is one of the more important bit of software architecture and design.

As for reading/writing to a file, it's once again, just another abstraction layer (albeit pretty far down compared to some of the other persistence mechanisms described here). You could easily take your DAO pattern, and back-end it with a file system. Once again, keeping that out of your business logic, because writing a unit test around file I/O is a nightmare, and watch out for concurrency/locking issues. A relational database is literally just an abstraction over files that provides some nice automatic-handling of things like locking, concurrent updates, etc. I've definitely used it for certain use-cases, usually heavy read and very light write-operations where I need complete control.

I'm sorry to say, like many answers in this forum, "it depends" is the correct answer to your question. Figuring out what it depends on is a larger discussion than what you will get here.

Layers of a simple Java program to persist data into MySQL using JDBC - ConnectionFactory, DAO, Service

Answers (2)

The very concept is mostly useless.

Serious DB interaction issues

But I am only going to store trivially small amounts of data

So what is right?

As an educational tool

Related Questions