All Downloads are FREE. Search and download functionalities are using the official Maven repository.

org.dromara.hutool.db.sql.SqlExecutor Maven / Gradle / Ivy

Go to download

Hutool是一个优雅而小巧的Java工具类库

There is a newer version: 6.0.0.M3
Show newest version
/*
 * Copyright (c) 2013-2024 Hutool Team and hutool.cn
 *
 * Licensed under the Apache License, Version 2.0 (the "License");
 * you may not use this file except in compliance with the License.
 * You may obtain a copy of the License at
 *
 * http://www.apache.org/licenses/LICENSE-2.0
 *
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
 */

package org.dromara.hutool.db.sql;

import org.dromara.hutool.core.collection.iter.ArrayIter;
import org.dromara.hutool.core.func.SerFunction;
import org.dromara.hutool.core.io.IoUtil;
import org.dromara.hutool.db.DbException;
import org.dromara.hutool.db.config.DbConfig;
import org.dromara.hutool.db.handler.RsHandler;

import java.sql.*;
import java.util.Map;

/**
 * SQL执行器，用于执行指定的SQL查询、更新语句。

 * 此执行器执行原始SQL。
 *
 * @author Looly
 */
public class SqlExecutor {

	/**
	 * 创建SqlExecutor
	 *
	 * @param config 配置
	 * @param conn   {@link Connection}
	 * @return SqlExecutor
	 */
	public static SqlExecutor of(final DbConfig config, final Connection conn) {
		return new SqlExecutor(config, conn);
	}

	private final DbConfig config;
	private final Connection conn;

	/**
	 * 构造
	 *
	 * @param config 配置
	 * @param conn   {@link Connection}
	 */
	public SqlExecutor(final DbConfig config, final Connection conn) {
		this.config = config;
		this.conn = conn;
	}

	/**
	 * 执行非查询语句

	 * 语句包括 插入、更新、删除

	 * 此方法不会关闭Connection
	 *
	 * @param sql      SQL，使用name做为占位符，例如:name
	 * @param paramMap 参数Map
	 * @return 影响的行数
	 * @throws DbException SQL执行异常
	 * @since 4.0.10
	 */
	public int execute(final String sql, final Map paramMap) throws DbException {
		final NamedSql namedSql = new NamedSql(sql, paramMap);
		return execute(namedSql.getSql(), namedSql.getParamArray());
	}

	/**
	 * 执行非查询语句

	 * 语句包括 插入、更新、删除

	 * 此方法不会关闭Connection
	 *
	 * @param sql    SQL
	 * @param params 参数
	 * @return 影响的行数
	 * @throws DbException SQL执行异常
	 */
	public int execute(final String sql, final Object... params) throws DbException {
		PreparedStatement ps = null;
		try {
			ps = StatementUtil.prepareStatement(false, this.config, this.conn, sql, params);
			return ps.executeUpdate();
		} catch (final SQLException e) {
			throw new DbException(e);
		} finally {
			IoUtil.closeQuietly(ps);
		}
	}

	/**
	 * 执行调用存储过程

	 * 此方法不会关闭Connection
	 *
	 * @param sql    SQL
	 * @param params 参数
	 * @return 如果执行后第一个结果是ResultSet，则返回true，否则返回false。
	 * @throws DbException SQL执行异常
	 */
	public boolean call(final String sql, final Object... params) throws DbException {
		CallableStatement call = null;
		try {
			call = StatementUtil.prepareCall(this.config, this.conn, sql, params);
			return call.execute();
		} catch (final SQLException e) {
			throw new DbException(e);
		} finally {
			IoUtil.closeQuietly(call);
		}
	}

	/**
	 * 执行调用存储过程

	 * 此方法不会关闭Connection
	 *
	 * @param sql    SQL
	 * @param params 参数
	 * @return ResultSet
	 * @throws DbException SQL执行异常
	 * @since 4.1.4
	 */
	public ResultSet callQuery(final String sql, final Object... params) throws DbException {
		try {
			return StatementUtil.prepareCall(this.config, this.conn, sql, params).executeQuery();
		} catch (final SQLException e) {
			throw new DbException(e);
		}
	}

	/**
	 * 执行非查询语句，返回主键

	 * 发查询语句包括 插入、更新、删除

	 * 此方法不会关闭Connection
	 *
	 * @param sql      SQL
	 * @param paramMap 参数Map
	 * @return 主键
	 * @throws DbException SQL执行异常
	 * @since 4.0.10
	 */
	public Long executeForGeneratedKey(final String sql, final Map paramMap) throws DbException {
		final NamedSql namedSql = new NamedSql(sql, paramMap);
		return executeForGeneratedKey(namedSql.getSql(), namedSql.getParamArray());
	}

	/**
	 * 执行非查询语句，返回主键

	 * 发查询语句包括 插入、更新、删除

	 * 此方法不会关闭Connection
	 *
	 * @param sql    SQL
	 * @param params 参数
	 * @return 主键
	 * @throws DbException SQL执行异常
	 */
	public Long executeForGeneratedKey(final String sql, final Object... params) throws DbException {
		PreparedStatement ps = null;
		try {
			ps = StatementUtil.prepareStatement(true, this.config, this.conn, sql, params);
			ps.executeUpdate();
			return StatementUtil.getGeneratedKeyOfLong(ps);
		} catch (final SQLException e) {
			throw new DbException(e);
		} finally {
			IoUtil.closeQuietly(ps);
		}
	}

	/**
	 * 批量执行非查询语句

	 * 语句包括 插入、更新、删除

	 * 此方法不会关闭Connection
	 *
	 * @param sql         SQL
	 * @param paramsBatch 批量的参数
	 * @return 每个SQL执行影响的行数
	 * @throws DbException SQL执行异常
	 */
	public int[] executeBatch(final String sql, final Iterable paramsBatch) throws DbException {
		PreparedStatement ps = null;
		try {
			ps = StatementUtil.prepareStatementForBatch(this.config, this.conn, sql, paramsBatch);
			return ps.executeBatch();
		} catch (final SQLException e) {
			throw new DbException(e);
		} finally {
			IoUtil.closeQuietly(ps);
		}
	}

	/**
	 * 批量执行非查询语句

	 * 语句包括 插入、更新、删除

	 * 此方法不会关闭Connection
	 *
	 * @param sqls SQL列表
	 * @return 每个SQL执行影响的行数
	 * @throws DbException SQL执行异常
	 * @since 4.5.6
	 */
	public int[] executeBatch(final String... sqls) throws DbException {
		return executeBatch(new ArrayIter<>(sqls));
	}

	/**
	 * 批量执行非查询语句

	 * 语句包括 插入、更新、删除

	 * 此方法不会关闭Connection
	 *
	 * @param sqls SQL列表
	 * @return 每个SQL执行影响的行数
	 * @throws DbException SQL执行异常
	 * @since 4.5.6
	 */
	public int[] executeBatch(final Iterable sqls) throws DbException {
		Statement statement = null;
		try {
			statement = this.conn.createStatement();
			for (final String sql : sqls) {
				statement.addBatch(sql);
			}
			return statement.executeBatch();
		} catch (final SQLException e) {
			throw new DbException(e);
		} finally {
			IoUtil.closeQuietly(statement);
		}
	}

	// region ----- query

	/**
	 * 执行查询语句，例如：select * from table where field1=:name1 

	 * 此方法不会关闭Connection
	 *
	 * @param       处理结果类型
	 * @param sql      查询语句，使用参数名占位符，例如:name
	 * @param rsh      结果集处理对象
	 * @param paramMap 参数对
	 * @return 结果对象
	 * @throws DbException SQL执行异常
	 * @since 4.0.10
	 */
	public  T query(final String sql, final RsHandler rsh, final Map paramMap) throws DbException {
		final NamedSql namedSql = new NamedSql(sql, paramMap);
		return query(namedSql.getSql(), rsh, namedSql.getParamArray());
	}

	/**
	 * 执行查询语句

	 * 此方法不会关闭Connection
	 *
	 * @param         处理结果类型
	 * @param sqlBuilder SQL构建器，包含参数
	 * @param rsh        结果集处理对象
	 * @return 结果对象
	 * @throws DbException SQL执行异常
	 * @since 5.5.3
	 */
	public  T query(final SqlBuilder sqlBuilder, final RsHandler rsh) throws DbException {
		return query(sqlBuilder.build(), rsh, sqlBuilder.getParamValueArray());
	}

	/**
	 * 执行查询语句

	 * 此方法不会关闭Connection
	 *
	 * @param     处理结果类型
	 * @param sql    查询语句
	 * @param rsh    结果集处理对象
	 * @param params 参数
	 * @return 结果对象
	 * @throws DbException SQL执行异常
	 */
	public  T query(final String sql, final RsHandler rsh, final Object... params) throws DbException {
		PreparedStatement ps = null;
		try {
			ps = StatementUtil.prepareStatement(false, this.config, this.conn, sql, params);
			return StatementUtil.executeQuery(ps, rsh);
		} finally {
			IoUtil.closeQuietly(ps);
		}
	}

	/**
	 * 执行自定义的{@link PreparedStatement}，结果使用{@link RsHandler}处理

	 * 此方法主要用于自定义场景，如游标查询等
	 *
	 * @param            处理结果类型
	 * @param statementFunc 自定义{@link PreparedStatement}创建函数
	 * @param rsh           自定义结果集处理
	 * @return 结果
	 * @throws DbException SQL执行异常
	 * @since 5.7.17
	 */
	public  T query(final SerFunction statementFunc, final RsHandler rsh) throws DbException {
		PreparedStatement ps = null;
		try {
			ps = statementFunc.apply(conn);
			return StatementUtil.executeQuery(ps, rsh);
		} finally {
			IoUtil.closeQuietly(ps);
		}
	}
	// endregion
}




© 2015 - 2024 Weber Informatics LLC | Privacy Policy