ibatis 数据增删改查一日一表的情况

假设数据库表为user_log_xxx（日期20220512），数据有id，name ,createTime

/**UserLog.java*/
import java.io.Serializable;
import java.util.Date;
 
public class User implements Serializable{
    /**id*/
	private Integer id;
    
    /**昵称*/
	private String name;
    
    /**开始时间*/
	private Date ceateTime;

    /** 开始时间 */
	private Date startTime;
	/** 结束时间 */
	private Date endTime;
	/** 开始时间字符串格式 */
	private String sStartTime;
	/** 结束时间字符串格式 */
	private String sEndTime;
    /** 表名后缀 */
	private String tableNameSuffix = DateService.getTableSuffixByType("", DateService.DATE_BY_DAY);

    
     public Integer getId() {
		return id;
	}
	public void setId(Integer id) {
		this.id = id;
	}
 
    public String getName() {
		return name;
	}
	public void setName(String name) {
		this.name = name;
	}
    public Date getCreateTime() {
		return createTime;
	}
	public void setCreateTime(Date createTime) {
		this.createTime = createTime;
	}
    
    public Date getStartTime() {
		return startTime;
	}

	public void setStartTime(Date startTime) {
		this.startTime = startTime;
		this.sStartTime = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(startTime);
	}

	public Date getEndTime() {
		return endTime;
	}

	public void setEndTime(Date endTime) {
		this.endTime = endTime;
		this.sEndTime = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(endTime);
	}
	
	public String getTableNameSuffix() {
		return tableNameSuffix;
	}

	public void setTableNameSuffix(String tableNameSuffix) {
		this.tableNameSuffix = tableNameSuffix;
	}
	
	public String getSStartTime() {
		return sStartTime;
	}

	public void setSStartTime(String startTime) {
		sStartTime = startTime;
	}

	public String getSEndTime() {
		return sEndTime;
	}

	public void setSEndTime(String endTime) {
		sEndTime = endTime;
	}
}

DateService.java

public class DateService {

	/** 默认时间  */
	private static java.util.Date defaultDate = null;
	public static final int DATE_BY_MONTH = 1; //按月分表
	public static final int DATE_BY_WEEK = 2;	//按周分表
	public static final int DATE_BY_DAY = 3;	//按天分表
}

UserLog.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE sqlMap PUBLIC "-//ibatis.apache.org//DTD SQL Map 2.0//EN" "http://ibatis.apache.org/dtd/sql-map-2.dtd">
<sqlMap namespace="UserLog">
    <typeAlias alias="UserLog" type="com.domain.UserLog" />
    
    <resultMap class="com.domain.UserLog"
		id="PlayerGameRecord">
		<result property="id" column="ID" />
		<result property="name" column="Name"/>
		<result property="createTime" column="create_Time" />
	</resultMap>
    
    <insert id="createTable" parameterClass="String">
		create table if not exists
		user_log_$tableNameSuffix$ like user_log
	</insert>

    <!--查看当天是否已存在表，game_log为数据库名-->
    <select id="checkTableIsExists" parameterClass="String" resultClass="String">
		SELECT table_name FROM information_schema.TABLES WHERE table_name = #tableName#  AND table_schema = "game_log";
	</select>

    <!--插入数据-->
    <insert id="insert" parameterClass="UserLog">
		INSERT INTO user_log_$tableNameSuffix$
		(ID,Name,create_Time)
		VALUES
		(#id#,#name#,#createTime#)
	</insert>

    <!--获取分页数据-->
    <select id="getUserLogByPage" parameterClass="java.util.Map"
		resultMap="UserLog">
		SELECT * FROM $tableName$ 
		WHERE DATE_FORMAT(BET_TIME,'%Y%m%d') = #date#
		<isNotNull property="startTime" prepend="AND">
			<![CDATA[ create_Time >= #startTime# ]]>
		</isNotNull>
		<isNotNull property="endTime"  prepend="AND">
			<![CDATA[ create_Time <= #endTime# ]]>
		</isNotNull>
		ORDER BY ID ASC LIMIT #start#,#onePageNum#
	</select>

    <!-- 查询多日数据 -->
    <select id="getUserDays" parameterClass="hashMap"  resultMap="UserLog">
       SELECT * FROM (
       <iterate property="days" conjunction="union"  removeFirstPrepend="true" > 
          select * from user_log__$days[]$ tt WHERE UNIX_TIMESTAMP(tt.create_Time) between UNIX_TIMESTAMP(#startTime#) and UNIX_TIMESTAMP(#endTime#)
          </iterate>
          )t
	</select>
</sqlMap>

UserLogDAO.java

import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.springframework.orm.ibatis.support.SqlMapClientDaoSupport;

import IUserLogDAO ;
import com.domain.UserLog;


public class UserLogDAO extends SqlMapClientDaoSupport implements IUserLogDAO {
    private static String lastTableSuffixName = "";

	public void createTable(String tableNameSuffix) {
		this.getSqlMapClientTemplate().insert("UserLog.createTable", tableNameSuffix);
	}

    /**创建数据*/
	public void createUserLog(UserLog userLog) {
		if (lastTableSuffixName.equals(userLog.getTableNameSuffix()) == false) {// 
			lastTableSuffixName = userLog.getTableNameSuffix();
			createTable(userLog.getTableNameSuffix());
		}
		userLog.setEndTime(new Date());
		this.getSqlMapClientTemplate().insert("UserLog.insert", userLog);
	}

    public String checkTableIsExists(String tableName) {
		String trueName = (String)         this.getSqlMapClientTemplate().queryForObject("UserLog.checkTableIsExists", tableName);
		return trueName;
	}

    /**分页查询 */
	public List<UserLog> getUserLogByPage(String date,Date startTime, Date endTime,Integer start, Integer onePageNum) {
		String tableName = "user_log__" + date;
		if (this.checkTableIsExists(tableName) != null) {
			Map<String, Object> params = new HashMap<String, Object>();
			params.put("tableName", tableName);
			params.put("date", date);
			params.put("start", start);
			params.put("onePageNum", onePageNum);
			params.put("startTime", startTime);
			params.put("endTime", endTime);
			return this.getSqlMapClientTemplate().queryForList("UserLog.getUserLogByPage", params);
		}else{
			return null;
		}
	}

    /**查询多日数据 */
    public List<UserLog> getUserDays(HashMap maps) throws SQLException, ParseException {
		String startTime = (String) maps.get("startTime");
		String endTime = (String) maps.get("endTime");
		Calendar cal = Calendar.getInstance();
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
		Date begin = sdf.parse(startTime.substring(0, 10));
		Date end = sdf.parse(endTime.substring(0, 10));
		cal.setTime(begin);
		logger.info("startTime:"+format.format(cal.getTime()));
		long daysBetween = (end.getTime() - begin.getTime() + 1000000) / (3600 * 24 * 1000);// 相隔的天数
		logger.info(startTime + "与 " + endTime + " 相隔 " + daysBetween + " 天");
		List<String> days = new ArrayList<String>();
		String tableBeginName = "user_log__" + format.format(cal.getTime());
		if (this.checkLoginLogTableIsExists(tableBeginName) != null) {
			days.add(format.format(cal.getTime()));
		}
		for (int i = 0; i <= daysBetween; i++) {
			if (daysBetween == 0) {
				String tableName = "user_log__" + startTime.substring(0, 4) + startTime.substring(5, 7) + startTime.substring(8, 10);
				if (this.checkLoginLogTableIsExists(tableName) != null) {
					days.add(startTime.substring(0, 4) + startTime.substring(5, 7) + startTime.substring(8, 10));
				}
			} else {
				cal.add(Calendar.DAY_OF_MONTH, 1);
				String a = format.format(cal.getTime());
				String tableName = "user_log__" + a;
				if (this.checkLoginLogTableIsExists(tableName) != null) {
					days.add(a);
				}
			}
		}
		logger.info(days);
		if (days.size() > 0) {
			maps.put("days", days);
			logger.info(maps);
			return  this.getSqlMapClientTemplate().queryForList("UserLog.getUserDays", maps);
		}else{
			return 0;
		}
	}
}

IUserLogDAO.java


import java.util.Date;
import java.util.List;
import java.util.Map;

import com.domain.UserLog;

public interface IUserLogDAO {
    public void createTable(String tableNameSuffix);
    /**创建数据*/
	public void createUserLog(UserLog userLog);

    public String checkTableIsExists(String tableName);

    /**分页查询 */
	public List<UserLog> getUserLogByPage(String date,Date startTime, Date endTime,Integer start, Integer onePageNum);

    /**查询多日数据 */
    public List<UserLog> getUserDays(HashMap maps) throws SQLException, ParseException
}

原文链接：https://blog.csdn.net/zi114323/article/details/124736685

你可能也喜欢