JDBC summary

We now have an overview of JDBC. The rest of this chapter will concentrate on writing abstractions that sit above JDBC, making database accesses feel more natural. Before we do this, let's summarize what we have seen so far.

We have used three JDBC classes:

  • The Connection class represents a connection to a specific SQL database. Instantiate a connection as follows:
    import java.sql._
Class.forName("com.mysql.jdbc.Driver")val connection = DriverManager.getConnection(
  "jdbc:mysql://127.0.0.1:3306/test",
  "root", // username when connecting
  "" // password
)

    Our main use of Connection instances has been to generate PreparedStatement objects:

    connection.prepareStatement("SELECT * FROM physicists")
  • A PreparedStatement instance represents a SQL statement about to be sent to the database. It also represents the template for a SQL statement with placeholders for values yet to be filled in. The class exposes the following methods:

    statement.executeUpdate

    This sends the statement to the database. Use this for SQL statements that modify the database and do not return any data, such as INSERT, UPDATE, DELETE, and CREATE statements.

    val results = statement.executeQuery

    This sends the statement to the database. Use this for SQL statements that return data (predominantly, the SELECT statements). This returns a ResultSet instance.

    statement.addBatch

    statement.executeBatch

    The addBatch method adds the current statement to a batch of statements, and executeBatch sends the batch of statements to the database.

    statement.setString(1, "Scala")

    statement.setInt(1, 42)

    statement.setBoolean(1, true)

    Fill in the placeholder values in the PreparedStatement. The first argument is the position in the statement (counting from 1). The second argument is the value.

    One common use case for these is in a batch update or insert: we might have a Scala list of objects that we want to insert into the database. We fill in the placeholders for each object in the list using the .setXXX methods, then add this statement to the batch using .addBatch. We can then send the entire batch to the database using .executeBatch.

    statement.setNull(1, java.sql.Types.BOOLEAN)

    This sets a particular item in the statement to NULL. The second argument specifies the NULL type. If we are setting a cell in a Boolean column, for instance, this should be Types.BOOLEAN. A full list of types is given in the API documentation for the java.sql.Types package (http://docs.oracle.com/javase/7/docs/api/java/sql/Types.html).

  • A ResultSet instance represents a set of rows returned by a SELECT or SHOW statement. ResultSet exposes methods to access fields in the current row:

    rs.getString(i)

    rs.getInt(i)

    These methods get the value of the ith field in the current row; i is measured from 1.

    rs.getString("name")

    rs.getInt("age")

    These methods get the value of a specific field, which is indexed by the column name.

    rs.wasNull

    This returns whether the last column read was NULL. This is particularly important when reading Java value types, such as getInt, getBoolean, or getDouble, as these return a default value when reading a NULL value.

The ResultSet instance exposes the .next method to move to the next row; .next returns true until the ResultSet has advanced to just beyond the last row.

..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset
3.137.217.220