[JAVA & SQL] wildcard-gebruik in prepared statement

[roeleboel]

Situatie:

• MSSQL-server, wordt benaderd via een jdbc-odbc brug
• tabel 'objects' bestaat, met de kolommen 'objectid' (integer - 4 bytes groot) en 'objectnumber' (char, 42 karakters)
• een record met als 'objectnumber' 'admin' bestaat, bijhorend 'objectid' is 151824
• gebruikte jre & jdk zijn de java 6 update 16

Java:

private boolean objectExists(String objectnummer, String wildcard){
    boolean found = false;
    try {
        PreparedStatement g = conTMS.prepareStatement("SELECT objectid FROM objects WHERE objectnumber = ? or objectnumber like ?");
        g.setString(1, objectnummer);
        g.setString(2, objectnummer+wildcard);

        ResultSet rs = g.executeQuery();
        
        while (rs.next()) {
            found = true;
            System.out.println("we hebben resultaten gevonden");
        }
        rs.close();
        g.close();
    } catch (Exception ex) {
        ex.printStackTrace();
    }
    return found;
}

Resultaten van aangeroepen functie:

code:

objectExists("admin", "%") => true

Dit resultaat snap ik: 'admin' komt namelijk letterlijk voor.

Omdat wildcards kunnen wisselen naargelang gebruikte server/taal/... heb ik alle (mij bekende) mogelijkheden geprobeert.

Groep 1:

code:

objectExists("admi", "*") => false
objectExists("admi", "?") => false

Groep 2:

code:

objectExists("admi", "%") => false
objectExists("admi", "_") => false

Van alle gebruikte wildcards hadden er mijn inziens 2 'true' moeten opleveren?

• er worden géén exceptions gegooid door de functie
• als ik in MS Access een query maak op dezelfde tabel, via dezelfde odbc-connectie, krijg ik met zoeken op "admi*" of "admi?" wel een objectid terug.
• de "we hebben resultaten gevonden" wordt enkel in de eerste aanroep geprint => de aanroepen met "admi" + wildcard geven lege resultsets.
• dat ik hier Nederlands en Engels door elkaar gebruik hier weet ik, dat dat geen goede zaak is weet ik ook, maar voor puur debuggen kan het volgens mij - dit is uiteraard géén productiecode
• ik heb al zowat iedere sql-tutorial doorgenomen, samen met de java-tutorials, ik zie de fout niet vrees ik

Kan iemand een licht werpen op wat ik hier gigantisch over het hoofd zie?

[ Voor 0% gewijzigd door roeleboel op 08-10-2009 12:17 . Reden: typo-tjes ]

[Niemand_Anders]

Weinig prepare statements ondersteunen de like parameter. Ik denk dat je het beste kunt beginnen met het doorlezen van de documentatie over PreparedStatement. Heb je als een een google zoekopdarcht geven met 'PreparedStatement like'?

Veel OR/M frameworks hebben wel support voor like operators. Misschien eens een kijkje nemen bij Hibernate?

[Verwijderd]

Kun je niet beter gewoon je SQL query aanpassen? Dat lijkt me sneller....

SQL:

SELECT objectid FROM objects WHERE objectnumber = ? or objectnumber like "%?%"

Hoef je ook niet meer je wildcard mee te geven in je methode aanroep.
(Tenzij dit natuurlijk op de één of andere vreemde reden gewenst zou zijn.)

[ Voor 0% gewijzigd door Verwijderd op 08-10-2009 13:25 . Reden: Quotes toegevoegd ]

[roeleboel]

Niemand_Anders schreef op donderdag 08 oktober 2009 @ 13:04:
Weinig prepare statements ondersteunen de like parameter. Ik denk dat je het beste kunt beginnen met het doorlezen van de documentatie over PreparedStatement. Heb je als een een google zoekopdarcht geven met 'PreparedStatement like'?

Veel OR/M frameworks hebben wel support voor like operators. Misschien eens een kijkje nemen bij Hibernate?

volgens deze site http://java.sun.com/docs/...jdbc/basics/prepared.html van Sun zelf zou het perfect moeten werken en geven ze zelfs een quasi identiek voorbeeld met LIKE.
Een OR/M is niet gewenst voor dit project.

Verwijderd schreef op donderdag 08 oktober 2009 @ 13:23:
Kun je niet beter gewoon je SQL query aanpassen? Dat lijkt me sneller....

SQL:

SELECT objectid FROM objects WHERE objectnumber = ? or objectnumber like "%?%"

Hoef je ook niet meer je wildcard mee te geven in je methode aanroep.
(Tenzij dit natuurlijk op de één of andere vreemde reden gewenst zou zijn.)

De getoonde code is de 'simpele' variant, herschreven om het probleem zo compact mogelijk te illustreren.
Verder is het _niet_ mogelijk om de wildcard al in de query te zetten, aangezien deze (in de productie-variant) op een aantal verschillende plaatsen kan voorkomen (en er wordt ook een mix gebouwt tussen de '_' en de '%').

Verder wordt er een PreparedStatement gebruikt om sql-injectie te voorkomen.

Bemerking die ik gemerkt heb bij het testen tot nu toe:
als ik de '%' wildcard gebruik (ipv '*') duurt het uitvoeren gevoelig langer, dit lijkt aan te tonen dat de database-server wel degelijk met wildcard zoekt, ik krijg echter een lege resultset terug...

Hetgeen mij echt mijn pet doet opeten is dat dit:

Java:

PreparedStatement pStmt = TMSDatabase.prepareStatement("SELECT objectid FROM objects WHERE objectnumber = ? or objectnumber like ?");
pStmt.setString(1, invNumberWeSearch);
pStmt.setString(2, invNumberWeSearch+"-dubbel__");
ResultSet rs = pStmt.executeQuery();

in een andere klasse _wel_werkt. (zelfde machine, zelfde ontwikkelomgeving, zelfde jdbc-odbc-brug, invoer daar is soortgelijk aan de test die ik hierboven beschreef, de '__' werkt hier wel degelijk als wildcard)

[Snake]

Als je hetzelfde eens test op een MDB (Access) werkt het dan wel?

Want je code is juist. Misschien ondersteund de MS SQL ODBC driver geen wildcards (which is quite possible).

[Cobalt]

Wellicht is dit de oplossing:
http://www.microsoft.com/...&cat=&lang=&cr=&sloc=&p=1

Java:

        PreparedStatement g = conTMS.prepareStatement("SELECT objectid FROM objects WHERE objectnumber = ? or objectnumber = '?';"); 
        g.setString(1, objectnummer); 
        g.setString(2, objectnummer+wildcard); 

[ Voor 37% gewijzigd door Cobalt op 08-10-2009 14:32 ]

[roeleboel]

als ik in Access de volgende query uitvoer (database is gelinkt, zelfde odbc-connectie wordt gebruikt):

SQL:

SELECT dbo_Objects.ObjectID
FROM dbo_Objects
WHERE (((dbo_Objects.ObjectNumber) Like "admi*"));

dan krijg ik inderdaad het correcte resultaat. (de 'dbo_' prefixen zijn access-specifieke toestanden)

Dus aan de odbc lijkt het me niet te liggen (Access kan wildcards doen, en vorige soortgelijke progsels kunnen het ook).

@Cobalt
Als ik het vraagteken tussen quotes zet krijg ik een nullpointer-exceptie omdat ik een waarde probeer toe te kennen aan een niet bestaande variabele binnen het preparedStatement. als ik de quotes weghaal is er geen exceptie. De ';' aan het einde heeft geen effect.

[Remus]

Wat als je het probeert met de MS SQL Server JDBC driver ipv de JDBC ODBC bridge? Overigens is % de SQL wildcard, * is mogelijk een SQL Server specifieke variant. En ja, het zou gewoon moeten werken (het doet het iig bij andere databases via JDBC).

[Snake]

roeleboel schreef op donderdag 08 oktober 2009 @ 14:44:
als ik in Access de volgende query uitvoer (database is gelinkt, zelfde odbc-connectie wordt gebruikt):

SQL:

SELECT dbo_Objects.ObjectID
FROM dbo_Objects
WHERE (((dbo_Objects.ObjectNumber) Like "admi*"));

dan krijg ik inderdaad het correcte resultaat. (de 'dbo_' prefixen zijn access-specifieke toestanden)

Dus aan de odbc lijkt het me niet te liggen (Access kan wildcards doen, en vorige soortgelijke progsels kunnen het ook).

Wat ik bedoelde is: Maak met Java connectie naar de Access Databank, dan gebruik je een andere ODBC driver als die van MSSQL.

[roeleboel]

@Remus: ik moet via de odbc connecten met de database-server (niet mijn keuze of beslissing helaas)
@Snake: dat ga ik morgenvroeg nu uittesten, even geduld voor resultaten aub

Wat verder gedebugged nog, met dit als resultaat:
dit werkt:

Java:

PreparedStatement g = conTMS.prepareStatement("SELECT objectid FROM Objects WHERE ObjectNumber LIKE '%'");

dit werkt niet:

Java:

PreparedStatement g = conTMS.prepareStatement("SELECT objectid FROM Objects WHERE ObjectNumber LIKE ?");
g.setString(1, "%");

en deze variant werkt ook niet...:

Java:

g.setString(1, "'%'");

In het kort lijkt het er op dat de omzetting van de wildcard-parameter naar het uitgevoerde statement niet goed gebeurt.
Heeft iemand een tip voor hoe ik kan zien wat er over de odbc-link gaat? (ik heb al de 'logging' proberen aanzetten in de eigenschappen van de odbc-connectie, maar dat levert op het eerste zicht niks bruikbaars op)
Een 'normale' netwerk-sniffer is helaas géén optie (beleid etc)

EDIT:
suggestie van Snake geprobeerd ondertussen, en jawel... dat werkt perfect... ('Groep 2' vanuit de startpost geeft daar een 'true')
Het ligt dus aan de odbc-driver van de mssql-server vermoed ik?
dat Access er wel mee overweg kan verbaast me nog niet zo hard aangezien dat ook een Microsoft-product is en ze de nodige tolerantie wel zullen hebben ingebouwd.

[ Voor 15% gewijzigd door roeleboel op 08-10-2009 16:33 . Reden: nieuwe tests gedaan... ]

[Snake]

Voor dat je 'm uitvoert, doe eens de toString op je preparedStatement Zo krijg je de SQL terug.

[Salandur]

roeleboel schreef op donderdag 08 oktober 2009 @ 12:16:
Situatie:

• MSSQL-server, wordt benaderd via een jdbc-odbc brug
• tabel 'objects' bestaat, met de kolommen 'objectid' (integer - 4 bytes groot) en 'objectnumber' (char, 42 karakters)
• een record met als 'objectnumber' 'admin' bestaat, bijhorend 'objectid' is 151824
• gebruikte jre & jdk zijn de java 6 update 16

Java:

....

Resultaten van aangeroepen functie:

code:

objectExists("admin", "%") => true

Dit resultaat snap ik: 'admin' komt namelijk letterlijk voor.

Omdat wildcards kunnen wisselen naargelang gebruikte server/taal/... heb ik alle (mij bekende) mogelijkheden geprobeert.

Groep 1:

code:

objectExists("admi", "*") => false
objectExists("admi", "?") => false

Groep 2:

code:

objectExists("admi", "%") => false
objectExists("admi", "_") => false

Van alle gebruikte wildcards hadden er mijn inziens 2 'true' moeten opleveren?

[...]

Kan iemand een licht werpen op wat ik hier gigantisch over het hoofd zie?

Dit zou gewoon meoten werken, maar je statement klopt voor geen meter.

Volgens mij wil je alle records ontvangen die beginnen met 'admi', dus dan moet je statement er anders uit zien.

Je probeert nu namelijk alle records op te halen die 'admi' heten, of die matchen met je wildcard.

Je goede statement dient te zijn:

Java:

  PreparedStatement g = conTMS.prepareStatement("SELECT objectid FROM objects WHERE objectnumber like ?");
  g.setString(1, objectnummer + "%");

Als je dan de aanroep doet met objectnummer = 'admi' zouden alle records die beginnen met 'admi' gevonden meoten worden.

De juiste wildcard voor MySQL moet je even opzoeken, misschien hangt dit af van de gebruikte tabel soort (InnoDB, MyISAM etc) en de MySQL versie.

Als je het voorbeeld op http://www.devdaily.com/b...redstatement-select-like/ volgt zou het gewoon moeten werken

[ Voor 19% gewijzigd door Salandur op 08-10-2009 20:39 ]

[roeleboel]

@Snake: sommige dingen zijn zo simpel dat je er niet direct aan denkt, ga ik proberen zodra ik terug op het werk ben (momenteel even ziek thuis)

@Salandur: het statement klopt wel degelijk, zoals al eerder aangehaald is dit zwaar versimpelde code om het debuggen simpeler te maken. In de 'echte' code gebeuren er een hoop mutaties op het objectnummer waar de wildcards in komen. Verder gaat het om een MSSQL en niet om een MySql. Dat voorbeeld ben ik ook al tegengekomen, maar werkt dus niet op de MSSQL, op een Access-database echter wel, wat doet vermoeden dat het aan de odbc-driver ligt.

Als ik terug op mijn werk ben ga ik eerst eens de odbc-driver nakijken, wordt vervolgd :-)

[Salandur]

je hebt gelijk. niet helemaal helder geweest gisteren.

gebruik je de juiste odbc drivers en de juiste mysql drivers? misschien dat die combi niet helemaal goed is.

[ElNillus]

Java:

// Search on criteria
query = conn.prepareStatement("SELECT spaces.*,users.name AS owner FROM spaces JOIN users ON spaces.ownerid = users.id WHERE spaces.name LIKE ? OR users.name = ? LIMIT 35;");
query.setString(1, "%" + this.criteria);
query.setString(2, this.criteria);

Zo gebruik ik LIKE met java.sql.PreparedStatement.
Ik voeg handmatig een wildcard ('%') toe aan het begin van de criteria en gebruik hem dan met PreparedStatement.setString, werkt prima.

[roeleboel]

Ok, terug van ziek geweest, nieuwe poging
- odbc driver veranderd naar versie die op andere pc perfect werkt, geen effect

- probleem nog wat vereenvoudigd:

Dit werkt:

Java:

PreparedStatement g = conTMS.prepareStatement("SELECT objectid FROM Objects WHERE ObjectNumber LIKE '%'");

Dit werkt niet:

Java:

PreparedStatement g = conTMS.prepareStatement("SELECT objectid FROM Objects WHERE ObjectNumber LIKE ?");
g.setString(1, "%");

Iemand enig idee waar ik dit moet gaan zoeken? Ik ben zowat aan het einde van mijn Latijn gekomen vrees ik

[Snake]

roeleboel schreef op maandag 12 oktober 2009 @ 11:33:
Ok, terug van ziek geweest, nieuwe poging
- odbc driver veranderd naar versie die op andere pc perfect werkt, geen effect

- probleem nog wat vereenvoudigd:

Dit werkt:

Java:

PreparedStatement g = conTMS.prepareStatement("SELECT objectid FROM Objects WHERE ObjectNumber LIKE '%'");

Dit werkt niet:

Java:

PreparedStatement g = conTMS.prepareStatement("SELECT objectid FROM Objects WHERE ObjectNumber LIKE ?");
g.setString(1, "%");

Iemand enig idee waar ik dit moet gaan zoeken? Ik ben zowat aan het einde van mijn Latijn gekomen vrees ik

Again: wat zegt toString na de setString?

[roeleboel]

@Snake, juist ik wist dat ik nog iets te beantwoorden had, nog niet tegoei wakker vrees ik

toString op het preparedstatement geeft deze leuke melding:

Java:

g.toString();

sun.jdbc.odbc.JdbcOdbcPreparedStatement@1b10d42
Ik heb de api voor preparedstatement al doorlopen, maar daar zit zo te zien niets bij dat de query teruggeeft.

-EDIT-
Nog wat info:

• als ik de query uitvoer met '%' dan duurt deze minstens 2,5 seconden
• als ik de query uitvoer met '*' dan duurt deze maximum 0,04 seconden

Het lijkt er (imho) op dat de wildcard toch goed wordt doorgegeven (anders zou de query merkelijk minder lang moeten duren), maar dat er iets mis gaat met het teruggeven van de resultset.

De duurtijd is gemeten met behulp van System.currentTimeMillis() op te vragen vlak voor & na uitvoering query, en het verschil te berekenen, niet direct super-exact, maar de verschillen hierboven zijn groot genoeg.
Ik heb beide query's meerdere malen door elkaar uitgevoerd om grote verschillen omwille van caching uit te sluiten.

[ Voor 50% gewijzigd door roeleboel op 12-10-2009 12:32 . Reden: nieuwe info ]

[BalusC]

[quote]roeleboel schreef op donderdag 08 oktober 2009 @ 12:16:
• MSSQL-server, wordt benaderd via een jdbc-odbc brug
Da's al een enorme fout op zich. Gebruik een echte MSSQL JDBC driver. Je kunt er 1 van microsoft.com neerhalen. De ODBC bridge driver is rijk aan bugs, je wilt niet weten wat daar allemaal in zit.