关键在于引入MySQL的驱动包,还有引入在jsp中用到的jstl包,流程如下:
1.建立数据库
CREATE TABLE `userinfo` (
`id` int(255) NOT NULL auto_increment,
`name` varchar(20) default NULL,
`age` varchar(4) default NULL,
`city` varchar(20) default NULL,
`remark` varchar(25) default NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=20 DEFAULT CHARSET=utf8;
2.建立web项目
**如果不会配置serlvet那么可以选择Java EE version2.5以前的版本,2.5以前的版本自动配置servlet**
3.在src文件夹下建立com包和servlet包,引入MySQL的驱动包
****引入MySQL的驱动包*******vv
4.创建数据库连接类 DBUtils.java(建立在com包中)
package com;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
public class DBUtils {
String user ="root";
String password ="123456";
Connection conn = null;
private static final String URI = "jdbc:mysql://localhost:3333/test" ;
public Connection getCon() throws SQLException{
try {
Class.forName("com.mysql.jdbc.Driver");
conn=DriverManager.getConnection(URI,user,password);
return conn ;
} catch (ClassNotFoundException e) {
System.out.println("驱动加载错误");
e.printStackTrace();
}
return null ;
}
}
String password="123456"; 是我数据库的密码,根据你们数据库的密码自行设定
private static final String URI = "jdbc:mysql://localhost:3333/test" ; 3333是我数据库的端口号,可根据你们数据库的端口 号而定“test”是我的数据库的名字,可根据自己数据库的名字填写
5.建立实体类 UserInfo.java(建立在com包中)
package com;
public class UserInfo {
private int id;
private String name;
private String age;
private String city;
private String remark;
public UserInfo(String name, String age, String city, String remark) {
super();
this.name = name;
this.age = age;
this.city = city;
this.remark = remark;
}
public UserInfo(int id, String name, String city) {
super();
this.id = id;
this.name = name;
this.city = city;
}
public UserInfo(int id, String name, String age, String city, String remark) {
super();
this.id = id;
this.name = name;
this.age = age;
this.city = city;
this.remark = remark;
}
@Override
public String toString() {
String msg = "id=" + id
+ " ,name = " + name
+ ",age = " + age
+ ",city = " + city
+ ",remark = " + remark;
System.out.println(msg);
return msg;
}
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getAge() {
return age;
}
public void setAge(String age) {
this.age = age;
}
public String getCity() {
return city;
}
public void setCity(String city) {
this.city = city;
}
public String getRemark() {
return remark;
}
public void setRemark(String remark) {
this.remark = remark;
}
}
6.建立一个接受服务的类(UserInfoService.java)建立在com包中
package com;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import com.mysql.jdbc.PreparedStatement;
import com.DBUtils;
import com.UserInfo;
public class UserInfoService extends DBUtils{
Connection conn = null ;
public UserInfoService() throws SQLException{
conn = getCon() ;
}
public boolean add(UserInfo u){
PreparedStatement ps = null;
boolean flag = false ;
try {
String sql = "insert into userinfo(name,age,city,remark) values ('"
+ u.getName()+"',"
+ u.getAge()+",'"
+ u.getCity()+"','"
+ u.getRemark()
+ "')" ;
System.out.println(sql);
ps = (PreparedStatement) conn.prepareStatement(sql);
ps.execute();
flag = true ;
System.out.println("注册结果:"+flag);
} catch (Exception e) {
e.printStackTrace();
}
return flag ;
}
public List<UserInfo> list(UserInfo u){
PreparedStatement ps = null;
List<UserInfo> list = new ArrayList<UserInfo>();
UserInfo userInfo = null;
try {
String name = u.getName();
String city = u.getCity();
int id = u.getId();
String sql = "select * from userinfo where 1=1 ";
if(id != 0 ){
sql = sql + " and id ="+id ;
}
if(name != null && !name.isEmpty()){
sql = sql + " and name like '%"+name+"%'" ;
}
if(city != null && !city.isEmpty() ){
sql = sql + " and city ='"+city+"'" ;
}
System.out.println(sql);
ps = (PreparedStatement) conn.prepareStatement(sql);
ResultSet rs= ps.executeQuery();
while (rs.next()) {
userInfo = new UserInfo(rs.getString("name"),
String.valueOf(rs.getInt("age")),
rs.getString("city"),
rs.getString("remark"));
userInfo.setId(rs.getInt("id"));
list.add(userInfo);
}
} catch (Exception e) {
e.printStackTrace();
}
return list ;
}
public void deleteuserInfo(int id) throws SQLException {
String sql = "DELETE FROM userinfo WHERE id = ?" ;
System.out.println("删除userinfo");
PreparedStatement pst = (PreparedStatement) conn.prepareStatement(sql) ;
pst.setInt(1, id);
pst.executeUpdate();
}
/* public UserInfo findUserById(int id) throws SQLException{
String sql = "select * from userinfo where id = ?" ;
PreparedStatement pst = (PreparedStatement) conn.prepareStatement(sql) ;
pst.setInt(1, id);
ResultSet rs =pst.executeQuery();
UserInfo userInfo = null ;
while (rs.next()) {
userInfo = new UserInfo(rs.getString("name"),
String.valueOf(rs.getInt("age")),
rs.getString("city"),
rs.getString("remark"));
userInfo.setId(id);
}
return userInfo ;
}
*/
}
7.注意在WebRoot文件夹的子文件夹WEB-INF中引入MySQL驱动包,不然页面会报错;同时也引入jstl的jar包
8.在servlet包中建立servlet
注册的servlet:
package servlet;
import java.io.IOException;
import java.sql.SQLException;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import com.UserInfo;
import com.UserInfoService;
public class AddUserServlet extends HttpServlet {
private static final long serialVersionUID = 1L;
/**
* @see HttpServlet#HttpServlet()
*/
public AddUserServlet() {
super();
// TODO Auto-generated constructor stub
}
/**
* @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
*/
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
request.setCharacterEncoding("utf-8");
String name = request.getParameter("name");
String age = request.getParameter("age");
String city = request.getParameter("city");
String remark = request.getParameter("remark");
UserInfo userinfo = new UserInfo(name, age, city, remark);
userinfo.toString();
boolean result = false ;
try {
UserInfoService us = new UserInfoService();
result = us.add(userinfo);
} catch (SQLException e) {
e.printStackTrace();
}
if(result){
System.out.println("add userinfo success .");
response.sendRedirect("QueryUserInfoServlet");
}else{
response.sendRedirect("error.jsp");
}
//request.getRequestDispatcher("QueryUserInfoServlet").forward(request, response);
}
/**
* @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response)
*/
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
doGet(request, response);
}
}
删除用户的servlet:
package servlet;
import java.io.IOException;
import java.sql.SQLException;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import com.UserInfoService;
/**
* Servlet implementation class DeleteUserInfoServelt
*/
public class DeleteUserInfoServelt extends HttpServlet {
private static final long serialVersionUID = 1L;
public DeleteUserInfoServelt() {
super();
}
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
String idStr = request.getParameter("id") ;
try {
UserInfoService u = new UserInfoService();
int id = Integer.valueOf(idStr);
u.deleteuserInfo(id);
System.out.println("删除成功!");
} catch (SQLException e) {
e.printStackTrace();
System.out.println("删除失败!");
}
response.sendRedirect("QueryUserInfoServlet");
}
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
doGet(request, response);
}
}
在jsp页面显示注册用户的servlet:
package servlet;
import java.io.IOException;
import java.io.IOException;
import java.sql.SQLException;
import java.util.List;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import com.UserInfo;
import com.UserInfoService;
public class QueryUserInfoServlet extends HttpServlet {
private static final long serialVersionUID = 1L;
public QueryUserInfoServlet() {
super();
}
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
request.setCharacterEncoding("utf-8");
int id = 0;
String idStr = request.getParameter("id") ;
if(idStr !=null && !idStr.isEmpty()){
id = Integer.valueOf(request.getParameter("id"));
}
String name = request.getParameter("name");
String city = request.getParameter("city");
if("0".equals(city)){
city = null ;
}
UserInfo userinfo = new UserInfo(id,name,city);
userinfo.toString();
List<UserInfo> list = null ;
try {
UserInfoService us = new UserInfoService();
list = us.list(userinfo);
} catch (SQLException e) {
e.printStackTrace();
}
request.setAttribute("list", list);
request.getRequestDispatcher("userlist.jsp").forward(request, response);
}
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
doGet(request, response);
}
}
8.建立jsp页面:
userAdd.jsp
<%@page contentType="text/html;charset=utf-8"%>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
<title>注册用户</title>
</head>
<body>
<form name="form1" method="post" action="AddUserServlet">
<table border="0" align="center">
<tr>
<td>姓名</td>
<td><input name="name" type="text" id="birthday"></td>
</tr>
<tr>
<td>年龄</td>
<td><input name="age" type="text" id="age"></td>
</tr>
<tr>
<td>所在城市</td>
<td><select name="city" id="city" style="width: 152px">
<option value="1">南宁</option>
<option value="2">桂林</option>
<option value="3">柳州</option>
</select></td>
</tr>
<tr>
<td>备注</td>
<td><input name="remark" type="text" id="remark"></td>
</tr>
<tr>
<td>
<div align="center">
<input type="submit" name="Submit" value="提交">
<input type="reset" name="Submit2" value="重置">
</div>
</td>
</tr>
</table>
</form>
</body>
</html>
userlist.jsp
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<%@ page import="java.util.*"%>
<%@ page import="com.*"%>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>Insert title here</title>
</head>
<%
List<UserInfo> list = (List) request.getAttribute("list");
%>
<body>
<!-- 查询-->
<form action="QueryUserInfoServlet" method="post">
编号: <input id="id" name="id" type="text" /><br> 姓名:<input
id="name" name="name" type="text" /><br> 城市:<select id="city"
name="city" style="width: 152px">
<option value="0">全部</option>
<option value="1">南宁</option>
<option value="2">桂林</option>
<option value="3">柳州</option>
</select><br> <input type="submit" value="查询" /> <input type="reset"
value="重置" /> <a href="userAdd.jsp">注册</a>
</form>
------------------------------------------------
<!-- 列表 -->
<table>
<tr>
<td>编号</td>
<td>姓名</td>
<td>所在城市</td>
<td>年龄</td>
<td>备注</td>
<td colspan="1" align="right">操作</td>
</tr>
<c:forEach items="${list}" var="userinfo">
<tr>
<td>${userinfo.id}</td>
<td>${userinfo.name}</td>
<td>${userinfo.city}</td>
<td>${userinfo.age}</td>
<td>${userinfo.remark}</td>
<td><a href="DeleteUserInfoServelt?id=${userinfo.id}" >删除</a></td>
</tr>
</c:forEach>
</table>
</body>
</html>
error.jsp
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>Insert title here</title>
</head>
<body>
注册失败
</body>
</html>
运行截图: