Ormlite operates SQLite in a general Java environment

Introduction

At first, I came into contact with ormlite in Android development. I was impressed by the simplicity and convenience of ormlite.

I have been studying customer shopping behavior over the past few days and need to use the database, but I have little requirement on the speed of the database. The first thing I think of is ormlite + SQLite.

Entity class

Ormlite official website http://ormlite.com/, Latest Version 4.41

Because it is used in a general Java environment, the android package is not required.

Create modal after the package is introduced, for example, commodity. java. Because SQLite is used, the primary key increases automatically and is set

 
@ Databasefield (generatedid = true)
 

CompleteCodeAs follows:

Package COM. cnblogs. htynkn. dataspider. modal; import COM. j256.ormlite. field. databasefield; import COM. j256.ormlite. table. databasetable;/*** @ author night-wide orphan light * @ date 2012-6-29 */@ databasetable (tablename = "commodity") public class commodity {@ databasefield (generatedid = true) private int ID; @ databasefield (columnname = "name") Private string name; @ databasefield (columnname = "jdkey") Private string jdkey; @ databasefield (columnname = "tbkey ") private string tbkey; public commodity () {} public int GETID () {return ID;} public void setid (int id) {This. id = ID;} Public String getname () {return name;} public void setname (string name) {This. name = Name;} Public String getjdkey () {return jdkey;} public void setjdkey (string jdkey) {This. jdkey = jdkey;} Public String gettbkey () {return tbkey;} public void settbkey (string tbkey) {This. tbkey = tbkey ;}}
 

Obtain Dao

Ormlite in the use of Android is more convenient, interested friends can refer to the http://www.cnblogs.com/htynkn/archive/2011/10/30/android_ormlite_1.html

The article aboveArticleIn, our datahelper inherits ormlitesqliteopenhelper. This class is unique to ormlite-android. It is not easy to use in a general Java environment.

The official website of ormlite mentioned that we should start with JDBC and use daomanager to create Dao. For example:

 
String connectionstring = "JDBC: SQLite: Data. DB ";
 

The data. DB file in the current directory is used. Ormlite does not include the JDBC package of SQLite. We need to download one by ourselves.

In this case, we must note that there are many JDBC packages for SQLite, and we must support auto-Growth of primary keys. For example, I used the zentus driver at the beginning of xerial driver.

Use

 
Daomanager. createdao (getconnectionsource (), commodity. Class );
 

Obtain Dao <commodity, integer>. To create a table, you can use tableutils.

The complete code is as follows:

Package COM. cnblogs. htynkn. dataspider. data; import Java. SQL. sqlexception; import Org. apache. commons. logging. log; import Org. apache. commons. logging. logfactory; import COM. cnblogs. htynkn. dataspider. modal. commodity; import COM. cnblogs. htynkn. dataspider. modal. transactionrecord; import COM. j256.ormlite. dao. dao; import COM. j256.ormlite. dao. daomanager; import COM. j256.ormlite. JDBC. jdbcconnectionsource; import COM. j256.ormlite. support. connectionsource; import COM. j256.ormlite. table. tableutils;/*** @ author night-wide lone row light * @ date 2012-6-29 */public class sqliteopenhelper {Private Static log = logfactory. getlog (sqliteopenhelper. class); Private Static Dao commoditydao; public static Dao getcommoditydao () throws sqlexception {If (commoditydao = NULL) {commoditydao = daomanager. createdao (getconnectionsource (), commodity. class);} return commoditydao;} public static void Init () {try {tableutils. createtable (getconnectionsource (), commodity. class);} catch (exception) {log. warn ("failed to create commodity table:" + exception. getmessage () ;}} public static connectionsource getconnectionsource () throws sqlexception {string connectionstring = "JDBC: SQLite: data. DB "; return New jdbcconnectionsource (connectionstring );}}

 

Advanced Query

Ormlite Dao supports simple addition, deletion, modification, and query by ID. Of course, native SQL can also be used. If conditional query is required, querybuilder is required.

 
Querybuilder QB = Dao. querybuilder ();
 

For example, we need to query the data of a time period.

 
QB. Where (). Between ("transactiontime", start, end); Return QB. Query ();
 

Here, a list will be returned, and the processing will continue.

If you really need to use native SQL statements, you can use

 
Dao. queryraw (query, arguments)