wood burning stoves 2.0*
The moose likes JDBC and the fly likes handling strings Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Murach's Java Servlets and JSP this week in the Servlets forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "handling strings" Watch "handling strings" New topic
Author

handling strings

Dave Robbins
Ranch Hand

Joined: Sep 16, 2003
Posts: 131
Hello All

I'm using mysql-connector to talk to a mysql database
a lot of the data I'm messing with is in the form of strings
is there a handy function for handling special characters in strings?
at the moment I'm having trouble with stings that contain an apostrophe
I think you need to escape them with a backslash, just wondering if there's some function to do it for me

Thanks
Dave
stu derby
Ranch Hand

Joined: Dec 15, 2005
Posts: 333
You should be using PreparedStatement instead of Statement; PreparedStatement lets you isolate the database code (the SQL) from the database data (your String objects).

Besides dealing with the quote issue, this also is more secure (Google for "SQL injection") and helps to avoid datatype conversion issues, especially implicit type conversions performed by the database, which can often be difficult to supply correct error handling for. On many databases, PreparedStatement will also perform better when the same SQL statements are executed again, but I don't think MySQL is one of those databases (yet, it's a pretty common DB optimisation technique, and they'll probably get to sometime).

PreparedStatement also lets you avoid all that string construction mess; that mess can hide subtle errors and just consumes a lot of programming time in the long run, especially when doing maintenance programming. All those constructed Strings have to get garbage collected too, which is a subtle drag on your applications performance, though usually not a critical issue except in large volume applications.

Here's a brief tutorial:
http://java.sun.com/docs/books/tutorial/jdbc/basics/prepared.html
Dave Robbins
Ranch Hand

Joined: Sep 16, 2003
Posts: 131
so let's say I want to put the string "xxx'xxx" into table "foo"
if I do something like this

PreparedStatement ps = con.prepareStatement("UPDATE FOO SET MYFIELD = ?");
ps.setInt(1, "xxx'xxx");

the apostrophe will be handled properly?

Thanks
Dave
Dave Robbins
Ranch Hand

Joined: Sep 16, 2003
Posts: 131
sorry
that would be setString
stu derby
Ranch Hand

Joined: Dec 15, 2005
Posts: 333
Yes, at least with a driver that truly conforms to the JDBC standard. MySQL's driver certainly does in this respect.
Dave Robbins
Ranch Hand

Joined: Sep 16, 2003
Posts: 131
Stu,

wanted to thank you for the advice
I already had quite a bit of code written to do what I'm messing with and it was a lot of trouble to change it, but it's worth it
the way preparedStatements handle strings and dates makes life WAY easier

Thanx
Dave
 
It is sorta covered in the JavaRanch Style Guide.
 
subject: handling strings
 
Similar Threads
SQL question: how to solve this query with 2 tables? (image included)
using UTF8 string as database key search performance
I would like to optmise multiplication ,please help
Problem in using java to Store 1GB+ files in a MySQL database
Jar file for JDBC...