Add to database rest webservice

Question

I am trying to create a rest webservice to add an element to data base. This is my connection class

@Path(value="/user")
public class classeConnection {
    Connection cn=null;
    Statement st=null;
    public classeConnection()  {
      try  {
        Class.forName("com.mysql.jdbc.Driver");
      } catch (ClassNotFoundException e ){};
    }
    public void connecter ()throws SQLException  {
      cn= DriverManager.getConnection("jdbc:mysql://localhost:3306/codezone4", "root","");
      st=cn.createStatement();
      System.out.print("Ping!!");
    }
    public ResultSet execSelect(String req)throws SQLException {
        return(st.executeQuery(req));
    }
    @GET
    @Path (value="/add/{nom}/{prenom}/{email}/{login}/{psw}")
    @Produces(MediaType.APPLICATION_JSON)
    public String execMAJ(@PathParam(value="nom")String nom,
                              @PathParam(value="prenom")String prenom,
            @PathParam(value="email")String email ,@PathParam(value="login")String login,
            @PathParam(value="psw")String psw) throws SQLException  {
      String req;
          req="INSERT INTO user (nom,prenom,email,login,psw) values(
            '"+nom+"','"+prenom+"','"+email+"','"+login+"','"+psw+"')";
          int r=0;
          System.out.println(st);
          r=st.executeUpdate(req);
          System.out.println(st);
      return "succee d'ajout";
    }
    public void fermeture()throws SQLException {
          st.close(); // line 57
      cn.close();
    }
    public static void main(String[] args) throws SQLException {
        classeConnection c=new classeConnection();
        c.connecter();
    }
}

And this is my web.xml

<?xml version="1.0" encoding="ASCII"?>
<web-app 
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
xmlns="http://java.sun.com/xml/ns/javaee" 
xmlns:web="http://java.sun.com/xml/ns/javaee/web-app_2_5.xsd" 
xsi:schemaLocation="http://java.sun.com/xml/ns/javaee       http://java.sun.com/xml/ns/javaee/web-app_3_0.xsd" 
version="3.0">
<display-name>rest</display-name>
<servlet>
<description>JAX-RS Tools Generated - Do not modify</description>
<servlet-name>JAX-RS Servlet</servlet-name>
<servlet-class>com.sun.jersey.spi.container.servlet.ServletContainer</servlet-class>
<init-param>
<param-name>com.sun.jersey.config.server.provider.packages</param-name>
<param-value>com.example</param-value>
</init-param><load-on-startup>1</load-on-startup>
</servlet>
<servlet-mapping>
<servlet-name>JAX-RS Servlet</servlet-name>
<url-pattern>/myrest/*</url-pattern>
</servlet-mapping>
</web-app>

When I run my webservice using the url : /AddToDataBase/myrest/user/add/test/test/test@test.com/test/test, I get this error :

java.lang.NullPointerException
com.example.classeConnection.execMAJ(classeConnection.java:57)
sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
java.lang.reflect.Method.invoke(Unknown Source)
com.sun.jersey.spi.container.JavaMethodInvokerFactory$1.invoke(JavaMethodInvokerFactory.java:60)
com.sun.jersey.server.impl.model.method.dispatch.AbstractResourceMethodDispatchProvider$TypeOutInvoker._dispatch(AbstractResourceMethodDispatchProvider.java:185)
com.sun.jersey.server.impl.model.method.dispatch.ResourceJavaMethodDispatcher.dispatch(ResourceJavaMethodDispatcher.java:75)
com.sun.jersey.server.impl.uri.rules.HttpMethodRule.accept(HttpMethodRule.java:302)
com.sun.jersey.server.impl.uri.rules.RightHandPathRule.accept(RightHandPathRule.java:147)
com.sun.jersey.server.impl.uri.rules.ResourceClassRule.accept(ResourceClassRule.java:108)
com.sun.jersey.server.impl.uri.rules.RightHandPathRule.accept(RightHandPathRule.java:147)
com.sun.jersey.server.impl.uri.rules.RootResourceClassesRule.accept(RootResourceClassesRule.java:84)
com.sun.jersey.server.impl.application.WebApplicationImpl._handleRequest(WebApplicationImpl.java:1542)
com.sun.jersey.server.impl.application.WebApplicationImpl._handleRequest(WebApplicationImpl.java:1473)
com.sun.jersey.server.impl.application.WebApplicationImpl.handleRequest(WebApplicationImpl.java:1419)
com.sun.jersey.server.impl.application.WebApplicationImpl.handleRequest(WebApplicationImpl.java:1409)
com.sun.jersey.spi.container.servlet.WebComponent.service(WebComponent.java:409)
com.sun.jersey.spi.container.servlet.ServletContainer.service(ServletContainer.java:540)
com.sun.jersey.spi.container.servlet.ServletContainer.service(ServletContainer.java:715)
javax.servlet.http.HttpServlet.service(HttpServlet.java:727)
org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)

Are there any suggestions to this issue? Please help!

Answer 1

Do not ask for a new connection in each endpoint this is not a good use of a database connection. You should use a central connection pool and inject it , or if you do not want to mess with DI just make a connection pool with a singleton class using a library such as http://www.mchange.com/projects/c3p0/ .

Answer 2

The method void connecter() never gets called so the Statement objects are not getting initialized and they remain null. You should probably change the Statement object to a local variable and initialize it right before using it, then close the statement right after using it. You can do the same with the database connection:

cn= DriverManager.getConnection("jdbc:mysql://localhost:3306/codezone4", "root","");

If you need faster performance, then the database connection could be opened when your application starts up (not by this class) and then closed when it shuts down. Or you can use a ConnectionPool.

Another issue to consider:

We are not allowed to pass special characters in a URL parameter without encoding them. For a list of what characters are allowed, see this question.

Your test URL should be /AddToDataBase/myrest/user/add/test/test/test%40test.com/test/test