2023.04.14 56일차 SpringBoot
56일차 SpringBoot + DB
자바 쿼리 실행해서 데이터 받는 연습 중이다.
이것이 JDBC이다.
3 ResultSet
쿼리를 실행하고 받아오는 객체 ResultSet 자바 17api에있다.
인터페이스이다.
테이블 형식의 데이터를 표현하고 있다. 쿼리 실행결과에 의해서 만들어진다.
테이블 형식의 데이터를 ResultSet이 가지고 있다.
ResultSet은 커서가 현재 행을 가리키고 있으면서 관리하고 있다.
처음 가리키고 있는 행이 첫번째 행 이전이다.
주요 메소드는 다음과 같다.
next() : 다음행으로 커서를 이동한다. 더이상 이동할 행이 없으면 false를 리턴한다.
getXXX() : 특정행의 특정 열의 값을 가져온다.
하나의 행은 여러개의 열을 가지고 있다. 이 열의 이름이 SELECT절에서 있다.
해당 행의 특정열 getXXX()메소드로가져온다.
@RequestMapping("link1")
public void method1() {
String sql = "SELECT lastName FROM Employees";
try {
Connection con = DriverManager.getConnection(url, dbId, pwd);
Statement st = con.createStatement();
ResultSet rs = st.executeQuery(sql);
try (con; st; rs;) {
System.out.println(rs.next()); // true
System.out.println(rs.getString("lastName"));
System.out.println(rs.next()); // true
System.out.println(rs.getString("lastName"));
System.out.println(rs.next()); // true
System.out.println(rs.getString("lastName"));
System.out.println(rs.next()); // true
System.out.println(rs.getString("lastName"));
System.out.println(rs.next()); // true
System.out.println(rs.getString("lastName"));
System.out.println(rs.next()); // true
System.out.println(rs.getString("lastName"));
System.out.println(rs.next()); // true
System.out.println(rs.getString("lastName"));
System.out.println(rs.next()); // true
System.out.println(rs.getString("lastName"));
System.out.println(rs.next()); // true
System.out.println(rs.getString("lastName"));
System.out.println(rs.next()); // false
}
} catch (Exception e) {
e.printStackTrace();
}
}3.1 ResultSet 연습
SELECT shipperName FROM Shippers을 출력하는 jdbc를 작성해보자.
@RequestMapping("link3")
public void method3(Model model) {
String sql = "SELECT shipperName FROM Shippers";
List<String> list = new ArrayList<>();
try {
Connection con = DriverManager.getConnection(url, dbId, pwd);
Statement st = con.createStatement();
ResultSet rs = st.executeQuery(sql);
try (con; st; rs;) {
while (rs.next()) {
list.add(rs.getString("shipperName"));
}
}
} catch (Exception e) {
e.printStackTrace();
}
model.addAttribute("shipperNames", list);
}3.2 ResultSet 여러 컬럼
그 행에서 특정 열의 값을 가져온 후 행을 또 옮겨서 필요한 일들을 하면된다.
행 옮기고 컬럼 가져오기를 반복하면된다.
@RequestMapping("link5")
public void method5() {
String sql = "SELECT * FROM Customers";
try {
Connection con = DriverManager.getConnection(url, dbId, pwd);
Statement st = con.createStatement();
ResultSet rs = st.executeQuery(sql);
try (con; st; rs;) {
while (rs.next()) {
System.out.println("############");
System.out.println(rs.getString("customername"));
System.out.println(rs.getString("contactname"));
System.out.println(rs.getString("customername"));
}
}
} catch (Exception e) {
e.printStackTrace();
}
}3.3 ResultSet 여러 컬럼 - 2
출력만 하는것은 getString으로 출력해도 상관이 없다.
하지만 값을 가져와서 가공을 해야한다면 getInt getDouble 등등의 메소드를 가지고 있기 때문에 각 형식에 맞춰서 가져오는게 낫다.
@RequestMapping("link6")
public void method6() {
String sql = "SELECT * FROM Products WHERE ProductId <= 3";
try {
Connection con = DriverManager.getConnection(url, dbId, pwd);
Statement st = con.createStatement();
ResultSet rs = st.executeQuery(sql);
try (con; st; rs;) {
while (rs.next()) {
System.out.println("############");
System.out.println(rs.getInt("productid"));
System.out.println(rs.getString("productname"));
System.out.println(rs.getDouble("price"));
}
}
} catch (Exception e) {
e.printStackTrace();
}
}3.4 연습
SELECT * FROM Customers WHERE customerid < 4
customerid는 int 타입 customername 은 string타입으로 가져오기
@RequestMapping("link7")
public void method7() {
String sql = "SELECT * FROM Customers WHERE customerid < 4";
try {
Connection con = DriverManager.getConnection(url, dbId, pwd);
Statement st = con.createStatement();
ResultSet rs = st.executeQuery(sql);
try (con; st; rs;) {
while (rs.next()) {
System.out.println(rs.getInt("customerid"));
System.out.println(rs.getString("customername"));
}
}
} catch (Exception e) {
e.printStackTrace();
}
}3.5 ResultSet - 3
ReustSet의 getXXX가 타입명과 조합이 되어있다.
그런데 같은 이름의 메소드가 오버로딩되어 두개씩 있다.
두 메소드들은 같은 역할을 한다. 특정컬럼의 값을 가져온다.
그런데 왜 두개씩 잇나? 파라미터 형식이 다르다.
하나는 String타입 하나는 int타입이다.
이전에 사용한것은 String타입으로 컬럼명을 넣었다.
int타입의 파라미터는 컬럼의 index를 넣는다.
쿼리를 실행시키면 실행시킨 결과가 테이블형식인데 컬럼이 순서대로 나열되어 있다.
컬럼의 순서가 컬럼의 index이다. 컬럼의 index는 1번부터 시작한다.
@RequestMapping("link7")
public void method7() {
String sql = "SELECT * FROM Customers WHERE customerid < 4";
try {
Connection con = DriverManager.getConnection(url, dbId, pwd);
Statement st = con.createStatement();
ResultSet rs = st.executeQuery(sql);
try (con; st; rs;) {
while (rs.next()) {
//System.out.println(rs.getInt("customerid"));
//System.out.println(rs.getString("customername"));
System.out.println(rs.getInt(1));
System.out.println(rs.getString(2));
}
}
} catch (Exception e) {
e.printStackTrace();
}
}주의할점이 있다.
쿼리를 실행시킬때 컬럼 index를 사용할때는 쿼리의 순서에 따라 index가 매겨짐을 알아야한다.
3.6 ResultSet 연습 - 4
customerId + customerName + country를 출력하는 쿼리를 작성해보자.
@RequestMapping("link9")
public void method9() {
String sql = "SELECT customerId, customerName, country FROM Customers";
try {
Connection con = DriverManager.getConnection(url, dbId, pwd);
Statement st = con.createStatement();
ResultSet rs = st.executeQuery(sql);
try (con; st; rs;) {
while (rs.next()) {
System.out.println("###############");
int customerId = rs.getInt(1);
String customerName = rs.getString(2);
String country = rs.getString(3);
System.out.println(customerId + ", " + customerName + ", " + country);
}
}
} catch (Exception e) {
e.printStackTrace();
}
}3.7 ResultSet + DTO
값들을 가져와서 JSP에 보여주는 것이 주 목적이다.
각각가져와서 각각 보여줘도 되는데 DTO를 통해 값을 받아오고 보내주는게 대부분이다.
@Data
public class Customer {
private int id;
private String name;
}@RequestMapping("link1")
public void method1(Model model) {
String sql = "SELECT customerId, customerName FROM Customers WHERE customerId < 4";
List<Customer> list = new ArrayList<>();
try {
Connection con = DriverManager.getConnection(url, dbId, pwd);
Statement st = con.createStatement();
ResultSet rs = st.executeQuery(sql);
try (con; st; rs;) {
while (rs.next()) {
int id = rs.getInt("customerId");
String name = rs.getString("customerName");
Customer customer = new Customer();
customer.setId(id);
customer.setName(name);
list.add(customer);
}
}
} catch (Exception e) {
e.printStackTrace();
}
// 3.add attribute
model.addAttribute("customerList", list);
}3.8 연습
SELECT employeeid, lastname, firstname FROM Employees
Employee클래스에 프로퍼티 id lastname firstname DTO만들고 jsp에 출력하기
@Data
public class Employee {
private int employeeId;
private String lastName;
private String firstName;
}@RequestMapping("link2")
public void method2(Model model) {
String sql = "SELECT employeeid, lastname, firstname FROM Employees";
List<Employee> list = new ArrayList<>();
try {
Connection con = DriverManager.getConnection(url, dbId, pwd);
Statement st = con.createStatement();
ResultSet rs = st.executeQuery(sql);
try (con; st; rs;) {
while (rs.next()) {
Employee employee = new Employee();
employee.setEmployeeId(rs.getInt("employeeid"));
employee.setLastName(rs.getString("lastname"));
employee.setFirstName(rs.getString("firstname"));
list.add(employee);
}
}
} catch (Exception e) {
e.printStackTrace();
}
model.addAttribute("employeeList", list);
}<div align="center">
<h1>직원 목록</h1>
<table border="1">
<thead>
<tr height="40">
<th width="50" align="center">아이디</th>
<th width="100" align="center">이름</th>
<th width="100" align="center">성</th>
</tr>
</thead>
<tbody>
<c:forEach items="${employeeList}" var="employee">
<tr height="40">
<td width="50" align="center">${employee.employeeId}</td>
<td width="100" align="center">${employee.lastName}</td>
<td width="100" align="center">${employee.firstName}</td>
</tr>
</c:forEach>
</tbody>
</table>
</div>3.9 연습 - 2
이전의 고객목록은 아이디와 이름만 나왔는데
시간이 지나서 고객의 목록에서 주소도 같이 보여지게 하고싶다.
추가로 jsp에서 테이블 형식으로 보여지게 하고싶다.
@Data
public class Customer {
private int id;
private String name;
private String address;
}@RequestMapping("link1")
public void method1(Model model) {
String sql = "SELECT customerId, customerName, address FROM Customers";
List<Customer> list = new ArrayList<>();
try {
Connection con = DriverManager.getConnection(url, dbId, pwd);
Statement st = con.createStatement();
ResultSet rs = st.executeQuery(sql);
try (con; st; rs;) {
while (rs.next()) {
int id = rs.getInt("customerId");
String name = rs.getString("customerName");
String address = rs.getString("address");
Customer customer = new Customer();
customer.setId(id);
customer.setName(name);
customer.setAddress(address);
list.add(customer);
}
}
} catch (Exception e) {
e.printStackTrace();
}
model.addAttribute("customerList", list);
}<div align="center">
<h1>고객 목록</h1>
<table border="1">
<thead>
<tr height="50">
<th width="50" align="center">아이디</th>
<th width="200" align="center">이름</th>
<th width="250" align="center">주소</th>
</tr>
</thead>
<tbody>
<c:forEach items="${customerList}" var="customer">
<tr height="50">
<th width="50" align="center">${customer.id}</th>
<th width="150" align="center">${customer.name}</th>
<th width="200" align="center">${customer.address}</th>
</tr>
</c:forEach>
</tbody>
</table>
</div>3.10 특정 레코드출력
request parameter를 받아서 그 값에 맞는 것만 출력하고 싶다.
String sql = "SELECT customerId, customerName, address FROM Customers WHERE customerId =";
sql += id;
sql에 값을 더함으로써 출력할 수 있다.
RequestParam을 이용해서 쿼리를 나중에 완성시킬 수 있다.
@RequestMapping("link3")
public String method3(int id, Model model) {
String sql = "SELECT customerId, customerName, address FROM Customers WHERE customerId =";
sql += id;
List<Customer> list = new ArrayList<>();
try {
Connection con = DriverManager.getConnection(url, dbId, pwd);
Statement st = con.createStatement();
ResultSet rs = st.executeQuery(sql);
try (con; st; rs;) {
while (rs.next()) {
Customer customer = new Customer();
customer.setId(rs.getInt("customerId"));
customer.setName(rs.getString("customerName"));
customer.setAddress(rs.getString("address"));
list.add(customer);
}
}
} catch (Exception e) {
e.printStackTrace();
}
model.addAttribute("customerList", list);
return "/sub13/link1";
}3.11 특정 레코드 출력 - 2
위의 방법에는 굉장히 큰 문제가 있다.
숫자만 입력하기를 원했는데 어떤 의도를 가진 사용자가 1 OR 1=1이런식으로 입력하면 의도하지 않은 SQL이 실행될 수도 있다.
작성한것을 SQL에 바로 접목 시켜버리니 문제가 될 수 있다.
이런것이 SQL injection공격이다. 악의적인 SQL이 실행되도록 할 수 있다.
그래서 직접 SQL을 연결연산자로 완성하게 하면 안된다.
이걸 방지하는 방법을 알아야한다.
사용자가 입력해야하는 부분을 ?로 바꾸면된다.
String sql = "SELECT customerId, customerName, address FROM Customers WHERE customerId = ?";
연결연산자로 직접 넣는게 아니라 jdbc 도움을 받아서 넣으면된다.
이럴때 사용하는 것이 PreparedStatement이다.
쿼리의 일부분을 ?로두고 ?에 값을 세팅해서 sql injection목록을 만들 수 있다.
PreparedStatement객체를 만들고 sql을 넣어준후
PreparedStatement객체에 값을 set해주면된다.
결론은 사용자가 입력한 값을 sql문에 추가하려면 PreparedStatement를 사용해야한다.
@RequestMapping("link4")
public String method4(String id, Model model) {
String sql = "SELECT customerId, customerName, address FROM Customers WHERE customerId = ?";
List<Customer> list = new ArrayList<>();
try {
Connection con = DriverManager.getConnection(url, dbId, pwd);
PreparedStatement pstmt = con.prepareStatement(sql);
pstmt.setString(1, id);
ResultSet rs = pstmt.executeQuery();
try (con; pstmt; rs;) {
while (rs.next()) {
Customer customer = new Customer();
customer.setId(rs.getInt("customerId"));
customer.setName(rs.getString("customerName"));
customer.setAddress(rs.getString("address"));
list.add(customer);
}
}
} catch (Exception e) {
e.printStackTrace();
}
model.addAttribute("customerList", list);
return "/sub13/link1";
}3.12 SQL Injection 연습
특정 직원의 정보를 출력하는코드를 작성해보자.
@RequestMapping("link5")
public String method5(int id, Model model) {
String sql = "SELECT EmployeeId, lastName, firstName FROM Employees WHERE EmployeeId = ?";
List<Employee> list = new ArrayList<>();
try {
Connection con = DriverManager.getConnection(url, dbId, pwd);
PreparedStatement pstmt = con.prepareStatement(sql);
pstmt.setInt(1, id);
ResultSet rs = pstmt.executeQuery();
try (con; pstmt; rs;) {
while (rs.next()) {
Employee employee = new Employee();
employee.setEmployeeId(rs.getInt("EmployeeId"));
employee.setLastName(rs.getString("lastName"));
employee.setFirstName(rs.getString("firstName"));
list.add(employee);
}
}
} catch (Exception e) {
e.printStackTrace();
}
model.addAttribute("employeeList", list);
return "/sub13/link2";
}사용자로부터 입력받는 sql문이라면 무조건 preparestatement를 꼭 사용해야한다.
4.1 SQL 비교 연산자
WHERE절에 가져올 데이터들의 조건들을 작성할 수 있었다.
조회 조건을 여러개로 두고 싶다.
4.1.1 AND
여러 조건들을 모두 만족하는 레코드들만 보이게 하는 연산자이다.
여러 조건들을 조합해서 특정 결과를 얻어낼 수 있다.
SELECT * FROM Customers WHERE CustomerID > 4 AND Country = 'Germany';
SELECT * FROM Employees WHERE LastName >= 'M' AND BirthDate >= '1960-01-01';연습
Products 에서 카테고리 id가 2이고 가격이 20.00이상인 상품을 조회해보기
SELECT * FROM Products WHERE CategoryID = 2 AND Price >= 20.00;1950-01-01 ~ 1959-12-31 사이태어난 직원 조회하기
SELECT * FROM Employees WHERE BirthDate >= '1950-01-01' AND BirthDate <= '1959-12-31';4.1.2 OR
여러 조건 중 하나라도 만족하는 레코드를 출력한다.
나라가 독일이거나 프랑스인 고객
SELECT * FROM Customers WHERE Country = 'Germany' OR Country = 'france';연습
도시가 london 또는 madrid에 있는 고객 조회
SELECT * FROM Customers WHERE City = 'london' OR City = 'madrid';카테고리가 1번 또는 2번인 상품들 조회
SELECT * FROM Products WHERE CategoryID = 1 OR CategoryID = 2;4.2 Not
조건이 아닌 것 조회하기
SELECT * FROM Customers; -- 91
SELECT * FROM Customers WHERE Country = 'GERMANY'; -- 11
SELECT * FROM Customers WHERE NOT Country = 'GERMANY'; -- 80 연습
london에 있지 않는 고객 조회
SELECT * FROM Customers WHERE NOT City = 'london';50년대 생이 아닌 직원들 조회
조건을 ()에 넣고 한번에 NOT을 붙여줘야한다.
OR로도 가능하다.
SELECT * FROM Employees WHERE NOT (BirthDate >= '1950-01-01' AND BirthDate <= '1959-12-31');
SELECT * FROM Employees WHERE BirthDate <= '1950-01-01' OR BirthDate >= '1959-12-31';1번 2번 카테고리가 아닌 상품들
SELECT * FROM Products WHERE NOT (CategoryID = 1 OR CategoryID = 2);
SELECT * FROM Products WHERE CategoryID != 1 AND CategoryID != 24.3 BETWEEN
범위 안에 있는 것이다.
10보다 크고 20보다 작은 고객을 AND를 사용하면 다음과 같다.
SELECT * FROM Customers WHERE CustomerID >= 10 AND CustomerID <= 19;BETWEEN 시작값 AND 끝값을 넣으면 사이의 값들이 나온다.
SELECT * FROM Customers WHERE CustomerID BETWEEN 10 AND 19;50년대 생 직원들 조회하기
SELECT * FROM Employees WHERE BirthDate BETWEEN '1950-01-01' AND '1959-12-31';50년대 생이 아닌 직원 조회하기
SELECT * FROM Employees WHERE NOT (BirthDate BETWEEN '1950-01-01' AND '1959-12-31');가격이 10달러 대인 상품 조회하기 10.00~19.99
SELECT * FROM Products WHERE Price BETWEEN 10.00 AND 19.99;4.4 IN
특정 컬럼의 값이 여러값들 중 하나이면 조회된다.
특정 값중에 있으면 조회된다.
국가가 독일이거나 프랑스인 손님
SELECT * FROM Customers WHERE Country IN ('Germany', 'France');3,5번 카테고리에 속한 상품들 조회
SELECT * FROM Products WHERE CategoryID IN (3,5);1,3,5,7번 카테고리에 속한 상품들 조회
or로 작성하면 SQL이 길어지지만 IN으로 작성하면 더 짧다.
SELECT * FROM Products WHERE CategoryID IN (1,3,5,7);베를린, 런던, 마드리드, 파리에 있는 고객들 조회하기
SELECT * FROM Customers WHERE City IN ('Berlin', 'London', 'Madrid', 'Paris');베를린, 런던, 마드리드, 파리에 있지않는 고객들 조회하기
NOT을 문장전체에 붙여도되고 NOT IN으로 해도된다.
SELECT * FROM Customers WHERE NOT City IN ('Berlin', 'London', 'Madrid', 'Paris');
SELECT * FROM Customers WHERE City NOT IN ('Berlin', 'London', 'Madrid', 'Paris');1,3,5,7 카테고리가 아닌 상품 조회
SELECT * FROM Products WHERE CategoryID NOT IN (1,3,5,7);4.5 LIKE
LIKE는 ~같다 패턴으로 레코드를 조회할때 사용한다.
LIKE는 %와 _를 사용할 수 있다.
%는 0개이상의 문자 _는 하나의 문자를 의미한다.
bl% 하면 bl로 시작하는 문자를 조회한다. bl black blue blob
h_t하면 한글자만 들어간다. hot hat hit
a로 시작하는 고객 조회하기
SELECT * FROM Customers WHERE CustomerName LIKE 'a%';a로 끝나는 고객 조회하기
SELECT * FROM Customers WHERE CustomerName LIKE '%a'; 고객명(CustomerName)또는 계약명(ContactName) 에 'or'이 포함된 고객들 조회하기
SELECT * FROM Customers WHERE CustomerName LIKE '%or%' OR ContactName LIKE '%or%';4.6 JDBC 조합
preparedstatement와 조합해서 특정 사람을 조회하는 코드를 작성해보자.
form으로 하지 않고 그냥 쿼리스트링으로 해보자.
sub14/link1?keyword=or or이 포함된 고객을 조회하기
// //sub14/link1?keyword=or
@RequestMapping("link1")
public String method1(String keyword, Model model) {
String sql = "SELECT customerid, customername, address FROM Customers "
+ "WHERE CustomerName LIKE ? ";
List<Customer> list = new ArrayList<>();
try {
Connection con = DriverManager.getConnection(url, dbId, pwd);
PreparedStatement pstmt = con.prepareStatement(sql);
pstmt.setString(1, "%" + keyword +"%");
ResultSet rs = pstmt.executeQuery();
try (con; pstmt; rs;) {
while(rs.next()) {
Customer customer = new Customer();
customer.setId(rs.getInt("customerid"));
customer.setName(rs.getString("customername"));
customer.setAddress(rs.getString("address"));
list.add(customer);
}
}
} catch (Exception e) {
e.printStackTrace();
}
model.addAttribute("customerList", list);
return "/sub13/link1";
}추가 업무 : ContactName에도 키워드 조회 추가
@RequestMapping("link2")
public String method2(String keyword, Model model) {
String sql = "SELECT customerId, customerName, contactName, address FROM Customers "
+ "WHERE customerName LIKE ? OR contactName LIKE ?";
if (keyword == null) {
keyword = "";
}
List<Customer> list = new ArrayList<>();
try {
Connection con = DriverManager.getConnection(url, dbId, pwd);
PreparedStatement pstmt = con.prepareStatement(sql);
pstmt.setString(1, "%" + keyword +"%");
pstmt.setString(2, "%" + keyword +"%");
ResultSet rs = pstmt.executeQuery();
try (con; pstmt; rs;) {
while(rs.next()) {
Customer customer = new Customer();
customer.setId(rs.getInt("customerId"));
customer.setName(rs.getString("customerName"));
customer.setContactName(rs.getString("contactName"));
customer.setAddress(rs.getString("address"));
list.add(customer);
}
}
} catch (Exception e) {
e.printStackTrace();
}
model.addAttribute("customerList", list);
return "/sub13/link1";
}2023.04.14
JDBC를 이용해서 데이터를 출력한다. SQL문을 잘 다루어야 원하는 데이터를 VIEW에 보여줄 수 있다.