The teaching process is mutually exclusive to the SQLite database.

Background: two independent processes (non-threads) simultaneously perform read and write operations on the SQLite file database. SQLite supports multi-process read operations, but does not support multi-process write operations. Therefore, I need to handle the issue of writing SQLite to many processes.


I wrote a small program to simulate this task. However, no protection measures are added to the program. I have checked a lot of methods, and I am currently using filelock to mutex processes to write sqilte files.

Putter thread code:

Package COM. locksqlite; import Java. io. fileoutputstream; import Java. io. ioexception; import Java. NIO. channels. filelock;/*** want to store data in the database **/public class putter implements runnable {private dB DB = NULL; private string name = NULL; Public Putter (string name) {This. name = Name; this. DB = new dB () ;}@ overridepublic void run () {for (INT I = 0; I <5000; I ++) {boolean result = dB. putone (New Entity ("wangxin" + I )) ; If (! Result) {system. Out. println (name + "failed to put");} else {system. Out. println (name + "succeeded ");}}}}

Reader thread reads Database

Package COM. locksqlite; import Java. util. arraylist; import Org. apache. log4j. logger; public class reader implements runnable {@ overridepublic void run () {arraylist <entity> List = new arraylist <entity> (); DB = new dB (); for (INT I = 0; I <5000; I ++) {list = dB. getall (); system. out. println ("read to: --->"); For (Int J = 0; j <list. size (); j ++) {} system. out. println ("read ended: --->"); try {thread. sleep (500);} catch (interruptedexception e) {e. printstacktrace ();}}}}

Database Operation class:

DB. Java

Package COM. locksqlite; import Java. SQL. connection; import Java. SQL. drivermanager; import Java. SQL. preparedstatement; import Java. SQL. resultset; import Java. SQL. sqlexception; import Java. util. arraylist; public class dB {Private Static connection = NULL; Private Static preparedstatement = NULL; private resultset = NULL; public static Boolean Init () {try {class. forname (Co Nstants. jdbc_url); connection = drivermanager. getconnection (constants. connection_url + constants. db_name);} catch (classnotfoundexception e) {system. out. println ("class not found"); Return false;} catch (sqlexception e) {system. out. println ("SQL exception"); Return false;} string SQL = "CREATE TABLE if not exists" + constants. hiep_tb + "'(Name text, age text);"; resultset = NULL; try {preparedstatement = connection .Preparestatement( SQL ##preparedstatement.exe cute (); Return true;} catch (sqlexception E1) {e1.printstacktrace (); Return false;} private Boolean isexist (string name) {string SQL = "select * from '" + constants. hiep_tb + "'where name =? "; Try {preparedstatement = connection. preparestatement (SQL); preparedstatement. setstring (1, name); resultset = preparedstatement.exe cutequery (); If (resultset. next () {return true ;}} catch (sqlexception e) {e. printstacktrace (); Return false;} return false;} public synchronized Boolean putone (entity one) {If (isexist (one. getname () {system. out. println ("already exists"); Return false;} else {string SQL = "insert int O' "+ constants. hiep_tb +" 'values (?,?) "; Try {preparedstatement = connection. preparestatement (SQL); preparedstatement. setstring (1, one. getname (); preparedstatement. setstring (2, one.getage(registry.preparedstatement.exe cuteupdate (); Return true;} catch (sqlexception e) {e. printstacktrace () ;}}return false;} public arraylist <entity> getall () {arraylist <entity> List = new arraylist <entity> (); entity one = new entity (); string SQL = "select * From hieptb"; try {preparedstatement = connection. preparestatement (SQL); resultset = preparedstatement.exe cutequery (); While (resultset. next () {one. setname (resultset. getstring ("name"); one. setage (resultset. getstring ("Age"); list. add (one);} return list;} catch (sqlexception e) {e. printstacktrace (); return NULL ;}}}

Entity class stored in the database: entity. Java

package com.lockSqlite;public class Entity {private String name;private String age;public Entity() {}public Entity(String name) {this.age = 0 + "";this.name = name;}public String getName() {return name;}public void setName(String name) {this.name = name;}public String getAge() {return age;}public void setAge(String age) {this.age = age;}@Overridepublic String toString() {return "Entity [name=" + name + ", age=" + age + "]";}}

Two process testing classes:

public class TestMain1 {public static void main(String[] args) throws Exception {DB.init();new Thread(new Putter("putter1")).start();new Thread(new Putter("putter2")).start();new Thread(new Putter("putter3")).start();new Thread(new Putter("putter4")).start();new Thread(new Putter("putter5")).start();new Thread(new Putter("putter6")).start();new Thread(new Putter("putter7")).start();new Thread(new Putter("putter8")).start();new Thread(new Reader()).start();}}

Another test process:

package com.lockSqlite;public class TestMain2 {public static void main(String args[]) {DB.init();new Thread(new Putter("putter1")).start();new Thread(new Putter("putter2")).start();new Thread(new Putter("putter3")).start();new Thread(new Putter("putter4")).start();new Thread(new Putter("putter5")).start();new Thread(new Putter("putter6")).start();new Thread(new Putter("putter7")).start();new Thread(new Putter("putter8")).start();new Thread(new Reader()).start();}}

I want to use filelock for processing, but it has never been successful. Hope you can help me.

Original project code: