PowerPoint Sunusu
advertisement
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