プログラム言語 データベース操作
目次
設定
DB接続
//接続文字列
var dbInfo = "";
dbInfo += @"Data Source=(サーバー名);";
dbInfo += @"Initial Catalog=(データベース名);";
//Windows認証時
dbInfo += @"Integrated Security=True";
//接続オブジェクト
SqlConnection cn = new SqlConnection(connectionString:dbInfo);
cn.Open();
～
cn.Close();
‘接続文字列
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()
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.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)
配列への一括取得
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 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
‘変更されていない行。
if (!$query) {
エラー処理
}
$results = pg_fetch_all($query);
foreach ($results as $result) {
$col1 = $result['col1'];
$col2 = $result['col2'];
}
列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
)
);
->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->get($id)
$result = $this->Model->get($id, [
'contain' => 'モデル名'
])
【SQL実行】
use Cake\Datasource\ConnectionManager;
$conn = ConnectionManager::get('default');
$result = $conn->execute('SELECT * FROM ～')->fetchAll('assoc')
戻り値は配列になるので、通常のpagenate処理は行えない(単一データ向け)
SQL関数を使用
$result = $query
->select([
'col1' => $query->func()->sum('col1'),
'col2' => $result->func()->count('col2'),
])
;
結果の存在チェック
if (count($this->Model->find()->toArray())) {
～
}
生の値を取得
$result = $this->Model->find();
$count = $result
->select(['count' => $result->func()->sum('count')])
->hydrate(false)
->toList()[0]['count'];
;
SqlDataReader(順方向限定読み込み)
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();
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 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.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(単一の戻り値を取得する読み込み)
int cnt = (int)cmd.ExecuteScalar();
Dim count As Integer = DirectCast(cmd.ExecuteScalar(), Integer)
更新(INSERT/UPDATE/DELETE)
cmd.ExecuteNonQuery();
Call cmd.ExecuteNonQuery()
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.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("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);
方法①
$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);
}
DataTable型変数
DataSet/DataTable型変数については「プログラム言語 変数/DataTable型」参照
トランザクション処理
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();
}
Connection connection=DriverManager.getConnection(～);
トランザクション開始
connection.setAutoCommit(false);
コミット
connection.commit();
ロールバック
connection.rollback();
元のモードに戻す
connection.setAutoCommit(true);
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パターン
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
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
Connection con = null;
Statement state = null;
List
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
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
request.setAttribute("list", list);
request.getRequestDispatcher("/select.jsp").forward(request, response);
}catch(Exception e){
e.printStackTrace();
}
}
}
１週間でできる会員サイト「Limited」
必要最小限。だから分かりやすい。始めやすい。続けやすい。
限定① 安⼼・安全の会員制
限定② 必要最⼩限の機能
限定③ 独⾃機能を追加可能
