selecting records in a derby database

[Bob M <rg...@orcon.net.nz>]

hi

I have a derby database and a table with over 4,000 records...............

I wish to:- 

a) get the number of records in the table
b) retrieve the last record
c) update this record
d) add a new record and
e) delete the first (oldest) record

Please could somebody give me examples of the type of code needed in each
instance

Thanks

Bob M



--
View this message in context: http://apache-database.10148.n7.nabble.com/selecting-records-in-a-derby-database-tp134896.html
Sent from the Apache Derby Users mailing list archive at Nabble.com.

Re: selecting records in a derby database

[Bryan Pendleton <bp...@gmail.com>]

> int recordCount = rs.getInt();
>
> I get compilation error on last line
>
> The method getInt(int) in the type ResultSet in not applicable for the
> arguments ()

Indeed, you have to specify the columnIndex.

http://docs.oracle.com/javase/7/docs/api/java/sql/ResultSet.html#getInt(int)

Specify getInt( 1 ), since there is one and only one
column in the query "select count(*) from..."

bryan

Re: selecting records in a derby database

[Bob M <rg...@orcon.net.nz>]

Hi Bryan

so..............

rs. = s.executeQuery("SELECT COUNT(*) FROM Table_name");
rs.next();
int recordCount = rs.getInt();

I get compilation error on last line

The method getInt(int) in the type ResultSet in not applicable for the
arguments ()

Bob M



--
View this message in context: http://apache-database.10148.n7.nabble.com/selecting-records-in-a-derby-database-tp134896p134906.html
Sent from the Apache Derby Users mailing list archive at Nabble.com.

Re: selecting records in a derby database

[Bryan Pendleton <bp...@gmail.com>]

> rs = s.executeQuery("SELECT COUNT(*) FROM TABLE_NAME");
> int recordCount = ??????;

The query returned a result set.

Call next() on the result set to move to the first (and only)
row in the result set.

Then you can call getInt() to get the integer value of the
first (and only) column in that row.

Here's a good place to start with your learning:

http://docs.oracle.com/javase/tutorial/jdbc/basics/

thanks,

bryan

Re: selecting records in a derby database

[Bob M <rg...@orcon.net.nz>]

A further question...............

Getting the number of records..........

My code is :-

rs = s.executeQuery("SELECT COUNT(*) FROM TABLE_NAME");
int recordCount = ??????;

What is ???????

Bob M




--
View this message in context: http://apache-database.10148.n7.nabble.com/selecting-records-in-a-derby-database-tp134896p134902.html
Sent from the Apache Derby Users mailing list archive at Nabble.com.

Re: selecting records in a derby database

[Dyre Tjeldvoll <Dy...@oracle.com>]

On 20. okt. 2013, at 04:01, Bob M <rg...@orcon.net.nz> wrote:

> hi
> 
> I have a derby database and a table with over 4,000 records............…

It is easier to think about a table as a hash table or tree, rather than an array. So there isn't really a concept of first or last other than the ordering imposed by the primary key (or other index). Meaning that there isn't an builtin way to retrieve the first or oldest entry (or the newest) unless you have a column that reflects the age (could be an auto increment column).

(Note that Derby is different from some other databases (e.g. Oracle) which have something called row id which would have given you this ordering column automatically)
 
> 
> I wish to:- 
> 
> a) get the number of records in the table

SELECT COUNT(*) FROM t

> b) retrieve the last record

Assuming you have a column x which imposes the order you're after:

SELECT MAX(x) FROM t

to get the max key

SELECT * FROM t WHERE x=<max key>

> c) update this record

UPDATE t SET a=.., b=.. … WHERE x = <max key>

> d) add a new record and

INSERT INTO t VALUES(…)

> e) delete the first (oldest) record

DELETE FROM t WHERE x = (SELECT MIN(x) FROM t)

HTH,

Dyre

Re: selecting records in a derby database

[Bob M <rg...@orcon.net.nz>]

Hello Dyre

Many thanks for your suggestions...............

The primary key consists of the first two columns being Date and Hours e.g.
01-01-2009, 0
01-01-2009, 6
01-01-2009, 12
01-01-2009, 18
02-01-2009, 0 etc.

Does this lead to alternative suggestions?

Bob M



--
View this message in context: http://apache-database.10148.n7.nabble.com/selecting-records-in-a-derby-database-tp134896p134901.html
Sent from the Apache Derby Users mailing list archive at Nabble.com.