JAVA DB Lab 1

DBLAb

1.     You will need MySQL installed on your machine to complete this lab. Go to MySQL.com/downloads → Click the download link under the MySQL community Edition → Download MySQL Community Server → Click on MySQL Installer for Windows (or select the appropriate OS from the drop down list). For windows: https://dev.mysql.com/downloads/windows/installer/8.0.html

2.     Install MySQL

3.     Once installed, open MySQL Workbench. Click on the Local Instance MySQL Router.

4.     Click users and privileges → click Add Account → Give login Name and password, use “localhost” for Limit to Hosts Matching field. → Select Schema Privileges tab → Click Add Entry → select selected schema → select sakila from the drop down list →  click OK Check Select → click Apply.

5.     Create a new project.

6.     Download and add the MySQL Connector/J  https://dev.mysql.com/downloads/connector/j/

7.     Uncompress the zip file and copy mysql-connector-java jar file.

8.     Create a new folder called libs by right clicking the project in the Package Explorer. Paste the jar file in the libs folder.

○      In eclipse, right click on the jar file → select Build path → Add build path.

○      In Intellij, click File → Project Structure →  button → choose the jar file

9.     Create a connection object using the code below. You may have to do this within a try catch block or Try-with-resources block. Don’t forget to close the connection using a finally block, if you are not using a Try-with-resources block.

○      Connection connection = DriverManager.getConnection( "jdbc:mysql://localhost/sakila","dbuser","dbpassword")

10.  Run the program to check if you are able to connect to the database.

11.  Modify the code to read data from a database table and write to the console screen. You’ll need a statement object and a resultset object, use the following code. Again if you are not using Try-with-resources block, don’t forget to close these object once you are done with them. Remember that the resources should be closed in the reverse order starting from resultset, statement and finally the connection.

○      Statement statement = connection.createStatement();

○      ResultSet resultset = statement.executeQuery("Select * from actor");

12.  We have created a forward only resultset. Therefore we can only navigate in one direction, forward. Use ResultSet::next() method to navigate sequentially from the first record to the last record. Use ResultSet::getString(n) method to get data from each record. Remember column index starts with 1 not 0. Your output should be as follows:

13.  MySQL by default returns a scrollable resultset, however most other databases returns forward only resultset by default. To use a scrollable resultset, you will have to request for one explicitly.

Statement statement = connection.createStatement( ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);

14.  Display First record, Last record and 100th record

15.  Modify the code the retrieve 20 records at a time. First display 20 record. When the user press a key retrieve next 20 records and so on.

16.  Getting a filtered result set using preparedStatements. Filter the films that have a lenght of less than 90 mins,

○      PreparedStatement statement = connection.prepareStatement( "Select * from film where length < ?", ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);

17.  Add the code statement.setDouble(1, lenght); where length holds the value entered by the user.

○      statement.setDouble(1, new Scanner(System.in).nextDouble());

○      resultSet = statement.executeQuery();

18.  Your output should be as shown below: