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实现类,实现相关的操作
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
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();
}
}
}