关于数据库SQLite的使用日记

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) {

    }

}
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.

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;
    }
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.

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;
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.

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+"%");
    }
}
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
68.
69.

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();

    }
}
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.