学生信息管理系统_javaWeb.zip

package myproject; import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; public class MysqlOperation { public static String user="root"; public static String password="200117x"; //数据库连接方法，自动创建数据库和表 public static Connection getConnection() { Connection con=null; Statement stat=null; try { Class.forName("com.mysql.jdbc.Driver"); // "jdbc:mysql://localhost:3306/mydatabase?characterEncoding=utf8&useSSL=false" String url_1="jdbc:mysql://localhost:3306?characterEncoding=utf-8&useSSL=false"; String url_2="jdbc:mysql://localhost:3306/mydatabase?characterEncoding=utf-8&useSSL=false"; // 建立连接 con=DriverManager.getConnection(url_1,user,password); // 创建数据库mydatabase stat=(Statement)con.createStatement(); String sql_1="create database if not exists mydatabase;"; // 执行语句创建数据库mydatabase stat.executeUpdate(sql_1); // 建立新的连接用于在mydatabase下创建student和alluser表 con=DriverManager.getConnection(url_2,user,password); String sql_2="create table if not exists student( sno varchar(20) primary key, " + "sname varchar(20), sex varchar(20), sage varchar(20), " + "phone varchar(20),dorm varchar(20));"; String sql_3="create table if not exists alluser( username varchar(20) primary key, " + "password varchar(20))"; stat=(Statement)con.createStatement(); // 执行语句创建student和alluser表 stat.executeUpdate(sql_2); stat.executeUpdate(sql_3); }catch (SQLException e) { e.printStackTrace(); } catch (ClassNotFoundException e) { e.printStackTrace(); } // 返回连接 return con; } // 注册 public static void register(String user_1,String password_1) { Connection con =null; Statement stat=null; try { con=getConnection(); stat=(Statement)con.createStatement(); String sql="insert into alluser values ('"+user_1+"','"+password_1+"');"; // 执行语句 stat.executeUpdate(sql); }catch (Exception e) { e.printStackTrace(); } } // 登录检查 public static boolean checkregister(String username,String password) { Connection con =null; Statement stat=null; ResultSet rs=null; boolean mark=false; try { con=getConnection(); stat=(Statement)con.createStatement(); String sql="select * from alluser where username='"+username+"' and password='"+password+"';"; // 执行语句 rs=stat.executeQuery(sql); mark=rs.next(); }catch (Exception e) { e.printStackTrace(); } return mark; } //添加学生的方法 public static void addStudent(String sno,String sname,String sex,String sage,String phone,String dorm) { Connection con =null; Statement stat=null; try { con=getConnection(); stat=(Statement)con.createStatement(); String sql="insert into student values ('"+sno+"','"+sname+"','" +sex+"','"+sage+"','"+phone+"','"+dorm+"')"; // 执行语句 stat.executeUpdate(sql); }catch (Exception e) { e.printStackTrace(); } } //删除学生信息 public static void deleteStudent(String sno) { Connection con =null; Statement stat=null; try { con=getConnection(); stat=(Statement)con.createStatement(); String sql="delete from student where sno='"+sno+"'"; stat.executeUpdate(sql); }catch (Exception e) { // TODO: handle exception e.printStackTrace(); } } //查询学生信息 public static ResultSet selectStudent(String sno) { Connection con =null; Statement stat=null; ResultSet rs=null; try { con=getConnection(); stat=(Statement)con.createStatement(); String sql="select * from student where sno='"+sno+"'"; rs=stat.executeQuery(sql); }catch (Exception e) { // TODO: handle exception e.printStackTrace(); } return rs; } //输出全部学生信息 public static ResultSet showStudent() { Connection con =null; Statement stat=null; ResultSet rs=null; try { con=getConnection(); stat=(Statement)con.createStatement(); String sql="select * from student;"; rs=stat.executeQuery(sql); }catch (Exception e) { // TODO: handle exception e.printStackTrace(); } return rs; } //修改学生的信息 public static void updateStudent(String sno,String sname,String sex,String sage,String phone,String dorm) { Connection con =null; Statement stat=null; try { con=getConnection(); stat=(Statement)con.createStatement(); String sql; //判断是否为空,空值不用修改 if(sname.equals("")) { } else { sql="update student set sname='"+sname+"'"+"where sno='"+sno+"'"; //执行语句 stat.executeUpdate(sql); } if(sex.equals("")) { } else { sql="update student set sex='"+sex+"'"+"where sno='"+sno+"'"; //执行语句 stat.executeUpdate(sql); } if(sage.equals("")) { } else { sql="update student set sage='"+sage+"'"+"where sno='"+sno+"'"; //执行语句 stat.executeUpdate(sql); } if(phone.equals("")) { } else { sql="update student set phone='"+phone+"'"+"where sno='"+sno+"'"; //执行语句 stat.executeUpdate(sql); } if(dorm.equals("")) { } else { sql="update student set dorm='"+dorm+"'"+"where sno='"+sno+"'"; //执行语句 stat.executeUpdate(sql); } }catch (Exception e) { // TODO: handle exception e.printStackTrace(); } } //查询某个学生是否存在，用于检测学号是否重复 public static boolean QueryStudent(String sno) { Connection con =null; Statement stat=null; ResultSet rs=null; boolean mark=false; try { con=getConnection(); stat=(Statement)con.createStatement(); String sql="select * from student where sno='"+sno+"';"; // 执行语句 rs=stat.executeQuery(sql); mark=rs.next(); }catch (Exception e) { e.printStackTrace(); } return mark; } }