Loop MySQL run with java -

i have 3 tables having following content :

author idauthor int name varchar  publication idpublication int title varchar date year type varchar conference  author_has_publication author_idauthor int publication_idpublication int 

i trying relational schema on authors. objectif show number of publication have in common. authors name parameters, can have 8 names. code giving number of common publication between 2 authors, have loop it. using java loop , sql statement that. here sql part

private int runquery(string a1, string a2){ // a1 author 1 , a2 author 2         try {             auth1 = new arraylist<string>();             class.forname("com.mysql.jdbc.driver");             connection connection = drivermanager.getconnection(                     "jdbc:mysql://localhost:3306/mydb", "root", "root");             statement stmt = connection.createstatement();             long start = system.currenttimemillis();               string queryupdate1 = "drop table if exists temp1;";             string queryupdate2 = "drop table if exists temp2;";             string queryupdate3 = "create temporary table if not exists temp1 (select author.name, publication.idpublication, publication.title author inner join author_has_publication on author_has_publication.author_idauthor=author.idauthor inner join publication on author_has_publication.publication_idpublication=publication.idpublication author.name='"+ a1+"');";             string queryupdate4 = "create temporary table if not exists temp2 (select author.name, publication.idpublication, publication.title author inner join author_has_publication on author_has_publication.author_idauthor=author.idauthor inner join publication on author_has_publication.publication_idpublication=publication.idpublication author.name='"+ a2+"');";             string query = "select count(*) (select temp1.title temp1 inner join temp2 on temp1.idpublication = temp2.idpublication) t;";              stmt.executeupdate(queryupdate1);             stmt.executeupdate(queryupdate2);             stmt.executeupdate(queryupdate3);             stmt.executeupdate(queryupdate4);             resultset rs = stmt.executequery(query);             int result = -1;             while (rs.next()) {                 result = rs.getint(1);             }              system.out.println("result = " + result);             long end = system.currenttimemillis() - start;             querytimelabel.settext("query execution time :"+end);             connection.close();             return result;         } catch (exception e) {             system.out.println(e);         }         return -1;     } 

here loop part (to repeat sql when there more 2 authors given) , generate graph :

public void actionperformed(actionevent e) {      graph = new mxgraph();     object parent = graph.getdefaultparent();     authvertex = getauthors();        // ///////////////////////////////////     // creates graph, graph shows after resize window     graph.getmodel().beginupdate();     try {          int = 0;         for(string a: authvertex.keyset()){             int j = 0;             for(string b: authvertex.keyset()){                 if(j > i) {                     graph.insertedge(parent, null, string.valueof(runquery(a,b)), authvertex.get(a), authvertex.get(b)); // loop sql statement 2 2.                 }                 j++;             }             i++;         }     } {         graph.getmodel().endupdate();     }      graphcomponent = new mxgraphcomponent(graph);     graphpan.removeall();     graphpan.add(graphcomponent);     setvisible(true);     // /////////////////////////////////////////   } 

my code working, know if possible increase performance passing mysql, means enter authors name in parameter , loop hangled mysql, check mysql procedure issue how handle authors names parameter variable.

one way, in single statement:

select  count(*)      author_has_publication ap1     join  author_has_publication ap2  on ap1.publication_idpublication =                                             ap2.publication_idpublication     join  author a1  on ap1.author_idauthor = a1.id_author     join  author a2  on ap2.author_idauthor = a2.id_author      a1.name = '...'       ,  a2.name = '...'  

another way may be

select  count(*)           (         select  ahp.publication_idpublication, count(*)              author_has_publication ahp             join  author  on a.id_author = ahp.author_idauthor              a.name in ('...', '...')             group  ahp.publication_idpublication             having  count(*) = 2   -- number of authors     ) x  

composite indexes needed:

author_has_publication:  (author_idauthor, publication_idpublication) author_has_publication:  (publication_idpublication, author_idauthor) author:  (name, id) 

note: each technique can rather extended more 2 authors. second query adapted "at least 3 of these 5 authors": 5 names in in , having count(*) >= 3.