查询所有学生记录，包含年级名称
一、BaseDao工具类
package BaseDao;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class BaseDao {
/**
* 连接数据库
*/
private static final String DRIVER="com.mysql.jdbc.Driver";
private static final String URL="jdbc:mysql://localhost:3306/myschool? useUniCode=true&characterEncoding=utf-8";
private static final String USERNAME="root";
private static final String PASSWORD="123";
Connection connection;
PreparedStatement statement;
ResultSet rs;
/**
* 获取连接
* @throws Exception
*/
public Connection getConnection(){
try {
Class.forName(DRIVER);
if (connection==null||connection.isClosed()) {
connection= DriverManager.getConnection(URL, USERNAME, PASSWORD);
}
} catch (ClassNotFoundException e) {
e.printStackTrace();
}catch (SQLException e) {
e.printStackTrace();
}
return connection;
}
/**
* 增删改
* @throws Exception
*/
public int executeUpdate(String sql,Object...obj) throws Exception{
//获取连接
getConnection();
//获取prepareStatement对象
statement= connection.prepareStatement(sql);
//循环加载参数
for (int i = 1; i <=obj.length; i++) {
statement.setObject(i,obj[i-1]);
}
//执行SQL
int count=statement.executeUpdate();
return count;
}
/**
* 查询的方法
* @throws Exception
*/
public ResultSet executeQuery(String sql,Object...obj) throws Exception{
//获取连接
getConnection();
//获取
statement= connection.prepareStatement(sql);
//循环加载参数
for (int i = 1; i <=obj.length; i++) {
statement.setObject(i, obj[i-1]);
}
//执行SQL
rs= statement.executeQuery();
return rs;
}
/**
* 回收资源
* @throws Exception
*/
public void clossResource() throws Exception{
if (rs!=null) {
rs.close();
}if (statement!=null) {
statement.close();
}if (connection!=null) {
connection.close();
connection=null;
}
}
}
二、Students实体类
package com.students;
public class students {
private int StudentNo;
private String LoginPwd;
private String studentName;
private int sex;
private String Phone;
private String Address;
private String bornDate;
private String Email;
private String IdentityCard;
private int Gradeid;
public int getGradeid() {
return Gradeid;
}
public void setGradeid(int gradeid) {
Gradeid = gradeid;
}
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) {
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) {
this.sex = sex;
}
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 String getBornDate() {
return bornDate;
}
public void setBornDate(String bornDate) {
this.bornDate = bornDate;
}
public String getEmail() {
return Email;
}
public void setEmail(String email) {
Email = email;
}
public String getIdentityCard() {
return IdentityCard;
}
public void setIdentityCard(String identityCard) {
IdentityCard = identityCard;
}
}
三、StudentDao接口
package com.student.dao;
import java.util.List;
import com.students.students;
public interface StudentDao {
/*
* 查询得方法
*/
List<students> stu() throws Exception;
}
四、StudentDaoImpl接口实现类
package com.student.dao.impl;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;
import BaseDao.BaseDao;
import com.student.dao.StudentDao;
import com.students.Grade;
import com.students.students;
public class StudentDaoImpl extends BaseDao implements StudentDao{
@Override
public List<students> stu() throws Exception {
List<students> students=new ArrayList<students>();
//准备SQL
String sql="SELECT student.*,grade.* FROM student,grade WHERE student.GradeId=grade.GradeID";
ResultSet rs = executeQuery(sql);
if (rs!=null) {
while (rs.next()) {
students stu=new students();
stu.setStudentNo(rs.getInt("StudentNo"));
stu.setLoginPwd(rs.getString("LoginPwd"));
stu.setStudentName(rs.getString("StudentName"));
stu.setSex(rs.getInt("Sex"));
stu.setGradeid(rs.getInt("Gradeid"));
stu.setPhone(rs.getString("Phone"));
stu.setAddress(rs.getString("Address"));
stu.setBornDate(rs.getString("BornDate"));
stu.setEmail(rs.getString("Email"));
stu.setIdentityCard(rs.getString("IdentityCard"));
Grade grade = new Grade();
grade.setGradeName(rs.getString("GradeName"));
stu.setGrade(grade);
students.add(stu);
}
}
clossResource();
return students;
}
}
五、Test实现类
package com.student.dao.impl;
import java.util.List;
import com.student.dao.StudentDao;
import com.students.students;
public class Test {
public static void main(String[] args) throws Exception {
StudentDao studentDao=new StudentDaoImpl();
System.out.println("学号 密码 姓名 性别 年级编号 地址 手机号 邮箱 日期 身份证号 年纪名称");
List<students> stu=studentDao.stu();
for (students students : stu) {
System.out.println(students.getStudentNo()+" "+students.getLoginPwd()+" "+students.getStudentName()+" "+students.getSex()+" "+students.getGradeid()+" "+students.getAddress()+" "+students.getPhone()+" "+students.getEmail()+" "+students.getBornDate()+" "+students.getIdentityCard()+" "+students.getGrade().getGradeName());
}
}
}
查询s1年级下的学生记录
一、grade类：
private int gradeid; //年级编号
private String gradename; //年级名称
public List<student> list; //学生表集合
public List<student> getList() {
return list;
}
public void setList(List<student> list) {
this.list = list;
}
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(){
}
public grade getgrade(String gradename) throws Exception {
List<student> list1=new ArrayList<student>();
String sql="select s.*,g.GradeName from student as s,grade as g where s.GradeId=g.GradeId and g.gradename=?";
Object[] obj={gradename};
rs=executeQuery(sql, obj);
grade grade=null;
if (rs!=null) {
while(rs.next()){
grade=new grade();
student student=new student();
student.setStudentno(rs.getInt("studentno"));
student.setStudentname(rs.getString("studentname"));
list1.add(student);
grade.setGradename(rs.getString("gradename"));
}
grade.setList(list1);
}
return grade;
}
getstudent();
System.out.println("=================================");
grade grade=dao.getgrade("S1");
for (student stu : grade.getList()) {
System.out.println(stu.getStudentno()+" "+stu.getStudentname()+" "+grade.getGradename());
}