1

I'm developing a java based Restful Api server. And I'm using Spark.

The server is gonna be providing Yelp, Foursquare like backend.

I'm starting with using only one Mysql database to store all my tables, such as users, reviews, businesses.

My question is, in my Restful server, how to I maintain the connection, or maybe queries to the database in order to get better performance. For example,

  • Do I have to create different connections for a bunch of similar queries?
  • Do I have to put each query into different threads?
  • If so, how should I structure my code to implement all these. Can I put all database related tasks in a Singleton class?
  • Are there popular frameworks or libraries to handle this?

Thanks

Scott Zhu
  • 8,341
  • 6
  • 31
  • 38
  • Use a Connection Pool. – Elliott Frisch Nov 01 '15 at 02:17
  • @ElliottFrisch Is that a Java class? or a library? – Scott Zhu Nov 01 '15 at 02:27
  • 1
    There are many Java connection pools. [c3p0](http://www.mchange.com/projects/c3p0/), [BoneCP](http://www.jolbox.com/), [DBCP](http://commons.apache.org/proper/commons-dbcp/), etc. – Elliott Frisch Nov 01 '15 at 02:31
  • 1
    May I recommend you reconsider using plural names for database tables (`user`, not `users`, etc.). It sounds petty but it'll really help you understand the entities which you are modeling if you use singular names. Read: http://stackoverflow.com/a/5841297/963076 – ryvantage Nov 01 '15 at 02:34
  • @ryvantage Thanks mate! – Scott Zhu Nov 01 '15 at 02:37
  • @ElliottFrisch Great, which one would you suggest? – Scott Zhu Nov 01 '15 at 02:38
  • 1
    Primarily *opinion* based. I'd suggest using the one you like best. You may want to check-out the [benchmarks](http://jolbox.com/). BoneCP is very nice. Or use Spring and the [Spring Guides](https://spring.io/guides), for example [Accessing Relational Data using JDBC with Spring](https://spring.io/guides/gs/relational-data-access/) and [Building a RESTful Web Service](https://spring.io/guides/gs/rest-service/). – Elliott Frisch Nov 01 '15 at 03:40
  • @ElliottFrisch Thanks, I've looked into BoneCP. So I should just keep a single instance of the `BoneCP` instance across the entire project, and simply use `boneCP.getConnection()` to get a connection whenever we wanna use one? Are there other tricks that I've to keep in mind? And how about `stmt.executeQuery()`, do I have to put this one into a separate thread every time I make a query? – Scott Zhu Nov 01 '15 at 18:42
  • @ScottZhu Generally. And, what do you intend to do in the first thread while the client is waiting? I think you're overcomplicating it. – Elliott Frisch Nov 01 '15 at 18:48
  • @ElliottFrisch Ok, let's say I gonna have many endpoints like `/login`, `/signup`, `/businesses`, `/reviews`, and many front-end clients might be calling it at the same time. In each endpoint is where I might need to call BoneCP to do different queries on my backend database, so that I have the right data to response to my front-end clients. Does that make sense? – Scott Zhu Nov 01 '15 at 18:56
  • Of course you could have multiple endpoints. What are you asking? – Elliott Frisch Nov 01 '15 at 19:02
  • @ElliottFrisch My question is, in the place where I handle all the endpoints, should I just keep a single instance of the BoneCP instance across the entire project, and simply use `boneCP.getConnection()` to get a connection whenever we wanna use one? Are there other tricks that I've to keep in mind? And how about `stmt.executeQuery()`, do I have to put this one into a separate thread every time I make a query? – Scott Zhu Nov 01 '15 at 19:08
  • 1
    @ScottZhu Just use one pool, and call `getConnection()` when you need to use it. I wouldn't use multiple threads. – Elliott Frisch Nov 01 '15 at 19:09
  • @ElliottFrisch Great, thanks mate! – Scott Zhu Nov 01 '15 at 19:16

0 Answers0