1
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

Java JSP,サーブレット MVCモデル。とりあえず、DB接続、SELECTで一覧表示。

Posted at

■ディレクトリ構成

スクリーンショット (110).png

・表示画面
スクリーンショット (111).png

■DB接続用(データソース)

・context.xml

.xml context.xml
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE Context>
<Context reloadable="true">
	<Resource name="jdbc/searchman"
		auth="Container"
		type="javax.sql.DataSource"
		driverClassName="com.mysql.cj.jdbc.Driver"
		url="jdbc:mysql://localhost:3306/company?serverTimezone=Asia/Tokyo&amp;characterEncoding=UTF-8"
		username="suser"
		password="spass"
		maxTotal="100"
		maxIdle="30"
		maxWaitMillis="10000"
		validationQuery="SELECT 0"
	/>
</Context>

・ConnectionBase.java(model)

ConnectionBase.java
package model;

import java.sql.Connection;
import java.sql.SQLException;

import javax.naming.Context;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.sql.DataSource;

public class ConnectionBase {
	
	public static Connection getConnection() throws SQLException, NamingException {
		String localName = "java:comp/env/jdbc/searchman";
		// コンテキストの生成
		Context context = new InitialContext();
		// コンテキストを検索
		DataSource ds = (DataSource) context.lookup(localName);
		// データベースへ接続
		Connection con = ds.getConnection();
		return con;
	}
}

■MVC

●M モデル(model)

ShainLogic.java
package model;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import javax.naming.NamingException;

import beans.ShainBean;

public class ShainLogic {
	
	
	/*
	 * SELECT 全社員 検索
	 */
	public ArrayList<ShainBean> getAllShain() throws SQLException, NamingException {
		
		ArrayList<ShainBean> shainList = new ArrayList<ShainBean>();
		
		// 社員を取得する SQL
		String sql = "SELECT id, name, sei, nen, address FROM shain";
		
		// SQL 実行
		try (Connection con = ConnectionBase.getConnection();
				PreparedStatement pstmt = con.prepareStatement(sql);) {
			
			// SQL文を表示
			System.out.println(pstmt.toString());
			
			// SQL 実行
			ResultSet rs = pstmt.executeQuery();
			
			// 取得した行数を繰り返す
			while(rs.next()) {
				
				// 初期化
				ShainBean shainBean = new ShainBean();
				
				// 値を格納
				shainBean.setId(rs.getInt("id"));
				shainBean.setName(rs.getString("name"));
				shainBean.setSei(rs.getString("sei"));
				shainBean.setNen(rs.getInt("nen"));
				shainBean.setAddress(rs.getString("address"));
				
				// リストに追加
				shainList.add(shainBean);
				
				// 値を出力
				/*
				System.out.println(rs.getInt("id"));
				System.out.println(rs.getString("name"));
				System.out.println(rs.getString("sei"));
				System.out.println(rs.getInt("nen"));
				System.out.println(rs.getString("address"));
				System.out.println("----------");
				*/
			}
		}
		
		return shainList;
		
	}

}

●V ビュー(view)

index.jsp
<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
   
 <%@page import="java.util.ArrayList"%>
 <%@page import="beans.*" %> 
    
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>社員一覧</title>
<style>
table {
border-collapse: collapse; /* セルの境界線を重ねて単線にする */
}

th, td {
border: 1px solid black; /* セルの境界線のスタイルを設定 */
padding: 10px;
}
</style>
</head>
<body>
<h1>社員一覧</h1>

<table border="1">
<tr bgcolor="#cccccc">
<th>ID</th>
<th>名前</th>
<th>姓</th>
<th>年</th>
<th>住所</th>
<th>変更</th>
<th>削除</th>
</tr>

<%
//社員リストを作る
ArrayList<ShainBean> shainList = (ArrayList<ShainBean>) request.getAttribute("shainList");

%>

<%
	for(ShainBean shain : shainList) {
%>
	<tr>
		<td><%= shain.getId() %></td>
		<td><%= shain.getName() %></td>
		<td><%= shain.getSei() %></td>
		<td><%= shain.getNen() %></td>
		<td><%= shain.getAddress() %></td>
		<td>変更</td>
		<td>削除</td>
	</tr>
	
<% 
	} 
%>




</table>
<p></p>
<!-- 「社員を登録する」ボタン -->
<form action="ShainInsert">
<input type="submit" value="社員を登録する">
</form>
</body>
</html>

●C コントローラー(controller)

ShainIndex.java
package controller;

import java.io.IOException;
import java.sql.SQLException;
import java.util.ArrayList;

import javax.naming.NamingException;

import beans.ShainBean;
import jakarta.servlet.ServletException;
import jakarta.servlet.annotation.WebServlet;
import jakarta.servlet.http.HttpServlet;
import jakarta.servlet.http.HttpServletRequest;
import jakarta.servlet.http.HttpServletResponse;
import model.ShainLogic;

/**
 * Servlet implementation class ShainIndex
 */
@WebServlet("/ShainIndex")
public class ShainIndex extends HttpServlet {
	private static final long serialVersionUID = 1L;
       
    /**
     * @see HttpServlet#HttpServlet()
     */
    public ShainIndex() {
        super();
        // TODO Auto-generated constructor stub
    }

	/**
	 * @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
	 */
	protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		
		ShainLogic shainLogic = new ShainLogic();
		
		try {
			// リスト取得
			ArrayList<ShainBean> shainList = shainLogic.getAllShain();
			
			// 社員リストをセットする
			request.setAttribute("shainList", shainList);
			
			// index.jsp へ転送
			request.getRequestDispatcher("/WEB-INF/view/index.jsp").forward(request, response);
			
		} catch (SQLException | NamingException e) {
			// TODO 自動生成された catch ブロック
			e.printStackTrace();
		}
		
	}

	/**
	 * @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response)
	 */
	protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		// TODO Auto-generated method stub
		doGet(request, response);
	}

}

1
0
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
1
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?