数据库

最新推荐文章于 2024-09-21 11:30:16 发布

Rokends

最新推荐文章于 2024-09-21 11:30:16 发布

阅读量327

点赞数

分类专栏：随笔文章标签：数据库

本文链接：https://blog.csdn.net/rokends/article/details/49619015

版权

随笔专栏收录该内容

106 篇文章 0 订阅

订阅专栏

<span style="font-size:18px;"></span><h2><span style="font-size:18px;">Person </span></h2>

<span style="font-size:18px;">
</span>

<span style="font-size:18px;">package com.xh.tx.bean;

public class Person {
	private Integer _id;
	private String name;
	private Integer age;
	
	public Integer get_id() {
		return _id;
	}
	public void set_id(Integer _id) {
		this._id = _id;
	}
	public String getName() {
		return name;
	}
	public void setName(String name) {
		this.name = name;
	}
	public Integer getAge() {
		return age;
	}
	public void setAge(Integer age) {
		this.age = age;
	}
	public Person() {
		super();
		// TODO Auto-generated constructor stub
	}
	public Person(Integer _id, String name, Integer age) {
		super();
		this._id = _id;
		this.name = name;
		this.age = age;
	}
	@Override
	public String toString(){
		return "Person[_id=]"+_id+",name="+name+",age"+age+"]";
		
	}

}</span>

PersonDao

<span style="font-size:18px;">package com.xh.tx.dao;

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

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

import com.xh.tx.SQLite.SQLite;
import com.xh.tx.bean.Person;

public class PersonDao {
	private SQLite helper;
	
	public PersonDao(Context context){
		helper=new SQLite(context,null,null,-1);
		
	}
	
	/**
	 * 添加一个对象
	 * sql：insert into person(name,age)values('zs',20);
	 * 缺陷：1、传递参数很麻烦 2、不能有返回值
	 */

	public void savePerson(Person p){
		
		String sql="insert into person(name,age)values(?,?);";
		
		SQLiteDatabase db=helper.getWritableDatabase();
		
		//db代表的是一个数据库的一个连接
		if(db.isOpen()){
			
			db.execSQL(sql, new Object[] {p.getName(),p.getAge()});
			db.close();
		}
	}
		
	/**
	 *  删除一个对象
	 *  sql:delete from person where _id=1;
	 */
	
	public void deletePerson(Integer id){
		
		String sql="delete from person where _id=?;";
		SQLiteDatabase db=helper.getWritableDatabase();
		
		if(db.isOpen()){
			
			db.execSQL(sql, new Integer[] {id});
			db.close();
			
		}
	}
	/**
	 * 修改一个对象
	 * sql:update person set name='cccc' where _id=1;
	 */
	
	public void updatePerson(Person p){
		
		String sql="update person set name=? where _id=?;";
		
		SQLiteDatabase db=helper.getWritableDatabase();
		
		if(db.isOpen()){
			
			db.execSQL(sql, new Object[] {p.getName(),p.get_id()});
			db.close();
		}
	}
	
	/**
	 * 查询所有
	 * sql:select * from person;
	 */
	
	public List<Person> queryPerson(){
		
		String sql="select _id,name,age from person;";
		SQLiteDatabase db=helper.getWritableDatabase();
		List<Person>list=null;
		
		if(db.isOpen()){
			
			Cursor cursor=db.rawQuery(sql, null);//查询
			
			//cursor.getCount()查看执行sql以后返回的结果集的个数
			if(cursor != null && cursor.getCount()>0){
				
				list=new ArrayList<Person>();
				
				while(cursor.moveToNext()){
					
					Integer id=cursor.getInt(0);
					String name=cursor.getString(1);
					Integer age=cursor.getInt(2);
					
					list.add(new Person(id,name,age));
					
				}
			}
		}
		return list;		
	}
}

</span>

PersonDao1

<span style="font-size:18px;">package com.xh.tx.dao;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.util.Log;

import com.xh.tx.SQLite.SQLite;
import com.xh.tx.bean.Person;

public class PersonDao1 {

		private static final String TAG = "PersonDao1";
		
		private SQLite helper;
		
		public PersonDao1(Context context){
			helper =new SQLite(context,null,null,-1);
		}
		
		//添加数据
		public void savePerson(Person p){
			
			SQLiteDatabase db=helper.getWritableDatabase();
			
			if(db.isOpen()){
				
				//nullColumnHack 如果数据库里面的name设计的时候不允许为空，但是你传递过来的参数是空，
				//如果不设置这个nullColumnHack参数那么就会报错
				//如果设置nullColumnHack这个参数的值为name那么不会报错
				
				ContentValues values = new ContentValues();
				
				values.put("name", p.getName());
				values.put("age", p.getAge());
				
				Long id=db.insert("person", null, values);
				
				Log.d(TAG,"=============:"+id);
				db.close();
			}
		}
		
		//删除数据
		public void deletePerson(Integer id){
			
			SQLiteDatabase db=helper.getWritableDatabase();
			
			if(db.isOpen()){
				
				String whereClause="_id=?";
				String [] whereArgs=new String []{String.valueOf(id)};
				
				int _id=db.delete("person", whereClause, whereArgs);
				
				Log.d(TAG, "===========:"+_id);
				db.close();
				
			}
		} 
		
		//修改数据
		public void updatePerson(Person p){
			
			SQLiteDatabase db=helper.getWritableDatabase();
			if(db.isOpen()){
				
				ContentValues values=new ContentValues();
				values.put("name", "wtw");
				values.put("age", "2");
				
				String whereClause="_id=?";
				String [] whereArgs=new String []{String.valueOf(p.get_id())};
				
				db.update("person", values, whereClause, whereArgs);
				db.close();
				
			}
		}
		
		//
		public void queryItem(Integer id){
			
			SQLiteDatabase db=helper.getWritableDatabase();
			if(db.isOpen()){
				
				String [] columns=new String[]{"_id","name","age"};
				String selection="_id=?";
				String [] selectionArgs = new String []{String.valueOf(id)};
				String groupBy=null;//按什么什么分组
				String having =null;//如果select里面包含组函数的时候，不能用where去查询 就只能用having
				String orderBy=null;//按什么排序 order by id desc；
				
				Cursor cursor =db.query("person", columns, selection, selectionArgs, groupBy, having, orderBy);
				
				if(null!= cursor && cursor.moveToFirst()){
					
					Integer _id=cursor.getInt(0);
					String name = cursor.getString(1);
					Integer age=cursor.getInt(2);
					
					Log.d(TAG,"_id="+_id+"name="+name+"age="+age);
					db.close();
				}
			}
			
		}
} </span>

MainActivity

<span style="font-size:18px;">package com.xh.tx.SQLite;

import android.app.Activity;
import android.os.Bundle;
import android.view.Menu;
import android.view.MenuItem;

public class MainActivity extends Activity {

	@Override
	protected void onCreate(Bundle savedInstanceState) {
		super.onCreate(savedInstanceState);
		setContentView(R.layout.activity_main);
	}

	@Override
	public boolean onCreateOptionsMenu(Menu menu) {
		// Inflate the menu; this adds items to the action bar if it is present.
		getMenuInflater().inflate(R.menu.main, menu);
		return true;
	}

	@Override
	public boolean onOptionsItemSelected(MenuItem item) {
		// Handle action bar item clicks here. The action bar will
		// automatically handle clicks on the Home/Up button, so long
		// as you specify a parent activity in AndroidManifest.xml.
		int id = item.getItemId();
		if (id == R.id.action_settings) {
			return true;
		}
		return super.onOptionsItemSelected(item);
	}
}</span>

SQLite

<span style="font-size:18px;">package com.xh.tx.SQLite;

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

public class SQLite extends SQLiteOpenHelper {
	
	/**
	 * 函数的作用：构造方法用来创建工具类
	 * @param context 上下文环境
	 * @param name  数据库的名称
	 * @param factory  游标对象 Cursor
	 * @param version   数据库的版本 不能从0开始
	 * 
	 */

	public SQLite(Context context, String name, CursorFactory factory,
			int version) {
		super(context, "test.db", null, 1);
	
	}
	/**
	 * 用来创建数据库
	 */
	@Override
	public void onCreate(SQLiteDatabase db) {
		
		String sql="create table person(_id integer primary key,name varchar(20),age integer);";
	
			db.execSQL(sql);//真正执行数据的创建
	}

		//数据库版本更新
	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
	
		
	}
	//每次打开数据库的时候调用
	@Override
	public void onOpen(SQLiteDatabase db) {
		super.onOpen(db);
	}
}
</span>

TestDB

<span style="font-size:18px;">package com.xh.tx.test;

import java.util.List;

import com.xh.tx.SQLite.SQLite;
import com.xh.tx.bean.Person;
import com.xh.tx.dao.PersonDao;

import android.test.AndroidTestCase;
import android.util.Log;

public class TestDB extends AndroidTestCase{
	
	private static final String TAG = "TestDB";

			
		public void testdb(){
			
			SQLite helper=new SQLite(getContext(),null,null,-1);
			helper.getWritableDatabase();
		}
		
		//添加数据
		public void testsavePerson(){
			Person p=new Person (1,"lisa",23);
			PersonDao dao=new PersonDao(getContext());
			
			dao.savePerson(p);
			
		}
		
		//删除数据
		public void testdeletePerson(){
			
			PersonDao dao=new PersonDao(getContext());
			dao.deletePerson(1);
			
		}
		
		//修改数据
		public void testupdatePerson(){
			
			Person p=new Person(1,"zhoujielun",36);
			PersonDao dao=new PersonDao(getContext());
			dao.updatePerson(p);
		}
		
		//
		public void testQuery(){
			
			PersonDao dao=new PersonDao(getContext());
			List<Person> list=dao.queryPerson();
			
			for(Person p:list){
				Log.d(TAG, p.toString());
			}		
		}
}</span>