본문 바로가기
JSP

230130_DB

by 경 훈 2023. 1. 30.

문자열 중에서 특정 부분만 만족하면 "참" 결과를 얻는 함수를

부분문자열 함수라 하여 '_'와 '%'로 구분한다.

 

_ : 1개의 문자를

% : all의 뜻을 포함하며 where 절에서 like 키워드와 함께 사용

 

select * from student;
select * from course;
select * from enrol;

select * from student where year='3' or year='4';
select * from student where year in (3,4);
select * from student where year<>'1' and year<>'2';

select sno,dept from student where sname='나연묵' and year='4'; --6번
select year,sname from student where dept='컴퓨터';  --7번
select sname,dept from student where dept!='컴퓨터';  --8번
select sno,sname,dept from student where sno in(100,300,500); --9번
select sno,sname from student where sname like '정%' or sname like '박%'; --10번
select count(*) from student where year='4';
select grade,count(grade) from enrol group by grade;

 

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%@ include file="top.jsp" %>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<section>
	<form action="save.jsp" method="post">
		<table border="1" align="center">
			<tr>
				<th>반</th>
				<td><input type="text" name="ban"></td>
			</tr>
			<tr>
				<th>아이디</th>
				<td><input type="text" name="id"></td>
			</tr>
			<tr>
				<th>이름</th>
				<td><input type="text" name="name"></td>
			</tr>
			<tr>
				<th>전화</th>
				<td><input type="text" name="phone"></td>
			</tr>
			<tr>
				<th>주소</th>
				<td><input type="text" name="address"></td>
			</tr>
			<tr>
				<th>국어점수</th>
				<td><input type="text" name="kor"></td>
			</tr>
			<tr>
				<td colspan="2" align="center">
					<input type="submit" value="전송">
					<input type="reset" value="초기화">
				</td>
			</tr>
		</table>
	</form>
</section>
<jsp:include page="footer.jsp"/>
</html>

 

<%@page import="day11_DB1.DAO"%>
<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<% request.setCharacterEncoding("utf-8"); %>
<jsp:useBean id="dto" class="day11_DB1.DTO"/>
<jsp:setProperty property="*" name="dto"/>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<body>
	<%
	DAO dao = new DAO();
	dao.insert(dto);
	response.sendRedirect("index.jsp");
	%>
</body>
</html>

 

<%@page import="java.sql.ResultSet"%>
<%@page import="day11_DB1.DAO"%>
<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%@ include file="top.jsp" %>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<section>
	<%
	DAO dao = new DAO();
	ResultSet rs = dao.out();
	%>
	<table border="1" align="center" width="800px">
		<tr>
			<th>반</th><th>아이디</th><th>이름</th><th>전화번호</th>
			<th>주소</th><th>국어점수</th>
		</tr>
	<%
	while(rs.next()) {
		int ban = rs.getInt(1);
		String id = rs.getString(2);
		String name = rs.getString(3);
		String phone = rs.getString(4);
		String address = rs.getString(5);
		int kor = rs.getInt(6);
	%>	
		<tr>
			<td><%=ban%></td><td><%=id%></td><td><%=name%></td>
			<td><%=phone%></td><td><%=address%></td><td><%=kor%></td>
		</tr>
	<%
	}
	%>
	</table>
</section>
<jsp:include page="footer.jsp"/>
</html>

 

<%@page import="java.sql.ResultSet"%>
<%@page import="day11_DB1.DAO"%>
<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%@ include file="top.jsp" %>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<section>
	<%
	DAO dao = new DAO();
	ResultSet rs = dao.totout();
	%>
	<table border="1" align="center" width="800px">
		<tr>
			<th>반</th><th>반인원</th><th>국어합계</th><th>국어평균</th>
		</tr>
	<%
	while(rs.next()) {
		int ban  = rs.getInt(1);
		int bancount = rs.getInt(2);
		int kortot = rs.getInt(3);
		double koravg = rs.getDouble(4);
	%>
		<tr>
			<td><%=ban%></td><td><%=bancount%></td>
			<td><%=kortot%></td><td><%=koravg%></td>
		</tr>
	<%
	}
	%>
	</table>
</section>
<jsp:include page="footer.jsp"/>
</html>

 

<%@page import="java.sql.ResultSet"%>
<%@page import="day11_DB1.DAO"%>
<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%@ include file="top.jsp" %>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<section>
	<%
	DAO dao = new DAO();
	ResultSet rs = dao.ascout();
	%>
	<table border="1" align="center" width="800px">
		<tr>
			<th>반</th><th>아이디</th><th>이름</th><th>전화번호</th>
			<th>주소</th><th>국어점수</th>
		</tr>
	<%
	while(rs.next()) {
		int ban = rs.getInt(1);
		String id = rs.getString(2);
		String name = rs.getString(3);
		String phone = rs.getString(4);
		String address = rs.getString(5);
		int kor = rs.getInt(6);
	%>	
		<tr>
			<td><%=ban%></td><td><%=id%></td><td><%=name%></td>
			<td><%=phone%></td><td><%=address%></td><td><%=kor%></td>
		</tr>
	<%
	}
	%>
	</table>
</section>
<jsp:include page="footer.jsp"/>
</html>

 

<%@page import="java.sql.ResultSet"%>
<%@page import="day11_DB1.DAO"%>
<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%@ include file="top.jsp" %>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<section>
	<%
	DAO dao = new DAO();
	ResultSet rs = dao.suwon();
	%>
	<table border="1" align="center" width="400px">
		<tr>
			<th>수원시 거주하는 인원</th>
		</tr>
	<%
	if(rs.next()) {
		int suwoncount = rs.getInt(1);
	
	%>	
		<tr>
			<td><%=suwoncount%></td>
		</tr>
	<%
	}
	%>
	</table>
</section>
<jsp:include page="footer.jsp"/>
</html>

 

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%@ include file="top.jsp" %>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<section>
	<form action="searchsave.jsp">
		<table border="1" align="center">
			<tr>
				<td>
					<select name="sname">
						<option value="name">이름</option>
						<option value="id">아이디</option>
						<option value="address">주소</option>
					</select>
				</td>
				<td>
					<input type="text" name="svalue">
				</td>
			</tr>
			<tr>
				<td colspan="2" align="center">
					<input type="submit" value="검색">
					<input type="reset" value="취소">
				</td>
			</tr>
		</table>
	</form>
</section>
<jsp:include page="footer.jsp"/>
</html>

 

<%@page import="java.sql.ResultSet"%>
<%@page import="day11_DB1.DAO"%>
<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%@ include file="top.jsp" %>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<section>
	<%!
	String sname,svalue;
	%>
	<%
	request.setCharacterEncoding("utf-8");
	sname = request.getParameter("sname");
	svalue = request.getParameter("svalue");
	DAO dao = new DAO();
	ResultSet rs = dao.search(sname, svalue);
	%>
	<table border="1" align="center">
		<tr>
			<th>반</th><th>아이디</th><th>이름</th><th>전화번호</th>
			<th>주소</th><th>국어점수</th>
		</tr>
	<%
	while(rs.next()) {
		int ban = rs.getInt(1);
		String id = rs.getString(2);
		String name = rs.getString(3);
		String phone = rs.getString(4);
		String address = rs.getString(5);
		int kor = rs.getInt(6);
	%>	
		<tr>
			<td><%=ban%></td><td><%=id%></td><td><%=name%></td>
			<td><%=phone%></td><td><%=address%></td><td><%=kor%></td>
		</tr>
	<%
	}
	%>	
	</table>
</section>
<jsp:include page="footer.jsp"/>
</html>

 

package day11_DB1;

public class DTO {
	//반 아이디 이름 전화 주소 국어점수
	//반별 출력 국어점수 총점..국어점수 평균
	int ban;
	String id,name,phone,address;
	int kor;
	public DTO() {}
	public DTO(int ban, String id, String name, String phone, String address, int kor) {
		super();
		this.ban = ban;
		this.id = id;
		this.name = name;
		this.phone = phone;
		this.address = address;
		this.kor = kor;
	}
	public int getBan() {
		return ban;
	}
	public void setBan(int ban) {
		this.ban = ban;
	}
	public String getId() {
		return id;
	}
	public void setId(String id) {
		this.id = id;
	}
	public String getName() {
		return name;
	}
	public void setName(String name) {
		this.name = name;
	}
	public String getPhone() {
		return phone;
	}
	public void setPhone(String phone) {
		this.phone = phone;
	}
	public String getAddress() {
		return address;
	}
	public void setAddress(String address) {
		this.address = address;
	}
	public int getKor() {
		return kor;
	}
	public void setKor(int kor) {
		this.kor = kor;
	}
}

 

package day11_DB1;

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

public class DAO {
	Connection con;
	PreparedStatement psmt;
	ResultSet rs;
	String driver = "oracle.jdbc.driver.OracleDriver";
	String url = "jdbc:oracle:thin:@localhost:1521:xe";
	public DAO() throws ClassNotFoundException {
		Class.forName(driver);
		try {
			con = DriverManager.getConnection(url, "ezen", "12345");
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	public void insert(DTO dto) {
		int ban = dto.getBan();
		String id = dto.getId();
		String name = dto.getName();
		String phone = dto.getPhone();
		String address = dto.getAddress();
		int kor = dto.getKor();
		String sql = "insert into db01301 values (?,?,?,?,?,?)";
		try {
			psmt = con.prepareStatement(sql);
			psmt.setInt(1, ban);
			psmt.setString(2, id);
			psmt.setString(3, name);
			psmt.setString(4, phone);
			psmt.setString(5, address);
			psmt.setInt(6, kor);
			psmt.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	public ResultSet out() {
		String sql = "select ban,id,name,phone,address,kor "
				+ "from db01301";
		try {
			psmt = con.prepareStatement(sql);
			rs = psmt.executeQuery();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return rs;
	}
	public ResultSet totout() {
		String sql = "select ban,count(*),sum(kor), "
				+ "round(avg(kor),1) "
				+ "from db01301 "
				+ "group by ban "
				+ "having count(*)>=2 "
				+ "order by ban asc";
		try {
			psmt = con.prepareStatement(sql);
			rs = psmt.executeQuery();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return rs;
	}
	public ResultSet ascout() {
		String sql = "select ban,id,name,phone,address,kor "
				+ "from db01301 order by ban asc,name asc";
		try {
			psmt = con.prepareStatement(sql);
			rs = psmt.executeQuery();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return rs;
	}
	public ResultSet suwon() {
		String sql = "select count(*) from db01301 "
				+ "where address like '%수원%'";
		try {
			psmt = con.prepareStatement(sql);
			rs = psmt.executeQuery();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return rs;
	}
	public ResultSet search(String sname,String svalue) {
		String sql = "select * from db01301 ";
		if(sname.equals("name")) {
			sql+=" where name like ?";
		}
		else if(sname.equals("address")){
			sql+=" where address like ?";
		}
		else {
			sql+=" where id like ?";
		}
		try {
			psmt = con.prepareStatement(sql);
			psmt.setString(1,'%'+svalue+'%');
			rs = psmt.executeQuery();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return rs;
	}
}

'JSP' 카테고리의 다른 글

230201_DB  (0) 2023.02.01
230131_DB  (0) 2023.01.31
230127_DB  (0) 2023.01.27
230126_DB  (0) 2023.01.26
230125_DB  (0) 2023.01.25

관련글

댓글

티스토리툴바