首页 > 代码库 > hibernate实现分表后获取insert自增ID方法。

hibernate实现分表后获取insert自增ID方法。

#第一种基于原生的JDBC的方式实现

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);
			pStatement.setInt(1, entity.getSId());
			pStatement.setInt(2, entity.getNum());
			pStatement.executeUpdate();
			resultSet = pStatement.getGeneratedKeys();
			if (resultSet.next()) {
				return resultSet.getInt(1);
			}
		} finally {
			if (resultSet != null) {
				resultSet.close();
				resultSet = null;
			}
			if (pStatement != null) {
				pStatement.close();
				pStatement = null;
			}
			if (connection != null) {
				connection.close();
				connection = null;
			}
		}
		return -1;
	}


#第二种 使用基于原生的Hibernate实现

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);
	}

}