sql_Activity(activity)
package com.example.myapplication_one;
import android.annotation.SuppressLint;
import android.content.ContentValues;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.support.v7.app.AppCompatActivity;
import android.os.Bundle;
import android.util.Log;
import android.view.View;
import android.widget.Button;
import android.widget.Toast;
public class sql_Activity extends AppCompatActivity {
Button button,button2,button3,button4,button5,button6,button7;
MyDatabase myDatabase;
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.sql_layout);
button = findViewById(R.id.button1);
button2 = findViewById(R.id.button2);
button3 = findViewById(R.id.button3);
button6 = findViewById(R.id.button6);
//初始化myDatabase对象
myDatabase = new MyDatabase(sql_Activity.this,"bookdata.db",null,1);
button.setOnClickListener(new View.OnClickListener() {
@Override
public void onClick(View view) {
/*
* SQLiteOpenHelper提供了两个实例化方法
* getReadableDatabase();
* getWritableDatabase();
* 如果存储空间已满, getReadableDatabase()以只读读取数据,getWritableDatabase();抛出异常
*/
//创建数据库的两种方法: myDatabase.getReadableDatabase(); -------- myDatabase.getWritableDatabase();
//myDatabase.getReadableDatabase();
myDatabase.getWritableDatabase();
}
});
button2.setOnClickListener(new View.OnClickListener() {
@Override
public void onClick(View view) {
//初始化myDatabase对象
myDatabase = new MyDatabase(sql_Activity.this,"bookdata.db",null,2);
}
});
button3.setOnClickListener(new View.OnClickListener() {
@Override
public void onClick(View view) {
SQLiteDatabase sqLiteDatabase = myDatabase.getWritableDatabase();
// sqLiteDatabase.execSQL("");//可以调用SQL语句发送器
/*
* sqLiteDatabase.insert添加数据
* 第一个参数:table:表名
* 第二个参数:nullColumnHack:可以添加null值列
* 第三个参数:values:ContentValues对象
*/
ContentValues contentValues = new ContentValues();// ContentValues()用于封装数据
contentValues.put("author","张三");
contentValues.put("price",4.5);
contentValues.put("pages",100);
contentValues.put("name","Java程序设计");
//添加数据
sqLiteDatabase.insert("book",null,contentValues);
//清除contentValues中的数据
contentValues.clear();
//添加第二条数据:
contentValues.put("author","李四");
contentValues.put("price",102);
contentValues.put("pages",203);
contentValues.put("name","快乐老家");
sqLiteDatabase.insert("book",null,contentValues);
contentValues.clear();
}
});
button4 = findViewById(R.id.button4);
button4.setOnClickListener(new View.OnClickListener() {
@Override
public void onClick(View view) {
SQLiteDatabase sqLiteDatabase = myDatabase.getWritableDatabase();
ContentValues contentValues = new ContentValues();
contentValues.put("price",30.5);
//更新数据
sqLiteDatabase.update("book",contentValues,"name=?",new String[]{"Java程序设计,快乐老家"});
}
});
//删除数据
button5 = findViewById(R.id.button5);
button5.setOnClickListener(new View.OnClickListener() {
@Override
public void onClick(View view) {
SQLiteDatabase sqLiteDatabase = myDatabase.getWritableDatabase();
// sqLiteDatabase.delete("book","name=?",new String[]{"快乐老家"});
sqLiteDatabase.delete("book","price>?",new String[]{"10"});
}
});
button6.setOnClickListener(new View.OnClickListener() {
@Override
public void onClick(View view) {
//myDatabase = new MyDatabase(sql_Activity.this,"bookdata.db",null,2);
SQLiteDatabase sqLiteDatabase = myDatabase.getWritableDatabase();
/*
* table:指定查询的表名
* columns:指定查询的列名
* selection:指定where的约束条件
* selectionArgs:指定where约束条件中占位符的值
* groupBy:指定需要groupBy列
* having:对于groupBy后的值进行进一步的约束
* orderBy:对于查询结果的排列方式
*/
Cursor cursor = sqLiteDatabase.query("book",null,null,
null,null,null,null);
if(cursor.moveToFirst()){
do {
@SuppressLint("Range") String author = cursor.getString(cursor.getColumnIndex("author"));
//getString()方法传递的是一个列标,而不是列名(key)
//cursor.getColumnIndex()得到当前这一列的列标
@SuppressLint("Range") String name = cursor.getString(cursor.getColumnIndex("name"));
@SuppressLint("Range") double price = cursor.getDouble(cursor.getColumnIndex("price"));
@SuppressLint("Range") int pages = cursor.getInt(cursor.getColumnIndex("pages"));
Log.i("tag_book",author+name+price+pages+"\n");//log:标签
Toast.makeText(sql_Activity.this, author+name+price+pages, Toast.LENGTH_SHORT).show();
}while (cursor.moveToNext());
}
}
});
button7 = findViewById(R.id.button7);
button7.setOnClickListener(new View.OnClickListener() {
@Override
public void onClick(View view) {
//myDatabase = new MyDatabase(sql_Activity.this,"bookdata.db",null,2);
SQLiteDatabase sqLiteDatabase = myDatabase.getWritableDatabase();
sqLiteDatabase.execSQL("insert into book (author,price,pages,name) values (?,?,?,?)",
new String[]{"长江","109.3","300","huhu"});
// sqLiteDatabase.execSQL("select * from book",null);
}
});
}
}
MyDatabase.java (java)
package com.example.myapplication_one;
import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.support.annotation.Nullable;
import android.widget.Toast;
public class MyDatabase extends SQLiteOpenHelper {
private static final String CREATE_BOOK = "create table book("+
"id integer primary key autoincrement,"+ //integer是数字型的,整型的
"author text,"+ //text用来输出字符串的,String类型的
"price real,"+ //real是浮点型的
"pages integer,"+
"name text)";
//补充:blob:二进制类型、布尔值类型
public static final String CREATE_CATEGORY = "create table category("+
"id integer primary key autoincrement,"+
"category_name text,"+
"category_code integory)";
Context MyContext;
public MyDatabase(@Nullable Context context, @Nullable String name, @Nullable SQLiteDatabase.CursorFactory factory, int version) {
super(context, name, factory, version);
MyContext = context;
}
@Override
//onCreate只运行一次,不管以后再加多少表,这个方法以前执行过,以后都不再执行。
public void onCreate(SQLiteDatabase db) {
db.execSQL(CREATE_BOOK);//SQL语句发送器
db.execSQL(CREATE_CATEGORY);//不可能运行成功,因为onCreate只运行一次
Toast.makeText(MyContext, "创建数据库book表", Toast.LENGTH_SHORT).show();
}
@Override
public void onUpgrade(SQLiteDatabase db, int i, int i1) {//实现更新数据库
db.execSQL("drop table if exists book");//如果表存在先把表删了
db.execSQL("drop table if exists category");//如果表存在先把表删了
onCreate(db);//表删除之后,没有了表,onCreate()方法就相当于还没有运行,可以进行运行。
}
}
sql_layout.xml
<?xml version="1.0" encoding="utf-8"?>
<RelativeLayout xmlns:android="http://schemas.android.com/apk/res/android"
android:layout_width="match_parent"
android:layout_height="match_parent">
<Button
android:id="@+id/button1"
android:layout_width="331dp"
android:layout_height="65dp"
android:layout_centerHorizontal="true"
android:text="创建数据库" />
<Button
android:id="@+id/button2"
android:layout_width="331dp"
android:layout_height="65dp"
android:layout_centerHorizontal="true"
android:layout_marginTop="100dp"
android:text="更新数据库" />
<Button
android:id="@+id/button3"
android:layout_width="331dp"
android:layout_height="65dp"
android:layout_centerHorizontal="true"
android:layout_marginTop="180dp"
android:text="添加数据" />
<Button
android:id="@+id/button4"
android:layout_width="331dp"
android:layout_height="65dp"
android:layout_marginTop="260dp"
android:layout_centerHorizontal="true"
android:text="更新数据" />
<Button
android:id="@+id/button5"
android:layout_width="331dp"
android:layout_height="65dp"
android:layout_marginTop="340dp"
android:layout_centerHorizontal="true"
android:text="删除数据" />
<Button
android:id="@+id/button6"
android:layout_width="331dp"
android:layout_height="65dp"
android:layout_centerHorizontal="true"
android:layout_marginTop="415dp"
android:text="查询" />
<Button
android:id="@+id/button7"
android:layout_width="331dp"
android:layout_height="65dp"
android:layout_centerHorizontal="true"
android:layout_marginTop="485dp"
android:text="查询111" />
</RelativeLayout>
对于运行结果:按照下图进行操作:
sava as在桌面上,然后在navicat Premium上查看结果: