Problème avec SQLite : Database locked

Salut,
J'ai développé une application avec MySql et pour des raisons de déploiement je tourne vers SQLite. J'ai modifié mon fichier de configuration hibernate et j'ai créé la classe SQLiteDialect mais j'ai une exception qui est levée : Database locked.

J'ai cherché à propos de cette exception et ça parait étre levée à cause de plusieurs connexions en méme temps alors que j'ai modifé mon code pour fermer la session et la session factory aprés chaque transaction mais sans succés. Je vous communique la trace de l'exception ainsi que mon code. Merci pour votre aide :)

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
Caused by: java.sql.SQLException: [SQLITE_BUSY]  The database file is locked (database is locked)
	at org.sqlite.DB.newSQLException(DB.java:383)
	at org.sqlite.DB.newSQLException(DB.java:387)
	at org.sqlite.DB.throwex(DB.java:374)
	at org.sqlite.NativeDB.prepare(Native Method)
	at org.sqlite.DB.prepare(DB.java:123)
	at org.sqlite.PrepStmt.<init>(PrepStmt.java:42)
	at org.sqlite.Conn.prepareStatement(Conn.java:404)
	at org.sqlite.Conn.prepareStatement(Conn.java:399)
	at org.sqlite.Conn.prepareStatement(Conn.java:383)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.lang.reflect.Method.invoke(Method.java:601)
	at org.hibernate.engine.jdbc.internal.proxy.ConnectionProxyHandler.continueInvocation(ConnectionProxyHandler.java:138)
	... 26 more

Mon fichier de configuration est le suivant : hibernate.config.xml

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
 
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE hibernate-configuration PUBLIC "-//Hibernate/Hibernate Configuration DTD 3.0//EN" 
"http://hibernate.sourceforge.net/hibernate-configuration-3.0.dtd">
 
<hibernate-configuration>
    <session-factory>
        <property name="show_sql">true</property>
        <property name="format_sql">true</property>
        <property name="dialect">org.hibernate.dialect.SQLiteDialect</property>
        <property name="connection.driver_class">org.sqlite.JDBC</property>
        <property name="connection.url">jdbc:sqlite:applicationData.db</property>
        <property name="connection.username"></property>
        <property name="connection.password"></property>
 
        <property name="hibernate.hbm2ddl.auto">update</property>
 
        <mapping class="model.Configuration"/>
        <mapping classe=".............."     />
 
 
    </session-factory>
</hibernate-configuration>

et la classe SQLiteDialec :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
 
package org.hibernate.dialect;
 
import java.sql.Types;
 
import org.hibernate.dialect.Dialect;
import org.hibernate.dialect.function.AbstractAnsiTrimEmulationFunction;
import org.hibernate.dialect.function.NoArgSQLFunction;
import org.hibernate.dialect.function.SQLFunction;
import org.hibernate.dialect.function.SQLFunctionTemplate;
import org.hibernate.dialect.function.StandardSQLFunction;
import org.hibernate.dialect.function.VarArgsSQLFunction;
import org.hibernate.type.StandardBasicTypes;
 
public class SQLiteDialect extends Dialect {
  public SQLiteDialect() {
    registerColumnType(Types.BIT, "boolean");
    registerColumnType(Types.TINYINT, "tinyint");
    registerColumnType(Types.SMALLINT, "smallint");
    registerColumnType(Types.INTEGER, "integer");
    registerColumnType(Types.BIGINT, "bigint");
    registerColumnType(Types.FLOAT, "float");
    registerColumnType(Types.REAL, "real");
    registerColumnType(Types.DOUBLE, "double");
    registerColumnType(Types.NUMERIC, "numeric($p, $s)");
    registerColumnType(Types.DECIMAL, "decimal");
    registerColumnType(Types.CHAR, "char");
    registerColumnType(Types.VARCHAR, "varchar($l)");
    registerColumnType(Types.LONGVARCHAR, "longvarchar");
    registerColumnType(Types.DATE, "date");
    registerColumnType(Types.TIME, "time");
    registerColumnType(Types.TIMESTAMP, "datetime");
    registerColumnType(Types.BINARY, "blob");
    registerColumnType(Types.VARBINARY, "blob");
    registerColumnType(Types.LONGVARBINARY, "blob");
    registerColumnType(Types.BLOB, "blob");
    registerColumnType(Types.CLOB, "clob");
    registerColumnType(Types.BOOLEAN, "boolean");
 
    //registerFunction( "abs", new StandardSQLFunction("abs") );
    registerFunction( "concat", new VarArgsSQLFunction(StandardBasicTypes.STRING, "", "||", "") );
    //registerFunction( "length", new StandardSQLFunction("length", StandardBasicTypes.LONG) );
    //registerFunction( "lower", new StandardSQLFunction("lower") );
    registerFunction( "mod", new SQLFunctionTemplate(StandardBasicTypes.INTEGER, "?1 % ?2" ) );
    registerFunction( "quote", new StandardSQLFunction("quote", StandardBasicTypes.STRING) );
    registerFunction( "random", new NoArgSQLFunction("random", StandardBasicTypes.INTEGER) );
    registerFunction( "round", new StandardSQLFunction("round") );
    registerFunction( "substr", new StandardSQLFunction("substr", StandardBasicTypes.STRING) );
    registerFunction( "substring", new SQLFunctionTemplate( StandardBasicTypes.STRING, "substr(?1, ?2, ?3)" ) );
    registerFunction( "trim", new AbstractAnsiTrimEmulationFunction() {
        protected SQLFunction resolveBothSpaceTrimFunction() {
          return new SQLFunctionTemplate(StandardBasicTypes.STRING, "trim(?1)");
        }
 
        protected SQLFunction resolveBothSpaceTrimFromFunction() {
          return new SQLFunctionTemplate(StandardBasicTypes.STRING, "trim(?2)");
        }
 
        protected SQLFunction resolveLeadingSpaceTrimFunction() {
          return new SQLFunctionTemplate(StandardBasicTypes.STRING, "ltrim(?1)");
        }
 
        protected SQLFunction resolveTrailingSpaceTrimFunction() {
          return new SQLFunctionTemplate(StandardBasicTypes.STRING, "rtrim(?1)");
        }
 
        protected SQLFunction resolveBothTrimFunction() {
          return new SQLFunctionTemplate(StandardBasicTypes.STRING, "trim(?1, ?2)");
        }
 
        protected SQLFunction resolveLeadingTrimFunction() {
          return new SQLFunctionTemplate(StandardBasicTypes.STRING, "ltrim(?1, ?2)");
        }
 
        protected SQLFunction resolveTrailingTrimFunction() {
          return new SQLFunctionTemplate(StandardBasicTypes.STRING, "rtrim(?1, ?2)");
        }
    } );
    //registerFunction( "upper", new StandardSQLFunction("upper") );
  }
 
  public boolean supportsIdentityColumns() {
    return true;
  }
 
  /*
  public boolean supportsInsertSelectIdentity() {
    return true; // As specify in NHibernate dialect
  }
  */
 
  public boolean hasDataTypeInIdentityColumn() {
    return false; // As specify in NHibernate dialect
  }
 
  /*
  public String appendIdentitySelectToInsert(String insertString) {
    return new StringBuffer(insertString.length()+30). // As specify in NHibernate dialect
      append(insertString).
      append("; ").append(getIdentitySelectString()).
      toString();
  }
  */
 
  public String getIdentityColumnString() {
    // return "integer primary key autoincrement";
    return "integer";
  }
 
  public String getIdentitySelectString() {
    return "select last_insert_rowid()";
  }
 
  public boolean supportsLimit() {
    return true;
  }
 
  public boolean bindLimitParametersInReverseOrder() {
    return true;
  }
 
  protected String getLimitString(String query, boolean hasOffset) {
    return new StringBuffer(query.length()+20).
      append(query).
      append(hasOffset ? " limit ? offset ?" : " limit ?").
      toString();
  }
 
  public boolean supportsTemporaryTables() {
    return true;
  }
 
  public String getCreateTemporaryTableString() {
    return "create temporary table if not exists";
  }
 
  public boolean dropTemporaryTableAfterUse() {
    return true; // TODO Validate
  }
 
  public boolean supportsCurrentTimestampSelection() {
    return true;
  }
 
  public boolean isCurrentTimestampSelectStringCallable() {
    return false;
  }
 
  public String getCurrentTimestampSelectString() {
    return "select current_timestamp";
  }
 
  public boolean supportsUnionAll() {
    return true;
  }
 
  public boolean hasAlterTable() {
    return false; // As specify in NHibernate dialect
  }
 
  public boolean dropConstraints() {
    return false;
  }
 
  /*
  public String getAddColumnString() {
    return "add column";
  }
  */
 
  public String getForUpdateString() {
    return "";
  }
 
  public boolean supportsOuterJoinForUpdate() {
    return false;
  }
 
  public String getDropForeignKeyString() {
    throw new UnsupportedOperationException("No drop foreign key syntax supported by SQLiteDialect");
  }
 
  public String getAddForeignKeyConstraintString(String constraintName,
      String[] foreignKey, String referencedTable, String[] primaryKey,
      boolean referencesPrimaryKey) {
    throw new UnsupportedOperationException("No add foreign key syntax supported by SQLiteDialect");
  }
 
  public String getAddPrimaryKeyConstraintString(String constraintName) {
    throw new UnsupportedOperationException("No add primary key syntax supported by SQLiteDialect");
  }
 
  public boolean supportsIfExistsBeforeTableName() {
    return true;
  }
 
  public boolean supportsCascadeDelete() {
    return true;
  }
 
  /* not case insensitive for unicode characters by default (ICU extension needed)
  public boolean supportsCaseInsensitiveLike() {
    return true;
  }
  */
 
  public boolean supportsTupleDistinctCounts() {
    return false;
  }
 
  public String getSelectGUIDString() {
    return "select hex(randomblob(16))";
  }
}

Un exemple d'une classe DAOImpl que j'utilise :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
 
 // les imports
 
public class ConfigurationDAOImpl implements ConfigurationDAO {
 
 
    Session session;
 
 
	@Override
	public void add(Configuration config) {
		// TODO Auto-generated method stub
 
		SessionFactory sf = HibernateConfig.getSessionFactory();
        Transaction transaction = null;
        session = sf.openSession();
 
	    try {
	      transaction = session.beginTransaction();
	      session.save(config);
	      session.flush();
	      transaction.commit();
	    } catch (HibernateException e) {
	      transaction.rollback();
	      e.printStackTrace();
	      e.getCause();
	    } finally {
	      session.close();
	      sf.close();
	    }
 
 
 
 
	}
 
	@Override
	public void update(Configuration config) {
		// TODO Auto-generated method stub
 
		Transaction transaction = null;
		SessionFactory sf = HibernateConfig.getSessionFactory();
		session = sf.openSession();
 
	    try {
	      transaction = session.beginTransaction();
	      session.update(config);
	      session.flush();
	      transaction.commit();
	    } catch (HibernateException e) {
	      transaction.rollback();
	      e.printStackTrace();
	      e.getCause();
	    } finally {
	      session.close();
	      sf.close();
	    }
 
 
 
	}
 
	@Override
	public void delete(Configuration config) {
		// TODO Auto-generated method stub
		Transaction transaction = null; 
		SessionFactory sf = HibernateConfig.getSessionFactory();
		session = sf.openSession();
	    try {
	      transaction = session.beginTransaction();
	      session.delete(config);
	      transaction.commit();
	    } catch (HibernateException e) {
	      transaction.rollback();
	      e.printStackTrace();
	      e.getCause();
	    } finally {
	      session.close();
	      sf.close();
	    }
 
 
	}
 
 
	@Override
	public Configuration getConfigById(int id) {
		// TODO Auto-generated method stub
		SessionFactory sf = HibernateConfig.getSessionFactory();
        Transaction transaction = null;
        session = sf.openSession();
        Configuration config = null;
 
	    try {
	      transaction = session.beginTransaction();
	      config = (Configuration) session.get(Configuration.class, id);
	    } catch (HibernateException e) {
	      transaction.rollback();
	      e.printStackTrace();
	      e.getCause();
	    } finally {
	      session.close();
	      sf.close();
	    }
 
		return config;
	}
 
	@Override
	public Configuration getLastConfig() {
		// TODO Auto-generated method stub
		Transaction transaction = null;
		SessionFactory sf = HibernateConfig.getSessionFactory();
		session = sf.openSession();
        Configuration lastConfig = null;
 
	    try {
	      transaction = session.beginTransaction();
	      Query query = session.createQuery("from Configuration order by CONFIG_ID DESC");
	      query.setMaxResults(1);
	      lastConfig = (Configuration) query.uniqueResult();
 
 
 
	    } catch (HibernateException e) {
	      transaction.rollback();
	      e.printStackTrace();
	      e.getCause();
	    } finally {
	      session.close();
	      sf.close();
	    }
 
		return lastConfig;
	}
 
	@Override
	public Iterator<Configuration> getAll() {
		// TODO Auto-generated method stub
		SessionFactory sf = HibernateConfig.getSessionFactory();
		session = sf.openSession();
		session.beginTransaction();
		Query hql = session.createQuery("Select c from " + Configuration.class.getSimpleName() + " c");
		Iterator<Configuration> configsList = hql.iterate();
 
		return configsList;
	}
 
 
 
}