上一篇已经学完了JDBC,这篇讲一下基本的CURD实现简单的业务。
基本准备
- 数据表两张分别是user表和学生表student1结构如下
分别对应,用户id,用户名,密码,错误次数,状态(是否锁定)
学生表,分别对应,学号,姓名,性别,年龄,地址
mysql的jar包
数据插入
SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for student1
-- ----------------------------
DROP TABLE IF EXISTS `student1`;
CREATE TABLE `student1` (
`sid` int(11) NOT NULL AUTO_INCREMENT,
`sname` varchar(8) NOT NULL,
`sgender` char(255) NOT NULL,
`sage` int(3) DEFAULT NULL,
`saddress` varchar(255) DEFAULT NULL,
PRIMARY KEY (`sid`)
) ENGINE=InnoDB AUTO_INCREMENT=37 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of student1
-- ----------------------------
INSERT INTO `student1` VALUES ('1', '李青', '男', '48', '德玛西亚');
INSERT INTO `student1` VALUES ('2', '艾瑞莉娅', '女', '26', '艾欧尼亚');
INSERT INTO `student1` VALUES ('3', '锐雯', '女', '36', '艾欧尼亚');
INSERT INTO `student1` VALUES ('4', '凯特琳', '女', '28', '皮尔特沃夫');
INSERT INTO `student1` VALUES ('5', '蔚', '女', '28', '皮尔特沃夫');
INSERT INTO `student1` VALUES ('6', '金克丝', '女', '28', '皮尔特沃夫');
INSERT INTO `student1` VALUES ('7', '普朗克', '男', '45', '比尔吉沃特');
INSERT INTO `student1` VALUES ('8', '好运姐', '女', '36', '比尔吉沃特');
INSERT INTO `student1` VALUES ('9', '锤石', '男', '96', '暗影岛');
INSERT INTO `student1` VALUES ('10', '佛耶戈', '男', '52', '暗影岛');
INSERT INTO `student1` VALUES ('11', '雷欧娜', '女', '36', '巨神峰');
INSERT INTO `student1` VALUES ('12', '阿兹尔', '男', '108', '恕瑞玛');
INSERT INTO `student1` VALUES ('13', '内瑟斯', '男', '118', '恕瑞玛');
INSERT INTO `student1` VALUES ('14', '雷克顿', '男', '118', '恕瑞玛');
INSERT INTO `student1` VALUES ('15', '艾希', '女', '38', '弗雷尔卓德');
INSERT INTO `student1` VALUES ('16', '瑟庄妮', '女', '45', '弗雷尔卓德');
INSERT INTO `student1` VALUES ('17', '丽桑卓', '女', '82', '弗雷尔卓德');
INSERT INTO `student1` VALUES ('18', '亚索', '男', '35', '艾欧尼亚');
INSERT INTO `student1` VALUES ('19', '永恩', '男', '36', '艾欧尼亚');
INSERT INTO `student1` VALUES ('20', '德莱厄斯', '男', '36', '诺克萨斯');
INSERT INTO `student1` VALUES ('21', '卡特琳娜', '女', '32', '诺克萨斯');
INSERT INTO `student1` VALUES ('22', '德莱文', '男', '30', '诺克萨斯');
INSERT INTO `student1` VALUES ('23', '斯维因', '男', '50', '诺克萨斯');
INSERT INTO `student1` VALUES ('24', '杰斯', '男', '40', '皮尔特沃夫');
INSERT INTO `student1` VALUES ('25', '维克托', '男', '41', '皮尔特沃夫');
INSERT INTO `student1` VALUES ('26', '沃里克', '男', '45', '祖安');
INSERT INTO `student1` VALUES ('27', '蒙多', '男', '40', '祖安');
INSERT INTO `student1` VALUES ('28', '凯尔', '女', '139', '烈焰阳刚');
INSERT INTO `student1` VALUES ('29', '莫甘娜', '女', '139', '烈焰阳刚');
INSERT INTO `student1` VALUES ('30', '卡蜜尔', '女', '30', '皮尔特沃夫');
INSERT INTO `student1` VALUES ('31', '菲奥娜', '女', '35', '皮尔特沃夫');
INSERT INTO `student1` VALUES ('32', '拉克丝', '女', '30', '德玛西亚');
INSERT INTO `student1` VALUES ('34', '乐芙兰', '女', '32', '黑色玫瑰');
INSERT INTO `student1` VALUES ('35', '亚托克斯', '男', '108', '恕瑞玛');
INSERT INTO `student1` VALUES ('36', '沃利贝尔', '男', '89', '无畏先锋');
SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for user
-- ----------------------------
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user` (
`uid` int(11) NOT NULL AUTO_INCREMENT,
`username` varchar(16) NOT NULL,
`password` varchar(16) NOT NULL,
`wrong_times` int(1) NOT NULL,
`status` int(1) NOT NULL DEFAULT '0',
PRIMARY KEY (`uid`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of user
-- ----------------------------
INSERT INTO `user` VALUES ('1', '奥特曼', '111111', '0', '0');
需求分析
- 要求实现模拟用户登陆
如果用户名不存在,则提示用户名不存在。密码错误则提示密码错误。
用户名输入正确密码输入正确,可以正常登陆系统。否则不允许登陆。
并且密码输错三次,对帐号进行锁定。再次登陆时提示已锁定。
只要未满三次错误,输入正确,则错误次数重置。 - 模拟学生管理,应包含以下功能
对数据表中的所有学生进行输出
可以根据关键字段模糊查询学生
根据学号,对学生信息进行修改
根据学号,对学生进行单条删除 - 系统功能
可以实现用户通过输入指令对所有功能进行操作
代码编写
目录层级如下:
entity包
Student
package com.lzl.StudentManage.entity;
import java.util.Date;
public class Student {
private Integer sid;
private String sname;
private String sgender;
private Integer sage;
private String saddress;
public Student(Integer sid, String sname, String sgender, Integer sage, String saddress) {
this.sid = sid;
this.sname = sname;
this.sgender = sgender;
this.sage = sage;
this.saddress = saddress;
}
public Student() {
}
public Integer getSid() {
return sid;
}
public void setSid(Integer sid) {
this.sid = sid;
}
public String getSname() {
return sname;
}
public void setSname(String sname) {
this.sname = sname;
}
public String getSgender() {
return sgender;
}
public void setSgender(String sgender) {
this.sgender = sgender;
}
public Integer getSage() {
return sage;
}
public void setSage(Integer sage) {
this.sage = sage;
}
public String getSaddress() {
return saddress;
}
public void setSaddress(String saddress) {
this.saddress = saddress;
}
@Override
public String toString() {
return "Student{" +
"sid=" + sid +
", sname='" + sname + '\'' +
", sgender='" + sgender + '\'' +
", sage=" + sage +
", saddress='" + saddress + '\'' +
'}';
}
}
User
package com.lzl.StudentManage.entity;
public class User {
private Integer uid;
private String username;
private String password;
private Integer wrong_times;
private Integer status;
public User(Integer uid, String username, String password, Integer wrong_times, Integer status) {
this.uid = uid;
this.username = username;
this.password = password;
this.wrong_times = wrong_times;
this.status = status;
}
public User() {
}
public Integer getUid() {
return uid;
}
public void setUid(Integer uid) {
this.uid = uid;
}
public String getUsername() {
return username;
}
public void setUsername(String username) {
this.username = username;
}
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
public Integer getWrong_times() {
return wrong_times;
}
public void setWrong_times(Integer wrong_times) {
this.wrong_times = wrong_times;
}
public Integer getStatus() {
return status;
}
public void setStatus(Integer status) {
this.status = status;
}
@Override
public String toString() {
return "User{" +
"uid=" + uid +
", username='" + username + '\'' +
", password='" + password + '\'' +
", wrong_times=" + wrong_times +
", status=" + status +
'}';
}
}
util包
DBUtil
package com.lzl.StudentManage.util;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
public class DBUtil {
private static final String driver = "com.mysql.jdbc.Driver";
private static final String url = "jdbc:mysql://localhost:3306/qianfeng";
private static final String user = "root";
private static final String password = "123456";
public static Connection getConnection() {
Connection con = null;
try {
Class.forName(driver);
con = DriverManager.getConnection(url, user, password);
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}
return con;
}
public static void closeAll(Connection conn, Statement state,ResultSet rs) {
try {
if (conn != null){
conn.close();
}
if (state != null){
state.close();
}
if(rs != null){
rs.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
public static int executeUpdate(String sql,ArrayList params){
//创建连接
Connection conn = null;
//创建平台
PreparedStatement ps = null;
try {
conn = getConnection();
//预编译sql语句
ps = conn.prepareStatement(sql);
//设置参数
for (int i = 0; i < params.size(); i++) {
ps.setObject(i + 1, params.get(i));
}
//返回受影响行数
return ps.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
} finally {
closeAll(conn, ps,null);
}
return -1;
}
}
StringUtil
package com.lzl.StudentManage.util;
/**
* 处理字符串工具类
*/
public class StringUtil {
/**
* 是null 或者 是 空字符串
*/
public static boolean isNull(Object obj) {
//将Object转为String
String str = (String)obj;
//返回true | false
return (str == null) || (str.length() == 0);
}
}
controller包
LoginController
package com.lzl.StudentManage.controller;
import com.lzl.StudentManage.entity.User;
import com.lzl.StudentManage.util.DBUtil;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;
import java.util.Scanner;
public class LoginController {
//获得连接
private Connection conn = null;
//预编译处理对象
private PreparedStatement pst = null;
//结果集
private ResultSet rts = null;
//注入student控制层
static StudentController studentController = new StudentController();
/**
* 登录方法
*/
public void getLogin(){
Scanner scanner = new Scanner(System.in);
String key = "y";
try {
conn = DBUtil.getConnection();
//预编译
String sql = "select * from user where username = ? ";
pst = conn.prepareStatement(sql);
do{
System.out.println("请输入帐号:");
String name = scanner.nextLine();
pst.setString(1,name);
System.out.println("请输入密码");
String pwd = scanner.nextLine();
System.out.println("正在登陆");
for (int i = 0; i < 3; i++) {
Thread.sleep(500);
System.out.print(".");
}
System.out.println();
rts = pst.executeQuery();
List<User> list = new ArrayList<>();
while (rts.next()){
User user = new User();
user.setUid(rts.getInt("uid"));
user.setUsername(rts.getString("username"));
user.setPassword(rts.getString("password"));
user.setWrong_times(rts.getInt("wrong_times"));
user.setStatus(rts.getInt("status"));
list.add(user);
}
if (list.size()!=0){
for (User user : list) {
if(user.getStatus()!=1){
if(user.getPassword().equals(pwd)){
System.out.println("登陆成功,欢迎回来!");
String sql1 = "update user set wrong_times = 0 where username = ?";
PreparedStatement pst1 = conn.prepareStatement(sql1);
pst1.setString(1,name);
pst1.executeUpdate();
studentController.getMenu();
key = "n";
DBUtil.closeAll(null,pst1,rts);
}else {
if (user.getWrong_times()<3){
String sql2 = "update user set wrong_times = wrong_times + 1 where username = ?";
PreparedStatement pst2 = conn.prepareStatement(sql2);
pst2.setString(1,name);
pst2.executeUpdate();
System.out.println("密码输入错误,您已输错"+(user.getWrong_times()+1)+"次!");
System.out.println("错误3次,帐号将被锁定!");
System.out.println("是否重试?y/n");
key = scanner.nextLine();
DBUtil.closeAll(null,pst2,rts);
}
if(user.getWrong_times()==3){
String sql3 = "update user set status = 1 where username = ?";
PreparedStatement pst3 = conn.prepareStatement(sql3);
pst3.setString(1,name);
pst3.executeUpdate();
System.out.println("密码错误已达3次,您的帐号疑似处于不安全状态!帐号已被锁定!!");
System.out.println("请联系管理员解锁!");
key = "n";
DBUtil.closeAll(null,pst3,rts);
}
}
}else {
System.out.println("密码错误已达3次,您的帐号疑似处于不安全状态!帐号已被锁定!!");
System.out.println("请联系管理员解锁!");
key = "n";
}
}
}else {
System.out.println("用户名不存在!");
System.out.println("是否重试!y/n");
key = scanner.nextLine();
}
}while (key.equals("y"));
} catch (Exception e) {
e.printStackTrace();
} finally {
DBUtil.closeAll(conn,pst,rts);
}
}
}
StudentController
package com.lzl.StudentManage.controller;
import com.lzl.StudentManage.entity.Student;
import com.lzl.StudentManage.service.serviceimpl.StudentServiceImpl;
import java.util.*;
public class StudentController {
static StudentServiceImpl impl = new StudentServiceImpl();
static Scanner scanner = new Scanner(System.in);
/**
* 查询(条件)所有用户
* @param student 查询的关键字
* @return 所有符合条件的用户
*/
private static List<Student> getAll(Student student){
Map<String, Object> map = new HashMap<>();
map.put("sid",student.getSid());
map.put("sname",student.getSname());
map.put("sgender",student.getSgender());
map.put("sage",student.getSage());
map.put("saddress",student.getSaddress());
return impl.getAll(map);
}
/**
*多条件查询用户
*/
public void searchPrintAll(){
System.out.println("您可以进行关键字查询,请选择您要查询的关键字类别");
System.out.println("1:学号,2:姓名,3:性别,4:年龄,5:家庭住址");
Integer key = scanner.nextInt();
Student student = searchWords(key);
List<Student> list = getAll(student);
if (list.size()>0){
System.out.println("查询信息如下");
System.out.println("学号\t姓名\t\t性别 年龄 家庭住址");
for (Student s : list) {
if (s.getSname().length()>=2){
if (s.getSage() > 99){
System.out.println(s.getSid()+"\t|"+s.getSname()+"\t|"+s.getSgender()
+"\t|"+s.getSage()+"\t|"+s.getSaddress());
}else {
System.out.println(s.getSid()+"\t|"+s.getSname()+"\t|"+s.getSgender()
+"\t|"+s.getSage()+"\t\t|"+s.getSaddress());
}
} else if(s.getSname().length()==1){
System.out.println(s.getSid()+"\t|"+s.getSname()+"\t\t|"+s.getSgender()
+"\t|"+s.getSage()+"\t\t|"+s.getSaddress());
}else {
System.out.println(s.getSid()+"\t|"+s.getSname()+"\t\t|"+s.getSgender()
+"\t|"+s.getSage()+"\t|"+s.getSaddress());
}
}
}else {
System.out.println("没有相关数据!");
}
}
/**
* 无条件输出所有学生信息
*/
private void printAll() {
Student student = new Student();
List<Student> list = getAll(student);
System.out.println("学号\t姓名\t\t性别 年龄 \t家庭住址");
for (Student s : list) {
if (s.getSname().length()>=2){
if (s.getSage() > 99){
System.out.println(s.getSid()+"\t|"+s.getSname()+"\t|"+s.getSgender()
+"\t|"+s.getSage()+"\t|"+s.getSaddress());
}else {
System.out.println(s.getSid()+"\t|"+s.getSname()+"\t|"+s.getSgender()
+"\t|"+s.getSage()+"\t\t|"+s.getSaddress());
}
} else if(s.getSname().length()==1){
System.out.println(s.getSid()+"\t|"+s.getSname()+"\t\t|"+s.getSgender()
+"\t|"+s.getSage()+"\t\t|"+s.getSaddress());
}else {
System.out.println(s.getSid()+"\t|"+s.getSname()+"\t\t|"+s.getSgender()
+"\t|"+s.getSage()+"\t|"+s.getSaddress());
}
}
}
/**
* 设置多条件查询的关键字
* @param key 查询编号
* @return 一个用于多条件查询的学生实体
*/
private static Student searchWords(Integer key) {
Student student = new Student();
if(key==1){
System.out.println("请输入您想要查询的学号:");
Integer sid = scanner.nextInt();
student.setSid(sid);
}else if (key==2){
System.out.println("请输入您想要查询的姓名关键字:");
String sname = scanner.next();
student.setSname(sname);
}else if (key == 3){
System.out.println("请输入您想要查询的性别(男/女):");
String sgender = scanner.next();
student.setSgender(sgender);
}else if (key == 4){
System.out.println("请输入您想要查询的年龄:");
Integer sage = scanner.nextInt();
student.setSage(sage);
}else if (key == 5){
System.out.println("请输入您想要查询的家庭住址:");
String saddress = scanner.next();
student.setSaddress(saddress);
}else {
System.out.println("你是来捣乱的吧!乱输入,给你所有的数据,玩去吧!");
}
return student;
}
/**
* 获得功能菜单
*/
public void getMenu() {
int key = 0;
do{
System.out.println("==============================功能菜单==========================");
System.out.println("您有以下功能:1:输出所有学生信息,2:模糊查询学生,3:新增学生,4:修改学生信息," +
"5:删除学生信息,0:退出系统");
key = scanner.nextInt();
switch (key){
case 1:
printAll();
break;
case 2:
searchPrintAll();
break;
case 3:
addNew();
break;
case 4:
updateBySid();
break;
case 5:
deleteBySid();
break;
case 0:
break;
default:
System.out.println("你是来找茬的吧?");
break;
}
}while (key!=0);
System.out.println("您已成功退出!");
}
/**
* 新增方法
*/
private void addNew() {
System.out.println("正在进行新增操作");
System.out.println("请输入新增学生姓名:");
String sname = scanner.next();
System.out.println("输入新增学生性别:");
String sgender = scanner.next();
System.out.println("输入新增学生年龄");
Integer sage = scanner.nextInt();
System.out.println("输入学生地址:");
String saddress = scanner.next();
Student student = new Student(null,sname,sgender,sage,saddress);
System.out.println("新增学生信息如下:");
System.out.println("姓名:"+sname+"|性别:"+sgender
+"|年龄:"+sage+"|地址:"+saddress);
Integer key = impl.insertStudent(student);
if (key > 0){
System.out.println("新增成功!");
}else {
System.out.println("新增失败!");
}
}
/**
* 修改方法
*/
private void updateBySid() {
System.out.println("正在进行修改操作");
System.out.println("请输入要修改学生的学号:");
String sid = scanner.next();
System.out.println("请输入新的姓名:");
String sname = scanner.next();
System.out.println("输入新的学生性别:");
String sgender = scanner.next();
System.out.println("输入新的学生年龄:");
String sage = scanner.next();
System.out.println("输入新的学生地址:");
String saddress = scanner.next();
System.out.println("修改后学生信息如下:");
System.out.println("姓名:"+sname+"|性别:"+sgender
+"|年龄:"+sage+"|地址:"+saddress);
ArrayList<String> parms = new ArrayList<>();
parms.add(sname);
parms.add(sgender);
parms.add(sage);
parms.add(saddress);
parms.add(sid);
//此处id要最后放入集合,因为DBUtil中字符串拼接按添加的先后顺序
//update语句的where子句在最后,所以sid放再最后,其它字段按原顺序
Integer key = impl.updateStudent(parms);
if (key > 0){
System.out.println("修改成功!");
}else {
System.out.println("修改失败!");
}
}
/**
* 删除方法
*/
private void deleteBySid() {
System.out.println("正在进行删除操作");
System.out.println("请输入要删除的学生学号:");
String sid = scanner.next();
Integer key = impl.deleteStudent(sid);
if (key > 0){
System.out.println("删除成功!");
}else {
System.out.println("删除失败!");
}
}
}
service包
StudentService
package com.lzl.StudentManage.service;
import com.lzl.StudentManage.entity.Student;
import com.lzl.day029.User;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
public interface StudentService {
/**
* 获得所有用户
* @param keys 多条件查询,分页参数
* @return 所有符合条件用户集合
*/
List<Student> getAll(Map<String,Object> keys);
/**
* 新增学生
* @param student 新增的学生信息
* @return 受影响行数
*/
Integer insertStudent(Student student);
/**
* 修改数据
* @param params 字符串集合
* @return 受影响行数
*/
Integer updateStudent(ArrayList<String> params);
/**
*
* @param sid 学生学号
* @return 受影响行数
*/
Integer deleteStudent(String sid);
}
StudentServiceImpl
package com.lzl.StudentManage.service.serviceimpl;
import com.lzl.StudentManage.entity.Student;
import com.lzl.StudentManage.service.StudentService;
import com.lzl.StudentManage.util.DBUtil;
import com.lzl.StudentManage.util.StringUtil;
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 java.util.Map;
public class StudentServiceImpl implements StudentService {
//获得连接
private Connection conn = null;
//预编译处理对象
private PreparedStatement pst = null;
//结果集
private ResultSet rts = null;
//参数集合
private ArrayList<Object> params = null;
/**
* 获得所有用户
*
* @param keys 多条件查询,分页参数
* @return 所有用户集合
*/
@Override
public List<Student> getAll(Map<String, Object> keys) {
//构建数据存放容器
List<Student> list = new ArrayList<>();
//构建查询所需参数的容器
params = new ArrayList<>();
//初始化sql语句 设置1=1,确保语句能执行不报错
String sql = "select * from student1 where 1 = 1";
//拼串,将多条件查询需要的参数全部拼接过来
//判断不为空,则进行拼接
if(!StringUtil.isNull(keys.get("sid"))){
//将sql语句进行拼接,注意and开头有一个空格
sql+=" and sid = ?";
//将后边要填补占位符的参数从Map(Controller传过来的参数)集合添加进参数容器
params.add(keys.get("sid"));
}
if(!StringUtil.isNull(keys.get("sname"))){
//将sql语句进行拼接,注意and开头有一个空格
sql+=" and sname like ?";
//将后边要填补占位符的参数从Map(Controller传过来的参数)集合添加进参数容器
params.add("%"+keys.get("sname")+"%");
}
if(!StringUtil.isNull(keys.get("sgender"))){
sql+=" and sgender = ?";
params.add(keys.get("sgender"));
}
if(!StringUtil.isNull(keys.get("sage"))){
sql+=" and sage = ?";
params.add(keys.get("sage"));
}
if(!StringUtil.isNull(keys.get("saddress"))){
sql+=" and saddress like ?";
params.add("%"+keys.get("saddress")+"%");
}
sql+=" order by sid";
try {
//获取连接
conn = DBUtil.getConnection();
//预编译处理sql
pst = conn.prepareStatement(sql);
//设置参数
for (int i = 0; i < params.size(); i++) {
pst.setObject(i+1,params.get(i));
}
//执行sql获得数据
rts = pst.executeQuery();
//处理数据
while (rts.next()){
//建一个对象
Student student = new Student();
//将所有结果集中的值取出,赋给对象
student.setSid(rts.getInt("sid"));
student.setSname(rts.getString("sname"));
student.setSgender(rts.getString("sgender"));
student.setSage(rts.getInt("sage"));
student.setSaddress(rts.getString("saddress"));
//将对象放到容器内
list.add(student);
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
//关流
DBUtil.closeAll(conn,pst,rts);
}
return list;
}
/**
* 新增学生
*
* @param student 新增的学生信息
* @return 受影响行数
*/
@Override
public Integer insertStudent(Student student) {
//给一个默认返回值
Integer key = 0;
try {
//获得连接
conn = DBUtil.getConnection();
//写sql语句
String sql = "insert into student1(sname,sgender,sage,saddress) values (?,?,?,?)";
//预编译sql
pst = conn.prepareStatement(sql);
//将插入的值赋给占位符
pst.setString(1,student.getSname());
pst.setString(2,student.getSgender());
pst.setInt(3,student.getSage());
pst.setString(4,student.getSaddress());
//交给executeUpdate执行,key接收受影响行数
key = pst.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
}finally {
//关流
DBUtil.closeAll(conn,pst,rts);
}
return key;
}
/**
*修改
* @param params 字符串集合
* @return 受影响行数
*/
@Override
public Integer updateStudent(ArrayList<String> params) {
//给一个默认返回值
Integer key = 0;
//编写sql语句
String sql = "update student1 set sname = ?,sgender = ?," +
"sage = ?,saddress = ? where sid = ?";
//将controller传过来的字符串集合,和sql语句交给DBUtil的方法执行,用key接收受影响行数
key = DBUtil.executeUpdate(sql,params);
return key;
}
/**删除
* @param sid 学生学号
* @return 受影响行数
*/
@Override
public Integer deleteStudent(String sid) {
Integer key = 0;
if (!StringUtil.isNull(sid)){
String sql = String.format("delete from student1 where sid in(%s)",sid);
key = DBUtil.executeUpdate(sql,params);
}
return key;
}
}
test包
测试类
package com.lzl.StudentManage.test;
import com.lzl.StudentManage.controller.LoginController;
import com.lzl.StudentManage.controller.StudentController;
import java.util.Scanner;
public class WebModel {
static StudentController studentController = new StudentController();
static LoginController loginController = new LoginController();
static Scanner scanner = new Scanner(System.in);
public static void main(String[] args) {
System.out.println("============欢迎来到学生管理系统============");
System.out.println("请您登陆");
loginController.getLogin();
}
}
附上效果演示
演示CRUD