org.dromara.hutool.db.AbstractDb Maven / Gradle / Ivy

Show more of this group Show more artifacts with this name
Show all versions of hutool-all Show documentation
Hutool是一个优雅而小巧的Java工具类库
There is a newer version: 6.0.0.M3
/*
 * 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;

import org.dromara.hutool.core.func.SerFunction;
import org.dromara.hutool.db.config.DbConfig;
import org.dromara.hutool.db.dialect.Dialect;
import org.dromara.hutool.db.ds.DSWrapper;
import org.dromara.hutool.db.handler.*;
import org.dromara.hutool.db.sql.*;
import org.dromara.hutool.db.sql.Condition.LikeType;

import javax.sql.DataSource;
import java.io.Serializable;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.Arrays;
import java.util.Collection;
import java.util.List;
import java.util.Map;

/**
 * 抽象数据库操作类

 * 通过给定的数据源执行给定SQL或者给定数据源和方言，执行相应的CRUD操作

 * 提供抽象方法getConnection和closeConnection，用于自定义数据库连接的打开和关闭
 *
 * @param  return this类型
 * @author looly
 */
public abstract class AbstractDb> extends DefaultConnectionHolder implements Serializable {
	private static final long serialVersionUID = 3858951941916349062L;

	/**
	 * 是否支持事务
	 */
	protected Boolean isSupportTransaction = null;
	protected DialectRunner runner;
	protected DbConfig dbConfig;
	/**
	 * 是否大小写不敏感（默认大小写不敏感）
	 */
	protected boolean caseInsensitive = true;

	// ------------------------------------------------------- Constructor start

	/**
	 * 构造
	 *
	 * @param ds      数据源
	 * @param dialect 数据库方言
	 */
	public AbstractDb(final DataSource ds, final Dialect dialect) {
		super(ds);
		if(ds instanceof DSWrapper){
			this.dbConfig = ((DSWrapper) ds).getDbConfig();
			this.caseInsensitive = this.dbConfig.isCaseInsensitive();
		}
		this.runner = new DialectRunner(this.dbConfig, dialect);
	}
	// ------------------------------------------------------- Constructor end

	// region ----- query

	/**
	 * 查询
	 *
	 * @param sql    查询语句
	 * @param params 参数
	 * @return 结果对象
	 * @throws DbException SQL执行异常
	 * @since 3.1.1
	 */
	public List query(final String sql, final Map params) throws DbException {
		return query(sql, new EntityListHandler(this.caseInsensitive), params);
	}

	/**
	 * 查询
	 *
	 * @param sql    查询语句
	 * @param params 参数
	 * @return 结果对象
	 * @throws DbException SQL执行异常
	 * @since 3.1.1
	 */
	public List query(final String sql, final Object... params) throws DbException {
		return query(sql, new EntityListHandler(this.caseInsensitive), params);
	}

	/**
	 * 查询
	 *
	 * @param        结果集需要处理的对象类型
	 * @param sql       查询语句
	 * @param beanClass 元素Bean类型
	 * @param params    参数
	 * @return 结果对象
	 * @throws DbException SQL执行异常
	 * @since 3.2.2
	 */
	public  List query(final String sql, final Class beanClass, final Object... params) throws DbException {
		return query(sql, new BeanListHandler<>(beanClass), params);
	}

	/**
	 * 查询单条记录
	 *
	 * @param sql    查询语句
	 * @param params 参数
	 * @return 结果对象
	 * @throws DbException SQL执行异常
	 */
	public Entity queryOne(final String sql, final Object... params) throws DbException {
		return query(sql, new EntityHandler(this.caseInsensitive), params);
	}

	/**
	 * 查询单条单个字段记录,并将其转换为Number
	 *
	 * @param sql    查询语句
	 * @param params 参数
	 * @return 结果对象
	 * @throws DbException SQL执行异常
	 */
	public Number queryNumber(final String sql, final Object... params) throws DbException {
		return query(sql, NumberHandler.INSTANCE, params);
	}

	/**
	 * 查询单条单个字段记录,并将其转换为String
	 *
	 * @param sql    查询语句
	 * @param params 参数
	 * @return 结果对象
	 * @throws DbException SQL执行异常
	 */
	public String queryString(final String sql, final Object... params) throws DbException {
		return query(sql, new StringHandler(), params);
	}

	/**
	 * 查询
	 *
	 * @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 {
		Connection conn = null;
		try {
			conn = this.getConnection();
			return SqlExecutor.of(this.dbConfig, conn).query(sql, rsh, params);
		} finally {
			this.closeConnection(conn);
		}
	}

	/**
	 * 支持占位符的查询，例如：select * from table where field1=:name1
	 *
	 * @param       结果集需要处理的对象类型
	 * @param sql      查询语句，使用参数名占位符，例如:name
	 * @param rsh      结果集处理对象
	 * @param paramMap 参数
	 * @return 结果对象
	 * @throws DbException SQL执行异常
	 * @since 5.2.2
	 */
	public  T query(final String sql, final RsHandler rsh, final Map paramMap) throws DbException {
		Connection conn = null;
		try {
			conn = this.getConnection();
			return SqlExecutor.of(this.dbConfig, conn).query(sql, rsh, paramMap);
		} finally {
			this.closeConnection(conn);
		}
	}

	/**
	 * 执行自定义的{@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 {
		Connection conn = null;
		try {
			conn = this.getConnection();
			return SqlExecutor.of(this.dbConfig, conn).query(statementFunc, rsh);
		} finally {
			this.closeConnection(conn);
		}
	}
	// endregion

	// region ----- execute

	/**
	 * 执行非查询语句

	 * 语句包括 插入、更新、删除
	 *
	 * @param sql    SQL
	 * @param params 参数
	 * @return 影响行数
	 * @throws DbException SQL执行异常
	 */
	public int execute(final String sql, final Object... params) throws DbException {
		Connection conn = null;
		try {
			conn = this.getConnection();
			return SqlExecutor.of(this.dbConfig, conn).execute(sql, params);
		} finally {
			this.closeConnection(conn);
		}
	}

	/**
	 * 执行非查询语句

	 * 语句包括 插入、更新、删除
	 *
	 * @param sql    SQL
	 * @param params 参数
	 * @return 主键
	 * @throws DbException SQL执行异常
	 */
	public Long executeForGeneratedKey(final String sql, final Object... params) throws DbException {
		Connection conn = null;
		try {
			conn = this.getConnection();
			return SqlExecutor.of(this.dbConfig, conn).executeForGeneratedKey(sql, params);
		} finally {
			this.closeConnection(conn);
		}
	}

	/**
	 * 批量执行非查询语句
	 *
	 * @param sql         SQL
	 * @param paramsBatch 批量的参数
	 * @return 每个SQL执行影响的行数
	 * @throws DbException SQL执行异常
	 * @since 5.4.2
	 */
	public int[] executeBatch(final String sql, final Iterable paramsBatch) throws DbException {
		Connection conn = null;
		try {
			conn = this.getConnection();
			return SqlExecutor.of(this.dbConfig, conn).executeBatch(sql, paramsBatch);
		} finally {
			this.closeConnection(conn);
		}
	}

	/**
	 * 批量执行非查询语句
	 *
	 * @param sqls SQL列表
	 * @return 每个SQL执行影响的行数
	 * @throws DbException SQL执行异常
	 * @since 4.5.6
	 */
	public int[] executeBatch(final String... sqls) throws DbException {
		Connection conn = null;
		try {
			conn = this.getConnection();
			return SqlExecutor.of(this.dbConfig, conn).executeBatch(sqls);
		} finally {
			this.closeConnection(conn);
		}
	}

	/**
	 * 批量执行非查询语句
	 *
	 * @param sqls SQL列表
	 * @return 每个SQL执行影响的行数
	 * @throws DbException SQL执行异常
	 * @since 5.4.2
	 */
	public int[] executeBatch(final Iterable sqls) throws DbException {
		Connection conn = null;
		try {
			conn = this.getConnection();
			return SqlExecutor.of(this.dbConfig, conn).executeBatch(sqls);
		} finally {
			this.closeConnection(conn);
		}
	}
	// endregion

	// ---------------------------------------------------------------------------- CRUD start

	// region ----- insert

	/**
	 * 插入数据
	 *
	 * @param record 记录
	 * @return 插入行数
	 * @throws DbException SQL执行异常
	 */
	public int insert(final Entity record) throws DbException {
		Connection conn = null;
		try {
			conn = this.getConnection();
			return runner.insert(conn, record)[0];
		} finally {
			this.closeConnection(conn);
		}
	}

	/**
	 * 插入或更新数据

	 * 根据给定的字段名查询数据，如果存在则更新这些数据，否则执行插入
	 *
	 * @param record 记录
	 * @param keys   需要检查唯一性的字段
	 * @return 插入行数
	 * @throws DbException SQL执行异常
	 * @since 4.0.10
	 */
	public int insertOrUpdate(final Entity record, final String... keys) throws DbException {
		Connection conn = null;
		try {
			conn = this.getConnection();
			return runner.insertOrUpdate(conn, record, keys);
		} finally {
			this.closeConnection(conn);
		}
	}

	/**
	 * 使用upsert语义插入或更新数据

	 * 根据给定的字段名查询数据，如果存在则更新这些数据，否则执行插入
	 * 如果方言未实现本方法，内部会自动调用insertOrUpdate来实现功能，由于upsert和insert使用有区别，为了兼容性保留原有insertOrUpdate不做变动
	 *
	 * @param record 记录
	 * @param keys   需要检查唯一性的字段
	 * @return 插入行数
	 * @throws DbException SQL执行异常
	 * @since 5.7.21
	 */
	public int upsert(final Entity record, final String... keys) throws DbException {
		Connection conn = null;
		try {
			conn = this.getConnection();
			return runner.upsert(conn, record, keys);
		} finally {
			this.closeConnection(conn);
		}
	}

	/**
	 * 批量插入数据

	 * 需要注意的是，批量插入每一条数据结构必须一致。批量插入数据时会获取第一条数据的字段结构，之后的数据会按照这个格式插入。

	 * 也就是说假如第一条数据只有2个字段，后边数据多于这两个字段的部分将被抛弃。
	 *
	 * @param records 记录列表
	 * @return 插入行数
	 * @throws DbException SQL执行异常
	 */
	public int[] insert(final Collection records) throws DbException {
		Connection conn = null;
		try {
			conn = this.getConnection();
			return runner.insert(conn, records.toArray(new Entity[0]));
		} finally {
			this.closeConnection(conn);
		}
	}

	/**
	 * 插入数据
	 *
	 * @param record 记录
	 * @return 主键列表
	 * @throws DbException SQL执行异常
	 */
	public List