存储过程与JAVA
来源：互联网 发布：mmd怎么保存姿势数据 编辑：程序博客网 时间：2024/06/11 05:42
1、存储过程
USE [linye]GO/****** Object: StoredProcedure [dbo].[proc_winddirection] Script Date: 01/15/2016 15:02:55 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO-- =============================================-- Author:<Author,,Name>-- Create date: <Create Date,,>-- Description:<Description,,>-- =============================================ALTER PROCEDURE [dbo].[proc_winddirection] -- Add the parameters for the stored procedure here@starttime varchar(255),@endtime varchar(255),@id int,@c1 float output,@c2 float output,@c3 float output,@c4 float output,@c5 float output,@c6 float output,@c7 float output,@c8 float outputASBEGIN-- SET NOCOUNT ON added to prevent extra result sets from-- interfering with SELECT statements.SET NOCOUNT ON;declare @no nvarchar(255),@w1 numeric=0,@w2 numeric=0,@w3 numeric=0,@w4 numeric=0,@w5 numeric=0,@w6 numeric=0,@w7 numeric=0,@w8 numeric=0,@w numeric=0 set @no=(select plateNo from GPSRealData where ID = @id) set @w=(select count(recordVelocity) from GPSHistoryData where plateNo = @no and updateDate > @starttime and updateDate < @endtime) set @w1=(select count(recordVelocity) from GPSHistoryData where plateNo = @no and recordVelocity = 0 and updateDate > @starttime and updateDate < @endtime) set @w2=(select count(recordVelocity) from GPSHistoryData where plateNo = @no and recordVelocity > 0 and recordVelocity < 90 and updateDate > @starttime and updateDate < @endtime) set @w3=(select count(recordVelocity) from GPSHistoryData where plateNo = @no and recordVelocity = 90 and updateDate > @starttime and updateDate < @endtime) set @w4=(select count(recordVelocity) from GPSHistoryData where plateNo = @no and recordVelocity > 90 and recordVelocity < 180 and updateDate > @starttime and updateDate < @endtime) set @w5=(select count(recordVelocity) from GPSHistoryData where plateNo = @no and recordVelocity = 180 and updateDate > @starttime and updateDate < @endtime) set @w6=(select count(recordVelocity) from GPSHistoryData where plateNo = @no and recordVelocity > 180 and recordVelocity < 270 and updateDate > @starttime and updateDate < @endtime) set @w7=(select count(recordVelocity) from GPSHistoryData where plateNo = @no and recordVelocity = 270 and updateDate > @starttime and updateDate < @endtime) set @w8=(select count(recordVelocity) from GPSHistoryData where plateNo = @no and recordVelocity > 270 and updateDate > @starttime and updateDate < @endtime) -- Insert statements for procedure here if(@w != 0) begin set @c1 = @w1/@w set @c2 = @w2/@w set @c3 = @w3/@w set @c4 = @w4/@w set @c5 = @w5/@w set @c6 = @w6/@w set @c7 = @w7/@w set @c8 = @w8/@wselect @w1/@w,@w2/@w,@w3/@w,@w4/@w,@w5/@w,@w6/@w,@w7/@w,@w8/@wendelsebeginset @c1 = 0 set @c2 = 0 set @c3 = 0 set @c4 = 0 set @c5 = 0 set @c6 = 0 set @c7 = 0 set @c8 = 0endEND2、服务器JAVA代码
public ArrayList<DataChart> getHistoryData(Integer id,String item, String type,String time,String userrealname, String username,String ip,String addr) throws ClassNotFoundException, SQLException{ ArrayList<DataChart> list = new ArrayList<DataChart>();connectDB(); if("year".equals(type)){for (int i = 1; i < 13; i++) {PreparedStatement ps;if("dvrStatus".equals(item)){ps = conn.prepareStatement("select avg(CONVERT (numeric(18,2), "+item+")) as AvgValue from dbo.GPSHistoryData "+ "where plateNo = (select plateNo from dbo.GPSRealData where ID = ?) and updateDate > ? and updateDate < ? and dvrStatus != '149280'"); }else if("status".equals(item)){ps = conn.prepareStatement("select avg(CONVERT (numeric(18,2), "+item+")) as AvgValue from dbo.GPSHistoryData "+ "where plateNo = (select plateNo from dbo.GPSRealData where ID = ?) and updateDate > ? and updateDate < ?"); }else{ps = conn.prepareStatement("select avg("+item+") as AvgValue from dbo.GPSHistoryData "+ "where plateNo = (select plateNo from dbo.GPSRealData where ID = ?) and updateDate > ? and updateDate < ?"); } ps.setInt(1 , id); ps.setString(2, getStartTime(type, time,i)); ps.setString(3, getEndTime(type, time,i));// System.out.println(getStartTime(type, time, i)+"-----------"+getEndTime(type, time, i)); ResultSet rs = ps.executeQuery(); if(rs.next()){ DataChart dataChart = new DataChart(); dataChart.setAvgValue(rs.getFloat("AvgValue")); dataChart.setDataTime(getStartTime(type, time, i).substring(0,7)); list.add(dataChart); } }}else if("month".equals(type)){int days = getDays(time);for (int i = 1; i < days+1; i++) {PreparedStatement ps;if("dvrStatus".equals(item)){ps = conn.prepareStatement("select avg(CONVERT (numeric(18,2), "+item+")) as AvgValue from dbo.GPSHistoryData "+ "where plateNo = (select plateNo from dbo.GPSRealData where ID = ?) and updateDate > ? and updateDate < ? and dvrStatus != '149280'"); }else if("status".equals(item)){ps = conn.prepareStatement("select avg(CONVERT (numeric(18,2), "+item+")) as AvgValue from dbo.GPSHistoryData "+ "where plateNo = (select plateNo from dbo.GPSRealData where ID = ?) and updateDate > ? and updateDate < ?"); }else{ps = conn.prepareStatement("select avg("+item+") as AvgValue from dbo.GPSHistoryData "+ "where plateNo = (select plateNo from dbo.GPSRealData where ID = ?) and updateDate > ? and updateDate < ?"); } ps.setInt(1 , id); ps.setString(2, getStartTime(type, time,i)); ps.setString(3, getEndTime(type, time,i)); ResultSet rs = ps.executeQuery(); if(rs.next()){ DataChart dataChart = new DataChart(); dataChart.setAvgValue(rs.getFloat("AvgValue")); dataChart.setDataTime(getStartTime(type, time, i).substring(5,10)); list.add(dataChart); } }}else if("day".equals(type)){for (int i = 0; i < 24; i++) {PreparedStatement ps;if("dvrStatus".equals(item)){ps = conn.prepareStatement("select avg(CONVERT (numeric(18,2), "+item+")) as AvgValue from dbo.GPSHistoryData "+ "where plateNo = (select plateNo from dbo.GPSRealData where ID = ?) and updateDate > ? and updateDate < ? and dvrStatus != '149280'"); }else if("status".equals(item)){ps = conn.prepareStatement("select avg(CONVERT (numeric(18,2), "+item+")) as AvgValue from dbo.GPSHistoryData "+ "where plateNo = (select plateNo from dbo.GPSRealData where ID = ?) and updateDate > ? and updateDate < ?"); }else if("altitude".equals(item)){ps = conn.prepareStatement("select max("+item+") as AvgValue from dbo.GPSHistoryData "+ "where plateNo = (select plateNo from dbo.GPSRealData where ID = ?) and updateDate > ? and updateDate < ?"); }else{ps = conn.prepareStatement("select avg("+item+") as AvgValue from dbo.GPSHistoryData "+ "where plateNo = (select plateNo from dbo.GPSRealData where ID = ?) and updateDate > ? and updateDate < ?"); } ps.setInt(1 , id); ps.setString(2, getStartTime(type, time,i)); ps.setString(3, getEndTime(type, time,i)); ResultSet rs = ps.executeQuery(); if(rs.next()){ DataChart dataChart = new DataChart(); dataChart.setAvgValue(rs.getFloat("AvgValue")); dataChart.setDataTime((Integer.valueOf(getStartTime(type, time, i).substring(11,13))+1)+":00"); list.add(dataChart); } }}else if("now".equals(type)){int y = Integer.valueOf(time.substring(11, 13));//for (int i = 0; i < y; i++) {//PreparedStatement ps;//if("dvrStatus".equals(item)){//ps = conn.prepareStatement("select avg(CONVERT (numeric(18,2), "+item+")) as AvgValue from dbo.GPSHistoryData "//+ "where plateNo = (select plateNo from dbo.GPSRealData where ID = ?) and updateDate > ? and updateDate < ? and dvrStatus != '149280'"); //}else if("status".equals(item)){//ps = conn.prepareStatement("select avg(CONVERT (numeric(18,2), "+item+")) as AvgValue from dbo.GPSHistoryData "//+ "where plateNo = (select plateNo from dbo.GPSRealData where ID = ?) and updateDate > ? and updateDate < ?"); //}else if("altitude".equals(item)){//ps = conn.prepareStatement("select max("+item+") as AvgValue from dbo.GPSHistoryData "//+ "where plateNo = (select plateNo from dbo.GPSRealData where ID = ?) and updateDate > ? and updateDate < ?"); //}else{//ps = conn.prepareStatement("select avg("+item+") as AvgValue from dbo.GPSHistoryData "//+ "where plateNo = (select plateNo from dbo.GPSRealData where ID = ?) and updateDate > ? and updateDate < ?"); //} // ps.setInt(1 , id);// ps.setString(2, getStartTime(type, time,i));// ps.setString(3, getEndTime(type, time,i));// ResultSet rs = ps.executeQuery();// if(rs.next()){// DataChart dataChart = new DataChart();// dataChart.setAvgValue(rs.getFloat("AvgValue"));// dataChart.setDataTime((Integer.valueOf(getStartTime(type, time, i).substring(11,13))+1)+":00");// list.add(dataChart);// } //} PreparedStatement ps = conn.prepareStatement("select avg(CONVERT (numeric(18,2), "+item+")) as AvgValue,Convert(int,DATENAME(Hour,updateDate)) as hour from dbo.GPSHistoryData "+ "where plateNo = (select plateNo from dbo.GPSRealData where ID = ?) and updateDate > ? and updateDate < ? group by DATENAME(Hour,updateDate) order by hour asc"); ps.setInt(1 , id); ps.setString(2, time.substring(0, 10)); ps.setString(3, time.substring(0,8)+(Integer.valueOf(time.substring(8, 10))+1)); ResultSet rs = ps.executeQuery(); while(rs.next()){ DataChart dataChart = new DataChart(); dataChart.setAvgValue(rs.getFloat("AvgValue")); dataChart.setDataTime(rs.getInt("hour")+":00"); list.add(dataChart); } }// PreparedStatement ps = conn.prepareStatement("select avg(?) from dbo.GPSHistoryData where plateNo = (select plateNo from dbo.GPSRealData where ID = ?) and updateDate > ? and updateDate < ?"); // ps.setString(1, item);// ps.setInt(2, id);// ps.setString(3, getStartTime(type, time));// ps.setString(4, getEndTime(type, getStartTime(type, time)));// ResultSet rs = ps.executeQuery();// while(rs.next()){// DataChart dataChart = new DataChart();// dataChart.setAvgValue(rs.getFloat("AvgValue"));// dataChart.setDataTime(rs.getString("DataTime").substring(11, 16));// list.add(dataChart);// } PreparedStatement ps1 = conn.prepareStatement("INSERT INTO dbo.operationLog(userName,detail,createDate,ip,url) values (?,?,?,?,?)"); ps1.setString(1, username+userrealname); ps1.setString(2, "数据图表模块"); ps1.setString(3, getTime()); ps1.setString(4, ip); ps1.setString(5, addr); ps1.executeUpdate();// conn.close(); return list; } public ArrayList<DataChart> getWDData(Integer id, String type,String time,String userrealname, String username,String ip,String addr) throws ClassNotFoundException, SQLException{final String[] WD = {"北风", "东北风", "东风","东南风","南风","西南风","西风","西北风"};ArrayList<DataChart> list = new ArrayList<DataChart>();connectDB(); CallableStatement prepareCall = conn.prepareCall("{call proc_winddirection(?,?,?,?,?,?,?,?,?,?,?)}");if("year".equals(type)){prepareCall.setString(1, time+"-01-01");prepareCall.setString(2, (Integer.valueOf(time)+1)+"-01-01");}else if("month".equals(type)){prepareCall.setString(1, time+"-01");prepareCall.setString(2, Integer.valueOf(time.split("-")[1])==12?(Integer.valueOf(time.split("-")[0])+1)+"-01-01":(time.split("-")[0]+"-"+(Integer.valueOf(time.split("-")[1])+1)+"-01"));}else if("day".equals(type)){prepareCall.setString(1, time);prepareCall.setString(2, getEndTime2(time));}else if("now".equals(type)){prepareCall.setString(1, time);prepareCall.setString(2, getEndTime2(time));}prepareCall.setInt(3, id);prepareCall.registerOutParameter(4, Types.FLOAT);prepareCall.registerOutParameter(5, Types.FLOAT);prepareCall.registerOutParameter(6, Types.FLOAT);prepareCall.registerOutParameter(7, Types.FLOAT);prepareCall.registerOutParameter(8, Types.FLOAT);prepareCall.registerOutParameter(9, Types.FLOAT);prepareCall.registerOutParameter(10, Types.FLOAT);prepareCall.registerOutParameter(11, Types.FLOAT);prepareCall.execute();for (int i = 0; i < WD.length; i++) {DataChart dataChart = new DataChart(); dataChart.setAvgValue(prepareCall.getFloat(i+4)); dataChart.setDataTime(WD[i]); list.add(dataChart);}//ResultSet rs = prepareCall.executeQuery();//int i = 0;//while(rs.next()){////DataChart dataChart = new DataChart();// dataChart.setAvgValue(rs.getFloat(i+1));// dataChart.setDataTime(WD[i]);// list.add(dataChart);// i++;//} PreparedStatement ps1 = conn.prepareStatement("INSERT INTO dbo.operationLog(userName,detail,createDate,ip,url) values (?,?,?,?,?)"); ps1.setString(1, username+userrealname); ps1.setString(2, "数据图表模块"); ps1.setString(3, getTime()); ps1.setString(4, ip); ps1.setString(5, addr); ps1.executeUpdate();// conn.close(); return list; } private String getStartTime(String type,String time,int i){if("year".equals(type)){return time+(i<10?("-0"+i):"-"+i)+"-01";}else if("month".equals(type)){return time+(i<10?("-0"+i):"-"+i);}else if("day".equals(type)){return time+" "+(i<10?("0"+i):i)+":00";}else if("now".equals(type)){return time.substring(0, 10)+" "+(i<10?("0"+i):i)+":00";}return time;}private String getEndTime(String type,String time,int i){if("year".equals(type)){if(i == 12)return ((Integer.valueOf(time))+1)+"-01-01";elsereturn time+(i<9?("-0"+(i+1)):"-"+(i+1))+"-01";}else if("month".equals(type)){if(i==getDays(time)){if(Integer.valueOf(time.substring(5, 7))==12)return (Integer.valueOf(time.substring(0, 4))+1)+"-01-01";elsereturn time.substring(0, 4)+"-"+(Integer.valueOf(time.substring(5, 7))+1)+"-01";}else{return time+(i<9?("-0"+(i+1)):"-"+(i+1));}}else if("day".equals(type)){if(i == 23){return time+" 23:59:59";}else{return time+" "+(i<9?("0"+(i+1)):(i+1))+":00";}}else if("now".equals(type)){if(i == 23){return time.substring(0, 10)+" 23:59:59";}else{return time.substring(0, 10)+" "+(i<9?("0"+(i+1)):(i+1))+":00";}}return time;}final int[] LARGE_MONTH = {1, 3, 5, 7, 8, 10, 12};private int getDays(String time){int month = Integer.valueOf(time.split("-")[1]);int year = Integer.valueOf(time.split("-")[0]);if(isLargeMonth(month))return 31;else if(month == 2){if(new GregorianCalendar().isLeapYear(year))return 29;else return 28;}elsereturn 30;}private boolean isLargeMonth(int month) {for (int i = 0; i < LARGE_MONTH.length; i++) {if (month == LARGE_MONTH[i])return true;}return false;}private String getEndTime2(String time){if(Integer.valueOf(time.split("-")[2])==getDays(time)){if(Integer.valueOf(time.substring(5, 7))==12)return (Integer.valueOf(time.substring(0, 4))+1)+"-01-01";elsereturn time.substring(0, 4)+"-"+(Integer.valueOf(time.substring(5, 7))+1)+"-01";}else{return time.substring(0, 8)+(Integer.valueOf(time.substring(8, 10))+1);}} 0 0
- java 与 存储过程
- 存储过程与JAVA
- java与oracle存储过程
- JAVA与Oracle存储过程
- 存储过程与Java调用
- java存储过程的创建与调用
- JAVA与Oracle存储过程(二)
- 存储过程与存储关系
- 存储过程与存储函数
- java 中 用到的jdbc 调用存储过程，与hibernate 调用存储过程
- ASP与存储过程
- ASP与存储过程
- ASP与存储过程
- ASP与存储过程
- ASP与存储过程
- ASP与存储过程
- ASP与存储过程
- sqlca 与存储过程
- 无效的列类型: getInt not implemented for class oracle.jdbc.driver.T4CRowidAccessor
- 阿里云centos 6安装Nginx+PHP+MySQL
- Ios开发Swift自定义UIbutton增加一个能设置高亮状态下背景颜色的方法
- 彻底删除VS2013
- cracking the coding interview 中文版
- 存储过程与JAVA
- JAVA汉字转拼音
- 如何构建创新文化——技术视角
- fread
- java调用存储过程
- 获取指定颜色和大小的图片
- leveldb代码阅读（13）——数据文件的格式和生成
- 在android studio 中使用SlidingMenu依赖库方法
- Android的Fragment中onActivityResult不被调用的终极解决方案
