Discussion :

[chady]

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

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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