Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations TouchToneTommy on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Dynamic SQL build in JAVA 1

Status
Not open for further replies.

DB2Problem

Programmer
Oct 17, 2002
53
US
Folks,

I am using prepared statement on one of my java calls to the database.

Here's is a sample of the same

String sql = "Select <table_nam>.<column_name> from <schema_name>.<table_name>

where <condition>"
and <column_name_1> = ? " +
" and <column_name_2> = ? " +
" and <column_name_3> = ? ";


I set the parameters for aboe column as below

stmt.setString( 1, column1 );
stmt.setString( 2, column2 );
stmt.setString( 3, column3 );

column1, column2 and column3 values are passed to the method that has the above code block.

Question:

How to make this SQL dynamic ?. If column1, column2 and column3 has particular values (say "X") I DO NOT want to add
in the SQL. For that I have an if statment and populate sql without problem. BUT i have to incorporate many permutations
for setting the statement block, so that it matches with the SQL string. How to do both things i.e generating SQL and setting values (when the values are conditional) in statements.

What is the best possible solution to have the code modular and more robust (avoiding lot of if's)

 
I don't know java but here is a VB example. Just use if statements....

sqlstr = "SELECT PTID, PTSVNAM, PTSVHSE#, PTSVDPRE, PTSVSTRT, PTSVCOMN, PTSVLOCT, PTSTATUS,"
sqlstr = sqlstr & " SVCID, SVACCT, SVBRCH, SVCOTY,"
sqlstr = sqlstr & " AMNAME, AMNAM2, AMADR1, AMADR2, AMADR3, AMSTAT, AMZIP, AMZIP4, AMACCT, AMBRCH, AMEMAL"
sqlstr = sqlstr & " FROM TDCPFASUR.PLTMAST inner join SVCSUR.SRVMAST99"
sqlstr = sqlstr & " on PTID=SVCID"
sqlstr = sqlstr & " inner join SVCSUR.ACTMAST"
sqlstr = sqlstr & " on SVACCT=AMACCT AND SVBRCH = AMBRCH"
sqlstr = sqlstr & " WHERE PTSVHSE# >=" & iStartNum & " and PTSVHSE#<=" & iEndNum & " and PTSVSTRT='" & sAddress & "' and PTSTATUS='AC' and PTRECTYP='SL'"

'check to see if there is a direction
If Len(Trim(sDirection)) > 0 Then
sqlstr = sqlstr & " and PTSVDPRE='" & sDirection & "'"
End If

'Check to see if there is anything in the city
If Len(Trim(sCity)) > 0 Then
sqlstr = sqlstr & " and PTSVCOMN='" & sCity & "'"
End If

'add an order by street number
sqlstr = sqlstr & " Order by PTSVHSE#"
 
Well this is remarkable..Though it may not help me a lot BUT i really appreciate your effort in explaining. Thanks a lot..

However, I will still be looking for valuable suggestion in JAVA..
 
Can you pass in your args as a String[] array (instead of (String arg1, String arg2) etc? If so, I can give you an example which will be close to the best you can achieve ...

--------------------------------------------------
Free Database Connection Pooling Software
 
Yes Sure, I can pass String array instead of separate values..
 
Actually a Hashtable or similar key=value object may be better ...

Here is a simple example :

Code:
		for (Enumeration e = ht.keys() ; e.hasMoreElements() ;) {
			String key = (String)e.nextElement();
			String val = (String)ht.get("" +key);
			if (++num == ht.size()) {
				baseSQL += (key + " = '" + val +"'");

			} else {
				baseSQL += (key +" = '" + val +"' and ");
			}
		}

		System.out.println(baseSQL);

and here is a slightly more complicated example (may need some slight tweaking) :

Code:
		Class.forName("com.mysql.jdbc.Driver");
		Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test", "", "");
		TreeMap tm = new TreeMap();
		tm.put("columnOne", "valueOne");
		tm.put("columnTwo", "valueTwo");
		tm.put("columnThree", "valueThree");
		SortedMap sm = Collections.synchronizedSortedMap(tm);
		int num = 0;
		String baseSQL = "Select * from bla";

		if (sm.size() > 0) baseSQL += " where ";

		for (Iterator e = sm.keySet().iterator() ; e.hasNext() ;) {
			String key = (String)e.next();
			String val = (String)sm.get("" +key);
			if (++num == sm.size()) {
				baseSQL += (key +" = ?");

			} else {
				baseSQL += (key +" = ? and ");
			}
		}
		System.out.println(baseSQL);

		PreparedStatement ps = conn.prepareStatement(baseSQL);
		num = 0;

		for (Iterator e = sm.keySet().iterator() ; e.hasNext() ;) {
			String key = (String)e.next();
			String val = (String)sm.get("" +key);
			ps.setString(++num, val);
			System.out.println("Setting " +num +" - " +key +"=" +val);

		}

--------------------------------------------------
Free Database Connection Pooling Software
 
Actually, even better ...

Code:
public class Test  {
	class Holder {
		public int id;
		public String key;
		public String val;

		public Holder(int id, String key, String val) {
			this.id = id;
			this.key = key;
			this.val = val;
		}
	}

	public static void main(String args[]) throws Exception {
		new Test().test();
	}

	public void test() throws Exception {
		Class.forName("com.mysql.jdbc.Driver");
		Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test", "", "");

		// dummy data
		Holder one = new Holder(1, "columnOne", "valueOne");
		Holder two = new Holder(2, "columnTwo", "valueTwo");
		Holder three = new Holder(3, "columnThree", "valueThree");
		ArrayList al = new ArrayList();
		al.add(one);
		al.add(two);
		al.add(three);

		int num = 0;
		String baseSQL = "Select * from bla";

		if (al.size() > 0) baseSQL += " where ";

		for (int i = 0; i < al.size(); i++) {
			Holder h = (Holder)al.get(i);

			if (i == al.size()) {
				baseSQL += (h.key +" = ?");

			} else {
				baseSQL += (h.key +" = ? and ");
			}
		}
		System.out.println(baseSQL);

		PreparedStatement ps = conn.prepareStatement(baseSQL);
		num = 0;
		for (int i = 0; i < al.size(); i++) {
			Holder h = (Holder)al.get(i);
			ps.setString(h.id, h.val);
			System.out.println("Setting " +h.id+" - " +h.key +"=" +h.val);

		}
	}
}

--------------------------------------------------
Free Database Connection Pooling Software
 
oops ...

if (i == al.size()) {

should be :

if (i == al.size()-1) {



--------------------------------------------------
Free Database Connection Pooling Software
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top