· 5 years ago · Mar 12, 2020, 03:22 AM
1package id.co.artajasa.cms.scheduler.worker;
2
3import id.co.artajasa.cms.scheduler.model.SchedulerItem;
4import org.slf4j.Logger;
5import org.slf4j.LoggerFactory;
6
7import java.sql.*;
8import java.text.ParseException;
9import java.text.SimpleDateFormat;
10import java.util.ArrayList;
11import java.util.List;
12
13/**
14 * Created by purwa on 25/10/16.
15 */
16public class SchedulerDbManager {
17 private Logger logger = LoggerFactory.getLogger(getClass());
18 private Connection con;
19 private int timeout;
20 private String jdbcUrl;
21
22 public SchedulerDbManager(int queryTimeout, String jdbcUrl) {
23 this.timeout = queryTimeout;
24 this.jdbcUrl = jdbcUrl;
25 }
26
27 public void openConnection() throws ClassNotFoundException, SQLException {
28 if(con != null)
29 con.close();
30 Class.forName("org.sqlite.JDBC");
31 con = DriverManager.getConnection(jdbcUrl);
32 }
33
34 public boolean createSchedulerTable() {
35 logger.debug("create table scheduler if not exists");
36 boolean status = false;
37 try {
38 String sql = "CREATE TABLE IF NOT EXISTS scheduler (" +
39 "id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT," +
40 "type INTEGER NOT NULL," +
41 "description TEXT," +
42 "execution_time TEXT NOT NULL," +
43 "flag_repeat INTEGER NOT NULL," +
44 "sched_id TEXT NOT NULL" +
45 ")";
46 openConnection();
47 PreparedStatement st = con.prepareStatement(sql);
48 st.setQueryTimeout(timeout);
49 status = st.execute();
50 st.close();
51 con.close();
52 } catch (SQLException e) {
53 e.printStackTrace();
54 } catch (ClassNotFoundException e) {
55 e.printStackTrace();
56 } finally {
57 if(con != null)
58 try {
59 con.close();
60 } catch (SQLException e) {
61 e.printStackTrace();
62 }
63 }
64 return status;
65 }
66
67 public boolean saveSchedulerRecord(SchedulerItem item) {
68 boolean status = false;
69 try {
70 SimpleDateFormat df = new SimpleDateFormat("dd-MM-yyyy HH:mm:ss");
71 openConnection();
72 PreparedStatement checking = con.prepareStatement("SELECT * FROM scheduler WHERE sched_id = ?");
73 checking.setString(1, item.getId());
74 ResultSet rs = checking.executeQuery();
75 if (rs.next())
76 {
77 PreparedStatement update = con.prepareStatement("UPDATE scheduler SET sched_id = ?, type = ?, description = ?, " +
78 "execution_time = ?, flag_repeat = ? WHERE sched_id = ?");
79 update.setString(1, item.getId());
80 update.setInt(2, item.getType());
81 update.setString(3, item.getDescription());
82 update.setString(4, df.format(item.getExecutionTime()));
83 update.setInt(5, item.getFlagRepeat());
84 update.setString(6, item.getId());
85 status = update.execute();
86 update.close();
87 }
88 else
89 {
90 PreparedStatement prep = con.prepareStatement("INSERT INTO scheduler(sched_id,type," +
91 "description,execution_time,flag_repeat) VALUES (?,?,?,?,?)");
92 prep.setString(1, item.getId());
93 prep.setInt(2, item.getType());
94 prep.setString(3, item.getDescription());
95 prep.setString(4, df.format(item.getExecutionTime()));
96 prep.setInt(5, item.getFlagRepeat());
97 status = prep.execute();
98 prep.close();
99 }
100 checking.close();
101 con.close();
102 } catch (SQLException e) {
103 e.printStackTrace();
104 } catch (ClassNotFoundException e) {
105 e.printStackTrace();
106 } finally {
107 if(con != null)
108 try {
109 con.close();
110 } catch (SQLException e) {
111 e.printStackTrace();
112 }
113 }
114 return status;
115 }
116
117 public boolean deleteSchedulerRecord(String schedId) {
118 boolean status = false;
119 try {
120 String sql = "DELETE FROM scheduler WHERE sched_id = ?";
121 openConnection();
122 PreparedStatement prep = con.prepareStatement(sql);
123 prep.setString(1, schedId);
124 status = prep.execute();
125 prep.close();
126 con.close();
127 } catch (ClassNotFoundException e) {
128 e.printStackTrace();
129 } catch (SQLException e) {
130 e.printStackTrace();
131 } finally {
132 if(con != null)
133 try {
134 con.close();
135 } catch (SQLException e) {
136 e.printStackTrace();
137 }
138 }
139 return status;
140 }
141
142 public List<SchedulerItem> getAllSchedulerRecord() {
143 List<SchedulerItem> items = new ArrayList<SchedulerItem>();
144 try {
145 openConnection();
146 String sql = "SELECT * FROM scheduler";
147 PreparedStatement prep = con.prepareStatement(sql);
148 ResultSet rs = prep.executeQuery();
149 while(rs.next())
150 {
151 SchedulerItem item = new SchedulerItem();
152 item.setId(rs.getString("sched_id"));
153 item.setType(rs.getInt("type"));
154 item.setDescription(rs.getString("description"));
155 SimpleDateFormat df = new SimpleDateFormat("dd-MM-yyyy HH:mm:ss");
156 item.setExecutionTime(df.parse(rs.getString("execution_time")));
157 item.setFlagRepeat(rs.getInt("flag_repeat"));
158 items.add(item);
159 }
160 } catch (ClassNotFoundException e) {
161 e.printStackTrace();
162 } catch (SQLException e) {
163 e.printStackTrace();
164 } catch (ParseException e) {
165 e.printStackTrace();
166 }
167
168 return items;
169 }
170}