JDBC - PreparedStatement & Order by Clause Conflict?
1 posts in topic
Flat View  Flat View
TOPIC ACTIONS:
 

Posted By:   Nathan_Bagby
Posted On:   Saturday, December 8, 2001 09:09 AM

Is there a conflict caused by the use of an order by clause
with a prepared statement? I have an applet that dynamically
loads a JComboBox using a prepared statement like
"SELECT ID, PRODUCT_NAME FROM PRODUCTS". This works
fine and the JComboBox is populated with selections but
they are not in alphabetical order. If I add "ORDER BY
PRODUCT_NAME" to the prepared query the applet will not load.

Any information/clarification of this issue would be greatly
appreciated.

Re: JDBC - PreparedStatement & Order by Clause Conflict?

Posted By:   Bernie_Acs  
Posted On:   Sunday, December 9, 2001 02:02 PM

I have a sample table which contains the following:




ID col1 col2
1 this 1
2 something 23
3 nothing 24
4 somethingelse 25
5 that 19


Sample JComboBox Class






// Copyright (c) 2000
package package6;

import javax.swing.JComboBox;
import java.sql.*;
import java.util.*;

/**
* A Class testJComboBox.
*


* @author Me
*/
public class testJComboBox extends JComboBox {
private static int minValue = 0;
private static int maxValue = 50;
private static String sortColumnName = "ID";
private static String sortDirection = "asc";

/**
* Constructs a new instance.
*/
public testJComboBox() {
this( minValue , maxValue, sortColumnName, sortDirection );
}
/**
* Constructs a new instance define min, max, and default sortBy, sortDirection
*/
public testJComboBox(int min, int max ) {
this( min , max, sortColumnName, sortDirection );
}
/**
* Constructs a new instance default min, max, sortDirection and define sortBy.
*/
public testJComboBox(String sortBy ) {
this( minValue , maxValue, sortBy, sortDirection );
}
/**
* Constructs a new instance default min, max and define sortBy, sortDirection .
*/
public testJComboBox(String sortBy, String sortOrder ) {
this( minValue , maxValue, sortBy, sortOrder );
}

/**
* Constructs a new instance define min, max, sortDirection and sortBy.
*/
public testJComboBox(int min, int max, String sortBy, String sortOrder ) {
super();
minValue = min;
maxValue = max;
sortColumnName = sortBy ;
sortDirection = sortOrder;
try {
Init();
}
catch (Exception e) {
e.printStackTrace();
}
}

/**
* Initializes the state of this instance.
* Here is where we will get the list of items from DB
* and populate the listItems in the ComboBox
*/
private void Init() throws Exception {
try
{
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver").newInstance();
java.sql.Connection conctn = DriverManager.getConnection(
"jdbc:odbc:;"+
"DRIVER=Driver do Microsoft Access (*.mdb);"+
"UID=admin;"+
// "Password=billybob;"+
"UserCommitSync=Yes;"+
"Threads=3;"+
"SafeTransactions=0;"+
"PageTimeout=5;"+
"MaxScanRows=8;"+
"MaxBufferSize=2048;"+
"FIL=MS Access;"+
"DriverId=25;"+
"DefaultDir=C:\Documents and Settings\Administrator\My Documents;"+
"DBQ=C:\Documents and Settings\Administrator\My Documents\unsecuredb1.mdb"
);

//
// Make PreparedStatement
//
PreparedStatement pstmt0 = conctn.prepareStatement(
"select * from table3 " +
" where col2 between ? and ? " +
" order by "+ sortColumnName+ " " + sortDirection );

pstmt0.setInt( 1, minValue );
pstmt0.setInt( 2, maxValue );

java.sql.ResultSet rsPStatement = pstmt0.executeQuery();
ResultSetMetaData rsmd0 = rsPStatement.getMetaData();
String rowOutPut = "";
int rsPSRowCount = 0 ;

while ( rsPStatement.next() ){
rsPSRowCount += 1;
for ( int i = 1; i <= rsmd0.getColumnCount() ; i++ ){
String rsPSObject = rsPStatement.getString( i );
rowOutPut = rowOutPut + rsPSObject + ",";
if( rsmd0.getColumnName( i ).compareToIgnoreCase( "col1" ) == 0 )
this.insertItemAt( ((Object)(rsPSObject)), rsPSRowCount-1 );
// this.addItem( ((Object)(rsPSObject)) );
}
//
// Trim off trailing comma
//
rowOutPut = rowOutPut.substring(0, ((int)(rowOutPut.length()-1 )) );
System.out.println( " PSResultRow Counter = " + rsPSRowCount +
" : " + rowOutPut );
rowOutPut = "" ;
}
rsPStatement.close();
pstmt0.close();


}catch(Exception e){
e.printStackTrace();
System.out.println("Well, something really didn't work out that well!!!");
}

}

/**
* main
* @param args
*/
public static void main(String[] args) {
// test#1
// testJComboBox testJCBox = new testJComboBox();
//
// test#2
// testJComboBox testJCBox = new testJComboBox( "col1" );
//
// test#3
testJComboBox testJCBox = new testJComboBox( "col1", "desc" );
//
//
//
// System.out.println( testJCBox.paramString() );
System.out.println( "Dump ComboBox's List Items:" );
for ( int i = 0 ; i< testJCBox.getItemCount(); i++ )
System.out.println( "Item# "+i+" = "+ testJCBox.getItemAt( i ).toString() );
}
}





Output Produced by the Above Code


TEST# 1


PSResultRow Counter = 1 : 1,this,1.0
PSResultRow Counter = 2 : 2,something,23.0
PSResultRow Counter = 3 : 3,nothing,24.0
PSResultRow Counter = 4 : 4,somethingelse,25.0
PSResultRow Counter = 5 : 5,that,19.0
Dump ComboBox's List Items:
Item# 0 = this
Item# 1 = something
Item# 2 = nothing
Item# 3 = somethingelse
Item# 4 = that

TEST# 2


PSResultRow Counter = 1 : 3,nothing,24.0
PSResultRow Counter = 2 : 2,something,23.0
PSResultRow Counter = 3 : 4,somethingelse,25.0
PSResultRow Counter = 4 : 5,that,19.0
PSResultRow Counter = 5 : 1,this,1.0
Dump ComboBox's List Items:
Item# 0 = nothing
Item# 1 = something
Item# 2 = somethingelse
Item# 3 = that
Item# 4 = this

TEST# 3


PSResultRow Counter = 1 : 1,this,1.0
PSResultRow Counter = 2 : 5,that,19.0
PSResultRow Counter = 3 : 4,somethingelse,25.0
PSResultRow Counter = 4 : 2,something,23.0
PSResultRow Counter = 5 : 3,nothing,24.0
Dump ComboBox's List Items:
Item# 0 = this
Item# 1 = that
Item# 2 = somethingelse
Item# 3 = something
Item# 4 = nothing
About | Sitemap | Contact