probleme de Table not found
bonjour a tous , j`essaie de faire Développement dirigé par
les tests : mise en pratique mais ma connection jdbc pour hsql fonctionne pas
voici l'erreur
Citation:
com.ibatis.common.jdbc.exception.NestedSQLException:
--- The error occurred in com/developpez/mynotes/dao/Security.xml.
--- The error occurred while executing query.
--- Check the select USER_ID as userId, FIRST_NAME as firstName, LAST_NAME as lastName, EMAIL as email, IS_ADMIN as isAdmin from UTILISATEUR where USER_ID = ? and PASSWORD = ? .
--- Check the SQL Statement (preparation failed).
--- Cause: java.sql.SQLException: Table not found in statement [ select USER_ID as userId, FIRST_NAME as firstName, LAST_NAME as lastName, EMAIL as email, IS_ADMIN as isAdmin from UTILISATEUR where USER_ID = ? and PASSWORD = ? ]
at com.ibatis.sqlmap.engine.mapping.statement.MappedStatement.executeQueryWithCallback(MappedStatement.java:201)
at com.ibatis.sqlmap.engine.mapping.statement.MappedStatement.executeQueryForObject(MappedStatement.java:120)
at com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.queryForObject(SqlMapExecutorDelegate.java:518)
at com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.queryForObject(SqlMapExecutorDelegate.java:493)
at com.ibatis.sqlmap.engine.impl.SqlMapSessionImpl.queryForObject(SqlMapSessionImpl.java:106)
at com.ibatis.sqlmap.engine.impl.SqlMapClientImpl.queryForObject(SqlMapClientImpl.java:82)
at com.developpez.mynotes.dao.SecurityDaoImpl.authenticate(SecurityDaoImpl.java:31)
at com.developpez.mynotes.dao.SecurityDaoTest.authenticateSuccess(SecurityDaoTest.java:95)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
at java.lang.reflect.Method.invoke(Unknown Source)
at org.junit.internal.runners.TestMethod.invoke(TestMethod.java:59)
at org.junit.internal.runners.MethodRoadie.runTestMethod(MethodRoadie.java:98)
at org.junit.internal.runners.MethodRoadie$2.run(MethodRoadie.java:79)
at org.junit.internal.runners.MethodRoadie.runBeforesThenTestThenAfters(MethodRoadie.java:87)
at org.junit.internal.runners.MethodRoadie.runTest(MethodRoadie.java:77)
at org.junit.internal.runners.MethodRoadie.run(MethodRoadie.java:42)
at org.junit.internal.runners.JUnit4ClassRunner.invokeTestMethod(JUnit4ClassRunner.java:88)
at org.junit.internal.runners.JUnit4ClassRunner.runMethods(JUnit4ClassRunner.java:51)
at org.junit.internal.runners.JUnit4ClassRunner$1.run(JUnit4ClassRunner.java:44)
at org.junit.internal.runners.ClassRoadie.runUnprotected(ClassRoadie.java:27)
at org.junit.internal.runners.ClassRoadie.runProtected(ClassRoadie.java:37)
at org.junit.internal.runners.JUnit4ClassRunner.run(JUnit4ClassRunner.java:42)
at org.eclipse.jdt.internal.junit4.runner.JUnit4TestReference.run(JUnit4TestReference.java:50)
at org.eclipse.jdt.internal.junit.runner.TestExecution.run(TestExecution.java:38)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:467)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:683)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:390)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:197)
Caused by: java.sql.SQLException: Table not found in statement [ select USER_ID as userId, FIRST_NAME as firstName, LAST_NAME as lastName, EMAIL as email, IS_ADMIN as isAdmin from UTILISATEUR where USER_ID = ? and PASSWORD = ? ]
at org.hsqldb.jdbc.Util.throwError(Unknown Source)
at org.hsqldb.jdbc.jdbcPreparedStatement.<init>(Unknown Source)
at org.hsqldb.jdbc.jdbcConnection.prepareStatement(Unknown Source)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
at java.lang.reflect.Method.invoke(Unknown Source)
at com.ibatis.common.jdbc.SimpleDataSource$SimplePooledConnection.invoke(SimpleDataSource.java:958)
at $Proxy5.prepareStatement(Unknown Source)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
at java.lang.reflect.Method.invoke(Unknown Source)
at com.ibatis.common.jdbc.logging.ConnectionLogProxy.invoke(ConnectionLogProxy.java:53)
at $Proxy5.prepareStatement(Unknown Source)
at com.ibatis.sqlmap.engine.execution.SqlExecutor.prepareStatement(SqlExecutor.java:497)
at com.ibatis.sqlmap.engine.execution.SqlExecutor.executeQuery(SqlExecutor.java:175)
at com.ibatis.sqlmap.engine.mapping.statement.MappedStatement.sqlExecuteQuery(MappedStatement.java:221)
at com.ibatis.sqlmap.engine.mapping.statement.MappedStatement.executeQueryWithCallback(MappedStatement.java:189)
... 29 more
mon code
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
|
package com.developpez.mynotes.dao;
import java.io.IOException;
import java.io.Reader;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.Map;
import com.developpez.mynotes.model.User;
import com.ibatis.sqlmap.client.SqlMapClient;
import com.ibatis.sqlmap.client.SqlMapClientBuilder;
public class SecurityDaoImpl implements SecurityDao {
private static SqlMapClient sqlMap;
static {
try {
Reader reader = com.ibatis.common.resources.Resources.getResourceAsReader("com/developpez/mynotes/dao/sqlMapConfig.xml");
sqlMap = SqlMapClientBuilder.buildSqlMapClient(reader);
} catch (IOException e) {
e.printStackTrace();
System.out.println("Erreur lors de l'initialisation du DAO = "
+ e.getMessage());
}
}
public User authenticate(String userId, String password)
throws SQLException {
Map<String, String> parameters = new HashMap<String, String>();
parameters.put("userId", userId);
parameters.put("password", password);
return (User) sqlMap.queryForObject("authenticateUser", parameters);
}
} |
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
|
package com.developpez.mynotes.dao;
import static org.junit.Assert.assertEquals;
import static org.junit.Assert.assertFalse;
import static org.junit.Assert.assertNotNull;
import java.io.File;
import java.io.FileNotFoundException;
import java.sql.Connection;
import java.sql.Statement;
import java.util.Scanner;
import org.dbunit.IDatabaseTester;
import org.dbunit.JdbcDatabaseTester;
import org.dbunit.database.DatabaseConfig;
import org.dbunit.database.IDatabaseConnection;
import org.dbunit.dataset.IDataSet;
import org.dbunit.dataset.xml.XmlDataSet;
import org.dbunit.ext.hsqldb.HsqldbDataTypeFactory;
import org.dbunit.operation.DatabaseOperation;
import org.junit.Before;
import org.junit.Test;
import com.developpez.mynotes.model.User;
public class SecurityDaoTest {
@Before
public void setUp() throws Exception {
IDatabaseTester databaseTester = new JdbcDatabaseTester(
"org.hsqldb.jdbcDriver", "jdbc:hsqldb:mem:mynotes", "sa", "");
// initialisation de la base de donnees
// grouille pour gérer le type BOOLEAN deHSQLDB
// initialisation de la base de donnees
// grouille pour gérer le type BOOLEAN deHSQLDB
IDatabaseConnection dbUnitConnection = databaseTester.getConnection();
Connection connection = dbUnitConnection.getConnection();
Statement statement = connection.createStatement();
try {
statement = connection.createStatement();
String sql = readSqlFile("/mynotes.ddl");
statement.execute(sql);
}catch (Exception e) {
e.printStackTrace(); // TODO: handle exception
}
finally {
statement.close();
}
// grouille pour gérer le type BOOLEAN deHSQLDB
DatabaseConfig config = dbUnitConnection.getConfig();
config.setProperty(DatabaseConfig.PROPERTY_DATATYPE_FACTORY,
new HsqldbDataTypeFactory());
DatabaseOperation.CLEAN_INSERT.execute(dbUnitConnection, getDataSet());
}
private String readSqlFile(String fileName) throws FileNotFoundException {
StringBuilder strBuilder = new StringBuilder();
Scanner scanner= null;
try {
scanner = new Scanner(new File(getClass().getResource(fileName)
.getFile()));
while (scanner.hasNextLine()) {
String line = scanner.nextLine();
strBuilder.append(line);
}
} catch (Exception e) {
e.printStackTrace();
}
// TODO: handle exception
scanner.close();
return strBuilder.toString();
}
private IDataSet getDataSet() throws Exception {
IDataSet dataSet = new XmlDataSet(getClass().getResourceAsStream(
"/SecurityDaoTest.xml"));
return dataSet;
}
@Test
public void authenticateSuccess() throws Exception {
SecurityDao securityDao = new SecurityDaoImpl();
User user = securityDao.authenticate("JohnDoe", "seven");
assertNotNull("User should be not null", user);
assertEquals("JohnDoe", user.getUserId());
assertEquals("John", user.getFirstName());
assertEquals("Doe", user.getLastName());
assertEquals("john.doe@abc.com", user.getEmail());
assertFalse(user.isAdmin());
}
} |
et
Code:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
| <?xml version="1.0" encoding="UTF-8" ?>
<sqlMap namespace="User">
<!DOCTYPE sqlMap
PUBLIC "-//ibatis.apache.org//DTD SQL Map 2.0//EN"
"http://ibatis.apache.org/dtd/sql-map-2.dtd">
<typeAlias alias="user" type="com.developpez.mynotes.model.User"/>
<typeAlias alias="map" type="java.util.Map"/>
<select id="authenticateUser" resultClass="user" parameterClass="map">
select
USER_ID as userId,
FIRST_NAME as firstName,
LAST_NAME as lastName,
EMAIL as email,
IS_ADMIN as isAdmin
from PUBLIC.UTILISATEUR
where USER_ID = #userId# and PASSWORD = #password#
</select>
</sqlMap> |
pour security.xml
Code:
1
2
3
4
5
6
7
8
9
10
11
12
13
| <?xml version="1.0" encoding="UTF-8" ?>
<sqlMapConfig>
<properties resource="com/developpez/mynotes/dao/database.properties"/>
<transactionManager type="JDBC">
<dataSource type="SIMPLE">
<property name="JDBC.Driver" value="${driver}"/>
<property name="JDBC.ConnectionURL" value="${url}"/>
<property name="JDBC.Username" value="${username}"/>
<property name="JDBC.Password" value="${password}"/>
</dataSource>
</transactionManager>
<sqlMap resource="com/developpez/mynotes/dao/Security.xml"/>
</sqlMapConfig> |
pour sqlMapConfig.xml
et pour finir
Code:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
| DROP TABLE NOTE IF EXISTS;
DROP TABLE UTILISATEUR IF EXISTS;
CREATE TABLE UTILISATEUR (
USER_ID VARCHAR(20) NOT NULL,
FIRST_NAME VARCHAR(50) NOT NULL,
LAST_NAME VARCHAR(50) NOT NULL,
PASSWORD VARCHAR(30) NOT NULL,
EMAIL VARCHAR(50) NOT NULL,
IS_ADMIN BOOLEAN NOT NULL,
PRIMARY KEY(USER_ID)
);
CREATE TABLE NOTE (
ID INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH 0) NOT NULL,
USER_ID VARCHAR(20) NOT NULL,
TITLE VARCHAR(255) NULL,
CONTENT LONGVARCHAR NULL,
PRIMARY KEY(ID),
FOREIGN KEY (USER_ID) REFERENCES UTILISATEUR (USER_ID)
); |
Code:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
| DROP TABLE NOTE IF EXISTS;
DROP TABLE UTILISATEUR IF EXISTS;
CREATE TABLE UTILISATEUR (
USER_ID VARCHAR(20) NOT NULL,
FIRST_NAME VARCHAR(50) NOT NULL,
LAST_NAME VARCHAR(50) NOT NULL,
PASSWORD VARCHAR(30) NOT NULL,
EMAIL VARCHAR(50) NOT NULL,
IS_ADMIN BOOLEAN NOT NULL,
PRIMARY KEY(USER_ID)
);
CREATE TABLE NOTE (
ID INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH 0) NOT NULL,
USER_ID VARCHAR(20) NOT NULL,
TITLE VARCHAR(255) NULL,
CONTENT LONGVARCHAR NULL,
PRIMARY KEY(ID),
FOREIGN KEY (USER_ID) REFERENCES UTILISATEUR (USER_ID)
); |
donc tout est la mais ou est le probleme merci d'avance
