본문 바로가기
개발일지/Java + Spring

데이터베이스 자바에서 연결하기

연습용365 2021. 10. 8. 13:06

 

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

'개발일지/Java + Spring' Related Articles

티스토리툴바