0
|
1 package nabble.model;
|
|
2
|
|
3 import java.sql.Connection;
|
|
4 import java.sql.PreparedStatement;
|
|
5 import java.sql.ResultSet;
|
|
6 import java.sql.SQLException;
|
|
7 import java.sql.Statement;
|
|
8 import java.sql.Types;
|
|
9 import java.util.ArrayList;
|
|
10 import java.util.List;
|
|
11 import fschmidt.db.postgres.DbDatabaseImpl;
|
|
12
|
|
13
|
|
14 final class TagImpl {
|
|
15
|
|
16 private TagImpl() {} // never
|
|
17
|
|
18 static void addTag(Site site,Node node,User user,String label) {
|
|
19 try {
|
|
20 Connection con = site.getDb().getConnection();
|
|
21 PreparedStatement stmt = con.prepareStatement(
|
|
22 "insert into tag (node_id,user_id,label) values (?,?,?)"
|
|
23 );
|
|
24 if( node == null ) {
|
|
25 stmt.setNull( 1, Types.INTEGER );
|
|
26 } else {
|
|
27 stmt.setLong( 1, node.getId() );
|
|
28 }
|
|
29 if( user == null ) {
|
|
30 stmt.setNull( 2, Types.INTEGER );
|
|
31 } else {
|
|
32 stmt.setLong( 2, user.getId() );
|
|
33 }
|
|
34 stmt.setString( 3, label );
|
|
35 DbDatabaseImpl.executeUpdateIgnoringDuplicateKeys(stmt);
|
|
36 stmt.close();
|
|
37 con.close();
|
|
38 } catch(SQLException e) {
|
|
39 throw new RuntimeException(e);
|
|
40 }
|
|
41 }
|
|
42
|
|
43 static int countTags(SiteKey siteKey,String sqlCondition) {
|
|
44 try {
|
|
45 Connection con = siteKey.getDb().getConnection();
|
|
46 try {
|
|
47 Statement stmt = con.createStatement();
|
|
48 ResultSet rs = stmt.executeQuery(
|
|
49 "select count(*) as n from tag where " + sqlCondition
|
|
50 );
|
|
51 rs.next();
|
|
52 int n = rs.getInt("n");
|
|
53 rs.close();
|
|
54 stmt.close();
|
|
55 return n;
|
|
56 } finally {
|
|
57 con.close();
|
|
58 }
|
|
59 } catch(SQLException e) {
|
|
60 throw new RuntimeException("sqlCondition = "+sqlCondition,e);
|
|
61 }
|
|
62 }
|
|
63
|
|
64 static List<String> findTagLabels(Site site,String sqlCondition) {
|
|
65 try {
|
|
66 List<String> list = new ArrayList<String>();
|
|
67 Connection con = site.getDb().getConnection();
|
|
68 Statement stmt = con.createStatement();
|
|
69 ResultSet rs = stmt.executeQuery(
|
|
70 "select distinct label from tag where " + sqlCondition
|
|
71 );
|
|
72 while( rs.next() ) {
|
|
73 list.add( rs.getString("label") );
|
|
74 }
|
|
75 rs.close();
|
|
76 stmt.close();
|
|
77 con.close();
|
|
78 return list;
|
|
79 } catch(SQLException e) {
|
|
80 throw new RuntimeException(e);
|
|
81 }
|
|
82 }
|
|
83
|
|
84 static List<Long> findTagUserIds(Site site,String sqlCondition) {
|
|
85 try {
|
|
86 List<Long> list = new ArrayList<Long>();
|
|
87 Connection con = site.getDb().getConnection();
|
|
88 Statement stmt = con.createStatement();
|
|
89 ResultSet rs = stmt.executeQuery(
|
|
90 "select distinct user_id from tag where " + sqlCondition
|
|
91 );
|
|
92 while( rs.next() ) {
|
|
93 list.add( rs.getLong("user_id") );
|
|
94 }
|
|
95 rs.close();
|
|
96 stmt.close();
|
|
97 con.close();
|
|
98 return list;
|
|
99 } catch(SQLException e) {
|
|
100 throw new RuntimeException(e);
|
|
101 }
|
|
102 }
|
|
103
|
|
104 static List<Long> findTagNodeIds(Site site,String sqlCondition) {
|
|
105 try {
|
|
106 List<Long> list = new ArrayList<Long>();
|
|
107 Connection con = site.getDb().getConnection();
|
|
108 Statement stmt = con.createStatement();
|
|
109 ResultSet rs = stmt.executeQuery(
|
|
110 "select distinct node_id from tag where " + sqlCondition
|
|
111 );
|
|
112 while( rs.next() ) {
|
|
113 list.add( rs.getLong("node_id") );
|
|
114 }
|
|
115 rs.close();
|
|
116 stmt.close();
|
|
117 con.close();
|
|
118 return list;
|
|
119 } catch(SQLException e) {
|
|
120 throw new RuntimeException(e);
|
|
121 }
|
|
122 }
|
|
123
|
|
124 static void deleteTags(SiteKey siteKey,String sqlCondition) {
|
|
125 try {
|
|
126 Connection con = siteKey.getDb().getConnection();
|
|
127 Statement stmt = con.createStatement();
|
|
128 stmt.executeUpdate(
|
|
129 "delete from tag where " + sqlCondition
|
|
130 );
|
|
131 stmt.close();
|
|
132 con.close();
|
|
133 } catch(SQLException e) {
|
|
134 throw new RuntimeException(e);
|
|
135 }
|
|
136 }
|
|
137
|
|
138 }
|