Java EE Servlet/JSP tutorial : Adding MySQL and JDBC to bookstore example

From Resin 4.0 Wiki

Jump to: navigation, search

This tutorial is part of Java EE Tutorial covering JSP_2.2, and Servlets 3.0.


Contents

Java EE Servlet/JSP tutorial | using JDBC

In the real world, you use real databases. It is time to move from our Java collection database simulation and use a real database.

The first part of this tutorial just shows how to use a database from JNDI. It shows how to install the JDBC jar files and how to configure Resin datasources.

Install mysql and get mysql jar file driver

First let's install MySQL and create a user called developer with a password. All isntall processes make you create a password for root, make sure you remember this password.


To get started, you really need to install mysql. To install mysql on Windows go here MySQL windows installation. To install mysql on Mac OSX go here Mac OSX installation.


To install MySQL on RedHat or CentOS just do this: 

$ sudo yum install mysql-server 
$ sudo yum install mysql 
$ sudo yum install mysql-devel
$ sudo yum install mysql-connector-java

This puts the jar file in /usr/share/java/mysql-connector-java.jar.

To install MySQL on Ubuntu or Debian just do this: 

 $ sudo apt-get install mysql-server
 $ sudo apt-get install libmysql-java

This also puts the jar file in /usr/share/java/mysql-connector-java.jar.

If you are not using RedHat, CentOS, Ubuntu or Debian, you can get the jar file this way:

Download and unpack the MySQL Jar file.


http://dev.mysql.com/downloads/connector/j/5.1.html

Unpack it: 

$ cd ~
$ cd Downloads/
$ tar xzvf mysql-connector-java-5.1.19.tar.gz


Create bookstore database

Then let's create a database called bookstore.


Startup MySQL client tool to create a new user and database: 

 $ mysql -u root -p

Next create a user called developer with the pass javaee6!. 

mysql> GRANT ALL PRIVILEGES ON *.* TO 'developer'@'%' IDENTIFIED BY 'javaee6!' WITH GRANT OPTION;

Output:
Query OK, 0 rows affected (0.00 sec)

Next create a database called bookstore. 

mysql> create database bookstore;

Output:
Query OK, 1 row affected (0.00 sec)

Use bookstore database and create a table called book. 


mysql> use bookstore;

Output:
Database changed

Create Book table

Create a table called book. 

mysql> CREATE TABLE book (id INT NOT NULL PRIMARY KEY AUTO_INCREMENT, 
        title VARCHAR(80), description VARCHAR(2000), pubdate DATE, price DECIMAL);

Output:
Query OK, 0 rows affected (0.01 sec)

Exit the MySQL client. 

mysql> exit;

Output:
Bye

Configure Resin to use the JDBC driver

If you are following along in Eclipse then you should have a Resin entry under Servers "Servers/Resin 4.0 at localhost-config".

This corresponds to a directory on the file system on my box this is under

/home/rick/workspace/javaee/Servers/Resin 4.0 at localhost-config


Modify resin.xml and add the following database entry tag

~/workspace/javaee/Servers/"Resin 4.0 at localhost-config"/resin.xml


<resin xmlns="http://caucho.com/ns/resin"
       xmlns:resin="urn:java:com.caucho.resin">
       
  <class-loader>
    <tree-loader path="/home/rick/resin-pro-4.0.27/resin-inf"/>
  </class-loader>     
       
  <database jndi-name="jdbc/bookstore">
        <driver type="com.mysql.jdbc.Driver">
                 <url>jdbc:mysql://localhost:3306/bookstore</url>
                 <user>developer</user>
                 <password>javaee6!</password>
         </driver>
 </database>
 
 ...

If you are not using Eclipse, then modify the resin.xml file where ever you installed Resin. (On a standard Unix/Linux install this is under /etc/conf/resin/resin.xml).

If you used Eclipse to install Resin runtime and you did not change the defaults then Resin is installed under your home directory ~/resin-pro-4.0.27 (/home/rick/resin-pro-4.0.27).

Move the MySQL jar file where Resin can find it

$ mkdir ~/resin-pro-4.0.27/resin-inf
$ cp /usr/share/java/mysql-connector-java.jar ~/resin-pro-4.0.27/resin-inf/

Depending how you installed the jar file it might be in a different location, make sure that you copy it to directory ~/resin-pro-4.0.27/resin-inf. (Your version number will vary.)

Create Repository class that implements BookRepository

Create a Repository class (DAO) that implements the BookRepository interface.

As follows:

BookRepositoryJDBCImpl.java 

package com.bookstore;

import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.List;

import javax.annotation.Resource;
import javax.enterprise.context.ApplicationScoped;
import javax.sql.DataSource;
import java.sql.Date;

@ApplicationScoped @JDBC
public class BookRepositoryJDBCImpl implements BookRepository {
	
	@Resource(name="jdbc/bookstore")
	private DataSource dataSource;
	
	private SimpleDateFormat dateFormat = new SimpleDateFormat("MM/dd/yyyy");

	@Override
	public Book lookupBookById(final String id) {
		return withDB(new JDBCRunner<Book>(){
			@Override
			public Book run(Connection connection) throws Exception {
				
				PreparedStatement prepareStatement = connection.prepareStatement("select * from book where id = ?");
				prepareStatement.setLong(1, Long.parseLong(id));
				ResultSet rs = prepareStatement.executeQuery();
						
				if (rs.next()) {
					Book book = new Book();
					book.setId("" + rs.getLong("id"));
					book.setPrice(rs.getBigDecimal("price"));
					book.setPubDate(rs.getDate("pubdate"));
					book.setTitle(rs.getString("title"));
					return book;
				} else {
					return null;
				}
				
			}});	
	}

	@Override
	public void addBook(final String title, final String description, final String price,
			final String pubDate) {
		withDB(new JDBCRunner<Book>(){
			@Override
			public Book run(Connection connection) throws Exception {
				
				PreparedStatement prepareStatement = connection.prepareStatement("insert into book (title, description, price, pubdate) values (?,?,?,?)");
				prepareStatement.setString(1, title);
				prepareStatement.setString(2, description);
				prepareStatement.setBigDecimal(3, new BigDecimal(price));
				
				Calendar calendar = Calendar.getInstance();
				calendar.setTime(dateFormat.parse(pubDate));
				prepareStatement.setDate(4, new Date(calendar.getTimeInMillis()));
				
				
				int rowCount = prepareStatement.executeUpdate();
				if (rowCount!=1) {
					throw new BookStoreException("Unable to insert book into bookstore");
				}
				return null;
			}});	

	}

	@Override
	public void updateBook(final String id, final String title, final String description,
			final String price, final String pubDate) {
		withDB(new JDBCRunner<Book>(){
			@Override
			public Book run(Connection connection) throws Exception {
				
				PreparedStatement prepareStatement = connection.prepareStatement("update book set  title=?, description=?, price=?, pubdate=? where id = ?");
				prepareStatement.setString(1, title);
				prepareStatement.setString(2, description);
				prepareStatement.setBigDecimal(3, new BigDecimal(price));
				
				Calendar calendar = Calendar.getInstance();
				calendar.setTime(dateFormat.parse(pubDate));
				prepareStatement.setDate(4, new Date(calendar.getTimeInMillis()));
				
				prepareStatement.setLong(5, Long.parseLong(id));
				
				int rowCount = prepareStatement.executeUpdate();
				if (rowCount!=1) {
					throw new BookStoreException("Unable to update book into bookstore");
				}
				return null;
			}});	

	}

	@Override
	public void removeBook(final String id) {
		withDB(new JDBCRunner<Book>(){
			@Override
			public Book run(Connection connection) throws Exception {
				
				PreparedStatement prepareStatement = connection.prepareStatement("delete from book where id = ?");
				prepareStatement.setLong(1, Long.parseLong(id));
				
				int rowCount = prepareStatement.executeUpdate();
				if (rowCount!=1) {
					throw new BookStoreException("Unable to remove book from bookstore");
				}
				return null;
			}});	

	}
	

	@Override
	public List<Book> listBooks() {
		return doList(null);
	}

	@Override
	public List<Book> listBooksSortByPriceAsc() {
		return doList("price ASC");
	}

	@Override
	public List<Book> listBooksSortByTitleAsc() {
		return doList("title ASC");
	}

	@Override
	public List<Book> listBooksSortByTitleDesc() {
		return doList("title DESC");
	}

	@Override
	public List<Book> listBooksSortByPriceDesc() {
		return doList("price DESC");
	}

	@Override
	public List<Book> listBooksSortByPubDateDesc() {
		return doList("pubDate DESC");
	}

	@Override
	public List<Book> listBooksSortByPubDateAsc() {
		return doList("pubDate ASC");
	}
	
	
	static interface JDBCRunner <T> {
		T run(Connection connection) throws Exception;
	}

	private List<Book> doList(final String orderBy) {
		return withDB(new JDBCRunner<List<Book>>(){
			@Override
			public List<Book> run(Connection connection) throws Exception {
				List<Book> listing = new ArrayList<Book>();				
				Statement statement = connection.createStatement();
				final String query = "select * from book" + 
						(orderBy != null ? " ORDER BY " + orderBy + ";" : ";");
				ResultSet rs = statement.executeQuery(query);
				while (rs.next()) {
					Book book = new Book();
					book.setId("" + rs.getLong("id"));
					book.setPrice(rs.getBigDecimal("price"));
					book.setPubDate(rs.getDate("pubdate"));
					book.setTitle(rs.getString("title"));
					listing.add(book);
				} 
				return listing;
			}});		
	}

	private  <T> T withDB(JDBCRunner<T> runner) {
		Connection connection = null;
		try {
			connection = dataSource.getConnection();
			boolean auto = connection.getAutoCommit();
			connection.setAutoCommit(false);
			T result = runner.run(connection);
			connection.commit();
			connection.setAutoCommit(auto); //set it to what it was previously.
			return result;
		}catch (Exception ex) {
			try {
				connection.rollback();
			} catch (SQLException e) {
				//should log this as a warn or info
			}
			throw new BookStoreException(ex);
		} finally {
			if (connection!=null) {
				try {
				connection.close();
				} catch (Exception ex) {
					//should log this as a warn or info
				}
			}
		}
	}


}


Notice that this is similar to what we have done before except now of course we are using JDBC.

Let's walk through this step by step

BookRepositoryJDBCImpl.java 

@ApplicationScoped @JDBC
public class BookRepositoryJDBCImpl implements BookRepository {
	
	@Resource(name="jdbc/bookstore")
	private DataSource dataSource;
...


We can inject the dataSource with the @Resource tag specifying the JNDI name that we configure in resin.xml earlier. Also notice that we defined a new annotation called @JDBC. This allows us to inject the right repository into the Servlets as follows:


JDBC.java 

package com.bookstore;

import java.lang.annotation.Retention;
import java.lang.annotation.Target;
import static java.lang.annotation.ElementType.*;
import static java.lang.annotation.RetentionPolicy.*;

import javax.inject.Qualifier;


@Qualifier @Retention(RUNTIME) @Target({TYPE, METHOD, FIELD, PARAMETER})
public @interface JDBC {
}


Now we can inject into the Servlets as follows:

BookEditorServlet : Using @Inject with custom @JDBC 

...
@WebServlet("/book")
public class BookEditorServlet extends HttpServlet {

	@Inject  @JDBC
	private BookRepository bookRepo;
...


BookListServlet : Using @Inject with custom @JDBC 

...
@WebServlet("/book/")
public class BookListServlet extends HttpServlet {
	
	@Inject  @JDBC
	private BookRepository bookRepo;
...

The rest of the code stays the same. You could even create a special annotation just for injecting the in memory, Java collection version of the Repository (I renamed it from BookRepositoryImpl to BookRepositoryCollectionsImpl) as follows:

InMemory.java 

package com.bookstore;

import java.lang.annotation.Retention;
import java.lang.annotation.Target;
import static java.lang.annotation.ElementType.*;
import static java.lang.annotation.RetentionPolicy.*;

import javax.inject.Qualifier;


@Qualifier @Retention(RUNTIME) @Target({TYPE, METHOD, FIELD, PARAMETER})
public @interface InMemory {
}

See Java EE 6 : CDI tutorial for more details on how this @Qualifier business works.

Just replace @Inject @JDBC with @Inject @InMemory and you should be all set to use the in memory version. Later when we change the example to show how to use MongoDB, we will use the @Alternative annotation.

Since we have JDBC all setup, let's exercise it a bit more by adding a remove feature and sortable headers.


Adding a remove feature (remove link on listing)

Update the book-list-content.jsp and add the following:

  1. 1 more header called actions
  2. A link to /book/remove

/WEB-INF/book-list-content.jsp 

<table class="listing">
	<tr>
	    ...
		<th>Action</th>
	</tr>

	<c:forEach var="book" items="${books}" varStatus="status">
		    ...
			<td>
			 	<a href="${pageContext.request.contextPath}/book/remove?id=${book.id}">remove</a>
			</td>
			...
</table>

Now we need to add a Servlet that can handle this remove action as follows:

RemoveBookServlet.java 

package com.bookstore.web;

import java.io.IOException;

import javax.inject.Inject;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import com.bookstore.BookRepository;
import com.bookstore.JDBC;

@WebServlet("/book/remove")
public class RemoveBookServlet extends HttpServlet {

	@Inject @JDBC
	private BookRepository bookRepo;
	
	

	/** Prepare the book form before we display it. */
	protected void doGet(HttpServletRequest request,
			HttpServletResponse response) throws ServletException, IOException {
		String id = request.getParameter("id");
		
		bookRepo.removeBook(id);

		/* Redirect to book-form. */
		getServletContext().getRequestDispatcher("/book/").forward(
				request, response);

	}
}

Making sortable headers (links to sort by field on headers)

At the top of the listing page (book-listing-content.jsp), we want to add links to sort ascending or descending for title, price and publication date.

  1. Add links to table headers
  2. Modify BookListServlet to handle sort links from headers of book listing

book-listing-content.jsp 

...
<table class="listing">
	<tr>
		<th>Title 
			<span class="sortLink"> [
		  		<a href="${pageContext.request.contextPath}/book/?order=asc&field=title">asc</a>|
				<a href="${pageContext.request.contextPath}/book/?order=desc&field=title">desc</a>]
			</span>
		</th>
		<th>Description</th>
		<th>Price 
			<span class="sortLink"> [
				<a href="${pageContext.request.contextPath}/book/?order=asc&field=price">asc</a>|
				<a href="${pageContext.request.contextPath}/book/?order=desc&field=price">desc</a>]
			</span>
		</th>
		<th>Publication Date 
			<span class="sortLink"> [
				<a href="${pageContext.request.contextPath}/book/?order=asc&field=pubDate">asc</a>|
		    	<a href="${pageContext.request.contextPath}/book/?order=desc&field=pubDate">desc</a>]
			</span>
		</th>
...

Now that we have the links that pass in field name and order operation parameters (ascending or descending), we can update BookListServlet.

BookListServlet.java updated to handle sortable columns from book-listing 

package com.bookstore.web;

import java.io.IOException;
import java.util.List;

import javax.inject.Inject;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import com.bookstore.Book;
import com.bookstore.BookRepository;
import com.bookstore.JDBC;

@WebServlet("/book/")
public class BookListServlet extends HttpServlet {
	
	@Inject  @JDBC
	private BookRepository bookRepo;

	protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		String field = request.getParameter("field");
		String order = request.getParameter("order");
		
		List<Book> books = null;
		
		if ("asc".equals(order) && "title".equals(field)) {
			books = bookRepo.listBooksSortByTitleAsc();
		} else if ("desc".equals(order) && "title".equals(field)) {
			books = bookRepo.listBooksSortByTitleDesc();
		} else if ("asc".equals(order) && "price".equals(field)) {
			books = bookRepo.listBooksSortByPriceAsc();
		} else if ("desc".equals(order) && "price".equals(field)) {
			books = bookRepo.listBooksSortByPriceDesc();
		} else if ("asc".equals(order) && "pubDate".equals(field)) {
			books = bookRepo.listBooksSortByPubDateAsc();
		} else if ("desc".equals(order) && "pubDate".equals(field)) {
			books = bookRepo.listBooksSortByPubDateDesc();
		} else {
			books = bookRepo.listBooks();			
		}
		
		request.setAttribute("books", books);			

		getServletContext().getRequestDispatcher("/WEB-INF/pages/book-list.jsp").forward(request, response);
	}

}


Cookbooks and Tutorials

Retrieved from "http://wiki4.caucho.com/index.php?title=Java_EE_Servlet/JSP_tutorial_:_Adding_MySQL_and_JDBC_to_bookstore_example&oldid=570"
Personal tools
TOOLBOX
LANGUAGES