更新为默认值时出现问题
问题描述
定义一个Student表。
public class Student extends LitePalSupport {
//主键
public long id;
//姓名
public String name;
//是否是男生,且设置默认值为false
public boolean isMale = false;
//学号
public String sno;
@Override
public String toString() {
return "Student{" +
"id=" + id +
", name='" + name + '\'' +
", isMale=" + isMale +
", sno='" + sno + '\'' +
'}';
}
}
保存一条学生记录。
Student student = new Student();
student.name = "张三";
student.isMale = true;
student.sno = "0001";
student.save();
执行如下代码,更新isMale字段
Student student = LitePal.where("name = ?", "张三")
.findFirst(Student.class);
Logger.d("原student: "+student);
student.isMale = false;//更新isMale字段为false
boolean isUpdateSucc = student.update(student.id) > 0;
Logger.d("更新isMale是否成功:"+isUpdateSucc);
student = LitePal.where("name = ?", "张三")
.findFirst(Student.class);
Logger.d("新student: "+student);
输出日志如下:
原student: Student{name='张三', isMale=true, sno='0001'}
更新isMale是否成功:true
新student: Student{name='张三', isMale=true, sno='0001'}
从日志可以看出,isMale字段没有更新成功。
原因是LitePal的update方法仅当字段值不为默认值时才会更新该字段。
解决办法
方法一:使用setToDefault
方法
Student student = LitePal.where("name = ?", "张三")
.findFirst(Student.class);
Logger.d("原student: "+student);
// student.isMale = false;//更新isMale字段为false
student.setToDefault("isMale");//更新isMale字段为false
boolean isUpdateSucc = student.update(student.id) > 0;
Logger.d("更新isMale是否成功:"+isUpdateSucc);
student = LitePal.where("name = ?", "张三")
.findFirst(Student.class);
Logger.d("新student: "+student);
输出日志如下:
原student: Student{name='张三', isMale=true, sno='0001'}
更新isMale是否成功:true
新student: Student{name='张三', isMale=false, sno='0001'}
方法二:改用save()
方法保存数据
Student student = LitePal.where("name = ?", "张三")
.findFirst(Student.class);
Logger.d("原student: "+student);
student.isMale = false;//更新isMale字段为false
boolean isUpdateSucc = student.save();//改成用save()保存字段
Logger.d("更新isMale是否成功:"+isUpdateSucc);
student = LitePal.where("name = ?", "张三")
.findFirst(Student.class);
Logger.d("新student: "+student);
输出日志如下:
原student: Student{name='张三', isMale=true, sno='0001'}
更新isMale是否成功:true
新student: Student{name='张三', isMale=false, sno='0001'}
save()
方法是将各字段直接覆盖,不论当前字段值是否为默认值。
但是要注意的是,如果表存在与之关联的另一张表时,不建议用这种方法,因为会将关联表的外键清空。
参考办法:
Update data:不能从其他类型更新到0
更新关联表时出现问题
问题描述
Student和Book是一对多的关系。
public class Student extends LitePalSupport {
//主键
public long id;
//姓名
public String name;
//是否是男生,且设置默认值为false
public boolean isMale = false;
//学号
public String sno;
public List<Book> books = new ArrayList<>();
@Override
public String toString() {
return "Student{" +
"name='" + name + '\'' +
", isMale=" + isMale +
", sno='" + sno + '\'' +
", books=" + books +
'}';
}
}
public class Book extends LitePalSupport {
//主键
public long id;
//书名
public String name;
public Student student;
@Override
public String toString() {
return "Book{" +
"name='" + name + '\'' +
'}';
}
}
为“张三”增加2本书
Student student = LitePal.where("name = ?", "张三").findFirst(Student.class);
Book chineseBook = new Book();
chineseBook.name = "语文";
chineseBook.student = student;
chineseBook.save();
Book mathBook = new Book();
mathBook.name = "数学";
mathBook.student = student;
mathBook.save();
这时Student表和Book表如下
接下来,更新张三的学号,并为张三再加一本书
Student student = LitePal.where("name = ?", "张三").findFirst(Student.class);
Logger.d("查询到的student: "+student);
student.sno = "0002";
student.save();
Book englishBook = new Book();
englishBook.name = "英语";
englishBook.student = student;
englishBook.save();
执行上面代码后,输出日志如下
查询到的student: Student{name='张三', isMale=true, sno='0001', books=[]}
这时Student表和Book表如下
从图中可以看出原本的语文书和数学书的外键被置空了。因为LitePal默认的模式就是懒查询。所以查询出张三时,张三的books
为空集合。这时候直接调用save()
方法,LitePal会认为你想清除关联关系,所以把语文书和数学书的外键都置空了。
解决办法
方法一:改用update()
更新
Student student = LitePal.where("name = ?", "张三").findFirst(Student.class);
Logger.d("查询到的student: "+student);
student.sno = "0002";
student.update(student.id);
Book englishBook = new Book();
englishBook.name = "英语";
englishBook.student = student;
englishBook.save();
执行上面的代码,输出日志如下
查询到的student: Student{name='张三', isMale=true, sno='0001', books=[]}
这时Student表和Book表如下
方法二:改用激进查询
Student student = LitePal.where("name = ?", "张三").findFirst(Student.class, true);//此处改用激进查询
Logger.d("查询到的student: "+student);
student.sno = "0002";
student.save();
Book englishBook = new Book();
englishBook.name = "英语";
englishBook.student = student;
englishBook.save();
执行上面的代码后,输出如下日志
查询到的student: Student{name='张三', isMale=true, sno='0001', books=[Book{name='语文'}, Book{name='数学'}]}
Student表和Book表的结果和方法一相同。
参考办法:
数据表中的数据更新 使用save 不使用update 随后会导致用该表的id作为外键的表数据查询不到
拼接where查询语句时出现的问题
问题描述
拼接字段值为汉字时抛出异常
String whereName = "name = 张三";
Student student = LitePal.where(whereName).findFirst(Student.class);
Logger.d("查询到的student: "+student);
执行上面的代码会抛出如下异常
LitePalSupportException: no such column: 张三 (code 1): , while compiling: SELECT * FROM student WHERE name = 张三 LIMIT 1
拼接字段值为第一个数字为0的数字字符串时查询不出记录
String whereSno = "sno = 0001";
Student student = LitePal.where(whereSno).findFirst(Student.class);
Logger.d("查询到的student: "+student);
执行上面代码,输出日志如下
查询到的student: null
解决办法
方法一:拼接时字段值用单引号括起来
// String whereName = "name = 张三";
String whereName = "name = '张三'";
Student student = LitePal.where(whereName).findFirst(Student.class);
Logger.d("查询到的student: "+student);
// String whereSno = "sno = 0001";
String whereSno = "sno = '0001'";
Student student = LitePal.where(whereSno).findFirst(Student.class);
Logger.d("查询到的student: "+student);
执行上面代码后,输出日志如下
查询到的student: Student{name='张三', isMale=true, sno='0001'}
查询到的student: Student{name='张三', isMale=true, sno='0001'}
方法二:使用LitePal的占位符(?)
Student student = LitePal.where("name = ?", "张三").findFirst(Student.class);
Logger.d("查询到的student: "+student);
student = LitePal.where("sno = ?", "0001").findFirst(Student.class);
Logger.d("查询到的student: "+student);
执行上面代码后,输出日志如下
查询到的student: Student{name='张三', isMale=true, sno='0001'}
查询到的student: Student{name='张三', isMale=true, sno='0001'}