blogger templates blogger widgets
This is part of a list of blog posts.
To browse the contents go to

Steps and Prerequisites for JDBC


All the examples that follows uses type-4 driver.

All examples are either web applications or standalone java applications.

Pre-requisites before starting JDBC

1. Install a DBMS software. I installed oracle 11g

2. Download jdbc driver. In our case it's orcale thin driver. I downloaded ojdbc6.jar

3. Create a sample table

First check if oracle service is running. If not run the "Start Database" command line program that is available if you had installed Oracle express edition on windows.
Click Start, point to Programs (or All Programs), point to Oracle Database 10g Express Edition, and then select Start Database.

Alternative: Find and run StartDB.bat file from oracle installtion location. For me it was under C:\oraclexe\app\oracle\product\11.2.0\server\bin

If you have ORACLE SQL command line configured, then open it and type in your username and password. If you hadn't created any new users then SYSTEM will be your username and password is what you had set during the installation.

SQL>conn SYSTEM
Enter password: *******

If you are unable to find the cmd line application the open the windows command prompt and type in
sqlplus

If you are wondering why a database creation is missing, it's because Oracle 11g installs a database by default. It's named XE. You could check this by running the following query in the command line.
SQL> select global_name from global_name;
GLOBAL_NAME
----------------------------------------------
XE

To display existing tables:
SQL> SELECT owner, table_name FROM dba_tables;
assuming that you have access to the DBA_TABLES data dictionary view. Else,
SQL>SELECT owner, table_name FROM all_tables;
displays tables your account have access to.
SQL>SELECT table_name FROM user_tables;
displays tables that you own.

I created a table as follows,
SQL> create table products (id INT NOT NULL, title VARCHAR2(25) NOT NULL);

SQL> desc products;
Name Null? Type
----------------------------------------- -------- ----------------------------
ID NOT NULL NUMBER(38)
TITLE NOT NULL VARCHAR2(25)

SQL> insert into products values(1, 'ipad');
1 row created.

SQL> insert into products values(2, 'galaxytab');
1 row created.

SQL> commit;

4. Note down the connection parameters

Take note of connection parameters which is later used in our application to make connections.

To connect to database we need to know 3 things
- know the driver class name (oracle.jdbc.OracleDriver)
- username/password for the oracle instance (SYSTEM/password)
- connection url
it takes of the form
protocol:hostname:port:database instance
In mycase, the
- driver is oracle thin driver and
- hostname is localhost
- port is the default port, 1521
- database instance is XE
So our final url is,
jdbc:oracle:thin:@localhost:1521:XE

Easiest way to find driver class name and connection url is to browse through the jdbc driver documentation - OracleDriver.


5. Simple web server/container.

I used tomcat 7.

If oracle service is already running, you might find difficulties starting tomcat because Oracle XE starts a web application within itself for administrative purposes.

Change the port for tomcat by double-clicking tomcat server in eclipse.


I changed port for HTTP from 8080 to 8081. You could change it to any port that's not in use.


Steps in making JDBC connections

Steps:

1. Load the driver

Two approaches are available.

Approach I

The most common approach to register a driver is to use Java's Class.forName() method to dynamically load the driver's class file into memory, which automatically registers it. This method is preferable because it allows you to make the driver registration configurable and portable.

try {
Class.forName("oracle.jdbc.OracleDriver");
}
catch(ClassNotFoundException ex) {
System.out.println("Error: unable to load driver class!");
System.exit(1);
}
You can use getInstance() method to work around noncompliant JVMs, but then you'll have to code for two extra Exceptions as follows:
try {
Class.forName("oracle.jdbc. OracleDriver").newInstance();
}
catch(ClassNotFoundException ex) {
System.out.println("Error: unable to load driver class!");
System.exit(1);
}catch(IllegalAccessException ex) {
System.out.println("Error: access problem while loading!");
System.exit(2);
}catch(InstantiationException ex) {
System.out.println("Error: unable to instantiate driver!");
System.exit(3);
}

Approach II:

The second approach you can use to register a driver is to use the static DriverManager.registerDriver() method.

You should use the registerDriver() method if you are using a non-JDK compliant JVM, such as the one provided by Microsoft.

The following example uses registerDriver() to register the Oracle driver:
try {
Driver myDriver = new oracle.jdbc.OracleDriver();
DriverManager.registerDriver( myDriver );
}
catch(ClassNotFoundException ex) {
System.out.println("Error: unable to load driver class!");
System.exit(1);
}

2. Get the connection using the driver manager

DriverManager.getConnection() methods:
• getConnection(String url)
• getConnection(String url, Properties prop)
• getConnection(String url, String user, String password)

Using a database URL:


getConnection(String url, String user, String password)

The most commonly used form of getConnection() requires you to pass a database URL, a username, and a password:
String URL = "jdbc:oracle:thin:@localhost:1521:XE";
String USER = "SYSTEM";
String PASS = "password"
Connection conn = DriverManager.getConnection(URL, USER, PASS);

getConnection(String url)
In this case, the database URL includes the username and password and has the following general form:
jdbc:oracle:driver:username/password@database
So the above connection can be created as follows:
String URL = "jdbc:oracle:thin:SYSTEM/password@localhost:1521:XE";
Connection conn = DriverManager.getConnection(URL);

getConnection(String url, Properties prop)

Using a database URL and a Properties object:

If you have more parameters to be passed for a getConnection call use a properties object. A Properties object holds a set of keyword-value pairs. It's used to pass driver properties to the driver during a call to the getConnection() method.
import java.util.*;

String URL = "jdbc:oracle:thin:@localhost:1521:XE";
Properties info = new Properties( );
info.put( "user", "SYSTEM" );
info.put( "password", "password" );

Connection conn = DriverManager.getConnection(URL, info);
Check out the driver documentation here
to know more about properties recognized by Oracle JDBC drivers.

Closing JDBC connections:

At the end of your JDBC program, it is required explicitly close all the connections to the database to end each database session. However, if you forget, Java's garbage collector will close the connection when it cleans up stale objects.
Relying on garbage collection, especially in database programming, is very poor programming practice. You should make a habit of always closing the connection with the close() method associated with connection object.
To ensure that a connection is closed, you could provide a finally block in your code. A finally block always executes, regardless if an exception occurs or not.
To close above opened connection you should call close() method as follows:
conn.close();

3. Executing SQL statements

JDBC API proves a interface called Statement that represents a SQL statement.
Statement stmt = conn.createStatement();
ResultSet objects are usually returned after executing SQL statements.

By default, only one ResultSet object per Statement object can be open at the same time. Therefore, if the reading of one ResultSet object is interleaved with the reading of another, each must have been generated by different Statement objects. All execution methods in the Statement interface implicitly close a statment's current ResultSet object if an open one exists.

There are three different kinds of statements:

Statement: Used to implement simple SQL statements with no parameters.
PreparedStatement: (Extends Statement.) Used for precompiling SQL statements that might contain input parameters.
CallableStatement: (Extends PreparedStatement.) Used to execute stored procedures that may contain both input and output parameters.

4. Executing Queries

execute:
If you dont know which method to be used for executing SQL statements, this method can be used.
This will return a boolean. TRUE indicates the result is a ResultSet and FALSE indicates it has the int value which denotes number of rows affected by the query.

stmt.execute("select * from products");
ResultSet rs = stmt.getResultSet();
executeQuery:
This is used generally for reading the content of the database.
The output will be in the form of ResultSet.
Generally SELECT statement is used.
ResultSet rs = stmt.executeQuery("select * from products");
executeUpdate:
This is generally used for altering the databases.
Generally DROP TABLE or DATABASE, INSERT into TABLE, UPDATE TABLE, DELETE from TABLE statements will be used in this.
The output will be in the form of int. This int value denotes the number of rows affected by the query.
int r = stmt.executeUpdate("truncate products");
if(r==0)
//unsuccessful
else
//successful

Continue reading: JDBC - simple standalone application

No comments:

Post a Comment