SimpleDaoExecTest.java

package org.nutz.dao.test.exec;

import static org.junit.Assert.*;

import java.sql.Types;

import org.junit.Test;
import org.nutz.dao.Sqls;
import org.nutz.dao.entity.Record;
import org.nutz.dao.sql.Sql;
import org.nutz.dao.test.DaoCase;
import org.nutz.dao.test.meta.Pet;

/**
 * 执行简单的存储过程,无输出参数
 * 
 * @author wendal
 * 
 */
public class SimpleDaoExecTest extends DaoCase {

	// H2 支持简单的CALL语句
	@Test
	public void test_simple_h2_call() {
		if (!dao.meta().isH2())
			return; // Only test for h2 now
		dao.create(Pet.class, true);
		dao.insert(Pet.create("wendal"));
		dao.execute(Sqls.create("CALL SELECT MAX(ID) FROM t_PET"));

		Sql sql = Sqls.fetchInt("CALL SELECT MAX(ID) FROM t_PET");
		dao.execute(sql);
		assertEquals(1, sql.getInt());

		sql = Sqls.fetchInt("CALL 15*25");
		dao.execute(sql);
		assertEquals(15 * 25, sql.getInt());
	}

	// Mysql支持存储过程
	@Test
	public void test_simple_mysql_exec() {
		if (!dao.meta().isMySql())
			return; // Only test for mysql now
		dao.create(Pet.class, true);
		dao.insert(Pet.create("wendal"));
		dao.execute(Sqls.create("DROP PROCEDURE IF EXISTS proc_pet_getCount"));

		dao.execute(Sqls.create("CREATE PROCEDURE proc_pet_getCount()\nBEGIN\n\tSELECT name FROM t_pet;\nEND"));
		Sql sql = Sqls.fetchString("CALL proc_pet_getCount()"); // 单一结果集,且没有输入输出参数
		dao.execute(sql);
		assertEquals("wendal", sql.getString());
	}

	// Mysql支持存储过程
	@Test
	public void test_simple_mysql_exec2() {
		if (!dao.meta().isMySql())
			return; // Only test for mysql now
		dao.create(Pet.class, true);
		dao.insert(Pet.create("wendal"));
		dao.execute(Sqls.create("DROP PROCEDURE IF EXISTS proc_pet_fetch"));

		dao.execute(Sqls.create("CREATE PROCEDURE proc_pet_fetch(IN nm varchar(1024))\nBEGIN\n\tSELECT * FROM t_pet where name=nm;\nEND"));
		Sql sql = Sqls.fetchEntity("CALL proc_pet_fetch(@nm)");
		sql.setEntity(dao.getEntity(Pet.class));
		sql.params().set("nm", "wendal");
		dao.execute(sql);
		
		Pet pet = sql.getObject(Pet.class);
		assertNotNull(pet);
		assertEquals("wendal", pet.getName());
	}

    @Test
    public void test_exec_out() {
        if (!dao.meta().isMySql())
            return; // Only test for mysql now
        dao.create(Pet.class, true);
        dao.insert(Pet.create("wendal"));
        Pet pet = dao.fetch(Pet.class, "wendal");
        dao.execute(Sqls.create("DROP PROCEDURE IF EXISTS proc_pet_fetch"));

        dao.execute(Sqls.create("CREATE PROCEDURE proc_pet_fetch(IN nm varchar(1024), OUT outId int)\nBEGIN\n\tselect id into outId from t_pet where name=nm;\nEND"));

        // 像普通自定义SQL那样创建SQL对象.
        Sql sql = Sqls.create("CALL proc_pet_fetch(@nm, @OUTid)");
        sql.setEntity(dao.getEntity(Pet.class));
        sql.params().set("nm", "wendal"); // 设置入参
        sql.params().set("OUTid", Types.INTEGER);// 设置出参类型,注意,必须加OUT开头
        dao.execute(sql);

        Record re = sql.getOutParams();
        assertNotNull(re);
        assertEquals(pet.getId(), re.get("id"));
    }
}