StudentDAO接口，定义学生相关的操作
/*
* StudentDAO接口，定义学生相关的操作
*/
public interface StudentDAO {
//添加学生
public void addStudent(Student stu);
//删除学生
public void deleteStudent(int id);
//修改学生
public void updateStudent(Student stu);
//查询所有学生
public List<Student> getAllStudents();
//根据学号查询学生
public Student getStudentById(int id);
//根据条件模糊查询
public List<Student> getStudentsByCondition(String name,String gender);
}
StudentDAOImpl实现类，实现相关的操作
/*
* StudentDAOImpl实现类，实现相关的操作
*/
public class StudentDAOImpl implements StudentDAO {
Connection conn=null;
PreparedStatement pstmt=null;
ResultSet rs=null;
@Override
public void addStudent(Student stu) {
String sql="insert into student values (null,?,?,?,?,?)";
try {
conn=DBUtil.getConnection();
pstmt=conn.prepareStatement(sql);
pstmt.setString(1, stu.getName());
pstmt.setInt(2, stu.getAge());
pstmt.setString(3, stu.getGender());
pstmt.setString(4, stu.getPhone());
pstmt.setString(5, stu.getEducation());
pstmt.executeUpdate();
System.out.println("添加學生成功！");
} catch (SQLException e) {
e.printStackTrace();
}finally{
DBUtil.closeAll(rs, pstmt, conn);
}
}
@Override
public void deleteStudent(int id) {
String sql="delete from student where id=?";
try {
conn=DBUtil.getConnection();
pstmt=conn.prepareStatement(sql);
pstmt.setObject(1, id);
pstmt.executeUpdate();
System.out.println("删除學生成功！");
} catch (SQLException e) {
e.printStackTrace();
}finally{
DBUtil.closeAll(rs, pstmt, conn);
}
}
@Override
public void updateStudent(Student stu) {
String sql="update student set name=?,age=?,gender=?,phone=?,education=? where id=?";
try {
conn=DBUtil.getConnection();
pstmt=conn.prepareStatement(sql);
pstmt.setString(1, stu.getName());
pstmt.setInt(2, stu.getAge());
pstmt.setString(3, stu.getGender());
pstmt.setString(4, stu.getPhone());
pstmt.setString(5, stu.getEducation());
pstmt.setInt(6, stu.getId());
pstmt.executeUpdate();
System.out.println("修改學生成功！");
} catch (SQLException e) {
e.printStackTrace();
}finally{
DBUtil.closeAll(rs, pstmt, conn);
}
}
@Override
public List<Student> getAllStudents() {
List<Student> students=new ArrayList<Student>();
String sql="select * from student";
try {
conn=DBUtil.getConnection();
pstmt=conn.prepareStatement(sql);
rs=pstmt.executeQuery();
while(rs.next()){
Student stu=new Student(rs.getInt(1), rs.getString(2), rs.getInt(3), rs.getString(4),rs.getString(5),rs.getString(6));
students.add(stu);
}
} catch (SQLException e) {
e.printStackTrace();
}finally{
DBUtil.closeAll(rs, pstmt, conn);
}
return students;
}
@Override
public Student getStudentById(int id) {
Student stu=null;
String sql="select * from student where id=?";
try {
conn=DBUtil.getConnection();
pstmt=conn.prepareStatement(sql);
pstmt.setInt(1, id);
rs=pstmt.executeQuery();
if(rs.next()){
stu=new Student(rs.getInt(1), rs.getString(2), rs.getInt(3), rs.getString(4),rs.getString(5),rs.getString(6));
}
} catch (SQLException e) {
e.printStackTrace();
}finally{
DBUtil.closeAll(rs, pstmt, conn);
}
return stu;
}
@Override
public List<Student> getStudentsByCondition(String name, String gender) {
List<Student> students=new ArrayList<Student>();
String sql="select * from student where name like ? and gender=?";
try {
conn=DBUtil.getConnection();
pstmt=conn.prepareStatement(sql);
pstmt.setObject(1, "%"+name+"%");//注意此写法
pstmt.setObject(2, gender);
rs=pstmt.executeQuery();
while(rs.next()){
Student stu=new Student(rs.getInt(1), rs.getString(2), rs.getInt(3), rs.getString(4),rs.getString(5),rs.getString(6));
students.add(stu);
}
} catch (SQLException e) {
e.printStackTrace();
}finally{
DBUtil.closeAll(rs, pstmt, conn);
}
return students;
}
}
实体类Student，POJO
/*
* 实体类Student，POJO
*/
public class Student {
private int id;
private String name;
private int age;
private String gender;
private String phone;
private String education;
public Student() {
super();
}
public Student(int id, String name, int age, String gender, String phone,
String education) {
super();
this.id = id;
this.name = name;
this.age = age;
this.gender = gender;
this.phone = phone;
this.education = education;
}
public Student(String name, int age, String gender, String phone,
String education) {
super();
this.name = name;
this.age = age;
this.gender = gender;
this.phone = phone;
this.education = education;
}
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public int getAge() {
return age;
}
public void setAge(int age) {
this.age = age;
}
public String getGender() {
return gender;
}
public void setGender(String gender) {
this.gender = gender;
}
public String getPhone() {
return phone;
}
public void setPhone(String phone) {
this.phone = phone;
}
public String getEducation() {
return education;
}
public void setEducation(String education) {
this.education = education;
}
}
测试类
import java.util.Iterator;
import java.util.List;
/*
* 测试类
*/
public class Test {
public static void main(String[] args) {
//添加学生
StudentDAO sd=new StudentDAOImpl();
sd.addStudent(new Student("tom",20,"男","110","本科"));
//删除学生
sd.deleteStudent(2);
//修改学生
sd.updateStudent(new Student(5, "汤姆", 25, "男", "112", "研究生"));
//查询所有学生
List<Student> students=sd.getAllStudents();
Iterator<Student> it=students.iterator();
while(it.hasNext()){
Student stu=it.next();
System.out.println(stu.getId()+","+stu.getName()+","+stu.getEducation());
}
//根据编号查询学生
Student stu=sd.getStudentById(5);
System.out.println(stu.getId()+","+stu.getName()+","+stu.getEducation());
//模糊查找
List<Student> students=sd.getStudentsByCondition("姆", "男");
Iterator<Student> it=students.iterator();
while(it.hasNext()){
Student stu=it.next();
System.out.println(stu.getId()+","+stu.getName()+","+stu.getEducation());
}
}
}
数据库工具类
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
/*
* 数据库工具类
*/
public class DBUtil {
//获取数据库连接
public static Connection getConnection(){
Connection conn=null;
try {
Class.forName("com.mysql.jdbc.Driver");
conn=DriverManager.getConnection("jdbc:mysql://localhost:3306/test", "root", "123456");
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}
return conn;
}
//关闭所有资源
public static void closeAll(ResultSet rs,Statement stmt,Connection conn){
try {
if(rs!=null)
rs.close();
if(stmt!=null)
stmt.close();
if(conn!=null)
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
