标签：SQLite

SQLite一个轻型的数据库


为什么要用 SQLite？
不需要一个单独的服务器进程或操作的系统（无服务器的）。
SQLite 不需要配置，这意味着不需要安装或管理。一个完整的 SQLite 数据库是存储在一个单一的跨平台的磁盘文件。SQLite 是非常小的，是轻量级的，完全配置时小于 400KiB，省略可选功能配置时小于250KiB。SQLite 是自给自足的，这意味着不需要任何外部的依赖。SQLite 事务是完全兼容 ACID 的，允许从多个进程或线程安全访问。SQLite 支持 SQL92（SQL2）标准的大多数查询语言的功能。SQLite 使用 ANSI-C 编写的，并提供了简单和易于使用的 API。SQLite 可在Linux、 Android、 iOS、Windows中运行。

下载地址：https://www.sqlite.org/download.html 阅读全文

Android 数据库 sqlite 操作

测试类

package cn.mf.cts.utils;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.util.Log;

/**
 * CopyRright (c)2018-2028: chanpinxue.cn
 * Project: cts
 * Module Name: SQLiteDBUtil
 * Comments: SQLite测试类
 * JDK version used: JDK1.8
 * Author: jzh
 * Create Date: 2018-11-23
 * Modified By: jzh
 * Modified Date: 2018-11-23
 * Why & What is modified:
 * Version: <1.0>
 */
public class SQLiteDBUtil {

    private static SQLiteDBUtil dbUtil;
    private SQLiteDatabase db;

    /**
     * 单例模式
     * @return
     */
    public static SQLiteDBUtil getInstance() {
        if (dbUtil == null) {
            dbUtil = new SQLiteDBUtil();
            return dbUtil;
        }
        return dbUtil;
    }

    /**
     * 初始化/建表
     * @param context 上下文对象
     */
    public void init(Context context) {
        String path = context.getCacheDir().getPath() + "/cts.db";
        db = SQLiteDatabase.openOrCreateDatabase(path, null);
        String sql = " create table if not exists app_users "
                   + "(user_id integer primary key autoincrement, "
                   + " user_code text(50), user_name text(50) ) ";
        db.execSQL(sql);
    }

    /**
     * 新增
     */
    public long insert(String usercode, String username) {
        ContentValues cv = new ContentValues();
        cv.put("user_code", usercode);
        cv.put("user_name", username);
        long flag = db.insert("app_users", null, cv);
        return flag;
    }

    /**
     * 修改
     */
    public int update(String usercode, String username) {
        ContentValues cv = new ContentValues();
        cv.put("user_name", username);
        int flag = db.update("app_users", cv, "user_code = ?", new String[]{usercode});
        return flag;
    }

    /**
     * 删除
     */
    public int delete(String usercode) {
        int flag = db.delete("app_users", "user_code = ?", new String[]{usercode});
        return flag;
    }

    /**
     * 查询数据
     */
    public String select(String user_code) {
        String username = "";
        Cursor cursor = db.query("app_users", null, "user_code = ?",  new String[]{user_code}, null, null, null);
        while (cursor.moveToNext()) {
            int userid = cursor.getInt(cursor.getColumnIndex("user_id"));
            username = cursor.getString(cursor.getColumnIndex("user_name"));
            Log.e("sqlite", String.valueOf(userid) + " " + username);
        }
        if (cursor != null) {
            cursor.close();
        }
        return username;
    }

}

调用 阅读全文

Java sqlite jdbc 测试

sqlitejdbc-v056.jar。

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class Test {
  
  /**
   * @author jzh
   * @remark sqlite jdbc 测试
   * @param args
   * @throws ClassNotFoundException
   */
  public static void main(String[] args) throws ClassNotFoundException {
    // 获取当前类生成class所在的路径
    String strPath = Test.class.getClassLoader().getResource("").toString();
    // 获取file:/后面的路径
    strPath = strPath.substring(6);
    // sqlite 所在路径
    String strDBFile = strPath + "test.db";
    
    // load the sqlite-JDBC driver using the current class loader
    Class.forName("org.sqlite.JDBC");

    Connection conn = null;
    try {
      // create a database connection
      conn = DriverManager.getConnection("jdbc:sqlite:" + strDBFile);
      Statement stmt = conn.createStatement();
      stmt.setQueryTimeout(30); // set timeout to 30 sec.

      stmt.executeUpdate("drop table if exists leader");
      stmt.executeUpdate("create table leader (id integer, name string)");
      stmt.executeUpdate("insert into leader values(1, 'howsky')");
      stmt.executeUpdate("insert into leader values(2, 'howsky.net')");
      ResultSet rs = stmt.executeQuery("select * from leader");
      while (rs.next()) {
        // read the result set
        System.out.println("name = " + rs.getString("name"));
        System.out.println("id = " + rs.getInt("id"));
      }
    } catch (SQLException e) {
      // if the error message is "out of memory",
      // it probably means no database file is found
      System.err.println(e.getMessage());
    } finally {
      try {
        if (conn != null)
          conn.close();
      } catch (SQLException e) {
        // connection close failed.
        System.err.println(e);
      }
    }
  }
}
阅读全文