JDBC进行子查询,接收多个值

ゝ一纸荒年。 2022-06-02 12:20 218阅读 0赞

package com.yanshu.service;

import java.util.HashMap;  
import java.util.List;  
import java.util.Map;  
  
  
import org.springframework.stereotype.Service;  
  
  
import com.alibaba.fastjson.JSON;  
import com.yanshu.tools.JdbcSelectUtils;  
import com.yanshu.utils.JsonUtil;  
import com.yanshu.utils.StringUtils;  
@Service  
public class Open\_CateServic \{  
public  String getAuctionEval(Object\[\] aucId) \{  
Map map1=new HashMap();  
map1.put("code", 304);  
map1.put("message", "The parameter cannot be empty!!!");  
try \{  
if(!StringUtils.isNullorEmpty(aucId))\{  
  
StringBuilder stBuilder = new StringBuilder();  
StringBuilder sstcout = new StringBuilder();  
  
  
  
String sql = " select id,cid,is\_parent,\`name\`,parent\_cid,sort\_order,\`status\`,taosir\_cat,operationTime from open\_Cate"  
\+ " where "  
\+ " cid in  ";  
stBuilder.append(sql);  
if (!StringUtils.isNullorEmpty(aucId)) \{  
StringBuilder idd=new StringBuilder();  
idd.append("(");  
for (Object string : aucId) \{  
idd.append("'"+string+"',");  
\}  
if(idd.length()>0)\{  
idd.deleteCharAt(idd.length()-1);  
\}  
idd.append(")");  
sstcout.append(idd);  
stBuilder.append(idd);  
  
System.out.println("sql--->>>>"+sql);  
String sqllat = stBuilder.toString();  
List<Map<String, Object>> table = JdbcSelectUtils.query(sqllat, null);  
System.out.println("table---->>>"+table);  
int size = table.size();  
return size!=0?JSON.toJSONString(table):JsonUtil.errorMessages(8);  
\}  
  
\}   
  
else\{  
  
return JSON.toJSONString(map1);  
  
\}  
\} catch (NumberFormatException e) \{  
Map map=new HashMap();  
map.put("code", 303);  
map.put("message", "Number Format Exception");  
return JSON.toJSONString(map);  
  
  
\}  
  
  
return JSON.toJSONString(map1);  
\}

\---

package com.yanshu.tools;  
  
  
import java.sql.Connection;  
import java.sql.PreparedStatement;  
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.yanshu.utils.JdbcUtil;  
  
  
  
  
public class JdbcSelectUtils \{  
public static List<Map<String, Object>> query(String sql,Map<Integer, Object> conditionMap)\{  
      
    Connection conn = null;  
    PreparedStatement pstmt = null;  
    ResultSet rs = null;  
    List<Map<String, Object>> resultList = new ArrayList<Map<String, Object>>();  
    try \{  
   conn =JdbcUtil.getConn();  
   pstmt = conn.prepareStatement(sql);  
    
   if(conditionMap!=null&&conditionMap.size()!=0)\{  
     
   int paramNum = conditionMap.size();  
   for(int i=1;i<=paramNum;i++)\{  
      
   Object paramValue = conditionMap.get(i);  
      
    if("java.lang.Integer".equalsIgnoreCase(paramValue.getClass().getName()))\{  
     pstmt.setInt(i, Integer.parseInt(paramValue.toString()));  
    \}else if("java.lang.String".equalsIgnoreCase(paramValue.getClass().getName()))\{  
     pstmt.setString(i, paramValue.toString());  
    \}  
   \}  
  \}  
    
  rs = pstmt.executeQuery();  
    
  ResultSetMetaData rsmd = rs.getMetaData();  
  int columnNum = rsmd.getColumnCount();  
  List listcolumn=new ArrayList();  
  while(rs.next())\{  
   Map<String, Object> dataMap = new HashMap<String, Object>(0);  
   for(int i=1;i<=columnNum;i++)\{  
   String columnName = rsmd.getColumnName(i);  
   //dataMap.put(columnName,i+1);  
    dataMap.put(columnName, rs.getObject(i));  
   \}  
   resultList.add(dataMap);  
  \}  
    
 \} catch (Exception e) \{  
  e.printStackTrace();  
 \}finally\{  
 free(conn, pstmt, rs);  
 \}  
     
    return resultList;  
   \}   
public static List queryAll(String sql,Map<Integer, Object> conditionMap)\{  
      
    Connection conn = null;  
    PreparedStatement pstmt = null;  
    ResultSet rs = null;  
    List listcolumn=new ArrayList();  
    List<Map<String, Object>> resultList = new ArrayList<Map<String, Object>>();  
    try \{  
   conn =JdbcUtil.getConn();  
   pstmt = conn.prepareStatement(sql);  
    
   if(conditionMap!=null&&conditionMap.size()!=0)\{  
     
   int paramNum = conditionMap.size();  
   for(int i=1;i<=paramNum;i++)\{  
      
   Object paramValue = conditionMap.get(i);  
      
    if("java.lang.Integer".equalsIgnoreCase(paramValue.getClass().getName()))\{  
     pstmt.setInt(i, Integer.parseInt(paramValue.toString()));  
    \}else if("java.lang.String".equalsIgnoreCase(paramValue.getClass().getName()))\{  
     pstmt.setString(i, paramValue.toString());  
    \}  
   \}  
  \}  
     
    
  rs = pstmt.executeQuery();  
    
  ResultSetMetaData rsmd = rs.getMetaData();  
  int columnNum = rsmd.getColumnCount();  
  Map<String, Object> dataMap = new HashMap<String, Object>(0);  
   for(int i=1;i<=columnNum;i++)\{  
   String columnName = rsmd.getColumnName(i);  
   //dataMap.put(columnName, columnName);  
   listcolumn.add(columnName);  
   \}  
     
 /\* while(rs.next())\{  
   Map<String, Object> dataMap = new HashMap<String, Object>(0);  
   for(int i=1;i<=columnNum;i++)\{  
   String columnName = rsmd.getColumnName(i);  
     
   listcolumn.add(columnName);  
   \}  
  \}\*/  
    
 \} catch (Exception e) \{  
  e.printStackTrace();  
 \}finally\{  
 free(conn, pstmt, rs);  
 \}  
      
    //System.out.println(listcolumn);  
    return listcolumn;  
\}   
  
  
  /\*\*   
     \* 释放资源   
     \*    
     \* @param conn   
     \* @param statement   
     \* @param rs   
     \*/    
    public static void free(Connection conn, PreparedStatement statement, ResultSet rs) \{    
        try \{  
if (rs != null) \{    
   
   rs.close();  
\}    
if (statement != null) \{    
     
   statement.close();    
\}    
if (conn != null) \{    
   
   conn.close();  
\}  
\} catch (Exception e) \{  
// TODO Auto-generated catch block  
  
\}    
    \}    
   public static void main(String\[\] args) \{  
   String sql = "select id,cid,is\_parent,\`name\`,parent\_cid,sort\_order,\`status\`,taosir\_cat,operationTime from open\_Cate where cid in(11,14,16,20) ";  
   List resultList1 = query(sql, null);  
   //Map map=new HashMap();  
   //map.put("columns", resultList1);  
   System.out.println("keyword='小黄瓜'===>"+resultList1);  
     
\}  
  
  
\}

\----

@RequestMapping(value="/selcat",method=\{RequestMethod.GET,RequestMethod.POST\})  
public String getsel(@RequestParam("aucId")Object\[\] aucId)  
\{  
//System.out.println("string--->>>"+aucId);  
//Map map=new HashMap<>();  
//map.put("cid", aucId);  
//System.out.println(map);  
//Object\[\]   
System.out.println(aucId);  
return opcatese.getAuctionEval(aucId);  
//List<Open\_Cate> findset=opdao.findCat(map);  
//int size = findset.size();  
//return size!=0?JSON.toJSONString(findset):JsonUtil.errorMessages(8);  
//return string;  
  
\}