Simple Direction Needed - Java/Databases

Hello all,

I am attempting a project in an area of which I know nothing

I have never used MS Access, mySQL or any other database products.

I did a short course on databases once so I can draw entity-relationship diagrams, I undersand Tuple and domain relational calculus but I know nothing of any practical use.

At the moment I am designing a program in Java that needs to have a database that is updated and queried by the java program.

I am at a loss as to where I can begin, heres the main Qs I have at the moment but what I really need is an outline of how this works overall:

How does java connect to the database?
Does the database just sit there as a file to be accessed by java?
Does a database need a server?
Do all my queries get written into methods in my java program? or do I have to activate the queries somehow ( I see you can save queries inside MS Access)

As you can see, these are bad questions and I really don't have a clue. Here's what I really need:

A walk through tutorial on how to make the simplest database that is updated and accessed by a java program?

Or a working example of the above, with all the files I'd be able to say how it fits together


Anyone who could make a helpfull reply would be a god send,

Please, send god!

clearz

How does java connect to the database?
This is the only 2 URLs you will ever need as a beginner java database programmer.
http://java.sun.com/j2se/1.4.2/docs/guide/jdbc/getstart/GettingStartedTOC.fm.html See Chapter 2 Connection.
http://www.w3schools.com/sql/default.asp


Does the database just sit there as a file to be accessed by java?
No the easist way is to use ODBC to connect java to an Access database file also explained in the above url
See Chapter 2 Connection.

Does a database need a server?
No an Access file can be used on its own but most real database systems such as Mysql and Oracle are run as servers you connect to the server to access the database.


Do all my queries get written into methods in my java program? or do I have to activate the queries somehow ( I see you can save queries inside MS Access)
Yes you use a special database language called SQL to do queries and updates on the database. It is very easy to learn.
See Chapter 5,6,7

The

OfflerCrocGod

psIRE let me guess UCD?:D

Draupnir

Might I recommend you get a copy of Deitel and Deitel Advanced Java How To Program, excellent chapter on JDBC and it comes with a copy of Cloudscape database software. Its dead easy.

Also, Access is such a big load of tripe my friend. Its not even a real database in the opinion of the database lecturer I once despised until I realised he was right.

UCD? I went there a few years ago to do a Law Degree but it wasn't my cup of tea.
I didnt like the orange faces

Thanks for the links clearz! Exactly what I need to get my understanding of the architecture up!!

I might even have an exact question to ask soon


Deitel had been mentioned to me before, but I thought it was too abstract, Advanced Java How To Program sounds more applied, I'll definitely leaf through it at the bookshop.

Question:

If I proceed to design the database in MS Access (I find the GUI easy to handle experimentally, even though I agree it may be bland overall) will it then just be a matter of redrawing the database in another language, and then uploading that file to a database server.

Hrm, what I mean is: Does the Database server only change the way I connect as opposed to changing the way I make the database file?

Karoma

most of the basic syntax will be the same... check if the DBserv is ANSI (standards) compliant... um, there are differences.. example: in most DBs you create a sequence (a counter that increments, typically with each entry into the DB), as aproperty of its own; whereas with access it has a feature called "auto increment" (?) which is does it automatically, but provides less flexibility...

Dr Pepper

Access gets a lot of flack but I think it's grand for small scale projects. As you said the interface is handy and most people have it. You can have it open and see at a glance what your Java queries are doing. It is dead easy to set up an ODBC connection to it also.

The *AutoNumber* feature is a bit annoying when you are using in conjunction with java as it doesn't seem to work. I usually just do a quick query to get the highest ID number in the table and then increment it to use for the next record:
SELECT MAX(idnumber) FROM Table;
.. where idnumber is declared as just a plain number field.

Changing from one database type to another is fairly handy when using the ODBC. The ODBC is supposed to be a common interface for all database types, however as Karoma said, the characters used in queries are sometimes different (e.g.) the 'wildcard' character in Access is % I think and in Oracle is * (correct me if I'm wrong!). Access also lets you away with forgetting your ; at the end of the query which others don't!

I have some relatively simple examples somewhere of ODBC connection and using queries in Java (although I think they are originally from Dietal & Dietal.. good book I find, btw). I can send them on to you if you want??

uberwolf

this is the book I used to get myself up and running on java and databases and I was coming from further behind than you are. Doesn't anser everything, but the lads here have been real helpful too

i thought they provided the full book, just checked and they don't. Still I got it on amazon market place for next to nothing.

Okely Dokely, so, at last I have got database and java connectivity down. I have worked through a few examples and am confident of being able to get it up.

I am running into a few problems I hadn't considered before, and, seeing as you guys have helped me so far, Ive come back with my bowl in mouth.

I have constructed my tables but want to be sure that I have got the relational side of things right before I proceed to queries.

Enclosed I have representations of each table.

Is my following logic right?
_________________________________________________________________
This is a Student Accomodation Application DB

'Student applications' will be updated by the program. The Primary Key is there 'Student number' to individually identify each applicant.

'RoomID' is the Primary Key for the table holding the Accomodation details. This is because each 'RoomID' represents an individual room.

'RoomID' is the Primary Key for 'Room Assignment' because a student will be registered for one room. All rooms will be assigned a student but while the assignment might change, the rooms will not.

_________________________________________________________________


I am hoping this will provide the basis for the entire database, I am hoping all necessary queries will be possible, and that room assignments will be quite simple with this format.

Unfortunately, from working with MS Access I cannot see how Foreign Keys work and I think that may break down the database.

Any unsightly errors gentlemen?