JSP - eclipse/oracle

최성현·2023년 8월 7일

CRUD TeamDto dto=list.get(i)oracle sql 삭제버튼 확인창 삼항연산자 체크박스 유무

JSP

목록 보기

7/13

CRUD - eclipse/oracle 연동(복습,myteam)

추가 학습

삭제버튼 확인창(list)

삭제버튼 클릭시 확인창 이벤트(삭제,취소 버튼)

<script type="text/javascript">
	function conf(num)
	{
		var ans=confirm('삭제하시겠습니까?');
		if(ans)
		{
			location.href='teamdelete.jsp?num='+num;
		}
	}

</script>

<button type="button" class="btn btn-danger btn-sm"
					onclick="conf(<%=dto.getNum() %>)">삭제</button>

버전2 삼항연산자 사용

<button type="button" class="btn btn-danger btn-sm"
onclick="confirm('삭제하시겠습니까?')?location.href='teamdelete.jsp?num=<%=dto.getNum() %>'
:alert('취소되었습니다')">삭제</button>

i번지의 dto 꺼내겠다
list.get(i)의 반환값이 dto이기때문에 TeamDto에 넣어줌

for(int i=0;i<list.size();i++)
{
	//i번지의 dto 꺼내겠다
	TeamDto dto=list.get(i); //list.get(i)의 반환값이 dto이기때문에 TeamDto에 넣어줌
	%>

체크박스 유무 값 (insertAction)

driver 체크박스의 널값 유무 체크 후 있음/없음 표시

dto.setDriver(driver==null?"없음":"있음");

updateAction
체크박스의 확인체크안할 경우 null,체크할 경우 on

System.out.println(driver); // 확인체크안할 경우 null,체크할 경우 on

sql문

--myteam
create table myteam(num number(3) primary key,
tname varchar2(20),
driver varchar2(10),
taddr varchar2(50),
writeday date);

select * from myteam;

DBConnect

package oracle.db;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class DBConnect {

static final String ORACLE_URL="jdbc:oracle:thin:@localhost:1521:XE";
	
	//driver
	String driver="oracle.jdbc.driver.OracleDriver";
	
	public DBConnect() {
		try {
			Class.forName(driver);
			System.out.println("오라클 드라이버 성공!!!");
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			System.out.println("오라클 드라이버 실패!!!");
		}
	}
	
	//Connection
	public Connection getConnection()
	{
		
		Connection conn=null;
		
		try {
			conn=DriverManager.getConnection(ORACLE_URL, "tjdgus", "1234");
			
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			System.out.println("오라클 연결 실패: url,계정,비밀번호 확인 요함! "+e.getMessage());
		}
		return conn;
	}
	
	//close메서드..총 4개
	//완성구문 2개
	public void dbClose(ResultSet rs,Statement stmt,Connection conn)
	{
		try {
			if(rs!=null) rs.close();
			if(stmt!=null) stmt.close();
			if(conn!=null) conn.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
	public void dbClose(Statement stmt,Connection conn)
	{
		try {
			if(stmt!=null) stmt.close();
			if(conn!=null) conn.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
	//미완구문 2개 //preparedstatement 나중에 정의해줌
	public void dbClose(ResultSet rs,PreparedStatement pstmt,Connection conn)
	{
		try {
			if(rs!=null) rs.close();
			if(pstmt!=null) pstmt.close();
			if(conn!=null) conn.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
	public void dbClose(PreparedStatement pstmt,Connection conn)
	{
		try {
			if(pstmt!=null) pstmt.close();
			if(conn!=null) conn.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

}

TeamDao.java (class)

package model.myteam;

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

import oracle.db.DBConnect;

public class TeamDao {
	
	DBConnect db=new DBConnect();
	
	//insert
	public void insertMyTeam(TeamDto dto)
	{
		Connection conn=db.getConnection();
		PreparedStatement pstmt=null;
		
		String sql="insert into myteam values(seq_1.nextval,?,?,?,sysdate)";
		
		try {
			pstmt=conn.prepareStatement(sql);
			pstmt.setString(1, dto.getName());
			pstmt.setString(2, dto.getDriver());
			pstmt.setString(3, dto.getAddr());
			
			pstmt.execute();
			
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			db.dbClose(pstmt, conn);
		}
	}
	
	public ArrayList<TeamDto> getAllMyTeams()
	{
		ArrayList<TeamDto> list=new ArrayList<TeamDto>();
		
		Connection conn=db.getConnection();
		PreparedStatement pstmt=null;
		ResultSet rs=null;
		
		String sql="select * from myteam order by num";
		
		try {
			pstmt=conn.prepareStatement(sql);
			rs=pstmt.executeQuery();
			
			while(rs.next())
			{
				TeamDto dto=new TeamDto();
				
				dto.setNum(rs.getString("num"));
				dto.setName(rs.getString("tname"));
				dto.setDriver(rs.getString("driver"));
				dto.setAddr(rs.getString("taddr"));
				dto.setWriteday(rs.getTimestamp("writeday"));
				
				list.add(dto);
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			db.dbClose(rs, pstmt, conn);
		}
		
		return list;
	}
	
	public void deleteMyTeam(String num)
	{
		Connection conn=db.getConnection();
		PreparedStatement pstmt=null;
		
		String sql="delete from myteam where num=?";
		
		try {
			pstmt=conn.prepareStatement(sql);
			pstmt.setString(1, num);
			
			pstmt.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			db.dbClose(pstmt, conn);
		}
	}
	
	public TeamDto getData(String num)
	{
		TeamDto dto=new TeamDto();
		
		Connection conn=db.getConnection();
		PreparedStatement pstmt=null;
		ResultSet rs=null;
		
		String sql="select * from myteam where num=?";
		
		try {
			pstmt=conn.prepareStatement(sql);
			pstmt.setString(1, num);
			rs=pstmt.executeQuery();
			
			if(rs.next())
			{
				//시퀀스를 넘기지만 안보이게 설정해줌
				dto.setNum(rs.getString("num"));
				dto.setName(rs.getString("tname"));
				dto.setDriver(rs.getString("driver"));
				dto.setAddr(rs.getString("taddr"));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			db.dbClose(rs, pstmt, conn);
		}
		
		return dto;
	}
	
	public void updateMyTeam(TeamDto dto)
	{
		Connection conn=db.getConnection();
		PreparedStatement pstmt=null;
		
		String sql="update myteam set tname=?, driver=?, taddr=? where num=?";
		
		try {
			pstmt=conn.prepareStatement(sql);
			pstmt.setString(1, dto.getName());
			pstmt.setString(2, dto.getDriver());
			pstmt.setString(3, dto.getAddr());
			pstmt.setString(4, dto.getNum());
			pstmt.execute();
			
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			db.dbClose(pstmt, conn);
		}
	}
}

TeamDto.java (class)

package model.myteam;

import java.sql.Timestamp;

public class TeamDto {
	
	private String num;
	private String name;
	private String driver;
	private String addr;
	private Timestamp writeday;
	
	
	public String getNum() {
		return num;
	}
	public void setNum(String num) {
		this.num = num;
	}
	public String getName() {
		return name;
	}
	public void setName(String name) {
		this.name = name;
	}
	public String getDriver() {
		return driver;
	}
	public void setDriver(String driver) {
		this.driver = driver;
	}
	public String getAddr() {
		return addr;
	}
	public void setAddr(String addr) {
		this.addr = addr;
	}
	public Timestamp getWriteday() {
		return writeday;
	}
	public void setWriteday(Timestamp writeday) {
		this.writeday = writeday;
	}
}

addForm.jsp(insert)

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<link href="https://cdn.jsdelivr.net/npm/bootstrap@5.3.0/dist/css/bootstrap.min.css" rel="stylesheet">
<link href="https://fonts.googleapis.com/css2?family=Cute+Font&family=Diphylleia&family=Dokdo&family=Nanum+Brush+Script&family=Nanum+Gothic+Coding&family=Noto+Sans+KR&display=swap" rel="stylesheet">
<script src="https://code.jquery.com/jquery-3.7.0.js"></script>
<title>Insert title here</title>
</head>
<body>
	<form action="addAction.jsp" method="post">
		<table class="table table-bordered" style="width: 300px;">
			<tr>
				<th>이름</th>
				<td>
					<input type="text" name="name" class="form-control"
					required="required" style="width: 120px;" placeholder="이름">
				</td>
			</tr>
			
			<tr>
				<th>운전면허</th>
				<td>
					<label><input type="checkbox" name="driver">있음</label>
				</td>
			</tr>
			
			<tr>
				<th>주소</th>
				<td>
					<input type="text" name="addr" class="form-control"
					required="required" style="width: 180px;" placeholder="주소">
				</td>
			</tr>
			
			<tr>
				<td colspan="2" align="center">
					<button type="submit" class="btn btn-info">팀원정보저장</button>
					<button type="button" class="btn btn-success"
					onclick="location.href='list.jsp'">목록으로이동</button>
				</td>
			</tr>
		</table>
	</form>
</body>
</html>

list.jsp (테이블 브라우저 출력)

<%@page import="java.text.SimpleDateFormat"%>
<%@page import="model.myteam.TeamDto"%>
<%@page import="java.util.ArrayList"%>
<%@page import="model.myteam.TeamDao"%>
<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<link href="https://cdn.jsdelivr.net/npm/bootstrap@5.3.0/dist/css/bootstrap.min.css" rel="stylesheet">
<link href="https://fonts.googleapis.com/css2?family=Cute+Font&family=Diphylleia&family=Dokdo&family=Nanum+Brush+Script&family=Nanum+Gothic+Coding&family=Noto+Sans+KR&display=swap" rel="stylesheet">
<script src="https://code.jquery.com/jquery-3.7.0.js"></script>
<title>Insert title here</title>
<script type="text/javascript">
	function conf(num)
	{
		var ans=confirm('삭제하시겠습니까?');
		if(ans)
		{
			location.href='teamdelete.jsp?num='+num;
		}
	}

</script>
</head>
<%
	TeamDao dao=new TeamDao();
	ArrayList<TeamDto> list=dao.getAllMyTeams();
	SimpleDateFormat sdf=new SimpleDateFormat("yyyy-MM-dd HH:mm");
%>
<body>
	<a href="addForm.jsp">팀원추가</a>
	<br>
	<table class="table table-bordered" style="width: 800px;">
		<tr align="center" class="table-warning">
			<th width="60">번호</th>
			<th width="120">이름</th>
			<th width="100">운전면허</th>
			<th width="260">주소</th>
			<th width="260">작성일</th>
			<th width="120">수정 삭제</th>
		</tr>
		
		<%
			for(int i=0;i<list.size();i++)
			{
				//i번지의 dto 꺼내겠다
				TeamDto dto=list.get(i); //list.get(i)의 반환값이 dto이기때문에 TeamDto에 넣어줌
				%>
				<tr>
					<td align="center"><%=i+1%></td>
					<td align="center"><%=dto.getName() %></td>
					<td align="center"><%=dto.getDriver() %></td>
					<td><%=dto.getAddr() %></td>
					<td><%=sdf.format(dto.getWriteday()) %></td>
					<td>
					<button type="button" class="btn btn-warning btn-sm"
					onclick="location.href='updateForm.jsp?num=<%=dto.getNum() %>'">수정</button>
					
					<%-- 삼항연산자 사용 버전
						  <button type="button" class="btn btn-danger btn-sm"
						  onclick="confirm('삭제하시겠습니까?')?location.href='teamdelete.jsp?num=<%=dto.getNum() %>'
							 	   :alert('취소되었습니다')">삭제</button> --%>
							 	   
					<%-- 선생님 버전 함수 호출 --%>
					<button type="button" class="btn btn-danger btn-sm"
					onclick="conf(<%=dto.getNum() %>)">삭제</button>
					</td>
				</tr>
			<%}
		%>
	</table>
</body>
</html>

addAction.jsp(insertAction)

<%@page import="model.myteam.TeamDao"%>
<%@page import="model.myteam.TeamDto"%>
<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<link href="https://cdn.jsdelivr.net/npm/bootstrap@5.3.0/dist/css/bootstrap.min.css" rel="stylesheet">
<link href="https://fonts.googleapis.com/css2?family=Cute+Font&family=Diphylleia&family=Dokdo&family=Nanum+Brush+Script&family=Nanum+Gothic+Coding&family=Noto+Sans+KR&display=swap" rel="stylesheet">
<script src="https://code.jquery.com/jquery-3.7.0.js"></script>
<title>Insert title here</title>
</head>
<body>
<%
	request.setCharacterEncoding("utf-8");
	
	String name=request.getParameter("name");
	String driver=request.getParameter("driver");
	String addr=request.getParameter("addr");
	
	TeamDto dto=new TeamDto();
	dto.setName(name);
	//체크박스 유무를 값 대입
	dto.setDriver(driver==null?"없음":"있음");
	dto.setAddr(addr);
	
	TeamDao dao=new TeamDao();
	dao.insertMyTeam(dto);
	
	response.sendRedirect("list.jsp");
%>
</body>
</html>

teamdelete.jsp

<%@page import="java.time.temporal.TemporalAdjuster"%>
<%@page import="model.myteam.TeamDao"%>
<%@page import="model.myteam.TeamDto"%>
<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<link href="https://cdn.jsdelivr.net/npm/bootstrap@5.3.0/dist/css/bootstrap.min.css" rel="stylesheet">
<link href="https://fonts.googleapis.com/css2?family=Cute+Font&family=Diphylleia&family=Dokdo&family=Nanum+Brush+Script&family=Nanum+Gothic+Coding&family=Noto+Sans+KR&display=swap" rel="stylesheet">
<script src="https://code.jquery.com/jquery-3.7.0.js"></script>
<title>Insert title here</title>
</head>
<body>
<%
	String num=request.getParameter("num");
	
	TeamDao dao=new TeamDao();
	dao.deleteMyTeam(num);
	
	response.sendRedirect("list.jsp");
%>
</body>
</html>

updateForm.jsp

<%@page import="model.myteam.TeamDto"%>
<%@page import="model.myteam.TeamDao"%>
<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<link href="https://cdn.jsdelivr.net/npm/bootstrap@5.3.0/dist/css/bootstrap.min.css" rel="stylesheet">
<link href="https://fonts.googleapis.com/css2?family=Cute+Font&family=Diphylleia&family=Dokdo&family=Nanum+Brush+Script&family=Nanum+Gothic+Coding&family=Noto+Sans+KR&display=swap" rel="stylesheet">
<script src="https://code.jquery.com/jquery-3.7.0.js"></script>
<title>Insert title here</title>
</head>
<%
	String num=request.getParameter("num");
	TeamDao dao=new TeamDao();
	TeamDto dto=dao.getData(num);
%>
<body>
	<form action="updateAction.jsp" method="post">
	
	<input type="hidden" name="num" value="<%=num %>">
		<table class="table table-bordered" style="width: 300px;">
			<tr>
				<th>이름</th>
				<td>
					<input type="text" name="name" class="form-control"
					required="required" style="width: 120px;" placeholder="이름"
					value="<%=dto.getName()%>">
				</td>
			</tr>
			
			<tr>
				<th>운전면허</th>
				<td>
					<label><input type="checkbox" name="driver" <%=dto.getDriver().equals("있음")?"checked":" "%>>&nbsp;운전면허여부</label>
				</td>
			</tr>
			
			<tr>
				<th>주소</th>
				<td>
					<input type="text" name="addr" class="form-control"
					required="required" style="width: 180px;" placeholder="주소"
					value="<%=dto.getAddr()%>">
				</td>
			</tr>
			
			<tr>
				<td colspan="2" align="center">
					<button type="submit" class="btn btn-warning">팀원정보수정</button>
					<button type="button" class="btn btn-success"
					onclick="location.href='list.jsp'">목록으로이동</button>
				</td>
			</tr>
		</table>
	</form>
</body>
</html>

updateAction.jsp

<%@page import="model.myteam.TeamDto"%>
<%@page import="model.myteam.TeamDao"%>
<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<link href="https://cdn.jsdelivr.net/npm/bootstrap@5.3.0/dist/css/bootstrap.min.css" rel="stylesheet">
<link href="https://fonts.googleapis.com/css2?family=Cute+Font&family=Diphylleia&family=Dokdo&family=Nanum+Brush+Script&family=Nanum+Gothic+Coding&family=Noto+Sans+KR&display=swap" rel="stylesheet">
<script src="https://code.jquery.com/jquery-3.7.0.js"></script>
<title>Insert title here</title>
</head>
<body>
<%
	String num=request.getParameter("num");
	String name=request.getParameter("name");
	String driver=request.getParameter("driver");
	
	System.out.println(driver); // 확인체크안할 경우 null,체크할 경우 on
	
	String addr=request.getParameter("addr");
	
	TeamDao dao=new TeamDao();
	TeamDto dto=new TeamDto();

	dto.setNum(num);	
	dto.setName(name);
	dto.setDriver(driver==null?"없음":"있음");
	dto.setAddr(addr);
	
	dao.updateMyTeam(dto);
	
	response.sendRedirect("list.jsp");
%>
</body>
</html>

최성현

백엔드 개발자로서 성장해 나가는 성현이의 블로그~

이전 포스트

JSP - eclipse

다음 포스트

JSP - eclipse/oracle

JSP

CRUD - eclipse/oracle 연동(복습,myteam)

추가 학습

삭제버튼 확인창(list)

체크박스 유무 값 (insertAction)

sql문

DBConnect

TeamDao.java (class)

TeamDto.java (class)

addForm.jsp(insert)

list.jsp (테이블 브라우저 출력)

addAction.jsp(insertAction)

teamdelete.jsp

updateForm.jsp

updateAction.jsp

JSP - eclipse

JSP - eclipse/oracle

0개의 댓글