JSP 공부 <5> - 데이터 베이스 생성 과 SQL

1. 데이터 베이스 생성

데이터 베이스를 생성하기 위해서는 먼저 root 계정으로 DB에 연결해야한다. 

이후 다음의 쿼리를 실행하자

create database 데이터베이스명 default character set utf8;

이후에 사용자를 추가와 권한 부여를 해준다.

create user '사용자명칭'@'localhost' identified by '패스워드';

grant all privileges on 데이터베이스명.* to '사용자명칭'@'localhost';

create user '사용자명칭'@'%' identified by '패스워드';

grant all privileges on 데이터베이스명.* to '사용자명칭'@'%';

Note:

1. localhost는 로컬에서 해당 계정을 접근하는 경우 이고 %는 모든 호스트에서 해당 게정을 접근할 때 해당 암호를 사용한다는 것을 의미한다.

여기서 중요한 것은 모든호스트가 로컬 호스트를 포함하지 않는다는 점이다. 그렇기 때문에 따로 설정해주는 점을 주목하자

2. grant 쿼리는 계정 권한으로 grant 권한목록 on 데이터베이스명.대상 to '사용자명칭'@'호스트';  형태를 이룬다. all privileges는 모든권한을 의미한다. 특정 권한의 경우에는  해당 권한을 전부 적어주면된다

grant select, insert, update, create , drop on 데이터베이스명.대상 to '사용자명칭'@'호스트';   이런식으로 전부 적어주면 되겠다

또 대상의 * 는 전체 대상(모든 테이블)을 의미한다

---

2. SQL 기초

 

먼저 SQL의 타입을 알아보자

SQL 타입	설명
CHAR	확정 길이의 문자열을 저장(표준 255글자)
VARCHAR	가변 길이의 문자열을 저장한다.(표준 255글자)
LONG VARCHAR	긴 가변 길이의 문자열을 저장
NUMERIC	숫자를 저장
DECIMAL	십진수를 저장
INTEGER	정수를 저장
TIMESTAMP	날짜와 시간을 저장
TIME	시간을 저장
DATE	날짜를 저장
CLOB	대량의 문자열 데이터를 저장한다.
BLOB	대량의 이진 데이터를 저장한다.

NOTE:

표준 SQL 타입이 존재하긴 하지만 DBMS는 표준 SQL 타입뿐만 아니라 DBMS 자체적으로 확장 타입을 추가 제공한다.

다음으로 기본적인 쿼리문들을 알아보자

1. 생성

create table 테이블명(

[필드명] [타입] [조건],

...

)engine = InnoDB default character set = utf8;

NOTE : InnoDB 저장 엔진을 사용하여 DB를 생성한다는 뜻으로 트랜잭션이 올바르게 처리되기 위함이다.

2. 조회

SELECT 필드1,필드2... FROM 테이블명 ;

SELECT 필드1,필드2... FROM 테이블명 WHERE 필드1='조건' AND 필드2='조건2';

SELECT * FROM 테이블명 WHERE 필드1 <> '';   <>는 같지 않음을 뜻하며 여기서 빈문자열이 아닌 필드1값을 조회하게된다.

SELECT * FROM 테이블명 WHERE 필드 IS NULL;   해당 필드가 NULL인지 여부 판단

SELECT * FROM 테이블명 WHERE 필드 IS NOT NULL;   해당 필드가 NULL이 아닌지를 판단

SELECT * FROM 테이블명 WHERE 필드 LIKE '김%';   필드의 값이 김으로 시작하는 레코드를 찾게 된다.

NOTE : SQL에서는 작은 따옴표를 사용하여 문자열을 표시한다. , 부등호기호를 사용할 수 있다.( <, > <= , >= 등)

3. 업데이트

UPDATE 테이블명 SET 필드1='값1' , 필드2 = '값2'... .WHERE 필드1 = '조건';

4. 삽입

INSERT INTO 테이블명 (필드1,필드2) VALUES ('값1','값2');  이 경우 빈 필드는 null 값이 들어간다.

INSERT INTO 테이블명 VALUES ('값1','값2');   이 경우에는 모든 필드값을 삽입 해야한다.

5. 삭제

delete from 테이블명 where 필드1='조건1';

6. 조인

SELECT A.칼럼1, A.칼럼2 B.칼럼3 B.칼럼3

FROM 테이블명1 AS A 테이블명2 AS B

WHERE A.칼럼x = B.칼럼y

간단하게 이 정도가 있다. 자세한건 나중에 SQL 공부시간에 정리하겠다.

---

3. JDBC

JSP를 포함한 자바 어플리케이션에서 데이터베이스에 접근할 때에는 데이터베이스의 종류에 상관 없이 JDBC API(java.sql,javax.sql)를 이용해서 데이터베이스에 접근한다. 그리고 각각의 DBMS는 각각의 알맞은 JDBC 드라이버를 제공하고 있다. JDBC API는 이 드라이버를 거쳐 데이터베이스와 통신을 하게 된다.

JDBC 프로그래밍 실행 순서는

1. JDBC 드라이버 로딩
2. 데이터베이스 커넥션 구함
3. 쿼리 실행을 위한 Statement 객체 생성
4. 쿼리 실행
5. 쿼리 실행 결과 사용
6. Statement 종료
7. 데이터 베이스 커넥션 종료
   

이 순서 대로 코드를 살펴 보자

1.

try{

Class.forName("JDBC 드라이버 클래스의 완전한 이름");

}catch(ClassNotFounException ex){

// 지정한 클래스가 존재하지 않는 경우 에러 발생

}

앞서 설명했듯이  JDBC드라이버가 있어야만 데이터베이스와 통신할 수가 있다. 

여기서 Class.forName() 매서든 지정한 클래스 정보를 담고 있는 Class 인스턴스를 구해주는 기능만을 제공한다. JDBC 드라이버에 해당하는 클래스들은 Class.forName() 매서드를 통해서 로딩될 때 자동으로 JDBC 드라이버로 등록한다.

2.

커넥션할 데이터 베이스를 식별 해야하는데 웹주소 URL과 같이 비슷한 형식으로 데이터베이스를 구분할 수 있다. 여기서는 MYSQL JDBC 드라이버의 경우를 보겠다

"jdbc:mysql://HOST:PORT/DBNAME?"

         + "useUnicode=true&characterEncoding=utf8&useSSL=false&serverTimezone=UTC";

HOST 는 MYSQL 서버의 호스트 주소를 나타내며 그 주소에 해당하는 포트를 함께 적어주면된다.

DBNAME은 데이터베이스의 이름이다. 그리고 ? 이후에 몇 가지 설정 정보를 정해주면된다.

이후 커넥션을 구하면된다.

• DriverManager.getConnection(String jdbcURL)
  
• DriverManager.getConnection(String jdbcURL,String userID,String userPwd)

try{

String jdbcDriver = "jdbc:mysql://localhost:3306/xxxx?"

         + "useUnicode=true&characterEncoding=utf8&useSSL=false&serverTimezone=UTC";

String dbUser = "id";

String dbPass = "pwd";

String query = "select * from member order by memberid";

conn = DriverManager.getConnection(jdbcDriver,dbUser,dbPass);

...

}catch(SQLException ex){

}finally{

if(conn != null) try{conn.close();} catch(SQLException ex){}

}

jdbcURL 에는 앞서 설명한 URL에 해당되고 뒤는 각각 id paswword 에 해당하는 스트링을 넣어주면된다.

올바르게 연결이 되면 db와 연결된 Connection 객체를 리턴하게 된다

그리고 객체를 다 사용한 이후에는 반드시 객체를 반환해야만 한다.

3.

커넥션에 성공하면 이후 쿼리를 실행하기 위해 Statement 객체를 생성해야한다.

Statement stmt = conn.createStatement();

• ResultSet executeQuery(String query) : SELECT 쿼리를 실행한다.
  
• int executeUpdate(String query) : INSERT , UPDATE , DELETE 쿼리를 실행한다. 

executeQuery의 결과값을 java.sql.ResultSet 객체에 저장해서 리턴하는데 

이런 형태를 띈다.

executeUpdate 는 결과로 변경된 레코드의 개수를 리턴하게 된다.

.) 참고 ResultSet 클래스의 주요 매서드 (https://docs.oracle.com/javase/7/docs/api/java/sql/ResultSet.html)

3.1 

PreparedStatement를 사용한 쿼리 실행

Statement 와 동일한 기능을 제공하지만 다른점은 SQL 쿼리의 틀을 미리 생성해놓고 값을 나중에 지정한다는 것이다.

1. Connection.prepareStatement() 매서드를 사용하여 PreparedStatement 생성
2. PreparedStatement의 set 매서드를 사용하여 필요한 값 지정
3. PreparedStatement의 exe... 를 사용하여 쿼리 실행
4. 이후 close를 통해 반환

사용 예를 보면

pstmt = conn.prepareStatement("insert into member values (?,?,?,?)");

pstmt.setString(1,memberID);

pstmt.setString(2, password);

pstmt.setString(3, name);

pstmt.setString(4, email);

 pstmt.executeUpdate();  // 쿼리 실행

.)참고 PreparedStatement매서드의 종료 (https://docs.oracle.com/javase/7/docs/api/java/sql/PreparedStatement.html)

Statement 보다 PreparedStatement를 사용하는 것이 더 좋은데 이유는

• 값 변환을 자동으로 함
• 간결한 코드 사용

값 변환을 자동으로 한다는 소리는 예를 들어 문자열중에서 ' (작은 따옴표를) 포함 할 경우 replaceAll 을 이용하여 바꿔줘야 하지만 PreparedStatement 를 사용하면 자동으로 된다.

간결한 코드 사용 역시 그러하다.

4,5,6,7 번과정은 예와 함께 들었으므로 다시 설명할 필요는 없을 것이다.

오늘은 생성과 SQL의 기초를 보았다 다음에는 트랜잭션과 풀 개념에 대해서 설명하겠다.