PHP中sqlite3的使用

SQLite是一种轻量级的关系型数据库，可以内嵌在我们的应用程序中一起发布出去，这样我们在部署应用的时候，就不需要额外数据库的支持了。
在php中如果要使用sqlite，只需开启php_sqlite3.dll扩展即可，非常方便。下面是我曾经的一个项目中，自己编写的一个操作SQLite数据库的工具类。
<span style="box-sizing: border-box; padding-right: 0.1px;"><span style="box-sizing: border-box; color: #981a1a;"><?</span><span style="box-sizing: border-box; color: #000000;">php</span></span>

<span style="box-sizing: border-box; padding-right: 0.1px;"><span style="box-sizing: border-box; color: #770088;">class</span> <span style="box-sizing: border-box; color: #0000ff;">SQLiteDB</span> <span style="box-sizing: border-box; color: #770088;">extends</span> <span style="box-sizing: border-box; color: #000000;">SQLite3</span> {</span>
<span style="box-sizing: border-box; padding-right: 0.1px;"><span style="box-sizing: border-box; color: #770088;">function</span> <span style="box-sizing: border-box; color: #0000ff;">__construct</span>(){</span>
<span style="box-sizing: border-box; padding-right: 0.1px;"><span style="box-sizing: border-box; color: #770088;">try</span> {</span>
<span style="box-sizing: border-box; padding-right: 0.1px;"><span style="box-sizing: border-box; color: #0055aa;">$this</span><span style="box-sizing: border-box; color: #981a1a;">-></span><span style="box-sizing: border-box; color: #000000;">open</span>(<span style="box-sizing: border-box; color: #3300aa;">dirname</span>(<span style="box-sizing: border-box; color: #221199;">__FILE__</span>).<span style="box-sizing: border-box; color: #22a2c9;">'/../data/sqlite_ecloud.db'</span>);</span>
<span style="box-sizing: border-box; padding-right: 0.1px;">}<span style="box-sizing: border-box; color: #770088;">catch</span> (<span style="box-sizing: border-box; color: #000000;">Exception</span> <span style="box-sizing: border-box; color: #0055aa;">$e</span>){</span>
<span style="box-sizing: border-box; padding-right: 0.1px;"><span style="box-sizing: border-box; color: #770088;">die</span>(<span style="box-sizing: border-box; color: #0055aa;">$e</span><span style="box-sizing: border-box; color: #981a1a;">-></span><span style="box-sizing: border-box; color: #000000;">getMessage</span>());</span>
<span style="box-sizing: border-box; padding-right: 0.1px;">}</span>
<span style="box-sizing: border-box; padding-right: 0.1px;">}</span>
<span style="box-sizing: border-box; padding-right: 0.1px;">}</span>

<span style="box-sizing: border-box; padding-right: 0.1px;"><span style="box-sizing: border-box; color: #770088;">class</span> <span style="box-sizing: border-box; color: #0000ff;">DBUtils</span> {</span>

<span style="box-sizing: border-box; padding-right: 0.1px;"><span style="box-sizing: border-box; color: #770088;">private</span> <span style="box-sizing: border-box; color: #770088;">static</span> <span style="box-sizing: border-box; color: #0055aa;">$db</span>;</span>

<span style="box-sizing: border-box; padding-right: 0.1px;"><span style="box-sizing: border-box; color: #770088;">private</span> <span style="box-sizing: border-box; color: #770088;">static</span> <span style="box-sizing: border-box; color: #770088;">function</span> <span style="box-sizing: border-box; color: #0000ff;">instance</span>(){</span>
<span style="box-sizing: border-box; padding-right: 0.1px;"><span style="box-sizing: border-box; color: #770088;">if</span> (<span style="box-sizing: border-box; color: #981a1a;">!</span><span style="box-sizing: border-box; color: #770088;">self</span>::<span style="box-sizing: border-box; color: #0055aa;">$db</span>) {</span>
<span style="box-sizing: border-box; padding-right: 0.1px;"><span style="box-sizing: border-box; color: #770088;">self</span>::<span style="box-sizing: border-box; color: #0055aa;">$db</span> <span style="box-sizing: border-box; color: #981a1a;">=</span> <span style="box-sizing: border-box; color: #770088;">new</span> <span style="box-sizing: border-box; color: #000000;">SQLiteDB</span>();</span>
<span style="box-sizing: border-box; padding-right: 0.1px;">}</span>
<span style="box-sizing: border-box; padding-right: 0.1px;">}</span>

<span style="box-sizing: border-box; padding-right: 0.1px;"><span style="box-sizing: border-box; color: #aa5500;">/**</span></span>
<span style="box-sizing: border-box; padding-right: 0.1px;"> <span style="box-sizing: border-box; color: #aa5500;">* 创建表</span></span>
<span style="box-sizing: border-box; padding-right: 0.1px;"> <span style="box-sizing: border-box; color: #aa5500;">* @param string $sql</span></span>
<span style="box-sizing: border-box; padding-right: 0.1px;"> <span style="box-sizing: border-box; color: #aa5500;">*/</span></span>
<span style="box-sizing: border-box; padding-right: 0.1px;"><span style="box-sizing: border-box; color: #770088;">public</span> <span style="box-sizing: border-box; color: #770088;">static</span> <span style="box-sizing: border-box; color: #770088;">function</span> <span style="box-sizing: border-box; color: #0000ff;">create</span>(<span style="box-sizing: border-box; color: #0055aa;">$sql</span>){</span>
<span style="box-sizing: border-box; padding-right: 0.1px;"><span style="box-sizing: border-box; color: #770088;">self</span>::<span style="box-sizing: border-box; color: #000000;">instance</span>();</span>
<span style="box-sizing: border-box; padding-right: 0.1px;"><span style="box-sizing: border-box; color: #0055aa;">$result</span> <span style="box-sizing: border-box; color: #981a1a;">=</span> <span style="box-sizing: border-box; color: #000000;">@self</span>::<span style="box-sizing: border-box; color: #0055aa;">$db</span><span style="box-sizing: border-box; color: #981a1a;">-></span><span style="box-sizing: border-box; color: #000000;">query</span>(<span style="box-sizing: border-box; color: #0055aa;">$sql</span>);</span>
<span style="box-sizing: border-box; padding-right: 0.1px;"><span style="box-sizing: border-box; color: #770088;">if</span> (<span style="box-sizing: border-box; color: #0055aa;">$result</span>) {</span>
<span style="box-sizing: border-box; padding-right: 0.1px;"><span style="box-sizing: border-box; color: #770088;">return</span> <span style="box-sizing: border-box; color: #221199;">true</span>;</span>
<span style="box-sizing: border-box; padding-right: 0.1px;">}</span>
<span style="box-sizing: border-box; padding-right: 0.1px;"><span style="box-sizing: border-box; color: #770088;">return</span> <span style="box-sizing: border-box; color: #221199;">false</span>;</span>
<span style="box-sizing: border-box; padding-right: 0.1px;">}</span>

<span style="box-sizing: border-box; padding-right: 0.1px;"><span style="box-sizing: border-box; color: #aa5500;">/**</span></span>
<span style="box-sizing: border-box; padding-right: 0.1px;"> <span style="box-sizing: border-box; color: #aa5500;">* 执行增删改操作</span></span>
<span style="box-sizing: border-box; padding-right: 0.1px;"> <span style="box-sizing: border-box; color: #aa5500;">* @param string $sql</span></span>
<span style="box-sizing: border-box; padding-right: 0.1px;"> <span style="box-sizing: border-box; color: #aa5500;">*/</span></span>
<span style="box-sizing: border-box; padding-right: 0.1px;"><span style="box-sizing: border-box; color: #770088;">public</span> <span style="box-sizing: border-box; color: #770088;">static</span> <span style="box-sizing: border-box; color: #770088;">function</span> <span style="box-sizing: border-box; color: #0000ff;">execute</span>(<span style="box-sizing: border-box; color: #0055aa;">$sql</span>){</span>
<span style="box-sizing: border-box; padding-right: 0.1px;"><span style="box-sizing: border-box; color: #770088;">self</span>::<span style="box-sizing: border-box; color: #000000;">instance</span>();</span>
<span style="box-sizing: border-box; padding-right: 0.1px;"><span style="box-sizing: border-box; color: #0055aa;">$result</span> <span style="box-sizing: border-box; color: #981a1a;">=</span> <span style="box-sizing: border-box; color: #000000;">@self</span>::<span style="box-sizing: border-box; color: #0055aa;">$db</span><span style="box-sizing: border-box; color: #981a1a;">-></span><span style="box-sizing: border-box; color: #3300aa;">exec</span>(<span style="box-sizing: border-box; color: #0055aa;">$sql</span>);</span>
<span style="box-sizing: border-box; padding-right: 0.1px;"><span style="box-sizing: border-box; color: #770088;">if</span> (<span style="box-sizing: border-box; color: #0055aa;">$result</span>) {</span>
<span style="box-sizing: border-box; padding-right: 0.1px;"><span style="box-sizing: border-box; color: #770088;">return</span> <span style="box-sizing: border-box; color: #221199;">true</span>;</span>
<span style="box-sizing: border-box; padding-right: 0.1px;">}</span>
<span style="box-sizing: border-box; padding-right: 0.1px;"><span style="box-sizing: border-box; color: #770088;">return</span> <span style="box-sizing: border-box; color: #221199;">false</span>;</span>
<span style="box-sizing: border-box; padding-right: 0.1px;">}</span>

<span style="box-sizing: border-box; padding-right: 0.1px;"><span style="box-sizing: border-box; color: #aa5500;">/**</span></span>
<span style="box-sizing: border-box; padding-right: 0.1px;"> <span style="box-sizing: border-box; color: #aa5500;">* 获取记录条数</span></span>
<span style="box-sizing: border-box; padding-right: 0.1px;"> <span style="box-sizing: border-box; color: #aa5500;">* @param string $sql</span></span>
<span style="box-sizing: border-box; padding-right: 0.1px;"> <span style="box-sizing: border-box; color: #aa5500;">* @return int</span></span>
<span style="box-sizing: border-box; padding-right: 0.1px;"> <span style="box-sizing: border-box; color: #aa5500;">*/</span></span>
<span style="box-sizing: border-box; padding-right: 0.1px;"><span style="box-sizing: border-box; color: #770088;">public</span> <span style="box-sizing: border-box; color: #770088;">static</span> <span style="box-sizing: border-box; color: #770088;">function</span> <span style="box-sizing: border-box; color: #3300aa;">count</span>(<span style="box-sizing: border-box; color: #0055aa;">$sql</span>){</span>
<span style="box-sizing: border-box; padding-right: 0.1px;"><span style="box-sizing: border-box; color: #770088;">self</span>::<span style="box-sizing: border-box; color: #000000;">instance</span>();</span>
<span style="box-sizing: border-box; padding-right: 0.1px;"><span style="box-sizing: border-box; color: #0055aa;">$result</span> <span style="box-sizing: border-box; color: #981a1a;">=</span> <span style="box-sizing: border-box; color: #000000;">@self</span>::<span style="box-sizing: border-box; color: #0055aa;">$db</span><span style="box-sizing: border-box; color: #981a1a;">-></span><span style="box-sizing: border-box; color: #000000;">querySingle</span>(<span style="box-sizing: border-box; color: #0055aa;">$sql</span>);</span>
<span style="box-sizing: border-box; padding-right: 0.1px;"><span style="box-sizing: border-box; color: #770088;">return</span> <span style="box-sizing: border-box; color: #0055aa;">$result</span> <span style="box-sizing: border-box; color: #981a1a;">?</span> <span style="box-sizing: border-box; color: #0055aa;">$result</span> : <span style="box-sizing: border-box; color: #116644;">0</span>;</span>
<span style="box-sizing: border-box; padding-right: 0.1px;">}</span>

<span style="box-sizing: border-box; padding-right: 0.1px;"><span style="box-sizing: border-box; color: #aa5500;">/**</span></span>
<span style="box-sizing: border-box; padding-right: 0.1px;"> <span style="box-sizing: border-box; color: #aa5500;">* 查询单个字段</span></span>
<span style="box-sizing: border-box; padding-right: 0.1px;"> <span style="box-sizing: border-box; color: #aa5500;">* @param string $sql</span></span>
<span style="box-sizing: border-box; padding-right: 0.1px;"> <span style="box-sizing: border-box; color: #aa5500;">* @return void|string</span></span>
<span style="box-sizing: border-box; padding-right: 0.1px;"> <span style="box-sizing: border-box; color: #aa5500;">*/</span></span>
<span style="box-sizing: border-box; padding-right: 0.1px;"><span style="box-sizing: border-box; color: #770088;">public</span> <span style="box-sizing: border-box; color: #770088;">static</span> <span style="box-sizing: border-box; color: #770088;">function</span> <span style="box-sizing: border-box; color: #0000ff;">querySingle</span>(<span style="box-sizing: border-box; color: #0055aa;">$sql</span>){</span>
<span style="box-sizing: border-box; padding-right: 0.1px;"><span style="box-sizing: border-box; color: #770088;">self</span>::<span style="box-sizing: border-box; color: #000000;">instance</span>();</span>
<span style="box-sizing: border-box; padding-right: 0.1px;"><span style="box-sizing: border-box; color: #0055aa;">$result</span> <span style="box-sizing: border-box; color: #981a1a;">=</span> <span style="box-sizing: border-box; color: #000000;">@self</span>::<span style="box-sizing: border-box; color: #0055aa;">$db</span><span style="box-sizing: border-box; color: #981a1a;">-></span><span style="box-sizing: border-box; color: #000000;">querySingle</span>(<span style="box-sizing: border-box; color: #0055aa;">$sql</span>);</span>
<span style="box-sizing: border-box; padding-right: 0.1px;"><span style="box-sizing: border-box; color: #770088;">return</span> <span style="box-sizing: border-box; color: #0055aa;">$result</span> <span style="box-sizing: border-box; color: #981a1a;">?</span> <span style="box-sizing: border-box; color: #0055aa;">$result</span> : <span style="box-sizing: border-box; color: #22a2c9;">''</span>;</span>
<span style="box-sizing: border-box; padding-right: 0.1px;">}</span>

<span style="box-sizing: border-box; padding-right: 0.1px;"><span style="box-sizing: border-box; color: #aa5500;">/**</span></span>
<span style="box-sizing: border-box; padding-right: 0.1px;"> <span style="box-sizing: border-box; color: #aa5500;">* 查询单条记录</span></span>
<span style="box-sizing: border-box; padding-right: 0.1px;"> <span style="box-sizing: border-box; color: #aa5500;">* @param string $sql</span></span>
<span style="box-sizing: border-box; padding-right: 0.1px;"> <span style="box-sizing: border-box; color: #aa5500;">* @return array</span></span>
<span style="box-sizing: border-box; padding-right: 0.1px;"> <span style="box-sizing: border-box; color: #aa5500;">*/</span></span>
<span style="box-sizing: border-box; padding-right: 0.1px;"><span style="box-sizing: border-box; color: #770088;">public</span> <span style="box-sizing: border-box; color: #770088;">static</span> <span style="box-sizing: border-box; color: #770088;">function</span> <span style="box-sizing: border-box; color: #0000ff;">queryRow</span>(<span style="box-sizing: border-box; color: #0055aa;">$sql</span>){</span>
<span style="box-sizing: border-box; padding-right: 0.1px;"><span style="box-sizing: border-box; color: #770088;">self</span>::<span style="box-sizing: border-box; color: #000000;">instance</span>();</span>
<span style="box-sizing: border-box; padding-right: 0.1px;"><span style="box-sizing: border-box; color: #0055aa;">$result</span> <span style="box-sizing: border-box; color: #981a1a;">=</span> <span style="box-sizing: border-box; color: #000000;">@self</span>::<span style="box-sizing: border-box; color: #0055aa;">$db</span><span style="box-sizing: border-box; color: #981a1a;">-></span><span style="box-sizing: border-box; color: #000000;">querySingle</span>(<span style="box-sizing: border-box; color: #0055aa;">$sql</span>,<span style="box-sizing: border-box; color: #221199;">true</span>);</span>
<span style="box-sizing: border-box; padding-right: 0.1px;"><span style="box-sizing: border-box; color: #770088;">return</span> <span style="box-sizing: border-box; color: #0055aa;">$result</span>;</span>
<span style="box-sizing: border-box; padding-right: 0.1px;">}</span>

<span style="box-sizing: border-box; padding-right: 0.1px;"><span style="box-sizing: border-box; color: #aa5500;">/**</span></span>
<span style="box-sizing: border-box; padding-right: 0.1px;"> <span style="box-sizing: border-box; color: #aa5500;">* 查询多条记录</span></span>
<span style="box-sizing: border-box; padding-right: 0.1px;"> <span style="box-sizing: border-box; color: #aa5500;">* @param string $sql</span></span>
<span style="box-sizing: border-box; padding-right: 0.1px;"> <span style="box-sizing: border-box; color: #aa5500;">* @return array</span></span>
<span style="box-sizing: border-box; padding-right: 0.1px;"> <span style="box-sizing: border-box; color: #aa5500;">*/</span></span>
<span style="box-sizing: border-box; padding-right: 0.1px;"><span style="box-sizing: border-box; color: #770088;">public</span> <span style="box-sizing: border-box; color: #770088;">static</span> <span style="box-sizing: border-box; color: #770088;">function</span> <span style="box-sizing: border-box; color: #0000ff;">queryList</span>(<span style="box-sizing: border-box; color: #0055aa;">$sql</span>){</span>
<span style="box-sizing: border-box; padding-right: 0.1px;"><span style="box-sizing: border-box; color: #770088;">self</span>::<span style="box-sizing: border-box; color: #000000;">instance</span>();</span>
<span style="box-sizing: border-box; padding-right: 0.1px;"><span style="box-sizing: border-box; color: #0055aa;">$result</span> <span style="box-sizing: border-box; color: #981a1a;">=</span> <span style="box-sizing: border-box; color: #770088;">array</span>();</span>
<span style="box-sizing: border-box; padding-right: 0.1px;"><span style="box-sizing: border-box; color: #0055aa;">$ret</span> <span style="box-sizing: border-box; color: #981a1a;">=</span> <span style="box-sizing: border-box; color: #000000;">@self</span>::<span style="box-sizing: border-box; color: #0055aa;">$db</span><span style="box-sizing: border-box; color: #981a1a;">-></span><span style="box-sizing: border-box; color: #000000;">query</span>(<span style="box-sizing: border-box; color: #0055aa;">$sql</span>);</span>
<span style="box-sizing: border-box; padding-right: 0.1px;"><span style="box-sizing: border-box; color: #770088;">if</span> (<span style="box-sizing: border-box; color: #981a1a;">!</span><span style="box-sizing: border-box; color: #0055aa;">$ret</span>) {</span>
<span style="box-sizing: border-box; padding-right: 0.1px;"><span style="box-sizing: border-box; color: #770088;">return</span> <span style="box-sizing: border-box; color: #0055aa;">$result</span>;</span>
<span style="box-sizing: border-box; padding-right: 0.1px;">}</span>
<span style="box-sizing: border-box; padding-right: 0.1px;"><span style="box-sizing: border-box; color: #770088;">while</span>(<span style="box-sizing: border-box; color: #0055aa;">$row</span> <span style="box-sizing: border-box; color: #981a1a;">=</span> <span style="box-sizing: border-box; color: #0055aa;">$ret</span><span style="box-sizing: border-box; color: #981a1a;">-></span><span style="box-sizing: border-box; color: #000000;">fetchArray</span>(<span style="box-sizing: border-box; color: #000000;">SQLITE3_ASSOC</span>) ){</span>
<span style="box-sizing: border-box; padding-right: 0.1px;"><span style="box-sizing: border-box; color: #3300aa;">array_push</span>(<span style="box-sizing: border-box; color: #0055aa;">$result</span>, <span style="box-sizing: border-box; color: #0055aa;">$row</span>);</span>
<span style="box-sizing: border-box; padding-right: 0.1px;">}</span>
<span style="box-sizing: border-box; padding-right: 0.1px;"><span style="box-sizing: border-box; color: #770088;">return</span> <span style="box-sizing: border-box; color: #0055aa;">$result</span>;</span>
<span style="box-sizing: border-box; padding-right: 0.1px;">}</span>
<span style="box-sizing: border-box; padding-right: 0.1px;">}</span>

<span style="box-sizing: border-box; padding-right: 0.1px;"><span style="box-sizing: border-box; color: #981a1a;">?></span></span>
100
101
<?php
class SQLiteDB extends SQLite3 {
function __construct(){
try {
$this->open(dirname(__FILE__).'/../data/sqlite_ecloud.db');
}catch (Exception $e){
die($e->getMessage());
}
private static $db;
if (!self::$db) {
self::$db = new SQLiteDB();
}
/**
* 创建表
* @param string $sql
*/
public static function create($sql){
$result = @self::$db->query($sql);
}
/**
* 执行增删改操作
* @param string $sql
*/
public static function execute($sql){
$result = @self::$db->exec($sql);
}
/**
* 获取记录条数
* @param string $sql
* @return int
*/
public static function count($sql){
$result = @self::$db->querySingle($sql);
return $result ? $result : 0;
}
/**
* 查询单个字段
* @param string $sql
* @return void|string
*/
public static function querySingle($sql){
$result = @self::$db->querySingle($sql);
}
/**
* 查询单条记录
* @param string $sql
* @return array
*/
public static function queryRow($sql){
$result = @self::$db->querySingle($sql,true);
}
/**
* 查询多条记录
* @param string $sql
* @return array
*/
public static function queryList($sql){
$ret = @self::$db->query($sql);
}
while($row = $ret->fetchArray(SQLITE3_ASSOC) ){
array_push($result, $row);
}
?>
调用方式：引入DBUtils.php文件，然后通过 *DBUtils::方法名* 这种形式，即可操作SQLite数据库了。
原文链接：https://blog.csdn.net/tdcqfyl/article/details/52055437
发表回复 取消回复

发表回复取消回复
