Re: [SPAM] - Re: [SPAM] - Re: [SPAM] - Re: JDBC HighLoad -

i don't understand you because it's this class that create the pool if i
instanciate it a hundred time i'll get a hundred pools, no ?
Do you mean i have to separate the pool and SQLoader class ?
Can you point me to a good tutorials on pooling connection or show me a
little example ?
Thank you

Dave Cramer wrote:

> The pooling mechanism will take care of this; which brings up another
> point. The internal pooling implementation is not production class.
> Have a look at apache's dbcp, it is much better.
>
> I think this works fine as long as it isn't a singleton. Instantiate
> the class, get a connection from the pool, do your inserts/updates,
> return the connection and you're done. I'd probably create the sql
> strings statically, there's no need for more than one instance of them.
>
> Dave
>
> Stéphane RIFF wrote:
>
>> Dave Cramer wrote:
>>
>>> Because every time you do pstmt_xxx = c.prepareStatement.... you are
>>> over writing the previous one.
>>>
>>> Dave
>>>
>>> Stéphane RIFF wrote:
>>>
>>>> Dave Cramer wrote:
>>>>
>>>>> Stephane,
>>>>>
>>>>> You are using this class as a singleton in a multithreaded
>>>>> environment ???
>>>>>
>>>>>
>>>>> Dave
>>>>>
>>>>> Stéphane RIFF wrote:
>>>>>
>>>>>> Stéphane RIFF wrote:
>>>>>>
>>>>>>> Hi i have a java aplication which connect to postgresql via jdbc.
>>>>>>> This app make a lot of request per second.
>>>>>>> The problem is that i get delayed records between the moment the
>>>>>>> informations arrived via socket to java and
>>>>>>> the moment it's recorded in the database. It seems that jdbc
>>>>>>> wait before inserting/updating the db because
>>>>>>> they are to much requests.
>>>>>>> For testing i made a little app in c using libpq to see if
>>>>>>> problem persist and there no delay.
>>>>>>>
>>>>>>> So i'd like to know a way to speed up jdbc if possible ???
>>>>>>>
>>>>>>>
>>>>>> Here my connection class, is there something wrong ?
>>>>>>
>>>>>> import java.io.*;
>>>>>> import java.util.*;
>>>>>> import java.sql.*;
>>>>>> import java.text.*;
>>>>>> import javax.sql.DataSource;
>>>>>> import org.postgresql.jdbc3.Jdbc3PoolingDataSource;
>>>>>>
>>>>>> import org.apache.log4j.Logger;
>>>>>> import org.apache.log4j.PropertyConfigurator;
>>>>>>
>>>>>> public class SQLoader {
>>>>>>      private Jdbc3PoolingDataSource datasource ;
>>>>>>    //query object gps
>>>>>>    private PreparedStatement pstmt_gps ;
>>>>>>    //query object io
>>>>>>    private PreparedStatement pstmt_io ;
>>>>>>    //query object gps
>>>>>>    private PreparedStatement pstmt_ugps ;
>>>>>>    //query object io
>>>>>>    private PreparedStatement pstmt_uio ;
>>>>>>    //query object gps
>>>>>>    private PreparedStatement pstmt_hgps ;
>>>>>>    //query object io
>>>>>>    private PreparedStatement pstmt_hio ;
>>>>>>    //singleton class
>>>>>>    private final static SQLoader myRef = new SQLoader();
>>>>>>    //Log4j logger object
>>>>>>    private static Logger logger =
>>>>>> Logger.getLogger(SQLoader.class.getName());
>>>>>>    //jdbc driver
>>>>>>    private String driver ;
>>>>>>    //jdbc connection string
>>>>>>    private String jdbcConnectionString ;
>>>>>>    //database user
>>>>>>    private String dbUser ;
>>>>>>    //database password
>>>>>>    private String dbPwd ;
>>>>>>      /**
>>>>>>     * Private constructor
>>>>>>     *
>>>>>>     * @since           1.0
>>>>>>     */
>>>>>>    private SQLoader() {}
>>>>>>      /**
>>>>>>     * Give access to the unique instance of this class
>>>>>>     *
>>>>>>     * @return          The instance
>>>>>>     * @since           1.0
>>>>>>     */
>>>>>>    public static SQLoader getRef()
>>>>>>    {
>>>>>>        return myRef ;
>>>>>>    }
>>>>>>      public void connect(String driver,String dbc, String dbu,
>>>>>> String dbp)
>>>>>>    {
>>>>>>        Connection m_conn = null;
>>>>>>        try {
>>>>>>            datasource = setupDataSource(dbc);
>>>>>>        }catch(Exception e){
>>>>>>            logger.fatal(e.toString());
>>>>>>            System.exit(-1);
>>>>>>        }
>>>>>>    }
>>>>>>      private void prepareQuery(Connection c)
>>>>>>    {
>>>>>>        try
>>>>>>        {
>>>>>>            //Construct predefined query
>>>>>>            String qry = "INSERT INTO gps_frame (" +
>>>>>>                            "\"sbox_id\"," +
>>>>>>                            "\"gps_date\"," +
>>>>>>                            "\"badge_id\"," +
>>>>>>                            "\"gmt_creation_date\"," +
>>>>>>
>>>>>> "\"wgs84_position\","+
>>>>>>                            "\"speed\"," +
>>>>>>                            "\"altitude\","+
>>>>>>                            "\"heading\","+
>>>>>>                            "\"validity\"," +
>>>>>>                            "\"geom\")"
>>>>>> +                                                      " VALUES(
>>>>>> ?,?,?,?,?,?,?,?,?,?)";
>>>>>>            pstmt_gps = c.prepareStatement(qry);
>>>>>>
>>>>>>            String qry1 = "INSERT INTO io_frame ("+
>>>>>>                            "\"sbox_id\","+
>>>>>>                            "\"gps_date\","+
>>>>>>                            "\"io_type\","+
>>>>>>                            "\"io_rank\","+
>>>>>>                            "\"io_value\")"+
>>>>>>                            " VALUES( ?,?,?,?,?)";
>>>>>>            pstmt_io = c.prepareStatement(qry1);
>>>>>>
>>>>>>            String uqry = "UPDATE gps_frame SET "+
>>>>>>                            "\"gps_date\"=?,"+
>>>>>>                            "\"badge_id\"=?,"+
>>>>>>                            "\"gmt_creation_date\"=?,"+
>>>>>>                            "\"wgs84_position\"=?,"+
>>>>>>                            "\"speed\"=?,"+
>>>>>>                            "\"altitude\"=?,"+
>>>>>>                            "\"heading\"=?,"+
>>>>>>                            "\"validity\"=?,"+
>>>>>>                            "\"geom\"=?"+
>>>>>>                            " WHERE \"sbox_id\"=?";
>>>>>>            pstmt_ugps = c.prepareStatement(uqry);
>>>>>>
>>>>>>            String uqry1 = "UPDATE io_frame SET "+
>>>>>>                            "\"gps_date\"=?,"+
>>>>>>                            "\"io_value\"=?"+
>>>>>>                            " WHERE \"sbox_id\"=? AND
>>>>>> \"io_rank\"=? AND io_type=?";
>>>>>>            pstmt_uio = c.prepareStatement(uqry1);
>>>>>>
>>>>>>            qry = "INSERT INTO gps_frame_history (" +
>>>>>>                            "\"sbox_id\"," +
>>>>>>                            "\"gps_date\"," +
>>>>>>                            "\"badge_id\"," +
>>>>>>                            "\"gmt_creation_date\"," +
>>>>>>
>>>>>> "\"wgs84_position\","+
>>>>>>                            "\"speed\"," +
>>>>>>                            "\"altitude\","+
>>>>>>                            "\"heading\","+
>>>>>>                            "\"validity\"," +
>>>>>>                            "\"geom\")"
>>>>>> +                                                      " VALUES(
>>>>>> ?,?,?,?,?,?,?,?,?,?)";
>>>>>>            pstmt_hgps = c.prepareStatement(qry);
>>>>>>
>>>>>>            qry1 = "INSERT INTO io_frame_history ("+
>>>>>>                            "\"sbox_id\","+
>>>>>>                            "\"gps_date\","+
>>>>>>                            "\"io_type\","+
>>>>>>                            "\"io_rank\","+
>>>>>>                            "\"io_value\")"+
>>>>>>                            " VALUES( ?,?,?,?,?)";
>>>>>>            pstmt_hio = c.prepareStatement(qry1);
>>>>>>        }catch( java.sql.SQLException e)
>>>>>>        {
>>>>>>            logger.fatal(e.toString());
>>>>>>            System.exit(-1);
>>>>>>        }
>>>>>>    }
>>>>>>      /**
>>>>>>     * Disconnect from DB
>>>>>>     *
>>>>>>     * @since           1.0
>>>>>>     */
>>>>>>    public void disconnect()
>>>>>>    {
>>>>>>        try {
>>>>>>            shutdownDataSource(datasource);
>>>>>>        } catch(Exception e){
>>>>>>            logger.fatal(e.toString());
>>>>>>            System.exit(-1);
>>>>>>        }
>>>>>>    }
>>>>>>        public void saveTrame(String boxID, String badgeID, String
>>>>>> gpsDate, double speed,
>>>>>>                                       String wgs84, double
>>>>>> altitude, double azimuth,
>>>>>>                                       String validity, String
>>>>>> geom, String sysDate, int[] input, int[] output)
>>>>>>    {             Connection m_conn = null;
>>>>>>        try
>>>>>>        {
>>>>>>            m_conn = datasource.getConnection();
>>>>>>            m_conn.setAutoCommit(false);
>>>>>>            prepareQuery(m_conn);
>>>>>>            //set query values for update gps_frame
>>>>>>            pstmt_ugps.setString(1, gpsDate);
>>>>>>            pstmt_ugps.setString(2, badgeID);
>>>>>>            pstmt_ugps.setString(3, sysDate);
>>>>>>            pstmt_ugps.setString(4, wgs84);
>>>>>>            pstmt_ugps.setDouble(5, speed);
>>>>>>            pstmt_ugps.setDouble(6, altitude);
>>>>>>            pstmt_ugps.setDouble(7, azimuth);
>>>>>>            pstmt_ugps.setString(8, validity);
>>>>>>            pstmt_ugps.setString(9, geom);
>>>>>>            pstmt_ugps.setString(10, boxID);
>>>>>>                      if(pstmt_ugps.executeUpdate()==0)
>>>>>>            { //if no frame already exists insert it
>>>>>>                pstmt_gps.setString(1, boxID);
>>>>>>                pstmt_gps.setString(2, gpsDate);
>>>>>>                pstmt_gps.setString(3, badgeID);
>>>>>>                pstmt_gps.setString(4, sysDate);
>>>>>>                pstmt_gps.setString(5, wgs84);
>>>>>>                pstmt_gps.setDouble(6, speed);
>>>>>>                pstmt_gps.setDouble(7, altitude);
>>>>>>                pstmt_gps.setDouble(8, azimuth);
>>>>>>                pstmt_gps.setString(9, validity);
>>>>>>                pstmt_gps.setString(10, geom);
>>>>>>                pstmt_gps.executeUpdate();
>>>>>>                              for(int i = 0; i < input.length; i++)
>>>>>>                {
>>>>>>                    pstmt_io.setString(1, boxID);
>>>>>>                    pstmt_io.setString(2, gpsDate);
>>>>>>                    pstmt_io.setString(3, "i");
>>>>>>                    pstmt_io.setInt(4, (i+1));
>>>>>>                    pstmt_io.setInt(5, input[i]);
>>>>>>                    pstmt_io.executeUpdate();
>>>>>>                }
>>>>>>                for(int o = 0; o < output.length; o++)
>>>>>>                {
>>>>>>                    pstmt_io.setString(1, boxID);
>>>>>>                    pstmt_io.setString(2, gpsDate);
>>>>>>                    pstmt_io.setString(3, "o");
>>>>>>                    pstmt_io.setInt(4, (o+1));
>>>>>>                    pstmt_io.setInt(5, output[o]);
>>>>>>                    pstmt_io.executeUpdate();
>>>>>>                }
>>>>>>            }else
>>>>>>            { //if frame already exists in gps_frame update his io
>>>>>>                for(int i = 0; i < input.length; i++)
>>>>>>                {
>>>>>>                  pstmt_uio.setString(1, gpsDate);
>>>>>>                  pstmt_uio.setInt(2, input[i]);
>>>>>>                  pstmt_uio.setString(3, boxID);
>>>>>>                  pstmt_uio.setInt(4,  (i+1));
>>>>>>                  pstmt_uio.setString(5,"i");
>>>>>>                  pstmt_uio.executeUpdate();
>>>>>>                }
>>>>>>                for(int o = 0; o < output.length; o++)
>>>>>>                {
>>>>>>                  pstmt_uio.setString(1, gpsDate);
>>>>>>                  pstmt_uio.setInt(2, output[o]);
>>>>>>                  pstmt_uio.setString(3, boxID);
>>>>>>                  pstmt_uio.setInt(4,  (o+1));
>>>>>>                  pstmt_uio.setString(5,"o");
>>>>>>                  pstmt_uio.executeUpdate();
>>>>>>                }
>>>>>>            }
>>>>>>            //insert in hisory
>>>>>>            pstmt_hgps.setString(1, boxID);
>>>>>>            pstmt_hgps.setString(2, gpsDate);
>>>>>>            pstmt_hgps.setString(3, badgeID);
>>>>>>            pstmt_hgps.setString(4, sysDate);
>>>>>>            pstmt_hgps.setString(5, wgs84);
>>>>>>            pstmt_hgps.setDouble(6, speed);
>>>>>>            pstmt_hgps.setDouble(7, altitude);
>>>>>>            pstmt_hgps.setDouble(8, azimuth);
>>>>>>            pstmt_hgps.setString(9, validity);
>>>>>>            pstmt_hgps.setString(10, geom);
>>>>>>            pstmt_hgps.executeUpdate();
>>>>>>
>>>>>>            for(int i = 0; i < input.length; i++)
>>>>>>            {
>>>>>>                pstmt_hio.setString(1, boxID);
>>>>>>                pstmt_hio.setString(2, gpsDate);
>>>>>>                pstmt_hio.setString(3, "i");
>>>>>>                pstmt_hio.setInt(4, (i+1));
>>>>>>                pstmt_hio.setInt(5, input[i]);
>>>>>>                pstmt_hio.executeUpdate();
>>>>>>            }
>>>>>>            for(int o = 0; o < output.length; o++)
>>>>>>            {
>>>>>>                pstmt_hio.setString(1, boxID);
>>>>>>                pstmt_hio.setString(2, gpsDate);
>>>>>>                pstmt_hio.setString(3, "o");
>>>>>>                pstmt_hio.setInt(4, (o+1));
>>>>>>                pstmt_hio.setInt(5, output[o]);
>>>>>>                pstmt_hio.executeUpdate();
>>>>>>            }
>>>>>>            m_conn.commit();
>>>>>>        }
>>>>>>        catch(java.sql.SQLException e){
>>>>>>            String msg = e ;
>>>>>>            logger.warn(msg);
>>>>>>        }
>>>>>>        finally {
>>>>>>            try { m_conn.close(); } catch(Exception e) { }
>>>>>>        }
>>>>>>    }
>>>>>>      public Jdbc3PoolingDataSource setupDataSource(String
>>>>>> connectURI) {
>>>>>>        Jdbc3PoolingDataSource ds = new Jdbc3PoolingDataSource();
>>>>>>        ds.setDataSourceName("Xxx");
>>>>>>        ds.setServerName("xxx.xxx.xxx.xxx");
>>>>>>        ds.setDatabaseName("xxxxxxxxxxxxx");
>>>>>>        ds.setUser("xxxxx");
>>>>>>        ds.setPassword("xxxxx");
>>>>>>        ds.setMaxConnections(10);
>>>>>>        return ds;
>>>>>>    }
>>>>>>    public static void shutdownDataSource(DataSource ds) throws
>>>>>> SQLException {
>>>>>>    }
>>>>>> }
>>>>>>
>>>>>> When i highload postgresql with a multi-threaded serverSocket
>>>>>> using this class,
>>>>>> my app freezes very quickly and all my connection are in idle state.
>>>>>> It seems that the connection aren't released.
>>>>>>
>>>>>> It don't know what i can do if someone could help ???
>>>>>> Thanks
>>>>>>
>>>>>>
>>>>>
>>>> Yes i use it as a singleon what's the problem with that ???
>>>> I instanciate One object => 1 pool for all threads and each thread
>>>> use the saveTrame
>>>> isn't this right ???
>>>>
>>>> Thanks
>>>>
>>>>
>>>
>> You're right but how can i do if my class is not singleton, i don't
>> want each thread with a pool of connections
>> i want a pool for all threads.
>>
>>
>>
>



--
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.300 / Virus Database: 265.7.4 - Release Date: 25/01/2005