第一部分---------数据库 直接手动建表 手动添加数据 在这里就不写具体建表步骤
第二部分 --------项目编写
1.(实体类)
FinancingProduct
package com.qhit.demo.entry;
import java.io.Serializable;
/**
* @author gzh
* @date 2022/3/8 14:34
*/
public class FinancingProduct implements Serializable {
private String id;
private String risk;
private String income;
private String saleStarting;
private String saleEnd;
private String end;
public FinancingProduct(String id, String risk, String income, String saleStarting, String saleEnd, String end) {
this.id = id;
this.risk = risk;
this.income = income;
this.saleStarting = saleStarting;
this.saleEnd = saleEnd;
this.end = end;
}
public FinancingProduct() {
}
public String getId() {
return id;
}
public void setId(String id) {
this.id = id;
}
public String getRisk() {
return risk;
}
public void setRisk(String risk) {
this.risk = risk;
}
public String getIncome() {
return income;
}
public void setIncome(String income) {
this.income = income;
}
public String getSaleStarting() {
return saleStarting;
}
public void setSaleStarting(String saleStarting) {
this.saleStarting = saleStarting;
}
public String getSaleEnd() {
return saleEnd;
}
public void setSaleEnd(String saleEnd) {
this.saleEnd = saleEnd;
}
public String getEnd() {
return end;
}
public void setEnd(String end) {
this.end = end;
}
@Override
public String toString() {
return "FinancingProduct{" +
"id='" + id + '\'' +
", risk='" + risk + '\'' +
", income='" + income + '\'' +
", saleStarting='" + saleStarting + '\'' +
", saleEnd='" + saleEnd + '\'' +
", end='" + end + '\'' +
'}';
}
}
2.dao层
(baseDao)
package com.qhit.demo.dao;
import com.qhit.demo.utils.JDBCUtils;
import java.lang.reflect.Field;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.util.ArrayList;
import java.util.List;
/*
* 封装了数据库增删改查操作
*
*
* */
public abstract class BaseDAO {
//通用的增删改操作 vsersion2.0(考虑上事务)
public int update(Connection conn, String sql, Object...args) {
PreparedStatement ps =null;
try {
//预编译SQL语句,返回PrepareStatement的实例
ps = conn.prepareStatement(sql);
//填充占位符
for (int i = 0; i <args.length ; i++) {
ps.setObject(i+1,args[i]);
}
//执行
return ps.executeUpdate();
}catch (Exception e){
e.printStackTrace();
}finally {
//资源关闭
JDBCUtils.closeResourse(null,ps);
}
return 0;
}
//通用查询返回到集合中version2(考虑上事务)
public <T> List<T> getFoeList(Connection conn, Class<T> clazz , String sql, Object...args) {
PreparedStatement ps=null;
ResultSet rs = null;
try {
//预编译sql语句返回PreparedStatement
ps = conn.prepareStatement(sql);
//填充占位符
for (int i = 0; i <args.length ; i++) {
ps.setObject(i+1,args[i]);
}
//3执行并返回结果集
rs = ps.executeQuery();
//获得结果集的元数据:ResultSetMetaData
ResultSetMetaData rsmd = rs.getMetaData();
//通过ResultSetMetaData获取结果集中的列数;
int columnCount = rsmd.getColumnCount();
ArrayList<T> ts = new ArrayList<>();
while (rs.next()){
T t = clazz.newInstance();
//处理结果集一行数据中的每一列
for (int i = 0; i <columnCount ; i++) {
//获得每个列的列名
String columnLabel = rsmd.getColumnName(i + 1);
//获得列值
Object columvalue = rs.getObject(columnLabel);
//给student对象指定的columName属性,赋值为columvalue , 通过反射
Field field = clazz.getDeclaredField(columnLabel);
field.setAccessible(true);
field.set(t,columvalue);
}
ts.add(t);
}
return ts;
}catch (Exception e){
e.printStackTrace();
}finally {
JDBCUtils.closeResourse1(null,ps,rs);
}
return null;
}
//通用查询返回一条记录version2(考虑上事务)
public <T> T getInatance(Connection conn, Class<T> clazz, String sql, Object...args){
PreparedStatement ps=null;
ResultSet rs = null;
try {
//获取连接
//预编译sql语句返回PreparedStatement
ps = conn.prepareStatement(sql);
//填充占位符
for (int i = 0; i <args.length ; i++) {
ps.setObject(i+1,args[i]);
}
//3执行并返回结果集
rs = ps.executeQuery();
//获得结果集的元数据:ResultSetMetaData
ResultSetMetaData rsmd = rs.getMetaData();
//通过ResultSetMetaData获取结果集中的列数;
int columnCount = rsmd.getColumnCount();
if (rs.next()){
T t = clazz.newInstance();
//处理结果集一行数据中的每一列
for (int i = 0; i <columnCount ; i++) {
//获得列值
Object columvalue = rs.getObject(i + 1);
//获得每个列的列名
String columnLabel = rsmd.getColumnLabel(i + 1);
//给student对象指定的columName属性,赋值为columvalue , 通过反射
Field field = clazz.getDeclaredField(columnLabel);
field.setAccessible(true);
field.set(t,columvalue);
}
return t;
}
}catch (Exception e){
e.printStackTrace();
}finally {
JDBCUtils.closeResourse1(null,ps,rs);
}
return null;
}
//通用查询特殊值的方法
public <E> E getValue(Connection conn, String sql, Object...args) {
PreparedStatement ps =null;
ResultSet rs =null;
try {
ps = conn.prepareStatement(sql);
for (int i = 0; i <args.length ; i++) {
ps.setObject(i+1,args[i]);
}
rs = ps.executeQuery();
if(rs.next()){
return (E) rs.getObject(1);
}
}catch (Exception e){
e.printStackTrace();
}finally {
JDBCUtils.closeResourse1(null,ps,rs);
}
return null;
}
}
(2) dao接口
package com.qhit.demo.dao;
import com.qhit.demo.entry.FinancingProduct;
import java.sql.Connection;
import java.util.List;
/**
* @author gzh
* @date 2022/3/8 14:37
*/
public interface FinancingProductDao {
//1.查询所有信息
List<FinancingProduct> findAll(Connection connection);
//2.根据产品代码或产品等级(risk) 查询所有信息
List<FinancingProduct> findByIdAndRisk(Connection connection, String id,String risk);
//3.添加方法
int add(Connection connection,FinancingProduct financingProduct);
}
(3)dao实现类
package com.qhit.demo.dao.impl;
import com.qhit.demo.dao.BaseDAO;
import com.qhit.demo.dao.FinancingProductDao;
import com.qhit.demo.entry.FinancingProduct;
import java.sql.Connection;
import java.util.List;
/**
* @author gzh
* @date 2022/3/8 14:46
*/
public class FinancingProductDaoImpl extends BaseDAO implements FinancingProductDao {
@Override
public List<FinancingProduct> findAll(Connection connection) {
String sql = "select * from product";
List<FinancingProduct> productList = super.getFoeList(connection, FinancingProduct.class, sql);
return productList;
}
@Override
public List<FinancingProduct> findByIdAndRisk(Connection connection, String id, String risk) {
String sql = "select * from product where id = ? or risk=?";
List<FinancingProduct> foeList = super.getFoeList(connection, FinancingProduct.class, sql, id, risk);
return foeList;
}
@Override
public int add(Connection connection, FinancingProduct financingProduct) {
String sql = "INSERT INTO product VALUES(?,?,?,?,?,?)";
int update = super.update(connection, sql, financingProduct.getId(), financingProduct.getRisk(), financingProduct.getIncome(), financingProduct.getSaleStarting(), financingProduct.getSaleEnd(), financingProduct.getEnd());
return update;
}
}
(4)Service 层
Service接口
package com.qhit.demo.service;
import com.qhit.demo.entry.FinancingProduct;
import java.sql.Connection;
import java.util.List;
/**
* @author gzh
* @date 2022/3/8 14:59
*/
public interface FinancingProductService {
//1.查询所有信息
List<FinancingProduct> findAll(Connection connection);
//2.根据产品代码或产品等级(risk) 查询所有信息
List<FinancingProduct> findByIdAndRisk(Connection connection, String id,String risk);
//3.添加方法
int add(Connection connection,FinancingProduct financingProduct);
}
Service 实现类
package com.qhit.demo.service.impl;
import com.qhit.demo.dao.FinancingProductDao;
import com.qhit.demo.dao.impl.FinancingProductDaoImpl;
import com.qhit.demo.entry.FinancingProduct;
import com.qhit.demo.service.FinancingProductService;
import java.sql.Connection;
import java.util.List;
/**
* @author gzh
* @date 2022/3/8 15:00
*/
public class FinancingProductServiceImpl implements FinancingProductService {
private FinancingProductDao financingProductDao = new FinancingProductDaoImpl();
@Override
public List<FinancingProduct> findAll(Connection connection) {
List<FinancingProduct> productList = financingProductDao.findAll(connection);
return productList;
}
@Override
public List<FinancingProduct> findByIdAndRisk(Connection connection, String id, String risk) {
return financingProductDao.findByIdAndRisk(connection,id,risk);
}
@Override
public int add(Connection connection, FinancingProduct financingProduct) {
int add = financingProductDao.add(connection, financingProduct);
return add;
}
}
(6)web 层 Servlet
QueryProductServlet
package com.qhit.demo.web;
import com.qhit.demo.entry.FinancingProduct;
import com.qhit.demo.service.FinancingProductService;
import com.qhit.demo.service.impl.FinancingProductServiceImpl;
import com.qhit.demo.utils.JDBCUtils;
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.util.List;
/**
* @author gzh
* @date 2022/3/8 16:33
*/
@WebServlet("/QueryProductServlet")
public class QueryProductServlet extends HttpServlet {
private FinancingProductService financingProductService = new FinancingProductServiceImpl();
@Override
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
request.setCharacterEncoding("UTF-8");
response.setCharacterEncoding("UTF-8");
response.setContentType("text/html;charset=UTF-8");
String id = request.getParameter("id");
String risk = request.getParameter("risk");
Connection conn = null;
try {
conn = JDBCUtils.getConnection();
List<FinancingProduct> byIdAndRisk = financingProductService.findByIdAndRisk(conn, id, risk);
request.setAttribute("productlist",byIdAndRisk);
request.getRequestDispatcher("index.jsp").forward(request,response);
} catch (Exception e) {
e.printStackTrace();
}
}
@Override
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
this.doGet(request, response);
}
}
AddProductServlet
package com.qhit.demo.web;
import com.qhit.demo.entry.FinancingProduct;
import com.qhit.demo.service.FinancingProductService;
import com.qhit.demo.service.impl.FinancingProductServiceImpl;
import com.qhit.demo.utils.JDBCUtils;
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.io.PrintWriter;
import java.sql.Connection;
/**
* @author gzh
* @date 2022/3/8 16:02
*/
@WebServlet("/addProductServlet")
public class AddProductServlet extends HttpServlet {
private FinancingProductService productService = new FinancingProductServiceImpl();
@Override
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
request.setCharacterEncoding("UTF-8");
response.setCharacterEncoding("UTF-8");
response.setContentType("text/html;charset=UTF-8");
Connection conn =null;
PrintWriter out = response.getWriter();
try {
conn = JDBCUtils.getConnection();
String id = request.getParameter("id");
String risk = request.getParameter("risk");
String income = request.getParameter("income");
String saleStarting = request.getParameter("saleStarting");
String saleEnd = request.getParameter("saleEnd");
String end = request.getParameter("end");
FinancingProduct financingProduct = new FinancingProduct(id,risk,income,saleStarting,saleEnd,end);
System.out.println(financingProduct);
int add = productService.add(conn, financingProduct);
if(add==0){
out.print("<script>alert('添加失败')</script>");
}else {
out.print("<script>alert('添加成功')</script>");
}
} catch (Exception e) {
e.printStackTrace();
}
}
@Override
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
this.doGet(request, response);
}
}
ProductManagerServlet
package com.qhit.demo.web;
import com.qhit.demo.entry.FinancingProduct;
import com.qhit.demo.service.FinancingProductService;
import com.qhit.demo.service.impl.FinancingProductServiceImpl;
import com.qhit.demo.utils.JDBCUtils;
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.util.List;
/**
* @author gzh
* @date 2022/3/8 15:13
*/
@WebServlet("/productManagerServlet")
public class ProductManagerServlet extends HttpServlet {
private FinancingProductService productService = new FinancingProductServiceImpl();
@Override
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
request.setCharacterEncoding("UTF-8");
response.setCharacterEncoding("UTF-8");
response.setContentType("text/html;charset=UTF-8");
Connection conn =null;
try {
conn = JDBCUtils.getConnection();
List<FinancingProduct> productList = productService.findAll(conn);
request.setAttribute("productlist",productList);
request.getRequestDispatcher("index.jsp").forward(request,response);
} catch (Exception e) {
e.printStackTrace();
}finally {
JDBCUtils.closeResourse(conn,null);
}
}
@Override
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
this.doGet(request, response);
}
}
还有一个 工具类 JDBCUtils
package com.qhit.demo.utils;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ResourceBundle;
public class JDBCUtils {
//连接
public static Connection getConnection() throws Exception{
//读取配置文件中的4个基本信息
ResourceBundle bundle = ResourceBundle.getBundle("JDBC");
String uesrname = bundle.getString("uesrname");
String url = bundle.getString("url");
String password = bundle.getString("password");
String driverClass = bundle.getString("driverclassName");
//加载驱动
Class.forName(driverClass);
//获取连接
Connection conn = DriverManager.getConnection(url,uesrname,password);
return conn;
}
//关闭资源
public static void closeResourse(Connection conn, Statement ps){
try {
if(conn!=null){
conn.close();
}
if(ps!=null){
ps.close();
}
}catch (Exception e){
e.printStackTrace();
}
}
//关闭资源二
public static void closeResourse1(Connection conn, Statement ps , ResultSet rs){
try {
if(conn!=null){
conn.close();
}
if(ps!=null){
ps.close();
}
if(rs!=null){
rs.close();
}
}catch (Exception e){
e.printStackTrace();
}
}
}
最后是 jsp 表示层代码
index.jsp
<%--
Created by IntelliJ IDEA.
User: hua
Date: 2022/3/8
Time: 14:27
To change this template use File | Settings | File Templates.
--%>
<%@ page language="java" contentType="text/html; charset=UTF-8"%>
<%@taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>首页</title>
<style type="text/css">
*{
padding: 0px;
margin: 0px;
}
.f_l{
float: left;
}
.f_r{
float: right;
}
.c{
clear: both;
}
#container{
width:600px;
height:auto;
padding:20px;
margin: 0px auto;
border: solid 1px black;
}
#d1{
margin-top:30px;
padding:0px;
height:60px;
}
table{
width:600px;
text-align: center;
}
li{
list-style: none;
padding:0px;
}
table tr:nth-child(odd){
background: #ccc;
}
</style>
<script type="text/javascript">
function check(){
var risk=document.getElementById("risk").value;
if(risk!="R0"){
return true;
}else{
return false;
}
}
</script>
</head>
<body>
<div id="container">
<form method="post" action="/QueryProductServlet" onsubmit="return checked">
<div id="d1">
<ul>
<li class="f_l">产品代码:<input type="text" id="id" name="id"/></li>
<li class="f_l">
风险评估<select name="risk" id="risk">
<option value="R0" selected="selected">--请选择--</option>
<option value="R1">R1</option>
<option value="R2">R2</option>
<option value="R3">R3</option>
</select>
</li>
<li class="f_l"><input type="submit" value=" 查询 "/></li>
<li class="f_r"><a href="addProduct.jsp">新增理财信息</a></li>
</ul>
</div>
<div class="c" id="d2">
<table border="1">
<tr>
<th>产品代码</th>
<th>风险评级</th>
<th>预期收益</th>
<th>发售起始日</th>
<th>发售截止日</th>
<th>产品到期日</th>
</tr>
<c:forEach var="product" items="${requestScope.productlist}">
<tr>
<td>${product.id}</td>
<td>${product.risk}</td>
<td>${product.income}</td>
<td>${product.saleStarting}</td>
<td>${product.saleEnd}</td>
<td>${product.end}</td>
</tr>
</c:forEach>
</table>
</div>
</form>
</div>
</body>
</html>
AddProduct.jsp
<%--
Created by IntelliJ IDEA.
User: hua
Date: 2022/3/8
Time: 15:57
To change this template use File | Settings | File Templates.
--%>
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>添加理财信息页面</title>
<style type="text/css">
*{
padding: 0px;
margin: 0px;
}
#container{
width: 365px;
height: 260px;
margin: 0px auto;
padding:40px;
border: solid 1px black;
background-color: #f3f3f3;
}
h1{
height: 60px;
color:blue;
text-align: center;
}
p{
line-height: 50px;
text-align: center;
}
#d2{
font-size: 10px;
}
</style>
<script type="text/javascript">
function isId1(){
var id=document.getElementById("id").value;
if(id!=null){
return true;
}else{
return false;
}
}
function checkRisk(){
var risk=document.getElementById("risk").value;
if(risk!=null){
return true
}else{
return false;
}
}
function isIncome1(){
var income=document.getElementById("income").value;
if(income!=null){
return true;
}else{
return false;
}
}
function checkSaleStarting1(){
var saleStarting=document.getElementById("saleStarting").value;
var regDate=/^(([0-9]{3}[1-9]|[0-9]{2}[1-9][0-9]{1}|[0-9]{1}[1-9][0-9]{2}|[1-9][0-9]{3})-(((0[13578]|1[02])-(0[1-9]|[12][0-9]|3[01]))|((0[469]|11)-(0[1-9]|[12][0-9]|30))|(02-(0[1-9]|[1][0-9]|2[0-8]))))|((([0-9]{2})(0[48]|[2468][048]|[13579][26])|((0[48]|[2468][048]|[3579][26])00))-02-29)$/;
if(regDate.test(saleStarting)&&saleStarting!=null){
return true;
}else{
alert("发售起始日期格式输入不正确!");
return false;
}
}
function checkSaleEnd1(){
var saleEnd=document.getElementById("saleEnd").value;
var regDate=/^(([0-9]{3}[1-9]|[0-9]{2}[1-9][0-9]{1}|[0-9]{1}[1-9][0-9]{2}|[1-9][0-9]{3})-(((0[13578]|1[02])-(0[1-9]|[12][0-9]|3[01]))|((0[469]|11)-(0[1-9]|[12][0-9]|30))|(02-(0[1-9]|[1][0-9]|2[0-8]))))|((([0-9]{2})(0[48]|[2468][048]|[13579][26])|((0[48]|[2468][048]|[3579][26])00))-02-29)$/;
if(regDate.test(saleEnd)&&saleEnd!=null){
return true;
}else{
alert("发售截止日期格式输入不正确!");
return false;
}
}
function checkEnd1(){
var end=document.getElementById("end").value;
var regDate=/^(([0-9]{3}[1-9]|[0-9]{2}[1-9][0-9]{1}|[0-9]{1}[1-9][0-9]{2}|[1-9][0-9]{3})-(((0[13578]|1[02])-(0[1-9]|[12][0-9]|3[01]))|((0[469]|11)-(0[1-9]|[12][0-9]|30))|(02-(0[1-9]|[1][0-9]|2[0-8]))))|((([0-9]{2})(0[48]|[2468][048]|[13579][26])|((0[48]|[2468][048]|[3579][26])00))-02-29)$/;
if(regDate.test(end)&&end!=null){
return true;
}else{
alert("产品到期日期格式输入不正确!");
return false;
}
}
function goBack1(){
location.href="QueryProductServlet";
}
function check(){
var checkId1=isId1();
var checkIncome1=isIncome1();
var checkSaleStarting1=checkSaleStarting1();
var checkSaleEnd1=checkSaleEnd1();
var checkEnd1=checkEnd1();
var checkRisk=checkRisk()
if(checkId1&&checkRisk&&checkIncome1&&checkSaleStarting1&&checkSaleEnd1&&checkEnd1){
return true;
}else{
return false;
}
}
</script>
</head>
<body>
<div id="container">
<form method="post" action="/addProductServlet" onsubmit="return check()">
<h1>新增理财信息</h1>
<table>
<tr>
<td>产品代码:</td>
<td><input type="text" id="id" name="id" onblur="isId1()" /></td>
<td></td>
</tr>
<tr>
<td>风险评级:</td>
<td>
<select name="risk" id="risk" onchange="checkRisk()">
<option>--请选择--</option>
<option value="R1">R1</option>
<option value="R2">R2</option>
<option value="R3">R3</option>
</select>
</td>
<td></td>
</tr>
<tr>
<td>预期收益:</td>
<td><input type="text" id="income" name="income" onblur="isIncome1()" /></td>
<td></td>
</tr>
<tr>
<td>发售起始日:</td>
<td><input type="text" id="saleStarting" name="saleStarting" onblur="checkSaleStarting1()" /></td>
<td id="d2">yyyy-MM-dd格式</td>
</tr>
<tr>
<td>发售截止日:</td>
<td><input type="text" id="saleEnd" name="saleEnd" onblur="checkSaleEnd1()" /></td>
<td id="d2">yyyy-MM-dd格式</td>
</tr>
<tr>
<td>产品到期日:</td>
<td><input type="text" id="end" name="end" onblur="checkEnd1()" /></td>
<td id="d2">yyyy-MM-dd格式</td>
</tr>
</table>
<p>
<input type="submit" value=" 保存 "/>
<input type="reset" value=" 重置 "/>
<input type="button" value=" 返回 " onclick="goBack1()"/>
</p>
</form>
</div>
</body>
</html>