Software Development

Developing with OpenSource and Microsoft Technologies

Archive for the ‘Oracle’ Category

How to create an oracle table with Java

with one comment

I ‘ve created  a Connection Class which has methods  like getConnection, createStatement,  executeQuery, executeNonQuery, etc.

import java.sql.*;

public class OracleConnection
{

      private static Connection _conn = null;

    public static  Connection GetConnection ()
    {
        try{
      oracle.jdbc.pool.OracleDataSource _ds  = new oracle.jdbc.pool.OracleDataSource();
              _ds.setDriverType("thin");
	      _ds.setServerName("localhost");
	      _ds.setPortNumber(1521);
	      _ds.setDatabaseName("BDI");
	      _ds.setUser("SYSTEM");
	      _ds.setPassword("admin");

              Class.forName("oracle.jdbc.OracleDriver") ;

        _conn = _ds.getConnection();

    }catch(SQLException e)
    {
        System.err.println(e.getMessage());

    }
        catch(Exception e)
    {
        System.err.println(e.getMessage());

    }
        finally
        {
             return _conn;
        }

    }

    public static  Statement CreateStatement(Connection conn)
    {
         Statement _command= null;
        try
        {
        _command = conn.createStatement();
        }catch(SQLException e)
        {
        System.err.println(e.getMessage());
        }

        return _command;
    }

    public static  ResultSet ExecuteQuery(Statement command,String query)
    {

        ResultSet _table= null;
        try
        {
        _table= command.executeQuery(query);
        command.close();
        }catch(SQLException e)
        {
        System.err.println(e.getMessage());
        }

        return  _table;
    }

    public static  void ExecuteNonQuery(Statement command,String query)
    {
        try
        {
            command.executeUpdate(query);
            command.close();
        }
        catch(SQLException e)
        {
            System.err.println(e.getMessage());
        }
    }

}

I will use this class to create a table in the main class passing the query through a parameter.

import java.sql.*;

public class CreateDatabase {

   private static  Connection _conn= null;
   private static  Statement _command = null;

   public static void main(String[] args) throws SQLException {

       try{
     _conn= OracleConnection.GetConnection();
     _command= OracleConnection.CreateStatement(_conn);
     String query ="CREATE TABLE DEPARTMENT (" +
             " ID INTEGER PRIMARY KEY," +
             " NAME VARCHAR(20) NOT NULL,"+
             " DESCRIPTION VARCHAR(100))";

    OracleConnection.ExecuteNonQuery(_command, query);
     _conn.close();
       }
       catch(SQLException e)
       {
       System.err.println(e.getMessage());
       }

   }
}

Result:

run:

BUILD SUCCESSFUL (total time: 1 second)

JavaOracle08

Advertisements

Written by mredison

July 19, 2009 at 10:46 pm

Posted in Java, Oracle

How to connect a Java’s application with Oracle 10g

with one comment

Hi, this code will help you to connect a Java’s application with Oracle 10g Release 2.

I’m using Eclipse or NetBeans as IDE for Java programming.

  • First of all, you need to download the Oracle’s driver for Java Technology, you can download it in this URL:

http://www.oracle.com/technology/software/tech/java/sqlj_jdbc/htdocs/jdbc_10201.html

*Oracle Database 10g Release 2 (10.2.0.4) JDBC Drivers

  • Then you need to put the .jar into your oracle’s directory installation like : C:\oracle\product\10.2.0\db_1\jdbc\bin

NetBeans

  • Create a new project “Java Application”

JavaOracle01

We need to add a library to our project, so right click to the project’s properties and select Libraries, then add Library.

JavaOracle02

Then click in Create button, and then write a Library Name.

JavaOracle03

After that we need to add a jar, so we search it at C:\oracle\product\10.2.0\db_1\jdbc\bin and add it.

JavaOracle04

Coding:

1.- Import java.sql.*; library.

import java.sql.*;

/**
 * @MrEdison https://opensw.wordpress.com
 */
public class Main {

    public static void main(String[] args) throws SQLException {
    }
}
 

2.-  Declare a connection variable and create a try /catch statement for the connection.

public static void main(String[] args) throws SQLException {

        Connection _conn = null;
        try{

        }catch(Exception ex)
        {

        }
        finally
        {

        }
    }

3.-  Inside the try statement you need to use the DataSource Class to connect the application, create a OracleDataSource object and pass some parameters as the DriverType, ServerName, PortNumber, DatabaseName, User and Password.

try{
             oracle.jdbc.pool.OracleDataSource _ds =
             new oracle.jdbc.pool.OracleDataSource();

       _ds.setDriverType("thin");
       _ds.setServerName("localhost");
       _ds.setPortNumber(1521);
       _ds.setDatabaseName("BDI");
       _ds.setUser("SYSTEM");
       _ds.setPassword("admin"); 

        }

4.-  Load the Oracle JBDC Driver to the Class and get the connection.

Class.forName("oracle.jdbc.OracleDriver") ;
System.out.println("OracleJDBC driver has been loaded");

_conn = _ds.getConnection();

System.out.println("!Connection successful!");
       

5.- Create a statement and execute the query into a ResultSet, then you will show the results into a while  and for cycles .

Statement _command = _conn.createStatement();
String _query="SELECT * FROM Employee";
ResultSet _table =  _command.executeQuery(_query);

  while (_table.next()) {
   int _columnsNumber = _table.getMetaData().getColumnCount();
      for ( int i = 1 ; i <= _columnsNumber ; i++ )
      {
        System.out.print(  _table.getObject(i) + " ");
      }
    System.out.println("");
    System.out.println();
 }

6.-  Close the connection.


         _ds.close(); 

     } catch (Exception e) {
       System.err.println("Exception: "+e.getMessage());
     }

7.-  If you had run this , you might have had this error:

run:
OracleJDBC driver has been loaded
!Connection successful!
Exception: ORA-00942: table or view does not exist

BUILD SUCCESSFUL (total time: 1 second)

8.- We need to create the table in the Oracle Database. Run SQL Plus.

JavaOracle05

JavaOracle06

9.- Result

JavaOracle07

Complete Code:

import java.sql.*;

/**
 * @MrEdison https://opensw.wordpress.com
 */
public class Main {

    public static void main(String[] args) throws SQLException {

        Connection _conn = null;
        try{
             oracle.jdbc.pool.OracleDataSource _ds =
             new oracle.jdbc.pool.OracleDataSource();

	      _ds.setDriverType("thin");
	      _ds.setServerName("localhost");
	      _ds.setPortNumber(1521);
	      _ds.setDatabaseName("BDI");
	      _ds.setUser("SYSTEM");
	      _ds.setPassword("admin");

Class.forName("oracle.jdbc.OracleDriver") ;
System.out.println("OracleJDBC driver has been loaded");

_conn = _ds.getConnection();

System.out.println("!Connection successful!");

	    Statement _command = _conn.createStatement();
            String _query="SELECT * FROM Employee";
            ResultSet _table =  _command.executeQuery(_query);

	 while (_table.next()) {
	  int _columnsNumber = _table.getMetaData().getColumnCount();
	    	for ( int i = 1 ; i &lt;= _columnsNumber ; i++ )
						{
	    	  System.out.print(  _table.getObject(i) + " ");
						}
	   System.out.println("");
	   System.out.println();
	}

	        _ds.close();

	    } catch (Exception e) {
	      System.err.println("Exception: "+e.getMessage());
	    }

    }

}

Written by mredison

July 19, 2009 at 5:35 pm

Posted in Java, Oracle