Simple MySQL DB Class
Snippet Menu
ranking 
|
date 
|
member
|
date
|
member
6
Fri. Jun. 16th, 2006 8:45 AM
Creates a new user with all privileges when logging in from localhost.
-3
I actually like SQL. So, things like Hibernate with it's own query language don't quite fit my style. But, I don't want to code the same catch SQLException conn.close over and over either. So, I came up with this.
The idea is you extend SQLCommand (usually anonymously) override getSql(), and call execute() to get a list of whatever objects you are selecting - all the cleanup stuff is taken care of. It also handles nullable attributes more intuitively using ResultSetWrapper and PreparedStatementWrapper so that in your overridden getRow(ResultSetWrapper rs) method, you can call getInt on a nullable column and have it return null - what a concept! Also I like java.util.Date for my dates, so the wrappers convert to/from java.sql.Timestamp.
Right now I've only bothered with some basic types - it should be pretty clear how to add more if you need em.
See what ya'll think.
Oh, it uses JDK 1.5 Generics, but stripping that away would be pretty easy if you wanted 1.2 compliance.
The idea is you extend SQLCommand (usually anonymously) override getSql(), and call execute() to get a list of whatever objects you are selecting - all the cleanup stuff is taken care of. It also handles nullable attributes more intuitively using ResultSetWrapper and PreparedStatementWrapper so that in your overridden getRow(ResultSetWrapper rs) method, you can call getInt on a nullable column and have it return null - what a concept! Also I like java.util.Date for my dates, so the wrappers convert to/from java.sql.Timestamp.
Right now I've only bothered with some basic types - it should be pretty clear how to add more if you need em.
See what ya'll think.
Oh, it uses JDK 1.5 Generics, but stripping that away would be pretty easy if you wanted 1.2 compliance.
9
Connect to MySQL db & display result from Table query
-7
I use this method for keeping my sql templates away from my code.
You can extend upon the idea, as I have done in the past, by placing SQL handing classes between your scripts and the template library.
Things to note here:
The lesser userd heredoc string method. The reason this is used is to keep the SQL clear and well laid out, and not as messy as using quotes.
vsprintf() is a very handy function if you don't want to hard code the number of parameters to interpolate your string with.
The use of sprintf templates offers you additional security. For example, only allowing numbers to be placed where a %d falls. This, of course, shouldn't be the only security on user supplied variables, but comes in extra handy for debugging purposes.
Regarding the TODO in there, it would take a check of the number of % placeholders there are in the template. One caveat is remembering to remove the count of %%'s that appear (the literal percentage).
You can extend upon the idea, as I have done in the past, by placing SQL handing classes between your scripts and the template library.
Things to note here:
The lesser userd heredoc string method. The reason this is used is to keep the SQL clear and well laid out, and not as messy as using quotes.
vsprintf() is a very handy function if you don't want to hard code the number of parameters to interpolate your string with.
The use of sprintf templates offers you additional security. For example, only allowing numbers to be placed where a %d falls. This, of course, shouldn't be the only security on user supplied variables, but comes in extra handy for debugging purposes.
Regarding the TODO in there, it would take a check of the number of % placeholders there are in the template. One caveat is remembering to remove the count of %%'s that appear (the literal percentage).
5
Return a Portion of a Character String
