package dao;
import java.sql.*;
/*
* 数据库连接关闭工具类*/
public class BaseDao {
private static final String DRIVER="com.mysql.jdbc.Driver";
private static final String URL="jdbc:mysql:///myschool";
private static final String USER="root";
private static final String PWD="ok";
protected Connection con;
protected PreparedStatement ps;
protected ResultSet rs;
/*
* 获取数据库连接方法
* */
private void getConnection(){
try {
Class.forName(DRIVER);
con=DriverManager.getConnection(URL,USER,PWD);
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
/*
* 关闭资源（逆序）
* */
protected void closeAll(){
if(null!=rs)
try {
rs.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
if(null!=ps)
try {
ps.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
if(null!=con)
try {
con.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
/*
* 统一执行增，删，改方法
* */
protected int executeUpdate(String sql,Object[] params){
getConnection();//获取连接
try {
ps=con.prepareStatement(sql);//预编译语句
if(null!=params){//若存在占位符数据，则依次设置参数
for(int i=0;i<params.length;i++){
ps.setObject(i+1, params[i]);
}
}
return ps.executeUpdate();//返回结果数
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
closeAll();//关闭资源方法
}
return -1;
}
/*
* 执行统一查询方法
* */
protected void executeQuery(String sql,Object[] params){
getConnection();
try {
ps=con.prepareStatement(sql);
if (null!=params) {
for (int i = 0; i < params.length; i++) {
ps.setObject(i+1, params[i]);
}
}
rs=ps.executeQuery();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
与数据库操作的具体方法
package dao;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import entity.StudentInfo;
public class StudentInfoDao extends BaseDao {
//统计学生人数
public List<StudentInfo> queryAllStudent(){//这个统计学生人数可以改进。 直接用sql域统计。因为后面的几个方法直接用了这个数组，这里就不改了
List<StudentInfo>list=new ArrayList<StudentInfo>();
try {
String sql="select * from student";
executeQuery(sql, null);//这里调用的是BaseDao里的查询方法
while(rs.next()){
StudentInfo student=new StudentInfo(rs.getString("StudentNo"),rs.getString("LoginPwd"),rs.getString("StudentName"),rs.getString("Sex"),rs.getInt("GradeId"),rs.getString("Phone"),rs.getString("Address"),rs.getDate("BornDate"),rs.getString("Email"));
list.add(student);
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}closeAll();//查询的要在这里关闭，而增删改关闭的方法都封装在一起了
return list;
}
//查看学生名单，直接用了上一个数组的的方法
//按学号修改查询学生姓名，也是用了数组的方法
//按姓名查询学生信息也是，用了第一个数组的方法
//根据学生学号修改学生出生日期方法
public int modfiy(String studentNo, String date ){
String sql="UPDATE student SET BornDate =? WHERE StudentNo =?";
Object[] params={date,studentNo};// 这里的形参的顺序要与sql查询语句里的顺序一致
return executeUpdate(sql,params);//这里调用的是增删改统一方法
}
//根据学生学号删除学生信息方法
public int delete(String studentNo){
String sql="DELETE FROM `student` WHERE `StudentNo` = ?";
Object[] params={studentNo};
return executeUpdate(sql,params);
}
//增加学号和年级方法
public int add(String studentNo,int grade){
String sql="INSERT INTO `student` (`StudentNo`, `GradeId`) VALUES (?, ?)";
Object[] params={studentNo,grade};
return executeUpdate(sql,params);
}
//登录方法
public boolean logindao(String StudentNo,String Pwd){
String sql="SELECT * FROM student WHERE StudentNo=? AND LoginPwd=?";
Object[] params={StudentNo,Pwd};//这里是select 方法记住是用executeQuery方法，因为每个结果集不一样，所以无法封装。
executeQuery(sql,params);
try {
while(rs.next()){
return true;
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}closeAll();
return false;
}
}
package entity;
import java.util.Date;
public class StudentInfo {
private String sdudentNo;
private String loginPwd;
private String studentName;
private String sex;
private Integer gradeId;
private String phone;
private String address;
private Date bornDate;
private String email;
public String getSdudentNo() {
return sdudentNo;
}
public void setSdudentNo(String sdudentNo) {
this.sdudentNo = sdudentNo;
}
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 String getSex() {
return sex;
}
public void setSex(String sex) {
this.sex = sex;
}
public Integer getGradeId() {
return gradeId;
}
public void setGradeId(Integer gradeId) {
this.gradeId = gradeId;
}
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 StudentInfo(String sdudentNo, String loginPwd, String studentName,
String sex,Integer gradeId, String phone, String address,
Date bornDate, String email) {
super();
this.sdudentNo = sdudentNo;
this.loginPwd = loginPwd;
this.studentName = studentName;
this.sex = sex;
this.gradeId = gradeId;
this.phone = phone;
this.address = address;
this.bornDate = bornDate;
this.email = email;
}
}
package manager;
import java.util.ArrayList;
import java.util.List;
import java.util.Scanner;
import dao.StudentInfoDao;
import entity.StudentInfo;
public class StudentManager {
Scanner input = new Scanner(System.in);
StudentInfoDao info = new StudentInfoDao();
//启动方法（三次验证），在test里面就是调用的这个方法。这个是整体。包含了密码验证，菜单显示选择
public void start(){
for(int i=2;i>=0;i--){
if(login()){
break;
}else{
if (i==0) {
System.out.println("三次均登录失败，谢谢使用");
return ;//直接退出整个程序
}
System.out.println("用户名或密码输入错误，你还有"+i+"次机会");
}
}
while(show());
}
//系统登录方法，实现登录验证功能
private boolean login(){
System.out.println("请输入登录学号");
String number=input.next();
System.out.println("请输入密码");
String pwd=input.next();
if(info.logindao(number, pwd)){
return true;
}
return false;
}
//菜单switch方法
public boolean show() {
System.out.println("============请选择操作键==============");
System.out.println("1.统计学生人数");
System.out.println("2.查看学生名单");
System.out.println("3.按学号查询学生姓名");
System.out.println("4.按姓名查询学生信息");
System.out.println("5.修改学生出生日期");
System.out.println("6.删除学生记录");
System.out.println("7.新增年级记录");
System.out.println("0.退出");
System.out.println("==================================");
System.out.println("请选择");
int choiceId = input.nextInt();
List<StudentInfo> list = new ArrayList<StudentInfo>();
switch (choiceId) {
// 查询学生人数
case 1:
list = info.queryAllStudent();
System.out.print("学生人数共有：");
System.out.println(list.size() + "人");
break;
// 查询学生细信息
case 2:
list = info.queryAllStudent();
System.out
.println("StudentNo\tLoginPwd\tStudentName\tSex\tGrade\tPhone\tAddress\tBorndate\tEmail");
for (int i = 0; i < list.size(); i++) {
System.out.println(list.get(i).getSdudentNo() + "\t"
+ list.get(i).getLoginPwd() + "\t"
+ list.get(i).getStudentName() + "\t"
+ list.get(i).getSex() + "\t"
+ list.get(i).getGradeId() + "\t"
+ list.get(i).getPhone() + "\t"
+ list.get(i).getAddress() + "\t"
+ list.get(i).getBornDate() + "\t"
+ list.get(i).getEmail());
}
break;
// 按学号查询学生姓名
case 3:
boolean flag=false;
list = info.queryAllStudent();
System.out.println("请输入您要查询的学号");
String StudenId = input.next();
for (int i = 0; i < list.size(); i++) {
if (list.get(i).getSdudentNo().equalsIgnoreCase(StudenId)) {
System.out.print("学号为：" + StudenId + "的学生姓名为：");
System.out.println(list.get(i).getStudentName());
flag=true;
break;
}
}
if (flag==false) {
System.out.println("学号为：" + StudenId + "的学生姓名输入有误请重新输入：");
}
break;
// 按姓名查询学生信息
case 4:
flag=false;
list = info.queryAllStudent();
System.out.println("请输入您要查询的学生姓名（支持模糊查询）");
String name = input.next();
for (int i = 0; i < list.size(); i++) {//这个必须要判断名字不为空，否则equqlas匹配不到会报空指针。
if (list.get(i).getStudentName()!=null&&list.get(i).getStudentName().indexOf(name)!=-1) {//这个indexof(name)实现模糊查询
System.out.println("学号\t性别\t地址\t学生的出生日期");
System.out.println(list.get(i).getSdudentNo() + "\t"
+ list.get(i).getSex() + "\t"
+ list.get(i).getAddress() + "\t"
+ list.get(i).getBornDate());
flag=true;
break;
}
}
if (flag==false) {
System.out.println("不存在学生姓名为："+name+"的学生");
}
break;
// 修改学生出生日期
case 5:
System.out.println("请输入您要修改的学生学号");
String studentNo = input.next();
System.out.println("请输入你要修改的日期为：");
String date = input.next();
if (info.modfiy(studentNo, date) > 0) {
System.out.println("修改成功");
} else {
System.out.println("修改失败");
}
break;
// 删除学生信息
case 6:
System.out.println("请输入您要删除的学生学号");
studentNo = input.next();
if (info.delete(studentNo) > 0) {
System.out.println("删除学号为：" + studentNo + "的学生信息成功");
} else {
System.out.println("删除学号为：" + studentNo + "的学生信息失败");
}
break;
case 7:
System.out.println("请输入你要增加的学号");
studentNo = input.next();
System.out.println("请输入你要增加年级");
int grade=input.nextInt();
if(info.add(studentNo, grade)>0){
System.out.println("新增学号年级成功");
}else{
System.out.println("新增年失败");
}
break;
case 0: //输入0退出
return false;
}
return true;//再启动方法的while（）里调用，只要不输入0就一直循环菜单
}
}
测试方法
package Test;
import manager.StudentManager;
public class StudentTest {
public static void main(String[] args) {
StudentManager stu=new StudentManager();
stu.start();
}
}
扫一扫
346
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
