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

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

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

학과테이블(tdept)

CREATE TABLE tdept (
	dept_no	INTEGER,
	dept_name	TEXT,
	dept_tel	TEXT,
	dept_room	TEXT,
	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 TEXT,

dept_tel TEXT,

dept_room TEXT,

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	TEXT,
	title	TEXT,
	pric	INTEGER,
	publ	TEXT,
	typ	TEXT,
	pos	TEXT,
	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 TEXT,

title TEXT,

pric INTEGER,

publ TEXT,

typ TEXT,

pos TEXT,

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	TEXT,
	name	TEXT,
	dept	INTEGER,
	major	TEXT,
	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 TEXT,

name TEXT,

dept INTEGER,

major TEXT,

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	TEXT,
	bno	TEXT,
	mno	TEXT,
	cdate	NUMERIC DEFAULT CURRENT_TIMESTAMP,
	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 TEXT,

bno TEXT,

mno TEXT,

cdate NUMERIC DEFAULT CURRENT_TIMESTAMP,

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"	TEXT,
	"bno"	TEXT,
	"mno"	TEXT,
	"cdate"	NUMERIC DEFAULT CURRENT_TIMESTAMP,
	"days"	INTEGER DEFAULT 14,
	PRIMARY KEY("no"),
	FOREIGN KEY("bno") REFERENCES "tbook"("KEYWORDASCOLUMNNAME")
		ON DELETE SET NULL
		ON UPDATE CASCADE,
	FOREIGN KEY("mno") REFERENCES "tmember"("KEYWORDASCOLUMNNAME")
		ON DELETE SET NULL
		ON UPDATE CASCADE
);

CREATE TABLE "tlend" (

"no" TEXT,

"bno" TEXT,

"mno" TEXT,

"cdate" NUMERIC DEFAULT CURRENT_TIMESTAMP,

"days" INTEGER DEFAULT 14,

PRIMARY KEY("no"),

FOREIGN KEY("bno") REFERENCES "tbook"("KEYWORDASCOLUMNNAME")

ON DELETE SET NULL

ON UPDATE CASCADE,

FOREIGN KEY("mno") REFERENCES "tmember"("KEYWORDASCOLUMNNAME")

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

package kr.co.moak.summer2020;

public class MyConst {
	//Sqlite 설정
	public static String DB = System.getProperty("user.dir") + "\\mybook.db";

	//MySql 설정
	public static final String DRIVER = "com.mysql.cj.jdbc.Driver"; //8.0.11버전
	public static final String URL = "jdbc:mysql://ora.jj.ac.kr:3306/XXXXX?serverTimezone=UTC";//8.0.11버전
	public static final String USER = "XXXXX"; // DB ID
	public static final String PASS = "XXXXX"; // DB 암호
}

package kr.co.moak.summer2020;

public class MyConst {

//Sqlite 설정

public static String DB = System.getProperty("user.dir") + "\\mybook.db";

//MySql 설정

public static final String DRIVER = "com.mysql.cj.jdbc.Driver"; //8.0.11버전

public static final String URL = "jdbc:mysql://ora.jj.ac.kr:3306/XXXXX?serverTimezone=UTC";//8.0.11버전

public static final String USER = "XXXXX"; // DB ID

public static final String PASS = "XXXXX"; // DB 암호

}

BookDTO.java

package kr.co.moak.summer2020;

public class BookDTO {
	String no;
	String title;
	int pric;
	String publ;
	String typ;
	String pos;

	public BookDTO() {}

	public BookDTO(String no, String title, int pric, String publ, String typ, String pos) {
		this.no = no;
		this.title = title;
		this.pric = pric;
		this.publ = publ;
		this.typ = typ;
		this.pos = pos;
	}

	public String getNo() {
		return no;
	}

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

	public String getTitle() {
		return title;
	}
	public void setTitle(String title) {
		this.title = title;
	}
	public int getPric() {
		return pric;
	}
	public void setPric(int pric) {
		this.pric = pric;
	}
	public String getPubl() {
		return publ;
	}
	public void setPubl(String publ) {
		this.publ = publ;
	}
	public String getTyp() {
		return typ;
	}
	public void setTyp(String typ) {
		this.typ = typ;
	}
	public String getPos() {
		return pos;
	}
	public void setPos(String pos) {
		this.pos = pos;
	}

}

package kr.co.moak.summer2020;

public class BookDTO {

String no;

String title;

int pric;

String publ;

String typ;

String pos;

public BookDTO() {}

public BookDTO(String no, String title, int pric, String publ, String typ, String pos) {

this.no = no;

this.title = title;

this.pric = pric;

this.publ = publ;

this.typ = typ;

this.pos = pos;

}

public String getNo() {

return no;

}

public void setNo(String no) {

this.no = no;

}

public String getTitle() {

return title;

}

public void setTitle(String title) {

this.title = title;

}

public int getPric() {

return pric;

}

public void setPric(int pric) {

this.pric = pric;

}

public String getPubl() {

return publ;

}

public void setPubl(String publ) {

this.publ = publ;

}

public String getTyp() {

return typ;

}

public void setTyp(String typ) {

this.typ = typ;

}

public String getPos() {

return pos;

}

public void setPos(String pos) {

this.pos = pos;

}

BookDAO.java

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 {
	String tableName = "tbook";

	public Connection getConn() {
		Connection conn = null;

		try {
			Class.forName("org.sqlite.JDBC");
			conn = DriverManager.getConnection("jdbc:sqlite:" + MyConst.DB);
		} catch (Exception e) {
			// TODO Auto-generated catch block
			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 {

String tableName = "tbook";

public Connection getConn() {

Connection conn = null;

try {

Class.forName("org.sqlite.JDBC");

conn = DriverManager.getConnection("jdbc:sqlite:" + MyConst.DB);

} catch (Exception e) {

// TODO Auto-generated catch block

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

package kr.co.moak.summer2020;

import java.awt.Color;
import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;
import java.awt.event.MouseEvent;
import java.awt.event.MouseListener;
import java.util.ArrayList;
import java.util.Vector;

import javax.swing.ButtonGroup;
import javax.swing.JButton;
import javax.swing.JComboBox;
import javax.swing.JFrame;
import javax.swing.JLabel;
import javax.swing.JPanel;
import javax.swing.JRadioButton;
import javax.swing.JScrollPane;
import javax.swing.JTable;
import javax.swing.JTextField;
import javax.swing.table.DefaultTableModel;

public class BookUI extends JFrame implements ActionListener, MouseListener {
	JTextField tfNo;
	JTextField tfTitle;
	JTextField tfPric;
	JTextField tfPubl;
	JComboBox<String> comTyp;
	JRadioButton rdoPos1, rdoPos2, rdoPos3;
	String strPos = "1층";

	DefaultTableModel model;
	JTable table;

	public BookUI() {
		this.setTitle("도서관리");
		this.setLayout(null);
		this.setResizable(false);
		this.setDefaultCloseOperation(JFrame.HIDE_ON_CLOSE);
		setSize(800, 600);

		JPanel panList = new JPanel();
		panList.setBounds(10, 10, 390, 550);
		panList.setBackground(Color.PINK);
		panList.setLayout(null);
		this.add(panList);

		BookDAO dao = new BookDAO();
		Vector<Object> vecs = dao.getListVector();
		Vector<Object> cols = dao.getTableHeader();
		model = new DefaultTableModel(vecs, cols);
		// model.setDataVector(vecs, cols);
		table = new JTable(model);
		JScrollPane scroll = new JScrollPane(table);
		scroll.setBounds(10, 10, 370, 530);
		panList.add(scroll);
		table.addMouseListener(this);

		JPanel panRight = new JPanel();
		panRight.setBounds(410, 10, 380, 550);
		panRight.setBackground(Color.GREEN);
		panRight.setLayout(null);
		this.add(panRight);

		// 도서코드
		JLabel lblNo = new JLabel("도서코드 : ");
		lblNo.setBounds(10, 10, 100, 25);
		panRight.add(lblNo);

		tfNo = new JTextField();
		tfNo.setBounds(120, 10, 100, 25);
		panRight.add(tfNo);

		// 도서명
		JLabel lblTitle = new JLabel("도서명 : ");
		lblTitle.setBounds(10, 50, 100, 25);
		panRight.add(lblTitle);

		tfTitle = new JTextField();
		tfTitle.setBounds(120, 50, 100, 25);
		panRight.add(tfTitle);

		// 가격
		JLabel lblPrice = new JLabel("가격 : ");
		lblPrice.setBounds(10, 90, 100, 25);
		panRight.add(lblPrice);

		tfPric = new JTextField();
		tfPric.setBounds(120, 90, 100, 25);
		panRight.add(tfPric);

		// 출판사
		JLabel lblPubl = new JLabel("출판사 : ");
		lblPubl.setBounds(10, 140, 100, 25);
		panRight.add(lblPubl);

		tfPubl = new JTextField();
		tfPubl.setBounds(120, 140, 100, 25);
		panRight.add(tfPubl);

		// 종류
		JLabel lblTyp = new JLabel("종류 : ");
		lblTyp.setBounds(10, 190, 100, 25);
		panRight.add(lblTyp);

		String[] strTyp = {"총류", "철학", "종교", "사회과학", "자연과학", "기술과학"};
		comTyp = new JComboBox<String>(strTyp);
		comTyp.setBounds(120, 190, 100, 25);
		panRight.add(comTyp);

		// 소장 위치
		JLabel lblPos = new JLabel("소장위치 : ");
		lblPos.setBounds(10, 240, 100, 25);
		panRight.add(lblPos);

		rdoPos1 = new JRadioButton("1층", true);
		rdoPos1.setBounds(120, 240, 60, 25);
		panRight.add(rdoPos1);
		rdoPos1.addActionListener(this);

		rdoPos2 = new JRadioButton("2층");
		rdoPos2.setBounds(190, 240, 60, 25);
		panRight.add(rdoPos2);
		rdoPos2.addActionListener(this);

		rdoPos3 = new JRadioButton("3층");
		rdoPos3.setBounds(260, 240, 60, 25);
		panRight.add(rdoPos3);
		rdoPos3.addActionListener(this);

		ButtonGroup grpPos = new ButtonGroup();
		grpPos.add(rdoPos1);
		grpPos.add(rdoPos2);
		grpPos.add(rdoPos3);


		JButton btnSave = new JButton("저장");
		btnSave.setBounds(10, 510, 100, 25);
		panRight.add(btnSave);
		btnSave.addActionListener(this);

		this.setVisible(true);
	}

	@Override
	public void actionPerformed(ActionEvent e) {
		// TODO Auto-generated method stub
		BookDAO dao = null;

		switch(e.getActionCommand()) {
		case "1층":
			strPos = "1층";
			break;
		case "2층":
			strPos = "2층";
			break;
		case "3층":
			strPos = "3층";
			break;
		case "저장":
			String no = tfNo.getText();
			String name = tfTitle.getText();
			int pric = Integer.parseInt(tfPric.getText());
			String dept = tfPubl.getText();
			String typ = comTyp.getSelectedItem().toString();
			String pos = strPos;

			BookDTO dto = new BookDTO(no, name, pric, dept, typ, pos);

			dao = new BookDAO();
			dao.insert(dto);

			tfNo.setText("");
			tfTitle.setText("");
			tfPric.setText("");
			tfPubl.setText("");

			model.setDataVector(dao.getListVector(), dao.getTableHeader());
			break;
		}
	}

	@Override
	public void mouseClicked(MouseEvent e) {
		// TODO Auto-generated method stub
		String no = table.getValueAt(table.getSelectedRow(), 0).toString();
		BookDAO dao = new BookDAO();
		BookDTO dto = dao.getBookDTO(no);

		tfNo.setText(dto.getNo());
		tfTitle.setText(dto.getTitle());
		tfPric.setText(dto.getPric() + "");
		tfPubl.setText(dto.getPubl());

		if(dto.getTyp() != null && dto.getTyp().compareTo("") != 0) {
			comTyp.setSelectedItem(dto.getTyp());
		}

		if(dto.getPos() != null) {
			switch(dto.getPos()) {
			case "1층":
				strPos = "1층";
				rdoPos1.setSelected(true);
				break;
			case "2층":
				strPos = "2층";
				rdoPos2.setSelected(true);
				break;
			case "3층":
				strPos = "3층";
				rdoPos3.setSelected(true);
				break;
			}
		}
	}

	@Override
	public void mouseEntered(MouseEvent e) {
		// TODO Auto-generated method stub

	}

	@Override
	public void mouseExited(MouseEvent e) {
		// TODO Auto-generated method stub

	}

	@Override
	public void mousePressed(MouseEvent e) {
		// TODO Auto-generated method stub

	}

	@Override
	public void mouseReleased(MouseEvent e) {
		// TODO Auto-generated method stub

	}
}

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

175

176

177

178

179

180

181

182

183

184

185

186

187

188

189

190

191

192

193

194

195

196

197

198

199

200

201

202

203

204

205

206

207

208

209

210

211

212

213

214

215

216

217

218

219

220

221

222

223

224

225

226

227

228

229

230

231

232

233

234

235

236

237

238

239

240

package kr.co.moak.summer2020;

import java.awt.Color;

import java.awt.event.ActionEvent;

import java.awt.event.ActionListener;

import java.awt.event.MouseEvent;

import java.awt.event.MouseListener;

import java.util.ArrayList;

import java.util.Vector;

import javax.swing.ButtonGroup;

import javax.swing.JButton;

import javax.swing.JComboBox;

import javax.swing.JFrame;

import javax.swing.JLabel;

import javax.swing.JPanel;

import javax.swing.JRadioButton;

import javax.swing.JScrollPane;

import javax.swing.JTable;

import javax.swing.JTextField;

import javax.swing.table.DefaultTableModel;

public class BookUI extends JFrame implements ActionListener, MouseListener {

JTextField tfNo;

JTextField tfTitle;

JTextField tfPric;

JTextField tfPubl;

JComboBox<String> comTyp;

JRadioButton rdoPos1, rdoPos2, rdoPos3;

String strPos = "1층";

DefaultTableModel model;

JTable table;

public BookUI() {

this.setTitle("도서관리");

this.setLayout(null);

this.setResizable(false);

this.setDefaultCloseOperation(JFrame.HIDE_ON_CLOSE);

setSize(800, 600);

JPanel panList = new JPanel();

panList.setBounds(10, 10, 390, 550);

panList.setBackground(Color.PINK);

panList.setLayout(null);

this.add(panList);

BookDAO dao = new BookDAO();

Vector<Object> vecs = dao.getListVector();

Vector<Object> cols = dao.getTableHeader();

model = new DefaultTableModel(vecs, cols);

// model.setDataVector(vecs, cols);

table = new JTable(model);

JScrollPane scroll = new JScrollPane(table);

scroll.setBounds(10, 10, 370, 530);

panList.add(scroll);

table.addMouseListener(this);

JPanel panRight = new JPanel();

panRight.setBounds(410, 10, 380, 550);

panRight.setBackground(Color.GREEN);

panRight.setLayout(null);

this.add(panRight);

// 도서코드

JLabel lblNo = new JLabel("도서코드 : ");

lblNo.setBounds(10, 10, 100, 25);

panRight.add(lblNo);

tfNo = new JTextField();

tfNo.setBounds(120, 10, 100, 25);

panRight.add(tfNo);

// 도서명

JLabel lblTitle = new JLabel("도서명 : ");

lblTitle.setBounds(10, 50, 100, 25);

panRight.add(lblTitle);

tfTitle = new JTextField();

tfTitle.setBounds(120, 50, 100, 25);

panRight.add(tfTitle);

// 가격

JLabel lblPrice = new JLabel("가격 : ");

lblPrice.setBounds(10, 90, 100, 25);

panRight.add(lblPrice);

tfPric = new JTextField();

tfPric.setBounds(120, 90, 100, 25);

panRight.add(tfPric);

// 출판사

JLabel lblPubl = new JLabel("출판사 : ");

lblPubl.setBounds(10, 140, 100, 25);

panRight.add(lblPubl);

tfPubl = new JTextField();

tfPubl.setBounds(120, 140, 100, 25);

panRight.add(tfPubl);

// 종류

JLabel lblTyp = new JLabel("종류 : ");

lblTyp.setBounds(10, 190, 100, 25);

panRight.add(lblTyp);

String[] strTyp = {"총류", "철학", "종교", "사회과학", "자연과학", "기술과학"};

comTyp = new JComboBox<String>(strTyp);

comTyp.setBounds(120, 190, 100, 25);

panRight.add(comTyp);

// 소장 위치

JLabel lblPos = new JLabel("소장위치 : ");

lblPos.setBounds(10, 240, 100, 25);

panRight.add(lblPos);

rdoPos1 = new JRadioButton("1층", true);

rdoPos1.setBounds(120, 240, 60, 25);

panRight.add(rdoPos1);

rdoPos1.addActionListener(this);

rdoPos2 = new JRadioButton("2층");

rdoPos2.setBounds(190, 240, 60, 25);

panRight.add(rdoPos2);

rdoPos2.addActionListener(this);

rdoPos3 = new JRadioButton("3층");

rdoPos3.setBounds(260, 240, 60, 25);

panRight.add(rdoPos3);

rdoPos3.addActionListener(this);

ButtonGroup grpPos = new ButtonGroup();

grpPos.add(rdoPos1);

grpPos.add(rdoPos2);

grpPos.add(rdoPos3);

JButton btnSave = new JButton("저장");

btnSave.setBounds(10, 510, 100, 25);

panRight.add(btnSave);

btnSave.addActionListener(this);

this.setVisible(true);

}

@Override

public void actionPerformed(ActionEvent e) {

// TODO Auto-generated method stub

BookDAO dao = null;

switch(e.getActionCommand()) {

case "1층":

strPos = "1층";

break;

case "2층":

strPos = "2층";

break;

case "3층":

strPos = "3층";

break;

case "저장":

String no = tfNo.getText();

String name = tfTitle.getText();

int pric = Integer.parseInt(tfPric.getText());

String dept = tfPubl.getText();

String typ = comTyp.getSelectedItem().toString();

String pos = strPos;

BookDTO dto = new BookDTO(no, name, pric, dept, typ, pos);

dao = new BookDAO();

dao.insert(dto);

tfNo.setText("");

tfTitle.setText("");

tfPric.setText("");

tfPubl.setText("");

model.setDataVector(dao.getListVector(), dao.getTableHeader());

break;

}

@Override

public void mouseClicked(MouseEvent e) {

// TODO Auto-generated method stub

String no = table.getValueAt(table.getSelectedRow(), 0).toString();

BookDAO dao = new BookDAO();

BookDTO dto = dao.getBookDTO(no);

tfNo.setText(dto.getNo());

tfTitle.setText(dto.getTitle());

tfPric.setText(dto.getPric() + "");

tfPubl.setText(dto.getPubl());

if(dto.getTyp() != null && dto.getTyp().compareTo("") != 0) {

comTyp.setSelectedItem(dto.getTyp());

}

if(dto.getPos() != null) {

switch(dto.getPos()) {

case "1층":

strPos = "1층";

rdoPos1.setSelected(true);

break;

case "2층":

strPos = "2층";

rdoPos2.setSelected(true);

break;

case "3층":

strPos = "3층";

rdoPos3.setSelected(true);

break;

}

@Override

public void mouseEntered(MouseEvent e) {

// TODO Auto-generated method stub

}

@Override

public void mouseExited(MouseEvent e) {

// TODO Auto-generated method stub

}

@Override

public void mousePressed(MouseEvent e) {

// TODO Auto-generated method stub

}

@Override

public void mouseReleased(MouseEvent e) {

// TODO Auto-generated method stub

}

DeptDAO.java

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;

public class DeptDAO {
	String tableName = "tdept";

	public Connection getConn() {
		Connection conn = null;

		try {
			Class.forName("org.sqlite.JDBC");
			conn = DriverManager.getConnection("jdbc:sqlite:" + MyConst.DB);
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		return conn;
	}


	/**  */
	public ArrayList<String> getListDeptCombo() {
		ArrayList<String> arList = new ArrayList<String>();

		Connection conn = null; // 연결
		PreparedStatement ps = null; // 명령
		ResultSet rs = null; // 결과
		try {
			conn = getConn();
			String sql = "select * from " + tableName + " order by dept_name asc;";
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			while (rs.next()) {
				arList.add(rs.getString("dept_no") + ":" + rs.getString("dept_name"));
			} // while
			conn.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return arList;
	}// getListDeptCombo()


}

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;

public class DeptDAO {

String tableName = "tdept";

public Connection getConn() {

Connection conn = null;

try {

Class.forName("org.sqlite.JDBC");

conn = DriverManager.getConnection("jdbc:sqlite:" + MyConst.DB);

} catch (Exception e) {

// TODO Auto-generated catch block

e.printStackTrace();

}

return conn;

}

/** */

public ArrayList<String> getListDeptCombo() {

ArrayList<String> arList = new ArrayList<String>();

Connection conn = null; // 연결

PreparedStatement ps = null; // 명령

ResultSet rs = null; // 결과

try {

conn = getConn();

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

ps = conn.prepareStatement(sql);

rs = ps.executeQuery();

while (rs.next()) {

arList.add(rs.getString("dept_no") + ":" + rs.getString("dept_name"));

} // while

conn.close();

} catch (Exception e) {

e.printStackTrace();

}

return arList;

}// getListDeptCombo()

}

MemberDTO.java

package kr.co.moak.summer2020;

public class MemberDTO {
	String no;
	String name;
	String dept;
	String major;

	public MemberDTO() {}

	public MemberDTO(String no, String name, String dept, String major) {
		this.no = no;
		this.name = name;
		this.dept = dept;
		this.major = major;
	}

	public String getNo() {
		return no;
	}

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

	public String getName() {
		return name;
	}
	public void setName(String title) {
		this.name = title;
	}
	public String getDept() {
		return dept;
	}
	public void setDept(String dept) {
		this.dept = dept;
	}
	public String getMajor() {
		return major;
	}
	public void setMajor(String major) {
		this.major = major;
	}
}

package kr.co.moak.summer2020;

public class MemberDTO {

String no;

String name;

String dept;

String major;

public MemberDTO() {}

public MemberDTO(String no, String name, String dept, String major) {

this.no = no;

this.name = name;

this.dept = dept;

this.major = major;

}

public String getNo() {

return no;

}

public void setNo(String no) {

this.no = no;

}

public String getName() {

return name;

}

public void setName(String title) {

this.name = title;

}

public String getDept() {

return dept;

}

public void setDept(String dept) {

this.dept = dept;

}

public String getMajor() {

return major;

}

public void setMajor(String major) {

this.major = major;

}

MemberDAO.java

package kr.co.moak.summer2020;

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

public class MemberDAO {
	String tableName = "tmember";
	String viewName = "vmember";

	public Connection getConn() {
		Connection conn = null;

		try {
			Class.forName("org.sqlite.JDBC");
			conn = DriverManager.getConnection("jdbc:sqlite:" + MyConst.DB);
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		return conn;
	}

	public void insert(MemberDTO dto) {
		Connection conn = null;
		PreparedStatement ps = null;
		String sql = "INSERT INTO " + tableName + "(no, name, dept, major) VALUES (?, ?, ?, ?);";

		try {
			conn = getConn();
			ps = conn.prepareStatement(sql);
			ps.setString(1, dto.getNo());
			ps.setString(2, dto.getName());
			ps.setString(3, dto.getDept());
			ps.setString(4, dto.getMajor());

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

	/** 회원1명 출력 */
	public MemberDTO getMemberDTO(String no) {
		MemberDTO dto = new MemberDTO();

		Connection conn = null; // 연결
		PreparedStatement ps = null; // 명령
		ResultSet rs = null; // 결과
		try {
			conn = getConn();
			String sql = "select * from " + tableName + " WHERE no = ?;";
			ps = conn.prepareStatement(sql);
			ps.setString(1, no);
			rs = ps.executeQuery();
			if (rs.next()) {
				dto.setNo(rs.getString("no"));
				dto.setName(rs.getString("name"));
				dto.setDept(rs.getString("dept"));
				dto.setMajor(rs.getString("major"));
			} // while
			conn.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return dto;
	}// getMemberDTO()


	/** 테이블의 캡션 출력 */
	public Vector<Object> getTableHeader() {
		Vector<Object> col = new Vector<Object>();
		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 name asc;";
			String sql = "select * from " + viewName + " order by name 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("name"));
				row.add(rs.getString("dept_name"));
				row.add(rs.getString("major"));

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


}

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

package kr.co.moak.summer2020;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.util.Vector;

public class MemberDAO {

String tableName = "tmember";

String viewName = "vmember";

public Connection getConn() {

Connection conn = null;

try {

Class.forName("org.sqlite.JDBC");

conn = DriverManager.getConnection("jdbc:sqlite:" + MyConst.DB);

} catch (Exception e) {

// TODO Auto-generated catch block

e.printStackTrace();

}

return conn;

}

public void insert(MemberDTO dto) {

Connection conn = null;

PreparedStatement ps = null;

String sql = "INSERT INTO " + tableName + "(no, name, dept, major) VALUES (?, ?, ?, ?);";

try {

conn = getConn();

ps = conn.prepareStatement(sql);

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

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

ps.setString(3, dto.getDept());

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

ps.executeUpdate();

conn.close();

} catch (Exception e) {

// TODO Auto-generated catch block

e.printStackTrace();

}

/** 회원1명 출력 */

public MemberDTO getMemberDTO(String no) {

MemberDTO dto = new MemberDTO();

Connection conn = null; // 연결

PreparedStatement ps = null; // 명령

ResultSet rs = null; // 결과

try {

conn = getConn();

String sql = "select * from " + tableName + " WHERE no = ?;";

ps = conn.prepareStatement(sql);

ps.setString(1, no);

rs = ps.executeQuery();

if (rs.next()) {

dto.setNo(rs.getString("no"));

dto.setName(rs.getString("name"));

dto.setDept(rs.getString("dept"));

dto.setMajor(rs.getString("major"));

} // while

conn.close();

} catch (Exception e) {

e.printStackTrace();

}

return dto;

}// getMemberDTO()

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

public Vector<Object> getTableHeader() {

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

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 name asc;";

String sql = "select * from " + viewName + " order by name 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("name"));

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

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

data.add(row);

} // while

conn.close();

} catch (Exception e) {

e.printStackTrace();

}

return data;

}// getListVector()

}

MemberUI.java

package kr.co.moak.summer2020;

import java.awt.Color;
import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;
import java.awt.event.MouseEvent;
import java.awt.event.MouseListener;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Vector;

import javax.swing.JButton;
import javax.swing.JComboBox;
import javax.swing.JFrame;
import javax.swing.JLabel;
import javax.swing.JPanel;
import javax.swing.JScrollPane;
import javax.swing.JTable;
import javax.swing.JTextField;
import javax.swing.table.DefaultTableModel;

public class MemberUI extends JFrame implements ActionListener, MouseListener {
	JTextField tfNo;
	JTextField tfName;
	JComboBox<String> comDept;
	JTextField tfMajor;

	ArrayList<String> arDept;
	HashMap<String, String> hashDept;
	HashMap<String, String> hashDeptRev;

	DefaultTableModel model;
	JTable table;

	public MemberUI() {
		this.setTitle("회원관리");
		this.setLayout(null);
		this.setResizable(false);
		this.setDefaultCloseOperation(JFrame.HIDE_ON_CLOSE);
		setSize(800, 600);

		JPanel panList = new JPanel();
		panList.setBounds(10, 10, 390, 550);
		panList.setBackground(Color.PINK);
		panList.setLayout(null);
		this.add(panList);

		MemberDAO dao = new MemberDAO();
		Vector<Object> vecs = dao.getListVector();
		Vector<Object> cols = dao.getTableHeader();
		model = new DefaultTableModel(vecs, cols);
		// model.setDataVector(vecs, cols);
		table = new JTable(model);
		JScrollPane scroll = new JScrollPane(table);
		scroll.setBounds(10, 10, 370, 530);
		panList.add(scroll);
		table.addMouseListener(this);

		JPanel panRight = new JPanel();
		panRight.setBounds(410, 10, 380, 550);
		panRight.setBackground(Color.GREEN);
		panRight.setLayout(null);
		this.add(panRight);

		// 학번
		JLabel lblNo = new JLabel("학번 : ");
		lblNo.setBounds(10, 10, 100, 25);
		panRight.add(lblNo);

		tfNo = new JTextField();
		tfNo.setBounds(120, 10, 100, 25);
		panRight.add(tfNo);

		// 이름
		JLabel lblName = new JLabel("이름 : ");
		lblName.setBounds(10, 50, 100, 25);
		panRight.add(lblName);

		tfName = new JTextField();
		tfName.setBounds(120, 50, 100, 25);
		panRight.add(tfName);

		// 학과
		JLabel lblDept = new JLabel("학과 : ");
		lblDept.setBounds(10, 90, 100, 25);
		panRight.add(lblDept);

		DeptDAO ddao = new DeptDAO();
		arDept = ddao.getListDeptCombo();
		//comDept = new JComboBox<String>(arDept.toArray(new String[arDept.size()]));
		comDept = new JComboBox<String>();
		comDept.setBounds(120, 90, 100, 25);
		panRight.add(comDept);

		hashDept = new HashMap<String, String>();
		hashDeptRev = new HashMap<String, String>();
		for(int i=0; i<arDept.size(); i++) {
			comDept.addItem(arDept.get(i).substring(3));
			hashDept.put(arDept.get(i).substring(3), arDept.get(i).substring(0,2));
			hashDeptRev.put(arDept.get(i).substring(0,2), arDept.get(i).substring(3));
		}

		// 출판사
		JLabel lblMajor = new JLabel("전공 : ");
		lblMajor.setBounds(10, 140, 100, 25);
		panRight.add(lblMajor);

		tfMajor = new JTextField();
		tfMajor.setBounds(120, 140, 100, 25);
		panRight.add(tfMajor);

		JButton btnSave = new JButton("저장");
		btnSave.setBounds(10, 510, 100, 25);
		panRight.add(btnSave);
		btnSave.addActionListener(this);

		this.setVisible(true);
	}

//	public static void main(String[] args) {
//		// TODO Auto-generated method stub
//		new MemberUI();
//	}

	@Override
	public void actionPerformed(ActionEvent e) {
		// TODO Auto-generated method stub
		MemberDAO dao = null;

		switch(e.getActionCommand()) {
		case "저장":
			String no = tfNo.getText();
			String name = tfName.getText();
			String dept = hashDept.get(comDept.getSelectedItem().toString());
			String major = tfMajor.getText();

			MemberDTO dto = new MemberDTO(no, name, dept, major);

			dao = new MemberDAO();
			dao.insert(dto);

			tfNo.setText("");
			tfName.setText("");
			//tfDept.setText("");
			tfMajor.setText("");

			model.setDataVector(dao.getListVector(), dao.getTableHeader());
			break;
		}
	}

	@Override
	public void mouseClicked(MouseEvent e) {
		// TODO Auto-generated method stub
		String no = table.getValueAt(table.getSelectedRow(), 0).toString();
		MemberDAO dao = new MemberDAO();
		MemberDTO dto = dao.getMemberDTO(no);

		tfNo.setText(dto.getNo());
		tfName.setText(dto.getName());
		//tfDept.setText(dto.getDept() + "");
		comDept.setSelectedItem(hashDeptRev.get(dto.getDept()));
		tfMajor.setText(dto.getMajor());
	}

	@Override
	public void mouseEntered(MouseEvent e) {
		// TODO Auto-generated method stub

	}

	@Override
	public void mouseExited(MouseEvent e) {
		// TODO Auto-generated method stub

	}

	@Override
	public void mousePressed(MouseEvent e) {
		// TODO Auto-generated method stub

	}

	@Override
	public void mouseReleased(MouseEvent e) {
		// TODO Auto-generated method stub

	}
}

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

175

176

177

178

179

180

181

182

183

184

185

186

187

188

189

package kr.co.moak.summer2020;

import java.awt.Color;

import java.awt.event.ActionEvent;

import java.awt.event.ActionListener;

import java.awt.event.MouseEvent;

import java.awt.event.MouseListener;

import java.util.ArrayList;

import java.util.HashMap;

import java.util.Vector;

import javax.swing.JButton;

import javax.swing.JComboBox;

import javax.swing.JFrame;

import javax.swing.JLabel;

import javax.swing.JPanel;

import javax.swing.JScrollPane;

import javax.swing.JTable;

import javax.swing.JTextField;

import javax.swing.table.DefaultTableModel;

public class MemberUI extends JFrame implements ActionListener, MouseListener {

JTextField tfNo;

JTextField tfName;

JComboBox<String> comDept;

JTextField tfMajor;

ArrayList<String> arDept;

HashMap<String, String> hashDept;

HashMap<String, String> hashDeptRev;

DefaultTableModel model;

JTable table;

public MemberUI() {

this.setTitle("회원관리");

this.setLayout(null);

this.setResizable(false);

this.setDefaultCloseOperation(JFrame.HIDE_ON_CLOSE);

setSize(800, 600);

JPanel panList = new JPanel();

panList.setBounds(10, 10, 390, 550);

panList.setBackground(Color.PINK);

panList.setLayout(null);

this.add(panList);

MemberDAO dao = new MemberDAO();

Vector<Object> vecs = dao.getListVector();

Vector<Object> cols = dao.getTableHeader();

model = new DefaultTableModel(vecs, cols);

// model.setDataVector(vecs, cols);

table = new JTable(model);

JScrollPane scroll = new JScrollPane(table);

scroll.setBounds(10, 10, 370, 530);

panList.add(scroll);

table.addMouseListener(this);

JPanel panRight = new JPanel();

panRight.setBounds(410, 10, 380, 550);

panRight.setBackground(Color.GREEN);

panRight.setLayout(null);

this.add(panRight);

// 학번

JLabel lblNo = new JLabel("학번 : ");

lblNo.setBounds(10, 10, 100, 25);

panRight.add(lblNo);

tfNo = new JTextField();

tfNo.setBounds(120, 10, 100, 25);

panRight.add(tfNo);

// 이름

JLabel lblName = new JLabel("이름 : ");

lblName.setBounds(10, 50, 100, 25);

panRight.add(lblName);

tfName = new JTextField();

tfName.setBounds(120, 50, 100, 25);

panRight.add(tfName);

// 학과

JLabel lblDept = new JLabel("학과 : ");

lblDept.setBounds(10, 90, 100, 25);

panRight.add(lblDept);

DeptDAO ddao = new DeptDAO();

arDept = ddao.getListDeptCombo();

//comDept = new JComboBox<String>(arDept.toArray(new String[arDept.size()]));

comDept = new JComboBox<String>();

comDept.setBounds(120, 90, 100, 25);

panRight.add(comDept);

hashDept = new HashMap<String, String>();

hashDeptRev = new HashMap<String, String>();

for(int i=0; i<arDept.size(); i++) {

comDept.addItem(arDept.get(i).substring(3));

hashDept.put(arDept.get(i).substring(3), arDept.get(i).substring(0,2));

hashDeptRev.put(arDept.get(i).substring(0,2), arDept.get(i).substring(3));

}

// 출판사

JLabel lblMajor = new JLabel("전공 : ");

lblMajor.setBounds(10, 140, 100, 25);

panRight.add(lblMajor);

tfMajor = new JTextField();

tfMajor.setBounds(120, 140, 100, 25);

panRight.add(tfMajor);

JButton btnSave = new JButton("저장");

btnSave.setBounds(10, 510, 100, 25);

panRight.add(btnSave);

btnSave.addActionListener(this);

this.setVisible(true);

}

// public static void main(String[] args) {

// // TODO Auto-generated method stub

// new MemberUI();

// }

@Override

public void actionPerformed(ActionEvent e) {

// TODO Auto-generated method stub

MemberDAO dao = null;

switch(e.getActionCommand()) {

case "저장":

String no = tfNo.getText();

String name = tfName.getText();

String dept = hashDept.get(comDept.getSelectedItem().toString());

String major = tfMajor.getText();

MemberDTO dto = new MemberDTO(no, name, dept, major);

dao = new MemberDAO();

dao.insert(dto);

tfNo.setText("");

tfName.setText("");

//tfDept.setText("");

tfMajor.setText("");

model.setDataVector(dao.getListVector(), dao.getTableHeader());

break;

}

@Override

public void mouseClicked(MouseEvent e) {

// TODO Auto-generated method stub

String no = table.getValueAt(table.getSelectedRow(), 0).toString();

MemberDAO dao = new MemberDAO();

MemberDTO dto = dao.getMemberDTO(no);

tfNo.setText(dto.getNo());

tfName.setText(dto.getName());

//tfDept.setText(dto.getDept() + "");

comDept.setSelectedItem(hashDeptRev.get(dto.getDept()));

tfMajor.setText(dto.getMajor());

}

@Override

public void mouseEntered(MouseEvent e) {

// TODO Auto-generated method stub

}

@Override

public void mouseExited(MouseEvent e) {

// TODO Auto-generated method stub

}

@Override

public void mousePressed(MouseEvent e) {

// TODO Auto-generated method stub

}

@Override

public void mouseReleased(MouseEvent e) {

// TODO Auto-generated method stub

}

LendDTO.java

package kr.co.moak.summer2020;

public class LendDTO {
	String no;
	String bno;
	String mno;
	long cdate;
	int days;


	public String getNo() {
		return no;
	}
	public void setNo(String no) {
		this.no = no;
	}
	public String getBno() {
		return bno;
	}
	public void setBno(String bno) {
		this.bno = bno;
	}
	public String getMno() {
		return mno;
	}
	public void setMno(String mno) {
		this.mno = mno;
	}
	public long getCdate() {
		return cdate;
	}
	public void setCdate(long cdate) {
		this.cdate = cdate;
	}
	public int getDays() {
		return days;
	}
	public void setDays(int days) {
		this.days = days;
	}

}

package kr.co.moak.summer2020;

public class LendDTO {

String no;

String bno;

String mno;

long cdate;

int days;

public String getNo() {

return no;

}

public void setNo(String no) {

this.no = no;

}

public String getBno() {

return bno;

}

public void setBno(String bno) {

this.bno = bno;

}

public String getMno() {

return mno;

}

public void setMno(String mno) {

this.mno = mno;

}

public long getCdate() {

return cdate;

}

public void setCdate(long cdate) {

this.cdate = cdate;

}

public int getDays() {

return days;

}

public void setDays(int days) {

this.days = days;

}

LendDAO.java

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 LendDAO {
	String tableName = "tlend";

	public Connection getConn() {
		Connection conn = null;

		try {
			Class.forName("org.sqlite.JDBC");
			conn = DriverManager.getConnection("jdbc:sqlite:" + MyConst.DB);
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		return conn;
	}

	public void insert(LendDTO dto) {
		Connection conn = null;
		PreparedStatement ps = null;
		String sql = "INSERT INTO " + tableName + "(no, bno, mno, days) VALUES (?, ?, ?, ?);";

		try {
			conn = getConn();
			ps = conn.prepareStatement(sql);
			ps.setString(1, dto.getNo());
			ps.setString(2, dto.getBno());
			ps.setString(3, dto.getMno());
			ps.setInt(4, dto.getDays());

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

	public void insert(ArrayList<LendDTO> arList) {
		Connection conn = null;
		PreparedStatement ps = null;
		String sql = "INSERT INTO " + tableName + "(no, bno, mno, days) VALUES (?, ?, ?, ?);";

		try {
			conn = getConn();
			for(int i=0; i<arList.size(); i++) {
				LendDTO dto = arList.get(i);
				ps = conn.prepareStatement(sql);
				ps.setString(1, dto.getNo());
				ps.setString(2, dto.getBno());
				ps.setString(3, dto.getMno());
				ps.setInt(4, dto.getDays());

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

	// 삭제
	public void delete(ArrayList<String> arNo) {
		StringBuffer sb = new StringBuffer();
		for(int i=0; i<arNo.size(); i++) {
			sb.append("'" + arNo.get(i) + "',");
		}
		String strNo = sb.toString();
		strNo = strNo.substring(0, strNo.length()-1);

		Connection conn = null;
		PreparedStatement ps = null;
		String sql = "DELETE FROM " + tableName + " WHERE no IN (" + strNo + ");";

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

	/** 대출목록 1개 출력 */
	public LendDTO getLendDTO(String no) {
		LendDTO dto = new LendDTO();

		Connection conn = null; // 연결
		PreparedStatement ps = null; // 명령
		ResultSet rs = null; // 결과
		try {
			conn = getConn();
			String sql = "select * from " + tableName + " WHERE no = ?;";
			ps = conn.prepareStatement(sql);
			ps.setString(1, no);
			rs = ps.executeQuery();
			if (rs.next()) {
				dto.setNo(rs.getString("no"));
				dto.setBno(rs.getString("bno"));
				dto.setMno(rs.getString("mno"));
				dto.setCdate(rs.getLong("cdate"));
				dto.setDays(rs.getInt("days"));
			} // while
			conn.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return dto;
	}// getLendDTO()


	/** 테이블의 캡션 출력 */
	public Vector<Object> getTableHeader() {
		Vector<Object> col = new Vector<Object>();
		col.add("번호");
		col.add("도서코드");
		col.add("학번");
		col.add("대출일");
		col.add("대출기간");

		return col;
	}

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

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

				row.add(rs.getString("no"));
				row.add(rs.getString("bno"));
				row.add(rs.getString("mno"));
				row.add(rs.getLong("cdate") + "");
				row.add(rs.getInt("days") + "");

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


}

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

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 LendDAO {

String tableName = "tlend";

public Connection getConn() {

Connection conn = null;

try {

Class.forName("org.sqlite.JDBC");

conn = DriverManager.getConnection("jdbc:sqlite:" + MyConst.DB);

} catch (Exception e) {

// TODO Auto-generated catch block

e.printStackTrace();

}

return conn;

}

public void insert(LendDTO dto) {

Connection conn = null;

PreparedStatement ps = null;

String sql = "INSERT INTO " + tableName + "(no, bno, mno, days) VALUES (?, ?, ?, ?);";

try {

conn = getConn();

ps = conn.prepareStatement(sql);

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

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

ps.setString(3, dto.getMno());

ps.setInt(4, dto.getDays());

ps.executeUpdate();

conn.close();

} catch (Exception e) {

// TODO Auto-generated catch block

e.printStackTrace();

}

public void insert(ArrayList<LendDTO> arList) {

Connection conn = null;

PreparedStatement ps = null;

String sql = "INSERT INTO " + tableName + "(no, bno, mno, days) VALUES (?, ?, ?, ?);";

try {

conn = getConn();

for(int i=0; i<arList.size(); i++) {

LendDTO dto = arList.get(i);

ps = conn.prepareStatement(sql);

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

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

ps.setString(3, dto.getMno());

ps.setInt(4, dto.getDays());

ps.executeUpdate();

}

conn.close();

} catch (Exception e) {

// TODO Auto-generated catch block

e.printStackTrace();

}

// 삭제

public void delete(ArrayList<String> arNo) {

StringBuffer sb = new StringBuffer();

for(int i=0; i<arNo.size(); i++) {

sb.append("'" + arNo.get(i) + "',");

}

String strNo = sb.toString();

strNo = strNo.substring(0, strNo.length()-1);

Connection conn = null;

PreparedStatement ps = null;

String sql = "DELETE FROM " + tableName + " WHERE no IN (" + strNo + ");";

try {

conn = getConn();

ps = conn.prepareStatement(sql);

ps.executeUpdate();

conn.close();

} catch (Exception e) {

// TODO Auto-generated catch block

e.printStackTrace();

}

/** 대출목록 1개 출력 */

public LendDTO getLendDTO(String no) {

LendDTO dto = new LendDTO();

Connection conn = null; // 연결

PreparedStatement ps = null; // 명령

ResultSet rs = null; // 결과

try {

conn = getConn();

String sql = "select * from " + tableName + " WHERE no = ?;";

ps = conn.prepareStatement(sql);

ps.setString(1, no);

rs = ps.executeQuery();

if (rs.next()) {

dto.setNo(rs.getString("no"));

dto.setBno(rs.getString("bno"));

dto.setMno(rs.getString("mno"));

dto.setCdate(rs.getLong("cdate"));

dto.setDays(rs.getInt("days"));

} // while

conn.close();

} catch (Exception e) {

e.printStackTrace();

}

return dto;

}// getLendDTO()

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

public Vector<Object> getTableHeader() {

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

col.add("번호");

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

col.add("학번");

col.add("대출일");

col.add("대출기간");

return col;

}

/** 도서목록 출력 */

public Vector<Object> getListVector(String mno) {

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

Connection conn = null; // 연결

PreparedStatement ps = null; // 명령

ResultSet rs = null; // 결과

try {

conn = getConn();

String sql = "select * from " + tableName + " WHERE mno=? order by cdate asc;";

ps = conn.prepareStatement(sql);

ps.setString(1, mno);

rs = ps.executeQuery();

while (rs.next()) {

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

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

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

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

row.add(rs.getLong("cdate") + "");

row.add(rs.getInt("days") + "");

data.add(row);

} // while

conn.close();

} catch (Exception e) {

e.printStackTrace();

}

return data;

}// getListVector()

}

LendUI.java

package kr.co.moak.summer2020;

import java.awt.Color;
import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;
import java.awt.event.MouseEvent;
import java.awt.event.MouseListener;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.TimeZone;
import java.util.Vector;

import javax.swing.JButton;
import javax.swing.JFrame;
import javax.swing.JLabel;
import javax.swing.JOptionPane;
import javax.swing.JPanel;
import javax.swing.JScrollPane;
import javax.swing.JTable;
import javax.swing.JTextField;
import javax.swing.table.DefaultTableModel;

public class LendUI extends JFrame implements ActionListener, MouseListener {
	JLabel lblNo;
	JLabel lblName;

	DefaultTableModel model_memb;
	JTable table_memb;

	DefaultTableModel model_book;
	JTable table_book;

	DefaultTableModel model_lend;
	JTable table_lend;

	public LendUI() {
		this.setTitle("대출/반납");
		this.setLayout(null);
		this.setResizable(false);
		this.setDefaultCloseOperation(JFrame.HIDE_ON_CLOSE);
		setSize(1000, 600);

		// 회원명
		lblNo = new JLabel("");
		lblNo.setBounds(10, 10, 100, 25);
		this.add(lblNo);

		// 이름
		lblName = new JLabel("");
		lblName.setBounds(120, 10, 100, 25);
		this.add(lblName);

		JPanel panLeft = new JPanel();
		panLeft.setBounds(10, 40, 390, 510);
		panLeft.setBackground(Color.PINK);
		panLeft.setLayout(null);
		this.add(panLeft);

		MemberDAO dao_memb = new MemberDAO();
		Vector<Object> vecs_memb = dao_memb.getListVector();
		Vector<Object> cols_memb = dao_memb.getTableHeader();
		model_memb = new DefaultTableModel(vecs_memb, cols_memb);
		// model.setDataVector(vecs, cols);
		table_memb = new JTable(model_memb);
		JScrollPane scroll_memb = new JScrollPane(table_memb);
		scroll_memb.setBounds(10, 10, 370, 200);
		panLeft.add(scroll_memb);
		table_memb.addMouseListener(this);

		LendableBookDAO dao_book = new LendableBookDAO();
		Vector<Object> vecs_book = dao_book.getListVector();
		Vector<Object> cols_book = dao_book.getTableHeader();
		model_book = new DefaultTableModel(vecs_book, cols_book);
		// model.setDataVector(vecs, cols);
		table_book = new JTable(model_book);

		JScrollPane scroll_book = new JScrollPane(table_book);
		scroll_book.setBounds(10, 220, 370, 280);
		panLeft.add(scroll_book);
		table_book.addMouseListener(this);

		JPanel panRight = new JPanel();
		panRight.setBounds(410, 40, 570, 510);
		panRight.setBackground(Color.GREEN);
		panRight.setLayout(null);
		this.add(panRight);

		LendBookDAO dao_lend = new LendBookDAO();
		Vector<Object> vecs_lend = null;//dao_lend.getListVector();
		Vector<Object> cols_lend = null;//dao_lend.getTableHeader();
		model_lend = new DefaultTableModel(vecs_lend, cols_lend);
		// model.setDataVector(vecs, cols);
		table_lend = new JTable(model_lend);

		JScrollPane scroll_lend = new JScrollPane(table_lend);
		scroll_lend.setBounds(10, 10, 550, 450);
		panRight.add(scroll_lend);
		table_lend.addMouseListener(this);

		JButton btnLend = new JButton("대출");
		btnLend.setBounds(10, 470, 100, 25);
		panRight.add(btnLend);
		btnLend.addActionListener(this);

		JButton btnReturn = new JButton("반납");
		btnReturn.setBounds(120, 470, 100, 25);
		panRight.add(btnReturn);
		btnReturn.addActionListener(this);

		this.setVisible(true);
	}

//	public static void main(String[] args) {
//		// TODO Auto-generated method stub
//		new LendUI();
//	}

	@Override
	public void actionPerformed(ActionEvent e) {
		// TODO Auto-generated method stub
	    SimpleDateFormat dateFormat = new SimpleDateFormat("yyyyMMddHHmmss");
        Calendar calCurday = Calendar.getInstance(TimeZone.getTimeZone("Asia/Seoul"));

        String mno = null;
        int[] selNo = null;
        ArrayList<String> arNo = null;

        LendDAO dao_lend = null;
        LendBookDAO dao_lendbook = null;
        LendableBookDAO dao_book = null;

        switch(e.getActionCommand()) {
		case "대출":
			mno = lblNo.getText();
			if(mno.compareTo("") == 0) {
				JOptionPane.showMessageDialog(this, "회원을 선택하세요");
				return;
			}

			if(table_book.getSelectedRowCount() <= 0) {
				JOptionPane.showMessageDialog(this, "대출할 도서들을 선택하세요");
				return;
			}

			selNo = table_book.getSelectedRows();
			arNo = new ArrayList<>();
			for(int i=0; i<selNo.length; i++) {
				arNo.add(table_book.getValueAt(selNo[i], 0).toString());
			}

			ArrayList<BookDTO> arListBook = (new BookDAO()).getBookDTO(arNo);
			ArrayList<LendDTO> arListLend = new ArrayList<>();

			for(int i=0; i<arListBook.size(); i++){
				LendDTO dto_lend = new LendDTO();
				dto_lend.setNo(dateFormat.format(calCurday.getTime()) + "_" + arListBook.get(i).no);
				dto_lend.setBno(arListBook.get(i).no);
				dto_lend.setMno(lblNo.getText());
				//dto_lend.setCdate(calCurday.getTimeInMillis());
				dto_lend.setDays((lblNo.getText().length() > 5)? 14 : 60);
				arListLend.add(dto_lend);
			}

			dao_lend = new LendDAO();
			dao_lend.insert(arListLend);


			dao_lendbook = new LendBookDAO();
			model_lend.setDataVector(dao_lendbook.getListVector(mno), dao_lendbook.getTableHeader());

			dao_book = new LendableBookDAO();
			model_book.setDataVector(dao_book.getListVector(), dao_book.getTableHeader());
			break;
		case "반납":
			mno = lblNo.getText();
			if(mno.compareTo("") == 0) {
				JOptionPane.showMessageDialog(this, "회원을 선택하세요");
				return;
			}
			if(table_lend.getSelectedRowCount() <= 0) {
				JOptionPane.showMessageDialog(this, "반납할 도서들을 선택하세요");
				return;
			}

			selNo = table_lend.getSelectedRows();
			arNo = new ArrayList<>();
			for(int i=0; i<selNo.length; i++) {
				arNo.add(table_lend.getValueAt(selNo[i], 0).toString());
			}

			dao_lend = new LendDAO();
			dao_lend.delete(arNo);

			dao_lendbook = new LendBookDAO();
			model_lend.setDataVector(dao_lendbook.getListVector(mno), dao_lendbook.getTableHeader());

			dao_book = new LendableBookDAO();
			model_book.setDataVector(dao_book.getListVector(), dao_book.getTableHeader());

			break;
		}
	}

	@Override
	public void mouseClicked(MouseEvent e) {
		// TODO Auto-generated method stub
		if(e.getSource().equals(table_memb)) {
			String mno = table_memb.getValueAt(table_memb.getSelectedRow(), 0).toString();
			MemberDAO dao = new MemberDAO();
			MemberDTO dto = dao.getMemberDTO(mno);

			lblNo.setText(dto.getNo());
			lblName.setText(dto.getName());

			LendBookDAO dao_lendbook = new LendBookDAO();
			model_lend.setDataVector(dao_lendbook.getListVector(mno), dao_lendbook.getTableHeader());

		}
	}

	@Override
	public void mouseEntered(MouseEvent e) {
		// TODO Auto-generated method stub

	}

	@Override
	public void mouseExited(MouseEvent e) {
		// TODO Auto-generated method stub

	}

	@Override
	public void mousePressed(MouseEvent e) {
		// TODO Auto-generated method stub

	}

	@Override
	public void mouseReleased(MouseEvent e) {
		// TODO Auto-generated method stub

	}

}

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

175

176

177

178

179

180

181

182

183

184

185

186

187

188

189

190

191

192

193

194

195

196

197

198

199

200

201

202

203

204

205

206

207

208

209

210

211

212

213

214

215

216

217

218

219

220

221

222

223

224

225

226

227

228

229

230

231

232

233

234

235

236

237

238

239

240

241

242

243

244

245

246

package kr.co.moak.summer2020;

import java.awt.Color;

import java.awt.event.ActionEvent;

import java.awt.event.ActionListener;

import java.awt.event.MouseEvent;

import java.awt.event.MouseListener;

import java.text.SimpleDateFormat;

import java.util.ArrayList;

import java.util.Calendar;

import java.util.TimeZone;

import java.util.Vector;

import javax.swing.JButton;

import javax.swing.JFrame;

import javax.swing.JLabel;

import javax.swing.JOptionPane;

import javax.swing.JPanel;

import javax.swing.JScrollPane;

import javax.swing.JTable;

import javax.swing.JTextField;

import javax.swing.table.DefaultTableModel;

public class LendUI extends JFrame implements ActionListener, MouseListener {

JLabel lblNo;

JLabel lblName;

DefaultTableModel model_memb;

JTable table_memb;

DefaultTableModel model_book;

JTable table_book;

DefaultTableModel model_lend;

JTable table_lend;

public LendUI() {

this.setTitle("대출/반납");

this.setLayout(null);

this.setResizable(false);

this.setDefaultCloseOperation(JFrame.HIDE_ON_CLOSE);

setSize(1000, 600);

// 회원명

lblNo = new JLabel("");

lblNo.setBounds(10, 10, 100, 25);

this.add(lblNo);

// 이름

lblName = new JLabel("");

lblName.setBounds(120, 10, 100, 25);

this.add(lblName);

JPanel panLeft = new JPanel();

panLeft.setBounds(10, 40, 390, 510);

panLeft.setBackground(Color.PINK);

panLeft.setLayout(null);

this.add(panLeft);

MemberDAO dao_memb = new MemberDAO();

Vector<Object> vecs_memb = dao_memb.getListVector();

Vector<Object> cols_memb = dao_memb.getTableHeader();

model_memb = new DefaultTableModel(vecs_memb, cols_memb);

// model.setDataVector(vecs, cols);

table_memb = new JTable(model_memb);

JScrollPane scroll_memb = new JScrollPane(table_memb);

scroll_memb.setBounds(10, 10, 370, 200);

panLeft.add(scroll_memb);

table_memb.addMouseListener(this);

LendableBookDAO dao_book = new LendableBookDAO();

Vector<Object> vecs_book = dao_book.getListVector();

Vector<Object> cols_book = dao_book.getTableHeader();

model_book = new DefaultTableModel(vecs_book, cols_book);

// model.setDataVector(vecs, cols);

table_book = new JTable(model_book);

JScrollPane scroll_book = new JScrollPane(table_book);

scroll_book.setBounds(10, 220, 370, 280);

panLeft.add(scroll_book);

table_book.addMouseListener(this);

JPanel panRight = new JPanel();

panRight.setBounds(410, 40, 570, 510);

panRight.setBackground(Color.GREEN);

panRight.setLayout(null);

this.add(panRight);

LendBookDAO dao_lend = new LendBookDAO();

Vector<Object> vecs_lend = null;//dao_lend.getListVector();

Vector<Object> cols_lend = null;//dao_lend.getTableHeader();

model_lend = new DefaultTableModel(vecs_lend, cols_lend);

// model.setDataVector(vecs, cols);

table_lend = new JTable(model_lend);

JScrollPane scroll_lend = new JScrollPane(table_lend);

scroll_lend.setBounds(10, 10, 550, 450);

panRight.add(scroll_lend);

table_lend.addMouseListener(this);

JButton btnLend = new JButton("대출");

btnLend.setBounds(10, 470, 100, 25);

panRight.add(btnLend);

btnLend.addActionListener(this);

JButton btnReturn = new JButton("반납");

btnReturn.setBounds(120, 470, 100, 25);

panRight.add(btnReturn);

btnReturn.addActionListener(this);

this.setVisible(true);

}

// public static void main(String[] args) {

// // TODO Auto-generated method stub

// new LendUI();

// }

@Override

public void actionPerformed(ActionEvent e) {

// TODO Auto-generated method stub

SimpleDateFormat dateFormat = new SimpleDateFormat("yyyyMMddHHmmss");

Calendar calCurday = Calendar.getInstance(TimeZone.getTimeZone("Asia/Seoul"));

String mno = null;

int[] selNo = null;

ArrayList<String> arNo = null;

LendDAO dao_lend = null;

LendBookDAO dao_lendbook = null;

LendableBookDAO dao_book = null;

switch(e.getActionCommand()) {

case "대출":

mno = lblNo.getText();

if(mno.compareTo("") == 0) {

JOptionPane.showMessageDialog(this, "회원을 선택하세요");

return;

}

if(table_book.getSelectedRowCount() <= 0) {

JOptionPane.showMessageDialog(this, "대출할 도서들을 선택하세요");

return;

}

selNo = table_book.getSelectedRows();

arNo = new ArrayList<>();

for(int i=0; i<selNo.length; i++) {

arNo.add(table_book.getValueAt(selNo[i], 0).toString());

}

ArrayList<BookDTO> arListBook = (new BookDAO()).getBookDTO(arNo);

ArrayList<LendDTO> arListLend = new ArrayList<>();

for(int i=0; i<arListBook.size(); i++){

LendDTO dto_lend = new LendDTO();

dto_lend.setNo(dateFormat.format(calCurday.getTime()) + "_" + arListBook.get(i).no);

dto_lend.setBno(arListBook.get(i).no);

dto_lend.setMno(lblNo.getText());

//dto_lend.setCdate(calCurday.getTimeInMillis());

dto_lend.setDays((lblNo.getText().length() > 5)? 14 : 60);

arListLend.add(dto_lend);

}

dao_lend = new LendDAO();

dao_lend.insert(arListLend);

dao_lendbook = new LendBookDAO();

model_lend.setDataVector(dao_lendbook.getListVector(mno), dao_lendbook.getTableHeader());

dao_book = new LendableBookDAO();

model_book.setDataVector(dao_book.getListVector(), dao_book.getTableHeader());

break;

case "반납":

mno = lblNo.getText();

if(mno.compareTo("") == 0) {

JOptionPane.showMessageDialog(this, "회원을 선택하세요");

return;

}

if(table_lend.getSelectedRowCount() <= 0) {

JOptionPane.showMessageDialog(this, "반납할 도서들을 선택하세요");

return;

}

selNo = table_lend.getSelectedRows();

arNo = new ArrayList<>();

for(int i=0; i<selNo.length; i++) {

arNo.add(table_lend.getValueAt(selNo[i], 0).toString());

}

dao_lend = new LendDAO();

dao_lend.delete(arNo);

dao_lendbook = new LendBookDAO();

model_lend.setDataVector(dao_lendbook.getListVector(mno), dao_lendbook.getTableHeader());

dao_book = new LendableBookDAO();

model_book.setDataVector(dao_book.getListVector(), dao_book.getTableHeader());

break;

}

@Override

public void mouseClicked(MouseEvent e) {

// TODO Auto-generated method stub

if(e.getSource().equals(table_memb)) {

String mno = table_memb.getValueAt(table_memb.getSelectedRow(), 0).toString();

MemberDAO dao = new MemberDAO();

MemberDTO dto = dao.getMemberDTO(mno);

lblNo.setText(dto.getNo());

lblName.setText(dto.getName());

LendBookDAO dao_lendbook = new LendBookDAO();

model_lend.setDataVector(dao_lendbook.getListVector(mno), dao_lendbook.getTableHeader());

}

@Override

public void mouseEntered(MouseEvent e) {

// TODO Auto-generated method stub

}

@Override

public void mouseExited(MouseEvent e) {

// TODO Auto-generated method stub

}

@Override

public void mousePressed(MouseEvent e) {

// TODO Auto-generated method stub

}

@Override

public void mouseReleased(MouseEvent e) {

// TODO Auto-generated method stub

}

LendableBookDAO.java

package kr.co.moak.summer2020;

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

public class LendableBookDAO {
	String tableName = "vlendablebook";

	public Connection getConn() {
		Connection conn = null;

		try {
			Class.forName("org.sqlite.JDBC");
			conn = DriverManager.getConnection("jdbc:sqlite:" + MyConst.DB);
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		return conn;
	}

	/** 도서1권 출력 */
	public BookDTO getBookDTO(String no) {
		BookDTO dto = new BookDTO();

		Connection con = null; // 연결
		PreparedStatement ps = null; // 명령
		ResultSet rs = null; // 결과
		try {
			con = getConn();
			String sql = "select * from " + tableName + " WHERE no = ?;";
			ps = con.prepareStatement(sql);
			ps.setString(1, no);
			rs = ps.executeQuery();
			if (rs.next()) {
				dto.setNo(rs.getString("no"));
				dto.setTitle(rs.getString("title"));
				dto.setPric(rs.getInt("pric"));
				dto.setPubl(rs.getString("publ"));
			} // while
		} catch (Exception e) {
			e.printStackTrace();
		}
		return dto;
	}// getBookDTO()


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

		return col;
	}

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

		Connection con = null; // 연결
		PreparedStatement ps = null; // 명령
		ResultSet rs = null; // 결과
		try {
			con = getConn();
			String sql = "select * from " + tableName + " order by title asc;";
			ps = con.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"));

				data.add(row);
			} // while
		} 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.Vector;

public class LendableBookDAO {

String tableName = "vlendablebook";

public Connection getConn() {

Connection conn = null;

try {

Class.forName("org.sqlite.JDBC");

conn = DriverManager.getConnection("jdbc:sqlite:" + MyConst.DB);

} catch (Exception e) {

// TODO Auto-generated catch block

e.printStackTrace();

}

return conn;

}

/** 도서1권 출력 */

public BookDTO getBookDTO(String no) {

BookDTO dto = new BookDTO();

Connection con = null; // 연결

PreparedStatement ps = null; // 명령

ResultSet rs = null; // 결과

try {

con = getConn();

String sql = "select * from " + tableName + " WHERE no = ?;";

ps = con.prepareStatement(sql);

ps.setString(1, no);

rs = ps.executeQuery();

if (rs.next()) {

dto.setNo(rs.getString("no"));

dto.setTitle(rs.getString("title"));

dto.setPric(rs.getInt("pric"));

dto.setPubl(rs.getString("publ"));

} // while

} catch (Exception e) {

e.printStackTrace();

}

return dto;

}// getBookDTO()

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

public Vector<Object> getTableHeader() {

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

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

col.add("제목");

col.add("가격");

col.add("출판사");

return col;

}

/** 도서목록 출력 */

public Vector<Object> getListVector() {

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

Connection con = null; // 연결

PreparedStatement ps = null; // 명령

ResultSet rs = null; // 결과

try {

con = getConn();

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

ps = con.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"));

data.add(row);

} // while

} catch (Exception e) {

e.printStackTrace();

}

return data;

}// getListVector()

}

LendBookDTO.java

package kr.co.moak.summer2020;

public class LendBookDTO {
	String no;
	String bno;
	String mno;
	String cdate;
	int days;
	String title;
	String name;


	public String getNo() {
		return no;
	}
	public void setNo(String no) {
		this.no = no;
	}
	public String getBno() {
		return bno;
	}
	public void setBno(String bno) {
		this.bno = bno;
	}
	public String getMno() {
		return mno;
	}
	public void setMno(String mno) {
		this.mno = mno;
	}
	public String getCdate() {
		return cdate;
	}
	public void setCdate(String cdate) {
		this.cdate = cdate;
	}
	public int getDays() {
		return days;
	}
	public void setDays(int days) {
		this.days = days;
	}
	public String getTitle() {
		return title;
	}
	public void setTitle(String title) {
		this.title = title;
	}
	public String getName() {
		return name;
	}
	public void setName(String name) {
		this.name = name;
	}

}

package kr.co.moak.summer2020;

public class LendBookDTO {

String no;

String bno;

String mno;

String cdate;

int days;

String title;

String name;

public String getNo() {

return no;

}

public void setNo(String no) {

this.no = no;

}

public String getBno() {

return bno;

}

public void setBno(String bno) {

this.bno = bno;

}

public String getMno() {

return mno;

}

public void setMno(String mno) {

this.mno = mno;

}

public String getCdate() {

return cdate;

}

public void setCdate(String cdate) {

this.cdate = cdate;

}

public int getDays() {

return days;

}

public void setDays(int days) {

this.days = days;

}

public String getTitle() {

return title;

}

public void setTitle(String title) {

this.title = title;

}

public String getName() {

return name;

}

public void setName(String name) {

this.name = name;

}

LendBookDAO.java

package kr.co.moak.summer2020;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.TimeZone;
import java.util.Vector;

public class LendBookDAO {
	String tableName = "vlendbook";

	public Connection getConn() {
		Connection conn = null;

		try {
			Class.forName("org.sqlite.JDBC");
			conn = DriverManager.getConnection("jdbc:sqlite:" + MyConst.DB);
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		return conn;
	}


	/** 대출목록 1개 출력 */
	public LendBookDTO getLendBookDTO(String no) {
		LendBookDTO dto = new LendBookDTO();

		Connection conn = null; // 연결
		PreparedStatement ps = null; // 명령
		ResultSet rs = null; // 결과
		try {
			conn = getConn();
			String sql = "select * from " + tableName + " WHERE no = ?;";
			ps = conn.prepareStatement(sql);
			ps.setString(1, no);
			rs = ps.executeQuery();
			if (rs.next()) {
				dto.setNo(rs.getString("no"));
				dto.setBno(rs.getString("bno"));
				dto.setTitle(rs.getString("title"));
				dto.setMno(rs.getString("mno"));
				dto.setName(rs.getString("name"));
				dto.setMno(rs.getString("mno"));
				dto.setCdate(rs.getString("cdate"));
				dto.setDays(rs.getInt("days"));
			} // while
			conn.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return dto;
	}// getLendBookDTO()


	/** 테이블의 캡션 출력 */
	public Vector<Object> getTableHeader() {
		Vector<Object> col = new Vector<Object>();
		col.add("번호");
		col.add("도서코드");
		col.add("도서명");
		col.add("학번");
		col.add("이름");
		col.add("대출일");
		col.add("대출기간");

		return col;
	}

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

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

				row.add(rs.getString("no"));
				row.add(rs.getString("bno"));
				row.add(rs.getString("title"));
				row.add(rs.getString("mno"));
				row.add(rs.getString("name"));
				row.add(rs.getString("cdate") + "");
				row.add(rs.getInt("days") + "");

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


}

100

101

102

103

104

105

106

107

108

109

package kr.co.moak.summer2020;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.text.SimpleDateFormat;

import java.util.Calendar;

import java.util.TimeZone;

import java.util.Vector;

public class LendBookDAO {

String tableName = "vlendbook";

public Connection getConn() {

Connection conn = null;

try {

Class.forName("org.sqlite.JDBC");

conn = DriverManager.getConnection("jdbc:sqlite:" + MyConst.DB);

} catch (Exception e) {

// TODO Auto-generated catch block

e.printStackTrace();

}

return conn;

}

/** 대출목록 1개 출력 */

public LendBookDTO getLendBookDTO(String no) {

LendBookDTO dto = new LendBookDTO();

Connection conn = null; // 연결

PreparedStatement ps = null; // 명령

ResultSet rs = null; // 결과

try {

conn = getConn();

String sql = "select * from " + tableName + " WHERE no = ?;";

ps = conn.prepareStatement(sql);

ps.setString(1, no);

rs = ps.executeQuery();

if (rs.next()) {

dto.setNo(rs.getString("no"));

dto.setBno(rs.getString("bno"));

dto.setTitle(rs.getString("title"));

dto.setMno(rs.getString("mno"));

dto.setName(rs.getString("name"));

dto.setMno(rs.getString("mno"));

dto.setCdate(rs.getString("cdate"));

dto.setDays(rs.getInt("days"));

} // while

conn.close();

} catch (Exception e) {

e.printStackTrace();

}

return dto;

}// getLendBookDTO()

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

public Vector<Object> getTableHeader() {

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

col.add("번호");

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

col.add("도서명");

col.add("학번");

col.add("이름");

col.add("대출일");

col.add("대출기간");

return col;

}

/** 도서목록 출력 */

public Vector<Object> getListVector(String mno) {

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

Connection conn = null; // 연결

PreparedStatement ps = null; // 명령

ResultSet rs = null; // 결과

try {

conn = getConn();

String sql = "select * from " + tableName + " WHERE mno=? order by cdate asc;";

ps = conn.prepareStatement(sql);

ps.setString(1, mno);

rs = ps.executeQuery();

while (rs.next()) {

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

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

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

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

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

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

row.add(rs.getString("cdate") + "");

row.add(rs.getInt("days") + "");

data.add(row);

} // while

conn.close();

} catch (Exception e) {

e.printStackTrace();

}

return data;

}// getListVector()

}

프로그램 샘플
1. 메인 화면
2. 도서
3. 회원
4. 대출/반납

whan0623

516