Web与数据库连接使用增加和查询操作
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
伪原创工具 SEO网站优化 https://www.237it.com/
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); } }
作者:Yummyq
链接:https://juejin.cn/post/7034526871370858509