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(); } } } }