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

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

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

※本構成は「HSQLDB」のJDBC

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) {
　エラー処理
}

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']

主キーでの取得
$result = $this->Model-&gtget($id)
$result = $this->Model->get($id, [
　　'contain' => 'モデル名'
])

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

$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'];
　　;

//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

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)

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();

【失敗補足】
use Cake\ORM\Exception\PersistenceFailedException;
try
{
　$entity = $this->モデル->get($id);
　$entity = $this->モデル->patchEntity($entity, $this->request->getData());
　$this->UserPlus->saveOrFail($entity);
} catch (PersistenceFailedException $e) {
　$this->log($e);
}

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

use Cake\Datasource\ConnectionManager;

$this->conn = ConnectionManager::get('default');

$this->conn->begin();

try{

　　$this->conn->execute('INSERT INTO ～');
　　$this->conn->execute('INSERT INTO ～');

　　$this->conn->commit();

}catch(\Exception $e){
　　～
　　$this->conn->rollback();
}

use Illuminate\Support\Facades\DB;

DB::beginTransaction();

try{
　　DB::insert('INSERT INTO ～', [ ～ ]);
　　DB::insert('INSERT INTO ～', [ ～ ]);

　　DB::commit();

}catch(\Exception $e){
　　～
　　DB::rollBack();
}

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();
　　}
　}
}