📄 jdbc访问技术.txt
字号:
一、JDBC数据库访问
二、JAVA数据库应用
1、加载JDBC-ODBC桥
调用Class类forName()方法注册ODBC-JDBC驱动程序
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
2、建立连接
DriverManager类的getConnection()方法试图定位能连接到数据库的驱动程序
String url="jdbc:odbc:MyDatasource";
MyDatasource指的是事先定义好的ODBC接口
Connection con=DriverManager.getConnection(url,"sa","");
3、访问数据库
JDBC提供三种类发送SQL语句给数据库
A、Statement对象：从Connection对象调用createStatement()方法，创建Statement对象。
B、PreparedStatement对象
从Connection对象调用preparedStatement()方法，创建PreparedStatement对象,实现发送可动态调整内容的SQL语句给SQL SERVER
(语句中包含input参数)
C、CallableStatement对象
从Connection对象调用prepareCall()方法，创建CallableStatement对象。它可包含调用存储过程的功能。
statement提供三种方法执行SQL语句：
1)executeQuery() 执行简单查询并返回单个ResultSet对象
2)executeUpdate() 执行Insert、Update、Delete语句
3)execute() 执行SQL语句，可返回多个结果
例：
//QueryApp.java
import java.sql.*;
public class QueryApp{
public static void main(String args[]){
ResultSet result;
try{
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
Connection c=DriverManager.getConnection("jdbc:odbc:CWJ","sa","");
Statement stat=c.createStatement();
result=stat.executeQuery("select * from Publishers");
while(result.next()){
System.out.println(result.getString(2));
}
}catch(Exception e){
System.out.println("Error"+e);
}
}
}
二、用TYPE4的JDBC Driver访问数据库
Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver");
Connection conn=DriverManager.getConnection("jdbc:microsoft:sqlserver://tsinghuacwj;DatabaseName=pubs;User=sa;Password=sa");
JBuilder配置三步骤：
１、Configure Libraries
２、Enterprise Setup->Database Drivers，完成后重启JBuilder
３、Database Pilot
三、可滚动、可更新的结果集
是JDBC2.0新特性之一，它支持将结果集中的游标按任意方向移动的能力。
语法：connection.createStatement(int rsType,int rsConcurrency);
注：rsType有三种选择：TYPE_FORWARD_ONLY 不可滚动的结果集
TYPE_SCROLL_INSENSITIVE 可滚动的结果集，当打开时不反映它的变化
TYPE_SCROLL_SENSITIVE 可滚动的结果集，当打开时反映它的变化
rsConcurrency有两种选择：CONCUR_READ_ONLY 只读的
CONCUR_UPDATABLE 可更新的
例：Statement stmt=con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_UPDATABLE);
first()、last()、beforefirst()、afterlast()、absolute(int rowNumber)、relative(int rowNumber)
例1：写一个程序实现定位到第一条记录，最后一条记录，将表中记录按正向顺序显示，然后按反向顺序显示。
例1：用RowSet实现上述功能 P117
可更新的ResultSet:(P112)
1、更新ResultSet: rs.updateString("Street","123 Main");
rs.updateRow();
2、插入新行：rs.moveToInsertRow();
rs.updateString("First_Name","cwj");
rs.insertRow();
3、删除行：rs.deleteRow();
四、各位Statement的使用
A、Statement对象：从Connection对象调用createStatement()方法，创建Statement对象。
statement提供三种方法执行SQL语句：
1)executeQuery() 执行简单查询并返回单个ResultSet对象
2)executeUpdate() 执行Insert、Update、Delete语句
3)execute() 执行SQL语句，可返回多个结果
例：创建表
String dTableSQL = "CREATE TABLE digest " + "(id INTEGER NOT NULL," + " title VARCHAR(64) NOT NULL," +
" author VARCHAR(64) NOT NULL)" ;
String mTableSQL = "CREATE TABLE messages " + "(id INTEGER NOT NULL," + " title VARCHAR(64) NOT NULL," +
" author VARCHAR(64) NOT NULL," +
" message CLOB(2048))" ;
String aTableSQL = "CREATE TABLE authors " + "(author VARCHAR(64) NOT NULL," + " photo BLOB(4096))";
Statement stmt = con.createStatement() ;
stmt.executeUpdate(dTableSQL) ;
stmt.executeUpdate(mTableSQL) ;
stmt.executeUpdate(aTableSQL) ;
例：删除表
String dDropSQL = "DROP TABLE digest" ;
String mDropSQL = "DROP TABLE messages" ;
String aDropSQL = "DROP TABLE authors" ;
...
Statement stmt = con.createStatement() ;
stmt.executeUpdate(dDropSQL) ;
stmt.executeUpdate(mDropSQL) ;
stmt.executeUpdate(aDropSQL) ;
例：往表中插入数据
String baseInsertSQL = "Insert INTO digest VALUES(" ;
int[] ids = {1, 2, 3, 4, 5} ;
String[] authors = {"java", "rjb", "java", "bill", "scott"} ;
String[] titles = { "Hello","Hello Java","Hello Robert","Hello from Bill","Hello from Scott"} ;
Statement stmt = con.createStatement() ;
for(int i = 0 ; i < ids.length ; i++) {
stmt.executeUpdate( baseInsertSQL+ids[i]+ ", '" + titles[i] + "', '" + authors[i] + "')") ;
}
B、PreparedStatement对象
从Connection对象调用preparedStatement()方法，创建PreparedStatement对象,实现发送可动态调整内容的SQL语句给SQL SERVER
(语句中包含input参数)
例：往表中插入数据（注：以与上方法相比，效率更佳）
String insertSQL = "Insert INTO digest VALUES(?, ?, ?)" ;
int[] ids = {1, 2, 3, 4, 5} ;
String[] authors = {"java", "rjb", "java", "bill", "scott"} ;
String[] titles = { "Prepared Hello",
"Prepared Hello Java",
"Prepared Hello Robert",
"Prepared Hello from Bill",
"Prepared Hello from Scott"} ;
Connection con = ds.getConnection("java", "sun") ;
PreparedStatement pstmt = con.prepareStatement(insertSQL) ;
for(int i = 0 ; i < ids.length ; i++){
pstmt.setInt(1, ids[i]) ;
pstmt.setString(2, titles[i]) ;
pstmt.setString(3, authors[i]) ;
pstmt.executeUpdate() ;
｝
C、CallableStatement对象
从Connection对象调用prepareCall()方法，创建CallableStatement对象。它可包含调用存储过程的功能。
{call AuthorList} if the procedure takes no parameters
{call AuthorList[(?, ?)]} if the procedure takes two parameters
{? = call AuthorList[(?, ?)]} if the procedure takes two parameters and returns one
例：创建存储过程
import java.sql.*;
import javax.sql.*;
public class CreateCallableStmt{
private static String dbUserName = "sa";
private static String dbPassword = "dba";
public static void main(String args[]){
String createProc = "CREATE PROCEDURE INSERT_CONTACT_INFO "+
"@ID INT, @FName VARCHAR(20), @MI CHAR(1), "+
"@LName VARCHAR(30),@Street VARCHAR(50), "+
"@City VARCHAR(30), @ST CHAR(2), "+
"@ZIP VARCHAR(10), @Phone VARCHAR(20), "+
"@Email VARCHAR(50) "+
"AS INSERT INTO CONTACT_INFO "+
"(ID, FName, MI, LName, Street, City, ST, ZIP, "+
"Phone, Email) "+
"VALUES "+
"(@ID, @FName, @MI, @LName, @Street, @City, "+
" @ST, @ZIP, @Phone, @Email);";
try {
Class.forName("com.inet.pool.PoolDriver");
com.inet.tds.TdsDataSource tds = new com.inet.tds.TdsDataSource();
tds.setServerName( "JUPITER" );
tds.setDatabaseName( "MEMBERS" );
tds.setUser( dbUserName );
tds.setPassword( dbPassword );
DataSource ds = tds;
Connection con = ds.getConnection(dbUserName,dbPassword);
Statement stmt = con.createStatement();
stmt.executeUpdate(createProc);
}
catch(ClassNotFoundException e){
e.printStackTrace();
}
catch(SQLException e){
e.printStackTrace();
}
}
}
例：存储过程调用
import java.sql.*;
import javax.sql.*;
public class CallableGetLogin{
private static String dbUserName = "sa";
private static String dbPassword = "dba";
public static void main(String args[]){
try {
Class.forName("com.inet.pool.PoolDriver");
com.inet.tds.TdsDataSource tds = new com.inet.tds.TdsDataSource();
tds.setServerName( "JUPITER" );
tds.setDatabaseName( "MEMBERS" );
tds.setUser( dbUserName );
tds.setPassword( dbPassword );
DataSource ds = tds;
Connection con = ds.getConnection(dbUserName,dbPassword);
CallableStatement cs = con.prepareCall("{call GET_LOGIN_FOR_USER(?)}");
cs.setString(1,"garfield");
ResultSet rs = cs.executeQuery();
ResultSetMetaData md = rs.getMetaData();
while(rs.next()){
for(int i=1;i<=md.getColumnCount();i++){
System.out.print(md.getColumnLabel(i)+"\t=\t");
if(md.getColumnType(i)==java.sql.Types.INTEGER)
System.out.println(rs.getInt(i));
else
System.out.println(rs.getString(i));
}
}
}
catch(ClassNotFoundException e){
e.printStackTrace();
}
catch(SQLException e){
e.printStackTrace();
}
}
}
五、Blob和Clob对象的使用
1、Inserting Blobs（binary large object）
注：将图片插入表中
...
String insertSQL = "Insert INTO authors VALUES(?, ?)" ;
PreparedStatement pstmt = con.prepareStatement(insertSQL) ;
File file = new File("C:/images/rjb.jpg") ;
FileInputStream fis = new FileInputStream(file);
pstmt.setString(1, "rjb");
pstmt.setBinaryStream(2, fis, (int)file.length());
if(1 != pstmt.executeUpdate())
System.err.println("Incorrect value returned during author insert.") ;
pstmt.close();
fis.close();
System.out.println("BLOB Insert Successful") ;
2、Selecting a Blob
String selectSQL = "SELECT photo FROM authors WHERE author = ?" ;
PreparedStatement pstmt = con.prepareStatement(selectSQL) ;
//用于查找作者名称为rjbr的记录
pstmt.setString(1, "rjb");
ResultSet rs = pstmt.executeQuery() ;
rs.next() ;
Blob blob = rs.getBlob("photo") ;
// Materialize BLOB onto client
ImageIcon icon = new ImageIcon(blob.getBytes(1, (int)blob.length())) ;
JLabel lPhoto = new JLabel(icon) ;
3、Inserting a Clob
String insertSQL = "Insert INTO messages VALUES(?, ?, ?, ?)" ;
PreparedStatement pstmt = con.prepareStatement(insertSQL) ;
File file = new File("C:/data/rjb.txt") ;
FileInputStream fis = new FileInputStream(file);
pstmt.setInt(1, 1);
pstmt.setString(2, "Hello Java");
pstmt.setString(3, "rjb");
pstmt.setAsciiStream(4, fis, (int)file.length());
if(1 != pstmt.executeUpdate())｛
System.err.println("Incorrect value returned during message insert.") ;
｝
4、Selecting a Clob
String selectSQL = "SELECT message FROM messages WHERE id = ?" ;
PreparedStatement pstmt = con.prepareStatement(selectSQL) ;
pstmt.setInt(1, 1);
ResultSet rs = pstmt.executeQuery() ;
rs.next() ;
Clob clob = rs.getClob("message") ;
// Materialize CLOB onto client
InputStreamReader in = new InputStreamReader(clob.getAsciiStream()) ;
JTextArea text = new JTextArea(readString(in)) ;
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -