Skip to content

Commit 2b17cbe

Browse files
lexbatlexbat
committed
3 10 db implementation
1 parent 8edeaa0 commit 2b17cbe

5 files changed

Lines changed: 150 additions & 0 deletions

File tree

‎pom.xml‎

Lines changed: 5 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -87,6 +87,11 @@
8787
</exclusion>
8888
</exclusions>
8989
</dependency>
90+
<dependency>
91+
<groupId>org.springframework</groupId>
92+
<artifactId>spring-jdbc</artifactId>
93+
<version>${spring.version}</version>
94+
</dependency>
9095

9196
<!--DataBase-->
9297
<dependency>

‎src/main/java/ru/javawebinar/topjava/repository/jdbc/JdbcUserRepositoryImpl.java‎

Lines changed: 82 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,82 @@
1+
package ru.javawebinar.topjava.repository.jdbc;
2+
3+
import org.springframework.beans.factory.annotation.Autowired;
4+
import org.springframework.dao.support.DataAccessUtils;
5+
import org.springframework.jdbc.core.BeanPropertyRowMapper;
6+
import org.springframework.jdbc.core.JdbcTemplate;
7+
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
8+
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
9+
import org.springframework.jdbc.core.simple.SimpleJdbcInsert;
10+
import org.springframework.stereotype.Repository;
11+
import ru.javawebinar.topjava.model.User;
12+
import ru.javawebinar.topjava.repository.UserRepository;
13+
14+
import javax.sql.DataSource;
15+
import java.util.List;
16+
17+
@Repository
18+
public class JdbcUserRepositoryImpl implements UserRepository {
19+
20+
private static final BeanPropertyRowMapper<User> ROW_MAPPER = BeanPropertyRowMapper.newInstance(User.class);
21+
22+
private final JdbcTemplate jdbcTemplate;
23+
24+
private final NamedParameterJdbcTemplate namedParameterJdbcTemplate;
25+
26+
private final SimpleJdbcInsert insertUser;
27+
28+
@Autowired
29+
public JdbcUserRepositoryImpl(DataSource dataSource, JdbcTemplate jdbcTemplate, NamedParameterJdbcTemplate namedParameterJdbcTemplate) {
30+
this.insertUser = new SimpleJdbcInsert(dataSource)
31+
.withTableName("users")
32+
.usingGeneratedKeyColumns("id");
33+
34+
this.jdbcTemplate = jdbcTemplate;
35+
this.namedParameterJdbcTemplate = namedParameterJdbcTemplate;
36+
}
37+
38+
@Override
39+
public User save(User user) {
40+
MapSqlParameterSource map = new MapSqlParameterSource()
41+
.addValue("id", user.getId())
42+
.addValue("name", user.getName())
43+
.addValue("email", user.getEmail())
44+
.addValue("password", user.getPassword())
45+
.addValue("registered", user.getRegistered())
46+
.addValue("enabled", user.isEnabled())
47+
.addValue("caloriesPerDay", user.getCaloriesPerDay());
48+
49+
if (user.isNew()) {
50+
Number newKey = insertUser.executeAndReturnKey(map);
51+
user.setId(newKey.intValue());
52+
} else {
53+
namedParameterJdbcTemplate.update(
54+
"UPDATE users SET name=:name, email=:email, password=:password, " +
55+
"registered=:registered, enabled=:enabled, calories_per_day=:caloriesPerDay WHERE id=:id", map);
56+
}
57+
return user;
58+
}
59+
60+
@Override
61+
public boolean delete(int id) {
62+
return jdbcTemplate.update("DELETE FROM users WHERE id=?", id) != 0;
63+
}
64+
65+
@Override
66+
public User get(int id) {
67+
List<User> users = jdbcTemplate.query("SELECT * FROM users WHERE id=?", ROW_MAPPER, id);
68+
return DataAccessUtils.singleResult(users);
69+
}
70+
71+
@Override
72+
public User getByEmail(String email) {
73+
// return jdbcTemplate.queryForObject("SELECT * FROM users WHERE email=?", ROW_MAPPER, email);
74+
List<User> users = jdbcTemplate.query("SELECT * FROM users WHERE email=?", ROW_MAPPER, email);
75+
return DataAccessUtils.singleResult(users);
76+
}
77+
78+
@Override
79+
public List<User> getAll() {
80+
return jdbcTemplate.query("SELECT * FROM users ORDER BY name, email", ROW_MAPPER);
81+
}
82+
}

‎src/main/resources/db/initDB.sql‎

Lines changed: 25 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,25 @@
1+
DROP TABLE IF EXISTS user_roles;
2+
DROP TABLE IF EXISTS users;
3+
DROP SEQUENCE IF EXISTS global_seq;
4+
5+
CREATE SEQUENCE global_seq START 100000;
6+
7+
CREATE TABLE users
8+
(
9+
id INTEGER PRIMARY KEY DEFAULT nextval('global_seq'),
10+
name VARCHAR NOT NULL,
11+
email VARCHAR NOT NULL,
12+
password VARCHAR NOT NULL,
13+
registered TIMESTAMP DEFAULT now(),
14+
enabled BOOL DEFAULT TRUE,
15+
calories_per_day INTEGER DEFAULT 2000 NOT NULL
16+
);
17+
CREATE UNIQUE INDEX users_unique_email_idx ON users (email);
18+
19+
CREATE TABLE user_roles
20+
(
21+
user_id INTEGER NOT NULL,
22+
role VARCHAR,
23+
CONSTRAINT user_roles_idx UNIQUE (user_id, role),
24+
FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE
25+
);

‎src/main/resources/db/populateDB.sql‎

Lines changed: 13 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,13 @@
1+
DELETE FROM user_roles;
2+
DELETE FROM users;
3+
ALTER SEQUENCE global_seq RESTART WITH 100000;
4+
5+
INSERT INTO users (name, email, password)
6+
VALUES ('User', 'user@yandex.ru', 'password');
7+
8+
INSERT INTO users (name, email, password)
9+
VALUES ('Admin', 'admin@gmail.com', 'admin');
10+
11+
INSERT INTO user_roles (role, user_id) VALUES
12+
('ROLE_USER', 100000),
13+
('ROLE_ADMIN', 100001);

‎src/main/resources/spring/spring-db.xml‎

Lines changed: 25 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,25 @@
1+
<beans xmlns="http://www.springframework.org/schema/beans"
2+
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
3+
xmlns:context="http://www.springframework.org/schema/context"
4+
xsi:schemaLocation="http://www.springframework.org/schema/beans
5+
http://www.springframework.org/schema/beans/spring-beans.xsd http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context.xsd">
6+
7+
<context:property-placeholder location="classpath:db/postgres.properties" system-properties-mode="OVERRIDE"/>
8+
9+
<!--no pooling-->
10+
<bean id="dataSource"
11+
class="org.springframework.jdbc.datasource.DriverManagerDataSource">
12+
<property name="driverClassName" value="org.postgresql.Driver"/>
13+
<property name="url" value="${database.url}"/>
14+
<property name="username" value="${database.username}"/>
15+
<property name="password" value="${database.password}"/>
16+
</bean>
17+
18+
<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
19+
<constructor-arg ref="dataSource"/>
20+
</bean>
21+
22+
<bean id="namedJdbcTemplate" class="org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate">
23+
<constructor-arg ref="dataSource"/>
24+
</bean>
25+
</beans>

0 commit comments

Comments
 (0)