SQLite 学习笔记

SQLite 学习笔记.md

资源

1. SQLite 教程
2. SQLite 教程
3. SQLite JDBC 库下载

个人笔记

列出所有表(SQLITE_MASTER)

SQLite数据库中一个特殊的名叫 SQLITE_MASTER 上执行一个SELECT查询以获得所有表的索引。每一个 SQLite 数据库都有一个叫 SQLITE_MASTER 的表， 它定义数据库的模式。 SQLITE_MASTER 表看起来如下：

CREATE TABLE sqlite_master (
type TEXT,
name TEXT,
tbl_name TEXT,
rootpage INTEGER,
sql TEXT
);

对于表来说，type 字段永远是 ‘table’，name 字段永远是表的名字。所以，要获得数据库中所有表的列表， 使用下列SELECT语句：

SELECT name FROM sqlite_master
WHERE type='table'
ORDER BY name;

对于索引，type 等于 ‘index’, name 则是索引的名字，tbl_name 是该索引所属的表的名字。 不管是表还是索引，sql 字段是原先用 CREATE TABLE 或 CREATE INDEX 语句创建它们时的命令文本。对于自动创建的索引（用来实现 PRIMARY KEY 或 UNIQUE 约束），sql字段为NULL。 SQLITE_MASTER 表是只读的。不能对它使用 UPDATE、INSERT 或 DELETE。 它会被 CREATE TABLE、CREATE INDEX、DROP TABLE 和 DROP INDEX 命令自动更新。 临时表不会出现在 SQLITE_MASTER 表中。临时表及其索引和触发器存放在另外一个叫 SQLITE_TEMP_MASTER 的表中。SQLITE_TEMP_MASTER 跟 SQLITE_MASTER 差不多， 但它只是对于创建那些临时表的应用可见。如果要获得所有表的列表， 不管是永久的还是临时的，可以使用类似下面的命令：

SELECT name FROM
(SELECT * FROM sqlite_master UNION ALL
SELECT * FROM sqlite_temp_master)
WHERE type='table'
ORDER BY name;

连接数据库

Java实现

/**
 * 首先连接数据库
 *
 * @param Url
 */
public static void connect(String Url) {
    //(jdbc:sqlite:test.db)
    try {
        Class.forName("org.sqlite.JDBC");
        Connection connection = DriverManager.getConnection(Url);
        Statement statement = connection.createStatement();
        connection.setAutoCommit(true);
        System.out.println("Database Connected!!!");
    } catch (SQLException e) {
        e.printStackTrace();
    } catch (ClassNotFoundException e) {
        e.printStackTrace();
    }
}

列出数据库中所有表(SQLITE_MASTER)

SQL语法

SELECT name FROM SQLITE_MASTER WHERE type='table' ORDER BY name;

Java实现

/**
 * 列出数据库中所有表
 *
 * @return
 */
public static ResultSet listTable() {
    //SELECT name FROM SQLITE_MASTER WHERE type='table' ORDER BY name;
    try {
        String listSQL = "SELECT name FROM SQLITE_MASTER WHERE type='table' ORDER BY name;";
        ResultSet rs = statement.executeQuery(listSQL);
        System.out.print("All lists: ");
        while (rs.next()) {
            String table = rs.getString("name");
            System.out.print(table + ", ");
        }
        System.out.println();
        return rs;
    } catch (SQLException e) {
        e.printStackTrace();
    }
    return null;
}

判断数据库表是否存在(SQLITE_MASTER)

SQL语法

SELECT count(*) FROM SQLITE_MASTER WHERE type='table' AND name='要查询的表名';

Java实现

/**
 * 判断表是否存在
 *
 * @param tableName
 * @return
 */
public static boolean isTableExist(String tableName) {
    //SELECT count(*) FROM SQLITE_MASTER WHERE type='table' AND name='tableName';
    try {
        ResultSet rs = statement.executeQuery("SELECT name FROM SQLITE_MASTER WHERE type='table' " +
                "AND name='" + tableName + "';");
        while (rs.next()) {
            if (rs.getString("name").equals(tableName)) {
                return true;
            }
        }
        return false;
    } catch (SQLException e) {
        System.err.println(tableName + " Not exit!!!");
        return false;
    }
}

表中添加一列(ALTER)

SQL语法

ALTER TABLE prepayment_parking ADD COLUMN attachedScript blob DEFAULT null;

Java实现

/**
 * 表中添加一列
 *
 * @param tableName
 * @param cName
 * @param cType
 * @param cValue
 */
public static boolean addColumn(String tableName, String cName, String cType, String cValue) {
    //ALTER TABLE prepayment_parking ADD COLUMN attachedScript BLOB DEFAULT null;
    String alterSQL = "ALTER TABLE " + tableName + " ADD COLUMN " + cName + " " +
            cType + " DEFAULT " + cValue + ";";
    try {
        statement.execute(alterSQL);
        return true;
    } catch (SQLException e) {
        System.err.println("Add column failed!!!");
        return false;
    }
}

删除操作(DELETE)

SQL语法

DELETE FROM entry_record WHERE exited=1;

Java实现

/**
 * 删除操作
 *
 * @param tableName
 * @param where
 */
public static boolean delete(String tableName, String where) {
    //DELETE FROM entry_record WHERE exited=1;
    String deleteSQL = "DELETE FROM " + tableName + " WHERE " + where + ";";
    try {
        statement.execute(deleteSQL);
        return true;
    } catch (SQLException e) {
        System.err.println("Delete Failed!!!");
        return false;
    }
}

关闭数据库连接

Java实现

/**
 * 关闭数据库连接
 */
public static void close() {
    try {
        if (statement != null) {
            statement.close();
        }
        if (connection != null) {
            connection.close();
        }
        System.out.println("Close database connected!!!");
    } catch (SQLException e) {
        System.err.print("Close database failed!!!");
    }
}