diff --git a/README.md b/README.md index c4ccd98..eb3b120 100644 --- a/README.md +++ b/README.md @@ -1,3 +1,4 @@ + # uid.database pom.xml: @@ -14,5 +15,5 @@ ua.net.uid uid.database - 1.0.2 + 1.0.0 diff --git a/src/main/java/ua/net/uid/utils/db/dao/DAO.java b/src/main/java/ua/net/uid/utils/db/dao/DAO.java new file mode 100644 index 0000000..d938102 --- /dev/null +++ b/src/main/java/ua/net/uid/utils/db/dao/DAO.java @@ -0,0 +1,22 @@ +/* + * Copyright 2020 nightfall. + * + * Licensed under the Apache License, Version 2.0 (the "License"); + * you may not use this file except in compliance with the License. + * You may obtain a copy of the License at + * + * http://www.apache.org/licenses/LICENSE-2.0 + * + * Unless required by applicable law or agreed to in writing, software + * distributed under the License is distributed on an "AS IS" BASIS, + * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. + * See the License for the specific language governing permissions and + * limitations under the License. + */ +package ua.net.uid.utils.db.dao; + +import ua.net.uid.utils.db.Session; + +public interface DAO { + Session getSession(); +} diff --git a/src/main/java/ua/net/uid/utils/db/dao/DAOAbstract.java b/src/main/java/ua/net/uid/utils/db/dao/DAOAbstract.java new file mode 100644 index 0000000..e171851 --- /dev/null +++ b/src/main/java/ua/net/uid/utils/db/dao/DAOAbstract.java @@ -0,0 +1,26 @@ +/* + * Copyright 2020 nightfall. + * + * Licensed under the Apache License, Version 2.0 (the "License"); + * you may not use this file except in compliance with the License. + * You may obtain a copy of the License at + * + * http://www.apache.org/licenses/LICENSE-2.0 + * + * Unless required by applicable law or agreed to in writing, software + * distributed under the License is distributed on an "AS IS" BASIS, + * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. + * See the License for the specific language governing permissions and + * limitations under the License. + */ +package ua.net.uid.utils.db.dao; + +import ua.net.uid.utils.db.Session; + +import java.io.Serializable; + +public abstract class DAOAbstract, PK extends Serializable> extends DAOCore implements DAOBase { + public DAOAbstract(Session session) { + super(session); + } +} diff --git a/src/main/java/ua/net/uid/utils/db/dao/DAOBase.java b/src/main/java/ua/net/uid/utils/db/dao/DAOBase.java new file mode 100644 index 0000000..9ec7f02 --- /dev/null +++ b/src/main/java/ua/net/uid/utils/db/dao/DAOBase.java @@ -0,0 +1,173 @@ +/* + * Copyright 2020 nightfall. + * + * Licensed under the Apache License, Version 2.0 (the "License"); + * you may not use this file except in compliance with the License. + * You may obtain a copy of the License at + * + * http://www.apache.org/licenses/LICENSE-2.0 + * + * Unless required by applicable law or agreed to in writing, software + * distributed under the License is distributed on an "AS IS" BASIS, + * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. + * See the License for the specific language governing permissions and + * limitations under the License. + */ +package ua.net.uid.utils.db.dao; + +import ua.net.uid.utils.db.Fetcher; +import ua.net.uid.utils.db.Outcome; +import ua.net.uid.utils.db.query.Condition; +import ua.net.uid.utils.db.query.Order; +import ua.net.uid.utils.db.query.QueryBuilder; + +import java.io.Serializable; +import java.sql.SQLException; +import java.util.List; +import ua.net.uid.utils.db.Processor; + +public interface DAOBase, PK extends Serializable> extends DAO { + String getTableName(); + + Condition getPrimaryCondition(PK key); + + default Order getDefaultOrder() { + return null; + } + + Fetcher getFetcher(); + + default T get(PK key) throws SQLException { + return getBy(getPrimaryCondition(key)); + } + + default T getBy(Condition condition) throws SQLException { + return new QueryBuilder() + .append("SELECT * FROM ").append(getTableName()) + .append(" WHERE ", condition) + .append(" LIMIT 1") + .on(getSession()).scalar(getFetcher()); + } + + default long countAll() throws SQLException { + return getSession().query("SELECT COUNT(*) FROM " + getTableName()).select(Outcome.FIRST_LONG); + } + + default List findAll() throws SQLException { + return findAll(getDefaultOrder()); + } + + default void foreachAll(Processor.Callback callback) throws SQLException { + foreachAll(callback, getDefaultOrder()); + } + + default List findAll(Order order) throws SQLException { + return new QueryBuilder().append("SELECT * FROM ").append(getTableName()) + .append(order).on(getSession()).list(getFetcher()); + } + + default void foreachAll(Processor.Callback callback, Order order) throws SQLException { + new QueryBuilder() + .append("SELECT * FROM ").append(getTableName()) + .append(order) + .on(getSession()).foreach(getFetcher(), callback); + } + + default List findAll(long limit, long offset) throws SQLException { + return findAll(getDefaultOrder(), limit, offset); + } + + default void foreachAll(Processor.Callback callback, long limit, long offset) throws SQLException { + foreachAll(callback, getDefaultOrder(), limit, offset); + } + + default List findAll(Order order, long limit, long offset) throws SQLException { + return new QueryBuilder().append("SELECT * FROM ").append(getTableName()) + .append(order).on(getSession()).list(getFetcher()); + } + + default void foreachAll(Processor.Callback callback, Order order, long limit, long offset) throws SQLException { + new QueryBuilder() + .append("SELECT * FROM ").append(getTableName()) + .append(order) + .append(" LIMIT ? OFFSET ?", limit, offset) + .on(getSession()).foreach(getFetcher(), callback); + } + + default long countBy(Condition condition) throws SQLException { + return new QueryBuilder() + .append("SELECT COUNT(*) FROM ") + .append(getTableName()) + .append(" WHERE ", condition) + .on(getSession()).select(Outcome.FIRST_LONG); + } + + default List findBy(Condition condition) throws SQLException { + return findBy(condition, getDefaultOrder()); + } + + default void foreachBy(Processor.Callback callback, Condition condition) throws SQLException { + foreachBy(callback, condition, getDefaultOrder()); + } + + default List findBy(Condition condition, Order order) throws SQLException { + return new QueryBuilder() + .append("SELECT * FROM ").append(getTableName()) + .append(" WHERE ", condition) + .append(order) + .on(getSession()).list(getFetcher()); + } + + default void foreachBy(Processor.Callback callback, Condition condition, Order order) throws SQLException { + new QueryBuilder() + .append("SELECT * FROM ").append(getTableName()) + .append(" WHERE ", condition) + .append(order) + .on(getSession()).foreach(getFetcher(), callback); + } + + default List findBy(Condition condition, long limit, long offset) throws SQLException { + return findBy(condition, getDefaultOrder(), limit, offset); + } + + default void foreachBy(Processor.Callback callback, Condition condition, long limit, long offset) throws SQLException { + foreachBy(callback, condition, getDefaultOrder(), limit, offset); + } + + default List findBy(Condition condition, Order order, long limit, long offset) throws SQLException { + return new QueryBuilder() + .append("SELECT * FROM ").append(getTableName()) + .append(" WHERE ", condition) + .append(" LIMIT ? OFFSET ?", limit, offset) + .append(order) + .on(getSession()).list(getFetcher()); + } + + default void foreachBy(Processor.Callback callback, Condition condition, Order order, long limit, long offset) throws SQLException { + new QueryBuilder() + .append("SELECT * FROM ").append(getTableName()) + .append(" WHERE ", condition) + .append(" LIMIT ? OFFSET ?", limit, offset) + .append(order) + .on(getSession()).foreach(getFetcher(), callback); + } + + boolean insert(T item) throws SQLException; + + boolean update(T item, PK key) throws SQLException; + + default boolean update(T item) throws SQLException { + return update(item, item.getPrimaryKey()); + } + + default boolean delete(PK key) throws SQLException { + return new QueryBuilder() + .append("DELETE FROM ").append(getTableName()) + .append(" WHERE ", getPrimaryCondition(key)) + .on(getSession()).update() > 0; + } + + default boolean delete(T item) throws SQLException { + return delete(item.getPrimaryKey()); + } +} diff --git a/src/main/java/ua/net/uid/utils/db/dao/DAOCore.java b/src/main/java/ua/net/uid/utils/db/dao/DAOCore.java new file mode 100644 index 0000000..90e84b7 --- /dev/null +++ b/src/main/java/ua/net/uid/utils/db/dao/DAOCore.java @@ -0,0 +1,31 @@ +/* + * Copyright 2020 nightfall. + * + * Licensed under the Apache License, Version 2.0 (the "License"); + * you may not use this file except in compliance with the License. + * You may obtain a copy of the License at + * + * http://www.apache.org/licenses/LICENSE-2.0 + * + * Unless required by applicable law or agreed to in writing, software + * distributed under the License is distributed on an "AS IS" BASIS, + * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. + * See the License for the specific language governing permissions and + * limitations under the License. + */ +package ua.net.uid.utils.db.dao; + +import ua.net.uid.utils.db.Session; + +public abstract class DAOCore implements DAO { + private final Session session; + + public DAOCore(Session session) { + this.session = session; + } + + @Override + public Session getSession() { + return session; + } +} diff --git a/src/main/java/ua/net/uid/utils/db/dao/Entity.java b/src/main/java/ua/net/uid/utils/db/dao/Entity.java new file mode 100644 index 0000000..c1eec12 --- /dev/null +++ b/src/main/java/ua/net/uid/utils/db/dao/Entity.java @@ -0,0 +1,22 @@ +/* + * Copyright 2020 nightfall. + * + * Licensed under the Apache License, Version 2.0 (the "License"); + * you may not use this file except in compliance with the License. + * You may obtain a copy of the License at + * + * http://www.apache.org/licenses/LICENSE-2.0 + * + * Unless required by applicable law or agreed to in writing, software + * distributed under the License is distributed on an "AS IS" BASIS, + * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. + * See the License for the specific language governing permissions and + * limitations under the License. + */ +package ua.net.uid.utils.db.dao; + +import java.io.Serializable; + +public interface Entity { + PK getPrimaryKey(); +} diff --git a/src/main/java/ua/net/uid/utils/db/query/Condition.java b/src/main/java/ua/net/uid/utils/db/query/Condition.java new file mode 100644 index 0000000..8fec961 --- /dev/null +++ b/src/main/java/ua/net/uid/utils/db/query/Condition.java @@ -0,0 +1,209 @@ +/* + * Copyright 2020 nightfall. + * + * Licensed under the Apache License, Version 2.0 (the "License"); + * you may not use this file except in compliance with the License. + * You may obtain a copy of the License at + * + * http://www.apache.org/licenses/LICENSE-2.0 + * + * Unless required by applicable law or agreed to in writing, software + * distributed under the License is distributed on an "AS IS" BASIS, + * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. + * See the License for the specific language governing permissions and + * limitations under the License. + */ +package ua.net.uid.utils.db.query; + +import java.util.ArrayList; +import java.util.Collection; +import java.util.Collections; +import java.util.List; + +public abstract class Condition implements QueryPart { + public static Condition raw(CharSequence condition, Object... params) { + if (condition == null) + throw new IllegalArgumentException("condition is null"); + return new Raw(condition, params); + } + + public static Condition not(CharSequence condition, Object... params) { + return new Not(raw(condition, params)); + } + + public static Condition not(Condition condition) { + if (condition == null) { + return null; + } else if (condition instanceof Not) { + return ((Not) condition).condition; + } else { + return new Not(condition); + } + } + + public static Condition and(Condition left, Condition right) { + if (left != null) { + return right == null ? left : new And(left, right); + } else { + return right; + } + } + + public static Condition and(Condition... conditions) { + if (conditions == null || conditions.length == 0) return null; + return conditions.length == 1 ? conditions[0] : new And(conditions); + } + + public static Condition or(Condition... conditions) { + if (conditions == null || conditions.length == 0) return null; + return conditions.length == 1 ? conditions[0] : new Or(conditions); + } + + public static Condition in(CharSequence left, Collection items) { + if (left == null || left.length() == 0) + throw new IllegalArgumentException("left side parameter for 'in' condition is null"); + if (items == null || items.isEmpty()) return null; + return new In(left, items.toArray(new Object[items.size()])); + } + + public static Condition in(CharSequence left, Object... items) { + if (left == null || left.length() == 0) + throw new IllegalArgumentException("left side parameter for 'in' condition is null"); + if (items == null || items.length == 0) return null; + return new In(left, items); + } + + public Object[] toParams(Object... post) { + ArrayList params = new ArrayList<>(); + bind(params); + if (post != null) Collections.addAll(params, post); + return params.toArray(); + } + + @Override + public String toString() { + StringBuilder builder = new StringBuilder(); + build(builder); + return builder.toString(); + } + + static final class Raw extends Condition { + private final CharSequence condition; + private final Object[] params; + + Raw(CharSequence condition, Object[] params) { + this.condition = condition; + this.params = params; + } + + @Override + public void build(StringBuilder builder) { + builder.append(condition); + } + + @Override + public void bind(List params) { + Collections.addAll(params, this.params); + } + } + + static final class Not extends Condition { + private final Condition condition; + + Not(Condition condition) { + this.condition = condition; + } + + @Override + public void build(StringBuilder builder) { + builder.append("NOT("); + condition.build(builder); + builder.append(')'); + } + + @Override + public void bind(List params) { + condition.bind(params); + } + } + + static abstract class Block extends Condition { + final List conditions; + + Block(Condition... conditions) { + this.conditions = new ArrayList<>(conditions.length); + for (Condition condition : conditions) + if (condition != null) { + if (condition.getClass() == getClass()) { + this.conditions.addAll(((Block) condition).conditions); + } else { + this.conditions.add(condition); + } + } + } + + @Override + public final void bind(List params) { + for (Condition condition : conditions) + condition.bind(params); + } + + final void build(StringBuilder builder, String div) { + if (conditions.size() > 1) { + builder.append("("); + conditions.get(0).build(builder); + for (int i = 1; i < conditions.size(); ++i) { + builder.append(div); + conditions.get(i).build(builder); + } + builder.append(")"); + } else { + conditions.get(0).build(builder); + } + } + } + + static final class And extends Block { + And(Condition... conditions) { + super(conditions); + } + + @Override + public void build(StringBuilder builder) { + build(builder, ") AND ("); + } + } + + static final class Or extends Block { + Or(Condition... conditions) { + super(conditions); + } + + @Override + public void build(StringBuilder builder) { + build(builder, ") OR ("); + } + } + + static final class In extends Condition { + private final CharSequence left; + private final Object[] items; + + public In(CharSequence left, Object[] items) { + this.left = left; + this.items = items; + } + + @Override + public void build(StringBuilder builder) { + builder.append(left).append(" IN (?"); + for (int i = 1; i < items.length; ++i) builder.append(",?"); + builder.append(')'); + } + + @Override + public void bind(List params) { + Collections.addAll(params, items); + } + } +} diff --git a/src/main/java/ua/net/uid/utils/db/query/Order.java b/src/main/java/ua/net/uid/utils/db/query/Order.java new file mode 100644 index 0000000..19bd5c6 --- /dev/null +++ b/src/main/java/ua/net/uid/utils/db/query/Order.java @@ -0,0 +1,72 @@ +/* + * Copyright 2020 nightfall. + * + * Licensed under the Apache License, Version 2.0 (the "License"); + * you may not use this file except in compliance with the License. + * You may obtain a copy of the License at + * + * http://www.apache.org/licenses/LICENSE-2.0 + * + * Unless required by applicable law or agreed to in writing, software + * distributed under the License is distributed on an "AS IS" BASIS, + * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. + * See the License for the specific language governing permissions and + * limitations under the License. + */ +package ua.net.uid.utils.db.query; + +import java.util.ArrayList; +import java.util.Collections; +import java.util.List; + +public class Order implements QueryPart { + private final StringBuilder expressions = new StringBuilder(); + private final ArrayList params = new ArrayList<>(1); + + private Order() { + } + + public static Order by() { + return new Order(); + } + + public Order asc(CharSequence expression, Object... args) { + return append(expression, " ASC", args); + } + + public Order desc(CharSequence expression, Object... args) { + return append(expression, " DESC", args); + } + + public Order ascNullFirst(CharSequence expression, Object... args) { + return append(expression, " ASC NULLS FIRST", args); + } + + public Order ascNullLast(CharSequence expression, Object... args) { + return append(expression, " ASC NULLS LAST", args); + } + + public Order descNullFirst(CharSequence expression, Object... args) { + return append(expression, " DESC NULLS FIRST", args); + } + + public Order descNullLast(CharSequence expression, Object... args) { + return append(expression, " DESC NULLS LAST", args); + } + + private Order append(CharSequence expression, String suffix, Object[] args) { + expressions.append(expressions.length() == 0 ? " ORDER BY " : ", ").append(expression).append(suffix); + if (args != null) Collections.addAll(params, args); + return this; + } + + @Override + public void build(StringBuilder builder) { + builder.append(expressions); + } + + @Override + public void bind(List params) { + params.addAll(this.params); + } +} diff --git a/src/main/java/ua/net/uid/utils/db/query/Query.java b/src/main/java/ua/net/uid/utils/db/query/Query.java new file mode 100644 index 0000000..bc29f5f --- /dev/null +++ b/src/main/java/ua/net/uid/utils/db/query/Query.java @@ -0,0 +1,22 @@ +/* + * Copyright 2020 nightfall. + * + * Licensed under the Apache License, Version 2.0 (the "License"); + * you may not use this file except in compliance with the License. + * You may obtain a copy of the License at + * + * http://www.apache.org/licenses/LICENSE-2.0 + * + * Unless required by applicable law or agreed to in writing, software + * distributed under the License is distributed on an "AS IS" BASIS, + * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. + * See the License for the specific language governing permissions and + * limitations under the License. + */ +package ua.net.uid.utils.db.query; + +public interface Query { + String getQuery(); + + Object[] getParams(); +} diff --git a/src/main/java/ua/net/uid/utils/db/query/QueryBuilder.java b/src/main/java/ua/net/uid/utils/db/query/QueryBuilder.java new file mode 100644 index 0000000..1222a74 --- /dev/null +++ b/src/main/java/ua/net/uid/utils/db/query/QueryBuilder.java @@ -0,0 +1,136 @@ +/* + * Copyright 2020 nightfall. + * + * Licensed under the Apache License, Version 2.0 (the "License"); + * you may not use this file except in compliance with the License. + * You may obtain a copy of the License at + * + * http://www.apache.org/licenses/LICENSE-2.0 + * + * Unless required by applicable law or agreed to in writing, software + * distributed under the License is distributed on an "AS IS" BASIS, + * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. + * See the License for the specific language governing permissions and + * limitations under the License. + */ +package ua.net.uid.utils.db.query; + +import ua.net.uid.utils.db.Processor; +import ua.net.uid.utils.db.Session; + +import java.util.ArrayList; +import java.util.Collections; +import java.util.List; + +public class QueryBuilder implements Query { + private final StringBuilder builder = new StringBuilder(); + private final List params = new ArrayList<>(); + + public QueryBuilder append(QueryPart part) { + if (part != null) { + part.build(builder); + part.bind(params); + } + return this; + } + + public QueryBuilder appendIf(boolean con, QueryPart part) { + if (con && part != null) { + part.build(builder); + part.bind(params); + } + return this; + } + + public QueryBuilder append(CharSequence prefix, QueryPart part) { + if (part != null) { + builder.append(prefix); + part.build(builder); + part.bind(params); + } + return this; + } + + public QueryBuilder appendIf(boolean con, CharSequence prefix, QueryPart part) { + if (con && part != null) { + builder.append(prefix); + part.build(builder); + part.bind(params); + } + return this; + } + + public QueryBuilder append(CharSequence part) { + if (part != null) + builder.append(part); + return this; + } + + public QueryBuilder appendIf(boolean con, CharSequence part) { + if (con && part != null) + builder.append(part); + return this; + } + + public QueryBuilder append(CharSequence part, Object... params) { + if (part == null || part.length() == 0) + throw new IllegalArgumentException("not empty part required"); + builder.append(part); + Collections.addAll(this.params, params); + return this; + } + + public QueryBuilder appendIf(boolean con, CharSequence part, Object... params) { + if (con) { + if (part == null || part.length() == 0) + throw new IllegalArgumentException("not empty part required"); + builder.append(part); + Collections.addAll(this.params, params); + } + return this; + } + + public QueryBuilder append(String part) { + if (part != null) + builder.append(part); + return this; + } + + public QueryBuilder appendIf(boolean con, String part) { + if (con && part != null) + builder.append(part); + return this; + } + + public QueryBuilder append(String part, Object... params) { + if (part == null || part.length() == 0) + throw new IllegalArgumentException("not empty part required"); + builder.append(part); + Collections.addAll(this.params, params); + return this; + } + + public QueryBuilder appendIf(boolean con, String part, Object... params) { + if (con) { + if (part == null || part.length() == 0) + throw new IllegalArgumentException("not empty part required"); + builder.append(part); + Collections.addAll(this.params, params); + } + return this; + } + + public Processor on(Session session) { + return session.query(getQuery(), getParams()); + } + + @Override + public String getQuery() { + return builder.toString(); + } + + @Override + public Object[] getParams() { + return params.toArray(); + } +} diff --git a/src/main/java/ua/net/uid/utils/db/query/QueryPart.java b/src/main/java/ua/net/uid/utils/db/query/QueryPart.java new file mode 100644 index 0000000..53401a9 --- /dev/null +++ b/src/main/java/ua/net/uid/utils/db/query/QueryPart.java @@ -0,0 +1,24 @@ +/* + * Copyright 2020 nightfall. + * + * Licensed under the Apache License, Version 2.0 (the "License"); + * you may not use this file except in compliance with the License. + * You may obtain a copy of the License at + * + * http://www.apache.org/licenses/LICENSE-2.0 + * + * Unless required by applicable law or agreed to in writing, software + * distributed under the License is distributed on an "AS IS" BASIS, + * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. + * See the License for the specific language governing permissions and + * limitations under the License. + */ +package ua.net.uid.utils.db.query; + +import java.util.List; + +public interface QueryPart { + void build(StringBuilder builder); + + void bind(List params); +} diff --git a/src/test/java/ua/net/uid/utils/db/dao/DAOAbstractTest.java b/src/test/java/ua/net/uid/utils/db/dao/DAOAbstractTest.java new file mode 100644 index 0000000..9e64e47 --- /dev/null +++ b/src/test/java/ua/net/uid/utils/db/dao/DAOAbstractTest.java @@ -0,0 +1,282 @@ +package ua.net.uid.utils.db.dao; + +import org.h2.jdbcx.JdbcConnectionPool; +import org.junit.jupiter.api.AfterAll; +import org.junit.jupiter.api.BeforeAll; +import org.junit.jupiter.api.Test; +import ua.net.uid.utils.db.*; +import ua.net.uid.utils.db.query.Condition; +import ua.net.uid.utils.db.query.Order; +import ua.net.uid.utils.db.query.QueryBuilder; + +import java.sql.ResultSet; +import java.sql.SQLException; +import java.util.Date; +import java.util.List; + +import static org.junit.jupiter.api.Assertions.*; + +class DAOAbstractTest { + private static JdbcConnectionPool source; + private static Database database; + + @BeforeAll + static void beforeAll() throws SQLException { + source = JdbcConnectionPool.create(String.format("jdbc:h2:mem:x%d", System.nanoTime()), "sa", ""); + database = new Database(source, true); + database.query( + "CREATE TABLE IF NOT EXISTS test_items (" + + "id INT IDENTITY NOT NULL PRIMARY KEY, " + + "title VARCHAR(30), " + + "value INTEGER NOT NULL," + + "disabled BOOLEAN NOT NULL," + + "modified TIMESTAMP NOT NULL" + + ")" + ).execute(); + + } + + @AfterAll + static void afterAll() { + source.dispose(); + } + + private static Database database() { + return database; + } + + @Test + void insertsAndCheckCount() throws SQLException { + ItemDAO dao = new ItemDAO(database()); + Item item; + + assertTrue(dao.insert(item = new Item("insertsAndCheckCount 1", 1, false))); + assertNotNull(item.getPrimaryKey()); + assertTrue(dao.insert(item = new Item("insertsAndCheckCount 2", 1, false))); + assertNotNull(item.getPrimaryKey()); + assertTrue(dao.insert(item = new Item("insertsAndCheckCount 3", 1, false))); + assertNotNull(item.getPrimaryKey()); + + assertTrue(dao.insert(item = new Item("insertsAndCheckCount 4", 2, false))); + assertNotNull(item.getPrimaryKey()); + assertTrue(dao.insert(item = new Item("insertsAndCheckCount 5", 2, false))); + assertNotNull(item.getPrimaryKey()); + assertTrue(dao.insert(item = new Item("insertsAndCheckCount 6", 3, false))); + assertNotNull(item.getPrimaryKey()); + + assertEquals(3, dao.countBy(Condition.raw("value = ?", 1))); + assertEquals(2, dao.countBy(Condition.raw("value = ?", 2))); + assertEquals(1, dao.countBy(Condition.raw("value = ?", 3))); + } + + @Test + void crudTest() throws SQLException { + ItemDAO dao = new ItemDAO(database()); + Item item1, item2; + assertTrue(dao.insert(item1 = new Item("insertUpdateGetAndDelete", 10, false))); + assertNotNull(item1.getPrimaryKey()); + + item2 = dao.get(item1.getPrimaryKey()); + assertNotNull(item2); + assertEquals(item1.getPrimaryKey(), item2.getPrimaryKey()); + assertEquals(item1.getTitle(), item2.getTitle()); + assertEquals(10, item2.getValue()); + + item1.setValue(11); + item1.setDisabled(true); + assertTrue(dao.update(item1)); + + item2 = dao.get(item1.getPrimaryKey()); + assertEquals(item1.getValue(), item2.getValue()); + assertEquals(item1.isDisabled(), item2.isDisabled()); + + dao.delete(item1); + assertNull(dao.get(item1.getPrimaryKey())); + } + + @Test + void insertAndGetBy() throws SQLException { + ItemDAO dao = new ItemDAO(database()); + Item item1, item2; + + assertTrue(dao.insert(item1 = new Item("insertAndGetBy", 20, false))); + item2 = dao.getBy(Condition.and( + Condition.raw("title = ?", "insertAndGetBy"), + Condition.not("disabled") + )); + assertNotNull(item2); + assertEquals(item1.getTitle(), item2.getTitle()); + assertEquals(item1.isDisabled(), item2.isDisabled()); + } + + @Test + void countAllInsertAndDelete() throws SQLException { + database().transaction((db -> { + ItemDAO dao = new ItemDAO(database()); + Item item; + long count = dao.countAll(); + assertTrue(dao.insert(item = new Item("countAllAndInsert", 30, false))); + assertEquals(count + 1, dao.countAll()); + assertTrue(dao.delete(item)); + assertEquals(count, dao.countAll()); + })); + } + + @Test + void insertsAndFind() throws SQLException { + database().transaction((db) -> { + ItemDAO dao = new ItemDAO(database()); + for (int i = 0; i < 10; i++) { + assertTrue(dao.insert(new Item("insertsAndFind 1:" + i, 40, false))); + assertTrue(dao.insert(new Item("insertsAndFind 2:" + i, 40, true))); + assertTrue(dao.insert(new Item("insertsAndFind 3:" + i, 40, i % 2 != 0))); + } + long total_count = dao.countAll(); + long local_count = dao.countBy(Condition.raw("value = 40")); + assertEquals(30, local_count); + + List all = dao.findAll(); + assertEquals(total_count, all.size()); + + List local = dao.findBy(Condition.raw("value = 40")); + assertEquals(local_count, local.size()); + + List sorded = dao.findBy(Condition.raw("value = 40"), Order.by().desc("title")); + assertEquals(local_count, sorded.size()); + assertEquals("insertsAndFind 3:9", sorded.get(0).getTitle()); + assertEquals("insertsAndFind 1:0", sorded.get(sorded.size() - 1).getTitle()); + }); + } + + private static final class Item implements Entity { + private Long id; + private String title; + private int value; + private boolean disabled; + private Date modified; + + Item() { + } + + Item(Long id, String title, int value, boolean disabled, Date modified) { + this.id = id; + this.title = title; + this.value = value; + this.disabled = disabled; + this.modified = modified; + } + + /*Item(String title, int value, boolean disabled, Date modified) { + this(null, title, value, disabled, modified); + }*/ + + Item(String title, int value, boolean disabled) { + this(null, title, value, disabled, new Date()); + } + + @Override + public Long getPrimaryKey() { + return getId(); + } + + Long getId() { + return id; + } + + void setId(Long id) { + this.id = id; + } + + String getTitle() { + return title; + } + + void setTitle(String title) { + this.title = title; + } + + int getValue() { + return value; + } + + void setValue(int value) { + this.value = value; + } + + boolean isDisabled() { + return disabled; + } + + void setDisabled(boolean disabled) { + this.disabled = disabled; + } + + Date getModified() { + return modified; + } + + void setModified(Date modified) { + this.modified = modified; + } + } + + private static final class ItemFetcher implements Fetcher { + @Override + public Item fetch(ResultSet result) throws SQLException { + Item item = new Item(); + item.setId(result.getLong("id")); + item.setTitle(result.getString("title")); + item.setValue(result.getInt("value")); + item.setDisabled(result.getBoolean("disabled")); + item.setModified(result.getTimestamp("modified")); + return item; + } + } + + private static final class ItemDAO extends DAOAbstract { + ItemDAO(Session session) { + super(session); + } + + @Override + public String getTableName() { + return "test_items"; + } + + @Override + public Condition getPrimaryCondition(Long key) { + return Condition.raw("id = ?", key); + } + + @Override + public Fetcher getFetcher() { + return new ItemFetcher(); + } + + @Override + public boolean insert(Item item) throws SQLException { + Processor processor = new QueryBuilder().append("INSERT INTO ").append(getTableName()).append( + " (title, value, disabled, modified) VALUES (?,?,?,?)" + , item.getTitle(), item.getValue(), item.isDisabled(), item.getModified() + ).on(getSession()); + if (item.getId() != null) { + return processor.update() > 0; + } else { + item.setId(processor.update(Outcome.FIRST_LONG)); + return item.getId() != null; + } + } + + @Override + public boolean update(Item item, Long key) throws SQLException { + return new QueryBuilder().append("UPDATE ").append(getTableName()).append(" SET ") + .appendIf(!key.equals(item.getPrimaryKey()), "id = ?, ", item.getId()) + .append( + "title = ?, value = ?, disabled = ?, modified = ?" + , item.getTitle(), item.getValue(), item.isDisabled(), item.getModified() + ) + .append(" WHERE ", getPrimaryCondition(key)) + .on(getSession()).update() > 0; + } + } +} \ No newline at end of file diff --git a/src/test/java/ua/net/uid/utils/db/query/ConditionTest.java b/src/test/java/ua/net/uid/utils/db/query/ConditionTest.java new file mode 100644 index 0000000..d855f50 --- /dev/null +++ b/src/test/java/ua/net/uid/utils/db/query/ConditionTest.java @@ -0,0 +1,131 @@ +package ua.net.uid.utils.db.query; + + +import org.junit.jupiter.api.Test; + +import static org.junit.jupiter.api.Assertions.*; +import static ua.net.uid.utils.db.query.Condition.*; + +class ConditionTest { + @Test + void rawConditionThrowTest() { + assertThrows(IllegalArgumentException.class, () -> raw(null)); + } + + @Test + void rawConditionTest() { + assertEquals("title IS NOT NULL", raw("title IS NOT NULL").toString()); + + Condition condition = raw("title = ? AND type = ?", "title", 1); + assertEquals("title = ? AND type = ?", condition.toString()); + assertArrayEquals(new Object[]{"title", 1}, condition.toParams()); + } + + @Test + void notConditionThrowTest() { + assertThrows(IllegalArgumentException.class, () -> not(null, 1)); + } + + @Test + void notConditionTest() { + assertNull(not(null)); + + Condition condition1 = not("title = ? AND type = ?", "title", 1); + assertEquals("NOT(title = ? AND type = ?)", condition1.toString()); + assertArrayEquals(new Object[]{"title", 1}, condition1.toParams()); + + Condition condition2 = not(condition1); + assertEquals("title = ? AND type = ?", condition2.toString()); + assertArrayEquals(new Object[]{"title", 1}, condition2.toParams()); + + Condition condition3 = not(and(condition1)); + assertEquals("title = ? AND type = ?", condition3.toString()); + assertArrayEquals(new Object[]{"title", 1}, condition3.toParams()); + } + + @Test + void andConditionTest() { + assertNull(and()); + //assertNull(and(new Condition[]{})); + + Condition condition1 = and(raw("title = ? AND type = ?", "title", 1)); + assertEquals("title = ? AND type = ?", condition1.toString()); + assertArrayEquals(new Object[]{"title", 1}, condition1.toParams()); + + Condition condition2 = and(raw("title = ? AND type = ? OR title IS NULL", "title", 1), null); + assertEquals("title = ? AND type = ? OR title IS NULL", condition2.toString()); + assertArrayEquals(new Object[]{"title", 1}, condition2.toParams()); + + Condition condition3 = and(not("title = ? AND type = ?", "title2", 2), condition1); + assertEquals("(NOT(title = ? AND type = ?)) AND (title = ? AND type = ?)", condition3.toString()); + assertArrayEquals(new Object[]{"title2", 2, "title", 1}, condition3.toParams()); + + Condition condition4 = and(condition3, condition1); + assertEquals("(NOT(title = ? AND type = ?)) AND (title = ? AND type = ?) AND (title = ? AND type = ?)", condition4.toString()); + assertArrayEquals(new Object[]{"title2", 2, "title", 1, "title", 1}, condition4.toParams()); + } + + @Test + void orConditionTest() { + assertNull(or()); + //assertNull(or(new Condition[]{})); + + Condition condition1 = or(raw("title = ? AND type = ?", "title", 1)); + assertEquals("title = ? AND type = ?", condition1.toString()); + assertArrayEquals(new Object[]{"title", 1}, condition1.toParams()); + + Condition condition2 = or(raw("title = ? AND type = ? OR title IS NULL", "title", 1), null); + assertEquals("title = ? AND type = ? OR title IS NULL", condition2.toString()); + assertArrayEquals(new Object[]{"title", 1}, condition2.toParams()); + + Condition condition3 = or(not("title = ? AND type = ?", "title2", 2), condition1); + assertEquals("(NOT(title = ? AND type = ?)) OR (title = ? AND type = ?)", condition3.toString()); + assertArrayEquals(new Object[]{"title2", 2, "title", 1}, condition3.toParams()); + + Condition condition4 = or(condition3, condition1); + assertEquals("(NOT(title = ? AND type = ?)) OR (title = ? AND type = ?) OR (title = ? AND type = ?)", condition4.toString()); + assertArrayEquals(new Object[]{"title2", 2, "title", 1, "title", 1}, condition4.toParams()); + } + + @SuppressWarnings("SpellCheckingInspection") + @Test + void conditionTest() { + //System.out.println(raw.toParams()); + Condition condition = and( + raw("date > ?", 86400), + not("disabled"), + and(raw("true"), not("false")), + null, + not(and( + not(or( + raw("title ILIKE '%'||?||'%'", "part"), + raw("descr ILIKE '%'||?||'%'", "part") + )) + )) + ); + assertEquals( + "(date > ?) AND (NOT(disabled)) AND (true) AND (NOT(false)) AND ((title ILIKE '%'||?||'%') OR (descr ILIKE '%'||?||'%'))", + condition.toString() + ); + assertArrayEquals( + new Object[]{86400, "part", "part"}, + condition.toParams() + ); + } + + @Test + void inExpressionThrowTest() { + assertThrows(IllegalArgumentException.class, () -> in(null)); + } + + @Test + void inExpressionTest() { + Condition condition1 = in("field", 1, 2, 3, 4, 5); + assertNotNull(condition1); + assertEquals("field IN (?,?,?,?,?)", condition1.toString()); + assertArrayEquals( + new Object[]{1, 2, 3, 4, 5}, + condition1.toParams() + ); + } +} \ No newline at end of file diff --git a/src/test/java/ua/net/uid/utils/db/query/QueryBuilderTest.java b/src/test/java/ua/net/uid/utils/db/query/QueryBuilderTest.java new file mode 100644 index 0000000..f7b87b6 --- /dev/null +++ b/src/test/java/ua/net/uid/utils/db/query/QueryBuilderTest.java @@ -0,0 +1,146 @@ +package ua.net.uid.utils.db.query; + +import org.junit.jupiter.api.Test; + +import java.util.Collections; +import java.util.List; + +import static org.junit.jupiter.api.Assertions.*; + +public class QueryBuilderTest { + @Test + void appendQueryPart() { + QueryBuilder builder1 = new QueryBuilder().append((QueryPart) null); + assertEquals("", builder1.getQuery()); + assertArrayEquals(new Object[]{}, builder1.getParams()); + + QueryBuilder builder2 = new QueryBuilder().append( + new SimpleQueryPart("field = ? AND type != ?", 1, 2) + ); + assertEquals("field = ? AND type != ?", builder2.getQuery()); + assertArrayEquals(new Object[]{1, 2}, builder2.getParams()); + } + + @Test + void appendQueryPartWithPrefix() { + QueryBuilder builder1 = new QueryBuilder().append( + " WHERE ", (QueryPart) null + ); + assertEquals("", builder1.getQuery()); + assertArrayEquals(new Object[]{}, builder1.getParams()); + + QueryBuilder builder2 = new QueryBuilder().append( + " WHERE ", + new SimpleQueryPart("field = ? AND type != ?", 1, 2) + ); + assertEquals(" WHERE field = ? AND type != ?", builder2.getQuery()); + assertArrayEquals(new Object[]{1, 2}, builder2.getParams()); + } + + @Test + void appendCharSequence() { + QueryBuilder builder1 = new QueryBuilder().append((CharSequence) null); + assertEquals("", builder1.getQuery()); + assertArrayEquals(new Object[]{}, builder1.getParams()); + + QueryBuilder builder2 = new QueryBuilder().append((CharSequence) ""); + assertEquals("", builder2.getQuery()); + assertArrayEquals(new Object[]{}, builder2.getParams()); + + QueryBuilder builder3 = new QueryBuilder().append((CharSequence) "test"); + assertEquals("test", builder3.getQuery()); + assertArrayEquals(new Object[]{}, builder3.getParams()); + } + + @Test + void appendCharSequenceWithParamsMustThrow1() { + assertThrows(IllegalArgumentException.class, () -> new QueryBuilder().append((CharSequence) null, 1, 2)); + } + + @Test + void appendCharSequenceWithParamsMustThrow2() { + assertThrows(IllegalArgumentException.class, () -> new QueryBuilder().append((CharSequence) "", 1, 2)); + } + + @Test + void appendCharSequenceWithParams() { + QueryBuilder builder1 = new QueryBuilder().append((CharSequence) "test", 1, 2); + assertEquals("test", builder1.getQuery()); + assertArrayEquals(new Object[]{1, 2}, builder1.getParams()); + } + + @Test + void appendString() { + QueryBuilder builder1 = new QueryBuilder().append((String) null); + assertEquals("", builder1.getQuery()); + assertArrayEquals(new Object[]{}, builder1.getParams()); + + QueryBuilder builder2 = new QueryBuilder().append(""); + assertEquals("", builder2.getQuery()); + assertArrayEquals(new Object[]{}, builder2.getParams()); + + QueryBuilder builder3 = new QueryBuilder().append("test"); + assertEquals("test", builder3.getQuery()); + assertArrayEquals(new Object[]{}, builder3.getParams()); + } + + @Test + void appendStringWithParamsMustThrow1() { + assertThrows(IllegalArgumentException.class, () -> new QueryBuilder().append(null, 1, 2)); + } + + @Test + void appendStringWithParamsMustThrow2() { + assertThrows(IllegalArgumentException.class, () -> new QueryBuilder().append("", 1, 2)); + } + + @Test + void appendStringWithParams() { + QueryBuilder builder1 = new QueryBuilder().append("test", 1, 2); + assertEquals("test", builder1.getQuery()); + assertArrayEquals(new Object[]{1, 2}, builder1.getParams()); + } + + @Test + void complexTest() { + QueryBuilder builder = new QueryBuilder() + .append("SELECT t.*") + .append(" FROM test AS t INNER JOIN cross AS c ON (t.type = c.id AND c.mask = ?)", 1) + .append(" WHERE ", Condition.and( + Condition.not("disabled"), + Condition.raw("BIT_AND(access, ?) = ?", 2, 3) + )) + .append(" HAVING ", Condition.and()) + .append(Order.by().desc("t.date")) + .append(" LIMIT ? OFFSET ?", 4, 5); + assertEquals( + "SELECT t.* FROM test AS t INNER JOIN cross AS c ON (t.type = c.id AND c.mask = ?) WHERE (NOT(disabled)) AND (BIT_AND(access, ?) = ?) ORDER BY t.date DESC LIMIT ? OFFSET ?", + builder.getQuery() + ); + assertArrayEquals(new Object[]{1, 2, 3, 4, 5}, builder.getParams()); + //System.out.println(builder.getQuery()); + } + + + static class SimpleQueryPart implements QueryPart { + private final String part; + private final Object[] args; + + SimpleQueryPart(String part, Object... args) { + this.part = part; + this.args = args; + } + + @Override + public void build(StringBuilder builder) { + builder.append(part); + } + + @Override + public void bind(List params) { + if (args != null && args.length > 0) { + Collections.addAll(params, args); + } + } + } +}