Definition

JDB is a java module linking the database to a web interface. The module contains interfaces, classes and exceptions.

Drivers§

Every application use a driver to communicate with a database, we have to link the driver to the application via the Class class and the method forName(String path).

Exemple§

Class.forName("linktotheDrive");

Connection§

To connect, we have to link the database to the application with the method getConnection get from DriverManager and which take the url of the database, the username and the password.

The connection have to be on a try element.

Connection con = null;
try{
	con = DriverManager.getConnection(url,nom,mdp);
}catch(Exception e){
	System.out.println(e.getMessage());
}
finally{
try {con.close();} catch(Exception e2) {} // Not the best but there's no other way
}

Always close the connection with the method close()

Statements§

A statement is used to send queries to the database. They exists multiple statements.

Statement§

The statement is use to send queries to the database. It is very basic and does not fit for complex queries (It can be possible but it is complex) .

To send a select to a database we use executeQuery(String query)

For the rest we use executeUpdate(String query)

Statement statement= con.createStatement();
String query = "";
statement.executeQuery(query);
statement.executeUpdate(query);

Prepare Statement§

This method is used when our query is missing some elements. For exemple if we want to compare a column with a value enter by the user or we want to insert values with a variable in it. We replace the missing elements with questions marks.

To send a select to a database we use executeQuery()

For the rest we use executeUpdate()

String query = "INSERT INTO table VALUES (?,?,?)";
PreparedStatement ps = con.prepareStatement(query);
for (int i = 10; i <= 20; i++) {
	ps.setint(1, i);
	ps.setString(2, "Durand" + i);
	ps.setString(3, "paul" + i);
	ps.executeUpdate();
}

Batch§

The batch method is often used when we want to insert a lot of data in a table.

Statement stmt = con.createStatement();
stmt.addBatch("INSERT INTO table VALUES('name0','surname0',0) ");
stmt.addBatch("INSERT INTO table VALUES('name1','surname1',1) ");
int[] count = stmt.executeBatch();

Result Set§

The executeQuery() method returns a ResultSet of the query.

The ResultSet contains the methods:

• next()
• getXXX(String nameofthecolumn)
• getXXX(int numberofthecolumn)

Where XXX equals the type of the column.

ResultSet rs = statement.executeQuery(query);
while (rs.next()) {
String n = rs.getString("name"); 
String p = rs.getString(numberofthecolumn); 
System.out.println(n + " " + p + " " + a);
}

Table infos§

In JDBC we can get table infos with the interfaceDatabaseMetaData.

To get table infos we use the method getMetaData() from the table ResulSetMetaData.

The table ResulSetMetaData comes with a list of methods :

Exemple§

ResultSet rs = stmt.executeQuery(query);
ResultSetMetaData rsmd = rs.getMetaData();
int nbCols = rsmd.getColumnCount();
System.out.println("This table contains" + nbCols + " columns.");

Transactions§

By default each query get immediately a result after the execute method being executed. This approach can be conflictual. If a query return an error by the half of his execution, this may cause some problems. To counter this, we can choose the manner to commit our queries (manually or automatically) via the method setAutoCommit(boolean result). If an error occur during the process we have to rollback, we use then the rollback() method.

If several queries work on the same table this can cause validity problems. To protect yourself, we have to set the transaction isolation protocol. With the method setTransactionIsolation(int valueoftheisolation).

Exemple§

Connection con = null;
try{
	con.setTransactionIsolation(Connection.TRANSACTION_REPEATABLE_READ);
	con.setAutoCommit(false);
	Statement stmt = con.createStatement();
	stmt.executeUpdate("UPDATE table SET column = 1");
	stmt.executeUpdate(query);
	con.commit();
	con.close();
}catch(Exception e){
	try{con.rollback()}catch(Exeption e2){}
	System.out.println(e.getMessage());
}finally{
	try{con.close()}catch(Exeption e3){}
}