/** 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();
}
}
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();
}
}
/**
* 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;
}
/**
* 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