CRUD with Java, JSP and MySQL

July 22, 2014

One of the most common kind of applications in today's tech/business world is the CRUD system, that is the idea of the user interacting with a database, so the user can Create, Retrieve, Update and Delete (yep, that awful name came from this) data from this database. Also, learning how to create a CRUD with a language is a great way to understand the basics of this language, the basics of software design, engineering and architecture. In layman's term, a CRUD page is just a page where you can insert a thing, edit that thing, search that thing and delete that thing, just that!


Our goal

Well, our scenario will be the simplest possible, so we can understand how to communicate with our database, so, it will be a simple contact book web app where we can do our CRUD with persons. This person will have a name, a phone number and a profession. We'll have a button to add a new person, a form to enter this new person's information, a page where will be loaded all persons from the database and buttons to edit and remove each person



Our Technology Stack

We'll be using Java as our backend technology, Java ServerPages(JSP) as our view technology and MySQL as our relational database. Also, we'll be using the MVC architecture, which is the idea of separating the code by Model, View and Controller, that's a whole other topic to study, you can take a look here, here and here!

For those who doesn't know JSP technology, I highly advice to study just a little bit of it before read this article, as this will be our main view technology, it's a great abstraction over the Servlets technology. It simply helps to process Java code on our pages without putting pure java syntax in the middle of our HTML, which would be extremely ugly and hard to maintain.


The Data Access Object (DAO) pattern

The DAO pattern is a great way to abstract the communication with our database, simply put, the ObjectDAO is a class that will implement the CRUD methods of the Object. This way, our entity class, which is a Java class with only its attributes and their respective getters and setters, won't have to deal directly with the database, you can read more about the DAO pattern here.


This will be the driver that will abstract the connection with the database, it's pretty simple to use and it's the standard of the oracle, you can read more about it here.

Finally, the final view of our scenario


And, for any guidance, here's the project hierarchy:


Creating the Database

Small and simple step, just create the MySQL scheme that we'll use on this project, that is, just a table person, with name, profession and phone. Here's the code:

CREATE TABLE IF NOT EXISTS `smallcrud`.`person` (
  `name` VARCHAR(45) NOT NULL,
  `phone` VARCHAR(45) NOT NULL,
  `profession` VARCHAR(45) NOT NULL,
  PRIMARY KEY (`idperson`))


Creating the model

We'll create the Person, which will be our Java Bean for Person.

Creating the connection class

The connection class will be the responsible for the connection with the database, using the JDBC API, it's fair simple:


!important: These Strings "jdbc:mysql://localhost:3306/simplecrud", "root", "sa" depends on the configuration of the database that you created, the first one is the URI of the database, the second "root" is the user of that database, and the last "sa" is the password of that database

So, what happens when we use this class? Every time we call its method "getConnection" it returns a Connection with the database, that will be useful to create Statements, execute SQL Queries and plenty more.


Creating the PersonDAO

The PersonDAO will be the class that will manipulate the class Person and communicate with the database, sending persons to database, updating persons, retrieving persons from the DB, which means, the CRUD main methods


Creating the Controller

This is the most confusing part. All because of the confusion of not using JSP file to process the request, so what we will use? Servlets!  A Servlet fits better in the Controller role, because they won't show anything to the user, it will only get the request data, verify which action the user is trying to make, do something and redirect the user to another page. If you're using JSP files as controllers, probably you're doing it wrong. So, basically, our PersonController will take the user request, see if the user is trying to edit, insert, update, remove or retrieve... and do the right thing, pay extremely attention to this code

Creating the View: Index.jsp

The index will be pretty simple and straight forward, it will just call our controller, passing the action by the get method, in this case, the ListPerson, the Controller will see that the action required is the ListPerson so it will do what it has to do, get all users from the database and list'em.

Creating the View: ListPerson.jsp

The controller got the action coming from the index and tried to retrieve every person in the database and put all person objects in the session, if there is any Person in the DB, of course. Here is the code to do it, as you can see we’re already using the JSP tags to not use pure Java Scriplets

Creating the View: Person.jsp

This will be the page that the user will be able to insert or edit a Person in the database so it can be loaded on the ListUser.jsp. As the Controller says, if the request has an ID, the Person.jsp will be loaded with a Person, otherwise, it won't. (expecting that the user fill the form to add a new Person to the database)


Last details and conclusion

As you can see, at the first sight this implementation can be a little confusing, mainly because of the flow logic and the controller built with Servlet, understanding the flow means understanding it as a whole. Make sure you understand the MVC logic and the concepts behind JSP, Servlets and JDBC.

If you're trying to copy the whole code, make sure you import the right libs, including the JSTL, responsible for a few tags I've used in this code. Also i used the Twitter's Bootstrap to help me with the frontend UI.

You can download the code here on my GitHub

If there's something wrong with your implementation, please let me know, drop me an email or post a comment here and I'll make sure it will work properly! :]