I need help refactoring the database module where all the creation, insertion, etc. code are in their own respective files.

Sean Francis N. Ballais on June 25, 2018

NOTE: I was given permission to post the codes here. I am in my internship currently. We are maintaining a system that is quite used in the orga... [Read Full]
markdown guide

Having them as singletons is a warning flag for me already.


If I proceed with DAOs, I'd modify Information first so that I would not touch the rest of the system and accidentally break anything. If I do break anything, at least I know where I screwed up.

Add some unit/integration tests instead. Given tests will be implemented properly, you'll have a guarantee that if anything breaks, your module is not the culprit.



Singletons tend to be misused so my warning flag goes up.

Add some unit/integration tests instead. Given tests will be implemented
properly, you'll have a guarantee that if anything breaks, your module is
not the culprit.

I'll be doing that in a bit.


Singletons tend to be misused so my warning flag goes up.

Singleton is just a way to ensure that only one instance of some class is present at runtime. If the project you're working on has DI set up, then you probably can get rid of them without much trouble.

Since there is a text file with queries, maybe try JDBI. It's not exactly an ORM, but from the SQL code you posted I've got an impression that you may do fine without one.

I do am considering JDBI and utilize the data access object pattern. Thanks!


Having them as singletons is a warning flag for me already. So, are there any problems with the approach?

Several, but the god class being a singleton in this case is not one of them. As a thought experiment, imagine someone would edit that property file and the system would read the file in at runtime in different states, so you'd be up to some possibly very dynamic behaviour (mind you, not in the good sense of the word). Luckily, that seems not to be the case. Externalizing the statements doesn't make a lot of sense, and I think inlining them would be quite appropriate, that seems not to be the main pain point.

From the snippets you gave, even without knowing the exact usage of the Information class it is very clear, that this class doesn't provide any meaningful abstraction in itself (really, which parts of any software system are NOT in some sense "Information") and exposes a much too large API surface to the classes which are using it, a text book example for a violation of the interface segregation principle actually. Therefore an important step would be to identify the several interfaces that are hidden in your twohundred-something methods, extract them, make the Information class implement them and make the current users of Information gradually independend from it. I wouldn't touch Information further, except for adding which of the newly identified and created interfaces it already implements. When you have the interfaces, you will see if it actually were DAOs in disguise or if the underlying design was more of a row data gateway or an active record. Be it as it may, when you have the interfaces extracted reimplement them and consider this as an opportunity to re-inline the queries. And when you are done with this, the names of the methods would be a worthy aspect on which some to spend some time working on.

Good luck with cleaning the augean stables ;)


I would look to using an ORM. Essentially you could create your entities and models from your database schema. This first step is fairly easy to do with most ORM's. I have done this many times with Hibernate. Focus on keeping things simple. Do not map your ER relationships (foreign keys). The reason not to map your foreign keys in your ORM is that you will very likely want to control the fetching and storing of your data on a finer-grained scale unless your database is relatively small and fast. One caveat here is that some ORMS require you to map the foreign keys (ie. they do not provide a means not to). Don't pick these ORMs.

Do not focus on performance at first. Keep a list of the tables / objects that are frequently read and seldom written.

There are many ways to generate your POJOS, here are a couple to get you started. Do some looking around and find one to your liking. I personally like the fluent style so I have listed these first (I have not used these):





Once you have generated your mappings you will still have another class (or classes) to hold some custom db logic (including that logic that makes up for not mapping the foreign keys). This will serve as the glue layer between your app(s) and your ORM.

Lastly, you will have a singleton class that will handle reading the configuration and connecting / disconnecting from your DB. Here is where I inject my encrypted passwords read from a secured auth source as well.

Lastly you will tune for performance. Here is where you will provide some stateless sessions for batch reading of those tables that were frequently read and seldom written as well as for anything that is written and seldom read (g.g. logs). Here you may also need to judiciously introduce some locking.

Depending on the size of your DB and your familiarity with ORMs these steps will take at least one and likely several of those mythical man-months.


I'd take issue with the db statements being isolated, can't insert and update in the same transaction (unless I'm missing something).

I would recommend against using a standard repository pattern in the case you migrate to an ORM since a repo pattern layer tends to have adverse effects on the ORMs performance and the additional abstraction doesn't add any benefit. But I guess that would depend on the ORM.

If a web application, I like creating a connection instance per request.

code of conduct - report abuse