Question

Choose a suitable language/technology/(ies) and implement connection to a database. Once connected to the database, your code should do the following: (a) Display multiple rows from a query result (b) Have a multi-statement transaction, where all statements succeed and the transaction commits. You can think of multiple sql statements that are planned to be run consecutively and both succeed. (c) Have a multi-statement transaction, where the earlier statements succeed and the later statement fails. In this case, the whole transaction must be rolled back. You can think of multiple sql statements that are planned to be run consecutively, but second one fails and therefore causes a rollback for your first statement.

Can this be done using JAVA/JDBC Connection. JDBC connection can be replaced.

Answer 1

I have provided a basic overview of the solution. And for complete understanding, you can learn more about how the transaction works on servers.

For establishing a connection:

public Connection getConnection() throws SQLException {

Connection conn = null;

Properties connectionProps = new Properties();

connectionProps.put("user", this.userName);

connectionProps.put("password", this.password);

if (this.dbms.equals("mysql")) {

conn = DriverManager.getConnection(

   "jdbc:" + this.dbms + "://" +

   this.serverName +

   ":" + this.portNumber + "/",

   connectionProps);

} else if (this.dbms.equals("derby")) {

conn = DriverManager.getConnection(

   "jdbc:" + this.dbms + ":" +

   this.dbName +

   ";create=true",

   connectionProps);

}

System.out.println("Connected to database");

return conn;

}

How to extract multiple rows:

while(rs.next()){ //Retrieve by column name int id = rs.getInt("id"); int age = rs.getInt("age"); String first = rs.getString("first"); String last = rs.getString("last"); //Display values System.out.print("ID: " + id); System.out.print(", Age: " + age); System.out.print(", First: " + first); System.out.println(", Last: " + last); }

And for the 2nd and 3rd part the JDBC implements the concept of transaction and ACID Properties i.e Atomicity, Consistency, Isolation, Durability so this can be done as it has all the functionality