关于android 数据库SQLite的使用日记
- 作者: 左手面包右手段子
- 来源: 51数据库
- 2021-11-02
自己也是第一次使用，中途遇到很多难题，总算努力还是会有收获，自己代码有很多不足的地方希望大神指出来一起学习，加油奥利给，话不多说贴代码
第一步，onCreate建立数据库
public class DBHelperop extends SQLiteOpenHelper {
// 数据库文件名
public static final String DB_NAME = "two.db";
// 数据库版本号
public static final int DB_VERSION = 1;
public DBHelperop(Context context) {
super(context, DB_NAME, null, DB_VERSION);
}
@Override
public void onCreate(SQLiteDatabase db) {
db.execSQL("CREATE TABLE practic(id INTEGER PRIMARY KEY AUTOINCREMENT," +
" num VARCHAR,newratio VARCHAR,numratio VARCHAR,total VARCHAR,str_time VARCHAR)");
}
@Override
public void onUpgrade(SQLiteDatabase db, int i, int i1) {
}
}
第二步，我建了一个实体类接收数据，代码没贴完
public class DbInfor {
private int id;
private String num;
private double newratio;
private double numratio;
private double total;
private String str_time;
public DbInfor(){}
public DbInfor(String num, double newratio, double numratio, double total, String str_time) {
this.num = num;
this.newratio = newratio;
this.numratio = numratio;
this.total = total;
this.str_time = str_time;
}
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getNum() {
return num;
}
第三步，建了一个管理数据库的类，我只做了两个查询和一个插入
public class DBManager {
private DBHelperop helper;
private SQLiteDatabase db;
public DBManager(Context context){
helper = new DBHelperop(context);
db = helper.getWritableDatabase();
}
public void add(List<DbInfor> persons){
db.beginTransaction();
try{
for (DbInfor p:persons){
db.execSQL("INSERT INTO practic (num,newratio,numratio,total,str_time)VALUES(?,?,?,?,?)",
//select * from role order by role_id desc limit 0,1;
new Object[]{p.getNum(),p.getNewratio(),p.getNumratio(),p.getTotal(),p.getStr_time()});
}
db.setTransactionSuccessful();
}catch(Exception e){
e.printStackTrace();
}finally {
db.endTransaction();
}
}
public List<DbInfor> findAllDbInfor(int a){
List<DbInfor> dbinfoe = new ArrayList<>();
//"select * from practic order by id desc limit "+a+",2;"
Cursor c = db.rawQuery("select * from practic order by id desc limit "+a+",6", null);
while(c.moveToNext()){
DbInfor p = new DbInfor();
p.setId(c.getInt(c.getColumnIndex("id")));
p.setNum(c.getString(c.getColumnIndex("num")));
p.setNewratio(c.getDouble(c.getColumnIndex("newratio")));
p.setNumratio(c.getDouble(c.getColumnIndex("numratio")));
p.setTotal(c.getDouble(c.getColumnIndex("total")));
p.setStr_time(c.getString(c.getColumnIndex("str_time")));
dbinfoe.add(p);
}
c.close();
return dbinfoe;
}
public DbInfor findDbInfor(){
DbInfor p = null;
Cursor c = db.rawQuery("select * from practic order by id desc limit 0,1", null);
while(c.moveToNext()){
p = new DbInfor();
p.setId(c.getInt(c.getColumnIndex("id")));
p.setNum(c.getString(c.getColumnIndex("num")));
p.setNewratio(c.getDouble(c.getColumnIndex("newratio")));
p.setNumratio(c.getDouble(c.getColumnIndex("numratio")));
p.setTotal(c.getDouble(c.getColumnIndex("total")));
p.setStr_time(c.getString(c.getColumnIndex("str_time")));
}
c.close();
return p;
第三步，一个输入数据的类，带一个查询，
public class MainActivity extends Activity{
private DBManager dm;
private EditText shuru;
private TextView shuchu;
private int a = 100;
private double newratio ;
private double numratio ;
private double total;
private Button btConform;
private TextView cahkan;
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_main);
shuru = findViewById(R.id.et_etnum);
shuchu = findViewById(R.id.la_view2);
cahkan= findViewById(R.id.cahkan);
btConform = findViewById(R.id.bt_conform);
dm = new DBManager(this);
btConform.setOnClickListener(new View.OnClickListener() {
@Override
public void onClick(View view) {
insertData();
}
});
cahkan.setOnClickListener(new View.OnClickListener() {
@Override
public void onClick(View view) {
startActivity(new Intent(MainActivity.this,DetialActivity.class));
}
});
}
public void insertData(){
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm E");
Date date = new Date(System.currentTimeMillis());
String time = sdf.format(date);//获取系统时间
String shurunum = shuru.getText().toString();
DbInfor dbInform= dm.findDbInfor();//查询最后一个数据
newratio = Double.valueOf(shurunum)/(double)a;
if (dbInform==null){
numratio = Double.valueOf(shurunum)+0/(double)a;
total =Double.valueOf(shurunum)+0;
}else {
numratio = (Double.valueOf(shurunum)+dbInform.getTotal())/(double)a;
total =Double.valueOf(shurunum)+dbInform.getTotal();
}
List<DbInfor> persons = new ArrayList<>();
DbInfor p1 = new DbInfor(shurunum,newratio,numratio,total,time);//插入获取的数据
persons.add(p1);
dm.add(persons);
findDb();
}
public void findDb(){
//输出方法
DbInfor dbInfor= dm.findDbInfor();
if (dbInfor.getNumratio()==0){
shuchu.setText(0+"%");
}
DecimalFormat df=new DecimalFormat(".##");
double d=dbInfor.getNumratio()*100;
String st=df.format(d);
shuchu.setText(st+"%");
}
}
第五步，查询所有数据并显示
public class DetialActivity extends AppCompatActivity implements XListView.IXListViewListener{
private TextView title,detail,time;
private ListRooAdapter listAdapter;
private XListView lvContent;
private DBManager dm;
private int a=0;
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_detial);
title= findViewById(R.id.news_tvtitle);
detail= findViewById(R.id.news_tvdetail);
title= findViewById(R.id.news_tvtime);
dm = new DBManager(this);
listAdapter= new ListRooAdapter(this);
lvContent =findViewById(R.id.lvcent);
lvContent.setXListViewListener(this);
lvContent.setPullRefreshEnable(true);
lvContent.setPullLoadEnable(true);
lvContent.hideFoot();
lvContent.setAdapter(listAdapter);
love();
}
public void love(){
onComplete();
List<DbInfor> datas = dm.findAllDbInfor(a);
if (datas.size()<0){
Toast.makeText(this, "暂时还没有记录呢", Toast.LENGTH_SHORT).show();
}
if(datas.size() < 5){
lvContent.hideFoot();//根据数据显示长度的，显示加载按钮
}else{
lvContent.showFoot();
}
// lvContent.showFoot();
if(a == 0) {
listAdapter.setDatas(datas);
}else{
listAdapter.addDatas(datas);
}
if(datas.size()>0){
a+=6;
}
}
@Override
public void onRefresh() {//下拉刷新
a=0;
love();
}
@Override
public void onLoadMore() {
love();//点击加载
}
public void onComplete(){
lvContent.stopLoadMore();
lvContent.stopRefresh();
}
}
其他代码我就不贴了，每个人用数据库有自己的用法 如需要联系我
最后效果因为原因没上传，有些方法参考了该作者链接为
2021年1月6日周三凌晨12：53
推荐阅读
- SQLite数据库操作：原生操作，GreenDao操作讲解
- 数据库SQL实战题：获取员工其当前的薪水比其manager当前薪水还高的相关信息（教程）
- SQLSERVER查询区分大小写的写法分析
- SQL学习总结之SQL的分类介绍
- 阶梯到高级T-SQL 1级：高级T-SQL介绍交叉连接
- 高级T-SQL级别1的Stairway:使用CROSS JOIN引入高级T-SQL分析
- 什么是SQL隔离级别？四个SQL隔离级别定义介绍
- Sql递归介绍之用with实现递归查询
- SQLSERVER查询时日期格式化的实例讲解
- 数据库SQL实战：从titles表获取按照title进行分组，注意对于重复的emp_no进行忽略（题解）
热点文章
SQLite数据库操作：原生操作，GreenDao操作讲解
18
数据库SQL实战题：获取员工其当前的薪水比其manager当前薪水还高的相关信息（教程）
4
SQLSERVER查询区分大小写的写法分析
36
SQL学习总结之SQL的分类介绍
6
阶梯到高级T-SQL 1级：高级T-SQL介绍交叉连接
4
高级T-SQL级别1的Stairway:使用CROSS JOIN引入高级T-SQL分析
4
什么是SQL隔离级别？四个SQL隔离级别定义介绍
2
Sql递归介绍之用with实现递归查询
6
SQLSERVER查询时日期格式化的实例讲解
4
数据库SQL实战：从titles表获取按照title进行分组，注意对于重复的emp_no进行忽略（题解）
7
