Dynamiczne zapytania JDBC + SQL?

Mam pytanie jak stworzyć dynamiczne zapytanie do bazy?
Powiedzmy ze mam coś takiego co niestety też nie działa...

	       
 pstmt = con.prepareStatement( "SELECT * FROM BAZA_X WHERE COL_1 = ?," +
	        		"Col_2= ?, Col_3 = ?, col_4 = ?,"+
	        		"Col_5 = ? Col_6 = ?");
	        pstmt.setString(1, x1);
	        pstmt.setString(2, x2);
	        pstmt.setString(3, x3);
	        pstmt.setInt(5, x4);
	        pstmt.setString(4, x5);
	        pstmt.setString(6, x6);
	        pstmt.executeUpdate();
	        pstmt.close()

Parametry są przekazywanie do funkcji, konwertowane odpowiednio na string/int/etc i podstawiane do pstmt.setXXX() jesli nie ma jakiegoś parametru to porostu pomija go w zapytaniu. Tak ma to wyglądać, niestety ten kod nie działa.
w Hibernate to dość proste sprawdzam if'em czy przekazany parametr ma jakaś wartość i robię wpis

if(par_1.isEmpty()==False) con.add(Restrictions.ilike("Col_2",Par_1));

i tak razy n parametrów później tylko sumuje, wsadzam do listy<typ> i wysyłam do modelu tabeli a jak by to wyglądało w JDBC? Bo przeglądam tutki i dokumentacje ale tam wszędzie jest tylko statyczne zapytanie?
W pstmt nie da się wstawiać np

 if(par_1.isEmpty()==false)to string par_1 = " Col_1 = " +par_1 

Wie ktoś jak to rozwiązać?
Może rozwiązanie jest dość banalne a ja jestem już zmęczony i nawet o tym nie pomyślałem...

Możesz pobawić się z http://openhms.sourceforge.net/sqlbuilder/ , ale to duża armata. Względnie napisać własny builder. W samym API JDBC nie ma obsługi dynamicznych zapytań.

Nie ma możliwości zrobienia koniunkcji zapytania?
Analogicznie do criteria w Hibernate?
jeśli jakiś warunek nie został podany to nie uwzględniaj go w zapytaniu?
A jak sobie radzono przed EJB i Hibernatebe z takimi zapytaniami tylko na tych parametrach które podał użytkownik?

Żeby to zrobić musiałbyś tworzyć zapytanie za pomocą jakiegoś buildera, a nie jako string.

WILSON GODDAMN IT! ;]
A nie da się tego jakoś obejść bez buildera?

No nie. Zresztą w Hibernate/JPA też korzystasz z buildera. Jeżeli nie chcesz się bawić to ściągnij to co dałem w pierwszym poście. Ułatwia życie.

udało mi się rozwiązać problem bez buildera

Jak udało Ci się rozwiązać problem to podziel się z nami jak to zrobiłeś?

Jasne w sumie mogłem od raz wkleić kod, od razu mówię że jest to rozwiązanie nie estetyczne i wielkość if;a rośnie proporcjonalnie do ilości kolumn ;]
A to KOD:

				
String sql="SELECT * FROM hibernatedata WHERE ";

			if(Id.isEmpty()==false){
				sql += "EmpID = "+"'"+Id+"'";
			}
			if(Fname.isEmpty()==false){
				if(Id.isEmpty()==false){
					sql += " AND ";
				}
				sql += "EmpFirstName = "+"'"+Fname+"'";
			}
			if(Lname.isEmpty()==false){
				if(Fname.isEmpty()==false || Id.isEmpty()==false){
					sql += " AND ";
				}
				sql += "EmpLastName = "+"'"+Lname+"'";
			}

			if(Tel.isEmpty()==false){
				if(Lname.isEmpty()==false || Fname.isEmpty()==false || Id.isEmpty()==false){
					sql += " AND ";
				}
				sql += "EmpTel = "+"'"+Tel+"'";
			}

			if(Worker.isEmpty()==false){
				if(Tel.isEmpty()==false || Lname.isEmpty()==false || Fname.isEmpty()==false || Id.isEmpty()==false){
					sql += " AND ";
				}
				sql += "EmpWorker = "+"'"+Worker+"'";
			}
			if(Age.isEmpty()==false){
				if(Worker.isEmpty()==false || Tel.isEmpty()==false || Lname.isEmpty()==false || Fname.isEmpty()==false || Id.isEmpty()==false){
					sql += " AND ";
				}
				sql += "EmpWorker = "+"'"+Age+"'";
			}
			if(Dep.isEmpty()==false){
				if(Worker.isEmpty()==false || Age.isEmpty()==false || Tel.isEmpty()==false || Lname.isEmpty()==false || Fname.isEmpty()==false || Id.isEmpty()==false){
					sql += " AND ";
				}
				sql += "EmpDep = "+"'"+Dep+"'";
			} 
System.out.println(sql);

i w zależności od tego co podamy na wejście mogą to być dowolne parametry z danych kolumn w dowolnej mieszance to ify budują odpowiedniego string
Na zdrowie! ;p

Czy słyszałeś o SQL Injection?
https://www.owasp.org/index.php/Preventing_SQL_Injection_in_Java
W tym przypadku SQL Injection raczej nie będzie groźny, ale lepiej zrobić sobie dobry nawyk.

Przy okazji - zamiast pisać "Id.isEmpty() == false" lepiej dać "!Id.isEmpty()". Kolejna rzecz to zmienne zaczynające się od wielkiej litery.

Jeszcze mała wskazówka:
Na początku klauzuli WHERE daj "1=1", tzn.

SELECT * FROM hibernatedata WHERE 1=1

dzięki temu nie będą potrzebne te wszystkie ify przy wstawianiu AND. Będziesz mógł od razu napisać:

sql += " AND EmpID = :id";

No i na koniec - użyj klasy StringBuilder, a nie zwykłego sklejania napisów.

OMG ale paskudny kod. Jak juz to zrob lepiej osobny StringBuilder na where, i porownuj where.length() > 0 i jesli tak to dodawaj 'and'. Na koniec jesli nadal jest pusty to po prostu nie dodasz where, a w Twoim przypadku powstanie bledne zapytanie (zdaje mi sie). Ale takie rzezbienie w gownie i tak do niczego dobrego nie prowadzi...

jambalaya napisał(a)

OMG ale paskudny kod. Jak juz to zrob lepiej osobny StringBuilder na where, i porownuj where.length() > 0 i jesli tak to dodawaj 'and'. Na koniec jesli nadal jest pusty to po prostu nie dodasz where, a w Twoim przypadku powstanie bledne zapytanie (zdaje mi sie). Ale takie rzezbienie w gownie i tak do niczego dobrego nie prowadzi...

Dużo lepiej dać od razu "WHERE 1=1". Zobacz mój poprzedni post.

Nie jestem profesjonalistą dopiero zaczynam się bawić w javie potrzebowałem szybkiego rozwiązania do testów a to działa (bo zawsze mam chociaż 1 parametr). Krzysiek a mógł byś mi dokładniej wyjaśnić jak to WHERE 1=1 działa w odniesienu do mojego kodu?
Ewentualnie jak by to inaczej wyglądało bardziej poprawnie?

@tomas1704, jeżeli masz jakiegoś SQLa i dasz warunek where 1=1 to otrzymasz to samo co w przypadku braku jakichkolwiek warunków. Warunek ten jest zawsze prawdziwy i pozwala na pobranie całej bazy danych jeżeli uda się go wstrzyknąć do zapytania:

Select * from tabela where kolumna1=X and koumna2=y; -- i modyfikując to zapytanie przez wstrzyknięcie:
Select * from tabela where kolumna1=X and koumna2=y OR 1=1; -- otrzymujesz całą tabele

W twoim przypadku jeżeli koniecznie chcesz otrzymać dynamiczne zapytanie i nie chcesz korzystać z zewnętrznej biblioteki do jego budowy możesz spróbować zbudować tablicę zawierającą obiekty SqlParam:

class SqlParam<T>{
    String colName;
    T value;
    int position;
}

I na ich podstawie zbudować odpowiednie preparedStatement i je wypełnić jak w pierwszym poście.

tomas1704 napisał(a)

Nie jestem profesjonalistą dopiero zaczynam się bawić w javie potrzebowałem szybkiego rozwiązania do testów a to działa (bo zawsze mam chociaż 1 parametr). Krzysiek a mógł byś mi dokładniej wyjaśnić jak to WHERE 1=1 działa w odniesienu do mojego kodu?
Ewentualnie jak by to inaczej wyglądało bardziej poprawnie?

StringBuilder sb = new StringBuilder("SELECT * FROM hibernatedata WHERE 1=1");
Map<String, Object> params = new HashMap<String, Object>();
if(!id.isEmpty()){
      sb.append(" AND EmpID = :id");
      params.put("id", Integer.valueOf(id));
}
if(!fName.isEmpty()){
      sb.append(" AND EmpFirstName = :fName");
      params.put("fName", fName);
}

PreparedStatement ps = con.prepareStatement(sb.toString());

for (Map.Entry<String, Object> param: params.entrySet()) {
    ps.setObject(param.getKey(), param.getValue());
}

@__krzysiek: tak czytalem, fajny trik.
Moze to i lamerskie pytanie, ale pytam powaznie: czy select * from tabela i select * from tabela where 1 = 1 jest rownoznaczne? Mam na mysli, ze w drugiej sytuacji sprawdzany jest ten warunek, ktory jest zbedny. Czy bazy umieja sobie to ladnie zoptymalizowac, czy dla kazdego wiersza bedzie smigal tego where? Sadze ze indeks nic tutaj nie pomoze bo przeciez nie jest ten where na zadnej indeksowanej kolumnie.
Jesli jednak taki where 1 = 1 zle wplywa na zapytanie, to wolalbym juz w ogole go nie wysylac.

tak, select * from tabela jest równoważne select * from tabela where 1 = 1

Każda profesjonalna baza jest na tyle inteligentna, aby pominąć warunek "1=1". Nie ma on wpływu na wydajność.
Ten trick stosowany jest np. w Hibernate Restrictions API. Wygenerowany sql zawiera właśnie te 1=1.

A tak jest poprawnie i blokuje przed "wstrzykiwaniem"?

				StringBuilder sql = new StringBuilder("SELECT * FROM hibernatedata WHERE 1=1 ");
			try{

			if(!Id.isEmpty()){
				sql.append(" AND EmpID = '"+Id+"'");
			}
			if(!Fname.isEmpty()){
				sql.append(" AND EmpFirstName = '"+Fname+"'");

			}
			if(!Lname.isEmpty()){
				sql.append(" AND EmpLastName = '"+Lname+"'");

			}

			if(!Tel.isEmpty()){
				sql.append(" AND EmpTel = '"+Tel+"'");

			}

			if(!Worker.isEmpty()){
			sql.append(" AND EmpWorker = '"+Worker+"'");

			}
			if(!Age.isEmpty()){
				sql.append(" AND EmpAge = '"+Age+"'");

			}
			if(!Dep.isEmpty()){
				sql.append(" AND EmpDep = '"+Dep+"'");

			}
			System.out.println(sql.toString());
                        Class.forName("com.mysql.jdbc.Driver");
			con = DriverManager.getConnection(url,userName,password);
			PreparedStatement ps = con.prepareStatement(sql.toString());
 

prędzej tak:

		   StringBuilder sql = new StringBuilder("SELECT * FROM hibernatedata WHERE 1=1 ");

		   Map<String, String> paramMap = new HashMap<String, String>();

           if(!Id.isEmpty()){
        	   paramMap.put("id", Id);
           }
           if(!Fname.isEmpty()){
        	   paramMap.put("Fname", Fname);
           }
           // ....

           Entry<String,String>[] entrySet = (Entry<String, String>[]) paramMap.entrySet().toArray();
           for(Entry<String,String> e : entrySet){
        	   sql.append(" AND "+e.getKey()+"=?");
           }

           PreparedStatement ps = con.prepareStatement(sql.toString());
           
           // przez i bo potrzeba do prepared statement
           for(int i = 0; i<entrySet.length; i++){
        	   ps.setString(i+1, entrySet[i].getValue());
           }

Przy czym warto pobawić się z własnymi klasami, które będą wstanie ustawiać parametry o odpowiednim typie.

zaraz sprawdzę bo wczoraj długo kombinowałem z rozwiązaniem Krzyśka i nie wiedziałem jak to zastosować z racji ze ps.setObject(...) musi mieć parametry <int , String/Object) i nie wiedziałem jak to obejść aby wyciągnąć wartość z mapy ale nie Stringa i budowałem różne cuda co kończyło się fiaskiem ;]
A co do klas to jestem jeszcze za cienki na takie rzeczy ;p

A co do klas to jestem jeszcze za cienki na takie rzeczy ;p

etam....

public abstract class SqlParam<T>{

  protected final T value;

  public SqlParam(T value){
     this.value= value;
  }
 
  public abstract void addToStatement(PreparedStatement ps, int position);

}

//... przykładowe ze Stringiem:

public class SqlStringParam extends SqlParam<String>{

    public SqlStringParam(String value){
       super(value);
    }

     public void addToStatement(PreparedStatement ps, int position){
             ps.setString(position,value);
     }
}

da się?