Re: [SPAM] - Re: [SPAM] - Re: JDBC HighLoad - Found word(s)

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