도도의 초록누리

1. [JSP] 8. 영속프레임워크 MyBatis를 활용한 CRUD 구현 - JSP와 Oracle (XML 방식), 2020-9-19
https://yyman.tistory.com/1390 

-> XML 방식으로 구현됨.

- IDE: Spring-Tool-Suite 4-4.7.2 Releases (2020-06 최신)

- DB: Oracle Databases 19g (2020-09 최신)

- Maven 3.6.3/1.16.0.20200610-1735 (2020-09 최신)

- JAR: javax.servlet-api.4.0.1.jar (2020-09 최신)

         ojdbc8-19.7.0.0.jar (2020-09 최신)

         MyBatis 3.5.5 (2020-09 최신)

CREATE TABLE comp_users (

username VARCHAR(50) NOT NULL,

password VARCHAR(300) NOT NULL,

enabled INT NOT NULL,

PRIMARY KEY (username)

);

-- 계정

INSERT INTO comp_users (username, password, enabled) VALUES ('user', '$2a$10$x04djNV2e9rpcPPRyXoLk.rMm6iZe2/vYdzpqHQcLeNSYdt7kc30O', 1);

INSERT INTO comp_users (username, password, enabled) VALUES ('admin', '$2a$10$QUddY3O/6ZgkYCR6MFlv9.nqA501Fm0cc/ZxQHX5pwb1o0CYCTiIS', 1);

<?xml version="1.0" encoding="UTF-8"?>

<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"

  xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">

  <modelVersion>4.0.0</modelVersion>

  <groupId>com.examplebatis</groupId>

  <artifactId>web</artifactId>

  <version>0.0.1-SNAPSHOT</version>

  <packaging>war</packaging>

  <name>web Maven Webapp</name>

  <!-- FIXME change it to the project's website -->

  <url>http://www.example.com</url>

  <properties>

    <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>

    <maven.compiler.source>1.7</maven.compiler.source>

    <maven.compiler.target>1.7</maven.compiler.target>

  </properties>

  <dependencies>

    <dependency>

      <groupId>junit</groupId>

      <artifactId>junit</artifactId>

      <version>4.11</version>

      <scope>test</scope>

    </dependency>

    <!-- https://mvnrepository.com/artifact/javax.servlet/javax.servlet-api -->

<dependency>

    <groupId>javax.servlet</groupId>

    <artifactId>javax.servlet-api</artifactId>

    <version>4.0.1</version>

    <scope>provided</scope>

</dependency>

<!-- https://mvnrepository.com/artifact/org.mybatis/mybatis -->

<dependency>

    <groupId>org.mybatis</groupId>

    <artifactId>mybatis</artifactId>

    <version>3.5.5</version>

</dependency>

<dependency>

<groupId>com.oracle.database.jdbc</groupId>

<artifactId>ojdbc8</artifactId>

<version>19.7.0.0</version>

</dependency>

  </dependencies>

  <build>

    <finalName>web</finalName>

    <pluginManagement><!-- lock down plugins versions to avoid using Maven defaults (may be moved to parent pom) -->

      <plugins>

        <plugin>

          <artifactId>maven-clean-plugin</artifactId>

          <version>3.1.0</version>

        </plugin>

        <!-- see http://maven.apache.org/ref/current/maven-core/default-bindings.html#Plugin_bindings_for_war_packaging -->

        <plugin>

          <artifactId>maven-resources-plugin</artifactId>

          <version>3.0.2</version>

        </plugin>

        <plugin>

          <artifactId>maven-compiler-plugin</artifactId>

          <version>3.8.0</version>

        </plugin>

        <plugin>

          <artifactId>maven-surefire-plugin</artifactId>

          <version>2.22.1</version>

        </plugin>

        <plugin>

          <artifactId>maven-war-plugin</artifactId>

          <version>3.2.2</version>

        </plugin>

        <plugin>

          <artifactId>maven-install-plugin</artifactId>

          <version>2.5.2</version>

        </plugin>

        <plugin>

          <artifactId>maven-deploy-plugin</artifactId>

          <version>2.8.2</version>

        </plugin>

      </plugins>

    </pluginManagement>

  </build>

</project>

package com.exmple.web.db;

import java.io.IOException;

import java.io.InputStream;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.sql.SQLException;

import javax.sql.DataSource;

import org.apache.ibatis.io.Resources;

import org.apache.ibatis.mapping.Environment;

import org.apache.ibatis.session.Configuration;

import org.apache.ibatis.session.SqlSessionFactory;

import org.apache.ibatis.session.SqlSessionFactoryBuilder;

import org.apache.ibatis.transaction.TransactionFactory;

import org.apache.ibatis.transaction.jdbc.JdbcTransactionFactory;

import com.exmple.web.mapper.CompUsersMapper;

import oracle.jdbc.pool.OracleDataSource;

public class SqlMapSessionFactory {

private static SqlMapSessionFactory factory = new SqlMapSessionFactory();

private SqlMapSessionFactory() {}

private final static String driverName = "oracle.jdbc.driver.OracleDriver";

private final static String dbUrl = "jdbc:oracle:thin:@127.0.0.1:1521:orcl";

private final static String userName = "사용자계정명";

private final static String userPassword = "비밀번호";

public static SqlMapSessionFactory getInstance() {

return factory;

}

public static SqlSessionFactory ssf;

    static{

    DataSource dataSource = getOracleDataSource();

    TransactionFactory transactionFactory = new JdbcTransactionFactory();

    Environment environment = new Environment("development", transactionFactory, dataSource);

    Configuration configuration = new Configuration(environment);

    configuration.addMapper(CompUsersMapper.class); // Mapper 클래스

        ssf = new SqlSessionFactoryBuilder().build(configuration);

    }

// iBatis(MyBatis 반환)

public static SqlSessionFactory getSqlSessionFactory(){

        return ssf;

    }

/*

*     public static DataSource getMySQLDataSource() {

        Properties props = new Properties();

        FileInputStream fis = null;

        MysqlDataSource mysqlDS = null;

        try {

            fis = new FileInputStream("db.properties");

            props.load(fis);

            mysqlDS = new MysqlDataSource();

            mysqlDS.setURL(props.getProperty("MYSQL_DB_URL"));

            mysqlDS.setUser(props.getProperty("MYSQL_DB_USERNAME"));

            mysqlDS.setPassword(props.getProperty("MYSQL_DB_PASSWORD"));

        } catch (IOException e) {

            e.printStackTrace();

        }

        return mysqlDS;

    }

    */

/*

* Description: 순정 오라클 데이터소스

*/

    private static DataSource getOracleDataSource(){

    OracleDataSource oracleDS = null;

    try {

            oracleDS = new OracleDataSource();

            oracleDS.setURL(dbUrl);

            oracleDS.setUser(userName);

            oracleDS.setPassword(userPassword);

        } catch (SQLException e) {

            e.printStackTrace();

        }

        return oracleDS;

    }

public Connection connect() {

Connection conn = null;

try {

Class.forName(driverName);

conn = DriverManager.getConnection(dbUrl, userName, userPassword);

}

catch(Exception ex) {

System.out.println("오류 발생: " + ex);

}

return conn;

}

public void close(Connection conn, PreparedStatement ps, ResultSet rs) {

if ( rs != null ) {

try {

rs.close();

}

catch(Exception ex) {

System.out.println("오류 발생: " + ex);

}

close(conn, ps); // Recursive 구조 응용(재귀 함수)

} // end of if

}

public void close(Connection conn, PreparedStatement ps) {

if (ps != null ) {

try {

ps.close();

}

catch(Exception ex) {

System.out.println("오류 발생: " + ex);

}

} // end of if

if (conn != null ) {

try {

conn.close();

}

catch(Exception ex) {

System.out.println("오류 발생: " + ex);

}

} // end of if

}

}

비고: MySQL의 지원에 대해서 코드를 적어놓았다.

<!DOCTYPE web-app PUBLIC

 "-//Sun Microsystems, Inc.//DTD Web Application 2.3//EN"

 "http://java.sun.com/dtd/web-app_2_3.dtd" >

<web-app>

  <display-name>Archetype Created Web Application</display-name>

  <servlet>

  <servlet-name>FrontController</servlet-name>

  <display-name>FrontController</display-name>

  <description></description>

  <servlet-class>com.exmple.web.controller.FrontController</servlet-class>

  </servlet>

  <servlet-mapping>

  <servlet-name>FrontController</servlet-name>

  <url-pattern>/sample</url-pattern>

  </servlet-mapping>

</web-app>

package com.exmple.web.model;

public class CompUsers {

private String username;

private String password;

private int enabled;

public String getUsername() {

return username;

}

public void setUsername(String username) {

this.username = username;

}

public String getPassword() {

return password;

}

public void setPassword(String password) {

this.password = password;

}

public int getEnabled() {

return enabled;

}

public void setEnabled(int enabled) {

this.enabled = enabled;

}

}

package com.exmple.web.mapper;

import org.apache.ibatis.annotations.Mapper;

import org.apache.ibatis.annotations.Select;

import com.exmple.web.model.CompUsers;

@Mapper

public interface CompUsersMapper {

  @Select("SELECT * FROM comp_users WHERE username = #{username}")

  public CompUsers findByUsername(String username);

}

대응하는 방식에는 XML Mapper를 적용하는 방법도 있다.
(참고로 자바방식으로 구현했다면, XML Mapper는 사용할 수 없다.)

(DB를 설계하면서 View도 만들 수도 있고 각종 조합을 할 수도 있는데, 엔터티를 잘 파악해서 구현하면 될 것 같다.)

[Spring Boot 버전의 MyBatis 핵심 사용법]

@Mapper

public interface StudentMyBatisRepository {

@Select("select * from student")

public List<Student> findAll();

@Select("SELECT * FROM student WHERE id = #{id}")

public Student findById(long id);

@Delete("DELETE FROM student WHERE id = #{id}")

public int deleteById(long id);

@Insert("INSERT INTO student(id, name, passport) VALUES (#{id}, #{name}, #{passport})")

public int insert(Student student);

@Update("Update student set name=#{name}, passport=#{passport} where id=#{id}")

public int update(Student student);

}

package com.exmple.web.controller;

import java.io.IOException;

import javax.servlet.ServletException;

import javax.servlet.http.HttpServlet;

import javax.servlet.http.HttpServletRequest;

import javax.servlet.http.HttpServletResponse;

import org.apache.ibatis.session.SqlSession;

import org.apache.ibatis.session.SqlSessionFactory;

import com.exmple.web.db.SqlMapSessionFactory;

import com.exmple.web.mapper.CompUsersMapper;

import com.exmple.web.model.CompUsers;

public class FrontController extends HttpServlet {

private static final long serialVersionUID = 1L;

    SqlSessionFactory factory = SqlMapSessionFactory.getSqlSessionFactory();

/**

* @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)

*/

protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {

doAction(request, response);

}

/**

* @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response)

*/

protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {

doAction(request, response);

}

protected void doAction(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {

try (SqlSession session = factory.openSession()) {

  CompUsersMapper mapper = session.getMapper(CompUsersMapper.class);

  CompUsers user = mapper.findByUsername("user");

  System.out.println("계정명:" + user.getUsername());

}

}

}

1. [JSP] 20. MyBatis-3.5.5, HikariCP 3.4.2 연동 - Maven(Servlet) Spring 제거버전 (Oracle 19g) - Java 방식, 2020-10-01

https://yyman.tistory.com/1435