プãƒã‚°ãƒ©ãƒ 言語 データベースæ“作
目次
è¨å®š
「JAVA é‡è¦ç”¨èªž/JDBCã€
JDBCドライãƒã«å¯¾ã—ã¦ãƒ‘スを通ã™
「JAVA é‡è¦ç”¨èªž/CLASSPATHã€å‚ç…§
※本構æˆã¯ã€ŒHSQLDBã€ã®JDBC
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'];
}
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
$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();
}
DB::beginTransaction();
try{
  DB::insert('INSERT INTO ~', [ ~ ]);
  DB::insert('INSERT INTO ~', [ ~ ]);
  DB::commit();
}catch(\Exception $e){
  ~
  DB::rollBack();
}
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();
  }
 }
}