Sample demonstrating Dynamic SQL Queries

Table Of Contents 

• Overview of the Sample Application
• Required Software
• Application Set-up and Configuration
• Running the Application 
• From Oracle9i JDeveloper 
• From JDK for Windows
• From JDK for Linux
• Description of Sample Files

This application shows how to create and execute dynamic sql queries using JDBC. The user selects a table from his schema, then the columns and provides the where clause. The dynamic SQL query is then formed based on the user input, executed and results are displayed.

Working of the Sample

• When the user invokes the application, the connection status and errors, if any, are displayed in the status bar.
• If the connection was successful then all the tables in the user's schema are displayed in a JTable.
• When a table is chosen then the columns in that table are retrieved and displayed in another JTable. The user can now choose the columns.
• When the user presses the Select button then a dynamic sql query is formed based on the Table and Columns chosen and also the Where Clause entered. The query is then executed and results displayed in the "Results" JTable whose structure changes based on the columns selected.

Here is the code where the SQL query is created dynamically. You can find more details of the code in DynamicSqlSample.java file under src/oracle/otnsamples/jdbc/dynamicsql folder. Look into Description of Sample Files section for folder and file details.

private void selectRecords() { // Get the concatenated string of column names for the columns // be retrieved String columnNames = gui.makeStringOfAllColumns(); // Get the Where clause for the query String whereClause = gui.restrictionTextfield.getText(); // Form the dynamic Query StringBuffer query = new StringBuffer( "SELECT " ); // Start the SQL statement query.append( columnNames + " FROM "); // Add columns to be retrieved query.append( gui.tableName ); // Add table to retrieve // Add WHERE clause if any if( !whereClause.equals("") ) query.append(" WHERE " + whereClause); try { gui.putStatus("Selecting Records ..."); // Create a SQL statement context to execute the Query Statement stmt = connection.createStatement(); // Execute the formed query and obtain the ResultSet ResultSet resultSet = stmt.executeQuery( new String(query) ); String[] columnValues = new String[gui.selectedColumns.length]; // Populate form the resultset while( resultSet.next() ) { // Point result set to next row // Retrieve column values for this row for( int i = 1; i < gui.selectedColumns.length + 1; i++ ) { // Check for null values columnValues[i -1] = (resultSet.getString(i) == null)? "" : (String )resultSet.getString(i); } gui.addToResultsTable(columnValues); // Update GUI } ..................... ......................

• Oracle9i JDeveloper ( Note: Oracle9i JDeveloper is Oracle's Visual Java Development Tool and can be downloaded from here )
  or JDK1.2.x or above This can be downloaded from here .
• Oracle9i Database or higher running SQL*Net TCP/IP listener. This can be downloaded from here .
• Oracle9i JDBC Driver. The JDBC driver is available at ORACLE_HOME/jdbc/lib. Or it could be downloaded from here.

• Unjar the provided DynamicSqlSample.jar using the following command 

> jar xvf DynamicSqlSample.jar

Note: You will find jar.exe in JDK_HOME\bin. Ensure JDK_HOME\bin is present in your system path. 
(JDK_HOME is the root directory of the JDKx.x installation). This creates a folder DynamicSqlSamplewith all the source files


• Edit DynamicSqlSample/Connection.properties file in your favorite editor. Change the HostName, Port, SID, UserName and 
Password to connect to required schema in your database.

This sample application can be run in 3 different ways listed below.

• From Oracle9i JDeveloper
• From JDK for Windows
• From JDK for Linux

From Oracle9i JDeveloper

• Open Oracle9i JDeveloper and use File/Open option to select the DynamicSqlSample.jws from the DynamicSqlSample directory.
• Next, select Project/Make DynamicSqlSample.jpr from main menu.
• Now, select Run/Run DynamicSqlSample.jpr from main menu to run the application.

From JDK for Windows

This section will describe steps to run the application from console using JDK on Windows. The sample can be run either manually or using a script file .

Run application using batch File: run.bat provided:

By setting few environment variables, the sample application could be directly run by just executing the batch file: run.bat from the command prompt, from DynamicSqlSample directory. Environmental variables JAVA_HOME and JDBC_HOME have to be set before running run.bat file.
Example:

D:\DynamicSqlSample> set JDBC_HOME=d:\oracle9i\jdbc\lib
D:\DynamicSqlSample> set JAVA_HOME=d:\jdk1.3.1
D:\DynamicSqlSample> run

Running the application manually:

• Set CLASSPATH to include Oracle9i JDBC Driver file: classes12.zip or classes12.jar. 
• Also add DynamicSqlSample directory where Connection.properties exists and the current directory to the CLASSPATH
  Example: 
  D:\DynamicSqlSample>set CLASSPATH=D:\oracle9i\jdbc\lib\classes12.zip;D:\DynamicSqlSample;.
  
• From the directory DynamicSqlSample\src\oracle\otnsamples\jdbc\dynamicsql, compile all the java files using javac:
  Example: 
  D:\DynamicSqlSample\src\oracle\otnsamples\jdbc\dynamicsql>javac -d . *.java
  
• Run the class file using java from  DynamicSqlSample\src\oracle\otnsamples\jdbc\dynamicsql directory
  Example:
  D:\DynamicSqlSample\src\oracle\otnsamples\jdbc\dynamicsql>
  java oracle.otnsamples.jdbc.dynamicsql.DynamicSqlSample

From JDK for Red Hat Linux Advanced Server release 2.1

This section will describe steps to run the application from console using JDK on Linux. The sample can be run either manually or using a script file .

Run application using batch File: run.sh provided:

By setting few environment variables, the sample application could be directly run by just executing the batch file: run.sh from the command prompt, from DynamicSqlSample directory. Environmental variables JAVA_HOME and JDBC_HOME have to be set, else the user will be prompted to enter values.

• Go to DynamicSqlSample directory and from the $ prompt use the command below to give execute permission to the file.
  $chmod 777 run.sh
• Now run the file:
  $sh run.sh

Running the application manually:

• Set CLASSPATH to include Oracle9i JDBC Driver file: classes12.zip or classes12.jar.
• DynamicSqlSample  directory where Connection.properties exists and current directory are also added to the CLASSPATH.
  Example:
  $export CLASSPATH=/home1/jdbc/lib/classes12.zip:/home1/DynamicSqlSample:.
• From the directory DynamicSqlSample/src/oracle/otnsamples/jdbc/dynamicsql, compile all the java files using javac:
  Example:
  $javac -d . *.java
• Run the class file using java from the same DynamicSqlSample/src/oracle/otnsamples/jdbc/dynamicsqldirectory.
  Example:
  $java oracle.otnsamples.jdbc.dynamicsql.DynamicSqlSample