[자바] 도서관리 #6 도서관리 데이터베이스 설계 및 구현(2020년 MySql)

Posted on: 2020년 06월 14일 2020년 06월 14일
Categories: 강의자료, 자바

참조
1. SQL(Structured Query Language) : 내가 정리한 것
최종 실행파일 :
1. Summber20200614.zip을 압축해지 한 후 a.bat 파일을 더블클릭하면 실행됨

학과테이블(tdept)

CREATE TABLE tdept (
	dept_no	INTEGER,
	dept_name	VARCHAR(50),
	dept_tel	VARCHAR(50),
	dept_room	VARCHAR(50),
	PRIMARY KEY(dept_no)
);

INSERT INTO tdept (dept_no,dept_name,dept_tel,dept_room) VALUES (14,'스마트미디어','2330','공학1관229호');
INSERT INTO tdept (dept_no,dept_name,dept_tel,dept_room) VALUES (19,'일본언어문화','2311','진리관107호');
INSERT INTO tdept (dept_no,dept_name,dept_tel,dept_room) VALUES (23,'게임콘텐츠','3181','예술관209호');
INSERT INTO tdept (dept_no,dept_name,dept_tel,dept_room) VALUES (25,'역사문화','2325','진리관107호');
INSERT INTO tdept (dept_no,dept_name,dept_tel,dept_room) VALUES (62,'사회복지','2426','평화관113호');
INSERT INTO tdept (dept_no,dept_name,dept_tel,dept_room) VALUES (70,'문헌정보','2235','평화관113호');

CREATE TABLE tdept (

dept_no INTEGER,

dept_name VARCHAR(50),

dept_tel VARCHAR(50),

dept_room VARCHAR(50),

PRIMARY KEY(dept_no)

);

INSERT INTO tdept (dept_no,dept_name,dept_tel,dept_room) VALUES (14,'스마트미디어','2330','공학1관229호');

INSERT INTO tdept (dept_no,dept_name,dept_tel,dept_room) VALUES (19,'일본언어문화','2311','진리관107호');

INSERT INTO tdept (dept_no,dept_name,dept_tel,dept_room) VALUES (23,'게임콘텐츠','3181','예술관209호');

INSERT INTO tdept (dept_no,dept_name,dept_tel,dept_room) VALUES (25,'역사문화','2325','진리관107호');

INSERT INTO tdept (dept_no,dept_name,dept_tel,dept_room) VALUES (62,'사회복지','2426','평화관113호');

INSERT INTO tdept (dept_no,dept_name,dept_tel,dept_room) VALUES (70,'문헌정보','2235','평화관113호');

도서테이블(tbook)

CREATE TABLE tbook (
	no	VARCHAR(10),
	title	VARCHAR(255),
	pric	INTEGER,
	publ	VARCHAR(50),
	typ	VARCHAR(20),
	pos	VARCHAR(10),
	PRIMARY KEY(no)
);

INSERT INTO tbook (no,title,pric,publ,typ,pos) VALUES ('201900001','자바',20000,'생능출판','기술과학', '3층');
INSERT INTO tbook (no,title,pric,publ,typ,pos) VALUES ('201900002','알고리즘',19000,'이지스퍼블릭','자연과학', '1층');
INSERT INTO tbook (no,title,pric,publ,typ,pos) VALUES ('201900003','데이터베이스',21000,'이한출판사','기술과학', '3층');
INSERT INTO tbook (no,title,pric,publ,typ,pos) VALUES ('201900004','정보검색',15000,'한빛미디어','자연과학', '2층');
INSERT INTO tbook (no,title,pric,publ,typ,pos) VALUES ('201900005','오라클',25000,'생능출판','기술과학', '1층');
INSERT INTO tbook (no,title,pric,publ,typ,pos) VALUES ('201900006','운영체제',20000,'이지스퍼블릭','기술과학', '2층');
INSERT INTO tbook (no,title,pric,publ,typ,pos) VALUES ('202000001','인공지능',30000,'길벗','기술과학','1층');
INSERT INTO tbook (no,title,pric,publ,typ,pos) VALUES ('202000004','정보검색',15000,'한빛미디어','자연과학','1층');
INSERT INTO tbook (no,title,pric,publ,typ,pos) VALUES ('202000007','운영체제',20000,'이지스퍼블릭','기술과학','1층');
INSERT INTO tbook (no,title,pric,publ,typ,pos) VALUES ('202000008','오라클',25000,'생능출판','기술과학','2층');

CREATE TABLE tbook (

no VARCHAR(10),

title VARCHAR(255),

pric INTEGER,

publ VARCHAR(50),

typ VARCHAR(20),

pos VARCHAR(10),

PRIMARY KEY(no)

);

INSERT INTO tbook (no,title,pric,publ,typ,pos) VALUES ('201900001','자바',20000,'생능출판','기술과학', '3층');

INSERT INTO tbook (no,title,pric,publ,typ,pos) VALUES ('201900002','알고리즘',19000,'이지스퍼블릭','자연과학', '1층');

INSERT INTO tbook (no,title,pric,publ,typ,pos) VALUES ('201900003','데이터베이스',21000,'이한출판사','기술과학', '3층');

INSERT INTO tbook (no,title,pric,publ,typ,pos) VALUES ('201900004','정보검색',15000,'한빛미디어','자연과학', '2층');

INSERT INTO tbook (no,title,pric,publ,typ,pos) VALUES ('201900005','오라클',25000,'생능출판','기술과학', '1층');

INSERT INTO tbook (no,title,pric,publ,typ,pos) VALUES ('201900006','운영체제',20000,'이지스퍼블릭','기술과학', '2층');

INSERT INTO tbook (no,title,pric,publ,typ,pos) VALUES ('202000001','인공지능',30000,'길벗','기술과학','1층');

INSERT INTO tbook (no,title,pric,publ,typ,pos) VALUES ('202000004','정보검색',15000,'한빛미디어','자연과학','1층');

INSERT INTO tbook (no,title,pric,publ,typ,pos) VALUES ('202000007','운영체제',20000,'이지스퍼블릭','기술과학','1층');

INSERT INTO tbook (no,title,pric,publ,typ,pos) VALUES ('202000008','오라클',25000,'생능출판','기술과학','2층');

회원테이블(tmember) : 학과를 학과테이블(tdept)와 외래키를 사용해서 연결

CREATE TABLE tmember (
	no	VARCHAR(10),
	name	VARCHAR(50),
	dept	INTEGER,
	major	VARCHAR(50),
	PRIMARY KEY(no),
	FOREIGN KEY(dept) REFERENCES tdept(dept_no)
		ON DELETE SET NULL
		ON UPDATE CASCADE
);

INSERT INTO tmember (no,name,dept,major) VALUES ('201912346','길동이',62,'2');
INSERT INTO tmember (no,name,dept,major) VALUES ('201912345','홍길동',19,'1');
INSERT INTO tmember (no,name,dept,major) VALUES ('10000','홍교수',70,'2');
INSERT INTO tmember (no,name,dept,major) VALUES ('202012345','박길동',14,'3');

CREATE TABLE tmember (

no VARCHAR(10),

name VARCHAR(50),

dept INTEGER,

major VARCHAR(50),

PRIMARY KEY(no),

FOREIGN KEY(dept) REFERENCES tdept(dept_no)

ON DELETE SET NULL

ON UPDATE CASCADE

);

INSERT INTO tmember (no,name,dept,major) VALUES ('201912346','길동이',62,'2');

INSERT INTO tmember (no,name,dept,major) VALUES ('201912345','홍길동',19,'1');

INSERT INTO tmember (no,name,dept,major) VALUES ('10000','홍교수',70,'2');

INSERT INTO tmember (no,name,dept,major) VALUES ('202012345','박길동',14,'3');

대출테이블(tlend) : 외래키(foreign key)를 사용해서 생성해야 함

CREATE TABLE tlend (
	no	VARCHAR(50),
	bno	VARCHAR(10),
	mno	VARCHAR(10),
	cdate	DATETIME DEFAULT NOW(),
	days	INTEGER DEFAULT 14,
	PRIMARY KEY(no),
	FOREIGN KEY(bno) REFERENCES tbook(no)
		ON DELETE SET NULL
		ON UPDATE CASCADE,
	FOREIGN KEY(mno) REFERENCES tmember(no)
		ON DELETE SET NULL
		ON UPDATE CASCADE
);

CREATE TABLE tlend (

no VARCHAR(50),

bno VARCHAR(10),

mno VARCHAR(10),

cdate DATETIME DEFAULT NOW(),

days INTEGER DEFAULT 14,

PRIMARY KEY(no),

FOREIGN KEY(bno) REFERENCES tbook(no)

ON DELETE SET NULL

ON UPDATE CASCADE,

FOREIGN KEY(mno) REFERENCES tmember(no)

ON DELETE SET NULL

ON UPDATE CASCADE

);

회원 뷰(vmember) : 회원정보와 학과정보를 조인

CREATE VIEW vmember AS SELECT * FROM tmember, tdept WHERE tmember.dept = tdept.dept_no

1
2
3
4

CREATE VIEW vmember AS
SELECT *
FROM tmember, tdept
WHERE tmember.dept = tdept.dept_no
대출가능한 도서 뷰(vlendablebook)

CREATE VIEW vlendablebook AS SELECT * FROM tbook WHERE no NOT IN (SELECT bno FROM tlend);

1
2
3

CREATE VIEW vlendablebook AS
SELECT * FROM tbook
WHERE no NOT IN (SELECT bno FROM tlend);

회원-대출-도서 테이블을 Join한 뷰(vlendbook)

CREATE VIEW vlendbook AS
	SELECT tlend.no, tlend.bno, tlend.mno, tlend.cdate, tlend.days,
		tbook.title as title,
		tmember.name as name
	FROM tlend, tbook, tmember
	WHERE tlend.bno = tbook.no AND tlend.mno = tmember.no

CREATE VIEW vlendbook AS

SELECT tlend.no, tlend.bno, tlend.mno, tlend.cdate, tlend.days,

tbook.title as title,

tmember.name as name

FROM tlend, tbook, tmember

WHERE tlend.bno = tbook.no AND tlend.mno = tmember.no

MyConst.java
1. sqlite버전의 파일 그대로 사용
2. 데이터베이스, 아이디, 암호부분 XXXXX를 본인의 것으로 수정
BookDTO.java
1. sqlite버전의 파일 그대로 사용

BookDAO.java : sqlite버전의 파일에서 getConn()만 수정

package kr.co.moak.summer2020;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.Vector;

public class BookDAO_MySql {
	String tableName = "tbook";

	/** DB연결 메소드 */
	public Connection getConn() {
		Connection conn = null;
		try {
			Class.forName(MyConst.DRIVER); // 1. 드라이버 로딩
			conn = DriverManager.getConnection(MyConst.URL, MyConst.USER, MyConst.PASS); // 2. 드라이버 연결
		} catch (Exception e) {
			e.printStackTrace();
		}
		return conn;
	}


	public void insert(BookDTO dto) {
		Connection conn = null;
		PreparedStatement ps = null;
		String sql = "INSERT INTO " + tableName + "(no, title, pric, publ, typ, pos) VALUES (?, ?, ?, ?, ?, ?);";

		conn = getConn();
		try {
			ps = conn.prepareStatement(sql);
			ps.setString(1, dto.getNo());
			ps.setString(2, dto.getTitle());
			ps.setInt(3, dto.getPric());
			ps.setString(4, dto.getPubl());
			ps.setString(5, dto.getTyp());
			ps.setString(6, dto.getPos());

			ps.executeUpdate();
			conn.close();
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	/** 테이블의 캡션 출력 */
	public Vector<Object> getTableHeader() {
		Vector<Object> col = new Vector<Object>();
		col.add("도서코드");
		col.add("제목");
		//col.add("가격");
		//col.add("출판사");
		col.add("종류");
		col.add("소장위치");

		return col;
	}

	/** 도서목록 출력 */
	public Vector<Object> getListVector() {
		Vector<Object> data = new Vector<Object>();

		Connection conn = null; // 연결
		PreparedStatement ps = null; // 명령
		ResultSet rs = null; // 결과
		try {
			conn = getConn();
			String sql = "select * from " + tableName + " order by title asc;";
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			while (rs.next()) {
				Vector<String> row = new Vector<String>();

				row.add(rs.getString("no"));
				row.add(rs.getString("title"));
				//row.add(rs.getString("pric"));
				//row.add(rs.getString("publ"));
				row.add(rs.getString("typ"));
				row.add(rs.getString("pos"));

				data.add(row);
			} // while
			conn.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return data;
	}// getListVector()
}

package kr.co.moak.summer2020;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.util.ArrayList;

import java.util.Vector;

public class BookDAO_MySql {

String tableName = "tbook";

/** DB연결 메소드 */

public Connection getConn() {

Connection conn = null;

try {

Class.forName(MyConst.DRIVER); // 1. 드라이버 로딩

conn = DriverManager.getConnection(MyConst.URL, MyConst.USER, MyConst.PASS); // 2. 드라이버 연결

} catch (Exception e) {

e.printStackTrace();

}

return conn;

}

public void insert(BookDTO dto) {

Connection conn = null;

PreparedStatement ps = null;

String sql = "INSERT INTO " + tableName + "(no, title, pric, publ, typ, pos) VALUES (?, ?, ?, ?, ?, ?);";

conn = getConn();

try {

ps = conn.prepareStatement(sql);

ps.setString(1, dto.getNo());

ps.setString(2, dto.getTitle());

ps.setInt(3, dto.getPric());

ps.setString(4, dto.getPubl());

ps.setString(5, dto.getTyp());

ps.setString(6, dto.getPos());

ps.executeUpdate();

conn.close();

} catch (Exception e) {

// TODO Auto-generated catch block

e.printStackTrace();

}

/** 테이블의 캡션 출력 */

public Vector<Object> getTableHeader() {

Vector<Object> col = new Vector<Object>();

col.add("도서코드");

col.add("제목");

//col.add("가격");

//col.add("출판사");

col.add("종류");

col.add("소장위치");

return col;

}

/** 도서목록 출력 */

public Vector<Object> getListVector() {

Vector<Object> data = new Vector<Object>();

Connection conn = null; // 연결

PreparedStatement ps = null; // 명령

ResultSet rs = null; // 결과

try {

conn = getConn();

String sql = "select * from " + tableName + " order by title asc;";

ps = conn.prepareStatement(sql);

rs = ps.executeQuery();

while (rs.next()) {

Vector<String> row = new Vector<String>();

row.add(rs.getString("no"));

row.add(rs.getString("title"));

//row.add(rs.getString("pric"));

//row.add(rs.getString("publ"));

row.add(rs.getString("typ"));

row.add(rs.getString("pos"));

data.add(row);

} // while

conn.close();

} catch (Exception e) {

e.printStackTrace();

}

return data;

}// getListVector()

}

BookUI.java
1. sqlite버전의 파일 그대로 사용
프로그램 샘플
1. 메인 화면
2. 도서
3. 회원
4. 대출/반납

whan0623

516