Using multiple statement objects in a multi-threaded application

Im developing a multi-threaded application in which different threads are required to update the database concurrently. Hence,i passed a new statement object to each thread, while creating it(to avoid locking,if i send a single object). My doubts are :

1. Is there a limit on the number of statement objects that could be obtained from a single jdbc connection ? would the database connection fail if i create too many statement objects ?

2. If i close the statement properly before the thread dies out,what would be the number of threads that can be spawned at a time (on a system with 512M开发者_C百科b RAM) ?

3. Wouldn't the driver update the database while keeping the data consistent,no matter how many statement objects i use to update the db parallelly ? i use mysql.

---

1. Practically the number of statement objects you would be able to create should suffice your needs. Then again, how much is "too many" in your case?
2. The number of threads that can be created depends on a lot of factors. Do realize that these threads you create would be "OS level" threads and not real threads (assuming you have a dual core system, that would make it 2 hardware threads or 4 if hyper-threading is available). Profiling you would be of prime importance here to determine how many threads can be created before your system slows down to a crawl.
3. This would depend on the locking mechanism used by the database. What are you aiming for; high integrity or high performance? Read this.

IMO, you would be better off looking up Connection objects from a connection pool in each of those threads rather than trying to pass around "statement" objects.

---

Although I am not a java programmer, sharing a single connection between multiple threads is a bad idea. What happens when 2 threads are trying to write on the same socket? - so - each thread must have its own db connection

Yes, the data should be consistent in the DB if many threads are writing at the same time - anyway, you will have to take care in code of managing the transactions correctly - and of course, use InnoDB as the storage engine for MySQL because MyISAM does not permit transactions

---

1. that's probably up to the jdbc implementation, but, in general, just about everything has limits.
2. who knows. in practice, probably thousands. however, that many probably won't increase your performance.
3. yes, you should be able to share 1 connection across multiple threads, however, many jdbc implementations perform poorly in this scenario. better to have a connection per thread (for some reasonable number of connections/threads).