Редактирование: Java EE MySQL database

In this part of the jee 5 tutorials, we will work with MySQL database.

MySQL is a leading open source database management system. It is a multi user, multithreaded database management system. MySQL is especially popular on the web. Recently, the company that developed MySQL database was bought by Sun.

== Starting and stopping MySQL ==
<source lang="java">
 sudo /etc/init.d/mysql start
</source>

Starting the MySQL database server.

<source lang="java">
 sudo /etc/init.d/mysql stop
</source>

Stopping the MySQL database server.

<source lang="java">
 $ sudo mysqladmin -p ping
 Password:
 Enter password: 
 mysqld is alive
</source>

Checking, if the server is running. We typed two passwords. One for the sudo command and one for the mysqladmin command.

== Creating a database ==
In this example, we will create an empty database. The example consists of two jsp files. The index.jsp and error.jsp.
The index.jsp creates a new database or reports an error in error.jsp file. 

<source lang="java">
<%@ page contentType="text/html;charset=UTF-8" %>
<%@ page errorPage="error.jsp" %>

<%@ page import="java.sql.*" %>

<html>
<head>
<title>MySQL Database creation</title>
<style>
* { font-size: 12px; font-family: Verdana }
</style>
</head>

<body>

<h2>Creation of a books database</h2>

<jsp:declaration>

Statement stmt;
Connection con;
String url = "jdbc:mysql://localhost:3306/";

</jsp:declaration>

<jsp:scriptlet><![CDATA[

Class.forName("com.mysql.jdbc.Driver");
con = DriverManager.getConnection(url, "root", ""); 

stmt = con.createStatement();
stmt.executeUpdate("CREATE DATABASE books");
con.close();

]]></jsp:scriptlet>

</body>
</html>
</source>

This jsp page connects to a server and creates a books database. If we refresh the page again, we get an error message and an error.jsp page is loaded. We cannot create two databases with a same name.

<source lang="java">

 <%@ page errorPage="error.jsp" %>
</source>

If an Exception occurs in page, the server loads an error.jsp page.

<source lang="java">
 <jsp:declaration>

 Statement stmt;
 Connection con;
 String url = "jdbc:mysql://localhost:3306/";

 </jsp:declaration>
</source>

Here we declare a <b>Statement</b> a <b>Connection</b> and a connection url.
The connection url consists of a protocol, host and a port number. The default MySQL port number is 3306.

<source lang="java">
 Class.forName("com.mysql.jdbc.Driver");
</source>

We register a MySQL JDBC driver.

<source lang="java">
 con = DriverManager.getConnection(url, "root", ""); 
</source>

We get a connection to the database for user root with blank password. This is the default  admin account on MySQL. 

<source lang="java">
 stmt = con.createStatement();
 stmt.executeUpdate("CREATE DATABASE books");
</source>

We get a Statement object and execute an SQL query. 

<source lang="java">
 con.close();
</source>

Finally we close the connection.

<source lang="java">

<%@ page contentType="text/html" pageEncoding="UTF-8"%>

<%@ page isErrorPage="true" %>

<html>
<head>
<title>Error page</title>
<style>
    * { font-size: 12px; font-family: Verdana }
</style>

</head>
<body>
<h2>Error occured!</h2>
<p>Message <jsp:expression> exception.getMessage() </jsp:expression>

</body>

</html>
</source>

This is our error page. 

<source lang="java">
 <%@ page isErrorPage="true" %>
</source>

This directive enables an <b>exception</b> object for a page.

<source lang="java">
 <p>Message <jsp:expression> exception.getMessage() </jsp:expression>
</source>

Here we print the error message.

[[image: java_ee_faq_error.png| center]]

<source lang="java">
 mysql> show databases;
 +--------------------+
 | Database           |
 +--------------------+
 | information_schema | 
 | books              | 
 | mysql              | 
 | testdb             | 
 +--------------------+
 4 rows in set (0.12 sec)
</source>

We can check from the mysql command line tool, if the database was created.

== Books ==
The following example will be a more complex application. We will be adding and deleting items.

<source lang="java">

mysql> create table books(id int not null primary key auto_increment, 
              author varchar(30), title varchar(40),
              year int, remark varchar(100));
Query OK, 0 rows affected (0.13 sec)
</source>

We create a books table first. The table will have five columns. The id of the row, author name, title of the book, year of publishing and a small remark conserning the book.

<source lang="java">
mysql> describe books;
+--------+--------------+------+-----+---------+----------------+
| Field  | Type         | Null | Key | Default | Extra          |
+--------+--------------+------+-----+---------+----------------+
| id     | int(11)      | NO   | PRI | NULL    | auto_increment | 
| author | varchar(30)  | YES  |     | NULL    |                | 
| title  | varchar(40)  | YES  |     | NULL    |                | 
| year   | int(11)      | YES  |     | NULL    |                | 
| remark | varchar(100) | YES  |     | NULL    |                | 
+--------+--------------+------+-----+---------+----------------+
5 rows in set (0.22 sec)
</source>

Here we see, how the table looks like using the mysql command line tool.

The example consists of four files. style.css, add.jsp, delete.jsp and BooksWorker.java.

<source lang="java">
* { font-size: 12px; font-family: Verdana }

input { border: 1px solid #ccc }

a#currentTab {
    border-bottom:1px solid #fff;
}

a { color: black; text-decoration:none;
    padding:5px; border: 1px solid #aaa;
}

a:hover { background: #ccc }

td { border: 1px solid #ccc; padding: 3px }
th { border: 1px solid #ccc; padding: 3px; 
    background: #009999; color: white }

.navigator { border-bottom:1px solid #aaa; width:300px; padding:5px }
</source>

The style.css file defines the style of our application. It defines the look and feel of input boxes, buttons, tables and anchors. It also creates a navigation toolbar.

<source lang="java">
<%@page contentType="text/html" pageEncoding="UTF-8"%>

<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>Books database</title>
<link rel="stylesheet" href="style.css" type="text/css">
</head>
<body>

<br>

<div class="navigator">
<a id="currenttab" href="index.jsp">Add</a>
<a href="delete.jsp">Delete</a>
</div>
<%
  String author = request.getParameter("author");
  String title = request.getParameter("title");
  String year = request.getParameter("year");
  String remark = request.getParameter("remark");
  if (author != null && title != null 
          && year != null && remark!= null) {
      com.zetcode.BooksWorker.Insert(author, title, year, remark);
  }
%>

<br> <br> <br>

<form method="post" action="index.jsp">
<table>
<tr>    
<td>Author</td><td><input type="text" name="author"></td>

</tr>
<tr>
<td>Title</td><td><input type="text" name="title"></td>
</tr>
<tr>
<td>Year</td><td> <input type="text" name="year"></td>

</tr>
<tr>
<td>Remark</td><td> <input type="text" name="remark"></td>
</tr>
</table>

<br>
<input type="submit" value="submit">
</form>
</body>
</html>

</source>

The index.jsp file is used to add new books into the database. It defines the form, where we input data.
Upon clicking submit button, it calles itself. 

<source lang="java">
<%
  String author = request.getParameter("author");
  String title = request.getParameter("title");
  String year = request.getParameter("year");
  String remark = request.getParameter("remark");
  if (author != null && title != null 
          && year != null && remark!= null) {
      com.zetcode.BooksWorker.Insert(author, title, year, remark);
  }
%>
</source>

This scriptlet will get the parameters from the request. If none of them is null, we call the Insert method of the <b>BooksWorker</b> class. We put all database related coding into a new class BooksWorker. One of the adopted principles in creating web applications is to divide the business logic from it's presentation layer as much as possible.

<source lang="java">
<%@page contentType="text/html" pageEncoding="UTF-8"%>
<%@page import="java.util.*" %>


<html>
<head>

<title>Delete</title>
<link rel="stylesheet" href="style.css" type="text/css">
        
</head>
<body>
<%
  Enumeration names = request.getParameterNames();
  while (names.hasMoreElements()) {
      String name = (String) names.nextElement();
      StringBuffer sb = new StringBuffer(name);
      sb.deleteCharAt(0);
      com.zetcode.BooksWorker.Delete(sb.toString());
  }

%>
<br>

<div class="navigator">

<a href="index.jsp">Add</a>
<a id="currenttab" href="delete.jsp">Delete</a>
</div>

<br> <br> <br>

<form action="delete.jsp" method="post">
<table>
<tr>
<th>Author</th>
<th>Title</th>
<th>Year</th>

<th>Remark</th>
</tr> 
<%

  List list = com.zetcode.BooksWorker.GetBooks();
  int id = 0;
  String box = null;

  Iterator<String> it = list.iterator();

  while (it.hasNext()) {
      id = Integer.parseInt(it.next());
      out.print("<tr>");
      for (int i = 0; i < 4; i++) {
          out.print("<td>");
          out.print(it.next());
          out.print("</td>");
  }
  out.print("<td>");
  box = "<input name=r" + id + " type='checkbox'>";
  out.print(box);
  out.print("</td>");
  out.print("</tr>");
 }
%>

 
</table>
 
<br>
<input type="submit" value="Delete">

</form>
 
</body>
</html>
</source>

The delete.jsp file does two things. It deletes selected items from the database table and shows a list of available books. 

<source lang="java">
<%
  Enumeration names = request.getParameterNames();
  while (names.hasMoreElements()) {
      String name = (String) names.nextElement();
      StringBuffer sb = new StringBuffer(name);
      sb.deleteCharAt(0);
      com.zetcode.BooksWorker.Delete(sb.toString());
  }
%>
</source>

This scriplet retrieves all parameters from the request. From the selected check boxes we receive values r1, r2 ... rn.
Where the r indicates a row and the number is the id of the row in the database table. We get the number by calling the <b>deleteCharAt()</b> method and call the <b>Delete()</b> method of the BooksWorker class.

<source lang="java">
<%

  List list = com.zetcode.BooksWorker.GetBooks();
  int id = 0;
  String box = null;

  Iterator<String> it = list.iterator();

  while (it.hasNext()) {
      id = Integer.parseInt(it.next());
      out.print("<tr>");
      for (int i = 0; i < 4; i++) {
          out.print("<td>");
          out.print(it.next());
          out.print("</td>");
  }
  out.print("<td>");
  box = "<input name=r" + id + " type='checkbox'>";
  out.print(box);
  out.print("</td>");
  out.print("</tr>");
 }
%>
</source>

The second scriptlet builds the table, that displays all items. 
We get all the data by calling the <b>GetBooks()</b> method. Next we parse the data and build a table.

<source lang="java">
package com.zetcode;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;


public class BooksWorker {

  static final String url = "jdbc:mysql://localhost:3306/books";

  public static void Insert(String author, String title, 
          String year, String remark) {
      try {

          String insert = "INSERT INTO books(author, title, year, remark)" +
                  "VALUES (?, ?, ?, ?)";

          Class.forName("com.mysql.jdbc.Driver");
          Connection con = DriverManager.getConnection(url, "root", "");
 
          PreparedStatement ps = con.prepareStatement(insert);

          ps.setString(1, author);
          ps.setString(2, title);
          ps.setString(3, year);
          ps.setString(4, remark);
          ps.executeUpdate();
          con.close();

      } catch (Exception ex) {
          Logger.getLogger(BooksWorker.class.getName()).log(
                           Level.SEVERE, null, ex);
      }
  }

  public static List GetBooks() {

      List<String> list = new ArrayList<String>();

      try {

          Class.forName("com.mysql.jdbc.Driver");
          Connection con = DriverManager.getConnection(url, "root", "");

          Statement stmt = con.createStatement();

          ResultSet result = stmt.executeQuery("SELECT * FROM books");

          while(result.next())
          {
             list.add(result.getString("id"));
             list.add(result.getString("author"));
             list.add(result.getString("title"));
             list.add(result.getString("year"));
             list.add(result.getString("remark"));
          } 

          con.close();

      } catch (Exception ex) {
          Logger.getLogger(BooksWorker.class.getName()).log( 
                           Level.SEVERE, null, ex);
      }
          return list;
  }

  public static void Delete(String id) {
      try {
          String delete = "DELETE from books WHERE id = ?";

          Class.forName("com.mysql.jdbc.Driver");
          Connection con = DriverManager.getConnection(url, "root", "");
          PreparedStatement ps = con.prepareStatement(delete);

          ps.setString(1, id);
          ps.executeUpdate();
          con.close();

      } catch (Exception ex) {
          Logger.getLogger(BooksWorker.class.getName()).log( 
             Level.SEVERE, null, ex);
      }
  }
}

</source>

We put all the database coding into the BooksWorker class, thus separating model from the view. Here we define methods for inserting data to the database, deleting data and getting all data from the table.

<source lang="java">
 String insert = "INSERT INTO books(author, title, year, remark)" +
        "VALUES (?, ?, ?, ?)";
 ...
 PreparedStatement ps = con.prepareStatement(insert);
 ps.setString(1, author);
 ps.setString(2, title);
 ps.setString(3, year);
 ps.setString(4, remark);
 ps.executeUpdate();
</source>

We use prepared statements. These are <b>parameterized SQL statements</b>. The statements use question mark (?) as a marker character for passing the parameters. There are two major reasons to use parameterized statements.
Securing the code against the SQL injections and easier coding due to difficulties with double and single quotes. 

<source lang="java">
 } catch (Exception ex) {
     Logger.getLogger(BooksWorker.class.getName()).log(
                      Level.SEVERE, null, ex);
 }
</source>

If there is some exception, we log a message about it. The messages can be viewed in a log file, which is located in domains/domain1/logs/serve.log file of the glassfish root directory. 

We have put most of the business code of the application outside the JSP files. But there is still some code left, which would be better to put elsewhere. We will manage this later, when we will talk about <b>servlets</b> and <b>custom jsp tags</b>.

=== Using ant to build and deploy the web application. ===

[[image: java_ee_faq_add.png| center]]

[[image: java_ee_faq_delete.png| center]]

[[Категория:Java]]