All Downloads are FREE. Search and download functionalities are using the official Maven repository.
  • Log In
  • Register

    • net.dongliu.dbutils.builder.SQLBuilder Maven / Gradle / Ivy

    package net.dongliu.dbutils.builder;

import net.dongliu.commons.collection.Lists;
import net.dongliu.commons.collection.Pair;
import net.dongliu.dbutils.exception.SQLBuilderException;
import net.dongliu.dbutils.mapping.BeanMapping;
import net.dongliu.dbutils.mapping.BeanMappingUtils;

import javax.annotation.Nonnull;
import javax.annotation.Nullable;
import java.util.*;
import java.util.stream.Stream;

import static java.util.stream.Collectors.*;

/**
 * Sql Builder
 *
 * @author Liu Dong
 */
public class SQLBuilder extends SQLNode {

    private final List list = new ArrayList<>();

    public SQLBuilder() {
    }

    public SQLBuilder(String str) {
        append(str);
    }

    /**
     * Build to get sql
     */
    public SQLHolder build() {
        SQLSegment segment = segment();
        String sql = segment.getClauses().map(String::trim).filter(it -> !it.isEmpty())
                .collect(joining(" "));
        Object[] params = segment.getParams().toArray();
        return new SQLHolder(sql, params);
    }

    /**
     * Append new sql segment. Will insert white space if need
     */
    public SQLBuilder append(String sqlSegment) {
        if (sqlSegment.isEmpty()) {
            return this;
        }
        addNode(new ClauseNode(sqlSegment));
        return this;
    }

    private void addNode(SQLNode node) {
        list.add(node);
    }

    /**
     * Append new value.
     */
    public SQLBuilder param(@Nullable Object value) {
        addNode(new ParamNode(value));
        return this;
    }

    /**
     * Append multi param, separated by ',' in sql clause
     */
    public SQLBuilder param(Object... values) {
        return param(Lists.of(values));
    }

    /**
     * Append multi param, separated by ',' in sql clause
     */
    public SQLBuilder param(Collection values) {
        addNode(new ParamsNode(values));
        return this;
    }

    /**
     * Select
     */
    public SQLBuilder select(String name) {
        return append("SELECT " + name);
    }

    /**
     * Select
     */
    public SQLBuilder select(String... names) {
        return append("SELECT " + String.join(", ", (CharSequence[]) names));
    }

    /**
     * Select
     */
    public SQLBuilder select(Collection names) {
        return append("SELECT " + String.join(", ", names));
    }

    /**
     * From
     */
    public SQLBuilder from(String table) {
        return append("FROM " + table);
    }

    public SQLBuilder update(String table) {
        return append("UPDATE " + table);
    }

    /**
     * insert into
     */
    public SQLBuilder insertInto(String table, CharSequence... columns) {
        return append("INSERT INTO " + table + "(" + String.join(", ", columns) + ")");
    }

    /**
     * insert into
     */
    public SQLBuilder insertInto(String table, Collection columns) {
        return append("INSERT INTO " + table + "(" + String.join(", ", columns) + ")");
    }

    /**
     * Used with insert Into, set values
     */
    public SQLBuilder values(Object... values) {
        return values(Lists.of(values));
    }

    /**
     * Used with insert Into, set value
     */
    public SQLBuilder values(List values) {
        return append("VALUES(").param(values).append(")");
    }

    /**
     * Combine insert into and values
     */
    public SQLBuilder insertValues(String table, Map values) {
        return insertValues(table, values.entrySet());
    }

    /**
     * Combine insert into and values
     */
    public SQLBuilder insertValues(String table, Collection> values) {
        List columns = values.stream().map(v -> v.getKey()).collect(toList());
        List vs = values.stream().map(v -> v.getValue()).collect(toList());
        return insertInto(table, columns).values(vs);
    }

    public SQLBuilder deleteFrom(String table) {
        return append("DELETE FROM " + table);
    }

    /**
     * Note:limit not work for all database
     */
    public SQLBuilder limit(int offset, int len) {
        return append("LIMIT").param(offset).append(", ").param(len);
    }

    /**
     * Note:limit not work for all database
     */
    public SQLBuilder limit(int len) {
        return append("LIMIT").param(len);
    }

    public SQLBuilder offset(int offset) {
        return append("OFFSET").param(offset);
    }

    public SQLBuilder groupBy(String column) {
        return append("GROUP BY " + column);
    }

    public SQLBuilder orderBy(String column) {
        return append("ORDER BY " + column);
    }

    public SQLBuilder groupBy(CharSequence... columns) {
        return append("GROUP BY " + String.join(", ", columns));
    }

    public SQLBuilder orderBy(CharSequence... columns) {
        return append("ORDER BY " + String.join(", ", columns));
    }


    /**
     * in clause
     */
    public SQLBuilder in(Object... values) {
        return in(Lists.of(values));
    }

    /**
     * Sql in clause
     */
    public SQLBuilder in(Collection values) {
        return append("IN (").param(values).append(")");
    }

    /**
     * Build dynamic where
     */
    public SQLBuilder whereAllEquals(Map equalConditions) {
        return whereAllEquals(equalConditions.entrySet());
    }

    /**
     * Build dynamic where
     */
    public SQLBuilder whereAllEquals(Collection> equalConditions) {
        if (equalConditions.isEmpty()) {
            return this;
        }
        append("WHERE");
        int index = 0;
        for (Map.Entry equalCondition : equalConditions) {
            append(equalCondition.getKey() + " = ").param(equalCondition.getValue());
            if (index++ < equalConditions.size() - 1) {
                append("AND");
            }
        }
        return this;
    }

    /**
     * Build dynamic set
     */
    public SQLBuilder setAll(Map setValues) {
        return setAll(setValues.entrySet());
    }

    /**
     * Build dynamic set
     */
    public SQLBuilder setAll(Collection> setValues) {
        if (setValues.isEmpty()) {
            throw new SQLBuilderException("No set values");
        }
        append("SET");
        int index = 0;
        for (Map.Entry equalCondition : setValues) {
            append(equalCondition.getKey() + " = ").param(equalCondition.getValue());
            if (index++ < setValues.size() - 1) {
                append(",");
            }
        }
        return this;
    }

    /**
     * Generate select columns from bean class
     */
    public SQLBuilder selectBeanColumns(Class cls) {
        BeanMapping beanMapping = BeanMappingUtils.getBeanMapping(cls);
        Collection columns = beanMapping.columnNames();
        return select(columns);
    }


    /**
     * Generate insert clause via bean class
     */
    public SQLBuilder insertBean(String table, Object bean) {
        List> values = BeanMappingUtils.beanToEntries(bean);
        return insertValues(table, values);
    }

    /**
     * Include a sql segment
     */
    public SQLBuilder include(SQLHolder sqlHolder) {
        return this;
    }

//    /**
//     * Build set sql for bean.
//     */
//    public SQLBuilder setBean(Object bean) {
//        List> values = BeanMappingUtils.beanToEntries(bean);
//        setAll(values);
//        return this;
//    }

    /**
     * Build dynamic where
     */
    @SafeVarargs
    public final SQLBuilder whereAllEquals(Map.Entry... equalConditions) {
        return whereAllEquals(Arrays.asList(equalConditions));
    }

    /**
     * Build dynamic set
     */
    @SafeVarargs
    public final SQLBuilder setAll(Map.Entry... setValues) {
        return setAll(Arrays.asList(setValues));
    }

    /**
     * Combine insert into and values
     */
    @SafeVarargs
    public final SQLBuilder insertValues(String table, Map.Entry... values) {
        return insertValues(table, Arrays.asList(values));
    }

    @Nonnull
    @Override
    protected SQLSegment segment() {
        List segments = Lists.map(list, SQLNode::segment);
        Stream clauses = segments.stream().flatMap(SQLSegment::getClauses);
        Stream params = segments.stream().flatMap(SQLSegment::getParams);
        return new SQLSegment(clauses, params);
    }
}
    
    
    
    

    




    
    
    © 2015 - 2025 Weber Informatics LLC | Privacy Policy