javaweb数据库增删改查——学生成绩管理系统
1.bean层
studentInfo.java
package bean; public class studentInfo { private String IDnumber; private String name; private String socre; private String classroom; private String major; public String getIDnumber() { return IDnumber; } public void setIDnumber(String iDnumber) { IDnumber = iDnumber; } public String getName() { return name; } public void setName(String name) { this.name = name; } public String getClassroom() { return classroom; } public void setClassroom(String classroom) { this.classroom = classroom; } public String getMajor() { return major; } public void setMajor(String major) { this.major = major; } public studentInfo() { } public String getSocre() { return socre; } public void setSocre(String socre) { this.socre = socre; } public studentInfo(String iDnumber, String name, String socre, String classroom, String major) { super(); IDnumber = iDnumber; this.name = name; this.socre = socre; this.classroom = classroom; this.major = major; } public void setstudentInfo(String iDnumber, String name, String socre, String classroom, String major) { IDnumber = iDnumber; this.name = name; this.socre = socre; this.classroom = classroom; this.major = major; } @Override public String toString() { return "student [IDnumber=" + IDnumber + ", name=" + name + ", socre=" + socre + ", classroom=" + classroom + ", major=" + major + ", getIDnumber()=" + getIDnumber() + ", getName()=" + getName() + ", getClassroom()=" + getClassroom() + ", getMajor()=" + getMajor() + ", getSocre()=" + getSocre() + "]"; } }
2.dao层
package dao; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import java.util.ArrayList; import java.util.List; import bean.DButil; import bean.studentInfo; public class dao { public studentInfo getbyname(String name) { String sql = "select * from studentinformation"+ " where name ='" + name + "'"; Connection conn = DButil.getCon(); Statement state = null; ResultSet rs = null; studentInfo book=new studentInfo(); try { state = conn.createStatement(); rs = state.executeQuery(sql); while (rs.next()) { String IDnumber=rs.getString("IDnumber"); String Name = rs.getString("name"); String major = rs.getString("major"); String score = rs.getString("score"); String classroom=rs.getString("classroom"); book.setstudentInfo(IDnumber,Name,score,classroom,major); } } catch (Exception e) { e.printStackTrace(); } finally { DButil.close(rs, state, conn); } return book; } //增加学生对象 public boolean add(studentInfo stu) { Connection conn = DButil.getCon(); PreparedStatement pstmt = null; boolean f = false; int a = 0; try { String sql = "insert into studentinformation(name,IDnumber,score,classroom,major) value(?,?,?,?,?)"; pstmt = conn.prepareStatement(sql); pstmt.setString(1, stu.getName()); pstmt.setString(2, stu.getIDnumber()); pstmt.setString(3, stu.getSocre()); pstmt.setString(4, stu.getClassroom()); pstmt.setString(5, stu.getMajor()); a = pstmt.executeUpdate(); } catch (SQLException e) { e.printStackTrace(); } finally { DButil.close(pstmt, conn); } if (a > 0) f = true; return f; } //删除学生对象 public boolean delete(String name) { String sql="delete from studentinformation "+"where name='" + name + "'"; Connection conn = DButil.getCon(); Statement state = null; int a = 0; boolean f = false; try { state = conn.createStatement(); a = state.executeUpdate(sql); } catch (SQLException e) { e.printStackTrace(); } finally { DButil.close(state, conn); } if (a > 0) { f = true; } return f; } //修改分数 public boolean update(studentInfo stu) { System.out.println(stu.getSocre()); String sql = "update studentinformation set score='"+ stu.getSocre() + "',classroom='" + stu.getClassroom()+ "',major='"+stu.getMajor()+"' where name='"+stu.getName()+"'"; Connection conn = DButil.getCon(); Statement state = null; boolean f = false; int a = 0; try { state = conn.createStatement(); System.out.println("看看是不是执行了"); a = state.executeUpdate(sql); System.out.println(a); } catch (SQLException e) { e.printStackTrace(); } finally { DButil.close(state, conn); } if (a > 0) { f = true; } System.out.println(f); return f; } //浏览学生信息 public List<studentInfo> list() { String sql = "select * from studentinformation"; List<studentInfo> list = new ArrayList<>(); Connection conn = DButil.getCon(); Statement state = null; ResultSet rs = null; try { state = conn.createStatement(); rs = state.executeQuery(sql); studentInfo bean = null; while (rs.next()) { String IDnumber=rs.getString("IDnumber"); String Name = rs.getString("name"); String major = rs.getString("major"); String score = rs.getString("score"); String classroom=rs.getString("classroom"); bean = new studentInfo(IDnumber,Name,score,classroom,major); list.add(bean); } } catch (SQLException e) { e.printStackTrace(); } finally { DButil.close(rs, state, conn); } return list; } //模糊查询学生成绩 public List<studentInfo> search(String IDnumber,String Name) { System.out.println(IDnumber+Name); String sql = "select * from studentinformation where (name like '%"+Name+"%' and IDnumber like '%"+IDnumber+"%')"; List<studentInfo> list = new ArrayList<>(); Connection conn = DButil.getCon(); Statement state = null; ResultSet rs = null; studentInfo bean = null; try { state = conn.createStatement(); rs = state.executeQuery(sql); while (rs.next()) { String classroom = rs.getString("classroom"); String major = rs.getString("major"); String score = rs.getString("score"); bean = new studentInfo(IDnumber,Name,score,classroom,major); list.add(bean); } } catch (SQLException e) { e.printStackTrace(); } finally { DButil.close(rs, state, conn); } return list; } }
3.DBUtil层
package DBUtil; import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; public class DButil { private static String mysqlname = "studentgrade"; private static Connection con; private static Statement sta; private static ResultSet re; private static String coursename = "com.mysql.cj.jdbc.Driver"; private static String url = "jdbc:mysql://localhost:3306/"+mysqlname+"?serverTimezone=UTC"; //注册驱动 public static Connection getCon() { try { Class.forName(coursename); System.out.println("驱动加载成功"); }catch(ClassNotFoundException e) { e.printStackTrace(); } try { con = DriverManager.getConnection(url,"root","123asd..00"); System.out.println("连接成功"); }catch(Exception e){ e.printStackTrace(); con = null; } return con; } public static void close (Statement state, Connection conn) { if (state != null) { try { state.close(); } catch (SQLException e) { e.printStackTrace(); } } if (conn != null) { try { conn.close(); } catch (SQLException e) { e.printStackTrace(); } } } public static void close (ResultSet rs, Statement state, Connection conn) { if (rs != null) { try { rs.close(); } catch (SQLException e) { e.printStackTrace(); } } if (state != null) { try { state.close(); } catch (SQLException e) { e.printStackTrace(); } } if (conn != null) { try { conn.close(); } catch (SQLException e) { e.printStackTrace(); } } } public static void main(String[] args) { getCon(); } }