用Servlet对MySQL数据库进行增删改查
采用MVC框架
- 分层截图
属性层：
package com.model;
import java.util.Date;
/**
* 电表结算表的属性
* @author 王炳祥
*
*/
public class MeterSettlement {
private int metersandsettlementid;//主键
private String metersettlementnumber;//电表结算编号
private int electricmeternumber;//电表编号
private float unitprice;//单价
private float price;//总费用
private float lastaccountbalance;//上次账户余额
private float amountofpayment;//本次缴纳金额
private Date createtime;//创建时间
private String creater;//创建人
private Date updatetime;//更新时间
private String updater;//更新人
private String ispostpone;//是否顺延
private float init;//重调的初始值
private String reinitexplain;//初始值说明
private String accessory;//附件
private String isinit;//是否为调平差值数据
private String state;//状态
public int getMetersandsettlementid() {
return metersandsettlementid;
}
public void setMetersandsettlementid(int metersandsettlementid) {
this.metersandsettlementid = metersandsettlementid;
}
public String getMetersettlementnumber() {
return metersettlementnumber;
}
public void setMetersettlementnumber(String metersettlementnumber) {
this.metersettlementnumber = metersettlementnumber;
}
public int getElectricmeternumber() {
return electricmeternumber;
}
public void setElectricmeternumber(int electricmeternumber) {
this.electricmeternumber = electricmeternumber;
}
public float getUnitprice() {
return unitprice;
}
public void setUnitprice(float unitprice) {
this.unitprice = unitprice;
}
public float getPrice() {
return price;
}
public void setPrice(float price) {
this.price = price;
}
public float getLastaccountbalance() {
return lastaccountbalance;
}
public void setLastaccountbalance(float lastaccountbalance) {
this.lastaccountbalance = lastaccountbalance;
}
public float getAmountofpayment() {
return amountofpayment;
}
public void setAmountofpayment(float amountofpayment) {
this.amountofpayment = amountofpayment;
}
public Date getCreatetime() {
return createtime;
}
public void setCreatetime(Date createtime) {
this.createtime = createtime;
}
public String getCreater() {
return creater;
}
public void setCreater(String creater) {
this.creater = creater;
}
public Date getUpdatetime() {
return updatetime;
}
public void setUpdatetime(Date updatetime) {
this.updatetime = updatetime;
}
public String getUpdater() {
return updater;
}
public void setUpdater(String updater) {
this.updater = updater;
}
public String getIspostpone() {
return ispostpone;
}
public void setIspostpone(String ispostpone) {
this.ispostpone = ispostpone;
}
public float getInit() {
return init;
}
public void setInit(float init) {
this.init = init;
}
public String getReinitexplain() {
return reinitexplain;
}
public void setReinitexplain(String reinitexplain) {
this.reinitexplain = reinitexplain;
}
public String getAccessory() {
return accessory;
}
public void setAccessory(String accessory) {
this.accessory = accessory;
}
public String getIsinit() {
return isinit;
}
public void setIsinit(String isinit) {
this.isinit = isinit;
}
public String getState() {
return state;
}
public void setState(String state) {
this.state = state;
}
}
连接层：
package com.conn;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
/**
* 连接数据库驱动
* @author 王炳祥
*
*/
public class ConDate {
Connection con = null;
/**
* 连接数据
* @return返回连接对象
*/
public Connection getCon() {
try {
//1，加载驱动程序
Class.forName("com.mysql.jdbc.Driver");
//2,获得数据库的链接 [数据库连接地址 端口号 用户名 密码]
con = DriverManager.getConnection("jdbc:mysql://10.96.126.184:3306/system_power_factory", "root", "root");
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
System.out.println("获得加载的驱动发生错误，详情为："+e.getMessage());//e.getMessage():获得错误信息
}
catch ( SQLException e) {
// TODO Auto-generated catch block
System.out.println("加载数据库连接报错发生错误，详情为："+e.getMessage());//e.getMessage():获得错误信息
}
return con;
}
//设置关闭
public void setClose() {
try {
con.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
Dao层
package com.dao;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import com.conn.ConDate;
import com.model.MeterSettlement;
import com.sun.org.glassfish.external.statistics.annotations.Reset;
import com.sun.xml.internal.messaging.saaj.soap.ver1_1.Message1_1Impl;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
/**
* 对电表结算表进行操作
*
* @author 王炳祥
*
*/
public class MeterSettlementDao {
Connection con = null;
ConDate conn = new ConDate();
/**
* 给电表结算表添加数据
*
* @param meterSettlement 用户实体
* @return 返回是否添加成功
*/
public int add(MeterSettlement meterSettlement) {
System.out.println("进入添加方法");
int num = 0;
// 1 创建sql对象
String sql = "INSERT INTO `metersandsettlement` (metersandsettlementid,electricmeternumber)VALUE(?,?)";
// 创建执行sql的对象
PreparedStatement ps;
try {
con = conn.getCon();
ps = con.prepareStatement(sql);
// 设置查询条件
ps.setInt(1, meterSettlement.getMetersandsettlementid());
ps.setInt(2, meterSettlement.getElectricmeternumber());
num = ps.executeUpdate();
ps.close();
con.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return num;
}
/**
* 根据电表号更新用户信息
*
* @param meterSettlement 用户实体
* @return 更新是否成功
*/
public int update(MeterSettlement meterSettlement) {
System.out.println("进入更新方法");
int isSuccess = 0;
// 1 创建sql对象
String str = "UPDATE metersandsettlement SET unitprice=? WHERE metersandsettlementid=?";
// 2 创建执行sql的对象
PreparedStatement ps;
try {
con = conn.getCon();
ps = con.prepareStatement(str);
// 设置查询条件
ps.setFloat(1, meterSettlement.getUnitprice());
ps.setInt(2, meterSettlement.getMetersandsettlementid());
isSuccess = ps.executeUpdate();
ps.close();
con.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return isSuccess;
}
/**
* 根据电表编号删除用户信息
*
* @param electricmeternumber 电表编号
* @return 删除是否成功
*/
public int del(MeterSettlement meterSettlement) {
System.out.println("进入删除方法");
int isDel = 0;
//1创建sql对象
String str = "DELETE FROM metersandsettlement WHERE metersandsettlementid=?";
//2 创建执行sql语句的对象
PreparedStatement ps;
con = conn.getCon();
try {
ps = con.prepareStatement(str);
ps.setInt(1, meterSettlement.getMetersandsettlementid());
isDel = ps.executeUpdate();
System.out.println("删除的行数"+isDel);
ps.close();
con.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return isDel;
}
/**
* 根据查询条件返回查询结果
*
* @param meterSettlement 查询条件
* @return 返回查询后的集合
*/
public List<MeterSettlement> getAll(MeterSettlement meterSettlement) {
System.out.println("进入查询方法");
List<MeterSettlement> lister = new ArrayList<MeterSettlement>();
/**
* 创建sql字符串
*/
String str = "SELECT * from metersandsettlement u where metersandsettlementid=? and electricmeternumber=?";
int electricmeternumber = 0;
int metersandsettlementid = 0;
if (meterSettlement.getElectricmeternumber() != 0 && meterSettlement.getMetersandsettlementid() != 0) {
electricmeternumber = meterSettlement.getElectricmeternumber();
metersandsettlementid = meterSettlement.getMetersandsettlementid();
}
con = conn.getCon();
try {
// 创建执行sql的对象
PreparedStatement ps = con.prepareStatement(str);
System.out.println("查询语句：" + str);
// 设置查询条件
ps.setInt(1, metersandsettlementid);
ps.setInt(2, electricmeternumber);
// 返回查询结果的记录集
ResultSet rs = ps.executeQuery();
// 将记录集存到集合中
while (rs.next()) {
// 声明一个实体对
MeterSettlement meterSettlement1 = new MeterSettlement();
// 给对象赋值 传的是索引值 不是变量
meterSettlement1.setMetersandsettlementid(rs.getInt("metersandsettlementid"));// 主键
meterSettlement1.setElectricmeternumber(rs.getInt("electricmeternumber"));// 电表编号
meterSettlement1.setMetersettlementnumber(rs.getString("metersettlementnumber"));// 电表结算编号
meterSettlement1.setUnitprice(rs.getFloat("unitprice"));// 单价
meterSettlement1.setPrice(rs.getFloat("price"));// 总费用
meterSettlement1.setLastaccountbalance(rs.getFloat("lastaccountbalance"));// 上次账户余额
meterSettlement1.setAmountofpayment(rs.getFloat("amountofpayment"));// 本次缴纳金额
meterSettlement1.setCreatetime(rs.getDate("createtime"));// 创建时间
meterSettlement1.setCreater(rs.getString("creater"));// 创建人
meterSettlement1.setUpdatetime(rs.getDate("updatetime"));// 更新时间
meterSettlement1.setUpdater(rs.getString("updater"));// 更新人
meterSettlement1.setIspostpone(rs.getString("ispostpone"));// 是否顺延
meterSettlement1.setInit(rs.getFloat("init"));// 重调的初始值
meterSettlement1.setReinitexplain(rs.getString("reinitexplain"));// 初始值说明
meterSettlement1.setAccessory(rs.getString("accessory"));// 附件
meterSettlement1.setState(rs.getString("state"));// 状态
// 将实体对象添加到集合中
lister.add(meterSettlement1);
}
// 关闭记录集
rs.close();
// 关闭执行的sql对象
ps.close();
// 关闭连接
con.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return lister;
}
}
Services层
package com.services;
import java.util.ArrayList;
import java.util.List;
import com.model.MeterSettlement;
import com.dao.MeterSettlementDao;
/**
* 对电表结算表操作前所必需的完成的业务进行编写
* @author 王炳祥
*
*/
public class MeterSettlementServices {
//创建一个MeterSettlementDao的对象
MeterSettlementDao MeterSettlementDao = new MeterSettlementDao();
/**
* 给电表结算表添加数据
* @param meterSettlement 用户实体
* @return 返回是否添加成功
*/
public int add(MeterSettlement meterSettlement) {
System.out.println("进入添加业务");
// List <MeterSettlement> lister = new ArrayList <MeterSettlement>();
int num=0;
//电表编号不能重复
MeterSettlement meterSettlement01 = new MeterSettlement();
//从添加的实体类中获取电脑编号
meterSettlement01.setElectricmeternumber(meterSettlement.getElectricmeternumber());
//获取有多少电表编号相同的人数
List<MeterSettlement> listMeterSettlement = MeterSettlementDao.getAll(meterSettlement01);
System.out.println(listMeterSettlement);
//判断是否可以添加
if(listMeterSettlement.size()>0) {
num = -1; //电表号重复
}else {
//添加数据
num = MeterSettlementDao.add(meterSettlement);
}
System.out.println("返回添加业务流程");
return num;
}
/**
* 根据电表号更新用户信息
* @param meterSettlement 用户实体
* @return 更新是否成功
*/
public int update(MeterSettlement meterSettlement) {
System.out.println("进入更新业务");
int isSuccess = 0;
//电表编号不可修改
MeterSettlement meterSettlement01 = new MeterSettlement();
//从添加的实体类中获取电表编号
meterSettlement01.setElectricmeternumber(meterSettlement.getElectricmeternumber());
//获取有多少电表编号相同的人数
List<MeterSettlement> listMeterSettlement = MeterSettlementDao.getAll(meterSettlement01);
System.out.println(listMeterSettlement);
//判断是否可以添加
if(listMeterSettlement.size()>0) {
isSuccess = -1; //电表号重复
System.out.println("1354456");
}else {
//gengxin数据
isSuccess = MeterSettlementDao.update(meterSettlement);
System.out.println(isSuccess);
}
return isSuccess;
}
/**
* 根据电表编号删除用户信息
* @param electricmeternumber 电表编号
* @return 删除是否成功
*/
public int del(MeterSettlement meterSettlement) {
System.out.println("进入删除业务");
int isDel = 0;
//电表编号不可删除
MeterSettlement meterSettlement01 = new MeterSettlement();
//从添加的实体类中获取电表编号
meterSettlement01.setElectricmeternumber(meterSettlement.getElectricmeternumber());
//获取有多少电表编号相同的人数
List<MeterSettlement> listMeterSettlement = MeterSettlementDao.getAll(meterSettlement01);
System.out.println(listMeterSettlement);
//判断是否可以添加
if(listMeterSettlement.size()>1) {
isDel = -1; //电表号重复
System.out.println("1354456");
}else {
//gengxin数据
isDel = MeterSettlementDao.del(meterSettlement);
System.out.println(isDel);
}
return isDel;
}
/**
* 根据查询条件返回查询结果
* @param meterSettlement 查询条件
* @return 返回查询后的集合
*/
public List<MeterSettlement> getAll(MeterSettlement meterSettlement){
System.out.println("进入查询业务：");
List <MeterSettlement> lister = new ArrayList <MeterSettlement>();
lister = MeterSettlementDao.getAll(meterSettlement);
if(lister.size()>0) {
System.out.println("有值"+lister);
}
return lister;
}
}
Controller层
package com.controller;
/**
* 接收前端所有的有关电表结算表的信息
*/
import java.io.IOException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
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.google.gson.Gson;
import com.model.MeterSettlement;
import com.mysql.fabric.Response;
import com.services.MeterSettlementServices;
import com.sun.net.httpserver.HttpServer;
@WebServlet("/MeterSettlement")
public class MeterSettlementController extends HttpServlet {
private static Gson gson = new Gson();
@Override
protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
// TODO Auto-generated method stub
// 用户所做的操作
String oper = req.getParameter("oper");
System.out.println("获取地址栏当中的数据为" + oper);
// 电表结算表的数据
System.out.println(req.getParameter("electricmeternumber"));
MeterSettlement me = new MeterSettlement();
if (req.getParameter("electricmeternumber") != null) {
int electricmeternumber = Integer.parseInt(req.getParameter("electricmeternumber"));
int metersandsettlementid = Integer.parseInt(req.getParameter("metersandsettlementid"));
// System.out.println("获取地址栏当中的数据为" + electricmeternumber);
if (oper != null) {
MeterSettlementServices meterSettlementServices = new MeterSettlementServices();
MeterSettlement meterSettlement = new MeterSettlement();
// 进行赋值
meterSettlement.setElectricmeternumber(electricmeternumber);
meterSettlement.setMetersandsettlementid(metersandsettlementid);
// 操作内容
switch (oper) {
case "1":
// 查询
if(metersandsettlementid!=0&&electricmeternumber != 0) {
List <MeterSettlement> lister=meterSettlementServices.getAll(meterSettlement);
String str = gson.toJson(lister);
resp.getWriter().print(str);
}
break;
case "2":
// 添加
if (electricmeternumber != 0) {
int a=meterSettlementServices.add(meterSettlement);
String str = gson.toJson(a);
resp.getWriter().print(str);
} else {
}
break;
case "3":
// 更新
float unitprice = Float.valueOf(req.getParameter("unitprice"));
meterSettlement.setPrice(unitprice);
int gx = meterSettlementServices.update(meterSettlement);
System.out.println(gx);
break;
case "4":
// 删除
int sc = meterSettlementServices.del(meterSettlement);
System.out.println(sc);
break;
}
} else {
}
}
// 设置返回信息
List<MeterSettlement> meterSettlementList = new ArrayList<MeterSettlement>();
// 将所有的返回值写入map中
Map map = new HashMap();
map.put("code", "1");// 返回编号 1:成功 0：失败 -1：电表编号重复
map.put("statue", "success");
map.put("result", "meterSettlement");
map.put("electricmeternumber",me.getElectricmeternumber());
map.put("metersandsettlementid", me.getMetersandsettlementid());
// 将数据转化为字符串
String rs = gson.toJson(map);
// 将数据发送后进行打印
resp.getWriter().print(rs);
}
@Override
protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
// TODO Auto-generated method stub
super.doGet(req, resp);
}
}
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
