使用Spring的SimpleJdbcTemplate做了一个小实例,简单的查询功能。代码如下:
@Autowired
private SimpleJdbcTemplate jdbcTemplate;
@Test
public void shouldCreateRowsAndSetIds() {
Spitter spitter = jdbcTemplate.queryForObject(
"select id, username, password, fullname from spitter where id = ?",
new ParameterizedRowMapper<Spitter>() {
public Spitter mapRow(ResultSet rs, int rowNum) throws SQLException {
// 将查询结果映射到对象
Spitter spitter = new Spitter();
spitter.setId(rs.getLong(1));
spitter.setUsername(rs.getString(2));
spitter.setPassword(rs.getString(3));
spitter.setFullName(rs.getString(4));
return spitter;
}
}, 1 //绑定参数
);
System.out.println(spitter);
}
总是报如下错误:
信息: Rolled back transaction after test execution for test context [TestContext@39ad977d testClass = AbstractSpitterDaoTest, testInstance = com.zkzong.springinaction.spitter.persistence.AbstractSpitterDaoTest@6da00fb9, testMethod = shouldCreateRowsAndSetIds@AbstractSpitterDaoTest, testException = org.springframework.dao.DataIntegrityViolationException: StatementCallback; SQL [DELETE FROM spitter]; Cannot delete or update a parent row: a foreign key constraint fails (`spitter`.`spittle`, CONSTRAINT `spittle_ibfk_1` FOREIGN KEY (`spitter_id`) REFERENCES `spitter` (`id`)); nested exception is com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Cannot delete or update a parent row: a foreign key constraint fails (`spitter`.`spittle`, CONSTRAINT `spittle_ibfk_1` FOREIGN KEY (`spitter_id`) REFERENCES `spitter` (`id`)), mergedContextConfiguration = [MergedContextConfiguration@a202ccb testClass = AbstractSpitterDaoTest, locations = '{classpath:persistence-context.xml, classpath:test-dataSource-context.xml, classpath:test-transaction-context.xml}', classes = '{}', contextInitializerClasses = '[]', activeProfiles = '{}', contextLoader = 'org.springframework.test.context.support.DelegatingSmartContextLoader', parent = [null]]]
org.springframework.dao.DataIntegrityViolationException: StatementCallback; SQL [DELETE FROM spitter]; Cannot delete or update a parent row: a foreign key constraint fails (`spitter`.`spittle`, CONSTRAINT `spittle_ibfk_1` FOREIGN KEY (`spitter_id`) REFERENCES `spitter` (`id`)); nested exception is com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Cannot delete or update a parent row: a foreign key constraint fails (`spitter`.`spittle`, CONSTRAINT `spittle_ibfk_1` FOREIGN KEY (`spitter_id`) REFERENCES `spitter` (`id`))
at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(SQLErrorCodeSQLExceptionTranslator.java:249)
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:72)
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:407)
at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:521)
at org.springframework.jdbc.core.simple.SimpleJdbcTemplate.update(SimpleJdbcTemplate.java:251)
at org.springframework.test.jdbc.SimpleJdbcTestUtils.deleteFromTables(SimpleJdbcTestUtils.java:69)
at com.zkzong.springinaction.spitter.persistence.AbstractSpitterDaoTest.cleanup(AbstractSpitterDaoTest.java:46)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:50)
at org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:12)
at org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:47)
at org.junit.internal.runners.statements.RunAfters.evaluate(RunAfters.java:33)
at org.springframework.test.context.junit4.statements.RunAfterTestMethodCallbacks.evaluate(RunAfterTestMethodCallbacks.java:83)
at org.springframework.test.context.junit4.statements.SpringRepeat.evaluate(SpringRepeat.java:72)
at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.runChild(SpringJUnit4ClassRunner.java:231)
at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.runChild(SpringJUnit4ClassRunner.java:88)
at org.junit.runners.ParentRunner$3.run(ParentRunner.java:290)
at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:71)
at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:288)
at org.junit.runners.ParentRunner.access$000(ParentRunner.java:58)
at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:268)
at org.springframework.test.context.junit4.statements.RunBeforeTestClassCallbacks.evaluate(RunBeforeTestClassCallbacks.java:61)
at org.springframework.test.context.junit4.statements.RunAfterTestClassCallbacks.evaluate(RunAfterTestClassCallbacks.java:71)
at org.junit.runners.ParentRunner.run(ParentRunner.java:363)
at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.run(SpringJUnit4ClassRunner.java:174)
at org.junit.runner.JUnitCore.run(JUnitCore.java:137)
at com.intellij.junit4.JUnit4IdeaTestRunner.startRunnerWithArgs(JUnit4IdeaTestRunner.java:117)
at com.intellij.junit4.JUnit4IdeaTestRunner.startRunnerWithArgs(JUnit4IdeaTestRunner.java:42)
at com.intellij.rt.execution.junit.JUnitStarter.prepareStreamsAndStart(JUnitStarter.java:262)
at com.intellij.rt.execution.junit.JUnitStarter.main(JUnitStarter.java:84)
Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Cannot delete or update a parent row: a foreign key constraint fails (`spitter`.`spittle`, CONSTRAINT `spittle_ibfk_1` FOREIGN KEY (`spitter_id`) REFERENCES `spitter` (`id`))
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:404)
at com.mysql.jdbc.Util.getInstance(Util.java:387)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:934)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3966)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3902)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2526)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2673)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2545)
at com.mysql.jdbc.StatementImpl.executeUpdateInternal(StatementImpl.java:1540)
at com.mysql.jdbc.StatementImpl.executeLargeUpdate(StatementImpl.java:2595)
at com.mysql.jdbc.StatementImpl.executeUpdate(StatementImpl.java:1468)
at com.alibaba.druid.pool.DruidPooledStatement.executeUpdate(DruidPooledStatement.java:164)
at org.springframework.jdbc.core.JdbcTemplate$1UpdateStatementCallback.doInStatement(JdbcTemplate.java:511)
at org.springframework.jdbc.core.JdbcTemplate$1UpdateStatementCallback.doInStatement(JdbcTemplate.java:509)
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:396)
... 30 more
只是查询功能,为什么会提示:Cannot delete or update a parent row: a foreign key constraint fails (`spitter`.`spittle`, CONSTRAINT `spittle_ibfk_1` FOREIGN KEY (`spitter_id`) REFERENCES `spitter` (`id`));
建表语句如下:
CREATE TABLE spitter (
id INTEGER PRIMARY KEY auto_increment,
username VARCHAR(25) NOT NULL,
PASSWORD VARCHAR(25) NOT NULL,
fullname VARCHAR(100) NOT NULL,
email VARCHAR(50) NOT NULL,
update_by_email boolean NOT NULL
);
CREATE TABLE spittle (
id INTEGER PRIMARY KEY auto_increment,
spitter_id INTEGER NOT NULL,
spittleText VARCHAR(2000) NOT NULL,
postedTime date NOT NULL,
FOREIGN KEY (spitter_id) REFERENCES spitter (id)
);
insert into spitter (username, password, fullname, email, update_by_email) values ('habuma', 'password', 'Craig Walls', 'craig@habuma.com', false);
insert into spitter (username, password, fullname, email, update_by_email) values ('artnames', 'password', 'Art Names', 'artnames@habuma.com', false);
insert into spittle (spitter_id, spittleText, postedTime) values (1, 'Have you read Spring in Action 3? I hear it is awesome!', '2010-06-09');
insert into spittle (spitter_id, spittleText, postedTime) values (2, 'Trying out Spring''s new expression language.', '2010-06-11');
insert into spittle (spitter_id, spittleText, postedTime) values (1, 'Who''s going to SpringOne/2GX this year?', '2010-06-19');
数据库有外键约束