/* * 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(); } } }