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

設定

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

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

ã€å¤±æ•—補足】
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åž‹ã€å‚ç…§

トランザクション処ç†

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

 }
}

Follow me!