JDBC (JAVA Database Connectivity)
- 자바를 이용한 데이터베이스 접속과 SQL 문장의 실행, 그리고 실행 결과로 얻어진 데이터의 핸들링을 제공하는 방법과 절차에 관한 규약
-자바 프로그램내에서 SQL문을 실행하기 위한 자바 API
<dependency> <groupId)mysql</groupId> <artifactId>mysql-connector-java</artifactId> <version>5.1.45</version> </dependency>
JDBC를 이용한 프로그래밍 방법
- import java.sql.*;
- 드라이버를 로드한다.
- Connection 객체 생성
- Statement 객체를 생성 및 질의 수행
- SQL문에 결과물이 잇다면 ResultSet 객체 생성
- 모든 객체를 닫는다
1. import
import java.sql.*;
2. 드라이버 로드
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");
6. ResultSet으로 결과 받기
while(rs.next())
System.out.println(rs.getInt("no"));7. Close
rs.close();
stmt.close();
con.close();실습 1
File -> New -> Other -> Maven Project
pom.xml 수정
plugins에 하위 코드 추가
<plugin> <groupId>org.apache.maven.plugins</groupId> <artifactId>maven-compiler-plugin</artifactId> <version>3.6.1</version> <configuration> <source>1.8</source> <target>1.8</target> </configuration> </plugin>dependencies에 하위 코드 추가
<dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <version>5.1.45</version> </dependency>
구조
- Role 클래스 생성
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 + "]";
}
}
- RoleDao 클래스 생성
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?useSSL=false";
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 role_id, description FROM role WHERE role_id = ?";
ps = conn.prepareStatement(sql);
ps.setInt(1, roleId);
rs = ps.executeQuery();
if (rs.next()) {
String description = rs.getString("description"); //첫번째 컬럼
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;
}
}- JDBCExam1 클래스 생성
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) {
// TODO Auto-generated method stub
RoleDao dao = new RoleDao();
Role role = dao.getRole(100);
System.out.println(role);
}
}
실행결과:
Role [roleId=100, description=Developer]
실습 2
INSERT 하기
public int addRole(Role role) {
int insertCount = 0;
Connection conn = null;
PreparedStatement ps = null;
try {
Class.forName("com.mysql.jdbc.Driver");
conn = DriverManager.getConnection(dburl, dbUser, dbpasswd);
String sql = "INSERT INTO role (role_id, description) VALUES (?, ?)";
ps = conn.prepareStatement(sql);
ps.setInt(1, role.getRoleId());
ps.setString(2, role.getDescription());
insertCount = ps.executeUpdate();
} catch (Exception ex) {
ex.printStackTrace();
} finally {
if (ps != null) {
try {
ps.close();
}catch (Exception ex) {
}
}
if (conn != null) {
try {
conn.close();
} catch (Exception ex) {
}
}
}
return insertCount;
}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 = 500;
String description = "CTO";
Role role = new Role(roleId, description);
RoleDao dao = new RoleDao();
int insertCount = dao.addRole(role);
System.out.println(insertCount);
}
}실습 3
모두 조회하기
try-with-resource 사용
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("description");
int id = rs.getInt("role_id");
Role role = new Role(id, description);
list.add(role);
}
} 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);
}
}
}
