SqlLiteralTest.java

package org.nutz.dao.impl.sql;

import static org.junit.Assert.assertArrayEquals;
import static org.junit.Assert.assertEquals;
import static org.junit.Assert.assertTrue;

import java.util.Arrays;
import java.util.HashMap;
import java.util.Iterator;
import java.util.Map;

import org.junit.Test;
import org.nutz.dao.test.meta.Pet;

public class SqlLiteralTest {

    private static NutSql L(String s) {
        return new NutSql(s);
    }

    @Test
    public void test_chinese_var_name() {
        NutSql sql = L("INSERT INTO t_chin(名称,描述) VALUES($名,$述)");
        assertEquals(2, sql.literal.getVarIndexes().size());
        Iterator<String> nms = sql.literal.getVarIndexes().names().iterator();
        assertEquals("名", nms.next());
        assertEquals("述", nms.next());

        String expect = "INSERT INTO t_chin(名称,描述) VALUES(,)";
        assertEquals(expect, sql.toPreparedStatement());
        sql.vars().set("名", "老张");
        sql.vars().set("述", "很棒");
        expect = "INSERT INTO t_chin(名称,描述) VALUES(老张,很棒)";
        assertEquals(expect, sql.toString());
    }

    @Test
    public void test_chinese_param_name() {
        NutSql sql = L("INSERT INTO t_chin(名称,描述) VALUES(@名,@述)");
        assertEquals(2, sql.literal.getParamIndexes().size());
        Iterator<String> nms = sql.literal.getParamIndexes().names().iterator();
        assertEquals("名", nms.next());
        assertEquals("述", nms.next());

        String expect = "INSERT INTO t_chin(名称,描述) VALUES(?,?)";
        assertEquals(expect, sql.toPreparedStatement());
        sql.params().set("名", "老张");
        sql.params().set("述", "很棒");
        expect = "INSERT INTO t_chin(名称,描述) VALUES('老张','很棒')";
        assertEquals(expect, sql.toString());
    }

    @Test
    public void test_name_with_underline() {
        NutSql sql = L("@a_1:$a_1");
        sql.params().set("a_1", "A");
        sql.vars().set("a_1", "B");
        assertEquals("'A':B", sql.toString());
        assertEquals("?:B", sql.toPreparedStatement());
    }

    @Test
    public void test_simple() {
        NutSql sql = L("A$a B@a C@b D$condition");
        sql.vars().set("a", "T");
        sql.params().set("a", 23);
        sql.params().set("b", false);
        assertEquals("AT B? C? D", sql.toPreparedStatement());
        assertEquals("AT B23 Cfalse D", sql.toString());
    }

    @Test
    public void test_holder_var_escaping() {
        NutSql sql = L("@@@@$$T$%$a@a;");
        sql.vars().set("a", "V");
        sql.params().set("a", "H");
        assertEquals("@@$T$%V?;", sql.toPreparedStatement());
        assertEquals("@@$T$%V'H';", sql.toString());
    }

    @Test
    public void test_sql_types() {
        assertTrue(L("InSeRT INTO $T ($id,$name) VALUES(@id,@name)").literal.isINSERT());
        assertTrue(L("UPDaTE $T SET $id=@id").literal.isUPDATE());
        assertTrue(L("sELECT * FROM $T").literal.isSELECT());
        assertTrue(L("DeLETE FROM $T").literal.isDELETE());
        assertTrue(L("Drop table $T").literal.isDROP());
        assertTrue(L("crEATE table abc(id INT)").literal.isCREATE());
    }

    @Test
    public void test_var_set_index() {
        NutSql sql = L("$A,$B,@C,@D,@C");
        int[] is = sql.literal.getParamIndexes().getOrderIndex("C");
        assertEquals(0, is[0]);
        assertEquals(2, is[1]);
        is = sql.literal.getParamIndexes().getOrderIndex("D");
        assertEquals(1, is[0]);
    }

    @Test
    public void test_toPreparedStatement() {
        NutSql sql = L("=@a=@b");
        String exp = "=?=?";
        String actural = sql.toPreparedStatement();
        assertEquals(exp, actural);

    }

    @Test
    public void test_toPreparedStatement_regularly() {
        NutSql sql = L("UPDATE dao_platoon SET name=@name,base=@baseName,leader=@leaderName WHERE id=@id");
        String exp = "UPDATE dao_platoon SET name=?,base=?,leader=? WHERE id=?";
        String actural = sql.toPreparedStatement();
        assertEquals(exp, actural);
    }

    @Test
    public void test_dot_with_var() {
        NutSql sql = L("$x.y");
        sql.vars().set("x", "T");
        assertEquals("T.y", sql.toString());
    }

    @Test
    public void test_dot_with_param() {
        NutSql sql = L("@x.y");
        sql.params().set("x", "T");
        assertEquals("'T'.y", sql.toString());
    }

    @Test
    public void test_param_names() {
        NutSql sql = L("UPDATE dao_platoon SET name=@name1,base=@baseName2,leader=@leaderName3 WHERE id=@id4");
        String[] paramNames = sql.literal.getParamIndexes()
                                            .names()
                                            .toArray(new String[sql.literal.getParamIndexes()
                                                                            .names()
                                                                            .size()]);
        String result[] = {"leaderName3", "id4", "baseName2", "name1"};
        Arrays.sort(paramNames);
        Arrays.sort(result);
        assertArrayEquals(paramNames, result);
    }

    @Test
    public void test_var_names() {
        NutSql sql = L("InSeRT INTO $T ($id,$name) VALUES(@id1,@name2)");
        String[] varNames = sql.literal.getVarIndexes()
                                        .names()
                                        .toArray(new String[sql.literal.getVarIndexes().size()]);
        String result[] = {"T", "name", "id"};
        // System.out.println(Json.toJson(varNames));

        Arrays.sort(varNames);
        Arrays.sort(result);
        assertArrayEquals(varNames, result);
    }
    
    @Test
    public void test_param_names_putall_map() {
        NutSql sql = L("INSERT INTO t_pet($id,$name,$alias,$age) VALUES(@id,@name,@nickName,@age)");
        
        Map<String, Object> map = new HashMap<String, Object>();
        map.put("id", "userId");
        map.put("name", "userName");
        map.put("alias", "alias");
        map.put("age", "age");
        
        Pet pet = new Pet();
        pet.setId(18);
        pet.setName("pet");
        pet.setNickName("haha");
        pet.setAge(5);
        
        sql.vars().putAll(map);
        sql.params().putAll(pet);

        String expect = "INSERT INTO t_pet(userId,userName,alias,age) VALUES(18,'pet','haha',5)";
        assertEquals(expect, sql.toString());
    }
}