学生信息系统service层

package org.student.dao;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.Scanner;

import ogr.student.entity.Student;


public class StudentDao {
    final String URL = "jdbc:mysql://localhost:3306/test";
    final String USERNAME = "root";
    final String PWD = "12345";
    
    //查询学生是否存在
    public boolean ifExit(int sno) {
        return queryStudentBySno(sno)==null?false:true;
    }
    
    
    //增加学生
    public boolean addStudent(Student student) {
        final String URL = "jdbc:mysql://localhost:3306/test";
        final String USERNAME = "root";
        final String PWD = "12345";
        Connection connection = null;
        PreparedStatement  pstmt = null;

        try {
            // a.导入驱动，加载具体的驱动类
            Class.forName("com.mysql.jdbc.Driver");// 加载具体的驱动类
            // b.与数据库建立连接
            connection = DriverManager.getConnection(URL, USERNAME, PWD);
            

            //PreparedStatement
            String sql = "insert into student(sno,sage,sname,saddress) values(?,?,?,?)";
            pstmt = connection.prepareStatement(sql);//预编译
            pstmt.setInt(1, student.getNum());
            pstmt.setInt(2, student.getAge());
            pstmt.setString(3, student.getName());
            pstmt.setString(4, student.getAdress());
            
            int count =pstmt.executeUpdate() ;
            if(count>0) {
                return true;
            }else {
                return false;
            }
            
            // d.处理结果
            
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
            return false;
        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        } catch(Exception e) {
            e.printStackTrace();
            return false;
        }
        finally {
            try {
                 if(pstmt!=null) pstmt.close();// 对象.方法
                 if(connection!=null)connection.close();
            }catch(SQLException e) {
                e.printStackTrace();
            }
        }
    }
    
    
    //根据学号查询学生
    public Student queryStudentBySno(int sno) {
        Student student=null;
        Connection connection = null;
        PreparedStatement stmt=null;
        ResultSet rs=null;
        try {
            Class.forName("com.mysql.jdbc.Driver");
            connection = DriverManager.getConnection(URL, USERNAME, PWD);
            String sql="select * form student where sno=?";
            stmt = connection.prepareStatement(sql);
            stmt.setInt(1, sno);
            rs=stmt.executeQuery();
            if(rs.next()) {
                int no= rs.getInt("sno");
                int age=rs.getInt("sage");
                String name=rs.getNString("sname");
                String address = rs.getNString("sadress");
                student= new Student(no,age,name,address);
            }
            return student;        
        } catch (ClassNotFoundException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
            return null;
        }catch (SQLException e) {
            e.printStackTrace();
            return null;
        } catch(Exception e) {
            e.printStackTrace();
            return null;
        }finally {
            try {
                 if(rs!=null) rs.close();
                 if(stmt!=null) stmt.close();// 对象.方法
                 if(connection!=null)connection.close();
            }catch(SQLException e) {
                e.printStackTrace();
            }
        }
        
    }
    //查询全部学生
    public List<Student> queryallStudent() {
        List<Student> students=new ArrayList<>();
        Student student = null;
        Connection connection = null;
        PreparedStatement stmt=null;
        ResultSet rs=null;
        try {
            Class.forName("com.mysql.jdbc.Driver");
            connection = DriverManager.getConnection(URL, USERNAME, PWD);
            String sql="select * form student ";
            stmt = connection.prepareStatement(sql);
            
            rs=stmt.executeQuery();
            while(rs.next()) {
                int no= rs.getInt("sno");
                int age=rs.getInt("sage");
                String name=rs.getNString("sname");
                String address = rs.getNString("sadress");
                student= new Student(no,age,name,address);
                students.add(student);
            }
            return students;        
        } catch (ClassNotFoundException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
            return null;
        }catch (SQLException e) {
            e.printStackTrace();
            return null;
        } catch(Exception e) {
            e.printStackTrace();
            return null;
        }finally {
            try {
                 if(rs!=null) rs.close();
                 if(stmt!=null) stmt.close();// 对象.方法
                 if(connection!=null)connection.close();
            }catch(SQLException e) {
                e.printStackTrace();
            }
        }
        
    }
    
    //根据学号删除学生
     public boolean deleteStudentsno(int sno) {
            final String URL = "jdbc:mysql://localhost:3306/test";
            final String USERNAME = "root";
            final String PWD = "12345";
            Connection connection = null;
            PreparedStatement  pstmt = null;

            try {
                // a.导入驱动，加载具体的驱动类
                Class.forName("com.mysql.jdbc.Driver");// 加载具体的驱动类
                // b.与数据库建立连接
                connection = DriverManager.getConnection(URL, USERNAME, PWD);
                

                //PreparedStatement
                String sql = "delete * from student where sno=?";
                pstmt = connection.prepareStatement(sql);//预编译
                pstmt.setInt(1, sno);
                
                int count =pstmt.executeUpdate() ;
                if(count>0) {
                    return true;
                }else {
                    return false;
                }
                
                // d.处理结果
                
            } catch (ClassNotFoundException e) {
                e.printStackTrace();
                return false;
            } catch (SQLException e) {
                e.printStackTrace();
                return false;
            } catch(Exception e) {
                e.printStackTrace();
                return false;
            }
            finally {
                try {
                     if(pstmt!=null) pstmt.close();// 对象.方法
                     if(connection!=null)connection.close();
                }catch(SQLException e) {
                    e.printStackTrace();
                }
            }
        }
     
     //根据学号修改
     public boolean updateStudent(int sno,Student student) {
            final String URL = "jdbc:mysql://localhost:3306/test";
            final String USERNAME = "root";
            final String PWD = "12345";
            Connection connection = null;
            PreparedStatement  pstmt = null;

            try {
                // a.导入驱动，加载具体的驱动类
                Class.forName("com.mysql.jdbc.Driver");// 加载具体的驱动类
                // b.与数据库建立连接
                connection = DriverManager.getConnection(URL, USERNAME, PWD);
                

                //PreparedStatement
                String sql = "update student set sname=?,sage=?,sadress=? wehere sno=?";
                pstmt = connection.prepareStatement(sql);//预编译
                pstmt.setString(1, student.getName());
                pstmt.setInt(2, student.getAge());
                pstmt.setString(3, student.getAdress());
                pstmt.setInt(4, student.getNum());
                
                
                
                int count =pstmt.executeUpdate() ;
                if(count>0) {
                    return true;
                }else {
                    return false;
                }
                
                // d.处理结果
                
            } catch (ClassNotFoundException e) {
                e.printStackTrace();
                return false;
            } catch (SQLException e) {
                e.printStackTrace();
                return false;
            } catch(Exception e) {
                e.printStackTrace();
                return false;
            }
            finally {
                try {
                     if(pstmt!=null) pstmt.close();// 对象.方法
                     if(connection!=null)connection.close();
                }catch(SQLException e) {
                    e.printStackTrace();
                }
            }
        }

}