Persistent Storage in Java

1. Persistent Storage in Java JDBC and Hibernate Joshua Scotton

2. JDBC Connecting to Relational DBs

3. Loading a Database Driver try { Class.forName("com.mysql.jdbc.Driver"); } catch (ClassNotFoundException e) { e.printStackTrace(); }

4. Creating a Connection try { Connection con = DriverManager.getConnection(url, user, pass); con.close(); } catch (SQLException e) { e.printStackTrace(); }

5. Statement Object stmt = con.createStatement(); stmt.executeUpdate(sqlString); stmt.close(); stmt.executeQuery(sqlString);

6. Example Class.forName("com.mysql.jdbc.Driver"); Connection con = DriverManager.getConnection(url, user, pass); Statement stmt = con.createStatement(); ResultSetrs = stmt.executeQuery(sqlString); while (rs.next()) { System.out.println(rs.getString("username")); } stmt.close(); con.close();

7. ResultSets • next() - moves the cursor forward one row. Returns true if the cursor is now positioned on a row and false if the cursor is positioned after the last row. • previous() - moves the cursor backwards one row. Returns true if the cursor is now positioned on a row and false if the cursor is positioned before the first row. • first() - moves the cursor to the first row in the ResultSet object. Returns true if the cursor is now positioned on the first row and false if the ResultSetobject does not contain any rows. • last() - moves the cursor to the last row in the ResultSet object. Returns true if the cursor is now positioned on the last row and false if the ResultSet objectdoes not contain any rows.

8. Registration Example • /register?action=new-user • New user entry form • /register?action=register • Saves new user to database

9. RegisterBean String sql1="insert into user (username, password) values ('"+username+"','"+password+"')"; String sql2="insert into role (username, role) values ('"+username+"','user')"; Class.forName("com.mysql.jdbc.Driver"); Connection con = DriverManager.getConnection(url, user, pass); Statement stmt = con.createStatement(); stmt.executeUpdate(sql1); stmt.executeUpdate(sql2); stmt.close(); con.close();

10. SQL Injection Hacking the Registration Form

11. Normal Input • Username: “Josh” • "insert into role (username, role) values ('"+username+"','user')“ • SQL: insert into role (username, role) values (‘Josh','user')

12. SQL Injection • Username: “Josh’,’admin’) -- “ • "insert into role (username, role) values ('"+username+"','user')“ • SQL: insert into role (username, role) values (‘Josh',’admin’) -- 'user')

13. Prepared Statements sql="insert into user (username, password) values (?,?)”; PreparedStatementpstmt = con.prepareStatement(sql); pstmt.setString(1, username); pstmt.setString(2, password); pstmt.executeUpdate();

14. Transactions try { con.setAutoCommit(false); PreparedStatementpstmt = con.prepareStatement(sql1); pstmt.setString(1, username); pstmt.setString(2, password); pstmt.executeUpdate(); pstmt = con.prepareStatement(sql2); pstmt.setString(1, username); pstmt.executeUpdate(); con.commit(); } catch ( SQLException e ) { con.rollback(); }

15. Hibernate Database Abstraction Layer

16. Object Relational Mapping (ORM)

17. Hibernate Setup • Download jar from http://www.hibernate.org/downloads • Create the Java Objects • Create Mapping Files • Create the Hibernate Configuration File • Create a Session management class

18. Create the Java Objects

19. Create Mapping Files <hibernate-mapping> <class name="TestBean" table="Test"> <id name="id" column="test_id"> <generator class="increment"/> </id> <property name="title" column="test_title"/> <property name="description" column="test_description"/> </class> </hibernate-mapping>

20. Defining Relationships • In TestBean: <set name="questions" cascade="save-update" inverse="true" table="TestQuestion"> <key> <column name="test_id" not-null="true" /> </key> <one-to-many class="TestQuestion" /> </set> • In TestQuestionBean: <many-to-one name="test" class="Test”> <column name="test_id" not-null="true" /> </many-to-one>

21. Hibernate Configuration File • This defines the database configuration • Hibernate will work with many different database types including: • MySQL • HSQL DB • Oracle • MS SQL Server

22. Confguration Example <session-factory> <property name="connection.driver_class"> com.mysql.jdbc.Driver</property> <property name="connection.url"> jdbc:mysql://localhost:3306/quizmaster</property> <property name="connection.username">username</property> <property name="connection.password">password</property> <property name="dialect"> org.hibernate.dialect.MySQLDialect</property> <property name="show_sql">true</property> <property name="current_session_context_class">thread</property> <property name="hibernate.hbm2ddl.auto">create</property> <mapping resource="webdev/quizmaster/HibernateMapping.hbm.xml" /> </session-factory>

23. Create a Session Utility Class • Not mandatory but used in most cases • Handles session creation

24. Creating a new Instance Session session = HibernateUtil.getCurrentSession(); session.beginTransaction(); TestBeantBean = new TestBean(); session.save(tBean); session.getTransaction().commit();

25. Getting Hibernate Objects Session session = HibernateUtil.getSessionFactory().openSession(); Transaction trans = session.beginTransaction(); List<TestBean> tests = session.createQuery("from Test as t order by t.test_titleasc").list(); trans.commit(); session.close();

26. Why to use an ORM? • You work with objects in your system (if your system has been designed well). Even if using JDBC, you will end up making some translation layer, so that you transfer your data to your objects. Unless you are extremely good Hibernate will be better at translation than any custom-made solution. • It doesn't deprive you of control. You can control things in very small details, and if the API doesn't have some remote feature - execute a native query and you have it. However: • ORMs do add a small performance overhead, which in some cases can't be ignored. It will depend on your application and whether this overhead is significant enough to outweigh the benefits of using an ORM.

27. Advantages of using Hibernate • Stability - being around for so many years, it lacks any major problems • dictates the standards in the ORM field • Documentation – There are many tutorials, common problem solutions, etc • Powerful - you can translate a very complex object model into a relational model. • Database Support - it has support for any major and medium RDBMS

28. QuizMaster Connecting to the Database

29. Changes • Added JavaBeans for Result, ResultAnswer • Added Hibernate mapping for all Beans • viewTest now allows the user to take the test • saveResult saves score • Added new view for listTests to index • Added new view for listResults to admin • Added TestManager