C#/Java连接sqlite与使用技巧
C#/Java连接sqlite与使用技巧
发布时间:2016-12-28 来源:查字典编辑
摘要:1)下载sqlitejdbc驱动http://www.xerial.org/maven/repository/artifact/org/xe...

1)下载sqlite jdbc驱动http://www.xerial.org/maven/repository/artifact/org/xerial/sqlite-jdbc/

2)将下载的驱动加入eclipse项目的built path中

3)示例代码:

复制代码 代码如下:

package com.hedalixin;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.sql.Statement;

public class test {

/**

* @param args

*/

public static void main(String[] args) throws Exception {

// TODO Auto-generated method stub

Class.forName("org.sqlite.JDBC");

Connection conn = DriverManager.getConnection("jdbc:sqlite:test.db");

Statement stat = conn.createStatement();

stat.executeUpdate("drop table if exists people;");

stat.executeUpdate("create table people (name, occupation);");

PreparedStatement prep = conn

.prepareStatement("insert into people values (?, ?);");

prep.setString(1, "Gandhi");

prep.setString(2, "politics");

prep.addBatch();

prep.setString(1, "Turing");

prep.setString(2, "computers");

prep.addBatch();

prep.setString(1, "Wittgenstein");

prep.setString(2, "smartypants");

prep.addBatch();

conn.setAutoCommit(false);

prep.executeBatch();

conn.setAutoCommit(true);

ResultSet rs = stat.executeQuery("select * from people;");

while (rs.next()) {

System.out.println("name = " + rs.getString("name"));

System.out.println("job = " + rs.getString("occupation"));

}

rs.close();

conn.close();

}

}

2. C#连接sqlite

2.1 使用SQLITE.NET

SQLite.NET也是一个数据访问组件,其中的System.Data.SQLite 就好像是.NET自带的System.Data.SqlClient一样。里面包含了connection、command等数据访问的常用对象,只是他们前面都有一个前缀sqlite。

1)下载System.Data.SQLite,下载地址http://system.data.sqlite.org/index.html/doc/trunk/www/downloads.wiki

2) 通过Add References引用SQLite ADO .NET安装目录的bin目录下的System.Data.SQLite.DLL。

3)创建表、读取数据等和Access或MS SQL没多大区别

复制代码 代码如下:

//创建一个数据库文件

string datasource="h:/test.db";

System.Data.SQLite.SQLiteConnection.CreateFile(datasource);

//连接数据库

System.Data.SQLite.SQLiteConnection conn = new System.Data.SQLite.SQLiteConnection();

System.Data.SQLite.SQLiteConnectionStringBuilder connstr = new System.Data.SQLite.SQLiteConnectionStringBuilder();

connstr.DataSource = datasource;

connstr.Password = "admin";//设置密码,SQLite ADO.NET实现了数据库密码保护

conn.ConnectionString = connstr.ToString();

conn.Open();

//创建表

System.Data.SQLite.SQLiteCommand cmd = new System.Data.SQLite.SQLiteCommand();

string sql = "CREATE TABLE test(username varchar(20),password varchar(20))";

cmd.CommandText=sql;

cmd.Connection=conn;

cmd.ExecuteNonQuery();

//插入数据

sql = "INSERT INTO test VALUES('ekinglong','mypassword')";

cmd.CommandText = sql;

cmd.ExecuteNonQuery();

//取出数据

sql = "SELECT * FROM test";

cmd.CommandText = sql;

System.Data.SQLite.SQLiteDataReader reader = cmd.ExecuteReader();

StringBuilder sb = new StringBuilder();

while (reader.Read())

{

sb.Append("username:").Append(reader.GetString(0)).Append("n")

.Append("password:").Append(reader.GetString(1));

}

MessageBox.Show(sb.ToString());

2.2使用原生态的ADO.NET访问SQLite

复制代码 代码如下:

using (DbConnection conn = new SQLiteConnection( System.Configuration.ConfigurationManager.ConnectionStrings["sqlite"].ConnectionString))

{

conn.Open();

DbCommand comm = conn.CreateCommand();

comm.CommandText = "select * from customer";

comm.CommandType = CommandType.Text;

using (IDataReader reader = comm.ExecuteReader())

{

while (reader.Read())

{

Response.Write(reader[0]);

}

}

}

SQLite.NET数据库连接字符串ConnectionString格式:

Basic(基本的)

Data Source=filename;Version=3;

Using UTF16(使用UTF16编码)

Data Source=filename;Version=3;UseUTF16Encoding=True;

With password(带密码的)

Data Source=filename;Version=3;Password=myPassword;

Using the pre 3.3x database format(使用3.3x前数据库格式)

Data Source=filename;Version=3;Legacy Format=True;

Read only connection(只读连接)

Data Source=filename;Version=3;Read Only=True;

With connection pooling(设置连接池)

Data Source=filename;Version=3;Pooling=False;Max Pool Size=100;

Using DateTime.Ticks as datetime format()

Data Source=filename;Version=3;DateTimeFormat=Ticks;

The default value is ISO8601 which activates the use of the ISO8601 datetime format

Store GUID as text(把Guid作为文本存储,默认是Binary)

Data Source=filename;Version=3;BinaryGUID=False;

如果把Guid作为文本存储需要更多的存储空间

Specify cache size(指定Cache大小)

Data Source=filename;Version=3;Cache Size=2000;

Cache Size 单位是字节

Specify page size(指定页大小)

Data Source=filename;Version=3;Page Size=1024;

Page Size 单位是字节

Disable enlistment in distributed transactions

Data Source=filename;Version=3;Enlist=N;

Disable create database behaviour(禁用创建数据库行为)

Data Source=filename;Version=3;FailIfMissing=True;

默认情况下,如果数据库文件不存在,会自动创建一个新的,使用这个参数,将不会创建,而是抛出异常信息

Limit the size of database(限制数据库大小)

Data Source=filename;Version=3;Max Page Count=5000;

The Max Page Count is measured in pages. This parameter limits the maximum number of pages of the database.

Disable the Journal File (禁用日志回滚)

Data Source=filename;Version=3;Journal Mode=Off;

This one disables the rollback journal entirely.

Persist the Journal File(持久)

Data Source=filename;Version=3;Journal Mode=Persist;

This one blanks and leaves the journal file on disk after a commit. Default behaviour is to delete the Journal File after each commit.

Controling file flushing

Data Source=filename;Version=3;Synchronous=Full;

Full specifies a full flush to take action after each write. Normal is the default value. Off means that the underlying OS flushes I/O's.

Sqlite使用技巧

1 .Sqlite判断数据表是否存在

SELECT COUNT(*) as CNT FROM sqlite_master where type='table' and name='DBInfo' //其中DBInfo为需要判断的表名。注意大小写敏感!

2. SQLite一条SQL语句插入多条记录

INSERT INTO TABLE(col1, col2) SELECT val11, val12 UNION ALL SELECT val21, val22 ;

这样的写法是属于复合SQL语句,表示先把两个SELECT的结果集进行无删减的联合,再把联合结果插入到TABLE中。

3. sqlite事务

是以文件的形式存在磁盘中,每次访问时都要打开一次文件,如果对数据进行大量操作时,会很慢~

解决办法是用事务的形式提交:因为我们开始事务后,进行大量操作的语句都保存在内存中,当提交时才全部写入数据库,此时,数据库文件也就只用打开一次。

sql语句:

begin;

INSERT INTO "table" VALUES ('a', 'b', 'c');

INSERT INTO "table" VALUES ('a', 'b', 'c');

INSERT INTO "table" VALUES ('a', 'b', 'c');

commit;

4. SQLite自增ID字段使用方法为 INTEGER PRIMARY KEY AUTOINCREMENT

5. 分页查询显示

类似MySQL数据库 ,是利用mySQL的LIMIT函数,LIMIT [offset,] rows从数据库表中M条记录开始检索N条记录的语句为:

SELECT * FROM 表名称 LIMIT M,N

例如从表Sys_option(主键为sys_id)中从10条记录开始检索20条记录,语句如下:

select * from sys_option limit 10,20

推荐文章
猜你喜欢
附近的人在看
推荐阅读
拓展阅读
相关阅读
网友关注
最新C#教程学习
热门C#教程学习
编程开发子分类