How to use mysql db within restful java api (JAX-RS, Jersey)?

Question

I'm pretty new to JAVA RESTful APIs, and now I'm trying to create one. I watched some tutorials, but the guy wasn't using database, he was only storing datas in an arraylist or sth at runtime.

I tried to make a database connection and then a simple query, and it works fine when I'm running it as a Java Application, but as soon as I try to use it inside my web-application, it cannot connect to my DB and throws an exception.

The exception:

SEVERE: Servlet.service() for servlet [Jersey Web Application] in context 
with path [/restapi] threw exception [java.lang.IllegalStateException: 
Cannot connect the database!] with root cause
java.sql.SQLException: No suitable driver found for 
jdbc:mysql://localhost:3306/restapi?useUnicode=true&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=UTC

Here is my code, which works as a Java Application, but not in a webapp:

public class AuthService {
    private String url = "jdbc:mysql://localhost:3306/restapi?useUnicode=true&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=UTC";
    public List<User> getAllUsers() {
        List<User> users = new ArrayList<User>();
        try (Connection conn = DriverManager.getConnection(url, "blabla", "blabla")) {

           Statement stmt = conn.createStatement();
           String sql;
           sql = "SELECT * FROM users";
           ResultSet rs = stmt.executeQuery(sql);
           while(rs.next()){

               int id  = rs.getInt("id");
               String login = rs.getString("login");
               String first = rs.getString("firstname");
               String last = rs.getString("lastname");

               users.add(new User(id, login, first, last));            
            }


       } catch (SQLException e) {
           throw new IllegalStateException("Cannot connect the database!", e);
       }
    return users;
}

Probably this won't be the correct way to connect to a DB (since I probably don't want to connect to the DB on every query), but it's not that important for me NOW.

(Sorry for my english, thanks for the answers.)

Can you clarify the distinction between "java application" and "webapp"? Looks to me like you're missing some supporting libraries for the mysql stuff. Possibly the build process in jersey needs a different way of including them or something? — erik258, Mar 26 '17 at 16:26

score 3 · Accepted Answer · edited Jul 17 '17 at 08:42

Error message clearly says that it couldn't find the mysql driver in below line of error message:-

java.sql.SQLException: No suitable driver found for jdbc:mysql://localhost:3306/restapi?useUnicode=true&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=UTC

You need to include the mysql-connector jar in your web-project to make it work. If you are using the Maven, then you need to include below dependency in your code, otherwise download jar manually and put it in your classpath.

 <!--Mysql-Connector-->
 <dependency>
     <groupId>mysql</groupId>
     <artifactId>mysql-connector-java</artifactId>
     <version>5.1.36</version>
 </dependency>

Also I've created a sample App, which is aims to give the quick-start on how to develop a RESTful java application using java,jersey,mysql,spring and hibernate. please read follow it here https://github.com/amitmbm/rest

How to use mysql db within restful java api (JAX-RS, Jersey)?

1 Answers1