DAY 56 - MySQL 설치 / 연동 ( 2024.09.24 )

D:

    MySQL

            lib

 

mysql download 검색하기

https://www.mysql.com/downloads/

MySQL :: MySQL Downloads

---

---

---

---

---

---

---

---

---

---

---

---

 

비밀번호 1234

---

---

---

---

---

---

---

---

---

---

---

 

Oracle

관리자 계정 : system

데이터베이스: 

 

MySQL

관리자 계정: root

sId(데이터베이스) : xe

 

Database -- Connect to Database

---

---

---

데이터베이스 사용

1. 왼쪽에 있는 mydb 더블클릭 -- 사용하겠다는 의미

2. user mydb; -- 코드 작성해서 사용

use mydb;

---

 member.sql

-- 회원가입과 로그인 (MySQL)
create table member(
name varchar(30) not null,
id varchar(30) primary key, -- 기본키, unique, not null, 무결성 제약 조건
pwd varchar(70) not null,
gender varchar(3),
email1 varchar(20),
email2 varchar(20),
tel1 varchar(10),
tel2 varchar(10),
tel3 varchar(10),
zipcode varchar(10),
addr1 varchar(100),
addr2 varchar(100),
logtime date);

---

insert into member(name, id, pwd, gender) values('홍길동', 'hong', '111', '0');

select * from member;

---

board.sql

-- [테이블] MySQL
CREATE TABLE board(
 seq INTEGER primary key auto_increment, -- 글번호 (시퀀스 객체 이용)
 id VARCHAR(20) NOT NULL,              -- 아이디
 name VARCHAR(40) NOT NULL,            -- 이름
 email VARCHAR(40),                    -- 이메일
 subject VARCHAR(255) NOT NULL,        -- 제목
 content VARCHAR(4000) NOT NULL,       -- 내용 
 
 ref INTEGER,                   -- 그룹번호
 lev INTEGER DEFAULT 0 NOT NULL,         -- 단계
 step INTEGER DEFAULT 0 NOT NULL,        -- 글순서
 pseq INTEGER DEFAULT 0 NOT NULL,        -- 원글번호
 reply INTEGER DEFAULT 0 NOT NULL,       -- 답변수
 hit INTEGER DEFAULT 0,                  -- 조회수
 logtime DATETIME DEFAULT now()      -- 날짜와 시간, current_timestamp
);

---

imageboard.sql

-- [테이블] MySQL
CREATE TABLE imageboard(
 seq INTEGER PRIMARY KEY AUTO_INCREMENT, 
 imageId VARCHAR(20) NOT NULL,       -- 상품코드 
 imageName VARCHAR(40) NOT NULL,     -- 상품명
 imagePrice int NOT NULL,            -- 단가
 imageQty int NOT NULL,              -- 개수
 imageContent VARCHAR(4000) NOT NULL, 
 image1 varchar(200), 
 logtime DATE DEFAULT (current_date) -- 날짜
);

---

https://mvnrepository.com/

---

---

---

---

db.properties

##MySQL
jdbc.driver=com.mysql.cj.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3306/mydb?serverTimezone=Asia/Seoul
jdbc.username=root
jdbc.password=1234

 

로그인하면 정상으로 되는 거 확인할 수 있다 !!

 

sysdate 다 now()로 바꿔야한다.

 

userMapper.xml

 	<!-- 회원가입 -->
 	 <insert id="memberWrite" parameterType="member">
 		insert into member values(#{name}, #{id}, #{pwd}, #{gender}, 
 								#{email1}, #{email2}, #{tel1}, #{tel2}, #{tel3}, 
 								#{zipcode}, #{addr1}, #{addr2}, now()) <!--  sysdate)-->
 	</insert>

 	
 	<!-- 회원정보 수정 -->
 	<update id="memberUpdate" parameterType="member">
 		update member set name=#{name}, pwd=#{pwd}, gender=#{gender}, 
 						  email1=#{email1}, email2=#{email2}, 
 						  tel1=#{tel1}, tel2=#{tel2}, tel3=#{tel3},
 						  zipcode=#{zipcode}, addr1=#{addr1}, addr2=#{addr2}, logtime=now() where id=#{id}
 	</update>

---

boardMapper.xml

	<!-- 글쓰기 -->
    <insert id="boardWrite" parameterType="board.bean.BoardDTO">
        insert into board (id, name, email, subject, content, ref)
        values(#{id}, #{name}, #{email}, #{subject}, #{content}, #{seq})
    </insert>

---

BoardListService.java

		//1페이지당 5개씩
		//오라클
		//int endNum = pg * 5;
		//int startNum = endNum - 4;

		//MySQL
		//int startNum = pg * 5 - 5; //시작위치, 0부터 시작 - 0, 5, 10
		//int endNum = 3; //개수
		
		int endNum = 5; //개수
		int startNum = pg*endNum - endNum; //시작위치, 0부터 시작 - 0, 5, 10

---

boardMapper.xml

    <!-- 글목록 -->
	<select id="boardList" parameterType="java.util.Map" resultType="board.bean.BoardDTO">
	    <!-- MySQL -->
	    select * from board order by ref desc, step asc limit #{startNum}, #{endNum} 
	</select>

 

 	<!-- 글 수정하기 -->
 	<update id="boardUpdate" parameterType="board.bean.BoardDTO">
 		update board set subject=#{subject}, content=#{content}, logtime=now() where seq=#{seq} 
 	</update>

 

---

BoardDAO.java

	public void boardWrite(BoardDTO boardDTO) {
		SqlSession sqlSession = sqlSessionFactory.openSession(); //생성
		//Oracle
		//sqlSession.insert("boardSQL.boardWrite", boardDTO);
		
		//MySQL
		sqlSession.insert("boardSQL.boardWrite", boardDTO);
		sqlSession.update("boardSQL.refUpdate");
		sqlSession.commit();
		sqlSession.close();
	}

---

boardMapper.xml

    <insert id="boardWrite" parameterType="board.bean.BoardDTO"  useGeneratedKeys="true" keyProperty="seq">
        insert into board (id, name, email, subject, content)
        values(#{id}, #{name}, #{email}, #{subject}, #{content})
    </insert>

    <update id="refUpdate">
    	update board set ref = LAST_INSERT_ID() WHERE seq = LAST_INSERT_ID()
    </update>

---

https://dbeaver.io/download/

Download | DBeaver Community

---

imageboardMapper.xml

	<!-- 이미지 등록 -->
	<insert id="imageboardWrite" parameterType="imageboard">
		insert into imageboard (seq, imageId, imageName, imagePrice, imageQty, imageContent, image1)
		values(null, #{imageId}, #{imageName}, #{imagePrice}, #{imageQty}, #{imageContent}, #{image1})
	</insert>

	<!-- 글목록 -->
	<select id="imageboardList" parameterType="java.util.Map" resultType="imageboard">
	    select * from imageboard order by seq desc limit #{startNum}, #{endNum} 
	</select>

 

---

ImageboardListService.java

		//1페이지당 3개씩
    	int endNum = 3;
    	int startNum = pg*endNum - endNum; //시작위치