I'm working on a project for school that requires a simple java server (a ServerSocket creates Socket connections which I send JSON strings over) which then communicates with a database. Obviously I have chosen PostgreSQL.
I'm using Apache DBCP for connection pooling. The DataSource is handed off to each thread. Each thread then makes a Connection and then creates statements, executes them, and then closes.
I'm using prepared statements to prevent SQL injection, but I have done some reading and it seems like using Prepared statements COULD improve performance. I understand the concept, but I do not know how to implement this. JDBC has PreparedStatements. Do these correspond with the Prepared Statements at the database level in postgres?
I read about the postgres prepared statements that they only work while a connection is kept open. But in my application, each thread makes it's own connection and then closes it. So I wouldn't be able to capitalize on the prepared statement. However, it has occurred to me that since I am using DBCP to pool connections, the Java Connection that I create is actually a connection that is being kept open by the pool. Does the DBCP system (poorly documented) manage the prepared statements?
I have considered opening a single connection to the server and letting all threads execute statements through it, and use prepared statements (serviced by postgresql, not JDBC or DBCP). Or simply opening 50 connections and establishing prepared statements and then handing them off to the threads. Is there a reason I shouldn't do this? I read that the JDBC driver for postgres is thread safe.
What's really frustrating is that I can't find anything to read about this. I set up DBCP, but how can I be sure my connection pool is working. How can I tell if prepared statements are really functioning by executing a prepared statement, as opposed to parsing and planning each time I run a query?
Where can I learn more about this? Any direct answers are also very appreciated.
