REST Web Service to consume MySQL DB

Question

I'm building a REST WebService with JAX-RS and Tomcat to consume a MySQL Database.

I'm following this model:

@Path("/login")  
public class Login {

String username;
String password;
// This method is called if POST is requested
@POST
@Produces(MediaType.APPLICATION_XML)
public String loginResponseXML(@FormParam("username") String user, @FormParam("password") String pass) {

    //Connection to MySQL Database
    try {
        Class.forName("com.mysql.jdbc.Driver");
        Connection con = DriverManager.getConnection("jdbc:mysql://localhost/sakila", "root","larcom");
        Statement stmt = con.createStatement();
        ResultSet rs = stmt.executeQuery("Select first_name, last_name From actor where first_name='" +
                                        user + "' and last_name='" + pass + "'");

        while (rs.next()){
            System.out.println(rs.getString("first_name") + " " + rs.getString("last_name"));
            username = rs.getString("first_name");
            password = rs.getString("last_name");
        }
    }           
    catch (ClassNotFoundException e) {
        e.printStackTrace();
    } catch (SQLException e) {
        e.printStackTrace();
    }

    if (user.equals(username) && pass.equals(password)) {
        return ("<?xml version=\"1.0\"?>" + "<auth>200" + "</auth>"); //Success
        //return "Success!";
    } else {
        return ("<?xml version=\"1.0\"?>" + "<auth>404" + "</auth>"); //Damn
        //return "Damn!";
    }

    }
}

I call this method with:

HttpPost httppost = new HttpPost("http://192.168.15.245:8080/org.jersey.andre/rest/login");

Now, my question is: If I want to query the DB for another table I have to create a new class like Login and make the JDBC connection again? A new class and a new JDBC connection for each class that make a query to the DB? Performance issues?

Hope you can understand. Thanks in advance.

So you want one class, one connection to one DB for all tables, for all Restful resources? Performance issues? ;) — PhD, Oct 13 '11 at 18:40

score 0 · Answer 1 · edited May 23 '17 at 09:59

A few tips are in order here: Please isolate the DB based code to a "data layer" so to speak...only perform dispatching/business logic within your resource classes.

Now If you are querying a different table, you WILL have a different query! You could either use the same connection (bad) or create a new one and fire a different query(s).

Now whether each resource hits a different table or the same table with a different query depends on your choice of 'representation' for that resource. There is a reason a RDB schema has multiple tables and it's quite common you'll have a different query involving multiple tables or to mutually independent tables.

Performance issues: For 'fresh data' you ARE always going to hit the DB so to speak. If you want to optimize that either develop your own cache (extremely hard) or use approaches like memcached or ehcache to boost performance - before you decide to do that make sure you verify if it's worth it.

Are you going to be having about 1000 DB hits per second? You probably need some performance boosting/handling. Per day...maybe not. Per 2-3 days...YAGNI (You ain't gonna need it, so don't worry for now)

So, for every 'resource' that you design in your application (Login is NOT a resource: See related post: Why is form based authentication NOT considered RESTful?) choose the representation. It may involve different queries etc., for you to return json/xml/xhtml (whatever you choose). Each 'DB related call' should be isolated into it's own 'data layer' - I suggest go with Spring JDBC to make your life easier. It'll take the burden of JDBC plumbing off your shoulders so you can focus on creating your DAOs (Data Access Objects - a patter for Data Access classes. All DAOs logically belong in the data layer)

Hope this helps

Thanks for the answer. I think I understand but I have a few questions about your answer. I agree in the performance issue. That's not a problem. What do you mean by "isolate de DB Code to a data layer" ? Now, if I want to create a new resource: I'll build a class like Login and define the path to /person for example, make a new jdbc connection and a new query. Is this rigth? — andreelias, Oct 13 '11 at 19:07
Code organization: Database access code should be in classes specifically meant for that purpose. You are mixing resource+business logic and embedding data access code within it...it'll make your code less modular - each class should have only a single responsibility. Data access classes only query and retrieve data. Resource classes do something with that data and send it back etc., — PhD, Oct 13 '11 at 20:21

REST Web Service to consume MySQL DB

1 Answers1