The previous article addressed the Oracle database creation. This article will handle the construction of a Java class for accessing the database.
We will be using NetBeans IDE 7.0.1, which may be downloaded from http://netbeans.org/community/releases/70/.
After installing NetBeans IDE 7.0.1, we must create a new "Java Class Library" project, which we will name HelperDB, because it will consist only of a single class for helping us access the database.
We also create a Java class, that we named DBAccessObj, in file DBAccessObj.java.
It is needed to include two or three (depending on the ORACLE version) JAR library classes to access the Oracle database by using a JDBC driver:
These jar files may be found in directory
oraclexe\app\oracle\product\10.2.0\server\jdbc\lib
being oraclexe the directory where Oracle XE has been installed.
Let's now create a package, which we name HelperDB, that will contain our class, and let's import package java.sql, which contains classes for connecting and sending SQL statements to the database:
package HelperDB;
import java.sql.*;
public class DBAccessObj{
private Connection conn;
public DBAccessObj()
{
}
}
As the only instance variable in our class, we shall create conn of "type" java.sql.Connection.
Actually, Connection is a Java interface, and so conn may contain an object of any type (class) that implements that interface. So, conn may contain an instance of any type of connection. The type will be given by the DriverManager according to the driver that is being used.
The class constructor will then be modified to create a database connection:
public DBAccessObj()
{
this.createDBConnection();
}
private void createDBConnection()
{
try {
Class.forName("oracle.jdbc.driver.OracleDriver");
}
catch(ClassNotFoundException e) {
System.out.println("Oops! Can't find class oracle.jdbc.driver.OracleDriver");
System.exit(1);
}
try{
conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:XE", "libraryuser","libraryuser");
}
catch(Exception e){
System.out.println("ERRO " + e.getMessage());
}
}
The driver we will be using is "oracle.jdbc.driver.OracleDriver" and the database connection string is:
"jdbc:oracle:thin:@localhost:1521:XE"
being "libraryuser","libraryuser", respectively the user and password of the user we want to use in the database access (refer to the previous article).
At this point, if we write a small test class (TestClass.java) to create an instance of our class, it will be possible to see if the connection is correctly made:
import HelperDB.DBAccessObj;
public class TestClass {
public static void main(String[] args) {
DBAccessObj conexao = new DBAccessObj();
}
}
If the connection isn't made, one of the error messages that we put in the catch part of the above try/catchs will appear.
After creating the database connection we want to send SQL statements to the database, to insert data in a table (INSERT), modify data (UPDATE), delete data (DELETE) or consult data (SELECT).
For that purpose, we need an instance of a class that implements interface java.sql.Statement:
public Statement createStatement()
{
Statement st=null;
if (this.conn==null)
this.createDBConnection();
try{
st = this.conn.createStatement();
}
catch(Exception e){
System.out.println("ERRO " + e.getMessage());
}
return st;
}
To execute an SQL command using the created Statement instance, we'll create another couple of methods in our class:
- To execute an SQL command that doesn't yield a result (INSERT, UPDATE or DELETE):
public void executeSQL(String SqlComm)
{
Statement st = this.createStatement();
try{
st.execute(SqlComm);
}
catch(Exception e){
System.out.println("ERRO " + e.getMessage());
}
}
- To execute an SQL command that yields a result (SELECT):
public ResultSet executeQuery(String SqlComm)
{
Statement st = this.createStatement();
ResultSet rs=null;
try{
rs = st.executeQuery(SqlComm);
}
catch(Exception e){
System.out.println("ERRO " + e.getMessage());
}
return rs;
}
Finally, let's write methods for opening and closing a database connection:
public Connection openConnection()
{
if(this.conn==null)
this.createDBConnection();
return this.conn;
}
public void closeConnection()
{
if(this.conn!=null)
{
try{
this.conn.close();
}
catch(Exception e){
System.out.println("ERRO " + e.getMessage());
}
}
}
This ends our class. To test it, we may now modify the test class:
import HelperDB.DBAccessObj;
import java.sql.*;
public class TestClass {
public static void main(String[] args) {
DBAccessObj conexao = new DBAccessObj();
conexao.executeSQL("Insert into Author columns (Name, SURNAME) VALUES ('XICO', 'MENDES')");
ResultSet rs = conexao.executeQuery("SELECT * FROM AUTHOR");
try{
while(rs.next()){
String aName = ((String)rs.getString("Name"));
String aSurname = ((String)rs.getString("Surname"));
//
System.out.println(aName + " " + aSurname);
}
}
catch(Exception e){
System.out.println("ERRO " + e.getMessage());
}
}
}
If everything is OK, the result of our test will be the insertion of author named XICO MENDES in table AUTHOR and the ulterior listing of the contents of the same table.
Methods for opening and closing a connection have not been tested, but we'll see their utility in a forthcoming article.
Other related articles:
- Example of Java Swing Application with Oracle Database - 1
- Example of Java Swing Application with Oracle Database - 3
- Example of Java Swing Application with Oracle Database – 4
No comments:
Post a Comment