25: JAVA SQL

1. 채팅 프로그램 구현을 위한 기술적 요소 3가지를 나열하고 채팅 프로그래밍을 구현하시오.

• Network
• Thread
• IOStream

//
//code1
//
import java.net.*;
import java.io.*;
import java.util.*;
class Cosnt {
}
class Mut {
    static ServerSocket serverSocket = null;
    static Socket socket = null;
}
class Print {
    private static StringBuilder print = new StringBuilder();
    private static void reset() {
        print.setLength(0);
    }
    static void printAndReset() {
        System.out.print(print);
        reset();
    }
    static <T> void print(T t) {
        print.append(t);
        printAndReset();
    }
}
class Fn {
    static void runMain() {
        Mut.serverSocket = null;
        try {
            server();
        } catch(Exception e) {
            e.printStackTrace();
        };
    }
    private static void server() {
        try {
            Mut.serverSocket = new ServerSocket(7777);
            Print.print("서버가 작동되었습니다.\n");
//            
            while(true) {
                Mut.socket = Mut.serverSocket.accept();
//                
                Sender sender = new Sender(Mut.socket);
                Receiver receiver = new Receiver(Mut.socket);
//                
                sender.start();
                receiver.start();
            }
        } catch(IOException e) {
            e.printStackTrace();
        };
    }
}
class ServerMain {
    public static void main(String[] args) {
        Fn.runMain();
    }
}
class Sender extends Thread {
    Socket socket;
    DataOutputStream out;
    String name;
    public Sender(Socket socket) {
        super();
        this.socket = socket;
        try {
            out = new DataOutputStream(socket.getOutputStream());
            name = "[".concat(String.valueOf(socket.getInetAddress())).concat(String.valueOf(socket.getPort())).concat("]");
        } catch(Exception e) {
            e.printStackTrace();
        };
    }
    @Override
    public void run() {
        //Scanner scanner = new Scanner(System.in);
        Scanner scanner = new Scanner(System.in, "Cp949");
        while(out != null) {
            try {
                out.writeUTF(name + scanner.nextLine());
            } catch(Exception e) {
                e.printStackTrace();
                Print.print("Sender err\n");
                break;
            };
        };
    }
}
class Receiver extends Thread {
    Socket socket;
    DataInputStream in;
    public Receiver(Socket socket) {
        this.socket = socket;
        try {
            in = new DataInputStream(socket.getInputStream());
        } catch(Exception e) {
            e.printStackTrace();
        };
    }
    @Override
    public void run() {
        while(in != null) {
            try {
                System.out.println(in.readUTF());
            } catch(Exception e) {
                e.printStackTrace();
                Print.print("Receiver err\n");
                break;
            };
        };
    }
}

//
//code2
//
//javac --module-path javafx-sdk-21.0.1\lib --add-modules javafx.controls,javafx.fxml test6\Main.java
package test6;
import javafx.application.Application;
public class Main {
    public static void main(String[] args) {
        ChatClient.client();
        GUI.launch(GUI.class, args);
        System.exit(0);
    }
}

//
//code3
//
package test6;
//
import java.io.*;
import java.net.*;
//
import javafx.application.*;
import javafx.event.EventHandler;
import javafx.stage.Stage;
//
import javafx.scene.*;
import javafx.scene.control.TextField;
import javafx.scene.input.*;
import javafx.scene.paint.*;
import javafx.scene.shape.*;
import javafx.scene.text.*;
import javafx.scene.image.*;
//
class Const {
    static final String FILE_NAME_CHAT = "chat.txt";
    static final String FILE_NAME_RECEIVE = "receive.txt";
}
class Mut {
    static String chatStack = new String();
    static double textY = 60.;
    static Color color = Color.rgb(255, 255, 255);
}
class Print {
    private static StringBuilder print = new StringBuilder();
    private static void reset() {
        print.setLength(0);
    }
    static void printAndReset() {
        System.out.print(print);
        reset();
    }
    static <T> void print(T t) {
        append(t);
        printAndReset();
    }
    static <T> void append(T t) {
        print.append(t);
    }
    static String getString() {
        return print.toString();
    }
}
class ChatClient {
    static void client() {
        Socket socket = null;
        try {
            Fn.writeText("", Const.FILE_NAME_CHAT);
            Fn.writeText("", Const.FILE_NAME_RECEIVE);
//            
            socket = new Socket("localhost", 7777);
            Print.print("서버에 연결되었습니다.\n");
//            
            Sender2 sender = new Sender2(socket);
            Receiver2 receiver = new Receiver2(socket);
//            
            sender.start();
            receiver.start();
        } catch(Exception e) {
            e.printStackTrace();
        };
    }
}
class Sender2 extends Thread {
    Socket socket;
    DataOutputStream out;
    String name;
    public Sender2(Socket socket) {
        super();
        this.socket = socket;
        try {
            out = new DataOutputStream(socket.getOutputStream());
//            
            Print.append("[");
            Print.append(socket.getInetAddress());
            Print.append(socket.getPort());
            Print.append("]");
//            
            name = Print.getString();
        } catch(Exception e) {
            e.printStackTrace();
        };
    }
    @Override
    public void run() {
        while(out != null) {
            try {
                String chat = Fn.readText(Const.FILE_NAME_CHAT);
                if(chat.length() == 0) {
                    continue;
                } else {
                    String chatScreen = Fn.readText(Const.FILE_NAME_RECEIVE);
                    chatScreen = chatScreen.concat(chat);
                    Fn.writeText(chatScreen, Const.FILE_NAME_RECEIVE);
//                    
                    Print.append(chat);
//                    
                    out.writeUTF(name.concat(chat));
//                    
                    Fn.writeText("", Const.FILE_NAME_CHAT);
                };
            } catch(Exception e) {
                e.printStackTrace();
                Print.print("Sender err\n");
                break;
            };
        };
    }
}
class Receiver2 extends Thread {
    Socket socket;
    DataInputStream in;
    public Receiver2(Socket socket) {
        this.socket = socket;
        try {
            in = new DataInputStream(socket.getInputStream());
        } catch(Exception e) {
            e.printStackTrace();
        };
    }
    @Override
    public void run() {
        while(in != null) {
            try{
                String receive = in.readUTF();
//                
                Mut.chatStack = Fn.readText(Const.FILE_NAME_RECEIVE);
                Mut.chatStack = Mut.chatStack.concat(receive).concat("\n");
//                
                Fn.writeText(Mut.chatStack, Const.FILE_NAME_RECEIVE);
            } catch(Exception e) {
                e.printStackTrace();
                Print.print("Receiver err\n");
                break;
            };
        };
    }
}
class Fn {
    static void writeText(String chat, String path) {
        try(BufferedWriter bufferedWriter = new BufferedWriter(new FileWriter(path))) {
            bufferedWriter.write(chat);
        } catch(IOException e) {
            e.printStackTrace();
        };
    }
    static String readText(String path) {
        String chat = "";
        try(BufferedReader bufferedReader = new BufferedReader(new FileReader(path))) {
            while(bufferedReader.ready()) {
                String temp = bufferedReader.readLine();
//                
                chat = chat.concat(temp);
                chat = chat.concat("\n");
            };
        } catch(IOException e) {
            e.printStackTrace();
        };
        return chat;
    }
}
public class GUI extends Application {
    @Override
    public void start(Stage stage) throws FileNotFoundException {
        Socket socket;
        DataOutputStream out;
        String name;
//        
        Image image = new Image(new FileInputStream("bg.png"));
        int width = (int)image.getWidth();
        int height = (int)image.getHeight();
//        
        Font font = Font.loadFont("file:DungGeunMo.ttf", 16);
//        
        Text text = new Text();
        text.setText("text initialized");
        text.setFont(font);
        text.setFill(Mut.color);
        text.setX(0);
        text.setY(Mut.textY);
//        
        PixelReader pixelReader = image.getPixelReader();
//        
        WritableImage wImage = new WritableImage(width, height);
        PixelWriter writer = wImage.getPixelWriter();
//        
        for(int y = 0; y < height; y++) {
            for(int x = 0; x < width; x++) {
                Color c = pixelReader.getColor(x, y);
                writer.setColor(x, y, c);
            };
        };
        ImageView imageView = new ImageView(wImage);
//        
        Rectangle rectangle = new Rectangle();
        rectangle.setY(7);
        rectangle.setWidth(width);
        rectangle.setHeight(30);
        rectangle.setFill(Mut.color);
        rectangle.setOpacity(0.5);
//        
        TextField textField = new TextField();
        textField.setLayoutX(0);
        textField.setLayoutY(height - 30);
        textField.setPrefWidth(width);
//        
        EventHandler<KeyEvent> eventHandlerTextField = new EventHandler<KeyEvent>() {
            @Override
            public void handle(KeyEvent e) {
                String tempRead = Fn.readText(Const.FILE_NAME_RECEIVE);
                text.setText(tempRead);
//                
                KeyCode keyCode = e.getCode();
//                
                if(keyCode == KeyCode.ENTER) {
                    String chat = textField.getText();
                    System.out.println(chat);
//                    
                    Fn.writeText(chat, Const.FILE_NAME_CHAT);
//                    
                    textField.clear();
                } else if(keyCode == KeyCode.ESCAPE) {
                    Platform.exit();
                    System.exit(0);
                };
            }
        };
        textField.addEventHandler(KeyEvent.KEY_PRESSED, eventHandlerTextField);
//        
        EventHandler<MouseEvent> eventHandlerRectangle = new EventHandler<MouseEvent>() {
            @Override
            public void handle(MouseEvent e) {
                MouseButton mouseButton = e.getButton();
                switch(mouseButton) {
                    case MouseButton.PRIMARY: {
                        Mut.color = Color.rgb(255, 0, 0);
                        break;
                    }
                    case MouseButton.SECONDARY: {
                        Mut.color = Color.rgb(0, 0, 255);
                        break;
                    }
                    case MouseButton.MIDDLE: {
                        Mut.color = Color.rgb(0, 255, 0);
                        break;
                    }
                    default: {
                    }
                };
                text.setFill(Mut.color);
                rectangle.setFill(Mut.color);
                rectangle.setOpacity(0.5);
            }
        };
        rectangle.addEventHandler(MouseEvent.MOUSE_CLICKED, eventHandlerRectangle);
//        
        EventHandler<ScrollEvent> eventHandlerRectangle2 = new EventHandler<ScrollEvent>() {
            @Override
            public void handle(ScrollEvent e) {
                double wheel = e.getDeltaY();
                Mut.textY += wheel;
                text.setY(Mut.textY);
            }
        };
        rectangle.addEventHandler(ScrollEvent.SCROLL, eventHandlerRectangle2);
//        
        EventHandler<MouseEvent> eventHandlerImageView = new EventHandler<MouseEvent>() {
            @Override
            public void handle(MouseEvent e) {
                String tempRead = Fn.readText(Const.FILE_NAME_RECEIVE);
                text.setText(tempRead);
            }
        };
        imageView.addEventHandler(MouseEvent.MOUSE_MOVED, eventHandlerImageView);
//        
        Group root = new Group();
        root.getChildren().add(imageView);
        root.getChildren().add(text);
        root.getChildren().add(textField);
        root.getChildren().add(rectangle);
//        
        Scene scene = new Scene(root, width, height);
//        
        stage.setResizable(false);
        stage.setTitle("Test6");
        stage.setScene(scene);
        stage.show();
    }
}

2.DBMS 란?

• DataBase Management System
• ex) Oracle database, MySQL

3.아래의 SQL 구문을 작성하시오.

1) DEPT 테이블이 구성 확인
2) 모든 emp 정보 출력
3) 사원테이블(EMP)에서 급여 (SAL)가 3000 이상인 사원을 출력하는 쿼리문->(DEPT면답없음)
4) 비교 연산자를 사용하여 부서 번호(DEPTNO)가 20인 사원에 관한 정보만 출력
5) 이름(ENAME)이 FORD인 사람의 사번(empno), 이름(ename), 급여(SAL)를 출력
6) 사원번호 10번 그리고 JOB 이 MANAGER 인 사원정보
7) DEPTNO 가 10 이 아닌 사원들 정보 출력
8) 급여가 2000~3000 사이의 사원
9) 1987년에 입사한 사원을 출력

--1) DEPT 테이블이 구성 확인
desc dept;
--2) 모든 emp 정보 출력
select * from emp;
--3) 사원테이블(EMP)에서 급여 (SAL)가 3000 이상인 사원을 출력하는 쿼리문->(DEPT면답없음)
select * from emp where sal >= 3000;
--4) 비교 연산자를 사용하여 부서 번호(DEPTNO)가 20인 사원에 관한 정보만 출력
select * from emp where deptno = 20;
--5) 이름(ENAME)이 FORD인 사람의 사번(empno), 이름(ename), 급여(SAL)를 출력
select empno, ename, sal from emp where ename = 'FORD';
--6) 사원번호 10번 그리고 JOB 이 MANAGER 인 사원정보
select * from emp where deptno = 10 and job = 'MANAGER';
--7) DEPTNO 가 10 이 아닌 사원들 정보 출력
select * from emp where not deptno = 10;
--8) 급여가 2000~3000 사이의 사원
select * from emp where sal between 2000 and 3000;
--9) 1987년에 입사한 사원을 출력
select * from emp where hiredate between '01-JAN-87' and '31-DEC-87';