安装和使用sqlite数据库

一、安装

sudoapt-get install sqlite sqlite3libsqlite3-dev

二、交互使用

$ sqlite3 stu.db 
SQLite version 3.11.0 2016-02-15 17:29:24
Enter ".help" for usage hints.
sqlite> CREATE TABLE student(name,num);
sqlite> insert into student values('wang',1002);
sqlite> insert into student values('limin',1003);
sqlite> select * from student;
wang|1002
limin|1003
sqlite> .exit

三、c语言接口

代码mysqlite.c

#include <stdio.h>  
#include <sqlite3.h>
#include <string.h>  

int printSqlResult(void *para,int column,char **value,char **key)  
{  
	int i;  
	printf("column=%dn",column);  
	for(i = 0; i < column; i++)  
	{  
	    printf("%st",*(value+i));  
	}  
	printf("n");  
	return SQLITE_OK;  
}  
int main(int argc,char *argv[])  
{  
	sqlite3 *pDb;  
	int j,i,pos,row,col,ret;  
	char acCmd[128];  
	char **ppRet;  

	if(argc < 2)  
	{  
	    printf("please input sql command!");  
	    return -1;  
	}  
	  
	strcpy(acCmd,argv[1]);  

	ret = sqlite3_open("./stu.db",&pDb);  
	if(ret != SQLITE_OK)  
	{  
	    printf("open database fail!n");  
	    return -1;  
	}  

	ret = sqlite3_exec(pDb,acCmd,printSqlResult,NULL,NULL);  
	{  
	    if(ret != SQLITE_OK)  
	    {  
		printf("exec fail,ret %dn",ret);  
		return -1;  
	    }  
	}  
	pos = sqlite3_last_insert_rowid(pDb);  
	printf("pos = %dn",pos);  

	sqlite3_get_table(pDb,"select * from student;",&ppRet,&row,&col,NULL);  

	for(i = 0; i <= row; i++)  
	{  
	    for(j = 0; j < col; j++)  
	    {  
		printf("%st",*(ppRet+i*col+j));  
	    }  
	    printf("n");  
	}  
	sqlite3_free_table(ppRet);  
	sqlite3_close(pDb);  
	return 0;  
}

Makefile

# Edit the following for your installation
CC	 = gcc  
CFLAG =    -g -Wall -O2   
LFLAGS	=	`pkg-config --libs sqlite3`
INCPATH	=	`pkg-config --cflags sqlite3`

####### Implicit rules
.c.o:
	$(CC) -c $(CFLAG) $(INCPATH) -o "$@" "$<"

# File dependencies and rules
OBJS = mysqlite.o
PRG = mysqlite

all: $(PRG)    
$(PRG): $(OBJS)  
	$(CC) -o $(PRG) $(OBJS) $(LFLAGS)
clean:  
	rm -rf $(OBJS) $(PRG)

当手动编译安装在自己的路径时需要设置好环境变量，特别要设置PKG_CONFIG_PATH，否则pkg-config会找不到sqlite3.pc(apt安装时该文件位置在/usr/lib/x86_64-linux-gnu/pkgconfig/sqlite3.pc)

运行

$ ./mysqlite "select * from student"
column=2
wang	1002	
column=2
limin	1003	
pos = 0
name	num	
wang	1002	
limin	1003

四、JDBC

下载驱动：

https://bitbucket.org/xerial/sqlite-jdbc/downloads/

wget https://bitbucket.org/xerial/sqlite-jdbc/downloads/sqlite-jdbc-3.18.0.jar

$ sqlite3 sample.db 
SQLite version 3.11.0 2016-02-15 17:29:24
Enter ".help" for usage hints.
sqlite> .exit

Java代码Sample.java

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

public class Sample
{
  public static void main(String[] args) throws ClassNotFoundException
  {
    // load the sqlite-JDBC driver using the current class loader
    Class.forName("org.sqlite.JDBC");

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

      statement.executeUpdate("drop table if exists person");
      statement.executeUpdate("create table person (id integer,name string)");
      statement.executeUpdate("insert into person values(1,'leo')");
      statement.executeUpdate("insert into person values(2,'yui')");
      ResultSet rs = statement.executeQuery("select * from person");
      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(connection != null)
          connection.close();
      }
      catch(SQLException e)
      {
        // connection close failed.
        System.err.println(e);
      }
    }
  }
}

如sample.db不在当前目录，可使用绝对路径

编译运行：

$ javac Sample.java
$  java -classpath ".:sqlite-jdbc-3.18.0.jar" Sample 
name = leo
id = 1
name = yui
id = 2

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!