package com.hxh.spring.test;

import java.util.List;

import org.hibernate.HibernateException;
import org.hibernate.Session;
import org.junit.Test;
import org.springframework.orm.hibernate4.HibernateCallback;

import com.yeshi.buwan.dao.VideoInfoDao;
import com.yeshi.buwan.util.BeanUtil;

public class MySqlTest {

	@Test
	public void test() {
		VideoInfoDao viDao = BeanUtil.getBean(VideoInfoDao.class);
		viDao.excute(new HibernateCallback() {

			public Object doInHibernate(Session session) throws HibernateException {
				List list = session
						.createSQLQuery(
								"select v.id,v.area,t.typeid from wk_video_area_video_temp t left join wk_video_video v on v.id=t.id where t.areas is null")
						.setFirstResult(0).setMaxResults(5000).list();
				for (int i = 0; i < list.size(); i++) {
					Object[] objs = (Object[]) list.get(i);
					List areas = session
							.createSQLQuery(
									"SELECT GROUP_CONCAT(DISTINCT (ca.`name`))  FROM (SELECT  ca.`pid`  FROM  wk_category_area ca   WHERE ? LIKE CONCAT('%', ca.`name`, '%')   AND ca.`pid` > 4) ca1  LEFT JOIN wk_category_area ca  ON ca1.`pid` = ca.id  WHERE ca.`pid` = ? - 149")
							.setParameter(0, objs[1] + "").setParameter(1, objs[2] + "").list();

					if (areas.size() > 0) {
						session.getTransaction().begin();
						session.createSQLQuery("update wk_video_area_video_temp wt set wt.areas=? where wt.id=?")
								.setParameter(0, areas.get(0) + "").setParameter(1, objs[0]).executeUpdate();
						session.flush();
						session.getTransaction().commit();
					}
				}

				return null;
			}
		});

	}

}