General Oracle-related JDBC questions
Where do I get JDBC drivers for Oracle (various versions)?What are all the driver files for?Oracle JDBC Developer's Guide: 10g, 10g Release 2, 11g Release 1, 11g Release 2Oracle JDBC Java API (Javadoc) (older version are available at the corresponding driver download page)Official Oracle JDBC FAQWhich version of JDBC driver shall I use?What should the URL for Oracle driver look like? (see also the immediatelly following point on that page)
Oracle-specific JDBC questions
this section deals with Oracle-specific JDBC code. If you follow advice in this section, your code won't be portable to other databases.
How to use arrays of Oracle SQL objects?
Is there a free edition of an Oracle database?How to use several different 'databases' on one Oracle server?How to correctly use pagination queries in Oracle?
Other Resources and Valuable Links
Oracle Technology Network. Downloads for the Database, the official documentation, and much more.oracle-developer.net. Great articles about what's new in the most recent Oracle versionsAsk Tom. Where one of the most respected expert in the field will help youRichard Foote's Oracle Blog. Blog focusing specifically on Oracle Indexes and Database Administration (advanced)
Q: Which version of JDBC driver shall I use?
Firstly, have a look at the driver compatibility matrix
. You need to choose a version that is compatible with the version(s) of the database you're going to connect to.
You might also want to make sure the driver version you choose supports certain version of JDBC specification
That still leaves a lot of options, though. Our suggestion is to use the newest compatible version, since it may contain improvements and optimizations not available in prior versions. A good example is the JDBC update batching, which offers the best performance in the 11g version of the driver. Prior to that, you'd have to use the Oracle's own way of update batching to attain the topmost performance.
The above paragraphs do not distinguish different types
of drivers. The JDBC Type 4 (thin) is mostly used nowadays, a brief introduction of other types of drivers can be found here
Q: How to use several different 'databases' in one database instance?
This question is often asked by people who move to Oracle from other RDBMs. Some database servers allow to create separate 'databases' on a single server, thereby logically separating data from different applications being run on the same server.
Oracle database does not offer such a functionality. To separate data from other applications you need to put them into different schemas (where schema roughly corresponds to a user in Oracle database).
In practice, it is often desirable to use at least two distinct schemas in an application anyway: one to actually hold the database objects, and another to connect to the database and actually access the data. This setup provides better security, as only the minimal set of privileges can be granted to the user(s) through which the connection will be made.
However, this brings in one more complication: the database objects, which now reside in a different schema, must be referred to using fully qualified name. This difficulty can be resolved by issuing the following statement:
in the JDBC connection, where MY_SCHEMA
is the name of the schema containing the database objects. This command affects the entire session until it is closed, or a new schema is set using the same statement. If you're using a connection pool, it means that setting the schema again on a connection that was previously returned to a pool is useless. Some connection pools allow you to assign values to connections so that you could mark the connection when you first set the schema and avoid setting the schema unless actually required, saving some resources and database roundtrips.
(Please note that you cannot use bind variable to fill in the schema name, and therefore need to pay attention not to be subject of SQL injection attack when issuing this statement.)
Q: How to correctly use pagination queries in Oracle?
Pagination queries are used whenever the result of an SQL query has to be split into several parts, typically to be displayed over several pages.
The subject of pagination queries is a little bit complicated in Oracle. Oracle doesn't provide a LIMIT
clause, so the output of queries has to be limited to the desired set of rows using proper WHERE
clause. The widely known ROWNUM
pseudocolumn can be used for this, but it is not as straightforward as it might seem. For example, the following query (intended to get rows 6 to 10 as ordered by a NAME column) doesn't work:
This query will never return any row. The problem is that ROWNUM
numbers are assigned only to the rows that are selected by the WHERE
clause, always starting at 1. To get a row with number greater than 5, rows numbered 1 to 5 would have to be generated first, but these rows will never be generated, since the WHERE
condition doesn't ever select them.
It is possible to overcome this arcane limitation by computing the ROWNUM
in an inner (nested) query. This way, the row numbers assigned by to the ROWNUM
pseudocolumn while processing the query can be "freezed". Correct pagination queries for row with numbers 1-5, 6-10 and 11-15 will therefore look like this:
A slightly less convoluted is pagination using ROW_NUMBER
analytic function. Unlike the ROWNUM
, this function always assigns numbers, even if the rows are not selected by a WHERE
clause. Unfortunately, analytic functions cannot be directly used in WHERE
clauses, so an inner query is still required:
In real-world code, you'd use parameters instead of hard-coded numbers, of course (see PreparedStatement
). And don't ever forget to use an ORDER BY
in a pagination query -- paginating some data without specifying the order doesn't really make much sense.