Windows MySQL 설치

https://dev.mysql.com/downloads/mysql/

 

오라클은 1521인데 MySQL은 33

 

설치후 MySQL bin 폴더의 경로를 환경변수에 추가

C:\Program Files\MySQL\MySQL Server 8.0\bin

 

show databases;

create database mydb character set utf8 collate utf8_general_ci;

show databases;

use mydb;

-- table설정
create table member(
	no int,
    name varchar(34) not null,
    sex varchar(10),
    content text not null,
    constraint mem_no_pk primary key(no),
    constraint mem_sex_ck check(sex in('남자','여자'))
);

drop table member;

insert into member values(1,'김두두1','여자','김두두입니다');
insert into member values(2,'김두두2','여자','김두두입니다');
insert into member values(3,'김두두3','여자','김두두입니다');
insert into member values(4,'김두두4','여자','김두두입니다');
insert into member values(5,'김두두5','여자','김두두입니다');
insert into member values(6,'김두두6','여자','김두두입니다');

commit;

 

Help > About Workbench

 

MySQL 버전 확인

https://mvnrepository.com/artifact/mysql/mysql-connector-java

 

 

jdbc 연결해보기

 

MemberDAO.java (Class)

드라이버 명칭, URL 주소만 변경되고 나머지는 다 동일하다.

package doo.doo.dao;

import java.util.*;
import java.sql.*;
public class MemberDAO {
  private Connection conn;
  private PreparedStatement ps;
  private final String URL = "jdbc:mysql://localhost:3306/mydb?serverTimezone=UTC";

  public MemberDAO() {
    try {
      //드라이버 등록
      Class.forName("com.mysql.cj.jdbc.Driver");
    } catch (Exception ex) {
      ex.printStackTrace();
    }
  }

  public void getConnection() {
    try {
      conn = DriverManager.getConnection(URL, "root", "happy");
    } catch (Exception ex) {
      ex.printStackTrace();
    }
  }
  public void disConnection() {
    try {
      if (ps != null)
        ps.close();
      if (conn != null)
        conn.close();
    } catch (Exception ex) {
      ex.printStackTrace();
    }
  }

  public List < MemberVO > memberListData() {
    List < MemberVO > list = new ArrayList < MemberVO > ();
    try {
      getConnection();
      String sql = "SELECT no, name, sex, content FROM member";
      ps = conn.prepareStatement(sql);
      ResultSet rs = ps.executeQuery();

      while (rs.next()) {
        MemberVO vo = new MemberVO();
        vo.setNo(rs.getInt(1));
        vo.setName(rs.getString(2));
        vo.setSex(rs.getString(3));
        vo.setContent(rs.getString(4));
        list.add(vo);
      }
    } catch (Exception ex) {
      ex.printStackTrace();
    } finally {
      disConnection();
    }
    return list;
  }
}

MainClass.java (Class)

package doo.doo.app;
import doo.doo.dao.*;
import java.util.*;

public class MainClass {

  public static void main(String[] args) {
    MemberDAO dao = new MemberDAO();
    List < MemberVO > list = dao.memberListData();

    for (MemberVO vo: list) {
      System.out.println(vo.getNo() + "." + vo.getName());
    }
  }

}

 

 

myBatis 연결해보기

app.xml 세팅

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
  xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  xmlns:context="http://www.springframework.org/schema/context"
  xmlns:mybatis-spring="http://mybatis.org/schema/mybatis-spring"
  xmlns:p="http://www.springframework.org/schema/p"
  xsi:schemaLocation="http://www.springframework.org/schema/beans 
  http://www.springframework.org/schema/beans/spring-beans.xsd
    http://www.springframework.org/schema/context 
    http://www.springframework.org/schema/context/spring-context-4.3.xsd
    http://mybatis.org/schema/mybatis-spring 
    http://mybatis.org/schema/mybatis-spring-1.2.xsd">
  <context:component-scan base-package="doo.doo.*"/>
  <bean id="ds"
    class="org.apache.commons.dbcp.BasicDataSource"
    p:driverClassName="com.mysql.cj.jdbc.Driver"
    p:url="jdbc:mysql://localhost:3306/mydb?serverTimezone=UTC"
    p:username="root"
    p:password="happy"
  />
  <bean id="ssf"
    class="org.mybatis.spring.SqlSessionFactoryBean"
    p:dataSource-ref="ds"
  />
  <bean id="mapper"
    class="org.mybatis.spring.mapper.MapperFactoryBean"
    p:sqlSessionFactory-ref="ssf"
    p:mapperInterface="doo.doo.mapper.*"
  />
</beans>

MemberMapper.java (Interface)

package doo.doo.dao;
import java.util.*;

import org.apache.ibatis.annotations.Select;

import doo.doo.dao.*;

public interface MemberMapper {
  @Select("SELECT no, name, sex, content FROM member")
  public List < MemberVO > memberListData();
}

MemberDAO.java (Class)

package doo.doo.dao;
import java.util.*;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

@Repository
public class MemberDAO {
  @Autowired
  private MemberMapper mapper;

  public List < MemberVO > memberListData() {
    return mapper.memberListData();
  }
}

MainController.java (Class)

package doo.doo.app2;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.GetMapping;

import java.util.*;
import doo.doo.dao.*;

@Controller
public class MainController {
  @Autowired
  private MemberDAO dao;

  @GetMapping("member/list.do")
  public String member_list(Model model) {
    List < MemberVO > list = dao.memberListData();
    model.addAttribute("list", list);
    return "member/list";
  }
}

config/app.xml

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
  xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  xmlns:context="http://www.springframework.org/schema/context"
  xmlns:mybatis-spring="http://mybatis.org/schema/mybatis-spring"
  xmlns:p="http://www.springframework.org/schema/p"
  xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd
    http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-4.3.xsd
    http://mybatis.org/schema/mybatis-spring http://mybatis.org/schema/mybatis-spring-1.2.xsd">
  <context:component-scan base-package="doo.doo.*"/>
  <bean id="ds"
    class="org.apache.commons.dbcp.BasicDataSource"
    p:driverClassName="com.mysql.cj.jdbc.Driver"
    p:url="jdbc:mysql://localhost:3306/mydb?serverTimezone=UTC"
    p:username="root"
    p:password="happy"
  />
  <bean id="ssf"
    class="org.mybatis.spring.SqlSessionFactoryBean"
    p:dataSource-ref="ds"
  />
  <bean id="mapper"
    class="org.mybatis.spring.mapper.MapperFactoryBean"
    p:sqlSessionFactory-ref="ssf"
    p:mapperInterface="doo.doo.dao.MemberMapper"
  />
  <bean id="viewResolver"
    class="org.springframework.web.servlet.view.InternalResourceViewResolver"
    p:prefix="/"
    p:suffix=".jsp"
  />
</beans>

 

web.xml

<?xml version="1.0" encoding="UTF-8"?>
<web-app version="2.5" xmlns="http://java.sun.com/xml/ns/javaee"
  xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  xsi:schemaLocation="http://java.sun.com/xml/ns/javaee https://java.sun.com/xml/ns/javaee/web-app_2_5.xsd">
<servlet>
    <servlet-name>appServlet</servlet-name>
    <servlet-class>org.springframework.web.servlet.DispatcherServlet</servlet-class>
       <init-param>
      <param-name>contextConfigLocation</param-name>
      <param-value>/WEB-INF/config/app.xml</param-value>
    </init-param>
  </servlet>
    
  <servlet-mapping>
    <servlet-name>appServlet</servlet-name>
    <url-pattern>*.do</url-pattern>
  </servlet-mapping>
     <filter>
      <filter-name>encodingFilter</filter-name>
      <filter-class>org.springframework.web.filter.CharacterEncodingFilter</filter-class>
      <init-param>
        <param-name>encoding</param-name>
        <param-value>UTF-8</param-value>
      </init-param>
     </filter>
    
     <filter-mapping>
      <filter-name>encodingFilter</filter-name>
      <url-pattern>/*</url-pattern>
  </filter-mapping>
</web-app>

 

list.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<body>
  <c:forEach var="vo" items="${list }">
    <li>
    ${vo.no }-${vo.name }-${vo.sex }-${vo.content }
    </li>
  </c:forEach>
</body>
</html>