Java – Database access

1. Java – Database access • Java provides a set of classes to access databases • JDBC = Java Database Connectivity classes • Package = java.sql

2. Java – Database access • Load a driver to enable connection to the database • Connect to the database • Perform SQL operations

3. Java – Load a driver • The class Class has a static method forName • void forName( String driverFileName ) throws ClassNotFoundException; • Class.forName( driverStringName ); • For us: • Class.forName(“com.mysql.jdbc.Driver”);

4. Java – Load a driver • Class.forName(“com.mysql.jdbc.Driver”); • Another possible driver is org.gjt.mm.mysql.Driver • Class.forName(“org.gjt.mm.mysql.Driver”); • Typically, those drivers are available free for download

5. Java – Database Connection • Connection interface encapsulates a connection to a database • interface  cannot instantiate an object • DriverManager class has a static method, getConnection, that returns a Connection object • static Connection getConnection( String connectionURLString ) throws SQLException;

6. Java – Database Connection • Connection con = DriverManager.getConnection( connectionURLString ); • // connectionURLString looks like • jdbc:rdbms://databaseHost/databaseName?user=username&password=passwordName • jdbc:mysql://localhost/cs225s13?user=yourUsername&password=yourMySqlPassword

7. Java – Database Connection • May need to add • useJvmCharsetConverters=true • depending on possible incompatibilities between gcj (GNU Compiler for Java), MySql version, and the JDBC MySql driver used • On leda, we need to add the above

8. Java – SQL operation • Use try and catch blocks • In debugging phase, output feedback after each phase • System.out.print( “driver loaded” ); • System.out.print(“connection established”); • System.out.print(“SQL executed”); • Also, output error in catch block

9. Java – Classpath • CLASSPATH environment variable needs to be set • In your .profile file (so that it is persistent) • CLASSPATH=.:javaMySqlDriverPath:$CLASSPATH • For us, the path to the mysql driver is: • /usr/share/java/mysql-connector-java-5.1.5.jar

10. Java – Statement • Statement interface • A Statement object is used to perform sql queries • interface  cannot instantiate an object • Connection interface has a method, createStatement, that returns a Statement object • Statement createStatement( ) throws SQLException; • Statement stmt = create.getStatement( );

11. Java – Statement • The Statement interface has a method, executeUpdate, that executes an sql insert, update, or delete statement and returns an int, the number of rows affected • int executeUpdate( String sql ) throws SQLException; • int rows = stmt.executeUpdate( sql ); • /* sql is a String representing an SQL insert, update, or delete statement */

12. Java – Statement • The Statement interface has a method, executeQuery, that executes an sql select query and returns a ResultSet object • ResultSet executeQuery( String sql ) throws SQLException; • ResultSet rs = stmt.executeQuery( sql ); • // sql is a String representing an SQL query

13. Java – ResultSet • The ResultSet interface has methods to process the results of an sql query • boolean next( ) • /* returns true if there are more rows and move cursor to next row in ResultSet*/ • int getRow( ) • // returns the row number (in the ResultSet)

14. Java – ResultSet • Process result of select query using a while loop • while( rs.next( ) ) • { • }

15. Java – ResultSet • String getString( int columnNumber ) • // column number starts at 1, not 0 • /* returns, as a String, column value of current row of ResultSet */ • String getString( String columnName ) • /* same as above with column name argument, not column number argument */

16. Java – ResultSet • int getInt( int columnNumber ) • // column number starts at 1, not 0 • /* returns, as an int, column value of current row of ResultSet */ • int getInt( String columnName ) • /* same as above with column name argument, not column number argument */ • Other methods for other data types

17. Java – SQL operation • Load a driver to enable connection to the database • Get a Connection object reference • Prepare an SQL statement • Get a Statement object reference • Execute the query • Process the results of the query if necessary