存储过程实现版

-----存储过程和表

create table TopUsers

(
id int identity(1,1) primary key,---编号
SellerNick varchar(600),
SellerId int,


)
---open_Trade a
create table open_Trade
(
id int identity(1,1) primary key,---编号
tid int,
buyer_nick varchar(200),
created varchar(200),---时间
pay_time  varchar(200),--时间
seller_nick varchar(300),
)
--open_Order b
create table open_Order
(
id int identity(1,1) primary key,---编号
oid int,
tid int,
num varchar(400),
payment varchar(200),
num_iid varchar(300),


)
select * from TopUsers
select * from open_Trade
select * from open_Order
op_auction_buy_information 
@seller_id=1,
@auction=1,
@begintime=2010525, ---开始时间是
@endtime=20170829,--
@page=1
--=============================================
select a.tid,b.oid,(case when a.buyer_nick is null then '-' else a.buyer_nick end),b.num,convert(nvarchar(50),a.created,20),convert(nvarchar(50),a.pay_time,20),
convert(varchar(10),datediff(s,created,pay_time)/3600)+':'+(case when len(convert(varchar(10),(datediff(s,created,pay_time)/60)%60))=1 then '0'+convert(varchar(10),(datediff(s,created,pay_time)/60)%60) else convert(varchar(10),(datediff(s,created,pay_time)/60)%60)end)+':'+
       (case when len(convert(varchar(10),datediff(s,created,pay_time)%60))=1 then '0'+convert(varchar(10),datediff(s,created,pay_time)%60) else convert(varchar(10),datediff(s,created,pay_time)%60) end),b.payment
 from  open_Trade a(nolock),open_Order b(nolock)   where a.tid=b.tid and pay_time between '2015-05-05' and '2017-09-26' and a.seller_nick='四海知音乐淘' and b.num_iid=1 and a.pay_time<>'1970-01-01'


-- Author: <Amy>
-- Create date: <20170105>
-- Description: <宝贝购买信息表>
-- =============================================




--op_auction_buy_information @seller_id=743524358,@auction=22349808348,@begintime=20170101,@endtime=20170107,@page=2
create PROCEDURE op_auction_buy_information
(@seller_id BIGINT,@auction bigint,@begintime bigint,@endtime bigint,@page int)
AS
BEGIN
SET NOCOUNT ON
declare @sller_nick nvarchar(50),@begin varchar(50),@end varchar(50),@sql nvarchar(2000),@SQL1 nvarchar(200)
create table #trade(Tid nvarchar(50),oid nvarchar(50),Buyer_nick nvarchar(50),Num int,Create_time nvarchar(50),Payment_time nvarchar(50),Difference_time nvarchar(50),Payment decimal(18,2))


select @begin=(@page-1)*10+1,@end=@page*10
select @sller_nick=SellerNick from TopUsers nolock where SellerId=@seller_id
 
insert into  #trade
select  distinct a.tid,b.oid,(case when a.buyer_nick is null then '-' else a.buyer_nick end),b.num,convert(nvarchar(50),a.created,20),convert(nvarchar(50),a.pay_time,20),
       convert(varchar(10),datediff(s,created,pay_time)/3600)+':'+(case when len(convert(varchar(10),(datediff(s,created,pay_time)/60)%60))=1 then '0'+convert(varchar(10),(datediff(s,created,pay_time)/60)%60) else convert(varchar(10),(datediff(s,created,pay_time)/60)%60)end)+':'+
       (case when len(convert(varchar(10),datediff(s,created,pay_time)%60))=1 then '0'+convert(varchar(10),datediff(s,created,pay_time)%60) else convert(varchar(10),datediff(s,created,pay_time)%60) end)
,b.payment from open_Trade a(nolock),open_Order b(nolock)  where a.tid=b.tid 


select @SQL1='select count(*) as 数量 from #trade '
select @sql='select distinct 父订单编号,子订单编号,买家ID,商品购买数量,下单时间,付款时间,时间差,实付金额 from (select *,ROW_NUMBER() OVER(ORDER BY 下单时间  )as RowRank from ( select Tid as 父订单编号,oid as 子订单编号,Buyer_nick as 买家ID,Num as 商品购买数量,
             Create_time as 下单时间,Payment_time as 付款时间,Difference_time as 时间差,Payment as 实付金额 from #trade ) as t1 ) as t2 where RowRank between '+ @begin +' and '+ @end +' order by 下单时间'
exec(@SQL1)
exec(@sql)
 

END

--------java代码

package com.yanshu.service;


import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;


import com.alibaba.fastjson.JSON;
import com.yanshu.util.ConnSql;
import com.yanshu.util.JdbcUtil;
/**
 * 成功的读取
 * @author Administrator
 *
 */
public class ProcInfor {
public static Map<String , Object> findjimi(String storageName , String[] args){


CallableStatement cs = null;


ResultSet rs = null;
Map<String, Object> map=new HashMap<String ,Object>();
Connection conn = JdbcUtil.getConn();
try {
String newStorageName = ConnSql.storageNameHandle(storageName, args.length);
cs = conn.prepareCall("{call "+newStorageName+"}");
for (int i = 0; i < args.length; i++) {
cs.setString(i+1, args[i]);
}
rs = cs.executeQuery();
ResultSetMetaData  rsmd=rs.getMetaData();
/**
* 读取
*/
/**
* 遍历循环出表的列名
*/
List list=new ArrayList<>();
for(int i=0;i<rsmd.getColumnCount();i++)
{
String columnLabel=rsmd.getColumnLabel(i+1);
list.add(columnLabel);
}
list= (List) JSON.toJSON(list);
System.out.println("表的列名---"+list);
/**
* 遍历循环出列向对应的字段
*/
List list2=new ArrayList<>();
int ii=rsmd.getColumnCount();
System.out.println(ii);
while(rs.next()){
 
   List list3=new ArrayList<>();
   for(int i=1;i<=ii;i++){
Object columnValue=rs.getObject(i);
    list3.add(columnValue);
   }
   list2.add(list3);
}
Map<String, ArrayList<?>> map1=new HashMap<String,ArrayList<?>>();
Map<String, Object> map2=new HashMap<String,Object>();

map2.put("rows", list2);
map.put("count", 2);
map2.put("columns", list);
map.put("data", map2);



} catch (SQLException e) {
e.printStackTrace();
}
return map ;  

}

public static void main(String[] args) {
 /**
  * op_auction_buy_information 
  @seller_id=743524358,
  @auction=22349808348,
  @begintime=20170101,
  @endtime=20170107,
  @page=2
  */
  String[] par=new String[]{"1","1","2010525","20170829","1"};
  Map<String,Object> map1=findjimi("op_auction_buy_information", par);
  System.out.println( JSON.toJSON(map1));
     

}


}