package dao;
import bean.Student;
import util.DBUtil;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
public class StudentDAO {
public int getTotal() {
int total = 0;
String sql = "SELECT COUNT(*) FROM student";
try (Connection c = DBUtil.getConnection(); Statement st = c.createStatement()) {
ResultSet rs = st.executeQuery(sql);
while (rs.next()) {
total = rs.getInt(1);
}
} catch (SQLException e) {
e.printStackTrace();
}
return total;
}
public void add(Student student) {
String sql = "INSERT INTO student VALUES(NULL,?,?,?,?)";
try (Connection c = DBUtil.getConnection(); PreparedStatement ps = c.prepareStatement(sql)) {
ps.setInt(1,student.getStudentID());
ps.setString(2,student.getName());
ps.setInt(3,student.getAge());
ps.setString(4,student.getSex());
ps.execute();
} catch (SQLException e) {
e.printStackTrace();
}
}
public void delete(int id) {
String sql = "DELETE FROM student WHERE ID = ?";
try (Connection c = DBUtil.getConnection(); PreparedStatement ps = c.prepareStatement(sql)) {
ps.setInt(1, id);
ps.execute();
} catch (SQLException e) {
e.printStackTrace();
}
}
public void update(Student student) {
String sql = "update student set studentId = ?, name = ?, age = ?, sex = ? where id = ? ";
try (Connection c = DBUtil.getConnection(); PreparedStatement ps = c.prepareStatement(sql)) {
ps.setInt(1, student.getStudentID());
ps.setString(2, student.getName());
ps.setInt(3, student.getAge());
ps.setString(4, student.getSex());
ps.setInt(5, student.getId());
ps.execute();
} catch (SQLException e) {
e.printStackTrace();
}
}
public Student get(int id) {
Student student = new Student();
String sql = "SELECT * FROM student WHERE ID = " + id;
try (Connection c = DBUtil.getConnection(); Statement st = c.createStatement()) {
ResultSet rs = st.executeQuery(sql);
if (rs.next()) {
int student_id = rs.getInt("studentId");
String name = rs.getString("name");
int age = rs.getInt("age");
String sex = rs.getString("sex");
student.setStudentID(student_id);
student.setName(name);
student.setAge(age);
student.setSex(sex);
student.setId(id);
}
} catch (SQLException e) {
e.printStackTrace();
}
return student;
}
public ArrayList<Student> getStudent(String type, String content) {
String sql = null;;
if (type.equals("sno")){
sql = "SELECT * FROM student WHERE studentId = " + content;
}else if(type.equals("sname")){
sql = "SELECT * FROM student WHERE name = '"+content+"'";//注意中文查询要加引号
}else return null;
ArrayList<Student> students = new ArrayList<>();
try (Connection c = DBUtil.getConnection(); Statement st = c.createStatement()) {
ResultSet rs = st.executeQuery(sql);
while (rs.next()) {
Student student = new Student();
int student_id = rs.getInt("studentID");
String name = rs.getString("name");
int age = rs.getInt("age");
String sex = rs.getString("sex");
student.setStudentID(student_id);
student.setName(name);
student.setAge(age);
student.setSex(sex);
student.setId(rs.getInt("id"));
students.add(student);
}
} catch (SQLException e) {
e.printStackTrace();
}
return students;
}
public List<Student> list() {
return list(0, Short.MAX_VALUE);
}
public List<Student> list(int start, int count) {
List<Student> students = new ArrayList<>();
String sql = "SELECT * FROM student ORDER BY studentId desc limit ?,?";
try (Connection c = DBUtil.getConnection(); PreparedStatement ps = c.prepareStatement(sql)) {
ps.setInt(1, start);
ps.setInt(2, count);
ResultSet rs = ps.executeQuery();
while (rs.next()) {
Student student = new Student();
int id = rs.getInt("id");
int studentID = rs.getInt("studentId");
String name = rs.getString("name");
int age = rs.getInt("age");
String sex = rs.getString("sex");
student.setId(id);
student.setStudentID(studentID);
student.setName(name);
student.setAge(age);
student.setSex(sex);
students.add(student);
}
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
return students;
}
public static void main(String[] args){
StudentDAO studentDAO = new StudentDAO();
List<Student> students = studentDAO.list();
System.out.println(students.get(0).getName());
}
}