ROWID and ROWNUM in SQL

ROWNUM and ROWID are both referred to as pseudo-columns. That is, they are
not "real" columns that will show up when you DESC a table. They don't
actually exist anywhere in the database. But they're available for you to
use.

In fact, ROWNUM only exists for a row once it is retrieved from a query. It
represents the sequential order in which Oracle has retrieved the row.
Therefore it will always exist, be at least 1, and be unique (among the
rows returned by the query). Obviously it will change from query-to-query.
Let's look at a quick example:

scott@Robert> SELECT ROWNUM, ENAME, SAL
2 FROM EMP;

ROWNUM ENAME SAL
---------- ---------- ----------
1 SMITH 800
2 ALLEN 1600
3 WARD 1250
4 JONES 2975
5 MARTIN 1250
6 BLAKE 2850
7 CLARK 2450
8 SCOTT 3000
9 VOLLMAN 5000
10 TURNER 1500
11 ADAMS 1100
12 JAMES 950
13 FORD 3000
14 MILLER 1300


Ok so let's say we want the 5 highest paid employees. Should be easy:

scott@Robert> SELECT ROWNUM, ENAME, SAL
2 FROM EMP
3 WHERE ROWNUM < 6
4 ORDER BY SAL DESC;

ROWNUM ENAME SAL
---------- ---------- ----------
4 JONES 2975
2 ALLEN 1600
3 WARD 1250
5 MARTIN 1250
1 SMITH 800


Whoops! Turns out ROWNUM is assigned before results are ordered, not after.
Knowing that, we can write it like this:

scott@Robert> SELECT ENAME, SAL
2 FROM (SELECT ENAME, SAL FROM EMP ORDER BY SAL DESC) E
3 WHERE ROWNUM < 6;

ENAME SAL
---------- ----------
VOLLMAN 5000
SCOTT 3000
FORD 3000
JONES 2975
BLAKE 2850


What about ROWID? ROWID actually represents the physical location of the
record/row in the database. That being the case, it is (according to Oracle
documentation) the fastest way to retrieve a particular row. Faster than an
index, even.

Can you use ROWID to differentiate between duplicate rows?
Yes, you can. Since it actually represents the physical location of a row,
no two rows within the same table will have the same ROWID. Notice the
caveat I added: within the same table. If you're using clustering, two
records from different tables could theoretically share the same ROWID.

Do ROWIDs change?
Yes, especially with index organized or partitioned tables. Because ROWIDs
represent the physical location of a record/row, the ROWID will change
every time the record is physically moved.

Can you use ROWID as a primary key?
No, that's not advisable. While the ROWID will be unique, you would ideally
want to use a primary key that doesn't change.

How do you use ROWID to figure out what was the last record that was
processed?
Using DBMS_SQL.LAST_ROW_ID to get the ROWID of the last row processed.

You'll see ROWNUM and ROWID pop up occasionally within solutions to
problems on AskTom and various Discussion Forums, so I recommend adding it
to your own toolbelt as well.

Scenario 1:

You want to know why this is not working:
delete employee where rownum not in (select min(rownum) from employee group
by name,eno)

Ans: 1. Rowid is totally different from the rownum that you used in your
query.

2. Rowid is Unique for a record in the table (In the schema
itself).While you can't believe rownum to work like rowid(It may vary from
quey to query for the same table).

3. That's why this works fine:

SQL> Delete from employee where rowid not in (select min(rowid) from
employee group by name,eno);

If you are still confuse rownum and rowid click bellow link:

http://www.oracle.com/technetwork/issue-archive/2006/06-sep/o56asktom-086197.html

Read more ...

Oracle/PLSQL: Sequences (Autonumber)

In Oracle, you can create an autonumber field by using sequences. A
sequence is an object in Oracle that is used to generate a number sequence.
This can be useful when you need to create a unique number to act as a
primary key.


The syntax for a sequence is:


CREATE SEQUENCE sequence_name
    MINVALUE value
    MAXVALUE value
    START WITH value
    INCREMENT BY value
    CACHE value;


For example:


CREATE SEQUENCE supplier_seq
    MINVALUE 1
    MAXVALUE 999999999999999999999999999
    START WITH 1
    INCREMENT BY 1
    CACHE 20;


This would create a sequence object called supplier_seq. The first sequence
number that it would use is 1 and each subsequent number would increment by
1 (ie: 2,3,4,...}. It will cache up to 20 values for performance.


If you omit the MAXVALUE option, your sequence will automatically default
to:


MAXVALUE 999999999999999999999999999


So you can simplify your CREATE SEQUENCE command as follows:


CREATE SEQUENCE supplier_seq
    MINVALUE 1
    START WITH 1
    INCREMENT BY 1
    CACHE 20;


Now that you've created a sequence object to simulate an autonumber field,
we'll cover how to retrieve a value from this sequence object. To retrieve
the next value in the sequence order, you need to use nextval.


For example:


supplier_seq.nextval


This would retrieve the next value from supplier_seq. The nextval statement
needs to be used in an SQL statement. For example:


INSERT INTO suppliers
(supplier_id, supplier_name)
VALUES
(supplier_seq.nextval, 'Kraft Foods');


This insert statement would insert a new record into the suppliers table.
The supplier_id field would be assigned the next number from the
supplier_seq sequence. The supplier_name field would be set to Kraft Foods.


Frequently Asked Questions in Sequences



One common question about sequences is:


Question: While creating a sequence, what does cache and nocache options
mean? For example, you could create a sequence with a cache of 20 as
follows:


CREATE SEQUENCE supplier_seq
    MINVALUE 1
    START WITH 1
    INCREMENT BY 1
    CACHE 20;





Or you could create the same sequence with the nocache option:


CREATE SEQUENCE supplier_seq
    MINVALUE 1
    START WITH 1
    INCREMENT BY 1
    NOCACHE;





Answer: With respect to a sequence, the cache option specifies how many
sequence values will be stored in memory for faster access.


The downside of creating a sequence with a cache is that if a system
failure occurs, all cached sequence values that have not be used, will be
"lost". This results in a "gap" in the assigned sequence values. When the
system comes back up, Oracle will cache new numbers from where it left off
in the sequence, ignoring the so called "lost" sequence values.


Note: To recover the lost sequence values, you can always execute an
ALTER SEQUENCE command to reset the counter to the correct value.


Nocache means that none of the sequence values are stored in memory. This
option may sacrifice some performance, however, you should not encounter a
gap in the assigned sequence values.



Question: How do we set the LASTVALUE value in an Oracle Sequence?


Answer: You can change the LASTVALUE for an Oracle sequence, by executing
an ALTER SEQUENCE command.


For example, if the last value used by the Oracle sequence was 100 and you
would like to reset the sequence to serve 225 as the next value. You would
execute the following commands.


alter sequence seq_name
increment by 124;


select seq_name.nextval from dual;


alter sequence seq_name
increment by 1;


Now, the next value to be served by the sequence will be 225.

Read more ...

Enum: JPA and Enums via @Enumerated

JPA and Enums via @Enumerated

share [gp] share [fb] share [tw] share [pin] contribute

It can sometimes be desirable to have a Java enum type to represent a particular column in a database. JPA supports converting database data to and from Java enum types via the @javax.persistence.Enumerated annotation.

This example will show basic @Enumerated usage in a field of an @Entity as well as enums as the parameter of a Query. We'll also see that the actual database representation can be effectively String or int.

Enum

For our example we will leverage the familiar Movie entity and add a new field to represent the MPAA.org rating of the movie. This is defined via a simple enumthat requires no JPA specific annotations.

public enum Rating {
    UNRATED,
    G,
    PG,
    PG13,
    R,
    NC17
}

@Enumerated

In our Movie entity, we add a rating field of the enum type Rating and annotate it with @Enumerated(EnumType.STRING) to declare that its value should be converted from what is effectively a String in the database to the Rating type.

@Entity
public class Movie {

    @Id
    @GeneratedValue
    private int id;
    private String director;
    private String title;
    private int year;

    @Enumerated(EnumType.STRING)
    private Rating rating;

    public Movie() {
    }

    public Movie(String director, String title, int year, Rating rating) {
        this.director = director;
        this.title = title;
        this.year = year;
        this.rating = rating;
    }

    public String getDirector() {
        return director;
    }

    public void setDirector(String director) {
        this.director = director;
    }

    public String getTitle() {
        return title;
    }

    public void setTitle(String title) {
        this.title = title;
    }

    public int getYear() {
        return year;
    }

    public void setYear(int year) {
        this.year = year;
    }

    public Rating getRating() {
        return rating;
    }

    public void setRating(Rating rating) {
        this.rating = rating;
    }
}

The above is enough and we are effectively done. For the sake of completeness we'll show a sample Query

Enum in JPQL Query

Note the findByRating method which creates a Query with a rating named parameter. The key thing to notice is that the rating enum instance itself is passed into the query.setParameter method, not rating.name() or rating.ordinal().

Regardless if you use EnumType.STRING or EnumType.ORDINAL, you still always pass the enum itself in calls to query.setParameter.

@Stateful
public class Movies {

    @PersistenceContext(unitName = "movie-unit", type = PersistenceContextType.EXTENDED)
    private EntityManager entityManager;

    public void addMovie(Movie movie) {
        entityManager.persist(movie);
    }

    public void deleteMovie(Movie movie) {
        entityManager.remove(movie);
    }

    public List<Movie> findByRating(Rating rating) {
        final Query query = entityManager.createQuery("SELECT m FROM Movie as m WHERE m.rating = :rating");
        query.setParameter("rating", rating);
        return query.getResultList();
    }

    public List<Movie> getMovies() throws Exception {
        Query query = entityManager.createQuery("SELECT m from Movie as m");
        return query.getResultList();
    }

}

EnumType.STRING vs EnumType.ORDINAL

It is a matter of style how you would like your enum data represented in the database. Either name() or ordinal() are supported:

• @Enumerated(EnumType.STRING) Rating rating the value of rating.name() is written and read from the corresponding database column; e.g. G, PG,PG13
• @Enumerated(EnumType.ORDINAL) Rating rating the value of rating.ordinal() is written and read from the corresponding database column; e.g. 0,1, 2

The default is EnumType.ORDINAL

There are advantages and disadvantages to each.

Disadvantage of EnumType.ORDINAL

A disadvantage of EnumType.ORDINAL is the effect of time and the desire to keep enums in a logical order. With EnumType.ORDINAL any new enum elements must be added to the end of the list or you will accidentally change the meaning of all your records.

Let's use our Rating enum and see how it would have had to evolve over time to keep up with changes in the MPAA.org ratings system.

1980

public enum Rating {
    G,
    PG,
    R,
    UNRATED
}

1984 PG-13 is added

public enum Rating {
    G,
    PG,
    R,
    UNRATED,
    PG13
}

1990 NC-17 is added

public enum Rating {
    G,
    PG,
    R,
    UNRATED,
    PG13,
    NC17
}

If EnumType.STRING was used, then the enum could be reordered at anytime and would instead look as we have defined it originally with ratings starting at Gand increasing in severity to NC17 and eventually UNRATED. With EnumType.ORDINAL the logical ordering would not have withstood the test of time as new values were added.

If the order of the enum values is significant to your code, avoid EnumType.ORDINAL

Unit Testing the JPA @Enumerated

public class MoviesTest extends TestCase {

    public void test() throws Exception {

        final Properties p = new Properties();
        p.put("movieDatabase", "new://Resource?type=DataSource");
        p.put("movieDatabase.JdbcDriver", "org.hsqldb.jdbcDriver");
        p.put("movieDatabase.JdbcUrl", "jdbc:hsqldb:mem:moviedb");

        EJBContainer container = EJBContainer.createEJBContainer(p);
        final Context context = container.getContext();

        final Movies movies = (Movies) context.lookup("java:global/jpa-scratch/Movies");

        movies.addMovie(new Movie("James Frawley", "The Muppet Movie", 1979, Rating.G));
        movies.addMovie(new Movie("Jim Henson", "The Great Muppet Caper", 1981, Rating.G));
        movies.addMovie(new Movie("Frank Oz", "The Muppets Take Manhattan", 1984, Rating.G));
        movies.addMovie(new Movie("James Bobin", "The Muppets", 2011, Rating.PG));

        assertEquals("List.size()", 4, movies.getMovies().size());

        assertEquals("List.size()", 3, movies.findByRating(Rating.G).size());

        assertEquals("List.size()", 1, movies.findByRating(Rating.PG).size());

        assertEquals("List.size()", 0, movies.findByRating(Rating.R).size());

        container.close();
    }
}

Running

To run the example via maven:

cd jpa-enumerated
mvn clean install

Which will generate output similar to the following:

-------------------------------------------------------
 T E S T S
-------------------------------------------------------
Running org.superbiz.jpa.enums.MoviesTest
Apache OpenEJB 4.0.0-beta-2    build: 20120115-08:26
http://tomee.apache.org/
INFO - openejb.home = /Users/dblevins/openejb/examples/jpa-enumerated
INFO - openejb.base = /Users/dblevins/openejb/examples/jpa-enumerated
INFO - Using 'javax.ejb.embeddable.EJBContainer=true'
INFO - Configuring Service(id=Default Security Service, type=SecurityService, provider-id=Default Security Service)
INFO - Configuring Service(id=Default Transaction Manager, type=TransactionManager, provider-id=Default Transaction Manager)
INFO - Configuring Service(id=movieDatabase, type=Resource, provider-id=Default JDBC Database)
INFO - Found EjbModule in classpath: /Users/dblevins/openejb/examples/jpa-enumerated/target/classes
INFO - Beginning load: /Users/dblevins/openejb/examples/jpa-enumerated/target/classes
INFO - Configuring enterprise application: /Users/dblevins/openejb/examples/jpa-enumerated
INFO - Configuring Service(id=Default Stateful Container, type=Container, provider-id=Default Stateful Container)
INFO - Auto-creating a container for bean Movies: Container(type=STATEFUL, id=Default Stateful Container)
INFO - Configuring Service(id=Default Managed Container, type=Container, provider-id=Default Managed Container)
INFO - Auto-creating a container for bean org.superbiz.jpa.enums.MoviesTest: Container(type=MANAGED, id=Default Managed Container)
INFO - Configuring PersistenceUnit(name=movie-unit)
INFO - Auto-creating a Resource with id 'movieDatabaseNonJta' of type 'DataSource for 'movie-unit'.
INFO - Configuring Service(id=movieDatabaseNonJta, type=Resource, provider-id=movieDatabase)
INFO - Adjusting PersistenceUnit movie-unit  to Resource ID 'movieDatabaseNonJta' from 'movieDatabaseUnmanaged'
INFO - Enterprise application "/Users/dblevins/openejb/examples/jpa-enumerated" loaded.
INFO - Assembling app: /Users/dblevins/openejb/examples/jpa-enumerated
INFO - PersistenceUnit(name=movie-unit, provider=org.apache.openjpa.persistence.PersistenceProviderImpl) - provider time 406ms
INFO - Jndi(name="java:global/jpa-enumerated/Movies!org.superbiz.jpa.enums.Movies")
INFO - Jndi(name="java:global/jpa-enumerated/Movies")
INFO - Created Ejb(deployment-id=Movies, ejb-name=Movies, container=Default Stateful Container)
INFO - Started Ejb(deployment-id=Movies, ejb-name=Movies, container=Default Stateful Container)
INFO - Deployed Application(path=/Users/dblevins/openejb/examples/jpa-enumerated)
INFO - Undeploying app: /Users/dblevins/openejb/examples/jpa-enumerated
INFO - Closing DataSource: movieDatabase
INFO - Closing DataSource: movieDatabaseNonJta
Tests run: 1, Failures: 0, Errors: 0, Skipped: 0, Time elapsed: 2.831 sec

Results :

Tests run: 1, Failures: 0, Errors: 0, Skipped: 0

Read more ...