(22.12.20)Lambda Expression 람다익스프레션과 JdbcTemplate 활용하여 Spring과 DB 연동하기

익명 클래스(Anonymous Class)
 - 선언 및 객체생성, 사용

함수형 인터페이스 
 - @FunctionalInterface
 - 선언 및 객체생성, 사용
 - Lambda Expression(람다식)

public class Main {
 
    public static void main(String[] args) 
    {
        //추상메소드의 기능 완성 > 구현
        //구현 방법1. implements 해주기
        //Sample s = new Sample(); < 에러가 생긴다.
        class SampleImple implements Sample
        {
            @Override
            public int add(int a, int b) 
            {
                return a+b;
            }
        }
        Sample s = new SampleImple();
        System.out.println(s.add(3, 5)); //8
        //익명클래스(인스턴스를 생성하면서 오버라이드까지 끝내는)
        //Anonymous Class
        
        System.out.println(
            new Sample() {
                @Override // 부모인 Sample 의 implements를 바로 하게됨
                public int add(int a, int b) {
                    return a+b;
                }    
            }.add(3, 4)//> 표현식
        );
        useSample(s);
        
        useSample(
            new Sample() {
                @Override // 부모인 Sample 의 implements를 바로 하게됨
                public int add(int a, int b) {
                    return a+b;
                }    
            }
        );
        
        useSample(
            new Sample() {
                @Override // 부모인 Sample 의 implements를 바로 하게됨
                public int add(int a, int b) {
                    return a+b;
                }    
            }, 3,5);
        
        useSample((a,b)->{return a+b;}); //Lambda Expression 
        //메소드가 한개인 인터페이스, 클래스인 경우 Lambda Expression 으로 구현이 가능하다.
        
    }
    public static void useSample(Sample s) {
        System.out.println(s.add(3, 5));
    }
    public static void useSample(Sample s, int a, int b) {
        System.out.println(s.add(a, b));
    }
}
//메소드를 한개만을 가진다는 걸 나타내는 주석
@FunctionalInterface  //함수형 인터페이스
interface Sample{
    public abstract int add(int a, int b);
}

 

사진의 23열부터 28열 까지 표현식이기 때문에 아규먼트로 들어갈 수 있다.

사진의 30열과 31열~38열까지 같은 걸 말한다. 객체의 참조이기 때문에 파라미터로 들어갈 수 있다.

---

Spring 과 DB 연결하기 
첫 번째 방법 : 저수준의 코드
두 번째 방법 : JdbcTemplate() (Boiler Plate Codes를 안써도 된다.)

기존에 DAO 클래스에서 DB에 연결하는 connection이나 rs, pstmt, conn 을 close을 하는 메소드나 try~catch같은 걸 생략할 수 있게 된다.

 

JdbcTemplate를 쓰기위해선 필요한 것들
-application.properties (DB 연결시 필요한 속성들) 설정
-pom.xml (dependancy) 설정
- DAO 클래스(JdbcTemDAO.java)

 

 

기존의 EmpDAO Class

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import org.springframework.stereotype.Component;
import com.ezen.spring.web.vo.Emp;
 
@Component
public class EmpDAO 
{
    private Connection conn;
    private PreparedStatement pstmt;
    private ResultSet rs;
    
    private Connection getConn()
    {
        try {
            Class.forName("oracle.jdbc.OracleDriver");
            conn = DriverManager.getConnection(
                    "jdbc:oracle:thin:@localhost:1521:xe", "SCOTT", "TIGER");
            this.conn = conn;
            return conn;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }
    public List<Emp> getList()
    {
        getConn();
        
        try {
            String sql="SELECT * FROM emp2";
            this.pstmt=conn.prepareStatement(sql);
            this.rs=pstmt.executeQuery();
            
            List<Emp> list = new ArrayList<>();
            while(rs.next())
            {
                int empno = rs.getInt("EMPNO");
                String ename = rs.getString("ENAME");
                int deptno    = rs.getInt("DEPTNO");
                java.sql.Date hiredate = rs.getDate("HIREDATE");
                float salary = rs.getFloat("SAL");
                
                Emp emp = new Emp();
                emp.setEmpno(empno);
                emp.setEname(ename);
                emp.setDeptno(deptno);
                emp.setHiredate(hiredate);
                emp.setSal(salary);
                
                list.add(emp);
            }
            return list;
        }catch(Exception ex) {
            ex.printStackTrace();
        }finally {
            closeAll();
        }
        return null;
    }
    public boolean addEmp(Emp emp)
    {
        getConn();
        
        try {
            String sql = "INSERT INTO emp2 "
                    + "(empno, ename, deptno, sal, hiredate) VALUES(?,?,?,?,?)";
            this.pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, emp.getEmpno());
            pstmt.setString(2, emp.getEname());
            pstmt.setInt(3, emp.getDeptno());
            pstmt.setFloat(4, emp.getSal());
            pstmt.setDate(5, emp.getHiredate());
 
            int rows = pstmt.executeUpdate();
            
            return rows>0 ? true : false;
 
        }catch(Exception ex) {
            ex.printStackTrace();
        }finally {
            closeAll();
        }
        return false;
    }
    public Emp getEmpByEmpno(int empno)
    {
        getConn();
        
        try {
            String sql = "SELECT * FROM emp2 WHERE empno="+empno;
            this.pstmt = conn.prepareStatement(sql);
            this.rs = pstmt.executeQuery(sql);
 
            if(rs.next())
            {
                int eno = rs.getInt("EMPNO");
                String ename = rs.getString("ENAME");
                int deptno = rs.getInt("DEPTNO");
                java.sql.Date hiredate = rs.getDate("HIREDATE");
                float salary = rs.getFloat("SAL");
                
                Emp emp = new Emp();
                emp.setEmpno(eno);
                emp.setEname(ename);
                emp.setDeptno(deptno);
                emp.setHiredate(hiredate);
                emp.setSal(salary);
                return emp;
            }
        }catch(Exception ex) {
            ex.printStackTrace();
        }finally {
            closeAll();
        }
        return null;
    }
    public boolean updateEmp(Emp emp)
    {
        getConn();
        
        try {
            String sql = "UPDATE emp2 SET sal=?, deptno=? WHERE empno=?";
            this.pstmt = conn.prepareStatement(sql);
            pstmt.setFloat(1, emp.getSal());
            pstmt.setInt(2, emp.getDeptno());
            pstmt.setInt(3, emp.getEmpno());
 
            int rows = pstmt.executeUpdate();
 
            return rows>0 ? true : false;
 
        }catch(Exception ex) {
            ex.printStackTrace();
        }finally {
            closeAll();
        }
        return false;
    }
    public boolean deleteEmp(int empno)
    {
        getConn();
        
        try {
            String sql = "DELETE FROM emp2 WHERE empno=?";
            this.pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1,empno);
 
            int rows = pstmt.executeUpdate();
 
            return rows>0 ? true : false;
 
        }catch(Exception ex) {
            ex.printStackTrace();
        }finally {
            closeAll();
        }
        return false;
    }
    private void closeAll()
    {
        try {
            if(rs!=null) rs.close();
            if(pstmt!=null) pstmt.close();
            if(conn!=null) conn.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

 

 

 

위 코드를 JdbcTemplate 사용하여 더 고수준의 코딩이 가능해 진다.

 

JdbcTemDAO.java

import java.sql.PreparedStatement;
import java.util.List;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.stereotype.Repository;
import com.ezen.spring.web.vo.Emp;
 
@Repository
public class JdbcTemDAO 
{
    @Autowired
    private JdbcTemplate jdbcTemplate;
    
    
    public List<Emp> getList()
    {
        String sql="SELECT * FROM emp2";
        List<Emp> list = jdbcTemplate.query(sql, (rs,i)->{
            int empno = rs.getInt("EMPNO");
            String ename = rs.getString("ENAME");
            int deptno    = rs.getInt("DEPTNO");
            java.sql.Date hiredate = rs.getDate("HIREDATE");
            float salary = rs.getFloat("SAL");
            
            Emp emp = new Emp();
            emp.setEmpno(empno);
            emp.setEname(ename);
            emp.setDeptno(deptno);
            emp.setHiredate(hiredate);
            emp.setSal(salary);
            return emp;
        });
        return list;
    }
    public List<Emp> getEmpByEmpno(int empno)
    {
        String sql = "SELECT * FROM emp2 WHERE empno=?";
        List<Emp> list = jdbcTemplate.query(sql, (rs,i)->{
            int getempno = rs.getInt("EMPNO");
            String ename = rs.getString("ENAME");
            int deptno    = rs.getInt("DEPTNO");
            java.sql.Date hiredate = rs.getDate("HIREDATE");
            float salary = rs.getFloat("SAL");
            
            Emp emp = new Emp();
            emp.setEmpno(getempno);
            emp.setEname(ename);
            emp.setDeptno(deptno);
            emp.setHiredate(hiredate);
            emp.setSal(salary);
            return emp;
        },empno);
        return list;
        
    }
    public List<Emp> detailByDeptno(int deptno)
    {
        String sql="SELECT * FROM emp2 WHERE deptno=?";
        List<Emp> list = jdbcTemplate.query(sql, (rs,i)->{
            int empno = rs.getInt("EMPNO");
            String ename = rs.getString("ENAME");
            int getdeptno    = rs.getInt("DEPTNO");
            java.sql.Date hiredate = rs.getDate("HIREDATE");
            float salary = rs.getFloat("SAL");
            
            Emp emp = new Emp();
            emp.setEmpno(empno);
            emp.setEname(ename);
            emp.setDeptno(getdeptno);
            emp.setHiredate(hiredate);
            emp.setSal(salary);
            return emp;
        },deptno);
        return list;
    }
    public boolean addEmp(Emp emp)
    {
        String sql = "INSERT INTO emp2 "
            + "(empno, ename, deptno, sal, hiredate) VALUES(?,?,?,?,?)";
        int rows = jdbcTemplate.update(sql, emp.getEmpno(),
                emp.getEname(),emp.getDeptno(),emp.getSal(),emp.getHiredate());
        return rows>0 ? true : false;
    }
    public boolean updateEmp(Emp emp)
    {
        String sql = "UPDATE emp2 SET sal=?, deptno=? WHERE empno=?";
        int rows = jdbcTemplate.update(sql,emp.getSal(),emp.getDeptno(),emp.getEmpno());
        return rows>0 ? true : false;
    }
    public boolean deleteEmp(int empno)
    {
        String sql = "DELETE FROM emp2 WHERE empno=?";
        int rows = jdbcTemplate.update(sql,empno);
        return rows>0 ? true : false;
    }
}