[자바 소스] JDBC를 이용한 sqlplus 구현하기! 이름하여 sqlminus~!!!

package minus;

 

import java.io.*;

import java.sql.*;

 

import conn.*;

 

public class SqlMinus {

   

    BufferedReader reader = new BufferedReader(new InputStreamReader(System.in));

   

    String[] reservedWords = {"select","delete","update","insert","alter","drop","create","modify"};        // 예약어 등록

   

    public static void main(String[] args) {

        new SqlMinus().execute();

    }

 

    public void execute() {

        // select, insert, update, delete

       

        System.out.println("Connected to:");

        System.out.println("Oracle Database 11g Express Edition Release 11.2.0.2.0 - Production");

 

        StringBuilder sb = new StringBuilder();

       

 

        while (true) {

            System.out.print("SQL > ");

       

            try {

                String query = "";

                String sql = reader.readLine().trim();

                    if(sql.length()>0) {                                   // 길이가 1이상이면

                       

                        if(sb.length()==0) {

                           

                            if(!isReserved(sql)) {

                                System.out.println("올바른 쿼리문을 작성하세요");

                                continue ;

                            }

                        }

                       

                        if(sql.equalsIgnoreCase("exit")) {

                            System.out.println("Disconnected from Oracle Database 11g Express Edition Release 11.2.0.2.0 - Production");

                            System.exit(0);

                        }

                       

                        if(sql.endsWith(";")) {                 // ;로 끝나면

                            sb.append(sql.replace(";", "")+" ");

 

                            System.out.println("실행 쿼리문 : "+sb.toString());

                           

                            query = sb.toString().substring(0,6);

                           

                            if(query.equalsIgnoreCase("select")) {

                                int result = execSelect(sb.toString());

                                System.out.println(result + "행이 검색되었습니다.");

                                sb.delete(0, sb.length());

                            }else if(query.equals("delete")) {

                                int result = execDML(sb.toString());

                                System.out.println(result + " 행이 삭제되었습니다.");

                                sb.delete(0, sb.length());

                            }else if(query.equals("update")) {

                                int result = execDML(sb.toString());

                                System.out.println(result + " 행이 수정되었습니다.");

                                sb.delete(0, sb.length());

                            }else if(query.equals("insert")) {

                                int result = execDML(sb.toString());

                                System.out.println(result + " 행이 입력되었습니다.");

                                sb.delete(0, sb.length());

                            }else {

                                System.out.println("미지원입니다.");

                            }

                       

                        } else {                                // 쿼리문이 끝나지 않으면

                           

                            sb.append(sql+" ");

                        }

                       

                    }

                    else {

                       

                    }

               

//                System.out.println(sql.trim().toLowerCase().matches("^\\s*(select).*"));  // 정규식 표현

               

            } catch (IOException e) {

                e.printStackTrace();

            }

        }

    }//end execute()

   

    // 예약어로 시작하는지 판별

    public boolean isReserved(String sql) {

        boolean result = false;

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

            if(sql.toLowerCase().startsWith(reservedWords[i])) {

                result = true;

            }

        }

        return result;

    }

   

    // select

    public int execSelect(String sql) {

       

        int count = 0;

        try (

                Connection conn = MyConn.getConnection();

                Statement stmt = conn.createStatement();

                ResultSet rs = stmt.executeQuery(sql);

            )

           

            {

                ResultSetMetaData rsmd = rs.getMetaData();

                int colCount = rsmd.getColumnCount();

                for(int i=1; i<=colCount; i++) {

                    System.out.printf("%10s \t", rsmd.getColumnLabel(i));

                }

                System.out.println();

               

                while (rs.next()) {

                    for (int i = 1; i <= colCount; i++) {

                        System.out.printf("%10s \t",rs.getString(i));

                    }

                    System.out.println();

                    count++;

                }

           

           

            } catch (Exception e) {

                   System.out.println(e.getMessage());

            }

        return count;

 

    }//end execSelect()

   

   

    // insert, delete, update

    public int execDML(String sql) {

               

        int result = 0;

       

        try (

                Connection conn = MyConn.getConnection();

                Statement stmt = conn.createStatement();

            )

           

            {

                result = stmt.executeUpdate(sql);

           

            } catch (Exception e) {

                   System.out.println(e.getMessage());

            }

       

        return result;

 

    }//end execDML()

   

    // insert, delete, update

    public void execQuery(String sql) {

 

        Connection conn = null;

        Statement stmt = null;

        try {

            conn = MyConn.getConnection();

            stmt = conn.createStatement();

            stmt.executeUpdate(sql);

        } catch (SQLException e) {

           

            e.printStackTrace();

        } finally {

            try { if(stmt!=null) stmt.close(); } catch (Exception e) { e.printStackTrace(); }

            try { if(conn!=null) conn.close(); } catch (Exception e) { e.printStackTrace(); }

           

        }

 

    }//end execDML()

 

}