学生 信息 管理 系统

package com; import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.util.ArrayList; import com.Student; public class StudentDB { String connType="com.mysql.jdbc.Driver"; String DBurl="jdbc:mysql://localhost:3306/student"; String user="root"; String pass="3465412"; Connection conn; public void createConnection() { try { Class.forName(connType); //加载jdbc驱动 conn = DriverManager.getConnection(DBurl,user,pass); //建立到MySQL的连接 } catch(Exception ex) { ex.printStackTrace(); } } public Admin queryAdmin(int id)//根据id值查询数据 { Admin admin = null; try { //使用PreparedStatement对象来实现 String sql = "select * from admin where id=?"; PreparedStatement pstmt = conn.prepareStatement(sql);//创建PreparedStatement对象 pstmt.setInt(1, id); ResultSet result = pstmt.executeQuery();//获得ResultSet对象 while(result.next())//遍历数据集 { admin = new Admin(); admin.setId(result.getInt("id")); admin.setPassword(result.getInt("password")); } pstmt.close(); return admin; } catch(Exception e) { e.printStackTrace(); return admin; } } public Student query(int id)//根据id值查询数据 { Student student = null; try { /* Statement stmt = conn.createStatement();//创建Statement对象 String sql = "select * from t_teacher where i_id=" + id; ResultSet result = stmt.executeQuery(sql);//获得ResultSet对象 */ //使用PreparedStatement对象来实现 String sql = "select * from student where id=?"; PreparedStatement pstmt = conn.prepareStatement(sql);//创建PreparedStatement对象 pstmt.setInt(1, id); ResultSet result = pstmt.executeQuery();//获得ResultSet对象 while(result.next())//遍历数据集 { student = new Student(); student.setId(result.getInt("id")); student.setName(result.getString("name")); student.setAge(result.getInt("age")); student.setSex(result.getString("sex")); student.setAddress(result.getString("address")); } pstmt.close(); return student; } catch(Exception e) { e.printStackTrace(); return student; } } public ArrayList<Student> queryByName(String name)//根据id值查询数据 { Student student = null; ArrayList<Student> list = null; try { String sql = "select * from student where name like ?"; PreparedStatement pstmt = conn.prepareStatement(sql);//创建PreparedStatement对象 name = "%"+name+"%"; ////////////////////////////////// pstmt.setString(1, name); ResultSet result = pstmt.executeQuery();//获得ResultSet对象 list = new ArrayList<Student>(); while(result.next())//遍历数据集 { student = new Student(); student.setId(result.getInt("id")); student.setName(result.getString("name")); student.setAge(result.getInt("age")); student.setSex(result.getString("sex")); student.setAddress(result.getString("address")); list.add(student);//把一条记录放进列表里 } pstmt.close(); return list; } catch(Exception e) { e.printStackTrace(); return list; } } public ArrayList<Student> queryAll()//查询所有记录 { Student student = null; ArrayList<Student> list = null; try { /* Statement stmt = conn.createStatement();//创建Statement对象 String sql = "select * from t_teacher"; ResultSet result = stmt.executeQuery(sql);//获得ResultSet对象 */ //使用PreparedStatement对象来实现 String sql = "select * from student"; PreparedStatement pstmt = conn.prepareStatement(sql);//创建PreparedStatement对象 ResultSet result = pstmt.executeQuery();//获得ResultSet对象 list = new ArrayList<Student>(); while(result.next())//遍历数据集 { student = new Student(); student.setId(result.getInt("id")); student.setName(result.getString("name")); student.setAge(result.getInt("age")); student.setSex(result.getString("sex")); student.setAddress(result.getString("address")); list.add(student);//把一条记录放进列表里 } pstmt.close(); return list; } catch(Exception e) { e.printStackTrace(); return list; } } public boolean delete(int id)//根据id值删除一条记录 { try { /* Statement stmt = conn.createStatement();//创建Statement对象 String sql = "delete from t_teacher where id=" + id; stmt.executeUpdate(sql);//删除记录 */ //使用PreparedStatement对象来实现 String sql = "delete from student where id=?"; PreparedStatement pstmt = conn.prepareStatement(sql);//创建PreparedStatement对象 pstmt.setInt(1, id); pstmt.executeUpdate();//删除记录 pstmt.close(); return true; } catch(Exception e) { e.printStackTrace(); return false; } } public boolean insert(Student student)//插入一条记录 { if(query(student.getId()) != null) { System.out.println("该记录已经存在"); return false; } try { /* Statement stmt = conn.createStatement();//创建Statement对象 String sql = "insert into t_teacher values(" + teacher.getI_id() + ",'" + teacher.getS_name() + "'," + teacher.getI_age() + ",'" + teacher.getS_sex() + "','" + teacher.getS_address() + "')"; stmt.executeUpdate(sql);//插入数据 */ //使用PreparedStatement对象来实现 String sql = "insert into student values(?,?,?,?,?)"; PreparedStatement pstmt = conn.prepareStatement(sql);//创建PreparedStatement对象 pstmt.setInt(1, student.getId()); pstmt.setString(2, student.getName()); pstmt.setInt(3, student.getAge()); pstmt.setString(4, student.getSex()); pstmt.setString(5, student.getAddress()); pstmt.executeUpdate();//插入数据 pstmt.close(); return true; } catch(Exception e) { e.printStackTrace(); return false; } } public boolean update(Student student)//修改一条记录 { if(query(student.getId()) == null) { System.out.println("该记录不存在"); return false; } try { //使用PreparedStatement对象来实现 String sql = "update student set id=?, name=?, age=?, sex=?, address=? where id=?"; PreparedStatement pstmt = conn.prepareStatement(sql);//创建PreparedStatement对象 pstmt.setInt(1, student.getId()); pstmt.setString(2, student.getName()); pstmt.setInt(3, student.getAge()); pstmt.setString(4, student.getSex()); pstmt.setString(5, student.getAddress()); pstmt.setInt(6, student.getId()); pstmt.executeUpdate();//插入数据 pstmt.close();//关闭 return true; } catch(Exception e) { e.printStackTrace(); return false; } } void printTeacher(Student s)//打印某一条记录内容 { System.out.println("工号：" + s.getId()); System.out.println("姓名：" + s.getName()); System.out.println("年龄：" + s.getAge()); System.out.println("姓别：" + s.getSex()); System.out.println("地址：" + s.getAddress()); } }