Day09. Database와 Java 연동 예제 - 전화번호부

컬럼 만들기

test.sql (테이블이 만들어지는 상황)

CREATE TABLE 사업주
(사업주번호 INT, 사업주명 VARCHAR(5), 지점명 VARCHAR(5));

CREATE TABLE 대리점
(지점명 VARCHAR(5) PRIMARY KEY, 도시 VARCHAR(2), 전화번호 VARCHAR(10), 
 종업원수 INT, 자본금 INT, 지점개설일 INT);

 

 

값 집어넣기

firstDB.java

package database;

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

public class firstDB {
	   private static Connection conn;
	   private static PreparedStatement pstmt;
	   public static void main(String[] args) {
	      try {
	         conn = DriverManager.getConnection
             		("jdbc:mysql://localhost:3306/databaseprogramming","root","******");
	         pstmt = conn.prepareStatement("INSERT INTO 대리점 VALUES(?,?,?,?,?,?);");
	         pstmt.setString(1, "강서점"); // 물음표(?)가 여기의 1~6과 매칭된다.
	         pstmt.setString(2, "서울");
	         pstmt.setString(3, "201-1111");
	         pstmt.setInt(4, 5);
	         pstmt.setInt(5, 50000000);
	         pstmt.setInt(6, 19801001);
	         pstmt.executeUpdate();
	      } catch (SQLException e) {
	         e.printStackTrace();
	      }
	  }

}

 

넣은 값 찾아오기

secondDB.java

package database;

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

public class secondDB {
	 private static Connection conn;
	 private static PreparedStatement pstmt;
	   
	 public static void main(String[] args) {
	 
		 try {
			 conn = DriverManager.getConnection
             		("jdbc:mysql://localhost:3306/databaseprogramming","root","******");
			 Statement stmt = conn.createStatement();
			 ResultSet rs = stmt.executeQuery("SELECT*FROM 대리점");
			 
			 while(rs.next()) {
				 System.out.println(rs.getString("지점명") + "\t");
				 System.out.println(rs.getString("도시") + "\t");
				 System.out.println(rs.getString("전화번호") + "\t");
			 }
		 } catch (SQLException e) {
			 e.printStackTrace();
		 }
	}

}

 

 

문제, 전화번호부 만들기

package database;

import java.util.Scanner;
import java.sql.*;

public class Q1 {
	
	private static Connection conn; //mysql 연결
	private static PreparedStatement pstmt;
	

	public static void main(String[] args) throws SQLException {

		Scanner sc = new Scanner(System.in);
		
		conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/databaseprogramming", "root", "1234");
		
		while(true) {
			System.out.print("1.추가 2.삭제 3.전체출력 4.이름검색 5.종료 > ");
			
			int num = sc.nextInt();
			if(num==1) {
				System.out.print("이름 : ");
				String name = sc.next();
				System.out.print("전화번호");
				String tel = sc.next();
				System.out.print("주소 : ");
				String address = sc.next();
				
				pstmt = conn.prepareStatement("Insert into teltbl VALUES(?,?,?);");
				pstmt.setString(1, name);
				pstmt.setString(2, tel);
				pstmt.setString(3, address);
				pstmt.executeUpdate(); // 업데이트만 추가 때 사용 가능. 어차피 추가만 하면 되니까.
				
			} else if(num == 2) {
				System.out.print("이름 : ");
				String name = sc.next();
				pstmt = conn.prepareStatement("DELETE FROM telTBL WHERE NAME = ?;");
				pstmt.setString(1, name);
				int result = pstmt.executeUpdate();
				System.out.println(result);
				
				if(result == 0) {
					System.out.println("찾는 이름이 없습니다.");
				}
				else {
					System.out.println(name + "이(가) 삭제되었습니다.");
				}
								
			} else if(num == 3) {
				Statement stmt = conn.createStatement();
				ResultSet rs = stmt.executeQuery("SELECT * FROM telTBL");
				System.out.println("이름\t전화번호\t주소");
				while(rs.next()) {
					System.out.println(rs.getString("Name") + "\t" + rs.getString("TelNumber")
					+ "\t" + rs.getString("Address"));
				}
				
			} else if(num == 4) {
				System.out.print("이름 : ");
				String name = sc.next();
				
				pstmt = conn.prepareStatement("SELECT * FROM telTBL WHERE NAME =?;");
				pstmt.setString(1, name);
				ResultSet rs = pstmt.executeQuery();
				
				if(rs.getRow()!=0) {
					while(rs.next()) {
						System.out.println(rs.getString("Name") + "\t" + rs.getString("TelNumber")
						+ "\t" + rs.getString("Address"));
					}
				} else {
					System.out.println("찾는 사람이 없습니다.");
				}	
			} else if(num == 5) {
				System.out.println("프로그램이 종료됩니다.");
				break;
				
			} else {
				System.out.println("잘못된 입력입니다.");
				
			}
			
		}
	}

}