This week's giveaway is in the EJB and other Java EE Technologies forum. We're giving away four copies of EJB 3 in Action and have Debu Panda, Reza Rahman, Ryan Cuprak, and Michael Remijan on-line! See this thread for details.
I have a JDBC question and would appreciate any answers or advice anyone could give me. I've never used JDBC before so my question may be trivial or obvious to someone. I want to write a Java application using JDBC to access a MS Access database. The schema design is something like this example: I have a table named "autoparts" which contains numerous, unique parts. I also have a table name "autos" which has numerous references to the "autoparts" table. However, each "autoparts" entry may be used by numerous "autos", so I have a many-to-many relationship. I wanted to keep an list/array/Vector of "autopart" entries in each "auto" record, but I don't see a "blob" type when defining a table in MS Access. Can Access handle this datatype? If not, is there another MS database which is installed with Win 2000 that will handle this? I don't have a lot of money to buy a high dollar database. Also, is there a good JDBC book, ISBN # ???, that could show me the best way to write this?
One way to handle this kind of relationship is to add a table to your schema that contains the Primary Keys of both entities involved. For instance in your case you could have a table composed of PART_NUMBER (for parts) and MAKE + MODEL (for cars). Whenever you need to relate both entities use this table in a join operation, so you can retrieve columns from each individual entity. Regards. -Paulo
MS Access doesn't have blob datatype but I'm not sure why you would need it. Before diving too far into JDBC, you should spend some time reading up on database design. As for JDBC, Sun has a good tutorial. As for books, my personal favorite is JDBC API Tutorial and Reference ISBN #0201433281 but there may be a new edition out by now and unless you plan to do signifiant JDBC work, you really don't need a book.
Although the main question of this thread from a few days ago is different from yours, along the way in that thread I give an example of the approach Paulo is talking about -- using a bridge table to break up a many-to-many relationship into two one-to-many relationships with the keys of the bridge table being the combination of the keys of the other tables. In your case you could have a table "auto" that contains identifying data about each auto, "part" that contains data about each part, and, say, "build" (can't think of a good name for this table) that links autos to their constituent parts.
I want to echo what the others have told you. This is neither a JDBC issue nor an Access issue. This is a fundamental database design and/or SQL issue. Very fundamental. If you stick a list of parts inside an auto record, you are wasting the power of a relational database and SQL. Create a table of autos, a table of parts, and ANOTHER TABLE that shows the relationship between autos and their parts. If you have more than one relationship, create more than one table. The is-a-part-of table needs two columns to serve as its key: the auto and the part. The values in both of these columns should be the keys from the corresponding (auto, part) tables. The is-a-part-of table might have additional columns, like how many of the part are required. E.g., 4mm x 15mm socket head capscrews: 142 required. But these are optional. To find the parts that belong to an auto, first look up the auto. Take the key from the auto table record, and use it to find all matching records in the is-a-part-of table. You will get back a set of records, one for each part that belongs to that auto. In SQL terms, this is called a "join". Assuming appropriate table declarations (I don't know if Access lets you create tables using SQL, or if you have to do it in Access directly): SELECT Part.StockNumber, Part.Description, AutoParts.RequiredCount FROM Auto, AutoParts, Parts WHERE Auto.Description = "Mercedes 180 sedan" AND Auto.Key = AutoParts.Auto AND AutoParts.Part = Part.Key There is other SQL syntax that explicitly uses the work JOIN, but it's not needed for something this simple. If you expect to do that query often, on large tables, you should create an index on the auto column of the AutoParts table. But that's an optimization that can wait until the basic database is laid out properly.