Prevent Multiple MySql Connection

Hi there,

seems you're issuing one request per insert.
Can you adjust the code on the client to consolidate the inserted data and
insert multible rows at a time like

insert into TABLE (`field1`,`field2`,`field3`,`field4`)
VALUES
('data-1-1','data-1-2','data-1-2','data-1-3'),
('data-2-1','data-2-2','data-2-2','data-2-3'),
...
('data-n-1','data-n-2','data-n-2','data-n-3');


The maximum rows you can insert is only limited by the max query size.


--TheDoctor

hi gurvinder372 and DocSeltsam

for  gurvinder372 :--   i m working on Web-Application and i m not using connection pooling and any type of other mechanism..

for DocSeltsam :--- no, its not possible to add all rows in same time .


Actually i make a class like dbConnection with the help of this i m getting my connection in all other classes which need it...

Hi there,

if you can't consolidate the inserts, I'd suggest you'll have a look at

http://de3.php.net/manual/en/function.mysql-pconnect.php


--TheDoctor

Sachin_Ajmani:

Can you show the Java code in which you are getting the dbConnection.  Most likely you are getting a connection before each insert in the code.  What you will want is open one connection (at least per client if not as you said persisted for application) and then iterate inserts then close the connection.  If using one, you will want some sort of synchronization or way to lock the connection when busy for another client.

When I need this scenario, I typically change the signature of the class that gets called to take in a dbConnection instead of creating its own; therefore, I can open a connection before the pertinent code and pass and already opened connection around ensuring a new one is not created on each call to my action methods.

M-1

hi mwvisa1

 my db class is attach below, and in my every class in which i need db connection i m using
this code in constructor to get the connection...

       MySQLConnection  mycon = new MySQLConnection();
       mycon.init();
       conn = mycon.getMyConnection();
       

import java.sql.*; 
 
public class MySQLConnection { 
    private Connection myConnection; 
    /** Creates a new instance of MyDBConnection */
    public MySQLConnection()
    {
        
    } 
    public void init() throws SQLException{ 
       try{ 
        Class.forName("com.mysql.jdbc.Driver");
myConnection=DriverManager.getConnection("jdbc:mysql://localhost:3306/db?","user", "pass"); 
        }
        catch(Exception e)
        { 
        e.printStackTrace();
        System.out.println("Unable to connect Database"); 
        }
    } 
       public void geoinit() throws SQLException{ 
       try{ 
        Class.forName("com.mysql.jdbc.Driver"); 
myConnection=DriverManager.getConnection("jdbc:mysql://localhost:3306/db?","user", "pass"); 
        }
        catch(Exception e)
        { 
        e.printStackTrace();
        System.out.println("Unable to connect Database"); 
        }
    } 
public Connection getgeoMyConnection(){
        return myConnection;
    }
    public Connection getMyConnection(){
        return myConnection;
    } 
 public void close(ResultSet rs){ 
        if(rs !=null){
            try{
               rs.close(); 
            }
            catch(Exception e){ } 
        }
    } 
     public void close(java.sql.Statement stmt){ 
        if(stmt !=null){
            try{
               stmt.close(); 
            }
            catch(Exception e){} 
        }
    } 
  public void destroy(){ 
    if(myConnection !=null){ 
         try{
               myConnection.close(); 
            }
            catch(Exception e){} 

    }
  } 
}

                                              

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:
56:
57:
58:
59:
60:
61:
62:
63:
64:
65:
66:
67:
68:
69:
70:
71:
72:
73:
74:
75:
76:
77:
78:
79:
80:
81:
82:
83:
84:
85:
86:
87:
88:
89:
90:
91:
92:
93:
94:
95:

if i want to insert data then i directly used

stmt.executeUpdate("insert data to xyz");

and if i want to get data then i used

stmt = conn.createStatement();
rs = stmt.executeQuery("select data from xyz");

Looks like you are calling init or geoinit method every time for doing a database operation. Because of this a new connection is being created.

You can try one of these 2 approaches

1)Instead of calling this method every time for a db operation, you can initialize a data-structure in the beginning of the application to store the pool of the connection objects and store it in the cache.
Every time a new db operation is requested, a Connection Object from the connection pool (a data-structure), will be fetch and used. At the end of the transaction the commit will be done and Connection object will be returned to the connection pool.
Visit http://commons.apache.org/dbcp/ for more info on Connection Pooling.

2) You can synchronize the methods of the class that access the db. This way only one user/thread will be able to access those methods and only one object will be created.

I would recommend first option, as it a more elegant and widely used approach.

hi mwvisa1

no, i m not obfuscate my code...

it was my typing mistake and they are both different method one is init() and 2nd is geoinit()  .

they both are using to connect different schema......
e.g one is using to connect db and 2nd is db1

thanks