0

I want to make java code that creates a sample database in 3-4 DBMS like mysql, oracle sql, sql server etc installed on any OS - windows, linux distro, Mac OS etc.

How can I make my code:

  1. automatically (or with help from a user) locate the jdbc driver in the computer.
  2. execute a fixed set of sql commands which will work regardless of the DBMS used.

Please suggest how I can do all these things.

EDIT: This will be a back end kind of app. I am a little new to JDBC, so I am looking for simple/elementary solutions to begin with. Will switch to advanced ones later.

Thanks.

Nitesh
  • 2,286
  • 2
  • 43
  • 65
sweet dreams
  • 2,094
  • 13
  • 32
  • 46
  • May be configure as datasource (assuming web application)? – kosa Aug 01 '12 at 21:47
  • Best bet, IMOHO, is to pick a ORM/DAL that *already supports* said target databases. SQL is SQL as much as an Egg is an Egg. Although the "homework" tag might impose additional restrictions .. –  Aug 01 '12 at 21:48
  • When you got the jar file can't you just load them like this (http://www.dzone.com/snippets/add-jar-file-java-load-path)? After that I think there is a common set of SQL commands that most of DBMS support – Masood_mj Aug 01 '12 at 21:48
  • @pst - I am not sure what this means "Although the "homework" tag might impose additional restrictions " ? – sweet dreams Aug 01 '12 at 22:25
  • @sweetdreams Students might not be able to use "advanced" libraries or techniques on account of the task being "homework". Sometime the means is (a graded) part of the end; sometimes it is not. –  Aug 01 '12 at 22:45

3 Answers3

3
  1. Bundle drivers for all the supported databases with your program. Users shouldn't have to deal with JDBC drivers or connection strings. (Provide a UI to edit the latter, which might differ between the databases.)
  2. Use an ORM (like Hibernate); or, if you don't need to populate the database with data, a database migration library (like Flyway)
millimoose
  • 39,073
  • 9
  • 82
  • 134
  • 1
    Given that this is homework, i double an ORM framework would be allowed. I assume the point is to learn abstraction and jdbc. A base dao interface/class with DBMS specific subclasses would work well for this case. – Matt Aug 01 '12 at 22:02
2

1, See here... How to use a JDBC driver from an arbitrary location

2, Different DBMSs use similar but not identical syntax. You have at least 3 options:

  • only use sql commands that are supported by all the DBMSs you're interested in;

  • sniff the DBMS and modify your SQL statements accordingly;

  • use a framework that comes with an SQL abstraction layer (e.g. Java Persistence API's JPQL). I suspect this may be too much work for what you're after.

Community
  • 1
  • 1
adam77
  • 2,797
  • 3
  • 21
  • 29
0

If you want to let users find the JDBC drivers, then you shall deal with ClassLoaders and implementing custom ClassLoaders, which is not a simple thing to do. Or you shall use a Application Server which will handle this for your.

Otherwise you shall have all your JDBC drivers available in your class-path.

By the way JDBC is an adapter for working with most RDBMSes which work with SQL, each database provider made vendor specific customization to their SQL. For example you have sequences in Oracle and auto-numbers in MySQL. Or you can use limit in MySQL queries which is not available in Oracle. The solution to this problem is doing what Hibernate does (having Dialects for handling database vendor specific stuff.)

Amir Pashazadeh
  • 7,170
  • 3
  • 39
  • 69