

import java.io.*;
import java.sql.*;
public class Main {
public static void main(String[] args) {
Connection conn;
Statement stmt = null;
try {
Class.forName("com.mysql.cj.jdbc.Driver");
conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/" + "Application?useSSL=false&serverTimezone=Asia/Seoul", "root", "1234");
//JDBC 연결
System.out.println("DB 연결 완료");
stmt = conn.createStatement();
ResultSet srs = stmt.executeQuery("select * from schoolRegister");
printData(srs, "studentID", "name", "sex", "department", "grade", "applyDate", "phoneNumber");
srs.close();
stmt.close();
} catch (ClassNotFoundException e) {
System.out.println("JDBC 드라이버 로드 오류");
} catch (SQLException e) {
System.out.println("SQL 실행오류");
}
}
private static void printData(ResultSet srs, String col1, String col2, String col3,
String col4, String col5, String col6, String col7) throws SQLException {
while(srs.next()) {
System.out.println();
if(!col1.equals(""))
System.out.print(srs.getString("studentID"));
if(!col2.equals(""))
System.out.print(" " + srs.getString("name"));
if(!col3.equals(""))
System.out.print("\t" + srs.getString("sex"));
if(!col4.equals(""))
System.out.print("\t" + srs.getString("department"));
if(!col5.equals(""))
System.out.print("\t" + srs.getString("grade"));
if(!col6.equals(""))
System.out.print("\t" + srs.getString("applyDate"));
if(!col7.equals(""))
System.out.print("\t" + srs.getString("phoneNumber"));
else
System.out.println();
}
}
}
DB 연결 완료 201310777 서다희 여 화학과 1 2013-03-16 010-1111-1111 201310778 김승규 남 공업화학과 2 2012-03-27 010-2222-2222 201310779 민웅기 남 수학교육과 2 2012-03-26 010-3333-3333 201310780 나현선 여 화학과 4 2010-03-08 010-4444-4444 201310781 이현석 남 물리학과 3 2011-03-04 010-5555-5555 201834021 장세나 여 세무회계 4 2018-03-08 010-4556-7877 201915751 주여덜 남 방송영상 3 2019-03-15 010-7744-1145 202034345 박두리 남 차이나비지니스 2 2020-03-07 010-2416-4545 202144021 강하나 여 컴퓨터공학 1 2021-03-02 010-0000-1111 202175841 한너이 여 뷰티미용 1 2021-03-13 010-7563-9512 |


import java.io.*;
import java.sql.*;
public class grade {
public static void main(String[] args) {
// TODO Auto-generated method stub
Connection conn;
Statement stmt =null;
try {
Class.forName("com.mysql.cj.jdbc.Driver"); //MySQL 드라이버 로드
conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/" // 집에서 할 때는 로컬호스트 3307
+ "Application?useSSL=false&serberTimezone=Asia/Seoul","root","1234");
stmt = conn.createStatement(); //SQL문 처리용 Statement 객체생성
ResultSet srs = stmt.executeQuery("select grade.studentID, schoolRegister.name, subject, lessonYear, lessonTerm, score "
+ "from schoolRegister,grade where schoolRegister.studentID = grade.studentID"); //테이블의 모든 데이터 검색
printData(srs,"grade.studentID","schoolRegister.name","subject","lessonYear","lessonTerm","score");
//srs = stmtj.executeQuery("select bookid,bookname,publisher from Book");
//printData(srs,"bookid","bookname","publisher","price");
srs.close();
stmt.close();
}
catch(ClassNotFoundException e){
System.out.println("JDBC 드라이버 로드 오류");
}
catch(SQLException e) {
System.out.println("SQL 설정오류");
}
}
//레코드의 각 열의 값 화면에 출력
private static void printData(ResultSet srs, String col1, String col2, String col3,
String col4, String col5, String col6) throws SQLException{
while(srs.next()) {
System.out.println();
if(!col1.equals(""))
System.out.print(srs.getString("grade.studentID"));
if(!col2.equals(""));
System.out.print("\t" + srs.getString("schoolRegister.name"));
if(!col3.equals(""))
System.out.print("\t" + srs.getString("subject"));
if(!col4.equals(""))
System.out.print("\t" + srs.getString("lessonYear"));
if(!col5.equals(""))
System.out.print("\t" + srs.getString("lessonTerm"));
if(!col6.equals(""))
System.out.print("\t" + srs.getString("score"));
else
System.out.println();
}
}
}
CREATE DATABASE Application;
USE Application;
CREATE TABLE schoolRegister(
studentID VARCHAR(10) PRIMARY KEY,
name VARCHAR(20),
sex VARCHAR(4),
department VARCHAR(20),
grade int,
applyDate DATE,
phoneNumber VARCHAR(20));
INSERT INTO schoolRegister VALUES('202144021', '강하나', '여', '컴퓨터공학', 1, 20210302, '010-0000-1111');
INSERT INTO schoolRegister VALUES('202034345', '박두리', '남', '차이나비지니스', 2, 20200307, '010-2416-4545');
INSERT INTO schoolRegister VALUES('201834021', '장세나', '여', '세무회계', 4, 20180308, '010-4556-7877');
INSERT INTO schoolRegister VALUES('202175841', '한너이', '여', '뷰티미용', 1, 20210313, '010-7563-9512');
INSERT INTO schoolRegister VALUES('201915751', '주여덜', '남', '방송영상', 3, 20190315, '010-7744-1145');
INSERT INTO schoolRegister VALUES('201310777', '서다희', '여', '화학과', 1, 20130316, '010-1111-1111');
INSERT INTO schoolRegister VALUES('201310778', '김승규', '남', '공업화학과', 2, 20120327, '010-2222-2222');
INSERT INTO schoolRegister VALUES('201310779', '민웅기', '남', '수학교육과', 2, 20120326, '010-3333-3333');
INSERT INTO schoolRegister VALUES('201310780', '나현선', '여', '화학과', 4, 20100308, '010-4444-4444');
INSERT INTO schoolRegister VALUES('201310781', '이현석', '남', '물리학과', 3, 20110304, '010-5555-5555');
CREATE TABLE courseRegistration(
studentID VARCHAR(10) PRIMARY KEY,
subject VARCHAR(30),
lessonYear DATE,
lessonTerm int,
proffessor VARCHAR(20));
INSERT INTO courseRegistration VALUES('202144021', '컴퓨터의 역사와 이해', 20220302, 2, '나다섯');
INSERT INTO courseRegistration VALUES('202034345', '중국의 모든것', 20210402, 1, '오해단');
INSERT INTO courseRegistration VALUES('201834021', '재미있는 숫자놀이', 20200103, 2, '부여성');
INSERT INTO courseRegistration VALUES('202175841', '한끝차이 아름다움', 20230506, 1, '차일곰');
INSERT INTO courseRegistration VALUES('201915751', '영상제작끝내기', 20190712, 1, '구회말');
INSERT INTO courseRegistration VALUES('201310777', '유기화학1', 20130812, 1, '백두종');
INSERT INTO courseRegistration VALUES('201310778', '공업유기화학1', 20130820, 1, '강상욱');
INSERT INTO courseRegistration VALUES('201310779', '기하와벡터2', 20131102, 1, '이창선');
INSERT INTO courseRegistration VALUES('201310780', '무기화학2', 20131023, 1, '김어진');
INSERT INTO courseRegistration VALUES('201310781', '물리화학1', 20131004, 2, '장선희');
CREATE TABLE Tuition(
studentID VARCHAR(10) PRIMARY KEY,
studyYear DATE,
studyTerm int,
payMent int,
payDay DATE);
INSERT INTO Tuition VALUES('202144021', 20210302, 1, 3400000, 20220120);
INSERT INTO Tuition VALUES('202034345', 20200307, 2, 3700000, 20200822);
INSERT INTO Tuition VALUES('201834021', 20180308, 2, 2900000, 20190826);
INSERT INTO Tuition VALUES('202175841', 20210313, 1, 4200000, 20210121);
INSERT INTO Tuition VALUES('201915751', 20190315, 1, 3900000, 20200119);
INSERT INTO Tuition VALUES('201310777', 20130316, 1, 4000000, 20121230);
INSERT INTO Tuition VALUES('201310778', 20120327, 1, 3800000, 20130528);
INSERT INTO Tuition VALUES('201310779', 20120326, 1, 3000000, 20131228);
INSERT INTO Tuition VALUES('201310780', 20100308, 1, 4000000, 20121229);
INSERT INTO Tuition VALUES('201310781', 20110304, 2, 3200000, 20130525);
create table grade(
studentID varchar(10) primary key,
subject varchar(30),
lessonYear date,
lessonTerm int,
score int );
drop table grade;
select * from grade;
INSERT INTO grade VALUES('202144021', '컴퓨터의 역사와 이해', 20220421, 2, 98);
INSERT INTO grade VALUES('202034345', '중국의 모든것', 20210502, 1, 87);
INSERT INTO grade VALUES('201834021', '재미있는 숫자놀이', 2020603, 2, 78);
INSERT INTO grade VALUES('202175841', '한끝차이 아름다움', 20210531, 1, 69);
INSERT INTO grade VALUES('201915751', '영상제작끝내기', 20191101, 1, 81);
INSERT INTO grade VALUES('201310777', '화학과', 20131201, 1, 100);
INSERT INTO grade VALUES('201310778', '공업화학과', 20131130, 1, 50);
INSERT INTO grade VALUES('201310779', '수학교육과', 20131203, 1, 78);
INSERT INTO grade VALUES('201310780', '화학과', 20130913, 1, 84);
INSERT INTO grade VALUES('201310781', '물리학과', 20130506, 2, 90);
SELECT * FROM grade;
SELECT schoolRegister.studentID, name, sex, score FROM schoolRegister, grade WHERE sex = '여' AND schoolRegister.studentID = grade.studentID;
학과에 '화학'이 들어가는 사람들의 학번, 이름, 성별, 학과, 성적을 검색
SELECT schoolRegister.studentID, name, sex, department, score FROM schoolRegister, grade WHERE department LIKE '%화학%' and schoolRegister.studentID = grade.studentID;
'개발일지 > Java + Spring' 카테고리의 다른 글
예외처리(try-catch) (0) | 2021.10.14 |
---|---|
인터페이스 클래스 (0) | 2021.10.14 |
[Java] File 만들기/읽기 (0) | 2021.09.30 |
[Java] 거스름 돈 출력 - 예제 (0) | 2021.09.30 |
[Java] 추상클래스 (0) | 2021.09.30 |