pikac

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.sql.Statement;

import java.util.ArrayList;

import java.util.List;

public class StudentDao {

private Connection connection;

private static final String USERNAME = "root";

private static final String PASSWORD = "mysql";

private static final String URL = "jdbc:mysql://localhost:3306/lkb";

public StudentDao(){ //여기서 connection객체를 생성해서 디비에 연결

try{

Class.forName("com.mysql.jdbc.Driver");

connection = DriverManager.getConnection(USERNAME,PASSWORD,URL);

}catch (ClassNotFoundException e) {

e.printStackTrace();

System.out.println("클래스 적재 실패");

} catch (SQLException e) {

e.printStackTrace();

System.out.println("연결 실패");

}

}

public void insertStudent(Student student){

String sql = "insert into student values (?,?,?)";

PreparedStatement pstmt = null;

try{

pstmt = connection.prepareStatement(sql);

pstmt.setInt(1, student.getId());

pstmt.setString(2, student.getName());

pstmt.setInt(3, student.getGrade());

pstmt.executeUpdate();

}catch(SQLException e){

e.printStackTrace();

}finally{

try{

if(pstmt != null && pstmt.isClosed())

pstmt.close();

}catch(SQLException e){

e.printStackTrace();

}

}

}

public void updateStudent(Student student){

//id에 해당하는 레코드를 찾아서 name과 grade를 매개변수로 받은 값으로 수정

String sql = "update student set name=?, grade=? where id=?";

PreparedStatement pstmt = null;

try{

pstmt = connection.prepareStatement(sql);

pstmt.setString(1, student.getName());

pstmt.setInt(2, student.getGrade());

pstmt.setInt(3, student.getId());

pstmt.executeUpdate();

}catch(SQLException e){

e.printStackTrace();

}finally{

try{

if(pstmt != null && pstmt.isClosed())

pstmt.close();

}catch(SQLException e){

e.printStackTrace();

}

}

}

public void deleteStudent(int id){

//유일한 컬럼값인 id가 매개변수 id에 해당하는 레코드를 삭제

String sql = "delete from student where id=?";

PreparedStatement pstmt = null;

try{

pstmt = connection.prepareStatement(sql);

pstmt.setInt(1, id);

pstmt.executeUpdate();

}catch(SQLException e){

e.printStackTrace();

}finally{

try{

if(pstmt != null && pstmt.isClosed())

pstmt.close();

}catch(SQLException e){

e.printStackTrace();

}

}

}

public Student selectOne(int id){

//유일한 컬럼값인 id가 매개변수 id에 해당하는 레코드를 조회

String sql = "select * from student where id = ?";

PreparedStatement pstmt = null;

ResultSet rs = null;

Student student = null; //리턴할 객체참조변수

try{

pstmt = connection.prepareStatement(sql);

pstmt.setInt(1, id);

rs =  pstmt.executeQuery();

if(rs.next())

{

student = new Student();

student.setId(rs.getInt("id"));

student.setName(rs.getString("name"));

student.setGrade(rs.getInt("grade"));

}

}catch(SQLException e){

e.printStackTrace();

}finally{

try{

if(pstmt != null && !pstmt.isClosed())

pstmt.close();

if( rs != null && !rs.isClosed())

pstmt.close();

}catch(SQLException e){

e.printStackTrace();

}

}

return student;

}

public List<Student> selectAll(){

String sql = "select*from student";

PreparedStatement pstmt = null;

ResultSet rs = null;

List<Student> studentList = new ArrayList<Student>();

try{

pstmt = connection.prepareStatement(sql);

rs = pstmt.executeQuery();

while(rs.next())

{

Student student = new Student();

student.setId(rs.getInt("id"));

student.setName(rs.getString("name"));

student.setGrade(rs.getInt("grade"));

studentList.add(student);

}

}catch(SQLException e){

e.printStackTrace();

}finally{

try{

if( pstmt != null && !pstmt.isClosed())

pstmt.close();

if( rs != null && !rs.isClosed())

pstmt.close();

}catch(SQLException e){

e.printStackTrace();

}

}

return studentList;

}

}