java 批量定义参数 java批量添加数据dao层

转载

jacksky 2023-08-21 16:30:18

文章标签 java 批量定义参数 code sql List bc 文章分类 Java 后端开发

需求

在项目开发过程中，最基础的除了实体便是底层实现的持久层Dao了。虽然它在一般情况下不做业务的处理，但我们有必要使其更显得优雅，因为这样可以在后续开发和维护时减少很多不必要的麻烦。比如字段的增减、方法的新需求、其他Dao的联合查询等。基本原则是更改尽量只有一处，引用时也只引用这一处。以Jdbc的Dao实现为例：

思路

1.提供基本的方法进行持久层的操作；
2.项目工具类加入语句封装实体见附录一；
3.Dao实现中添加方法用作字段语句拼装见附录二；
4.使用该方法进行查询等操作。

步骤

1.添加、批量添加、删除、批量删除、更新、批量更新、查询单个参见附录三。
2.分页查询

/**
	 * 分页查询所有
	 */
	@SuppressWarnings({ "unchecked", "rawtypes" })
	@Override
	public List<FireTaskTypeEntity> findAll(Integer currentPage, Integer pageSize, FireTaskTypeEntity fireTaskTypeEntity) {
		StringBuffer sql = new StringBuffer();
		List<Object> params = new ArrayList<Object>();

		sql.append(" SELECT * ");
		sql.append("FROM T_FIRE_TASK_TYPE WHERE 1=1 ");

		// 任务类型检索条件拼装
		AssemblingSqlEntity assemblingSqlEntity = fireTaskTypeAssemblingSql(fireTaskTypeEntity, sql, params);
		sql = assemblingSqlEntity.getSql();
		params = assemblingSqlEntity.getParams();

		if (currentPage > 0 && pageSize > 0)
		{
			currentPage =  (currentPage-1) * pageSize;
			sql.append(" limit ?,?");
			params.add(currentPage);
			params.add(pageSize);
		}

		return this.jdbcTemplate.query(sql.toString(), params.toArray(), new BeanPropertyRowMapper(FireTaskTypeEntity.class));
	}

3.统计及判断是否存在

/**
	 * 统计
	 */
	@Override
	public int count(FireTaskTypeEntity fireTaskTypeEntity) {
		StringBuffer sql = new StringBuffer();
		List<Object> params = new ArrayList<Object>();
		sql.append("SELECT COUNT(*) FROM T_FIRE_TASK_TYPE WHERE 1=1 ");

		// 任务类型检索条件拼装
		AssemblingSqlEntity assemblingSqlEntity = fireTaskTypeAssemblingSql(
				fireTaskTypeEntity, sql, params);
		sql = assemblingSqlEntity.getSql();
		params = assemblingSqlEntity.getParams();

		return this.jdbcTemplate.queryForObject(sql.toString(), params.toArray(), Integer.class);
	}
	
    /**
	 * 判断是否存在
	 */
	 @Override
	public boolean exists(FireTaskTypeEntity fireTaskTypeEntity) {
		if (count(fireTaskTypeEntity) > 0) {
			return true;
		} else {
			return false;
		}
	}

这样的话将查询时的字段处理进行了统一管理。若是其他表需要关联查询时，只需要引用该方法即可，减少了需求变化时的错漏现象：

/**
	 * 关联用户表条件分页查询
	 */
	@SuppressWarnings({ "unchecked", "rawtypes" })
	@Override
	public List<FireTaskTypeEntity> findAll(Integer currentPage, Integer pageSize, FireTaskTypeEntity fireTaskTypeEntity) {
		StringBuffer sql = new StringBuffer();
		List<Object> params = new ArrayList<Object>();

		sql.append(" SELECT T_FIRE_TASK_TYPE.* ");
		sql.append(" FROM T_FIRE_TASK_TYPE ");
		sql.append(" INNER JOIN USER");
		sql.append(" ON T_FIRE_TASK_TYPE.ID=USER.ID WHERE 1=1 ");

		// 任务类型检索条件拼装
		AssemblingSqlEntity assemblingSqlEntity = fireTaskTypeAssemblingSql(fireTaskTypeEntity, sql, params);
		sql = assemblingSqlEntity.getSql();
		params = assemblingSqlEntity.getParams();

		// 关联用户表条件进行查询
		UserEntity userEntity = new UserEntity ();
		assemblingSqlEntity = UserDaoImpl.userAssemblingSql(userEntity , sql, params);
		sql = assemblingSqlEntity.getSql();
		params = assemblingSqlEntity.getParams();

		if (currentPage > 0 && pageSize > 0) {
			currentPage = (currentPage - 1) * pageSize;
			sql.append(" limit ?,?");
			params.add(currentPage);
			params.add(pageSize);
		}

		return this.jdbcTemplate.query(sql.toString(), params.toArray(), new BeanPropertyRowMapper(FireTaskTypeEntity.class));
	}

说明

考虑过将字段语句利用反射封装成工具，只是各数据表业务可能涉及到诸如模糊查询、多字段联合查询等场景，故暂以当前代码为项目Dao层稳定代码。另，技术实现不涉及理念，纯属个人风格，大家仅做参考！

附录一

package com.xakj.model;

import java.util.List;

/**
 * 语句拼装实体类
 */
public class AssemblingSqlEntity {
	private StringBuffer sql;// sql语句
	private List<Object> params;// 参数集合

	public StringBuffer getSql() {
		return sql;
	}

	public void setSql(StringBuffer sql) {
		this.sql = sql;
	}

	public List<Object> getParams() {
		return params;
	}

	public void setParams(List<Object> params) {
		this.params = params;
	}

	public AssemblingSqlEntity(StringBuffer sql, List<Object> params) {
		super();
		this.sql = sql;
		this.params = params;
	}
}

附录二

/**
	 * @param fireTaskTypeEntity
	 * @param sql
	 * @param params
	 * @return
	 */
	public static AssemblingSqlEntity fireTaskTypeAssemblingSql(
			FireTaskTypeEntity fireTaskTypeEntity, StringBuffer sql,
			List<Object> params) {
		if (!StringUtils.isEmpty(fireTaskTypeEntity.getId())) 
		{
			sql.append(" AND T_FIRE_TASK_TYPE.ID=? ");
			params.add(fireTaskTypeEntity.getId());
		}
		if (!StringUtils.isEmpty(fireTaskTypeEntity.getCreatetime())) 
		{
			sql.append(" AND T_FIRE_TASK_TYPE.CREATETIME=? ");
			params.add(fireTaskTypeEntity.getCreatetime());
		}
		if (!StringUtils.isEmpty(fireTaskTypeEntity.getDescribes())) 
		{
			sql.append(" AND T_FIRE_TASK_TYPE.DESCRIBES=? ");
			params.add(fireTaskTypeEntity.getDescribes());
		}
		if (!StringUtils.isEmpty(fireTaskTypeEntity.getCreater_id())) 
		{
			sql.append(" AND T_FIRE_TASK_TYPE.CREATER_ID=? ");
			params.add(fireTaskTypeEntity.getCreater_id());
		}
		if (!StringUtils.isEmpty(fireTaskTypeEntity.getStatus())) 
		{
			sql.append(" AND T_FIRE_TASK_TYPE.STATUS=? ");
			params.add(fireTaskTypeEntity.getStatus());
		}
		if (!StringUtils.isEmpty(fireTaskTypeEntity.getName())) 
		{
			sql.append(" AND T_FIRE_TASK_TYPE.NAME=? ");
			params.add(fireTaskTypeEntity.getName());
		}
		if (!StringUtils.isEmpty(fireTaskTypeEntity.getCreater())) 
		{
			sql.append(" AND T_FIRE_TASK_TYPE.CREATER=? ");
			params.add(fireTaskTypeEntity.getCreater());
		}
		return new AssemblingSqlEntity(sql, params);
	}

附录三

添加

/**
	 * 增加
	 */
	@Override
	public int save(FireTaskTypeEntity fireTaskTypeEntity) {
		List<Object> params = new ArrayList<Object>();
		StringBuffer sql = new StringBuffer();

		sql.append("INSERT INTO T_FIRE_TASK_TYPE (ID,CREATETIME,DESCRIBES,CREATER_ID,STATUS,NAME,CREATER) VALUES (?,?,?,?,?,?,?)");
		params.add(fireTaskTypeEntity.getId());
		params.add(fireTaskTypeEntity.getCreatetime());
		params.add(fireTaskTypeEntity.getDescribes());
		params.add(fireTaskTypeEntity.getCreater_id());
		params.add(fireTaskTypeEntity.getStatus());
		params.add(fireTaskTypeEntity.getName());
		params.add(fireTaskTypeEntity.getCreater());

		return this.jdbcTemplate.update(sql.toString(), params.toArray());

	}

批量添加

/**
	 * 批量增加
	 */
	@Override
	public void savaBatch(final List<FireTaskTypeEntity> fireTaskTypeList) {
		String sql = "INSERT INTO T_FIRE_TASK_TYPE (ID,CREATETIME,DESCRIBES,CREATER_ID,STATUS,NAME,CREATER) VALUES (?,?,?,?,?,?,?)";
		jdbcTemplate.batchUpdate(sql, new BatchPreparedStatementSetter() {
			public void setValues(PreparedStatement ps, int i) throws SQLException {
				FireTaskTypeEntity fireTaskTypeEntity = fireTaskTypeList.get(i);
				ps.setString(1, fireTaskTypeEntity.getId());
				ps.setString(2, fireTaskTypeEntity.getCreatetime());
				ps.setString(3, fireTaskTypeEntity.getDescribes());
				ps.setString(4, fireTaskTypeEntity.getCreater_id());
				ps.setInt(5, fireTaskTypeEntity.getStatus());
				ps.setString(6, fireTaskTypeEntity.getName());
				ps.setString(7, fireTaskTypeEntity.getCreater());
			}

			public int getBatchSize() {
				return fireTaskTypeList.size();
			}
		});
	}

删除

/**
	 * 删除
	 */
	@Override
	public int delete(String id) {
		StringBuffer sql = new StringBuffer();
		sql.append("DELETE FROM T_FIRE_TASK_TYPE WHERE ID=?");
		return this.jdbcTemplate.update(sql.toString(), id);
	}

批量删除

/**
	 * 批量删除
	 */
	@Override
	public void deleteBatch(final List<FireTaskTypeEntity> fireTaskTypeList) {
		String sql="DELETE FROM T_FIRE_TASK_TYPE WHERE ID=? ";

		this.jdbcTemplate.batchUpdate(sql,new BatchPreparedStatementSetter() {
			public void setValues(PreparedStatement ps, int i) throws SQLException {
				FireTaskTypeEntity fireTaskTypeEntity = fireTaskTypeList.get(i);
				ps.setString(1, fireTaskTypeEntity.getId());
			}
			public int getBatchSize() {
				return fireTaskTypeList.size();
			}
		});
	}

更新

/**
	 * 更新
	 */
	@Override
	public int update(FireTaskTypeEntity fireTaskTypeEntity) {
		StringBuffer sql = new StringBuffer();
		List<Object> params = new ArrayList<Object>();
		sql.append("UPDATE T_FIRE_TASK_TYPE SET ");
		if (!StringUtils.isEmpty(fireTaskTypeEntity.getId())) 
		{
		     sql.append(" ID=?, ");
		     params.add(fireTaskTypeEntity.getId());
		}
		if (!StringUtils.isEmpty(fireTaskTypeEntity.getCreatetime())) 
		{
		     sql.append(" CREATETIME=?, ");
		     params.add(fireTaskTypeEntity.getCreatetime());
		}
		if (!StringUtils.isEmpty(fireTaskTypeEntity.getDescribes())) 
		{
		     sql.append(" DESCRIBES=?, ");
		     params.add(fireTaskTypeEntity.getDescribes());
		}
		if (!StringUtils.isEmpty(fireTaskTypeEntity.getCreater_id())) 
		{
		     sql.append(" CREATER_ID=?, ");
		     params.add(fireTaskTypeEntity.getCreater_id());
		}
		if (!StringUtils.isEmpty(fireTaskTypeEntity.getStatus())) 
		{
		     sql.append(" STATUS=?, ");
		     params.add(fireTaskTypeEntity.getStatus());
		}
		if (!StringUtils.isEmpty(fireTaskTypeEntity.getName())) 
		{
		     sql.append(" NAME=?, ");
		     params.add(fireTaskTypeEntity.getName());
		}
		if (!StringUtils.isEmpty(fireTaskTypeEntity.getCreater())) 
		{
		     sql.append(" CREATER=?, ");
		     params.add(fireTaskTypeEntity.getCreater());
		}
		sql.delete(sql.lastIndexOf(","), sql.lastIndexOf(",")+1);
		sql.append(" WHERE ID=? ");
		params.add(fireTaskTypeEntity.getId());

		return this.jdbcTemplate.update(sql.toString(), params.toArray());

	}

批量更新

/**
	 * 批量更新
	 */
	@Override
	public void updateBatch(final List<FireTaskTypeEntity> fireTaskTypeList) {
		String sql="UPDATE T_FIRE_TASK_TYPE SET ID=? ,CREATETIME=? ,DESCRIBES=? ,CREATER_ID=? ,STATUS=? ,NAME=? ,CREATER=?  WHERE ID=? ";

		this.jdbcTemplate.batchUpdate(sql,new BatchPreparedStatementSetter() {
			public void setValues(PreparedStatement ps, int i) throws SQLException {
				FireTaskTypeEntity fireTaskTypeEntity = fireTaskTypeList.get(i);
				ps.setString(1, fireTaskTypeEntity.getId());
				ps.setString(2, fireTaskTypeEntity.getCreatetime());
				ps.setString(3, fireTaskTypeEntity.getDescribes());
				ps.setString(4, fireTaskTypeEntity.getCreater_id());
				ps.setInt(5, fireTaskTypeEntity.getStatus());
				ps.setString(6, fireTaskTypeEntity.getName());
				ps.setString(7, fireTaskTypeEntity.getCreater());
				ps.setString(8, fireTaskTypeEntity.getId());
			}

			public int getBatchSize() {
				return fireTaskTypeList.size();
			}
		});
	}

查询单个

/**
	 * 查询单个对象
	 */
	@Override
	public FireTaskTypeEntity findOne(String id) {
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT * FROM T_FIRE_TASK_TYPE WHERE ID=?");

		RowMapper<FireTaskTypeEntity> rowMapper = new BeanPropertyRowMapper<FireTaskTypeEntity>(FireTaskTypeEntity.class);
		return this.jdbcTemplate.queryForObject(sql.toString(), rowMapper, id);
	}

本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。