You are viewing a plain text version of this content. The canonical link for it is here.
Posted to jetspeed-user@portals.apache.org by Stephane Grialet <St...@ac-toulouse.fr> on 2001/05/15 16:27:29 UTC

MySql connection issues

Hello,

i have to set a little MySql database (only two tables) in my portal.
I have created these two tables in the "Turbine" already existing database context.
Is it a good choice ?

To connect, i use the following code portion in my Java programs:

<
 db = TurbineDB.getConnection();
 con = db.getConnection();
>

It works well, but is it a correct way to do it ?

Then when i want to query the db, i use :

<
 stmt = con.createStatement();
 rs = stmt.executeQuery("SELECT * FROM <mytable> WHERE <mycondition>");
 while (rs.next()) {
    <get and process data...>
 }
 rs.close();
 stmt.close();
 con.close();
>

It works fine for one query, but it fails (NullPointerException) if i try to make two queries one after the other like that:

<
 db = TurbineDB.getConnection();
 con = db.getConnection();
 stmt = con.createStatement();
 rs = stmt.executeQuery("<first query>");
 while (rs.next()) {
    <get and process 1st query data...>
 }
 rs.close();
 stmt.close();
 con.close();
 con = db.getConnection();
 stmt = con.createStatement();
 rs = stmt.executeQuery("<second query>");
 while (rs.next()) {
    <get and process 2nd query data...>
 }
 rs.close();
 stmt.close();
 con.close();
 db.close();
>

I don't understand why.
The only way i could have it worked was by not closing the "connection objects" between queries, like that:

<
 db = TurbineDB.getConnection();
 con = db.getConnection();
 stmt = con.createStatement();
 rs = stmt.executeQuery("<first query>");
 while (rs.next()) {
    <get and process 1st query data...>
 }
 rs = stmt.executeQuery("<second query>");
 while (rs.next()) {
    <get and process 2nd query data...>
 }
 rs.close();
 stmt.close();
 con.close();
>

Is it the correct way to make multiple queries on same or different tables of one database ?
Should i better use different objects for each db statement (con1, con2,..., stmt1, stmt2,..., rs1, rs2,...) to achieve it ?
does it work similarly for other db statements than the select one (update, insert, delete,...) ?

thanks for your help,
Stéphane