• Home
• org.voovan
• voovan-database
• 4.0.14
• source code
• TSQL.java

×

Project download

Many resources are needed to download a project. Please understand that we have to compensate our server costs. Thank you in advance.
Project price only 1 $

You can buy this project and download/modify it how often you want.

org.voovan.tools.TSQL Maven / Gradle / Ivy

package org.voovan.tools;

import org.voovan.db.CallType;
import org.voovan.db.DataBaseType;
import org.voovan.db.JdbcOperate;
import org.voovan.tools.json.JSON;
import org.voovan.tools.log.Logger;
import org.voovan.tools.reflect.TReflect;

import java.lang.reflect.Method;
import java.math.BigDecimal;
import java.sql.*;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.Date;
import java.util.Map.Entry;
import java.util.regex.Pattern;

/**
 * SQL处理帮助类
 *
 * 注意所有的时间都用TDateTime.STANDER_DATETIME_TEMPLATE的格式
 * @author helyho
 *
 * Voovan Framework.
 * WebSite: https://github.com/helyho/Voovan
 * Licence: Apache v2 License
 */
public class TSQL {
	/**
	 * 从 SQL 字符串中,取 SQL 参数表
	 * @param sqlStr  原始 sql 字符串 (select * from table where x=::x and y=::y)
	 * @return  sql 参数对照表 ([:x,:y])
	 */
	public static List getSqlParamNames(String sqlStr){
		String[] params = TString.searchByRegex(sqlStr, "::\\w+\\b");
		ArrayList sqlParamNames = new ArrayList();
		for(String param : params){
			sqlParamNames.add(param);
		}
		return sqlParamNames;
	}

	/**
	 * 转换preparedStatement对象为可用的 sql 字符串(参数用?表示)
	 * @param sqlStr		原始 sql 字符串 (select * from table where x=:x and y=::y)
	 * @return				将所有的:引导的参数转换成? (select * from table where x=? and y=?)
	 */
	public static String preparedSql(String sqlStr){
		return TString.fastReplaceAll(sqlStr, "::\\w+\\b", "?");
	}

	/**
	 * 给preparedStatement对象设置参数
	 *
	 * @param preparedStatement  preparedStatement对象
	 * @param sqlParamNames			sql 参数表
	 * @param params			参数键值 Map
	 * @throws SQLException SQL 异常
	 */
	public static void setPreparedParams(PreparedStatement preparedStatement,List sqlParamNames,Map params) throws SQLException{
		for(int i=0;i params) throws SQLException{

		//将没有提供查询参数的条件移除
		sqlStr = TSQL.removeEmptyCondiction(sqlStr, params);

		//获取参数列表
		List sqlParamNames = TSQL.getSqlParamNames(sqlStr);

		if(Logger.isLogLevel("DEBUG")) {
			Logger.fremawork("[SQL_Executed]: " + assembleSQLWithMap(sqlStr, params));
		}

		//获取preparedStatement可用的 SQL
		String preparedSql = TSQL.preparedSql(sqlStr);

		PreparedStatement preparedStatement = (PreparedStatement) conn.prepareStatement(preparedSql);

		//如果params为空,则新建一个
		if(params==null){
			params = new Hashtable();
		}

		//为preparedStatement参数填充
		TSQL.setPreparedParams(preparedStatement, sqlParamNames, params);
		return preparedStatement;
	}

	/**
	 * 创建PreparedStatement
	 * @param conn      数据库连接
	 * @param sqlStr    sql 自负穿
	 * @param params    Map 参数
	 * @param callTypes 调用参数类型
	 * @return			PreparedStatement 对象
	 * @throws SQLException SQL 异常
	 */
	public static CallableStatement createCallableStatement(Connection conn,String sqlStr,Map params, CallType[] callTypes) throws SQLException{
		Logger.fremawork("[SQL_Executed]: " + sqlStr);
		//获取参数列表
		List sqlParamNames = TSQL.getSqlParamNames(sqlStr);
		//获取preparedStatement可用的 SQL
		String preparedSql = TSQL.preparedSql(sqlStr);

		//定义 jdbc statement 对象
		CallableStatement callableStatement = (CallableStatement) conn.prepareCall(preparedSql);

		//如果params为空,则新建一个
		if(params==null){
			params = new Hashtable();
		}

		//callableStatement参数填充
		TSQL.setPreparedParams(callableStatement,sqlParamNames,params);

		//根据存储过程参数定义,注册 OUT 参数
		ParameterMetaData parameterMetaData = callableStatement.getParameterMetaData();
		for(int i=0;i getCallableStatementResult(CallableStatement callableStatement) throws SQLException{
		ArrayList