Create a simple bulletin board with Java + MySQL
I made a simple bulletin board using mysql.
Overview
-Display ID, name, comment, and time stamp on the bulletin board. -If no name or comment is entered, an error message will pop up.
Where I had a hard time
-I didn't know how to retrieve the data stored in the DB. At first I tried to retrieve the ID, name, comment, and timestamp separately in the array, but it didn't work. ArrayList can store an entire instance!
-I didn't know how to retrieve the contents of ArrayList with jsp.
I tried to take it out with the EL method, but it didn't work.
I was able to retrieve it using forEach in JSTL's core tag library.
-I get a time zone error when connecting to mysql. Change the connection URL referring to the following. If you want to use Japan time, change the end to JST. What to do if you get an error in "The server time zone value" when running-app with Grails 3 + MySQL
Source code
model
Board.java
package model;
import java.io.Serializable;
import java.sql.Timestamp;
public class Board implements Serializable {
private int id;
private String name;
private String comment;
private Timestamp time;
public Timestamp getTime() {
return time;
}
public void setTime(Timestamp time) {
this.time = time;
}
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getComment() {
return comment;
}
public void setComment(String comment) {
this.comment = comment;
}
}
FindCommentDAO.java
package model;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
public class FindCommentDAO {
public List<Board> findcomment() {
// id,name,List to store comments
List<Board> list = new ArrayList<>();
final String jdbcId = "id";
final String jdbcPass = "password";
final String jdbcUrl = "jdbc:mysql://localhost:3306/dbname?useUnicode=true&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=JST";
Connection con = null;
try {
con = DriverManager.getConnection(jdbcUrl, jdbcId, jdbcPass);
System.out.println("Connected....");
try {
Statement st = con.createStatement();
String sql = "select * from board";
try {
//Send sql
ResultSet rs = st.executeQuery(sql);
while (rs.next()) {
//Id retrieved from DB,name,set comment to JavaBeans
Board bo = new Board();
bo.setId(rs.getInt("id"));
bo.setName(rs.getString("name"));
bo.setComment(rs.getString("comment"));
bo.setTime(rs.getTimestamp("time"));
//Store one in the list. Elements are added at the end.
list.add(bo);
}
rs.close();
st.close();
} catch (SQLException e) {
e.printStackTrace();
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
//Lost database connection
if (con != null) {
try {
con.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
} catch (SQLException e) {
e.printStackTrace();
System.out.println("Connection Failed.");
return null;
}
return list;
}
}
FindCommentLogic.java
package model;
import java.util.List;
public class FindCommentLogic {
public List<Board> executeFindComment() {
FindCommentDAO fcdao = new FindCommentDAO();
List<Board> list = fcdao.findcomment();
return list;
}
}
AddCommentDAO.java
package model;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
public class AddCommentDAO {
//ID to DB,name,Method to add comment
public AddCommentDAO(Board bo) {
if(bo.getName().isEmpty()) {
bo.setName( "Anonymous");
}
if(bo.getComment().isEmpty()) {
bo.setComment( "no comment");
}
final String jdbcId = "id";
final String jdbcPass = "password";
final String jdbcUrl = "jdbc:mysql://localhost:3306/dbname?useUnicode=true&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=JST";
Connection con = null;
try {
con = DriverManager.getConnection(jdbcUrl, jdbcId, jdbcPass);
System.out.println("Connected....");
try {
PreparedStatement ps = con.prepareStatement("INSERT INTO board (name, comment) VALUES (?, ?)");
ps.setString(1, bo.getName());
ps.setString(2, bo.getComment());
//Send stationery
int r = ps.executeUpdate();
if (r != 0) {
System.out.println(r + "Added writing.");
} else {
System.out.println("Could not write.");
}
ps.close();
} catch (SQLException e) {
e.printStackTrace();
} finally {
//Lost database connection
if (con != null) {
try {
con.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
} catch (SQLException e) {
e.printStackTrace();
System.out.println("Connection Failed.");
}
}
}
AddCommentLogic
package model;
public class AddCommentLogic {
public void executeAddComment(Board bo) {
AddCommentDAO acdao = new AddCommentDAO(bo);
}
}
view
main.jsp
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<script type="text/javascript">
function check(){
var flag = 0;
if(document.form1.name.value == ""){
flag = 1;
}
else if(document.form1.comment.value == ""){
flag = 1;
}
if(flag){
window.alert('Please enter your name and comment');
return false; //Stop sending
}
else{
return true; //Perform send
}
}
</script>
<title>Bulletin board</title>
</head>
<body>
<form action="/board/BoardServlet" method="post" name="form1" onSubmit="return check()">
<p>name:<input type="text" name="name"></p>
<p>comment:<br>
<textarea name="comment" rows="5" cols="40"></textarea>
</p>
<p><input type="submit" value="Send"><input type="reset" value="reset">
</p>
</form>
<c:forEach var="list" items="${listAttribute}">
<p>ID:<c:out value="${list.id}"/>name:<c:out value="${list.name}"/>date:<c:out value="${list.time}"/><br>
<c:out value="${list.comment}"/></p>
</c:forEach>
</body>
</html>
controller
BoardServlet.java
package servlet;
import java.io.IOException;
import java.util.List;
import javax.servlet.RequestDispatcher;
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 javax.servlet.http.HttpSession;
import model.AddCommentLogic;
import model.Board;
import model.FindCommentLogic;
/**
* Servlet implementation class BoardServlet
*/
@WebServlet("/BoardServlet")
public class BoardServlet extends HttpServlet {
private static final long serialVersionUID = 1L;
/**
* @see HttpServlet#HttpServlet()
*/
public BoardServlet() {
super();
// TODO Auto-generated constructor stub
}
/**
* @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
*/
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
//I want to move to the listener class
request.setCharacterEncoding("UTF-8");
//Check existing comments
FindCommentLogic fcl = new FindCommentLogic();
List<Board> list = fcl.executeFindComment();
//Save comment list in session scope
HttpSession session = request.getSession();
session.setAttribute("listAttribute", list);
RequestDispatcher rd =request.getRequestDispatcher("/WEB-INF/jsp/main.jsp");
rd.forward(request, response);
}
/**
* @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response)
*/
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
request.setCharacterEncoding("UTF-8");
//Get the entered value
String name = request.getParameter("name");
String comment = request.getParameter("comment");
//Store in JavaBeans
Board bo = new Board();
bo.setName(name);
bo.setComment(comment);
//Store in mysql
AddCommentLogic acl = new AddCommentLogic();
acl.executeAddComment(bo);
//Get the comment just entered and the existing comment from mysql
FindCommentLogic fcl = new FindCommentLogic();
List<Board> list = fcl.executeFindComment();
//Save comment list in session scope
HttpSession session = request.getSession();
session.setAttribute("listAttribute", list);
RequestDispatcher rd =request.getRequestDispatcher("/WEB-INF/jsp/main.jsp");
rd.forward(request, response);
}
}
Recommended Posts