Orindasoft

JDBCWizard Extension for Oracle JDeveloper 11g

    Topics

  1. What is JDBCWizard
  2. Installing and Upgrading
  3. Start JDBCWizard
  4. Using the JDBCWizard GUI
  5. Working with Generated Code
  6. Further Information

What is JDBCWizard

Installing and Upgrading

Installing

The extension can be obtained from the following locations:

  1. From Oracle's Update Center -

    http://www.oracle.com/technology/products/jdev/101/update/exchange.xml

    To use this update center select "Help/Check for updates" and select "Open Source and Partner's Extensions". Select "JDBCWizard" from the list of available extensions and follow the instructions on the screen.

  2. From our Update Center -

    /public/jdev11gCenter.xml

    To use this update center select "Help/Check for updates" and select "Orinda Software Update Center". If the Orinda update Center is not on your list you can add it by clicking on the "Add" button and entering "OrindaSoft" in the Name field and " /public/jdev11gCenter.xml" Select "JDBCWizard" from the list of available extensions and follow the instructions on the screen.

  3. By downloading the stand alone version of the JDBCWizard demo from here.

    4. You will have to restart JDeveloper in order for the extension to be loaded. You can check that the extension has been installed by selecting "Help/About/Extensions". JDBCWizard should appear in the list of loaded extensions.

    Upgrading the Demo

    Your demo copy will expire roughly one month after you download it. You can then download the demo again. This does not happen if you buy a full copy. You can find out the expiration date of your demo by looking at the "JDBCWizard Configuration" section in the "Tools/Preferences" menu.

    To upgrade your demo use one of the download sites mentioned above.

    Starting JDBCWizard

    There are two ways to use JDBCWizard

    Creating JDBCWizard configuration files in the Resources area of your JDeveloper Project.

    To do this select "File/New" and add an JDBCWizard configuration file. When you open this file the JDBCWizard GUI will open with default values for the connection information. Provide appropriate values and select "Connect" to login to Oracle and enagle the rest of the GUI.

    Creating JDBCWizard configuration files that are associated with Oracle Connections

    To do this go to the Connections Navigator and open an Oracle database connection. An JDBCWizard Folder will appear beneath the schema. A default configuration file is automaticly created for you. You can create additional JDBCWizard files by right clicking on the JDBCWizard Folder. To start the JDBCWizard GUI double click on a configuration file.

    Using the JDBCWizard GUI

    The GUI Consists of 4 tabs and a "Generate" button. The tabs are used to select objects to generate Java for and to define what features the generated code will have.

    The "Login" Tab

    This tab has the following fields:

    Field
    		 Requires
    TCP/IP Hostname
    		 The hostname or IP address of the database server.
    Oracle SID
    		 The Oracle instance identifier.
    SQL*NET Port
    		 The SQL*NET port used by the Oracle Listener, usually 1521
    Oracle User
    		 Your Oracle username.
    Oracle Password
    		 Your Oracle username's password.
    When you have filled in the fields select "Connect... to connect to the database.


    Note: This tab does not always appear when using configuration files associated with database connections.


    The "Select Objects" Tab

    The 'Select Objects' page allows the user to select database objects and SQL files to generate code against.



    Use "Objects Belonging To..." To find objects owned by other people.

    JDBCWizard generates code for your own objects and for those owned by other users, provided they have given you access rights.
    1. Choose from the following options to select the required database objects:
      • The Current User check box allows you to retrieve the objects owned by the current username.
      • The User check box allows you retrieve the objects owned by users whose names match the pattern in the field next to the Refresh button. Enter a complete Oracle username or a partial username with a wildcard character like '%' or '_'.

        Note: If you use a wildcard character, wait until JDBCWizard completes the retrieval of all selected objects.

    2. Choose the Refresh button.
      The Refresh function caches retrieved data on your computer. To add retrieved resources to the selection grid table or to remove them, you must disconnect and reconnect to the database.

    The "PL/SQL Packages, Procedures and Functions" Tab

    PL/SQL Packages, Procedures and Functions tab shows the PL/SQL objects for which JDBCWizard generates Java access code. Each row in the selection grid table represents a standalone procedure or a procedure within a package.

    Choose Select All or Select None buttons to select or de-select all objects.

    The grid table has the following fields:

    Field
    		Description
    Owner
    		 The owner of the resource. If this is a synonym, this will be your username.

    If you have access rights to an object owned by another user, you see their username.

    The name PUBLIC means that the resource is a publicly owned synonym.
    Name
    		 The name of the object or the synonym name. For procedures inside packages, JDBCWizard adds the package name to the beginning of the procedure name. If another procedure exists with the same name but different parameters the name will be followed by " overload n".
    Selected
    		 Choose this check box to add the object to the list of objects that require generated code.
    Accessed Via
    		 There are four methods ways of accessing objects:
    1. User Object: An object owned by the current user
    2. Other User's Object: An object for which you have EXECUTE or SELECT privileges, but is owned by another user
    3. Private Synonym: A synonym you have created for an object owned by someone else.
    4. Public Synonym: A public synonym for an object.
    Real Owner
    		 The actual owner of the selected object.
    Real Name
    		 The actual name of the selected object.

    The "Sequences" Tab

    Select the sequences for which to generate access code using the Sequences tab:

    The "Sql Statements" Tab

    The Sql Statements tab allows the user to select a directory of SQL statements to generate Java code for and to give names and data types to parameters.
    Enter the directory where you keep your SQL statement files and click on the 'Refresh' button.

    The Tree structure on the left shows the parent directory and its SQL files. Clicking on a file brings it up in the file parameters window.

     

    File Parameters Window.

    This window consists of a checkbox, a table of Parameters and a listing of the SQL Statement.

    The checkbox is used to control whether matching Java code is generated or not.

    Parameter Table

    The parameter table contains the following fields:

    Field
    		Description
    Line #
    		 The number of the line in the SQL file that the parameter was found on.
    Parameter Name
    		 The Name that has been given to this parameter. This must be a valid Java identifier. When first selected the name will default to 'ParamX' where 'X' is the number of the parameter.If the same parameter name is reused it will be assumed that there is one parameter that is used in multiple locations. If This field is greyed out it means that the Parameter name has been set by a Hint in the SQL Statement.  
    Parameter Data Type
    		 In addition to naming parameters you must also specify a data type. The list allows you to select from the following:
    • STRING - Text Data types including VARCHAR and VARCHAR2
    • NUMBER - All numeric Oracle Data Types
    • DATE - Date (to within 1 second)
    • LONG - Oracle LONG columns
    • CLOB - Oracle CLOB columns
    • BLOB - Oracle BLOB columns
    • BFILE - Oracle BFILE columns
    • RAW  - Oracle RAW columns
    • LONG RAW - Oracle LONG RAW columns
    • TIMESTAMP - Oracle TIMESTAMP columns
    If this field is greyed out it means that the Parameter Data type has been set by a Hint in the Sql Statement.  

    SQL Statement listing table

    This lists the SQL statement and shows which parameter is on which line. The Tooltip Text will reveal the name and data types that have been assigned to a parameter on any given line:

    Support for SQL*Plus and JDBC Parameters

    JDBCWizard supports SQL files that contain parameters in JDBC syntax ("empno = ?") or Sql*Plus syntax ("empno  = &empno"). If processing a SQL*Plus parameter quotes on either side of it will be ignored and the statement will be presented to the user in JDBC syntax.

    Hints in SQL statements

    If the parameter is followed by a comment consisting of a name and a data type then they will be used by JDBCWizard. For example:

    where e.empno = ? /* empNumber NUMBER */

    will lead to the parameter being called 'empNumber' and the data type being set to 'NUMBER'. If a comment is used then the Name and Data Type fields will be greyed out. Hints work slightly differently for SQL files that have been edited in SQL*Plus:

    where e.empno = &empNumber /* NUMBER */

    Requirements for SQL files

    JDBCWizard makes the following assumptions about the SQL files it uses: 

    • Only one statement per file
    • Comments should be clearly marked with "/* ... */"
    • The file should end in .SQL
    • The SQL statement is a valid statement.

    The "Tables" Tab

    The Tables tab shows the Tables for which JDBCWizard generates Java access code. Each row in the selection grid table represents a table you own or have the right to access.



    Choose Select All or Select None buttons to select or de-select all tables.

    The grid table has the following fields:

    Field
    		Description
    Owner
    		 The owner of the table. In the case of a synonym this is your username.

    If you have access rights to a table owned by another user, you see their username.

    The name PUBLIC means that the table  is a publicly owned synonym.
    Name
    		 The name of the table or the synonym name.
    Selected
    		 Choose this check box to add the object to the list of objects that require generated code.
    Accessed Via
    		 There are four methods ways of accessing objects:
    1. User Object: An object owned by the current user
    2. Other User's Object: A table for which you have privileges, but is owned by another user
    3. Private Synonym: A synonym you have created for a table owned by someone else.
    4. Public Synonym: A public synonym for a table
    Real Owner
    		 The actual owner of the selected table.
    Real Name
    		 The actual name of the selected table.

    Using Multiple Tables

    If you want generated table code that allows you to retrieve all the child records for a parent record you must select both tables in the user interface.

    Using Synonyms and other user's tables

    JDBCWizard will happily generate code to access tables using synonyms or by prepending other peoples usernames.

    Views and tables without Primary Keys

    JDBCWizard relies on the existance of a primary key to write code to update, delete and retrieve records. If you select a view or a table that does not have a Primary Key constraint JDBCWizard will generate code but most of usual methods will be left out.

    The "Code Options" Tab

    The 'Code Options' page allows you to control the code that is generated:



    Author and Version fields

    Enter your name and the version number of your code. This information appears as header comments in the generated code.

    Comment

    This field can be used in one of two ways:
    1. Enter comments for your code. These comments appear at the beginning of your generated code.
    2. Enter the path to a file in include text as a comment in your code. Use this approach for copyright and other standard notices.

    Code Options

    Choose from the following options

    Section
    		Option
    		Purpose
    Messages in generated code 
    		Debug Messages in code
    		 Adding debug messages to the generated code
    Other Messages in Code
    		 Adding other messages to the generated code
    Comments in generated code
    		 Comments in Code
    		 Adding detailed comments to the generated code.
    Maintain usage statistics
    		 Maintain Usage Statistics
    		 Generating JDBCWizard performance metrics. Each generated class implements the StatsInterface Java Interface.
    Naming convention for
    generated files    		 Java Naming Convention
    		 Choosing the naming conventions for generated Java files:
    JavaStandard.java The filename starts with an upper case letter and then uses upper case letters where the previous character in the matching database object was an underscore.
    InitialCapitalLetters.java The filename is lower case except where the previous character was an underscore. This means that generated classes names will normally start with a lower case letter
    spaces_between_words.java
    		 The filename is lower case. Underscores are left intact.

    This option is deprecated.

    Target version of Oracle Oracle Version Generating code optimized for one of the following versions of Oracle:

    8.1.5, 8.1.6, 8.1.7, 9.0.1, 9.2.0, 10.1.0, 10.2.0
    Use the following numeric
    data types
    		 A checkbox for each of the following Java data types:
    • byte
    • short
    • int
    • long
    • float
    • double
    • java.lang.Byte
    • java.lang.short
    • java.lang.Integer
    • java.lang.Long
    • java.lang.Float
    • java.lang.Double

    		 Whenever JDBCWizard is writing a method for passing numbers to or from a procedure, SQL statement or table its default behaviour is to generate a seperate method for each of the data types on the left. By unticking the appropriate checkboxes it is possible to prevent JDBCWizard from generating code with datatypes such as short, which are rarely used in practice. This feature was added in build 4.0.1709
    Table Specific Options
    		 Check columns before insert or update
    		 If checked generated table access code will make sure that all non-null fields have values and that numeric fields do not have decimal places if they aren't supposed to. This feature was added in build 4.0.1709
    Create TYPE definitions for tables in a file called 'extraObjects.sql' If checked a SQL file called 'extraObjects.sql' will be created in the 'tables' subdirectory of the generated code. This file contains matching oracle TYPE definitions for the tables selected earlier. For each table it will create a record type and an array type.

    This code is provided to assist developers who need to pass arrays into PL/SQL procedures. Because of limitations in JDBC the only kind of array of records that can be passed from Java to a PL/SQL procedure is one that uses ORACLE TYPEs.

    For further information on this subject see the manual entry for Working with PL/SQL Arrays that can not be directly accessed by JDBC.  This feature was added in build 4.0.1935.

    The "Service Options" Tab

    Purpose

    The Service Options screen allows you to

    1. Pick a directory for generated code
    2. Choose a package for generated code
    3. Define how your Data Access Object Factory class will behave
    4. Define how the Web Service classes behave.

    Java code root directory

    Enter the path to the directory on your computer for the generated code.

    Package name

    Enter the package name for the generated code. In the previous illustration, JDBCWizard generates code in the following directory:

    C:/Test3/com/yourcompany/yourproduct

    DAO Factory Class options

    In addition to creating classes for each procedure and table in your database JDBCWizard can also create a single Data Access Object Factory class that handles logging, database  connections and the creation of access class instances.

    This step has the following options:

    Option
    		 Purpose
    DAO Factory Class Name
    		 The name of the class. This can be any valid Java class name.If this field is blank no field will be created.
    Log messages using...
    		 There are five ways of logging messages
    1. JDBCWizard's TextLog - Messages are written to text files.
    2. JDBCWizard's ConsoleLog - Messages are written to System.out and System.err
    3. Java 1.4 logging - Messages are written using java.util.logging.
    4. Log4J - Messages are written using Log4J
    5. User Implemented LogInterface - Messages are written using a user created class that implements the com.orindasoft.pub.LogInterface Interface.
    Log name/Directory
    • If messages are logged using TextLog this will be the name of the log file directory.
    • If Java 1.4 logging or Log4J are in use this will be the logging context.
    Get DB Connection using...
    		 There are four methods of obtaining a Connection:
    1. Hard coded connect string: A connect string used by the thin driver is entered in the field below.
    2. JNDI used to get DataSource: JNDI is used to retrieve a DataSource object. the JNDI resource name is entered in the field below.
    3. DataSource provided at runtime: The generated code will be passed a DataSource at runtime.
    4. oracle.jbo.server.DBTransaction: The connection will be extracted from an instance of this class which is passed in at runtime.
    Connection Name
    		 An Oracle thin driver connect string or the name of the DataSource in JNDI
    Implement javax.ejb.SessionBean
    		 If selected the generated class will implement the SessionBean interface.
    Add 'finalize()' method
    		 If selected the generated class will have a finalize method that releases database resources.
    Close Connections
    		 Close connections when releaseConnection() is called
    Commit Connections
    		 Commit connections when releaseConnection() is called
    Temporary Directory
    		 Location for temporary files when working with LOBS. Can be a real operating system directory or a System property such as 'user.dir'.
    Temporary File Prefix/Suffix
    		 Prefix and suffix used by temporary files.

    Full information on how to use the DAO Factory class is available in the DAO Factory Manual section.

    Web Service Class options

    The 'Create Web Services' checkbox controls whether Web Services classes are created or not. The other options are:

    Option
    		 Purpose
    Interface Class Name
    		 The name of the interface class that contains web service methods
    Implementing Class Name
    		 The name of the class that implements the Web Service
    Number Type used by service
    		 By default generated code will use java.math.BigDecimal to represent  numbers, as this is the closest match in Java to Oracle's number datatype. For convenience JDBCWizard can also use the following data types to represent numbers in Web Services code:
    • int
    • long
    • float
    • double
    Always release database connection after call.
    		 If selected releaseConnection() will be called after each service method call.
    Add pre call code stubs
    		 An empty method is created that is called by each service method just before execution. By extending the implementing class and overiding this method users can add custom functionality without modifiying generated code.
    Add post call code stubs
    		 An empty method is created that is called by each service method after execution. By extending the implementing class and overiding this method users can add custom functionality without modifiying generated code.
    Web Service Record Type
    		 Generated web service records can either be:
    • public, no access methods - Records have public variables and no 'set' and 'get' methods
    • private, 'set' and 'get' methods - Records have protected variables and you must use 'set' and 'get' methods.
    Uploaded BFILE naming is abstract
    		 JDBCWizard will write code that under some circumstances allows the creation of BFILEs by web services. This option gives the user control over how BFILEs are named. For more information see Creation of BFILEs using a Web Service

    Full information on how to use the Web Services classes is available in the Web Service Manual section.

    The "Generate" Button

    Pressing the generate button saves your configuration and results in Java code being generated to match your selected objects. If you are using JDeveloper 10.1.3 JDBCWizard libraries will be added to your project.

    Note: Do not modify generated classes. Do not create additional classes in the same directory as generated classes.


    Working with generated code

    Required Libraries

    TickAn Oracle JDBC driver is required to use the generated code.

    TickAll generated code will require a copy of the com.orindasoft.pub library. A demo version of this is included in the JAR file containing the extension. Add this Jar file to your CLASSPATH. This is done for you if you are using Jdeveloper 10.1.3. If you buy JDBCWizard you get the source for this library.

    Tick JDBCWizard has additonal libraries for Apache Log4J or Java 1.4's java.util.logging. If you select Log4j or java.util.logging as log types you will need to add the appropriate library class to your CLASSPATH. The libraries are in a subdirectory in the Extensions's directory. This is done for you if you are using Jdeveloper 10.1.3.

    Further Information

    The JDBCWizard configuration screen

    To access this screen select "Tools/Preferences" and then "JDBCWizard Configuration". This window contains the current licence code, the expiration date and product build information. The only editable field specifies where Configuration Files associated with database connections are kept. There are also hyperlinks to JDBCWizard's documentation and purchase pages.

    Jdeveloper and SQL Developer version limitations

    This extension works with Oracle JDeveloper 11g.

    Do not try to use the JDeveloper 11g extension with SQL Developer or vice versa. They have the same names but are different internally.

    General Reference Information

    More information is obtainable from the JDBCWizard Documentation page. The support and FAQ are also useful. Information on purchasing JDBCWizard is available here.

    The JDBCWizard Demo schema.

    JDBCWizard comes with a demo schema. To obtain your own copy of the tables, procedures and data you will need to download a version of JDBCWizard that uses a windows installer from here.








Privacy Policy
Legal Terms
Site Map
About Us