Sqlite数据库的增、删、改、查方法

Sqlite 前端之家
前端之家收集整理的这篇文章主要介绍了Sqlite数据库的增、删、改、查方法，前端之家小编觉得挺不错的，现在分享给大家，也给大家做个参考。
<?xml version="1.0" encoding="utf-8"?>
<manifest xmlns:android="http://schemas.android.com/apk/res/android"
    package="com.example.databasedemo"
    android:versionCode="1"
    android:versionName="1.0" >

    <uses-sdk
        android:minSdkVersion="18"
        android:targetSdkVersion="18" />

    <uses-permission android:name="android.permission.WRITE_EXTERNAL_STORAGE" />

    <application
        android:allowBackup="true"
        android:name="com.example.databasedemo.MvpApplication"
        android:icon="@drawable/ic_launcher"
        android:label="@string/app_name"
        android:theme="@style/AppTheme" >
        <activity
            android:name=".MainActivity"
            android:label="@string/app_name" >
            <intent-filter>
                <action android:name="android.intent.action.MAIN" />

                <category android:name="android.intent.category.LAUNCHER" />
            </intent-filter>
        </activity>
    </application>

</manifest>


<LinearLayout 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"
    android:orientation="vertical"
    tools:context="com.example.databasedemo.MainActivity" >

 <Button
     android:id="@+id/copy"
     android:layout_width="wrap_content"
     android:layout_height="wrap_content"
     android:text="copy" />

    <Button
        android:id="@+id/insert"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:text="insert" />

    <Button
        android:id="@+id/dele"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:text="dele" />

    <Button
        android:id="@+id/update"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:text="update" />

    <Button
        android:id="@+id/query"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:text="query" />
    
</LinearLayout>

MvpApplication 
package com.example.databasedemo;  

import android.app.Application;  

public class MvpApplication extends Application{  
      
    private static MvpApplication mApplication;  
      
    @Override  
    public void onCreate() {  
        super.onCreate();  
        MvpApplication.mApplication = this;  
        VChatSipInfoStorage.getInstance();
    }  

      
    public static Application getApplication(){  
        return mApplication;  
    }  
}

DataBaseHelper 
package com.example.databasedemo;

import android.content.Context;
import android.database.sqlite.sqliteDatabase;
import android.database.sqlite.sqliteOpenHelper;
import android.util.Log;

public class DataBaseHelper extends sqliteOpenHelper {
	/**
	 * 数据库名字
	 */
	private static final String DATABASE_NAME = "dandy.db";

	/**
	 * 学生信息表名
	 */
	public static final String TABLE_NAME_STUDENT = "students";
	private static int version = 1;

	// 构造器
	public DataBaseHelper(Context context) {
		super(context,DATABASE_NAME,null,version);
	}

	// 带版本更新的构造器
	public DataBaseHelper(Context context,int version) {
		super(context,version);
		DataBaseHelper.version = version;
	}

	@Override
	public void onCreate(sqliteDatabase db) {
		createTables(db);
	}

	void createTables(sqliteDatabase db) {
		String sql = "CREATE TABLE IF NOT EXISTS " + TABLE_NAME_STUDENT + " ("
				+ BaseColum.ID + " INTEGER PRIMARY KEY AUTOINCREMENT,"
				+ BaseColum.NAME + " TEXT," + BaseColum.AGE
				+ " TEXT," + BaseColum.SEX + " TEXT,"
				+ BaseColum.ADD + " TEXT" + ")";
		db.execsql(sql);
	}

	@Override
	public void onUpgrade(sqliteDatabase db,int oldVersion,int newVersion) {

	}

	/**
	 * 学生信息表字段名
	 */
	static class BaseColum {
		public static final String ID = "id";
		public static final String NAME = "name";
		public static final String AGE = "age";
		public static final String SEX = "sex";
		public static final String ADD = "address";
	}
}

MainActivity 
package com.example.databasedemo;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.nio.channels.FileChannel;

import android.app.Activity;
import android.content.ContentValues;
import android.database.sqlite.sqliteDatabase;
import android.os.Bundle;
import android.os.Environment;
import android.util.Log;
import android.view.View;
import android.view.View.OnClickListener;
import android.widget.Button;
import android.widget.Toast;

public class MainActivity extends Activity implements OnClickListener {

	private Button insert;
	private Button dele;
	private Button update;
	private Button query;
	private Button copy;

	String[] nameArray = {"xxx","ooo","aaa","ggg"};
	@Override
	protected void onCreate(Bundle savedInstanceState) {
		super.onCreate(savedInstanceState);
		setContentView(R.layout.activity_main);
		VChatSipInfoStorage.getInstance();

		init();

	}

	private void init() {

		insert = (Button) findViewById(R.id.insert);
		dele = (Button) findViewById(R.id.dele);
		update = (Button) findViewById(R.id.update);
		query = (Button) findViewById(R.id.query);
		copy = (Button) findViewById(R.id.copy);

		insert.setOnClickListener(this);
		dele.setOnClickListener(this);
		update.setOnClickListener(this);
		query.setOnClickListener(this);
		copy.setOnClickListener(this);
	}

	@Override
	public void onClick(View v) {
		switch (v.getId()) {
		case R.id.copy:
			copyToSDCard();
			Toast.makeText(this,"已复制",0).show();
			break;
		case R.id.insert:
			
			VChatSipInfoStorage.getInstance().insertSduFromNamesArr(nameArray);
//			VChatSipInfoStorage.getInstance().insertStuAllColumn();
//			VChatSipInfoStorage.getInstance().insertStuSomeColumn();

			break;

		case R.id.dele:
//			VChatSipInfoStorage.getInstance().deleteStuByColumn();
//			VChatSipInfoStorage.getInstance().deleteStuById();
			
			break;
		case R.id.update:

//			VChatSipInfoStorage.getInstance().updataStuById();
			VChatSipInfoStorage.getInstance().updataStuByIdAndOtherColumn();
			break;
		case R.id.query:

			int queryStuCount = VChatSipInfoStorage.getInstance().queryStuCount();
			int queryStuByColumn = VChatSipInfoStorage.getInstance().queryStuByColumn();
			Log.e("TAG","query"+queryStuByColumn+queryStuCount);
			
			break;
		default:
			break;
		}
	}

	private void copyToSDCard() {
		if (!Environment.MEDIA_MOUNTED.equals(Environment
				.getExternalStorageState())) {
			Log.e("wyf","no sd");
			return;
		}
		this.getDatabasePath("dandy");
		File dbFile = new File(this.getDatabasePath("dandy") + ".db");
		Log.e("TAG",""+dbFile.getAbsolutePath());
		File file = new File(Environment.getExternalStorageDirectory(),"dandy.db");
		Log.e("TAG",""+file.getAbsolutePath());
		try {
			file.createNewFile();
			copyFile(dbFile,file);
		} catch (IOException e) {
			e.printStackTrace();
		}

	}

	@SuppressWarnings("resource")
	public static void copyFile(File src,File dst) throws IOException {

		FileChannel inChannel = new FileInputStream(src).getChannel();

		FileChannel outChannel = new FileOutputStream(dst).getChannel();

		try {

			inChannel.transferTo(0,inChannel.size(),outChannel);
			Log.e("TAG","copy");

		} finally {

			if (inChannel != null)

				inChannel.close();

			if (outChannel != null)

				outChannel.close();

		}

	}
}

VChatSipInfoStorage 
package com.example.databasedemo;

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

import android.app.Application;
import android.content.ContentValues;
import android.content.Context;
import android.content.CursorLoader;
import android.database.Cursor;
import android.database.sqlException;
import android.database.sqlite.sqliteDatabase;
import android.text.TextUtils;
import android.util.Log;
import android.widget.Toast;

public class VChatSipInfoStorage {

	private DataBaseHelper mDataBaseHelper;
	private sqliteDatabase msqlitedb;

	private static VChatSipInfoStorage mInstance;

	public static VChatSipInfoStorage getInstance() {
		if (null == mInstance) {
			synchronized (VChatSipInfoStorage.class) {
				if (null == mInstance) {
					mInstance = new VChatSipInfoStorage();
				}

			}
		}
		return mInstance;
	}

	private VChatSipInfoStorage() {
		super();
		if (msqlitedb == null) {
			openDataBase(MvpApplication.getApplication());
		}
	}

	private void openDataBase(Context context) {
		if (mDataBaseHelper == null) {
			mDataBaseHelper = new DataBaseHelper(context);
		}
		if (msqlitedb == null) {
			msqlitedb = mDataBaseHelper.getWritableDatabase();
		}
	}

	/**
	 * 插入数据 插入所有列
	 */

	public void insertStuAllColumn() {
		// 准备数据
		ContentValues values = new ContentValues();
		values.put("name","xxx");
		values.put("age","27");
		values.put("sex","nan");
		values.put("address","1358192");
		msqlitedb.insert(DataBaseHelper.TABLE_NAME_STUDENT,values);
	}

	/**
	 * 插入数据 插入部分列
	 */
	public void insertStuSomeColumn() {
		ContentValues values = new ContentValues();
		values.put("name","ooo");
		values.put("age","27");
		msqlitedb.insert(DataBaseHelper.TABLE_NAME_STUDENT,values);
	}

	/**
	 * 通过id删除，可以删除唯一的一条记录
	 */
	public void deleteStuById() {
		try {
			msqlitedb.delete(DataBaseHelper.TABLE_NAME_STUDENT,"id=?",new String[] { "1" });
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	/**
	 * 通过非id删除，可以删除唯一、不唯一的记录
	 */
	public void deleteStuByColumn() {
		try {
			msqlitedb.delete(DataBaseHelper.TABLE_NAME_STUDENT,"name=?",new String[] { "xxx" });
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	public void updataStuById() {
		ContentValues values = new ContentValues();
		values.put("name","yyy");

		// 更新资源database.update(table,values,whereClause,whereArgs)
		msqlitedb.update(DataBaseHelper.TABLE_NAME_STUDENT,new String[] { "1" });
	}

	public void updataStuByIdAndOtherColumn() {
		ContentValues values = new ContentValues();
		values.put("name","zzz");

		// 更新资源database.update(table,whereArgs)

		int update = msqlitedb.update(DataBaseHelper.TABLE_NAME_STUDENT,"id=? and age=?",new String[] { "2","27" });

		Log.e("TAG","" + update);
	}

	/**
	 * 获取记录总数
	 * 
	 * @return
	 */
	public int queryStuCount() {
		Cursor cursor = msqlitedb.query(DataBaseHelper.TABLE_NAME_STUDENT,null);
		while (cursor != null && cursor.getCount() > 0 && cursor.moveToNext()) {
			cursor.getInt(cursor.getColumnIndexOrThrow("id"));
			cursor.getInt(cursor.getColumnIndexOrThrow("name"));
			cursor.getInt(cursor.getColumnIndexOrThrow("age"));
			cursor.getInt(cursor.getColumnIndexOrThrow("sex"));
			cursor.getInt(cursor.getColumnIndexOrThrow("address"));
		}

		return cursor.getCount();
	}

	public int queryStuByColumn() {
		Cursor cursor = msqlitedb.query(DataBaseHelper.TABLE_NAME_STUDENT,"27" },null);
		while (cursor != null && cursor.getCount() > 0 && cursor.moveToNext()) {
			cursor.getInt(cursor.getColumnIndexOrThrow("id"));
			cursor.getInt(cursor.getColumnIndexOrThrow("name"));
			cursor.getInt(cursor.getColumnIndexOrThrow("age"));
			cursor.getInt(cursor.getColumnIndexOrThrow("sex"));
			cursor.getInt(cursor.getColumnIndexOrThrow("address"));
		}

		return cursor.getCount();
	}

	/**
	 * 获取所有微信好友的godinId
	 */
	public String[] queryAllStuName() {
		Cursor cursor = null;
		String names[] = null;
		try {
			cursor = msqlitedb.query(DataBaseHelper.TABLE_NAME_STUDENT,new String[] { "name" },null);
			if (cursor != null && cursor.getCount() > 0) {
				names = new String[cursor.getCount()];
				int i = 0;
				while (cursor.moveToNext()) {
					String name = cursor.getString(cursor
							.getColumnIndex("name"));
					if (!TextUtils.isEmpty(name)) {
						names[i] = name;
						i++;
					}
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (cursor != null) {
				cursor.close();
				cursor = null;
			}
		}
		return names;
	}

	public void insertSduFromNamesArr(String[] nameArray) {
		ContentValues values = null;
		String[] names = queryAllStuName();
		try {
			for (int i = 0; i < nameArray.length; i++) {
				values = new ContentValues();
				values.put("name",nameArray[i]);
				boolean contains = false;
				if (names != null && names.length > 0) {
					ArrayList<String> list = new ArrayList<String>(
							Arrays.asList(names));
					contains = list.contains(nameArray[i]);
					if (contains) {
						// 存在更新
						continue;
					} else {
						// 如果不存在
						msqlitedb.insert(DataBaseHelper.TABLE_NAME_STUDENT,values);
					}
				} else {
					msqlitedb.insert(DataBaseHelper.TABLE_NAME_STUDENT,values);
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
			throw new sqlException(e.getMessage());
		} finally {
			if (values != null) {
				values.clear();
				values = null;
			}
		}
	}

}

猜你在找的Sqlite相关文章

SQLite学习笔记
安装 在Windows上安装SQLite。 访问官网下载下Precompliled Binaries for Windows的两个压...
windows上如何安装Sqlite
一、安装 下载地址：http://www.sqlite.org/download.html 将Precompiled Binaries for Wi...
【Android数据存储】SQLite使用实例(附源码)
实例: 会员信息管理 功能：1.查看数据库 2.清空数据库 3.增加会员 4.删除会员 5.更新会员 ...
SQLite数据库管理
SQLite3常用命令
加密你的SQLite
关于SQLite SQLite是一个轻量的、跨平台的、开源的数据库引擎，它的在读写效率、消耗总量、...
SQLite这么娇小可爱，不多了解点都不行啊
在我眼里，MySQL和Oracle是这样的 而SQLite在是这样的 所以这么萌的数据库，我真的应该多了...
SQLite这么娇小可爱，不多了解点都不行啊
在我眼里，MySQL和Oracle是这样的 而SQLite在是这样的 所以这么萌的数据库，我真的应该多了...
SQLite 这么娇小可爱，不多了解点都不行啊
（点击上方蓝字，快速关注我们) 作者：空之境界 网址：http://supermao.cn/sqlitezhe-yao-...
SQLITE3 使用总结
SQLITE3 使用总结 投稿：hebedich 字体：[ 增加  减小] 类型：转载 时间：2014-08-19 Sql...
MySQLMsSQLOracleSqlitePostgre SQLMariadbMongoDBNoSQLHBaseJDBC
undo日志persistent-cmysql-error-postal-codesql-match-almysql-5.6mysql-8.0database-tri安装路径系统错误data_dir丢失文件主从同步sql_mode数据库目录匿名用户character_seID归零数据库位置查询表重复字段查询字段截断日志SUSPECT7391Remote ServeLinked Serve玄学问题登录不上开启远程访问