· 6 years ago · Aug 25, 2019, 05:30 AM
1
2import java.io.File;
3import java.sql.Connection;
4import java.sql.DriverManager;
5import java.sql.PreparedStatement;
6import java.sql.ResultSet;
7import java.sql.SQLException;
8import java.sql.Statement;
9import java.util.LinkedList;
10
11public class DataBase {
12 private static final String EXDB_ADRESS = "jdbc:sqlite:db.db";
13
14 private int mNextGid;
15 private int mNextTid;
16
17 public DataBase() {
18 super();
19
20 System.out.println("in");
21
22 initTables();
23 initNextIdNumber();
24
25 }
26
27 public void insertGallery(LinkedList<Gallery> records) throws SQLException {
28 String sql = "INSERT INTO gallerys(name, category, gid) VALUES(?, ?, ?);";
29 String sql2 = "INSERT INTO tags(tagName, tid) VALUES(?, ?);";
30 String sql3 = "INSERT INTO relation(gid, tid) VALUES(?, ?);";
31
32 Connection conn = getConnection();
33 PreparedStatement stmt = conn.prepareStatement(sql);
34 PreparedStatement stmt2 = conn.prepareStatement(sql2);
35 PreparedStatement stmt3 = conn.prepareStatement(sql3);
36
37 while (!records.isEmpty()) {
38
39 Gallery record = records.poll();
40 stmt.setString(1, record.getTitle());
41 stmt.setString(2, record.getCategory());
42 stmt.setInt(3, mNextGid);
43 stmt.execute();
44
45 LinkedList<String> tags = record.getTags();
46
47 String tag = null;
48 while ((tag = tags.poll()) != null) {
49 // System.out.println("Tags: " + tag.toString());
50 String sql4 = "SELECT tid FROM tags WHERE tagName='" + tag + "'";
51 ResultSet rs = getResultSet(sql4);
52 int tid;
53 if (rs.next())
54 tid = rs.getInt(1);
55 else
56 tid = mNextGid++;
57
58 stmt2.setString(1, tag);
59 stmt2.setInt(2, tid);
60 stmt2.execute();
61
62 stmt3.setInt(1, mNextGid);
63 stmt3.setInt(2, tid++);
64 stmt3.addBatch();
65 stmt3.executeBatch();
66 }
67
68 mNextGid++;
69 }
70
71 }
72
73 public void insert(LinkedList<Gallery> records) throws SQLException {
74 String sql = "INSERT INTO gallerys(name, category, gid) VALUES(?, ?, ?);";
75
76 Connection conn = getConnection();
77
78 PreparedStatement stmt = conn.prepareStatement(sql);
79
80 while (!records.isEmpty()) {
81
82 Gallery record = records.poll();
83 System.out.println(record.toString());
84 stmt.setString(1, record.getTitle());
85 stmt.setString(2, record.getCategory());
86 stmt.setInt(3, mNextGid);
87 stmt.addBatch();
88
89 }
90 stmt.addBatch();
91
92 }
93
94 private void initNextIdNumber() {
95 // TODO Auto-generated method stub
96
97 try {
98 String sql = "SELECT MAX(gid) FROM gallerys;";
99 ResultSet rs = getResultSet(sql);
100 mNextGid = rs.getInt(1);
101 } catch (SQLException e) {
102 // TODO Auto-generated catch block
103 mNextGid = 0;
104 }
105 try {
106 String sql = "SELECT MAX(tid) FROM gallerys;";
107 ResultSet rs = getResultSet(sql);
108 mNextTid = rs.getInt(1);
109 } catch (SQLException e) {
110 // TODO Auto-generated catch block
111 mNextTid = 0;
112 }
113
114 System.out.println("mNextGid: " + mNextGid);
115 System.out.println("mNextTid: " + mNextTid);
116
117 }
118
119 private ResultSet getResultSet(String sql) throws SQLException {
120 // System.out.println("Getting Results");
121 Connection conn = getConnection();
122
123 ResultSet rs = conn.createStatement().executeQuery(sql);
124 return rs;
125
126 }
127
128 private Connection getConnection() {
129 try {
130 return DriverManager.getConnection(EXDB_ADRESS);
131 } catch (SQLException e) {
132 System.out.println("DataBase connection fail!!");
133 return null;
134 }
135 }
136
137 private void initTables() {
138 creatGalleryTable();
139 creatTagsTable();
140 creatRelationsTable();
141 }
142
143 private void creatGalleryTable() {
144
145 String sql = " CREATE TABLE IF NOT EXISTS gallerys(name TEXT,category TEXT,gid INT);";
146 createTable("gallerys", sql);
147 }
148
149 private void creatRelationsTable() {
150
151 String sql = " CREATE TABLE IF NOT EXISTS relation(gid INT,tid INT);";
152 createTable("nodes", sql);
153 }
154
155 private void creatTagsTable() {
156 String sql = " CREATE TABLE IF NOT EXISTS tags(tagName TEXT, tid INT);";
157 createTable("nodes", sql);
158
159 }
160
161 private void createTable(String name, String sql) {
162 try {
163
164 Connection conn = getConnection();
165 Statement stmt = conn.createStatement();
166 stmt.execute(sql);
167
168 } catch (SQLException e) {
169 // TODO Auto-generated catch block
170 e.printStackTrace();
171 }
172
173 }
174
175}