db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Jean T. Anderson" <...@bristowhill.com>
Subject Re: How to select a random record.
Date Tue, 03 Jan 2006 17:41:38 GMT
Legolas Woodland wrote:
> ...
> by your reply i should consider that Derby has no built-in mechanism for 
> selecting a random record in an effective way.
> now , should i create an stored procedure to select a random record , 
> should i use normal sql command , or something else is correct way of 
> doing the job.

If you want to test a SQL function that returns a random number, the 
embedded tutorial from ApacheCon 2004 provides simple code that creates 
a tutRand function:

    http://db.apache.org/derby/papers/fortune_tut.html

The SQL function gets created like this:

    create function tutRand(minInt int, maxInt int)
    returns int
    language java
    parameter style java
    no sql
    external name 'examples.tutorial.derby.DerbyRand.randIntJakarta';

I put the java source for the function at the end of this email, and it 
also includes an option to use the built-in Random instead of the 
Jakarta commons math library.

The function returns a random integer between lower and upper integer 
bounds. For example, the query below returns a random integer between 1 
and 5000:

    ij> values tutRand(1,5000);
    1
    -----------
    1530

    ij> values tutRand(1,5000);
    1
    -----------
    2589

It gets used to return a random row from the fortunes table, which has 
an integer id key:

    select id, fortune
    from fortunes
    where id=tutRand(1,5000);

There are two problems with this approach.

The first problem is the values in the id column must be sequential, 
can't have any gaps at all.

The second problem is other where clauses in the query can eliminate the 
id returned by tutRand from the result set. For example, the fortunes 
table sets the level of offensiveness for a particular fortune. It would 
be possible for tutRand to return the id for an offensive fortune, so 
this query could return no results:

    select id, fortune
    from fortunes
    where id=tutRand(1,5000)
    and offense=0;

regards,

  -jean

Below is the source code (which also provides an option to use the 
built-in Random).


package examples.tutorial.derby;
import java.util.*;
import org.apache.commons.math.random.*;

public class DerbyRand
{

    /**
     * Entrypoint for SQL function that obtains a randomly generated 
integer by
     * calling java.util.Random.nextInt(), which sets the lower bound to 0.
     *
     * @param maxInt Upper bound on the number to be returned.
     * @return Randomly generated integer.
     */
    public static int randIntSun(int maxInt)
    {
        int return_int=0;
        Random rand = new Random();
        return_int = rand.nextInt(maxInt);
        return return_int;
    }

    /**
     * Entrypoint for SQL function that obtains a randomly generated 
integer by
     * calling org.apache.commons.math.random.nextInt().
     *
     * @param minInt Lower bound on the number to be returned.
     * @param maxInt Upper bound on the number to be returned.
     * @return Randomly generated integer.
     */
    public static int randIntJakarta(int minInt, int maxInt)
    {
        int return_int=0;
        RandomDataImpl randomData = new RandomDataImpl();
        return_int = randomData.nextInt(minInt,maxInt);
        return return_int;
    }
}



Mime
View raw message