글 수 52

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

admin

http://webs.co.kr/index.php?document_srl=19583

2013.03.31 13:57:07 (*.160.42.88)

16338

http://wiki4.caucho.com/Java_EE_Servlet/JSP_tutorial_:_Adding_MySQL_and_JDBC_to_bookstore_example

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

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

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 more header called actions
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.

Add links to table headers
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

이 게시물을...

번호	제목	글쓴이	날짜	조회 수
22	What Is VPN?	admin	2013-04-08	15922
21	How VPN Works	admin	2013-04-08	15000
20	PPP Authentication pap spap CHAP MS-CHAP MS-CHAPv2 EAP	admin	2013-04-08	15518
19	Account Policy Settings Windows server	admin	2013-04-08	24911
18	Windows 2003 VPN Server 1 LAN CARD 윈도우 2003 VPN 서버 랜카드 1개일때 설치 방법	admin	2013-04-05	14702
17	How to install VPN Server in Windows 2003	admin	2013-04-05	13303
16	Configure a Windows Server 2003 VPN on the server side	admin	2013-04-05	14515
15	[안드로이드] 서버/클라이언트 소켓(Socket) 통신하기	admin	2013-03-31	44012
	Java EE Servlet/JSP tutorial : Adding MySQL and JDBC to bookstore example	admin	2013-03-31	16338
13	A Simple Java TCP Server and TCP Client	admin	2013-03-31	24577
12	TCP Server and TCP Client in Java	admin	2013-03-31	19566
11	Writing the Server Side of a Socket	admin	2013-03-31	16863
10	Client Server < Java Programming	admin	2013-03-31	14585
9	The ChatMessage class.	admin	2013-03-31	13132
8	Server Program using TCP/IP	admin	2013-03-31	13119
7	Sockets Example JAVA	admin	2013-03-31	14673
6	Source code for client server connection program by socket programming in Java	admin	2013-03-31	14392
5	This application is a simple client-server application which has a Android mobile	admin	2013-03-31	14155
4	Java Socket client server	admin	2013-03-31	14513
3	Thread Pooled Server in Java	admin	2013-03-31	18050
2	Sockets: Basic Client-Server Programming in Java - By Rick Proctor	admin	2013-03-31	15715
1	기본 학습: 소켓 프로그래밍 기본	admin	2013-03-31	13989

쓰기

첫 페이지 1 2 끝 페이지

스마트폰앱

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

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

Contents

Java EE Servlet/JSP tutorial | using JDBC

Install mysql and get mysql jar file driver

Create bookstore database

Create Book table

Configure Resin to use the JDBC driver

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

Move the MySQL jar file where Resin can find it

Create Repository class that implements BookRepository

BookRepositoryJDBCImpl.java

BookRepositoryJDBCImpl.java

JDBC.java

BookEditorServlet : Using @Inject with custom @JDBC

BookListServlet : Using @Inject with custom @JDBC

InMemory.java

Adding a remove feature (remove link on listing)

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

RemoveBookServlet.java

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

book-listing-content.jsp

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

Cookbooks and Tutorials