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

How to run odoo(openerp8) in IDE from source on windows

1. install python 2.7 (per openerp8's official doc, python 27 is required.) 2. download get-pip.py from https://bootstrap.pypa.io/get-pip.py , execute the command: python get-pip.py 3. get source of openerp8 from https://github.com/odoo/odoo.git 4. execute the command: pip install -r D:\source_code\odoo\openerp8/requirements.txt . (requirements.txt contains all dependencies. ) The pip will install the python module automatically. However, the real world always bring us the issues because our C++ compile environment is not setup correctly.  we will get the link error when pip try to install psycopg2 (driver to access postgresql db.). Go to  http://www.stickpeople.com/projects/python/win-psycopg/  and choose the compiled binary file directly. For Python-ldap, go to  http://www.lfd.uci.edu/~gohlke/pythonlibs/ 5. Finally, go to http://sourceforge.net/projects/pywin32/files/pywin32 and choose correct version for python-win32service. 6. If you are family with eclipse a lot,
6 comments
Read more