Install jdbc driver linux

Ubuntu Documentation

For Ubuntu versions : 5.10, 6.06, 6.10, 7.04, 9.10

This document explains how to set up Java applications to communicate with the MySQL Database. This can be used either in Java development, or applications which use JDBC such as OpenOffice.Org. See Using MySQL, JDBC and OpenOffice for more information.

This document was written originally for 5.10, but has been updated for 6.06. Changes required for 5.10 are shown in italics

It has also been checked against 6.10 (Edgy Eft) and 7.04 (Feisty Fawn), which should be installed as for 6.06

Installation

Install mysql client, server and the jdbc connector, either via synaptic or by using the following

On 5.10 you may need select «No configuration» for postfix if it is installed.

Set up MySQL default password

Set up the password for the root user as ‘root’ or whatever you want. The last entry is the password

Check you can connect to mysql

then enter the password you just set and press return again. In this case it would be «root».

Create SQL Databases and tables

Create databases and tables or whatever, so you have some data to work with. You can disconnect and use mysql-query-browser for this if you wish. For the sample code below just enter

User creation and privileges

Create a user with access to that table. Replace the items in square brackets by the database name, and the chosen user and password. Don’t type in the square brackets !

Note: Ubuntu 5.10 requires localhost.localdomain

This is required because when you connect using «mysql -u root -p» it connects directly to the Sql Server. The «grant» line creates access via 127.0.0.1 (localhost). This is no longer an issue on 6.10.

You can test this by

This will work only for the user you have just «granted», not for root. Counter-intuitively, the ‘default’ server and localhost/127.0.0.1 aren’t the same thing

Eclipse

For those who are using Eclipse, you will likely to have a ‘Class Not Found’ exception. To fix this, go to: Project, click Properties, select Java Build Path, and choose the Libraries tab. Then select ‘Add External JARs’, and find ‘/usr/share/java/mysql-connector-java.jar’.

Setting up the user to use JDBC

Add these two lines to /home/[user]/.bashrc. I am running Java 5 which doesn’t require the current directory to be on the Classpath ; I *think* Java 2 does, but I’m not going back to check it In that case you may need to have $CLASSPATH. /user/share/java/mysql.jar

Alternatively, you can set it for all users, by editing /etc/environment (use sudo — sudo vi /etc/environment)

Log out and Log in again. (If you only edited /home/[user]/.bashrc you don’t need to log out/in, only execute in a terminal «$source .bashrc» in your home dir). Start up a terminal and type:

It should print out something like «:/usr/share/java/mysql.jar»

Testing in Java

It should now work. Here is some typical code (clearing up removed for simplicity)

JDBCAndMySQL (последним исправлял пользователь chengzhe-wu 2010-04-20 13:28:19)

The material on this wiki is available under a free license, see Copyright / License for details
You can contribute to this wiki, see Wiki Guide for details

Источник

2 Getting Started

This chapter discusses the compatibility of Oracle Java Database Connectivity (JDBC) driver versions, database versions, and Java Development Kit (JDK) versions. It also describes the basics of testing a client installation and configuration and running a simple application. This chapter contains the following sections:

Version Compatibility for Oracle JDBC Drivers

This section discusses the general JDBC version compatibility issues.

Oracle Database 12 c Release 1 (12.1) JDBC drivers are certified with supported Oracle Database releases (11.x.0.x). However, they are not certified to work with older, unsupported database releases, such as 10.2.x, 10.1.x, 9.2.x, and 9.0.1.x.

Existing and supported JDBC drivers are certified to work with Oracle Database 12 c Release 1 (12.1).

In Oracle Database 12 c Release 1 (12.1), Oracle JDBC drivers no longer support JDK 1.4.x or earlier versions.

Verifying a JDBC Client Installation

To verify a JDBC client installation, you must do all of the following:

This section describes the steps for verifying an Oracle client installation of the JDBC drivers, assuming that you have already installed the driver of your choice. Installation of an Oracle JDBC driver is platform-specific. You must follow the installation instructions for the driver you want to install in your platform-specific documentation.

If you use the JDBC Thin driver, then there is no additional installation on the client computer. If you use the JDBC Oracle Call Interface (OCI) driver, then you must also install the Oracle client software. This includes Oracle Net and the OCI libraries.

The JDBC Thin driver requires a TCP/IP listener to be running on the computer where the database is installed.

Check the Installed Directories and Files

Installing the Oracle Java products creates, among other things, the following directories:

Check whether or not the following directories and files have been created and populated in the ORACLE_HOME /jdbc directory:

This directory contains a compressed file, demo.zip or demo.tar . When you uncompress this compressed file, the samples directory and the Samples-Readme.txt file are created. The samples directory contains sample programs, including examples of how to use JDBC escape syntax and Oracle SQL syntax, PL/SQL blocks, streams, user-defined types, additional Oracle type extensions, and Oracle performance extensions.

This directory contains the javadoc.zip file, which is the Oracle JDBC application programming interface (API) documentation.

The lib directory contains the following required Java classes:

orai18n.jar and orai18n-mapping.jar

Contain classes for globalization and multibyte character sets support

ojdbc6.jar , ojdbc6_g.jar , ojdbc7.jar , and ojdbc7_g.jar

Contain the JDBC driver classes for use with JDK 6 and JDK 7

Since Oracle Database 11 g Release 1, support for a version of JDK earlier than version 5.0 has been removed. Also, the ojdbc14.jar , ojdbc5.jar and classes12.jar files are no longer shipped. Instead, you can use the ojdbc6.jar and ojdbc7.jar files, which are shipped with Oracle Database 12 c .

If you are using JSE 6 and later, then there is no need to explicitly load the JDBC driver. This means that the Java run-time loads the driver when needed and you need not include Class.forName(«oracle.jdbc.OracleDriver») or new oracle.jdbc.OracleDriver() in your code. But if you are using J2SE 5.0, then you need to load the JDBC driver explicitly.

This file contains late-breaking and release-specific information about the drivers, which may not have been included in other documentation on the product.

Check whether or not the following directories have been created and populated in the ORACLE_HOME /jlib directory:

jta.jar and jndi.jar

These files contain classes for the Java Transaction API (JTA) and the Java Naming and Directory Interface (JNDI). These are required only if you are using JTA features for distributed transaction management or JNDI features for naming services.

For more information about these files, visit the following sites

This JAR file contains classes for Oracle RAC Fast Application Notification. It is also required for Universal Connection Pool (UCP) features like Fast Connection Failover, Run-time Load Balancing, Web Session Affinity, and Transaction Affinity.

Check the Environment Variables

This section describes the environment variables that must be set for the JDBC OCI driver and the JDBC Thin driver, focusing on Solaris, Linux, and Microsoft Windows platforms.

You must set the CLASSPATH environment variable for JDBC OCI or Thin driver. Include the following in the CLASSPATH environment variable:

If you use the JTA features and the JNDI features, then you must specify jta.jar and jndi.jar in your CLASSPATH environment variable.

JDBC OCI Driver

To use the JDBC OCI driver, you must also set the following value for the library path environment variable:

On Solaris or Linux, set the LD_LIBRARY_PATH environment variable as follows:

This directory contains the libocijdbc11.so shared object library.

On Microsoft Windows, set the PATH environment variable as follows:

This directory contains the ocijdbc11.dll dynamic link library.

All of the JDBC OCI demonstration programs can be run in the Instant Client mode by including the JDBC OCI Instant Client data shared library on the library path environment variable.

JDBC Thin Driver

To use the JDBC Thin driver, you do not have to set any other environment variables. However, to use the JDBC server-side Thin driver, you need to set permission.

Setting Permission for the Server-Side Thin Driver

The JDBC server-side Thin driver opens a socket for its connection to the database. Because Oracle Database enforces the Java security model, a check is performed for a SocketPermission object.

To use the JDBC server-side Thin driver, the connecting user must be granted the appropriate permission. The following is an example of how the permission can be granted for the user HR :

Note that JDBCTHIN in the grant_permission call must be in uppercase. The asterisk ( * ) is a pattern. You can restrict the user by granting permission to connect to only specific computers or ports.

Ensure that the Java Code Can Be Compiled and Run

To further ensure that Java is set up properly on your client system, go to the samples directory under the ORACLE_HOME /jdbc/demo directory. Now, type the following commands on the command line, one after the other, to see if the Java compiler and the Java interpreter run without error:

Each of the preceding commands should display a list of options and parameters and then exit. Ideally, verify that you can compile and run a simple test program, such as jdbc/demo/samples/generic/SelectExample .

Determine the Version of the JDBC Driver

To determine the version of the JDBC driver, call the getDriverVersion method of the OracleDatabaseMetaData class as shown in the following sample code:

You can also determine the version of the JDBC driver by executing the following commands:

java -jar ojdbc6.jar

java -jar ojdbc7.jar

Test JDBC and the Database Connection

The samples directory contains sample programs for a particular Oracle JDBC driver. One of the programs, JdbcCheckup.java , is designed to test JDBC and the database connection. The program queries for the user name, password, and the name of the database to which you want to connect. The program connects to the database, queries for the string » Hello World «, and prints it to the screen.

Go to the samples directory, and compile and run the JdbcCheckup.java program. If the results of the query print without error, then your Java and JDBC installations are correct.

Although JdbcCheckup.java is a simple program, it demonstrates several important functions by performing the following:

Imports the necessary Java classes, including JDBC classes

Creates a DataSource instance

Connects to the database

Runs a simple query

Prints the query results to your screen

The JdbcCheckup.java program, which uses the JDBC OCI driver, is as follows:

Basic Steps in JDBC

After verifying the JDBC client installation, you can start creating your JDBC applications. When using Oracle JDBC drivers, you must include certain driver-specific information in your programs. This section describes, in the form of a tutorial, where and how to add the information. The tutorial guides you through the steps to create code that connects to and queries a database from the client.

You must write code to perform the following tasks:

You must supply Oracle driver-specific information for the first three tasks that enable your program to use the JDBC application programming interface (API) to access a database. For the other tasks, you can use standard JDBC Java code, as you would for any Java application.

Importing Packages

Regardless of which Oracle JDBC driver you use, include the import statements shown in Table 2-1 at the beginning of your program.

Table 2-1 Import Statements for JDBC Driver

Standard JDBC packages.

The BigDecimal and BigInteger classes. You can omit this package if you are not going to use these classes in your application.

Oracle extensions to JDBC. This is optional.

Oracle type extensions. This is optional.

The Oracle packages listed as optional provide access to the extended functionality provided by Oracle JDBC drivers, but are not required for the example presented in this section.

It is better to import only the classes your application needs, rather than using the wildcard asterisk ( * ). This guide uses the asterisk (*) for simplicity, but this is not the recommended way of importing classes and interfaces.

O pening a Connection to a Database

First, you must create an OracleDataSource instance. Then, open a connection to the database using the OracleDataSource.getConnection method. The properties of the retrieved connection are derived from the OracleDataSource instance. If you set the URL connection property, then all other properties, including TNSEntryName , DatabaseName , ServiceName , ServerName , PortNumber , Network Protocol , and driver type are ignored.

Specifying a Database URL, User Name, and Password

The following code sets the URL, user name, and password for a data source:

The following example connects user HR with password hr to a database with service orcl through port 5221 of the host myhost , using the JDBC Thin driver:

The user name and password specified in the arguments override any user name and password specified in the URL.

Specifying a Database URL that Includes User Name and Password

The following example connects user HR with password hr to a database host whose Transparent Network Substrate (TNS) entry is myTNSEntry , using the JDBC Oracle Call Interface (OCI) driver. In this case, the URL includes the user name and password and is the only input parameter.

If you want to connect using the Thin driver, then you must specify the port number. For example, if you want to connect to the database on the host myhost that has a TCP/IP listener on port 5221 and the service identifier is orcl , then provide the following code:

Creating a Statement Object

Once you connect to the database and, in the process, create a Connection object, the next step is to create a Statement object. The createStatement method of the JDBC Connection object returns an object of the JDBC Statement type. To continue the example from the previous section, where the Connection object conn was created, here is an example of how to create the Statement object:

Running a Query and Retrieving a Result Set Object

To query the database, use the executeQuery method of the Statement object. This method takes a SQL statement as input and returns a JDBC ResultSet object.

The method used to execute a Statement object depends on the type of SQL statement being executed. If the Statement object represents a SQL query returning a ResultSet object, the executeQuery method should be used. If the SQL is known to be a DDL statement or a DML statement returning an update count, the executeUpdate method should be used. If the type of the SQL statement is not known, the execute method should be used.

In case of a standard JDBC driver, if the SQL string being executed does not return a ResultSet object, then the executeQuery method throws a SQLException exception. In case of an Oracle JDBC driver, the executeQuery method does not throw a SQLException exception even if the SQL string being executed does not return a ResultSet object.

To continue the example, once you create the Statement object stmt , the next step is to run a query that returns a ResultSet object with the contents of the first_name column of a table of employees named EMPLOYEES :

Processing the Result Set Object

Once you run your query, use the next() method of the ResultSet object to iterate through the results. This method steps through the result set row by row, detecting the end of the result set when it is reached.

To pull data out of the result set as you iterate through it, use the appropriate get XXX methods of the ResultSet object, where XXX corresponds to a Java data type.

For example, the following code will iterate through the ResultSet object, rset , from the previous section and will retrieve and print each employee name:

The next() method returns false when it reaches the end of the result set. The employee names are materialized as Java String values.

Closing the Result Set and Statement Objects

You must explicitly close the ResultSet and Statement objects after you finish using them. This applies to all ResultSet and Statement objects you create when using Oracle JDBC drivers. The drivers do not have finalizer methods. The cleanup routines are performed by the close method of the ResultSet and Statement classes. If you do not explicitly close the ResultSet and Statement objects, serious memory leaks could occur. You could also run out of cursors in the database. Closing both the result set and the statement releases the corresponding cursor in the database. If you close only the result set, then the cursor is not released.

For example, if your ResultSet object is rset and your Statement object is stmt , then close the result set and statement with the following lines of code:

When you close a Statement object that a given Connection object creates, the connection itself remains open.

Typically, you should put close statements in a finally clause.

Making Changes to the Database

To perform DML (Data Manipulation Language) operations, such as INSERT or UPDATE operations, you can create either a Statement object or a PreparedStatement object. PreparedStatement objects enable you to run a statement with varying sets of input parameters. The prepareStatement method of the JDBC Connection object lets you define a statement that takes variable bind parameters and returns a JDBC PreparedStatement object with your statement definition.

Use the set XXX methods on the PreparedStatement object to bind data to the prepared statement to be sent to the database.

The following example shows how to use a prepared statement to run INSERT operations that add two rows to the EMPLOYEES table.

To perform data definition language (DDL) operations, you must create a Statement object. The following example shows how to create a table in the database:

You can also use a PreparedStatement object to perform DDL operations. However, you should not use a PreparedStatement object because the useful part of such an object is that it can have parameters and a DDL operation does not have any parameters.

Also, due to a Database limitation, if you use a PreparedStatement object for a DDL operation, then it only works for the first time it is executed. So, you should use only Statement objects for DDL operations.

The following example shows how to prepare your DDL statements before any reexecution:

Com mitting Changes

By default, data manipulation language (DML) operations are committed automatically as soon as they are run. This is known as the auto-commit mode. If auto-commit mode is on and you perform a COMMIT or ROLLBACK operation using the commit or rollback method on a connection object, then you get the following error messages:

Table 2-2 Error Messages for Operations Performed When Auto-Commit Mode is ON

Import statement	Provides

Could not commit with auto-commit set on

Could not rollback with auto-commit set on

If a SQLException is raised during a COMMIT or ROLLBACK operation with the error messages as mentioned in Table 2-2, then check the auto-commit status of the connection because you get an exception when these operations are performed on a connection that has auto-commit value set to true .

This exception is raised for any one of the following cases:

When auto-commit status is set to true and commit or rollback method is called

When the default status of auto-commit is not changed and commit or rollback method is called

When the value of the COMMIT_ON_ACCEPT_CHANGES property is true and commit or rollback method is called after calling the acceptChanges method on a rowset

However, you can disable auto-commit mode with the following method call on the Connection object:

If you disable the auto-commit mode, then you must manually commit or roll back changes with the appropriate method call on the Connection object:

A COMMIT or ROLLBACK operation affects all DML statements run since the last COMMIT or ROLLBACK .

If the auto-commit mode is disabled and you close the connection without explicitly committing or rolling back your last changes, then an implicit COMMIT operation is run.

Any data definition language (DDL) operation always causes an implicit COMMIT . If the auto-commit mode is disabled, then this implicit COMMIT will commit any pending DML operations that had not yet been explicitly committed or rolled back.

Changing Commit Behavior

When a transaction updates the database, it generates a redo entry corresponding to this update. Oracle Database buffers this redo in memory until the completion of the transaction. When you commit the transaction, the Log Writer (LGWR) process writes the redo entry for the commit to disk, along with the accumulated redo entries of all changes in the transaction. By default, Oracle Database writes the redo to disk before the call returns to the client. This behavior introduces latency in the commit because the application must wait for the redo entry to be persisted on disk.

If your application requires very high transaction throughput and you are willing to trade commit durability for lower commit latency, then you can change the behavior of the default COMMIT operation, depending on the needs of your application. You can change the behavior of the COMMIT operation with the following options:

These options let you control two different aspects of the commit phase:

Whether the COMMIT call should wait for the server to process it or not. This is achieved by using the WAIT or NOWAIT option.

Whether the Log Writer should batch the call or not. This is achieved by using the WRITEIMMED or WRITEBATCH option.

You can also combine different options together. For example, if you want the COMMIT call to return without waiting for the server to process it and also the log writer to process the commits in batch, then you can use the NOWAIT and WRITEBATCH options together. For example:

you cannot use the WAIT and NOWAIT options together because they have opposite meanings. If you do so, then the JDBC driver will throw an exception. The same applies to the WRITEIMMED and WRITEBATCH options.

Working with Invisible Columns

Starting from this release, Oracle Database supports invisible columns. Using this feature, you can add a column to the table in hidden mode and make it visible later. JDBC provides APIs to retrieve information about invisible columns. To get information about whether a column is invisible or not, you can use the isColumnInvisible method available in the oracle.jdbc.OracleResultSetMetaData interface in the following way:

Alternatively, you can also use the getColumns method available in the oracle.jdbc.OracleDatabaseMetaData class to retrieve information about invisible columns.

The server-side internal driver, kprb does not support fetching information about invisible columns.

Closing the Connection

You must close the connection to the database after you have performed all the required operations and no longer require the connection. You can close the connection by using the close method of the Connection object, as follows:

Typically, you should put close statements in a finally clause.

Sample: Connecting, Querying, and Processing the Results

The steps in the preceding sections are illustrated in the following example, which uses the Oracle JDBC Thin driver to create a data source, connects to the database, creates a Statement object, runs a query, and processes the result set.

Note that the code for creating the Statement object, running the query, returning and processing the ResultSet object, and closing the statement and connection uses the standard JDBC API.

If you want to adapt the code for the OCI driver, then replace the call to the OracleDataSource.setURL method with the following:

where, MyHostString is an entry in the TNSNAMES.ORA file.

Stored Procedure Calls in JDBC Programs

This section describes how Oracle JDBC drivers support the following kinds of stored procedures:

PL/SQL Stored Procedures

JDBC supports the invocation of PL/SQL procedures/functions and anonymous blocks, using either JDBC escape syntax or PL/SQL block syntax. The following PL/SQL calls would work with any Oracle JDBC driver:

As an example of using the Oracle syntax, here is a PL/SQL code snippet that creates a stored function. The PL/SQL function gets a character sequence and concatenates a suffix to it:

The function invocation in your JDBC program should look like the following:

Java Stored Procedures

You can use JDBC to call Java stored procedures through the SQL interface. The syntax for calling Java stored procedures is the same as the syntax for calling PL/SQL stored procedures, presuming they have been properly published. That is, you have written call specifications to publish them to the Oracle data dictionary. Applications can call Java stored procedures using the Native Java Interface for direct invocation of static Java methods.

Support for Implicit Results

Starting from this release, Oracle Database supports results of SQL statements executed in a stored procedure to be returned implicitly to the client applications without the need to explicitly use a REF CURSOR . You can use the following methods to retrieve and process the implicit results returned by PL/SQL procedures or blocks:

Operation	Error Messages

Method	Description
getMoreResults	Checks if there are more results available in the result set
getMoreResults(int)	Checks if there are more results available in the result set, like the overloaded method. This method accepts an int parameter that can have one of the following values:
getResultSet	Iteratively retrieves each implicit result from an executed PL/SQL statement

The server-side internal driver, kprb does not support fetching information about implicit results.

Only SELECT queries can be returned implicitly.

Applications retrieve each result set sequentially, but can fetch rows from any result set independent of the sequence.

Suppose you have a procedure called foo as the following:

The following code snippet demonstrates how to retrieve the implicit results returned by PL/SQL procedures using the getMoreResults methods:

Suppose you have another procedure called foo as the following:

The following code snippet demonstrates how to retrieve the implicit results returned by PL/SQL procedures using the getMoreResults(int) methods:

Processing SQL Exceptions

To handle error conditions, Oracle JDBC drivers throw SQL exceptions, producing instances of the java.sql.SQLException class or its subclass. Errors can originate either in the JDBC driver or in the database itself. Resulting messages describe the error and identify the method that threw the error. Additional run-time information can also be appended.

JDBC 3.0 defines only a single exception, SQLException . However, there are large categories of errors and it is useful to distinguish them. Therefore, in JDBC 4.0, a set of subclasses of the SQLException exception is introduced to identify the different categories of errors. To know more about this feature, see Support for JDBC 4.0 Standard.

Basic exception handling can include retrieving the error message, retrieving the error code, retrieving the SQL state, and printing the stack trace. The SQLException class includes functionality to retrieve all of this information, when available.

Retrieving Error Information

You can retrieve basic error information with the following methods of the SQLException class:

The following example prints output from a getMessage method call:

This would print the output, such as the following, for an error originating in the JDBC driver:

Error message text is available in alternative languages and character sets supported by Oracle.

Printing the Stack Trace

The SQLException class provides the printStackTrace() method for printing a stack trace. This method prints the stack trace of the Throwable object to the standard error stream. You can also specify a java.io.PrintStream object or java.io.PrintWriter object for output.

The following code fragment illustrates how you can catch SQL exceptions and print the stack trace.

To illustrate how the JDBC drivers handle errors, assume the following code uses an incorrect column index:

Assuming the column index is incorrect, running the program would produce the following error text:

Источник