查询所有学生信息,年级信息以年级名称显示

  一、DAO层搭建:

    1.实体类:

      Student

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
package com.myschool.entity;
 
import java.io.Serializable;
import java.util.Date;
 
public class Student implements Serializable {
     
    private static final long serialVersionUID = 6439763802252472361L;
     
    //定义实体属性
    private int studentNo;
    private String longinPwd;
    private String studentName;
    private int sex;
    private grade grade;//把年级对象作为属性
    private String phone;
    private String address;
    private Date bornDate;
    private String email;
    private String identityCard;
     
        //封装
    public int getStudentNo() {
        return studentNo;
    }
    public void setStudentNo(int studentNo) {
        this.studentNo = studentNo;
    }
    public String getLonginPwd() {
        return longinPwd;
    }
    public void setLonginPwd(String longinPwd) {
        this.longinPwd = longinPwd;
    }
    public String getStudentName() {
        return studentName;
    }
    public void setStudentName(String studentName) {
        this.studentName = studentName;
    }
    public int getSex() {
        return sex;
    }
    public void setSex(int sex) {
        this.sex = sex;
    }
    public grade getGrade() {
        return grade;
    }
    public void setGrade(grade grades) {
        this.grade = grades;
    }
    public String getPhone() {
        return phone;
    }
    public void setPhone(String phone) {
        this.phone = phone;
    }
    public String getAddress() {
        return address;
    }
    public void setAddress(String address) {
        this.address = address;
    }
    public Date getBornDate() {
        return bornDate;
    }
    public void setBornDate(Date bornDate) {
        this.bornDate = bornDate;
    }
    public String getEmail() {
        return email;
    }
    public void setEmail(String email) {
        this.email = email;
    }
    public String getIdentityCard() {
        return identityCard;
    }
    public void setIdentityCard(String identityCard) {
        this.identityCard = identityCard;
    }
     
       //有参构造
    public Student(int studentNo, String longinPwd, String studentName,
            int sex, grade gradeID, String phone, String address, Date bornDate,
            String email, String identityCard) {
        this.studentNo = studentNo;
        this.longinPwd = longinPwd;
        this.studentName = studentName;
        this.sex = sex;
        this.gradeID = gradeID;
        this.phone = phone;
        this.address = address;
        this.bornDate = bornDate;
        this.email = email;
        this.identityCard = identityCard;
    }
 
    //无参构造
    public Student() {
         
    }

     Grade类:

复制代码
package com.myschool.entity;

public class grade {
    private int gradeid;
    private String gradeName;
    public int getGradeid() {
        return gradeid;
    }
    public void setGradeid(int gradeid) {
        this.gradeid = gradeid;
    }
    public String getGradeName() {
        return gradeName;
    }
    public void setGradeName(String gradeName) {
        this.gradeName = gradeName;
    }
    public grade(int gradeid, String gradeName) {
        super();
        this.gradeid = gradeid;
        this.gradeName = gradeName;
    }
    public grade() {
        super();
        
    }
    
}
复制代码

    2.Daobase:

复制代码
package com.myschool.dao;

import java.beans.Statement;

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

public class baseDao {

    // 创建连接参数
    private final static String DRIVER = "com.mysql.jdbc.Driver";
    private final static String URL = "jdbc:mysql:///myschool";
    private final static String USER_NAME = "root";
    private final static String PASSWORD = "123";

    Connection con = null;
    PreparedStatement prestatement = null;
    ResultSet rs = null;

    // 获取连接
    private Connection getConnection() {
        try {
            Class.forName(DRIVER);
            if (con == null) {
                con = DriverManager.getConnection(URL, USER_NAME, PASSWORD);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }

        return con;
    }

    // 增删改
    public int executeUpdate(String sql, Object... obj) throws Exception {
        // 获取连接
        getConnection();

        // 获取prepareStatement对象
        prestatement = con.prepareStatement(sql);

        // 循环添加参数
        for (int i = 1; i <= obj.length; i++) {
            prestatement.setObject(i, obj[i - 1]);
        }
        // 执行SQL语句
        int count = prestatement.executeUpdate();
        return count;

    }

    // 查
    public ResultSet executeQuery(String sql, Object... obj) throws Exception {
        // 获取连接
        getConnection();

        // 获取prepareStatement对象
        prestatement = con.prepareStatement(sql);

        // 循环添加参数
        for (int i = 1; i <= obj.length; i++) {
            prestatement.setObject(i, obj[i - 1]);
        }
        // 执行SQL语句
        rs = prestatement.executeQuery();

        return rs;

    }
    
    //回收资源
    public void closeResouse() throws Exception
    {
        if (rs!=null) {
            rs.close();
        }
        if (prestatement!=null) {
            prestatement.close();
        }
        if (con!=null) {
            con.close();
        }
        
    }
 
}
复制代码

 

    3.DAO接口:

复制代码
package com.myschool.dao;

import java.util.List;

import com.myschool.entity.Student;

public interface IStudentDao {

    /*
     * 查询所有学生记录,年级名称
     */
    public List<Student> Search() throws Exception;

    
}
复制代码

 

     4.DAO接口实现类:

复制代码
package com.mychool.dao.impl;

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

import com.myschool.dao.IStudentDao;
import com.myschool.dao.baseDao;
import com.myschool.entity.Student;
import com.myschool.entity.grade;

public class IStudentDaoImpl extends baseDao implements IStudentDao {

    
    @Override
    public List<Student> Search() throws Exception {
        //创建student对象泛型集合
        List<Student> stus=new ArrayList<Student>();
        
        String sql="Select studentName,GradeName from Student,grade where Grade.gradeid=Student.gradeid ";
//定义resultSet对象接收basedao的查询方法查出来的数据 ResultSet rSet=executeQuery(sql); if (rSet!=null) { while (rSet.next()) { Student stu=new Student(); grade grade=new grade();//创建年级对象 // stu.setAddress(rSet.getString("address")); // stu.setEmail(rSet.getString("email")); // stu.setIdentityCard(rSet.getString("identityCard")); // stu.setLonginPwd(rSet.getString("longinPwd")); // stu.setPhone(rSet.getString("phone")); // stu.setSex(rSet.getInt("sex")); grade.setGradeName(rSet.getString("gradeName"));//给年级对象赋值 stu.setStudentName(rSet.getString("studentName"));//给学生对象赋值 stu.setGradeID(grade); // stu.setStudentNo(rSet.getInt("StudentNo")); // SimpleDateFormat sdFormat=new SimpleDateFormat("yyyy-MM-dd"); // stu.setBornDate(sdFormat.parse(rSet.getString("bornDate"))); stus.add(stu);//将学生对象添加到对象集合中 } }
    //回收释放资源 closeResouse(); return stus; } }
为了简单明洁,部分属性不进行值查询
复制代码

  二、service层:

    1.service接口:

复制代码
package com.myschool.service;

import java.util.List;

import com.myschool.entity.Student;

public interface IService {
    
    public List<Student> Search() throws Exception;
}
复制代码

    2.service接口实现类:

复制代码
package com.mychool.service.Impl;

import java.util.List;

import com.mychool.dao.impl.IStudentDaoImpl;
import com.myschool.dao.IStudentDao;
import com.myschool.entity.Student;
import com.myschool.service.IService;

public class IServiceImpl implements IService{

    IStudentDao isd=new IStudentDaoImpl();
    @Override
    public List<Student> Search() throws Exception {
        return isd.Search();
    }
    
}
复制代码

  三、UI层:

    

复制代码
package com.myschool.ui;

import java.util.List;

import com.mychool.service.Impl.IServiceImpl;
import com.myschool.entity.Student;
import com.myschool.service.IService;

public class test {    
    public static void main(String[] args) throws Exception {
     //创建service接口实现类的对象 IService isv=new IServiceImpl(); List<Student> lsList=isv.Search();
for (Student student : lsList) { System.out.println(student.getStudentName()+"\t"+student.getGradeID().getGradeName()); } } }
复制代码

    DAO实现类写法:

 

 

package com.myschool.dao.impl;

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

import com.myschool.dao.BaseDao;
import com.myschool.dao.IGradeDao;
import com.myschool.entity.Grade;
import com.myschool.entity.Student;

public class IGradeDaoImpl extends BaseDao implements IGradeDao{

    @Override
    public Grade getStudentByGrade(String gradeName) throws Exception {
        Grade grade=new Grade();
        String sql="SELECT * FROM Student,Grade WHERE Student.GradeId=Grade.GradeId AND GradeName=?";
        ResultSet rs = executeQuery(sql, gradeName);
        if(rs!=null){
            
            while (rs.next()) {
                
                //获取年级信息
                grade.setGradeName(rs.getString("gradeName"));
                //获取学生信息
                Student student=new Student();
                student.setGradeId(rs.getInt("gradeId"));
                student.setStudentName(rs.getString("StudentName"));
                student.setStudentNo(rs.getInt("studentNo"));
                //将查询出来的学生信息添加到集合当中
                grade.getStulist().add(student);
            }
            
        }
        return grade;
    }

}
 
 
posted on 2019-05-29 17:27  神剑战王  阅读(450)  评论(0编辑  收藏  举报