pw: admin1234
add entry
스패너 표시
shift-tab 줄 맞춰짐
alt + shift + 마우스 클릭을 누른 상태에서 드래그
drop table if exists product;
create table if not exists product(
productId varchar(10) not null,
pname varchar(20) ,
unitPrice integer,
descriptions text,
manufacturer varchar(20) ,
category varchar(20) ,
unitsInStock long,
conditions varchar(20) ,
filename varchar(20) ,
primary key (productId)
);
# insert 추가
insert into product values(
'P1234',
'iPhone 6s',
800000,
'4.7-inch, 1334X750 Renina HD display, 8-megapixel iSight Camera',
'Apple',
'Smart Phone',
1000,
'New',
'P1234.png' );
insert into product values(
'P1235',
'LG PC 그램',
1500000, '13.3-inch, IPS LED display, 5rd Generation Intel Core processors',
'LG',
'Notebook',
1000,
'Refurbished',
'P1235.png' );
insert into product values(
'P1236',
'Galaxy Tab S',
900000,
'212.8*125.6*6.6mm, Super AMOLED display, Octa-Core processor',
'Samsung',
'Tablet',
1000,
'Old',
'P1236.png' );
# Read 조회
use webmarketdb ;
select * from product ;
-- # update 수정
-- update product set pname = 'Galaxy T S' where productId = 'P1236';
-- # delete 삭제
-- delete from product where productId = 'P1236';
잘 연결 됐으면 db에서 지우면 웹페이지 에서도 없어져야 맞음
package dao;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import dto.Product;
public class ProductRepository {
private ArrayList<Product> listOfProducts = new ArrayList<Product>();
private static ProductRepository instance = new ProductRepository();
public static ProductRepository getInstance(){
return instance;
}
public ProductRepository() {
try {
Connection con = Connectiondb.getConnection();
Statement stmt = con.createStatement();
String sql = "select * from product";
ResultSet rs = stmt.executeQuery(sql);
while (rs.next()) {
Product p = new Product(
rs.getString("productId"),
rs.getString("pname"),
rs.getInt("unitPrice"),
rs.getString("descriptions"),
rs.getString("manufacturer"),
rs.getString("category"),
rs.getLong("unitsInStock"),
rs.getString("conditions"),
rs.getString("filename")
);
listOfProducts.add(p);
}
} catch (Exception e) {e.printStackTrace();}
}
public ArrayList<Product> getAllProducts() {
return listOfProducts;
}
public Product getProductById(String productId) {
Product productById = null;
for (int i = 0; i < listOfProducts.size(); i++) {
Product product = listOfProducts.get(i);
if (product != null && product.getProductId() != null && product.getProductId().equals(productId)) {
productById = product;
break;
}
}
return productById;
}
public void addProduct(Product product) {
listOfProducts.add(product);
}
}
products에서도 instance 고쳐주기
// 얘 때문에 계속 서버를 다시 시작해야 반영되었던 것 = 싱글톤이라 : 그래서 지움?
//== 그니까.. 원래는 instance 싱글톤으로 그 해당 싱글톤의 동일한 주소를 계속 불러왔던 것
// 그래서 repository의 기본 생성자 속 구성 요소를 바꿔도 바뀌지 않았던 것
// 결론은 싱글톤의 동일한 주소를 가져오지 않고 바뀐 db로부터 정보가 담긴 새로운 주소를 계속 불러올 수 있는 방법은
// new productRepostory로 새로운 객체를 만들어주는 방법인 것.
private static ProductRepository instance = new ProductRepository();
public static ProductRepository getInstance(){
return instance;
}
ProductRepository dao = ProductRepository.getInstance();
ProductRepository dao = new ProductRepository();
ArrayList<Product> listOfProducts = dao.getAllProducts();
package dao;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import dto.Product;
public class ProductRepository {
private ArrayList<Product> listOfProducts = new ArrayList<Product>();
// 얘 때문에 계속 서버를 다시 시작해야 반영되었던 것 = 싱글톤이라 : 그래서 지움?
//== 그니까.. 원래는 instance 싱글톤으로 그 해당 싱글톤의 동일한 주소를 계속 불러왔던 것
// 그래서 repository의 기본 생성자 속 구성 요소를 바꿔도 바뀌지 않았던 것
// 결론은 싱글톤의 동일한 주소를 가져오지 않고 바뀐 db로부터 정보가 담긴 새로운 주소를 계속 불러올 수 있는 방법은
// new productRepostory로 새로운 객체를 만들어주는 방법인 것.
// private static ProductRepository instance = new ProductRepository();
// public static ProductRepository getInstance(){
// return instance;
// }
public ArrayList<Product> getAllProducts() {
ArrayList<Product> listOfProducts = new ArrayList<Product>();
try {
Connection con = ConnectionDb.getConnection();
Statement stmt = con.createStatement(); // 쿼리 실행
String sql = "select * from product";
ResultSet rs = stmt.executeQuery(sql);
while (rs.next()) {
Product p = setProductVo(rs);
listOfProducts.add(p);
}
} catch (Exception e) {
System.out.println(e.getMessage());
System.out.println(e.getStackTrace());
}
return listOfProducts;
}
private Product setProductVo(ResultSet rs) throws SQLException {
Product p = new Product(
rs.getString("productId"),
rs.getString("pname"),
rs.getInt("unitPrice"),
rs.getString("descriptions"),
rs.getString("manufacturer"),
rs.getString("category"),
rs.getLong("unitsInStock"),
rs.getString("conditions"),
rs.getString("filename")
);
return p;
}
public Product getProductById(String productId) {
Product p = null;
try {
Connection con = ConnectionDb.getConnection();
String sql = "select * from product where productId = ?";
PreparedStatement pstmt = con.prepareStatement(sql);
pstmt.setString(1, productId);
ResultSet rs = pstmt.executeQuery();
if(rs.next()) {
p = setProductVo(rs);
}
} catch (ClassNotFoundException | SQLException e) {
e.printStackTrace();
}
return p;
}
public void addProduct(Product product) {
try {
Connection con = ConnectionDb.getConnection();
String sql = "insert into product "
+ " values("
+ " ?,?,?,?,?,?,?,?,?"
+ " );";
PreparedStatement pstmt = con.prepareStatement(sql);
setPstmt(pstmt,product);
pstmt.executeUpdate();
} catch (ClassNotFoundException | SQLException e) {
e.printStackTrace();
}
}
private void setPstmt(PreparedStatement pstmt,Product product) throws SQLException {
pstmt.setString(1, product.getProductId());
pstmt.setString(2, product.getPname());
pstmt.setInt(3, product.getUnitPrice());
pstmt.setString(4, product.getDescription());
pstmt.setString(5, product.getManufacturer());
pstmt.setString(6, product.getCategory());
pstmt.setLong(7, product.getUnitsInStock());
pstmt.setString(8, product.getCondition());
pstmt.setString(9, product.getFilename());
}
}
이거 해야 원래 폴더로 들어감
이부분 다 바꾸기
<sql:setDataSource var="dataSource"
url="jdbc:mysql://localhost:3306/webmarketdb"
driver="com.mysql.cj.jdbc.Driver" user="webmarket_admin" password="admin1234" />
주중에 해야할 일?:
process login member 같은 부분을
jsp 순수 파일인데 이걸 자바로 바꾸든?
process 붙은 건 java로 바꿀 것
로그인, 회원가입 해보기
<%@ page contentType="text/html; charset=utf-8"%>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core"%>
<%@ taglib prefix="sql" uri="http://java.sun.com/jsp/jstl/sql"%>
<html>
<head>
<link rel="stylesheet" href="../resources/css/bootstrap.min.css" />
<%
String sessionId = (String) session.getAttribute("sessionId");
%>
<sql:setDataSource var="dataSource"
url="jdbc:mysql://localhost:3306/webmarketdb"
driver="com.mysql.jdbc.Driver" user="webmarket_admin" password="admin1234" />
<sql:query dataSource="${dataSource}" var="resultSet">
SELECT * FROM MEMBER WHERE ID=?
<sql:param value="<%=sessionId%>" />
</sql:query>
<title>회원 수정</title>
</head>
<body onload="init()">
<jsp:include page="/menu.jsp" />
<div class="jumbotron">
<div class="container">
<h1 class="display-3">회원 수정</h1>
</div>
</div>
<c:forEach var="row" items="${resultSet.rows}">
<c:set var="mail" value="${row.mail}" />
<c:set var="mail1" value="${mail.split('@')[0]}" />
<c:set var="mail2" value="${mail.split('@')[1]}" />
<c:set var="birth" value="${row.birth }" />
<c:set var="year" value="${birth.split('/')[0]}" />
<c:set var="month" value="${birth.split('/')[1]}" />
<c:set var="day" value="${birth.split('/')[2]}" />
<div class="container">
<form name="newMember" class="form-horizontal"
action="processUpdateMember.jsp" method="post"
onsubmit="return checkForm()">
<div class="form-group row">
<label class="col-sm-2 ">아이디</label>
<div class="col-sm-3">
<input name="id" type="text" class="form-control" placeholder="id"
value="<c:out value='${row.id }'/>" />
</div>
</div>
<div class="form-group row">
<label class="col-sm-2">비밀번호</label>
<div class="col-sm-3">
<input name="password" type="text" class="form-control"
placeholder="password" value="<c:out value='${row.password }'/>" >
</div>
</div>
<div class="form-group row">
<label class="col-sm-2">비밀번호확인</label>
<div class="col-sm-3">
<input name="password_confirm" type="text" class="form-control"
placeholder="password_confirm" >
</div>
</div>
<div class="form-group row">
<label class="col-sm-2">성명</label>
<div class="col-sm-3">
<input name="name" type="text" class="form-control"
placeholder="name" value="<c:out value='${row.name }'/>" >
</div>
</div>
<div class="form-group row">
<label class="col-sm-2">성별</label>
<div class="col-sm-10">
<c:set var="gender" value="${row.gender }" />
<input name="gender" type="radio" value="남" <c:if test="${gender.equals('남')}"> <c:out value="checked" /> </c:if> >남
<input name="gender" type="radio" value="여" <c:if test="${gender.equals('여')}"> <c:out value="checked" /> </c:if> >여
</div>
</div>
<div class="form-group row">
<label class="col-sm-2">생일</label>
<div class="col-sm-4 ">
<input type="text" name="birthyy" maxlength="4" placeholder="년(4자)" size="6" value="${year}">
<select name="birthmm" id="birthmm">
<option value="">월</option>
<option value="01">1</option>
<option value="02">2</option>
<option value="03">3</option>
<option value="04">4</option>
<option value="05">5</option>
<option value="06">6</option>
<option value="07">7</option>
<option value="08">8</option>
<option value="09">9</option>
<option value="10">10</option>
<option value="11">11</option>
<option value="12">12</option>
</select> <input type="text" name="birthdd" maxlength="2" placeholder="일" size="4" value="${day}">
</div>
</div>
<div class="form-group row ">
<label class="col-sm-2">이메일</label>
<div class="col-sm-10">
<input type="text" name="mail1" maxlength="50" value="${mail1}">@
<select name="mail2" id="mail2">
<option>naver.com</option>
<option>daum.net</option>
<option>gmail.com</option>
<option>nate.com</option>
</select>
</div>
</div>
<div class="form-group row">
<label class="col-sm-2">전화번호</label>
<div class="col-sm-3">
<input name="phone" type="text" class="form-control" placeholder="phone" value="<c:out value='${row.phone}'/>">
</div>
</div>
<div class="form-group row">
<label class="col-sm-2 ">주소</label>
<div class="col-sm-5">
<input name="address" type="text" class="form-control" placeholder="address" value="<c:out value='${row.address}'/>">
</div>
</div>
<div class="form-group row">
<div class="col-sm-offset-2 col-sm-10 ">
<input type="submit" class="btn btn-primary" value="회원수정 ">
<a href="deleteMember.jsp" class="btn btn-primary">회원탈퇴</a>
</div>
</div>
</form>
</div>
</c:forEach>
</body>
</html>
<script type="text/javascript">
function init() {
setComboMailValue("${mail2}");
setComboBirthValue("${month}");
}
function setComboMailValue(val) {
var selectMail = document.getElementById('mail2');
for (i = 0, j = selectMail.length; i < j; i++) {
if (selectMail.options[i].value == val) {
selectMail.options[i].selected = true;
break;
}
}
}
function setComboBirthValue(val) {
var selectBirth = document.getElementById('birthmm');
for (i = 0, j = selectBirth.length; i < j; i++){
if (selectBirth.options[i].value == val){
selectBirth.options[i].selected = true;
break;
}
}
}
function checkForm() {
if (!document.newMember.id.value) {
alert("아이디를 입력하세요.");
return false;
}
if (!document.newMember.password.value) {
alert("비밀번호를 입력하세요.");
return false;
}
if (document.newMember.password.value != document.newMember.password_confirm.value) {
alert("비밀번호를 동일하게 입력하세요.");
return false;
}
}
</script>
요렇게 줄일 수 있음