Skip to content

Commit f7c8ed4

Browse files
codedatacodedata
committed
added exercise12
1 parent 9f27c35 commit f7c8ed4

24 files changed

Lines changed: 671 additions & 0 deletions

File tree

‎exercises/exercise12/DVDLibrary/build.gradle‎

Lines changed: 30 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,30 @@
1+
apply plugin: 'tomcat'
2+
3+
buildscript {
4+
repositories {
5+
jcenter()
6+
}
7+
8+
dependencies {
9+
classpath 'org.gradle.api.plugins:gradle-tomcat-plugin:1.0'
10+
}
11+
}
12+
13+
repositories {
14+
mavenCentral()
15+
}
16+
17+
dependencies {
18+
providedCompile 'javax.servlet:javax.servlet-api:3.1.0'
19+
compile 'org.springframework:spring-webmvc:4.0.0.RELEASE'
20+
compile 'org.springframework:spring-jdbc:4.0.0.RELEASE'
21+
compile 'org.hsqldb:hsqldb:2.3.1'
22+
23+
24+
def tomcatVersion = '7.0.11'
25+
tomcat "org.apache.tomcat.embed:tomcat-embed-core:${tomcatVersion}",
26+
"org.apache.tomcat.embed:tomcat-embed-logging-juli:${tomcatVersion}"
27+
tomcat("org.apache.tomcat.embed:tomcat-embed-jasper:${tomcatVersion}") {
28+
exclude group: 'org.eclipse.jdt.core.compiler', module: 'ecj'
29+
}
30+
}

‎exercises/exercise12/DVDLibrary/src/main/java/tw/codedata/Dvd.java‎

Lines changed: 33 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,33 @@
1+
package tw.codedata;
2+
3+
public class Dvd {
4+
private String title;
5+
private Integer year;
6+
private Integer duration;
7+
private String director;
8+
9+
public Dvd(String title, Integer year, Integer duration, String director) {
10+
this.title = title;
11+
this.year = year;
12+
this.duration = duration;
13+
this.director = director;
14+
}
15+
16+
public String getTitle() {
17+
return title;
18+
}
19+
20+
public Integer getYear() {
21+
return year;
22+
}
23+
24+
public Integer getDuration() {
25+
return duration;
26+
}
27+
28+
public String getDirector() {
29+
return director;
30+
}
31+
32+
33+
}

‎exercises/exercise12/DVDLibrary/src/main/java/tw/codedata/DvdDao.java‎

Lines changed: 8 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,8 @@
1+
package tw.codedata;
2+
3+
import java.util.List;
4+
5+
public interface DvdDao {
6+
void saveDvd(Dvd dvd);
7+
List<Dvd> allDvds();
8+
}

‎exercises/exercise12/DVDLibrary/src/main/java/tw/codedata/DvdDaoJdbcImpl.java‎

Lines changed: 92 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,92 @@
1+
package tw.codedata;
2+
3+
import org.springframework.jdbc.core.support.JdbcDaoSupport;
4+
import org.springframework.jdbc.core.RowMapper;
5+
import org.springframework.jdbc.core.JdbcTemplate;
6+
import javax.sql.DataSource;
7+
import java.sql.*;
8+
import java.util.*;
9+
import org.springframework.beans.factory.annotation.Autowired;
10+
import org.springframework.stereotype.Service;
11+
12+
@Service
13+
public class DvdDaoJdbcImpl extends JdbcDaoSupport implements DvdDao {
14+
15+
@Autowired
16+
public DvdDaoJdbcImpl(DataSource dataSource) {
17+
setDataSource(dataSource);
18+
}
19+
20+
public DvdDaoJdbcImpl createTables() {
21+
JdbcTemplate jdbcTemplate = getJdbcTemplate();
22+
23+
jdbcTemplate.execute("DROP TABLE dvds if exists");
24+
jdbcTemplate.execute("DROP TABLE directors if exists");
25+
26+
jdbcTemplate.execute(
27+
"CREATE TABLE directors ("
28+
+ "id INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH 1),"
29+
+ "name VARCHAR(255),"
30+
+ "PRIMARY KEY(id))"
31+
);
32+
jdbcTemplate.execute(
33+
"CREATE TABLE dvds ("
34+
+ "id INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH 1),"
35+
+ "title VARCHAR(255),"
36+
+ "year INTEGER NOT NULL,"
37+
+ "duration INTEGER NOT NULL, "
38+
+ "director_id INTEGER NOT NULL, "
39+
+ "FOREIGN KEY (director_id) REFERENCES directors(id))"
40+
);
41+
42+
return this;
43+
}
44+
45+
public void saveDvd(Dvd dvd) {
46+
Integer directorId = getDirectorId(dvd.getDirector());
47+
getJdbcTemplate().update(
48+
"INSERT INTO dvds "
49+
+ "(title, year, duration, director_id) "
50+
+ "VALUES (?, ?, ?, ?)",
51+
dvd.getTitle(), dvd.getYear(), dvd.getDuration(), directorId);
52+
}
53+
54+
public List<Dvd> allDvds() {
55+
return getJdbcTemplate().query(
56+
"SELECT dvds.title, dvds.year, dvds.duration, "
57+
+ "directors.name FROM dvds, directors "
58+
+ "WHERE dvds.director_id = directors.id"
59+
+ " ORDER BY dvds.title",
60+
new RowMapper<Dvd>() {
61+
@Override
62+
public Dvd mapRow(ResultSet rs, int rowNum) throws SQLException {
63+
return new Dvd(
64+
rs.getString("title"),
65+
rs.getInt("year"),
66+
rs.getInt("duration"),
67+
rs.getString("name")
68+
);
69+
}
70+
});
71+
}
72+
73+
private Integer getDirectorId(String director) {
74+
if (hasNoDirector(director)) {
75+
saveDirector(director);
76+
}
77+
List ids = getJdbcTemplate().queryForList(
78+
"SELECT id FROM directors WHERE name=?", director);
79+
Map row = (Map) ids.get(0);
80+
return (Integer) row.get("id");
81+
}
82+
83+
private boolean hasNoDirector(String director) {
84+
return getJdbcTemplate().queryForObject(
85+
"SELECT COUNT(*) FROM directors WHERE name=?", new Object[] {director}, Integer.class) == 0;
86+
}
87+
88+
private void saveDirector(String director) {
89+
getJdbcTemplate().update(
90+
"INSERT INTO directors(name) values(?)", director);
91+
}
92+
}

‎exercises/exercise12/DVDLibrary/src/main/resources/db/dvd_library.properties‎

Lines changed: 4 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,4 @@
1+
#HSQL Database Engine 2.3.0
2+
#Tue Dec 17 11:44:04 CST 2013
3+
version=2.3.0
4+
modified=yes

‎exercises/exercise12/DVDLibrary/src/main/resources/db/dvd_library.script‎

Lines changed: 54 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,54 @@
1+
SET DATABASE UNIQUE NAME HSQLDB428E459364
2+
SET DATABASE GC 0
3+
SET DATABASE DEFAULT RESULT MEMORY ROWS 0
4+
SET DATABASE EVENT LOG LEVEL 0
5+
SET DATABASE SQL NAMES FALSE
6+
SET DATABASE SQL REFERENCES FALSE
7+
SET DATABASE SQL SIZE TRUE
8+
SET DATABASE SQL TYPES FALSE
9+
SET DATABASE SQL TDC DELETE TRUE
10+
SET DATABASE SQL TDC UPDATE TRUE
11+
SET DATABASE SQL TRANSLATE TTI TYPES TRUE
12+
SET DATABASE SQL CONCAT NULLS TRUE
13+
SET DATABASE SQL UNIQUE NULLS TRUE
14+
SET DATABASE SQL CONVERT TRUNCATE TRUE
15+
SET DATABASE SQL AVG SCALE 0
16+
SET DATABASE SQL DOUBLE NAN TRUE
17+
SET DATABASE TRANSACTION CONTROL LOCKS
18+
SET DATABASE DEFAULT ISOLATION LEVEL READ COMMITTED
19+
SET DATABASE TRANSACTION ROLLBACK ON CONFLICT TRUE
20+
SET DATABASE TEXT TABLE DEFAULTS ''
21+
SET FILES WRITE DELAY 500 MILLIS
22+
SET FILES BACKUP INCREMENT TRUE
23+
SET FILES CACHE SIZE 10000
24+
SET FILES CACHE ROWS 50000
25+
SET FILES SCALE 32
26+
SET FILES LOB SCALE 32
27+
SET FILES DEFRAG 0
28+
SET FILES NIO TRUE
29+
SET FILES NIO SIZE 256
30+
SET FILES LOG TRUE
31+
SET FILES LOG SIZE 50
32+
CREATE USER "codedata" PASSWORD DIGEST 'e10adc3949ba59abbe56e057f20f883e'
33+
ALTER USER "codedata" SET LOCAL TRUE
34+
CREATE SCHEMA PUBLIC AUTHORIZATION DBA
35+
SET SCHEMA PUBLIC
36+
CREATE MEMORY TABLE PUBLIC.DIRECTORS(ID INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH 1) NOT NULL PRIMARY KEY,NAME VARCHAR(255))
37+
ALTER TABLE PUBLIC.DIRECTORS ALTER COLUMN ID RESTART WITH 2
38+
CREATE MEMORY TABLE PUBLIC.DVDS(ID INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH 1) NOT NULL,TITLE VARCHAR(255),YEAR INTEGER NOT NULL,DURATION INTEGER NOT NULL,DIRECTOR_ID INTEGER NOT NULL,FOREIGN KEY(DIRECTOR_ID) REFERENCES PUBLIC.DIRECTORS(ID))
39+
ALTER TABLE PUBLIC.DVDS ALTER COLUMN ID RESTART WITH 4
40+
ALTER SEQUENCE SYSTEM_LOBS.LOB_ID RESTART WITH 1
41+
SET DATABASE DEFAULT INITIAL SCHEMA PUBLIC
42+
GRANT USAGE ON DOMAIN INFORMATION_SCHEMA.SQL_IDENTIFIER TO PUBLIC
43+
GRANT USAGE ON DOMAIN INFORMATION_SCHEMA.YES_OR_NO TO PUBLIC
44+
GRANT USAGE ON DOMAIN INFORMATION_SCHEMA.TIME_STAMP TO PUBLIC
45+
GRANT USAGE ON DOMAIN INFORMATION_SCHEMA.CARDINAL_NUMBER TO PUBLIC
46+
GRANT USAGE ON DOMAIN INFORMATION_SCHEMA.CHARACTER_DATA TO PUBLIC
47+
GRANT DBA TO "codedata"
48+
SET SCHEMA SYSTEM_LOBS
49+
INSERT INTO BLOCKS VALUES(0,2147483647,0)
50+
SET SCHEMA PUBLIC
51+
INSERT INTO DIRECTORS VALUES(1,'test')
52+
INSERT INTO DVDS VALUES(1,'test1',1234,1,1)
53+
INSERT INTO DVDS VALUES(2,'test2',2001,2,1)
54+
INSERT INTO DVDS VALUES(3,'test3',2005,3,1)

‎exercises/exercise12/DVDLibrary/src/main/webapp/WEB-INF/dispatcher-servlet.xml‎

Lines changed: 18 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,18 @@
1+
<?xml version="1.0" encoding="UTF-8"?>
2+
<beans xmlns="http://www.springframework.org/schema/beans"
3+
xmlns:mvc="http://www.springframework.org/schema/mvc"
4+
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
5+
xmlns:p="http://www.springframework.org/schema/p"
6+
xmlns:context="http://www.springframework.org/schema/context"
7+
xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-4.0.xsd http://www.springframework.org/schema/mvc http://www.springframework.org/schema/mvc/spring-mvc-4.0.xsd http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-4.0.xsd">
8+
9+
<context:component-scan base-package="tw.codedata" />
10+
<mvc:annotation-driven />
11+
12+
<bean class="org.springframework.web.servlet.view.InternalResourceViewResolver"
13+
p:prefix="/"
14+
p:suffix=".jsp"/>
15+
16+
17+
18+
</beans>

‎exercises/exercise12/DVDLibrary/src/main/webapp/WEB-INF/web.xml‎

Lines changed: 14 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,14 @@
1+
<?xml version="1.0" encoding="UTF-8"?>
2+
<web-app version="3.0" xmlns="http://java.sun.com/xml/ns/javaee"
3+
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
4+
xsi:schemaLocation="http://java.sun.com/xml/ns/javaee
5+
http://java.sun.com/xml/ns/javaee/web-app_3_0.xsd">
6+
<servlet>
7+
<servlet-name>dispatcher</servlet-name>
8+
<servlet-class>org.springframework.web.servlet.DispatcherServlet</servlet-class>
9+
</servlet>
10+
<servlet-mapping>
11+
<servlet-name>dispatcher</servlet-name>
12+
<url-pattern>/</url-pattern>
13+
</servlet-mapping>
14+
</web-app>

‎exercises/exercise12/DVDLibrary/src/main/webapp/add.jsp‎

Lines changed: 18 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,18 @@
1+
<%@page contentType="text/html" pageEncoding="UTF-8"%>
2+
<!DOCTYPE html>
3+
<html>
4+
<head>
5+
<title>Add a Dvd</title>
6+
<meta charset="UTF-8">
7+
<meta name="viewport" content="width=device-width">
8+
</head>
9+
<body>
10+
<form action="add" method="post">
11+
Title: <input type="text" name="title"/></br>
12+
Year: <input type="text" name="year"/></br>
13+
Duration: <input type="text" name="duration"/></br>
14+
Director: <input type="text" name="director"/></br>
15+
<input type="submit"/>
16+
</form>
17+
</body>
18+
</html>

‎exercises/exercise12/DVDLibrary/src/main/webapp/index.jsp‎

Lines changed: 15 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,15 @@
1+
<%@page contentType="text/html" pageEncoding="UTF-8"%>
2+
<!DOCTYPE html>
3+
<html>
4+
<head>
5+
<title>Dvd Library</title>
6+
<meta charset="UTF-8">
7+
<meta name="viewport" content="width=device-width">
8+
</head>
9+
<body>
10+
<ul>
11+
<li><a href="list">List Dvds</a></li>
12+
<li><a href="add.jsp">Add a Dvd</a></li>
13+
</ul>
14+
</body>
15+
</html>

0 commit comments

Comments
 (0)