📄 studydao.java
字号:
package com.hb.studentmanager.date;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import com.hb.studentmanager.connection.ConnectionDateBase;
public class StudyDAO {
ConnectionDateBase cdb=new ConnectionDateBase();
Connection con=cdb.getDateBaseConnection();
PreparedStatement ps=null;
ResultSet rs=null;
/**
* 初始化页面
* @return
*/
public List FirstSelect(){
List list=new ArrayList();
try {
String sql="select * from study_stu order by stu_id";
ps=con.prepareStatement(sql);
rs=ps.executeQuery();
while(rs.next()){
StudyDTO dto=new StudyDTO();
dto.setStu_id(rs.getString("stu_id"));
dto.setStu_name(rs.getString("stu_name"));
dto.setStu_sta(rs.getString("stu_sta"));
dto.setStu_men(rs.getString("stu_men"));
dto.setStu_week(rs.getString("stu_week"));
dto.setStu_month(rs.getString("stu_month"));
dto.setStu_year(rs.getString("stu_year"));
list.add(dto);
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return list;
}
/**
* 添加学习记录
* @param dto
*/
public void AddStudy(StudyDTO dto){
try {
String sql="insert into study (stu_sta,stu_men,stu_week,stu_id,stu_month,stu_year) values(?,?,?,?,?,?)";
ps=con.prepareStatement(sql);
ps.setString(1, dto.getStu_sta());
ps.setString(2, dto.getStu_men());
ps.setString(3, dto.getStu_week());
ps.setString(4, dto.getStu_id());
ps.setString(5, dto.getStu_month());
ps.setString(6, dto.getStu_year());
ps.executeQuery();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
/**
* 更新study表中数据
* @param dto
*/
public void updateStudy(StudyDTO dto){
try {
String sql="update study set stu_sta=?,stu_men=? where stu_id=? and stu_week=? and stu_month=? and stu_year=?";
ps=con.prepareStatement(sql);
ps.setString(1, dto.getStu_sta());
ps.setString(2, dto.getStu_men());
ps.setString(3, dto.getStu_id());
ps.setString(4, dto.getStu_week());
ps.setString(5, dto.getStu_month());
ps.setString(6, dto.getStu_year());
ps.executeUpdate();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
/**
* 删除学习表中数据
* @param stuid
* @param week
* @param month
* @param year
*/
public void deleteStudy(String stuid,String week,String month,String year){
try {
String sql="delete from study where stu_id=? and stu_week=? and stu_month=? and stu_year=?";
ps=con.prepareStatement(sql);
ps.setString(1, stuid);
ps.setString(2, week);
ps.setString(3, month);
ps.setString(4, year);
ps.executeUpdate();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
/**
* 根据学号得到study表中的所有数据再传到StudyAction中
* @param stuid
* @return
*/
public List getStudy(String stuid,String week,String month,String year){
List list =new ArrayList();
try {
String sql="select * from study where stu_id=? and stu_week=? and stu_month=? and stu_year=?";
ps=con.prepareStatement(sql);
ps.setString(1, stuid);
ps.setString(2, week);
ps.setString(3, month);
ps.setString(4, year);
rs=ps.executeQuery();
while(rs.next()){
StudyDTO dto=new StudyDTO();
dto.setStu_sta(rs.getString("stu_sta"));
dto.setStu_men(rs.getString("stu_men"));
dto.setStu_week(rs.getString("stu_week"));
dto.setStu_id(rs.getString("stu_id"));
dto.setStu_month(rs.getString("stu_month"));
dto.setStu_year(rs.getString("stu_year"));
list.add(dto);
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return list;
}
/**
* 查询学生表得到学生学号
*将学号传给StudyAction
*/
public List getStudentId(){
List list =new ArrayList();
try {
String sql="select stu_id from student";
ps=con.prepareStatement(sql);
rs=ps.executeQuery();
while(rs.next()){
StudyDTO dto=new StudyDTO();
dto.setStu_id(rs.getString("stu_id"));
list.add(dto);
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return list;
}
/**
* 得到4个学生表里面的主键
* @return
*/
public List getStudyPk(StudyDTO dto){
String stuId=dto.getStu_id();
String stuWeek=dto.getStu_week();
String sutMonth=dto.getStu_month();
String sutYear=dto.getStu_year();
List list =new ArrayList();
try {
String sql="select *from study where stu_id=? and stu_week=? and stu_month=? and stu_year=?";
ps=con.prepareStatement(sql);
ps.setString(1, stuId);
ps.setString(2, stuWeek);
ps.setString(3, sutMonth);
ps.setString(4, sutYear);
rs=ps.executeQuery();
while(rs.next()){
dto=new StudyDTO();
dto.setStu_id(rs.getString("stu_id"));
dto.setStu_week(rs.getString("stu_week"));
dto.setStu_month(rs.getString("stu_month"));
dto.setStu_year(rs.getString("stu_year"));
list.add(dto);
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return list;
}
/**
* 联合查询
* @param stuid
* @param name
* @param week
* @param month
* @param year
* @param xinli
* @param xuexi
* @return
*/
public List selectView(String stuid,String name,String xuexi,String xinli,String week,String month,String year){
List list = new ArrayList();
try {
if(name.equals("")&&xinli.equals("")&&xuexi.equals("")){
String sql="select * from study_stu where stu_id like '%"+stuid+"%' and stu_week like '%"+week+"%' and stu_month like '%"+month+"%' and stu_year like '%"+year+"%'";
ps=con.prepareStatement(sql);
}
else{
if(name.equals("")&&xinli.equals("")&&!xuexi.equals("")){
String sql="select * from study_stu where stu_id like '%"+stuid+"%' and stu_sta like '%"+xuexi+"%' and stu_week like '%"+week+"%' and stu_month like '%"+month+"%' and stu_year like '%"+year+"%' ";
ps=con.prepareStatement(sql);
}
else{
if(name.equals("")&&!xinli.equals("")&&xuexi.equals("")){
String sql="select * from study_stu where stu_id like '%"+stuid+"%' and stu_men like '%"+xinli+"%' and stu_week like '%"+week+"%' and stu_month like '%"+month+"%' and stu_year like '%"+year+"%'";
ps=con.prepareStatement(sql);
}
else{
if(!name.equals("")&&xinli.equals("")&&xuexi.equals("")){
String sql="select * from study_stu where stu_id like '%"+stuid+"%' and stu_name like '%"+name+"%' and stu_week like '%"+week+"%' and stu_month like '%"+month+"%' and stu_year like '%"+year+"%'";
ps=con.prepareStatement(sql);
}
else{
if(name.equals("")&&!xinli.equals("")&&!xuexi.equals("")){
String sql="select * from study_stu where stu_id like '%"+stuid+"%' and stu_sta like '%"+xuexi+"%' and stu_men like '%"+xinli+"%' and stu_week like '%"+week+"%' and stu_month like '%"+month+"%' and stu_year like '%"+year+"%'";
ps=con.prepareStatement(sql);
}
else{
if(!name.equals("")&&xinli.equals("")&&!xuexi.equals("")){
String sql="select * from study_stu where stu_id like '%"+stuid+"%' and stu_name like '%"+name+"%' and stu_sta like '%"+xuexi+"%' and stu_week like '%"+week+"%' and stu_month like '%"+month+"%' and stu_year like '%"+year+"%'";
ps=con.prepareStatement(sql);
}
else{
if(!name.equals("")&&!xinli.equals("")&&xuexi.equals("")){
String sql="select * from study_stu where stu_id like '%"+stuid+"%' and stu_name like '%"+name+"%' and stu_men like '%"+xinli+"%' and stu_week like '%"+week+"%' and stu_month like '%"+month+"%' and stu_year like '%"+year+"%'";
ps=con.prepareStatement(sql);
}
else{
String sql="select * from study_stu where stu_id like '%"+stuid+"%' and stu_name like '%"+name+"%' and stu_sta like '%"+xuexi+"%' and stu_men like '%"+xinli+"%' and stu_week like '%"+week+"%' and stu_month like '%"+month+"%' and stu_year like '%"+year+"%'";
ps=con.prepareStatement(sql);
}
}
}
}
}
}
}
rs=ps.executeQuery();
while(rs.next()){
StudyDTO dto=new StudyDTO();
dto.setStu_sta(rs.getString("stu_sta"));
dto.setStu_men(rs.getString("stu_men"));
dto.setStu_week(rs.getString("stu_week"));
dto.setStu_id(rs.getString("stu_id"));
dto.setStu_month(rs.getString("stu_month"));
dto.setStu_year(rs.getString("stu_year"));
dto.setStu_name(rs.getString("stu_name"));
list.add(dto);
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return list;
}
}
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -