You will get this error when you call any of the setXxx() methods on PreparedStatement, while the SQL query string does not have any placeholders ? for this. For example this is wrong: String sql = “INSERT INTO tablename (col1, col2, col3) VALUES (val1, val2, val3)”; // … preparedStatement = connection.prepareStatement(sql); preparedStatement.setString(1, val1); // Fail. … Read more
is my use in static Connection object thread safe? Absolutely not! This way the connection going to be shared among all requests sent by all users and thus all queries will interfere with each other. But threadsafety is not your only problem, resource leaking is also your other problem. You’re keeping a single connection open … Read more
A common practice is to configure this as a DataSource in the servlet container in question. It will provide you connection pooling facilities which will greatly improve performance. Also a common practice is to externalize the raw settings in some configuration file which is been placed in the classpath. In case you’re using Tomcat as … Read more
Consider two ways of doing the same thing: PreparedStatement stmt = conn.createStatement(“INSERT INTO students VALUES(‘” + user + “‘)”); stmt.execute(); Or PreparedStatement stmt = conn.prepareStatement(“INSERT INTO student VALUES(?)”); stmt.setString(1, user); stmt.execute(); If “user” came from user input and the user input was Robert’); DROP TABLE students; — Then in the first instance, you’d be hosed. … Read more
Since version 6.0.24, Tomcat ships with a memory leak detection feature, which in turn can lead to this kind of warning messages when there’s a JDBC 4.0 compatible driver in the webapp’s /WEB-INF/lib which auto-registers itself during webapp’s startup using the ServiceLoader API, but which did not auto-deregister itself during webapp’s shutdown. This message is … Read more
If you need a standalone connection pool, my preference goes to C3P0 over DBCP (that I’ve mentioned in this previous answer), I just had too much problems with DBCP under heavy load. Using C3P0 is dead simple. From the documentation: ComboPooledDataSource cpds = new ComboPooledDataSource(); cpds.setDriverClass( “org.postgresql.Driver” ); //loads the jdbc driver cpds.setJdbcUrl( “jdbc:postgresql://localhost/testdb” ); … Read more
Advantages of a PreparedStatement: Precompilation and DB-side caching of the SQL statement leads to overall faster execution and the ability to reuse the same SQL statement in batches. Automatic prevention of SQL injection attacks by builtin escaping of quotes and other special characters. Note that this requires that you use any of the PreparedStatement setXxx() … Read more
I was wondering if it is possible to execute something like this using JDBC. “SELECT FROM * TABLE;INSERT INTO TABLE;” Yes it is possible. There are two ways, as far as I know. They are By setting database connection property to allow multiple queries, separated by a semi-colon by default. By calling a stored procedure … Read more
The others are right about making the driver JAR available to your servlet container. My comment was meant to suggest that you verify from the command line whether the driver itself is intact. Rather than an empty main(), try something like this, adapted from the included documentation: public class LoadDriver { public static void main(String[] … Read more
You think it’s simpler and faster to do it with JDBC because you aren’t considering the real world operating environment of phones and portable devices. They often have flakey connectivity through buggy traffic rewriting proxies and insane firewalls. They’re typically using a network transport layer that has high and variable packet loss rates and latencies … Read more