There are various ways to connect to a relational database to store and retrieve information. Depending on your needs you can go with a low level implementation or a higher one.
You could directly use JDBC. You need a driver that knows how to talk to your particular database, you open a connection, you prepare a statement with some SQL query, set the needed parameters for the statement (if any), execute the statement, get back a result set, iterate the result set to create objects out of the results, then close the resources you have used (result set, statement, connection).
That's the most low level way of doing it. But it has some disadvantages:
- You have a lot of boiler plate code: get connection, create statement, execute statement, loop through results, build objects, release used resources. You have to do these each time you want to run some SQL. Only the SQL is different each time, the rest you have to do again and again and again.
- you open a database connection each time you run a query. There is some overhead involved in that and depending on how many queries you run at a time, you could end up opening too many connections. Some database might have limitations per client so you can't go too high.
For limiting the connections you open you might use a connection pool like Apache DBCP, C3P0, HikariCP, etc. You still have the same boiler plate code, but now instead of creating and closing a connection, you borrow and return one to the pool. More efficient.
Now, since the boiler plate is the same each time, why not move it away in some framework or library that does it for you and you just concentrate on writing the SQL. That's what a data mapper like MyBatis does, for example. You configure it once, write the SQLs you need and map them to methods, tell MyBatis how to map the result from rows to objects, and all the boiler plate is handled by MyBatis for you. Run a method and get back the objects you want.
With MyBatis you only need to write the SQL. But some people don't even want to bother with that. You have boiler plate code and the connections handled by some library/framework for you, but why not get rid of SQL also?
That's what ORMs like Hibernate do. You map classes to tables and then Hibernate handles everything for you. It generates the needed SQL when you want to save or retrieve data from the database. Once you configure Hibernate, you can pretend that the database does not exist (at least for a while).
Each of these methods have advantages and disadvantages.
- with JDBC you need to write a lot of boiler plate code, manage transactions on your own, make sure you don't leak resources, etc. It's low level;
- with data mappers you need to write the SQLs. The data mapper does not pretend there is no database, you have to deal with it.
- with ORMs you can pretend there is no relational database involved. You deal with objects only. ORMs are great for CRUD applications but for others, an ORM might get you in some trouble. There is this thing called the object-relational impedance mismatch that shows it's ugly head. And when it does, performance is usually the first thing that goes down the drain.
These are your options. Look at your application and see which solution might be more appropriate. Considering your question, you might want to use something lightweight (not as low as direct JDBC, and not as high as Hibernate either).
But don't reinvent the wheel. Something like Commons DbUtils for example can be a good starting point. It takes JDBC boiler plate code away from you, without changing/adding to much to the way you interact with a database.