· 8 years ago · Mar 08, 2018, 05:40 PM
1package postgres;
2
3import java.sql.Connection;
4import java.sql.DriverManager;
5import java.sql.PreparedStatement;
6import java.sql.ResultSet;
7import java.sql.ResultSetMetaData;
8import java.sql.SQLException;
9import java.sql.Statement;
10
11import org.postgresql.util.PGobject;
12
13import com.google.gson.JsonObject;
14import com.google.gson.JsonParser;
15
16/**
17 * Program inserting and querying JSON objects using PostgreSQL.
18 */
19public class QueryPostgres
20{
21 /**
22 * Connection to database
23 */
24 private Connection con;
25
26 /**
27 * Table name
28 */
29 public static final String TABLE_NAME = "data";
30
31 /**
32 * Main method
33 *
34 * @param args
35 * no arguments required
36 */
37 public static void main(String [] args) throws SQLException
38 {
39 QueryPostgres qpostgres = new QueryPostgres();
40 qpostgres.connect();
41 qpostgres.load();
42 System.out.println(QueryPostgres.resultSetToString(qpostgres.query(), 100));
43 qpostgres.update(3);
44 System.out.println(QueryPostgres.resultSetToString(qpostgres.query(), 100));
45 System.out.println(QueryPostgres.resultSetToString(qpostgres.query1(), 100));
46 System.out.println(QueryPostgres.resultSetToString(qpostgres.query2(), 100));
47 }
48
49 /**
50 * Connects to Postgres database and returns connection.
51 *
52 * @return
53 * connection
54 */
55 public Connection connect() throws SQLException
56 {
57 // TODO: Modify this URL and user id and password for your own database
58 String url = "jdbc:postgresql://cosc304.ok.ubc.ca/db_jcampbel";
59 String uid = "jcampbel";
60 String pw = "27756162";
61
62 System.out.println("Connecting to database.");
63 con = DriverManager.getConnection(url, uid, pw);
64 return con;
65 }
66
67 /**
68 * Loads some sample JSON data into Postgres.
69 */
70 public void load() throws SQLException
71 {
72 // Drop table if it exists
73 Statement stmt = con.createStatement();
74 try
75 {
76 stmt.executeUpdate("DROP TABLE IF EXISTS "+TABLE_NAME);
77 }
78 catch (SQLException e)
79 { // Ignore any exception with DROP
80 }
81
82 // TODO: Create a table called "data"
83 // Table should have field "id" as a serial primary key and field "text" as a json field
84 String sqlc = "create table data (id serial NOT NULL PRIMARY KEY, text json NOT NULL)";
85
86 // TODO: Add 5 objects to table of the form: key, name, num, values
87 // - where key is an increasing integer starting at 1 (i.e. 1, 2, 3, ...)
88 // - name is "text"+key (e.g. "text1")
89 // - num is key (e.g. 1)
90 // - values is an array of 3 objects of the form: {"val":1, "text":"text1"}, {"val":2, "text":"text2"}, {"val":3, "text":"text3"}
91 // - The example is above for key = 1, for key = 2 the values should be 2,3,4, etc.
92 // Note: Use PreparedStatements!
93 PreparedStatement pstmt = con.prepareStatement(sqlc);
94 pstmt.execute();
95 String sqli = "INSERT INTO data (text) VALUES(?)";
96 pstmt = con.prepareStatement(sqli);
97
98 for(int i = 1; i <= 5; i++) {
99 String buf = "'{ \"key\": \""+String.valueOf(i)+"\", \"text\": \"text"+String.valueOf(i)+"\", \"num\": \""+String.valueOf(i)+"\", \"values\": {\"val\": \""+String.valueOf(i)+"\",\"text\": \"text"+String.valueOf(i)+"\"}}'";
100 PGobject jsonObject = new PGobject();
101 jsonObject.setType("json");
102 jsonObject.setValue(buf.toString());
103 pstmt.setObject(1, jsonObject);
104 pstmt.execute();
105 }
106
107 /* Note: This code is useful in PreparedStatements to set a JSON value
108 String buf = "'{ \"key\": \"\", \"text\": \"text?\", \"num\": \"?\", \"values\": {\"val\": \"?\",\"text\": \"text?\"}}'";
109 PGobject jsonObject = new PGobject();
110 jsonObject.setType("json");
111 jsonObject.setValue(buf.toString());
112 pstmt.setObject(1, jsonObject); */
113
114
115 }
116
117 /**
118 * Updates a record with given key so that the key is 10 times bigger. The name field should also be updated with the new key value (e.g. text10).
119 */
120 public void update(int key) throws SQLException
121 {
122 // Note: No support for UPDATE on json fields in Postgres 9.4 so will resort to downloading object to Java program, updating it, then saving it back.
123 // Alternative is to write a stored procedure which we will avoid.
124
125 // TODO: Retrieve JSON object from database given key
126
127 // Retrieve key and json text
128
129 // TODO: Use Google Gson library to convert string into a JsonObject
130 // JsonObject jsonobj = new JsonParser().parse(jsontext).getAsJsonObject();
131
132 // TODO: Modify JsonObject
133
134 // TODO: Update JSON object in database
135
136 }
137
138 /**
139 * Performs a query that prints out all data.
140 */
141 public ResultSet query() throws SQLException
142 {
143 Statement stmt = con.createStatement();
144 return stmt.executeQuery("SELECT * FROM "+TABLE_NAME);
145 }
146
147 /**
148 * Performs a query that returns all documents with key < 4. Only show the key, name, and num fields. Name the output field as "output".
149 */
150 public ResultSet query1() throws SQLException
151 {
152 // TODO: Write a query that returns all documents with key < 4. Only show the key, name, and num fields.
153 // See: http://www.postgresql.org/docs/9.4/static/functions-json.html
154 // See: http://www.postgresql.org/docs/9.4/static/datatype-json.html
155 // Note: You will need to use cast() to convert 'num' field to an int to do the comparison.
156 // Note: You may need to use json_build_object().
157 return null;
158 }
159
160 /**
161 * Performs a query that returns all documents with key > 2 OR contains an element in the array with val = 4.
162 */
163 public ResultSet query2() throws SQLException
164 {
165 // TODO: Write a query that returns all documents with key > 2 OR contains an element in the array with val = 4.
166 // See: http://www.postgresql.org/docs/9.4/static/functions-json.html
167 // See: http://www.postgresql.org/docs/9.4/static/datatype-json.html
168 // Note: You will need to use cast() to convert 'key' field to an int to do the comparison.
169 // Note: Getting the values out of the JSON array values may require an SQL subquery and the method json_array_elements().
170
171 return null;
172 }
173
174
175 /*
176 * Do not change anything below here.
177 */
178 /**
179 * Converts a ResultSet to a string with a given number of rows displayed.
180 * Total rows are determined but only the first few are put into a string.
181 *
182 * @param rst
183 * ResultSet
184 * @param maxrows
185 * maximum number of rows to display
186 * @return
187 * String form of results
188 * @throws SQLException
189 * if a database error occurs
190 */
191 public static String resultSetToString(ResultSet rst, int maxrows) throws SQLException
192 {
193 StringBuffer buf = new StringBuffer(5000);
194 int rowCount = 0;
195 if (rst != null)
196 {
197 ResultSetMetaData meta = rst.getMetaData();
198 buf.append("Total columns: " + meta.getColumnCount());
199 buf.append('\n');
200 if (meta.getColumnCount() > 0)
201 buf.append(meta.getColumnName(1));
202 for (int j = 2; j <= meta.getColumnCount(); j++)
203 buf.append(", " + meta.getColumnName(j));
204 buf.append('\n');
205
206 while (rst.next())
207 {
208 if (rowCount < maxrows)
209 {
210 for (int j = 0; j < meta.getColumnCount(); j++)
211 {
212 Object obj = rst.getObject(j + 1);
213 buf.append(obj);
214 if (j != meta.getColumnCount() - 1)
215 buf.append(", ");
216 }
217 buf.append('\n');
218 }
219 rowCount++;
220 }
221 }
222 buf.append("Total results: " + rowCount);
223 return buf.toString();
224 }
225}