Database connection layer in java

Hi All

I need to write a database connection layer using java and jdbc. It should be able to connect to various databases including oracle, db2, sql server.

I have been able to write a small class using jdbc-odbc bridge drivers. But these drivers requires end user to create a DSN on their machine.

1. I need to know which drivers should I use  so that end users do not need to create DSN. they will just provide necessary DB information to my class and class will take care of everything.
2. Are these free drivers?
3. I have never written a complete DB layer. What are the essential methods this should have?

Depending on the answers I'll post further queries.

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

What is the context - a web application? You can, btw, create a DSN-less connection
avi_indiaAuthor Commented:
A standalone Java application.
1) You need to either use type 3 or type 4 (pure Java) drivers. Have a look here for more info:

2) Some of them are. Have a look here:

3) It should basically be independent of other layers. For example if you change somethign in the upper/lower layer the database layer should not be affected. There are no specific methods you should include but in a few words you should have a method that loads the appropriate driver (maybe through a config file), and opens and closes connections to the database. Mayeb you should get more advance and look at concepts such as connection pooling and stuff but this is depends on the design of your implementation. If you need info on that though you can have a look here:

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

if ur application will run over application servers u will need not to develop connection layers at all u will use Datasource methods to get and release database connection, also the datasource configuration will hide the JDBC drivers details from the development

>>1. I need to know which drivers should I use  so that end users do not need to create DSN. they will just provide necessary DB information to my class and class will take care of everything.
use each DB vender JBDC driver for example user Oracle thin/OCI drivers to use Oracle DB, etc
>> 2. Are these free drivers?
all Vendors drivers are free as long as u have the database licence
avi_indiaAuthor Commented:
Gotta go now. Will look at these tomorrow and try them. Hopefully they will help me in addressing this problem.

Why to reinvent the wheel?
Use Hibernate instead:
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.