# DsUtils.java Sample

This topic inludes the source code for the DsUtils.java Sample.

## Sample Location

This sample is located in the following directory in your WebLogic Workshop installation:

BEA_HOME/weblogic81/samples/workshop/ExtensionDevKit/ControlDevKit/DBScripter/dbScript/util/

## Sample Source Code

`001 package dbScript.util; 002 003 import com.bea.control.JwsContext; 004 import java.sql.*; 005 import java.util.*; 006 007 import javax.naming.*; 008 import javax.management.*; 009 import weblogic.jndi.Environment; 010 import weblogic.management.*; 011 import weblogic.management.configuration.*; 012 import weblogic.management.deploy.*; 013 import weblogic.management.runtime.*; 014 import java.text.MessageFormat; 015 import dbScript.util.Constants; 016 import com.bea.ide.control.EditorContext; 017 018 public class DsUtils 019 { 020 private transient Map mAvailConns; 021 022 // this list of available drivers and their connection attributes 023 // should come from the file BEAHOME/weblogic81/lib/jdbcdrivers.xml 024 private static JDBCConn _connOracle_Thin = new JDBCConn 025 ( 026 "oracle.jdbc.driver.OracleDriver", 027 "jdbc:oracle:thin:@{0}:{1}:{2}", 028 "server;port;database", 029 "localhost;1521;sample", // or use ip addr 030 "user=guest;protocol=thin", 031 "DUAL" 032 ); 033 034 private static JDBCConn _connOracle_WLS = new JDBCConn 035 ( 036 "weblogic.jdbc.oci.Driver", 037 "jdbc:oracle:thin:@{0}:{1}:{2}", 038 "server;port;database", 039 "localhost;1521;sample", // or use ip addr 040 "user=guest;dll=ocijdbc9;protocol=thin", 041 "DUAL" 042 ); 043 private static JDBCConn _connSQLServer_DataDirect = new JDBCConn 044 ( 045 "com.ddtek.jdbc.sqlserver.SQLServerDriver", 046 "jdbc:microsoft:sqlsever:{0}:{1};DatabaseName={2}", 047 "server;port;database", 048 "//localhost;1433;Northwind", 049 "user=sa;password=notnull", 050 "systypes" 051 ); 052 053 private static JDBCConn _connSQLServer_WLS = new JDBCConn 054 ( 055 "weblogic.jdbc.mssqlserver4.Driver", 056 //"jdbc:weblogic:mssqlserver4:Northwind@localhost:1433", 057 "jdbc:weblogic:mssqlserver4:{0}@{1}:{2}", 058 "database;server;port", 059 "Northwind;localhost;1433", 060 "user=sa", 061 "systypes" 062 ); 063 064 private static JDBCConn _connPointBase = new JDBCConn 065 ( 066 "com.pointbase.jdbc.jdbcUniversalDriver", 067 //"jdbc:pointbase:server://localhost:9093/cajun", 068 "jdbc:pointbase:server:{0}:{1}/{2}", 069 "server;port;database", 070 "//localhost;9093;cajun", 071 "user=PBPUBLIC;password=PBPUBLIC", 072 "POINTBASE.SYSSCHEMATA" 073 ); 074 private static String _nameServer = null; 075 076 077 private static class JDBCConn 078 { 079 JDBCConn(String d, String u, String urlP, String urlD, String p, String t) 080 { 081 _driver = d; 082 _url = u; 083 _urlPrompts=urlP; 084 _urlDefaults=urlD; 085 _properties = p; 086 _testTable = t; 087 } 088 public Properties getProperties() 089 { 090 Properties p = new Properties(); 091 String[] a = _properties.split(";"); 092 for (int i=0 ; i<a.length ; i++) 093 { 094 String s = a[i]; 095 int ind = s.indexOf('='); 096 p.put(s.substring(0,ind), s.substring(ind+1)); 097 } 098 return p; 099 } 100 public String _driver; 101 public String _url; 102 public String _urlPrompts; 103 public String _urlDefaults; 104 public String _properties; 105 public String _testTable; 106 } 107 108 public DsUtils() 109 { 110 mAvailConns=new HashMap(); 111 mAvailConns.put("PointBase", _connPointBase); 112 mAvailConns.put("Oracle", _connOracle_Thin); 113 mAvailConns.put("SQLServer", _connSQLServer_WLS); 114 115 mAvailConns.put("Oracle_Thin", _connOracle_Thin); 116 mAvailConns.put("Oracle_WLS", _connOracle_WLS); 117 mAvailConns.put("SQLServer_DataDirect", _connSQLServer_DataDirect); 118 mAvailConns.put("SQLServer_WLS", _connSQLServer_WLS); 119 } 120 public String [] getDriverList() 121 { 122 Object [] oa; 123 oa=mAvailConns.keySet().toArray(); 124 String [] sa=new String [oa.length]; 125 for (int i=0;i<oa.length;i++) 126 sa[i]=oa[i].toString(); 127 128 return sa; 129 } 130 131 public String [] getDriverURLPrompts(String driverType) 132 { 133 JDBCConn c = (JDBCConn)mAvailConns.get(driverType); 134 return c._urlPrompts.split(";"); 135 } 136 public String getDriverURLDefaults(String driverType) 137 { 138 JDBCConn c = (JDBCConn)mAvailConns.get(driverType); 139 return c._urlDefaults; 140 } 141 public String getDriverProperties(String driverType) 142 { 143 JDBCConn c = (JDBCConn)mAvailConns.get(driverType); 144 return c._properties; 145 } 146 public Properties getPropertiesFromString(String props) 147 { 148 Properties p = new Properties(); 149 String[] a = props.split(";"); 150 for (int i=0 ; i<a.length ; i++) 151 { 152 String s = a[i]; 153 int ind = s.indexOf('='); 154 p.put(s.substring(0,ind), s.substring(ind+1)); 155 } 156 return p; 157 } 158 159 public String createDataSource( 160 String driverType, // Oracle, PointBase, etc. 161 String baseName, //The DSName to be created 162 String [] urlValues, // url parts 163 Properties p, // connection properties 164 String passwordToEncrypt, // stored as encrypted 165 String wlsAdminUser, // e.g. installadministrator 166 String wlsAdminPassword, // e.g. installadministrator 167 boolean fCreateNonTxDs // 168 ) throws Exception 169 { 170 MBeanHome home = _getMBeanHome(wlsAdminUser, wlsAdminPassword); 171 return _createConnection(home, driverType, baseName , urlValues, p, passwordToEncrypt, fCreateNonTxDs); 172 } 173 174 private void _dropMBean(MBeanHome home, DeploymentMBean bean) throws Exception 175 { 176 // Remove dynamically created connection pool from the server 177 TargetMBean[] targets = bean.getTargets(); 178 for (int i=targets.length ; --i >= 0 ; ) 179 bean.removeTarget(targets[i]); 180 // Remove dynamically created connection pool from the configuration 181 home.deleteMBean(bean); 182 } 183 184 private MBeanHome _getMBeanHome(String user, String password) throws Exception 185 { 186 Environment env = new Environment(); 187 if (null != user) 188 { 189 env.setSecurityPrincipal(user); 190 env.setSecurityCredentials(password); 191 } 192 Context c = env.getInitialContext(); 193 MBeanHome home = (MBeanHome)c.lookup("weblogic.management.home.localhome"); 194 195 if (null == _nameServer) 196 { 197 RemoteMBeanServer serverMBean = home.getMBeanServer(); 198 _nameServer = serverMBean.getServerName(); 199 } 200 201 return home; 202 } 203 204 private String _createConnection( 205 MBeanHome home, 206 String driverType, 207 String baseName, 208 String [] urlValues, 209 Properties p, 210 String passwordToEncrypt, 211 boolean fBothTxTypes) throws Exception 212 { 213 StringBuffer sb = new StringBuffer(); 214 boolean fCreateAttempted=false; 215 216 JDBCConn conn = (JDBCConn)mAvailConns.get(driverType); 217 if (null==conn) 218 throw new Exception("Unknown Driver Type"); 219 220 JDBCConnectionPoolMBean jdbcBean = null; 221 222 String poolName = getPoolName(baseName); 223 String dsNameNoTx = getDsName(baseName, true); 224 String dsTxName = getDsName(baseName, false); 225 226 sb.append("\n-- Drop Existing Data Sources and Pool --"); 227 sb.append(dropExistingObjects(home, dsNameNoTx, dsTxName, poolName)); 228 229 try 230 { 231 sb.append("\n\n-- Create New Connection Pool and Data Sources--"); 232 fCreateAttempted=true; 233 jdbcBean = (JDBCConnectionPoolMBean)home.createAdminMBean 234 ( 235 poolName, 236 "JDBCConnectionPool" 237 ); 238 String url = MessageFormat.format(conn._url, urlValues); 239 jdbcBean.setURL(url); 240 jdbcBean.setDriverName(conn._driver); 241 jdbcBean.setProperties(p); 242 if (passwordToEncrypt != null) 243 jdbcBean.setPassword(passwordToEncrypt); 244 jdbcBean.setLoginDelaySeconds(1); 245 jdbcBean.setInitialCapacity(1); 246 jdbcBean.setMaxCapacity(10); 247 jdbcBean.setCapacityIncrement(1); 248 jdbcBean.setShrinkingEnabled(true); 249 jdbcBean.setShrinkPeriodMinutes(10); 250 jdbcBean.setRefreshMinutes(10); 251 jdbcBean.setTestTableName(conn._testTable); 252 253 sb.append("\n New Connection Pool "); 254 sb.append("\n\t Driver: " + conn._driver); 255 sb.append("\n\t URL: " + url); 256 sb.append("\n\t Properties: ").append(p); 257 jdbcBean.addTarget((ServerMBean)home.getAdminMBean(_nameServer, "Server")); 258 sb.append("\n\t\t CREATED"); 259 260 sb.append("\n New DataSource (tx): ").append("\t" + dsTxName); 261 createTxDS(home, conn, poolName, dsTxName); 262 sb.append("\t CREATED"); 263 264 if (fBothTxTypes) 265 { 266 sb.append("\n New DataSource (NoTx): ").append("\t" + dsNameNoTx); 267 createDS(home, conn, poolName, dsNameNoTx); 268 sb.append("\t CREATED"); 269 } 270 jdbcBean=null; 271 sb.append("\n"); 272 return sb.toString(); 273 274 } 275 catch (Exception x) 276 { 277 Exception ex = new Exception("Exception After " + sb.toString() + "\n\n", x); 278 // failed create leaves half-created pool 279 if (fCreateAttempted) 280 dropExistingObjects(home, dsNameNoTx, dsTxName, poolName); 281 throw ex; 282 } 283 } 284 285 private void createTxDS(MBeanHome home, JDBCConn conn, String poolName, String dsName) throws Exception 286 { 287 JDBCTxDataSourceMBean dsTxBean = null; 288 dsTxBean = (JDBCTxDataSourceMBean)home.createAdminMBean 289 ( 290 dsName, 291 "JDBCTxDataSource" 292 ); 293 dsTxBean.setJNDIName(dsName); 294 dsTxBean.setPoolName(poolName); 295 dsTxBean.addTarget((ServerMBean)home.getAdminMBean(_nameServer, "Server")); 296 297 } 298 299 private void createDS(MBeanHome home, JDBCConn conn, String poolName, String dsNameNoTx) throws Exception 300 { 301 302 JDBCDataSourceMBean dsBean = null; 303 dsBean = (JDBCDataSourceMBean)home.createAdminMBean 304 ( 305 dsNameNoTx, 306 "JDBCDataSource" 307 ); 308 dsBean.setJNDIName(dsNameNoTx); 309 dsBean.setPoolName(poolName); 310 dsBean.addTarget((ServerMBean)home.getAdminMBean(_nameServer, "Server")); 311 312 } 313 314 private String dropExistingObjects(MBeanHome home, String dsNameNoTx, String dsTxName, String poolName) throws Exception 315 { 316 StringBuffer sb = new StringBuffer(); 317 JDBCConnectionPoolMBean jdbcBean = null; 318 try 319 { 320 JDBCDataSourceMBean dsBean = null; 321 sb.append("\n DataSource (noTx) : ").append(dsNameNoTx); 322 dsBean = (JDBCDataSourceMBean)home.getAdminMBean(dsNameNoTx, "JDBCDataSource"); 323 _dropMBean(home, dsBean); 324 sb.append(" DROPPED"); 325 dsBean = null; 326 } 327 catch (InstanceNotFoundException x) {sb.append(" NOT FOUND");} 328 329 try 330 { 331 JDBCTxDataSourceMBean dsTxBean = null; 332 sb.append("\n DataSource (Tx) ").append(dsTxName); 333 dsTxBean = (JDBCTxDataSourceMBean)home.getAdminMBean(dsTxName, "JDBCTxDataSource"); 334 _dropMBean(home, dsTxBean); 335 sb.append(" DROPPED"); 336 dsTxBean = null; 337 } 338 catch (InstanceNotFoundException x) {sb.append(" NOT FOUND");} 339 340 try 341 { 342 sb.append("\n ConnectionPool: ").append(poolName); 343 jdbcBean = (JDBCConnectionPoolMBean)home.getAdminMBean(poolName, "JDBCConnectionPool"); 344 345 sb.append("\n\t").append(jdbcBean.getDriverName()); 346 sb.append("\n\t").append(jdbcBean.getURL()); 347 sb.append("\n\t").append(jdbcBean.getProperties()); 348 349 _dropMBean(home, jdbcBean); 350 351 sb.append("\n\t\t DROPPED"); 352 jdbcBean = null; 353 } 354 catch (InstanceNotFoundException x) {sb.append("\n NOT FOUND");} 355 catch (UndeploymentException ux) 356 { 357 if (ux.getMessage().indexOf("Unknown Connection Pool")>=0) 358 sb.append("\n NOT FOUND "); 359 else 360 throw ux; 361 } 362 catch (DistributedManagementException dx) 363 { 364 if (dx.getMessage().indexOf("Unknown Connection Pool")>=0) 365 sb.append("\n NOT FOUND "); 366 else 367 throw dx; 368 } 369 370 jdbcBean = null; 371 return sb.toString(); 372 } 373 374 375 public Collection getDatasourceJNDINames (MBeanHome home) 376 throws Exception 377 { 378 Set allMBeans = home.getMBeansByType ( "JDBCTxDataSource" ); //Config 379 Iterator iter = allMBeans.iterator (); 380 381 ArrayList list = new ArrayList (); 382 while ( iter.hasNext () ) 383 { 384 WebLogicMBean mbean = ( WebLogicMBean ) iter.next (); 385 list.add ( mbean.getName () ); 386 } 387 allMBeans = home.getMBeansByType ( "JDBCDataSource" ); //Config 388 iter = allMBeans.iterator (); 389 390 while ( iter.hasNext () ) 391 { 392 WebLogicMBean mbean = ( WebLogicMBean ) iter.next (); 393 list.add ( mbean.getName () ); 394 } 395 return list; 396 397 } 398 public String getTypeForDSName (String dsName) 399 throws Exception 400 { 401 Iterator iter = mAvailConns.keySet().iterator(); 402 String dsType=null; 403 while (iter.hasNext()) 404 { 405 dsType = (String) iter.next(); 406 if (dsName.indexOf(dsType)>=0) 407 return dsType; 408 } 409 return null; 410 } 411 public String getDsName(String baseName, boolean fNoTx) 412 { 413 String name = baseName.substring(0,1).toUpperCase() + baseName.substring(1); 414 415 if (fNoTx) 416 return name + Constants.DS_DEFAULT_NOTXSUFFIX; 417 else 418 return name; 419 } 420 private String getPoolName(String baseName) 421 { 422 String name = baseName.substring(0,1).toUpperCase() + baseName.substring(1); 423 return name + Constants.DS_DEFAULT_POOLSUFFIX; 424 } 425 426 }`