SQL工具类

最新推荐文章于 2024-05-11 11:02:38 发布
最新推荐文章于 2024-05-11 11:02:38 发布
阅读量140 收藏
点赞数
文章标签： java
原文链接：http://www.cnblogs.com/airycode/p/4832648.html
版权 
package com.ebizwindow.crm.utils;

import java.util.List;

import com.ebizwindow.crm.constants.SqlConst;
import com.ebizwindow.crm.constants.TableConst;
import com.ebizwindow.crm.model.TableDefinition;
import com.ebizwindow.crm.portlet.base.SystemStatus;
import com.ebizwindow.crm.service.OpportunityLocalServiceUtil;
import com.ebizwindow.crm.service.TableDefinitionLocalServiceUtil;
import com.liferay.portal.kernel.exception.PortalException;
import com.liferay.portal.kernel.exception.SystemException;
import com.liferay.portal.kernel.util.StringPool;

public class SQLUtil {
	private static final String PK_COLUMN_CREATEUSERID = "createUserId";
	private static final String PK_COLUMN_EDITUSERID = "editUserId";
	private static final String PK_COLUMN_AUDITUSERID = "auditUserId";
	private static final String PK_COLUMN_CLOSEUSERID = "closeUserId";
	private static final String PK_COLUMN_CONFIRMUSERID = "confirmUserId";
	private static final String PK_COLUMN_REQUESTUSERID = "requestUserId";
	private static final String PK_COLUMN_EXECUTORID = "executorId";
	private static final String PK_COLUMN_SUBMITUSERID = "submitUserId";
	private static final String PK_COLUMN_OWNERID = "ownerId";
	private static final String PK_COLUMN_UPID = "upId";
	private static final String PK_COLUMN_CUSTOMERID = "customerId";
	private static final String PK_COLUMN_CONTACTID = "contactId";
	private static final String PK_COLUMN_CONTRACTID = "contractId";
	private static final String PK_COLUMN_OPPORTUNITYID = "opportunityId";
	private static final String PK_COLUMN_QUOTATIONID = "quotationId";
	private static final String PK_COLUMN_CLUEID = "clueId";
	private static final String PK_COLUMN_ACTIVITYID = "activityId";
	private static final String PK_COLUMN_MARKETID = "marketId";
	private static final String PK_COLUMN_SALESTEMPLATEID = "salesTemplateId";
	private static final String PK_COLUMN_DEPARTMENTID = "departmentId";
	private static final String PK_COLUMN_PRODUCTID = "productId";
	private static final String PK_COLUMN_PROJECTID = "projectId";

	public static String getQueryValue(String queryValue, String columnName, long companyId) throws SystemException {
		String results = StringPool.BLANK;
		String query = StringPool.BLANK;
		if (columnName.equals(PK_COLUMN_CREATEUSERID)
				|| columnName.equals(PK_COLUMN_EDITUSERID)
				|| columnName.equals(PK_COLUMN_AUDITUSERID)
				|| columnName.equals(PK_COLUMN_OWNERID)
				|| columnName.equals(PK_COLUMN_CLOSEUSERID)
				|| columnName.equals(PK_COLUMN_CONFIRMUSERID)
				|| columnName.equals(PK_COLUMN_REQUESTUSERID)
				|| columnName.equals(PK_COLUMN_SUBMITUSERID)
				|| columnName.equals(PK_COLUMN_EXECUTORID) ) {

			query = "select userId from User_ where firstName like '%" + queryValue + "%'";
			
		} else if (columnName.equals(PK_COLUMN_UPID) || columnName.equals(PK_COLUMN_CUSTOMERID)) {
			
			query = "select customerId from CRM_Customer where chineseName like '%" + queryValue + "%'";
		
		} else if (columnName.equals(PK_COLUMN_CONTACTID)) {
			
			query = "select contactId from CRM_Contact where chineseName like '%" + queryValue + "%'";

		} else if (columnName.equals(PK_COLUMN_MARKETID)) {
			
			query = "select marketId from CRM_Market where name like '%" + queryValue + "%'";
	
		} else if (columnName.equals(PK_COLUMN_CLUEID)) {
			
			query = "select clueId from CRM_Clue where name like '%" + queryValue + "%'";
	
		} else if (columnName.equals(PK_COLUMN_OPPORTUNITYID)) {
			
			query = "select opportunityId from CRM_Opportunity where topic like '%" + queryValue + "%'";
	
		} else if (columnName.equals(PK_COLUMN_QUOTATIONID)) {
			
			query = "select quotationId from CRM_Quotation where name like '%" + queryValue + "%'";
	
		} else if (columnName.equals(PK_COLUMN_CONTRACTID)) {
			
			query = "select contractId from CRM_Contract where name like '%" + queryValue + "%'";
	
		} else if (columnName.equals(PK_COLUMN_ACTIVITYID)) {
			
			query = "select activityId from CRM_Activity where name like '%" + queryValue + "%'";
	
		} else if (columnName.equals(PK_COLUMN_SALESTEMPLATEID)) {
			
			query = "select salesTemplateId from CRM_SalesTemplate where name like '%" + queryValue + "%'";
	
		} else if (columnName.equals(PK_COLUMN_PRODUCTID)) {
			
			query = "select productId from CRM_Product where name like '%" + queryValue + "%'";
			
		} else if (columnName.equals(PK_COLUMN_DEPARTMENTID)) {
			
			query = "select departmentId from OPERATOR_Department where departmentName like '%" + queryValue + "%'";

		} else if (columnName.equals(PK_COLUMN_PROJECTID)) {
			
			query = "select projectId from CRM_Project where name like '%" + queryValue + "%'";

		}
		
		query += " and companyId = '" + companyId + "'";
		
		List<Long> entityIDs = OpportunityLocalServiceUtil.searchBySQLQueryString(query, -1, -1);
		
		String entityIDsStr = entityIDs.toString();
		results = StringPool.OPEN_PARENTHESIS + entityIDsStr.subSequence(1, entityIDsStr.length() - 1) + StringPool.CLOSE_PARENTHESIS;

		return results;
	}
	
	public static String symbolToString(String symbol, String value) {
		String str = StringPool.BLANK;
		if (!symbol.equals(StringPool.BLANK)) {
			if (symbol.equals("eq") || symbol.equals(StringPool.EQUAL)) {

				str = " = '" + value + "'";

			} else if (symbol.equals("gt") || symbol.equals(StringPool.GREATER_THAN)) {

				str = " > '" + value + "'";

			} else if (symbol.equals("lt") || symbol.equals(StringPool.LESS_THAN)) {

				str = " < '" + value + "'";

			} else if (symbol.equals("gteq") || symbol.equals(StringPool.GREATER_THAN_OR_EQUAL)) {

				str = " >= '" + value + "'";

			} else if (symbol.equals("lteq") || symbol.equals(StringPool.LESS_THAN_OR_EQUAL)) {

				str = " <= '" + value + "'";

			} else if (symbol.equals("ne") || symbol.equals(StringPool.NOT_EQUAL)) {

				str = " <> '" + value + "'";

			} else if (symbol.equals("c")) {

				str = " like '%" + value + "%'";

			} else if (symbol.equals("sl")) {

				str = " like '" + value + "%'";
				
			} else if (symbol.equals("sr")) {

				str = " like '%" + value + "'";

			} else if (symbol.equals("nn")) {

				str = " <> '' ";

			} else if (symbol.equals("n")) {

				str = " = '' ";
				
			} else if (symbol.equals("isn")) {

				str = " is null ";
			} else if (symbol.equals("!eq")) {
				str = " != '" + value + "'";
			} else if (symbol.equals("tc")) {
				str = " in " + value;
			}
		} else {
			str = " = '' ";
		}
		return str;
	}

	public static String getActivitySQL(long userId) throws SystemException {
		StringBuffer sb = new StringBuffer("select activity.activityId from CRM_Activity activity where (activity.executorId in ")
			.append(OperatorUtil.searchViewOperatorIds(userId,TableConst.ACTIVITY))
			.append(" or activity.createUserId in ")
			.append(OperatorUtil.searchViewOperatorIds(userId,TableConst.ACTIVITY))
			.append(")");
		return sb.toString();
	}

	public static String getCustomerSQL(long userId) throws SystemException {
		String sql = "select customer.customerId from CRM_Customer customer where (customer.ownerId in "
				+ OperatorUtil.searchViewOperatorIds(userId, TableConst.CUSTOMER) + ")";
		return sql;
	}

	public static String getContactSQL(long userId) throws SystemException {
		String sql = "select contact.contactId from CRM_Contact contact where (contact.ownerId in "
			+ OperatorUtil.searchViewOperatorIds(userId,TableConst.CONTACT) + ")";
		return sql;
	}
	
	public static String getContactTop10SQL(long userId) throws SystemException {
		String sql = "select contact.contactId from CRM_Contact contact where (contact.ownerId in "
				+ OperatorUtil.searchViewOperatorIds(userId,TableConst.CONTACT) + ") order by contact.createDate limit 10 ";
		return sql;
	}

	public static String getContractSQL(long userId) throws SystemException {
		String sql = "select contract.contractId from CRM_Contract contract where (contract.ownerId in "
				+ OperatorUtil.searchViewOperatorIds(userId,TableConst.CONTRACT) + ")";
		return sql;
	}
	
	public static String getMarketSQL(long userId) throws SystemException {
		String sql = "select market.marketId from CRM_Market market where (market.ownerId in "
				+ OperatorUtil.searchViewOperatorIds(userId,TableConst.MARKET)  + ")";
		return sql;
	}
	
	public static String getClueSQL(long userId) throws SystemException {
		String sql = "select clue.clueId from CRM_Clue clue where (clue.ownerId in "
			+ OperatorUtil.searchViewOperatorIds(userId,TableConst.CLUE) + ") and clue.auditStatus='"+SystemStatus.Audit.getStatus()+"'";
		return sql;
	}
	
	public static String getOpportunitySQL(long userId) throws SystemException {
		String sql = "select opportunity.opportunityId from CRM_Opportunity opportunity where (opportunity.ownerId in "
			+ OperatorUtil.searchViewOperatorIds(userId,TableConst.OPPORTUNITY) + ")";
		return sql;
	}

	public static String getOpportunityTop10SQL(long userId) throws SystemException {
		String sql = "select opportunity.opportunityId from CRM_Opportunity opportunity where (opportunity.ownerId in "
				+ OperatorUtil.searchViewOperatorIds(userId,TableConst.OPPORTUNITY) + ") order by opportunity.createDate limit 10";
		return sql;
	}

	public static String getQuotationSQL(long userId) throws SystemException {
		String sql = "select quotation.quotationId from CRM_Quotation quotation where (quotation.ownerId in "
				+ OperatorUtil.searchViewOperatorIds(userId,TableConst.QUOTATION) + ")";
		return sql;
	}
	
	public static String getOrderSQL(long userId) throws SystemException {
		String sql = "select order_.orderId from CRM_Order order_ where (order_.ownerId in "
			+ OperatorUtil.searchViewOperatorIds(userId,TableConst.ORDER) + ")";
		
		return sql;
	}

	public static String getProductSQL(long companyId) throws SystemException {
		String sql = "select product.productId from CRM_Product product where product.companyId = '" + companyId + "'";
		return sql;
	}
	
	public static String getRPlanSQL(long userId) throws SystemException {
		String sql = "select receivablesPlan.receivablesPlanId from CRM_ReceivablesPlan receivablesPlan where (receivablesPlan.ownerId in "
			 + OperatorUtil.searchViewOperatorIds(userId,TableConst.RPLAN) + ")";
		return sql;
	}
	
	public static String getRRecordSQL(long userId) throws SystemException{
		String sql = "select receivablesRecord.receivablesRecordId from CRM_ReceivablesRecord receivablesRecord where (receivablesRecord.ownerId in "
			 + OperatorUtil.searchViewOperatorIds(userId,TableConst.RRECORD) + ")";
		return sql;
	}

	public static String getSQLBeginningByTableDefinitionId(long tableDefinitionId) throws PortalException, SystemException {
		String result = "";
		TableDefinition tableDefinition = TableDefinitionLocalServiceUtil.getTableDefinition(tableDefinitionId);
		String tableName = tableDefinition.getTableName();
		if (tableName.equals(TableConst.CRM_Customer)) {
			result = SqlConst.CUSTOMER_SQL_BEGINNING;
		} else if (tableName.equals(TableConst.CRM_Customer)) {
			result = SqlConst.CUSTOMER_SQL_BEGINNING;
		} else if (tableName.equals(TableConst.CRM_Contact)) {
			result = SqlConst.CONTACT_SQL_BEGINNING;
		} else if (tableName.equals(TableConst.CRM_Market)) {
			result = SqlConst.MARKET_SQL_BEGINNING;
		} else if (tableName.equals(TableConst.CRM_Clue)) {
			result = SqlConst.CLUE_SQL_BEGINNING;
		} else if (tableName.equals(TableConst.CRM_Activity)) {
			result = SqlConst.ACTIVITY_SQL_BEGINNING;
		} else if (tableName.equals(TableConst.CRM_Opportunity)) {
			result = SqlConst.OPPORTUNITY_SQL_BEGINNING;
		} else if (tableName.equals(TableConst.CRM_Quotation)) {
			result = SqlConst.QUOTATION_SQL_BEGINNING;
		} else if (tableName.equals(TableConst.CRM_Contract)) {
			result = SqlConst.CONTRACT_SQL_BEGINNING;
		} else if (tableName.equals(TableConst.CRM_Product)) {
			result = SqlConst.PRODUCT_SQL_BEGINNING;
		} else {
			result = SqlConst.CUSTOMER_SQL_BEGINNING;
		}
		return result;
	}
	
	public static String filterQuery(String columnName){
		if (columnName.equals("type") || columnName.equals("code")) {
			return columnName + StringPool.UNDERLINE;
		} else {
			return columnName;
		}
	}
	
	//private static Log _log = LogFactoryUtil.getLog(SQLUtil.class);

}

　　

转载于:https://www.cnblogs.com/airycode/p/4832648.html

weixin_30627381
关注
sql访问数据库工具类--SqlUtils(java源码)
01-14
package com.hexiang.utils; /** * SQLUtils utils = new SQLUtils(User.class); utils.setWhereStr("", "id", "=", 100).setWhereStr("and", "name", " ", "is null").setWhereStr("and", "date", ">=", new Date()); utils.setOrderByStr("id", "desc").setOrderByStr("name", "asc"); System.out.println(utils.buildSelectSQL()); System.out.println(utils.buildCountSQL()); */ import java.sql.Timestamp; import java.text.SimpleDateFormat; import java.util.Date; import java.util.LinkedHashMap; import java.util.LinkedList; import java.util.List; import java.util.Map; public class SqlUtils { private String beanName; private String beanShortName; private Map propertyMap; private List conditionList; private List relationList; private Map orderByMap; public SqlUtils(Class instance) { this.setBeanName(instance.getSimpleName()); this.setBeanShortName(Character.toLowerCase(this.getBeanName() .charAt(0)) + ""); init(); } public SqlUtils() { init(); } void init(){ propertyMap = new LinkedHashMap(); conditionList = new LinkedList(); relationList = new LinkedList(); orderByMap = new LinkedHashMap(); } /** * 添加查询条件 * * @param relation * 关联 "and","or"等 * @param property * 查询的对象属性 * @param condition * 查询的条件，关系符 * @param value * 查询的值 */ public SqlUtils setWhereStr(String relation, String property, String condition, Object value) { if(value != null){ relationList.add(relation); propertyMap.put(property, value); conditionList.add(condition); } return this; } private String buildWhereStr() { StringBuffer buffer = new StringBuffer(); if (!propertyMap.isEmpty() && propertyMap.size() > 0) { buffer.append("WHERE 1 = 1 "); int index = 0; for (String property : propertyMap.keySet()) { if (property != null && !property.equals("")) { buffer.append(r
SQL 工具类
qq_28392947的博客
10-14 237
package com.zjson.rights.commons.tools.utils; import org.apache.commons.lang3.StringUtils; /** * SQL 工具类 * * @author zj * @version v1.0.0 * @date 2020/10/12 17:55 */ public class SqlUtil { private final static String BACKSLASH = "\\"; priv
SqlUtils工具类，Sql IN 条件拼接工具类
蕃薯耀的博客
06-28 2303
ss SqlUtils { /** * * @param params List&lt;Object&gt;，sql执行的？参数List * @param sqlBuffer StringBuffer * @param column String，表字段，当column=user_name，生成如下：user_name in (?,?) * @param values Object[]，问号（?）的数组值， */ public static
基于 Spring-Jpa 的动态 Sql 工具类分享
qq_45489824的博客
05-11 1492
在这里先贴一段代码如果项目用过 Spring-Jpa 的人对这种写法应该都不会陌生，反正我们公司是这样用的，每一个可能用到的查询字段都要手动写这样一段逻辑，算上 count 的话相似的逻辑要写4遍，笔者真的很讨厌这种重复度高的代码，所以在写了几次后就开始构思工具类来缓解这一情况。通过 Java 提供的反射特性动态判断每一个字段是否存在并拼装 sql使用自定义注解来标识需要查询的字段和字段对应的 sql 语句频繁获取反射方法会大大降低性能，关键的数据需要封装并存到缓存中。
Dao数据模型转sql 工具类
最新发布
05-21
可以快速批量的将dao数据模型转化为sql语句，可以用于分析数据结构，可以快速创建未知的数据库业务，解决sql 的创建问题。快速生成sql文件。让数据结构更好更快捷展现在你面前 解决被sql卡主业务的问题。适用于 只有...
生成SQL的工具类
07-12
生成SQL的工具类
动态拼接sql语句工具类,拼接where后面语句
05-25
动态拼接sql语句工具类,拼接where后面语句 配合原生jdbc仿动态sql注入 if (ObjectUtil.isNotEmpty(maxLat)&&ObjectUtil.isNotEmpty(minLat)){ sqlParamList.add(new SqlParam("lat",minLat, SqlOpEnum.GE)); ...
防止sql注入工具类l
03-22
在这个主题中，我们将深入探讨“防止SQL注入工具类”的相关知识点，以及如何在实际开发中有效地应用这些工具。 首先，我们需要理解SQL注入的基本原理。当应用程序直接将用户输入的数据拼接到SQL查询语句中时，如果...
SqlHelper工具类（封装了对数据库的各种操作的方法）
04-26
文件封装了所有的关于数据库的操作. 我的这个SqlHelper.cs 是同学给我的版本,可能与其它人的不太一样,但是变动不大.要连接数据库。。SqlHelper是一个基于.NET Framework的数据库操作组件。组件中包含数据库操作方法， 工具类写的时候很费劲，但一旦写完之后，以后用起来就方便多了，所以给大家分享一下，技术宅的人其实还不是很多，我们都需 努力、共勉
sql生成代码工具
05-30
一个简单的sql生成代码工具,(๑′ᴗ‵๑)Ｉ Lᵒᵛᵉᵧₒᵤ❤
sql代码格式化工具类
06-01
该工具可以放在java项目中直接运行该文件，把需要格式sql放进去，进行格式化
Mybatis常用工具类-SQL类
qq_45701514的博客
04-03 1386
Mybatis工具类
SqlHelper工具类
飘_Float Yang'S Blog
06-01 515
import java.io.*; import java.sql.*; import java.util.*; public class SqlHelper { private Connection conn=null; private PreparedStatement ps=null; private ResultSet rs=null; private static String
Mybatis工具—SQL构造器（SQL类）
乐只悠悠
07-04 3226
  Mybatis为了提高构建SQL语句的统一性和便捷性，提供了一个构建SQL语句的工具类——SQL（org.apache.ibatis.jdbc.SQL），是的，你没看错它就叫SQL，使用这个工具类可以很方便地在Java代码中动态构建SQL语句。...
SqlKit工具类
xiongyouqiang的博客
05-06 634
package com.trendy.fw.common.db; import java.sql.Timestamp; import java.text.SimpleDateFormat; import java.util.Date; import java.util.List; import java.util.Set; import java.util.regex.Matcher; impo...
自动生成SQL语句的工具类
二进制天堂
05-04 7576
大量字段的修改，插入操作的SQL语句拼写起来实在令人挠头，项目又没有用HIBERNATE之类的ORM框架，怎么办呢？我写了几个类简单应付一下，献丑了 。
韩顺平推荐：高效SQL工具类库及使用指南
“韩顺平超强sql工具类”是一个适合web开发初学者的SQL工具包，由韩顺平提供，经过他人验证，对于新手来说非常有帮助。它包含配置文件（dbInfo.properties）、DBUtil.java、SqlHelper.java三个主要部分，支持根据两...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值