create sql query in c++/java?

Question

which method do you prefer for creating dynamic sql queries? formating or streaming? Is it just preference or there any reason one is better than other?Or any special library you use to it.

EDIT: Please answer in case of c++.

Answer 1

In Java you should use a PreparedStatement.

PreparedStatement statement = connection.prepareStatement("SELECT * FROM Table WHERE ID = ?");
statement.setInt(1, 17);
ResultSet resultSet = statement.executeQuery();

Answer 2

Always use "prepare" there will be an equivalent to prepareStatement but the exact function name will depend on your database and driver combination.

The advantages of a prepared statement over an execute(String) are many:-

The statement is parsed and an access plan determind only once when the "prepare" statement is executed. Depending on how many times you run the statement this can result in much better performance.

You dont need to worry about special characters in string data when you pass it through setString(). In an execute(String) any single quotes or semicolons in the data will result in a parse error.

Worse this is how "sql injection" attacks work. If a string something like "x' from cust_table; delete from cust_table; select " is entered as data it might well result in the delete statement being parsed and executed.

Handling of numbers is much more efficient. A setInt call take an integer value as is the for the equvalent SQL string you must convert to characters then the DBMS has to convert it back to an integer.

Readability. You code a single SQL statement with a few question marks where the variables go which is relatively easy to read, as opposed to mentally parsing and analysing a series of string concatinations will extra noise for escaped quotes etc.

There are however a couple of cases where execute(String) is actually better.

Where your keys are very unevenly distributed. E.G. If 95% of your customers live in the USA and you want to list the 4% who live in Canada then "where country = ?" would normally result in a table space scan while with "where country = 'CA'" you have some chance of using an index.

The other case is where the user can enter or omit several search criteria. Its much better to build an SQL string for the criteria you are given than construct a complex query which copes with all possible permutaions of the input criteria.

Answer 3

When using prepared statements are not possible, I find using C++ streams is the best way to write the query:

std::ostringstream sql;
sql << "exec loadStuff(" << param1 << ", " << param2 << ")";

Not having to worry about the types of the parameters and the length of the string is great!

Answer 4

There is some thing called SOCI - The C++ Database Access Library for C++