This week's book giveaway is in the OCPJP forum.
We're giving away four copies of OCA/OCP Java SE 7 Programmer I & II Study Guide and have Kathy Sierra & Bert Bates on-line!
See this thread for details.
The moose likes JDBC and the fly likes JDBC question from novice Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of OCA/OCP Java SE 7 Programmer I & II Study Guide this week in the OCPJP forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "JDBC question from novice" Watch "JDBC question from novice" New topic
Author

JDBC question from novice

Duane Riech
Ranch Hand

Joined: Feb 27, 2003
Posts: 52
Although I'm a SCJP and SCJD, I have never worked with JDBC so I'd appreciate some help/advice from some experts...
I'd like to write and sell a Java application I've been designing but I'd need to use a database. I would want it to run on both Windows and Unix, Linux, etc., so I'd like to hide the database portion from the application. Just issue a SQL statement and keep the two separate.
1. Does JDBC supply that layer between the application and database.
2. Will I run into significant problems trying to write SQL statements that are generic? I don't anticipate doing really crazy SQL statements, it's more an issue of accessing information.
3. Has anyone ever written an application for distribution and run into copywrite issues by bundling a database. i.e. How/what do you do when an application uses a database and your selling the application to the general public.

Thanks....
Ron Newman
Ranch Hand

Joined: Jun 06, 2002
Posts: 1056
> Will I run into significant problems trying to write SQL statements that are generic?
Possibly. Most releases of MySQL, for instance, do not implement subqueries or check constraints. Oracle and MySQL have somewhat different syntax for declaring foreign and primary keys. Outer join syntax differs as well.


Ron Newman - SCJP 1.2 (100%, 7 August 2002)
Avi Abrami
Ranch Hand

Joined: Oct 11, 2000
Posts: 1134

Hi Ron,

Oracle and MySQL have somewhat different syntax
for declaring foreign and primary keys. Outer
join syntax differs as well.

Actually, since version 9i of the Oracle [O]RDBMS, the ANSII standard "join" syntax is now supported. I understand that there longstanding "(+)" syntax will eventually be deprecated.
Good Luck,
Avi.
Ron Newman
Ranch Hand

Joined: Jun 06, 2002
Posts: 1056
I'm glad to hear that. I liked the MySQL syntax a lot better. With Oracle's, there was no obvious way to feed the result of an outer join to an inner join, or vice versa, for instance:

[ July 21, 2003: Message edited by: Ron Newman ]
Mohanraj Naidu
Greenhorn

Joined: Jun 29, 2003
Posts: 12
Hi All,
There are lot many syntax diff between leading databases. You'll have to do speciall handling for those. Better idea will to write your seprate database layer to make it generic which will used JDBC layer internally
Regards,
<Mohan/>
Philip Shanks
Ranch Hand

Joined: Oct 15, 2002
Posts: 189
You didn't mention what you need to use the database for or what sort of application this is, so my suggestion may seem to come from left field.
Could you consider using an embedded database in your application? If this meets your application requirements you could control all aspects of data storage and retreival.
There are a few good Java embedded database products around, like HSQL (an Open Source project),
Berkeley DB (a very well-known Open Source embedded DB), Pointbase or IBM's Cloudscape (both commercial products).
[ August 06, 2003: Message edited by: Philip Shanks ]

Philip Shanks, SCJP - Castro Valley, CA
My boss never outsources or has lay-offs, and He's always hiring. I work for Jesus! Prepare your resume!
Stuart Friedberg
Greenhorn

Joined: Aug 11, 2003
Posts: 8
> I'd like to write and sell a Java application I've been designing
> but I'd need to use a database.
I'm in exactly the same situation, and did a bit of research.
> 1. Does JDBC supply that layer between the application and database.
Not entirely. You also need a database-specific driver. I say
database-specific, but on Windows there is a driver for ODBC. Of course,
that just pushes the database-specific issue one level further away,
as ODBC must have the right driver for the database.
> 2. Will I run into significant problems trying to write SQL
> statements that are generic?
There will definitely be issues. They may or may not be "significant
problems" depending on what you want to do. Some combinations of
database and driver will only scroll forward through resultsets
(BDE 5.1.1.1 and Paradox 8). Until fairly recently, MySQL didn't support
even basic subqueries (with any driver). In my modest experience,
support for DDL (e.g., CREATE TABLE) is much spottier and more erratic
than DML (e.g., SELECT). Keep in mind that there is no standard SQL
(well, through SQL-92, don't know everything that's in SQL-99) for
things as simple as creating an INDEX.
> 3. Has anyone ever written an application for distribution and
> run into copywrite issues by bundling a database. i.e. How/what
> do you do when an application uses a database and your selling the
> application to the general public.
If you want to be legal, you pay the license fees and pay attention
to the licensing restrictions of, for example, the GPL. In many cases,
the fees and/or restrictions may be the determining factor in your
selection of a bundled database. The royalty fee quote I got for
distributing MySQL as part of my planned project far exceeded the
intended retail price for the entire product! (Note that this is for
a commercial product. If I were releasing my project under the GPL,
the situation would be quite different.)
Be aware that you may have to license both the database itself and
the drivers needed by JDBC.
It's of no use for inexperienced customers, but you can always leave
an opening for the customer to configure their own JDBC datasource, and
leave the licensing issues for the database and its drivers to them.
If you do, you need to supply the expert customer with enough information
about the SQL you use for them to evaluate a database/driver combo and
see if it supports everything your app does. This is where the "generic
SQL" issue really kicks in. If you lock down the database/driver combo,
you can use any feature supported by that combo.
I suspect this is one reason products like ThumbsPlus (excellent product
from www.cerious.com) end up distributing Access. Not because Access
is especially good, but because it's cheap to distribute on the Windows
platform.
Dana Hanna
Ranch Hand

Joined: Feb 28, 2003
Posts: 227
Does your application need a database server shared by several clients? If not, check out Hypersonic(sp?). It's a 100% pure Java embedded database that supports serialization to a file accross sessions. This means that it loads with the application, and runs in the same JVM.
 
 
subject: JDBC question from novice