这是我参与11月更文挑战的第二十三天，活动详情查看：2021最后一次更文挑战
Web 增加和查询操作
创建数据库
创建db_crm数据库 并且设置数据库的编码格式为UTF-8
CREATE DATABASE IF NOT EXISTS db_crm DEFAULT CHARSET utf8 COLLATE utf8_general_ci;
创建客户数据表
cid 客户主键
cname 客户姓名
phone 客户手机号码
email 客户电子邮箱
url 客户公司网址
come 客户来源
remark 备注信息
CREATE TABLE tb_customer(
cid int primary key auto_increment,
cname varchar(200),
phone varchar(25),
email varchar(100),
url varchar(100),
come varchar(50),
remark varchar(200)
)
数据库连接工具类
package com.hbwl.utils;
import com.mysql.cj.jdbc.Driver;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Iterator;
import java.util.Map;
import java.util.Map.Entry;
import java.util.Set;
public class JdbcUtils {
private static final String URL="jdbc:mysql://localhost:3306/db_crm?serverTimezone=UTC&useUnicode=true&characterEncoding=utf-8";
private static final String USERNAME="root";
private static final String PASSWORD="123456";
//注册驱动
static{
try {
DriverManager.registerDriver(new Driver());
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
//获取数据库的链接
private static Connection openConn()
{
Connection connection=null;
try {
connection = DriverManager.getConnection(URL,USERNAME,PASSWORD);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return connection;
}
//关闭数据库的链接
private static void closeConn(Connection conn,PreparedStatement ps,ResultSet rs)
{
if(rs!=null)
{
try {
rs.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
if(ps!=null)
{
try {
ps.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
if(conn!=null)
{
try {
conn.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
/**
* 执行增删改操作的
* @param sql 要执行的SQL语句
* @param params 这里是SQL语句的?号注入的参数
* @return 是否影响了多少行 如果影响了就为true 否则为false
*/
public static boolean executeUpdate(String sql,Map<Integer,Object> params)
{
int row=0;
//打开链接
Connection openConn = openConn();
PreparedStatement ps=null;
try {
ps = openConn.prepareStatement(sql);
Set<Entry<Integer, Object>> entry = params.entrySet();
Iterator<Entry<Integer, Object>> iterator = entry.iterator();
while(iterator.hasNext())
{
Entry<Integer, Object> next = iterator.next();
ps.setObject(next.getKey(),next.getValue());
}
row=ps.executeUpdate();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
finally
{
//关闭链接
closeConn(openConn,ps,null);
}
return row>0?true:false;
}
/**
* 执行查询的动作
* @param sql
* @param params
* @return
*/
public static ResultSet executeQuery(String sql,Map<Integer,Object> params)
{
ResultSet rs=null;
Connection openConn = openConn();
PreparedStatement ps=null;
try {
ps = openConn.prepareStatement(sql);
if(params!=null&¶ms.size()>0){
Set<Entry<Integer, Object>> entry = params.entrySet();
Iterator<Entry<Integer, Object>> iterator = entry.iterator();
while(iterator.hasNext())
{
Entry<Integer, Object> next = iterator.next();
ps.setObject(next.getKey(),next.getValue());
}
}
rs=ps.executeQuery();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return rs;
}
}
在web.xml中设置首页
<?xml version="1.0" encoding="UTF-8"?>
<web-app xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://xmlns.jcp.org/xml/ns/javaee" xsi:schemaLocation="http://xmlns.jcp.org/xml/ns/javaee http://xmlns.jcp.org/xml/ns/javaee/web-app_3_1.xsd" id="WebApp_ID" version="3.1">
<display-name>SYSTEM</display-name>
<welcome-file-list>
<welcome-file>add.html</welcome-file>
</welcome-file-list>
</web-app>
整合静态资源
将所有的静态文件夹拷贝到项目的webapp中
新增页面
在webapp下新建add.html
新增控制Servlet
package com.hbwl.servlet;
import java.io.IOException;
import java.util.HashMap;
import java.util.Map;
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 com.hbwl.utils.JdbcUtils;
@WebServlet("/doAdd")
public class AddServlet extends HttpServlet {
@Override
protected void service(HttpServletRequest req, HttpServletResponse resp)
throws ServletException, IOException {
//接收前台的参数
String cname = req.getParameter("cname");
String phone = req.getParameter("phone");
String email = req.getParameter("email");
String url = req.getParameter("url");
String come = req.getParameter("come");
String remark = req.getParameter("remark");
//调用JDBCUtils的插入方法
String sql="INSERT INTO tb_customer VALUES(newid(),?,?,?,?,?,?)";
HashMap<Integer,Object> params=new HashMap<Integer,Object>();
params.put(1,cname);
params.put(2,phone);
params.put(3,email);
params.put(4,url);
params.put(5,come);
params.put(6,remark);
//如果插入成功 则跳转到查询页面
boolean flag=JdbcUtils.executeUpdate(sql, params);
if(flag)
{
resp.sendRedirect("/list");
}
}
}
查询页面
在webapp下新建list.jsp 要转换为jsp页面 因为要在页面上编写Java代码
在JSP页面中 代码必须写在<%%>里面 后面我们会学到JSTL语句和EL表达式 是用来替代下面的 JSP页面中的JAVA语句块 <% 可以编写任意的JAVA代码 %>
#JSP页面中的表达式块 <%=Java表达式/变量值%>
例如：在jsp文件头表明文件为java类型 <%@page language="java" contentType="text/html; charset=UTF-8" import="java.sql.ResultSet"%>
查询控制Servlet
package com.hbwl.servlet;
import java.io.IOException;
import java.sql.ResultSet;
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 com.hbwl.utils.JdbcUtils;
@WebServlet("/list")
public class ListServlet extends HttpServlet{
@Override
protected void service(HttpServletRequest req, HttpServletResponse resp)
throws ServletException, IOException {
String sql="SELECT * FROM tb_customer";
ResultSet rs=JdbcUtils.executeQuery(sql, null);
req.setAttribute("resultSet", rs);
req.getRequestDispatcher("table_basic.jsp").forward(req, resp);
}
}
