PowerPoint Sunusu

NETBEANS GUI İLE
MS SQL İŞLEMLERİ
JDBC Driver Registry
Database Reference
Download
Java Class
MS Access (32bit v6)
Default available on less recent Windows systems (*.mdb)
MS Access (32bit v12,v14)
http://www.microsoft.com/enus/download/details.aspx?id=13255,
Default available on recent Windows systems (*.mdb, *.accdb)
MS Access (64bit v12,v14)
http://www.microsoft.com/enus/download/details.aspx?id=13255
ODBC
Default available on Windows
MS/SQL
http://msdn.microsoft.com/en-us/data/aa937724.aspx
com.microsoft.sqlserver.jdbc.SQLServerDriver
MySQL
http://dev.mysql.com/downloads/connector/j
com.mysql.jdbc.Driver
Oracle
http://www.oracle.com/technetwork/database/features/jdbc/ind oracle.jdbc.OracleDriver
ex.html
PostgreSQL
http://jdbc.postgresql.org/download.html
http://www.orbitgis.com/kb/technologies/basic_concepts/database_driver
sun.jdbc.odbc.JdbcOdbcDriver
org.postgresql.Driver
MS SQL AYARLARI
 SQL sever network Ayarlarından TCP/IP enabled
olduğundan emin olunuz.
 TCP Port numarasını da SQL standart portu olan 1433
yapınız.
 TCP Dynamic Ports numarasını 49428 olarak
belirleyebilirsiniz.
JAVA MS SQL
Java ile MS SQL sever üzerinde işlem yapmak için MS SQL Java Driver
yükle işlemi yapılmalıdır. Aşağıdaki adresten yapabilirsiniz
https://www.microsoft.com/en-us/download/details.aspx?displaylang=en&id=11774.
Sqljdbc42.jar java dosyasını
programımıza library olarak
eklemeliyiz. Bunu için;
M.İlkuçar MAKU-MYO 2014
4
JAVA MS SQL
sqljdbc42.jar dosyasını programımıza library olarak eklemek için;
M.İlkuçar MAKU-MYO 2014
5
Libraries- Add JAR/ Folder- internetten
indireceğniz sqljdbc42.jar (şimdilik son
sürüm) seçin ve Open deyin. SQL
sürücünüz aşağıdaki gibi yüklenecektir.
1
4
2
5
3
JAVA MS SQL
M.İlkuçar MAKU-MYO 2014
7
JAVA MS SQL
Java’ dan MS SQL sunucuya bağlantı oluşturmak.
package javasql;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
public class JavaSQL {
public static void main(String[] args) {
try{
Connection con = DriverManager.getConnection( "jdbc:sqlserver://Sunucu:port;DatabaseName=Database Adı", "kullanıcı", "şifre" );
//Connection bag= DriverManager.getConnection("jdbc:sqlserver://localhost:1433;databasename=myo;user=sa;password=12345");
Statement stmt = con.createStatement();
con.close();
}catch(Exception ex){
System.out.print(ex.getMessage());
}
}
}
M.İlkuçar MAKU-MYO 2014
8
JAVA MS SQL
Java’ dan MS SQL sunucuda SQL komutları işletmek
package msSQL;
import
import
import
import
import
java.sql.Connection;
java.sql.DriverManager;
java.sql.Statement;
java.sql.ResultSet;
java.sql.CallableStatement;
public class ANA {
public static void main(String[] args) {
try {
Connection baglanti =null;
Statement statement =null;
ResultSet resultset=null;
baglanti= DriverManager.getConnection("jdbc:sqlserver://localhost:1433;databasename=myo;user=sad;password=12345");
statement=baglanti.createStatement();
statement.execute("insert into ogr VALUES(107,'Ali',‘Can')");
statement.executeUpdate("Update ogr set ad=‘Ferdi’, soy=‘Torun' where num=105");
resultset= statement.executeQuery("select * from ogr");
while(resultset.next())
{
System.out.println(resultset.getString("num") +" "+resultset.getString("ad") +" "+resultset.getString("soy") );
}
} catch (Exception e) {System.out.println("HATA..." +e); }
}
}
JAVA MS SQL
Java’ dan MS SQL sunucuda SQL komutları işletmek
package javasql;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class JavaSQL {
public static void main(String[] args) {
try{
Connection con = DriverManager.getConnection( "jdbc:sqlserver://Sunucu:1433;DatabaseName=Database Adı", «kullanıcı", «şifre" );
Statement stmt = con.createStatement();
String sorgur1="update ogr set ad=‘Ali' where num=201";
String sorgur2 = "delete from ogr where num=201";
String sorgur3 = "insert into ogr (num,ad,soyad) values (101,‘Fatma',‘Kara')";
stmt.execute(sorgur1);
stmt.execute(sorgur2);
stmt.execute(sorgur3);
ResultSet rs = stmt.executeQuery("SELECT * FROM ogr");
con.close();
}catch(Exception ex){ System.out.print(ex.getMessage());
}
}
}
M.İlkuçar MAKU-MYO 2014
10
JAVA MS SQL
SQL’den okunan 8select) Listeyi ekrana yazdırma:
package javasql;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class JavaSQL {
public static void main(String[] args) {
try{
Connection con = DriverManager.getConnection( "jdbc:sqlserver://Sunucu:1433;DatabaseName=Database Adı", «kullanıcı", «şifre" );
Statement stmt = con.createStatement();
ResultSet rs = stmt.executeQuery("SELECT * FROM ogr");
while (rs.next()) {
System.out.println(rs.getString(«num")+"-"+ rs.getString("ad")+"-"+ rs.getString("soyad"));
}
con.close();
}catch(Exception ex){ System.out.print(ex.getMessage());
}
}
}
M.İlkuçar MAKU-MYO 2014
11
PARAMETRE KULLANIMI
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.sql.PreparedStatement;
public class JavaSQL {
public static void main(String[] args) {
try{
Connection con = DriverManager.getConnection( "jdbc:sqlserver://Sunucu:1433;DatabaseName=Database Adı", «kullanıcı", «şifre" );
Statement stmt = con.createStatement();
PreparedStatement ps = con.prepareStatement("INSERT INTO ogr (num,ad,soyad) VALUES( ?,?,?)");
ps.setString(1,"106");
ps.setString(2,"Handan");
ps.setString(3,"Kor");
ps.execute();
ResultSet rs = stmt.executeQuery("SELECT * FROM ogr");
while (rs.next()) {
System.out.println(rs.getString("num")+"-"+ rs.getString("ad")+"-"+ rs.getString("soyad"));
}
con.close();
}catch(Exception ex){
System.out.print(ex.getMessage());
}
}
M.İlkuçar MAKU-MYO 2014
12
UPDATE
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.sql.PreparedStatement;
public class JavaSQL {
public static void main(String[] args) {
try{
Connection con = DriverManager.getConnection( "jdbc:sqlserver://Sunucu:1433;DatabaseName=Database Adı", «kullanıcı", «şifre" );
Statement stmt = con.createStatement();
PreparedStatement ps = con.prepareStatement("UPDATE ogr SET ad = ?, soyad = ? WHERE num = ? ");
ps.setString(3,"106");
ps.setString(1,"Ali");
ps.setString(2,"Can");
ps.execute();
ps.executeUpdate();
ResultSet rs = stmt.executeQuery("SELECT * FROM ogr");
while (rs.next()) {
System.out.println(rs.getString("num")+"-"+ rs.getString("ad")+"-"+ rs.getString("soyad"));
}
con.close();
}catch(Exception ex){
System.out.print(ex.getMessage());
}
}
M.İlkuçar MAKU-MYO 2014
13
STORED PROCEDURE KULLANIMI
create proc ekle @n int, @a varchar(15), @s varchar(15)
AS
insert into ogr VALUES(@n,@a,@s)
import java.sql.CallableStatement;
CallableStatement proc_stmt = baglanti.prepareCall("{call ekle(?,?,?)}");
proc_stmt.setEscapeProcessing(true);
proc_stmt.setQueryTimeout(5000); // time out value
proc_stmt.setString("n", "222");
proc_stmt.setString("a", "employee");
proc_stmt.setString("s", "employee");
boolean results = proc_stmt.execute();
OUT Parametreli Stored Procedure kullanımı
CREATE PROCEDURE sil @n int, @mesaj Varchar(15) OUT
AS BEGIN
SET NOCOUNT ON;
if(Exists(select * from ogr where num=@n))
begin
delete from ogr where num=@n
set @mesaj='SiLiNDi'
end
else set @mesaj='KAYIT YOK'
END
CallableStatement cstmt = baglanti.prepareCall("{call sil(?,?)}");
cstmt.setString("n", "222");
cstmt.registerOutParameter("mesaj", java.sql.Types.VARCHAR);
cstmt.execute();
String mesaj= cstmt.getString("mesaj");
System.out.println(".........." + mesaj);
verilerin JTable’ a aktarılması (rs2xml.jar)
Resultset= statement.executeQuery("select * from ogr");
table.setModel(DbUtils.resultSetToTableModel(resultset));
//rs2xml.jar dosyası gerekli
rs2xml.jar indir ve ekle
ECLPSE GUI İLE MS SQL
2
1
5
3
4
package msSQL;
import
import
import
import
java.sql.Connection;
java.sql.DriverManager;
java.sql.Statement;
java.sql.ResultSet;
public class ANA {
public static void main(String[] args) {
try {
Connection baglanti =null;
Statement statement =null;
ResultSet resultset=null;
baglanti= DriverManager.getConnection("jdbc:sqlserver://localhost:1433;databasename=myo;user=sad;password=12345");
statement=baglanti.createStatement();
System.out.println(statement.isClosed());
statement.execute("insert into ogr VALUES(107,'Furkan',‘Aydın')");
statement.executeUpdate("Update ogr set ad='Afra', soy=‘Kara' where num=105");
resultset= statement.executeQuery("select * from ogr");
while(resultset.next())
{
System.out.println(resultset.getString("num") +" "+resultset.getString("ad") +" "+resultset.getString("soy") );
}
} catch (Exception e) {
System.out.println("HATA..." +e);
}
}
}
Connection baglanti =null;
Statement statement =null;
ResultSet resultset=null;
baglanti= DriverManager.getConnection("jdbc:sqlserver://localhost:1433;databasename=myo;user=sad;password=12345");
statement=baglanti.createStatement();
resultset= statement.executeQuery("select * from ogr");
table.setModel(DbUtils.resultSetToTableModel(resultset));
//rs2xml.jar dosyası gerekli
rs2xml.jar indir ve ekle