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>
