第十八部分_使用Servlet、JSP與JDBC實現資料庫操縱

標籤：

使用Servlet、JSP與JDBC實現資料庫操縱——JDBC串連MySQL執行個體：

• 將MySQL資料庫驅動的包（我的驅動mysql-connector-java-5.1.34-bin.jar，需單獨下載）放在WEB-INF->lib目錄下  
• 修改Tomcat解壓目錄下的conf下的server.xml，配置當前應用上下文
• 建立新資料庫mydb（MySql中內建一個名為test的資料庫，因此不能用test這個名字）
• 建立資料庫表users，欄位為：id（primary key），username，password，truename，birthday，registerdate，sex，interest，remark

準備工作：cmd->net start，查看如果MySql沒有啟動網路服務的話，手動啟動它。

命令列下：

• mysql -uroot -proot
• create database mydb;
• use mydb;
• show tables;

使用Navicat建立表

navicat使用攻略： 點擊[串連]，填寫一個合適的串連名，然後切換到[進階]選項卡，在[使用進階串連]前打鉤，選擇一個非系統資料庫，填寫使用者名稱和密碼即可。

利用這個可視化的工具，可以方便的更改表結構和表資料 點擊[查詢]->[建立查詢]可以執行SQL語句。

更改字型大小： 工具->選項中可更改字型屬性。

步入主題：建立一個表，命名為users，欄位為：id（primary key），username，password，truename，birthday，registerdate，sex，interest，remark，設定相應的類型，id為int，設為主鍵不可為空（not null），設定為自動增加，username、password、truename均為varchar(20)，這裡一定不要忘記指定長度，否則用JDBC往資料庫中添加資訊拋異常，直接在資料庫中插入記錄則報錯不能完成，birthday、registerdate均為datetime，sex為tinyint，interest為varchar(20)，remark為text或者mediumtext。

查看錶訊息中的DDL如下:

CREATE TABLE `users` (  `id` int(11) NOT NULL AUTO_INCREMENT,  `hh` varchar(0) DEFAULT NULL,  `username` varchar(20) DEFAULT NULL,  `password` varchar(20) DEFAULT NULL,  `truename` varchar(20) DEFAULT NULL,  `birthday` datetime DEFAULT NULL,  `registerdate` datetime DEFAULT NULL,  `sex` tinyint(4) DEFAULT NULL,  `interest` varchar(20) DEFAULT NULL,  `remark` mediumtext,  PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8

建立相關的類檔案：

com.test.bean包下面建立類：Users.java

com.test.db包下建立類：UsersDB.java

web目錄下建立頁面：register.jsp

com.test.servlet包下面建立Servlet:ProcessUsersServlet

com.test.filter下建立過濾器：UserFilter.java（web.xml中配置）

web目錄下建立頁面：listAllUsers.jsp；listSingleUser.jsp

使用者資訊修改留給大家去完成

com.test.bean包下的Users.java:

package com.test.bean;import java.sql.Date;public class Users{private int id;private String username;private String password;private String truename;private Date birthday;private Date registerdate;private String sex;private String interest;private String remark;public int getId(){return id;}public void setId(int id){this.id = id;}public String getUsername(){return username;}public void setUsername(String username){this.username = username;}public String getPassword(){return password;}public void setPassword(String password){this.password = password;}public String getTruename(){return truename;}public void setTruename(String truename){this.truename = truename;}public Date getBirthday(){return birthday;}public void setBirthday(Date birthday){this.birthday = birthday;}public Date getRegisterdate(){return registerdate;}public void setRegisterdate(Date registerdate){this.registerdate = registerdate;}public String getSex(){return sex;}public void setSex(String sex){this.sex = sex;}public String getInterest(){return interest;}public void setInterest(String interest){this.interest = interest;}public String getRemark(){return remark;}public void setRemark(String remark){this.remark = remark;}}

com.test.db包下的UserDB.java（關鍵，資料庫操縱）:

package com.test.db;import java.io.InputStream;import java.sql.Connection;import java.sql.DriverManager;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.util.ArrayList;import java.util.Properties;import com.test.bean.Users;/** * @author user *  * TODO 該類完成與users表相關的資料庫操作的所有方法 */public class UsersDB{private String resource = "users.properties";private Properties props = null;// 得到資料庫連接private Connection getConnection(){try{props = new Properties();InputStream in = getClass().getResourceAsStream(resource);props.load(in);String drivers = props.getProperty("jdbc.drivers");String url = props.getProperty("jdbc.url");String username = props.getProperty("jdbc.username");String password = props.getProperty("jdbc.password");Class.forName(drivers);//載入資料庫驅動return DriverManager.getConnection(url, username, password);}catch (Exception ex){ex.printStackTrace();System.out.println("串連資料庫異常發生 : " + ex.getMessage());}return null;}// 插入一筆記錄public void save(Users bean){try{Connection con = this.getConnection();String sql = "insert into users(username,password,truename,birthday,registerdate,sex,interest,remark) values(?,?,?,?,?,?,?,?)";PreparedStatement ps = con.prepareStatement(sql);ps.setString(1, bean.getUsername());ps.setString(2, bean.getPassword());ps.setString(3, bean.getTruename());ps.setDate(4, bean.getBirthday());ps.setDate(5, bean.getRegisterdate());ps.setString(6, bean.getSex());ps.setString(7, bean.getInterest());ps.setString(8, bean.getRemark());ps.executeUpdate(); //完成真正的資料庫插入con.close();}catch (Exception ex){System.out.println("增加記錄異常發生：" + ex.getMessage() + "\n");// 調試用，我自己添加的ex.printStackTrace();}}// 更新一筆記錄public void update(Users bean){try{Connection con = this.getConnection();String sql = "update users set password=?,truename=?,birthday=?,sex=?,interest=?,remark=? where id=?";PreparedStatement ps = con.prepareStatement(sql);ps.setString(1, bean.getPassword());ps.setString(2, bean.getTruename());ps.setDate(3, bean.getBirthday());ps.setString(4, bean.getSex());ps.setString(5, bean.getInterest());ps.setString(6, bean.getRemark());ps.setInt(7, bean.getId());ps.executeUpdate();//真正完成資料的更新con.close();}catch (Exception ex){System.out.println("修改記錄異常發生：" + ex.getMessage());}}// 刪除一筆記錄public void remove(int id){try{Connection con = this.getConnection();String sql = "delete from users where id=?";PreparedStatement ps = con.prepareStatement(sql);ps.setInt(1, id);ps.executeUpdate(); //完成真正的刪除con.close();}catch (Exception ex){System.out.println("刪除記錄異常發生：" + ex.getMessage());}}// 查詢一筆記錄public Users restore(int id){Users bean = null;try{Connection con = this.getConnection();String sql = "select * from users where id=?";PreparedStatement ps = con.prepareStatement(sql);ps.setInt(1, id);ResultSet rs = ps.executeQuery();if (rs.next()){bean = new Users();bean.setId(rs.getInt("id"));bean.setUsername(rs.getString("username"));bean.setPassword(rs.getString("password"));bean.setTruename(rs.getString("truename"));bean.setBirthday(rs.getDate("birthday"));bean.setRegisterdate(rs.getDate("registerdate"));bean.setSex(rs.getString("sex"));bean.setInterest(rs.getString("interest"));bean.setRemark(rs.getString("remark"));}con.close();}catch (Exception ex){System.out.println("查詢記錄異常發生：" + ex.getMessage());}return bean;}// 查詢所有記錄public ArrayList getAllUsers(){ArrayList arrayList = new ArrayList();Users bean = null;try{Connection con = this.getConnection();String sql = "select * from users";PreparedStatement ps = con.prepareStatement(sql);ResultSet rs = ps.executeQuery();while (rs.next()){bean = new Users();bean.setId(rs.getInt("id"));bean.setUsername(rs.getString("username"));bean.setPassword(rs.getString("password"));bean.setTruename(rs.getString("truename"));bean.setBirthday(rs.getDate("birthday"));bean.setRegisterdate(rs.getDate("registerdate"));bean.setSex(rs.getString("sex"));bean.setInterest(rs.getString("interest"));bean.setRemark(rs.getString("remark"));arrayList.add(bean);}con.close();}catch (Exception ex){System.out.println("查詢所有記錄異常發生：" + ex.getMessage());}return arrayList;}}

web目錄下的register.jsp:

<%@ page language="java" pageEncoding="GB2312" %><!DOCTYPE HTML PUBLIC "-//w3c//dtd html 4.0 transitional//en"><html><head><title>使用者註冊</title><script language="javascript">function validate(){with(document.form1){if(username.value.length < 1){alert("使用者名稱不可為空");return false;}if(password.value.length < 1){alert("密碼不可為空");return false;}if(password_again.value.length < 1){alert("重複密碼不可為空");return false;}if(username.value.length < 4){alert("使用者名稱長度過短");return false;}if(username.value.length > 10){alert("使用者名稱長度過長");return false;}if(password.value.length < 4){alert("密碼長度過短");return false;}if(password.value.length> 10 ){alert("密碼長度過長");return false;}if(password.value != password_again.value){alert("密碼輸入不一致");return false;}if(truename.value.length < 1){alert("真實姓名不可為空");return false;}if(birthday.value.length < 1){alert("生日不可為空");return false;}if(!sex[0].checked && !sex[1].checked){alert("性別必須選擇");return false;}var n = 0;for( i = 0 ; i < 4 ; i++){if(interest[i].checked){n++;}}if(n < 1){alert("興趣至少需要選擇一個");return false;}if(n > 3){alert("興趣最多選擇三個");return false;}if(remark.value.length < 1){alert("說明必須填寫");return false;}}return true;}</script></head><body bgcolor="#FFFFFF"><form action="/test/ProcessUsersServlet" method="post" name="form1" onSubmit="return validate();">使用者名稱：<input type="text" name="username" size="20"><br>密  碼：<input type="password" name="password" size="20"><br> 重複輸入密碼：<input type="password" name="password_again" size="20"><br>真實姓名：<input type="text" name="truename" size="20"><br> 出生日期：<input type="text" name="birthday" size="20"><br>性別：男<input type="radio" name="sex" value="0">  女<input type="radio" name="sex" value="1"><br>興趣: 足球<input type="checkbox" name="interest" value="0">  籃球<input type="checkbox" name="interest" value="1">  排球<input type="checkbox" name="interest" value="2">  羽毛球<input type="checkbox" name="interest" value="3">  <br>說明：<textarea name="remark" rows="15" cols="20"></textarea><br><input type="submit" value="點擊確認">   <input type="reset" value="重  置"><input type="hidden" name="type" value="save"></form></body></html>

com.test.servlet包下的ProcessUsersServlet：

package com.test.servlet;import java.io.IOException;import java.text.ParseException;import java.text.SimpleDateFormat;import java.util.ArrayList;import javax.servlet.ServletException;import javax.servlet.http.HttpServlet;import javax.servlet.http.HttpServletRequest;import javax.servlet.http.HttpServletResponse;import com.test.bean.Users;import com.test.db.UsersDB;public class ProcessUsersServlet extends HttpServlet{protected void doGet(HttpServletRequest request,HttpServletResponse response) throws ServletException, IOException{// TODO Method stub generated by Lombozprocess(request, response);}protected void doPost(HttpServletRequest request,HttpServletResponse response) throws ServletException, IOException{// TODO Method stub generated by Lombozprocess(request, response);}protected void process(HttpServletRequest request,HttpServletResponse response) throws ServletException, IOException{String type = request.getParameter("type");if ("save".equals(type)){String username = request.getParameter("username");String password = request.getParameter("password");String truename = request.getParameter("truename");String birthday = request.getParameter("birthday");String sex = request.getParameter("sex");String[] interest = request.getParameterValues("interest");String remark = request.getParameter("remark");java.sql.Date birthdayToDate = null;SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");try{java.util.Date date = sdf.parse(birthday);birthdayToDate = new java.sql.Date(date.getTime());}catch (ParseException e){e.printStackTrace();}String interestToString = null;StringBuffer sb = new StringBuffer();for (int i = 0; i < interest.length; i++){sb.append(interest[i]);}interestToString = sb.toString();Users bean = new Users();bean.setUsername(username);bean.setPassword(password);bean.setTruename(truename);bean.setInterest(interestToString);bean.setSex(sex);bean.setBirthday(birthdayToDate);bean.setRegisterdate(new java.sql.Date(new java.util.Date().getTime()));bean.setRemark(remark);UsersDB userDB = new UsersDB();userDB.save(bean);ArrayList arrayList = userDB.getAllUsers();request.setAttribute("all", arrayList);request.getRequestDispatcher("/listAllUsers.jsp").forward(request, response);}if ("remove".equals(type)){String id = request.getParameter("id");UsersDB userDB = new UsersDB();userDB.remove(Integer.parseInt(id));ArrayList arrayList = userDB.getAllUsers();request.setAttribute("all", arrayList);request.getRequestDispatcher("/listAllUsers.jsp").forward(request, response);}}}

com.test.filter下的過濾器：UserFilter.java

package com.test.filter;import java.io.IOException;import javax.servlet.Filter;import javax.servlet.FilterChain;import javax.servlet.FilterConfig;import javax.servlet.ServletException;import javax.servlet.ServletRequest;import javax.servlet.ServletResponse;import javax.servlet.http.HttpServletRequest;/** * @author user *  * TODO 要更改此產生的類型注釋的模板，請轉至 視窗 － 喜好設定 － Java － 代碼樣式 － 代碼模板 */public class UsersFilter implements Filter{public void init(FilterConfig config) throws ServletException{// TODO Method stub generated by Lomboz}public void doFilter(ServletRequest request, ServletResponse response,FilterChain chain) throws IOException, ServletException{// TODO Method stub generated by Lomboz((HttpServletRequest) request).setCharacterEncoding("gb2312");chain.doFilter(request, response);}public void destroy(){// TODO Method stub generated by Lomboz}public FilterConfig getFilterConfig(){// TODO Method stub generated by Lombozreturn null;}public void setFilterConfig(FilterConfig config){// TODO Method stub generated by Lomboz}}

web.xml中配置過濾器：

<filter> <filter-name>usersFilter</filter-name> <filter-class>com.test.filter.UsersFilter</filter-class> </filter>  <filter-mapping> <filter-name>usersFilter</filter-name> <url-pattern>/ProcessUsersServlet</url-pattern> </filter-mapping>

listAllUsers.jsp:

<%@ page language="java" pageEncoding="GB2312" %><%@ page import="java.util.*,com.test.bean.*"%><!DOCTYPE HTML PUBLIC "-//w3c//dtd html 4.0 transitional//en"><%ArrayList arrayList = (ArrayList)request.getAttribute("all");Users bean = null;%><html><head><title>Lomboz JSP</title></head><body bgcolor="#FFFFFF"><table width="50%" align="center" border="1"><tr><td>使用者名稱</td><td>刪除</td></tr><%for(int i = 0;i < arrayList.size(); i++){ bean = (Users)arrayList.get(i);%><tr> <td><a href="listSingleUser.jsp?id=<%= bean.getId()%>"><%= bean.getUsername()%></a></td><td><a href="/test/ProcessUsersServlet?type=remove&id=<%= bean.getId()%>" onClick="javascript:return confirm(‘您確實要刪除該記錄嗎？‘)">刪除</a></td></tr><%}%></table></body></html>

listSingleUser.jsp:

<%@ page language="java" pageEncoding="GB2312" %><%@ page import="java.util.*,com.test.bean.*,com.test.db.*"%><%String id = request.getParameter("id");UsersDB usersDB = new UsersDB();Users bean = usersDB.restore(Integer.parseInt(id));%><!DOCTYPE HTML PUBLIC "-//w3c//dtd html 4.0 transitional//en"><html><head><title>Lomboz JSP</title></head><body bgcolor="#FFFFFF"><table width="100%" align="center" align="center"><tr bgcolor="gray"><td>使用者名稱</td><td>密碼</td><td>真實姓名</td><td>生日</td><td>註冊時間</td><td>性別</td><td>愛好</td><td>說明</td></tr><tr><td><%= bean.getUsername()%></td><td><%= bean.getPassword()%></td><td><%= bean.getTruename()%></td><td><%= bean.getBirthday()%></td><td><%= bean.getRegisterdate()%></td><td><%= "0".equals(bean.getSex()) ? "男" : "女"%></td><td><%String interest = bean.getInterest();%><%= interest.indexOf("0") != -1 ? "足球" : ""%> <%= interest.indexOf("1") != -1 ? "籃球" : ""%> <%= interest.indexOf("2") != -1 ? "排球" : ""%> <%= interest.indexOf("3") != -1 ? "羽毛球" : ""%> </td><td><%= bean.getRemark()%></td></tr></table></body></html>

啟動Tomcat伺服器，瀏覽器中鍵入：http://localhost:8080/test/register.jsp，出現頁面：

下面是分別註冊兩次後的頁面，預設列出所有使用者：

點擊lisi，出現頁面：

第十八部分_使用Servlet、JSP與JDBC實現資料庫操縱