Discussion :

[wazalop]

Bonsoir (ou bonjour ^^),

je viens poster un gros message pour demander de l'aide sur une requete que je n'arrive pas a créer.

c'est pour un ptit logiciel de recherche personnel, (je rempli la base grace a un parseur web avec jsoup et ui4j)

j'ai une base de donnée sqlite, avec les tables :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
`tags` (idTag,nameTag);
`artists` (idArtist,nameArtist);
`li_ItemTag` (id,idItem,idTag);
`li_ItemArtist` (id,idItem,idArtist);
`li_ItemPersonnage` (id,idItem,idPersonnage);
`li_ItemAuthor` (id,idItem,idAuthor);
`items` (idItem,nameItem,typeItem,nbChapitres,link);
`personnages` (idPersonnage,namePersonnage);
`authors` (idAuthor,nameAuthor);

item correspond aux object a trier.(l'object Items contient un id,nom,nbChapitre,link et des list<Object> pour les auteurs,tags,etc...)
un item peux avoir aucun ou plusieurs tags,artist,auteur,et personnage d'ou les table li_

je souhaite faire une recherche :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
public List<Items> getItemsBymultiple(String name, 
            String type, 
            List<Authors> authors, 
            List<Artists> artists, 
            List<Tags> tags, 
            List<Personnages> personnages);

chaque argument peut etre vide ou non.
je penser donc faire une boucle for sur chaque liste pour completer la requete en fonction du nombre d'element de chaque liste.
et un simple if sur le type (sachant que le name ne sert que pour un LIKE qu'il vale "" n'est pas genant non?)

quelque chose comme cela :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
if(!tags.isEmpty()){
             whereTag += " AND i.id = t.idItem";
            joinTag += " LEFT JOIN li_ItemTag t ON t.idTag =";
            for(int i = 0;i<tags.size();i++){
                if(i==0){
                    joinTag += " "+tags.get(i).getId()+" ";
                }else{
                    joinTag += "OR a.idTag = "+tags.get(i).getId()+" ";
                }
            }
        }

mais ca je devrai pouvoir me demerder a le faire ^^ (si c la bonne methode ^^).

le soucis c'est donc de reussir a modeliser ma requete :s

je voudrai recuperer les valeurs de la table items, ainsi que des listes des auteurs,tags,etc.. correspondant au criteres passé dans la fonction.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
           "SELECT * FROM items i"
                    + " LEFT JOIN li_ItemAuthor a ON a.idAuthor = :author etc..." // ici la fameuse boucle for du dessus :p
                    + joinTag // exemple d'utilisation de la boucle for du dessus.
                    + " WHERE i.name LIKE :name"
                        + " AND i.type = :type" // cet ligne ne serait ajouter que si la valeur de type de la fonction n'est pas ""
                        + " AND i.id = a.idItem" // pareil qu'au dessus mais pour les auteurs cet fois.
                        + whereTag + ""; // exemple d'utilisation de la boucle for du dessus.

en gros avoir en resultset : [idItem,nameItem,typeItem,nbChapitres,link,nameAuthor1,...,nameAuthorX,nameTagX,namePersonnageX,nameArtistX]* x resultat.

je suis perdu ici, cela fait un moment que je n'ais pas utiliser sql, j'ai deja galerer a me souvenir du fonctionnement des jointures ^^

avant de finir de griller mon cerveaux, j'espere avoir des conseils de votre part xD (voir meme des insultes pour avoir completement foirer ma requete? x) )

j'espere avoir été assez claire sur l'explication ^^

je vous souhaite une bonne nuit a tous

[OButterlin]

Dans la mesure où tu ne veux qu'une liste d'Item, pourquoi faire des jonctions ?
Personnellement, je ferais quelque chose comme ça

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
 
public List<Items> getItemsBymultiple(String name, String type, List<Authors> authors, List<Artists> artists, List<Tags> tags, List<Personnages> personnages)
{            
    StringBuilder sb = new StringBuilder();
 
    sb.append("select * from Item a where 1=1");
 
    if (type != null)
    {
        sb.append(" and typeItem = '").append(type).append("'");
    }
 
    if (authors != null && authors.size() > 0)
    {
        sb.append(" and a.id in (select idItem from li_ItemAuthor where idAuthor in (");
        for (Authors author : authors)
        {
            sb.append(author.getId()).append(", ");
        }
        sb.replace(sb.length()-2, sb.length(), ")");
    }
 
    if (artists != null && artists.size() > 0)
    {
        sb.append(" or a.id in (select idItem from li_ItemArtist where idArtist in (");
        for (Artists artist : artists)
        {
            sb.append(artist.getId()).append(", ");
        }
        sb.replace(sb.length()-2, sb.length(), ")");
    }
    ...
 
    PreparedStatement pstmt = connection.prepareStatement(sb.toString());
    ...
 
}

Là c'est brut de fonderie, il y a un risque d'injection en procédant comme ça, tu devrais passer par des paramètres, mais c'est pour te montrer une piste

[wazalop]

merci pour les infos,

en relisant des texte sur les jointure je me suis rendu compte de mes erreurs je pense ^^

pour les jointures c'est parceque je veux aussi afficher les auteurs,tag de l'item en question et donc cree les liste contenu dans l'item
exemple de mon item :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
 voila ce que contient un item :
id = 1
name = "One Piece"
type = "mangas"
nbChapitre = 841
link = "http://a.site.com/" //je recup l'adresse ici car le site utilise plusieurs sous domaine.
List<Authors> authors
List<Tags> tags
List<Artists> artists
List<Personnages> Personnages

en gros je recupere le resultat de ma requete et fait :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
 
Items item = new Items(id,name,type,nbChapitre,link);
List<Tags> tags = new ArrayList<>();
for(chaque tags) tags.add(new Tags(nameTag));
item.addTags(tags);
listItem.add(item);

et a partir de ca je peux afficher les infos qui m'interresse.

en gros j'aurai une requete du type :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
SELECT * FROM items i
 
LEFT JOIN li_ItemTags it ON i.idItem = it.idItem
LEFT JOIN tags t ON t.idTag = it.idTag
 
LEFT JOIN li_ItemArtist iar ON i.idItem = iar.idItem
LEFT JOIN artists ar ON ar.idartist  = iar.idartist 
 
LEFT JOIN li_ItemAuthor iau ON i.idItem = iau.idItem
LEFT JOIN Authors au ON au.idAuthor = iau.idAuthor
 
LEFT JOIN li_ItemActor iac ON i.idItem = iac.idItem
LEFT JOIN personnages ac ON ac.idPersonnage = iac.idPersonnage
 
WHERE i.nameItem LIKE ?
si(...) AND i.typeItem = ?
si(...) AND (it.idTag = ? OR it.idTag = ? OR etc...)
si(...) AND (iar.idArtist = ? OR iar.idArtist= ? OR etc...)
si(...) AND (iac.idPersonnage = ? OR iac.idPersonnage = ? OR etc...)
si(...) AND (iau.idAuthor = ? OR iau.idAuthor = ? OR etc...)
 
GROUP BY i.idItem

la requete semble bonne non?

qu'entend tu par des parametres? utiliser les ? dans le texte des boucles comme au dessus?

j'arrive pas a trouver d'exemple de requete avec un nombre de parametres variable en java,
quelques exemples en php qui utlise le meme principe de boucle pour completer la requete avec les valeur defini dans les boucles.

les valeurs pour le type et les list seront recuperer depuis des JComboBox (donc normalement non modifiable par l'utilisateur?) le seul item modifiable par l'utilisateur pour cet requete sera le nom demander qui sera recuperer depuis un JTextField.

je penser d'ailleur laisser le name = ? et utiliser un setString(1,name) juste pour lui et les autre parametre rentrer lors des boucles.

[wazalop]

bonsoir,

apres avoir fait plusieurs test avec la requete du dessus,

je pense avoir reussit a faire ma requete meme si c'est moche ^^

par contre jsuis emmerdé pour recup les differentes valeurs :s

par exemple pour les tags, comment recuperer tout les tag de l'item? si je fait getString ca me donne qu'un seul tag.

le getArray semble ne pas etre implementé avec sqlite?

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
not implemented by SQLite JDBC driver
java.sql.SQLException: not implemented by SQLite JDBC driver
id trouver : 1214586
nom de l'id : Test4
auteur1
	at org.sqlite.jdbc4.JDBC4ResultSet.unused(JDBC4ResultSet.java:333)
	at org.sqlite.jdbc4.JDBC4ResultSet.getArray(JDBC4ResultSet.java:342)
	at com.tunis.hitomiSql.data.implementations.DataImpl.getItemsBymultiple(DataImpl.java:271)
	at com.tunis.hitomiSql.Launch.main(Launch.java:92)

le code :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
while(result.next()){
                System.out.println("id trouver : " + result.getInt("idItem"));
                System.out.println("nom de l'id : " + result.getString("nameItem"));
                System.out.println(result.getString("nameAuthor"));
                System.out.println(result.getArray("nameTag"));
            }

la ligne 271 est bien la ligne getArray()

la requete est celle du post precedent. sans les ? je regarderai pour remettre les ? lorsque j'aurai tester les setArray(1, array); si c'est bien ce que je pense ou pas ^^ (on mets tout nos paramettres dans l'array dans l'ordre ou il apparaissent dans la requete et c good?)

si je ne met pas le getArray mais un simple getString je recupere bien un tag et tout les items qui correspondent a ma requete.


va t'il faloir que je tente avec une autre bdd?


Edit :

apres d'autre test, la requete ne me retourne pas tous les tag possible pour l'item trouvé :s

voici les label et type des colone retourner :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
idItem - > INTEGER
nameTag - > TEXT
nameAuthor - > TEXT
nameItem - > TEXT
typeItem - > TEXT

la requete me retourne bien les bon items selon tous les criteres, du coup pour recuperer tous les tag possible suis je obliger de faire une nouvelle requete en prenant l'id de l'item pour recuperer tous les tag de cet item?

[wazalop]

Bonsoir a tous,


voila apres plusieurs test j'ai enfin un resultat satisfaisant.

pour ceux que ca peux intereser :

fonction principale :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
 
public List<Items> getItemsBymultiple(String name, List<String> type, List<Integer> authors, List<Integer> tags, List<Integer> artists, List<Integer> personnages) {
        try {
            List<Items> items = new ArrayList<>();
            String sql = "SELECT i.idItem, i.nameItem, i.typeItem, i.nbChapitres, i.link, i.date "
                    + "FROM items i "
                    + "LEFT JOIN li_ItemTag it ON i.idItem = it.idItem "
                    + "LEFT JOIN tags t ON t.idTag = it.idTag "
                    + "LEFT JOIN li_ItemArtist lis ON i.idItem = lis.idItem "
                    + "LEFT JOIN artists s ON s.idArtist  = lis.idArtist "
                    + "LEFT JOIN li_ItemAuthor ia ON i.idItem = ia.idItem "
                    + "LEFT JOIN Authors a ON a.idAuthor = ia.idAuthor "
                    + "LEFT JOIN li_ItemPersonnage ip ON i.idItem = ip.idItem "
                    + "LEFT JOIN personnages p ON p.idPersonnage = ip.idPersonnage ";
            // faire whereTruc ici :
            String where = setWhere(name, type, authors, tags, artists, personnages);
 
            sql = sql + where + "GROUP BY i.idItem";
            System.out.println(sql);
            sttm = conn.prepareStatement(sql);
            // faire setParam ici :
            setParams(name, type, authors, tags, artists, personnages);
 
            result = sttm.executeQuery();            
            while(result.next()){
                Items item = new Items(result.getInt("idItem"),
                        result.getString("nameItem"),
                        result.getString("typeItem"),
                        result.getInt("nbChapitres"),
                        result.getString("link"),
                        result.getTimestamp("date"));
                item.setAuthors(getAuthorsByItem(item));
                item.setTags(getTagsByItem(item));
                item.setArtists(getArtistsByItem(item));
                item.setPersonnages(getPersonnagesByItem(item));
                items.add(item);                
            }
            return items;
        } catch (SQLException ex) {
            Logger.getLogger(DataImpl.class.getName()).log(Level.SEVERE, null, ex);
            return null;
        }
    }

le setWhere :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
private String setWhere(String name, List<String> type, List<Integer> authors, List<Integer> tags, List<Integer> artists, List<Integer> personnages){
        String sql = "";
 
        if(!name.isEmpty())
            sql += "WHERE i.nameItem LIKE ? ";
        if(!type.isEmpty()){
            for(int i = 0; i < type.size(); i++){
                if(i == 0 && name.isEmpty())
                    sql += "WHERE (i.typeItem LIKE ? ";
                else sql += "OR i.typeItem LIKE ? ";
            }
            sql += ") ";
        }
        if(!tags.isEmpty()){
            for(int i = 0; i < tags.size(); i++){
                if(i == 0 && name.isEmpty() && type.isEmpty())
                    sql += "WHERE (it.idTag = ? ";
                if(i== 0 && (!name.isEmpty() || !type.isEmpty()))
                    sql += "AND (it.idTag = ? ";
                else sql += "OR it.idTag = ? ";
            }
            sql += ") ";
        }
        if(!authors.isEmpty()){
            for(int i = 0; i < authors.size(); i++){
                if(i == 0 && name.isEmpty() && type.isEmpty() && tags.isEmpty())
                    sql += "WHERE (ia.idAuthor = ? ";
                if(i== 0 && (!name.isEmpty() || !type.isEmpty() || !tags.isEmpty()))
                    sql += "AND (ia.idAuthor = ? ";
                else sql += "OR ia.idAuthor = ? ";
            }
            sql += ") ";
        }        
        if(!artists.isEmpty()){
            for(int i = 0; i < artists.size(); i++){
                if(i == 0 && name.isEmpty() && type.isEmpty() && tags.isEmpty() && authors.isEmpty())
                    sql += "WHERE (is.idArtist = ? ";
                if(i== 0 && (!name.isEmpty() || !type.isEmpty() || !tags.isEmpty() || !authors.isEmpty()))
                    sql += "AND (is.idArtist = ? ";
                else sql += "OR is.idArtist = ? ";
            }
            sql += ") ";
        }        
        if(!personnages.isEmpty()){
            for(int i = 0; i < personnages.size(); i++){
                if(i == 0 && name.isEmpty() && type.isEmpty() && tags.isEmpty() && authors.isEmpty() && artist.isEmpty())
                    sql += "WHERE (ip.idPersonnage = ? ";
                if(i== 0 && (!name.isEmpty() || !type.isEmpty() || !tags.isEmpty() || !authors.isEmpty() || !artist.isEmpty()))
                    sql += "AND (ip.idPersonnage = ? ";
                else sql += "OR ip.idPersonnage = ? ";
            }
            sql += ") ";
        } 
        return sql;
    }

le setParams :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
private void setParams(String name, List<String> type, List<Integer> authors, List<Integer> tags, List<Integer> artists, List<Integer> personnages) throws SQLException{
        int index = 1;
        if(!name.isEmpty()){
            sttm.setString(index, name);
            index++;
        }
        if(!type.isEmpty()){
            for(int i = 0; i < type.size(); i++){
                sttm.setString(index, type.get(i));
                index++;
            }
        }
        if(!tags.isEmpty()){
            for(int i = 0; i < tags.size(); i++){
                sttm.setInt(index, tags.get(i));
                index++;
            }
        }
        if(!authors.isEmpty()){
            for(int i = 0; i < authors.size(); i++){
                sttm.setInt(index, authors.get(i));
                index++;
            }
        }        
        if(!artists.isEmpty()){
            for(int i = 0; i < artists.size(); i++){
                sttm.setInt(index, artists.get(i));
                index++;
            }
        }        
        if(!personnages.isEmpty()){
            for(int i = 0; i < personnages.size(); i++){
                sttm.setInt(index, personnages.get(i));
                index++;
            }
        }
    }

et pour recuperer les liste complete plusieurs fonction du type :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
private List<Tags> getTagsByItem(Items item) {
        List<Tags> tags = new ArrayList();
        try {
            sttm2 = conn.prepareStatement("SELECT * FROM tags t LEFT JOIN li_ItemTag it ON it.idTag = t.idTag WHERE it.idItem = ?");
            sttm2.setInt(1, item.getId());
            result2 = sttm2.executeQuery();
            while(result2.next()){
                tags.add(new Tags(result2.getInt("idTag"), result2.getString("nameTag")));
            }
            return tags;
        } catch (SQLException ex) {
            Logger.getLogger(DataImpl.class.getName()).log(Level.SEVERE, null, ex);
            return null;
        }
    }

voila voila, j'ai essayé le setArray pour les parametres mais ce n'est pas implementer :'(

je marque le sujet comme resolut, mon plus gros probleme etant resolu, par contre vaut il mieu utiliser un string builder comme dans ton example ou un simple += suffit? (j'ai utiliser le += ^^)
et pareil pour l'ajout des listes de tags etc... c'est mieu de le faire comme j'ai fait la, dirrectement dans la boucle du premier resultset, ou bien de le faire apres a part? (y'a t'il une vrai difference?)


Merci pour ton aide OButterlin

bonne soirée a tous.

ps : en me relisant apres avoir poster, j'ai remarquer dans la premiere requete je peux enlever les LEFT JOIN des table non li_ ^^

[OButterlin]

Personnellement, j'utilise StringBuilder qui me paraît plus adapté à l'ajout d'éléments de types différents à une chaine de caractères mais d'un point de vue performance, il semblerait que le compilateur soit capable de transformer une suite de "String1" + "String2" + "String3" en StringBuilder.append...

Sinon, sur le fond, je ne pense pas que ton code soit optimisé dans la mesure où tes jonctions vont créer un produit cartésien entre ta table items et les tables filles (dans tes left join ou join)... surtout que derrière tu refais des requêtes pour extraire les données des tables jointes...
Il faudrait traiter les "ruptures" dans la jointure pour charger les données liées... ou plus simplement supprimer les jointures comme je te l'avais suggéré et faire tes extractions connexes comme actuellement.