Database Technologies for Enterprise JDBC, ADO.NET, LINQ. Antonius Rachmat C, S.Kom, M.Cs

Database Technologies for Enterprise JDBC, ADO.NET, LINQ AASE 6 Antonius Rachmat C, S.Kom, M.Cs

JDBC • Java Database Connectivity? • Java menyediakan JDBC yang berfungsi untuk berhubungan dengan database. • Database yang didukung oleh Java cukup banyak, seperti : MySQL, Postgres, Oracle, DB2, Access dan lain-lain. • JDBC berisi kumpulan kelas-kelas dan interface yang ditulis dengan bahasa Java.

JDBC (2) • Yang dilakukan JDBC – Membangun koneksi ke data source – Mengirim statement ke data source – Memproses hasil statement tersebut

• Java menyediakan tiga produk JDBC: – JDBC driver manager – JDBC driver test suite – JDBC ODBC bridge

ODBC vs JDBC • ODBC tidak cocok dipakai langsung dengan Java karena ditulis dengan bahasa C, pemanggilan dari Java ke C memiliki masalah keamanan, implementasi, robustness, dan portabilitas sistem. • Penerjemahan dari C ke Java tidak akan berhasil baik. – Contoh: Java tidak memiliki pointer.

• ODBC sulit dipelajari karena optionnya yang sulit walaupun untuk query yang sederhana. • Java API diperlukan untuk mempertahankan solusi “murni Java”, agar dapat berjalan di berbagai platform. • Karena ODBC harus diinstall dahulu di setiap client dan tidak semua platform.

ODBC option - SQLServer • Standard Connection Data Source=myServerAddress;Initial Catalog=myDataBase;User Id=myUsername;Password=myPassword; • Trusted Connection Data Source=myServerAddress;Initial Catalog=myDataBase;Integrated Security=SSPI;

connectionstrings.com • MySQL – Server=myServerAddress;Database=myDataBase;Uid=my Username;Pwd=myPassword;

• Oracle – Data Source=username/password@myserver//instancename;

• Postgres – User ID=root;Password=myPassword;Host=localhost;Port=5432 ;Database=myDataBase; Pooling=true;Min Pool Size=0;Max Pool Size=100;Connection Lifetime=0;

• SQLite – Data Source=filename;Version=3;

Keunggulan JDBC • Mempertahankan data yang ada • Menyederhanakan development • Tidak memerlukan konfigurasi pada jaringan komputer • Akses penuh ke metadata • Koneksi database menggunakan URL dan DataSource – Load driver tergantung pada Driver databasenya

Arsitektur JDBC Lapisan Vendor Specific JDBC Driver merupakan driver JDBC yang dikeluarkan oleh para vendor pengembang RDBMS. Sedangkan JDBC- ODBC Bridge berfungsi sebagai perantara untuk mengakses database melalui ODBC driver. Baik JDBC driver maupun JDBC-ODBC Bridge diatur dan dapat diakses melalui JDBC Driver Manager. Aplikasi yang dikembangkan untuk mengakses database dengan memanfaatkan JDBC akan berinteraksi dengan JDBC Driver Manager.

JDBC API • Tersedia dalam paket java.sql dan javax.sql. • DriverManager – memanggil driver JDBC ke memori, dan dapat juga digunakan untuk membuka koneksi ke sumber data. • Connection – mempresentasikan suatu koneksi dengan suatu data source, juga digunakan untuk membuat objek Statement, PreparedStatement dan CallableStatement. • Statement – mempresentasikan suatu perintah SQL, dan dapat digunakan untuk menerima objek ResultSet.

JDBC API (2) • PreparedStatement – merupakan alternatif untuk objek Statement SQL yang telah terkompilasi awal. • CallableStatement – mempresentasikan suatu stored procedure, dan dapat digunakan untuk menjalankan stored procedures yang terkompilasi dalam suatu RDBMS yang mendukung fasilitas tersebut. • ResultSet – mempresentasikan sebuah hasil dari database yang dihasilkan dari statemen SQL SELECT. • ResultSetMetaData – mempresentasikan hasil informasi metadata dari kolom-kolom dalam suatu table • SQLException – suatu class exception yang membungkus kesalahan (error) pengaksesan database.

JDBC Data Type

Pemrograman JDBC •

Membangun koneksi –

Memuat driver ODBC Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");

–

Atau DriverManager.registerDriver(new sun.jdbc.odbc.JdbcOdbcDriver ());

• •

Membangun koneksi URL Format: jdbc:odbc: Contoh lengkap: String url = “jdbc:odbc:Buku”; String user = “”; String pass = “”; Connection con = DriverManager.getConnection(url,user,pass);

Pemrograman JDBC (2) •

Membuat Statement Menggunakan Obyek Connection yang sudah kita buat sebelumnya: –

• •

Statement stmt = con.createStatement();

Menjalankan Statement Method executeUpdate untuk DDL dan DML insert, update, dan delete. String query = “delete from tabel where id=1”: Statement stmt = con.createStatement(); int hsl = Stmt.executeUpdate(query);

•

Method executeQuery untuk DML select String query = “select * from tabel”; Statement stmt = con.createStatement(); ResultSet rs = Stmt.executeQuery(query);

Pemrograman JDBC (3) • •

Mengambil hasil Statement dari Query dan Memprosesnya DDL dan DML: update, insert, dan delete int hsl = Stmt.executeUpdate(query); if(hsl == 1) System.out.println(“Berhasil”); else System.out.println(“Gagal”);

•

DML: select ResultSet rs = Stmt.executeQuery(query); while(rs.next()){ int a = rs.getInt(“fieldA”); String b = rs.getString(“fieldB”); float c = rs.getFloat(“fieldC”);

}

Pemrograman JDBC (4) • Tutup koneksi yang sudah dibuat. – con.close();

• Kita dapat membuat class yang berisi semua method yang membantu kita untuk melakukan koneksi dan transaksi ke database!

Penting! • Harus mengetahui dan memiliki JDBC driver sesuai dengan database yang digunakan. – Misalnya MySQL beda dengan Oracle!

• Harus mengetahui cara koneksi dengan database. – Menggunakan string koneksi, misalnya: String url = "jdbc:msql://www.myserver.com:1114/contact_mgr"; Connection conn = DriverManager.getConnection(url,"user1","password");

Install JDBC • Kopikan Driver JAR file ke : <JAVA_HOME>\jre\lib\ext • Cara pemanggilan driver tergantung package Drivernya

JAR MySQL

Contoh: MySQL Create Table

Membaca Isi Data ODBC

Penanganan Transaction • getAutoCommit() / setAutoCommit() – Get/Set otomatis commit atau tidak

• commit() – setelah transaksi selesai disimpan • rollback() – setelah transaksi selesai dibatalkan

Transaction Example

Tes Performa MySQL InnoDb

Tes Performa Oracle 10g, (10.2.0)

Prepared statement

PreparedStatement

Callable Statement – stored procedure

Contoh callable statement

ResultSet • ResultSet is used for get all of the query select • ResultSet is automatically closed when Statement executes a new query

Cursor ResultSet • Method pergerakan kursor yang didukung oleh ResultSet: – – – – – –

previous() next() first() last() absolute() relative()

ke record sebelumnya ke record selanjutnya ke record pertama ke record terakhir ke nomor baris tertentu ke nomor baris dari baris sekarang – beforeFirst() ke nomor baris sebelum pertama – afterLast() ke nomor baris setelah terakhir

Cursor ResultSet • Jika suatu ResultSet dibuat, selalu ResultSet tersebut berada pada poisi record sebelum record pertama (rs.beforeFirst()). • Sehingga untuk mengambil data yang hanya terdiri dari satu baris, harus terlebih dahulu digunakan method rs.next() sekali.

Informasi kolom ResultSetMetaData • getColumnCount() – Jumlah kolom

• getColumnDisplaySize() – Jumlah karakter kolom tersebut

• getColumnLabel/getColumnName() – Nama kolom tersebut

• getColumnType() – Tipe data kolom tersebut

Cursor ResultSet • getMetaData(), untuk yg mengembalikan ResultSetMetaData mengambil informasi metadata – System.out.println(dbkolom.getColumnName(1)+"\t"+dbkolom.getColum nName(2)+"\t"+dbkolom.getColumnName(3));

• Method untuk mengambil jumlah baris: – int getRow() - yang mengembalikan nilai integer • Method int findColumn() – Kembaliannya int posisi

• Method untuk membatasi jumlah baris hasil query select: – Statement.setFetchSize(number)

• getWarnings() untuk mengambil warning SQL

Contoh ambil nama kolom dinamis

setFetchSize() • setFetchSize() memiliki arah, yaitu: – ResultSet.FETCH_FORWARD untuk proses maju – ResultSet.FETCH_REVERSE untuk proses berbalik – ResultSet.FETCH_UNKNOWN untuk proses yang tidak diketahui

• Contoh: Statement stmt = con.createStatement(); stmt.setFetchDirection(ResultSet.FETCH_FORWARD);

stmt.setFetchSize(30); ResultSet rs = stmt.executeQuery(…);

Kembalian ResultSet • null – Untuk metode getXXX yang mengembalikan obyek

• 0 – Untuk metode getXXX yang mengembalikan tipe data primitif biasa

• false – Untuk metode getXXX yang mengembalikan tipe data boolean.

Exception dalam JDBC • SQLException: ketika ada masalah pengaksesan data (SQL) • SQLWarning: ketika ada peringatan SQL • DataTruncation: ketika data mungkin terpotong • BatchUpdateException: ketika tidak semua perintah update berhasil dilakukan.

SqlException Example

Access Database • Microsoft Acess Database : db1

Setting Access Database • Menset Database yang dipakai sebagai acuan dalam Program • Start -> Control Panel -> ODBC Data Source Download di http://dev.mysql.com/downloads/connector/odbc/

Setting Access Database • • •

Tampil jendela ODBC Data Source Administrator Click System DSN-> Add-> pilih Driver (Microsoft Access Driver) -> Finish. Dalam hal ini kita memakai Access Database. Tentukan Letak File Database yang digunakan

Setting Access Database

• Click OK • Data base db1 siap diakses oleh Program

ADO • is a set of Component Object Model (COM) objects for accessing data sources • ADO allows a developer to write programs that access data without knowing how the database is implemented – No knowledge of SQL? FALSE -> NoSQL

• ADO introducted on October 1996.

ADO elements • • • • • • • • •

Fields Properties Parameters Errors Connection Command RecordSet Record Parameter

kolom property tabel tertentu parameter strored procedure error message koneksi database perintah SQL hasil kumpulan record satu buah record parameter stored procedure

ADO Steps 1. Create a connection object to connect to the database. 2. Create a recordset object in order to receive data in. 3. Open the connection. 4. Populate the recordset by opening it and passing the desired table name or SQL statement as a parameter to open function. 5. Do all the desired searching/processing on the fetched data. 6. Commit the changes you made to the data (if any) by using Update or UpdateBatch methods. 7. Close the connection. 8. Close the recordset.

ADO versus ADO.NET Feature

ADO

ADO.NET

Primary Aim Client/server coupled

Disconnected collection of data from data server

Form of data in memory

Uses RECORDSET object (contains one table)

Uses DATASET object (contains one or more DATATABLE objects)

Disconnect ed access

Uses CONNECTION Uses object and RECORDSET DATASETCOMMAND object with OLEDB object with OLEDB

Disconnecte d access across multitiers

Uses COM to marshal RECORDSET

Transfers DATASET object via XML. No data conversions required

ADO versus ADO.NET (continued) Feature

ADO

ADO.NET

XML capabilities

XML aware

XML is the native transfer medium for the objects

Code

Coupled to the language Managed code library – Uses Common Language used, various Runtime, therefore, implementation language agnostic

ADO.NET Basic principles (1) • All data providers implement the same set of standard interfaces: – DataConnection – DataCommand – DataParameter – DataReader – DataAdapter

ADO.NET Basic principles (2) • Microsoft ships some data providers: – – – –

SQL Server Oracle OleDb ODBC

• But third parties can implement their own: – IBM (for DB2) – MySQL – SQLite

ADO.NET Architecture Diagram

ADO.NET Namespaces System.Data

Core namespace, defines types that represent data

System.Data.Common

Types shared between managed providers

System.Data.OleDb

Types that allow connection to OLE DB compliant data sources

System.Data.SqlClient

Types that are optimized to connect to Microsoft® SQL Server

System.Data.SqlTypes

Native data types in Microsoft® SQL Server

Importing the ADO.NET Namespaces Needed to build a data access application • For OLE DB: Imports System.Data Imports System.Data.OleDB

• For SQL Server: Imports System.Data Imports System.Data.SqLClient

• For MySQL: Imports System.Data. Imports System.Data.MySqL.

SQL Server Namespace Objects using System.Data.SqlClient; System.Data.SqlClient; • SqlConnection : represents an open connect to a SQL server • SqlCommand : represents a TSQL statement or a stored procedure to execute against a SqlConnection • SqlDataReader : provides a fast, forward only, read only method of retrieving rows • SqlDataAdapter : Represents a set of methods and mappings to read/write a DataSet from/to a data source • SqlParameter : represents a parameter for a SqlCommand

SQL Namespace Objects • SqlError : collects a single error or warning from SQL server • SqlException : the exception thrown when the server returns an error, warning or exception • SqlTransaction : represents a T-SQL transaction to be made to a SQL database • SqlDbType : an enumeration to represent each type of data available on a SQL server

ADO.NET Data Connection • Represents an open connection to a data source • Provider-specific classes o SqlConnection o OleDbConnection. o OralceConnection

MySQLConnection SQLiteConnection PostgreSQLConnection

• Properties – ConnectionString(String)

• Methods – Open() – Close() – BeginTransaction()

ADO.NET Data Command • Represents a command statement that is executed while connected to a data source • Provider-specific classes o SqlCommand o OleDbCommand o OracleCommand

MySQLCommand PostgreSQLCommand SQLiteCommand

• Properties – Connection – CommandType, CommandText – Parameters

• Methods – ExecuteNonQuery() -> non select – ExecuteReader() -> select – ExecuteScalar() -> select kolom pertama

ADO.NET Data Reader • Provides a means of reading a forward-only stream of results from a command o SqlDataReader o OleDb DataReader o Oracle DataReader

MySQLDataReader PostgreSQLDataReader SQLite DataReader

• myDataReader(“columnname”); • Properties – FieldCount – Item[“name”], Item[index]

• Methods – – – –

Read() GetName(index) GetValue(index), GetString(index) GetEnumerator()

Create DataReader Example

ADO.NET DataSet • Represents an in-memory cache of data. • A class, not an interface! • Properties – DataSetName – Tables //Data and schema – Relations

• Methods – Clear(), Copy(), Merge() – GetChanges(), AcceptChanges(), RejectChanges() – ReadXml(), WriteXml()

DataSet

ADO.NET DataTable • Properties – – – – – –

TableName Columns //DataColumn Constraints //Constraint Rows //DataRow DefaultView //DataView ParentRelations, ChildRelations

• Methods – NewRow(), – Select(“expression”), Compute(“expression”)

ADO.NET Data Adapter • Represents a set of methods and mappings to read/write a DataSet from/to a data source • Allows the use of stored procedures • Connects to the database to fill the DataSet and also update the database • Properties – SelectCommand, InsertCommand – UpdateCommand, DeleteCommand

• Methods – Fill(DataSet) – Update(DataSet)

Contoh

DataView Object • Provides methods and properties that enable UI objects such as a DataGrid to bind to a DataSet • Only used in conjunction with a DataSet • DataGridView is DataView Object

Examples Connection String • Private Const SELECT_STRING As String = "SELECT * FROM Contacts ORDER BY LastName, FirstName" • Private Const CONNECT_STRING As String = "Data Source=ANTONIE\SQLEXPRESS;InitialCatalog =Contacts;User Id=sa"

Data binding example

Examples

Examples

• Adding Relations

DataRows

DataRow – Reading Data

DataView

DataAdapter

What is LINQ? • Language Integrated Query • Merupakan integrasi antara perintah query dengan sintaks bahasa pemrograman • Didukung oleh bahasa VB.NET dan C# • LINQ dapat mengakses: – – – –

Objects (Ex: Array) SQL XML Entities (Class)

LINQ • Enables querying of any iEnumerable • Unique, but familiar, Query syntax A combination of new language features, and new .NET framework FX 3.5 classes It’s TYPED language... The compiler can check your queries The compiler can check your results

LINQ Architecture Compilers (language-level support) Standard Query Operators

Expression Trees

Linq-enabled data sources Linq to objects

Linq-enabled ADO.NET Linq to SQL (DLinq)

Linq to datasets

Linq to entities

Linq to XML (XLinq)

The LINQ Architecture (2) C# 3.0

Visual Basic 9.0

Others

.NET Language Integrated Query

LINQ to Objects

LINQ to DataSets

LINQ to SQL

LINQ to Entities

LINQ to XML