Skip to main content

How to build your own SQLBuilder from scartch

Hibernate is used widely for data access. It is great to release developer from tons of sql scripts. However, the performance is about 50% less than pure jdbc access. For core function, my current project is still using the pure jdbc to access. The pain part is the current code is all write down by stringbuilder which is easy to make a mistake in syntax and missing space between key sql words.

My purpose is trying to create simple java class which help build sql prepared statement.
The class should be working in the following feature as the first version:
1. Support select, Update, delete sql generation.
2.  Code usage should go with the flow feature
3. Support where, join, and, or key words.
4. support parameters for prepared statement.


Let's start from simple simple select/delete statement, the unit test will be as following at first:


The implementation will be simple as well:

After that, we add support of "WHERE" "UPDATE" as well as all other SQL key words, always have the unittest firstly and then implementation:


Based on code above, the sql builder can create sql with incorrect sql syntax. for example, where().where().select()... The next step is to add possible syntax support.
Firstly, we draw the sql syntax state change diagram:

Support syntax of select, update, and delete Support syntax of from:

See https://github.com/LeiZheng/sql-utils for more detail

Comments

Post a Comment

Popular posts from this blog

How to fix "ValueError when trying to compile python module with VC Express"

When I tried to compile the python, I always get compile issue as following: ------------ ... File "C:\Python26\lib\ distutils\msvc9compiler.py ", line 358, in initialize vc_env = query_vcvarsall(VERSION, plat_spec) File "C:\Python26\lib\ distutils\msvc9compiler.py ", line 274, in query_vcvarsall raise ValueError(str(list(result.keys()))) ValueError: [u'path'] --------------------- Python community discussed a lot but no solution: http://bugs.python.org/issue7511 The root cause is because the latest visual studio change the *.bat file a lot especially on 64bit env. The python 2.7 didn't update the path accordingly. Based on the assumption above, the following solution worked for me. To install Visual Studio 2008 Express Edition with all required components: 1. Install Microsoft Visual Studio 2008 Express Edition. The main Visual Studio 2008 Express installer is available from (the C++ installer name is vcsetup.exe): https://ww
21 comments
Read more

How to convert the ResultSet to Stream

Java 8 provided the Stream family and easy operation of it. The way of pipeline usage made the code clear and smart. However, ResultSet is still go with very legacy way to process. Per actual ResultSet usage, it is really helpful if converted as Stream. Here is the simple usage of above: StreamUtils.uncheckedConsumer is required to convert the the SQLException to runtimeException to make the Lamda clear.
Post a Comment
Read more

Interview for System Design 1: Designing a URL Shortening service like TinyURL.

Problem:  This service will provide short aliases redirecting to long URLs. Step 1: Requirement Analysis Understand the the basic core features: 1. create short url from long url. 2. get the long url from  the short url.  Nice to have feature: 3. will url get expired in certain time? 4. could user define their customized short url? here is some questions need to clarify:  1. How long we need keep the url?  (it will have impact on storage, it is very import to understand to how long will the data be if such data will be stored in local storage). 2. Do we allow N : 1 or only 1: 1 mapping? (have impact about algorithm and data storage.  Step 2:   Estimation Of  Resource Usage common resources: data storage || web services: QPS Let's the estimation right now:  Assume DAU is about 500M,  Create: and one user will create new one item every 5 days. so the total creation per Second will be a. yearly new record: 500M/5 * 365 ~ 50G, new records a. monthly storage: 500M/5 * 100  * 30 = 100M *
Post a Comment
Read more