Thread: JDBC HighLoad
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 ??? -- 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
Stéphane RIFF wrote: > It seems that jdbc wait before > inserting/updating the db because > they are to much requests. There is no code in the JDBC driver that deliberately does this. Perhaps you are seeing pauses due to GC? -O
On Wed, 26 Jan 2005, [ISO-8859-1] St�phane RIFF wrote: > 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. > Perhaps you could make a little app in java using jdbc that clearly demonstrates the problem. Kris Jurka
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
--
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
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
>
>
--
Dave Cramer
http://www.postgresintl.com
519 939 0336
ICQ#14675561
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
--
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
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
>
>
--
Dave Cramer
http://www.postgresintl.com
519 939 0336
ICQ#14675561
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
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.
>
>
>
--
Dave Cramer
http://www.postgresintl.com
519 939 0336
ICQ#14675561
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
Well, just make the data source a static variable and set it up in a static block. ie private static Jdbc3PoolingDataSource datasource = setupDataSource(); as I said look at http://jakarta.apache.org/commons/dbcp/ there are examples here http://cvs.apache.org/viewcvs.cgi/jakarta-commons/dbcp/doc/ Dave Stéphane RIFF wrote: > 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. >>> >>> >>> >> > > > -- Dave Cramer http://www.postgresintl.com 519 939 0336 ICQ#14675561
On Fri, 28 Jan 2005, [ISO-8859-1] St�phane RIFF wrote: > 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 The problem is you have all your preprared statements allocated in the singleton instead of per connection. This is bad because you overwrite them upon each call to prepareQuery. When you have multiple threads doing this at once what will happen is the first thread will prepare them and start executing, the second thread will also prepare them and begin executing, but since the statements are global, you've replaced the first threads versions and it will now be executing on the second threads connection because it's using the global statements. You've got chaos going on here. Kris Jurka
Ok so how can i make a preparedStatement pool or allocated my preparedStatements for each connection ??? Kris Jurka wrote: >On Fri, 28 Jan 2005, [ISO-8859-1] Stéphane RIFF wrote: > > > >>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 >> >> > >The problem is you have all your preprared statements allocated in the >singleton instead of per connection. This is bad because you >overwrite them upon each call to prepareQuery. When you have multiple >threads doing this at once what will happen is the first thread will >prepare them and start executing, the second thread will also prepare them >and begin executing, but since the statements are global, you've replaced >the first threads versions and it will now be executing on the second >threads connection because it's using the global statements. You've got >chaos going on here. > >Kris Jurka > >---------------------------(end of broadcast)--------------------------- >TIP 3: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly > > > > -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.300 / Virus Database: 265.8.2 - Release Date: 28/01/2005
On Mon, 31 Jan 2005, [ISO-8859-1] Stéphane RIFF wrote: > Ok so how can i make a preparedStatement pool or > allocated my preparedStatements for each connection ??? > Statements are allocated per connection, the problem is that you only have one variable that tracks only the latest allocated statement. I would suggest splitting your class into two. One, the singleton which creates the pool, and two a class which holds the statements. Then you create a new ClassTwo object for each thread/connection. Kris Jurka
Hello,
I implement like you said in the last post but now i get some errors
like this :
2005-02-04 09:03:26,234 : [WARN] SQLoader - java.sql.SQLException:
org.apache.commons.dbcp.DelegatingPreparedStatement is closed.
I attach the two class i you want to see
Thanks
Kris Jurka wrote:
>On Mon, 31 Jan 2005, [ISO-8859-1] Stéphane RIFF wrote:
>
>
>
>>Ok so how can i make a preparedStatement pool or
>>allocated my preparedStatements for each connection ???
>>
>>
>>
>
>Statements are allocated per connection, the problem is that you only have
>one variable that tracks only the latest allocated statement. I would
>suggest splitting your class into two. One, the singleton which creates
>the pool, and two a class which holds the statements. Then you create a
>new ClassTwo object for each thread/connection.
>
>Kris Jurka
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 7: don't forget to increase your free space map settings
>
>
>
>
/*
* SQLoader.java
*
* Created on 4 ao�t 2004, 14:33
*/
/**
* Handle all jdbc access to the database
* This is a singleton class that ensure
* only one instance of the class is created
*
* @version %I%, %G%
* @author steff
*/
package fr.cerene.geosiara.gprs.receiver;
import java.io.*;
import java.util.*;
import java.sql.*;
import java.text.*;
import org.apache.log4j.Logger;
import org.apache.log4j.PropertyConfigurator;
public class SQLoader {
//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 ;
//Log4j logger object
private static Logger logger = Logger.getLogger(SQLoader.class.getName());
Connection m_conn = null;
public SQLoader()
{
m_conn = ConnectionPool.getRef().getConnection();
prepareQuery(m_conn);
}
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);
}
}
/**
* Store the gps frame information to the DB
*
* @param veh Sensor box ID
* @param driver Code represent the driver
* @param gpsDate GMT Date at which the point took
* @param speed Vehicle's speed
* @param e1 Sensor box input number 1
* @param e2 Sensor box input number 2
* @param e3 Sensor box input number 3
* @param e4 Sensor box input number 4
* @param s1 Sensor box output number 1
* @param s2 Sensor box output number 2
* @param s3 Sensor box output number 3
* @param s4 Sensor box output number 4
* @param valid The GPS point accuracy (min 4 satelits)
* @param geom Postgis geometry format of the point
* @param sysDate Locale date of the insertion in DB
* @since 1.0
*/
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)
{
try
{
//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.addBatch();
}
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.addBatch();
}
pstmt_io.executeBatch();
}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.addBatch();
}
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.addBatch();
}
pstmt_uio.executeBatch();
}
//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.addBatch();
}
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.addBatch();
}
pstmt_hio.executeBatch();
}
catch(java.sql.SQLException e){
String msg = java.util.ResourceBundle.getBundle("i18n/MessageBundle").getString("excp_savetrame")+e ;
logger.warn(msg);
}
finally {
try{
m_conn.commit();
m_conn.close();
/*pstmt_ugps.close();
pstmt_gps.close();
pstmt_io.close();
pstmt_uio.close();
pstmt_hgps.close();
pstmt_hio.close();*/
}catch(SQLException e){}
System.out.println("UPDATE gps_frame SET "+
"\"gps_date\"='"+gpsDate+"',"+
"\"badge_id\"='"+badgeID+"',"+
"\"gmt_creation_date\"='"+sysDate+"',"+
"\"wgs84_position\"='"+wgs84+"',"+
"\"speed\"="+speed+","+
"\"altitude\"="+altitude+","+
"\"heading\"="+azimuth+","+
"\"validity\"='"+validity+"',"+
"\"geom\"='"+geom+"'"+
" WHERE \"sbox_id\"="+boxID);
}
}
/*public void saveTrame2(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;
System.out.println("UPDATE gps_frame SET "+
"\"gps_date\"='"+gpsDate+"',"+
"\"badge_id\"='"+badgeID+"',"+
"\"gmt_creation_date\"='"+sysDate+"',"+
"\"wgs84_position\"='"+wgs84+"',"+
"\"speed\"="+speed+","+
"\"altitude\"="+altitude+","+
"\"heading\"="+azimuth+","+
"\"validity\"='"+validity+"',"+
"\"geom\"='"+geom+"'"+
" WHERE \"sbox_id\"="+boxID);
try
{
m_conn = datasource.getConnection();
//set query values for update gps_frame
Statement stmt = m_conn.createStatement();
int isExist = stmt.executeUpdate("UPDATE gps_frame SET "+
"\"gps_date\"='"+gpsDate+"',"+
"\"badge_id\"='"+badgeID+"',"+
"\"gmt_creation_date\"='"+sysDate+"',"+
"\"wgs84_position\"='"+wgs84+"',"+
"\"speed\"="+speed+","+
"\"altitude\"="+altitude+","+
"\"heading\"="+azimuth+","+
"\"validity\"='"+validity+"',"+
"\"geom\"='"+geom+"'"+
" WHERE \"sbox_id\"="+boxID);
stmt.close();
if(isExist==0)
{ //if no frame already exists insert it
stmt = m_conn.createStatement();
stmt.executeUpdate("INSERT INTO gps_frame (" +
"\"sbox_id\"," +
"\"gps_date\"," +
"\"badge_id\"," +
"\"gmt_creation_date\"," +
"\"wgs84_position\","+
"\"speed\"," +
"\"altitude\","+
"\"heading\","+
"\"validity\"," +
"\"geom\")" +
" VALUES( "+boxID+",'"+gpsDate+"','"+badgeID+"','"+sysDate+"','"+wgs84+"',"+
speed+","+altitude+","+azimuth+",'"+validity+"','"+geom+"')");
stmt.close();
for(int i = 0; i < input.length; i++)
{
stmt = m_conn.createStatement();
stmt.executeUpdate("INSERT INTO io_frame ("+
"\"sbox_id\","+
"\"gps_date\","+
"\"io_type\","+
"\"io_rank\","+
"\"io_value\")"+
" VALUES( "+boxID+",'"+gpsDate+"','i',"+(i+1)+","+input[i]+")");
stmt.close();
}
for(int o = 0; o < output.length; o++)
{
stmt = m_conn.createStatement();
stmt.executeUpdate("INSERT INTO io_frame ("+
"\"sbox_id\","+
"\"gps_date\","+
"\"io_type\","+
"\"io_rank\","+
"\"io_value\")"+
" VALUES( "+boxID+",'"+gpsDate+"','o',"+(o+1)+","+output[o]+")");
stmt.close();
}
}else
{ //if frame already exists in gps_frame update his io
for(int i = 0; i < input.length; i++)
{
stmt = m_conn.createStatement();
stmt.executeUpdate("UPDATE io_frame SET "+
"\"gps_date\"='"+gpsDate+"',"+
"\"io_value\"="+input[i]+
" WHERE \"sbox_id\"="+boxID+" AND \"io_rank\"="+(i+1)+" AND io_type='i'");
stmt.close();
}
for(int o = 0; o < output.length; o++)
{
stmt = m_conn.createStatement();
stmt.executeUpdate("UPDATE io_frame SET "+
"\"gps_date\"='"+gpsDate+"',"+
"\"io_value\"="+output[o]+
" WHERE \"sbox_id\"="+boxID+" AND \"io_rank\"="+(o+1)+" AND io_type='o'");
stmt.close();
}
}
//insert in hisory
stmt = m_conn.createStatement();
stmt.executeUpdate("INSERT INTO gps_frame_history (" +
"\"sbox_id\"," +
"\"gps_date\"," +
"\"badge_id\"," +
"\"gmt_creation_date\"," +
"\"wgs84_position\","+
"\"speed\"," +
"\"altitude\","+
"\"heading\","+
"\"validity\"," +
"\"geom\")" +
" VALUES( "+boxID+",'"+gpsDate+"','"+badgeID+"','"+sysDate+"','"+wgs84+"'"+
","+speed+","+altitude+","+azimuth+",'"+validity+"','"+geom+"')");
stmt.close();
for(int i = 0; i < input.length; i++)
{
stmt = m_conn.createStatement();
stmt.executeUpdate("INSERT INTO io_frame_history ("+
"\"sbox_id\","+
"\"gps_date\","+
"\"io_type\","+
"\"io_rank\","+
"\"io_value\")"+
" VALUES( "+boxID+",'"+gpsDate+"','i',"+(i+1)+","+input[i]+")");
stmt.close();
}
for(int o = 0; o < output.length; o++)
{
stmt = m_conn.createStatement();
stmt.executeUpdate("INSERT INTO io_frame_history ("+
"\"sbox_id\","+
"\"gps_date\","+
"\"io_type\","+
"\"io_rank\","+
"\"io_value\")"+
" VALUES( "+boxID+",'"+gpsDate+"','o',"+(o+1)+","+output[o]+")");
stmt.close();
}
m_conn.commit();
m_conn.close();
}
catch(java.sql.SQLException e){
String msg = java.util.ResourceBundle.getBundle("i18n/MessageBundle").getString("excp_savetrame")+e ;
logger.warn(msg);
}
}*/
/**
* test SQLoader class
*
* @since 1.0
*/
public static void main(String[] args)
{
//Load log4j parameters
PropertyConfigurator.configure("conf/log4j.properties");
for(int proc=0; proc<100;proc++)
{
Thread th = new Thread(new Runnable()
{
public void run()
{
String id = Thread.currentThread().getName();
SQLoader sl = new SQLoader();
for(int i=0;i<100;i++)
{
java.util.GregorianCalendar gc = new java.util.GregorianCalendar();
String date = new java.text.SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(gc.getTime());
int inp[] = {1,1,1,1};
int oup[] = {1,1,1,1};
sl.saveTrame(id,"017BC354080000C2",date.toString(),0.000,"3.05340666666667,35.7437066666667",0.0,0.0,"A","SRID=4326;POINT(3.05340666666667
35.7437066666667)","now()",inp,oup);
try{
Thread.sleep(1000);
}catch(Exception e){System.out.println(e);}
}
}
},Integer.toString(proc)
);
th.start();
}
}
}
/*
* ConnectionPool.java
*
* Created on 3 f�vrier 2005, 14:25
*/
package fr.cerene.geosiara.gprs.receiver;
import javax.sql.DataSource ;
import java.sql.*;
import java.io.*;
import java.util.*;
import org.apache.commons.dbcp.*;
import org.apache.commons.pool.impl.*;
import org.apache.commons.pool.*;
import org.apache.log4j.Logger;
/**
*
* @author steff
*/
public class ConnectionPool {
private static DataSource datasource = setupDataSource() ;
private final static ConnectionPool connectionPool = new ConnectionPool();
private static Logger logger = Logger.getLogger(ConnectionPool.class.getName());
/** Creates a new instance of ConnectionPool */
private ConnectionPool() {
}
public static ConnectionPool getRef()
{
return connectionPool ;
}
private static DataSource setupDataSource() {
try {
Properties conf = new Properties();
FileInputStream fis = new FileInputStream("conf/conf.properties");
conf.load(fis);
String dbd = conf.getProperty("jdbc_driver","org.postgresql.Driver");
String dbc = conf.getProperty("db_connection_string","jdbc:postgresql://200.200.200.34:5432/geosiara2");
String dbu = conf.getProperty("dbuser","apache");
String dbp = conf.getProperty("dbpass","apache");
BasicDataSource ds = new BasicDataSource();
ds.setUrl(dbc);
ds.setDriverClassName(dbd);
ds.setUsername(dbu);
ds.setPassword(dbp);
ds.setMaxActive(10);
ds.setMaxIdle(1);
ds.setPoolPreparedStatements(true);
ObjectPool connectionPool = new GenericObjectPool(null);
ConnectionFactory connectionFactory = new DataSourceConnectionFactory(ds);
PoolableObjectFactory poolableConnectionFactory = new
PoolableConnectionFactory(connectionFactory,connectionPool,null,null,false,false);
PoolingDataSource dataSource = new PoolingDataSource(connectionPool);
return dataSource;
}catch(IOException e) {
logger.fatal(e.toString());
System.exit(1);
return null;
}
}
public static void shutdownDataSource() throws SQLException {
BasicDataSource bds = (BasicDataSource) datasource;
bds.close();
}
public Connection getConnection()
{
Connection conn = null;
try {
conn = datasource.getConnection();
}catch(SQLException e){}
return conn;
}
public static void main(String[] args)
{
try
{
Connection conn = ConnectionPool.getRef().getConnection();
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery("SELECT * FROM gps_frame");
while(rs.next())
{
System.out.println(rs.getString("sbox_id"));
}
rs.close();
stmt.close();
conn.close();
}catch(SQLException e){System.out.println(e);}
}
}
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.300 / Virus Database: 265.8.5 - Release Date: 03/02/2005
On Fri, 4 Feb 2005, [ISO-8859-1] St�phane RIFF wrote:
> Hello,
> I implement like you said in the last post but now i get some errors
> like this :
>
> 2005-02-04 09:03:26,234 : [WARN] SQLoader - java.sql.SQLException:
> org.apache.commons.dbcp.DelegatingPreparedStatement is closed.
> I attach the two class i you want to see
>
Your main loop is written:
SQLoader sl = new SQLoader();
for(int i=0;i<100;i++) {
sl.saveTrame( ... );
}
but the end of the saveTrame method you have:
finally {
try {
m_conn.commit();
m_conn.close();
This closes the connection on the first iteration of the loop. I'd
suggest something like adding SQLoader.close() which gets called at the
end of the for loop.
Kris Jurka
I thought that the m_conn.close() released the connection to the pool
doesn't it ?
If i close the connection at the end of the loop byt a SQLoader.close()
this will
make one connection for each thread. I want each thread ask a connection
to the pool
and released it after each update.
Thanks for your time
Bye
Kris Jurka wrote:
>On Fri, 4 Feb 2005, [ISO-8859-1] St�phane RIFF wrote:
>
>
>
>>Hello,
>>I implement like you said in the last post but now i get some errors
>>like this :
>>
>>2005-02-04 09:03:26,234 : [WARN] SQLoader - java.sql.SQLException:
>>org.apache.commons.dbcp.DelegatingPreparedStatement is closed.
>>I attach the two class i you want to see
>>
>>
>>
>
>Your main loop is written:
>
>SQLoader sl = new SQLoader();
>for(int i=0;i<100;i++) {
> sl.saveTrame( ... );
>}
>
>but the end of the saveTrame method you have:
>
>finally {
> try {
> m_conn.commit();
> m_conn.close();
>
>This closes the connection on the first iteration of the loop. I'd
>suggest something like adding SQLoader.close() which gets called at the
>end of the for loop.
>
>Kris Jurka
>
>---------------------------(end of broadcast)---------------------------
>TIP 8: explain analyze is your friend
>
>
>
>
--
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.300 / Virus Database: 265.8.5 - Release Date: 03/02/2005
Stephane,
Yes, that is true, but where do you get the connection from on the next
iteration of the loop ? The constructor of SQLoader.... so the next loop
it is gone.
Dave
Stéphane RIFF wrote:
> I thought that the m_conn.close() released the connection to the pool
> doesn't it ?
> If i close the connection at the end of the loop byt a
> SQLoader.close() this will
> make one connection for each thread. I want each thread ask a
> connection to the pool
> and released it after each update.
>
> Thanks for your time
> Bye
>
> Kris Jurka wrote:
>
>> On Fri, 4 Feb 2005, [ISO-8859-1] St�phane RIFF wrote:
>>
>>
>>
>>> Hello,
>>> I implement like you said in the last post but now i get some errors
>>> like this :
>>>
>>> 2005-02-04 09:03:26,234 : [WARN] SQLoader - java.sql.SQLException:
>>> org.apache.commons.dbcp.DelegatingPreparedStatement is closed.
>>> I attach the two class i you want to see
>>>
>>>
>>
>>
>> Your main loop is written:
>>
>> SQLoader sl = new SQLoader();
>> for(int i=0;i<100;i++) {
>> sl.saveTrame( ... );
>> }
>>
>> but the end of the saveTrame method you have:
>>
>> finally {
>> try {
>> m_conn.commit();
>> m_conn.close();
>>
>> This closes the connection on the first iteration of the loop. I'd
>> suggest something like adding SQLoader.close() which gets called at
>> the end of the for loop.
>>
>> Kris Jurka
>>
>> ---------------------------(end of broadcast)---------------------------
>> TIP 8: explain analyze is your friend
>>
>>
>>
>>
>
>
>
--
Dave Cramer
http://www.postgresintl.com
519 939 0336
ICQ#14675561
I thought that preparedStatement know what connection to use even if i
return it to the pool
Dave Cramer wrote:
> Stephane,
>
> Yes, that is true, but where do you get the connection from on the
> next iteration of the loop ? The constructor of SQLoader.... so the
> next loop it is gone.
>
> Dave
>
> Stéphane RIFF wrote:
>
>> I thought that the m_conn.close() released the connection to the pool
>> doesn't it ?
>> If i close the connection at the end of the loop byt a
>> SQLoader.close() this will
>> make one connection for each thread. I want each thread ask a
>> connection to the pool
>> and released it after each update.
>>
>> Thanks for your time
>> Bye
>>
>> Kris Jurka wrote:
>>
>>> On Fri, 4 Feb 2005, [ISO-8859-1] St�phane RIFF wrote:
>>>
>>>
>>>
>>>> Hello,
>>>> I implement like you said in the last post but now i get some
>>>> errors like this :
>>>>
>>>> 2005-02-04 09:03:26,234 : [WARN] SQLoader - java.sql.SQLException:
>>>> org.apache.commons.dbcp.DelegatingPreparedStatement is closed.
>>>> I attach the two class i you want to see
>>>>
>>>>
>>>
>>>
>>>
>>> Your main loop is written:
>>>
>>> SQLoader sl = new SQLoader();
>>> for(int i=0;i<100;i++) {
>>> sl.saveTrame( ... );
>>> }
>>>
>>> but the end of the saveTrame method you have:
>>>
>>> finally {
>>> try {
>>> m_conn.commit();
>>> m_conn.close();
>>>
>>> This closes the connection on the first iteration of the loop. I'd
>>> suggest something like adding SQLoader.close() which gets called at
>>> the end of the for loop.
>>>
>>> Kris Jurka
>>>
>>> ---------------------------(end of
>>> broadcast)---------------------------
>>> TIP 8: explain analyze is your friend
>>>
>>>
>>>
>>>
>>
>>
>>
>
--
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.300 / Virus Database: 265.8.5 - Release Date: 03/02/2005
Stephane,
No.
Here is the basic usage of the connection ( regardless of the existance
of the pool)
get the connection
prepare the statement
execute the statement
do something with result
close statement
close connection ( this allows the connection to be re-used by another
request )
Having a pool does not change this pattern.
All the pool does is cache connections so that they can be re-used by
multiple threads without going through the overhead of opening the
connection.
It also minimizes the number of connections required for an application.
For example if you have 1000 requests you might only need 100
connections since they will share
the connections in the pool.
Dave
Stéphane RIFF wrote:
> I thought that preparedStatement know what connection to use even if i
> return it to the pool
>
> Dave Cramer wrote:
>
>> Stephane,
>>
>> Yes, that is true, but where do you get the connection from on the
>> next iteration of the loop ? The constructor of SQLoader.... so the
>> next loop it is gone.
>>
>> Dave
>>
>> Stéphane RIFF wrote:
>>
>>> I thought that the m_conn.close() released the connection to the
>>> pool doesn't it ?
>>> If i close the connection at the end of the loop byt a
>>> SQLoader.close() this will
>>> make one connection for each thread. I want each thread ask a
>>> connection to the pool
>>> and released it after each update.
>>>
>>> Thanks for your time
>>> Bye
>>>
>>> Kris Jurka wrote:
>>>
>>>> On Fri, 4 Feb 2005, [ISO-8859-1] St�phane RIFF wrote:
>>>>
>>>>
>>>>
>>>>> Hello,
>>>>> I implement like you said in the last post but now i get some
>>>>> errors like this :
>>>>>
>>>>> 2005-02-04 09:03:26,234 : [WARN] SQLoader - java.sql.SQLException:
>>>>> org.apache.commons.dbcp.DelegatingPreparedStatement is closed.
>>>>> I attach the two class i you want to see
>>>>>
>>>>>
>>>>
>>>>
>>>>
>>>>
>>>> Your main loop is written:
>>>>
>>>> SQLoader sl = new SQLoader();
>>>> for(int i=0;i<100;i++) {
>>>> sl.saveTrame( ... );
>>>> }
>>>>
>>>> but the end of the saveTrame method you have:
>>>>
>>>> finally {
>>>> try {
>>>> m_conn.commit();
>>>> m_conn.close();
>>>>
>>>> This closes the connection on the first iteration of the loop. I'd
>>>> suggest something like adding SQLoader.close() which gets called at
>>>> the end of the for loop.
>>>>
>>>> Kris Jurka
>>>>
>>>> ---------------------------(end of
>>>> broadcast)---------------------------
>>>> TIP 8: explain analyze is your friend
>>>>
>>>>
>>>>
>>>>
>>>
>>>
>>>
>>
>
>
>
--
Dave Cramer
http://www.postgresintl.com
519 939 0336
ICQ#14675561
But with this usage i cannot reuse a preparedStatement.
Is it possible to create the preparedStatement in the constructor and
reused them
for each update. So that each thread has his own preparedStatements because
each thread do an update every second, i also think that preparedStatements
was good to use in this configuration but in your usage i have to
prepare the statement each time i get the connection.
Dave Cramer wrote:
> Stephane,
>
> No.
>
> Here is the basic usage of the connection ( regardless of the
> existance of the pool)
>
> get the connection
> prepare the statement
> execute the statement
> do something with result
> close statement
> close connection ( this allows the connection to be re-used by another
> request )
>
>
> Having a pool does not change this pattern.
>
> All the pool does is cache connections so that they can be re-used by
> multiple threads without going through the overhead of opening the
> connection.
> It also minimizes the number of connections required for an
> application. For example if you have 1000 requests you might only need
> 100 connections since they will share
> the connections in the pool.
>
> Dave
>
> Stéphane RIFF wrote:
>
>> I thought that preparedStatement know what connection to use even if
>> i return it to the pool
>>
>> Dave Cramer wrote:
>>
>>> Stephane,
>>>
>>> Yes, that is true, but where do you get the connection from on the
>>> next iteration of the loop ? The constructor of SQLoader.... so the
>>> next loop it is gone.
>>>
>>> Dave
>>>
>>> Stéphane RIFF wrote:
>>>
>>>> I thought that the m_conn.close() released the connection to the
>>>> pool doesn't it ?
>>>> If i close the connection at the end of the loop byt a
>>>> SQLoader.close() this will
>>>> make one connection for each thread. I want each thread ask a
>>>> connection to the pool
>>>> and released it after each update.
>>>>
>>>> Thanks for your time
>>>> Bye
>>>>
>>>> Kris Jurka wrote:
>>>>
>>>>> On Fri, 4 Feb 2005, [ISO-8859-1] St�phane RIFF wrote:
>>>>>
>>>>>
>>>>>
>>>>>> Hello,
>>>>>> I implement like you said in the last post but now i get some
>>>>>> errors like this :
>>>>>>
>>>>>> 2005-02-04 09:03:26,234 : [WARN] SQLoader -
>>>>>> java.sql.SQLException:
>>>>>> org.apache.commons.dbcp.DelegatingPreparedStatement is closed.
>>>>>> I attach the two class i you want to see
>>>>>>
>>>>>>
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>
>>>>> Your main loop is written:
>>>>>
>>>>> SQLoader sl = new SQLoader();
>>>>> for(int i=0;i<100;i++) {
>>>>> sl.saveTrame( ... );
>>>>> }
>>>>>
>>>>> but the end of the saveTrame method you have:
>>>>>
>>>>> finally {
>>>>> try {
>>>>> m_conn.commit();
>>>>> m_conn.close();
>>>>>
>>>>> This closes the connection on the first iteration of the loop.
>>>>> I'd suggest something like adding SQLoader.close() which gets
>>>>> called at the end of the for loop.
>>>>>
>>>>> Kris Jurka
>>>>>
>>>>> ---------------------------(end of
>>>>> broadcast)---------------------------
>>>>> TIP 8: explain analyze is your friend
>>>>>
>>>>>
>>>>>
>>>>>
>>>>
>>>>
>>>>
>>>
>>
>>
>>
>
--
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.300 / Virus Database: 265.8.5 - Release Date: 03/02/2005
Stephane,
I didn't write the spec, I'm just explaining how to use it.
Dave
Stéphane RIFF wrote:
> But with this usage i cannot reuse a preparedStatement.
> Is it possible to create the preparedStatement in the constructor and
> reused them
> for each update. So that each thread has his own preparedStatements
> because
> each thread do an update every second, i also think that
> preparedStatements
> was good to use in this configuration but in your usage i have to
> prepare the statement each time i get the connection.
>
> Dave Cramer wrote:
>
>> Stephane,
>>
>> No.
>>
>> Here is the basic usage of the connection ( regardless of the
>> existance of the pool)
>>
>> get the connection
>> prepare the statement
>> execute the statement
>> do something with result
>> close statement
>> close connection ( this allows the connection to be re-used by
>> another request )
>>
>>
>> Having a pool does not change this pattern.
>>
>> All the pool does is cache connections so that they can be re-used by
>> multiple threads without going through the overhead of opening the
>> connection.
>> It also minimizes the number of connections required for an
>> application. For example if you have 1000 requests you might only
>> need 100 connections since they will share
>> the connections in the pool.
>>
>> Dave
>>
>> Stéphane RIFF wrote:
>>
>>> I thought that preparedStatement know what connection to use even if
>>> i return it to the pool
>>>
>>> Dave Cramer wrote:
>>>
>>>> Stephane,
>>>>
>>>> Yes, that is true, but where do you get the connection from on the
>>>> next iteration of the loop ? The constructor of SQLoader.... so the
>>>> next loop it is gone.
>>>>
>>>> Dave
>>>>
>>>> Stéphane RIFF wrote:
>>>>
>>>>> I thought that the m_conn.close() released the connection to the
>>>>> pool doesn't it ?
>>>>> If i close the connection at the end of the loop byt a
>>>>> SQLoader.close() this will
>>>>> make one connection for each thread. I want each thread ask a
>>>>> connection to the pool
>>>>> and released it after each update.
>>>>>
>>>>> Thanks for your time
>>>>> Bye
>>>>>
>>>>> Kris Jurka wrote:
>>>>>
>>>>>> On Fri, 4 Feb 2005, [ISO-8859-1] St�phane RIFF wrote:
>>>>>>
>>>>>>
>>>>>>
>>>>>>> Hello,
>>>>>>> I implement like you said in the last post but now i get some
>>>>>>> errors like this :
>>>>>>>
>>>>>>> 2005-02-04 09:03:26,234 : [WARN] SQLoader -
>>>>>>> java.sql.SQLException:
>>>>>>> org.apache.commons.dbcp.DelegatingPreparedStatement is closed.
>>>>>>> I attach the two class i you want to see
>>>>>>>
>>>>>>>
>>>>>>
>>>>>>
>>>>>>
>>>>>>
>>>>>>
>>>>>>
>>>>>> Your main loop is written:
>>>>>>
>>>>>> SQLoader sl = new SQLoader();
>>>>>> for(int i=0;i<100;i++) {
>>>>>> sl.saveTrame( ... );
>>>>>> }
>>>>>>
>>>>>> but the end of the saveTrame method you have:
>>>>>>
>>>>>> finally {
>>>>>> try {
>>>>>> m_conn.commit();
>>>>>> m_conn.close();
>>>>>>
>>>>>> This closes the connection on the first iteration of the loop.
>>>>>> I'd suggest something like adding SQLoader.close() which gets
>>>>>> called at the end of the for loop.
>>>>>>
>>>>>> Kris Jurka
>>>>>>
>>>>>> ---------------------------(end of
>>>>>> broadcast)---------------------------
>>>>>> TIP 8: explain analyze is your friend
>>>>>>
>>>>>>
>>>>>>
>>>>>>
>>>>>
>>>>>
>>>>>
>>>>
>>>
>>>
>>>
>>
>
>
>
--
Dave Cramer
http://www.postgresintl.com
519 939 0336
ICQ#14675561
If you want to pool prepared statements, this can only be done on a PER
CONNECTION basis. That is, you are going to have to make each connection
have a pool of prepared statements so that when you make a call to
prepareStatement it checks the pool OF THAT SPECIFIC CONNECTION to see
if it's already in there, and so that close (on the prepared statement)
returns it to the pool.
Apache DBCP has an implementation of this, see the Commons DBCP project
at jakarta.apache.org.
Greetings,
Sebastiaan van Erk
Stéphane RIFF wrote:
> But with this usage i cannot reuse a preparedStatement.
> Is it possible to create the preparedStatement in the constructor and
> reused them
> for each update. So that each thread has his own preparedStatements because
> each thread do an update every second, i also think that preparedStatements
> was good to use in this configuration but in your usage i have to
> prepare the statement each time i get the connection.
>
> Dave Cramer wrote:
>
>> Stephane,
>>
>> No.
>>
>> Here is the basic usage of the connection ( regardless of the
>> existance of the pool)
>>
>> get the connection
>> prepare the statement
>> execute the statement
>> do something with result
>> close statement
>> close connection ( this allows the connection to be re-used by another
>> request )
>>
>>
>> Having a pool does not change this pattern.
>>
>> All the pool does is cache connections so that they can be re-used by
>> multiple threads without going through the overhead of opening the
>> connection.
>> It also minimizes the number of connections required for an
>> application. For example if you have 1000 requests you might only need
>> 100 connections since they will share
>> the connections in the pool.
>>
>> Dave
>>
>> Stéphane RIFF wrote:
>>
>>> I thought that preparedStatement know what connection to use even if
>>> i return it to the pool
>>>
>>> Dave Cramer wrote:
>>>
>>>> Stephane,
>>>>
>>>> Yes, that is true, but where do you get the connection from on the
>>>> next iteration of the loop ? The constructor of SQLoader.... so the
>>>> next loop it is gone.
>>>>
>>>> Dave
>>>>
>>>> Stéphane RIFF wrote:
>>>>
>>>>> I thought that the m_conn.close() released the connection to the
>>>>> pool doesn't it ?
>>>>> If i close the connection at the end of the loop byt a
>>>>> SQLoader.close() this will
>>>>> make one connection for each thread. I want each thread ask a
>>>>> connection to the pool
>>>>> and released it after each update.
>>>>>
>>>>> Thanks for your time
>>>>> Bye
>>>>>
>>>>> Kris Jurka wrote:
>>>>>
>>>>>> On Fri, 4 Feb 2005, [ISO-8859-1] St�phane RIFF wrote:
>>>>>>
>>>>>>
>>>>>>
>>>>>>> Hello,
>>>>>>> I implement like you said in the last post but now i get some
>>>>>>> errors like this :
>>>>>>>
>>>>>>> 2005-02-04 09:03:26,234 : [WARN] SQLoader -
>>>>>>> java.sql.SQLException:
>>>>>>> org.apache.commons.dbcp.DelegatingPreparedStatement is closed.
>>>>>>> I attach the two class i you want to see
>>>>>>>
>>>>>>>
>>>>>>
>>>>>>
>>>>>>
>>>>>>
>>>>>>
>>>>>>
>>>>>> Your main loop is written:
>>>>>>
>>>>>> SQLoader sl = new SQLoader();
>>>>>> for(int i=0;i<100;i++) {
>>>>>> sl.saveTrame( ... );
>>>>>> }
>>>>>>
>>>>>> but the end of the saveTrame method you have:
>>>>>>
>>>>>> finally {
>>>>>> try {
>>>>>> m_conn.commit();
>>>>>> m_conn.close();
>>>>>>
>>>>>> This closes the connection on the first iteration of the loop.
>>>>>> I'd suggest something like adding SQLoader.close() which gets
>>>>>> called at the end of the for loop.
>>>>>>
>>>>>> Kris Jurka
>>>>>>
>>>>>> ---------------------------(end of
>>>>>> broadcast)---------------------------
>>>>>> TIP 8: explain analyze is your friend
>>>>>>
>>>>>>
>>>>>>
>>>>>>
>>>>>
>>>>>
>>>>>
>>>>
>>>
>>>
>>>
>>
>
>
>