티스토리 뷰
데이터베이스
MySQL 기본명령어
2.데이터 조작 명령어
데이터 조작 명령어는 사용자가 적절한 데이터 모델로 구성된 데이터에 접근하거나 데이터를 조작할 수 있도록 하는 언어를 말합니다. 데이터베이스 내의 데이터 연산을 위한 언어로 데이터베이스 내에 검색, 추가, 삭제, 갱신 작업이 가능합니다.
데이터 조작 명령어의 종류
명령어 설명 insert 데이터를 등록합니다. update 데이터를 수정합니다. delete 데이터를 삭제합니다. select 데이터를 조회,선택합니다.
데이터 등록하기
: 테이블에 새로운 데이터를 삽입하는 명령어는 insert입니다.
insert into 테이블 이름 [(필드 이름, 필드 이름, ... )] values (필드 값, 필드 값, ...)
ex) insert into sheet2 values("aaa", "a123",30,"aa");
데이터 조회하기
: 테이블에 존재하는 데이터를 검색하는 명령어는 select입니다.
select 필드 이름[,필드 이름, ...] from 테이블 이름 [where 검색 조건] [order by 필드 이름 [asc or desc]] [group by 필드 이름[, 필드 이름,...]] ...
ex)
모든 행 검색 : select * from sheet2;
column 필드의 데이터 검색 : select id, pw from sheet2;
모든 컬럼에서 30보다 큰 열만 가져오기.
select * from sheet2 where age>30;
위에 선착순 3줄만 출력
select * from sheet2 limit 3;
데이터 수정하기
: 테이블의 데이터를 수정(갱신)하는 명령어는 update입니다.
update 테이블 이름 set 필드 이름=필드 값[,필드 이름 = 필드 값, ...] [where 검색조건]
ex)update sheet2 set id="abcd" where id="aaa"; // sheet2 테이블에서 id가 aaa인 것을 abcd로 변경.
데이터 삭제하기
: 테이블의 데이터를 삭제하는 명령어는 delete입니다.
delete from 테이블 이름 [where 검색조건]
ex)delete from sheet2 where id="abcd"; // sheet2 테이블에서 id가 "abcd" 인 것을 삭제.
use jspbookdb_test; insert into sheet2 values("aaa","a123",30,"kwangsu"); insert into sheet2 values("bbb","a133",40,"minsu"); insert into sheet2 values("ccc","a143",50,"doyoung"); insert into sheet2 values("ddd","a153",60,"tealim"); insert into sheet2 values("eee","a163",70,"teayoung"); desc sheet2; select * from sheet2; -- *가로방향 검색 방법 'where' -- sheet2의 id 컬럼만 가져오기 select id from sheet2; -- sheet2의 id,age 컬럼만 가져오기 select id,age from sheet2; -- sheet2에서 id,passwd,age,name을 나오게 하기 select id,password,age,name from sheet2; -- 모든 컬럼에서 age가 30보다 큰 열만 가져오기. select * from sheet2 where age>30; -- 위에 선착순 3줄만 출력 select * from sheet2 limit 3; -- select 끝 -- 데이터 수정 -- id 전체를 "abcd로 변경 ../ safe모드때문에 헤제해야 바뀐다. -- update sheet2 set id="abcd"; -- sheet2 테이블에서 id 가 aaa인 것을 abcd로 변경 update sheet2 set id="abcd" where id="aaa"; -- sheet2 테이블에서 id가 "abcd" 인 것을 삭제하라 delete from sheet2 where id="abcd"; select * from sheet2;
JDBC로 데이터베이스와 JSP연동
JDBC
JDBC는 자바/JSP 프로그램 내에서 데이터베이스 내에서 데이터베이스와 관련된 작업을 처리할 수 있도록 도와주는 자바 표준 인터페이스로, 관계형 데이터베이스 시스템에 접근해 SQL문을 실행하기 위한 자바 API or 자바 라이브러리입니다.
JDBC API를 사용하면 DBMS의 종류에 상관없이 데이터베이스 작업을 처리할 수 있습니다.
JDBC를 사용한 JSP와 데이터베이스의 연동은 다음과 같은 단계로 프로그래밍이 됩니다.
- java.sql.* 패키지 임포트
- JDBC 드라이버 로딩
- 데이터베이스 접속을 위한 Connection 객체 생성
- 쿼리문을 실행하기 위한 객체 생성
- 쿼리 실행
- 쿼리 실행의 결과 값(int, ResultSet) 사용
- 사용된 객체가 역순으로 종료
JDBC드라이버 로딩 및 DBMS 접속
데이터베이스에 접근하는 첫 단계는 JDBC 드라이버를 로딩하는 것입니다.
JDBC드라이버를 로딩하고 나면 데이터베이스를 연결합니다. 후에 데이터베이스 관련 작업이 종료가 되면 데이터베이스연결이 해제됩니다.
JDBC 드라이버 로딩하기
JDBC 드라이버 로딩 단계에서는 드라이버 인터페이스를 구현하는 작업으로 Class.forName()메서드를 통해 JDBC드라이버를 로딩합니다. JDBC가 로딩이 되면 자동으로 객체가 생성이 되며, DriverManager클래스에 등록이 됩니다.
Class.forName(String className); // className은 JDBC 드라이버의 이름입니다.
ex) class.forName("com.mysql.jdbc.Driver");
*JDBC드라이버 로딩을 하는 다른 방법으로는 web.xml에 로딩을 하는 방법으로
<init-param>
<param-name>jdbcDriver</param-name>
<param-value>com.mysql.jdbc.Driver</param-value>
</init-param>
을 통해 미리 로딩을 할 수가 있습니다.
후에 설정한 드라이버 이름을 사용한다면 데이터베이스에 연결이 됩니다.
java.lang.Class.forName(config.getInitParameter("jdbcDriver"));
Connection 객체 생성하기
JDBC 드라이버에서 데이터베이스와 연결된 커넥션을 가져오기 위해 DriverManager 클래스의 getConnection()
메서드를 사용합니다. DriverManager클래스로 Connection 객체를 생성할 때 JDBC 드라이버를 검색하고, 검색된 드라이버를 이용해 Connection 객체를 생성한 후 이를 반환합니다.
static Connection getConnection(String url)
static Connection getConnection(String url, String user, String password)
static Connection getConnection(String url, Properties info)
데이터베이스 연결 닫기
데이터 베이스 연결이 더 이상 필요치 않다면 데이터베이스와 JDBC 리소스가 자동으로 닫힐 때까지 대기하는 것이 아니라 close()메서드를 생성한 Coneection 객체를 해제합니다. 일반적으로 데이터베이스 리소스를 사용하지 않기 위해서 사용을 끝내자마자 리소스를 해제하는 것이 좋습니다.
void close() throws SQLException
ex)
Connection conn = null;
if(conn != null)
{
conn.close();
}
<%@page import="java.sql.*"%> <%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%> <!DOCTYPE html> <html> <head> <meta charset="UTF-8"> <title>Insert title here</title> </head> <body> <% Connection conn = null; try { String url = "jdbc:mysql://localhost:3306/JSPBookDB"; String user = "root"; String password = "1234"; Class.forName("com.mysql.jdbc.Driver"); conn = DriverManager.getConnection(url, user, password); out.println("데이터베이스 연결이 성공했습니다."); } catch(SQLException ex) { out.println("데이터베이스 연결이 실패했습니다."); out.println("SQLException : " + ex.getMessage()); } finally { if(conn != null) { conn.close(); } } %> </body> </html>
데이터베이스 쿼리 실행
Connection 객체를 생성해 데이터베이스가 연결이 되었다면 쿼리실행 객체를 이용해 쿼리를 실행합니다. 쿼리 실행 객체는 Statement, PreparedStatement, CallableStatement로 3가지 입니다.
쿼리의 실행 성공이나 실패 여부와 상관없이 쿼리 실행 객체와 Coneection 객체의 리소스를 해제해야합니다.
Statement 객체로 데이터 접근하기
Statement 객체는 정적인 쿼리에 사용합니다. 이 객체는 하나의 쿼리를 사용하고나면 더는 사용을 할 수가 없습니다.(일회성) 하나의 쿼리를 끝낸다면 close()를 사용해 객체를 즉시 해제하여야 합니다.
*객체를 즉시 해제하지 않는다면 무시할 수 없는 공간이 필요하며, 페이지가 다른 작업을 수행하는 동안 멈추기 않기때문입니다 Statement 객체는 복잡하지 않은 간단한 쿼리문을 사용하는 경우에 좋습니다.
Statement createStatement() throws SELEception
객체를 생성한다면 Statement 객체의 executeQuery() 메서드나 executeUpdate()메서드를 호출해 쿼리문을 실행합니다.
Statement 객체의 메서드 종류
메서드 반환 유형 설명 executeQuery(String sql) ResultSet select문을 실행할 때 사용합니다.(ResultSet 객체 반환) executeUpdate(String sql) int 삽입, 수정, 삭제와 관련된 SQL 문 실행에 사용합니다. close() void Statement 객체를 반환할 때 사용합니다.
executeQuery() 메서드로 데이터 조회하기
: 이 메서드는 정적인 select 쿼리문을 통해 데이터를 검색하는 데 사용합니다.
ResultSet executeQuery(String sql) throws SQLException // sql은 데이터베이스에 보낼 쿼리문입니다.
executeUpdate() 메서드로 삽입, 수정, 삭제하기
: 이 메서드는 insert, update, delete 쿼리문을 통해서 데이터를 삽입, 수정, 삭제하는데 사용합니다.
int executeUpdate(String sql) throws SQLException // sql은 데이터베이스에 보낼 쿼리문입니다.
use jspbookdb;
create table if not exists member
(
id varchar(20) not null,
passwd varchar(20),
name varchar(30),
primary key(id)
);
select * from member;<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<body>
<form action="insert01_process.jsp" method="post" >
<p>아이디:<input type="text" name="id">
<p>비밀번호:<input type="password" name="passwd">
<p> 이름 : <input type="text" name="name">
<p><input type="submit" value="전송">
</form>
</body>
</html><%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<%@page import="java.sql.*"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<body>
<%
Connection conn = null;
String url = "jdbc:mysql://localhost:3306/jspbookdb";
String user = "root";
String password = "1234";
Class.forName("com.mysql.jdbc.Driver");
conn = DriverManager.getConnection(url,user,password);
%>
</body>
</html><%@page import="java.sql.*"%>
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<body>
<%@ include file="dbconn.jsp" %>
<%
request.setCharacterEncoding("utf-8");
String id = request.getParameter("id");
String passwd = request.getParameter("passwd");
String name = request.getParameter("name");
Statement stmt = null;
// PreparedStatement ppst = null;
try
{
String sql = "insert into member(id, passwd, name) values('" + id + "','" + passwd + "','" + name + "')";
// String sql2 = "insert into member(id,passwd,name) values(?,?,?)";
// ppst = conn.prepareStatement(sql2);
// ppst.setString(1,id);
// ppst.setString(2,passwd);
// ppst.setString(3,name);
// ppst.executeUpdate();
stmt = conn.createStatement();
stmt.executeUpdate(sql);
out.println("Member 테이블 삽입이 성공했습니다.");
}
catch(SQLException ex)
{
out.println("Member 테이블 삽입이 실패했습니다.<br>");
out.println("SQLException : " + ex.getMessage());
}
finally
{
if(stmt != null)
{
stmt.close();
}
if(conn != null)
{
conn.close();
}
}
%>
</body>
</html>
id는 member.sql 에서 Primary key 처리를 하였기 때문에 유일한 값을 가져야합니다. 따라서 동일한 id value를 설정한다면 아래와 같이 삽입에 실패했는 문구와 함께 삽입 처리가 되지 않습니다.
PreparedStatement 객체로 데이터 접근하기
PreparedStatement 객체는 동적인 쿼리에 사용합니다. preparedStatement 객체는 하나의 객체로 여러번의 쿼리를 실행할 수가 있으며(재사용가능), 동일한 쿼리문을 특정 값만 바꾸어서 여러번 실행해야 할 때, 매개변수가 많아서 쿼리문을 정리해야할 때 유용합니다.
PreparedStatement preparedStatement(String sql) throws SQLException // sql은 데이터베이스에 보낼 쿼리문.
쿼리문에 정해지지 않은 값을 물음표(?)로 표기하여 사용합니다. 이 물음표에 값을 할당하기 위해서 setXXX() 메서드를 사용하는데, 이 메서드는 2개의 매개변수로 설정한 물음표 위치(1부터 시작) 값과 실제 할당될 값을 가집니다. 이때 XXX는 필드의 데이터형입니다.
setXXX()메서드의 종류
메서드 반환 유형 설명 setString void 문자열인 경우 setInt void 정수형인 경우 SetLong void 정수형인 경우 setDouble void 실수형인 경우 setFloat void 실수형인 경우 setObject void 객체형인 경우 setDate void 날짜형인 경우 setTimestamp void 시간형인 경우