创建Student、service、controlle层和各个接口
Student类
public class Student {
private Integer sid;
private String name;
private Integer age;
private Date birthday;
public Student() {
}
public Student(Integer sid, String name, Integer age, Date birthday) {
this.sid = sid;
this.name = name;
this.age = age;
this.birthday = birthday;
}
public Integer getSid() {
return this.sid;
}
public void setSid(Integer sid) {
this.sid = sid;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public Integer getAge() {
return age;
}
public void setAge(Integer age) {
this.age = age;
}
public Date getBirthday() {
return birthday;
}
public void setBirthday(Date birthday) {
this.birthday = birthday;
}
@Override
public String toString() {
return "Student{" +
"sid=" + sid +
", name='" + name + '\'' +
", age=" + age +
", birthday=" + birthday +
'}';
}
service类和接口
接口
//查询所有的学生信息
public abstract ArrayList<Student> findAll();
//条件查询,根据id获取学生信息
public abstract Student findById(Integer id);
//新增学生信息
public abstract int insert(Student stu);
//修改学生信息
public abstract int update(Student stu);
//删除学生信息
public abstract int delete(Integer id);
}
service类
public class StudentServiceImpl implements StudentService{
private StudentDao dao=new StudentDaoImpl();
@Override
public ArrayList<Student> findAll() {
return dao.findAll();
}
@Override
public Student findById(Integer id) {
return dao.findById(id);
}
@Override
public int insert(Student stu) {
return dao.insert(stu);
}
@Override
public int update(Student stu) {
return dao.update(stu);
}
@Override
public int delete(Integer id) {
return dao.delete(id);
}
}
Dao类和接口
接口
public interface StudentDao {
//查询所有的学生信息
public abstract ArrayList<Student> findAll();
//条件查询,根据id获取学生信息
public abstract Student findById(Integer id);
//新增学生信息
public abstract int insert(Student stu);
//修改学生信息
public abstract int update(Student stu);
//删除学生信息
public abstract int delete(Integer id);
}
dao类
public class StudentDaoImpl implements StudentDao{
//查询所有的学生信息
@Override
public ArrayList<Student> findAll() {
//创建存储列表
ArrayList<Student> list=new ArrayList<>();
Connection con =null;
Statement stat=null;
ResultSet re =null;
try {
//注册驱动
Class.forName("com.mysql.cj.jdbc.Driver");
//获取连接
con = DriverManager.getConnection("jdbc:mysql://localhost:3306/db2", "root", "123123");
//获取执行者对象
stat = con.createStatement();
//执行sql语句并返回结果
String sql="SELECT * FROM student";
re = stat.executeQuery(sql);
//处理结果
while (re.next()){
Integer sid=re.getInt("sid");
String name=re.getString("name");
Integer age=re.getInt("age");
Date birthday=re.getDate("birthday");
//封装成学生对象
Student stu=new Student(sid,name,age,birthday);
//存入列表中
list.add(stu);
}
}catch (Exception e){
e.printStackTrace();
}finally {
//释放资源
if (con !=null){
try {
con.close();
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
if (stat !=null){
try {
stat.close();
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
if(re !=null){
try {
re.close();
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
}
//将结果返回
return list;
}
//条件查询,根据id获取学生信息
@Override
public Student findById(Integer id) {
Student stu=new Student();
Connection con =null;
Statement stat =null;
ResultSet re =null;
try {
//注册驱动
Class.forName("com.mysql.cj.jdbc.Driver");
//连接数据库
con = DriverManager.getConnection("jdbc:mysql://localhost:3306/db2", "root", "123123");
//获取执行者对象
stat = con.createStatement();
//执行sql语句,并返回结果
String sql="SELECT * FROM student where sid='"+id+"'";
re = stat.executeQuery(sql);
//处理结果
while (re.next()){
Integer sid=re.getInt("sid");
String name=re.getString("name");
Integer age=re.getInt("age");
Date birthday=re.getDate("birthday");
//封装
stu.setSid(sid);
stu.setName(name);
stu.setAge(age);
stu.setBirthday(birthday);
}
} catch (Exception e) {
throw new RuntimeException(e);
}finally {
//释放资源
try {
con.close();
} catch (SQLException e) {
throw new RuntimeException(e);
}
try {
stat.close();
} catch (SQLException e) {
throw new RuntimeException(e);
}
try {
re.close();
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
return stu;
}
//新增学生信息
@Override
public int insert(Student stu) {
Connection con =null;
Statement stat =null;
int result=0;
try {
//注册驱动
Class.forName("com.mysql.cj.jdbc.Driver");
//获取连接
con = DriverManager.getConnection("jdbc:mysql://localhost:3306/db2", "root", "123123");
//获取执行者对象
stat = con.createStatement();
//执行sql语句,并处理结果
Date a = stu.getBirthday();
SimpleDateFormat sdf=new SimpleDateFormat("yyyy-MM-dd");
String birthday = sdf.format(a);
String sql="INSERT INTO student VALUES('"+stu.getSid()+"','"+stu.getName()+"','"+stu.getAge()+"','"+birthday+"')";
result = stat.executeUpdate(sql);
//处理结果
//释放资源
} catch (Exception e) {
throw new RuntimeException(e);
}finally {
try {
con.close();
} catch (SQLException e) {
throw new RuntimeException(e);
}
try {
stat.close();
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
return result;
}
//修改学生信息
@Override
public int update(Student stu) {
Connection con =null;
Statement stat =null;
int result=0;
try {
//注册驱动
Class.forName("com.mysql.cj.jdbc.Driver");
//获取连接
con = DriverManager.getConnection("jdbc:mysql://localhost:3306/db2", "root", "123123");
//获取执行者对象
stat = con.createStatement();
//执行sql语句,并处理结果
Date a = stu.getBirthday();
SimpleDateFormat sdf=new SimpleDateFormat("yyyy-MM-dd");
String birthday = sdf.format(a);
String sql="UPDATE student SET sid='"+stu.getSid()+"',name='"+stu.getName()+"',age='"+stu.getAge()+"',birthday='"+birthday+"' WHERE sid='"+stu.getSid()+"'";
result = stat.executeUpdate(sql);
//处理结果
//释放资源
} catch (Exception e) {
throw new RuntimeException(e);
}finally {
try {
con.close();
} catch (SQLException e) {
throw new RuntimeException(e);
}
try {
stat.close();
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
return result;
}
//删除学生信息
@Override
public int delete(Integer id) {
Connection con =null;
Statement stat =null;
int result=0;
try {
//注册驱动
Class.forName("com.mysql.cj.jdbc.Driver");
//获取连接
con = DriverManager.getConnection("jdbc:mysql://localhost:3306/db2", "root", "123123");
//获取执行者对象
stat = con.createStatement();
//执行sql语句,并处理结果
String sql="DELETE FROM student WHERE sid='"+id+"'";
result = stat.executeUpdate(sql);
//处理结果
//释放资源
} catch (Exception e) {
throw new RuntimeException(e);
}finally {
try {
con.close();
} catch (SQLException e) {
throw new RuntimeException(e);
}
try {
stat.close();
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
return result;
}
}
controlle类
public class StudentControlle {
private StudentService service=new StudentServiceImpl();
//查询学生信息
@Test
public void findAll(){
ArrayList<Student> list = service.findAll();
for (Student stu:list){
System.out.println(stu);
}
}
@Test
//条件查询,根据id获取学生信息
public void findById(){
System.out.println("请输入查询的id:");
Scanner sc=new Scanner(System.in);
int a = sc.nextInt();
Student id = service.findById(a);
System.out.println(id);
}
@Test
//新增学生信息
public void insert(){
Student stu=new Student(5,"1",23,NEW DATA());
int insert = service.insert(stu);
if (insert!=0){
System.out.println("添加成功");
}else {
System.out.println("添加失败");
}
}
@Test
//修改学生信息
public void update(){
Student stu = service.findById(1);
stu.setAge(19);
int update = service.update(stu);
if (update !=0){
System.out.println("修改成功!");
}else {
System.out.println("修改失败!");
}
}
@Test
//删除学生信息
public void delete(){
int delete = service.delete(2);
if (delete!=0){
System.out.println("删除成功!");
}else {
System.out.println("删除失败!");
}
}
}```