存储过程的学习及java调用

1.开发存储过程

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

create or replace procedure SAVE_VEHICLE_LATEST_GIS_DATA as

begin
  merge into ET_VEHICLE_LATEST_GPS_DATA EVLGD
using (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) EVOGD

ON (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_CODE
when 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());
                }
            }
        }
    }