Project

General

Profile

« Previous | Next » 

Revision 6953

look up nodedata values first, then update each one - trying to avoid out of memory exception.

View differences:

src/edu/ucsb/nceas/metacat/admin/upgrade/UpgradeNodeDataDatetime.java
35 35
import java.sql.DriverManager;
36 36
import java.sql.Timestamp;
37 37
import java.util.Calendar;
38
import java.util.Map;
39
import java.util.TreeMap;
38 40

  
39 41
import javax.xml.bind.DatatypeConverter;
40 42

  
......
63 65
    	return success;
64 66
    }
65 67
    
66
    private boolean upgradeSingleTable(String tableName) throws AdminException {
68
    private Connection getConnection() throws AdminException {
69

  
70
    	Connection conn = null;
67 71
    	try {
68
    		// get the properties
69
    		driver = PropertyService.getProperty("database.driver");
70
    	    url = PropertyService.getProperty("database.connectionURI");
71
    	    user = PropertyService.getProperty("database.user");
72
    	    password = PropertyService.getProperty("database.password");
73
    	    
72
	    	// get the properties
73
			driver = PropertyService.getProperty("database.driver");
74
		    url = PropertyService.getProperty("database.connectionURI");
75
		    user = PropertyService.getProperty("database.user");
76
		    password = PropertyService.getProperty("database.password");
77
		    
74 78
	        // Create a JDBC connection to the database    	
75
	    	Connection sqlca = null;
76 79
	        Driver d = (Driver) Class.forName(driver).newInstance();
77 80
	        DriverManager.registerDriver(d);
78
	        sqlca = DriverManager.getConnection(url, user, password);
79
	    	
81
	        conn = DriverManager.getConnection(url, user, password);
82
    	} catch (Exception e) {
83
			String msg = "Could not get connection: " + e.getMessage();
84
			log.error(msg);
85
			AdminException ae = new AdminException(msg);
86
			ae.initCause(e);
87
			throw ae;
88
		} 
89
    	return conn;
90
    	
91
    }
92
    
93
    private Map<String, Timestamp> getDataValues(String tableName) throws AdminException {
94
    	
95
		Map<String, Timestamp> dataValues = new TreeMap<String, Timestamp>();
96

  
97
    	try {
98
    		
99
        	Connection conn = getConnection();
80 100
	        
81 101
	        Statement sqlStatement = null;
82
	        PreparedStatement pstmt = null;
83 102
	        ResultSet rset = null;
84 103
	        String nodeid = null;
85 104
            String nodedata = null;
86 105
            int count = 0;
87 106

  
88
	        	// select the nodes that are not numeric and not null
89
		        sqlStatement = sqlca.createStatement();
90
		        rset = sqlStatement.executeQuery(
91
		            "SELECT DISTINCT NODEID, NODEDATA "
92
		            + "FROM " + tableName + " WHERE "
93
		            + "nodedata IS NOT NULL "
94
		            //+ "AND nodedatanumerical IS NULL"
95
		            );
96
		
97
		        count = 0;
98
		        while (rset.next()) {
99
		
100
		            nodeid = rset.getString(1);
101
		            nodedata = rset.getString(2);
102
		
103
		            try {
104
		                if (!nodedata.trim().equals("")) {
105
		                	
106
		                	
107
		            		Calendar dataDateValue = DatatypeConverter.parseDateTime(nodedata);
108
		                    Timestamp dataTimestamp = new Timestamp(dataDateValue.getTimeInMillis());
109
		
110
		                    pstmt = sqlca.prepareStatement(
111
		                        "update " + tableName +
112
		                        " set nodedatadate = ?" +
113
		                        " where nodeid = " + nodeid);
114
		                    pstmt.setTimestamp(1, dataTimestamp);
115
		                    pstmt.execute();
116
		                    //pstmt.close();
107
			// select the nodes that are not numeric and not null
108
	        sqlStatement = conn .createStatement();
109
	        rset = sqlStatement.executeQuery(
110
	            "SELECT DISTINCT NODEID, NODEDATA "
111
	            + "FROM " + tableName + " WHERE "
112
	            + "nodedata IS NOT NULL "
113
	            //+ "AND nodedatanumerical IS NULL"
114
	            );
115
	
116
	        count = 0;
117
	        while (rset.next()) {
118
	
119
	            nodeid = rset.getString(1);
120
	            nodedata = rset.getString(2);
121
	
122
	            if (!nodedata.trim().equals("")) {
123
	            	try {
124
	            		Calendar dataDateValue = DatatypeConverter.parseDateTime(nodedata);
125
	                    Timestamp dataTimestamp = new Timestamp(dataDateValue.getTimeInMillis());
126
	                    dataValues.put(nodeid, dataTimestamp);
127
	            	} catch (Exception e) {
128
						// probably not a valid date
129
					}
130
	            }
131
	            
132
                log.debug("parsed: " + nodedata);
133
                count++;
134
                if (count%5 == 0) {
135
                    log.debug(count + "...");
136
                }		               
137
	        }
138
	        log.warn("Table: " + tableName + " complete. " + count + " values converted.");
139
	        rset.close();
140
	        sqlStatement.close();
141
	        conn.close();
142
    	} catch (Exception e) {
143
    		String msg = "Error parsing date time node data: " + e.getMessage();
144
    		log.error(msg, e);
145
            throw new AdminException(msg);
146
        }
147
    	return dataValues;
148
    }
149
    
150
    private boolean upgradeSingleTable(String tableName) throws AdminException {
151
    	
152
    	Map<String, Timestamp> dataValues = getDataValues(tableName);
153
    	
154
    	Connection conn = getConnection();
155
    	
156
    	for (String nodeid: dataValues.keySet()) {
157
    		Timestamp dataTimestamp = dataValues.get(nodeid);
158
			upgradeSingleValue(tableName, nodeid, dataTimestamp , conn);
159
    	}
160
    	
161
    	if (conn != null) {
162
    		try {
163
				conn.close();
164
			} catch (SQLException e) {
165
				throw new AdminException("Error closing connection: " + e.getMessage());
166
			}
167
    	}
168
    	
169
    	return true;
170
    	
171
    }
172
    
173
    private boolean upgradeSingleValue(String tableName, String nodeid, Timestamp dataTimestamp, Connection conn) throws AdminException {
174
    	try {
175
    		
176
	        PreparedStatement pstmt = null;
117 177

  
118
		                    log.debug("processed: " + nodedata);
119
		                    count++;
120
		                    if (count%5 == 0) {
121
		                        log.debug(count + "...");
122
		                    }
123
		            		
124
		                }
125
		            } catch (Exception e) { 
126
		            	// do nothing, was not a valid date
127
		            	//e.printStackTrace();
128
		            } finally {
129
		            	if (pstmt != null) {
130
		            		pstmt.close();
131
		            	}
132
		            }
133
		        }
134
		        log.warn("Table: " + tableName + " complete. " + count + " values converted.");
135
		
136
		        rset.close();
137
		        sqlStatement.close();
138
	        sqlca.close();
178
            try {
179
                    pstmt = conn.prepareStatement(
180
                        "update " + tableName +
181
                        " set nodedatadate = ? " +
182
                        " where nodeid = ? ");
183
                    pstmt.setTimestamp(1, dataTimestamp);
184
                    pstmt.setString(2, nodeid);
185

  
186
                    pstmt.execute();
187

  
188
                    log.debug("processed nodedata: " + dataTimestamp);
189
                    
190
            } catch (Exception e) { 
191
            	// do nothing, was not a valid date
192
            	//e.printStackTrace();
193
            } finally {
194
            	if (pstmt != null) {
195
            		pstmt.close();
196
            	}
197
            }
198
        		
139 199
    	} catch (Exception e) {
140 200
    		String msg = "Error updating date time node data: " + e.getMessage();
141 201
    		log.error(msg, e);

Also available in: Unified diff