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.
> 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.
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/>
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!
> 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 220.127.116.11 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.
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.