更新部分字段或者插入新数据

版权声明:本文为博主原创文章,遵循 CC 4.0 by-sa 版权协议,转载请附上原文出处链接和本声明。
本文链接:https://blog.csdn.net/ZhonKai_Ren/article/details/79923836

因为业务的原因,一张表的数据需要整合两张外键关联不强的表数据,这里涉及到更新部分字段或者插入新数据。今天简单的做个笔记,后续有需要再补充完整

测试表结构:

CREATE TABLE `test` (
  `id` int(10) NOT NULL,
  `name` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `udex` int(10) DEFAULT NULL,
  PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;


1运行以下sql:

INSERT INTO test2.test (id,name,udex) (SELECT 12,3 FROM tb_alter_plan_log GROUP BY ui_student_id) ON DUPLICATE KEY UPDATE name= VALUES(name);

1运行结果:


2运行以下sql:

INSERT INTO test2.test (id,udex) (SELECT 12,3 FROM tb_alter_plan_log GROUP BY ui_student_id) ON DUPLICATE KEY UPDATE udex = VALUES(udex);

2运行结果:



3运行以下sql:

INSERT INTO test2.test (id,name,udex) (SELECT 13,6,6 FROM tb_alter_plan_log GROUP BY ui_student_id) ON DUPLICATE KEY UPDATE name= VALUES(name),udex = VALUES(udex);

3运行结果:



4运行以下sql:

INSERT INTO test2.test (id,name) (SELECT 13,8 FROM tb_alter_plan_log GROUP BY ui_student_id) ON DUPLICATE KEY UPDATE name = VALUES(name);

4运行结果:


5运行以下sql:

INSERT INTO test2.test (id,udex) (SELECT 13,8 FROM tb_alter_plan_log GROUP BY ui_student_id) ON DUPLICATE KEY UPDATE udex = VALUES(udex);

5运行结果:


展开阅读全文

数据插入/更新时 只插入/更新 指定字段

06-12

问题:数据插入/更新时 只插入/更新 指定字段rnrn序列化/反序列化工具 : jacksonrnrnvo示例:rnpublic class User extends BaseVOrn private String id;rn private String userName;rn private String code;rnrn //getter setter ...rnrnrnpublic class BaseVO//公共字段rn private String createrId;rn private String updaterId;rn private Date createDate;rn private Date updateDate;rn //getter setter ...rnrnrnrnController示例:rn @PutMapping(value = "/update")rn public void update(@RequestBody User user) rn Json json = new Json();rn try rn user.setUpdaterId(this.getRequest().getUserId);//取当前登录用户idrn userService.update(user);rn json.setResult(true);rn catch (Exception e) rn printErrorLog(json, e, "更新失败");rn rn writeJson(json);rn rnrn//Service和DAO都是一样的rnService:rn public void update(User user)rn // ...校验rn userDao.update(user);rn rnDAO: rn public void update(User user)rn Single.update(user);//Single 为自定义的持久层 基于jdbcTemplate 构造sql时 默认更新所有字段rn // Single.update(user, new String[]"userName"); // 可传入自定义字段列表 只更新 userName字段rn rnSingle:rn public void update(User user,String[] fieldNames)rn //构造sqlrn String [] fields = null;rn if(fieldNames != null && fieldNames.length > 0)rn fields =fieldNames; // 指定字段rn elsern fields = getAll();// 取vo所有字段rn rn //根据fields构造sql并执行rn ...rn rn前端json字符串示例:case1 : "id":"123456","userName":"zhangsan"rn case2 : "id":"123456","code":"001"rnrn case1时,传入controller层的user 字段id=123456 userName=zhangsan code=null 公共字段=null ;rn case2时,传入controller层的user 字段id=123456 userName=null code=001 公共字段=null ;rn 直接调service更新 会把不传递的字段 置空。rn rn 那么问题来了,在service和dao不变的情况下如何实现只更新前端传递的字段呢?rn 方案1:在BaseVO增加 String[] saveFields 字段,在由json字符串反序列化成vo对象时 ,将前端传递的字段列表作为String数组 设置到 vo的 String[] saveFields 中rn 在Single类 update方法中 fields 取 vo.getSaveFields();rn rnrnrnrnrn 论坛

mvc 更新部分字段

01-21

我的数据表类如下:rn[code=csharp]rnpublic class Shoprn rn public int ShopId get; set; rnrn [ScaffoldColumn(false)]rn public string WXOpenId get; set; rnrn [Required(ErrorMessage = "0 字段必须填写")]rn [StringLength(20, MinimumLength = 5, ErrorMessage = "0 长度为5-20个字符")]rn [Display(Name = "店铺名称")]rn public string ShopName get; set; rnrn [Required(ErrorMessage = "0 字段必须填写")]rn [StringLength(50, MinimumLength = 5, ErrorMessage = "0 长度为5-50个字符")]rn [Display(Name = "店铺地址")]rn public string ShopLocation get; set; rnrn [Required(ErrorMessage = "0 字段必须填写")]rn [Display(Name = "店铺电话")]rn [RegularExpression(@"(^(13[0-9]|14[5|7]|15[0|1|2|3|5|6|7|8|9]|17[0|6|7|8]|18[0|1|2|3|5|6|7|8|9])\d8$)|(\d3-\d8|\d4-\d7)", ErrorMessage = "号码示例:手机号码17783237890或者座机023-55165556")]rn public string ShopPhone get; set; rnrn [ReadOnly(true)]rn [DisplayFormat(ApplyFormatInEditMode = true, DataFormatString = "0:yyyy-MM-dd")]rn [Display(Name = "注册时间")]rn public DateTime Date get; set; rnrn [ReadOnly(true)]rn [DisplayFormat(ApplyFormatInEditMode = true, DataFormatString = "0:yyyy-MM-dd")]rn [Display(Name = "激活时间")]rn public DateTime ActivatedDate get; set; rnrn [ReadOnly(true)] rn [Display(Name = "到期时间")]rn public int EffectiveDate get; set; rn rn[/code]rnrn基架自动生成的编辑方法如下:rn[code=csharp]rn[HttpPost]rn [ValidateAntiForgeryToken]rn public ActionResult Edit([Bind(Include = "ShopId,WXOpenId,ShopName,ShopLocation,ShopPhone,Date,ActivatedDate,EffectiveDate")] Shop shop)rn rn if (ModelState.IsValid)rn rn db.Entry(shop).State = EntityState.Modified;rn db.SaveChanges();rn return RedirectToAction("Index");rn rn return View(shop);rn rn[/code]rnrn我查资料,介绍更新部分字段只要显式地bind属性就行了,修改后如下:rn[code=csharp]rn[HttpPost]rn [ValidateAntiForgeryToken]rn public ActionResult Edit([Bind(Include = "ShopName,ShopLocation,ShopPhone")] Shop shop)rn rn if (ModelState.IsValid)rn rn db.Entry(shop).State = EntityState.Modified;rn db.SaveChanges();rn return RedirectToAction("Index");rn rn return View(shop);rn rn[/code]rnrn可是运行编辑却出错了:rnStore update, insert, or delete statement affected an unexpected number of rows (0). Entities may have been modified or deleted since entities were loaded. See http://go.microsoft.com/fwlink/?LinkId=472540 for information on understanding and handling optimistic concurrency exceptions.rn翻译了一下:rn存储更新、插入或删除语句影响到意外的行数(0)。实体可能已被修改或删除,因为实体加载。看到http://go.microsoft.com/fwlink/?linkid = 472540的信息,在认识和处理乐观并发异常。rnrn请各位大侠帮忙指导一下 论坛

ASP.NET数据插入更新

11-17

dsQuyMst.Tables["QuyMst"].Rows[0]["SHDOCO"] = "1111";//这句报错(未将对象引用设置到对象的实例。)rn这知道是什么原因?rn高人指点~~~rnrnusing System;rnusing System.Data;rnusing System.Configuration;rnusing System.Collections;rnusing System.Web;rnusing System.Web.Security;rnusing System.Web.UI;rnusing System.Web.UI.WebControls;rnusing System.Web.UI.WebControls.WebParts;rnusing System.Web.UI.HtmlControls;rnusing System.Data.SqlClient;rnusing System.Collections.Generic;rnrnpublic partial class P81001A : System.Web.UI.Pagernrn DataSet dsQuyMst;rn // SqlDataAdapter da;rn protected void Page_Load(object sender, EventArgs e)rn rn if (!Page.IsPostBack)rn rn LoadData();rn rn //Menu1_MenuItemClick(object,e);rn //DropDownList1_SelectedIndexChanged(object,e);rn rnrnrn rnrn private void LoadData()rn rn DataSet ds = new DataSet();//rn SqlConnection con = new SqlConnection();rn con.ConnectionString = GetConnectionStr();rn con.Open();rn string strsql = "select SHTYPENO,SHTYPENAME from f81101";rn SqlCommand sqlcmd = new SqlCommand(strsql, con);rn //SqlDataReader dr = sqlcmd.EndExecuteReader();// new SqlDataReader();rn SqlDataAdapter da = new SqlDataAdapter(sqlcmd);rn da.Fill(ds, "bustype");rn rn for (int i = -1; i <= ds.Tables["bustype"].Rows.Count - 1; i++)rn rn ListItem litem = new ListItem();rn if (i == -1)rn rn litem.Text = "请选择商品类型";rn litem.Value = "";rn DropDownList1.Items.Add(litem);rn rn elsern rn litem.Text = ds.Tables[0].Rows[i][1].ToString();rn litem.Value = ds.Tables[0].Rows[i][0].ToString();rn DropDownList1.Items.Add(litem);rn rn rn strsql = "select * from f81111";rn sqlcmd.CommandText = strsql;rn da.SelectCommand = sqlcmd;rn da.Fill(ds, "aa"); //外包或自有rn for (int i = 0; i <= ds.Tables["aa"].Rows.Count - 1; i++)rn rn ListItem litem = new ListItem();rn litem.Text = ds.Tables["aa"].Rows[i][1].ToString();rn litem.Value = ds.Tables["aa"].Rows[i][0].ToString();rn DropDownList2.Items.Add(litem);rn rnrn DropDownList1.DataBind();rn DropDownList2.DataBind();rn //售后单主表rn strsql = "select * from f81001 where 1=2";rn sqlcmd.CommandText = strsql;rn da.SelectCommand = sqlcmd;rn dsQuyMst = new DataSet();rn da.Fill(dsQuyMst, "QuyMst");rn DataTable dt = new DataTable();rn dt = dsQuyMst.Tables["QuyMst"];rn DataRow dr;rn dr = dt.NewRow();rn //dr["SHKCOO"]="00888";rn //dr["SHDOCO"] = "";rn dt.Rows.Add(dr);rn FormView1.DataSource = dsQuyMst.Tables["QuyMst"];rn FormView1.DataBind();rn //手机表rn rnrn con.Close();rn rn private static string GetConnectionStr()rn rn string ss = ConfigurationManager.ConnectionStrings["SHMISConnectionString2"].ConnectionString.ToString();rn // con.ConnectionString = ss;rn return ss;rn rn protected void DropDownList1_SelectedIndexChanged(object sender, EventArgs e)rn rn //if (DropDownList1.SelectedValue == "A1")rn //rnrn // List listm = new List();//创建类列表,用于保存移除的菜单项rnrn rn // listm.Add(Menu1.Items[1]);rn // //移动listm中的菜单项rn // foreach (MenuItem m in listm)rn // rn // Menu1.Items.Remove(m);rn // rn //rn rn protected void Menu1_MenuItemClick(object sender, MenuEventArgs e)rn rn int index = int.Parse(e.Item.Value);rn this.MultiView1.ActiveViewIndex = index;rn rn protected void Button1_Click(object sender, EventArgs e)rn rn string constr=GetConnectionStr();rn SqlConnection con=new SqlConnection(constr);rn con.Open(); rn SqlCommand sqlcmd = new SqlCommand("SP_GenerateDocNoF0905", con);rn // 设置SqlCommand的属性为存储过程rn sqlcmd.CommandType = CommandType.StoredProcedure;rn sqlcmd.Parameters.Add("@Dct", SqlDbType.Char, 2);rn sqlcmd.Parameters.Add("@Doco", SqlDbType.Int, 8);rn //set return value direction rn sqlcmd.Parameters["@Dct"].Direction=ParameterDirection.Input;rn sqlcmd.Parameters["@Doco"].Direction = ParameterDirection.Output;rn // 返回值rn sqlcmd.Parameters["@Dct"].Value = "1S";rn int rowsAffected = sqlcmd.ExecuteNonQuery();//行影响值rn //int result = sqlcmd.parameters["Returnvalue"].value;//存储过程返回值rn int newID = int.Parse( sqlcmd.Parameters["@Doco"].Value.ToString());rn //功能挺强的吧,可以得到三个值,分别是行影响值,存储过程返回值,新的ID值。rnrn dsQuyMst.Tables["QuyMst"].Rows[0]["SHDOCO"] = "1111";//这句报错(未将对象引用设置到对象的实例。)rn rn Response.Write("Doco:" + newID.ToString());rnrn rn private void UpdateData()rn rnrn UpdateQuyMst(dsQuyMst);rnrn rn private void UpdateQuyMst(DataSet ds)rn rn string constr = GetConnectionStr();rn rn string ss = "Insert into F81001(SHDOCO,SHDCTO,SHBUSTYPE,SHLITM," +rn "SHDSC1,SHLOTN,SHCLTN,SHCLT,SHINST,SHTYPE) values(" +rn "@SHDOCO," +rn "@SHDCTO, @SHBusType," +rn "@SHLITM, @SHDSC1," +rn "@SHLOTN," +rn "@SHCLTN," +rn "@SHCLT," +rn "@SHINST,@SHTYPE)";rn rn SqlConnection con = new SqlConnection(constr);rn con.Open();rn SqlCommand sqlcmd = new SqlCommand(ss,con);rn SqlDataAdapter da = new SqlDataAdapter(sqlcmd);rn // ds.Tables["QuyMst"].Rows[0]["SHDOCO"] = newID;rn da.InsertCommand = sqlcmd;rn da.InsertCommand.Parameters.Add("@SHDOCO", SqlDbType.Int, 8, "SHDOCO");rn da.InsertCommand.Parameters.Add("@SHDCTO", SqlDbType.Char, 2, "SHDCTO");rn da.InsertCommand.Parameters.Add("@SHBUSTYPE", SqlDbType.Int, 4, "SHBUSTYPE");rn da.InsertCommand.Parameters.Add("@SHLITM", SqlDbType.Int, 8, "SHLITM");rn da.InsertCommand.Parameters.Add("@SHDSC1", SqlDbType.Int, 8, "SHDSC1");rn da.InsertCommand.Parameters.Add("@SHLOTN", SqlDbType.Int, 8, "SHLOTN");rn da.InsertCommand.Parameters.Add("@SHCLTN", SqlDbType.Int, 8, "SHCLTN");rn da.InsertCommand.Parameters.Add("@SHCLT", SqlDbType.Int, 8, "SHCLT");rn da.InsertCommand.Parameters.Add("@SHINST", SqlDbType.VarChar, 20, "SHINST");rn da.InsertCommand.Parameters.Add("@SHTYPE", SqlDbType.Char, 2, "SHTYPE");rnrn //ds.Tables["QuyMst"].Rows[0]["SHDOCO"] = newID;rnrnrn da.Update(ds.Tables["QuyMst"]);rn rnrnrnrnrn 论坛

没有更多推荐了,返回首页