Android——SQLite数据库使用详解

对于任何语言都是离不开数据库的，对于学习过其他语言的人来说，一般都是接触过数据库的。现在的主流移动设备像Android、iPhone等都使用SQLite作为复杂数据的存储引擎，在我们为移动设备开发应用程序时，也许就要使用到SQLite来存储我们大量的数据，所以我们就需要掌握移动设备上的SQLite开发技巧。对于Android平台来说，系统内置了丰富的API来供开发人员操作SQLite，我们可以轻松的完成对数据的存取。

下面来看一下SQLite的常用操作，结合前面的布局，文本框按钮样式写的登录界面，登录有验证。

开始准备把数据显示在页面上，要用到listview，以为这里还有一点问题，所以这里就把基本的语句先写了（语句都已测试过），数据显示，增加删除与listview详细操作请访问：https://blog.csdn.net/qq_40205116/article/details/88866785，数据存储使用的是LitePal2.0实现的。LitePal2.0使用也有详细讲解。

 

目录：

因为页面中颜色好像不能使用英文，所以在colos.xml文件中定义了常用的颜色

<?xml version="1.0" encoding="utf-8"?>
<resources>
    <color name="lightPink">#FFB6C1</color>
    <color name="red">#FF0000</color>
    <color name="black">#000000</color>
    <color name="white">#FFFFFF</color>
    <color name="blue">#0000FF</color>
    <color name="slategray">#708090</color>
    <color name="skyblue">#87CEEB</color>
    <color name="green">#008000</color>
    <color name="orange">#FFA500</color>
    <color name="gray">#808080</color>
</resources>

button_style.xml按钮样式：

<?xml version="1.0" encoding="utf-8"?>
<shape xmlns:android="http://schemas.android.com/apk/res/android"
    android:shape="rectangle" >		<!-- 设置按钮边框 -->
    
    <!-- 填充颜色 -->
    <solid android:color="#99ff00" />
    
    <padding 
        android:top="10dp"
        android:bottom="10dp" />

    <corners android:radius="50px" />	<!-- 设置边框弧度 -->
</shape>

edit_style.xml文本框样式：

<?xml version="1.0" encoding="utf-8"?>
<shape xmlns:android="http://schemas.android.com/apk/res/android"
    android:shape="rectangle" >	<!-- 设置文本框 -->
    
    <stroke 
        android:width="1px"
        android:color="@android:color/holo_blue_dark" />	<!-- 设置文本框边框属性，颜色 -->
        
	<padding 
	    android:left="30px"
	    android:right="10px"
	    android:top="5px"
	    android:bottom="5px" />		<!-- 设置内边距 -->
	    
	<corners android:radius="50px"/>	<!-- 设置弧度 -->
</shape>

activity_main.xml登录页面：

<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
    xmlns:tools="http://schemas.android.com/tools"
    android:id="@+id/LinearLayout1"
    android:layout_width="match_parent"
    android:layout_height="match_parent"
    android:orientation="vertical"
    android:background="@drawable/logo" >

    <EditText
        android:layout_width="0dp"
        android:layout_height="0dp" />

    <TextView
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:layout_gravity="center"
        android:layout_marginTop="80dp"
        android:textColor="#ff0000"
        android:textSize="35sp"
        android:text="数据库操作" />
    
    <EditText
        android:id="@+id/username"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:background="@drawable/edit_style"
        android:layout_margin="20dp"
        android:inputType="text"
        android:textSize="25dp"
        android:maxLines="12"
        android:singleLine="true"
        android:hint="用户名" />
    
    <EditText
        android:id="@+id/password"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:background="@drawable/edit_style"
        android:inputType="textPassword"
        android:layout_marginLeft="20dp"
        android:layout_marginRight="20dp"
        android:textSize="25dp"
        android:maxLines="12"
        android:singleLine="true"
        android:hint="密码" />

    <Button 
        android:id="@+id/submit"
        android:layout_marginTop="30dp"
        android:layout_marginLeft="20dp"
        android:layout_marginRight="20dp"
        android:layout_marginBottom="10dp"
        android:textSize="25dp"
        android:background="@drawable/button_style"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:text="登录" />
    
    <Button
        android:id="@+id/register"
        android:layout_marginLeft="20dp"
        android:layout_marginRight="20dp"
        android:textSize="25dp"
        android:background="@drawable/button_style"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:text="注册" />

</LinearLayout>

登录后的页面activity_index.xml，这个页面就显示了一个用户的姓名（开始还有几个测试按钮，之后感觉不好看删除了O(∩_∩)O哈哈~，你们可以自己加几个按钮测试）

<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
    xmlns:tools="http://schemas.android.com/tools"
    android:id="@+id/LinearLayout1"
    android:layout_width="match_parent"
    android:layout_height="match_parent"
    android:orientation="vertical"
    tools:context="${relativePackage}.${activityClass}" >

   	<TextView
        android:id="@+id/text"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:layout_margin="20dp" /> 

   	<ImageView
   	    android:layout_width="match_parent"
   	    android:layout_height="0.5dp"
   	    android:background="@color/gray"
   	    android:layout_marginLeft="20dp"
   	    android:layout_marginRight="20dp" />
   	
   	
   	
</LinearLayout>

页面，样式代码都在这里了，还一张背景图片，可以到网上随便找一张。下面来看看数据库操作的代码。

TUser.java相当于实体类。

package com.example.androiddemo01.model;

public class TUser {
	private Integer id;
	private String username;
	private String password;
	private String name;
	private String sex;
	public Integer getId() {
		return id;
	}
	public void setId(Integer id) {
		this.id = id;
	}
	public String getUsername() {
		return username;
	}
	public void setUsername(String username) {
		this.username = username;
	}
	public String getPassword() {
		return password;
	}
	public void setPassword(String password) {
		this.password = password;
	}
	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;
	}
	public TUser(Integer id, String username, String password, String name,
			String sex) {
		super();
		this.id = id;
		this.username = username;
		this.password = password;
		this.name = name;
		this.sex = sex;
	}
	public TUser() {
		super();
	}
	
}

DbOpenHelper.java链接数据库，创建数据库类

package com.example.androiddemo01.dao;

import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteDatabase.CursorFactory;
import android.database.sqlite.SQLiteOpenHelper;

public class DbOpenHelper extends SQLiteOpenHelper {

	private static final String DBNAME = "data.db";
	private static int version = 1;

	//content容器，name链接数据库名字，version版本号,factory游标工厂
	public DbOpenHelper(Context context) {
		super(context, DBNAME, null, version);
	}

	//运行DbOpenHelper时执行，建立表和数据库，没有数据库则建立，只有第一次运行执行
	@Override
	public void onCreate(SQLiteDatabase db) {
		String sql = "create table t_user("+
				"id integer primary key autoincrement not null,"+
				"username text(10),"+
				"password text(10),"+
				"name text(10),"+
				"sex text(2))";
		//执行SQL语句
		db.execSQL(sql);
		sql = "insert into t_user ('username', 'password', 'name', 'sex') values('admin','admin', '张三', '男')";
		db.execSQL(sql);
	}

	//更新，版本升级
	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		
	}
	
}

TUserDAO.java数据库操作类，增删改查。

package com.example.androiddemo01.dao;

import java.util.ArrayList;
import java.util.List;

import com.example.androiddemo01.model.TUser;

import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;

//持久层类，对数据库的操作
public class TUserDAO {
	private SQLiteDatabase db;
	
	//content容器,上下文
	public TUserDAO(Context context){
		super();
		DbOpenHelper helper = new DbOpenHelper(context);
		db = helper.getWritableDatabase();
	}

	//登录验证
	public TUser findUserNameAndPassword(String str, String str1) {
		String sql = "select * from t_user where username = ? and password = ?";
		Cursor cursor = db.rawQuery(sql, new String[] {str, str1});
		TUser user = null;
		//cursor.moveToNext判断下一个是否有值
		if(cursor.moveToNext()){
			user = new TUser(cursor.getInt(0), cursor.getString(1), cursor.getString(2), cursor.getString(3), cursor.getString(4));
		}
		return user;
	}
	
	//查找表中所有数据
	public List<TUser> findAll(){
		String sql = "select * from t_user";
		Cursor cursor = db.rawQuery(sql, null);
		List<TUser> list = new ArrayList<TUser>();
		if(cursor.moveToNext()){
			TUser user = new TUser(cursor.getInt(0), cursor.getString(1), cursor.getString(2), cursor.getString(3), cursor.getString(4));
			list.add(user);
		}
		return list;
	}
	
	//根据姓名模糊查找
	public List<TUser> searchByName(String key){
		List<TUser> list = new ArrayList<TUser>();
		key = "%"+key+"%";
		Cursor cursor = db.rawQuery("select * from t_user where name like ?", new String[]{key});
		while(cursor.moveToNext()){
			TUser user = new TUser(cursor.getInt(0), cursor.getString(1), cursor.getString(2), cursor.getString(3), cursor.getString(4));
			list.add(user);
		}
		return list;
	}
	
	//添加
	public void insert(TUser t){
		db.execSQL("insert into t_user(username, password, name, sex) values(?, ?, ?, ?)", new Object[] {t.getUsername(), t.getPassword(), t.getName(), t.getSex()});
		System.out.println("添加数据成功！");
	}
	
	//修改
	public void update (TUser user){
		db.execSQL("update t_user set name=?, sex = ?, password = ?, username = ? where id = ?",
				new Object[] {user.getName(), user.getSex(), user.getPassword(), user.getUsername(), user.getId()});
	}
	
	//根据id删除
	public void delete(int id){
		Integer i = new Integer(id);
		db.execSQL("delete from student where id = ?", new Object[]{i});
	}
}

MainActivity.java登录处理类

package com.example.androiddemo01;

import com.example.androiddemo01.dao.TUserDAO;
import com.example.androiddemo01.model.TUser;

import android.app.Activity;
import android.content.Intent;
import android.os.Bundle;
import android.view.Menu;
import android.view.MenuItem;
import android.view.View;
import android.view.View.OnClickListener;
import android.widget.Button;
import android.widget.TextView;
import android.widget.Toast;

public class MainActivity extends Activity implements OnClickListener {

	private TextView username;
	private TextView password;
	private Button submit;
	private Button register;

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);
        
        username = (TextView) findViewById(R.id.username);
        password = (TextView) findViewById(R.id.password);
        submit = (Button) findViewById(R.id.submit);
        register = (Button) findViewById(R.id.register);
        submit.setOnClickListener(this);
        register.setOnClickListener(this);
    }

	@Override
	public void onClick(View v) {
		TUserDAO user = new TUserDAO(this);
		//创建intent对象
		Intent intent = new Intent();
		switch (v.getId()) {
		case R.id.submit:
			String str = username.getText().toString();
			String str1 = password.getText().toString();
			if(str.equals("") || str1.equals("")){
				//弹框提示
				Toast.makeText(MainActivity.this, "账号密码不能为空", Toast.LENGTH_SHORT).show();
			}else{
				TUser t_user = user.findUserNameAndPassword(str, str1);
				if(t_user == null){
					Toast.makeText(MainActivity.this, "账号或密码输入错误", Toast.LENGTH_SHORT).show();
				}else{
					//跳转指定的Activity
					intent.setClass(MainActivity.this, IndexActivity.class);
					//传递参数
					intent.putExtra("name", t_user.getName());
					//执行方法
					startActivityForResult(intent, 2);
				}
			}
			break;

		default:
			break;
		}
	}
}

IndexActivity.java登录成功显示姓名。最后一个类了^_^

package com.example.androiddemo01;

import android.app.Activity;
import android.content.Intent;
import android.os.Bundle;
import android.view.Menu;
import android.view.MenuItem;
import android.widget.TextView;

public class IndexActivity extends Activity {
	
	private TextView text;

	@Override
	protected void onCreate(Bundle savedInstanceState) {
		super.onCreate(savedInstanceState);
		setContentView(R.layout.activity_index);
		
		text = (TextView) findViewById(R.id.text);
		
		//取得两个页面之间传递的intent
		Intent intent = getIntent();
		//取出传递的参数
		Bundle bundle = intent.getExtras();
		text.setText("欢迎您："+bundle.getString("name"));
	}
}

 

如果这个都掌握了，可以学一下litepal第三方数据库框架，地址：https://blog.csdn.net/qq_40205116/article/details/88741724