#02. BE - JDBC

BoostCource/Back-end

#02. BE - JDBC

칸타탓 2018. 7. 17. 18:34

<2. DB 연결 웹 애플리케이션 - MySQL>
http://www.edwith.org/boostcourse-web

* JDBC

Java Database Connectivity
자바를 이용한 데이터베이스 접속과 SQL 문장의 실행, 그 결과로 얻어진 데이터의 핸들링을 제공하는 방법과 절차에 관한 규약이다.
자바 프로그램 내에서 SQL문을 실행하기 위한 자바 API이다.
SQL과 프로그래밍 언어의 통합 접근 중 한 형태이다.
JAVA는 표준 인터페이스인 JDBC API를 제공한다.
데이터베이스 벤더, 기타 써드파티에서는 JDBC 인터페이스를 구현한 드라이버(driver)를 제공한다.

JDBC를 이용한 프로그래밍 방법

import java.sql.*;
드라이버를 로드 한다.
Connection 객체를 생성한다. (접속)
Statement 객체를 생성 및 질의 수행
SQL문에 결과물이 있다면 ResultSet 객체를 생성한다.
모든 객체를 닫는다.

* JDBC 사용하기

0. Maven에 의존성 추가하기

<dependency>   
  <groupId>mysql</groupId>   
       <artifactId>mysql-connector-java</artifactId>
       <version>5.1.45</version>
 </dependency>

1. IMPORT

import java.sql.*;

2. 드라이버 로드 (MySQL 사용)

Class.forName( "com.mysql.jdbc.Driver" );

3. Connection 객체 얻기

String dburl  = "jdbc:mysql://localhost/dbName";

Connection con =  DriverManager.getConnection ( dburl, ID, PWD );

4. Statement 객체 생성

Statement stmt = con.createStatement();

5. 질의 수행

ResultSet rs = stmt.executeQuery("select no from user" );

참고
stmt.execute(“query”);             //any SQL
stmt.executeQuery(“query”);     //SELECT
stmt.executeUpdate(“query”);   //INSERT, UPDATE, DELETE

6. ResultSet으로 결과 받기

ResultSet rs =  stmt.executeQuery( "select no from user" );
while ( rs.next() )
      System.out.println( rs.getInt( "no") );

7. Close (close 할 떄는 순서를 반대로하여 닫아주어야 한다.)

rs.close();

stmt.close();

con.close();

* JDBC를 통해 테이블에서 하나의 값 가져오기 (select)

- Role.java (Dto)

package kr.or.connect.jdbcexam.dto;

public class Role {
	private Integer roleId;
	private String description;

	public Role() {

	}

	public Role(Integer roleId, String description) {
		super();
		this.roleId = roleId;
		this.description = description;
	}

	public Integer getRoleId() {
		return roleId;
	}

	public void setRoleId(Integer roleId) {
		this.roleId = roleId;
	}

	public String getDescription() {
		return description;
	}

	public void setDescription(String description) {
		this.description = description;
	}

	@Override
	public String toString() {
		return "Role [roleId=" + roleId + ", description=" + description + "]";
	}
}

마우스 오른쪽버튼 -> Sources에서 getter, setter, generate toString을 각각 추가한다.

- RoleDao.java (Select)

package kr.or.connect.jdbcexam.dao;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import kr.or.connect.jdbcexam.dto.Role;

public class RoleDao {
	private static String dburl = "jdbc:mysql://localhost:3306/connectdb";
	private static String dbUser = "connectuser";
	private static String dbpasswd = "connect123!@#";

	public Role getRole(Integer roleId) {
		Role role = null;
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;

		try {
			Class.forName("com.mysql.jdbc.Driver");
			conn = DriverManager.getConnection(dburl, dbUser, dbpasswd);
			String sql = "SELECT description,role_id FROM role WHERE role_id = ?";
			ps = conn.prepareStatement(sql);
			ps.setInt(1, roleId);
			rs = ps.executeQuery();

			if (rs.next()) {
				String description = rs.getString(1);
				int id = rs.getInt("role_id");
				role = new Role(id, description);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (ps != null) {
				try {
					ps.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}

		return role;
	}
}

String sql = "SELECT description,role_id FROM role WHERE role_id = ?";

ps = conn.prepareStatement(sql);

ps.setInt(1, roleId);

=> 첫번째 물음표에 roleId를 넣겠다는 뜻이다.

물음표의 순서에 따라 앞에 숫자를 다르게 적어주면 된다. (Statement방식)

finally는 반드시 실행되는 것을 말한다. 따라서 닫아주는 코드를 써준다.

- jdbcExam1.java (Select)

package kr.or.connect.jdbcexam;

import kr.or.connect.jdbcexam.dao.RoleDao;
import kr.or.connect.jdbcexam.dto.Role;

public class JDBCExam1 {

	public static void main(String[] args) {
		RoleDao dao = new RoleDao();
		Role role = dao.getRole(100);
		System.out.println(role);
	}

}

* JDBC를 통해 테이블에서 모든 값 가져오기 (select)

list를 사용하며, 출력할 때 반복문을 사용하면 된다.

package kr.or.connect.jdbcexam.dao;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import kr.or.connect.jdbcexam.dto.Role;

public class RoleDao {
	private static String dburl = "jdbc:mysql://localhost:3306/connectdb";
	private static String dbUser = "connectuser";
	private static String dbpasswd = "connect123!@#";

	public List<Role> getRoles() {
		List<Role> list = new ArrayList<>();

		try {
			Class.forName("com.mysql.jdbc.Driver");
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}

		String sql = "SELECT description, role_id FROM role order by role_id desc";
		try (Connection conn = DriverManager.getConnection(dburl, dbUser, dbpasswd);
				PreparedStatement ps = conn.prepareStatement(sql)) {

			try (ResultSet rs = ps.executeQuery()) {

				while (rs.next()) {
					String description = rs.getString(1);
					int id = rs.getInt("role_id");
					Role role = new Role(id, description);
					list.add(role); // list에 반복할때마다 Role인스턴스를 생성하여 list에 추가한다.
				}
			} catch (Exception e) {
				e.printStackTrace();
			}
		} catch (Exception ex) {
			ex.printStackTrace();
		}
		return list;
	}
}

package kr.or.connect.jdbcexam;

import java.util.List;

import kr.or.connect.jdbcexam.dao.RoleDao;
import kr.or.connect.jdbcexam.dto.Role;

public class JDBCExam3 {
	public static void main(String[] args) {

		RoleDao dao = new RoleDao();
		
		List<Role> list = dao.getRoles();

		for(Role role : list) {
			System.out.println(role);
		}
	} 
}

* JDBC를 통해 테이블 값 추가하기 (insert)

- RoleDao.java (Insert)

package kr.or.connect.jdbcexam.dao;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import kr.or.connect.jdbcexam.dto.Role;

public class RoleDao {
	private static String dburl = "jdbc:mysql://localhost:3306/connectdb";
	private static String dbUser = "connectuser";
	private static String dbpasswd = "connect123!@#";

	public int addRole(Role role) {
		int insertCount = 0;

		try {
			Class.forName("com.mysql.jdbc.Driver");
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
		String sql = "INSERT INTO role (role_id, description) VALUES ( ?, ? )";

		try (Connection conn = DriverManager.getConnection(dburl, dbUser, dbpasswd);
				PreparedStatement ps = conn.prepareStatement(sql)) {

			ps.setInt(1, role.getRoleId());
			ps.setString(2, role.getDescription());

			insertCount = ps.executeUpdate();

		} catch (Exception ex) {
			ex.printStackTrace();
		}
		return insertCount;
	}
}

select가 아닌 insert이기 때문에 resultset은 추가하지 않아도 된다.

그리고 물음표의 순서는 앞 쿼리문에서 나열한 칼럼의 순서이다.

insert, update, delete는 executeUpdate();를 사용한다. <=> select문은 executeQuery();

- JDBCExam2.java (Insert)

package kr.or.connect.jdbcexam;

import kr.or.connect.jdbcexam.dao.RoleDao;
import kr.or.connect.jdbcexam.dto.Role;

public class JDBCExam2 {
	public static void main(String[] args) {
		int roleId = 501;
		String description = "CTO";
		
		Role role = new Role(roleId, description);
		
		RoleDao dao = new RoleDao();
		int insertCount = dao.addRole(role);

		System.out.println(insertCount);
	}
}

* JDBC를 통해 테이블 값 수정하기 (update)

- RoleDao.java (Update)

package kr.or.connect.jdbcexam.dao;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import kr.or.connect.jdbcexam.dto.Role;

public class RoleDao {
	private static String dburl = "jdbc:mysql://localhost:3306/connectdb";
	private static String dbUser = "connectuser";
	private static String dbpasswd = "connect123!@#";
	
	public int updateRole(Role role) {
		int updateCount = 0;
		
		
		Connection conn = null;
		PreparedStatement ps = null;
		
		try {
			Class.forName( "com.mysql.jdbc.Driver" );
			
			conn = DriverManager.getConnection ( dburl, dbUser, dbpasswd );
			
			String sql = "update role set description = ? where role_id = ?";
			
			ps = conn.prepareStatement(sql);
			
			ps.setString(1, role.getDescription());
			ps.setInt(2,  role.getRoleId());
			
			updateCount = ps.executeUpdate();

		}catch(Exception ex) {
			ex.printStackTrace();
		}finally {
			if(ps != null) {
				try {
					ps.close();
				}catch(Exception ex) {}
			} // if
			
			if(conn != null) {
				try {
					conn.close();
				}catch(Exception ex) {}
			} // if
		} // finally
		
		return updateCount;
	}
}

- JDBCExam5.java (Update)

package kr.or.connect.jdbcexam;

import kr.or.connect.jdbcexam.dao.RoleDao;
import kr.or.connect.jdbcexam.dto.Role;

public class JDBCExam5 {
	public static void main(String[] args) {
//수정테스트
		int roleId = 500;
		String description = "CEO";
		
		Role role = new Role(roleId, description);
		
		RoleDao dao = new RoleDao();
		int updateCount = dao.updateRole(role);

		System.out.println(updateCount);
	} 
}

* JDBC를 통해 테이블 값 삭제하기 (delete)

- RoleDao.java (Delete)

package kr.or.connect.jdbcexam.dao;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import kr.or.connect.jdbcexam.dto.Role;

public class RoleDao {
	private static String dburl = "jdbc:mysql://localhost:3306/connectdb";
	private static String dbUser = "connectuser";
	private static String dbpasswd = "connect123!@#";

	public int deleteRole(Integer roleId) {
		int deleteCount = 0;
		
		Connection conn = null;
		PreparedStatement ps = null;
		
		try {
			Class.forName( "com.mysql.jdbc.Driver" );
			
			conn = DriverManager.getConnection ( dburl, dbUser, dbpasswd );
			
			String sql = "DELETE FROM role WHERE role_id = ?";

			ps = conn.prepareStatement(sql);
			
			ps.setInt(1,  roleId);

			deleteCount = ps.executeUpdate();

		}catch(Exception ex) {
			ex.printStackTrace();
		}finally {
			if(ps != null) {
				try {
					ps.close();
				}catch(Exception ex) {}
			} // if
			
			if(conn != null) {
				try {
					conn.close();
				}catch(Exception ex) {}
			} // if
		} // finally

		return deleteCount;
	}
}

- JDBCExam4.java (Delete)

package kr.or.connect.jdbcexam;

import kr.or.connect.jdbcexam.dao.RoleDao;

public class JDBCExam4 {
	public static void main(String[] args) {
//삭제 테스트
		int roleId = 500;

		RoleDao dao = new RoleDao();
		int deleteCount = dao.deleteRole(roleId);

		System.out.println(deleteCount);
	}
}

java.sql.SQLException: Unable to load authentication plugin 'caching_sha2_password'

JDBC 사용하면서 위와 같은 오류가 발생했을 때 (MySQL 8.0버전 이상 사용), 아래와 같은 방법으로 해결하였다.

방법1. ALTER USER 'username'@'IP' IDENTIFIED WITH mysql_native_password BY 'password';

방법2. CREATE USER 'username'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password';

방법3. ALTER USER username@'%' IDENTIFIED WITH mysql_native_password BY 'password!@#';

(username, password에는 각자 설정해 놓은 값을 입력한다.)

Tue Jan 29 20:27:28 KST 2019 WARN: Establishing SSL connection without server's identity verification is not recommended. According to MySQL 5.5.45+, 5.6.26+ and 5.7.6+ requirements SSL connection must be established by default if explicit option isn't set. For compliance with existing applications not using SSL the verifyServerCertificate property is set to 'false'. You need either to explicitly disable SSL by setting useSSL=false, or set useSSL=true and provide truststore for server certificate verification

jdbc 실습 과정에서 위와 같은 SSL 관련 에러가 발생할 경우에는 아래와 같이 dburl을 수정한다.

private static String dburl = "jdbc:mysql://localhost:3306/connectdb?serverTimezone=Asia/Seoul&useSSL=false";

MySQLIntegrityConstraintViolationException: Duplicate entry '501' for key 'PRIMARY'

jdbc를 이용하여 insert할 때 위와 같은 에러가 발생하는 이유는 기본키 값이 중복되기 때문이다. (501이 이미 존재)

role_id는 기본키로 설정 되어있기 때문에 null값과 중복 값을 허용하지 않는다. 따라서 중복값과 널값이 존재하는지 확인하고 수정하여 해결한다.

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

'BoostCource > Back-end' 카테고리의 다른 글

#02. BE - Web API (0)	2018.07.18
#02. BE - Rest API (0)	2018.07.18
#02. BE - Maven으로 프로젝트 만들기 (0)	2018.07.15
#02. BE - JSTL (0)	2018.07.14
#02. BE - EL (0)	2018.07.14

현재글#02. BE - JDBC

Study-Note

#02. BE - JDBC

'BoostCource > Back-end' 카테고리의 다른 글

'BoostCource/Back-end'의 다른글

티스토리툴바