AppletTalk.com Forum Index AppletTalk.com
Java discussions newsgroups
 
Archives   FAQFAQ   SearchSearch   MemberlistMemberlist   UsergroupsUsergroups   RegisterRegister 
 ProfileProfile   Log in to check your private messagesLog in to check your private messages   Log inLog in 

Lost with MS Access and Java

 
Post new topic   Reply to topic    AppletTalk.com Forum Index -> Java and Databases
View previous topic :: View next topic  
Author Message
raas
Guest
PostPosted: Sun Apr 02, 2006 1:12 am    Post subject: Lost with MS Access and Java Reply with quote


Admittedly I'm very new at Java and could be in over my head, but I
need to dig my way out. My dilema is that I need to be able to connect
to an MS Access database. I need to be able to read from the database
and display those records on a GUI. I also need to be able to write
new records to the database from a GUI. The fields will, of course, be
variables and not hard coded. I mention this because I can already
connect to the database, and I can write to it if it is hard coded.

I need the Access connection code in a class of its own so it can be
used by other classes.

How do I accomplish all of this? As you can tell, I've spent a lot of
time getting this far, but now I'm stuck.

thanks in advance
Back to top
Bjorn Abelli
Guest
PostPosted: Sun Apr 02, 2006 3:12 pm    Post subject: Re: Lost with MS Access and Java Reply with quote


"raas" wrote...

Quote:
My dilema is that I need to be able to connect
to an MS Access database. I need to be able to
read from the database and display those records
on a GUI.

As you write further down your post, you've already managed to insert
records in the database. Then this would be the simplest part.

Just use the same connection as when you write, but read the records from
the db instead. Simple example:

Connection conn = <as you've created it before...>;
Statement stmt = conn.createStatement ();

// Depending on what data you want to retrieve,
// you formulate a query
String query = "SELECT column1, column2 FROM mytable";

// Retrieve the result in a ResultSet
ResultSet rset = stmt.executeQuery (query);

while (rset.next ())
{
String colOne = rset.getString (1);
String colTwo = rset.getString (2);

// Put the result in the appropriate fields,
// or table, which no longer is a database related
// question, but a GUI one. Read a tutorial
// on e.g. Swing.
}

rset.close();
stmt.close();
conn.close()

Quote:
I also need to be able to write
new records to the database from a GUI.

The fields will, of course, be
variables and not hard coded.

I mention this because I can already connect to the
database, and I can write to it if it is hard coded.

If you already can do that, what's your problem? For starters, you could
just exchange the hardcoded values with those retrieved from the GUI.

However, it might be a better question than you think. It's a good practice
to use PreparedStatements when you can.

Suppose you have the values in two JTextFields:

String colOne = textField1.getText();
String colTwo = textField2.getText();


String sql =
"INSERT INTO mytable (column1, column2) VALUES (?,?)";

PreparedStatement ps = con.prepareStatement(sql);
ps.setString(1, colOne);
ps.setString(2, colTwo);

ps.executeUpdate();

Quote:
I need the Access connection code in a class of its
own so it can be used by other classes.

So you need to layer your application?

Then you could start off with just creating a class that does just that, as
a helper class to the other classes.

But as it seems you're not familiar working with neither GUIs nor databases,
I'd suggest you wait with that until you're comfortable with the other
things first.

// Bjorn A
Back to top
raas
Guest
PostPosted: Sun Apr 02, 2006 4:12 pm    Post subject: Re: Lost with MS Access and Java Reply with quote


That helped. I'm not totally unfamiliar with programming, but mostly
in Cobol with a little in VB. Java's way of doing things is different
from what I'm used to, so I need help in understanding the basics of
manuverability from one area to another. Once it's shown me in
actuality, then I have no problem being able to apply it to my own
program.

Your help was GREAT! I do have the connection to the database in its
own class. I just can't seem to call it from another class and be able
to access variables created by the connection, such as con for my
connection, or s for my SQL variable. I can copy the connection into
the other class and it works, but then I have to copy the connection
coding into every class that I want to use the connection for. Maybe
an old Cobol programmer can't get help unlearning old ways, I don't
know. If you or anyone can help some more, it would be appreciated.
If not........ I fully understand.

Thanks again for a great post.
Back to top
Bjorn Abelli
Guest
PostPosted: Sun Apr 02, 2006 7:12 pm    Post subject: Re: Lost with MS Access and Java Reply with quote
"raas" wrote...
Quote:
That helped. I'm not totally unfamiliar with programming, but mostly
in Cobol with a little in VB. Java's way of doing things is different
from what I'm used to, so I need help in understanding the basics of
manuverability from one area to another. Once it's shown me in
actuality, then I have no problem being able to apply it to my own
program.

Your help was GREAT! I do have the connection to the database in its
own class. I just can't seem to call it from another class and be able
to access variables created by the connection, such as con for my
connection, or s for my SQL variable. I can copy the connection into
the other class and it works, but then I have to copy the connection
coding into every class that I want to use the connection for. Maybe
an old Cobol programmer can't get help unlearning old ways, I don't
know.

It's not that complicated, once you've grasped the idea of Object-oriented
programming, or at least the fundamentals of OO Analysis and Design. That
will help you immensly.

I began a million years ago as a COBOL programmer myself, so believe me when
I say it's not impossible...

I actually believe it's easier for somone proficient in COBOL, than one with
only VB... ;-)

Quote:
If you or anyone can help some more, it would be appreciated.
If not... I fully understand.

What I think you struggle with, is the concepts of classes and objects.

As a COBOL-programmer, you're familiar with calling subroutines and other
procedures. With OO, it's quite similar, but I think you can get a bit
further if you think of it as you're creating another computer (the object)
which you call the procedures on.

In order to make an object to do something, it needs to exist, i.e. to be
instantiated from a class.

To give a short example:
========================
import java.io.*;
import java.sql.*;

class ConnectionHolder
{
private Connection con = null;

public ConnectionHolder(String path) throws SQLException
{
DriverManager.registerDriver(new sun.jdbc.odbc.JdbcOdbcDriver());

String url =
"jdbc:odbc:DRIVER={Microsoft Access Driver (*.mdb)};DBQ="
+ path;

File file = new File(path);

if (!file.exists())
{
throw new SQLException("\nFile \"" + path + "\" not found");
}
con = DriverManager.getConnection(url);
}

public Connection getConnection()
{
return con;
}

public void closeConnection()
{
try
{
con.close();
}
catch (SQLException x)
{
// Already closed, somehow...
}
}
}
========================

Now, if you want to *use* a class, you need to instantiate an object from
it, and call some method on the object, e.g.:

try
{
ConnectionHolder ch =
new ConnectionHolder("C:\\db\\mytables.mdb");
}
catch (SQLException se)
{
// Display some error message,
// and break out of the routine...
}

Connection conn = ch.getConnection();

// Do whatever with your connection...

ch.closeConnection();

There are thousands of variants on this, depending on how you need to use
it, e.g. if you want to use a single connection from many places, you could
probably make your ConnectionHolder instance "static", in order to get the
connection where you need it, and close it when you close the application.

Possibly.

I suggest you look up some good book on OO Analysis and Design, so you don't
get stuck in the procedural habits of COBOL and VB. There's a lot more to
say about OO than can fit into a small post on Usenet...

// Bjorn A
Back to top
raas
Guest
PostPosted: Mon Apr 03, 2006 9:12 pm    Post subject: Re: Lost with MS Access and Java Reply with quote
That's great information! And, yes, I want to use the connection from
many places. So I set my class as : public static class
ConnectionHolder ??? then how do I call the class from another class?
I know how to instantiate it from another class with the "new"
keyword. is that all there is to it and I can then use the connection
in the new class?

Thanks again.
Back to top
Bjorn Abelli
Guest
PostPosted: Mon Apr 03, 2006 10:12 pm    Post subject: Re: Lost with MS Access and Java Reply with quote
"raas" wrote...

Quote:
That's great information! And, yes, I want to use the connection from
many places. So I set my class as : public static class
ConnectionHolder ???

No...

What I meant was that you could have a static instance of "ConnectionHolder"
in one of the other classes...

Quote:
then how do I call the class from another class?
I know how to instantiate it from another class with the
"new" keyword. is that all there is to it and I can
then use the connection in the new class?

As I said, there's thousands of variants on how the classes "need" to be
defined, depending on what and where they're used, and most importantly, how
they're related to each other.

I'll try to give you another example, this time with a more "global"
approach, as you haven't provided any clues on what the other classes look
like.

It's not how I would have done it myself, but maybe it will help you for
now... ;-)

=============================================

import java.io.*;
import java.sql.*;

class TheCon
{
private static Connection con = null;

public static void setConnection(String path) throws SQLException
{
DriverManager.registerDriver(new sun.jdbc.odbc.JdbcOdbcDriver());

String url =
"jdbc:odbc:DRIVER={Microsoft Access Driver (*.mdb)};DBQ="
+ path;

File file = new File(path);

if (!file.exists())
{
throw new SQLException("\nFile \"" + path + "\" not found");
}
con = DriverManager.getConnection(url);
}

public static Connection getConnection()
{
return con;
}

public static void closeConnection()
{
try
{
con.close();
}
catch (SQLException x)
{
// Already closed, somehow...
}
}
}
========================

Now, if you want to use this example, the *first* time you need the
connection, start with:

try
{
TheCon.setConnection("C:\\db\\mytables.mdb");
}
catch (SQLException se)
{
// Display some error message,
// and break out of the routine...
}

Then you can use this line wherever you need the connection, no need to set
it again:

Connection conn = TheCon.getConnection();

// Do whatever with your connection...

Now you can do that over and over again from all kinds of classes and
objects within your application, until it's all over and done, where you
clean up things with:

TheCon.closeConnection();


// Bjorn A
Back to top
Bjorn Abelli
Guest
PostPosted: Tue Apr 04, 2006 1:12 am    Post subject: Re: Lost with MS Access and Java Reply with quote
"raas" wrote...

Quote:
I'm trying to compare for true the textbox on a
form created by NetBeans. I use the .getText() to get the text out of
the box and then I use the parseInt() to strip it into numeric so that
I can do computations with it. However, if the textbox from the
NetBeans form happens to be blank (i.e. spaces or null (I don't know
which)) then I get an error. I want to test for spaces, but my test
always give me a false, never a true. Can you help on this one?

if (HireDateTBox.getText() == "") {HireDate = 0;} else { HireDate =
Integer.parseInt(HireDateTBox.getText());}

The "==" tests for equality, but here comes the OO aspect of Java that
easily can be missed when coming from other languages, when dealing with
objects it tests for equality of the *references* on each side.

In this case "==" tests if what you get from the textbox is the *same
object* as ""!

Strings in Java are objects, and it's a common mistake to make.

Instead you want to check if the objects *values* are the same:

if ( HireDateTBox.getText().trim().equals("") ) {...}

I added a "trim" just in case someone puts in a blank... ;-)

Quote:
Sorry, should I have started a new thread?

Well, maybe you should have, and maybe posted it in comp.java.lang.help
instead, as it doesn't have so much to do with databases anymore... ;-)

// Bjorn A
Back to top
raas
Guest
PostPosted: Tue Apr 04, 2006 1:12 am    Post subject: Re: Lost with MS Access and Java Reply with quote
That worked Great! I have programmed for 39 years, but feel like a
starting novice trying to learn Java and connect to an Access Database
at the same time. There is no one within 150 miles of me that would
know a thing about Java, so no one to ask for even the simplest of
questions, and I don't like to keep posting simple questions that I
know can be done and have done a hundred times in another language.
The only way I'm going to learn anything is by getting working code
somewhere and disecting it. It's how I learned before, but I can't
seem to find anywhere, or any books, that give full code and explain it
for any circumstances.

Therefore, I REALLY APPRECIATE what you've done for me.

One question, I hope. I'm trying to compare for true the textbox on a
form created by NetBeans. I use the .getText() to get the text out of
the box and then I use the parseInt() to strip it into numeric so that
I can do computations with it. However, if the textbox from the
NetBeans form happens to be blank (i.e. spaces or null (I don't know
which)) then I get an error. I want to test for spaces, but my test
always give me a false, never a true. Can you help on this one?

if (HireDateTBox.getText() == "") {HireDate = 0;} else { HireDate =
Integer.parseInt(HireDateTBox.getText());}

No matter what I put in the HireDateTBox, This test works the same.
Data or no data.

Thanks again.

Sorry, should I have started a new thread? Good to know there are
Cobol-ers out there.
Back to top
Roedy Green
Guest
PostPosted: Tue Apr 04, 2006 2:12 am    Post subject: Re: Lost with MS Access and Java Reply with quote
On Tue, 4 Apr 2006 03:11:06 +0200, "Bjorn Abelli"
<bjorn_abelli (AT) DoNotSpam (DOT) hotmail.com> wrote, quoted or indirectly
quoted someone who said :

Quote:
if (HireDateTBox.getText() == "") {HireDate = 0;} else { HireDate =
Integer.parseInt(HireDateTBox.getText());}

see http://mindprod.com/jgloss/gotchas.html#COMPARISON
--
Canadian Mind Products, Roedy Green.
http://mindprod.com Java custom programming, consulting and coaching.
Back to top
raas
Guest
PostPosted: Tue Apr 04, 2006 2:12 am    Post subject: Re: Lost with MS Access and Java Reply with quote
Thanks for all your help!!!! That worked as well!!!!

I'll learn. Later I hope to hear from you again. You do a good
service helping learners like me.
Back to top
Sandor Spruit
Guest
PostPosted: Tue Apr 04, 2006 9:12 am    Post subject: Re: Lost with MS Access and Java Reply with quote
raas wrote:
Quote:
Admittedly I'm very new at Java and could be in over my head, but I
need to dig my way out. My dilema is that I need to be able to connect
to an MS Access database. I need to be able to read from the database
and display those records on a GUI. I also need to be able to write
new records to the database from a GUI. The fields will, of course, be
variables and not hard coded. I mention this because I can already
connect to the database, and I can write to it if it is hard coded.

I need the Access connection code in a class of its own so it can be
used by other classes.

How do I accomplish all of this? As you can tell, I've spent a lot of
time getting this far, but now I'm stuck.

Just FYI, if you want to get to know Java a little better.
Sun has some good tutorials and introduction stuff online:
http://java.sun.com/docs/books/tutorial/

Most of it comes with examples and sourcecode, there's an
introduction on OO principles, too.

Nice to see an experienced person's that's not scared to
get his hands dirty working with new stuff :)

Hope this helps,
Sandor
Back to top
raas
Guest
PostPosted: Tue Apr 04, 2006 11:12 pm    Post subject: Re: Lost with MS Access and Java Reply with quote
I appreciate the help and direction. Everyone on this site shows a
great deal of class (no pun intended). THANKS to all of you!
Back to top
John Mishefske
Guest
PostPosted: Fri Apr 07, 2006 5:12 am    Post subject: Re: Lost with MS Access and Java Reply with quote
raas wrote:
Quote:
I appreciate the help and direction. Everyone on this site shows a
great deal of class (no pun intended). THANKS to all of you!

I'll add my 2 cents...


using the jdbc:odbc:DRIVER with MS Access has always been an iffy task. Sun clearly
states that the jdbc:odbc:DRIVER is not production quality.

I've used it ad-hoc apps with success. My purpose in stating this is that you may
run into problems that are caused by the driver that should not reflect badly on
Java as a whole. Just a FYI...

--
'---------------
'John Mishefske
'---------------
Back to top
raas
Guest
PostPosted: Sat Apr 15, 2006 6:12 pm    Post subject: Re: Lost with MS Access and Java Reply with quote
Thank you, I've taken some hits already. This helps.
Back to top
Display posts from previous:   
Post new topic   Reply to topic    AppletTalk.com Forum Index -> Java and Databases All times are GMT
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2006 phpBB Group
SEO toolkit © 2004-2006 webmedic.