KendoUI for JSP - How to connect to MySQL

Question

guys, I am relatively new to KendoUI and now I am learning by their examples to populate Kendo Grid with data from Database. However, their example is with SQLite and I want to try it with MySQL. Here is what I've done so far:
Clients.java

@WebServlet(description = "A servlet to return data about employees from the database", urlPatterns = {"/src.api/clients"})
public class Clients extends HttpServlet {

    private static final long serialVersionUID = 1L;

    private ClientRepository _repository = null;
    private Gson _gson = null;

    public Clients() {
        super();
    
        _gson = new Gson();
    }

    public void init() throws ServletException {
        super.init();
    }

    protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        try {

            // get the clients from the database
            _repository = new ProductsRepository(this.getServletContext().getRealPath("data/sample.db"));
            // set the content type we are sending back as JSON
            response.setContentType("application/json");

            // convert the list to json and write it to the response
            response.getWriter().print(_gson.toJson(clients));
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        doGet(request, response);
    }
}

ClientsRepository.java

public class ClientRepository {
    
    public ClientRepository() { }

    public ClientRepository(String path) {
        
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            
            Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/clients_table?" +
                                   "user=*****&password=******");

        } catch (ClassNotFoundException | SQLException e) {
            e.printStackTrace();
        }

    }

    public List listClients() {         
        List clients = new ArrayList();

        try {
            
            PreparedStatement stmt = null;
            
            String query = "SELECT c.id, c.first_name, c.second_name, c.family_name, "
                    + "c.city_born, c.age "
                    + "From clients_db.clients_table c ";               
            
            ResultSet rs = stmt.executeQuery();

            while (rs.next()) {
                
                Client client = new Client();
                
                client.setClientID("setClientID");
                client.setFirstName("setFirstName");
                client.setSecondName("setSecondName");
                client.setLastName("setLastName");
                client.setCityBorn("setCityBorn");
                client.setAge("setAge");
                
                clients.add(client);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }

        // return the result list        
        return clients;
    }
}

DataSourceResult.java

public class DataSourceResult {

    private int Total;
    private List Data;
    public int getTotal() {
        return Total;
    }
    public void setTotal(int total) {
        Total = total;
    }
    public List getData() {
        return Data;
    }
    public void setData(List data) {
        Data = data;
    }       
}

They're using this _repository and getting the data from local .db file. How can I refactor their solution to use MySQL and not local .db file?

user_10606400 · Answer

In your doGet you need to create new object of class ClientRepository and then call your listClients() to get the list of user from database and then back response to ajax.Some of the changes you need make in you servlet :

 ClientRepository  _repository = new ClientRepository();//create object
 List clients = _repository.listClients();//call method
 response.setContentType("application/json");
// convert the list to json and write it to the response
 response.getWriter().print(_gson.toJson(clients));

Then in ClientRepository make below changes :

//to get connection
    public static Connection getConnection() {

     try {
      Class.forName("com.mysql.cj.jdbc.Driver");

      Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/clients_table?" +
       "user=*****&password=******");

     } catch (ClassNotFoundException | SQLException e) {
      e.printStackTrace();
     }
     return conn; //return connection object
    }
    public List  listClients() {
     List  clients = new ArrayList  ();

     try {
      //get connection
      Connection connection = getConnection();
      PreparedStatement stmt = null;

      String query = "SELECT c.id, c.first_name, c.second_name, c.family_name, " +
       "c.city_born, c.age " +
       "From clients_db.clients_table c ";
      stmt = connection.prepareStatement(query);
      ResultSet rs = stmt.executeQuery();

      while (rs.next()) {

       //adding code .. here
            client.setClientID(rs.getInt("id"));
            client.setFirstName(rs.getString("first_name"));
       //and so on..
           
      }
     } catch (SQLException e) {
      e.printStackTrace();
     }

     // return the result list        
     return clients;
    }

KendoUI for JSP - How to connect to MySQL

1 Answers