Geek question (very)

Myranda

Java and SQL...

How do I get class 1 to talk to the databasehandler (class 2) and extract all of the primary key numbers?

All the code I can think of uses the key to get the other data... but I want to list everything in the database in various ways in various class based windows...

The way I have been doing it 

debug.append(StockData.getName(key));

calling 

public static String getName(String key) {

        try {

            ResultSet res = stmt.executeQuery("SELECT * FROM StockDB WHERE stockID = '" + key + "'");

            if (res.next()) { // there is a result

                return res.getString(2);

            } else {

                return null;

            }

        } catch (SQLException e) {

            System.out.println(e);

            return null;

        }

    }

Which works just fine. But I've been manually adding the keys to save time... which is fine if the database were never to change size, but pretty annoying if it did change as I'd have to hard code in all the new calls for it... and on one text field I want to list all the primary keys anyway.

All the online examples I'm looking at connect to, query and print within the same class so the syntax is not right so I'm getting befuddled

Sambostar

I just pooled my load on your elbow.

thomasross20

Ask on a non-guitar related forum? Lol

chrispy108

Bit rich from a man who asks us how to buy jeans and t-shirts!

Afraid I'm not geek enough for this Myranda! Good luck

stickyfiddle

I don't know any Java but your SQL looks fine!

jonnyburgo

Hehe, she's talking funny talk.

Axe_meister

So you want to build a dynamic select statement? So you only have to build one select statement and call it with different "where clauses"?

Deijavoo

I thought I was a wee bit of a geek.

digitalscream

What do you mean by "primary key numbers"?

Do you mean all the possible values of the primary key? Or are you looking for a list of indexed fields (bearing in mind that there can only be one primary key)?

If it's latter, this SQL statement might help you...

SHOW INDEX FROM StockDB

joeyowen

I fully understand the code, but I don't understand what you are asking...

Do you want the code to know how many entities there is?

digitalscream

Also, thinking about it, if you wanted a list of all the primary key values, you could get them all in one go like this:

SELECT GROUP_CONCAT(DISTINCT StockID) as key_values FROM StockDB

That'll return a single row with one field in it, containing all of the values as a comma-separated string. The problem is that it's limited to 1024 bytes by default; if you need more than that, you can change it with:

SET SESSION group_concat_max_len = <string length>

digitalscream

Oh, and your query line is a SQL injection vulnerability waiting to happen. Never drop un-sanitised strings directly into SQL statements - use prepared statements instead:

// con is your database connection

PreparedStatement stmt = con.prepareStatement("SELECT * FROM StockDB WHERE StockId = ?");

stmt.setString(1, key);

ResultSet res = stmt.executeQuery();

Doing it this way, the substituted key value will be escaped properly and you don't have to worry about a naughty value of key breaking your code and letting the nasties in.

I haven't tested that, because I ditched Java many years ago, and for similar reasons that code may not compile. However, it should give you an idea of where to go.

Phil_aka_Pip

Beyond me I'm afraid. Bringing CPUs out of reset and getting an RTOS ready to run seems to be the highest level I can work at these days. Oh, and handling interrupts - last time I looked that code was all right.

monquixote

Is what you are asking how to get all records ?

In which case you just need to remove the where part of your query

SimonC

Have you tried turning it off, and turning it back on again?

Emp_Fab

joeyowen said:

I fully understand the code, but I don't understand what you are asking...

I don't understand the code or what she's asking, but I am strangely aroused.

GIJoe

Have you tried not using Java?

digitalscream

GIJoe said:

Have you tried not using Java?

That would, of course, be the obvious answer. For example, the Ruby/Rails equivalent of this whole arrangement would probably be:

StockDB.all.each do |stock_item|
# do something
end

...however, I'm assuming that this is for a university class, so ridiculous language requirements are pretty much the norm

Myranda

digitalscream said:

GIJoe said:

Have you tried not using Java?

That would, of course, be the obvious answer. For example, the Ruby/Rails equivalent of this whole arrangement would probably be:

StockDB.all.each do |stock_item|
# do something
end

...however, I'm assuming that this is for a university class, so ridiculous language requirements are pretty much the norm

Nail. Head. Bingo.

As for better explaining what I'm after...

I have a database handler class, and three other classes which generate frames to display information.

Now at the moment I'm typing in the primary keys to call the other columns ... but I want a better way for calling all the information I need... so right now I have 5 blobs of code to call all the information ... a getName, getPrice, getQuantity... I could do a getKey method, but right now I'd be calling it by key... which I'd be typing in manually.

And yes, I'd LOVE to use better code... but then next week is Assembly... then next month C... 

joeyowen

If it's uni work, and works just fine, why are you changing it? Why would the db change size in the future if it's just assignment work?

If it does, cross the bridge then

I teach programming on our BScs, and honestly I still don't understand what you are asking (no offence of course).

Myranda

Currently my CheckStock.class has this in

String key ="00";

debug.append(" " + newline);

debug.append(" " + StockData.getName(key));

debug.append(" " + StockData.getPrice(key)); 

debug.append(" " + StockData.getQuantity(key));

key ="11";

debug.append(" " + newline);

debug.append(" " + StockData.getName(key));

debug.append(" " + StockData.getPrice(key)); 

debug.append(" " + StockData.getQuantity(key));

key ="22";

debug.append(" " + newline);

debug.append(" " + StockData.getName(key));

debug.append(" " + StockData.getPrice(key)); 

debug.append(" " + StockData.getQuantity(key));

key ="33";

debug.append(" " + newline);

debug.append(" " + StockData.getName(key));

debug.append(" " + StockData.getPrice(key)); 

debug.append(" " + StockData.getQuantity(key));

String key ="44";

debug.append(" " + newline);

debug.append(" " + StockData.getName(key));

debug.append(" " + StockData.getPrice(key)); 

debug.append(" " + StockData.getQuantity(key));

...

Which works... but it's awful. I need to get the key from the database in the external classes in a much neater way... 

(the debug field is just me testing things... there are proper fields to put the information into.