Win a copy of The Little Book of Impediments (e-book only) this week in the Agile and Other Processes forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

JDBC question from novice

 
Duane Riech
Ranch Hand
Posts: 52
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 1056
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
> 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.
 
Avi Abrami
Ranch Hand
Posts: 1141
1
Java Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 1056
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 12
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 189
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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 ]
 
Stuart Friedberg
Greenhorn
Posts: 8
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
> 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
Posts: 227
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic