This is an incidental study memo for acquiring the Gold qualification of Java8.
It seems that there is no try-with-resource mechanism in Scala, so the latter half describes how to implement it in Scala.
Java
Create a connection management class.
import java.sql.*;
/**
*DB connection acquisition class
*/
public class DbConnector {
public static Connection getConnect() throws SQLException {
String url = "jdbc:mysql://localhost/golddb";
String user = "username";
String password = "password";
Connection connection = DriverManager.getConnection(url, user, password);
return connection;
}
}
Connect using try-with-resource.
The basic points are as follows.
Connection object and get a connectionStatement object using the connection.Statement object is used, and the result is stored in the ResultSet` object.import java.sql.*;
public class JDBCExecuteQuerySample {
public static void main(String[] args) {
String sql = "SELECT dept_name FROM department";
try (Connection connection = DbConnector.getConnect();
Statement stmt = connection.createStatement()) {
ResultSet rs = stmt.executeQuery(sql);
if (rs != null) {
System.out.println("rs != null");
}
while (rs.next()) {
System.out.println("dept_name : " + rs.getString(1));
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
import java.sql.*;
public class JDBCExecuteUpdateSample {
public static void main(String[] args) {
try (Connection connection = DbConnector.getConnect();
Statement stmt = connection.createStatement()) {
String sql =
"INSERT INTO department VALUES (6 , 'Plannning', 'Yokohama', '909-000-0000')";
int col = stmt.executeUpdate(sql);
System.out.println("col : " + col);
} catch (SQLException e) {
System.out.println(e.getMessage());
e.printStackTrace();
}
}
}
import java.sql.*;
public class JDBCExecuteSample {
public static void main(String[] args) {
try (Connection connection = DbConnector.getConnect();
Statement statement = connection.createStatement()) {
String[] sqls = {
//"insert into department values " + "(7, 'Planning', 'Yokohama', '055-555-5555')",
"select dept_name from department where dept_code = 2"
};
for (String sql : sqls) {
//The return value of the execute method is boolean
boolean isResultSet = statement.execute(sql);
if (isResultSet) { //In case of select, the result of isResultSet is true.
//When executed by execute, the object of ResultSet
// getResultSet()Get with method
ResultSet rs = statement.getResultSet();
rs.next();
System.out.println(rs.getString(1));
} else { //isResultSet is false for insert
int count = statement.getUpdateCount();
System.out.println(count);
}
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
PreparedStatement
In most cases, use PreparedStatement instead of Statement to prevent SQL injection.
import java.sql.*;
public class JDBCPreparedStatementSample {
public static void main(String[] args) {
String sql = "SELECT dept_code, dept_name FROM department WHERE dept_name = ?";
try (Connection connection = DbConnector.getConnect();
PreparedStatement statement = connection.prepareStatement(sql)) {
// ?Set the part of and execute.
statement.setString(1, "Education");
ResultSet resultSet = statement.executeQuery();
resultSet.next();
System.out.format("dept_code: %d, dept_name: %s",
resultSet.getInt(1), resultSet.getString(2));
} catch (SQLException e) {
e.printStackTrace();
}
}
}
In addition to using the ResultSet object in forward mode and read-only, you can also use the following functions.
| Constant name | Description |
|---|---|
| CONCUR_READ_ONLY | Concurrency mode for ResultSet objects that cannot be updated |
| CONCUR_UPDATABLE | Concurrency mode for updatable ResultSet objects |
| TYPE_FORWARD_ONLY | The type of ResultSet object in which the cursor moves only forward |
| TYPE_SCROLL_INSENTIVE | A type of ResultSet object that is scrollable but does not reflect changes made to the data in the database |
| TYPE_SCROLL_SENSITIVE | ResultSet object type that is scrollable and reflects the latest contents of the database |
To use it, specify a constant in the argument of the createStatement method as shown below.
Statement stmt = connection.createStatement(
ResultSet.TYPE_SCROLL_INSENSITIVE, //Specify not to change to the database in the forward or reverse direction
ResultSet.CONCUR_READ_ONLY //Cannot be updated, specify as reference only
)
[Notes]
It also depends on the JDBC (Oracle, PostgreSQL, MySQL, etc.) implementation implemented by the DB product.
For example, in the case of MySQL, the JDBC driver (mysql-connector-java-5.1.42.jar) only supported TYPE_SCROLL_INSENSITIVE.
Even if specified, it becomes an implicitly scrollable ResultSet object.
import java.sql.*;
public class JDBCGetMetaDataSample {
public static void main(String[] args) {
try (Connection connection = DbConnector.getConnect()) {
DatabaseMetaData metaData = connection.getMetaData();
System.out.println("TYPE_SCROLL_SENSITIVE: " + metaData.supportsResultSetType(
ResultSet.TYPE_SCROLL_SENSITIVE));
System.out.println("TYPE_SCROLL_INSENSITIVE: " + metaData.supportsResultSetType(
ResultSet.TYPE_SCROLL_INSENSITIVE));
System.out.println("TYPE_FORWARD_ONLY: " + metaData.supportsResultSetType(
ResultSet.TYPE_FORWARD_ONLY));
System.out.println("CONCUR_READ_ONLY: " + metaData.supportsResultSetType(
ResultSet.CONCUR_READ_ONLY));
System.out.println("CONCUR_UPDATABLE: " + metaData.supportsResultSetType(
ResultSet.CONCUR_UPDATABLE));
} catch (SQLException e) {
e.printStackTrace();
}
}
}
TYPE_SCROLL_SENSITIVE: false
TYPE_SCROLL_INSENSITIVE: true
TYPE_FORWARD_ONLY: false
CONCUR_READ_ONLY: false
CONCUR_UPDATABLE: false
mysql> select * from department;
+-----------+-------------+--------------+--------------+
| dept_code | dept_name | dept_address | pilot_number |
+-----------+-------------+--------------+--------------+
| 1 | Sales | Tokyo | 03-3333-xxxx |
| 2 | Engineer | Yokohama | 045-444-xxxx |
| 3 | Development | Osaka | NULL |
| 4 | Marketing | Fukuoka | 092-222-xxxx |
| 5 | Education | Tokyo | NULL |
| 6 | Plannning | Yokohama | 909-000-0000 |
| 7 | Planning | Yokohama | 055-555-5555 |
+-----------+-------------+--------------+--------------+
7 rows in set (0.00 sec)
Try the cursor movement method for these.
import java.sql.*;
public class JDBCCursorMoveSample {
public static void main(String[] args) {
//Sort in ascending order to make the results easier to understand.
String sql = "SELECT dept_code, dept_name FROM department ORDER BY dept_code";
try (Connection con = DbConnector.getConnect();
Statement stmt = con.createStatement(
ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_UPDATABLE);
ResultSet rs = stmt.executeQuery(sql)) {
//Move cursor to last line
rs.absolute(-1);
System.out.format("cursor: %d, dept_code: %d, dept_name: %s\n",
rs.getRow(), rs.getInt(1), rs.getString(2));
//Move cursor to the beginning
rs.absolute(1);
System.out.format("cursor: %d, dept_code: %d, dept_name: %s\n",
rs.getRow(), rs.getInt(1), rs.getString(2));
//Move cursor to the last line
rs.last();
System.out.format("cursor: %d, dept_code: %d, dept_name: %s\n",
rs.getRow(), rs.getInt(1), rs.getString(2));
//Move the cursor to the line following the last line
rs.afterLast();
System.out.format("cursor: %d\n", rs.getRow());
//Move cursor to the beginning
rs.first();
System.out.format("dept_code: %d, dept_name: %s\n",
rs.getInt(1), rs.getString(2));
//Move the cursor to the line before the beginning
rs.beforeFirst();
System.out.format("cursor: %d\n", rs.getRow());
//Move to the next line after the last line and then scroll in the opposite direction
rs.afterLast();
System.out.println("Output result by reverse scrolling----");
while (rs.previous()) { //Reverse scroll
System.out.format("dept_code: %d, dept_name: %s\n",
rs.getInt(1), rs.getString(2));
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
Scala
There is no such thing as try-with-resources in Scala, so
Implement it yourself. ʻUse` method is defined and used.
First, we will prepare a singleton object that implements the using method.
The function f uses the resource to do something and
When the process is completed, the close () method is executed.
object LoanPattern {
/**
*using method
*Processing to close when processing is completed
*Java try-catch-Alternative method of resource
*
* @Method to call close with param resource finally
* @param f Process to be executed using the argument resource
* @A type that has a tparam A close method
* @return value of tparam B function f
*/
def using[A <: {def close() : Unit}, B](resource:A)(f:A=>B): B = {
try {
f(resource) //Processing execution
} finally {
if (resource != null) resource.close()
}
}
Holds the value obtained in the case class.
case class UserAccount(id: Long, firstName: String, lastName: String)
DAO trait. If you want to change the repository for RDB or KVS, inherit this trait.
trait UserDao {
//Get all users
def getUsers(): Seq[UserAccount]
//Get user by id
def getById(id: Long): Option[UserAccount]
}
Implementation class. This time is MySQL.
/**
*UserDao implementation class
*Connect to MySQL.
*/
class UserDaoOnMySQL extends UserDao {
import java.sql._
import scala.collection.mutable.ArrayBuffer
import LoanPattern.using
override def getUsers(): Seq[UserAccount] = {
using(getConnection()) { dbResource =>
val stmt = dbResource.createStatement()
val rs = stmt.executeQuery("select * from customers")
val arrayBuffer = ArrayBuffer[UserAccount]()
while (rs.next()) {
arrayBuffer += UserAccount(
rs.getInt("id"),
rs.getString("first_name"),
rs.getString("last_name"))
}
arrayBuffer.toList
}
}
override def getById(id: Long): Option[UserAccount] = {
using(getConnection()) { dbResource =>
val stmt = dbResource.createStatement()
val rs = stmt.executeQuery(s"select * from customers where id = ${id}")
val arrayBuffer = ArrayBuffer[UserAccount]()
while (rs.next()) {
arrayBuffer += UserAccount(
rs.getInt("id"),
rs.getString("first_name"),
rs.getString("last_name"))
}
arrayBuffer.find(_.id == id)
}
}
private def getConnection() =
DriverManager.getConnection("jdbc:mysql://localhost/db", "username", "password")
}
The user side.
object SampleLoanPatternApp extends App {
val dao = new UserDaoOnMySQL
println(dao.getUsers())
println(dao.getById(1))
}
Recommended Posts