mirage-sql / mirage Goto Github PK

Mirage-SQL - the SQL Centric Database Access Library for Java

License: Apache License 2.0

Java 96.23% Groovy 3.77%

java sql mirage-sql jdbc

mirage's Introduction

Mirage-SQL

Mirage-SQL is an easy and powerful SQL-centric database access library for Java (or JVM based languages) which provides dynamic SQL templates in plain SQL files.

Using

You can get Mirage-SQL from the Maven Central repository. Add the following fragment into your pom.xml.

<dependency>
    <groupId>com.miragesql</groupId>
    <artifactId>miragesql</artifactId>
    <version>2.1.2</version>
</dependency>

or in a Gradle based project add to your build.gradle the following line:

compile 'com.miragesql:miragesql:2.1.2'

or just download it from the Release Page.

Other Mirage-SQL Modules:

Module	Description	Gradle
Mirage-SQL Test	The testing functionality.	`testCompile 'com.miragesql:miragesql-test:2.1.2'`
Mirage-SQL Tools	The development tools.	`testCompile 'com.miragesql:miragesql-tools:2.1.2'`
Mirage-SQL Integration	The integration with Spring, Guice and Seasar2..	`compile 'com.miragesql:miragesql-integration:2.1.2'`

If you are updating your application from a previous Mirage-SQL version, see the Migration Guide.

Example

This is a simple example of a SQL template:

SELECT * FROM BOOK
/*BEGIN*/
  WHERE
  /*IF author != null */
        AUTHOR = /*author*/'Naoki Takezoe'
  /*END*/
  /*IF minPrice != null */
    AND PRICE >= /*minPrice*/20
  /*END*/
  /*IF maxPrice != null */
    AND PRICE <= /*maxPrice*/100
  /*END*/
/*END*/
ORDER BY BOOK_ID ASC

With Mirage-SQL you can embed variables or conditions using special SQL comments, so it's a plain SQL that can be run with any SQL client tool directly. This feature used in the Mirage's SQL template it's called 2Way SQL.

mirage's People

Contributors

Stargazers

Watchers

mirage's Issues

Enable Travis CI

Support Maps, not just Entities.

Support Maps, not just Entities. At the moment this does not seem to really work because:

there's no way to associate a name for an entity "instance" (or same way to pass a name)
passing a Map.class as an entity will try to work with a table called map (so no more than one Map can be used in a project)
the convention from #55 seems to be missing

Rename the project to be easier to find?

I would propose to rename the project to be easier to find.

Searching after "mirage" on Google or other search engines, and even on Github does not return this project, nor the sourceforge one :(.

If I search after "mirage + SQL" than I can find it, but I have to know this.

IMO best bet would be to rename this project to mirageSQL or mirage-sql or at least to something that's not such a common name :).

DB2 Dialect Support

Please add DB2 Dialect support.

Make MirageSQL Java 8 Compatible.

Please make MirageSQL Java 8 compatible.

First thing that fails when building it is even the JavaDoc task with Java 8.

Thank you.

Update project information in pom.xml

We should update some parts of project information in pom.xml.
Also we need assembly plugin no longer.

Generate DDLs from Entities

Generate DDLs from Entities - so the opposite of EntityGenTask.

Generate from Java entities
Generate from Groovy entities

Also:

evaluate if JavaDoc comments can be easily red, to generate comments on columns/table statements.
evaluate the effort to import from JPA format.

Generate Website

Generate Website the website using https://github.com/staticmesh

Derby Dialect Support

Please add Derby dialect support.

Remove maven site

All docs was already moved to GitHub Wiki. So we should remove docs from source tree and remove Maven site and settings about site from pom.xml.

jdbc.properties too restrictive!

Please allow other means to configure the connection too, since this is just too restrictive.

At the moment SessionFactory seems to have only one method: getSession(), so without any parameters, thus relying only on thejdbc.properties for connections.

E.g. when used with H2 in a web application, H2 puts the connection in the ServletContext: getServletContext().getAttribute("connection");

For webapplications is also common to use JNDI to get the connection (from the pool) since this is configured in the application server itself (e.g. Tomcat).

regards.

javadocs maven artifacts

Please add JavaDocs Maven artifacts to the build process and also to the deployed Amateras repository (at the moment they seem to be missing).

regards.

update the mirage mvn site to use bootstrap

Update the mirage maven site to user bootstrap.
Here's how it can be done: http://blog.akquinet.de/2012/04/12/maven-sites-reloaded/

Add Integration Tests.

Add integration tests with as many as possible DBs (not just embedded hsqldb).

One approach might be to use: https://github.com/flapdoodle-oss/de.flapdoodle.embed.process .

Another one might be to use CI provided DBs.

Add convention "id" for field as PK.

Add the convention that "id" for fields are treated as primary keys even if they are not annotated, and there's no other annotated field.

The generation type should be that of the default for the selected Dialect.

Have any roadmap to support immutable entity?

I would like to use entity with immutability, such as @PersistenceConstructor in spring data mongodb.
Do you have any roadmap for immutable entity?

I try to implement in #73.

Make project Java 9 compatible too.

Make project Java 9 compatible (all it's modules) too:

Travis config
Releases for java 8 and java 9 too, with correct dependencies
Java 9 modules support

Add H2 Database Dialect

Add H2 Database Dialect.

JUnit dependency

The JUnit dependency is "compile" configuration.
So, build artifacts(e.g. war, jar etc) include "junit.jar" in them.
Is this correct setting?

Avoid log output that print sql at INFO level

SqlExecutor outputs a SQL and parameters for SQL at INFO level, so useful for debug.

But, parameters sometimes contains some sensitive information. (ex. address)
So, I think that the logging in SqlExecutor should make lower logging level. (ex. DEBUG)

Regards.

Welcome Page Keypoint Update ...

Hi,

IMO the main page (welcome page) in the "What is Mirage" section besides "2WaySQL" and "SQL less Update" should be updated with the most important feature Mirage SQL has: Dynamic SQL support (with externalized queries to SQLs to files with named parameters ).

IMO this is the most important feature Mirage has, and in all projects I've seen mirage, the reason why it was used was to:

avoid hardcoding SQLs in Java files
still have support for dynamic statements (using the mirage comments)
named parameters to avoid the indexed parameter hell (and the many bugs that come with it) for prepared statements.

All in one, the above allow an extremely maintainable code compared to the traditional approach, and also a very fast development process, since the DB people can work in parallel directly on those SQL files.

Mirage for other languages too?

The idea of Mirage and the problem it solves is simply fantastic.
It's just a pity that most users don't get it, and your project is not very well known (maybe some better docs could help ?).

Another option would be to have it not just in Java, but in other languages too, at least the part with prepared statement variable handling.

Deploy Maven Artifacts to Maven Central!

Please deploy the Mirage Maven Artifacts to MavenCentral too.

Nowadays people use http://search.maven.org to search for packages and versions, and Mirage is not there :(.

The SEO is quite bad as even the website is hard to find: see #8

regards.

Use SLF4J instead of java.util.logging!

User SLF4J instead of java.util.logging to allow the users to user whatever logger they need in the project, and not be forced to use more than one.

Add Java 8 JSR 310 compatibility

Add Java 8 JSR 310 compatibility.

Split project in several maven modules!

Split the project in several maven modules (and jars).
It would allow an easier reuse of the different parts of Mirage SQL, and also easier to extend.

E.g. there's stuff not needed at runtime, or not by everybody, but still gets deployed since it's there in the same JAR:

test
integration(s)
tools/ant/updater

and other parts that would be reusable outside of Mirage like:

SQL parser

How to use arrays as parameters?

Please give an example how to use arrays as parameters in dynamic sqls.

Looking at "ParenBindVariableNode.java" seems to be possible to use arrays and lists as parameters.

Thanks in advance.

Can't save Null value into BIT column type

The javaTypeToSqlType method in AbstractValueTpe.java is not accept java's boolean data type.

Add new Icon and Logo.

Create a new Icon and Logo, since there are no "sources" for the old one.

Any suggestion of how could/should it look is welcome.

Mirage-SQL 2.0.x

Could you tell me when the Mirage-SQL 2.0.x is released?

Update dependent libraries

many of the libraries mirage depends on (from pom.xml) seem outdated.

please update them.

Translate and document the SQL Parser.

Please translate and document at least the SQL parser part of Mirage.

It looks like a very interesting functionality that might have use in many projects.

Thank you very much.

Documentation for tools

Describe EntityGen and EntityGenTask.

Generate Groovy Entities

Generated Insert and Delete statement field order is not predictable.

The generated Insert and Delete field order is not predictable, thus causing unit test errors.

Update group id

Refactor Tools to it's own module.

Extend DB Dialect Support with Version Info

Extend the DB Dialect support with DB Version information too.

This is needed because:

different DB Versions (for the same DB Type) behave differently
the DB Version is required for testing too, in order to know what versions are supported and tested in first place.
the generated SQLs will depend on this version too.

SQL Parser - List Variables and their default values.

It would be quite useful for us if the API would allow to get list of all the parsed variables and their default values from a SQL String

Since we have many and big SQLs it happend quite allot that users make errors, so we would like to write a checking task, but for this we need the list of variables and values an SQL contains.

tnx

SQLite Dialect Support

Please add SQLite dialect support.

HikariCPSessionImpl fails to "new HikariCPSessionImpl ()"

HikariCPSessionImpl fails to "new HikariCPSessionImpl (properties)".

Around line 97
PropertyElf.setTargetFromProperties(config, properties)
properties is checked and an exception is thrown.

// set Hikari optional properties. All start with "dataSource.XXX"
PropertyElf.setTargetFromProperties(config, properties);

↓↓↓

Properties hikariProperties = new Properties(properties);

hikariProperties.remove("jdbc.driver");
hikariProperties.remove("jdbc.url");
hikariProperties.remove("jdbc.user");
hikariProperties.remove("jdbc.password");
hikariProperties.remove("sql.cache");
// set Hikari optional properties. All start with "dataSource.XXX"
PropertyElf.setTargetFromProperties(config, hikariProperties);

This change workes for me.

Reproduction code

final Properties properties = new Properties();
p.setProperty("jdbc.driver", "org.h2.Driver");
p.setProperty("jdbc.user", "sa");
p.setProperty("jdbc.password", "pas");
p.setProperty("jdbc.url", "jdbc:h2:mem:testdb;DB_CLOSE_DELAY=-1");
p.setProperty("sql.cache", "false");
final HikariCPSessionImpl hs = new HikariCPSessionImpl(properties);

Exception Log

18:42:03.823 [main] DEBUG com.zaxxer.hikari.HikariConfig - Driver class org.h2.Driver found in Thread context class loader jdk.internal.loader.ClassLoaders$AppClassLoader@383534aa
18:42:03.824 [main] ERROR com.zaxxer.hikari.util.PropertyElf - Property jdbc.password does not exist on target class com.zaxxer.hikari.HikariConfig
18:42:03.847 [main] ERROR j.t.MirageSessionTransactionManagerTest - Property jdbc.password does not exist on target class com.zaxxer.hikari.HikariConfig
java.lang.RuntimeException: Property jdbc.password does not exist on target class com.zaxxer.hikari.HikariConfig
	at com.zaxxer.hikari.util.PropertyElf.setProperty(PropertyElf.java:135)
	at com.zaxxer.hikari.util.PropertyElf.lambda$setTargetFromProperties$0(PropertyElf.java:59)
	at java.base/java.util.concurrent.ConcurrentHashMap.forEach(ConcurrentHashMap.java:1603)
	at java.base/java.util.Properties.forEach(Properties.java:1443)
	at com.zaxxer.hikari.util.PropertyElf.setTargetFromProperties(PropertyElf.java:54)
	at com.miragesql.miragesql.session.HikariCPSessionImpl.<init>(HikariCPSessionImpl.java:97)
	at javamavensample.dao.miragesession.HikariCPSessionManager.init(HikariCPSessionManager.java:19)
	at javamavensample.transaction.MirageSessionTransactionManager.init(MirageSessionTransactionManager.java:29)
	at javamavensample.transaction.MirageSessionTransactionManagerTest.transactionInitTest(MirageSessionTransactionManagerTest.java:18)
	at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
	at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.base/java.lang.reflect.Method.invoke(Method.java:566)
	at org.junit.platform.commons.util.ReflectionUtils.invokeMethod(ReflectionUtils.java:688)
	at org.junit.jupiter.engine.execution.MethodInvocation.proceed(MethodInvocation.java:60)
	at org.junit.jupiter.engine.execution.InvocationInterceptorChain$ValidatingInvocation.proceed(InvocationInterceptorChain.java:131)
	at org.junit.jupiter.engine.extension.TimeoutExtension.intercept(TimeoutExtension.java:149)
	at org.junit.jupiter.engine.extension.TimeoutExtension.interceptTestableMethod(TimeoutExtension.java:140)
	at org.junit.jupiter.engine.extension.TimeoutExtension.interceptTestMethod(TimeoutExtension.java:84)
	at org.junit.jupiter.engine.execution.ExecutableInvoker$ReflectiveInterceptorCall.lambda$ofVoidMethod$0(ExecutableInvoker.java:115)
	at org.junit.jupiter.engine.execution.ExecutableInvoker.lambda$invoke$0(ExecutableInvoker.java:105)
	at org.junit.jupiter.engine.execution.InvocationInterceptorChain$InterceptedInvocation.proceed(InvocationInterceptorChain.java:106)
	at org.junit.jupiter.engine.execution.InvocationInterceptorChain.proceed(InvocationInterceptorChain.java:64)
	at org.junit.jupiter.engine.execution.InvocationInterceptorChain.chainAndInvoke(InvocationInterceptorChain.java:45)
	at org.junit.jupiter.engine.execution.InvocationInterceptorChain.invoke(InvocationInterceptorChain.java:37)
	at org.junit.jupiter.engine.execution.ExecutableInvoker.invoke(ExecutableInvoker.java:104)
	at org.junit.jupiter.engine.execution.ExecutableInvoker.invoke(ExecutableInvoker.java:98)
	at org.junit.jupiter.engine.descriptor.TestMethodTestDescriptor.lambda$invokeTestMethod$6(TestMethodTestDescriptor.java:210)
	at org.junit.platform.engine.support.hierarchical.ThrowableCollector.execute(ThrowableCollector.java:73)
	at org.junit.jupiter.engine.descriptor.TestMethodTestDescriptor.invokeTestMethod(TestMethodTestDescriptor.java:206)
	at org.junit.jupiter.engine.descriptor.TestMethodTestDescriptor.execute(TestMethodTestDescriptor.java:131)
	at org.junit.jupiter.engine.descriptor.TestMethodTestDescriptor.execute(TestMethodTestDescriptor.java:65)
	at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$5(NodeTestTask.java:139)
	at org.junit.platform.engine.support.hierarchical.ThrowableCollector.execute(ThrowableCollector.java:73)
	at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$7(NodeTestTask.java:129)
	at org.junit.platform.engine.support.hierarchical.Node.around(Node.java:137)
	at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$8(NodeTestTask.java:127)
	at org.junit.platform.engine.support.hierarchical.ThrowableCollector.execute(ThrowableCollector.java:73)
	at org.junit.platform.engine.support.hierarchical.NodeTestTask.executeRecursively(NodeTestTask.java:126)
	at org.junit.platform.engine.support.hierarchical.NodeTestTask.execute(NodeTestTask.java:84)
	at java.base/java.util.ArrayList.forEach(ArrayList.java:1540)
	at org.junit.platform.engine.support.hierarchical.SameThreadHierarchicalTestExecutorService.invokeAll(SameThreadHierarchicalTestExecutorService.java:38)
	at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$5(NodeTestTask.java:143)
	at org.junit.platform.engine.support.hierarchical.ThrowableCollector.execute(ThrowableCollector.java:73)
	at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$7(NodeTestTask.java:129)
	at org.junit.platform.engine.support.hierarchical.Node.around(Node.java:137)
	at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$8(NodeTestTask.java:127)
	at org.junit.platform.engine.support.hierarchical.ThrowableCollector.execute(ThrowableCollector.java:73)
	at org.junit.platform.engine.support.hierarchical.NodeTestTask.executeRecursively(NodeTestTask.java:126)
	at org.junit.platform.engine.support.hierarchical.NodeTestTask.execute(NodeTestTask.java:84)
	at java.base/java.util.ArrayList.forEach(ArrayList.java:1540)
	at org.junit.platform.engine.support.hierarchical.SameThreadHierarchicalTestExecutorService.invokeAll(SameThreadHierarchicalTestExecutorService.java:38)
	at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$5(NodeTestTask.java:143)
	at org.junit.platform.engine.support.hierarchical.ThrowableCollector.execute(ThrowableCollector.java:73)
	at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$7(NodeTestTask.java:129)
	at org.junit.platform.engine.support.hierarchical.Node.around(Node.java:137)
	at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$8(NodeTestTask.java:127)
	at org.junit.platform.engine.support.hierarchical.ThrowableCollector.execute(ThrowableCollector.java:73)
	at org.junit.platform.engine.support.hierarchical.NodeTestTask.executeRecursively(NodeTestTask.java:126)
	at org.junit.platform.engine.support.hierarchical.NodeTestTask.execute(NodeTestTask.java:84)
	at org.junit.platform.engine.support.hierarchical.SameThreadHierarchicalTestExecutorService.submit(SameThreadHierarchicalTestExecutorService.java:32)
	at org.junit.platform.engine.support.hierarchical.HierarchicalTestExecutor.execute(HierarchicalTestExecutor.java:57)
	at org.junit.platform.engine.support.hierarchical.HierarchicalTestEngine.execute(HierarchicalTestEngine.java:51)
	at org.junit.platform.launcher.core.DefaultLauncher.execute(DefaultLauncher.java:220)
	at org.junit.platform.launcher.core.DefaultLauncher.lambda$execute$6(DefaultLauncher.java:188)
	at org.junit.platform.launcher.core.DefaultLauncher.withInterceptedStreams(DefaultLauncher.java:202)
	at org.junit.platform.launcher.core.DefaultLauncher.execute(DefaultLauncher.java:181)
	at org.junit.platform.launcher.core.DefaultLauncher.execute(DefaultLauncher.java:128)
	at org.apache.maven.surefire.junitplatform.JUnitPlatformProvider.invokeAllTests(JUnitPlatformProvider.java:142)
	at org.apache.maven.surefire.junitplatform.JUnitPlatformProvider.invoke(JUnitPlatformProvider.java:117)
	at org.apache.maven.surefire.booter.ForkedBooter.invokeProviderInSameClassLoader(ForkedBooter.java:384)
	at org.apache.maven.surefire.booter.ForkedBooter.runSuitesInProcess(ForkedBooter.java:345)
	at org.apache.maven.surefire.booter.ForkedBooter.execute(ForkedBooter.java:126)
	at org.apache.maven.surefire.booter.ForkedBooter.main(ForkedBooter.java:418)

Mirage-SQL 2.1.0

The version 2.1.0 is written on the README, but it has not been published.
If the release schedule has been planed, could you please let me know it?

MSSql Server Dialect Support

Please add MS SQL Server dialect support.

Support for 'for' (loop) command in the SQL parser.

Please add support for the "for" command in the SQL parser.
E.g. Something like below might be a possibility:

select * from book
/* if $tags !=null AND !$tags.isEmpty() */
    where book.published > 2011
    /* FOR $tag in $tags */
       and  book.tag = /*$tag*/'mirage'
    /*END*/
/*END*/

Of course, one option might be for the above example to use IN with arrays, but because it's bound to only one parameter this is open to SQL Injection, so in this case the Prepared Statement doesn't bring too much advantage and security.
(besides there are many more cases where it's simply not possible to reformulate the statement to make use of IN with arrays).

Thank you very much.

Publish to Maven the sources too.

Please publish to Maven the sources too: the latest version seems to have only the binary, and javadoc jar, but not the sources jar (required by IDEs)

Thank you.

mirage-sql / mirage Goto Github PK

mirage's Introduction

Mirage-SQL

Using

Example

Links:

mirage's People

Contributors

Stargazers

Watchers

Forkers

mirage's Issues

Recommend Projects

Recommend Topics

Recommend Org