File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes JDBC and the fly likes Using JDBC.... I have no clue what I'm doing. Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Soft Skills this week in the Jobs Discussion forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Using JDBC.... I have no clue what I Watch "Using JDBC.... I have no clue what I New topic
Author

Using JDBC.... I have no clue what I'm doing.

Janeice DelVecchio
Saloon Keeper

Joined: Sep 14, 2009
Posts: 1732
    
  12

Alright, let me preface this by saying I've read the Java JDBC tutorial and several other websites trying to figure this out. I think I just need real human interaction to help me through this.

The only part of this school project I'm having trouble with is the JDBC/SQL part. I think I have the connection set up correctly:



I'm creating a student registry. Each subclass of student has the methods add(), delete(), update(), and query(). I think if I can figure out how to do one, then the others will be easy. Putting SQL statements inside Java is not coming very easy for me.

The SQL statement for the Undergraduate.add() method will look something like this, I think:


And I'm working this out that the Java method will look like this



I dunno what to put for the values parameters to make it unique for each student. I WANT to use this.studentID, et cetera, but something tells me that if I'm inside that quotation area it has no idea what's going on in the object.

I have been putting this off because it seems so hard.... although my bet is it'll be easy if someone talks me through it.



Thanks, in advance for any support anyone can offer....
Janeice


When you do things right, people won't be sure you've done anything at all.
Paul Clapham
Bartender

Joined: Oct 14, 2005
Posts: 18987
    
    8

You should use a PreparedStatement. The part you are missing is concatenating pieces of SQL with variables from your program, but really you don't want to do it because (a) it's ugly and error-prone, and (b) it can lead to an exploit called "SQL injection" where users can manipulate the inputs to damage your database. So use a PreparedStatement instead, like this:

There should have been something about PreparedStatement in the tutorials you read. If there wasn't, then in my opinion they are bad tutorials and you should go off and look for others.
Janeice DelVecchio
Saloon Keeper

Joined: Sep 14, 2009
Posts: 1732
    
  12

Thanks for your super fast response!

I will read more about prepared statements... I think the tutorials go into it, but I'm not really understanding how they will work for me. I'll keep researching.

One more question: how will my connection class change if I use PreparedStatement instead of Statement? Do I need to scrap it and start over? Should I use getConnection() instead of getStatement()?

Thanks again,
Janeice
Paul Clapham
Bartender

Joined: Oct 14, 2005
Posts: 18987
    
    8

I would suggest not writing classes which are supposed to encapsulate JDBC objects, until you understand better how they work. Just write some code which creates a connection, does some database stuff, and then closes the connection and the other JDBC objects.
Janeice DelVecchio
Saloon Keeper

Joined: Sep 14, 2009
Posts: 1732
    
  12

Paul Clapham wrote:I would suggest not writing classes which are supposed to encapsulate JDBC objects, until you understand better how they work. Just write some code which creates a connection, does some database stuff, and then closes the connection and the other JDBC objects.


I think I need some clarification here......

So I should make a new connection and statements for each method in each subclass? Like this:



I think I need to be set straight about some things:

I thought by creating one connection with the database for the application it would eliminate the need to connect and disconnect multiple times. This is a school project, but in the real world, this application might be running on many computers... wouldn't this increase network problems?

My intent (and I know what road is paved with intentions) was to connect once on logging into the application, have the application run and do the work the user needs to do, then disconnect at the end when the user exits. I was also trying to use good design (our last unit covered MVC and whatnot) and keep the database stuff out of the functional parts of the application. Are you saying I'm making things harder on myself, or am I just not doing things correctly?

Paul Clapham
Bartender

Joined: Oct 14, 2005
Posts: 18987
    
    8

Not exactly. What I'm saying is (I think) don't do any of that application-oriented business until you understand JDBC properly. Start by working through the tutorials. Get something that works. Then adapt it to your needs once you know how to produce working JDBC code.
Janeice DelVecchio
Saloon Keeper

Joined: Sep 14, 2009
Posts: 1732
    
  12

Thanks for your patience.

Hopefully this will all work out.

Janeice
Davie Lin
Ranch Hand

Joined: Aug 05, 2007
Posts: 294
I was wondering if you heard of sequences, it can help with generating the unique number you need for each student record you inserted. This would be best done from the DB level not from Java level. Hope this helps
Janeice DelVecchio
Saloon Keeper

Joined: Sep 14, 2009
Posts: 1732
    
  12

I was assigning StudentIDs on the java level... using a constructor with an incrementor and a synchronized factory method.

The mentor objects are not supposed to be saved in the db for this project and they need to have access to the Students before they are put in the db. This has caused me a lot of heartache too because I need to serialize both the mentors and the students in order for the application to work properly (mentors have an arraylist populated with students).

Do you have more information about sequences? I am particularly interested in databases and I think I'd like to read about them.

Thanks,
Janeice
Davie Lin
Ranch Hand

Joined: Aug 05, 2007
Posts: 294
google mySql sequence, that should help with understanding mySql sequence and how it works.

The mentor object, I am not clear about it, why does it need to have access to student before it is inserted into db?
so the mentor object have a list of students. do you have a query for the mentor object to get a list of students?

Janeice DelVecchio
Saloon Keeper

Joined: Sep 14, 2009
Posts: 1732
    
  12

The mentor object has an arraylist of students, each mentor works with many students. Each student holds a reference variable to point to the mentor it uses.

The databsae requires a mentor ID to not be "null" before it can be created in the db. That, to me, means the student can't exist in the db before it is associated with a mentor.

I don't have a query, I just have an arraylist of Students, and a map of mentorIDs that I let the user choose which mentor id to associate the student with. The student gets the mentorID in its reference variable, then the mentor gets the mentor.add(student) method called. Is this poorly planned?

Thanks for your input,
Janeice
Davie Lin
Ranch Hand

Joined: Aug 05, 2007
Posts: 294
Janeice, I feel like I don't understand enough about your project enough to help you decide if your plan is good or bad. perhaps you can answer some questions first,

is this a desktop app or a web app (I am better with web app but the good OO principle should apply to either case)

a map of mentorIDs that I let the user choose which mentor id to associate the student with


is the user the student or the mentor?

The student gets the mentorID in its reference variable, then the mentor gets the mentor.add(student) method called.


sounds like this answers my question from above, student is the user and when student choose a mentor the mentor.add(student) method is called.

is there a mentor table?

how much more time do you have left for this project?

can you list the problems you're having with the project again, I lost track from the previous post or the previous post wasn't very clear to me what issue you're having.

Thanks
Janeice DelVecchio
Saloon Keeper

Joined: Sep 14, 2009
Posts: 1732
    
  12

Davie Lin wrote:Janeice, I feel like I don't understand enough about your project enough to help you decide if your plan is good or bad. perhaps you can answer some questions first,

is this a desktop app or a web app (I am better with web app but the good OO principle should apply to either case)


This is a desktop application, as a project for a class.

Dave Lin wrote:
a map of mentorIDs that I let the user choose which mentor id to associate the student with


is the user the student or the mentor?


The hypothetical user, I'm assuming, is the person(s) who sign the students up for classes.

DaveLin wrote:
The student gets the mentorID in its reference variable, then the mentor gets the mentor.add(student) method called.


sounds like this answers my question from above, student is the user and when student choose a mentor the mentor.add(student) method is called.

is there a mentor table?


The spec specifically states not to make a mentor table, although I think that it would be easier.

Dave Lin wrote:how much more time do you have left for this project?

can you list the problems you're having with the project again, I lost track from the previous post or the previous post wasn't very clear to me what issue you're having.


Although I don't have UNLIMITED time, I have as much time as I need. It's a self paced online class.

Right now, the specific problem I'm having is, well....
Mentors get serialized because they are not a part of the db -- this, again, was in the spec. Each mentor HAS-A ArrayList of the students the mentor takes care of. This leads me to believe the Students need to be serialized too... unless there's a way to repopulate all the student objects from the db. This causes a catch-22 of sorts. Why should I serialize the Students when they are living happily in the db? I need to serialize them to keep the serialized mentors happy. I didn't think/know if there was a better way to approach this issue. There is also the hypothetical issue of multiple machines deserializing and then adding mentors... they're not going to get unique ids. I feel like, at that point, I cannot go crazy over the poorly designed spec and just make it work. I have, in my mind, gone above and beyond to make the db design work (using mentorID instead of mentor... for forward compatibility), and the application work after many uses.

On the whole, I don't know if the application will even compile and run. I'm hoping to have it compileable and runnable by the end of today. I'm sure I will have only minor problems with this, because I have been incredibly meticulous with the Student hierarchy and the JDBC coding. I will know better later today if I'm having any "unforseen" issues.

Thanks a bunch!
Janeice

P.S. If you need to see any of my code/classes/spec for any reason let me know.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Using JDBC.... I have no clue what I'm doing.