这篇记录一下Android自带了的数据库——SQLite和对话框——AlertDialog。(其实就是一个课后作业,必须得用SQLite😐)。要求是:
- 使用SQLite创建一个数据库。
- 两张表,一张用户表user,存放用户信息;一张行程表,村放用户的行程信息。
- 初始插入两个用户,每个用户两个行程。
- 尽量实现增删查改操作。
看起来真简单,但是过段时间我肯定又忘了我写的啥💀。而且以前写代码习惯不好,注释很少写,还喜欢乱起名字,借着这个整理、记录的机会改改,多打点注释,改改起名的习惯😑。
贴代码了。
最终效果
先看看最终效果。
目录结构
继承SQLiteOpenHelper类
- myHelper.java
public class myHelper extends SQLiteOpenHelper {
//实现父类的构造方法,context:当前上下文环境;name:数据库名称;factory:游标;version:版本号,这里指你的数据库版本号,不是SQLite的版本号
public myHelper(@Nullable Context context) {
super(context, "text.db", null, 1);
}
@Override
public void onCreate(SQLiteDatabase db) {
String create1 = "create table user(uid INTEGER PRIMARY KEY AUTOINCREMENT,uname varchar(20),upsd int)";
String create2 = "create table route(rid INTEGER PRIMARY KEY AUTOINCREMENT,uid int,rname varchar(20),rrouting text,rtime date,foreign key(uid) references user(uid))";
//创建用户数表user
db.execSQL(create1);
//创建行程表route
db.execSQL(create2);
initData(db);
}
//表初始数据
public void initData(SQLiteDatabase database){
//用ContentValues类的键值对来插入数据,两种方法都试试:)
ContentValues values = new ContentValues();
//键值对的键必须是表中的属性名
values.put("uname","阿涛");
values.put("upsd",123456);
database.insert("user",null,values);
values.clear();
//用SQL语句插入数据,我还是喜欢用这种方法:)
database.execSQL("insert into user(uid,uname,upsd) values (null,'阿叶',654321)");
database.execSQL("insert into route(rid,uid,rname,rrouting,rtime) values (null,'1','上课','家->学校->教室','2021-05-04')");
database.execSQL("insert into route(rid,uid,rname,rrouting,rtime) values (null,'1','回家','教室->地铁->家','2021-05-04')");
database.execSQL("insert into route(rid,uid,rname,rrouting,rtime) values (null,'2','上课','寝室->教室','2021-05-05')");
database.execSQL("insert into route(rid,uid,rname,rrouting,rtime) values (null,'2','回寝室','教室->寝室','2021-05-05')");
//看看数据插入没有
print(database);
}
//查询route表数据
private void print(SQLiteDatabase db){
String str = "";
//用SQL语句查询route所有数据项,返回一个游标对象
Cursor cursor = db.rawQuery("select * from route",null);
//只要非空就继续查询下一个数据项
while (cursor.moveToNext()){
str += (cursor.getString(0)+"、 "+cursor.getString(1)+" "+
cursor.getString(2)+" "+cursor.getString(3))+"\r\n";
}
//logcat打印
Log.i("yyg",str);
}
//版本升级
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
}
}
操作管理Manager类
- DBManager
public class DBManager {
private myHelper helper;
private SQLiteDatabase database;
public DBManager(Context context){
helper = new myHelper(context); //获取到helper对象,此方法初次调用会创建数据库
database = helper.getWritableDatabase(); //得到可读写数据库对象
}
public int queryyon(String name,String password){
Cursor cursor = database.rawQuery("select uname,upsd from user " +
"where uname=? AND upsd=?",new String[]{name,password}); //用SQL语句查询表里是否有传入的用户名和密码,返回一个游标对象
return cursor.getCount(); //方法返回查询结果,如果存在返回1,不存在返回0
}
public Cursor query(String name){
Cursor cursor = database.rawQuery("select rid,rname,rrouting,rtime from user,route " +
"where user.uname=? AND user.uid=route.uid order by date(rtime)",new String[]{name}); //同上,rawQuery方法的SQL语句的参数用new String[]{}传入
return cursor;
}
public void delete(String name){
database.delete("route","rname=?",new String[]{name}); //删除操作用SQLite的delete方法更方便点
}
public void update(String uname, String name, String ing, String time, String bname, String bing, String btime){
Cursor cursor = database.rawQuery("select uid from user where uname=?",new String[]{uname}); //查询该用户对应的用户ID
cursor.moveToPosition(0); //必须将游标移动至初始位,因为在上述查询中游标已经移动至最后,否则会导致该页面直接崩溃
int uid = cursor.getInt(cursor.getColumnIndex("uid")); //获取到ID值
database.execSQL("update route set rname=?,rrouting=?,rtime=? " +
"where uid=? and rname=? and rrouting=? and rtime=?",new Object[]{name,ing,time,uid,bname,bing,btime}); //使用execSQL方法需要使用new Object[]{}传入参数
cursor.close();
}
public void insert(String uname, String name, String ing, String time){
Cursor cs = database.rawQuery("select uid from user where uname=?",new String[]{uname});
cs.moveToPosition(0);
int uid = cs.getInt(cs.getColumnIndex("uid"));
database.execSQL("insert into route(rid,uid,rname,rrouting,rtime) values (null,?,?,?,?)",new Object[]{uid,name,ing,time}); //同上
cs.close();
}
public void closeDB(){
database.close();
} //关闭数据库连接
}
登录界面
司空见惯的登陆界面了……
- activity_main.xml
<?xml version="1.0" encoding="utf-8"?>
<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"
tools:context=".MainActivity"
android:orientation="vertical"
android:padding="10dp">
<LinearLayout
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:orientation="horizontal"
android:layout_marginTop="150dp">
<TextView
android:layout_width="0dp"
android:layout_height="match_parent"
android:layout_weight="2"
android:text="用户名:"
android:textSize="20sp"
android:gravity="center"/>
<EditText
android:id="@+id/username"
android:layout_width="0dp"
android:layout_height="45dp"
android:layout_weight="8"
android:background="@drawable/bg_circle"
android:hint="点击输入用户名"
android:paddingLeft="5dp"/>
</LinearLayout>
<LinearLayout
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:orientation="horizontal"
android:layout_marginTop="20dp">
<TextView
android:layout_width="0dp"
android:layout_height="match_parent"
android:layout_weight="2"
android:text="密码:"
android:textSize="20sp"
android:gravity="center"/>
<EditText
android:id="@+id/password"
android:layout_width="0dp"
android:layout_height="45dp"
android:layout_weight="8"
android:background="@drawable/bg_circle"
android:hint="点击输入密码"
android:paddingLeft="5dp"/>
</LinearLayout>
<Button
android:id="@+id/login"
android:layout_width="match_parent"
android:layout_height="50dp"
android:background="@drawable/bg_buttonchange"
android:text="登录"
android:textSize="25sp"
android:layout_gravity="center"
android:layout_marginTop="40dp"/>
</LinearLayout>
- MainActivity.java
public class MainActivity extends AppCompatActivity {
private EditText username,password;
private Button login;
private DBManager manager;
private String TAG = "MainActivity";
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_main);
username = findViewById(R.id.username);
password = findViewById(R.id.password);
login = findViewById(R.id.login);
manager = new DBManager(this); //实例化manager对象,然后调用公有方法操作数据库
login.setOnClickListener(new View.OnClickListener() {
@Override
public void onClick(View v) {
String user = username.getText().toString();
String psd = password.getText().toString();
//查询该用户名和密码是否存在该数据库的user表里
if (manager.queryyon(user,psd) != 0){
Toast.makeText(getApplicationContext(),"登陆成功!",Toast.LENGTH_SHORT).show();
Intent intent = new Intent(MainActivity.this,LoginActivity.class);
intent.putExtra("username",user); //把用户名传给下个activity
startActivity(intent);
}
else
Toast.makeText(getApplicationContext(),"登录失败!\r\n请检查您的用户名或密码!",Toast.LENGTH_SHORT).show();
}
});
}
@Override
protected void onDestroy() {
super.onDestroy();
manager.closeDB();
}
}
新增行程对话框界面
- addline.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:orientation="vertical">
<TextView
android:layout_width="match_parent"
android:layout_height="30dp"
android:text="新增行程"
android:textColor="#ffffff"
android:textSize="20sp"
android:background="#53CC66"
android:gravity="center_vertical"/>
<LinearLayout
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:orientation="horizontal"
android:layout_marginTop="10dp">
<TextView
android:layout_width="0dp"
android:layout_height="wrap_content"
android:layout_weight="2"
android:textSize="18sp"
android:text="行程名称"/>
<EditText
android:id="@+id/routename"
android:layout_width="0dp"
android:layout_height="40dp"
android:layout_weight="8"
android:background="@drawable/bg_circle"
android:hint="点击输入行程名称"
android:paddingLeft="5dp"/>
</LinearLayout>
<LinearLayout
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:orientation="horizontal"
android:layout_marginTop="10dp">
<TextView
android:layout_width="0dp"
android:layout_height="wrap_content"
android:layout_weight="2"
android:textSize="18sp"
android:text="行程内容"/>
<EditText
android:id="@+id/routing"
android:layout_width="0dp"
android:layout_height="40dp"
android:layout_weight="8"
android:background="@drawable/bg_circle"
android:hint="点击输入行程内容"
android:paddingLeft="5dp"/>
</LinearLayout>
<LinearLayout
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:orientation="horizontal"
android:layout_marginTop="10dp">
<TextView
android:layout_width="0dp"
android:layout_height="wrap_content"
android:layout_weight="2"
android:textSize="18sp"
android:text="行程时间"/>
<EditText
android:id="@+id/routetime"
android:layout_width="0dp"
android:layout_height="40dp"
android:layout_weight="8"
android:background="@drawable/bg_circle"
android:hint="点击输入行程时间"
android:paddingLeft="5dp"/>
</LinearLayout>
<LinearLayout
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:layout_marginTop="10dp"
android:orientation="horizontal">
<Button
android:id="@+id/btn_add"
android:layout_width="match_parent"
android:layout_height="40dp"
android:layout_margin="5dp"
android:layout_weight="1"
android:background="@drawable/bg_buttonchange"
android:text="添加"
android:textColor="#000000"
android:textSize="20sp" />
<Button
android:id="@+id/btn_close"
android:layout_width="match_parent"
android:layout_height="40dp"
android:layout_margin="5dp"
android:layout_weight="1"
android:background="@drawable/bg_buttonchange"
android:text="关闭"
android:textColor="#000000"
android:textSize="20sp" />
</LinearLayout>
</LinearLayout>
修改行程对话框界面
- change.xml
<?xml version="1.0" encoding="utf-8"?>
<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
android:orientation="vertical" android:layout_width="match_parent"
android:layout_height="match_parent">
<TextView
android:layout_width="match_parent"
android:layout_height="30dp"
android:text="修改行程"
android:textColor="#ffffff"
android:textSize="20sp"
android:background="#9C27B0"
android:gravity="center_vertical"/>
<LinearLayout
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:orientation="horizontal"
android:layout_marginTop="10dp">
<TextView
android:layout_width="0dp"
android:layout_height="wrap_content"
android:layout_weight="2"
android:textSize="18sp"
android:text="行程名称"/>
<EditText
android:id="@+id/routename"
android:layout_width="0dp"
android:layout_height="40dp"
android:layout_weight="8"
android:background="@drawable/bg_circle"
android:hint="点击输入修改后的行程名称"
android:paddingLeft="5dp"/>
</LinearLayout>
<LinearLayout
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:orientation="horizontal"
android:layout_marginTop="10dp">
<TextView
android:layout_width="0dp"
android:layout_height="wrap_content"
android:layout_weight="2"
android:textSize="18sp"
android:text="行程内容"/>
<EditText
android:id="@+id/routing"
android:layout_width="0dp"
android:layout_height="40dp"
android:layout_weight="8"
android:background="@drawable/bg_circle"
android:hint="点击输入修改后的行程内容"
android:paddingLeft="5dp"/>
</LinearLayout>
<LinearLayout
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:orientation="horizontal"
android:layout_marginTop="10dp">
<TextView
android:layout_width="0dp"
android:layout_height="wrap_content"
android:layout_weight="2"
android:textSize="18sp"
android:text="行程时间"/>
<EditText
android:id="@+id/routetime"
android:layout_width="0dp"
android:layout_height="40dp"
android:layout_weight="8"
android:background="@drawable/bg_circle"
android:hint="点击输入修改后的行程时间"
android:paddingLeft="5dp"/>
</LinearLayout>
<LinearLayout
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:layout_marginTop="10dp"
android:orientation="horizontal">
<Button
android:id="@+id/btn_change"
android:layout_width="match_parent"
android:layout_height="40dp"
android:layout_margin="5dp"
android:layout_weight="1"
android:background="@drawable/bg_buttonchange"
android:text="修改"
android:textColor="#000000"
android:textSize="20sp" />
<Button
android:id="@+id/btn_close_3"
android:layout_width="match_parent"
android:layout_height="40dp"
android:layout_margin="5dp"
android:layout_weight="1"
android:background="@drawable/bg_buttonchange"
android:text="关闭"
android:textColor="#000000"
android:textSize="20sp" />
</LinearLayout>
</LinearLayout>
行程展示主界面
- activity_login.xml
<?xml version="1.0" encoding="utf-8"?>
<LinearLayout 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=".LoginActivity"
android:orientation="vertical"
android:padding="10dp">
<Button
android:id="@+id/addline"
android:layout_width="match_parent"
android:layout_height="50dp"
android:text="添加行程"
android:textSize="25sp"
android:textColor="#000000"
android:background="@drawable/bg_btn"/>
<ListView
android:id="@+id/showlist"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:layout_marginTop="10dp"
android:divider="#84CDED"
android:dividerHeight="2dp"
/>
</LinearLayout>
- LoginActivity.java
public class LoginActivity extends AppCompatActivity implements View.OnClickListener, AdapterView.OnItemClickListener {
private DBManager manager;
private ListView showroute;
private Button addline;
private EditText routename,routing,routetime,croutename,crouting,croutetime;
private View view,view3;
private AlertDialog dialog,dialog2,dialog3;
private AlertDialog.Builder builder,builder2,builder3;
private List<String> list;
private String TAG = "LoginActivity";
private String username;
private String[] split;
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_login);
manager = new DBManager(this);
showroute = findViewById(R.id.showlist);
addline = findViewById(R.id.addline);
showroute.setOnItemClickListener(this);
addline.setOnClickListener(this);
//添加行程对话框
builder = new AlertDialog.Builder(this);
final LayoutInflater inflater = LoginActivity.this.getLayoutInflater();
view = inflater.inflate(R.layout.addline,null,false);
builder.setView(view);
builder.setCancelable(false);
dialog = builder.create();
//修改行程对话框
builder3 = new AlertDialog.Builder(this);
final LayoutInflater inflater3 = LoginActivity.this.getLayoutInflater();
view3 = inflater3.inflate(R.layout.change,null,false);
builder3.setView(view3);
builder3.setCancelable(false);
dialog3 = builder3.create();
//添加行程对话框中的控件
view.findViewById(R.id.btn_add).setOnClickListener(this);
view.findViewById(R.id.btn_close).setOnClickListener(this);
routename = view.findViewById(R.id.routename);
routing = view.findViewById(R.id.routing);
routetime = view.findViewById(R.id.routetime);
//修改行程对话框中的控件
view3.findViewById(R.id.btn_change).setOnClickListener(this);
view3.findViewById(R.id.btn_close_3).setOnClickListener(this);
croutename = view3.findViewById(R.id.routename);
crouting = view3.findViewById(R.id.routing);
croutetime = view3.findViewById(R.id.routetime);
//获取上个activity传入的用户名
Intent intent = getIntent();
username = intent.getStringExtra("username");
queryflash();
}
private void queryflash(){ //查询数据库并刷新页面,显示最新的用户行程
Cursor cursor = manager.query(username);
list = new ArrayList<>();
while (cursor.moveToNext()){
list.add(cursor.getString(0)+"、 "+cursor.getString(1)+" "+
cursor.getString(2)+" "+cursor.getString(3));
}
ArrayAdapter<String> adapter = new ArrayAdapter<String>(this,android.R.layout.simple_list_item_1,list); //更新适配器
showroute.setAdapter(adapter); //加载更新后的适配器到ListView中
}
@Override
public void onClick(View v) {
switch (v.getId()){
case R.id.addline:
dialog.show(); //显示对添加行程对话框
break;
case R.id.btn_add:
String name = routename.getText().toString();
String ing = routing.getText().toString();
String time = routetime.getText().toString();
if (name.equals("") || ing.equals("") || time.equals("")){
Toast.makeText(getApplicationContext(),"输入不能为空!",Toast.LENGTH_SHORT).show();
}
else{
manager.insert(username,name,ing,time); //调用insert方法插入数据
Toast.makeText(getApplicationContext(),"行程插入成功!",Toast.LENGTH_SHORT).show();
queryflash(); //插入完后立即更新页面
dialog.dismiss(); //并将添加行程对话框关闭
}
break;
case R.id.btn_close:
dialog.dismiss();
break;
case R.id.btn_change:
String cname = croutename.getText().toString();
String cing = crouting.getText().toString();
String ctime = croutetime.getText().toString();
if (cname.equals("") || cing.equals("") || ctime.equals("")){
Toast.makeText(getApplicationContext(),"输入不能为空!",Toast.LENGTH_SHORT).show();
}
else{
manager.update(username,cname,cing,ctime,split[1],split[2],split[3]); //通过当前ListView子项的数据和输入的数据来更新表中数据
Toast.makeText(getApplicationContext(),"行程修改成功!",Toast.LENGTH_SHORT).show();
queryflash();
dialog3.dismiss();
}
break;
case R.id.btn_close_3:
dialog3.dismiss();
break;
default:
break;
}
}
//点击ListView子项后调用的方法
@Override
public void onItemClick(final AdapterView<?> parent, final View view, final int position, long id) {
final String str = String.valueOf(showroute.getItemAtPosition(position)); //获取到当前点击的ListView子项中的内容。以String方式保存
split = str.split("\\s+"); //正则表达式,以空格分割该字符串,这会分割出当前点击行程的id、名称、内容、时间四项数据
dialog2 = null;
builder2 = new AlertDialog.Builder(this);
builder2.setCancelable(false); //设置该对话框无法通过点击周围区域关闭
dialog2 = builder2.setIcon(R.drawable.route) //设置左上角图标,可以去网上随便找一张png小图标放在res的目录下
.setTitle("系统提示:") //对话框标题
.setMessage("请选择要进行的操作") //对话框中间的信息提示
.setNegativeButton("删除", new DialogInterface.OnClickListener() {
@Override
public void onClick(DialogInterface dialog, int which) { //对话框右边部分的第一个按钮
manager.delete(split[1]); //从被分割出的数组中取出行程名成删除
queryflash(); //刷新界面
Toast.makeText(getApplicationContext(),"删除了行程:"+str,Toast.LENGTH_LONG).show();
}
})
.setPositiveButton("取消", new DialogInterface.OnClickListener() {
@Override
public void onClick(DialogInterface dialog, int which) { //对话框右边部分的第二个按钮
Toast.makeText(getApplicationContext(),"点击取消!",Toast.LENGTH_SHORT).show();
}
})
.setNeutralButton("修改", new DialogInterface.OnClickListener() {
@Override
public void onClick(final DialogInterface dialog, int which) { //对话框左边部分的按钮
dialog2.dismiss(); //取消显示当前对话框
croutename.setText(split[1]); //将被分割数组中的行程名放入行程修改对话框中的行程名,可以基于以往的行程修改
crouting.setText(split[2]); //同上,显示行程内容
croutetime.setText(split[3]); //同上,显示行程时间
dialog3.show(); //弹出行程修改对话框
Toast.makeText(getApplicationContext(),"点击修改!",Toast.LENGTH_SHORT).show();
}
}).create();
dialog2.show(); //显示该对话框
}
@Override
protected void onDestroy() {
super.onDestroy();
manager.closeDB(); //在该页面被销毁时同时关闭数据库连接
}
}
其中的按键的背景样式代码在这里Android内部存储:SharedPreferences(存储xml文件)、File(存储INI文件)、OrmLite(数据库)贴过,都是一样的。
总结一下
显然,注释打的挺多,起名还是没改🙃。以后注意起名规范!以后注意起名规范!以后注意起名规范!妄图记住