SQLite编程
1.案例
使用SQLite数据库实现教师管理系统,实现数据库的创建,表的创建以及数据的增删改查操作
技术:界面ListView和界面布局。SimpleAdapter和事件监听器。
效果图:
2.实现
2.1目录
2.2数据库帮助类
Helper类继承SQLiteHelper主要用于数据库的创建
/*
* 数据库帮助类
* 作用:完成数据库的创建,创建操作数据库表的对象
* */
public class Helper extends SQLiteOpenHelper {
// 创建构造函数
public Helper(@Nullable Context context, @Nullable String name, @Nullable SQLiteDatabase.CursorFactory factory, int version) {
super(context, name, factory, version);
}
// 不带游标的构造方法
public Helper(@Nullable Context context, @Nullable String name, int version) {
super(context, name, null, version);
}
// 创建对象的时候调用
@Override
public void onCreate(SQLiteDatabase db) {
// AUTO_INCREMENT设置自增的
String sql1="DROP TABLE if exists teacher";
String sql2="create table teacher(id int primary key,name varchar,sex varchar)";
db.execSQL(sql1);
db.execSQL(sql2);
}
// 更新数据库的时候调用
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
db.execSQL("alter table teacher add age int");
}
}
2.3实体类
public class Teacher {
private Integer id;
private String name;
private String sex;
public Teacher(Integer id, String name, String sex) {
this.id = id;
this.name = name;
this.sex = sex;
}
public Teacher() {
}
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getSex() {
return sex;
}
public void setSex(String sex) {
this.sex = sex;
}
@Override
public String toString() {
return "Teacher{" +
"id=" + id +
", name='" + name + '\'' +
", sex='" + sex + '\'' +
'}';
}
}
2.4dao层
重点
主要通过自定义的Helper完成基本的增删改查的操作
接口定义:
public interface TeacherDao {
//add-添加教师信息
public boolean addTeacher(Context context, Teacher teacher);
// remove-根据教师编号进行删除
public boolean removeTeacher(Context context,String id);
// update-更新教师信息
public boolean updateTeacher(Context context, Teacher teacher);
// getAllStudentByVague-模糊查询教师信息
public List<Teacher> getAllTeacherByVague(Context context, Teacher teacher);
// 获取所有教师信息
public List<Teacher> getAllTeachers(Context context);
}
实现类定义:
public class TeacherDaoImpl implements TeacherDao {
@Override
public boolean addTeacher(Context context, Teacher teacher) {
boolean flag=false;
try {
Helper helper=new Helper(context,"teacher_db",1);
SQLiteDatabase database= helper.getWritableDatabase();
ContentValues values=new ContentValues();
values.put("id",teacher.getId());
values.put("name",teacher.getName());
values.put("sex",teacher.getSex());
Long line=database.insert("teacher",null,values);
if (line>=1) {
flag=true;
}
else {
flag=false;
}
}
catch (SQLException e){
flag=false;
}
catch (Exception e){
flag=false;
}
finally {
return flag;
}
}
@Override
public boolean removeTeacher(Context context, String id) {
boolean flag=false;
try {
Helper helper=new Helper(context,"teacher_db",1);
SQLiteDatabase database= helper.getWritableDatabase();
int line=database.delete("teacher","id=?",new String []{id});
if (line>=1) {
flag=true;
}
else {
flag=false;
}
}
catch (SQLException e){
flag=false;
}
catch (Exception e){
flag=false;
}
finally {
return flag;
}
}
@Override
public boolean updateTeacher(Context context, Teacher teacher) {
boolean flag=false;
try {
Helper helper=new Helper(context,"teacher_db",1);
SQLiteDatabase database= helper.getWritableDatabase();
ContentValues values=new ContentValues();
values.put("name",teacher.getName());
values.put("sex",teacher.getSex());
int line=database.update("teacher",values,"id=?",new String []{teacher.getId()+""});
if (line>=1) {
flag=true;
}
else {
flag=false;
}
}
catch (SQLException e){
flag=false;
}
catch (Exception e){
flag=false;
}
finally {
return flag;
}
}
@Override
public List<Teacher> getAllTeacherByVague(Context context, Teacher teacher) {
List<Teacher> list=new ArrayList<>();
try {
Helper helper=new Helper(context,"teacher_db",1);
SQLiteDatabase database= helper.getWritableDatabase();
String sql="select * from teacher where id like ? and name like ? and sex like ? order by id";
Cursor cursor=database.rawQuery(sql,new String[]{
( teacher.getId()!=null?("%"+teacher.getId()+"%"):"%"),
( teacher.getName()!=null?("%"+teacher.getName()+"%"):"%"),
( teacher.getSex()!=null?("%"+teacher.getSex()+"%"):"%"),
}
);
while (cursor.moveToNext()){
Integer id= cursor.getInt(0);
String name= cursor.getString(1);
String sex= cursor.getString(2);
Teacher t=new Teacher(id,name,sex);
list.add(t);
// System.out.println(t);
}
}
catch (Exception e){
list=null;
}
finally {
return list;
}
}
@Override
public List<Teacher> getAllTeachers(Context context) {
List<Teacher> list=new ArrayList<>();
try {
Helper helper=new Helper(context,"teacher_db",1);
SQLiteDatabase database= helper.getWritableDatabase();
String sql="select * from teacher";
Cursor cursor=database.rawQuery(sql,null);
while (cursor.moveToNext()){
Integer id= cursor.getInt(0);
String name= cursor.getString(1);
String sex= cursor.getString(2);
Teacher teacher=new Teacher(id,name,sex);
list.add(teacher);
// System.out.println(teacher);
}
}
catch (Exception e){
list=null;
}
finally {
return list;
}
}
}
2.5service层
主要工作就是为界面展示提供服务
接口定义:
public interface TeacherService {
//add-添加教师信息
public boolean addTeacher(Context context, Teacher teacher);
// remove-根据教师编号进行删除
public boolean removeTeacher(Context context,String id);
// update-更新教师信息
public boolean updateTeacher(Context context, Teacher teacher);
// getAllStudentByVague-模糊查询教师信息
public List<Teacher> getAllTeacherByVague(Context context, Teacher teacher);
// 获取所有教师信息
public List<Teacher> getAllTeachers(Context context);
}
实现类定义:
public class TeacherServiceImpl implements TeacherService {
private TeacherDao teacherDao=new TeacherDaoImpl();
String regix="\\d*";
@Override
public boolean addTeacher(Context context, Teacher teacher) {
if (teacher==null||teacher.getId()==null||teacher.getId().toString().matches(regix)==false)
{
return false;
}
else
{return teacherDao.addTeacher(context,teacher);
}
}
@Override
public boolean removeTeacher(Context context, String id) {
if (id==null||id.equals("")||id.matches(regix)==false)
{
return false;
}
else
{ return teacherDao.removeTeacher(context,id);
}
}
@Override
public boolean updateTeacher(Context context, Teacher teacher) {
if (teacher==null||
teacher.getId()==null||
teacher.getId().equals("")||
teacher.getId().toString().matches(regix)==false)
{
return false;
}
else
{return teacherDao.updateTeacher(context,teacher);
}
}
@Override
public List<Teacher> getAllTeacherByVague(Context context, Teacher teacher) {
return teacherDao.getAllTeacherByVague(context,teacher);
}
@Override
public List<Teacher> getAllTeachers(Context context) {
return teacherDao.getAllTeachers(context);
}
}
2.6controller层
也就是展示界面的层也就是activity
主要完成的操作
控件的定义
控件的初始化
事件监听器的设置
public class TeacherActivity extends Activity {
//定义对象
private EditText id,name,sex;
private Button add,update,delete,select;
private ListView listView;
private TeacherService teacherService=new TeacherServiceImpl();
private List<Teacher> teachers;
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_teacher);
//初始化控件
init();
//获取控件中的信息
BtnListenner();
//初始化ListView
// listViewInit();
/* System.out.println(teacherService.getAllTeachers(this));
System.out.println(teacherService.updateTeacher(this,new Teacher(null,"张三","男")));
System.out.println(teacherService.getAllTeachers(this));
System.out.println(teacherService.getAllTeacherByVague(this,new Teacher(2,"张三","女")));*/
}
// 获取控件对象
private void init(){
id=findViewById(R.id.teacherId);
name=findViewById(R.id.teacherName);
sex=findViewById(R.id.teacherSex);
add=findViewById(R.id.btnAdd);
update=findViewById(R.id.btnUpdate);
delete=findViewById(R.id.btnDelete);
select=findViewById(R.id.btnSelect);
listView=findViewById(R.id.listview);
View view= LayoutInflater.from(this).inflate(R.layout.teacher_itemtitle,null);
listView.addHeaderView(view);
}
// 获取控件中的信息
private void BtnListenner(){
//增加事件监听
this.AddBtnListenner();
this.SelectBtnListenner();
this.DeleteBtnListenner();
this.UpdateBtnListenner();
this.listViewListener();
}
// 增加按钮的事件监听
private void AddBtnListenner(){
add.setOnClickListener(new View.OnClickListener() {
@Override
public void onClick(View view) {
String teacherId=id.getText().toString();
String teacherName=name.getText().toString();
String teacherSex=sex.getText().toString();
try {
Teacher teacher=new Teacher(Integer.parseInt(teacherId),teacherName,teacherSex);
boolean flag=teacherService.addTeacher(getApplicationContext(),teacher);
if (flag){
Toast.makeText(TeacherActivity.this, "添加成功!", Toast.LENGTH_SHORT).show();
}
else {
Toast.makeText(TeacherActivity.this, "添加失败!请确认教师编号是否重复!", Toast.LENGTH_SHORT).show();
}
}
catch (Exception e){
if (!teacherId.matches("\\d*")){
Toast.makeText(TeacherActivity.this, "请输入正确的教师编号!", Toast.LENGTH_SHORT).show();
}
else {
Toast.makeText(TeacherActivity.this, "请按规定格式输入教师信息!", Toast.LENGTH_SHORT).show();
}
}
id.setText("");
name.setText("");
sex.setText("");
//重新获取全部信息
teachers.clear();
teachers=teacherService.getAllTeachers(getApplicationContext());
listViewInit();
}
});
}
private void DeleteBtnListenner(){
delete.setOnClickListener(new View.OnClickListener() {
@Override
public void onClick(View view) {
String teacherId=id.getText().toString();
if (teacherId==null||teacherId.equals("")){
Toast.makeText(TeacherActivity.this, "请输入教师编号!", Toast.LENGTH_SHORT).show();
}
else if (!teacherId.matches("\\d*")){
Toast.makeText(TeacherActivity.this, "请输入正确的教师编号!", Toast.LENGTH_SHORT).show();
}
else
{
boolean flag=teacherService.removeTeacher(getApplicationContext(),teacherId);
if (flag){
Toast.makeText(TeacherActivity.this, "删除成功!", Toast.LENGTH_SHORT).show();
id.setText("");
name.setText("");
sex.setText("");
}
else {
Toast.makeText(TeacherActivity.this, "删除失败!请检查要删除的教师编号!", Toast.LENGTH_SHORT).show();
}
}
//重新获取全部信息
teachers.clear();
teachers=teacherService.getAllTeachers(getApplicationContext());
listViewInit();
}
});
}
private void UpdateBtnListenner(){
update.setOnClickListener(new View.OnClickListener() {
@Override
public void onClick(View view) {
String teacherId=id.getText().toString();
String teacherName=name.getText().toString();
String teacherSex=sex.getText().toString();
if (teacherId==null||teacherId.equals("")){
Toast.makeText(TeacherActivity.this, "请输入教师编号!", Toast.LENGTH_SHORT).show();
}
else if (!teacherId.matches("\\d*")){
Toast.makeText(TeacherActivity.this, "请输入正确的教师编号!", Toast.LENGTH_SHORT).show();
}
else{
try {
Teacher teacher=new Teacher(Integer.parseInt(teacherId),teacherName,teacherSex);
boolean flag=teacherService.updateTeacher(getApplicationContext(),teacher);
if (flag){
Toast.makeText(TeacherActivity.this, "修改成功!", Toast.LENGTH_SHORT).show();
}
else {
Toast.makeText(TeacherActivity.this, "修改失败!请检查要修改的信息!", Toast.LENGTH_SHORT).show();
}
}
catch (Exception e){
if (!teacherId.matches("\\d*")){
Toast.makeText(TeacherActivity.this, "请输入正确的教师编号!", Toast.LENGTH_SHORT).show();
}
else {
Toast.makeText(TeacherActivity.this, "请按规定格式输入教师信息!", Toast.LENGTH_SHORT).show();
}
}
}
id.setText("");
name.setText("");
sex.setText("");
//重新获取全部信息
teachers.clear();
teachers=teacherService.getAllTeachers(getApplicationContext());
listViewInit();
}
});
}
private void SelectBtnListenner(){
select.setOnClickListener(new View.OnClickListener() {
@Override
public void onClick(View view) {
String teacherId=id.getText().toString();
String teacherName=name.getText().toString();
String teacherSex=sex.getText().toString();
teachers=new ArrayList<>();
teachers.clear();
if (teacherId==null||teacherId.equals("")){
teachers= teacherService.getAllTeacherByVague(getApplicationContext(),new Teacher(null,teacherName,teacherSex));
}
else if (!teacherId.matches("\\d*")){
Toast.makeText(TeacherActivity.this, "请输入有效的教师编号", Toast.LENGTH_SHORT).show();
}
else {
teachers= teacherService.getAllTeacherByVague(getApplicationContext(),new Teacher(Integer.parseInt(teacherId),teacherName,teacherSex));
}
listViewInit();
}
});
}
// 初始化listView
private void listViewInit(){
List<Map<String,Object>> list=new ArrayList<>();
for(Teacher teacher :teachers){
Map<String,Object> map=new HashMap<>();
//key是子布局中的id的值
map.put("text_id",teacher.getId());
map.put("text_name",teacher.getName());
map.put("text_sex",teacher.getSex());
list.add(map);
}
SimpleAdapter adapter=new SimpleAdapter(
this,
list,R.layout.teacher_item,
new String[]{"text_id","text_name","text_sex"},
new int[]{R.id.text_id,R.id.text_name,R.id.text_sex});
listView.setAdapter(adapter);
}
//列表项的事件监听
private void listViewListener(){
listView.setOnItemClickListener(new AdapterView.OnItemClickListener() {
@Override
public void onItemClick(AdapterView<?> adapterView, View view, int position, long idx) {
if (position==0){
id.setText("");
name.setText("");
sex.setText("");
}
else {
id.setText(((TextView)view.findViewById(R.id.text_id)).getText());
name.setText(((TextView)(view.findViewById(R.id.text_name))).getText());
sex.setText(((TextView)view.findViewById(R.id.text_sex)).getText());
}
}
});
}
}
3.效果图
3.1模糊查询
3.2增加
增加之后会自动的进行刷新
3.3修改
修改之后也会重新获取全部的数据重新展示界面的信息
3.4删除
根据id进行删除
例如删除id=6的信息,删除成功之后也会重新获取全部的数据