/** Constructeur */
private RelDBUtils() {
try {
// Source de données Oracle
ods = new OracleDataSource();
// type de pilote oracle
ods.setDriverType("thin");
// nom de la machine sur laquelle se trouve la base
ods.setServerName(nomDeServeur);
// numero du port pour se connecter à la base
ods.setPortNumber(port);
// nom de la base
ods.setDatabaseName(nomDeLaBase);
// Pour ouvrir une session (représentée par l'objet connect
connect = ods.getConnection(login, pwd);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
/* (non-Javadoc)
* @see com.googlecode.psiprobe.beans.DatasourceAccessor#getInfo(java.lang.Object)
*/
public DataSourceInfo getInfo(Object resource) throws Exception {
DataSourceInfo dataSourceInfo = null;
if (canMap(resource)) {
OracleDataSource source = (OracleDataSource) resource;
OracleConnectionCacheManager occm =
OracleConnectionCacheManager.getConnectionCacheManagerInstance();
Properties cacheProperties = source.getConnectionCacheProperties();
String cacheName = source.getConnectionCacheName();
cacheName = cacheName != null && occm.existsCache(cacheName) ? cacheName : null;
if (cacheProperties != null) {
dataSourceInfo = new DataSourceInfo();
if (cacheName != null) {
dataSourceInfo.setBusyConnections(occm.getNumberOfActiveConnections(cacheName));
dataSourceInfo.setEstablishedConnections(
occm.getNumberOfAvailableConnections(cacheName)
+ dataSourceInfo.getBusyConnections());
} else {
dataSourceInfo.setBusyConnections(0);
dataSourceInfo.setEstablishedConnections(0);
}
dataSourceInfo.setMaxConnections(Utils.toInt(cacheProperties.getProperty("MaxLimit"), -1));
dataSourceInfo.setJdbcUrl(source.getURL());
dataSourceInfo.setUsername(source.getUser());
dataSourceInfo.setResettable(true);
dataSourceInfo.setType("oracle-jdbc");
}
}
return dataSourceInfo;
}
public static void main(String args[]) throws SQLException {
String url = "jdbc:oracle:oci8:@";
try {
String url1 = System.getProperty("JDBC_URL");
if (url1 != null) url = url1;
} catch (Exception e) {
// If there is any security exception, ignore it
// and use the default
}
// Create a OracleDataSource instance and set properties
OracleDataSource ods = new OracleDataSource();
ods.setUser("hr");
ods.setPassword("hr");
ods.setURL(url);
// Connect to the database
Connection conn = ods.getConnection();
Statement stmt = conn.createStatement();
stmt.execute("delete from departments where department_id > 2000");
// Default batch value set to 50 for all prepared statements
// belonging to this connection.
((OracleConnection) conn).setDefaultExecuteBatch(50);
PreparedStatement ps = conn.prepareStatement("insert into departments values (?, ?, ?, ?)");
ps.setInt(1, 2010);
ps.setString(2, "Import");
ps.setInt(3, 114);
ps.setInt(4, 1700);
// this execute does not actually happen at this point
System.out.println(ps.executeUpdate());
ps.setInt(1, 2020);
ps.setString(2, "Export");
ps.setInt(3, 145);
ps.setInt(4, 2500);
// this execute does not actually happen at this point
int rows = ps.executeUpdate();
System.out.println("Number of rows updated before calling sendBatch: " + rows);
// Execution of both previously batched executes will happen
// at this point. The number of rows updated will be
// returned by sendBatch.
rows = ((OraclePreparedStatement) ps).sendBatch();
System.out.println("Number of rows updated by calling sendBatch: " + rows);
ps.close();
conn.close();
}
/**
* Requete Constructeur Ici nous simulons la connexion d'un utilisateur.
* ****************************************************** Pour 'connecter un autre utilisateur que
* W.Soulaimana' vous pouvez changer les lignes 38 et 39 afin de rentrer vos login et mot de passe
* de connexion a SQL*Plus sur la machine Oracle de l'école. !! Il est recommandé de faire pareil
* dans Exemple.java ******************************************************
*
* @param ods Oracle Database Source
*/
public Requete() throws SQLException, ClassNotFoundException, java.io.IOException {
// -------------------------------------
// Login et Mot de passe. A modifier.
// ------------------------------------
ods.setUser("wsoulaimana");
ods.setPassword("wsoulaimana");
// URL de connexion.
ods.setURL("jdbc:oracle:thin:@localhost:1521/oracle");
conn = ods.getConnection();
}
public DBManager(String url, String userName, String dbPassword, String dbName) {
try {
oracleDataSource = new OracleDataSource();
oracleDataSource.setUser(userName);
oracleDataSource.setPassword(dbPassword);
oracleDataSource.setURL(url);
oracleDataSource.setDatabaseName(dbName);
System.out.println("data source created successfully....");
connection = oracleDataSource.getConnection();
System.out.println("connected...");
} catch (SQLException e) {
System.err.println("error...");
e.printStackTrace();
}
}
/* (non-Javadoc)
* @see com.googlecode.psiprobe.beans.DatasourceAccessor#reset(java.lang.Object)
*/
public boolean reset(Object resource) throws Exception {
if (canMap(resource)) {
((OracleDataSource) resource).close();
return true;
}
return false;
}
/**
* Vrati seznam objektu
*
* @return Klic je ID_BILDING, hodnota je BILDING_COL
* @throws SQLException
*/
public Map<Integer, String> getList() throws SQLException {
Map<Integer, String> listOfCustomers = new LinkedHashMap<>();
OracleDataSource ods = DataBase.getConnection();
try (Connection conn = ods.getConnection();
PreparedStatement stmt =
conn.prepareStatement("SELECT * FROM BILDING ORDER BY BILDING_COL"); ) {
try (ResultSet rs = stmt.executeQuery()) {
while (rs.next()) {
listOfCustomers.put(
rs.getInt("ID_BILDING"),
rs.getString("ID_BILDING") + " " + rs.getString("BILDING_COL"));
}
}
}
return listOfCustomers;
}
public static OracleDataSource createDataSourceForUser(String userName) throws SQLException {
OracleDataSource dataSource = new OracleDataSource();
dataSource.setDriverType(DRIVER_TYPE);
dataSource.setServerName(SERVER_NAME);
dataSource.setPortNumber(PORT_NUMBER);
dataSource.setDatabaseName(DATABASE_NAME);
dataSource.setUser(userName);
dataSource.setPassword(PASSWORD);
return dataSource;
}
/**
* Vlozime noveho workera
*
* @param nazev
* @param type
* @return
* @throws SQLException
*/
public int insert(String nazev, String type) throws SQLException {
OracleDataSource ods = DataBase.getConnection();
try (Connection conn = ods.getConnection();
PreparedStatement stmt =
conn.prepareStatement(
"INSERT INTO BILDING (BILDING_COL, TYPE_BILDING_ID) VALUES(?,?)"); ) {
stmt.setString(1, nazev);
stmt.setString(2, type);
stmt.execute();
try (Statement stmt2 = conn.createStatement();
ResultSet rs =
stmt2.executeQuery("SELECT ID_BILDING FROM BILDING ORDER BY ID_BILDING DESC")) {
rs.next();
return rs.getInt("ID_BILDING");
}
}
}
/**
* Vratime bilding se zadanym ID
*
* @param id
* @return Klic je atribut bilding, hodnota je typu bilding. Vypisovat pomoci .toString()
* @throws SQLException
* @throws Exception
*/
public Map<String, Object> get(int id) throws SQLException, Exception {
Map<String, Object> map = new HashMap<>();
OracleDataSource ods = DataBase.getConnection();
try (Connection conn = ods.getConnection();
PreparedStatement stmt =
conn.prepareStatement("SELECT * FROM BILDING WHERE ID_BILDING = ?"); ) {
stmt.setInt(1, id);
try (ResultSet rs = stmt.executeQuery()) {
if (rs.next()) {
map.put("ID_BILDING", id);
map.put("BILDING_COL", rs.getString("BILDING_COL"));
} else {
return null;
}
}
}
return map;
}
public void doGet(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
response.setContentType("text/html");
PrintWriter out = response.getWriter();
String dbUser = "******"; // enter your username here
String dbPassword = "******"; // enter your password here
try {
OracleDataSource ods = new oracle.jdbc.pool.OracleDataSource();
ods.setURL("jdbc:oracle:thin:@//w4111b.cs.columbia.edu:1521/ADB");
ods.setUser(dbUser);
ods.setPassword(dbPassword);
Connection conn = ods.getConnection();
String query = new String();
Statement s = conn.createStatement();
query = "select * from events";
ResultSet r = s.executeQuery(query);
while (r.next()) {
out.println("Today's Date: " + r.getString(1) + " ");
}
r.close();
s.close();
conn.close();
} catch (Exception e) {
out.println("The database could not be accessed.<br>");
out.println("More information is available as follows:<br>");
e.printStackTrace(out);
}
} // end doGet method
public static DataSource getDS(KPIGenProperties p) throws SQLException, IOException {
if ("DB2".equalsIgnoreCase(p.get("DB"))) {
DB2SimpleDataSource ds = new DB2SimpleDataSource();
ds.setDriverType(4);
ds.setLoginTimeout(5); // sec
ds.setServerName(p.get("ServerName"));
ds.setPortNumber(Integer.parseInt(p.get("PortNumber")));
ds.setDatabaseName(p.get("DatabaseName"));
ds.setUser(p.get("User"));
ds.setPassword(p.get("Password"));
return ds;
} else {
OracleDataSource ds = new OracleDataSource();
ds.setDriverType("thin");
ds.setServerName(p.get("ServerName"));
ds.setPortNumber(Integer.parseInt(p.get("PortNumber")));
ds.setDatabaseName(p.get("DatabaseName"));
ds.setUser(p.get("User"));
ds.setPassword(p.get("Password"));
return ds;
}
}
public void changeDatabaseName(String dbName) {
oracleDataSource.setDatabaseName(dbName);
}