· 7 years ago · Jan 11, 2019, 11:28 PM
1package sample.lib;
2
3import javafx.collections.ObservableList;
4
5import java.sql.*;
6
7public class db_pg {
8
9 public static final String DB = "oidata_pg";
10 public static final String URL = "jdbc:postgresql://localhost:5432/"+DB;
11 public static final String USER = "stockdeveloper";
12 public static final String PASS = "555555";
13
14
15 /*############################################################
16 DB CREATION LOGIC
17 ############################################################*/
18
19 public void createDB(String dbname) throws SQLException{
20 Connection c = DriverManager.getConnection("jdbc:postgresql://localhost:5432/postgres", "stockdeveloper", "555555");
21 Statement statement = c.createStatement();
22 ResultSet rs = statement.executeQuery("SELECT datname FROM pg_catalog.pg_database WHERE datname = '"+dbname+"';");
23 if(!rs.next())
24 {
25 statement.executeUpdate("CREATE DATABASE "+dbname);
26 }
27 }
28
29 /*############################################################
30 DB DROP LOGIC
31 ############################################################*/
32
33 public void deleteDB() throws SQLException {
34 String sql = "DROP TABLE IF EXISTS OptionData";
35 try(Connection conn = this.connect();
36 PreparedStatement pstmt = conn.prepareStatement(sql))
37 {
38 pstmt.executeUpdate();
39 }
40 catch (SQLException e)
41 {
42 System.out.println(e.getMessage());
43 }
44 }
45
46 /*############################################################
47 DB CONNECTION LOGIC
48 ############################################################*/
49
50 public static Connection getConnection(){
51 try{
52 return DriverManager.getConnection(URL, USER, PASS);
53 } catch (SQLException ex) {
54 throw new RuntimeException("Error connecting to the database", ex);
55 }
56 }
57
58 /*############################################################
59 TABLE CREATION LOGIC
60 ############################################################*/
61
62 //Table for FnO Master List
63 public void createPGTableFnoMaster() throws SQLException {
64
65 String sql = "CREATE TABLE IF NOT EXISTS FnoMaster ("
66 + " id BIGSERIAL PRIMARY KEY,"
67 + " symcode TEXT NOT NULL,\n"
68 + " sym1 TEXT NOT NULL,\n"
69 + " sym2 TEXT NOT NULL,\n"
70 + " inst TEXT NOT NULL,\n"
71 + " fullname TEXT NOT NULL,\n"
72 + " status TEXT NOT NULL,\n"
73 + " optionType CHAR(2) NOT NULL,"
74 + " strikePrice REAL NOT NULL,\n"
75 + " expiryDate DATE NOT NULL,\n"
76 + " lotsize INT NOT NULL,\n"
77 + "underlying REAL NOT NULL\n"
78 + ");";
79
80 try(Connection conn = getConnection();
81 Statement stmt = conn.createStatement())
82 {
83 stmt.execute(sql);
84 }
85 catch (SQLException e)
86 {
87 System.out.println(e.getMessage());
88 }
89 }
90
91 //Table for FnO Data Fetch Log
92 public void createPGTableFnoLog() throws SQLException {
93
94 String sql = "CREATE TABLE IF NOT EXISTS FnoLog(\n"
95 + " id BIGSERIAL PRIMARY KEY,\n"
96 + " lastAttempted TIMESTAMP NOT NULL,\n"
97 + " lastUpdated TIMESTAMP NOT NULL\n"
98 + ");";
99
100 try(Connection conn = getConnection();
101 Statement stmt = conn.createStatement())
102 {
103 stmt.execute(sql);
104 }
105 catch (SQLException e)
106 {
107 System.out.println(e.getMessage());
108 }
109 }
110
111
112
113
114 //Table for FnO Data
115 public void createPGTableOptionData() throws SQLException {
116
117 String sql = "CREATE TABLE IF NOT EXISTS OptionData ("
118 + " id BIGSERIAL PRIMARY KEY,"
119 + " optionType CHAR(2) NOT NULL,"
120 + " strikePrice REAL NOT NULL,\n"
121 + " expiryDate DATE NOT NULL,\n"
122 + " OI BIGINT NOT NULL,\n"
123 + " OIChng BIGINT NOT NULL,\n"
124 + " vol BIGINT NOT NULL,\n"
125 + " IV REAL NOT NULL,\n"
126 + " LTP REAL NOT NULL,\n"
127 + " netChng REAL NOT NULL,\n"
128 + " bidQty INTEGER NOT NULL,\n"
129 + " bidPrice REAL NOT NULL,\n"
130 + " ofrQty INTEGER NOT NULL,\n"
131 + "ofrPrice REAL NOT NULL,\n"
132 + " lastUpdate TIMESTAMP NOT NULL,\n"
133 + "underlying REAL NOT NULL\n"
134 + ");";
135
136 try(Connection conn = getConnection();
137 Statement stmt = conn.createStatement())
138 {
139 stmt.execute(sql);
140 }
141 catch (SQLException e)
142 {
143 System.out.println(e.getMessage());
144 }
145 }
146
147 /*############################################################
148 DATA INSERTION LOGIC
149 ############################################################*/
150
151 public void insertToFnoList(ObservableList<fnoList> data) throws SQLException {
152
153 try{
154 Connection conn = getConnection();
155 String sql ="INSERT INTO FnoList(symcode,sym1,sym2,inst) VALUES(?,?,?,?);";
156 conn.setAutoCommit(false);
157 PreparedStatement pstmt = conn.prepareStatement(sql);
158 int i = 0;
159 for (fnoList dat : data) {
160 pstmt.setString(1, dat.getSymcode());
161 pstmt.setString(2, dat.getSym1());
162 pstmt.setString(3, dat.getSym2());
163 pstmt.setString(4, dat.getInst());
164 pstmt.addBatch();
165 i++;
166 if (i % 1000 == 0) {
167 pstmt.executeBatch(); // Execute every 200 items.
168 }
169 }
170 pstmt.executeBatch();
171 conn.commit();
172 pstmt.close();
173 }
174 catch(Exception e){
175 //if(co)
176 }
177 }
178
179
180 public void insertToOptionData(ObservableList<OptionData> data) throws SQLException {
181
182
183 String sql = "INSERT INTO OptionData(optionType,strikePrice,expiryDate,OI,OIChng,vol,IV,LTP,netChng,bidQty,bidPrice,ofrQty,ofrPrice, lastUpdate, underlying) VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
184
185 Connection conn = getConnection();
186 conn.setAutoCommit(false);
187 //conn.setTransactionIsolation();
188 //SQLiteDatabase database = this.getWritableDatabase();
189 PreparedStatement pstmt = conn.prepareStatement(sql);
190 int i = 0;
191 for (OptionData dat : data) {
192 pstmt.setString(1, dat.getOptionType());
193 pstmt.setFloat(2, Converter.toFloat(dat.getStrikePrice()));
194 pstmt.setString(3, dat.getExpiryDate());
195 pstmt.setInt(4, Converter.toInt(dat.getOI()));
196 pstmt.setInt(5, Converter.toInt(dat.getOIChng()));
197 pstmt.setLong(6, Converter.toLong(dat.getVol()));
198 pstmt.setFloat(7, Converter.toFloat(dat.getIV()));
199 pstmt.setFloat(8, Converter.toFloat(dat.getLTP()));
200 pstmt.setFloat(9, Converter.toFloat(dat.getNetChng()));
201 pstmt.setInt(10, Converter.toInt(dat.getBidQty()));
202 pstmt.setFloat(11, Converter.toFloat(dat.getBidPrice()));
203 pstmt.setInt(12, Converter.toInt(dat.getOfrQty()));
204 pstmt.setFloat(13, Converter.toFloat(dat.getOfrPrice()));
205 pstmt.setString(14, dat.getLastUpdate());
206 pstmt.setFloat(15, Converter.toFloat(dat.getUnderlying()));
207 pstmt.addBatch();
208 i++;
209 if (i % 1000 == 0) {
210 pstmt.executeBatch(); // Execute every 200 items.
211 }
212 }
213 pstmt.executeBatch();
214 conn.commit();
215 pstmt.close();
216 }
217
218
219 /*############################################################
220 TEST RUN THIS CLASS ALONE
221 ############################################################*/
222
223
224 public void initialize() throws SQLException {
225 this.connect();
226 //this.createTableOptionData();
227 //this.createTableFnoList();
228 }
229
230 public void initializePG() throws SQLException{
231 db_pg database = new db_pg();
232 //database.connect();
233 //database.deleteDB();
234 //database.initialize();
235 database.createDB(DB);
236 database.createPGTableOptionData();
237 database.createPGTableFnoMaster();
238 }
239
240
241 public static void main(String[] args) throws SQLException {
242 db_pg database = new db_pg();
243 //database.connect();
244 //database.deleteDB();
245 //database.initialize();
246 //database.createDB(DB);
247 database.initializePG();
248
249 }