脚本宝典收集整理的这篇文章主要介绍了WEB应用程序开发---实践开发(MySQL+JavaJDBC+Ajax+JavaScript+Jquery),脚本宝典觉得挺不错的,现在分享给大家,也给大家做个参考。
基于MySQL+JavaJDBC+Ajax实现的学生信息管理系统附带演示图片+视频,前端利用了HTML+JavaScript+Jquery技术,来实现基本系统功能(增删改查)的操作实现
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>无标题文档</title>
<link href="css/style.css" rel="stylesheet" type="text/css" />
</head>
<script src="js/jquery-1.8.3.min.js" type="text/javascript" charset="utf-8"></script>
<script>
function checkAccount(account){
$.get("back/register",{account:account},function(res){
if(res==1){
$("#msgId").html("账号已注册");
return;
}else if(res==0){
$("#msgId").html("√");
}else{
$("#msgId").html("服务器忙!");
}
});
}
function subform(){
$.post("back/register",$("#forminfo").serialize(),function(res){
if(res==0){
alert("注册成功");
location.assign("login.html");
}else{
alert("服务器忙!");
}
});
}
</script>
<body>
<div class="formbody">
<div class="formtitle"><span>基本信息</span></div>
<ul class="forminfo" id="forminfo">
<li>
<label>用户名</label>
<input name="admin" type="text" class="dfinput" onblur="checkAccount(this.value)"/><br/>
<span id="msgId"></span><br/>
</li>
<li>
<label>密码设置</label>
<input name="password" type="text" class="dfinput" />
</li>
<br/>
<input value="登录" style="width:40%;height: 50px" type="button" onclick="subform()">
</ul>
</div>
</body>
</html>
package com.qn.FProgram.Servlet;
import com.qn.FProgram.Dao.RegisterDao;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.PrintWriter;
public class RegisterServlet extends HttpServlet {
//主要用于向数据库发送信息
@Override
protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
PrintWriter out=null;
try {
out= resp.getWriter();
String account1 = req.getParameter("account");//从表单中获取account的值
String password1 = req.getParameter("password");
RegisterDao registerDao=new RegisterDao();
registerDao.save(account1,password1);
out.println(0);
} catch (Exception e) {
e.printStackTrace();
out.println(1);
}
}
//主要用于进行消息验证
@Override
protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
PrintWriter out = null;
try {
out = resp.getWriter();
String account = req.getParameter("account");
RegisterDao registerDao = new RegisterDao();
int res = registerDao.select(account);
out.println(res);//0-未注册 非0已注册
}catch (Exception e){
e.printStackTrace();
out.println(500);
}
}
}
package com.qn.FProgram.Dao;
import java.sql.*;
public class RegisterDao {
public int select(String account) throws SQLException, ClassNotFoundException {
Connection connection = null;
PreparedStatement pt = null;
ResultSet resultSet = null;
try {
Class.forName("com.mysql.jdbc.Driver");
connection = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/ssm?characterEncoding=utf8&useSSL=false&serverTimezone=Asia/Shanghai", "root", "root");
pt = connection.prepareStatement("select count(*) from t_student where account=?");
pt.setString(1, account);
resultSet=pt.executeQuery();
resultSet.next();
return resultSet.getInt(1);
} finally {
if (connection != null) {
connection.close();
}
if (pt != null) {
pt.close();
}
}
}
public void save(String account,String password) throws ClassNotFoundException, SQLException {
Connection connection=null;
PreparedStatement ps=null;
Class.forName("com.mysql.jdbc.Driver");
connection = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/ssm?characterEncoding=utf8&useSSL=false&serverTimezone=Asia/Shanghai", "root", "root");
ps=connection.prepareStatement("insert into t_student(account,password)values(?,?)");
ps.setString(1,account);
ps.setString(2,password);
ps.executeUpdate();
if (connection != null) {
connection.close();
}
if (ps != null) {
ps.close();
}
}
}
package com.qn.FProgram.Mode;
import java.util.Date;
public class User {
private int id;
private String account;
private String password;
private Date reg_time;
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getAccount(String string) {
return account;
}
public void setAccount(String account) {
this.account = account;
}
public String getPassword(String string) {
return password;
}
public void setPassword(String password) {
this.password = password;
}
public Date getReg_time() {
return reg_time;
}
public void setReg_time(Date reg_time) {
this.reg_time = reg_time;
}
@Override
public String toString() {
return "User{" +
"id=" + id +
", account='" + account + ''' +
", password='" + password + ''' +
", reg_time=" + reg_time +
'}';
}
}
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>后台管理</title>
<link href="css/login.css" rel="stylesheet" type="text/css" />
</head>
<script src="js/jquery-1.8.3.min.js" type="text/javascript" charset="utf-8"></script>
<script type="application/javascript">
function subform(){
$.post("back/login",$("#formId").serialize(),function (res) {
var obj=$.parseJSON(res)//将接收值转化为字符串
if(obj.id!=null){
alert("登录成功");
//在前端储存用户信息
window.sessionStorage.setItem("user",res);
location.replace("main.html");
}else{
alert("账号或密码错误");
}
})
}
</script>
<body>
<div class="login_box">
<div class="login_l_img"><img src="images/login-img.png" /></div>
<div class="login">
<div class="login_logo"><a href="#"><img src="images/login_logo.png" /></a></div>
<div class="login_name">
<p>后台管理系统</p>
</div>
<form method="post" id="formId">
<input name="account" type="text" value="账号">
<input name="password" type="password" id="password" />
<input value="登录" style="width:100%;" type="button" onclick="subform()"><br/>
<br/>
<div align="center"><a href="register.html">没有账号?点击注册</a></div>
</form>
</div>
<div class="copyright">某某有限公司 版权所有©2016-2018 技术支持电话:000-00000000</div>
</div>
</body>
</html>
package com.qn.FProgram.Servlet;
import com.google.gson.Gson;
import com.qn.FProgram.Dao.LoginDao;
import com.qn.FProgram.Mode.User;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;
import java.io.IOException;
import java.io.PrintWriter;
public class LoginServlet extends HttpServlet {
@Override
protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
PrintWriter out=null;
//ServletContext一个应用程序对应一个ServletContext对象,储存全局信息
/* ServletContext servletContext= req.getServletContext();
System.out.println(servletContext);
System.out.println(servletContext.getInitParameter("name"));//获得当前应用的初始化参数
System.out.println(servletContext.getAttribute("a"));*/
try{
resp.setContentType("text/html;charset=utf-8");//响应格式设置
req.setCharacterEncoding("utf-8");//解码格式
String account = req.getParameter("account");//从表单中获取account的值
String password = req.getParameter("password");
LoginDao loginDao=new LoginDao();
User user= loginDao.checkLogin(account,password);
out=resp.getWriter();
//java程序一般将数据封装到对象中,响应到客户端,而客户端是JavaScript语言,俩边对象格式不一致
//为解决此问题诞生了一种轻量级解决方案:Json(JavaScript对象表现形式)是一种轻量级数据格式
if(user!=null){
HttpSession httpSession=req.getSession();
httpSession.setAttribute("user",user);
Gson gson=new Gson();
String s=gson.toJson(user);
System.out.println(s);
out.println(s);
}else{
out.println(1);
}
}
catch (Exception e){
e.printStackTrace();
out.println("服务器忙");
}
}
@Override
protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
HttpSession session = req.getSession();
User user=(User)session.getAttribute("user");
session.invalidate();//销毁浏览器
}
}
package com.qn.FProgram.Dao;
import com.qn.FProgram.Mode.User;
import java.sql.*;
public class LoginDao {
public User checkLogin(String account, String password) throws SQLException, ClassNotFoundException {
Connection connection = null;
PreparedStatement ps = null;
ResultSet resultSet = null;
User user = null;
try {
Class.forName("com.mysql.cj.jdbc.Driver");
/* 创建与数据库连接的方式*/
connection = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/ssm?characterEncoding=utf8&useSSL=false&serverTimezone=Asia/Shanghai", "root", "root");
ps = connection.prepareStatement("select id,account,password from t_student where account=?and password=?");
ps.setString(1, account);
ps.setString(2, password);
resultSet = ps.executeQuery();
if (resultSet.next()) {
user = new User();
user.setId(resultSet.getInt("id"));
user.setAccount(resultSet.getString("account"));
user.setPassword(resultSet.getString("password"));
}
} finally {
if (connection != null) {
connection.close();
}
if (ps != null) {
ps.close();
}
if (resultSet != null) {
resultSet.close();
}
}
return user;
}
}
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Frameset//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-frameset.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>信息管理系统界面</title>
<link href="css/style.css" rel="stylesheet" type="text/css" />
<script src="js/jquery-1.8.3.min.js" type="text/javascript" charset="utf-8"></script>
<style type="text/css">
*{
margin: 0px;
padding: 0px;
}
</style>
<script type="text/javascript">
$(function () {
var str=window.sessionStorage.getItem("user");
if(str==null){
location.replace("login.html");
return;
}
var user=$.parseJSON(str);
$("#account").html(user.account);
})
</script>
</head>
<body>
<table border="1" cellspacing="0" cellpadding="0" width="100%" height="100%">
<tr style="background:url(images/topbg.gif) repeat-x;">
<td colspan="2">
<div class="topleft">
<img src="images/logo.png" title="系统首页" />
</div>
<div class="topright">
<ul>
<li><span><img src="images/help.png" title="帮助" class="helpimg"/></span><a href="#">帮助</a></li>
<li><a href="#">关于</a></li>
<li><a href="login.html" target="_parent">退出</a></li>
</ul>
<div class="user">
<span id="account"></span>
</div>
</div>
</td>
</tr>
<tr>
<td width="187" valign="top" height="100%" style="background:#f0f9fd;">
<div class="lefttop"><span></span>操作菜单</div>
<dl class="leftmenu">
<dd>
<div class="title">
<span><img src="images/leftico01.png" /></span>
<a href="">管理信息</a>
</div>
<ul class="menuson">
<li><cite></cite>
<a href="student/list.html" target="rightFrame">信息管理</a>
</li>
</ul>
</dd>
</dl>
</td>
<td>
<iframe name="rightFrame" src="student/list.html" width="100%" height="600"></iframe>
</td>
</tr>
</table>
</body>
</html>
中间通过Student类当做传输信息的媒介,将信息集合进行传输
package com.qn.FProgram.Mode;
import java.util.Date;
public class Student {
private int id;
private int number;
private String name;
private String sex;
private String grade;
private int telephone;
private String address;
private String user;
private Date oper_time;
private int gradeId;
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public int getNumber() {
return number;
}
public void setNumber(int number) {
this.number = number;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getSex() {
return sex;
}
public void setSex(String sex) {
this.sex = sex;
}
public String getGrade() {
return grade;
}
public void setGrade(String grade) {
this.grade = grade;
}
public int getTelephone() {
return telephone;
}
public void setTelephone(int telephone) {
this.telephone = telephone;
}
public String getAddress() {
return address;
}
public void setAddress(String address) {
this.address = address;
}
public String getUser() {
return user;
}
public void setUser(String user) {
this.user = user;
}
public Date getOper_time() {
return oper_time;
}
public void setOper_time(Date oper_time) {
this.oper_time = oper_time;
}
public int getGradeId() {
return gradeId;
}
public void setGradeId(int gradeId) {
this.gradeId = gradeId;
}
@Override
public String toString() {
return "Student{" +
"id=" + id +
", number=" + number +
", name='" + name + ''' +
", sex='" + sex + ''' +
", grade='" + grade + ''' +
", telephone=" + telephone +
", address='" + address + ''' +
", user='" + user + ''' +
", oper_time=" + oper_time +
'}';
}
}
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>Title</title>
<link href="../css/style.css" rel="stylesheet" type="text/css" />
<script src="../js/jquery-1.8.3.min.js" type="text/javascript" charset="utf-8"></script>
<script>
$(function(){
$.get("../back/student",{mark:"gradelist"},function (res){
if(res==201){
window.parent.location.replace("../login.html");
}else if(res==500) {
alert("服务器忙");
}else{
var str="";
for(i=0;i<res.length;i++){
str+="<option value='"+res[i].id+"'>"+res[i].grade+"</option>";
}
$("#gradeid").append(str);
}
},"json");
})
//验证学号
function checkNum(number) {
var result=true;
if (number.length==0){
return ;
}
$.ajax({
url:"../back/student",
type:"get",
data:{number:number,mark:"checkNum"},
async:false,
success:function (res) {
if (res==1){
$("#msgid").html("学号已注册,再换一个吧");
result = false;
}else if(res==0){
$("#msgid").html("学号未使用,可以注册");
result = true;
}else {
$("#msgid").html("服务器忙");
result = false;
}
}
})
return result;
}
function save(){
var result=checkNum($("#number").val());
console.log(result);
if(result){
$.post("../back/student",$("#formid").serialize(),function(res){
if (res==201){
alert("服务器出错");
}else if(res==500){
alert("保存出错");
}else{
alert("保存成功");
location.assign("../student/list.html");
}
})}
}
</script>
</head>
<body>
<form id="formid">
<div class="formbody">
<div class="formtitle"><span>基本信息</span></div>
<ul class="forminfo">
<li>
<label>学号:</label>
<input type="text" class="dfinput" name="number" id="number" onblur="checkNum(this.value)">
<br/>
<span id="msgid"></span></td>
</li>
<li>
<label>姓名:</label>
<input type="text" class="dfinput" name="name" id="nameid" />
</li>
<li>
<label>性别:</label>
<input type="radio" name="sex" value="男">男
<input type="radio" name="sex" value="女">女
</li>
<li>
<label>年级:</label>
<select id="gradeid" name="gradeid" class="dfinput"></select>
</li>
<li>
<label>电话:</label>
<input type="text" name="telephone" class="dfinput">
</li>
<li>
<label>家庭地址:</label>
<input type="text" name="address" id="address" class="dfinput">
</li>
<li>
<input type="button" value="保存" class="btn" onclick="save()">
</li>
</ul>
</div>
</form>
</body>
</html>
其中包含List.html界面,我们将利用内联框架嵌入式main.html中
<!DOCTYPE html>
<html lang="en">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>无标题文档</title>
<link href="../css/style.css" rel="stylesheet" type="text/css" />
<script src="../js/jquery-1.8.3.min.js" type="text/javascript" charset="utf-8"></script>
<script>
$(function(){
$.get("../back/student",{mark:"list"},function(res) {
if(res==201){
window.parent.location.replace("../login.html");
}else if(res==500) {
alert("服务器忙");
}
else{
var str = "";
for (var i = 0; i < res.length; i++){
str += "<tr align='center'>";
str += "<td>" + (i + 1) + "</td>";
str += "<td>" + res[i].name + "</td>";
str += "<td>" + res[i].sex + "</td>";
str += "<td>" + res[i].grade+ "</td>";
str += "<td>"+res[i].telephone+ "</td>";
str += "<td>"+res[i].address+ "</td>";
str += "<td>"+res[i].user+ "</td>";
str += "<td>"+(new Date(res[i].oper_time).toLocaleString())+ "</td>";
str += "<td><a href='javaScript:void(0)' οnclick='update("+res[i].id+")'>修改</a>"+" "+
"<a href='javaScript:void(0)' οnclick='del("+res[i].id+")'>删除</a></td>";
str += "</tr>";
}
$("#tableid").append(str);
}
},"json");
})
function add(){
location.assign("Add.html");
}
function del(id) {
if (confirm("您确定删除?")){
$.get("../back/student",{mark:"del",id:id},function(res) {
if (res==201){
alert(登录失效);
}else if(res==200){
alert("删除成功");
location.reload();
}else{
alert("删除失败");
}
})
}
}
function update(id) {
location.assign("Update.html?id="+id);
console.log(id)
}
</script>
</head>
<body>
<div class="rightinfo">
<!-- 顶部按钮 -->
<div class="tools">
<ul class="toolbar">
<a href="../student/Add.html">
<li class="click">
<span><img src="../images/t01.png" /></span>
添加
</li>
</a>
</ul>
</div>
<!--数据列表 -->
<table class="tablelist" id="tableid">
<tr>
<th>编号<i class="sort"><img src="../images/px.gif" /></i></th>
<th>姓名</th>
<th>性别</th>
<th>年级</th>
<th>电话</th>
<th>地址</th>
<th>操作人</th>
<th>操作时间</th>
<th>操作</th>
</tr>
</table>
</div>
</body>
</html>
```java
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>Title</title>
</head>
<link href="../css/style.css" rel="stylesheet" type="text/css" />
<script src="../js/jquery-1.8.3.min.js" type="text/javascript" charset="utf-8"></script>
<script>
$(function(){
//获取路径中?后的参数
var id=(location.search).split("=")[1];
//页面打开后,自动向servlet发送请求
$(function(){
$.get("../back/student",{mark:"gradelist"},function (res){
if(res==201){
window.parent.location.replace("../login.html");
}else if(res==500) {
alert("服务器忙");
}else{
var str="";
for(i=0;i<res.length;i++){
str+="<option value='"+res[i].id+"'>"+res[i].grade+"</option>";
}
$("#gradeid").append(str);
}
},"json");
})
//根据学生id查询学生信息
$.get("../back/student",{mark:"findStudent",id:id},function (res){
if(res==201){
window.parent.location.replace("../login.html");
}else if(res==500) {
alert("服务器忙");
}
else{
console.log(res);
$("input[name='number']").val(res.number);
$("input[name='name']").val(res.name);
if(res.sex=='男'){
$("input[value='男']").attr("checked",true);
}else{
$("input[value='女']").attr("checked",true);
}
$("input[name='telephone']").val(res.telephone);
$("input[name='address']").val(res.address);
$("input[name='id']").val(res.id);
}
},"json");
})
function save(){
$.post("../back/student",$("#formid",).serialize(),function(res){
if (res==201){
alert("服务器出错");
}else if(res==500){
alert("保存出错");
}else{
alert("保存成功");
location.assign("../student/list.html");
}
})
}
</script>
<body>
<form id="formid">
<div class="formbody">
<div class="formtitle"><span>基本信息</span></div>
<input type="hidden" name="id">
<ul class="forminfo">
<li>
<label>学号:</label>
<input type="text" class="dfinput" name="number" id="numid">
<span id="msgid"></span></td>
</li>
<li>
<label>姓名:</label>
<input type="text" class="dfinput" name="name" id="nameid" />
</li>
<li>
<label>性别:</label>
<input type="radio" name="sex" value="男">男
<input type="radio" name="sex" value="女">女
</li>
<li>
<label>年级:</label>
<select id="gradeid" name="gradeid" class="dfinput"></select>
</li>
<li>
<label>电话:</label>
<input type="text" name="telephone" class="dfinput">
</li>
<li>
<label>家庭地址:</label>
<input type="text" name="address" id="address" class="dfinput">
</li>
<li>
<input type="button" value="修改" class="btn" onclick="save()">
</li>
</ul>
</div>
</form>
</body>
</html>
package com.qn.FProgram.Servlet;
import com.google.gson.Gson;
import com.qn.FProgram.Dao.StudentDao;
import com.qn.FProgram.Mode.Grade;
import com.qn.FProgram.Mode.Student;
import com.qn.FProgram.Mode.User;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.sql.SQLException;
import java.util.List;
public class StudentServlet extends HttpServlet {
@Override
protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
String mark=req.getParameter("mark");
if(mark.equals("list")){
PrintWriter out=null;
try{
resp.setContentType("text/html;charset=utf-8");
out=resp.getWriter();
//查询学生信息列表
StudentDao studentDao=new StudentDao();
List<Student> studentList = studentDao.findStudentList();//返回学生集合
out.print(new Gson().toJson(studentList));//转为字符串进行输出
System.out.println(new Gson().toJson(studentList));
}
catch (Exception e){
e.printStackTrace();
out.print(500);
}
}else if(mark.equals("gradelist")){
PrintWriter out=null;
try{
resp.setContentType("text/html;charset=utf-8");
out= resp.getWriter();
StudentDao studentDao1=new StudentDao();
List<Grade>gradeList= studentDao1.findGradeList();
out.print(new Gson().toJson(gradeList));
}
catch(Exception e){
e.printStackTrace();
out.print(500);
}
}else if(mark.equals("del")){
PrintWriter out=null;
try{
resp.setContentType("text/html;charset=utf-8");
out= resp.getWriter();
String id=req.getParameter("id");
StudentDao studentDao2=new StudentDao();
studentDao2.deleteStudent(id);
out.print(200);
}
catch(Exception e){
e.printStackTrace();
out.print(500);
}
}
else if(mark.equals("checkNum")){
PrintWriter out=null;
try{
resp.setContentType("text/html;charset=utf-8");
out= resp.getWriter();
String number=req.getParameter("number");
StudentDao studentDao3=new StudentDao();
int i= studentDao3.checkNum(number);
out.print(i);
}
catch(Exception e){
e.printStackTrace();
out.print(500);
}
}
else if(mark.equals("findStudent")){
PrintWriter out=null;
try{
resp.setContentType("text/html;charset=utf-8");
out= resp.getWriter();
String id=req.getParameter("id");
StudentDao studentDao4=new StudentDao();
Student student=studentDao4.findStudentByID(id);
out.print(new Gson().toJson(student));
}
catch(Exception e){
e.printStackTrace();
out.print(500);
}
}
}
@Override
protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
PrintWriter out = null;
try {
String number = req.getParameter("number");
String id = req.getParameter("id");
String name = req.getParameter("name");
String sex = req.getParameter("sex");
String address = req.getParameter("address");
String telephone = req.getParameter("telephone");
String gradeid = req.getParameter("gradeid");
User user = (User) req.getSession().getAttribute("user");//获得登录的用户
StudentDao addDao = new StudentDao();
if (id == null) {
addDao.save(number, name, sex, address, telephone, gradeid, user.getId());
}else{
addDao.updateStudent(id, name, sex, address, telephone, gradeid, user.getId());
}
}
catch (Exception e) {
e.printStackTrace();
out.print(500);
}
}
}
package com.qn.FProgram.Dao;
import com.qn.FProgram.Mode.Grade;
import com.qn.FProgram.Mode.Student;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
public class StudentDao {
public List<Student>findStudentList() throws ClassNotFoundException, SQLException {
Connection connection = null;
PreparedStatement pt = null;
ResultSet resultSet = null;
List<Student>list=new ArrayList<>();
try {
Class.forName("com.mysql.jdbc.Driver");
connection = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/ssm?characterEncoding=utf8&useSSL=false&serverTimezone=Asia/Shanghai", "root", "root");
pt = connection.prepareStatement("SELECT n" +
"s.id,n" +
"s.num,n" +
"s.name,n" +
"s.sex,n" +
"s.address,n" +
"s.phone,n" +
"g.gradename,n" +
"u.account,n" +
"s.oper_timen" +
"FROM student s LEFT JOIN grade g ON s.gradeid=g.idn" +
" LEFT JOIN USER u ON s.userid=u.idn" +
"n");
resultSet = pt.executeQuery();
while(resultSet.next()){
Student student=new Student();
student.setId(resultSet.getInt("id"));
student.setNumber(resultSet.getInt("num"));
student.setName(resultSet.getString("name"));
student.setSex(resultSet.getString("sex"));
student.setAddress(resultSet.getString("address"));
student.setTelephone(resultSet.getInt("phone"));
student.setGrade(resultSet.getString("gradename"));
student.setUser(resultSet.getString("account"));
student.setOper_time(resultSet.getTimestamp("oper_time"));
list.add(student);
}
} finally {
if (connection != null) {
connection.close();
}
if (pt != null) {
pt.close();
}
if (resultSet != null) {
resultSet.close();
}
}
return list;
}
public List<Grade>findGradeList() throws ClassNotFoundException, SQLException {
Connection connection = null;
PreparedStatement pt = null;
ResultSet resultSet = null;
List<Grade>list=new ArrayList<>();
try {
Class.forName("com.mysql.jdbc.Driver");
connection = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/ssm?characterEncoding=utf8&useSSL=false&serverTimezone=Asia/Shanghai", "root", "root");
pt = connection.prepareStatement("SELECT id,gradename FROM grade");
resultSet = pt.executeQuery();
while(resultSet.next()){
Grade grade=new Grade();
grade.setId(resultSet.getInt("id"));
grade.setGrade(resultSet.getString("gradename"));
list.add(grade);
}
} finally {
if (connection != null) {
connection.close();
}
if (pt != null) {
pt.close();
}
if (resultSet != null) {
resultSet.close();
}
}
return list;
}
public void save(String number, String name, String sex, String address, String telephone, String gradeid, int userid) throws ClassNotFoundException, SQLException {
Connection connection=null;
PreparedStatement ps=null;
Class.forName("com.mysql.jdbc.Driver");
connection = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/ssm?characterEncoding=utf8&useSSL=false&serverTimezone=Asia/Shanghai", "root", "root");
ps=connection.prepareStatement("insert into student(num,name,sex,address,phone,gradeid,userid,oper_time)values(?,?,?,?,?,?,?,now())");
ps.setString(1,number);
ps.setString(2,name);
ps.setString(3,sex);
ps.setString(4,address);
ps.setString(5,telephone);
ps.setString(6,gradeid);
ps.setObject(7,userid);
ps.executeUpdate();
if (connection != null) {
connection.close();
}
if (ps != null) {
ps.close();
}
}
public void deleteStudent(String id) throws ClassNotFoundException, SQLException {
Connection connection = null;
PreparedStatement pt = null;
try {
Class.forName("com.mysql.jdbc.Driver");
connection = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/ssm?characterEncoding=utf8&useSSL=false&serverTimezone=Asia/Shanghai", "root", "root");
pt = connection.prepareStatement("delete from student where id= ?");
pt.setObject(1, id);
pt.executeUpdate();
} finally {
if (connection != null) {
connection.close();
}
if (pt != null) {
pt.close();
}
}
}
public int checkNum(String number) throws ClassNotFoundException, SQLException {
Connection connection = null;
PreparedStatement pt = null;
ResultSet resultSet = null;
try {
Class.forName("com.mysql.jdbc.Driver");
connection = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/ssm?characterEncoding=utf8&useSSL=false&serverTimezone=Asia/Shanghai", "root", "root");
pt = connection.prepareStatement("select count(*) from student where num=?");
pt.setString(1, number);
resultSet=pt.executeQuery();
resultSet.next();
return resultSet.getInt(1);
} finally {
if (connection != null) {
connection.close();
}
if (pt != null) {
pt.close();
}if (resultSet!=null){
resultSet.close();
}
}
}
public Student findStudentByID(String id) throws ClassNotFoundException, SQLException {
Connection connection = null;
PreparedStatement pt = null;
ResultSet resultSet = null;
Student student=new Student();
try {
Class.forName("com.mysql.jdbc.Driver");
connection = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/ssm?characterEncoding=utf8&useSSL=false&serverTimezone=Asia/Shanghai", "root", "root");
pt = connection.prepareStatement(" SELECT id,num,NAME,sex,address,phone,gradeid FROM student s WHERE id=? ");
pt.setObject(1, id);
resultSet = pt.executeQuery();
while (resultSet.next()) {
student.setId(resultSet.getInt("id"));
student.setNumber(resultSet.getInt("num"));
student.setName(resultSet.getString("name"));
student.setSex(resultSet.getString("sex"));
student.setAddress(resultSet.getString("address"));
student.setTelephone(resultSet.getInt("phone"));
student.setGradeId(resultSet.getInt("gradeid"));
}
} finally {
if (connection != null) {
connection.close();
}
if (pt != null) {
pt.close();
}
if (resultSet != null) {
resultSet.close();
}
}
return student;
}
public void updateStudent(String id, String name, String sex, String address, String telephone, String gradeid, int userid)throws ClassNotFoundException, SQLException {
Connection connection = null;
PreparedStatement pt = null;
try {
Class.forName("com.mysql.jdbc.Driver");
connection = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/ssm?characterEncoding=utf8&useSSL=false&serverTimezone=Asia/Shanghai", "root", "root");
pt = connection.prepareStatement(" UPDATE student SET NAME=?,sex=?,address=?,phone=?,gradeid=?,userid=?,oper_time=NOW() WHERE id= ?");
pt.setObject(1, name);
pt.setObject(2, sex);
pt.setObject(3, address);
pt.setObject(4, telephone);
pt.setObject(5, gradeid);
pt.setObject(6, userid);
pt.setObject(7, id);
pt.executeUpdate();
} finally {
if (connection != null) {
connection.close();
}
if (pt != null) {
pt.close();
}
}
}
}
进行更新时将自动查询所在人的年级,为方便我们将搭建一个Grade类
package com.qn.FProgram.Mode;
public class Grade {
private int id;
private String grade;
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getGrade() {
return grade;
}
public void setGrade(String grade) {
this.grade = grade;
}
}
CREATE TABLE USER(
id INT PRIMARY KEY AUTO_INCREMENT,
account VARCHAR(10) UNIQUE NOT NULL,
PASSWORD VARCHAR(20) NOT NULL,
reg_time DATETIME
)
CREATE TABLE grade(
id INT PRIMARY KEY AUTO_INCREMENT,
NAME CHAR(10) NOT NULL
)
ALTER TABLE student ADD CONSTRAINT fk_userid FOREIGN KEY(userid) REFERENCES USER(id)
ALTER TABLE student ADD CONSTRAINT fk_gradeid FOREIGN KEY(gradeid) REFERENCES grade(id)
通过此次实践我们可以明显感觉到代码量的巨大与繁琐,在后续我们将学习新的框架部分代码对总结构进行优化还有对新功能的拓展,尽请期待,再见!
以上是脚本宝典为你收集整理的WEB应用程序开发---实践开发(MySQL+JavaJDBC+Ajax+JavaScript+Jquery)全部内容,希望文章能够帮你解决WEB应用程序开发---实践开发(MySQL+JavaJDBC+Ajax+JavaScript+Jquery)所遇到的问题。
本图文内容来源于网友网络收集整理提供,作为学习参考使用,版权属于原作者。
如您有任何意见或建议可联系处理。小编QQ:384754419,请注明来意。