看完界面后再来看数据字典
模拟考试系统数据字典(Sqlite3)
用户表user
| 字段 | user_id | user_name | user_age | user_password | user_male | user_create |
| 字段描述 | 用户ID | 用户名 | 年 | 登录密码 | 性别 | 创建日期 |
| 字段类型 | integer | text | text | text | text | text |
| 字段约束 | primary key autoincrement |
题库表tb_itembank
| 字段 | _id | itembank_no | itembank_name | itembank_stems | itembank_choices | itembank_answer | itembank_create | itembank_modified |
| 字段描述 | 题库ID | 题库号 | 题库名 | 题干 | 选项 | 答案 | 创建日期 | 修改日期 |
| 字段类型 | integer | INTEGER | TEXT | TEXT | TEXT | TEXT | DATE | DATE |
| 字段约束 |
选卷表tb_select_paper
| 字段 | tb_sp_user_id | tb_sp_itemB_id | tb_sp_itembank_no | stuAuswer | isRight | testtime | credit |
| 字段描述 | 用户ID | 题库名 | 题库号 | 学生答案 | 是否正确 | 考试时间 | 得分 |
| 字段类型 | INTEGER | INTEGER | INTEGER | TEXT | TEXT | datetime | INTEGER |
| 字段约束 | FOREIGN KEY("tb_sp_user_id") REFERENCES "user"("user_id") ) | FOREIGN KEY("tb_sp_itemB_id") REFERENCES "tb_itembank"("_id") |
接着再来看代码建数据库代码：
package com.example.mock_exam;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.text.DateFormat;
import java.util.Date;
import android.content.Context;
import android.content.ContentValues;
import android.content.res.Resources;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteDatabase.CursorFactory;
import android.database.sqlite.SQLiteException;
import android.database.sqlite.SQLiteOpenHelper;
import android.os.Environment;
import android.util.Log;
public class DBAdapter {
private static String DB_PATH = "";
private static final String DB_NAME="mock_exam_db.db";//数据库名字
private SQLiteDatabase db;
final static String TAG="LOGCAT";
private final Context context;
private DBOpenHelper dbOpenHelper;
private int pageNo=1;//页码
private int pageCount=0;//页面总数
private boolean isfirst=false;
private int limit=10;//显示10行数据
//用户表
private static final String DB_TABLE_User="user";
private static final String KEY_USER_ID="user_id";
private static final String KEY_USER_NAME="user_name";
private static final String KEY_USER_AGE="user_age";
private static final String KEY_USER_PASSWORD="user_password";
private static final String KEY_USER_MALE="user_male";
private static final String KEY_USER_CREATE="user_create";
private static final String KEY_USER_MODIFIED="user_modified";
//题库表各个字段
private static final String DB_TABLE_ItemBank="tb_itembank";
private static final String KEY_ItemBank_ID="_id";
private static final String KEY_ItemBank_No="itembank_no";
private static final String KEY_ItemBank_Name="itembank_name";
private static final String KEY_ItemBank_Stems="itembank_stems";
private static final String KEY_ItemBank_Choices="itembank_choices";
private static final String KEY_ItemBank_Answer="itembank_answer";
private static final String KEY_ItemBank_CREATE="itembank_create";
private static final String KEY_ItemBank_MODIFIED="itembank_modified";
//学生选试卷表
private static final String DB_TABLE_SelectPaper="tb_select_paper";
private static final String KEY_SP_User_ID="tb_sp_user_id";
private static final String KEY_SP_ItemBank_ID="tb_sp_itemB_id";
private static final String KEY_SP_ItemBank_No="tb_sp_itembank_no";
private static final String KEY_StuAuswer="stuAuswer";
private static final String KEY_IsRight="isRight";
private static final String KEY_TestTime="testtime";
private static final String KEY_Credit="credit";
private static class DBOpenHelper extends SQLiteOpenHelper{
//
public DBOpenHelper(Context context,String name, CursorFactory factory,int version){
super(context,name,factory,version);
//copyDb(mock_exam_db.db);
if(android.os.Build.VERSION.SDK_INT >= 4.2){
Log.d(TAG,"dbapter");
Log.i(TAG,"ibapter");
Log.w(TAG,"ibapter");
Log.e(TAG,"ibapter");
Log.v(TAG,"ibapter");
//Log.v(TAG,DB_NAME);
DB_PATH = context.getApplicationInfo().dataDir + "/databases/";
Log.d(TAG,DB_PATH);
} else {
Log.d(TAG,"elsedbapter");
Log.i(TAG,"elseibapter");
//Log.w(TAG,"wbapter");
DB_PATH = "/data/data/" + context.getPackageName() + "/databases/";
Log.d(TAG,DB_PATH);
}
}
//建立用户表
private static final String DB_CREATE_table_user="create table IF NOT EXISTS "+DB_TABLE_User+
"("+KEY_USER_ID+" integer primary key autoincrement," +
KEY_USER_NAME+" text," +
KEY_USER_AGE+" text,"+
KEY_USER_PASSWORD+" text,"+
KEY_USER_MALE+" text,"+
KEY_USER_CREATE+" text,"+
KEY_USER_MODIFIED+" text);";
//建立题库表的语句
private static final String DB_CREATE_TB_ItemBank="create table IF NOT EXISTS "+DB_TABLE_ItemBank+
"("+KEY_ItemBank_ID+" integer primary key autoincrement," +
KEY_ItemBank_No+" integer,"+
KEY_ItemBank_Name+" text,"+
KEY_ItemBank_Stems+" text,"+
KEY_ItemBank_Choices+" text,"+
KEY_ItemBank_Answer+" text,"+
KEY_ItemBank_CREATE+" date,"+
KEY_ItemBank_MODIFIED+" date);";
//建立学生选择试卷的表语句
private static final String DB_CREATE_TABLE_SelectPaper="create table IF NOT EXISTS "+DB_TABLE_SelectPaper+
"("+KEY_SP_User_ID+" integer," +
KEY_SP_ItemBank_ID+" integer,"+
KEY_SP_ItemBank_No+" integer,"+
KEY_StuAuswer+" text,"+
KEY_IsRight+" text,"+
KEY_TestTime+" datetime,"+
KEY_Credit+" integer,"+
"FOREIGN KEY"+"("+KEY_SP_User_ID+") "+"REFERENCES "+DB_TABLE_User+"("+KEY_USER_ID+"),"+
"FOREIGN KEY"+"("+KEY_SP_ItemBank_ID+") "+"REFERENCES "+DB_TABLE_ItemBank+"("+KEY_ItemBank_ID+")"+
");";
public void onCreate(SQLiteDatabase _db){
Log.d(TAG,"DBAdapter建库成功");
//创建数据库目录
_db.execSQL(DB_CREATE_table_user);
_db.execSQL(DB_CREATE_TB_ItemBank);
_db.execSQL(DB_CREATE_TABLE_SelectPaper);//执行建立学生选试卷sql语句
}
public void onUpgrade(SQLiteDatabase _db,int _oldVersion,int _newVersion){
_db.execSQL("DROP TABLE IF EXISTS "+DB_TABLE_User);
_db.execSQL("DROP TABLE IF EXISTS "+DB_TABLE_ItemBank);
_db.execSQL("DROP TABLE IF EXISTS "+DB_CREATE_TABLE_SelectPaper);//执行建立学生选试卷sql语句
Log.d(TAG,"DBAdapter更新数据库成功");
onCreate(_db);}}
public DBAdapter(Context _context){
context=_context;open();
isfirst=true;}
public void open()throws SQLiteException{
Log.d(TAG,"open");
dbOpenHelper=new DBOpenHelper(context,DB_NAME,null,DB_VERSION);
Log.d(TAG,"dbOpenHelper");
Log.i(TAG,"dbOpenHelper");
try{db=dbOpenHelper.getWritableDatabase();}
catch(SQLiteException ex){
db=dbOpenHelper.getReadableDatabase();
}
}
public void close(){
if (db!=null){db.close();
db=null;}}
public int getStemsCount() {
Cursor results =db.query(DB_TABLE, new String[] { KEY_ID, KEY_STEMS,
KEY_CHOICES, KEY_ANSWER}, null, null, null, null, null);
int intResults=results.getCount();
return intResults; }
看完了建库的代码再来看看user表、tb_itembank表及选卷表的增、删、改、查。
//插入用户
public long insert(User user){
ContentValues newValues =new ContentValues();
newValues.put(KEY_USER_NAME,user.USER_NAME);
newValues.put(KEY_USER_PASSWORD,user.USER_PASSWORD);
newValues.put(KEY_USER_MALE,user.USER_MALE);
newValues.put(KEY_USER_CREATE,user.USER_CREATE);
Log.d(TAG,user.toString());
return db.insert(DB_TABLE_User, null, newValues);}
//删除全部用户
public long deleteAllUserData(){
return db.delete(DB_TABLE_User, null, null);}
//根据id删除一条数据
public long deleteoneUserData(long id){
return db.delete(DB_TABLE_User, KEY_USER_ID+"="+id, null);
}
//根据id更新一条用户数据
public long updateoneData(int id, User user) {
ContentValues updatevalues=new ContentValues();
updatevalues.put(KEY_USER_NAME,user.USER_NAME);
updatevalues.put(KEY_USER_PASSWORD,user.USER_PASSWORD);
updatevalues.put(KEY_USER_MALE,user.USER_MALE);
DateFormat ddf = DateFormat.getDateInstance();
Date date=new Date();
user.USER_MODIFIED=ddf.format(date);
updatevalues.put(KEY_USER_MODIFIED,user.USER_MODIFIED);
//数据库的表名，第二个参数是更新数据，第三个参数更新条件
return db.update(DB_TABLE_User, updatevalues, KEY_USER_ID+"="+id,null );
}
//用来获取全部数据
public User[] queryAllUserData(){
Cursor results = db.query(DB_TABLE_User, new String[] { KEY_USER_ID,
KEY_USER_NAME,
KEY_USER_PASSWORD,
KEY_USER_MALE}, null, null, null, null, null);
return ConvertToUser(results); }
//根据id获取新的一条数据
public User[] queryoneUserData(long id) {
Cursor results = db.query(DB_TABLE_User, new String[] { KEY_USER_ID,
KEY_USER_NAME,
KEY_USER_PASSWORD,
KEY_USER_MALE}, KEY_USER_ID + "=" + id, null, null, null, null);
return ConvertToUser(results);}
//查询用户功能
private User[] ConvertToUser (Cursor cursou){
int resultCounts= cursou.getCount();
if(resultCounts==0||!cursou.moveToFirst()){
return null;
}
User [] User =new User[resultCounts];
for(int i=0;i
//插入题库
public long insert(ItemBank ItemBank){
ContentValues newValues =new ContentValues();
newValues.put(KEY_ItemBank_No,ItemBank.ITEMBANK_NO);
newValues.put(KEY_ItemBank_Name,ItemBank.ITEMBANK_NAME);
newValues.put(KEY_ItemBank_Stems,ItemBank.ITEMBANK_STEMS);
newValues.put(KEY_ItemBank_Choices,ItemBank.ITEMBANK_CHOICES);
newValues.put(KEY_ItemBank_Answer,ItemBank.ITEMBANK_ANSWER);
newValues.put(KEY_ItemBank_CREATE,ItemBank.ITEMBANK_CREATE);
//Log.d(TAG,ItemBank.toString());
return db.insert(DB_TABLE_ItemBank, null, newValues);}
//根据id删除一条题库数据
//创建日期2019-10-22
public long deleteoneItemBankData(long id){
return db.delete(DB_TABLE_ItemBank, KEY_ItemBank_ID+"="+id, null);
}
//根据id更新一条题库
//创建日期2019-10-22
public long updateoneData(long id,ItemBank itembank){
ContentValues updatevalues =new ContentValues();
updatevalues.put(KEY_ItemBank_Name,itembank.ITEMBANK_NAME);
updatevalues.put(KEY_ItemBank_Stems,itembank.ITEMBANK_STEMS);
updatevalues.put(KEY_ItemBank_Choices,itembank.ITEMBANK_CHOICES);
DateFormat ddf = DateFormat.getDateInstance();
Date date=new Date();
itembank.ITEMBANK_MODIFIED=ddf.format(date);
updatevalues.put(KEY_ItemBank_MODIFIED,itembank.ITEMBANK_MODIFIED);
return db.update(DB_TABLE_ItemBank,updatevalues,KEY_ItemBank_ID+"="+id, null);
}
//根据id获取新的一条数据
public ItemBank[] queryoneItemBankData(long id)
{ Cursor results = db.query(DB_TABLE_ItemBank, new String[] { KEY_ItemBank_ID,
KEY_ItemBank_No,
KEY_ItemBank_Name,
KEY_ItemBank_Stems,
KEY_ItemBank_Choices,
KEY_ItemBank_Answer,
KEY_ItemBank_CREATE,
KEY_ItemBank_MODIFIED},
KEY_ItemBank_ID + "=" + id, null, null, null, null);
return ConvertToItembank(results);}
//用来获取全部数据
public ItemBank[] queryAllItemBankData(int pageNo){
//int limit=20;
Cursor results=null;
results = db.query(DB_TABLE_ItemBank, new String[] { KEY_ItemBank_ID,
KEY_ItemBank_No,
KEY_ItemBank_Name,
KEY_ItemBank_Stems,
KEY_ItemBank_Choices,
KEY_ItemBank_Answer,
KEY_ItemBank_CREATE,
//KEY_ItemBank_MODIFIED}, null, null, null, null, null,"20,20");//使用分页查询
KEY_ItemBank_MODIFIED}, null, null, null, null, null,limit*(pageNo-1)+","+limit);//使用分页查询
return ConvertToItembank(results);
}
//查询题库功能
private ItemBank[] ConvertToItembank (Cursor cursou){
int resultCounts= cursou.getCount();
String strResultCounts=String.valueOf(resultCounts);
Log.d(TAG,"cursou.getCount()="+strResultCounts);
if(resultCounts==0||!cursou.moveToFirst()){
return null;
}
ItemBank[] Itembank =new ItemBank[resultCounts];
for(int i=0;i
public long insert(SelectPaper select_paper){
ContentValues newValues =new ContentValues();
newValues.put(KEY_SP_User_ID,select_paper.Tb_sp_USER_ID);
newValues.put(KEY_SP_ItemBank_ID,select_paper.Tb_sp_ItemBank_ID);
newValues.put(KEY_SP_ItemBank_No,select_paper.Tb_sp_ItemBank_NO);
newValues.put(KEY_StuAuswer,select_paper.StuAuswer);
newValues.put(KEY_IsRight,select_paper.isRight);
newValues.put(KEY_TestTime,select_paper.TestTime);
newValues.put(KEY_Credit,select_paper.Credit);
Log.d(TAG,select_paper.toString());
return db.insert(DB_TABLE_SelectPaper, null, newValues);}
//根据id删除一条试题数据
public long deleteoneSelectPaper(long id){
return db.delete(DB_TABLE_SelectPaper, KEY_SP_User_ID+"="+id, null);
}
//根据id更新一条用户数据
public long updateoneData(int id, SelectPaper select_paper) {
ContentValues updatevalues=new ContentValues();
updatevalues.put(KEY_SP_User_ID,select_paper.Tb_sp_USER_ID);
updatevalues.put(KEY_SP_ItemBank_ID,select_paper.Tb_sp_ItemBank_ID);
updatevalues.put(KEY_SP_ItemBank_No,select_paper.Tb_sp_ItemBank_NO);
updatevalues.put(KEY_StuAuswer,select_paper.StuAuswer);
updatevalues.put(KEY_IsRight,select_paper.isRight);
updatevalues.put(KEY_TestTime,select_paper.TestTime);
//数据库的表名，第二个参数是更新数据，第三个参数更新条件
return db.update(DB_TABLE_SelectPaper, updatevalues, KEY_SP_User_ID+"="+id,null );
}
public SelectPaper[] queryoneSelectPaperData(long id)
{ Cursor results = db.query(DB_TABLE_SelectPaper, new String[] { KEY_SP_User_ID,
KEY_SP_ItemBank_ID,
KEY_SP_ItemBank_No,
KEY_StuAuswer,
KEY_IsRight,
KEY_TestTime,
KEY_Credit
}, KEY_SP_User_ID + "=" + id, null, null, null, null);
return ConvertToSelectPaper(results);
}
public SelectPaper[] queryAllSelectPaperData()
{ Cursor results = db.query(DB_TABLE_SelectPaper, new String[] { KEY_SP_User_ID,
KEY_SP_ItemBank_ID,
KEY_SP_ItemBank_No,
KEY_StuAuswer,
KEY_IsRight,
KEY_TestTime,
KEY_Credit
}, null, null, null, null, null);
return ConvertToSelectPaper(results);
}
private SelectPaper[] ConvertToSelectPaper(Cursor cursou) {
// TODO Auto-generated method stub
int resultCounts= cursou.getCount();
if(resultCounts==0||!cursou.moveToFirst()){
return null;
}
SelectPaper [] SelectPaper =new SelectPaper[resultCounts];
for(int i=0;i
管理用户的代码即用户的增、删、改、查代码
先看直观感受
然后看布局代码manage_user.xml
ManageUser.java
package com.example.mock_exam;
import android.os.Bundle;
import android.support.v7.app.ActionBarActivity;
import android.util.Log;
import android.view.View;
import android.view.View.OnClickListener;
import android.widget.Button;
import android.widget.EditText;
import android.widget.RadioButton;
import android.widget.RadioGroup;
import android.widget.TextView;
public class ManageUser extends ActionBarActivity implements OnClickListener{
final static String TAG="LOGCAT";
private Button btAddUser=null;
private Button btDisplayAll=null;
private Button btDeleteDisplay=null;
private Button btIdQuery=null;
private Button btIdDelete=null;
private Button btIdUpdate=null;
private EditText editTextUserName=null;
private EditText editTextPassword=null;
private EditText editTextId=null;
//private EditText editTextInputIDQuer=null;
private RadioGroup radioGroup=null;
private RadioButton radioButtonMan=null;
private RadioButton radioButtonWoman=null;
private TextView textViewDatabs=null;
//final DBAdapter dbAdapter=new DBAdapter(this);//定义打开数据库的变量
//private TextView textViewPassword=null;
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
Log.d(TAG,"dSingle");
Log.i(TAG,"iSingle");
setContentView(R.layout.manage_user); //设置显示单选
TextView tV_UserN=(TextView)super.findViewById(R.id.textViewUserName);
TextView tV_Password=(TextView)super.findViewById(R.id.textViewPassword);
textViewDatabs=(TextView)super.findViewById(R.id.textView_databs);
btAddUser=(Button)super.findViewById(R.id.button_add_user);
btDisplayAll=(Button)super.findViewById(R.id.button_all_display);
btDeleteDisplay=(Button)super.findViewById(R.id.button_delect_display);
btIdQuery=(Button)super.findViewById(R.id.buttonId_query);
btIdDelete=(Button)super.findViewById(R.id.button_id_delect);
btIdUpdate=(Button)super.findViewById(R.id.button_id_update);
radioButtonMan=(RadioButton)super.findViewById(R.id.radioButtouMan);
radioButtonWoman=(RadioButton)super.findViewById(R.id.radioButtonWoman);
radioGroup=(RadioGroup)super.findViewById(R.id.radioGroup);
radioGroup.setVisibility(View.VISIBLE);
btAddUser.setonClickListener(this);
btDisplayAll.setonClickListener(this);
btDeleteDisplay.setonClickListener(this);
btIdQuery.setonClickListener(this);
btIdDelete.setonClickListener(this);
btIdUpdate.setonClickListener(this);
editTextId=(EditText)super.findViewById(R.id.editText_id);
editTextUserName=(EditText)super.findViewById(R.id.editText_user_name);
//editTextInputIDQuer=(EditText)super.findViewById(R.id.editText_id);
editTextPassword=(EditText)super.findViewById(R.id.editText_password);}
@Override
public void onClick(View v) {
// TODO Auto-generated method stub
final DBAdapter dbAdapter=new DBAdapter(this);//定义打开数据库的变量
//String strInputIDquer=editTextUserName.getText().toString();
if(v==btAddUser){
User user;
String strUsern=editTextUserName.getText().toString();
String strPassword=editTextPassword.getText().toString();
String strMale=null;
if(radioButtonMan.isChecked()||radioButtonWoman.isChecked()){
if(radioButtonMan.isChecked()){
strMale="男";
}else if(radioButtonWoman.isChecked()){
strMale="女";}
}
if(strUsern.length()==0||strPassword.length()==0){
textViewDatabs.setText("用户名或密码不能为空");
}else{
Log.i(TAG,strUsern+"--"+strPassword+"--"+strMale);
user=new User(strUsern,strPassword,strMale);
dbAdapter.insert(user);
textViewDatabs.setText("ID为"+user.ID+"数据添加成功！"); }}
else if(v==btDisplayAll){
textViewDatabs.setText("null");
User[] all= dbAdapter.queryAllUserData();
if(null != all)
{
StringBuilder sb=new StringBuilder();
for(User user:all)
{
sb.append("ID: "+user.ID);
sb.append(" 用户名: "+user.USER_NAME);
sb.append(" 密码: "+user.USER_PASSWORD);
sb.append(" 性别: "+user.USER_MALE);
sb.append(" 创建时间: "+user.USER_CREATE);
sb.append(" 修改时间: "+user.USER_MODIFIED+"n");
}
textViewDatabs.setText(sb.toString());
}
}else if(v==btDeleteDisplay){
textViewDatabs.setText("null");
}else if(v==btIdQuery){
Log.d(TAG,"ID查询"+ editTextUserName);
String strInputUserID=editTextId.getText().toString();
if(strInputUserID.length()>0){
//int id =Integer.parseInt(strInputIDquer);
int intInputidQuery =Integer.parseInt(strInputUserID);
User[] all= dbAdapter.queryoneUserData(intInputidQuery);
StringBuilder sb=new StringBuilder();
if(null!=all){
for(User user:all)
{ sb.append("ID:"+user.ID);
sb.append(" 用户名:"+user.USER_NAME);
sb.append(" 密码:"+user.USER_PASSWORD);
sb.append(" 性别:"+user.USER_MALE);
sb.append(" 创建时间:"+user.USER_CREATE);
sb.append(" 修改时间:"+user.USER_MODIFIED+"n"); }
textViewDatabs.setText(sb.toString());}
else if(null==all){
textViewDatabs.setText("数据库中无内容");}
}
}else if(v==btIdDelete){
String strID=editTextId.getText().toString();
if(strID.length()==0){
textViewDatabs.setText("请输入ID");}
else if(strID.trim().length()>0){
int id =Integer.parseInt(strID);
dbAdapter.deleteoneUserData(id);
textViewDatabs.setText("ID为"+id+"删除成功！");}
}else if(v==btIdUpdate){
String strMale=null;
//textViewDatabs.setText("null");
String strID=editTextId.getText().toString();
String strUserN=editTextUserName.getText().toString();
String strPassw=editTextPassword.getText().toString();
if(strUserN.length()==0){
textViewDatabs.setText("请输入ID");}
else if(strUserN.trim().length()>0&&strUserN.trim().length()>0&&
radioButtonMan.isChecked()
||radioButtonWoman.isChecked()&&strPassw.trim().length()>0){
int id =Integer.parseInt(strID);
if(radioButtonMan.isChecked()){
strMale="男";
}else if(radioButtonWoman.isChecked()){
strMale="女";
}
User user=new User(strUserN,strPassw,strMale);
dbAdapter.updateoneData(id,user);
textViewDatabs.setText("ID为"+id+"更新成功！");}
}
dbAdapter.close();//数据库关闭
}
}
由于内容比较多，时间有限。下面是项目代码下载地址
Mock_exam.zip-教育文档类资源-CSDN下载
