一:连接数据库:
package com.files.readdata;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Properties;
import java.util.logging.Level;
import java.util.logging.Logger;
public class DbConnection {
public static Connection getConnection(){
Connection con = null;
Properties properties = new Properties();
InputStream in = Thread.currentThread().getClass().getResourceAsStream("/database.properties");
try {
properties.load(in);
String driver = properties.getProperty("driver");
if(driver != null){
System.setProperty("jdbc.drvers", driver);
}
String url = properties.getProperty("jdbc.url");
String username = properties.getProperty("jdbc.username");
String password = properties.getProperty("jdbc.password");
try {
con = DriverManager.getConnection(url,username,password);
} catch (SQLException e) {
e.printStackTrace();
Logger.getLogger(DbConnection.class.getName()).log(Level.SEVERE, null, e);;
}
} catch (IOException e1) {
e1.printStackTrace();
}
return con;
}
}
二:数据库操作:
package com.files.readdata;
import java.io.IOException;
import java.io.InputStream;
import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.List;
import java.util.Properties;
public class CallDatabase {
private Connection con =null;
public void selectData(List<InfoBean> retList) throws SQLException {
Properties properties = new Properties();
InputStream in = Thread.currentThread().getClass().getResourceAsStream("/database.properties");
String sql;
String sqlSA;
String sqlDW;
con = DbConnection.getConnection();
PreparedStatement pstmt;
ResultSet rs;
try {
properties.load(in);
} catch (IOException e) {
e.printStackTrace();
}
WriteFile writerFile = new WriteFile();
//承保对账NB
List<InfoBean> retListNB = new ArrayList<InfoBean>();
sql = "select seq_No,bill_No,policy_No,certi_No,amount,return_code,return_desc from t_pa_nb_list";
pstmt = con.prepareStatement(sql);
rs = pstmt.executeQuery(sql);
Double sumAmountNB = 0D;
while (rs.next()) {
InfoBean infoBean = new InfoBean();
BigDecimal seqNo = rs.getBigDecimal(1); // 获取的表字段换成自己需要的
String billNo = rs.getString(2);
String policyNo = rs.getString(3);
String certiNo = rs.getString(4);
BigDecimal amount = rs.getBigDecimal(5);
String returnCode = rs.getString(6);
String returnDesc = rs.getString(7);
infoBean.setSeqNo(seqNo);
infoBean.setBillNo(billNo);
infoBean.setPolicyNo(policyNo);
infoBean.setCertiNo(certiNo);
infoBean.setAmount(amount);
infoBean.setReturnCode(returnCode);
infoBean.setReturnDesc(returnDesc);
BigDecimal amountNB = infoBean.getAmount();//获取金额
Double amNB = amountNB.doubleValue();//转换成double型
sumAmountNB = amNB+sumAmountNB;
retListNB.add(infoBean);
}
System.out.println("数据库中总条数NB:"+retListNB.size());
//追加对账SA
sqlSA = "select seq_No,bill_No,policy_No,certi_No,amount,return_code,return_desc from t_cs_sa_list";
pstmt = con.prepareStatement(sqlSA);
rs = pstmt.executeQuery(sqlSA);
Double sumAmountSA = 0D;
List<InfoBean> retListSA = new ArrayList<InfoBean>();
while (rs.next()) {
InfoBean infoBeanSA = new InfoBean();
BigDecimal seqNo = rs.getBigDecimal(1); // 获取的表字段换成自己需要的
String billNo = rs.getString(2);
String policyNo = rs.getString(3);
String certiNo = rs.getString(4);
BigDecimal amount = rs.getBigDecimal(5);
String returnCode = rs.getString(6);
String returnDesc = rs.getString(7);
infoBeanSA.setSeqNo(seqNo);
infoBeanSA.setBillNo(billNo);
infoBeanSA.setPolicyNo(policyNo);
infoBeanSA.setCertiNo(certiNo);
infoBeanSA.setAmount(amount);
infoBeanSA.setReturnCode(returnCode);
infoBeanSA.setReturnDesc(returnDesc);
BigDecimal amountSA = infoBeanSA.getAmount();//获取金额
Double amSA = amountSA.doubleValue();//转换成double型
sumAmountSA = amSA+sumAmountSA;
retListSA.add(infoBeanSA);
}
//领取对账DW
sqlDW = "select seq_No,bill_No,policy_No,certi_No,amount,return_code,return_desc from t_cs_dw_list";
pstmt = con.prepareStatement(sqlDW);
rs = pstmt.executeQuery(sqlDW);
Double sumAmountDW = 0D;
List<InfoBean> retListDW = new ArrayList<InfoBean>();
while (rs.next()) {
InfoBean infoBeanDW = new InfoBean();
BigDecimal seqNo = rs.getBigDecimal(1); // 获取的表字段换成自己需要的
String billNo = rs.getString(2);
String policyNo = rs.getString(3);
String certiNo = rs.getString(4);
BigDecimal amount = rs.getBigDecimal(5);
String returnCode = rs.getString(6);
String returnDesc = rs.getString(7);
infoBeanDW.setSeqNo(seqNo);
infoBeanDW.setBillNo(billNo);
infoBeanDW.setPolicyNo(policyNo);
infoBeanDW.setCertiNo(certiNo);
infoBeanDW.setAmount(amount);
infoBeanDW.setReturnCode(returnCode);
infoBeanDW.setReturnDesc(returnDesc);
BigDecimal amount2 = infoBeanDW.getAmount();//获取金额
Double amDW = amount2.doubleValue();//转换成double型
sumAmountDW = amDW+sumAmountDW;
retListDW.add(infoBeanDW);
}
if (rs != null) {
try {
rs.close();
} catch (Exception e) {
e.printStackTrace();
}
}
if (pstmt != null) {
try {
pstmt.close();
} catch (Exception e) {
e.printStackTrace();
}
}
if (con != null) {
try {
con.close();
} catch (Exception e) {
e.printStackTrace();
}
}
Calendar calendar = Calendar.getInstance();// 此时打印它获取的是系统当前时间
calendar.add(Calendar.DATE, Integer.valueOf(properties.getProperty("indexday"))); // 得到某一天
String yestedayDate = new SimpleDateFormat("yyyyMMdd").format(calendar.getTime());
String yestedayDay = new SimpleDateFormat("yyyyMMddHHmmss").format(calendar.getTime());
String path = properties.getProperty("path");
String filePath = path + yestedayDate;
String suffixs = properties.getProperty("suffixs");
String splict = properties.getProperty("splict");
String prefixion = properties.getProperty("prefixion");//NB
String prefix = properties.getProperty("prefix");//SA
String pref = properties.getProperty("pref");//DW
if(prefixion.equals("NB")){
String filePathName = filePath+splict+prefixion+yestedayDay+suffixs;
writerFile.writeFile(filePath,filePathName,retListNB,yestedayDate,sumAmountNB);
}
if(prefix.equals("SA")){
String filePathName = filePath+splict+prefix+yestedayDay+suffixs;
writerFile.writeFileSA(filePath,filePathName,retListSA,yestedayDate,sumAmountSA);
}
if(pref.equals("DW")){
String filePathName = filePath+splict+pref+yestedayDay+suffixs;
writerFile.writeFileDW(filePath,filePathName,retListDW,yestedayDate,sumAmountDW);
}
}
/**
* 结息文件
* @param settlementBeanList
* @throws SQLException
*/
public void selectSettlementBean(List<SettlementBean> settlementBeanList) throws SQLException{
Properties properties = new Properties();
InputStream in = Thread.currentThread().getClass().getResourceAsStream("/database.properties");
con = DbConnection.getConnection();
PreparedStatement ptmt;
String sqlSM;
ResultSet rs;
try {
properties.load(in);
} catch (IOException e) {
e.printStackTrace();
}
WriteFile writeFileSM = new WriteFile();
List<SettlementBean> settlementBeanlist = new ArrayList<SettlementBean>();
sqlSM = "select seq_no,policy_no,account_type,interest_rate,settle_int_sum,start_capital,end_capital,start_balance,end_balance,day_bene_amount,settle_date from t_settlement_list";
ptmt = con.prepareStatement(sqlSM);
rs = ptmt.executeQuery(sqlSM);
while(rs.next()){
SettlementBean settlement = new SettlementBean();
BigDecimal seqNo = rs.getBigDecimal(1); // 获取的表字段换成自己需要的
String policyNo = rs.getString(2);
String accountType = rs.getString(3);
BigDecimal interestRate = rs.getBigDecimal(4);
BigDecimal settleIntSum = rs.getBigDecimal(5);
BigDecimal startCapital = rs.getBigDecimal(6);
BigDecimal endCapital = rs.getBigDecimal(7);
BigDecimal startBalance = rs.getBigDecimal(8);
BigDecimal endBalance = rs.getBigDecimal(9);
BigDecimal dayBeneAmount = rs.getBigDecimal(10);
Date settleDate = rs.getDate(11);
settlement.setSeqNo(seqNo);
settlement.setPolicyNo(policyNo);
settlement.setAccountType(accountType);
settlement.setInterestRate(interestRate);
settlement.setSettleIntSum(settleIntSum);
settlement.setStartCapital(startCapital);
settlement.setEndCapital(endCapital);
settlement.setStartBalance(startBalance);
settlement.setEndBalance(endBalance);
settlement.setDayBeneAmount(dayBeneAmount);
settlement.setSettleDate(settleDate);
settlementBeanlist.add(settlement);
}
int countINV = settlementBeanlist.size();
if(rs != null){
try {
rs.close();
} catch (Exception e) {
e.printStackTrace();
}
}
if(ptmt != null){
try {
ptmt.close();
} catch (Exception e) {
e.printStackTrace();
}
}
if(con != null){
try {
con.close();
} catch (Exception e) {
e.printStackTrace();
}
}
Calendar calendar = Calendar.getInstance();// 此时打印它获取的是系统当前时间
calendar.add(Calendar.DATE, Integer.valueOf(properties.getProperty("indexday"))); // 得到某一天
String yestedayDate = new SimpleDateFormat("yyyyMMdd").format(calendar.getTime());
String pathINV = properties.getProperty("pathINV");
String filePath = pathINV + yestedayDate;
writeFileSM.writFileINV(countINV,settlementBeanlist,filePath);
}
}
三:写文件
package com.files.readdata;
import java.io.BufferedWriter;
import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStreamWriter;
import java.io.UnsupportedEncodingException;
import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.List;
import java.util.Properties;
public class WriteFile {
/**将数据内容写到txt文件中
* NB领取对账
* @param filePath
* @param filePathName
* @param retList1
*/
public void writeFile(String filePath,String filePathName,List<InfoBean> retListNB,String yestedayDate,Double sumAmountNB){
File folder = new File(filePath); // 自定义文件路径
File file = new File(filePathName); // 自定义文件路径
if(!folder.exists() && !folder.isDirectory()){
folder.mkdir();
}else{
System.out.println("文件夹已存在");
}
if (!file.exists()) {
try {
file.createNewFile();
} catch (IOException e) {
e.printStackTrace();
}
}else{
System.out.println("文件已替换");
}
String encoding = "UTF-8";
OutputStreamWriter write;
try {
write = new OutputStreamWriter(new FileOutputStream(file),encoding);
BufferedWriter writer = new BufferedWriter(write);
System.out.println("汇总金额:"+sumAmountNB);
String returnCode = "0";
String returnDesc = "成功";
System.out.println("交易日期:"+yestedayDate);
int account = retListNB.size();//总笔数
System.out.println("总笔数:"+account);
String strr = yestedayDate+"|"+sumAmountNB+"|"+ account+"|"+returnCode+"|"+returnDesc;
writer.write(strr+"\r\n");
System.out.println(strr);
for (InfoBean ib : retListNB) {
String str = ib.getSeqNo()+"|"+ib.getBillNo()+"|"+ib.getPolicyNo()+"|"+ib.getCertiNo()+"|"+ib.getAmount()+"|"+ib.getReturnCode()+"|"+ib.getReturnDesc();
System.out.println(str);
// writer.write(ib.toString()); // 输出流写入到文件中
writer.write(str+"\r\n");
}
writer.close();
} catch (UnsupportedEncodingException e) {
e.printStackTrace();
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
}
/**
* 追加对账
* @param filePath
* @param filePathName
* @param retListSA
* @param yestedayDate
* @param sumAmountSA
*/
public void writeFileSA(String filePath,String filePathName,List<InfoBean> retListSA,String yestedayDate,Double sumAmountSA){
File folder = new File(filePath); // 自定义文件路径
File file = new File(filePathName); // 自定义文件路径
if(!folder.exists() && !folder.isDirectory()){
folder.mkdir();
}else{
System.out.println("文件夹已存在");
}
if (!file.exists()) {
try {
file.createNewFile();
} catch (IOException e) {
e.printStackTrace();
}
}else{
System.out.println("文件已替换");
}
String encoding = "UTF-8";
OutputStreamWriter write;
try {
write = new OutputStreamWriter(new FileOutputStream(file),encoding);
BufferedWriter writer = new BufferedWriter(write);
System.out.println("SA汇总金额:"+sumAmountSA);
String returnCode = "0";
String returnDesc = "成功";
System.out.println("SA交易日期:"+yestedayDate);
int accountSA = retListSA.size();
System.out.println("SA总笔数:"+accountSA);
String strr = yestedayDate+"|"+sumAmountSA+"|"+ accountSA+"|"+returnCode+"|"+returnDesc;
writer.write(strr+"\r\n");
System.out.println(strr);
for (InfoBean ib : retListSA) {
String str = ib.getSeqNo()+"|"+ib.getBillNo()+"|"+ib.getPolicyNo()+"|"+ib.getCertiNo()+"|"+ib.getAmount()+"|"+ib.getReturnCode()+"|"+ib.getReturnDesc();
System.out.println(str);
// writer.write(ib.toString()); // 输出流写入到文件中
writer.write(str+"\r\n");
}
writer.close();
} catch (UnsupportedEncodingException e) {
e.printStackTrace();
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
}
/**
* 承保对账
* @param filePath
* @param filePathName
* @param retListDW
* @param yestedayDate
* @param sumAmountDW
*/
public void writeFileDW(String filePath,String filePathName,List<InfoBean> retListDW,String yestedayDate,Double sumAmountDW){
File folder = new File(filePath); // 自定义文件路径
File file = new File(filePathName); // 自定义文件路径
if(!folder.exists() && !folder.isDirectory()){
folder.mkdir();
}else{
System.out.println("文件夹已存在");
}
if (!file.exists()) {
try {
file.createNewFile();
} catch (IOException e) {
e.printStackTrace();
}
}else{
System.out.println("文件已替换");
}
String encoding = "UTF-8";
OutputStreamWriter write;
try {
write = new OutputStreamWriter(new FileOutputStream(file),encoding);
BufferedWriter writer = new BufferedWriter(write);
System.out.println("SA汇总金额:"+sumAmountDW);
String returnCode = "0";
String returnDesc = "成功";
System.out.println("SA交易日期:"+yestedayDate);
int accountDW = retListDW.size();
System.out.println("SA总笔数:"+accountDW);
String strr = yestedayDate+"|"+sumAmountDW+"|"+ accountDW+"|"+returnCode+"|"+returnDesc;
writer.write(strr+"\r\n");
System.out.println(strr);
for (InfoBean ib : retListDW) {
String str = ib.getSeqNo()+"|"+ib.getBillNo()+"|"+ib.getPolicyNo()+"|"+ib.getCertiNo()+"|"+ib.getAmount()+"|"+ib.getReturnCode()+"|"+ib.getReturnDesc();
System.out.println(str);
writer.write(str+"\r\n");
}
writer.close();
} catch (UnsupportedEncodingException e) {
e.printStackTrace();
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
}
/**
* 写文件
* @param filePath
* @param filePathName
* @param settlementBeanlist
* @param yestedayDate
* @param emptyFile
* @return
*/
public void writFileINV(int countINV,List<SettlementBean> settlementBeanlist,String filePath){
//定义字符编码格式
String encoding = "UTF-8";
try {
int i = 0;
int y = 0;
File file = createFileINV(i);
File file1 = null;
OutputStreamWriter outWrite = new OutputStreamWriter(new FileOutputStream(file),encoding);
for(SettlementBean stb: settlementBeanlist){//进行写文件
String settnew = stb.getSeqNo()+"|"+stb.getPolicyNo()+"|"+stb.getAccountType()+"|"+stb.getInterestRate()+"|"+stb.getSettleIntSum()+"|"+stb.getStartCapital()+"|"+stb.getEndCapital()+"|"+stb.getStartBalance()+"|"+stb.getEndBalance()+"|"+stb.getDayBeneAmount()+"|"+stb.getSettleDate();
i++;
if (y <= 4999) {
outWrite.write(settnew+"\r\n");
y++;
}
if (y%5000==0) {
outWrite.close();
file1 = createFileINV(i);
outWrite = new OutputStreamWriter(new FileOutputStream(file1),encoding);
// outWrite.write(settnew+"\r\n");
y=0;
}
}
outWrite.close();
} catch (UnsupportedEncodingException e) {
e.printStackTrace();
} catch (FileNotFoundException e) {
e.printStackTrace();
}catch (IOException e) {
e.printStackTrace();
}
}
/**
* 创建文件
* @param countINV
* @return
*/
public File createFileINV(int i){
int mulriple= (i/5000)+1;
String strp=String.format("%03d",mulriple);
Properties properties = new Properties();
InputStream in = Thread.currentThread().getClass().getResourceAsStream("/database.properties");
try {
properties.load(in);
} catch (IOException e) {
e.printStackTrace();
}
Calendar calendar = Calendar.getInstance();// 此时打印它获取的是系统当前时间
calendar.add(Calendar.DATE, Integer.valueOf(properties.getProperty("indexday")));
String yestedayDay = new SimpleDateFormat("yyyyMMddHHmmss").format(calendar.getTime());
String suffixs = properties.getProperty("suffixs");
String splict = properties.getProperty("splict");
String pre = properties.getProperty("pre");//INV
String suffix = properties.getProperty("suffix");//tmp
String yestedayDate = new SimpleDateFormat("yyyyMMdd").format(calendar.getTime());
String pathINV = properties.getProperty("pathINV");
String filePath = pathINV + yestedayDate;
String beforeFileName = filePath+splict+pre+yestedayDay+strp;
String filePathName = beforeFileName+suffixs;
String emptyFile = beforeFileName+suffix;
File folder = new File(filePath);
File file = new File(filePathName);
File file2 = new File(emptyFile);
if(!folder.exists() && !folder.isDirectory()){
folder.mkdir();
}else{
System.out.println("文件夹已存在");
}
if(!file.exists() && !file.isDirectory()){
try {
file.createNewFile();
} catch (IOException e) {
e.printStackTrace();
}
}else{
System.out.println("文件已替换");
}
if(!file2.exists() && !file2.isDirectory()){
try {
file2.createNewFile();
} catch (IOException e) {
e.printStackTrace();
}
}else{
System.out.println("文件已替换");
}
return file;
}
}
四:主函数运行
package com.files.readdata;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
/**
* 主函数运行
*
* @author Frank.dai
*
*/
public class Execution {
public static void main(String[] args) {
CallDatabase callDatabase = new CallDatabase();
List<InfoBean> retList = new ArrayList<InfoBean>();
List<SettlementBean> settlementBeanList = new ArrayList<SettlementBean>();
try {
callDatabase.selectData(retList);
callDatabase.selectSettlementBean(settlementBeanList);
} catch (SQLException e) {
e.printStackTrace();
}
}
}