hibernate实现分表

Bertha 。 2022-08-27 09:52 5阅读 0赞

Hibernate实现分表处理，分表原理基于SQLInterceptor方式实现

import java.util.Date;
    
    import org.hibernate.Session;
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.beans.factory.annotation.Qualifier;
    import org.springframework.orm.hibernate3.HibernateTemplate;
    import org.springframework.stereotype.Component;
    
    @Component
    public class TestDAO {
    	@Autowired
    	@Qualifier("XXXXTemplate")
    	private HibernateTemplate template;
    	private SQLInterceptor interceptor = new SQLInterceptor("tableName");
    	
    	public void save(POJO entity){
    		Session session = template.getSessionFactory().openSession(interceptor);
    		/*分表策略：按月份对数据进行分割*/
    		Integer yearMonth = Integer.parseInt(DateUtils.format(new Date(), "yyyyMM"));
    		interceptor.setNewTable("tableName_"+yearMonth);
    		session.save(entity);
    		session.close();
    		System.out.println("返回主键ID:" + entity.getId());
    	}
    }

\#HibernateSQL拦截器

import org.hibernate.EmptyInterceptor;
    public class SQLInterceptor extends EmptyInterceptor {
    	private static final long serialVersionUID = 1637672155224242981L;
    
    	public SQLInterceptor(String table) {
    		this.table = table;
    	}
    
    	public String table;
    	private String newTable;
    
    	public void setNewTable(String newTable) {
    		this.newTable = newTable;
    	}
    
    	@Override
    	public String onPrepareStatement(String sql) {
    		if (StringUtils.isNotEmpty(table, newTable)) {
    			sql = sql.replaceAll(table, newTable);
    		}
    		return super.onPrepareStatement(sql);
    	}
    
    }

使用JDBC方式获取数据库表生成自增ID

private Integer insert(POJO entity, String table) throws SQLException {
    		String insertSQL = "INSERT INTO " + table + " (XXXX)VALUES(?,?)";
    		DataSource dataSource = SessionFactoryUtils.getDataSource(getHibernateTemplate().getSessionFactory());
    		Connection connection = null;
    		PreparedStatement pStatement = null;
    		ResultSet resultSet = null;
    		try {
    			connection = dataSource.getConnection();
    			pStatement = connection.prepareStatement(insertSQL, Statement.RETURN_GENERATED_KEYS);//返回数据库生成自增ID
    			pStatement.setInt(1, entity.getSId());
    			pStatement.setInt(2, entity.getNum());
    			pStatement.executeUpdate();
    			resultSet = pStatement.getGeneratedKeys();
    			if (resultSet.next()) {//获取数据库生成ID
    				return resultSet.getInt(1);
    			}
    		} finally {
    			//close()
    		}
    		return -1;
    	}