试题
一，查询所有学生记录，包含年级名称
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 = "root"; private Connection conn; private PreparedStatement statement; private ResultSet rs; /** * 获取连接的方法 */ public Connection getConnection() { try { Class.forName(DRIVER); //判断连接对象是否为空或者当前连接对象是否是isClosed()已经关闭的 if(conn==null||conn.isClosed()){ conn = DriverManager.getConnection(URL, USERNAME, PASSWORD); } } catch (ClassNotFoundException e) { e.printStackTrace(); } catch (SQLException e) { e.printStackTrace(); } return conn; } /** * 增删改 * * @throws Exception */ public int executeUpdate(String sql, Object... obj) throws Exception { // 获取连接 getConnection(); // 获取PreparedStatement对象 statement = conn.prepareStatement(sql); // 循环加载参数 for (int i = 1; i <= obj.length; i++) { statement.setObject(i, obj[i-1]); } // 执行SQL int count = statement.executeUpdate(); return count; } /** * 查询 * @throws SQLException */ public ResultSet executeQuery(String sql, Object... obj) throws Exception { // 获取连接 getConnection(); // 获取PreparedStatement对象 statement = conn.prepareStatement(sql); // 循环加载参数 for (int i = 1; i <= obj.length; i++) { statement.setObject(i, obj[i-1]); } rs = statement.executeQuery(); return rs; } /** * 关闭连接 * @throws Exception */ public void closeResource() throws Exception { if(rs!=null){ rs.close(); } if(statement!=null){ statement.close(); } if(conn!=null){ //关闭连接 conn.close(); } } }
public interface IStudentDao {
/**
* 查询所有学生信息，包含年级名称
* 一个学生对应一个年级
*
*/
public List<Student> getAllStudent() throws Exception;
}
public class IStudentDaoImpl extends BaseDao implements IStudentDao{
@Override
public List<Student> getAllStudent() throws Exception {
List<Student> list=new ArrayList<Student>();
String sql="SELECT * FROM Student,Grade WHERE Student.GradeId=Grade.GradeId";
ResultSet rs = executeQuery(sql);
if(rs!=null){
while(rs.next()){
//获取学生信息
Student student=new Student();
student.setGradeId(rs.getInt("gradeId"));
student.setStudentName(rs.getString("StudentName"));
student.setStudentNo(rs.getInt("studentNo"));
//获取的就是当前学生的年级信息
Grade grade=new Grade();
grade.setGradeName(rs.getString("gradeName"));
student.setGrade(grade);
list.add(student);
}
}
closeResource();
return list;
}
}
public class Student {
private int studentNo;
private String studentName;
private int 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) {
this.studentNo = studentNo;
}
public String getStudentName() {
return studentName;
}
public void setStudentName(String studentName) {
this.studentName = studentName;
}
public int getGradeId() {
return gradeId;
}
public void setGradeId(int gradeId) {
this.gradeId = gradeId;
}
public Student(int studentNo, String studentName, int gradeId) {
this.studentNo = studentNo;
this.studentName = studentName;
this.gradeId = gradeId;
}
public Student() {
}
}
public interface IStudentService {
/**
* 查询所有学生信息，包含年级名称
*
*
*/
public List<Student> getAllStudent() throws Exception;
}
public class IStudentServiceImpl implements IStudentService{
//创建Dao层对象
IStudentDao studentDao=new IStudentDaoImpl();
@Override
public List<Student> getAllStudent() throws Exception {
return studentDao.getAllStudent();
}
}
public class MyMian {
//创建Service对象
static IStudentService studentService=new IStudentServiceImpl();
static IGradeService gradeService=new IGradeServiceImpl();
public static void main(String[] args) throws Exception {
System.out.println("=================查询所有学生信息========================");
List<Student> allStudent = studentService.getAllStudent();
for (Student student : allStudent) {
System.out.println("学生姓名："+student.getStudentName()+"\t学生年级："+student.getGradeId()+"\t年级名称："+student.getGrade().getGradeName());
}
}
二，查询S1年级下的学生记录
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 = "root";
private Connection conn;
private PreparedStatement statement;
private ResultSet rs;
/**
* 获取连接的方法
*/
public Connection getConnection() {
try {
Class.forName(DRIVER);
//判断连接对象是否为空或者当前连接对象是否是isClosed()已经关闭的
if(conn==null||conn.isClosed()){
conn = DriverManager.getConnection(URL, USERNAME, PASSWORD);
}
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}
return conn;
}
/**
* 增删改
*
* @throws Exception
*/
public int executeUpdate(String sql, Object... obj) throws Exception {
// 获取连接
getConnection();
// 获取PreparedStatement对象
statement = conn.prepareStatement(sql);
// 循环加载参数
for (int i = 1; i <= obj.length; i++) {
statement.setObject(i, obj[i-1]);
}
// 执行SQL
int count = statement.executeUpdate();
return count;
}
/**
* 查询
* @throws SQLException
*/
public ResultSet executeQuery(String sql, Object... obj) throws Exception {
// 获取连接
getConnection();
// 获取PreparedStatement对象
statement = conn.prepareStatement(sql);
// 循环加载参数
for (int i = 1; i <= obj.length; i++) {
statement.setObject(i, obj[i-1]);
}
rs = statement.executeQuery();
return rs;
}
/**
* 关闭连接
* @throws Exception
*/
public void closeResource() throws Exception {
if(rs!=null){
rs.close();
}
if(statement!=null){
statement.close();
}
if(conn!=null){
//关闭连接
conn.close();
}
}
}
public interface IGradeDao {
/**
* 查询年级下的所有学生信息
* 一个年级有多名学生
*/
public Grade getStudentByGrade(String gradeName) throws Exception;
}
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;
}
}
public class Grade {
private int gradeId;
private String gradeName;
private List<Student> stulist=new ArrayList<Student>();
public List<Student> getStulist() {
return stulist;
}
public void setStulist(List<Student> stulist) {
this.stulist = stulist;
}
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) {
this.gradeId = gradeId;
this.gradeName = gradeName;
}
public Grade() {
}
}
public interface IGradeService {
/**
* 查询年级下的所有学生信息
* 一个年级有多名学生
*/
public Grade getStudentByGrade(String gradeName) throws Exception;
}
public class IGradeServiceImpl implements IGradeService{
IGradeDao gradeDao=new IGradeDaoImpl();
@Override
public Grade getStudentByGrade(String gradeName) throws Exception {
// TODO Auto-generated method stub
return gradeDao.getStudentByGrade(gradeName);
}
}
public class MyMian {
//创建Service对象
static IStudentService studentService=new IStudentServiceImpl();
static IGradeService gradeService=new IGradeServiceImpl();
public static void main(String[] args) throws Exception {
Grade grade = gradeService.getStudentByGrade("S1");
for (Student stu : grade.getStulist()) {
System.out.println(stu.getStudentName());
}
}
}
