常用的与数据库/SQL相关的工具类和String相关的工具类

于 2021-08-10 11:52:56 发布
阅读量271 收藏
点赞数
文章标签： java eclipse
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/L905128009/article/details/119566674
版权 
package ths.project.util;

import java.io.UnsupportedEncodingException;
import java.sql.Connection;
import java.sql.SQLException;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.HashMap;
import java.util.Map;
import java.util.ResourceBundle;
import java.util.UUID;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import java.util.regex.PatternSyntaxException;

import javax.sql.DataSource;

import org.springframework.jdbc.datasource.DataSourceUtils;

import ths.jdp.core.context.SpringContextHelper;
import ths.jdp.core.datasource.DynamicDataSource;
/**
 * 工具类
 * @author tw
 */
public class CommUtil {
	
	public static void main(String[] args){
		String s= FormatStr(12.06,1);
		System.out.println(s);
	}
	/**
	 * 读取配置文件
	 * @param name 配置文件key值
	 * @return 对应的配置信息
	 */
	public static String getResource(String name){
		ResourceBundle rb = ResourceBundle.getBundle("application");
		String value = "";
		try {
			value=rb.getString(name);
		} catch (Exception e) {
			value= "";
		}
		return value;
	}
	/**
	 * 获取数据库方言
	 * @return 数据库方言 oracleDialet db2Dialet mysqlDialet mssqlDialet
	 */
	public static String dialet(){
		//oracleDialet db2Dialet mysqlDialet mssqlDialet
		
		Connection conn = null;
		String value="";
		String dialetString;
		try {
			DynamicDataSource dynamicDataSource=(DynamicDataSource)SpringContextHelper.getBean("multiDataSource");
			DataSource datasource=(DataSource) dynamicDataSource.getDynamicDefaultTargetDataSource();
			conn = DataSourceUtils.getConnection(datasource);
			dialetString = conn.getMetaData().getDatabaseProductName();
			if(dialetString.contains("DB2")){
				value="db2Dialet";
			}else if(dialetString.contains("SQL Server")){
				value="mssqlDialet";
			}else if(dialetString.contains("Oracle")){
				value="oracleDialet";
			}else if(dialetString.contains("MySQL")){
				value="mysqlDialet";
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			try {
				conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		
		return value;
	} 
	/**
	 * 生成主键 uuid
	 * @return 主键字符串
	 */
	public static String createPkid(){
		return UUID.randomUUID().toString();
	}
	/**
	  * 获取现在时间
	  * 
	  * @return返回字符串格式 yyyy-MM-dd HH:mm:ss
	  */
	public static String getStringDate() {
		Date currentTime = new Date();
		SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		String dateString = formatter.format(currentTime);
		return dateString;
	}

	/**
	 * 
	 * @param str 字符串
	 * @return 清除掉所有特殊字符只允许字母和数字 
	 * @throws PatternSyntaxException
	 */
	public static String StringFilter(String str) throws PatternSyntaxException {
		// 只允许字母和数字       
		// String   regEx  =  "[^a-zA-Z0-9]";                     
		// 清除掉所有特殊字符  
		String regEx = "[`~!@#$%^&*()+=|{}':;',\\[\\].<>/?~！@#￥%……&*（）——+|{}【】‘；：”“’。，、？]";
		Pattern p = Pattern.compile(regEx);
		Matcher m = p.matcher(str);
		return m.replaceAll("").trim();
	} 
	/**
	 * 保留小数位，支持四舍五入
	 * @param str 数字
	 * @param no 保留多少位
	 * @return 如：12.0102，2 反回 12.01
	 */
	public static String FormatStr(double str,int no){
		String form="#";
		if(no>0){
			form+=".";
			for(int i=0;i<no;i++){
				form+="#";
			}
		}
		DecimalFormat df = new DecimalFormat(form);
		return df.format(str);
	}

	/**
	 * 是否为数字
	 */
	public static boolean isNumeric(String str) {
		Pattern pattern = Pattern.compile("[0-9]*");
		Matcher isNum = pattern.matcher(str);
		if (!isNum.matches()) {
			return false;
		}
		return true;
	}
	
	
	
	public static int getPolluteMagnify(String polluteCode){
		Map<String,Unit> polluteMagnify = initPolluteMagnify();
		Unit obj = polluteMagnify.get(polluteCode);
		if(obj == null || obj.getUnitRate() == 0) return 1;
		return obj.getUnitRate();
	}
	
	public static String getPolluteMagnifyName(String polluteCode){
		Map<String,Unit> polluteMagnify = initPolluteMagnify();
		Unit obj = polluteMagnify.get(polluteCode);
		if(obj == null || obj.getUnitName() == null || "".equals(obj.getUnitName())) return "吨";
		return obj.getUnitName();
	}
	
	private static Map<String,Unit>  initPolluteMagnify(){
		Map<String,Unit> polluteMagnify = new HashMap<String,Unit>();
		polluteMagnify.put("W00000", new Unit(10000,"万吨"));//废水(DB单位：吨)
		polluteMagnify.put("W01018", new Unit(1000,"吨"));//化学需氧量(DB单位：千克)
		polluteMagnify.put("W21003", new Unit(1000,"吨"));//氨氮(DB单位：千克)
		polluteMagnify.put("W99044", new Unit(1000,"千克"));//铅(DB单位：克)
		polluteMagnify.put("W99053", new Unit(1000,"千克"));//汞(DB单位：克)
		polluteMagnify.put("W99078", new Unit(1,"千克"));//生化需氧量(DB单位：千克)
		polluteMagnify.put("W20116", new Unit(1000,"千克"));//总铬((DB单位：克)
		polluteMagnify.put("W20117", new Unit(1000,"千克"));//六价铬(DB单位：克)
		polluteMagnify.put("W21001", new Unit(1000,"吨"));//总氮(DB单位：千克)
		polluteMagnify.put("W21011", new Unit(1000,"吨"));//总磷(DB单位：千克)
		polluteMagnify.put("W21016", new Unit(1000,"千克"));//氰化物(DB单位：克)
		polluteMagnify.put("W22001", new Unit(1000,"吨"));//石油类(DB单位：千克)
		polluteMagnify.put("W99043", new Unit(1000,"千克"));//镉(DB单位：克)
		polluteMagnify.put("W23002", new Unit(1,"千克"));//挥发酚(DB单位：千克)
		polluteMagnify.put("W99042", new Unit(1000,"千克"));//砷(DB单位：克)
		polluteMagnify.put("W99071", new Unit(1,"千克"));//铬(DB单位：千克)
		polluteMagnify.put("W34011", new Unit(1,"千克"));//臭氧(DB单位：千克)
		
		polluteMagnify.put("A00000", new Unit(1,"万立方米"));//废气 (DB单位：万立方米)
		polluteMagnify.put("A20007", new Unit(1,"千克"));//砷(气)(DB单位：千克)
		polluteMagnify.put("A34001", new Unit(1,"千克"));//总悬浮颗粒物... (DB单位：千克)
		polluteMagnify.put("A20044", new Unit(1,"千克"));//铅(气)(DB单位：千克)
		polluteMagnify.put("A20058", new Unit(1,"千克"));//汞(气)(DB单位：千克)
		polluteMagnify.put("A20033", new Unit(1,"千克"));//铬(气)(DB单位：千克)
		polluteMagnify.put("A20026", new Unit(1000,"吨"));//镉(气)(DB单位：千克)
		polluteMagnify.put("A21026", new Unit(1000,"吨"));//二氧化硫(DB单位：千克)
		polluteMagnify.put("A21002", new Unit(1000,"吨"));//氮氧化物(DB单位：千克)
		polluteMagnify.put("A21005", new Unit(1000,"吨"));//一氧化碳 (DB单位：千克)
		polluteMagnify.put("A21018", new Unit(1000,"吨"));//氟化物(DB单位：千克)
		polluteMagnify.put("84", new Unit(1000,"吨"));//工业粉尘(DB单位：千克)
		polluteMagnify.put("A24087", new Unit(1000,"吨"));//碳氢化合物 (DB单位：千克)
		polluteMagnify.put("A34013", new Unit(1000,"吨"));//烟尘 (DB单位：千克)
		polluteMagnify.put("A34012", new Unit(1000,"吨"));//粉尘  (DB单位：千克)
		polluteMagnify.put("CA34013", new Unit(1000,"吨"));//烟（粉）尘  (DB单位：千克)
		return polluteMagnify;
		
	}
	
	 static class Unit{
		private String unitName;
		private int unitRate;
		
		Unit(int unitRate,String unitName){
			this.unitRate = unitRate;
			this.unitName = unitName;
		}
		
		public int  getUnitRate(){
			return this.unitRate;
		}
		
		public String getUnitName(){
			return this.unitName;
		}
		
		public void setUnitRate(int unitRate){
			this.unitRate = unitRate;
		}
		
		public void setUnitName(String unitName){
			this.unitName = unitName;
		}
	}

		/**
		 * 将“a,b,c”或“(a)东;(b)南;(c)西”样式的行业/流域字符串转换为代码数组
		 * @param str
		 * @return
		 */
		public static String[] getCodeArray(String str){
			String tempStr = "";
			if(str.contains(";")){
				for (String r : str.split(";")) {
					if(r != null && !r.equals("") && r.contains("(") && r.contains(")")){
						tempStr = tempStr
						+ r.substring(1, r.indexOf(")"))
						+ ",";
					}else{
						tempStr = tempStr
						+ r
						+ ",";
					}
				}
			}else{
				if(str.contains("(") && str.contains(")")){
					tempStr = tempStr
					+ str.substring(1, str.indexOf(")"))
					+ ",";
				}else{
					tempStr = tempStr
					+ str
					+ ",";
				}
			}
			return tempStr.split(",");
		}
		
		/**
		 * 字符串数组没有加上单引号，通过该方法添加单引号
		 * @param str
		 * @return
		 */
		public static String getInString(String str){
			String tempStr = "";
			if(str.contains(",")){
				String[] r=str.split(",");
				for(int i=0;i<r.length;i++){
					if(i<r.length-1){
						tempStr = tempStr+"'"+r[i]+"',";
					}else{
						tempStr = tempStr+"'"+r[i]+"'";
					}
				}
			}
			return tempStr;
		}
		public static String encodeStr(String str) {  
	        try {  
	            return new String(str.getBytes("ISO-8859-1"), "UTF-8");  
	        } catch (UnsupportedEncodingException e) {  
	            e.printStackTrace();  
	            return null;  
	        }  
	    }  
}

SQL相关的常用工具类

package ths.project.util;

import org.apache.commons.lang.StringUtils;
/**
 * SQL兼容性转换类
 * @author tianwei
 *
 */

public class SqlFunctionTools {
	public static String dialet = CommUtil.dialet(); //获得方言
	
	
	/**
	 * 获取连接符，不同数据库连接符不一样有+和||
	 * @return 转换后的sql
	 */
	public static String getConnSign(){
		return dialet.equalsIgnoreCase("mssqlDialet") ? "+":"||";
	}
	
	/**
	 * 字符串连接
	 * @param field1,field2:字段名
	 * 支持数据库 oracle db2 mysql mssql
	 * @return 转换后的sql
	 */
	public static String getConnectField(String field1,String field2,String split){
		String function = "";
		if(dialet.equals("oracleDialet")){
			function = field1 + "||'"+split+"'||" + field2;
		}else if(dialet.equals("db2Dialet")){
			function = field1 + "||'"+split+"'||" + field2;
		}else if(dialet.equals("mysqlDialet")){
			function = "concat_ws('"+split+"'," + field1 + "," + field2 + ")";
		}else if(dialet.equals("mssqlDialet")){
			function = "cast("+field1+" as varchar)" + "+'"+split+"'+" +field2;
		}else{
			function = field1 + "||'"+split+"'||" + field2;
		}
		
		return function;
	}
	/**
	 * 也是连接字符串方法多个字段以数组形式传参，最后一个参数不知道为什么加。
	 * @param split
	 * @param fields
	 * @param lastStr
	 * @return 转换后的sql
	 */
	public static String getConnectField(String split,String[] fields,String lastStr){
		
		String str = getConnectField(split,fields);
		
		if(dialet.equalsIgnoreCase("oracleDialet") || dialet.equalsIgnoreCase("db2Dialet")){
			
			return str + "||'" + lastStr + "'";
		}else{
			return str + "+'" + lastStr + "'";
		}
	}
	/**
	 * 连接字符串
	 * @param split 分隔符
	 * @param fields 字段 可多个
	 * @return 转换后的sql
	 */
	public static String getConnectField(String split,String...fields){
		
		String function = "";
		String lastFieldName = "";
		String curSplit = StringUtils.isEmpty(split) ? "" : split;
		
		if(dialet.equalsIgnoreCase("oracleDialet") || dialet.equalsIgnoreCase("db2Dialet")){
			
			for (String field : fields) {
				
				function += field + "||'" + curSplit + "'||";
				lastFieldName = field;
			}
			if(fields.length > 0){
				function = function.substring(0,function.lastIndexOf(lastFieldName) + lastFieldName.length());
			}
		}else{
			for (String field : fields) {
				
				function += field + "+'" + curSplit + "'+" ;
				lastFieldName = field;
			}
			if(fields.length > 0){
				function = function.substring(0,function.lastIndexOf(lastFieldName) + lastFieldName.length());
			}
		}
		
		return function;
	}
	
	/**
	 * 字符串连接
	 * @param field1,field2:字段名
	 * 支持数据库 oracle db2 mysql mssql
	 * @return 转换后的sql
	 */
	public static String getConnectOperator(){
		String function = "";
		if(dialet.equals("oracleDialet")){
			function = "||";
		}else if(dialet.equals("db2Dialet")){
			function = "||";
		}else if(dialet.equals("mssqlDialet")){
			function = "+";
		}else{
			function = "||";
		}
		
		return function;
	}
	
	/**
	 * 日期转字符串 oracle:YYYY-MM-DD mysql:%Y-%m-%d mssql:23
	 * @param filed 字段名
	 * @param format为null时默认是YYYY-MM-DD， %Y-%m-%d， 23 field:字段名 支持数据库 oracle db2 mysql
	 * mssql
	 * @return 转换后的sql
	 */
	public static String getDateToChar(String field, String format) {

		String function = "";
		if (dialet.equals("oracleDialet")) {
			format = format == null ? "YYYY-MM-DD" : format;
			function = "to_char(" + field + ",'" + format + "')";
		} else if (dialet.equals("db2Dialet")) {
			format = format == null ? "YYYY-MM-DD" : format;
			function = "to_char(" + field + ",'" + format + "')";
		} else if (dialet.equals("mysqlDialet")) {
			format = format == null ? "%Y-%m-%d" : format;
			function = "date_format(" + field + ",'" + format + "')";
		} else if (dialet.equals("mssqlDialet")) {
			function = getSqlServerDateToStringFunction(field, format);
		} else {
			function = "" + field + "";
		}

		return function;
	}

	/**
	 * 日期转字符串 oracle:YYYY-MM-DD mysql:%Y-%m-%d mssql:23
	 * format为null时默认是YYYY-MM-DD， %Y-%m-%d， 23 field:字段名 支持数据库 oracle db2 mysql
	 * mssql
	 * @return 转换后的sql
	 */
	private static String getSqlServerDateToStringFunction(String field,
			String format) {
		if (format.equalsIgnoreCase("YYYY")){
			return getFieldSubstr("CONVERT(varchar(100)," + field + ",23)", 0,4);
		}else if (format.equalsIgnoreCase("YYYY-MM")){
			return getFieldSubstr("CONVERT(varchar(100)," + field + ",23)", 0,7);
		}else if(format.equalsIgnoreCase("MM")){
			return getFieldSubstr("CONVERT(varchar(100)," + field + ",23)", 5,1);
		}else if(format.equalsIgnoreCase("MM-DD")){
			return getFieldSubstr("CONVERT(varchar(100)," + field + ",23)", 5,5);
		}else if(format.equalsIgnoreCase("DD")){
			return getFieldSubstr("CONVERT(varchar(100)," + field + ",23)", 8,1);
		}else if(format.equalsIgnoreCase("HH24")){
			return getFieldSubstr("CONVERT(varchar(100)," + field + ",120)", 11,2);
		}else if(format.equalsIgnoreCase("HH24:mi")){
			return getFieldSubstr("CONVERT(varchar(100)," + field + ",120)", 11,4);
		}else if (format.equalsIgnoreCase("YYYY-MM-DD HH24:mi:ss")){
			return "CONVERT(varchar(100)," + field + ",120)";
		}else{
			return "CONVERT(varchar(100)," + field + ",23)";// YYYY-MM-DD
		}
	}
	
	
	/**
	 * 字符串转日期
	 * format为null时默认是YYYY-MM-DD
	 * 支持数据库 oracle db2 mysql mssql
	 * @return 转换后的sql
	 */
	public static String getCharToDate(String str,String format){
		String function = "";
		if(dialet.equals("oracleDialet")){
			format = format == null ? "YYYY-MM-DD" : format;
			function = "to_date('" + str + "','"+format+"')";
		}else if(dialet.equals("db2Dialet")){
			format = format == null ? "YYYY-MM-DD" : format;
			function = "to_date('" + str + "','"+format+"')";
		}else if(dialet.equals("mysqlDialet")){
			function = "'" + str + "'";
		}else if(dialet.equals("mssqlDialet")){
			function = "CAST('" + str + "' AS datetime)";
		}else{
			function = "'"+str+"'";
		}
		
		return function;
	}
	
	/**
	 * 字段或函数转日期
	 * format为null时默认是YYYY-MM-DD
	 * 支持数据库 oracle db2 mysql mssql
	 * @return 转换后的sql
	 */
	public static String getFieldToDate(String field,String format){
		String function = "";
		if(dialet.equals("oracleDialet")){
			format = format == null ? "YYYY-MM-DD" : format;
			function = "to_date(" + field + ",'"+format+"')";
		}else if(dialet.equals("db2Dialet")){
			format = format == null ? "YYYY-MM-DD" : format;
			function = "to_date(" + field + ",'"+format+"')";
		}else if(dialet.equals("mysqlDialet")){
			function = "" + field + "";
		}else if(dialet.equals("mssqlDialet")){
			function = "CAST(" + field + " AS date)";
		}else{
			function = ""+field+"";
		}
		
		return function;
	}
	
	/**
	 * 字段值转大写
	 * 支持数据库 oracle db2 mysql mssql
	 *@param filed 字段名
	 *@return 转换后的sql
	 */
	public static String getFieldUpper(String field){
		String function = "";
		if(dialet.equals("oracleDialet")){
			function = "upper(" + field + ")";
		}else if(dialet.equals("db2Dialet")){
			function = "upper(" + field + ")";
		}else if(dialet.equals("mysqlDialet")){
			function = "upper(" + field + ")";
		}else if(dialet.equals("mssqlDialet")){
			function = "upper(" + field + ")";
		}else{
			function = "upper(" + field + ")";
		}
		
		return function;
	}
	
	/**
	 * 截取子串
	 * @param field字段名称，start起始位置，结束位置end
	 * 支持数据库 oracle db2 mysql mssql
	 * @return 转换后的sql
	 */
	public static String getFieldSubstr(String field,int start,int length){
		String function = "";
		if(dialet.equals("oracleDialet")){
			if(start > 0){
				function = "substr(" + field + "," + (start + 1) + "," + length + ")";
			}else{
				function = "substr(" + field + "," + start + "," + length + ")";
			}
		}else if(dialet.equals("db2Dialet")){
			function = "substr(" + field + "," + (start + 1) +  "," + length + ")";
		}else if(dialet.equals("mysqlDialet")){
			function = "substr(" + field + "," + start + "," + length + ")";
		}else if(dialet.equals("mssqlDialet")){
			function = "substring(" + field + "," + (start + 1) + "," + length + ")";//sqlserver中起始位置为1，db2和oracle中起始位置为0
		}else{
			function = "substr(" + field + "," + start + "," + length + ")";
		}
		
		return function;
	}
	public static String getChildrenPrefix(String column,
			String[] codeValues) {
		String result="";
		for(String cv : codeValues){
			result = result + column+" like '"+cv+"%' or ";
		}
		if(result.trim().endsWith("or")){
			result = result.substring(0, result.trim().length()-2);
		}
		return result;
	}
	
	public static String getRegionChildrenPrefix(String column,
			String[] codeValues) {
		String result="";
		for(String cv : codeValues){
			String _cv=cv;
			char [] tem = cv.toCharArray();
			for(int i=tem.length-1;i>=0;i--){
				if('0'!=tem[i]){
					if(cv.lastIndexOf(tem[i])!= tem.length-1){
						_cv=cv.substring(0, cv.lastIndexOf(tem[i])+1);
					}					
					break;
				}
			}
			
			result = result + column+" like '"+_cv+"%' or ";
		}
		if(result.trim().endsWith("or")){
			result = result.substring(0, result.trim().length()-2);
		}
		return result;
	}
	/**
	 * caseWhen转换
	 * @param field1 列1
	 * @param field2 列2
	 * @return 转换后的sql
	 */
	public static String caseWhen(String field1,String field2){
		
		if (dialet.equalsIgnoreCase("mssqlDialet") || dialet.equalsIgnoreCase("db2Dialet")) {
			return  " case when " + field1 + " is null then " + field2+ " else " + field1 + " end ";
		}
		return " case when " + field1 + " is null then cast('" + field2 + "' as  nvarchar2("+field2.length()+")) else " + field1 + " end ";
	}
}

把每天当成生命中的最后一天
关注
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值