プログラム言語 データベース操作

設定

JDBCドライバを用意
JAVA 重要用語/JDBC

JDBCドライバに対してパスを通す
JAVA 重要用語/CLASSPATH」参照
java_db1

java_db3

※本構成は「HSQLDB」のJDBC

DB接続

using System.Data.SqlClient;

//接続文字列
var dbInfo = "";
dbInfo += @"Data Source=(サーバー名);";
dbInfo += @"Initial Catalog=(データベース名);";
//Windows認証時
dbInfo += @"Integrated Security=True";

//接続オブジェクト
SqlConnection cn = new SqlConnection(connectionString:dbInfo);
cn.Open();
 ~
cn.Close();

Imports System.Data.SqlClient

‘接続文字列
Dim dbInfo As String = ""
dbInfo &= "Data Source=(サーバー名);"
dbInfo &= "Initial Catalog=(データベース名);"
‘Windows認証時
dbInfo &= "Integrated Security=True"
‘接続オブジェクト
Dim cn As New SqlConnection(connectionString:=dbInfo)
Call cn.Open()
 ~
Call cn.Close()

‘SQL SERVERの時
Dim cn As ADODB.Connection
Set cn = New ADODB.Connection

cn.Provider = "SQLOLEDB"

‘データソース名
cn.Properties("Data Source").Value = "127.0.0.1"

‘ユーザID
cn.Properties("User ID").Value = "sa"

‘パスワード
cn.Properties("Password").Value = "pass"

‘コネクション文字列
cn.ConnectionString = ""

cn.Open

サーバ名
cn.DefaultDatabase = "MyDB"

import java.sql.Connection;
import java.sql.Driver;
import java.sql.DriverManager;
import java.sql.Statement;

Class.forName("oracle.jdbc.driver.OracleDriver");
JDBC4.0以降は不要

Connection cn = DriverManager.getConnection("jdbc:oracle:thin@servername:1521:sid,user,pass");

Statement state = cn.createStatement();

if ( cn != null){cn.close(); cn = null;}
if ( state != null){state.close(); state = null;}


$host = 'localhost';
$port = '9999';
$db = 'test';
$user = 'user';
$pass = 'pass';
$conn = pg_connect("host=${host} port=${port} dbname=${db} user=${user} pass=${pass}");

if (!$conn) {
 エラー処理
}

読み込み(SELECT)

配列への一括取得

SqlDataAdapter adapter = new SqlDataAdapter(selectCommandText: "SELECT * FROM dbo.myTable1", selectConnection: cn);
DataSet myDataset = new DataSet();
adapter.Fill(dataSet: myDataset);
//adapter.Fill(dataTable: myDatatable); も可

foreach (DataRow myRow in myDataset.Tables[0].Rows)
{
  string myRecord = myRow["field1"].ToString() + ":" + myRow["field2"].ToString();
};

【SqlDataAdapter(データセットへの条件付き一括読み込み)】
//Where句
string strwhere = "field1 >= '3'";
//OrderBy句
string strsort = "field1, field2";

foreach (DataRow myRow in myDataset.Tables[0].Select(filterExpression: strwhere, sort: strsort, recordStates: DataViewRowState.Added))
{
  string myRecord = myRow["field1"].ToString() + ":" + myRow["field2"].ToString();
};

//Datasetに関して。
//初期値と比較して変更されたレコード等を抽出できる

//DataViewRowState.Added
//新しい行。

//DataViewRowState.CurrentRows
//変更されていない行、新しい行、および変更された行を含む現在の行。

//DataViewRowState.Deleted
//削除された行。
//DataViewRowState.ModifiedCurrent
//元のデータの変更後のバージョンである現在のバージョン。

//DataViewRowState.ModifiedOriginal
//元のバージョン。既に変更されていてModifiedCurrent として使用できます。

//DataViewRowState.None
//なし。

//DataViewRowState.OriginalRows
//変更されていない行と削除された行を含む元の行。

//DataViewRowState.Unchanged
//変更されていない行。

Dim adapter As New SqlDataAdapter(selectCommandText:="SELECT * FROM dbo.myTable1", selectConnection:=cn)
Dim myDataset As New DataSet
Call adapter.Fill(dataSet:=myDataset)
‘Call adapter.Fill(dataTable:=myDatatable) も可

For Each myRow As DataRow In myDataset.Tables(0).Rows
  Dim strRecord As String = myRow.Item("field1").ToString & ":" & myRow.Item("field2").ToString
Next myRow

【SqlDataAdapter(データセットへの条件付き一括読み込み)】
‘Where句
Dim strwhere As String = "field1 >= '3'"
‘OrderBy句
Dim strsort As String = "field1, field2"

For Each myRow As DataRow In myDataset.Tables(0).Select(filterExpression:=strwhere, sort:=strsort, recordStates:=DataViewRowState.Added)
  dim myRecord As String = myRow["field1"].ToString() + ":" + myRow["field2"].ToString()
Next

‘Datasetに関して。
‘初期値と比較して変更されたレコード等を抽出できる

‘DataViewRowState.Added
‘新しい行。
‘DataViewRowState.CurrentRows
‘変更されていない行、新しい行、および変更された行を含む現在の行。

‘DataViewRowState.Deleted
‘削除された行。

‘DataViewRowState.ModifiedCurrent
‘元のデータの変更後のバージョンである現在のバージョン。

‘DataViewRowState.ModifiedOriginal
‘元のバージョン。既に変更されていてModifiedCurrent として使用できます。

‘DataViewRowState.None
‘なし。

‘DataViewRowState.OriginalRows
‘変更されていない行と削除された行を含む元の行。

‘DataViewRowState.Unchanged
‘変更されていない行。

$query = pg_query("select * from ~");
if (!$query) {
 エラー処理
}

$results = pg_fetch_all($query);

foreach ($results as $result) {
 $col1 = $result['col1'];
 $col2 = $result['col2'];
}

$fields$ = array(
 列1',
 列2',
);
$conditions$ = array(
 '表1.列1' => 値1,
 '表1.列2 >' => ~,
否定
'NOT' => array(
  '表1.列1 >' => ~,
 ),
);
$order = array(
 '列1' => 'desc',
 '列2' => 'asc',
);
$joins = array(
  array(
   'type' => 'inner', right/left
   'table' => '物理名',
   'alias' => 'モデル名',
   'conditions' => array(
    '表2.列 = 表1.列',
   ),
  ),
 );

$data = $this->モデル名->find(
  'all', //first
  array(
   'fields' => $fields,
   'joins' => $joins,
   'conditions' => $conditions
   'order' => $order
  )
 );

$result = $this->Model->find()

->select(["col1", "col2"])

->where(["col1 is" => null])
->where(["col1 like" => '%aa%'])
->where(["col1" => '5'])
->where(['col1' => 1, 'OR' => [['col2 LIKE' => '%太郎%']), ['col3 LIKE' => '%花子%']]])
※col1 = 1 AND (col2 LIKE '%太郎%' OR col3 LIKE '%花子%')

->order(['col1' => 'ASC'])
->order(['col2' => 'DESC'])

->group(['col1'])
->having(['col2 >' => 100])

->limit(100)
->offset(10)

->all();
->first();
->last();

->contain(['table1']

【SQL実行】
use Cake\Datasource\ConnectionManager;
$conn = ConnectionManager::get('default');
$result = $conn->execute('SELECT * FROM ~')->fetchAll('assoc')
戻り値は配列になるので、通常のpagenate処理は行えない(単一データ向け)

SQL関数を使用

$query = $this->Model->find();
$result = $query
  ->select([
    'col1' => $query->func()->sum('col1'),
    'col2' => $result->func()->count('col2'),
  ])
  ;

結果の存在チェック

find()結果の存在チェック
if (count($this->Model->find()->toArray())) {
 
}

生の値を取得

クエリでなく生データを取得
$result = $this->Model->find();
$count = $result
  ->select(['count' => $result->func()->sum('count')])
  ->hydrate(false)
  ->toList()[0]['count'];
  ;

SqlDataReader(順方向限定読み込み)

//SQL命令
SqlCommand cmd = new SqlCommand(cmdText:"SELECT * FROM dbo.myTable1",connection:cn);

//データアクセス①SqlDataReader(順方向限定読み込み)
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read()){
  string myRecord = reader["field1"].ToString() + ":" + reader["field2"].ToString();
};
reader.Close();

‘SQL命令
Dim cmd As New SqlCommand(cmdText:="SELECT * FROM dbo.myTable1", connection:=cn)

Dim reader As SqlDataReader = cmd.ExecuteReader()
Do While reader.Read()
  Dim strRecord As String = reader("field1").ToString & ":" & reader("field2").ToString
Loop
reader.Close()

Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset

Set cn = New ADODB.Connection
Set rs = New ADODB.Recordset

‘SQL文実行
cn.Execute("SELECT * FROM ~")

re.Open ~

Do Until rs.EOF
  Debug.Print(re.Fields("fld"))
  re.MoveFirst
  re.MoveNext
  re.MovePreve
  re.MoveLast
Loop

import java.sql.Connection;
import java.sql.Driver;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

Class.forName("oracle.jdbc.driver.OracleDriver");
Connection cn = DriverManager.getConnection("jdbc:oracle:thin@servername:1521:sid,user,pass");
Statement state = cn.createStatement();

ResultSet result = state.executeQuery("SELECT * FROM EMP");

while(result.next())
{
 String col1 = result.getString(0);
 String col2 = result.getString(1);
 String col3 = result.getString(2);
}

DBの型 GETTER
CHAR getString()
VARCHAR getString()
NUMBER getBigDecimal()
INTEGER getInt()
INT getInt()
DOUBLE getDouble()
DATE getDate()
TIME getTime()
TIMESTAMP getTimeStamp()

Statement state = cn.createStatement(読み書き指定, 機能);

第一引数 内容 規定
ResultSet.CONCUR_READ_ONLY
ResultSet.CONCUR_UPDATABLE

第二引数 内容 規定
ResultSet.TYPE_FORWARD_ONLY
ResultSet.TYPE_SCROLL_INSENSITIVE
ResultSet.TYPE_SCROLL_SENSITIVE

ExecuteScalar(単一の戻り値を取得する読み込み)

cmd = new SqlCommand(cmdText: "SELECT COUNT(field1) FROM dbo.myTable1", connection: cn);
int cnt = (int)cmd.ExecuteScalar();
cmd = New SqlCommand(cmdText:="SELECT COUNT(field1) FROM dbo.myTable1", connection:=cn)
Dim count As Integer = DirectCast(cmd.ExecuteScalar(), Integer)

更新(INSERT/UPDATE/DELETE)

cmd = new SqlCommand(cmdText: "DELETE * FROM dbo.myTable1", connection: cn);
cmd.ExecuteNonQuery();

cmd = New SqlCommand(cmdText:="DELETE * FROM dbo.myTable1", connection:=cn)
Call cmd.ExecuteNonQuery()

Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset

Set cn = New ADODB.Connection
Set rs = New ADODB.Recordset

strSQL = "SELECT * FROM 表名 WHERE 列名 ='~' "
rs.Open strSQL, cn, adOpenForwardOnly, adLockReadOnly, adCmdText
If Not rs.EOF Then
 rs!列2 = "~"
 rs.Update '更新
ELSE
rs.AddNew '新規レコード追加
 rs!列1 = "~"
 rs!列2 = "~"
 rs!列3 = "~"
 rs.Update '挿入
End If

rs.Delete '削除

import java.sql.Connection;
import java.sql.Driver;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;

Class.forName("oracle.jdbc.driver.OracleDriver");
Connection cn = DriverManager.getConnection("jdbc:oracle:thin@servername:1521:sid,user,pass");
Statement state = cn.createStatement();

int ret = state.executeUpdate("UPDATE ~");

boolean ret = state.execute("UPDATE ~");

$query = pg_query("insert ~");
$query = pg_query("update ~");
$query = pg_query("delete ~");
if (!$query) {
 エラー処理
}

一括INSERT
$data = [
 'col1' => 1,
 'col2' => 'aaa',
 'col3' => '{"col3_1":1, "col3_2":"aaa"}',
];

$res = pg_insert( 接続情報, INSERT先テーブル名, INSETデータ配列);
接続情報は「DB接続」参照
$res = pg_insert( $conn, 'test', $data);

挿入/更新
$update_data = array();
$update_data['列1'] = ~;
$update_data['列2'] = ~;
更新対象モデルの初期化
$this->モデル名->create(update_data);
更新(キーが重複していればUPDATE/そうでなければINSERT)
$this->モデル名->save($update_data, array('validate' => false));

削除
$delete_data = array();
$delete_data['列1'] = ~;
$delete_data['列2'] = ~;
$this->モデル名->deleteAll($delete_data, false);

【Insert】
方法①
$data = $this->Models->newEntity();
$data->col1 = 値;
$data->col2 = 値;
更新
if ($this->Models->save($data)) {
 成功時
}

方法②
$this->Models->query()
 ->insert(['col1', 'col2', 'col3']);
 ->values([
   'col1' => ~,
   'col2' => ~,
   'col3' => ~,
 ])
 ->execute();

【Update】
方法①
$data = $this->Models->get($id);
$data = $this->Models->patchEntity($data, $this->request->getData());
更新
if ($this->Models->save($data)) {
 成功時
}

$this->Models->updateAll(['col1'] => '1', ['col2'] => '2');

方法②
$this->Models->query()
  ->update()
  ->set(['count' => ++$count])
  ->where(['~' => ~])
  ->execute();
  ;

【Delete】
$this->Models->query()
  ->delete()
  ->where(['col1' => ~])
  ->execute();

DataTable型変数

DataSet/DataTable型変数については「プログラム言語 変数/DataTable型」参照

トランザクション処理

using System.Data.SqlClient;

SqlConnection cn = new SqlConnection(connectionString: @"~");
SqlTransaction trn = cn.BeginTransaction(transactionName: @"~");

try
{
  SqlCommand cmd = new SqlCommand(cmdText: @"INSERT ~", connection: cn, transaction: trn);
  cmd.ExecuteNonQuery();

  // ↑ で実行したSQLを確定させる
  trn.Commit();
}
catch (Exception ex)
{
  // ↑ で実行したSQLを戻す
  trn.Rollback();
}

import java.sql.*;

Connection connection=DriverManager.getConnection(~);

トランザクション開始
connection.setAutoCommit(false);

コミット
connection.commit();

ロールバック
connection.rollback();

元のモードに戻す
connection.setAutoCommit(true);

Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Set cn = New ADODB.Connection
Set rs = New ADODB.Recordset

cn.BiginTrans

Call rs.Open("Insert ~", cn)
Call rs.Open("Update ~", cn)
Call rs.Open("Delete ~", cn)

cn.CommitTrans
cn.RollbackTrans

DAO/DTOパターン

DAO
Data Access Object

DTO
Data Transfer Object
model」参照

model
package myPack;

public class Account {
 private int _id;
 private String _name;
 private int _money;
 
 public int getId(){
  return this._id;
 }
 public void setId(int id){
  this._id = id;
 }
 
 public String getName(){
  return this._name;
 }
 public void setName(String name){
  this._name = name;
 }
 
 public int getMoney(){
  return this._money;
 }
 public void setMoney(int money){
  this._money = money;
 }
}

package myPack;
import ~;

public class AccountDAO {

 public List findAll(){
  String s = "SELECT * FROM ACCOUNT;";
  return new DBManager().find(s);
 }

 public int insert(Account account){
  String s = "INSERT INTO ACCOUNT "
    + "VALUES("
    + account.getId() + ","
    + account.getName() + ","
    + account.getMoney()
    + ");";
  return new DBManager().insert(s);
 }
}

※DAOから呼び出されるSQL実行処理
package myPack;
import ~;

public class DBManager {

 public List find(String sql) {
 
  Connection con = null;
  Statement state = null;
  List list = null;
  
  try{
   con = ConnectService.getConnection();
   state = con.createStatement();
   con.setAutoCommit(false);

   list.add(new AccountMapping().createFromResultSet(state.executeQuery(sql)));
  }catch(Exception e){
   e.printStackTrace();
  }finally{
   try{ state.close(); }catch(Exception e){}
   try{ con.close(); }catch(Exception e){}
  }

 
  return list;
 }

 public int insert(String sql){

  Connection con = null;
  Statement state = null;
  List list = null;

  try{
   con = ConnectService.getConnection();
   state = con.createStatement();
   con.setAutoCommit(false);

   return state.executeUpdate(sql);
  }catch(Exception e){
   e.printStackTrace();
  }finally{
   try{ state.close(); }catch(Exception e){}
   try{ con.close(); }catch(Exception e){}
  }

 }
}

DAOの利用
※InsertService.java
package myPack;
import ~;

@WebServlet("/InsertService")
public class SelectService extends HttpServlet {

 protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {

  try{
   Account account = new Account();
   account.setId(4);
   account.setName("C++");
   account.setMoney(1000);
   int result = new AccountDAO().insert(account);

   List list = new AccountDAO().findAll();

   request.setAttribute("list", list);
   request.getRequestDispatcher("/select.jsp").forward(request, response);

  }catch(Exception e){
   e.printStackTrace();
  }

 }
}