Dao习题

Grade实体类：

package com.wdk.entity;

import java.util.List;

public class Grade {
private int GradeID;
private String gradeName;
private List<Student> students;

public int getGradeID() {
 return GradeID;
}
public List<Student> getStudents() {
 return students;
}
public void setStudents(List<Student> students) {
 this.students = students;
}
public void setGradeID(int gradeID) {
 GradeID = gradeID;
}
public String getGradeName() {
 return gradeName;
}
public void setGradeName(String gradeName) {
 this.gradeName = gradeName;
}
public Grade(int gradeID, String gradeName) {
 super();
 GradeID = gradeID;
 this.gradeName = gradeName;
}
public Grade(){}
}

 

 

Student实体类：

package com.wdk.entity;

import javax.xml.crypto.Data;

public class Student {
   private int StudentNo;
   private String loginPwd;
   private String studentName;
   private int Sex;
   private int GardeId;
   private String Phone;
   private String Address;
   private Data broData;
   private String Email;
   private String IdentityCard;
   private Grade grade;
public Grade getGrade() {
 return grade;
}
public void setGrade(Grade grade) {
 this.grade = grade;
}
public int getStudentNo() {
 return StudentNo;
}
public void setStudentNo(int studentNo) {
 StudentNo = studentNo;
}
public String getLoginPwd() {
 return loginPwd;
}
public void setLoginPwd(String loginPwd) {
 this.loginPwd = loginPwd;
}
public String getStudentName() {
 return studentName;
}
public void setStudentName(String studentName) {
 this.studentName = studentName;
}
public int getSex() {
 return Sex;
}
public void setSex(int sex) {
 Sex = sex;
}
public int getGardeId() {
 return GardeId;
}
public void setGardeId(int gardeId) {
 GardeId = gardeId;
}
public String getPhone() {
 return Phone;
}
public void setPhone(String phone) {
 Phone = phone;
}
public String getAddress() {
 return Address;
}
public void setAddress(String address) {
 Address = address;
}
public Data getBroData() {
 return broData;
}
public void setBroData(Data broData) {
 this.broData = broData;
}
public String getEmail() {
 return Email;
}
public void setEmail(String email) {
 Email = email;
}
public String getIdentityCard() {
 return IdentityCard;
}
public void setIdentityCard(String identityCard) {
 IdentityCard = identityCard;
}

public Student(int studentNo, String loginPwd, String studentName, int sex,
  int gardeId, String phone, String address, Data broData, String email,
  String identityCard, Grade grade) {
 super();
 StudentNo = studentNo;
 this.loginPwd = loginPwd;
 this.studentName = studentName;
 Sex = sex;
 GardeId = gardeId;
 Phone = phone;
 Address = address;
 this.broData = broData;
 Email = email;
 IdentityCard = identityCard;
 this.grade = grade;
}
public Student(){}
}

 

   BaseD工具类：

package com.wdk.dao;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

public class BaseDao {
 public static final String DRIVER="com.mysql.jdbc.Driver";
    public static final String URL="jdbc:mysql:///myschool";
    public static final String USERNAME="root";
    public static final String PASSWORD="123";
 //连接对象
 Connection con=null;
 //PreparedStatement对象
 PreparedStatement ps=null;
 //ResultSet对象
 ResultSet rs=null;
 
 
 //获取连接对象
 public Connection getConnection() throws Exception{
  if(con==null){
    Class.forName(DRIVER);
   con=DriverManager.getConnection(URL,USERNAME,PASSWORD);
  }
   return con;
 }
 
 //关闭连接对象(和其他资源对象)
 public void closeConnection() throws Exception{
  if(con!=null){
   con.close();
  }if(rs!=null){
   rs.close();
  }
  if(ps!=null){
   ps.close();} 
   
  }
 
 //增删改
 public int executeUpdate(String sql,Object... objs) throws Exception{
  getConnection();
  ps=con.prepareStatement(sql);
  for (int i = 0; i < objs.length; i++) {
   ps.setObject(i+1, objs[i]);
  }
  int count=ps.executeUpdate();
  
  return count;
 }
 

 //查
 public ResultSet executeQuery(String sql,Object... objs) throws Exception{
  getConnection();
  ps=con.prepareStatement(sql);
  for (int i = 0; i < objs.length; i++) {
   ps.setObject(i+1, objs[i]);
  }
  rs=ps.executeQuery();
  return rs;
 }
}

 

 

IStudentDao 接口：

两个方法：

//查询所有学生记录，包含年纪名称
 List<Student> student() throws Exception;

 //查询S1年纪下的学生录
 Grade student2(String gradeName) throws Exception;

 

 

 

IStudentDaoImpl实现类：

 

package com.wdk.dao.Impl;

import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import com.wdk.dao.BaseDao;
import com.wdk.dao.IStudentDao;
import com.wdk.entity.Grade;
import com.wdk.entity.Student;

public class IStudentDaoImpl extends BaseDao implements IStudentDao{

 @Override
 //查询所有学生记录，包含年纪名称
 public List<Student> student() throws Exception {
  List<Student> students=null;
  String sql="select s.*,g.gradeName from student as s,grade as g where s.gradeid=g.gradeid";
  ResultSet rs = this.executeQuery(sql);
  if(rs!=null){
   students=new ArrayList<Student>();
   while (rs.next()) {
    Student student =new Student();
    Grade grdae=new Grade();
    student.setStudentNo(rs.getInt("StudentNo"));
    student.setLoginPwd(rs.getString("LoginPwd"));
    student.setStudentName(rs.getString("StudentName"));
    student.setSex(rs.getInt("Sex"));
    
    
    grdae.setGradeName(rs.getString("GradeName")); 
    student.setGrade(grdae);
    students.add(student);

   } 
  }
  return students;
 }
 @Override
 public Grade student2(String gradeName) throws Exception {
  Grade grade=null;
  Student student=null;
  List<Student> students=new ArrayList<Student>();
  String sql="select s.*,g.gradeName from student as s,grade as g where s.gradeid=g.gradeid and gradeName=?";
  ResultSet rs = this.executeQuery(sql,gradeName);
  if(rs!=null){
   while (rs.next()) {
   grade=new Grade();
   grade.setGradeName(rs.getString("gradeName"));
    student =new Student();
   student.setStudentName(rs.getString("studentName"));
   student.setGardeId(rs.getInt("gradeId"));
   student.setStudentNo(rs.getInt("studentNo"));
   
   students.add(student);
  }
   grade.setStudents(students);
  }
    return  grade;
  }
 }

 

 

Txet测试类：

package com.wdk.ui;

import java.util.List;

import com.wdk.dao.IStudentDao;
import com.wdk.dao.Impl.IStudentDaoImpl;
import com.wdk.entity.Grade;
import com.wdk.entity.Student;

public class Test {
     static IStudentDao Dao =new IStudentDaoImpl();
 public static void main(String[] args) throws Exception {
 List<Student> students=Dao.student();
 for(Student student:students){
  System.out.println(student.getStudentName()+" "+student.getGrade().getGradeName());
 }
 System.out.println("===========================================");
 Grade grade =Dao.student2("S1");
 for (Student student : grade.getStudents()) {
  System.out.println(student.getStudentName()+" "+grade.getGradeName());
 }
 }

}