45 |
45 |
46 private final PreparedStatement insert, update, list, find; |
46 private final PreparedStatement insert, update, list, find; |
47 |
47 |
48 public PGVersionDao(Connection connection) throws SQLException { |
48 public PGVersionDao(Connection connection) throws SQLException { |
49 list = connection.prepareStatement( |
49 list = connection.prepareStatement( |
50 "select versionid, project, p.name as projectname, v.name, ordinal, status " + |
50 "select versionid, project, name, ordinal, status " + |
51 "from lpit_version v " + |
51 "from lpit_version " + |
52 "join lpit_project p on v.project = p.projectid " + |
|
53 "where project = ? " + |
52 "where project = ? " + |
54 "order by ordinal desc, lower(v.name) desc"); |
53 "order by ordinal desc, lower(name) desc"); |
55 |
54 |
56 find = connection.prepareStatement( |
55 find = connection.prepareStatement( |
57 "select versionid, project, p.name as projectname, v.name, ordinal, status " + |
56 "select versionid, project, name, ordinal, status " + |
58 "from lpit_version v " + |
57 "from lpit_version " + |
59 "join lpit_project p on v.project = p.projectid " + |
|
60 "where versionid = ?"); |
58 "where versionid = ?"); |
61 |
59 |
62 insert = connection.prepareStatement( |
60 insert = connection.prepareStatement( |
63 "insert into lpit_version (project, name, ordinal, status) values (?, ?, ?, ?::version_status)" |
61 "insert into lpit_version (project, name, ordinal, status) values (?, ?, ?, ?::version_status)" |
64 ); |
62 ); |
66 "update lpit_version set name = ?, ordinal = ?, status = ?::version_status where versionid = ?" |
64 "update lpit_version set name = ?, ordinal = ?, status = ?::version_status where versionid = ?" |
67 ); |
65 ); |
68 } |
66 } |
69 |
67 |
70 private Version mapColumns(ResultSet result) throws SQLException { |
68 private Version mapColumns(ResultSet result) throws SQLException { |
71 final var project = new Project(result.getInt("project")); |
|
72 project.setName(result.getString("projectname")); |
|
73 final var version = new Version(result.getInt("versionid")); |
69 final var version = new Version(result.getInt("versionid")); |
74 version.setProject(project); |
|
75 version.setName(result.getString("name")); |
70 version.setName(result.getString("name")); |
76 version.setOrdinal(result.getInt("ordinal")); |
71 version.setOrdinal(result.getInt("ordinal")); |
77 version.setStatus(VersionStatus.valueOf(result.getString("status"))); |
72 version.setStatus(VersionStatus.valueOf(result.getString("status"))); |
78 return version; |
73 return version; |
79 } |
74 } |
80 |
75 |
81 @Override |
76 @Override |
82 public void save(Version instance) throws SQLException { |
77 public void save(Version instance, Project project) throws SQLException { |
83 Objects.requireNonNull(instance.getName()); |
78 Objects.requireNonNull(instance.getName()); |
84 Objects.requireNonNull(instance.getProject()); |
79 insert.setInt(1, project.getId()); |
85 insert.setInt(1, instance.getProject().getId()); |
|
86 insert.setString(2, instance.getName()); |
80 insert.setString(2, instance.getName()); |
87 insert.setInt(3, instance.getOrdinal()); |
81 insert.setInt(3, instance.getOrdinal()); |
88 insert.setString(4, instance.getStatus().name()); |
82 insert.setString(4, instance.getStatus().name()); |
89 insert.executeUpdate(); |
83 insert.executeUpdate(); |
90 } |
84 } |
104 public List<Version> list(Project project) throws SQLException { |
98 public List<Version> list(Project project) throws SQLException { |
105 list.setInt(1, project.getId()); |
99 list.setInt(1, project.getId()); |
106 List<Version> versions = new ArrayList<>(); |
100 List<Version> versions = new ArrayList<>(); |
107 try (var result = list.executeQuery()) { |
101 try (var result = list.executeQuery()) { |
108 while (result.next()) { |
102 while (result.next()) { |
109 final var v = mapColumns(result); |
103 versions.add(mapColumns(result)); |
110 v.setProject(project); |
|
111 versions.add(v); |
|
112 } |
104 } |
113 } |
105 } |
114 return versions; |
106 return versions; |
115 } |
107 } |
116 |
108 |