diff --git a/LICENSE b/LICENSE new file mode 100644 index 0000000..261eeb9 --- /dev/null +++ b/LICENSE @@ -0,0 +1,201 @@ + Apache License + Version 2.0, January 2004 + http://www.apache.org/licenses/ + + TERMS AND CONDITIONS FOR USE, REPRODUCTION, AND DISTRIBUTION + + 1. Definitions. + + "License" shall mean the terms and conditions for use, reproduction, + and distribution as defined by Sections 1 through 9 of this document. + + "Licensor" shall mean the copyright owner or entity authorized by + the copyright owner that is granting the License. + + "Legal Entity" shall mean the union of the acting entity and all + other entities that control, are controlled by, or are under common + control with that entity. For the purposes of this definition, + "control" means (i) the power, direct or indirect, to cause the + direction or management of such entity, whether by contract or + otherwise, or (ii) ownership of fifty percent (50%) or more of the + outstanding shares, or (iii) beneficial ownership of such entity. + + "You" (or "Your") shall mean an individual or Legal Entity + exercising permissions granted by this License. + + "Source" form shall mean the preferred form for making modifications, + including but not limited to software source code, documentation + source, and configuration files. + + "Object" form shall mean any form resulting from mechanical + transformation or translation of a Source form, including but + not limited to compiled object code, generated documentation, + and conversions to other media types. + + "Work" shall mean the work of authorship, whether in Source or + Object form, made available under the License, as indicated by a + copyright notice that is included in or attached to the work + (an example is provided in the Appendix below). + + "Derivative Works" shall mean any work, whether in Source or Object + form, that is based on (or derived from) the Work and for which the + editorial revisions, annotations, elaborations, or other modifications + represent, as a whole, an original work of authorship. For the purposes + of this License, Derivative Works shall not include works that remain + separable from, or merely link (or bind by name) to the interfaces of, + the Work and Derivative Works thereof. + + "Contribution" shall mean any work of authorship, including + the original version of the Work and any modifications or additions + to that Work or Derivative Works thereof, that is intentionally + submitted to Licensor for inclusion in the Work by the copyright owner + or by an individual or Legal Entity authorized to submit on behalf of + the copyright owner. For the purposes of this definition, "submitted" + means any form of electronic, verbal, or written communication sent + to the Licensor or its representatives, including but not limited to + communication on electronic mailing lists, source code control systems, + and issue tracking systems that are managed by, or on behalf of, the + Licensor for the purpose of discussing and improving the Work, but + excluding communication that is conspicuously marked or otherwise + designated in writing by the copyright owner as "Not a Contribution." + + "Contributor" shall mean Licensor and any individual or Legal Entity + on behalf of whom a Contribution has been received by Licensor and + subsequently incorporated within the Work. + + 2. Grant of Copyright License. Subject to the terms and conditions of + this License, each Contributor hereby grants to You a perpetual, + worldwide, non-exclusive, no-charge, royalty-free, irrevocable + copyright license to reproduce, prepare Derivative Works of, + publicly display, publicly perform, sublicense, and distribute the + Work and such Derivative Works in Source or Object form. + + 3. Grant of Patent License. Subject to the terms and conditions of + this License, each Contributor hereby grants to You a perpetual, + worldwide, non-exclusive, no-charge, royalty-free, irrevocable + (except as stated in this section) patent license to make, have made, + use, offer to sell, sell, import, and otherwise transfer the Work, + where such license applies only to those patent claims licensable + by such Contributor that are necessarily infringed by their + Contribution(s) alone or by combination of their Contribution(s) + with the Work to which such Contribution(s) was submitted. If You + institute patent litigation against any entity (including a + cross-claim or counterclaim in a lawsuit) alleging that the Work + or a Contribution incorporated within the Work constitutes direct + or contributory patent infringement, then any patent licenses + granted to You under this License for that Work shall terminate + as of the date such litigation is filed. + + 4. Redistribution. You may reproduce and distribute copies of the + Work or Derivative Works thereof in any medium, with or without + modifications, and in Source or Object form, provided that You + meet the following conditions: + + (a) You must give any other recipients of the Work or + Derivative Works a copy of this License; and + + (b) You must cause any modified files to carry prominent notices + stating that You changed the files; and + + (c) You must retain, in the Source form of any Derivative Works + that You distribute, all copyright, patent, trademark, and + attribution notices from the Source form of the Work, + excluding those notices that do not pertain to any part of + the Derivative Works; and + + (d) If the Work includes a "NOTICE" text file as part of its + distribution, then any Derivative Works that You distribute must + include a readable copy of the attribution notices contained + within such NOTICE file, excluding those notices that do not + pertain to any part of the Derivative Works, in at least one + of the following places: within a NOTICE text file distributed + as part of the Derivative Works; within the Source form or + documentation, if provided along with the Derivative Works; or, + within a display generated by the Derivative Works, if and + wherever such third-party notices normally appear. The contents + of the NOTICE file are for informational purposes only and + do not modify the License. You may add Your own attribution + notices within Derivative Works that You distribute, alongside + or as an addendum to the NOTICE text from the Work, provided + that such additional attribution notices cannot be construed + as modifying the License. + + You may add Your own copyright statement to Your modifications and + may provide additional or different license terms and conditions + for use, reproduction, or distribution of Your modifications, or + for any such Derivative Works as a whole, provided Your use, + reproduction, and distribution of the Work otherwise complies with + the conditions stated in this License. + + 5. Submission of Contributions. Unless You explicitly state otherwise, + any Contribution intentionally submitted for inclusion in the Work + by You to the Licensor shall be under the terms and conditions of + this License, without any additional terms or conditions. + Notwithstanding the above, nothing herein shall supersede or modify + the terms of any separate license agreement you may have executed + with Licensor regarding such Contributions. + + 6. Trademarks. This License does not grant permission to use the trade + names, trademarks, service marks, or product names of the Licensor, + except as required for reasonable and customary use in describing the + origin of the Work and reproducing the content of the NOTICE file. + + 7. Disclaimer of Warranty. Unless required by applicable law or + agreed to in writing, Licensor provides the Work (and each + Contributor provides its Contributions) on an "AS IS" BASIS, + WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or + implied, including, without limitation, any warranties or conditions + of TITLE, NON-INFRINGEMENT, MERCHANTABILITY, or FITNESS FOR A + PARTICULAR PURPOSE. You are solely responsible for determining the + appropriateness of using or redistributing the Work and assume any + risks associated with Your exercise of permissions under this License. + + 8. Limitation of Liability. In no event and under no legal theory, + whether in tort (including negligence), contract, or otherwise, + unless required by applicable law (such as deliberate and grossly + negligent acts) or agreed to in writing, shall any Contributor be + liable to You for damages, including any direct, indirect, special, + incidental, or consequential damages of any character arising as a + result of this License or out of the use or inability to use the + Work (including but not limited to damages for loss of goodwill, + work stoppage, computer failure or malfunction, or any and all + other commercial damages or losses), even if such Contributor + has been advised of the possibility of such damages. + + 9. Accepting Warranty or Additional Liability. While redistributing + the Work or Derivative Works thereof, You may choose to offer, + and charge a fee for, acceptance of support, warranty, indemnity, + or other liability obligations and/or rights consistent with this + License. However, in accepting such obligations, You may act only + on Your own behalf and on Your sole responsibility, not on behalf + of any other Contributor, and only if You agree to indemnify, + defend, and hold each Contributor harmless for any liability + incurred by, or claims asserted against, such Contributor by reason + of your accepting any such warranty or additional liability. + + END OF TERMS AND CONDITIONS + + APPENDIX: How to apply the Apache License to your work. + + To apply the Apache License to your work, attach the following + boilerplate notice, with the fields enclosed by brackets "[]" + replaced with your own identifying information. (Don't include + the brackets!) The text should be enclosed in the appropriate + comment syntax for the file format. We also recommend that a + file or class name and description of purpose be included on the + same "printed page" as the copyright notice for easier + identification within third-party archives. + + Copyright [yyyy] [name of copyright owner] + + 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. diff --git a/README.md b/README.md new file mode 100644 index 0000000..eb3b120 --- /dev/null +++ b/README.md @@ -0,0 +1,19 @@ + +# uid.database + +pom.xml: + +1. in repositories section: + + + ua.net.uid.releases + https://git.uid.net.ua/maven/releases/ + + +2. in dependencies section: + + + ua.net.uid + uid.database + 1.0.0 + diff --git a/pom.xml b/pom.xml new file mode 100644 index 0000000..6b37a72 --- /dev/null +++ b/pom.xml @@ -0,0 +1,115 @@ + + + 4.0.0 + ua.net.uid + uid.database + 1.0.0-SNAPSHOT + jar + + + + ASL + http://www.apache.org/licenses/LICENSE-2.0.txt + repo + + + + + + org.junit.jupiter + junit-jupiter-engine + 5.5.1 + test + + + + com.h2database + h2 + [1.4.193, 1.5.0) + test + + + + + + + . + + LICENSE + NOTICE.txt + RELEASE.txt + + + + + + + org.apache.maven.plugins + maven-compiler-plugin + 3.8.0 + + 1.8 + 1.8 + true + + + + org.apache.maven.plugins + maven-surefire-plugin + 2.22.2 + + + maven-failsafe-plugin + 2.22.2 + + + org.apache.maven.plugins + maven-release-plugin + 2.5.3 + + true + @{project.version} + release + + + + + + + UTF-8 + 1.8 + 1.8 + uid-releases + + + + + ua.net.uid-releases + https://git.uid.net.ua/maven/releases/ + + + ua.net.uid-snapshots + https://git.uid.net.ua/maven/snapshots/ + + + + + scm:git:https://git.uid.net.ua/git/uid/uid.database.git + scm:git:https://git.uid.net.ua/git/uid/uid.database.git + https://git.uid.net.ua/git/uid/uid.database.git + + + + + uid-releases + Release repository + https://git.uid.net.ua/maven/releases/ + + + uid-snapshots + Snapshots repository + https://git.uid.net.ua/maven/snapshots/ + + + + \ No newline at end of file diff --git a/src/main/java/ua/net/uid/utils/db/Batch.java b/src/main/java/ua/net/uid/utils/db/Batch.java new file mode 100644 index 0000000..dffd4bd --- /dev/null +++ b/src/main/java/ua/net/uid/utils/db/Batch.java @@ -0,0 +1,70 @@ +package ua.net.uid.utils.db; + +import java.io.Closeable; +import java.sql.Connection; +import java.sql.PreparedStatement; +import java.sql.SQLException; + +public final class Batch implements Closeable { + private final Session session; + private final String query; + private Connection connection = null; + private PreparedStatement statement = null; + + Batch(Session session, String query) { + this.session = session; + this.query = query; + } + + private Connection connection() throws SQLException { + if (connection == null) + connection = session.connection(); + return connection; + } + + private PreparedStatement statement() throws SQLException { + if (statement == null) { + try { + statement = connection().prepareStatement(query); + } catch (SQLException ex) { + close(); + throw ex; + } + } + return statement; + } + + public Batch values(Object... params) throws SQLException { + if (params != null) { + try { + final PreparedStatement stmt = statement(); + Processor.bind(stmt, params); + stmt.addBatch(); + } catch (SQLException ex) { + close(); + throw ex; + } + } + return this; + } + + public int[] execute() throws SQLException { + try { + return statement.executeBatch(); + } finally { + close(); + } + } + + @Override + public void close() { + if (statement != null) { + try { statement.close(); } catch (final SQLException ignored) {} + } + statement = null; + if (connection != null) { + session.release(connection); + connection = null; + } + } +} diff --git a/src/main/java/ua/net/uid/utils/db/Block.java b/src/main/java/ua/net/uid/utils/db/Block.java new file mode 100644 index 0000000..66424ab --- /dev/null +++ b/src/main/java/ua/net/uid/utils/db/Block.java @@ -0,0 +1,7 @@ +package ua.net.uid.utils.db; + +import java.sql.SQLException; + +public interface Block { + void execute(Session db) throws SQLException; +} diff --git a/src/main/java/ua/net/uid/utils/db/Connector.java b/src/main/java/ua/net/uid/utils/db/Connector.java new file mode 100644 index 0000000..f2573d0 --- /dev/null +++ b/src/main/java/ua/net/uid/utils/db/Connector.java @@ -0,0 +1,89 @@ +package ua.net.uid.utils.db; + +import javax.sql.DataSource; +import java.sql.Connection; +import java.sql.SQLException; + +public interface Connector { + Connection get() throws SQLException; + + void release(Connection connection); + + void close(); + + final class Pooled implements Connector { + private final DataSource source; + + public Pooled(DataSource source) { + this.source = source; + } + + @Override + public Connection get() throws SQLException { + return source.getConnection(); + } + + @Override + public void release(Connection connection) { + if (connection != null) { + try { connection.close(); } catch (final SQLException ignored) {} + } + } + + @Override + public void close() { + } + } + + final class Source implements Connector { + private final DataSource source; + private Connection connection = null; + + public Source(DataSource source) { + this.source = source; + } + + @Override + public Connection get() throws SQLException { + if (connection == null || connection.isClosed()) + connection = source.getConnection(); + return connection; + } + + @Override + public void release(Connection connection) { + } + + @Override + public void close() { + if (connection != null) { + try { connection.close(); } catch (final SQLException ignored) {} + connection = null; + } + } + } + + final class Static implements Connector { + private final Connection connection; + + public Static(Connection connection) { + this.connection = connection; + } + + @Override + public Connection get() { + return connection; + } + + @Override + public void release(Connection connection) { + } + + @Override + public void close() { + if (connection != null) { + try { connection.close(); } catch (final SQLException ignored) {} + } + } + } +} diff --git a/src/main/java/ua/net/uid/utils/db/Database.java b/src/main/java/ua/net/uid/utils/db/Database.java new file mode 100644 index 0000000..5c899f6 --- /dev/null +++ b/src/main/java/ua/net/uid/utils/db/Database.java @@ -0,0 +1,57 @@ +package ua.net.uid.utils.db; + +import javax.sql.ConnectionPoolDataSource; +import javax.sql.DataSource; +import java.sql.Connection; +import java.sql.SQLException; + +public final class Database extends Session { + private final Connector connector; + + public Database(Connector connector) { + this.connector = connector; + } + + public Database(DataSource source, boolean pooled) { + this(pooled ? new Connector.Pooled(source) : new Connector.Source(source)); + } + + public Database(DataSource source) { + this(source, (source instanceof ConnectionPoolDataSource)); + } + + public Database(Connection connection) { + this(new Connector.Static(connection)); + } + + @Override + protected Connection connection() throws SQLException { + return connector.get(); + } + + @Override + protected void release(Connection connection) { + connector.release(connection); + } + + public void transaction(Solid tx) throws SQLException { + Connection connection = null; + try { + connection = connection(); + connection.setAutoCommit(false); + tx.execute(new Transaction(connection)); + } catch (Exception ex) { + if (connection != null) + connection.rollback(); + throw ex; + } finally { + if (connection != null) { + try { + connection.setAutoCommit(true); + } catch (SQLException ignore) { + } + release(connection); + } + } + } +} diff --git a/src/main/java/ua/net/uid/utils/db/Fetcher.java b/src/main/java/ua/net/uid/utils/db/Fetcher.java new file mode 100644 index 0000000..39e772a --- /dev/null +++ b/src/main/java/ua/net/uid/utils/db/Fetcher.java @@ -0,0 +1,8 @@ +package ua.net.uid.utils.db; + +import java.sql.ResultSet; +import java.sql.SQLException; + +public interface Fetcher { + T fetch(final ResultSet result) throws SQLException; +} diff --git a/src/main/java/ua/net/uid/utils/db/Outcome.java b/src/main/java/ua/net/uid/utils/db/Outcome.java new file mode 100644 index 0000000..233766d --- /dev/null +++ b/src/main/java/ua/net/uid/utils/db/Outcome.java @@ -0,0 +1,14 @@ +package ua.net.uid.utils.db; + +import java.sql.ResultSet; +import java.sql.SQLException; + +public interface Outcome { + Outcome NOT_EMPTY = ResultSet::next; + Outcome UPDATES_COUNT = result -> result.getStatement().getUpdateCount(); + Outcome FIRST_INT = result -> result.next() ? result.getInt(1) : null; + Outcome FIRST_LONG = result -> result.next() ? result.getLong(1) : null; + Outcome FIRST_OBJECT = result -> result.next() ? result.getObject(1) : null; + + T process(final ResultSet result) throws SQLException; +} diff --git a/src/main/java/ua/net/uid/utils/db/Processor.java b/src/main/java/ua/net/uid/utils/db/Processor.java new file mode 100644 index 0000000..b3dd8b2 --- /dev/null +++ b/src/main/java/ua/net/uid/utils/db/Processor.java @@ -0,0 +1,156 @@ +package ua.net.uid.utils.db; + +import java.sql.*; +import java.util.*; + +public final class Processor { + private final Session session; + private final String query; + private final Object[] params; + + Processor(Session session, String query, Object[] params) { + this.session = session; + this.query = query; + this.params = params; + } + + static void bind(PreparedStatement statement, Object[] params) throws SQLException { + if (params != null && params.length > 0) { + int count = params.length; + for (int i = 0; i < count; ) { + final Object param = params[i++]; + if (param instanceof Enum || param instanceof CharSequence) { + statement.setString(i, param.toString()); + } else { + statement.setObject(i, param); + } + } + } + } + + public boolean execute() throws SQLException { + return process(Connection::prepareStatement, PreparedStatement::execute); + } + + public int update() throws SQLException { + return process(Connection::prepareStatement, PreparedStatement::executeUpdate); + } + + public T update(final Outcome outcome) throws SQLException { + return process( + (connection, query) -> connection.prepareStatement(query, Statement.RETURN_GENERATED_KEYS), + statement -> { + ResultSet result = null; + try { + statement.execute(); + result = statement.getGeneratedKeys(); + return outcome.process(result); + } finally { + if (result != null) { + try { result.close(); } catch (final SQLException ignored) {} + } + } + } + ); + } + + public T select(final Outcome outcome) throws SQLException { + return process( + Connection::prepareStatement, + statement -> { + ResultSet result = null; + try { + result = statement.executeQuery(); + return outcome.process(result); + } finally { + if (result != null) { + try { result.close(); } catch (final SQLException ignored) {} + } + } + } + ); + } + + public T scalar(final Fetcher fetcher) throws SQLException { + return select(result -> result.next() ? fetcher.fetch(result) : null); + } + + public void foreach(final Fetcher fetcher) throws SQLException { + select(result -> { + while (result.next()) + fetcher.fetch(result); + return null; + }); + } + + public void foreach(final Fetcher fetcher, final Callback callback) throws SQLException { + select(result -> { + while (result.next()) + callback.call(fetcher.fetch(result)); + return null; + }); + } + + public void collect(final Fetcher fetcher, final Collection collection) throws SQLException { + select(result -> { + while (result.next()) + collection.add(fetcher.fetch(result)); + return null; + }); + } + + public List list(final Fetcher fetcher) throws SQLException { + ArrayList list = new ArrayList<>(); + collect(fetcher, list); + return list; + } + + public Set set(final Fetcher fetcher) throws SQLException { + LinkedHashSet set = new LinkedHashSet<>(); + collect(fetcher, set); + return set; + } + + @SuppressWarnings("unchecked") + public void map(final Fetcher fetcher, final String key, final Map map) throws SQLException { + select(result -> { + while (result.next()) + map.put((K) result.getObject(key), fetcher.fetch(result)); + return null; + }); + } + + public Map map(final Fetcher fetcher, final String key) throws SQLException { + Map map = new LinkedHashMap<>(); + map(fetcher, key, map); + return map; + } + + protected T process(Preparer preparer, final Handler callback) throws SQLException { + Connection connection = null; + PreparedStatement statement = null; + try { + connection = session.connection(); + statement = preparer.prepare(connection, query); + bind(statement, params); + return callback.handle(statement); + } finally { + if (statement != null) { + try { statement.close(); } catch (final SQLException ignored) {} + } + session.release(connection); + } + } + + public interface Callback

{ + void call(P param); + } + + private interface Handler { + T handle(final PreparedStatement statement) throws SQLException; + } + + private interface Preparer { + PreparedStatement prepare(final Connection connection, final String query) throws SQLException; + } +} diff --git a/src/main/java/ua/net/uid/utils/db/Session.java b/src/main/java/ua/net/uid/utils/db/Session.java new file mode 100644 index 0000000..2625248 --- /dev/null +++ b/src/main/java/ua/net/uid/utils/db/Session.java @@ -0,0 +1,45 @@ +package ua.net.uid.utils.db; + +import java.sql.Connection; +import java.sql.SQLException; + +public abstract class Session { + protected abstract Connection connection() throws SQLException; + + protected abstract void release(Connection connection); + + public Processor query(String query, Object... params) { + return new Processor(this, query, params); + } + + public Batch batch(String query) { + return new Batch(this, query); + } + + public void block(Block block) throws SQLException { + Connection connection = null; + try { + connection = connection(); + block.execute(new SubSession(connection)); + } finally { + release(connection); + } + } + + private static final class SubSession extends Session { + private final Connection connection; + + SubSession(final Connection connection) { + this.connection = connection; + } + + @Override + protected Connection connection() { + return connection; + } + + @Override + protected void release(Connection connection) { + } + } +} diff --git a/src/main/java/ua/net/uid/utils/db/Solid.java b/src/main/java/ua/net/uid/utils/db/Solid.java new file mode 100644 index 0000000..a32efc1 --- /dev/null +++ b/src/main/java/ua/net/uid/utils/db/Solid.java @@ -0,0 +1,7 @@ +package ua.net.uid.utils.db; + +import java.sql.SQLException; + +public interface Solid { + void execute(Transaction db) throws SQLException; +} diff --git a/src/main/java/ua/net/uid/utils/db/Transaction.java b/src/main/java/ua/net/uid/utils/db/Transaction.java new file mode 100644 index 0000000..0223813 --- /dev/null +++ b/src/main/java/ua/net/uid/utils/db/Transaction.java @@ -0,0 +1,29 @@ +package ua.net.uid.utils.db; + +import java.sql.Connection; +import java.sql.SQLException; + +public final class Transaction extends Session { + private final Connection connection; + + Transaction(final Connection connection) { + this.connection = connection; + } + + @Override + protected Connection connection() { + return connection; + } + + @Override + protected void release(Connection connection) { + } + + public void commit() throws SQLException { + connection.commit(); + } + + public void rollback() throws SQLException { + connection.rollback(); + } +} 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..73cff9b --- /dev/null +++ b/src/main/java/ua/net/uid/utils/db/dao/DAO.java @@ -0,0 +1,7 @@ +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..efcc608 --- /dev/null +++ b/src/main/java/ua/net/uid/utils/db/dao/DAOAbstract.java @@ -0,0 +1,11 @@ +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..f70b168 --- /dev/null +++ b/src/main/java/ua/net/uid/utils/db/dao/DAOBase.java @@ -0,0 +1,158 @@ +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..65d6be1 --- /dev/null +++ b/src/main/java/ua/net/uid/utils/db/dao/DAOCore.java @@ -0,0 +1,16 @@ +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..485fa5b --- /dev/null +++ b/src/main/java/ua/net/uid/utils/db/dao/Entity.java @@ -0,0 +1,7 @@ +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..90b338b --- /dev/null +++ b/src/main/java/ua/net/uid/utils/db/query/Condition.java @@ -0,0 +1,194 @@ +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..f981db9 --- /dev/null +++ b/src/main/java/ua/net/uid/utils/db/query/Order.java @@ -0,0 +1,57 @@ +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..a432586 --- /dev/null +++ b/src/main/java/ua/net/uid/utils/db/query/Query.java @@ -0,0 +1,7 @@ +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..c29d7a4 --- /dev/null +++ b/src/main/java/ua/net/uid/utils/db/query/QueryBuilder.java @@ -0,0 +1,121 @@ +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..bc041e3 --- /dev/null +++ b/src/main/java/ua/net/uid/utils/db/query/QueryPart.java @@ -0,0 +1,9 @@ +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/ConnectorTest.java b/src/test/java/ua/net/uid/utils/db/ConnectorTest.java new file mode 100644 index 0000000..8451ad7 --- /dev/null +++ b/src/test/java/ua/net/uid/utils/db/ConnectorTest.java @@ -0,0 +1,107 @@ +package ua.net.uid.utils.db; + +import org.h2.jdbcx.JdbcConnectionPool; +import org.junit.jupiter.api.AfterAll; +import org.junit.jupiter.api.BeforeAll; +import org.junit.jupiter.api.Test; + +import java.sql.Connection; + +import static org.junit.jupiter.api.Assertions.*; + +class ConnectorTest { + private static JdbcConnectionPool source; + + @BeforeAll + static void beforeAll() { + source = JdbcConnectionPool.create(String.format("jdbc:h2:mem:x%d", System.nanoTime()), "sa", ""); + } + + @AfterAll + static void afterAll() { + source.dispose(); + } + + @Test + void pooledConnector() throws Exception { + assertEquals(0, source.getActiveConnections()); + + Connector connector = new Connector.Pooled(source); + assertEquals(0, source.getActiveConnections()); + + Connection connection1 = connector.get(); + assertNotNull(connection1); + assertFalse(connection1.isClosed()); + assertEquals(1, source.getActiveConnections()); + + connector.close(); + assertEquals(1, source.getActiveConnections()); + + Connection connection2 = connector.get(); + assertNotNull(connection2); + assertFalse(connection2.isClosed()); + assertEquals(2, source.getActiveConnections()); + assertNotEquals(connection1, connection2); + + connector.release(connection1); + assertTrue(connection1.isClosed()); + assertEquals(1, source.getActiveConnections()); + + connector.release(connection2); + assertTrue(connection2.isClosed()); + assertEquals(0, source.getActiveConnections()); + } + + @Test + void sourceConnector() throws Exception { + assertEquals(0, source.getActiveConnections()); + + Connector connector = new Connector.Source(source); + assertEquals(0, source.getActiveConnections()); + + Connection connection1 = connector.get(); + assertNotNull(connection1); + assertFalse(connection1.isClosed()); + assertEquals(1, source.getActiveConnections()); + + connector.release(connection1); + assertFalse(connection1.isClosed()); + assertEquals(1, source.getActiveConnections()); + + connector.close(); + assertTrue(connection1.isClosed()); + assertEquals(0, source.getActiveConnections()); + } + + @Test + void staticConnector() throws Exception { + assertEquals(0, source.getActiveConnections()); + + Connector connector = new Connector.Static(source.getConnection()); + assertEquals(1, source.getActiveConnections()); + + Connection connection1 = connector.get(); + assertNotNull(connection1); + assertFalse(connection1.isClosed()); + assertEquals(1, source.getActiveConnections()); + + Connection connection2 = connector.get(); + assertNotNull(connection2); + assertFalse(connection2.isClosed()); + assertEquals(1, source.getActiveConnections()); + assertEquals(connection1, connection2); + + connector.release(connection1); + assertFalse(connection1.isClosed()); + assertEquals(1, source.getActiveConnections()); + + connector.release(connection2); + assertFalse(connection2.isClosed()); + assertEquals(1, source.getActiveConnections()); + + connector.close(); + assertTrue(connection1.isClosed()); + assertEquals(0, source.getActiveConnections()); + } + +} diff --git a/src/test/java/ua/net/uid/utils/db/DatabaseTest.java b/src/test/java/ua/net/uid/utils/db/DatabaseTest.java new file mode 100644 index 0000000..06afda0 --- /dev/null +++ b/src/test/java/ua/net/uid/utils/db/DatabaseTest.java @@ -0,0 +1,121 @@ +package ua.net.uid.utils.db; + +import org.h2.jdbcx.JdbcConnectionPool; +import org.junit.jupiter.api.AfterAll; +import org.junit.jupiter.api.BeforeAll; +import org.junit.jupiter.api.Test; + +import java.sql.Connection; +import java.sql.SQLException; + +import static org.junit.jupiter.api.Assertions.*; + +class DatabaseTest { + private static JdbcConnectionPool source; + private static Database db; + + @BeforeAll + static void beforeAll() throws Exception { + //noinspection SpellCheckingInspection + source = JdbcConnectionPool.create(String.format("jdbc:h2:mem:x%d", System.nanoTime()), "sa", ""); + db = new Database(source, true); + db.query("CREATE TABLE IF NOT EXISTS \"test\" (\"id\" INT IDENTITY NOT NULL PRIMARY KEY, \"name\" VARCHAR(30), \"type\" INTEGER NOT NULL DEFAULT 0)").execute(); + } + + @AfterAll + static void afterClass() { + source.dispose(); + } + + @Test + void simpleExecuteTest() throws Exception { + assertFalse(db.query("INSERT INTO \"test\" (\"name\") VALUES (?)", "Execute").execute()); + assertTrue(db.query("SELECT * FROM \"test\" WHERE \"name\" = ?", "Execute").execute()); + } + + @Test + void simpleUpdateTest() throws Exception { + assertEquals(1, db.query("INSERT INTO \"test\" (\"name\") VALUES (?)", "Update").update()); + //noinspection UnnecessaryBoxing + assertEquals(Integer.valueOf(1), db.query("SELECT COUNT(*) FROM \"test\" WHERE \"name\" = ?", "Update").select(Outcome.FIRST_INT)); + } + + @Test + void updateWithOutcomeTest() throws Exception { + Integer id = db.query("INSERT INTO \"test\" (\"name\") VALUES (?)", "Update With Outcome").update(Outcome.FIRST_INT); + assertNotNull(id); + assertEquals(id, db.query("SELECT \"id\" FROM \"test\" WHERE \"name\" = ?", "Update With Outcome").select(Outcome.FIRST_INT)); + } + + @Test + void simpleBatchTest() throws Exception { + assertArrayEquals( + new int[]{1, 1, 1}, + db.batch("INSERT INTO \"test\" (\"name\", \"type\") VALUES (?,?)").values("batch", 1).values("batch", 2).values("batch", 3).execute() + ); + } + + @SuppressWarnings("UnnecessaryBoxing") + @Test + void blockTest() throws Exception { + db.block(db -> { + Connection connection = db.connection(); + assertEquals(1, db.query("INSERT INTO \"test\" (\"name\") VALUES (?)", "Block").update()); + assertEquals(Integer.valueOf(1), db.query("SELECT COUNT(*) FROM \"test\" WHERE \"name\" = 'Block'").select(Outcome.FIRST_INT)); + assertEquals(connection, db.connection()); + assertEquals(Integer.valueOf(1), db.query("DELETE FROM \"test\" WHERE \"name\" = 'Block'").update(Outcome.UPDATES_COUNT)); + assertEquals(connection, db.connection()); + }); + } + + @SuppressWarnings("UnnecessaryBoxing") + @Test + void transactionManualRollbackTest() throws Exception { + db.transaction(pt -> { + assertEquals(1, pt.query("INSERT INTO \"test\" (\"name\") VALUES (?)", "Transaction1").update()); + assertEquals(1, pt.query("INSERT INTO \"test\" (\"name\") VALUES (?)", "Transaction1").update()); + assertEquals(1, pt.query("INSERT INTO \"test\" (\"name\") VALUES (?)", "Transaction1").update()); + assertEquals(Integer.valueOf(3), pt.query("SELECT COUNT(*) FROM \"test\" WHERE \"name\" = 'Transaction1'").select(Outcome.FIRST_INT)); + pt.rollback(); + }); + assertEquals(Integer.valueOf(0), db.query("SELECT COUNT(*) FROM \"test\" WHERE \"name\" = 'Transaction1'").select(Outcome.FIRST_INT)); + } + + @SuppressWarnings("UnnecessaryBoxing") + @Test + void transactionExceptionRollbackTest() throws Exception { + assertThrows(SQLException.class, () -> + db.transaction(pt -> { + assertEquals(1, pt.query("INSERT INTO \"test\" (\"name\") VALUES (?)", "Transaction2").update()); + assertEquals(Integer.valueOf(1), pt.query("SELECT COUNT(*) FROM \"test\" WHERE \"name\" = 'Transaction2'").select(Outcome.FIRST_INT)); + throw new SQLException("Test exception"); + }) + ); + assertEquals(Integer.valueOf(0), db.query("SELECT COUNT(*) FROM \"test\" WHERE \"name\" = 'Transaction2'").select(Outcome.FIRST_INT)); + } + + @SuppressWarnings("UnnecessaryBoxing") + @Test + void transactionTest() throws Exception { + db.transaction(pt -> { + assertEquals(1, pt.query("INSERT INTO \"test\" (\"name\") VALUES (?)", "Transaction3").update()); + assertEquals(1, pt.query("INSERT INTO \"test\" (\"name\") VALUES (?)", "Transaction3").update()); + assertEquals(Integer.valueOf(2), pt.query("SELECT COUNT(*) FROM \"test\" WHERE \"name\" = 'Transaction3'").select(Outcome.FIRST_INT)); + }); + + assertEquals(Integer.valueOf(2), db.query("SELECT COUNT(*) FROM \"test\" WHERE \"name\" = 'Transaction3'").select(Outcome.FIRST_INT)); + } + + @SuppressWarnings("UnnecessaryBoxing") + @Test + void transactionCommitTest() throws Exception { + db.transaction(pt -> { + assertEquals(1, pt.query("INSERT INTO \"test\" (\"name\") VALUES (?)", "Transaction4").update()); + assertEquals(Integer.valueOf(1), pt.query("SELECT COUNT(*) FROM \"test\" WHERE \"name\" = 'Transaction4'").select(Outcome.FIRST_INT)); + pt.commit(); + }); + assertEquals(Integer.valueOf(1), db.query("SELECT COUNT(*) FROM \"test\" WHERE \"name\" = 'Transaction4'").select(Outcome.FIRST_INT)); + + assertEquals(Integer.valueOf(1), db.query("DELETE FROM \"test\" WHERE \"name\" = 'Transaction4'").update(Outcome.UPDATES_COUNT)); + } +} 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..f5a8d0f --- /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); + } + } + } +}