1、创建数据库
用户
drop database if exists mydb;
create database mydb;
use mydb;
create table t_user
(
uid int primary key auto_increment,
username varchar(20),
password varchar(20),
phone varchar(11),
address varchar(50)
);
insert into t_user(username,password,phone,address)values("张三","666","18755685452","旧金山");
insert into t_user(username,password,phone,address)values("李四","333","18794658126","挪威");
insert into t_user(username,password,phone,address)values("驴子","129","17826789436","广州");
商品
create table t_goods
(
gid int primary key auto_increment,
gname varchar(20),
price double,
mark varchar(100)
);
insert into t_goods(gname,price,mark) values('泡面',4.5,'够香够辣就是这个味!');
insert into t_goods(gname,price,mark) values('火腿',8.5,'肉质细腻Q弹!');
insert into t_goods(gname,price,mark) values('雪碧',3.5,'清爽冰凉随心爽!');
2、创建跳转页面
文件创建框架
3、创建实体类,用于链接数据库
用户实体类
package com.bean;
public class User {
private int uid;
private String username;
private String password;
private String phone;
private String address;
public int getUid() {
return uid;
}
public void setUid(int uid) {
this.uid = uid;
}
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 getPhone() {
return phone;
}
public void setPhone(String phone) {
this.phone = phone;
}
public String getAddress() {
return address;
}
public void setAddress(String address) {
this.address = address;
}
@Override
public String toString() {
return "User{" +
"uid=" + uid +
", username='" + username + '\'' +
", password='" + password + '\'' +
", phone='" + phone + '\'' +
", address='" + address + '\'' +
'}';
}
}
商品实体类
public class Goods {
private int gid;
private String gname;
private double price;
private String mark;
public int getGid() {
return gid;
}
public void setGid(int gid) {
this.gid = gid;
}
public String getGname() {
return gname;
}
public void setGname(String gname) {
this.gname = gname;
}
public double getPrice() {
return price;
}
public void setPrice(double price) {
this.price = price;
}
public String getMark() {
return mark;
}
public void setMark(String mark) {
this.mark = mark;
}
@Override
public String toString() {
return "Goods{" +
"gid=" + gid +
", gname='" + gname + '\'' +
", price=" + price +
", mark='" + mark + '\'' +
'}';
}
4、创建JDBC工具包---JDBCUtil
package com.util;
import java.sql.*;
public class JDBCUtil {
private static String drive="com.mysql.cj.jdbc.Driver";
private static String url="jdbc:mysql://127.0.0.1:3306/mydb";
private static String use="root";
private static String pow="root";
private static Connection con=null;
public static Connection getCon(){
try {
Class.forName(drive);
con = DriverManager.getConnection(url, use, pow);
} catch (ClassNotFoundException | SQLException e) {
e.printStackTrace();
}
return con;
}
public static void close(ResultSet rs, PreparedStatement pst,Connection con) throws SQLException {
try {
if (rs!=null){
rs.close();
}
if (pst!=null){
pst.close();
}
if (con!=null){
con.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
public static void close( PreparedStatement pst,Connection con) throws SQLException {
try {
if (pst!=null){
pst.close();
}
if (con!=null){
con.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
5、创建Dao包,用于存放接口
package com.Dao;
import com.bean.User;
public interface UserDao {
/**
*完成用户的登录操作
* @param username
* @param password
* @return 数据库中查询到完整用户信息
*/
User login(String username,String password);
/**
* 完成用户信息注册
* @param user 封装了用户信息
* @return 插入数据库后影响的行数
*/
int reg(User user);
}
6、login的登录页面
接口类实现
public class UseDaolp implements UserDao {
private Connection con=null;//数据库链接对象
private PreparedStatement pst=null;
private ResultSet rs=null;
private int row=0;
User eag=null;
@Override
public User login(String username, String password) {
try {
con= JDBCUtil.getCon();
String sql="select * from t_user where username=? and password=?";
// 获取处理对象
pst = con.prepareStatement(sql);
// 传参
pst.setObject(1,username);
pst.setObject(2,password);
// 执行SQL
rs = pst.executeQuery();
// 判断结果集
if (rs.next()){
eag=new User();
eag.setUid(rs.getInt("uid"));
eag.setUsername(rs.getString("username"));
eag.setPassword(rs.getString("password"));
eag.setPhone(rs.getString("phone"));
eag.setAddress(rs.getString("address"));
}
} catch (Exception e) {
e.printStackTrace();
}finally {
try {
JDBCUtil.close(rs,pst,con);
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
return eag;
}
}
登录
package com.select;
import com.Dao.UserDao;
import com.Dao.impl.UseDaolp;
import com.bean.User;
import com.util.JDBCUtil;
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.sql.*;
@WebServlet("/login")
public class login extends HttpServlet {
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
doPost(request,response);
}
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
request.setCharacterEncoding("utf-8");
response.setCharacterEncoding("utf-8");
response.setContentType("text/html;charset=utf-8");
// 获取请求参数
String username = request.getParameter("username");
String password = request.getParameter("password");
UserDao userDao=new UseDaolp();
User eag=userDao.login(username,password);
if (eag!=null){
response.sendRedirect("cg.jsp");
}else{
response.sendRedirect("error.jsp");
}
}
}
注册zhuce
接口类实现
public class UseDaolp implements UserDao {
private Connection con=null;//数据库链接对象
private PreparedStatement pst=null;
private ResultSet rs=null;
private int row=0;
User eag=null;
@Override
public int reg(User user) {
try {
con = JDBCUtil.getCon();
String sql="insert into t_user(username,password,phone,address) values(?,?,?,?) ";
// 获取预处理对象
pst = con.prepareStatement(sql);
// 传参
pst.setObject(1,user.getUsername());
pst.setObject(2,user.getPassword());
pst.setObject(3,user.getPhone());
pst.setObject(4,user.getAddress());
// 执行Sql语句
row=pst.executeUpdate();
} catch (Exception e) {
e.printStackTrace();
}finally {
try {
JDBCUtil.close(pst,con);
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
return row;
}
}
注册
package com.select;
import com.Dao.UserDao;
import com.Dao.impl.UseDaolp;
import com.bean.User;
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.sql.Connection;
import java.sql.DriverManager;
@WebServlet( "/zhuce")
public class zhuce extends HttpServlet {
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
doPost(request, response);
}
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
request.setCharacterEncoding("utf-8");
response.setCharacterEncoding("utf-8");
// response.setContentType("text/html;charset=utf-8");
String username = request.getParameter("username");
String password = request.getParameter("password");
String phone = request.getParameter("phone");
String address = request.getParameter("address");
// 封装到User对象中
User user=new User();
user.setUsername(username);
user.setPassword(password);
user.setPhone(phone);
user.setAddress(address);
System.out.println(user);
UserDao userDao=new UseDaolp();
int row=userDao.reg(user);
if (row>0){
response.sendRedirect("login.jsp");
}else {
response.sendRedirect("zhuce.jsp");
}
}
}
商品页面
创建一个商品接口,用来展示商品内容。创建List<>集合,返回一个goodsList返回值
public class Goodsdao {
private String drive="com.mysql.cj.jdbc.Driver";
private String url="jdbc:mysql://127.0.0.1:3306/mydb";
private String use="root";
private String pas="root";
private Connection con=null;
private PreparedStatement pst=null;
private ResultSet rs=null;
private int row=0;//受增删改影响的行数
public List<Goods> select(){
List<Goods> goodsList=new ArrayList<>();
try {
Class.forName(drive);
con=DriverManager.getConnection(url,use,pas);
String sql="select * from t_goods";
pst=con.prepareStatement(sql);
rs= pst.executeQuery();
while (rs.next()){
Goods goods=new Goods();
goods.setGid(rs.getInt("gid"));
goods.setGname(rs.getString("gname"));
goods.setPrice(rs.getDouble("price"));
goods.setMark(rs.getString("mark"));
goodsList.add(goods);
}
} catch (ClassNotFoundException | SQLException e) {
e.printStackTrace();
}finally {
try{
if (rs!=null){
rs.close();
}
if (pst!=null){
pst.close();
}
if (con!=null){
con.close();
}
} catch (Exception e) {
e.printStackTrace();
}
}
return goodsList;
}
在login内编写语句,用来转场
if (eag!=null){
System.out.println("登陆成功");
// Goodsdao goodsdao=new Goodsdao();
// List<Goods> goodsList=goodsdao.select();
HttpSession session = request.getSession();
session.setAttribute("as",eag);
request.getRequestDispatcher("selectAllGoods").forward(request,response);
// response.sendRedirect("cg.jsp");
}else{
response.sendRedirect("error.jsp");
}
在select中创建一个selectAllGoods的select类,用来把商品传输到页面中进行展示
@Override
protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
doPost(req, resp);
}
@Override
protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
// 1、设置请求和响应的编码
req.setCharacterEncoding("utf-8");
resp.setCharacterEncoding("utf-8");
System.out.println("SelectAllGoods.....doPost");
// 去查询数据库中商品信息标中的数据
Goodsdao goodsdao=new Goodsdao();
List<Goods> goodsList=goodsdao.select();
System.out.println(goodsList);
// 如何把商品信息集合传输到页面中进行展示--此处jie'z
HttpSession session=req.getSession();
// session.setAttribute("ea",goodsList);
session.setAttribute("goodsList",goodsList);
resp.sendRedirect("shangp.jsp");
}
在jsp中进行商品遍历,展示商品信息
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<html>
<head>
<title>主页</title>
</head>
<body>
<h2>欢迎${as.address}的${as.username}来到主页</h2>
<table>
<tr>
<th>商品编号</th>
<th>商品名称</th>
<th>商品价格</th>
<th>商品说明</th>
</tr>
<c:forEach items="${goodsList}" var="goods" >
<tr>
<td>${goods.gid}</td>
<td>${goods.gname}</td>
<td>${goods.price}</td>
<td>${goods.mark}</td>
</tr>
</c:forEach>
</table>
</body>
</html>
页面导入jstl的核心类库
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>