课堂测试

package Util;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import Util.util;
import Util.dao;
public class dao {
	public static String shenfen(String username) {
		Connection connection = util.getConnection();
		PreparedStatement ps = null;
		String shenfen="";
		try {
			String sql = "SELECT * FROM zhuce where username ='"+username+"'";
			ps = connection.prepareStatement(sql);
			ResultSet rs = ps.executeQuery();

			while(rs.next()){
				
				 shenfen = rs.getString("shenfen");
				
			}
		} catch (SQLException e) {
			// TODO 自动生成的 catch 块
			e.printStackTrace();
		}
		return shenfen;
	}
	
	
	public static String denglu(String username) {
		Connection connection = util.getConnection();
		PreparedStatement ps = null;
		String password="";
		try {
			String sql = "SELECT * FROM zhuce where username ='"+username+"'";
			ps = connection.prepareStatement(sql);
			ResultSet rs = ps.executeQuery();

			while(rs.next()){
				
				 password = rs.getString("password");
				
			}
		} catch (SQLException e) {
			// TODO 自动生成的 catch 块
			e.printStackTrace();
		}
		String password1 = util.decryptKaiser(password, 3);
		return password1;
	}
	public static void yhzeng(String username,String password,String shenfen)
	{
		String password1 = util.encryptKaisa(password, 3);
		Connection conn = util.getConnection();
		PreparedStatement ps = null;
		
		
		try {
			String sql="insert into zhuce values(?,?,?);";
		    ps=conn.prepareStatement(sql);
		    ps.setString(1,username);
		    ps.setString(2,password1);
		    ps.setString(3,shenfen);
		    ps.executeUpdate();
		} catch (SQLException e) {
			// TODO 自动生成的 catch 块
			e.printStackTrace();
		}finally {
			
			
		}
	}
	public static void xinxizeng(String name,String sex,String age,String banji,String number)
	{
		//新增学生信息
		Connection conn = util.getConnection();
		PreparedStatement ps = null;
		try {
			String sql="insert into xinxi values(?,?,?,?,?);";
		    ps=conn.prepareStatement(sql);
		    ps.setString(1,name);
		    ps.setString(2,sex);
		    ps.setString(3,age);
		    ps.setString(4,banji);
		    ps.setString(5,number);

		    ps.executeUpdate();
		} catch (SQLException e) {
			// TODO 自动生成的 catch 块
			e.printStackTrace();
		}finally {
			
			
		}
	}
	public static void seexinxi(String name)
	{
		//新增学生信息
		Connection conn = util.getConnection();
		PreparedStatement ps = null;
		try {
			String sql= "SELECT * FROM xinxi where name ='"+name+"'";
		    ps=conn.prepareStatement(sql);
		    ps.setString(1,name);
		    ps.executeUpdate();
		} catch (SQLException e) {
			// TODO 自动生成的 catch 块
			e.printStackTrace();
		}finally {
			
			
		}
	}
	public static void updatexinxi(String name,String sex,String age,String banji,String number)
	{
		Connection conn = util.getConnection();
		PreparedStatement ps = null;
		
		try {
			String sql = "update xinxi set sex = ? ,age = ?,banji = ?,number = ? where name =?";
			ps=conn.prepareStatement(sql);
			ps.setString(1,sex);
			ps.setString(2,age);
		    ps.setString(3,banji);
		    ps.setString(4,number);
		    ps.setString(5,name);
		
		    ps.executeUpdate();

		} catch (SQLException e) {
			// TODO 自动生成的 catch 块
			e.printStackTrace();
		}finally {
			
			
		}
	}
	public static void addchengji(String xuehao,String banji,String gaoshu,String xinxi,String shujvku)
	{
		//新增学生信息
		Connection conn = util.getConnection();
		PreparedStatement ps = null;
		try {
			String sql="insert into chengji values(?,?,?,?,?);";
		    ps=conn.prepareStatement(sql);
		    ps.setString(1,xuehao);
		    ps.setString(2,banji);
		    ps.setString(3,gaoshu);
		    ps.setString(4,xinxi);
		    ps.setString(5,shujvku);

		    ps.executeUpdate();
		} catch (SQLException e) {
			// TODO 自动生成的 catch 块
			e.printStackTrace();
		}finally {
			
			
		}
	}
	public static void updatechengji(String xuehao,String gaoshu,String xinxi,String shujvku)
	{
		Connection conn = util.getConnection();
		PreparedStatement ps = null;
		
		try {
			String sql = "update chengji set gaoshu = ? ,xinxi = ?,shujvku = ? where xuehao =?";
			ps=conn.prepareStatement(sql);
			ps.setString(1,gaoshu);
			ps.setString(2,xinxi);
		    ps.setString(3,shujvku);
		    ps.setString(4,xuehao);
		    ps.executeUpdate();

		} catch (SQLException e) {
			// TODO 自动生成的 catch 块
			e.printStackTrace();
		}finally {
			
			
		}
	}
	public static void deletexinxi(String name)
	{
		Connection conn = util.getConnection();
		PreparedStatement ps = null;
		
		try {
			String sql="DELETE FROM xinxi WHERE name='" +name+"';";
		    Statement stmt = conn.createStatement();
		    stmt.executeUpdate(sql);
		} catch (SQLException e) {
			// TODO 自动生成的 catch 块
			e.printStackTrace();
		}finally {
			
			
		}
	}
	public static void updatezhucexinxi(String username,String password,String profession)
	{
		Connection conn = util.getConnection();
		PreparedStatement ps = null;
		
		try {
			String sql = "update zhuce set password = ? ,profession = ? where username =?";
			ps=conn.prepareStatement(sql);
			ps.setString(1,password);
			ps.setString(2,profession);
		    ps.setString(3,username);

		    ps.executeUpdate();

		} catch (SQLException e) {
			// TODO 自动生成的 catch 块
			e.printStackTrace();
		}finally {
			
			
		}
	}
}

　　

package Util;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class util {
	static final String JDBC_DRIVER = "com.mysql.cj.jdbc.Driver";
	static final String DB_URL = "jdbc:mysql://localhost:3306/kaoshi?useUnicode=true&characterEncoding=UTF-8";

	// 账号和密码
	static final String USER = "root";
	static final String PASS = "NN06280055";

	public static Connection getConnection() {
		try {
			// 创建链接
			Class.forName(JDBC_DRIVER);
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		Connection conn = null;
		try {
			conn = DriverManager.getConnection(DB_URL, USER, PASS);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return conn;
	}

	// �ر���Դ�ķ���
	public static void close(Connection conn) {
		try {
			if (conn != null) {
				conn.close();
			}

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	public static void close(ResultSet resultSet) {
		try {
			if (resultSet != null) {
				resultSet.close();
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	public static void close(Statement statement) {
		try {
			if (statement != null) {
				statement.close();
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	public static void close(PreparedStatement pstatement) {
		try {
			if (pstatement != null) {
				pstatement.close();
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	 public boolean isSame(String s)
	    {
	        Connection connection = getConnection();
	        PreparedStatement preparedStatement=null; 
	        ResultSet rs=null;
	        boolean b=false;
	        try {
	            String sql = "select * from test2";
	            preparedStatement=connection.prepareStatement(sql);
	            rs=preparedStatement.executeQuery();
	            while(rs.next()){
	                if( s.equals(rs.getString("zhuti")))
	                        b=true;
	            }
	            //preparedStatement.executeUpdate();
	            
	        } catch (SQLException  e) {
	            e.printStackTrace();
	        }finally{
	            close(rs);
	            close(preparedStatement);
	            close(connection);
	        }
	        return b;
	    }
	 
	 //加密密码
	 public static String encryptKaisa(String orignal, int key) {
			//将字符串转换为数组
			char[] chars = orignal.toCharArray();
			StringBuffer buffer = new StringBuffer();
			//遍历数组
			for(char aChar : chars) {
				//获取字符的ASCII编码
				int asciiCode = aChar;
				//偏移数据
				asciiCode += key;
				//将偏移后的数据转为字符
				char result = (char)asciiCode;
				//拼接数据
				buffer.append(result);
			}
			return buffer.toString();
		}
		
		//解密密码
		public static String decryptKaiser(String encryptedData, int key) {
		    // 将字符串转为字符数组
		    char[] chars = encryptedData.toCharArray();
		    StringBuilder sb = new StringBuilder();
		    // 遍历数组
		    for (char aChar : chars) {
		        // 获取字符的ASCII编码
		        int asciiCode = aChar;
		        // 偏移数据
		        asciiCode -= key;
		        // 将偏移后的数据转为字符
		        char result = (char) asciiCode;
		        // 拼接数据
		        sb.append(result);
		    }
	 
		    return sb.toString();
		}
	 
}

　　

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<%@ page language="java" contentType="text/html; charset=GB18030"
    pageEncoding="GB18030"%>
<meta http-equiv="Content-Type" content="text/html; charset=GB18030">
<title>在此处插入标题</title>
</head>
<body>
     <form action="addchengji1.jsp" method="get">
<table align="center" border="1" width="500">
			
			<tr>

				<td>学号</td>
				<td><input type="text" name="xuehao" /></td>
			</tr>
						<tr>

				<td>班级</td>
				<td><input type="text" name="banji" /></td>
			</tr>
						
			<tr>

				<td>高数成绩</td>
				<td><input type="text" name="gaoshu" /></td>
			</tr>
						<tr>

				<td>信息成绩</td>
				<td><input type="text" name="xinxi" /></td>
			</tr>
						<tr>

				<td>数据库成绩成绩</td>
				<td><input type="text" name="shujvku" /></td>
			</tr>
						<tr align="center">

				<td colspan="2"><input type="submit" value="提交" /></td>

			</tr>
</body>
</html>

　　

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
		<%@ page import="Util.dao"%>
	<%@ page import="Util.util"%>
<%@ page language="java" contentType="text/html; charset=GB18030"
    pageEncoding="GB18030"%>
<meta http-equiv="Content-Type" content="text/html; charset=GB18030">
<title>在此处插入标题</title>
</head>
<body>
<%
String xuehao = request.getParameter("xuehao");
String banji = request.getParameter("banji");
String gaoshu = request.getParameter("gaoshu");
String xinxi = request.getParameter("xinxi");
String shujvku = request.getParameter("shujvku");
dao.addchengji(xuehao,banji,gaoshu,xinxi,shujvku);
%>
</body>
</html>

　　

<%@ page language="java" contentType="text/html; charset=UTF-8"
	pageEncoding="UTF-8"%>
	<%@ page import="Util.dao"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<body>
<form action="denglu.jsp" method="get">
<h1 style="text-align: center;">河北宏志大学学生成绩管理系统</h1>
<br>
<div style="text-align: center;">
    <tr>
        <td>身份：

            <select name="profession">
                <option value="1">学生</option>
                <option value="2">教师</option>
                <option value="3" selected>管理员</option>
            </select>
        </td>
    </tr>

    <tr><td>
        用户名:<input type="text" name="username"><br></tr>

    <tr><td>
        密码:<input type="text" name="password"></td> </tr>
     <tr><td>

    <tr>
        <td colspan="2"><div align="center">
            <input type="submit" value="登录"></div></td> </tr>
    </table>
    </form>
    <center><input type="button" onclick="window.location.href='zhuce.jsp';" value="注册"><br>
<br>
</div>
</body>
</html>

　　

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<%@ page language="java" contentType="text/html; charset=UTF-8"
	pageEncoding="UTF-8"%>
	<%@ page import="Util.util"%>
	<%@ page import="java.sql.Connection"%> 
	<%@ page import="java.sql.PreparedStatement"%> 
<%@ page import="java.sql.SQLException"%> 
<%@ page import="java.sql.Statement"%> 
<%@ page import="java.sql.ResultSet"%> 
<%@page import="Util.dao" %>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>在此处插入标题</title>
</head>
<body>
<jsp:useBean id="util" class="Util.util" scope="page" />
<table border="1"style="text-align:center;">
	         <tr>
	        <td align="center" >姓名</td>
	         <td align="center" >性别</td>
	        <td align="center" >年龄</td>
	        <td align="center" >班级</td>
	        <td align="center" >学号</td>
	        </tr>
<%
Connection connection = util.getConnection();
PreparedStatement ps = null;
try {
	String sql = "SELECT name,sex,age,banji,number FROM xinxi WHERE EXISTS ( SELECT * FROM chengji WHERE xinxi.number = chengji.xuehao AND chengji.`gaoshu`<60)";
	ps = connection.prepareStatement(sql);
	ResultSet rs = ps.executeQuery();//要用statement类的executeQuery()方法来下达select指令以查询数据库，executeQuery()方法会把数据库响应的查询结果存放在ResultSet类对象中供我们使用。即语句：ResultSet rs=s.executeQuery(sql);
	while(rs.next()){
%>
			 <tr>
			<td><%=(rs.getString("name")) %></td>//获取数据表中的信息
			<td><%=(rs.getString("sex")) %></td>
			<td><%=(rs.getString("age")) %></td>
			<td><%=(rs.getString("banji")) %></td>
			<td><%=(rs.getString("number")) %></td>
			</tr>	
<%
            }
        } catch (Exception e) {
            out.println(e);
        }
    %>
</body>
</html>

　　

<%@ page language="java" contentType="text/html; charset=UTF-8"
	pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<body>
<form action="deletexinxi1.jsp" method="get">
<h1 style="text-align: center;"></h1>
<br>
<div style="text-align: center;">

    <tr><td>
        输入姓名:<input type="text" name="name"><br></tr>

    <tr>
        <td colspan="2"><div align="center">
            <input type="submit" value="删除"></div></td> </tr>
    </table>
    </form>
</div>
</body>
</html>

　　

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
		<%@ page import="Util.dao"%>
	<%@ page import="Util.util"%>
<%@ page language="java" contentType="text/html; charset=GB18030"
    pageEncoding="GB18030"%>
<meta http-equiv="Content-Type" content="text/html; charset=GB18030">
<title>在此处插入标题</title>
</head>
<body>
<%
String name = request.getParameter("name");
dao.deletexinxi(name);
%>
</body>
</html>

　　

<%@ page language="java" contentType="text/html; charset=UTF-8"
	pageEncoding="UTF-8"%>
	<%@ page import="Util.dao"%>
	<%@ page import="Util.util"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<body>
<%
String profession = request.getParameter("profession");
String username = request.getParameter("username");
session.setAttribute("username",username);
String password = request.getParameter("password");
String shenfen = dao.shenfen(username);
if(profession.equals("1"))
{
	if(profession.equals(shenfen)&&password.equals(dao.denglu(username)))
		%>
	<jsp:forward page="xszhuym.jsp"></jsp:forward>
	<%
}
if(profession.equals("2"))
{
	if(profession.equals(shenfen)&&password.equals(dao.denglu(username)))
		%>
	<jsp:forward page="jszhuym.jsp"></jsp:forward>
	<%
}
if(profession.equals("3"))
{
	if(profession.equals(shenfen)&&password.equals(dao.denglu(username)))
		%>
	<jsp:forward page="glyzhuym.jsp"></jsp:forward>
	<%
}


%>
</body>
</html>