Android数据存储方式——SQLite数据库的增删改查用法样例和注意事项.txt

2019-11-06 09:34:40

字体：大中小

来源：转载

供稿：网友

声明：1.sqlite是用的时候才创建2.oncreate:只执行一次。*3、执行SQL语句：db.execSQL("update user set username=?, passWord=? where id=?",new Object[]{user.getUsername(),user.getPassword(),user.getId()}); 注意设置的字段和条件字段之间不能加逗号，否则会报出 near "where": syntax error (code 1): 类型的错误思路：写一个User工具类，操作User表。步骤：１.新建User实体类 2.新建UserServcie工具类：有一个Helper类的属性，在UserServcie的构造函数中实例化Helper类的实例 3.UserService中写一个addUser(User); 4.写一个查询所有： public List<User> queryAll() { SQLiteDatabase db = helper.getReadableDatabase(); List<User> list = new ArrayList<>(); //ResultSet Cursor cursor = db.rawQuery("select * from user", null); while (cursor.moveToNext()) { int id = cursor.getInt(cursor.getColumnIndex("id")); String username = cursor.getString(cursor.getColumnIndex("username")); String password = cursor.getString(cursor.getColumnIndex("password")); User user = new User(id, username, password); list.add(user); } return list; } 5.activity中写页面，实例化控件（两个EditeTest,一个Button,TextView） 6.点击添加按钮，先插入数据，然后查询所有，展示到Textview上面。数据处理的类：package com.work.MySQLite.db;import android.content.Context;import android.database.Cursor;import android.database.sqlite.SQLiteDatabase;import com.work.mysqlite.entity.User;import java.util.ArrayList;import java.util.List;/** * Created by Administrator on 2017/2/28. * 用于操作数据库 */public class UserService { PRivate MySQLHelper helper; public UserService(Context context) { helper=new MySQLHelper(context); } //添加数据的方法： public void addUser(User user){ SQLiteDatabase db=helper.getWritableDatabase(); db.execSQL("insert into user(username,password) values (?,?) ",new Object[]{user.getUsername(),user.getPassword()}); db.close(); } //查询全部数据的方法： public List<User> queryAll(){ SQLiteDatabase db=helper.getReadableDatabase(); List<User> list=new ArrayList<>(); //查询返回结果集用Cursor类接收，表示游标（相当于下标） Cursor cursor= db.rawQuery("select * from user",null); while (cursor.moveToNext()){//如果有下一个游标，通过数据库中的相应字段获取值 int id=cursor.getInt(cursor.getColumnIndex("id")); String username=cursor.getString(cursor.getColumnIndex("username")); String password=cursor.getString(cursor.getColumnIndex("password")); User user=new User(id,username,password); list.add(user); } return list; } //删除数据的方法 public void delete(String id){ SQLiteDatabase db=helper.getWritableDatabase(); db.execSQL("delete from user where id=?",new Object[]{id}); db.close(); } //修改数据的方法 public void update(User user){ SQLiteDatabase db=helper.getWritableDatabase(); db.execSQL("update user set username=?, password=? where id=?",new Object[]{user.getUsername(),user.getPassword(),user.getId()}); db.close(); }}自定义adapterpackage com.work.mysqlite.adapter;import android.content.Context;import android.view.LayoutInflater;import android.view.View;import android.view.ViewGroup;import android.widget.BaseAdapter;import android.widget.EditText;import android.widget.TextView;import com.work.mysqlite.R;import com.work.mysqlite.entity.User;import java.util.List;/** * Created by Administrator on 2017/2/28. */public class UserAdapter extends BaseAdapter { private Context context; public List<User> list; private LayoutInflater inflater; public UserAdapter(Context context, List<User> list) { this.context = context; this.list = list; inflater=LayoutInflater.from(context); } @Override public int getCount() { return list.size(); } @Override public Object getItem(int position) { return list.get(position); } @Override public long getItemId(int position) { return position; } @Override public View getView(int position, View convertView, ViewGroup parent) { ViewHolder holder=null; if(convertView==null){ convertView=inflater.inflate(R.layout.user_item,null); holder=new ViewHolder(); holder.tv_id= (TextView) convertView.findViewById(R.id.tv_id); holder.tv_username= (TextView) convertView.findViewById(R.id.tv_username); holder.tv_pwd= (TextView) convertView.findViewById(R.id.tv_pwd); convertView.setTag(holder); }else { holder= (ViewHolder) convertView.getTag(); } User user= (User) getItem(position);//获取对应位置的user对象 holder.tv_id.setText(""+user.getId()); holder.tv_username.setText(user.getUsername()); holder.tv_pwd.setText(user.getPassword()); return convertView; } class ViewHolder{//声明自定义布局中的变量 private TextView tv_id,tv_username,tv_pwd; }}SQLite数据库的创建package com.work.mysqlite.db;import android.content.Context;import android.database.sqlite.SQLiteDatabase;import android.database.sqlite.SQLiteOpenHelper;/** * Created by Administrator on 2017/2/28. */public class MySQLHelper extends SQLiteOpenHelper { public MySQLHelper(Context context, String name, SQLiteDatabase.CursorFactory factory, int version) { super(context, name, factory, version); } public MySQLHelper(Context context) { super(context, "tset.db", null, 1); } @Override public void onCreate(SQLiteDatabase db) {//本方法只会执行一次，创建一张user表，有一个作为主键自增的int型的id String sql="create table user(id integer primary key autoincrement,username varchar(20),password varchar(20))"; db.execSQL(sql); } @Override public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) { }}主函数的调用方法package com.work.mysqlite;import android.database.sqlite.SQLiteDatabase;import android.support.v7.app.AppCompatActivity;import android.os.Bundle;import android.text.method.ScrollingMovementMethod;import android.view.View;import android.widget.Button;import android.widget.EditText;import android.widget.ListView;import android.widget.TextView;import com.work.mysqlite.adapter.UserAdapter;import com.work.mysqlite.db.MySQLHelper;import com.work.mysqlite.db.UserService;import com.work.mysqlite.entity.User;import java.util.ArrayList;import java.util.List;public class MainActivity extends AppCompatActivity implements View.OnClickListener{ private EditText sql_id,sql_username,sql_pwd; private Button btn_add,btn_delete,btn_update; private ListView sql_lv; public List<User> list =new ArrayList<>(); private UserService service; public UserAdapter adapter; @Override protected void onCreate(Bundle savedInstanceState) { super.onCreate(savedInstanceState); setContentView(R.layout.activity_main); intiView(); } private void intiView(){ service=new UserService(this); sql_id= (EditText) findViewById(R.id.sql_id); sql_username= (EditText) findViewById(R.id.sql_username); sql_pwd= (EditText) findViewById(R.id.sql_pwd); btn_add= (Button) findViewById(R.id.btn_add); btn_delete= (Button) findViewById(R.id.btn_delete); btn_update= (Button) findViewById(R.id.btn_update); sql_lv= (ListView) findViewById(R.id.sql_lv);// sql_tv.setMovementMethod(ScrollingMovementMethod.getInstance());//设置TextView滚动显示 btn_add.setOnClickListener(this); btn_delete.setOnClickListener(this); btn_update.setOnClickListener(this); adapter=new UserAdapter(this,list); sql_lv.setAdapter(adapter);//将自定义adapter应用于ListView中 quaryAll(); } @Override public void onClick(View v) { switch (v.getId()){ case R.id.btn_add : addUser(); break; case R.id.btn_delete: delete(); break; case R.id.btn_update: update(); break; } } //添加数据 public void addUser(){ String username=sql_username.getText().toString(); String password=sql_pwd.getText().toString(); User user=new User(username,password); service.addUser(user); quaryAll(); } //查询全部的方法 public void quaryAll(){ adapter.list=service.queryAll(); adapter.notifyDataSetChanged();//刷新页面// sql_tv.setText("");// for (int i = 0; i <list.size() ; i++) {// User user=list.get(i);// user.setId(i);// sql_tv.append("id:"+user.getId()+",username:"+user.getUsername()+",password:"+user.getPassword()+"/n");// } }//删除数据的方法 public void delete(){ String id=sql_id.getText().toString(); service.delete(id); quaryAll(); } //修改数据的方法 public void update(){ String id=sql_id.getText().toString(); String username=sql_username.getText().toString(); String password=sql_pwd.getText().toString(); User user=new User(Integer.parseInt(id),username,password); service.update(user); quaryAll(); }}

上一篇：Android网络请求操作httpurlconnection和httpclient基本使用

下一篇：Android中startActivity中的permission检测与UID机制