SqlUtils工具类mysql和oracle的java通用新增和更新sql

X.I.O

于 2022-12-09 00:08:18 发布

阅读量401

点赞数

分类专栏： java工具类文章标签： oracle mysql java

本文链接：https://blog.csdn.net/xiong305/article/details/128245799

版权

java工具类专栏收录该内容

4 篇文章 0 订阅

订阅专栏

SqlUtils工具类
低代码平台可能会用的比较多一点
主要用于mysql和oracle的通用新增和更新
primaryKey 未主键key



import org.junit.Test;

import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.HashMap;
import java.util.Map;
import java.util.StringJoiner;

/**
 * @Classname SqlUtils
 * @Description TODO
 * @Date 2022/12/8 13:48
 * @Created by XiongXiong
 * @Author: X.I.O
 */
public class SqlUtils {



        private static String insertSqlOracle(String tableName,Map<String,Object> map){
                StringBuilder stringBuilder=new StringBuilder();
                StringJoiner field=new StringJoiner(",","(",")");
                StringJoiner value=new StringJoiner(",","(",")");
                SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                for (Map.Entry< String,Object> entry : map.entrySet()) {
                        String key=entry.getKey();
                        Object val=entry.getValue();
                        if(val==null || "".equals(val)){
                                continue;
                        }
                        field.add(key);
                        if(val instanceof Date){
                                Date date = (Date) val;
                                val="to_date('"+sdf.format(date)+"' , 'yyyy-mm-dd hh24:mi:ss')";
                                value.add(String.valueOf(val));
                        }else {
                                val="'"+val+"'";
                                value.add(String.valueOf(val));
                        }

                }
                stringBuilder.append("insert into ").append(tableName).append(" ").append(field.toString()).append(" VALUES ").append(value.toString());
                String sql=stringBuilder.toString();
                return sql;
        }

		
        private static String updateSqlOracle(String tableName,String primaryKey,Map<String,Object> map){
                StringBuilder sb = new StringBuilder();
                StringJoiner stringJoiner=new StringJoiner(",");
                sb.append("UPDATE ").append(tableName).append(" SET ");
                SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                for (Map.Entry< String,Object> entry : map.entrySet()){
                        String key = entry.getKey();
                        Object value = entry.getValue();
                        if(key.equals(primaryKey)){
                                continue;
                        }
                        if(value==null){
                                continue;
                        }
                        if("".equals(value)){
                                continue;
                        }
                        if(value instanceof Date){
                                Date date = (Date) value;
                                value="to_date('"+sdf.format(date)+"' , 'yyyy-mm-dd hh24:mi:ss')";
                                stringJoiner.add("" + key + "" + "="  + value );
                        }else {
                                stringJoiner.add("" + key + "" + "=" + "'" + value + "'");
                        }
                }
                sb.append(stringJoiner.toString());
                Object primaryValue=map.get(primaryKey);
                sb.append(" WHERE ").append(primaryKey).append("=").
                        append("'").append(primaryValue).append("'");
                return sb.toString();
        }

        private static String updateSqlMySql(String tableName,String primaryKey,Map<String,Object> map){
                StringBuilder sb = new StringBuilder();
                StringJoiner stringJoiner=new StringJoiner(",");
                sb.append("UPDATE ").append("`").append(tableName).append("`").append(" SET ");
                for (Map.Entry< String,Object> entry : map.entrySet()){
                        String key = entry.getKey();
                        Object value = entry.getValue();
                        if(key.equals(primaryKey)){
                                continue;
                        }
                        if(value==null){
                                continue;
                        }
                        if("".equals(value)){
                                continue;
                        }
                        if(value instanceof Date){
                                SimpleDateFormat dft=new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                                Date date = (Date) value;
                                value=dft.format(date);
                        }
                        stringJoiner.add("`" + key + "`" + "=" + "'" + value + "'");
                }
                sb.append(stringJoiner.toString());
                Object primaryValue=map.get(primaryKey);
                sb.append(" WHERE ").append("`").append(primaryKey).append("`").append("=").
                        append("'").append(primaryValue).append("'");
                return sb.toString();
        }

        private static String insertSqlMySql(String tableName, Map<String,Object> map){
                StringBuilder stringBuilder=new StringBuilder();
                StringJoiner field=new StringJoiner("`,`","(`","`)");
                StringJoiner value=new StringJoiner("','","('","')");
                for (Map.Entry< String,Object> entry : map.entrySet()) {
                        String key=entry.getKey();
                        Object val=entry.getValue();
                        if(val==null || "".equals(val)){
                                continue;
                        }
                        if(val instanceof Date){
                                Date date = (Date) val;
                                SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                                val=sdf.format(date);
                        }
                        field.add(key);
                        value.add(String.valueOf(val));
                }
                stringBuilder.append("insert into ").append(tableName).append(" ").append(field.toString()).append(" VALUES ").append(value.toString());
                String sql=stringBuilder.toString();
                return sql;
        }
}