DBアクセス

C#

★接続文字列（ADO.NET）

・書き方１：Data Source

Data Source=localhost;Initial Catalog=Northwind;Persist Security Info=True;User ID=sa;Password=xxx"

Data Source=.\SQLExpress;Initial Catalog=Northwind;Integrated Security=True

Data Source=.;Initial Catalog=Northwind;Integrated Security=SSPI

Provider=Microsoft.Jet.OLEDB.4.0;Data source=C:\DataSource\mydb.mdb Access用

・書き方２：Server

Server=.\SQLExpress;Database=Northwind;UID=sa;PWD=xxx"

Server=.\SQLExpress;Database=Northwind;Trusted_Connection=True

★App.configとWeb.configでの設定

・方法１：appSettings

<appSettings>

<add key="connStr" value="接続文字列"/>

</appSettings>

取得：System.Configuration.ConfigurationManager.AppSettings["connStr"]

・方法２：connectionStrings

<connectionStrings>

<add name="connStr" connectionString="接続文字列" providerName="System.Data.SqlClient" />

</connectionStrings>

取得：System.Configuration.ConfigurationManager.ConnectionStrings["connStr"]

ADO.NET

SqlConnection System.Data.SqlClient

OleDbConnection System.Data.OleDb

OdbcConnection System.Data.Odbc

OracleConnection System.Data.OracleClinet

using (SqlConnection conn = new SqlConnection(@"接続文字列"))

using (SqlCommand cmd = conn.CreateCommand())

{

// 実行タイプを指定

cmd.CommandType = CommandType.StoredProcedure;

cmd.CommandText = "stored_xxx";

// 入出力引数を指定

cmd.Parameters.Add("@tag", SqlDbType.VarChar, 50);

cmd.Parameters.Add("@count", SqlDbType.Int);

cmd.Parameters["@tag"].Value = "aaa";

cmd.Parameters["@count"].Direction = ParameterDirection.Output;

conn.Open();

// ●ExecuteScalar 一行目の一列目を返す SELECT <集計関数>

object result1 = cmd.ExecuteScalar();

// ●ExecuteNonQuery 影響行数を返す UPDATE, INSERT, DELETE

int result2 = cmd.ExecuteNonQuery();

// ●ExecuteReader DataReaderを返す SELECT

using (SqlDataReader dr = cmd.ExecuteReader())

{

while (dr.Read())

{

object id = dr["id"]; // 方法１

//object id = dr.GetValue(0); // 方法２

//object id = dr[0]; // 方法３

object name = dr["name"];

}

}

conn.Close();

// 出力を取得

object output = cmd.Parameters["@count"].Value;

}

Java

★接続文字列（JDBC）

oracle

driverClass oracle.jdbc.driver.OracleDriver

url jdbc:oracle:thin:@//127.0.0.1:1521/XE ServiceName（推薦）

jdbc:oracle:thin@127.0.0.1:1521:dbname SID

jdbc:oracle:thin:@GL TNSName

mysql

driverClass com.mysql.jdbc.Driver org.gjt.mm.mysql.Driver （旧式）

url jdbc:mysql://127.0.0.1:3306/dbname

DB2

driverClass com.ibm.db2.jcc.DB2Driver

url jdbc:db2://127.0.0.1:50000/dbname

sybase

driverClass com.sybase.jdbc.SybDriver

url jdbc:sybase:Tds:127.0.0.1:5007/dbname

PostgreSQL

driverClass org.postgresql.Driver

url jdbc:postgresql://127.0.0.1/dbname

Sql Server2000

driverClass com.microsoft.jdbc.sqlserver.SQLServerDriver

url jdbc:microsoft:sqlserver://127.0.0.1:1433;DatabaseName=dbname

Sql Server2005

driverClass com.microsoft.sqlserver.jdbc.SQLServerDriver

url jdbc:sqlserver://127.0.0.1:1433; DatabaseName=dbname

JDBC

Connection conn = null;

Statement stmt = null; // 引数なし

PreparedStatement pStmt = null; // 引数あり

CallableStatement cStmt = null; // ストアド

ResultSet rs = null;

try {

Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver");

//書き方１

conn = DriverManager.getConnection(

"jdbc:microsoft:sqlserver://127.0.0.1:1433;DatabaseName=dbname;user=sa;password=1234");

//書き方２（推薦）

conn = DriverManager.getConnection(

"jdbc:microsoft:sqlserver://127.0.0.1:1433;DatabaseName=dbname", "sa", "1234");

conn.setAutoCommit(false);

//●引数なしのSQL文

// 戻り値なし

stmt = conn.createStatement();

stmt.execute("INSERT INTO Table1 ...");

// 戻り値あり

rs = stmt.executeQuery("SELECT * FROM Table1");

while(rs.next()){

String name = rs.getString("Name");

...

}

//●引数ありのSQL文

pStmt = conn.prepareStatement("INSERT INTO book VALUES(?,?)");

pStmt.setString(1, "bookid");

pStmt.setString(2, "bookname");

// 日付の場合

Calendar today = Calendar.getInstance();

java.sql.Date sqlDate = new java.sql.Date(today.getTimeInMillis());

pStmt.setDate(1,sqlDate);

pStmt.executeUpdate();

//●ストアド

cStmt = conn.prepareCall("{?=call stored_xxx(?,?)}");

cStmt.registerOutParameter(1, Types.INTEGER);

cStmt.setString(2, "bookid");

cStmt.setString(3, "bookname");

rs = cStmt.executeQuery();

while(rs.next()){

String id = rs.getString("id"); // 方法１

//String id = rs.getString(2); // 方法２

String name = rs.getString("name");

}

// 出力を取得

int count = cStmt.getInt(1);

} catch (ClassNotFoundException e) {

e.printStackTrace();

} catch (SQLException e) {

e.printStackTrace();

} finally {

try {

if (rs != null && !rs.isClosed()) {

rs.close();

}

if (stmt != null && !stmt.isClosed()) {

stmt.close();

}

if (pStmt != null && !pStmt.isClosed()) {

pStmt.close();

}

if (cStmt != null && !cStmt.isClosed()) {

cStmt.close();

}

if (conn != null && !conn.isClosed()) {

conn.close();

}

} catch (SQLException e) {

e.printStackTrace();

}

}//try-catch-finally

★注意点：時分秒の欠落

SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");

ResultSet rset = stmt.executeQuery("SELECT SYSDATE FROM DUAL");

rset.next();

String date = sdf.format(rset.getDate("SYSDATE")); // 2012-04-11 00:00:00

環境によってgetDateとgetObjectでは時分秒を取得できないため、getTimestamp()を利用しよう

★Fetch Size

デフォルト（Oracleの場合）：10

大量データを取得する際、100に設定

JDBC

pStmt = conn.prepareStatement(

"SELECT * FROM table",

ResultSet.TYPE_FORWARD_ONLY,

ResultSet.CONCUR_READ_ONLY);

pStmt.setFetchSize(100);

rs = pStmt.executeQuery();

int cnt = rs.getMetaData().getColumnCount();

Object obj;

while (rs.next()) {

for (int i = 1; i <= cnt; i++) {

obj = rs.getObject(i);

}

}

iBatis

<select id="getAllUser" resultMap="HashMap" fetchSize="100">

select * from table

</select>

Connection Pool

Tomcatの場合

context.xml

<Context>

<Resource

name="jdbc/myoracle"

auth="Container"

type="javax.sql.DataSource"

driverClassName="oracle.jdbc.OracleDriver"

url="jdbc:oracle:thin:@127.0.0.1:1521:orclsid"

username="admin"

password="1234"

maxActive="20"

maxIdle="10"

maxWait="-1"/>

</Context>

web.xml

<resource-ref>

<description>Oracle Datasource example</description>

<res-ref-name>jdbc/myoracle</res-ref-name>

<res-type>javax.sql.DataSource</res-type>

<res-auth>Container</res-auth>

</resource-ref>

try {

//書き方１

Context ctx = new InitialContext();

DataSource ds = (DataSource) ctx.lookup("java:comp/env/jdbc/myoracle");

//書き方２

Context ctx2 = (Context) new InitialContext().lookup("java:comp/env");

DataSource ds2 = (DataSource) ctx.lookup("myoracle");

Connection conn = ds.getConnection();

Statement stmt = conn.createStatement(ResultSet.CONCUR_READ_ONLY, ResultSet.CONCUR_UPDATABLE);

ResultSet rs = stmt.executeQuery("SELECT * FROM example");

while (rs.next()) {

System.out.println(rs.getString("id"));

}

} catch (NamingException e) {

e.printStackTrace();

} catch (SQLException e) {

e.printStackTrace();

}