This post is a part of the Servlet to Spring series.

In the first post of our build shopping cart the hard way series, we added a simple listing page of wines. In this series we would add persistence so that you would have capability to add more wines to sell. For this you can use any database, however to avoid additional steps of installing a database, we would go ahead and use a in memory database h2. You can download it from the website but since we would be using it in embedded mode, we would add it as a part of our maven dependency.

If you have been following our series, we would start from the completed code in last post, you can check out our last post JSP and Servlets or you can download the completed application from last post at https://github.com/nitizkumar/shopping-cart-jsp-servlet.

Add the following snippet to the dependency section of the pom.xml

<dependency>
<groupId>com.h2database</groupId>
<artifactId>h2</artifactId>
<version>1.4.200</version>
</dependency>

Lets start by adding a Wine Dao which will extract a list of wines from our database. But first we need to set up h2 database and define our database structure. We can use eclipse database perspective and add h2 connection profile, if you do not find h2 in db types, just add generic database type.

Now that we have our database setup, we can run some script to create tables. We will not be discussing the database details in this series, feel free to checkout my understanding database series on the blog which goes into very basic and fundamentals of database. We will be using following script to create a wine_details table and populate it.

CREATE TABLE WINE_DETAIL (
WINE_ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
WINE_NAME VARCHAR(500) NOT NULL,
PRICE DECIMAL (10,2) NOT NULL
)

INSERT INTO WINE_DETAIL(WINE_NAME,PRICE) VALUES ('Chardonnay White',629);
INSERT INTO WINE_DETAIL(WINE_NAME,PRICE) VALUES ('Sauvignon Blanc',529);
INSERT INTO WINE_DETAIL(WINE_NAME,PRICE) VALUES ('Chennin Blanc',429);

Now our database is setup, we would now a java class to fetch the wine details from database, so lets add a WineDao class to connect and fetch the wine details.

public class WineDao {

	public List getListOfWines() {
		ArrayList listOfWine = new ArrayList<>();
		try {
			DriverManager.registerDriver(new Driver());
			Connection connection = null;
			Statement stmt = null;
			ResultSet resultSet = null;
			try {
				connection = DriverManager.getConnection("jdbc:h2:~/test", "sa", "");

				stmt = connection.createStatement();

				resultSet = stmt.executeQuery("SELECT WINE_ID,WINE_NAME,PRICE FROM WINE_DETAIL");

				while (resultSet.next()) {
					WineDetail detail = new WineDetail();
					detail.setId(resultSet.getInt("WINE_ID"));
					detail.setName(resultSet.getString("WINE_NAME"));
					detail.setPrice(resultSet.getDouble("PRICE"));
					listOfWine.add(detail);
				}
			} finally {
				resultSet.close();
				stmt.close();
				connection.close();
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}

		return listOfWine;
	}
}

Lets look at these code in details. To start with we have a code to register driver

DriverManager.registerDriver(new Driver());

Lot of example you may have seen would do the same thing by using class.forName of the driver class, I have intentionally used a explicit registration to explain why we need to introduce class dependency of the driver. Essentially when you do following.

Class.forName("org.h2.Driver");

It would do the driver registration through a static initializer block, for example following is the code in H2 Driver.

static {
        load();
    }
public static synchronized Driver load() {
        try {
            if (!registered) {
                registered = true;
                DriverManager.registerDriver(INSTANCE);
            }
        } catch (SQLException e) {
            DbException.traceThrowable(e);
        }
        return INSTANCE;
    }

You can look at the full code at Driver.java on github. This lets DriverManager know that there is a driver available, when you eventually call getConnection, DriverManager is going to call every registered driver to ask if they can understand the url and make a connection, which is why you would always see the jdbc url of patter jdbc:DATABASE:url, the database identifier is used by driver to know if we are connecting to their database or some other database. Once we get the connection, we are creating a statement and executing it, execution of a query is going to return a result set object, which is essentially a paginated view of connected connection to database. We can use it to move forward or backward and iterate over the row of data which is selected by the query.

stmt = connection.createStatement();
resultSet = stmt.executeQuery("SELECT WINE_ID,WINE_NAME,PRICE FROM WINE_DETAIL");

As we have mentioned above the statement and result set are still using open connection, we need to close all the resource, including statement, resultset and connection. It seems to be a lot of boiler plate code to run all the try catch block. Fortunately since Java 7 we can use try with resource to simplify the code. Lets look at the simplified version.

public List getListOfWines() {
	ArrayList listOfWine = new ArrayList<>();
	try {
		DriverManager.registerDriver(new Driver());
		try (Connection connection = DriverManager.getConnection("jdbc:h2:~/test", "sa", "")) {

		try (Statement stmt = connection.createStatement()) {

			try (ResultSet resultSet = stmt.executeQuery("SELECT WINE_ID,WINE_NAME,PRICE FROM WINE_DETAIL")) {

				while (resultSet.next()) {
					WineDetail detail = new WineDetail();
					detail.setId(resultSet.getInt("WINE_ID"));
					detail.setName(resultSet.getString("WINE_NAME"));
					detail.setPrice(resultSet.getDouble("PRICE"));
					listOfWine.add(detail);
				}
			}
			}
		}
	} catch (SQLException e) {
		e.printStackTrace();
	}
	return listOfWine;
}

Looks a bit cleaner, right? but we still have too many try. Lets try to combine a few of them and simplify it further.

public List getListOfWines() {
	ArrayList listOfWine = new ArrayList<>();
	try {
		DriverManager.registerDriver(new Driver());
		try (Connection connection = DriverManager.getConnection("jdbc:h2:~/test", "sa", "");
				Statement stmt = connection.createStatement();
				ResultSet resultSet = stmt.executeQuery("SELECT WINE_ID,WINE_NAME,PRICE FROM WINE_DETAIL")) {

			while (resultSet.next()) {
				WineDetail detail = new WineDetail();
				detail.setId(resultSet.getInt("WINE_ID"));
				detail.setName(resultSet.getString("WINE_NAME"));
				detail.setPrice(resultSet.getDouble("PRICE"));
				listOfWine.add(detail);
			}
		}
	} catch (SQLException e) {
		e.printStackTrace();
	}
	return listOfWine;
}

Now we are done with our Wine data access layer aka DAO, lets change our servlet to use the wine dao.

public class IndexServlet extends HttpServlet {
	
	private static final long serialVersionUID = 1L;

	private static List wines = new ArrayList<>();
	
	WineDao dao = new WineDao();
	
	/**
	 * @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
	 */
	protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		List listOfWines = dao.getListOfWines();
		request.setAttribute("wines", listOfWines);
		request.getRequestDispatcher("wines.jsp").forward(request, response);
	}

}

Lets change our jsp to include the price along with name of wine.

<c:forEach items="${wines}" var="wine">          
          <div class="col-lg-4 mb-5 col-md-6">

            <div class="wine_v_1 text-center pb-4">
              <a href="shop-single.html" class="thumbnail d-block mb-4"><img src="images/wine_2.png" alt="Image" class="img-fluid"></a>
              <div>
                <h3 class="heading mb-1"><a href="#">${wine.name}</a></h3>
                <span class="price">$${wine.price}</span>
              </div>
              

              <div class="wine-actions">
                  
                <h3 class="heading-2"><a href="#">${wine}</a></h3>
                <span class="price d-block">$${wine.price}</span>                
                <div class="rating">
                  <span class="icon-star"></span>
                  <span class="icon-star"></span>
                  <span class="icon-star"></span>
                  <span class="icon-star"></span>
                  <span class="icon-star-o"></span>
                </div>
                
                <a href="#" class="btn add"><span class="icon-shopping-bag mr-3"></span> Add to Cart</a>
              </div>
            </div>

          </div>
          
</c:forEach>

Now if you run the code and see, you should see that not only we have the names of the wine but the price of the wine also.

Great job folks. In the next post we would look at coupling and dependency injection. The full source is available on github at https://github.com/nitizkumar/shopping-cart-jdbc , feel free to download and play around with the code.


Author

Nitiz

There are currently no comments.

Bitnami