Efficient, Rapid, Accurate

💻 Programming/JSP

[JSP] Database CRUD (데이터베이스 연동)

오늘은 JSP를 이용하여 데이타베이스에 접속하고 SELECT, INSERT, DELETE, 그리고 UPDATE 하는 방법에 대해서 알아보도록 하겠습니다.

우선 데이타베이스를 설치가되어있고 emp계정이 만들어져 있으며 Employees테이블이 id, first, last, age 컬럼으로 이루어져 있다고 가정을 했습니다.

SELECT Operation:

JTSL을 이용하여 JSP 페이지를 작성합니다.

<%@ page import="java.io.*,java.util.*,java.sql.*"%> <%@ page import="javax.servlet.http.*,javax.servlet.*" %>
<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c"%>
<%@ taglib uri="http://java.sun.com/jsp/jstl/sql" prefix="sql"%> <html>

<head>
    <title>SELECT Operation</title>
</head>

<body>
    <sql:setDataSource var="snapshot" driver="com.mysql.jdbc.Driver" url="jdbc:mysql://localhost/TEST" user="emp"
        password="emp" />
    <sql:query dataSource="${snapshot}" var="result"> SELECT * from Employees; </sql:query>
    <table border="1" width="100%">
        <tr>
            <th>Emp ID</th>
            <th>First Name</th>
            <th>Last Name</th>
            <th>Age</th>
        </tr>
        <c:forEach var="row" items="${result.rows}">
            <tr>
                <td>
                    <c:out value="${row.id}" />
                </td>
                <td>
                    <c:out value="${row.first}" />
                </td>
                <td>
                    <c:out value="${row.last}" />
                </td>
                <td>
                    <c:out value="${row.age}" />
                </td>
            </tr>
        </c:forEach>
    </table>
</body>

</html>

위 JSP페이지에 접속하면 아래와 같은 결과가 나오겠죠?

Emp ID	First Name	Last Name	Age
100	Zara	Ali	18
101	Mahnaz	Fatma	25
102	Zaid	Khan	30
103	Sumit	Mittal	28

INSERT Operation:

역시나 JSTL을 이용한 JSP 페이지입니다.

<%@ page import="java.io.*,java.util.*,java.sql.*"%> <%@ page import="javax.servlet.http.*,javax.servlet.*" %>
<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c"%>
<%@ taglib uri="http://java.sun.com/jsp/jstl/sql" prefix="sql"%> <html>

<head>
    <title>JINSERT Operation</title>
</head>

<body>
    <sql:setDataSource var="snapshot" driver="com.mysql.jdbc.Driver" url="jdbc:mysql://localhost/TEST" user="emp"
        password="emp" />
    <sql:update dataSource="${snapshot}" var="result"> INSERT INTO Employees VALUES (104, 2, 'Nuha', 'Ali');
    </sql:update>
    <sql:query dataSource="${snapshot}" var="result"> SELECT * from Employees; </sql:query>
    <table border="1" width="100%">
        <tr>
            <th>Emp ID</th>
            <th>First Name</th>
            <th>Last Name</th>
            <th>Age</th>
        </tr>
        <c:forEach var="row" items="${result.rows}">
            <tr>
                <td>
                    <c:out value="${row.id}" />
                </td>
                <td>
                    <c:out value="${row.first}" />
                </td>
                <td>
                    <c:out value="${row.last}" />
                </td>
                <td>
                    <c:out value="${row.age}" />
                </td>
            </tr>
        </c:forEach>
    </table>
</body>

</html>

결과는 아래처럼 한줄이 추가가 되어 나오겠죠.

Emp ID	First Name	Last Name	Age
100	Zara	Ali	18
101	Mahnaz	Fatma	25
102	Zaid	Khan	30
103	Sumit	Mittal	28
104	Nuha	Ali	2

DELETE Operation:

<%@ page import="java.io.*,java.util.*,java.sql.*"%> <%@ page import="javax.servlet.http.*,javax.servlet.*" %>
<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c"%>
<%@ taglib uri="http://java.sun.com/jsp/jstl/sql" prefix="sql"%> <html>

<head>
    <title>DELETE Operation</title>
</head>

<body>
    <sql:setDataSource var="snapshot" driver="com.mysql.jdbc.Driver" url="jdbc:mysql://localhost/TEST" user="emp"
        password="emp" />
    <c:set var="empId" value="103" />
    <sql:update dataSource="${snapshot}" var="count"> DELETE FROM Employees WHERE Id = ?
        <sql:param value="${empId}" />
    </sql:update>
    <sql:query dataSource="${snapshot}" var="result"> SELECT * from Employees; </sql:query>
    <table border="1" width="100%">
        <tr>
            <th>Emp ID</th>
            <th>First Name</th>
            <th>Last Name</th>
            <th>Age</th>
        </tr>
        <c:forEach var="row" items="${result.rows}">
            <tr>
                <td>
                    <c:out value="${row.id}" />
                </td>
                <td>
                    <c:out value="${row.first}" />
                </td>
                <td>
                    <c:out value="${row.last}" />
                </td>
                <td>
                    <c:out value="${row.age}" />
                </td>
            </tr>
        </c:forEach>
    </table>
</body>

</html>

결과는 아래와 같습니다.

Emp ID	First Name	Last Name	Age
100	Zara	Ali	18
101	Mahnaz	Fatma	25
102	Zaid	Khan	30
104	Nuha	Ali	2

UPDATE Operation:

<%@ page import="java.io.*,java.util.*,java.sql.*"%> <%@ page import="javax.servlet.http.*,javax.servlet.*" %>
<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c"%>
<%@ taglib uri="http://java.sun.com/jsp/jstl/sql" prefix="sql"%> <html>

<head>
    <title>DELETE Operation</title>
</head>

<body>
    <sql:setDataSource var="snapshot" driver="com.mysql.jdbc.Driver" url="jdbc:mysql://localhost/TEST" user="emp"
        password="emp" />
    <c:set var="empId" value="102" />
    <sql:update dataSource="${snapshot}" var="count"> UPDATE Employees SET last = 'Ali'
        <sql:param value="${empId}" />
    </sql:update>
    <sql:query dataSource="${snapshot}" var="result"> SELECT * from Employees; </sql:query>
    <table border="1" width="100%">
        <tr>
            <th>Emp ID</th>
            <th>First Name</th>
            <th>Last Name</th>
            <th>Age</th>
        </tr>
        <c:forEach var="row" items="${result.rows}">
            <tr>
                <td>
                    <c:out value="${row.id}" />
                </td>
                <td>
                    <c:out value="${row.first}" />
                </td>
                <td>
                    <c:out value="${row.last}" />
                </td>
                <td>
                    <c:out value="${row.age}" />
                </td>
            </tr>
        </c:forEach>
    </table>
</body>

</html>

결과는 아래처럼 나오겠죠?

Emp ID	First Name	Last Name	Age
100	Zara	Ali	18
101	Mahnaz	Fatma	25
102	Zaid	Ali	30
104	Nuha	Ali	2

저작자표시 비영리 변경금지

'💻 Programming > JSP' 카테고리의 다른 글

JSP vs Javascript (Difference between JSP and Javascript) (0)	2019.06.28
[JSP] Security ( 보안 ) (0)	2019.02.15
[JSP] JSTL ( JSP Standard Tag Library ) (0)	2019.02.15
[JSP] Page Redirection ( 페이지 리디렉션 ) (0)	2019.02.15
[JSP] File Upload ( 파일 업로드 ) (0)	2019.02.15

💻 Programming/JSP

[JSP] Database CRUD (데이터베이스 연동)

SELECT Operation:

INSERT Operation:

DELETE Operation:

UPDATE Operation:

'💻 Programming > JSP' 카테고리의 다른 글

Category

Recent Posts

Recent Comments

티스토리툴바