본문 바로가기
Java 웹 개발

21.10.06 - 웹 개발 입문 38일차

개발이란 2021. 10. 6. 23:34

JDBC - 조건

 

Q. member 테이블에서 사용자가 입력한 검색어와 유사한 아이디 또는 닉네임을 가지는 회원을 조회하여 출력

package jdbc.select;

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

public class Test08 {
	public static void main(String[] args) throws SQLException, ClassNotFoundException {
//		select * from member where member_id like '%'||?||'%' or member_nick like '%'||?||'%';
//		select * from member where instr(member_id, ?) > 0 or instr(member_nick, ?) > 0;

//		입력
		String keyword = "2";

//		처리
		Class.forName("oracle.jdbc.OracleDriver");
		Connection con = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe", "계정아이디", "계정비밀번호");

//		String sql = "select * from member where member_id like '%'||?||'%' or member_nick like '%'||?||'%';";
		String sql = "select * from member where instr(member_id, ?) > 0 or instr(member_nick, ?) > 0";
		PreparedStatement ps = con.prepareStatement(sql);
		ps.setString(1, keyword);
		ps.setString(2, keyword);
		ResultSet rs = ps.executeQuery();

		while (rs.next()) {
			System.out.print(rs.getString("member_id"));
			System.out.print(" / ");
			System.out.print(rs.getString("member_nick"));
			System.out.print(" / ");
			System.out.print(rs.getString("member_grade"));
			System.out.println();
		}

		con.close();

	}
}

zxczxc / 안녕1234 / 준회원
asdasd1234 / 학생1237 / 준회원
qweqwe123 / 안녕123 / 준회원
zxc55555 / 그림12 / 준회원
test1234 / 테스트1234 / 준회원

 

 

 

Q : product 테이블에서 원하는 항목에 맞는 검사를 수행
= 원하는 항목은 이름, 분류

 

String kind = "name";

String keyword = "바";

(ex) String sql = "select * from product where instr(?, ?) > 0";

-> 홀더를 쓰면 'name' 로 입력이 된다

-> 구문을 변경해서 입력을 넣어줘야 한다 

 

(1) 구문에 직접 항목을 더하여 완성
String sql = "select * from product where instr("+kind+", ?) > 0";
System.out.println(sql);

(2) 구문에 replace 명령을 사용하여 치환 처리
String sql = "select * from product where instr(#1, ?) > 0";
sql = sql.replace("#1", kind);
System.out.println(sql);

 

package jdbc.select;

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

public class Test09_3 {
	public static void main(String[] args) throws Exception {

		// 입력
		String kind = "name";
		String keyword = "바";

		// 처리
		String sql = "select  * from product where instr(#1, ?) >0 ";
		sql = sql.replace("#1", kind);
		System.out.println(sql);

		Class.forName("oracle.jdbc.OracleDriver");
		Connection con = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe", "계정아이디", "계정비밀번호");

		PreparedStatement ps = con.prepareStatement(sql);
		ps.setString(1, keyword);
		ResultSet rs = ps.executeQuery();

		while (rs.next()) {
			System.out.print(rs.getString("name"));
			System.out.print(" / ");
			System.out.print(rs.getString("type"));
			System.out.print(" / ");
			System.out.print(rs.getInt("price"));
			System.out.println();
		}

		con.close();

	}
}

스크류바 / 아이스크림 / 1200
바나나킥 / 과자 / 1500

 

 

 

Q. member 테이블에서 다음 항목에 대한 유사검색이 가능하도록 구현
- 회원아이디
- 회원닉네임
- 회원이메일
- 회원전화번호
- 회원권한
검색하는 항목에 대해서 오름차순 정렬 후 출력

package jdbc.select;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.Map;

public class Test10_4 {
	public static void main(String[] args) throws ClassNotFoundException, SQLException {
		// 입력
		String kind = "회원등급";
		String keyword = "준회원";

		Map<String, String> map = new HashMap<>();
		map.put("회원아이디", "member_id");
		map.put("회원닉네임", "member_nick");
		map.put("회원등급", "member_grade");
		map.put("회원이메일", "member_email");
		map.put("회원전화번호", "member_phone");

		if (!map.containsKey(kind)) {
			System.out.println("분류 선택 오류");
			System.exit(-1);
		}

		kind = map.get(kind);

		// 처리
		Class.forName("oracle.jdbc.OracleDriver");
		Connection con = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe", "계정아이디", "계정비밀번호");

		String sql = "select * from member where instr(#1, ?) > 0 order by #1 asc";
		sql = sql.replace("#1", kind);
		// System.out.println(sql);

		PreparedStatement ps = con.prepareStatement(sql);
		ps.setString(1, keyword);
		ResultSet rs = ps.executeQuery();

		while (rs.next()) {
			System.out.print(rs.getString("member_id"));
			System.out.print(" / ");
			System.out.print(rs.getString("member_nick"));
			System.out.print(" / ");
			System.out.print(rs.getString("member_email"));
			System.out.print(" / ");
			System.out.print(rs.getString("member_phone"));
			System.out.print(" / ");
			System.out.print(rs.getString("member_grade"));
			System.out.print("\n");
		}

		con.close();
	}
}

zxczxc / 안녕1234 / null / 010-1234-1234 / 준회원
asdasd1234 / 학생1237 / asd1234@aa.com / 010-1231-3453 / 준회원
test1234 / 테스트1234 / null / null / 준회원
zxc55555 / 그림12 / null / 010-1234-1234 / 준회원
qweqwe123 / 안녕123 / qwe123@aa.com / 010-1234-1234 / 준회원

 

 

 

JDBC - 단일조회

Q : 번호(no)를 이용한 상품정보(product) 조회(단일조회)
ex : 3번 상품의 정보를 출력
=예상되는 결과는 (1) 아예 없거나 (2) 한 개 나오거나

package jdbc.select2;

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

public class Test01_1 {
	public static void main(String[] args) throws Exception {

		// 입력
		int no = 30;

		Class.forName("oracle.jdbc.OracleDriver");
		Connection con = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe", "계정아이디", "계정비밀번호");

		String sql = "select * from product where no = ?";
		PreparedStatement ps = con.prepareStatement(sql);
		ps.setInt(1, no);
		ResultSet rs = ps.executeQuery();

		if (rs.next()) {//있으면
			System.out.print(rs.getInt("no"));
			System.out.print(" / ");
			System.out.print(rs.getString("name"));
			System.out.print(" / ");
			System.out.println(rs.getInt("price"));
		}
		else {//없으면
			System.out.println("찾으시는 번호의 상품이 없습니다");
		}

		con.close();

	}
}

찾으시는 번호의 상품이 없습니다

 

 

 

오라클 연결 생성 메소드 만들기

Class.forName("oracle.jdbc.OracleDriver");
		Connection con = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe", "계정아이디", "계정비밀번호");

-> 매번 치는게 힘들다

 

메소드 구현

package jdbc.util;

import java.sql.Connection;
import java.sql.DriverManager;

public class JdbcUtils {

	public static Connection connect(String username, String password) throws Exception {
		Class.forName("oracle.jdbc.OracleDriver");
		Connection con = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe", "계정아이디", "계정비밀번호");
		return con;
	}

}

 

 

Q.`member` 테이블의 회원정보를 `member_id`를 입력받아서 조회하고 출력(ex:회원상세페이지)

- 위에서 만든 JdbcUtils 메소드 사용

package jdbc.select2;

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

import jdbc.util.JdbcUtils;

public class Test03 {
	public static void main(String[] args) throws Exception {

		String member_id = "zxczxc";

		Connection con = JdbcUtils.connect("계정아이디", "계정비밀번호");

		String sql = "select * from member where member_id = ?";
		PreparedStatement ps = con.prepareStatement(sql);
		ps.setString(1, member_id);
		ResultSet rs = ps.executeQuery();

		if (rs.next()) {// 있으면
			System.out.print(rs.getString("member_id"));
			System.out.print(" / ");
			System.out.print(rs.getString("member_nick"));
			System.out.print(" / ");
			System.out.print(rs.getDate("member_birth"));
			System.out.print(" / ");
			System.out.print(rs.getString("member_email"));
			System.out.print(" / ");
			System.out.print(rs.getString("member_phone"));
			System.out.print(" / ");
			System.out.print(rs.getDate("member_join"));
			System.out.print(" / ");
			System.out.print(rs.getInt("member_point"));
			System.out.print(" / ");
			System.out.println(rs.getString("member_grade"));
		} else {// 없으면
			System.out.println("찾으시는 아이디의 회원이 없습니다");
		}

		con.close();

	}
}

zxczxc / 안녕1234 / 1999-01-10 / null / 010-1234-1234 / 2021-10-05 / 100 / 준회원

 

 

 

Q. `member` 테이블을 이용해서 사용자에게 아이디, 비밀번호를 입력받아 로그인 처리를 수행하는 프로그램을 구현
출력 메세지는 다음 두 가지로 구분된다.
- OOO님 로그인 성공
- 로그인 정보가 일치하지 않습니다

package jdbc.select2;

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

import jdbc.util.JdbcUtils;

public class Test04_1 {
	public static void main(String[] args) throws Exception {
		// 입력
		String memberId = "testuser2";
		String memberPw = "testuser1234";

		// 처리
		Connection con = JdbcUtils.connect("계정아이디", "계정비밀번호");

		String sql = "select * from member where member_id = ? and member_pw = ?";
		PreparedStatement ps = con.prepareStatement(sql);
		ps.setString(1, memberId);
		ps.setString(2, memberPw);
		ResultSet rs = ps.executeQuery();

		if (rs.next()) {
			System.out.println("로그인 성공");
		} else {
			System.out.println("로그인 실패");
		}

		con.close();
	}
}

로그인 실패

 

 

 

 

JDBC - 자바빈즈(Java Beans)로 insert 기능

 

 

ex) Exam 테이블에 대한 데이터베이스 작업을 처리하는 클래스

 

= DAO : Data Access Object (ex : 택배기사님)
= 각각의 작업(CRRUD)들을 메소드 단위로 보관하고 호출하여 사용

 

[1] 등록(Create) 메소드 - ExamDao 모듈

package jdbc.beans;

import java.sql.Connection;
import java.sql.PreparedStatement;

import jdbc.util.JdbcUtils;

public class ExamDao {

	//= insert into exam(형식) values(번호, 이름, 과목, 유형, 점수)
	//= insert into exam(형식) values(exam_seq.nextval, ?, ?, ?, ?)
	public void insert(String student, String subject, String type, int score) throws Exception {
		Connection con = JdbcUtils.connect("계정아이디", "계정비밀번호");

		String sql = "insert into exam values(exam_seq.nextval, ?, ?, ?, ?)";
		PreparedStatement ps = con.prepareStatement(sql);
		ps.setString(1, student);
		ps.setString(2, subject);
		ps.setString(3, type);
		ps.setInt(4, score);
		ps.execute();

		con.close();		
	}

}

 

[2] ExamDao 모듈을 이용하여 exam 테이블에 데이터를 insert 하도록 코드 구현

package jdbc.test;

import jdbc.beans.ExamDao;

public class Test01 {
	public static void main(String[] args) throws Exception {

		// 1. 모듈 객체 생성
		ExamDao examDao = new ExamDao();

		// 2. 등록 메소드 호출
		examDao.insert("거북왕", "데이터입출력구현", "서술형", 80);
		System.out.println("끝!");
	}
}

끝!

 

 

- 추가

[3] ExamDto

= DTO : Datea Transfer Object (ex : 택배상자(포장상자) 역할)

 

시험정보를 보관하기 위해 만든 클래스
= 번호(exam_id) + 학생명(student) + 과목명(subject) + 유형(type) + 점수(score)
= 자바에서 배웠던 가장 일반적인 클래스
= DB 테이블의 1줄 정보를 저장할 수 있도록 구성한다

package jdbc.beans;

public class ExamDto {
	private int examId;
	private String student;
	private String subject;
	private String type;
	private int score;

	// 기본생성자 + setter/getter (+toString)
	public ExamDto() {
		super();
	}

	public int getExamId() {
		return examId;
	}

	public void setExamId(int examId) {
		this.examId = examId;
	}

	public String getStudent() {
		return student;
	}

	public void setStudent(String student) {
		this.student = student;
	}

	public String getSubject() {
		return subject;
	}

	public void setSubject(String subject) {
		this.subject = subject;
	}

	public String getType() {
		return type;
	}

	public void setType(String type) {
		this.type = type;
	}

	public int getScore() {
		return score;
	}

	public void setScore(int score) {
		this.score = score;
	}
}

 

 

[4] 등록(Create) 메소드 재구현
= 낱개 데이터가 아닌 의미있는 단위(객체)로 데이터를 전달받도록 구현

package jdbc.beans;

import java.sql.Connection;
import java.sql.PreparedStatement;

import jdbc.util.JdbcUtils;

	public void insert(ExamDto examDto) throws Exception {
		Connection con = JdbcUtils.connect("계정아이디", "계정비밀번호");

		String sql = "insert into exam values(exam_seq.nextval, ?, ?, ?, ?)";
		PreparedStatement ps = con.prepareStatement(sql);
		ps.setString(1, examDto.getStudent());
		ps.setString(2, examDto.getSubject());
		ps.setString(3, examDto.getType());
		ps.setInt(4, examDto.getScore());
		ps.execute();

		con.close();
	}
}

 

[5] -> [3], [4] 을 이용해서 재구현

= ExamDto를 준비하여 데이터를 미리 포장한 뒤 등록하도록 처리

package jdbc.test;

import jdbc.beans.ExamDao;
import jdbc.beans.ExamDto;

public class Test01_1 {
	public static void main(String[] args) throws Exception {
        
		ExamDto examDto = new ExamDto();// 비어있는 객체 생성
		examDto.setStudent("거북왕");// 이름 담고
		examDto.setSubject("데이터입출력구현");// 과목 담고
		examDto.setType("서술형");// 유형 담고
		examDto.setScore(80);// 점수 담고

		// 1. 모듈 객체 생성
		ExamDao examDao = new ExamDao();

		// 2. 등록 메소드 호출
		// examDao.insert("거북왕", "데이터입출력구현", "서술형", 80);//데이터를 낱개로 관리
		examDao.insert(examDto);// 데이터를 의미있는 단위로 관리
		System.out.println("끝!");
	}
}

 

 

 

Q. product 테이블에 대한 데이터베이스 작업을 처리하는 클래스

 

[ProductDao 클래스]

package jdbc.beans;

import java.sql.Connection;
import java.sql.PreparedStatement;

import jdbc.util.JdbcUtils;

public class ProductDao {
	public void insert(ProductDto productDto) throws Exception {
		Connection con = JdbcUtils.connect("계정아이디", "계정비밀번호");

		String sql = "insert into product values(?, ?, ?, ?, to_date(?,'YYYY-MM-dd'), to_date(?,'YYYY-MM-dd'))";
		PreparedStatement ps = con.prepareStatement(sql);
		ps.setInt(1, productDto.getNo());
		ps.setString(2, productDto.getName());
		ps.setString(3, productDto.getType());
		ps.setInt(4, productDto.getPrice());
		ps.setString(5, productDto.getMade());
		ps.setString(6, productDto.getExpire());
		ps.execute();

		con.close();
	}
}

 

[ProductDto 클래스]

package jdbc.beans;

public class ProductDto {
	// no name type price made expire

	private int no;
	private String name;
	private String type;
	private int price;
	private String made;
	private String expire;

	public ProductDto() {
		super();
	}

	public int getNo() {
		return no;
	}

	public void setNo(int no) {
		this.no = no;
	}

	public String getName() {
		return name;
	}

	public void setName(String name) {
		this.name = name;
	}

	public String getType() {
		return type;
	}

	public void setType(String type) {
		this.type = type;
	}

	public int getPrice() {
		return price;
	}

	public void setPrice(int price) {
		this.price = price;
	}

	public String getMade() {
		return made;
	}

	public void setMade(String made) {
		this.made = made;
	}

	public String getExpire() {
		return expire;
	}

	public void setExpire(String expire) {
		this.expire = expire;
	}
}

 

[실행]

package jdbc.test;

import jdbc.beans.ExamDto;
import jdbc.beans.ProductDao;
import jdbc.beans.ProductDto;

public class Test02_1 {
	public static void main(String[] args) throws Exception {

		ProductDto productDto = new ProductDto();
		productDto.setNo(25);
		productDto.setName("메로나이슬");
		productDto.setType("주류");
		productDto.setPrice(2500);
		productDto.setMade("2020-10-06");
		productDto.setExpire("2022-10-06");

		ProductDao productDao = new ProductDao();
		productDao.insert(productDto);

		System.out.println("끝!");

	}
}

 

 

 

Q. 회원 가입을 Java Beans를 이용하여 처리하도록 구현하세요.

 

[MemberDao 클래스]

package jdbc.beans;

import java.sql.Connection;
import java.sql.PreparedStatement;

import jdbc.util.JdbcUtils;

public class MemberDao {

	public void join(MemberDto memberDto) throws Exception {
		Connection con = JdbcUtils.connect("계정아이디", "계정비밀번호");

		String sql = "insert into member values(?, ?, ?, to_date(?,'YYYY-MM-DD'), ?, ?, sysdate, 100, '준회원')";
		PreparedStatement ps = con.prepareStatement(sql);

		ps.setString(1, memberDto.getMemberId());
		ps.setString(2, memberDto.getMemberPw());
		ps.setString(3, memberDto.getMemberNick());
		ps.setString(4, memberDto.getMemberBrith());
		ps.setString(5, memberDto.getMemberEmail());
		ps.setString(6, memberDto.getMemberPhone());
		ps.execute();

		con.close();
	}

}

 

[MemberDto 클래스]

package jdbc.beans;

public class MemberDto {

	private String memberId;
	private String memberPw;
	private String memberNick;
	private String memberBrith;
	private String memberEmail;
	private String memberPhone;
	private String memberJoin;
	private int memberPoint;
	private String memberGrade;
	
	public MemberDto() {
		super();
	}
	
	public String getMemberId() {
		return memberId;
	}
	public void setMemberId(String memberId) {
		this.memberId = memberId;
	}
	public String getMemberPw() {
		return memberPw;
	}
	public void setMemberPw(String memberPw) {
		this.memberPw = memberPw;
	}
	public String getMemberNick() {
		return memberNick;
	}
	public void setMemberNick(String memberNick) {
		this.memberNick = memberNick;
	}
	public String getMemberBrith() {
		return memberBrith;
	}
	public void setMemberBrith(String memberBrith) {
		this.memberBrith = memberBrith;
	}
	public String getMemberEmail() {
		return memberEmail;
	}
	public void setMemberEmail(String memberEmail) {
		this.memberEmail = memberEmail;
	}
	public String getMemberPhone() {
		return memberPhone;
	}
	public void setMemberPhone(String memberPhone) {
		this.memberPhone = memberPhone;
	}
	public String getMemberJoin() {
		return memberJoin;
	}
	public void setMemberJoin(String memberJoin) {
		this.memberJoin = memberJoin;
	}
	public int getMemberPoint() {
		return memberPoint;
	}
	public void setMemberPoint(int memberPoint) {
		this.memberPoint = memberPoint;
	}
	public String getMemberGrade() {
		return memberGrade;
	}
	public void setMemberGrade(String memberGrade) {
		this.memberGrade = memberGrade;
	}


}

 

 

[ 실행 ]

package jdbc.test;

import java.util.Scanner;

import jdbc.beans.MemberDao;
import jdbc.beans.MemberDto;

public class Test03 {
	public static void main(String[] args) throws Exception {

		Scanner sc = new Scanner(System.in);

		MemberDto memberDto = new MemberDto();

		System.out.print("아이디를 입력하세요 : ");
		memberDto.setMemberId(sc.nextLine());

		System.out.print("비밀번호를 입력하세요 : ");
		memberDto.setMemberPw(sc.nextLine());

		System.out.print("닉네임을 입력하세요 : ");
		memberDto.setMemberNick(sc.nextLine());

		System.out.print("생년월일을 입력하세요 : ");
		memberDto.setMemberBrith(sc.nextLine());

		System.out.print("이메일을 입력하세요 : ");
		memberDto.setMemberEmail(sc.nextLine());
		
		System.out.print("전화번호를 입력하세요 : ");
		memberDto.setMemberPhone(sc.nextLine());

		MemberDao memberDao = new MemberDao();
		memberDao.join(memberDto);

		System.out.println("회원 가입 완료");

		sc.close();

	}
}

아이디를 입력하세요 : qwer1234
비밀번호를 입력하세요 : qwer1234
닉네임을 입력하세요 : 홍길동
생년월일을 입력하세요 : 2010-10-10
이메일을 입력하세요 : qwer1234@aa.com
전화번호를 입력하세요 : 010-0123-4567
회원 가입 완료

'Java 웹 개발' 카테고리의 다른 글

21.10.08 - 웹 개발 입문 40일차  (0) 2021.10.08
21.10.07 - 웹 개발 입문 39일차  (0) 2021.10.07
21.10.05 - 웹 개발 입문 37일차  (0) 2021.10.05
21.10.04 - 웹 개발 입문 36일차  (0) 2021.10.04
21.10.01 - 웹 개발 입문 35일차  (0) 2021.10.02

'Java 웹 개발' Related Articles

티스토리툴바