只是簡單的使用mysql、簡單的練習。
有很多待完善的地方,比如list的servlet頁面,應該判斷有沒有用戶的。
比如list.jsp 應該循環list而不是寫死
index.jsp 樣式可以再優化一下的。比如按鈕就特丑。
本文展示了一個簡單的MySQL數據庫操作練習項目,主要包含以下內容:
數據庫連接工具類(BaseDao):
提供連接MySQL數據庫的基本功能
實現SQL執行(增刪改查)和資源關閉方法
使用JDBC驅動和預處理語句
用戶實體類(User):
包含id、用戶名、密碼等基本屬性
DAO層接口及實現:
定義用戶登錄、查詢、增刪改等操作
實現具體SQL執行和結果集處理
包含UserList(登錄驗證)、ALLUser(查詢所有)、addUser(添加用戶)等方法
項目目前存在以下待改進點:
前端頁面需要優化樣式
列表頁面應實現動態循環而非硬編碼
需要增加用戶存在性判斷等邏輯驗證
代碼結構有待完善
這是一個基礎的JDBC實踐項目,展示了數據庫連接、CRUD操作的基本實現方式。
連接后端
package com.zhang.dao;import java.sql.*;//連接數據庫工具
public class BaseDao {private String url = "jdbc:mysql://127.0.0.1:3306/test?useUnicode=true&characterEncoding=UTF-8";private String username = "root";private String password = "1234";private static String driver = "com.mysql.jdbc.Driver";private Connection conn;private PreparedStatement ps;private ResultSet rs;static {try {//加載驅動Class.forName(driver);} catch (ClassNotFoundException e) {e.printStackTrace();}}//創建鏈接private void getConnection() {try {conn = DriverManager.getConnection(url, username, password);} catch (SQLException e) {e.printStackTrace();}}//執行sql//增刪改public int executeUpdate(String sql, Object... objs) {//獲得連接getConnection();try {
//select * from user where username=?and password=?ps = conn.prepareStatement(sql);if (objs != null) {for (int i = 0; i < objs.length; i++) {ps.setObject(i + 1, objs[i]);}}int i = ps.executeUpdate();close();//關閉資源return i;} catch (SQLException e) {e.printStackTrace();}return -1;}//查詢public ResultSet executeQuery(String sql,Object... objs) {getConnection();try {ps = conn.prepareStatement(sql);if (objs != null) {for (int i = 0; i < objs.length; i++) {ps.setObject(i + 1, objs[i]);}}rs = ps.executeQuery();return rs;} catch (SQLException e) {e.printStackTrace();}return null;}//關閉public void close() {try {if (rs != null) {rs.close();}if (ps != null) {ps.close();}if (conn != null) {conn.close();}} catch (SQLException e) {System.out.println("關閉出錯");}}public void test() {try {Class.forName(driver);Connection connection = DriverManager.getConnection(url, username, password);System.out.println("連接成功");} catch (ClassNotFoundException e) {e.printStackTrace();} catch (SQLException e) {e.printStackTrace();}}public static void main(String[] args) {BaseDao BaseDao = new BaseDao();BaseDao.test();}
}
實體類
public class User {private Integer id;private String username;private String password;private String name;private String addr;private String tel;
}
dao層
import com.zhang.entity.User;import java.util.List;public interface UserDao {//用戶登錄,新增用戶,刪除用戶,修改用戶。//用戶登錄User UserList(User user);//查詢所有List<User> ALLUser();//增加用戶信息int addUser(User user);//刪除用戶int delUserByID(Integer id);//修改用戶int updUserByID(Integer id);
}import com.zhang.dao.BaseDao;
import com.zhang.dao.UserDao;
import com.zhang.entity.User;import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;public class UserDaoImpl extends BaseDao implements UserDao {@Overridepublic User UserList(User user) {String sql = "select * from user where username= ? and password=?";ResultSet rs = executeQuery(sql,user.getUsername(),user.getPassword());try {User user1=null;if (rs.next()){user1= new User(rs.getInt(1),rs.getString(2),rs.getString(3),rs.getString(4),rs.getString(5),rs.getString(6));}close();return user1;} catch (SQLException e) {e.printStackTrace();}return null;}@Overridepublic List<User> ALLUser() {String sql = "select * from user";ResultSet rs = executeQuery(sql);List list=new ArrayList();try {while (rs.next()){list.add(new User(rs.getInt(1),rs.getString(2),rs.getString(3),rs.getString(4),rs.getString(5),rs.getString(6)));}close();} catch (SQLException e) {e.printStackTrace();}return list;}@Overridepublic int addUser(User user) {String sql = " insert into user(id,username,password,name,addr,tel) values(?,?,?,?,?,?);";int i = this.executeUpdate(sql,user.getId(),user.getUsername(),user.getPassword(),user.getName(),user.getAddr(),user.getTel());return i ;}@Overridepublic int delUserByID(Integer id) {return 0;}@Overridepublic int updUserByID(Integer id) {return 0;}public static void main(String[] args) {
// UserDaoImpl UserDaoImpl=new UserDaoImpl();
//// System.out.println(UserDaoImpl.ALLUser());
// List<User> users = UserDaoImpl.ALLUser();}}
service層
import com.zhang.entity.User;import java.util.List;public interface UserService {User UserList(User user);//增加用戶信息boolean addUser(User user);//刪除用戶boolean delUserByID(Integer id);//修改用戶boolean updUserByID(Integer id);//查詢所有List<User> ALLUser();
}import com.zhang.dao.UserDao;
import com.zhang.dao.impl.UserDaoImpl;
import com.zhang.entity.User;
import com.zhang.service.UserService;import java.util.List;public class UserServceImpl implements UserService {UserDao userDao=new UserDaoImpl();@Overridepublic User UserList(User user) {return userDao.UserList(user);}@Overridepublic boolean addUser(User user) {return userDao.addUser(user)>0?true:false;}@Overridepublic boolean delUserByID(Integer id) {return false;}@Overridepublic boolean updUserByID(Integer id) {return false;}@Overridepublic List<User> ALLUser() {return userDao.ALLUser();}
}
servlet層
import com.zhang.entity.User;
import com.zhang.service.UserService;
import com.zhang.service.impl.UserServceImpl;import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;@WebServlet("/addServlet")
public class AddServlet extends HttpServlet {UserService userService=new UserServceImpl();protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {String username = request.getParameter("username");String password = request.getParameter("password");String name = request.getParameter("name");String addr = request.getParameter("addr");String tel = request.getParameter("tel");boolean b = userService.addUser(new User(null, username, password, name, addr, tel));response.sendRedirect("./list.jsp");}protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {}
}import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;@WebServlet("/listServlet")
public class ListServlet extends HttpServlet {protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {System.out.println(123);response.sendRedirect("./list.jsp");}protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {}
}import com.zhang.entity.User;
import com.zhang.service.UserService;
import com.zhang.service.impl.UserServceImpl;import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.util.List;@WebServlet("/loginServlet")
public class LoginServlet extends HttpServlet {UserService userService=new UserServceImpl();protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {String username = request.getParameter("username");String password = request.getParameter("password");System.out.println(username+password);User user = userService.UserList(new User(username,password));List<User> list = userService.ALLUser();if(user!=null){//登陸成功request.getSession().setAttribute("user",user);request.getSession().setAttribute("list",list);response.sendRedirect("./list.jsp");}else {response.sendRedirect("./err.jsp");}}protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {}
}
web.xml
<?xml version="1.0" encoding="UTF-8"?>
<web-app xmlns="http://xmlns.jcp.org/xml/ns/javaee"xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"xsi:schemaLocation="http://xmlns.jcp.org/xml/ns/javaee http://xmlns.jcp.org/xml/ns/javaee/web-app_4_0.xsd"version="4.0">
</web-app>
前端頁面
<!-- add.jsp --><%@ page contentType="text/html;charset=UTF-8" language="java" %>
<html>
<head><title>添加</title>
</head>
<body>
<form action="./addServlet" method="post"><input type="text" name="id" hidden><br>username:<input type="text" name="username"><br>password:<input type="text" name="password"><br>name:<input type="text" name="name"><br>addr:<input type="text" name="addr"><br>tel:<input type="text" name="tel"><br><input type="submit" value="提交">
</form>
</body>
</html><!-- err.jsp -->
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<html>
<head><title>Title</title>
</head>
<body>
<h1>錯誤</h1>
</body>
</html><!-- index.jsp -->
<%@ page contentType="text/html;charset=UTF-8" language="java" %><html>
<head><meta http-equiv="Content-Type" content="text/html; charset=UTF-8"><title>Brook系統</title><script type="text/javascript" src="js/jquery-3.4.1.min.js"></script><style type="text/css">body {background-color:#00b38a;text-align:center;}.lp-login {position:absolute;width:500px;height:300px;top:50%;left:50%;margin-top:-250px;margin-left:-250px;background: #fff;border-radius: 4px;box-shadow: 0 0 10px #12a591;padding: 57px 50px 35px;box-sizing: border-box}.lp-login .loginBtn {display:block;text-decoration:none;height: 48px;width: 150px;line-height: 48px;font-size: 16px;color: #fff;text-align: center;background-image: -webkit-gradient(linear, left top, right top, from(#09cb9d), to(#02b389));background-image: linear-gradient(90deg, #09cb9d, #02b389);border-radius: 3px}input[type='text'] {height:30px;width:250px;}span {font-style: normal;font-variant-ligatures: normal;font-variant-caps: normal;font-variant-numeric: normal;font-variant-east-asian: normal;font-weight: normal;font-stretch: normal;font-size: 14px;line-height: 22px;font-family: "Hiragino Sans GB", "Microsoft Yahei", SimSun, Arial, "Helvetica Neue", Helvetica;}</style><script type="text/javascript">$(function(){$(".loginBtn").bind("click",function(){let username = $("#username").val();let password = $("#password").val();if(username == null){alert("請輸入用戶名");return;}$.ajax({url:'resume/login',type:'POST', //GETasync:false, //或false,是否異步data:{userid:username,password:password},timeout:5000, //超時時間dataType:'json', //返回的數據格式:json/xml/html/script/jsonp/textsuccess:function(data){alert(data.message);if (data.code == '0') {window.location.href = 'list.jsp';}},failure:function (data) {}})})})</script>
</head>
<body><form action="./loginServlet" method="post"><table class="lp-login"><tr><td align="right"><span>用戶名</span></td><td align="center"><input type="text" id="username" name="username" placeholder="username"></input></td></tr><tr><td align="right"><span>密碼</span></td><td align="center"><input type="text" id="password" name="password" placeholder="password"></input></td></tr><tr align="center"><td colspan="2"><a class="loginBtn"><input type="submit" value="登陸"></a></td></tr></table>
</form></body>
</html><!-- list.jsp -->
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<%@ page import="com.zhang.entity.User" %>
<%@ page import="java.util.List" %>
<%@ page import="com.zhang.service.UserService" %>
<%@ page import="com.zhang.service.impl.UserServceImpl" %>
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<html lang="en"><head><title>簡歷列表</title></head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<style type="text/css">table, td{font:100% '微軟雅黑';}table{width:80%;border-collapse:collapse; margin:0 0 0 100px}th, td{text-align:center;border:1px solid #fff;}th{background:#328aa4}td{background:#e5f1f4;}
</style>
<script type="text/javascript" src="js/jquery-3.4.1.min.js"></script><script type="text/javascript">$(function(){loadResumeListData();let tds = $("td");tds.click(tdClick);});//添加點擊事件function addClickEvent() {//第一行綁定添加事件$("#add_btn").bind("click", function() {addNewLine(this);})//第一行綁定編輯事件$("#edit_btn").bind("click", function() {saveData(this);})//第一行綁定刪除事件$("#delete_btn").bind("click", function() {deleteData();})}//刪除數據function deleteData() {if(window.confirm("您確定要刪除數據嗎?")) {let id = $('table tr').eq(1).find("td").eq(0).text();$.ajax({url:'resume/delete',type:'POST',async:false,data:{id: id},timeout:5000, //超時時間dataType:'json', //返回的數據格式:json/xml/html/script/jsonp/textsuccess:function(data){loadResumeListData();},failure:function (data) {}})}}function saveData(saveBtn) {let currLine = $(saveBtn).parent().parent().prevAll().length + 1;let id = $('table tr').eq(currLine).find("td").eq(0).text();let name = $('table tr').eq(currLine).find("td").eq(1).text();let address = $('table tr').eq(currLine).find("td").eq(2).text();let phone = $('table tr').eq(currLine).find("td").eq(3).text();$.ajax({url:'resume/update',type:'POST',async:false,data:{id: id,name: name,address: address,phone: phone},timeout:5000, //超時時間dataType:'json', //返回的數據格式:json/xml/html/script/jsonp/textsuccess:function(data){alert("保存成功!");loadResumeListData();},failure:function (data) {}})}//給表格添加點擊事件,使表格可編輯function tdClick(){let tdnode = $(this);let tdtext = tdnode.text();if (tdtext == '修改 刪除' || tdtext == '新建') {return;}tdnode.html("");let input = $("<input>");input.val(tdtext);input.keyup(function(event){let myEvent = event || window.event;let keyCode = myEvent.keyCode;//判斷是否按下Entry鍵if(keyCode == 13) {let inputnode = $(this);let inputtext = inputnode.val();let td = inputnode.parent();td.html(inputtext);td.click(tdClick);}//判斷是否按下ESC鍵if(keyCode == 27) {$(this).parent().html(tdtext);$(this).parent().click(tdClick);}});tdnode.append(input);tdnode.children("input").trigger("select");//輸入框失去焦點,所執行的方法input.blur(function() {tdnode.html($(this).val());tdnode.click(tdClick);});tdnode.unbind("click");}<!--請求列表數據-->function loadResumeListData() {$.ajax({url:'resume/findList',type:'POST',async:false,data:{},timeout:5000, //超時時間dataType:'json', //返回的數據格式:json/xml/html/script/jsonp/textsuccess:function(data){refreshList(data);addClickEvent();},failure:function (data) {}})}<!--刷新列表-->function refreshList(data) {let str1 = "";$("#resumeBody").html("");for(let i = 0; i<data.length; i++) {str1 = "<tr>" +"<td id=\"id\">" + data[i].id + "</td>" +"<td id=\"name\">" + data[i].name + "</td>" +"<td id=\"address\">" + data[i].address + "</td>" +"<td id=\"phone\">" + data[i].phone + "</td>" +"<td>" + "<a href=\"#\" id=\"edit_btn\">修改</a>" + " " +"<a href=\"#\" id=\"delete_btn\">刪除</a>" +"</td>" +"</tr>";$("#resumeBody").append(str1);}}<!--添加一行-->function addNewLine() {let str1 = "";str1 = "<tr>" +"<td id=\"id\">" + "</td>" +"<td id=\"address\">" + "</td>" +"<td id=\"name\">" + "</td>" +"<td id=\"phone\">" + "</td>" +"<td>" + "<a href=\"#\" id=\"addNew_btn\">新建</a>" +"</td>" +"</tr>";$("#resumeBody").append(str1);let tds = $("td");tds.click(tdClick);$("#addNew_btn").bind("click", function() {saveData(this);})}
</script><!--繪制表格-->
<body>
<a href="./add.jsp" style ="margin:100px" id="add_btn">新增</a>
<form action=""></form>
<table id="tb"><c:forEach items="list"><tr id="listTable"><th style="width:100px" >ID</th><th style="width:100px" >姓名</th><th style="width:100px" >地址</th><th style="width:100px" >電話</th>
<%-- <th style="width:100px" >功能</th>--%></tr><th style="width:100px" > ${list.get(0).id}</th><th style="width:100px" > ${list.get(0).username}</th><th style="width:100px" > ${list.get(0).addr}</th><th style="width:100px" > ${list.get(0).tel}</th><br><th style="width:100px" > ${list.get(1).id}</th><th style="width:100px" > ${list.get(1).username}</th><th style="width:100px" > ${list.get(1).addr}</th><th style="width:100px" > ${list.get(1).tel}</th><br><tbody id="resumeBody"></tbody>
</table></c:forEach><%
// UserService userService=new UserServceImpl();
// List<User> list = userService.ALLUser();
// out.println(list.get(0));
// for (User user: list){
// out.println(user);
// out.println();
// }
%>
</body>
</html>