存储过程的学习及java调用

1.开发存储过程

例如：功能是利用线程定时调用存储过程将最新数据更新到新表里。

create or replace procedure SAVE_VEHICLE_LATEST_GIS_DATA asbegin  merge into ET_VEHICLE_LATEST_GPS_DATA EVLGDusing (SELECT *         FROM (SELECT ROW_NUMBER() OVER(PARTITION BY EVGD.PLATE_NUMBER ORDER BY EVGD.GIS_TIME DESC) rn,                      EVGD.PLATE_NUMBER,                      EVGD.GIS_TIME,                      EVGD.LONGITUDE,                      EVGD.LATITUDE,                      EVGD.GIS_SPEED,                      EVGD.DIRECTION,                      EVGD.DRIVER_CODE,                      EVGD.DRIVER_NAME,                      EVGD.TASK_CODE                 FROM ET_VEHICLE_GPS_DATA EVGD                WHERE 1 = 1 AND EVGD.PLATE_NUMBER IS NOT NULL AND EVGD.LONGITUDE>=1                  AND evgd.task_code is not null)        WHERE rn = 1) EVOGDON (EVOGD.PLATE_NUMBER = EVLGD.PLATE_NUMBER)when matched then  update     set EVLGD.GIS_TIME    = EVOGD.GIS_TIME,         EVLGD.modify_time = sysDate,         EVLGD.rec_ver     = EVLGD.rec_ver + 1,         EVLGD.LONGITUDE   = EVOGD.LONGITUDE,         EVLGD.LATITUDE    = EVOGD.LATITUDE,         EVLGD.GIS_SPEED   = EVOGD.GIS_SPEED,         EVLGD.DIRECTION   = EVOGD.DIRECTION,         EVLGD.DRIVER_CODE = EVOGD.DRIVER_CODE,         EVLGD.DRIVER_NAME = EVOGD.DRIVER_NAME,         EVLGD.TASK_CODE   = EVOGD.TASK_CODEwhen not matched then  insert    (job_id,     pm_code,     creator,     create_time,     modifier,     modify_time,     rec_ver,     rec_status,     PLATE_NUMBER,     GIS_TIME,     LONGITUDE,     LATITUDE,     GIS_SPEED,     DIRECTION,     DRIVER_CODE,     DRIVER_NAME,     TASK_CODE)  values    (SEQ_ET_VEHICLE_LATEST_GPS_DATA.NEXTVAL,     sys_guid(),     null,     sysDate,     null,     sysDate,     0,     0,     EVOGD.PLATE_NUMBER,     EVOGD.GIS_TIME,     EVOGD.LONGITUDE,     EVOGD.LATITUDE,     EVOGD.GIS_SPEED,     EVOGD.DIRECTION,     EVOGD.DRIVER_CODE,     EVOGD.DRIVER_NAME,     EVOGD.TASK_CODE);end SAVE_VEHICLE_LATEST_GIS_DATA;

2.java代码后台调用

class GetAndSaveVehicleLatestGpsData extends BaseManagerImpl implements Runnable {        private static final long serialVersionUID = -761600265448167130L;        @Override        public void run() {            Properties properties = new Properties();            InputStream inputStream = EtElectricFenceSettingManagerImpl.class.getResourceAsStream("/purtms/module.properties");            try {                properties.load(inputStream);            } catch (IOException e1) {                e1.printStackTrace();            }            String url = properties.getProperty("jdbc.url");            String user = properties.getProperty("jdbc.username");            String password = properties.getProperty("jdbc.password");            Connection connection = null;            CallableStatement callableStatement = null;            while (true) {                try {                    Thread.sleep(1000 * 60 * 2);// 睡2分                    connection = DriverManager.getConnection(url, user, password);                    try {                        synchronized (ONLY_READ_ONE_BY_ONE) {                            // 获取链接并调用存储过程                            callableStatement = connection.prepareCall("{call SAVE_VEHICLE_LATEST_GIS_DATA()}");                            callableStatement.execute();                        }                    } catch (Exception e) {                        Assert.isTrue(false, "保存车辆最新GPS数据失败！");                        log.info("[GetAndSaveVehicleLatestGpsData] | FAILED" + e.getMessage());                    } finally {                        // 关闭所有连接                        callableStatement.close();                        connection.close();                    }                } catch (Exception e) {                    Assert.isTrue(false, "保存车辆最新GPS数据失败！");                    log.info("[GetAndSaveVehicleLatestGpsData] | FAILED" + e.getMessage());                }            }        }    }