文章目录
1. 数据库的简介
SQLite是一个轻量级数据库,占用资源非常低,在内存中只需要占用几百KB的存储空间。
2. 数据库的创建
自定义个类MyHelper 继承 SQLiteOpenHelper
public class MyHelper extends SQLiteOpenHelper {
public MyHelper(Context context,String name,SQLiteDatabase.CursorFactory factory,int version) {
//参数: 上下文、数据库名称、游标工厂、数据库版本号
super(context, name, factory, version);
}
/**
* 数据库第一次被创建时调用该方法
*/
@Override
public void onCreate(SQLiteDatabase sqLiteDatabase) {
//创建数据库的表information
sqLiteDatabase.execSQL("CREATE TABLE information(_id INTEGER PRIMARY KEY AUTOINCREMENT, name VARCHAR(20), price INTEGER)");
}
/**
* 当数据库的版本号增加时调用
*/
@Override
public void onUpgrade(SQLiteDatabase sqLiteDatabase, int i, int i1) {
}
}
3. 数据库的增删改查
方式一:使用封装好的方法
//myHelper 使用构造方法创建数据库和 information表
myHelper = new MyHelper(getApplicationContext(),"test.db",null,2);
find(2);
private boolean find(int id) {
SQLiteDatabase db = myHelper.getReadableDatabase();
Cursor cursor = db.query("information",null,"id=?",new String[]{id+""},null,null,null);
//游标从0开始,会在查询结果集的行之上一行,判断有没有查到数据需要下移一行
boolean result = cursor.moveToNext();
db.close();
return result;
}
private void add(String name, String price) {
//获取可读的数据库资源
SQLiteDatabase db = myHelper.getWritableDatabase();
//创建ContextValue对象
ContentValues values = new ContentValues();
values.put("name",name);
values.put("price",price);
//完成插入数据操作
db.insert("information",null,values);
db.close();
}
private void update(String name, String price) {
SQLiteDatabase db = myHelper.getWritableDatabase();
ContentValues values = new ContentValues();
values.put("price",price);
db.update("information",values,"name=?",new String[]{name});
db.close();
}
private void delete(int id) {
SQLiteDatabase db = myHelper.getWritableDatabase();
db.delete("information","_id=?",new String[]{id+""});
db.close();
}
方式二: db.execSQL使用sql语句
public class InfoDao {
private MyHelper myHelper;
/**
* 初始化 利用myHelper构造函数创建数据库 和 information表
* @param context
*/
public InfoDao(Context context) {
this.myHelper = new MyHelper(context,"InfoDao.db",null,2);
}
/**
* 添加数据
*/
public void addInfo(String name,String price){
SQLiteDatabase db = myHelper.getWritableDatabase();
String sql = "insert into information(name,price) values(?,?)";
db.execSQL(sql,new Object[]{name,price});
db.close();
}
/**
* 删除数据
*/
public void deleteInfo(String name){
SQLiteDatabase db = myHelper.getWritableDatabase();
String sql = "delete from information where name=?";
db.execSQL(sql,new String[]{name});
db.close();
}
/**
* 修改数据
*/
public void updateInfo(String name,String price){
SQLiteDatabase db = myHelper.getWritableDatabase();
String sql = "update information set price=? where name=?";
db.execSQL(sql,new Object[]{price,name});
db.close();
}
/**
* 根据名字查询一条数据
*/
public String findInfo(String name){
String result_price;
String result_name;
SQLiteDatabase db = myHelper.getReadableDatabase();
String sql = "select * from information where name=?";
Cursor cursor = db.rawQuery(sql, new String[]{name});
if(cursor.getCount() == 0){
//没有数据
String erro = "没有数据";
return erro;
}else{
cursor.moveToFirst();
result_name = cursor.getString(1);
result_price = cursor.getString(2);
}
return result_price;
}
/**
* 查询所有
*/
public List<Map<String, String>> findAllInfo(){
//定义集合用于保存查询结果集
List<Map<String,String>> list = new ArrayList<>();
Map<String,String> map = new HashMap<>();
SQLiteDatabase db = myHelper.getReadableDatabase();
String sql = "select * from information";
Cursor cursor = db.rawQuery(sql,null);
if(cursor.getCount() == 0){
//没有数据
return null;
}else{
cursor.moveToFirst();
String first_name = cursor.getString(1);
String first_price = cursor.getString(2);
map.put(first_name,first_price);
while (cursor.moveToNext()){
String name2 = cursor.getString(1);
String price2 = cursor.getString(2);
map.put(name2,price2);
}
list.add(map);
}
return list;
}
}
public class MainActivity extends AppCompatActivity implements View.OnClickListener {
private EditText tv_price;
private EditText tv_name;
private TextView tv_results;
private Button btn_find;
private Button btn_findAll;
private Button btn_add;
private Button btn_update;
private Button btn_delete;
private MyHelper myHelper;
private InfoDao infoDao;
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_main);
tv_price = findViewById(R.id.tv_price);
tv_name = findViewById(R.id.tv_name);
tv_results = findViewById(R.id.tv_results);
btn_find = findViewById(R.id.btn_find);
btn_findAll = findViewById(R.id.btn_findAll);
btn_add = findViewById(R.id.btn_add);
btn_update = findViewById(R.id.btn_update);
btn_delete = findViewById(R.id.btn_delete);
btn_find.setOnClickListener(this);
btn_findAll.setOnClickListener(this);
btn_add.setOnClickListener(this);
btn_update.setOnClickListener(this);
btn_delete.setOnClickListener(this);
infoDao = new InfoDao(this);
}
@Override
public void onClick(View view) {
String param_name;
String param_price;
switch (view.getId()){
case R.id.btn_find:
String price = infoDao.findInfo(tv_name.getText().toString().trim());
tv_results.setText(" ");
tv_results.setText("\n\n"+price);
break;
case R.id.btn_findAll:
tv_results.setText(" ");
List<Map<String, String>> allInfoList = infoDao.findAllInfo();
for (Map<String,String> map :allInfoList){
for (String key:map.keySet()){
String value = map.get(key);
tv_results.append("\n");
tv_results.append(key+" : "+value+"\n");
}
}
break;
case R.id.btn_add:
param_name = tv_name.getText().toString().trim();
param_price = tv_price.getText().toString().trim();
infoDao.addInfo(param_name,param_price);
break;
case R.id.btn_update:
param_name = tv_name.getText().toString().trim();
param_price = tv_price.getText().toString().trim();
infoDao.updateInfo(param_name,param_price);
break;
case R.id.btn_delete:
param_name = tv_name.getText().toString().trim();
infoDao.deleteInfo(param_name);
break;
}
}
}
测试结果
layout_mian
<?xml version="1.0" encoding="utf-8"?>
<androidx.constraintlayout.widget.ConstraintLayout xmlns:android="http://schemas.android.com/apk/res/android"
xmlns:app="http://schemas.android.com/apk/res-auto"
xmlns:tools="http://schemas.android.com/tools"
android:layout_width="match_parent"
android:layout_height="match_parent"
tools:context=".MainActivity">
<EditText
android:id="@+id/tv_price"
android:layout_width="203dp"
android:layout_height="62dp"
android:text=""
app:layout_constraintBottom_toBottomOf="parent"
app:layout_constraintHorizontal_bias="0.774"
app:layout_constraintLeft_toLeftOf="parent"
app:layout_constraintRight_toRightOf="parent"
app:layout_constraintTop_toTopOf="parent"
app:layout_constraintVertical_bias="0.255" />
<EditText
android:id="@+id/tv_name"
android:layout_width="196dp"
android:layout_height="51dp"
android:text=""
app:layout_constraintBottom_toBottomOf="parent"
app:layout_constraintEnd_toEndOf="parent"
app:layout_constraintHorizontal_bias="0.734"
app:layout_constraintStart_toStartOf="parent"
app:layout_constraintTop_toTopOf="parent"
app:layout_constraintVertical_bias="0.141" />
<Button
android:id="@+id/btn_find"
android:layout_width="91dp"
android:layout_height="51dp"
android:text="查询"
app:layout_constraintBottom_toBottomOf="parent"
app:layout_constraintEnd_toEndOf="parent"
app:layout_constraintHorizontal_bias="0.05"
app:layout_constraintStart_toStartOf="parent"
app:layout_constraintTop_toTopOf="parent"
app:layout_constraintVertical_bias="0.88" />
<TextView
android:id="@+id/tv_results"
android:layout_width="343dp"
android:layout_height="221dp"
android:fadeScrollbars="false"
android:gravity="center_horizontal"
android:scrollbars="vertical"
android:text=""
app:layout_constraintBottom_toBottomOf="parent"
app:layout_constraintEnd_toEndOf="parent"
app:layout_constraintHorizontal_bias="0.547"
app:layout_constraintStart_toStartOf="parent"
app:layout_constraintTop_toTopOf="parent"
app:layout_constraintVertical_bias="0.669" />
<Button
android:id="@+id/btn_findAll"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:text="查询所有"
app:layout_constraintBottom_toBottomOf="parent"
app:layout_constraintEnd_toEndOf="parent"
app:layout_constraintHorizontal_bias="0.925"
app:layout_constraintStart_toStartOf="parent"
app:layout_constraintTop_toTopOf="parent"
app:layout_constraintVertical_bias="0.881" />
<TextView
android:id="@+id/textView4"
android:layout_width="88dp"
android:layout_height="22dp"
android:text="请输入名字"
app:layout_constraintBottom_toBottomOf="parent"
app:layout_constraintEnd_toEndOf="parent"
app:layout_constraintHorizontal_bias="0.179"
app:layout_constraintStart_toStartOf="parent"
app:layout_constraintTop_toTopOf="parent"
app:layout_constraintVertical_bias="0.15" />
<TextView
android:id="@+id/textView5"
android:layout_width="88dp"
android:layout_height="22dp"
android:text="请输入价格"
app:layout_constraintBottom_toBottomOf="parent"
app:layout_constraintEnd_toEndOf="parent"
app:layout_constraintHorizontal_bias="0.179"
app:layout_constraintStart_toStartOf="parent"
app:layout_constraintTop_toTopOf="parent"
app:layout_constraintVertical_bias="0.273" />
<Button
android:id="@+id/btn_add"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:text="新建"
app:layout_constraintBottom_toBottomOf="parent"
app:layout_constraintEnd_toEndOf="parent"
app:layout_constraintHorizontal_bias="0.058"
app:layout_constraintStart_toStartOf="parent"
app:layout_constraintTop_toTopOf="parent"
app:layout_constraintVertical_bias="0.976" />
<Button
android:id="@+id/btn_update"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:text="修改"
app:layout_constraintBottom_toBottomOf="parent"
app:layout_constraintEnd_toEndOf="parent"
app:layout_constraintHorizontal_bias="0.498"
app:layout_constraintStart_toStartOf="parent"
app:layout_constraintTop_toTopOf="parent"
app:layout_constraintVertical_bias="0.976" />
<Button
android:id="@+id/btn_delete"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:text="删除"
app:layout_constraintBottom_toBottomOf="parent"
app:layout_constraintEnd_toEndOf="parent"
app:layout_constraintHorizontal_bias="0.925"
app:layout_constraintStart_toStartOf="parent"
app:layout_constraintTop_toTopOf="parent"
app:layout_constraintVertical_bias="0.976" />
<TextView
android:id="@+id/textView"
android:layout_width="171dp"
android:layout_height="19dp"
android:text="查询所有结果列表如下:"
app:layout_constraintBottom_toBottomOf="parent"
app:layout_constraintEnd_toEndOf="parent"
app:layout_constraintHorizontal_bias="0.12"
app:layout_constraintStart_toStartOf="parent"
app:layout_constraintTop_toTopOf="parent"
app:layout_constraintVertical_bias="0.391" />
</androidx.constraintlayout.widget.ConstraintLayout>
2. ListView
activity_main.xml
<?xml version="1.0" encoding="utf-8"?>
<RelativeLayout xmlns:android="http://schemas.android.com/apk/res/android"
xmlns:tools="http://schemas.android.com/tools"
android:layout_width="match_parent"
android:layout_height="match_parent"
tools:context=".MainActivity">
<ListView
android:id="@+id/lv"
android:layout_width="match_parent"
android:layout_height="match_parent"
android:layout_margin="5dp"
android:divider="#d9d9d9"
android:dividerHeight="1dp">
</ListView>
</RelativeLayout>
list_item.xml
<?xml version="1.0" encoding="utf-8"?>
<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
android:layout_width="match_parent"
android:layout_height="match_parent"
android:gravity="center_vertical">
<ImageView
android:id="@+id/item_image"
android:layout_width="48dp"
android:layout_height="48dp"
android:layout_margin="8dp"
android:background="@drawable/wx" />
<TextView
android:id="@+id/item_tv"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:text="我是ListView的Item布局"
android:textSize="18sp" />
</LinearLayout>
MainActivity
public class MainActivity extends AppCompatActivity {
private ListView mlistView;
//需要适配的数据
private String[] names = {"京东商城", "QQ", "QQ斗地主", "新浪微博", "天猫",
"UC浏览器", "微信"};
//图片集合
private int[] icons = {R.drawable.jd, R.drawable.qq, R.drawable.dz,
R.drawable.xl, R.drawable.tm, R.drawable.uc,R.drawable.wx};
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_main);
//初始化ListView控件
mlistView = findViewById(R.id.lv);
//创建一个Adapter的实例
MyBaseAdapter mAdapter = new MyBaseAdapter();
//设置Adapter
mlistView.setAdapter(mAdapter);
}
class MyBaseAdapter extends BaseAdapter {
//得到item的总数
@Override
public int getCount() {
//返回ListView Item条目的总数
return names.length;
}
//得到Item代表的对象
@Override
public Object getItem(int position) {
//返回ListView Item条目代表的对象
return names[position];
}
//得到Item的id
@Override
public long getItemId(int position) {
//返回ListView Item的id
return position;
}
@Override
public View getView(int position, View convertView, ViewGroup parent) {
// //获取视图
// View view = View.inflate(MainActivity.this,R.layout.list_item,null);
//
// //找到控件
// TextView textView = view.findViewById(R.id.item_tv);
// ImageView imageView = view.findViewById(R.id.item_image);
// //数据适配
// textView.setText(names[position]);
// imageView.setBackgroundResource(icons[position]);
// return view;
ViewHolder holder;
if (convertView == null) {
convertView = LayoutInflater.from(getApplicationContext()).inflate(R.layout.list_item,parent,false);
holder = new ViewHolder();
holder.mTextView = (TextView)convertView.findViewById(R.id. item_tv);
holder.imageView=(ImageView) convertView.findViewById(R.id.item_image);
convertView.setTag(holder);
}else{
//如果不是第一次访问,直接通过getTag()来完成初始化
holder = (ViewHolder)convertView.getTag();
}
holder.mTextView.setText(names[position]);
holder.imageView.setBackgroundResource(icons[position]);
return convertView;
}
class ViewHolder{
TextView mTextView;
ImageView imageView;
}
}
}