SQL查询 - ITeye问答

0 0

SQL查询0

String sql = "";
sql = "select t3.pack_num, t2.order_num from lgs_pick_data t,lgs_stack_data t2, lgs_stack_material t3,tcs_ct_userorder t4"
+ " where t.ship_lot_num='"
+ shipLotNum
+ "'"
+ " and t.pick_num = t2.pick_num"
+ " and t.year = t2.year"
+ " and t3.stacking_rec_num = t2.stacking_rec_num"
+ " and t3.manu_id = t2.manu_id "
+ " and t4.order_num=t2.order_num "
+ " and t2.company_code=t4.company_code"
+ " and t4.ord_owner='"
+ memberId
+ "' order by t2.order_num";
/**
* 调用下载程序
*/
String factoryId = getFactoryId(orderNum1);
if(factoryId!=null && factoryId.length()>=3 && Constants.FACTORY_ID_TG_COMPANY_CODE.equals(factoryId.substring(1,3))){//
if(orderNum1.startsWith("J")){
sql = "downLoadTGJ";
}else{
sql = "downLoadTG";
}
}else{
sql = (String) Constants.prodSql2Map
.get(prod);
}

String sqlFlag = sql;
if (sql == null) {
continue;
}
logger.info("prod =" + prod + ",sql key = "
+ sql);
String[] s = cdtcListForSql.split(",");
List pickNums = new ArrayList();
if (sql.equals("downQaReZhaWithBill")) {
sql = Constants.sqlMap.get(sql).toString();
// 用值来替代sql中的问号
sql = sql.replaceFirst("\\?",
cdtcListForSql);
sql = sql.replaceFirst("\\?", "'"
+ shipLotNum + "'");
sql = sql.replaceFirst("\\?", "'"
+ orderNum1 + "'");
// sql = sql.replaceFirst("\\?",
// cdtcListForSql);
// sql = sql.replaceFirst("\\?", "'"
// + shipLotNum + "'");
} else {
if("downLoadTGJ".endsWith(sql)){
sql=Constants.SQL_TG_JPROD;

sql = sql.replaceFirst("\\?",
cdtcListForSql);
}else if("downLoadTG".endsWith(sql)){
sql=Constants.SQL_TG_EXCEPT_JPROD;
}else{
sql = Constants.sqlMap.get(sql)
.toString();
sql = sql.replaceFirst("\\?",
cdtcListForSql);
}

sql = sql.replaceFirst("\\?", "'"
+ shipLotNum + "'");
sql = sql.replaceFirst("\\?", "'"
+ orderNum1 + "'");
}
logger.info("=================下载SQL：========"+ sql);
if (collectType != null && !"".equals(collectType)) {
sql = sql.replaceFirst("\\?", "'" + collectType + "'");
} else {
sql = sql.replaceFirst("and B.collect_type=\\?", "");
}
log.info("20080115" + sql);
log.info(cdtcListForSql + " "+ shipLotNum);
ps = con.prepareStatement(sql);
rs = ps.executeQuery();
// create date cell
HSSFCellStyle cellStyleDate = wb.createCellStyle();
cellStyleDate.setDataFormat(HSSFDataFormat
.getBuiltinFormat("yyyy-mm-dd"));
int rsCount = 0;// 记录每个品种的结果集数
while (rs.next()) {
rsCount++;
rsmd = rs.getMetaData();
orderNum = rs.getString("ORDER_NUM");
System.out.println(orderNum);
if ("".equals(oldOrderNum)) {
oldOrderNum = orderNum;
}
if (rownum == 0) {
for (int i = 0; i < rsmd.getColumnCount(); i++) {
// create cell
HSSFCell cell = srow.createCell((short) i);
cell.setEncoding(HSSFCell.ENCODING_UTF_16);
cell.setCellValue(rsmd.getColumnLabel(i + 1));
}
}
rownum++;
srow = sheet.createRow((short) rownum);
if (!oldOrderNum.equals(orderNum)) {
rownum++;
srow = sheet.createRow((short) rownum);
oldOrderNum = orderNum;
}
// hasFound = true;

for (int i = 0; i < rsmd.getColumnCount(); i++) {

if ("DATEB_L".equals(rsmd.getColumnLabel(i + 1))) {
String dateb_l = TLfqmUtil.getString(TLfqmUtil.getRsString(rs,i + 1));
if (dateb_l != null && dateb_l.trim().length() > 0) {
SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMdd");
Date newDate = sdf.parse(dateb_l);
HSSFCell cell = srow.createCell((short) i);
cell.setCellValue(TLfqmUtil.getDate(newDate,"yyyy-MM-dd"));
cell.setCellStyle(cellStyleDate);
}
} else {
HSSFCell cell = srow.createCell((short) i);
cell.setEncoding(HSSFCell.ENCODING_UTF_16);
cell.setCellValue(TLfqmUtil.getString(TLfqmUtil.getRsString(rs,i + 1)));
}
}
}
log.info("result size:" + rsCount);
ps.close();
rs.close();
}
不太理解这些SQL 请帮我解析一下大概指点一下还有sql.replaceFirst("\\?", "'" + collectType + "'");这是什么意思

SQL