Dalesbred QueryBuilder enhancement

gistfile1.java

/*
 * Copyright (c) 2012 Evident Solutions Oy
 *
 * Permission is hereby granted, free of charge, to any person obtaining a copy
 * of this software and associated documentation files (the "Software"), to deal
 * in the Software without restriction, including without limitation the rights
 * to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
 * copies of the Software, and to permit persons to whom the Software is
 * furnished to do so, subject to the following conditions:
 *
 * The above copyright notice and this permission notice shall be included in
 * all copies or substantial portions of the Software.
 *
 * THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
 * IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
 * FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
 * AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
 * LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
 * OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN
 * THE SOFTWARE.
 */

package fi.evident.dalesbred.query;

import fi.evident.dalesbred.SqlQuery;
import org.hamcrest.CoreMatchers;
import org.junit.Before;
import org.junit.Test;

import static org.hamcrest.CoreMatchers.is;
import static org.hamcrest.CoreMatchers.not;
import static org.junit.Assert.assertThat;
import static fi.evident.dalesbred.query.Operator.*;

/**
 * Created with IntelliJ IDEA.
 * User: Blackrush
 * Date: 22/11/12
 * Time: 18:57
 */
public class QueryBuilderTest {
    private QueryBuilder qb;

    @Before
    public void setUp() {
        qb = new QueryBuilder();
    }

    @Test
    public void simpleQuery() {
        SqlQuery query = qb.select("42").createQuery();
        assertThat(query, is(SqlQuery.query("select 42")));
    }

    @Test
    public void simpleQueryWithPlaceholders() {
        SqlQuery query = qb.select("?", "?").createQuery(42, "foo");
        assertThat(query, is(SqlQuery.query("select ?, ?", 42, "foo")));
    }

    @Test
    public void queryWithPlaceholders() {
        SqlQuery query = qb
                .select("*")
                .from("document")
                .where("id", Operator.EQ)
                .or("status", Operator.EQ)
                .createQuery(4, "rejected");

        assertThat(query, is(SqlQuery.query("select * from document where id=? or status=?", 4, "rejected")));

        SqlQuery query2 = qb
                .from("document")
                .where("id", EQ)
                .or("status", EQ)
                .createQuery(4, "rejected");

        assertThat(query, is(query2));
    }

    @Test
    public void selectQueryWithManyFields() {
        SqlQuery query = qb
                .select("id", "status", "name")
                .from("document")
                .createQuery();

        assertThat(query, is(SqlQuery.query("select id, status, name from document")));
    }

    @Test
    public void listPlaceholders() {
        SqlQuery query = qb
                .from("document")
                .where("id", IN, Placeholder.list(4))
                .createQuery(1, 2, 3, 4);

        assertThat(query, is(SqlQuery.query("select * from document where id in (?,?,?,?)", 1, 2, 3, 4)));
    }

    @Test
    public void confidential() {
        SqlQuery query = qb
                .from("user")
                .where("password", EQ)
                .createQuery(SqlQuery.confidential("secret"));

        assertThat(query, not(SqlQuery.query("select * from user where password=?", "secret")));
        assertThat(query.toString(), is("select * from user where password=? [****]"));
    }

    @Test
    public void insertQuery() {
        SqlQuery query = qb
                .insert("document")
                .set("id", "status")
                .createQuery(4, "rejected");

        assertThat(query, is(SqlQuery.query("insert into document (id, status) values(?,?)", 4, "rejected")));
    }

    @Test
    public void updateQuery() {
        SqlQuery query = qb
                .update("document")
                .set("status")
                .where("id", EQ)
                .createQuery("corrected", 4);

        assertThat(query, is(SqlQuery.query("update document set status=? where id=?", "corrected", 4)));
    }

    @Test
    public void deleteQuery() {
        SqlQuery query = qb
                .delete().from("document")
                .where("status", EQ)
                .createQuery("corrected");

        assertThat(query, is(SqlQuery.query("delete from document where status=?", "corrected")));
    }

    @Test
    public void orderByQuery() {
        SqlQuery query = qb
                .from("document")
                .where("status", EQ)
                .orderBy("priority", Order.DESC)
                .createQuery("pending");

        assertThat(query, is(SqlQuery.query("select * from document where status=? order by priority desc", "pending")));
    }
}