Day21 - java에서 sql 사용하기

Java에서 Oracle query 사용하기

https://docs.oracle.com/en/database/oracle/oracle-database/21/jajdb/oracle/jdbc/OracleDriver.html

새로운 sql 파일 만들기

edit emp01.sql

=> 새로운 메모장이 열린다.

<메모장>

-- 주석
create table emp01(
sabun number,
name varchar2(5),
pay number
);

sql 파일 실행시키기

@ emp.sql

java Oracle driver 받기

접속하기

import oracle.jdbc.driver.OracleDriver;

public class InsertEmp {

    public static void main(String[] args) {
        String sql = "Insert into emp01 values (1111, 'user1', 1000)";
        OracleDriver driver = new oracle.jdbc.driver.OracleDriver();
        // 표준화 하기
        // jdbc:oracle:<drivertype>:@<database>
        String url = "jdbc:oracle:thin:@localhost:1521:xe";
        Properties info = new Properties();
        info.put ("user", "scott");
        info.put ("password","tiger");
        java.sql.Connection conn = null;

        try {
            System.out.println("접속전");
            conn = java.sql.DriverManager.getConnection(url, info);
            System.out.println("접속후" + (conn!=null));
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            try {
                if(conn != null) conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

statment 문장 처리하기

public class InsertEmp {

    public static void main(String[] args) {
        String sql = "Insert into emp01 values (2222, 'user2', 2000)";
        OracleDriver driver = new oracle.jdbc.driver.OracleDriver();
        // 표준화 하기
        // jdbc:oracle:<drivertype>:@<database>
        String url = "jdbc:oracle:thin:@localhost:1521:xe";
        Properties info = new Properties();
        info.put ("user", "scott");
        info.put ("password","tiger");
        java.sql.Connection conn = null;
        java.sql.Statement stmt = null;

        try {
            System.out.println("접속전");

            conn = java.sql.DriverManager.getConnection(url, info);
            stmt = conn.createStatement();
            stmt.executeUpdate(sql);

            System.out.println("입력완료");
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            try {
                if(stmt != null) stmt.close();
                if(conn != null) conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

사용자의 입력 문장 처리하기

Scanner sc = new Scanner(System.in);
System.out.print("사번 > ");
String input1 = sc.nextLine();
System.out.print("이름 > ");
String input2 = sc.nextLine();
System.out.print("금액 > ");
String input3 = sc.nextLine();

String sql = "Insert into emp01 values (" + input1 + ", '" + input2 + "'," + input3 + ")";

java를 이용한 기존의 DB 수정하기

public class UpdateEmp {

    public static void main(String[] args) {
        Scanner sc = new Scanner(System.in);
        System.out.print("사번 > ");
        String input1 = sc.nextLine();
        System.out.print("이름 > ");
        String input2 = sc.nextLine();
        System.out.print("금액 > ");
        String input3 = sc.nextLine();

        String sql = "update emp01 set name='" + input2 + "', pay=" + input3 + " where sabun=" + input1 + "";

        String url = "jdbc:oracle:thin:@localhost:1521:xe";
        Properties props = new Properties();
        props.setProperty("user", "scott");
        props.setProperty("password", "tiger");

        new OracleDriver();
        Connection conn = null;

        Statement stmt = null;

        try {
            System.out.println(sql);
            conn = DriverManager.getConnection(url, props);
            stmt = conn.createStatement();
            int result = stmt.executeUpdate(sql);
            if(result > 0) System.out.println("성공");
            else System.out.println("실패");
        } catch (SQLException e) {
            e.printStackTrace();
        }finally{
            try {
                if(stmt != null) stmt.close();
                if(conn != null) conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

java를 이용한 기존의 DB 삭제하기

public class DeleteEmp {

    public static void main(String[] args) {
        String sql = "delete from emp01 where sabun=1111";

        String url = "jdbc:oracle:thin:scott/tiger@localhost:1521:xe";
        Connection conn = null;
        Statement stmt = null;

        try {
            Class.forName("oracle.jdbc.driver.OracleDriver");
            conn = DriverManager.getConnection(url);

            stmt = conn.createStatement();
            int result = stmt.executeUpdate(sql);

            if(result > 0) System.out.println("성공");
            else System.out.println("실패");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            try {
                if(stmt != null) stmt.close();
                if(conn != null) conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

java를 이용한 select 문장 사용하기

public class SelectEmp {
    public static void main(String[] args) {
        String sql = "select * from emp01";

        String driver = "oracle.jdbc.driver.OracleDriver";
        String url = "jdbc:oracle:thin:@localhost:1521:xe";
        String user = "scott";
        String password = "tiger";

        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;

        try {
            Class.forName(driver);
            conn = DriverManager.getConnection(url, user, password);
            stmt = conn.createStatement();
            rs = stmt.executeQuery(sql);

            System.out.println("사번" + "\t" + "이름" + "\t" + "금액");
            while(rs.next()) {
                String col1 = rs.getString(1);
                String col2 = rs.getString(2);
                String col3 = rs.getString(3);
                System.out.println(col1 + "\t" + col2 + "\t" + col3);
            }

        } catch (SQLException e) {
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }finally {
            try {
                if(rs != null) rs.close();
                if(stmt != null) stmt.close();
                if(conn != null) conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

Web 에서 Java Oracle Database 사용하기

index

<%@ page language="java" contentType="text/html; charset=EUC-KR"
    pageEncoding="EUC-KR"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=EUC-KR">
<title>Insert title here</title>
</head>
<body>
    <table width="700" align="center">
        <tr>
            <td colspan="2" align="center">
            <h1>학생성적관리프로그램 (ver 0.11.0)</h1>
            </td>
        </tr>
        <tr>
            <td valign="top" bgcolor="#999999" width="100" align="center"><br>
                <a href="list.jsp" style="color:white;">보 기</a><br><br><br>
                <a href="add.jsp" style="color:white;">입 력</a><br><br><br>
                <a href="edit.jsp" style="color:white;">수 정</a><br><br><br>
                <a href="del.jsp" style="color:white;">삭 제</a><br><br><br>
                </td>
            <td>
                <!-- begin content -->
                <img height="300" alt="" src="https://encrypted-tbn0.gstatic.com/images?q=tbn:ANd9GcRvIdD74Dj1FwX3sOCRuwFgNlXNW64dknk8VTifSvZC4gsYJGEp7FKRiSO1NYp9BfVvToU&usqp=CAU:orig">
                <!-- end content -->
            </td>
        </tr>
        <tr>
            <td colspan="2" align="center" bgcolor="gray">
                <p>Copyright &copy; 2024 SYK Corp. All rights reserved.</p>
            </td>
        </tr>
    </table>
</body>
</html>

list

<%@ page language="java" contentType="text/html; charset=EUC-KR"
    pageEncoding="EUC-KR"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=EUC-KR">
<title>Insert title here</title>
</head>
<body>
    <table width="700" align="center">
        <tr>
            <td colspan="2" align="center">
            <h1>학생성적관리프로그램 (ver 0.11.0)</h1>
            </td>
        </tr>
        <tr>
            <td valign="top" bgcolor="#999999" width="100" align="center"><br>
                <a href="list.jsp" style="color:white;">보 기</a><br><br><br>
                <a href="add.jsp" style="color:white;">입 력</a><br><br><br>
                <a href="edit.jsp" style="color:white;">수 정</a><br><br><br>
                <a href="del.jsp" style="color:white;">삭 제</a><br><br><br>
                </td>
            <td align="center" valign="top">
                <!-- begin content -->
                <h2>성적 리스트</h2>
                <table width="90%" border="1" cellspacing="0">
                    <tr>
                        <td>학번</td>
                        <td>이름</td>
                        <td>국어</td>
                        <td>영어</td>
                        <td>수학</td>
                    </tr>
                    <%@ page import="java.sql.*" %>
                    <%
                    String sql = "select * from stu02 order by num asc";
                    String url = "jdbc:oracle:thin:@localhost:1521:xe";
                    String user = "scott";
                    String password = "tiger";
                    Class.forName("oracle.jdbc.driver.OracleDriver");

                    Connection conn = null;
                    Statement stmt = null;
                    ResultSet rs = null;

                    try{
                        conn = DriverManager.getConnection(url, user, password);
                        stmt = conn.createStatement();
                        rs = stmt.executeQuery(sql);

                        while(rs.next()) { %>
                    <tr>
                        <td><%=rs.getString(1) %></td>
                        <td><%=rs.getString(2) %></td>
                        <td><%=rs.getString(3) %></td>
                        <td><%=rs.getString(4) %></td>
                        <td><%=rs.getString(5) %></td>
                    </tr>
                    <%        } 
                        } finally{
                        if(rs != null) rs.close();
                        if(stmt != null) stmt.close();
                        if(conn != null) conn.close();
                    }%>
                </table>
                <!-- end content -->
            </td>
        </tr>
        <tr>
            <td colspan="2" align="center" bgcolor="gray">
                <p>Copyright &copy; 2024 SYK Corp. All rights reserved.</p>
            </td>
        </tr>
    </table>
</body>
</html>

edit

<%@ page language="java" contentType="text/html; charset=EUC-KR"
    pageEncoding="EUC-KR"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=EUC-KR">
<title>Insert title here</title>
</head>
<body>
    <table width="700" align="center">
        <tr>
            <td colspan="2" align="center">
            <h1>학생성적관리프로그램 (ver 0.11.0)</h1>
            </td>
        </tr>
        <tr>
            <td valign="top" bgcolor="#999999" width="100" align="center"><br>
                <a href="list.jsp" style="color:white;">보 기</a><br><br><br>
                <a href="add.jsp" style="color:white;">입 력</a><br><br><br>
                <a href="edit.jsp" style="color:white;">수 정</a><br><br><br>
                <a href="del.jsp" style="color:white;">삭 제</a><br><br><br>
                </td>
            <td align="center" valign="top">
                <!-- begin content -->
                <h2>학생 성적 수정</h2>
                <form action="insert.jsp">
                    <table>
                        <tr>
                            <td width="100" bgcolor="#999999">학번</td>
                            <td><input type="text" name="num"></td>
                        </tr>
                        <tr>
                            <td width="100" bgcolor="#999999">이름</td>
                            <td><input type="text" name="name"></td>
                        </tr>
                        <tr>
                            <td width="100" bgcolor="#999999">국어</td>
                            <td><input type="text" name="kor"></td>
                        </tr>
                        <tr>
                            <td width="100" bgcolor="#999999">영어</td>
                            <td><input type="text" name="eng"></td>
                        </tr>
                        <tr>
                            <td width="100" bgcolor="#999999">수학</td>
                            <td><input type="text" name="math"></td>
                        </tr>
                        <tr>
                            <td colspan="2" align="right">
                                <input type="submit" value="입력">
                                <input type="reset" value="취소">
                            </td>
                        </tr>
                    </table>
                </form>
                <!-- end content -->
            </td>
        </tr>
        <tr>
            <td colspan="2" align="center" bgcolor="gray">
                <p>Copyright &copy; 2024 SYK Corp. All rights reserved.</p>
            </td>
        </tr>
    </table>
</body>
</html>

insert

<%@ page language="java" contentType="text/html; charset=EUC-KR"
    pageEncoding="EUC-KR"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=EUC-KR">
<title>Insert title here</title>
</head>
<body>
<%@ page import="java.sql.*" %>
<%
    String num = request.getParameter("num");
    String name = request.getParameter("name");
    String kor = request.getParameter("kor");
    String eng = request.getParameter("eng");
    String math = request.getParameter("math");

    String sql = "Insert into stu02 values (" + num + ", '" + name + "', " + kor + ", " + eng + " , " + math + ")";
    Connection conn = null;
    Statement stmt = null;
    String driver = "oracle.jdbc.driver.OracleDriver";
    String url = "jdbc:oracle:thin:@localhost:1521:xe";
    String user = "scott";
    String password = "tiger";

    try{
        Class.forName(driver);
        conn = DriverManager.getConnection(url, user, password);
        stmt = conn.createStatement();
        stmt.executeUpdate(sql);
    } finally{
        if(stmt != null) stmt.close();
        if(conn != null) conn.close();
    }

    response.sendRedirect("list.jsp");
%>
</body>
</html>

del

<%@ page language="java" contentType="text/html; charset=EUC-KR"
    pageEncoding="EUC-KR"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=EUC-KR">
<title>Insert title here</title>
</head>
<body>
    <table width="700" align="center">
        <tr>
            <td colspan="2" align="center">
            <h1>학생성적관리프로그램 (ver 0.11.0)</h1>
            </td>
        </tr>
        <tr>
            <td valign="top" bgcolor="#999999" width="100" align="center"><br>
                <a href="list.jsp" style="color:white;">보 기</a><br><br><br>
                <a href="add.jsp" style="color:white;">입 력</a><br><br><br>
                <a href="edit.jsp" style="color:white;">수 정</a><br><br><br>
                <a href="del.jsp" style="color:white;">삭 제</a><br><br><br>
                </td>
            <td align="center" valign="top">
                <!-- begin content -->
                <h2>삭제할 학생</h2>
                <form action="delete.jsp">
                    <select>
                    <%@ page import="java.sql.*" %>
                    <%
                        String sql = "select num from stu02 order by num";
                        String driver = "oracle.jdbc.driver.OracleDriver";
                        String url = "jdbc:oracle:thin:@localhost:1521:xe";
                        String user = "scott";
                        String password = "tiger";

                        Connection conn = null;
                        Statement stmt = null;
                        ResultSet rs = null;
                        try{
                            conn = DriverManager.getConnection(url, user, password);
                            stmt = conn.createStatement();
                            rs = stmt.executeQuery(sql);
                            while(rs.next()){
                    %>
                        <option><%=rs.getString(1) %></option>
                    <%
                            }
                        }finally{
                        if(rs != null) rs.close();
                        if(stmt != null) stmt.close();
                        if(conn != null) conn.close();
                        }
                    %>
                    </select>
                    <input type="submit" value="삭제">
                </form>
                <!-- end content -->
            </td>
        </tr>
        <tr>
            <td colspan="2" align="center" bgcolor="gray">
                <p>Copyright &copy; 2024 SYK Corp. All rights reserved.</p>
            </td>
        </tr>
    </table>
</body>
</html>

delete

<%@ page language="java" contentType="text/html; charset=EUC-KR"
    pageEncoding="EUC-KR"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=EUC-KR">
<title>Insert title here</title>
</head>
<body>
<%@ page import="java.sql.*" %>
<%
    String num = request.getParameter("num");

    String sql = "delete from stu02 where num=" + num;
    Connection conn = null;
    Statement stmt = null;
    String driver = "oracle.jdbc.driver.OracleDriver";
    String url = "jdbc:oracle:thin:@localhost:1521:xe";
    String user = "scott";
    String password = "tiger";

    try{
        Class.forName(driver);
        conn = DriverManager.getConnection(url, user, password);
        stmt = conn.createStatement();
        stmt.executeUpdate(sql);
    } finally{
        if(stmt != null) stmt.close();
        if(conn != null) conn.close();
    }

    response.sendRedirect("list.jsp");
%>
</body>
</html>