1 package org.simantics.jdbc.variable;
3 import java.sql.PreparedStatement;
4 import java.sql.ResultSet;
5 import java.sql.SQLException;
6 import java.sql.Statement;
7 import java.util.Collections;
8 import java.util.HashMap;
11 import org.simantics.databoard.Bindings;
12 import org.simantics.databoard.binding.Binding;
13 import org.simantics.simulator.toolkit.StandardNodeManagerSupport;
14 import org.simantics.simulator.variable.exceptions.NodeManagerException;
15 import org.slf4j.Logger;
16 import org.slf4j.LoggerFactory;
18 import com.impossibl.postgres.api.jdbc.PGConnection;
19 import com.impossibl.postgres.jdbc.PGDataSource;
21 public class JDBCNodeManagerSupport implements StandardNodeManagerSupport<JDBCNode> {
23 private static final Logger LOGGER = LoggerFactory.getLogger(JDBCNodeManagerSupport.class);
24 @SuppressWarnings("unused")
25 private String id; // this might have some use later in the future?
26 private PGDataSource dataSource;
27 private String channelName;
29 public JDBCNodeManagerSupport(String id, PGDataSource dataSource, String channelName) {
31 this.dataSource = dataSource;
32 this.channelName = channelName;
36 public Object getEngineValue(JDBCNode node) throws NodeManagerException {
37 if (LOGGER.isDebugEnabled())
38 LOGGER.debug("Getting value for {}", node.getName());
40 try (PGConnection connection = (PGConnection) dataSource.getConnection()) {
42 PreparedStatement ps = connection.prepareStatement("SELECT value->'value' FROM simantics_table WHERE key IN ('" + node.getName() + "');");
43 ResultSet rs = ps.executeQuery();
45 if (LOGGER.isDebugEnabled()) {
46 LOGGER.debug("no value for query {}", ps.toString());
50 return rs.getObject(1);
51 } catch (Exception e) {
52 LOGGER.error("Failed to get value for {}", node.getName(), e);
53 throw new NodeManagerException("Failed to get value for " + node.getName(), e);
58 public Binding getEngineBinding(JDBCNode node) throws NodeManagerException {
59 return Bindings.OBJECT;
63 public void setEngineValue(JDBCNode node, Object value) throws NodeManagerException {
64 if (LOGGER.isDebugEnabled())
65 LOGGER.debug("Setting value for {} to {}", node.getName(), value);
67 setValueImpl(node.getName(), value);
70 private void setValueImpl(String name, Object value) throws NodeManagerException {
71 try (PGConnection connection = (PGConnection) dataSource.getConnection()) {
73 PreparedStatement statement = connection.prepareStatement("INSERT INTO simantics_table VALUES (?, ?::JSON) ON CONFLICT (key) DO UPDATE SET value= ?::JSON");
74 statement.setString(1, name);
75 statement.setObject(2, "{\"value\": " + value.toString() + "}");
76 statement.setObject(3, "{\"value\": " + value.toString() + "}");
77 statement.executeUpdate();
79 // notify others (including ourselves)
80 doNotify(connection, name);
81 } catch (Exception e) {
82 LOGGER.error("Failed to set value for {} to {}", name, value, e);
83 throw new NodeManagerException("Failed to set value for " + name + " to " + String.valueOf(value), e);
87 private void doNotify(PGConnection connection, String name) throws SQLException {
88 if (LOGGER.isDebugEnabled())
89 LOGGER.debug("Notifying change {} to channel {}", name, this.channelName);
90 Statement statement = connection.createStatement();
91 String sql = "NOTIFY " + this.channelName + ", '" + name + "'";
92 statement.execute(sql);
97 public String getName(JDBCNode node) {
98 return node.getName();
102 public Map<String, JDBCNode> getChildren(JDBCNode node) {
103 return Collections.emptyMap();
107 public Map<String, JDBCNode> getProperties(JDBCNode node) {
108 HashMap<String, JDBCNode> properties = new HashMap<>();
109 try (PGConnection connection = (PGConnection) dataSource.getConnection()) {
110 Statement st = connection.createStatement();
111 ResultSet executeQuery = st.executeQuery("SELECT key FROM simantics_table");
112 while (executeQuery.next()) {
113 String key = executeQuery.getString(1);
114 properties.put(key, new JDBCNode(key));
116 } catch (Exception e) {
117 LOGGER.error("Could not read properties", e);