jdbc 操作 批量插入数据

package com.soft.dzkj.dao;

 

import java.sql.Connection;

import java.sql.DriverManager;

 

public class BaseDao {

 

private static String driver = "com.mysql.jdbc.Driver";

private static String url = "jdbc:mysql://192.168.0.200:3306/mkt_tw?useUnicode=true&characterEncoding=UTF-8";

private static String userName = "root";

private static String passWord = "root";

private static Connection con = null;

 

/**

* 创建连接

*/

public static Connection createConnection() {

try {

Class.forName(driver);

con = DriverManager.getConnection(url, userName, passWord);

} catch (Exception ce) {

ce.printStackTrace();

}

return con;

}

 

/**

* 关闭连接

*/

public static void closeConnection(Connection con) {

try {

con.close();

} catch (Exception ce) {

ce.printStackTrace();

}

}

}

 

 

package com.soft.dzkj.dao;

 

import java.sql.Connection;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.util.HashSet;

import java.util.List;

import java.util.Set;

 

import com.soft.dzkj.bean.StkDayHkex;

 

public class HkDayDao {

 

private final static int MAX = 500;

 

private Connection con=null;

 

private PreparedStatement pstm = null;

 

/**

* 保存港股日K

* @param hkdayList

* @throws Exception

*/

public void saveHkDayInfo(List<StkDayHkex> hkdayList){

try{

String sql=" insert into stk_day_hkex(asset_id,date,stk_code,open,high,low,close,prev_close,volume,turnover,turn_rate,adj_factor,"

+ " k_val,k_val_adj,d_val,d_val_adj,j_val,j_val_adj,ema1,ema2,dea,ema1_bkw,ema2_bkw,dea_bkw,"

+ " up_seq1,down_seq1,up_seq2,down_seq2,up_seq3,down_seq3,"

+ " up_seq1_bkw,down_seq1_bkw,up_seq2_bkw,down_seq2_bkw,up_seq3_bkw,down_seq3_bkw) "

+ " values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";

con=BaseDao.createConnection();

con.setAutoCommit(false);     

pstm = con.prepareStatement(sql);

int size=hkdayList.size();

for(int i=0;i<size;i++){         

StkDayHkex day=hkdayList.get(i);

pstm.setString(1,day.getAssetId());

pstm.setString(2,day.getDateStr());

pstm.setString(3,day.getStkCode());

pstm.setDouble(4,day.getOpen());

pstm.setDouble(5,day.getHigh());

pstm.setDouble(6,day.getLow());

pstm.setDouble(7,day.getClose());

pstm.setDouble(8, day.getPrevClose());

pstm.setDouble(9, day.getVolume());

pstm.setDouble(10, day.getTurnover());

pstm.setDouble(11, 0);

pstm.setDouble(12, 1);

 

pstm.setDouble(13, 0);

pstm.setDouble(14, 0);

pstm.setDouble(15, 0);

pstm.setDouble(16, 0);

pstm.setDouble(17, 0);

pstm.setDouble(18, 0);

pstm.setDouble(19, 0);

pstm.setDouble(20, 0);

pstm.setDouble(21, 0);

pstm.setDouble(22, 0);

pstm.setDouble(23, 0);

pstm.setDouble(24, 0);

 

pstm.setDouble(25, 0);

pstm.setDouble(26, 0);

pstm.setDouble(27, 0);

pstm.setDouble(28, 0);

pstm.setDouble(29, 0);

pstm.setDouble(30, 0);

 

pstm.setDouble(31, 0);

pstm.setDouble(32, 0);

pstm.setDouble(33, 0);

pstm.setDouble(34, 0);

pstm.setDouble(35, 0);

pstm.setDouble(36, 0);

 

   pstm.addBatch();     

   if(i%MAX==0){

     pstm.executeBatch();     

     con.commit();     

     pstm.clearBatch();     

  }

}

pstm.executeBatch();

con.commit();

con.setAutoCommit(true);

pstm.close();

con.close();  

}catch(Exception ce){

ce.printStackTrace();

}finally{

BaseDao.closeConnection(con);

}

}

 

/**

* 查询已存在的数据

* @param queryDate

* @return

*/

public Set<String> queryExit(String queryDate){ 

Set<String> assetIds=new HashSet<String>();

String sql = "SELECT distinct asset_id FROM stk_day_hkex where date<=? ";

try{

con=BaseDao.createConnection();

pstm = con.prepareStatement(sql);

pstm.setString(1, queryDate);

ResultSet rs = pstm.executeQuery();

while (rs.next()) {

assetIds.add(rs.getString("asset_id"));

}

}catch(Exception ce){

ce.printStackTrace();

}

return assetIds;

}

 

}

 

package com.soft.dzkj.server;

 

import java.text.SimpleDateFormat;

import java.util.ArrayList;

import java.util.Date;

import java.util.List;

import java.util.Set;

 

import com.alibaba.fastjson.JSONArray;

import com.alibaba.fastjson.JSONObject;

import com.soft.dzkj.bean.AssetInfo;

import com.soft.dzkj.bean.StkDayHkex;

import com.soft.dzkj.dao.AssetInfoDao;

import com.soft.dzkj.dao.HkDayDao;

import com.soft.dzkj.util.HttpTool;

 

public class DayServer {

 

public static void main(String [] args){

System.out.println("开始");

AssetInfoDao assetDao=new AssetInfoDao();

HkDayDao dayDao=new HkDayDao();

String serverIp="http://weixin.guosen.com.cn:9102";

java.text.SimpleDateFormat df=new java.text.SimpleDateFormat("yyyy-MM-dd");

String minDateStr="2017-05-09";

String queryDate="20170509";

DayServer t=new DayServer();

t.queryHkDay(serverIp, minDateStr,queryDate,df, assetDao,dayDao);

System.out.println("结束");

}

 

/**

* 查询日k

* @param serverIp

* @param queryDate

* @param dateStr

* @param df

* @param assetDao

*/

private void queryHkDay(String serverIp,String minDateStr,String queryDate,SimpleDateFormat df,AssetInfoDao assetDao,HkDayDao dayDao){

try{

Date minDate=df.parse(minDateStr);

//补历史数据

List<AssetInfo> assetInfoList=assetDao.queryAssetInfo(); 

Set<String> assetIds=dayDao.queryExit(minDateStr);

for(AssetInfo entry:assetInfoList){

String assetId=entry.getAssetId();

String stkCode=entry.getStkCode();

String code="";

boolean flag=false;

   if(assetIds.size()==0){

  flag=true;

       }else{

      if(!assetIds.contains(assetId)){

      flag=true;

      }

       }

if(flag){

   List<StkDayHkex> dayHkList=new ArrayList<StkDayHkex>();

   code=stkCode+".3";

   JSONObject jsonObj=new JSONObject();

   jsonObj.put("SerID", 191);

jsonObj.put("MF", 2);

jsonObj.put("SF", 0);

 

String str="code="+code+"&date="+queryDate+"&time=0&type=513&direct=1&recover=0&count=500&return=4"; 

jsonObj.put("Req", str);

int len=jsonObj.toString().length();

String head="1,"+len+",0,100,114,0,0,0,0,\r\n\r\n";

String content=head+jsonObj.toString();

String resultStr=HttpTool.httpToJson(serverIp, "POST", content, "UTF-8");

System.out.println(code+"返回:"+resultStr);

int start=resultStr.indexOf("{");

String con=resultStr.substring(start);

if(con.contains("?")){

System.out.println(assetId+"查询出现乱码了");

break;

}else{

   JSONObject obj=JSONObject.parseObject(con);

if(obj.containsKey("Ret")){

JSONArray array=obj.getJSONArray("Ret");

int size=array.size();

if(size>0){

convertHkDay(size, array, assetId, stkCode, df, minDate, dayHkList);

} 

if(dayHkList.size()>0){

System.out.println("save data:"+assetId);

dayDao.saveHkDayInfo(dayHkList);

}

Thread.sleep(2000); 

dayHkList.clear();

}else{

System.out.println(assetId+"无返回数据");

Thread.sleep(500);

}

 }

  }

}

}catch(Exception ce){

System.out.println("query sh day error"); 

ce.printStackTrace();

}

}

 

/**

* 设置日K

* @param size

* @param array

* @param assetId

* @param stkCode

* @param df

* @param minDate

* @param dayShList

* @throws Exception

*/

private void convertHkDay(int size,JSONArray array,String assetId,String stkCode,SimpleDateFormat df,Date minDate,List<StkDayHkex> dayShList)throws Exception{

for(int i=0;i<size;i++){

if(i==1){

 JSONArray jsarrays=(JSONArray)array.get(i);

 for(int j=0;j<jsarrays.size();j++){

if(j>0){

  JSONArray details=(JSONArray)jsarrays.get(j);

  StkDayHkex daySh=new StkDayHkex();

  //资产ID

  daySh.setAssetId(assetId);

  //股票代码

  daySh.setStkCode(stkCode);

  String hqda=details.get(0).toString();

  String date=hqda.substring(0, 4)+"-"+hqda.substring(4,6)+"-"+hqda.substring(6);

  //日期

  daySh.setDateStr(date);

  Date queryDate=df.parse(date);

  String prevClose=details.get(2).toString();

  //前收盘价

  daySh.setPrevClose(Double.valueOf(prevClose));

  String open=details.get(3).toString();

  //开盘价

  daySh.setOpen(Double.valueOf(open));

  String high=details.get(4).toString();

  //最高价

  daySh.setHigh(Double.valueOf(high));

  String low=details.get(5).toString();

  //最低价

  daySh.setLow(Double.valueOf(low));

  String close=details.get(6).toString();

  //收盘价

  daySh.setClose(Double.valueOf(close));

  String volume=details.get(7).toString();

  //成交量

  daySh.setVolume(Double.valueOf(volume)/100); 

  String turnover=details.get(8).toString();

  //成交额

  daySh.setTurnover(Double.valueOf(turnover));

/*//单日补数

if(daySh.getDate().compareTo(minDate)==0){

dayShList.add(daySh);

}*/

   if(queryDate.compareTo(minDate)<=0){

dayShList.add(daySh);

  }

}

 }

  }

}

}

 

}