jdbc Select

DBConnection

package db;
 
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
 
public class DBConnection {
    public static void initConnection() {
        try {
            Class.forName("oracle.jdbc.driver.OracleDriver");
 
            System.out.println("Driver Loading Success! ");
 
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }
    
    public static Connection getConnection() {
 
        Connection conn = null;
 
//        New Oracle : 우클릭 프로퍼티 -> 드라이버 프로퍼티 -> Connection Url 복사 
        try {
            conn = DriverManager.getConnection("jdbc:oracle:thin:@192.168.2.5:1521:xe", "hr", "hr");
 
            System.out.println("DB Connection Success");
 
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
 
        return conn;
 
    }
}
 

 

 

 

 

 

DBClose

package db;
 
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
 
 
public class DBClose {
    public static void close(PreparedStatement psmt, Connection conn, ResultSet rs) {
 
        try {
            if (psmt != null) {
                psmt.close();
            }
            if(conn != null) {
                conn.close();
            }
            if(rs != null) {
                rs.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
                                
    }
}
 

 

 

SelectTest

 

package jdbc;
 
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
 
import db.DBClose;
import db.DBConnection;
import dto.userDto;
 
public class SelectTest {
    // 1개의 데이터만을 취득
    
    // select는 1개의 데이터만을 취득하거나
    // 데이터 전부를 취득
    // 크게 2가지로 나눌수 있다.
    
    // 1개의 데이터(row)만을 취득 
    
    //statement 사용 
    public userDto search(String id) {
        String sql = " SELECT ID, NAME, AGE, JOINDATE"
                + " FROM USERDTO "
                + " WHERE ID = '"  + id + "'";
        Connection conn = DBConnection.getConnection();
        PreparedStatement psmt = null;
        
        ResultSet rs = null;
        
        userDto dto = null;
        
        System.out.println("sql: " + sql);
        
        try {
            psmt = conn.prepareStatement(sql);
            rs = psmt.executeQuery();
            
            // 현재 rs부분의 데이터가 있는 경우 
            if(rs.next()) {    
                String _id = rs.getString("id"); //SELECT ID
                String _name = rs.getString("name"); // SELECT NAME
                int _age = rs.getInt("age");
                String _joindate = rs.getString("joindate");
                
                dto = new userDto(_id, _name, _age, _joindate);            
                            
            }
            
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } finally {
            DBClose.close(psmt, conn, rs);
        }
        
        return dto;
    }
    
    
    //PreparedStatement 사용하기 
    //PreparedStatement를 사용하면 ? 로 대체가 가능하다 
    public userDto select(String id) {
        String sql = " SELECT ID, NAME, AGE, JOINDATE "
                + " FROM USERDTO "
                + " WHERE ID = ? ";
        
        Connection conn = DBConnection.getConnection();
        PreparedStatement psmt = null;
        ResultSet rs = null;
        
        userDto dto = null;
            
        try {
            psmt = conn.prepareStatement(sql);
            psmt.setString(1, id);    // 위의 ? 에 대입된다
            
            rs = psmt.executeQuery();
            
            if(rs.next()) {
                dto = new userDto();
                
                dto.setId(rs.getString("id"));
                dto.setName(rs.getString("name"));
                dto.setAge(rs.getInt("age"));
                dto.setJoindate(rs.getString("joindate"));
                
            }
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } finally {
            
            DBClose.close(psmt, conn, rs);
        }
        
        return dto;
    }
    
    // 데이터 다수의 데이터 취득
        public List<userDto> getUserList(){
            
            String sql = " SELECT ID, NAME, AGE, JOINDATE "
                    + " FROM USERDTO ";
            
            Connection conn = null;
            PreparedStatement psmt = null;
            ResultSet rs = null;
            
            List<userDto> list = new ArrayList<userDto>();
            
            System.out.println("sql: " + sql);
            
            try {
                conn = DBConnection.getConnection();
                psmt = conn.prepareStatement(sql);
                rs = psmt.executeQuery();
                
                while(rs.next()) {
                    String id = rs.getString("id");
                    String name = rs.getString("name");
                    int age = rs.getInt("age");
                    String joindate = rs.getString("joindate");
                    
                    list.add(new userDto(id, name, age, joindate));
                }
                
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }finally {
                DBClose.close(psmt, conn, rs);
            }
            return list;
        }
 
}
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 

 

 

메인

 

package selectTest;
 
import java.util.List;
 
import db.DBConnection;
import dto.userDto;
import jdbc.SelectTest;
 
public class mainClass {
    public static void main(String[] args) {
        DBConnection.initConnection();
        
        SelectTest st = new SelectTest();
        
        String id = "ccc";
        userDto dto = st.search(id);
        if(dto != null) {
            System.out.println(dto.toString());
        }else {
            System.out.println("id를 찾을 수 없습니다");
        }
        
        id = "aaa";
        dto = st.select(id);
        if(dto != null) {
            System.out.println(dto.toString());
        }else {
            System.out.println("id를 찾을 수 없습니다");
        }
        
        List<userDto> list = st.getUserList();
        
        for (int i = 0; i < list.size(); i++) {
            System.out.println(list.get(i).toString());
        }
    }
    
}