Sql query builder java

Saved searches

Use saved searches to filter your results more quickly

You signed in with another tab or window. Reload to refresh your session. You signed out in another tab or window. Reload to refresh your session. You switched accounts on another tab or window. Reload to refresh your session.

Just another SQL query builder focused on Java legacy projects.

License

geevisoft/sql-query-builder

This commit does not belong to any branch on this repository, and may belong to a fork outside of the repository.

Name already in use

A tag already exists with the provided branch name. Many Git commands accept both tag and branch names, so creating this branch may cause unexpected behavior. Are you sure you want to create this branch?

Sign In Required

Please sign in to use Codespaces.

Launching GitHub Desktop

If nothing happens, download GitHub Desktop and try again.

Launching GitHub Desktop

If nothing happens, download GitHub Desktop and try again.

Launching Xcode

If nothing happens, download Xcode and try again.

Launching Visual Studio Code

Your codespace will open once ready.

There was a problem preparing your codespace, please try again.

Latest commit

Git stats

Files

Failed to load latest commit information.

README.md

Just another SQL query builder focused on Java legacy projects. You will not have to modify any of your existing code to start using this when adding new functionality in your project.

Load SqlQueryBuilder.jar as a library in your project

It’s built on Java 6, so it will fit in most of your projects.

String builder = new SqlQueryBuilder(); String query = builder .select() .from("Users") .innerJoin("Clients", "ClientID", "ID") .whereEqual("LastName", "Doe") .orderBy("FirstName") .query();

String builder = new SqlQueryBuilder(); String query = builder .update("Users") .set("FirstName", "John") .set("Age", 21) .whereEqual("ID", 1) .query();

String builder = new SqlQueryBuilder(); String query = builder .insertInto("Users") .value("FirstName", "John") .value("LastName", "Doe") .value("Age", 21) .query();

String builder = new SqlQueryBuilder(); String query = builder .delete("Users") .whereEquals("ID", 1) .query();

You have more examples in the test project.

This project is licensed under the MIT License — see the LICENSE.md file for details

• Avoid to write raw queries in old projects where no framework was used.
• Don’t have to edit any other file except the one where you «want» to add functionality.

About

Just another SQL query builder focused on Java legacy projects.

Источник

Sql query builder java

SqlBuilder

SqlBuilder is a library which attempts to take the pain out of generating SQL queries within Java programs. Using one programming language (Java) to generate code for another language (i.e. SQL) is always a challenge. There are always issues with escaping characters within string literals, getting spaces in the right place, and getting the parentheses to match up. And often, even after the code is debugged and fully tested, it is still very fragile. The slightest change will throw things out of balance and require another round of testing and tweaking.

SqlBuilder changes that whole scenario by wrapping the SQL syntax within very lightweight and easy to use Java objects which follow the «builder» paradigm (similar to StringBuilder). This changes many common SQL syntactical, runtime errors into Java compile-time errors! Let’s dive right in to some quick examples to to see how it all works.

SelectQuery Example

A fairly simple SQL select query embedded in a Java program might currently look something like this:

// assuming a variety of predefined string constants String selectQuery = "SELECT " + T1_COL1 + "," + T1_COL2 + "," + T2_COL1 + " FROM " + TABLE1 + " " + T1 + " INNER JOIN " + TABLE2 + " " + T2 + " ON (" + T1_IDCOL + " = " + T2_IDCOL + ") ORDER BY " + T1_COL1;

Whenever this query is modified, you will need to make sure there are sufficient commas, parentheses, and spaces to generate the correct query (not to mention the correct columns for the given tables and the correct aliases for those tables).

An attempted improvement may look something like this:

String selectQuery = MessageFormat.format( "SELECT ,, FROM  INNER JOIN  ON ( = ) " + "ORDER BY ", T1_COL1, T2_COL2, T2_COL1, TABLE1, T1, TABLE2, T2, T1_IDCOL, T2_IDCOL, T1_COL1);

This technique at least removes some of the formatting issues, as the query syntax is in one contiguous string. However, matching up the placeholders with the arguments is no simple task and simple rearrangements can easily mess up the resulting query string. Additionally, this is still not a viable solution for any sort of dynamic query generation.

Now, let’s see how this query looks using SqlBuilder classes:

// assuming these objects have already been created Table table1, table2; Column t1Col1, t1Col2, t2Col1; Join joinOfT1AndT2; String selectQuery = (new SelectQuery()) .addColumns(t1Col1, t1Col2, t2Col1) .addJoin(SelectQuery.JoinType.INNER_JOIN, joinOfT1AndT2) .addOrderings(t1Col1) .validate().toString();

See how easy that was? Not a single embedded comma, space, or parenthesis to be seen! Notice how much more readable this version is compared to the previous versions? While the Java is a bit more verbose, turning the SQL into Java allows you to utilize your existing Java toolset when writing SQL (think compile-time syntax checking and IDE code completion). This added functionality is well worth the trade-off.

On top of that, check out the validate() method call slipped into the end of the statement. That call will verify that the columns and tables in the query actually make sense. Maintainability, readability, and verifiability all wrapped into one easy to use package.

That was a very simple example of course. Imagine a much more complicated query with multiple sub-expressions in the where clause, and it should be easy to see the power and utility of the SqlBuilder library.

As a final note—for all that the SqlBuilder package is, there are a couple of things it is not. This package will not help you write SQL if you do not already know how to write SQL and use JDBC. It does not abstract away the knowledge necessary to deal with a database, but instead provides tools for avoiding the more error-prone parts of generating SQL queries. If you want a tool to completely abstract away the database, check out Hibernate instead. There is a time and place for straight SQL and a time and place for an ORM tool. If you are currently in the former time and place, then SqlBuilder is a tool you should definitely check out.

Getting Started

• Basic examples
• Additional code examples in the sqlbuilder overview javadocs
• Major highlights of the supported syntax in the Syntax Reference
• Examples for all the classes in the unit tests
• Javadoc API documentation
• Complete source code (with cross references)

Features

• Good portion of commonly used SQL (see the package summary for a more complete list), including:
  • SELECT, UPDATE, DELETE, INSERT, CREATE, DROP, UNION
  • Most boolean logic
  • Most numeric expressions
  • Predefined, strongly verifiable database model (optionally using the included basic implementation)
  • Fully customized, weakly verifiable constants using CustomSql
  • SQL escapes are database dependent
  • SQL escaping is pretty much impossible to do correctly in a library external to the database
  • PreparedStatements should always be used to avoid any need for string escaping (use QueryPreparer for a convenient helper)

  Copyright ©2006–2021 OpenHMS. All rights reserved.

  Источник

  Saved searches

  Use saved searches to filter your results more quickly

  You signed in with another tab or window. Reload to refresh your session. You signed out in another tab or window. Reload to refresh your session. You switched accounts on another tab or window. Reload to refresh your session.

  Stop to lost time writing repeated SQL queries and let Java SQL Query Builder do the job for you. It’s simple, fast and lightweight.

  derickfelix/jsqb

  This commit does not belong to any branch on this repository, and may belong to a fork outside of the repository.

  Name already in use

  A tag already exists with the provided branch name. Many Git commands accept both tag and branch names, so creating this branch may cause unexpected behavior. Are you sure you want to create this branch?

  Sign In Required

  Please sign in to use Codespaces.

  Launching GitHub Desktop

  If nothing happens, download GitHub Desktop and try again.

  Launching GitHub Desktop

  If nothing happens, download GitHub Desktop and try again.

  Launching Xcode

  If nothing happens, download Xcode and try again.

  Launching Visual Studio Code

  Your codespace will open once ready.

  There was a problem preparing your codespace, please try again.

  Latest commit

  Git stats

  Files

  Failed to load latest commit information.

  README.md

  Stop to lost time writing repeated SQL queries and let Java SQL Query Builder do the job for you. It’s simple, fast and lightweight. You don’t need to make a connection with a database.

  For default installation, see Releases section to download the .jar file and add it to the path of your project.

  1.1. Installation with Maven ↑

  To install with maven, you can use the Jitpack for that.

  Step 1. Add the JitPack repository to your build file

  repositories> . repository> id>jitpack.ioid> url>https://jitpack.iourl> repository> repositories>

  Step 2. Add the dependency

  dependencies> . dependency> groupId>com.github.derickfelixgroupId> artifactId>jsqbartifactId> version>LATESTversion> dependency> dependencies>

  If the project doesn’t have any GitHub Releases you can use the short commit hash or ‘master-SNAPSHOT’ as the version. Check the Jitpack page for more details.

  2.1. Basic SELECT statement ↑

  public class Usage < public static void main(String[] args) < Jsqb jsqb = new Jsqb(); String sql = jsqb.select("users").write(); System.out.println(sql); > >

  2.2. SELECT with Specific Fields ↑

  public class Usage < public static void main(String[] args) < Jsqb jsqb = new Jsqb(); String sql = jsqb.select("users", "id", "name", "email").write(); System.out.println(sql); > >

  SELECT users.id, users.name, users.email FROM users

  2.2. Aliased SELECT statement ↑

  The same of the previous one but with more information.

  public class Usage < public static void main(String[] args) < Jsqb jsqb = new Jsqb(); String sql = jsqb.select("users", "id as userId", "name as username", "email as receiver").write(); System.out.println(sql); > >

  SELECT users.id as userId, users.name as username, users.email as receiver FROM users

  The innerJoin() method expects the tableName , and the on detail, and the fields parameter is optional. This method is described as: innerJoin(String tableName, String on, String. fields) .

  public class Usage < public static void main(String[] args) < Jsqb jsqb = new Jsqb(); String sql = jsqb.select("users", "id", "name", "email") .innerJoin("roles", "roles.id = users.role_id", "name", "level") .write(); System.out.println(sql); > >

  SELECT users.id, users.name, users.email, roles.name, roles.level FROM users INNER JOIN roles on roles.id = users.role_id

  Java SQL Query Builder is licensed under the Apache license.

  Copyright 2018 derickfelix. 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. 

  About

  Stop to lost time writing repeated SQL queries and let Java SQL Query Builder do the job for you. It’s simple, fast and lightweight.

  Источник