sqlUtils
标签：throw use java map tin order by throws get ignore
sqlUtils
/**
* *************************************************************************
* <PRE>
* @ClassName: : SqlUtils
*
* @Description: : dynamic sql
*
* @Creation Date : Jun 15, 2021 1:41:48 PM
*
* @Author : Sea
*
*
* </PRE>
**************************************************************************
*/
public class SqlUtils {
// @Test
// public void testSql() throws Exception {
// JSONObject IncriteriaMap = new JSONObject();
// IncriteriaMap.put("sea","aa,bb,cc,dd");
// JSONObject mycriteriaMap = new JSONObject();
// mycriteriaMap.put("in", IncriteriaMap);
// String inlude="name,age,total";
// String sql="select " +inlude+ " from user where "+ mapToWhere(mycriteriaMap) +" order by sold desc";
// System.err.println(sql);
// }
/**
* JSONObject andcriteriaMap = new JSONObject();
andcriteriaMap.put("sea", "sea");
andcriteriaMap.put("number", 123);
andcriteriaMap.put("double", 12.31);
JSONObject orcriteriaMap = new JSONObject();
orcriteriaMap.put("sea", "sea");
orcriteriaMap.put("double", 12.31);
JSONObject IncriteriaMap = new JSONObject();
IncriteriaMap.put("sea","aa,bb,cc,dd");
JSONObject mycriteriaMap = new JSONObject();
mycriteriaMap.put("and", andcriteriaMap);
mycriteriaMap.put("or", andcriteriaMap);
mycriteriaMap.put("in", IncriteriaMap);
* @param mycriteriaMap
* @return
* @throws Exception
*/
public static String mapToWhere(JSONObject mycriteriaMap) throws Exception {
String criteria="";
int criteriaMapsize = mycriteriaMap.size();
for (Entry<String, Object> okv : mycriteriaMap.entrySet()) {
String option = okv.getKey();
HashMap<String, Object> criteriaMap=(HashMap<String, Object>) okv.getValue();;
int msize=criteriaMap.size();
for (Entry<String, Object> kv : criteriaMap.entrySet())
{
String key = kv.getKey();
Object value = kv.getValue();
if(StringUtils.isBlank(key)||StringUtils.isBlank(value+""))
{
continue;
}
//if option is in
if("in".equalsIgnoreCase(option))
{
criteria+=" " +key+" in( " +convert2SqlIn(Arrays.asList((value+"").split(","))) +" )";
}else //option is and | or
{
if(String.class.isInstance(value)) {
criteria+=" " +key+"=‘" +value +"‘ ";
}else
{
criteria+=" " +key+"=" +value +" ";
}
msize--;
if(msize!=0) {
criteria+=" "+option+" ";
}
}
}
criteriaMapsize--;
if(criteriaMapsize!=0) {
criteria+=" and ";
}
};
return criteria;
}
/**
* @Desc list<String> to sql in
* @param list<String>
* @return
*/
public static String convert2SqlIn(List<String> list){
StringBuilder sb = new StringBuilder();
if(list != null && list.size()>0){
for(int i=0,len=list.size();i<len;i++){
sb.append("‘"+ list.get(i) + "‘");
if(i < len-1){
sb.append(",");
}
}
}
return sb.toString();
}
}