Scala＋DBMS+Webスカラ座の夜

ScalaからJDBCでMySQLに接続してみました。

JDBCを直接使っているのでJavaで書いているのと処理の流れは同じです。

package Hello

import java.sql.{DriverManager, Connection, Statement, ResultSet,SQLException}

object SqlSelect {
  def main(args : Array[String]) : Unit = {
    try {
        Class.forName("com.mysql.jdbc.Driver").newInstance();
        var con = 
            DriverManager.getConnection("jdbc:mysql://localhost/test?" + 
                                   "user=scott&password=tiger");
        try {
           var stmt = con.createStatement()
           var rs = stmt.executeQuery("SELECT * FROM EMP")
           while (rs.next()){
              print(rs.getString(1) + " ")
              print(rs.getString(2) + " ")
              print(rs.getString(3) + " ")
              print(rs.getString(4) + " ")
              print(rs.getString(5) + " ")
              print(rs.getString(6) + " ")
              println(rs.getString(7))
          }
          stmt.close()
      } catch {
         case e:SQLException => println("Database error "+e)

         case e => {
           println("Some other exception type:")
           e.printStackTrace()
         }
      } finally {
         con.close()
      }
   } catch {
      case e:SQLException => println("Database error "+e)
      case e => {
        println("Some other exception type:")
        e.printStackTrace()
     }
  }
 }
}

このプログラムをテストするにはMySQLデータベースの環境が必要です

MySQLにユーザーのscottを作るなら

grant select,insert,delete,update,create,drop,file,alter,index on *.* to scott identified by 'tiger';

EMP表も作ってしまおうということなら、

CREATE TABLE EMP (
 EMPNO     INT NOT NULL PRIMARY KEY,
 ENAME     VARCHAR(10),
 JOB          VARCHAR(9),
 MGR         INT,
 HIREDATE DATE,
 SAL           INT,
 COMM       INT,
 DEPTNO    INT
);

DEPT表も作ります。

CREATE TABLE DEPT (
 DEPTNO    INT NOT NULL PRIMARY KEY,
 DNAME     VARCHAR(14),
 LOC       VARCHAR(13)
);

データをINSERTするなら、

insert into EMP values (7369, 'SMITH', 'CLERK'    ,7902 ,'1980-12-17' , 800  ,null, 20);
insert into EMP values (7499, 'ALLEN', 'SALESMAN' ,7698 ,'1981-02-20' , 1600 ,300 , 30);
insert into EMP values (7521, 'WARD',  'SALESMAN' ,7698 ,'1981-02-22' , 1250 ,500 , 30);
insert into EMP values (7566, 'JONES', 'MANAGER'  ,7839 ,'1981-04-02' , 2975 ,null, 20);
insert into EMP values (7654, 'MARTIN','SALESMAN' ,7698 ,'1981-09-28' , 1250 ,1400, 30);
insert into EMP values (7698, 'BLAKE', 'MANAGER'  ,7839 ,'1981-05-01' , 2850 ,null, 30);
insert into EMP values (7782, 'CLARK', 'MANAGER'  ,7839 ,'1981-06-09' , 2450 ,null, 10);
insert into EMP values (7788, 'SCOTT', 'ANALYST'  ,7566 ,'1987-04-19' , 3000 ,null, 20);
insert into EMP values (7839, 'KING',  'PRESIDENT',null ,'1981-11-17' , 5000 ,null, 10);
insert into EMP values (7844, 'TURNER', 'SALESMAN' ,7698 ,'1981-09-08' , 1500 ,0   , 30);
insert into EMP values (7876, 'ADAMS', 'CLERK'    ,7788 ,'1987-05-23' , 1100 ,null, 20);
insert into EMP values (7900, 'JAMES', 'CLERK'    ,7698 ,'1981-12-03' , 950  ,null, 30);
insert into EMP values (7902, 'FORD',  'ANALYST'  ,7566 ,'1981-12-03' , 3000 ,null, 20);
insert into EMP values (7934, 'MILLER','CLERK'    ,7782 ,'1982-01-23' , 1300 ,null, 10);
commit;

mysql> select * from EMP;
+-------+--------+-----------+------+------------+------+------+--------+
| EMPNO | ENAME  | JOB       | MGR  | HIREDATE   | SAL  | COMM | DEPTNO |
+-------+--------+-----------+------+------------+------+------+--------+
|  7369 | SMITH  | CLERK     | 7902 | 1980-12-17 |  800 | NULL |     20 |
|  7499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 | 1600 |  300 |     30 |
|  7521 | WARD   | SALESMAN  | 7698 | 1981-02-22 | 1250 |  500 |     30 |
|  7566 | JONES  | MANAGER   | 7839 | 1981-04-02 | 2975 | NULL |     20 |
|  7654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 | 1250 | 1400 |     30 |
|  7698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 | 2850 | NULL |     30 |
|  7782 | CLARK  | MANAGER   | 7839 | 1981-06-09 | 2450 | NULL |     10 |
|  7788 | SCOTT  | ANALYST   | 7566 | 1987-04-19 | 3000 | NULL |     20 |
|  7839 | KING   | PRESIDENT | NULL | 1981-11-17 | 5000 | NULL |     10 |
|  7876 | ADAMS  | CLERK     | 7788 | 1987-05-23 | 1100 | NULL |     20 |
|  7900 | JAMES  | CLERK     | 7698 | 1981-12-03 |  950 | NULL |     30 |
|  7902 | FORD   | ANALYST   | 7566 | 1981-12-03 | 3000 | NULL |     20 |
|  7934 | MILLER | CLERK     | 7782 | 1982-01-23 | 1300 | NULL |     10 |
+-------+--------+-----------+------+------------+------+------+--------+
13 rows in set (0.00 sec)

DEPT表へINSERT

insert into DEPT values (10,'ACCOUNTING','NEW YORK');
insert into DEPT values (20,'RESEARCH','DALLAS');
insert into DEPT values (30,'SALES','CHICAGO');
insert into DEPT values (40,'OPERATIONS','BOSTON');
commit;