JDBC(二)

最新推荐文章于 2023-09-11 01:10:18 发布

原创最新推荐文章于 2023-09-11 01:10:18 发布 · 4.1k 阅读

2 ·

CC 4.0 BY-SA版权

文章标签：

#jdbc #exception #import #null #string #数据库

JDBC 专栏收录该内容

5 篇文章

订阅专栏

DatabaseMetaData接口:

通过这个接口中的方法可以查看数据库的整体综合信息, DatabaseMetaData给出的信息描述DBMS所提供的事务支持水平

比如:查看驱动程序(数据库)的版本号等

boolean supportsTransactionIsolationLevel (int level):

检索此数据库是否支持给定事务隔离级别

事务:

什么是事务?

什么是事物隔离级别?

事务是一组操作,这些操作要么都执行成功,要么就都失败,例如:银行转帐,要成功都成功,有个失败就都失败,这种事务操作是并发执行的,同时进行操作,当调用方法commit或rollback时，当前事务即告结束，另一个事务随即开始

什么是并发访问(执行,操作)?多用户访问同一资源(表,记录,数据)

并发操作可能带来的一些问题:

脏读:包含未提交数据的读取,比如说:一个用户产生了一个事务,修改了一个表的数据,另一个用户这时候读取了这个表数据,并根据这个表数据做一些操作或分析,然而,那个用户做了事务的回滚,就是取消了一些列操作,另一个用户做的操作和分析就没有意义了,这个就是脏读

不可重复的读取: 一个用户读取同一数据好几次,在他读的期间有另一个用户修改了该数据,这样就会产生读取到的数据不一致

幻觉读:是指当事务不是独立执行时发生的一种现象。例如事务A对一个表中的数据进行了修改，这种修改涉及到表中的全部数据行。同时，事务B也修改这个表中的数据，这种修改是向表中插入一行新数据。那么，操作事务A的用户就会发现表中还有未修改的数据行，就好象发生了幻觉一样

这些情况发生的根本原因是因为在并发访问的时候，没有一个机制避免交叉存取所造成的

我们可以通过隔离级别设置解决这些问题

事务隔离级别:

脏读 不可重复读 幻觉读

未提交读 yes yes yes

提交读 no yes yes

可重复读 no no yes

可串行读 no no no

/**

*知识点:

*DatabaseMetaData接口:得到数据库驱动程序的全局信息

*程序目标:

*JdbcUtil.java:工具类

*DBMetadata.java:判断这个数据库都支持哪些事务隔离级别

package moudule1.data;

import moudule1.com.*;

import java.sql.*;

public class DBMetadata{

public static void main(String[] args)throws Exception{

Connection con=JdbcUtil.getConnection();

DatabaseMetaData dbmd=con.getMetaData();

System.out.println(dbmd.supportsTransactionIsolationLevel(Connection.TRANSACTION_NONE));

System.out.println(dbmd.supportsTransactionIsolationLevel(Connection.TRANSACTION_READ_UNCOMMITTED));

System.out.println(dbmd.supportsTransactionIsolationLevel(Connection.TRANSACTION_READ_COMMITTED));

System.out.println(dbmd.supportsTransactionIsolationLevel(Connection.TRANSACTION_REPEATABLE_READ ));

System.out.println(dbmd.supportsTransactionIsolationLevel(Connection.TRANSACTION_SERIALIZABLE));

con.close();

}

Java中的事务的处理:

/**

* 知识点:

* java中事务的处理

* Java文件:

* AccountBiz.java:事务处理的一个方法

* Bank.java:man方法

package moudule1.sqlException;

import java.sql.*;

import moudule1.sqlException.*;

import moudule1.com.*;

public class Bank{

public static void main(String[] args){

try{

AccountBiz.fangfa(1,2,Integer.parseInt(args[0]));

System.out.println("成功");

}catch(Exception e){

System.out.println("全部失败");

}

package moudule1.sqlException;

import java.sql.*;

import moudule1.com.*;

public class AccountBiz{

public static void fangfa(int a,int b,int price)throws Exception{

String sql1 = "update allan_account set balance = balance + " + price + " where id = " + a;

String sql2="update allan_account set balance = balance - " + price + " where id = " + b;

System.out.println(sql1);

System.out.println(sql2);

Connection con=null;

Statement st=null;

try{

con=JdbcUtil.getConnection();

con.setAutoCommit(false);

st=con.createStatement();

st.executeUpdate(sql1);

int row=st.executeUpdate(sql2);

if(row==0){

throw new SQLException();

}

con.commit();

}catch(SQLException e){

try{

con.rollback();

}catch(SQLException es){

}

throw new Exception("继续抛个异常,这是抛给调用此方法的地方");

}finally{

JdbcUtil.close(con,st);

}

在java中得到结果集的字段信息:

ResutlSetMetaData接口可以实现这个目标

Rs.getMetaData()可以得到ResutlSetMetaData接口对象

/**

*知识点:

*得到表中列的信息

*java文件:

*ResultSetUtil.java

*testResultSetUtil.java

package moudule1.testResultSetUtil;

import java.sql.*;

import moudule1.com.*;

public class testResultSetUtil{

public static void main(String[] args){

String sql="select * from yuchen_user";

Connection con=null;

Statement st=null;

ResultSet rs=null;

try{

con=JdbcUtil.getConnection();

st=con.createStatement();

rs=st.executeQuery(sql);

ResultSetUtil.print(rs);

}catch(Exception e){

System.out.println("出错");

}finally{

JdbcUtil.close(con,st,rs);

}

package moudule1.com;

import java.sql.*;

public class ResultSetUtil{

public static void print(ResultSet rs)throws SQLException{

ResultSetMetaData rsmd=rs.getMetaData();

int count=rsmd.getColumnCount();

while(rs.next()){

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

if(i!=1){

System.out.print(",");

}

String colName=rsmd.getColumnName(i);

String value=rs.getString(colName);

// String value=rs.getString(colName);

System.out.print(colName+"="+value);

}

System.out.println();

}

项目中关于代码复用的知识:

1. 采取复用

2. 继承复用

3. 聚合复用

4. static工具方法复用

JDBC2.0特性:

结果集的特性:

1. 滚动特性

2. 更新特性

什么是滚动特性?在1.0中游标只能前进活动,但是在2.0中游标更加灵活了,可向前可向后

绝对定位,相对定位,向前向后

Scrollabilty游标控制

向前和向后滚动

绝对和相对游标指定

ResultSet rs=stat.createStatement();

rs.absolute(int a);

rs.afterLast();

rs.beforeFirst();

rs.first();

rs.last();

rs.next();

rs.previous();

rs.relative(int);

rs.isAfterLast();

rs.isBeforeFirst();

rs.isFirst();

rs.isLast();

/**

* 知识点:

* JDBC2.0特性:ResultSet特性:滚动特性

* 程序目标:

* 测试ResultSet滚动特性

* ConnectionFactory.java:数据库连接对象工厂

* JdbcUtil.java:关闭资源

* ResultSetScroll.java:测试类,向下读取,向上读取,绝对读取,相对读取

package moudule1.resultset20.youbiao;

import java.sql.*;

import moudule1.com.*;

public class ResultSetScroll {

/**

* @param args

* @throws Exception

public static void main(String[] args) throws Exception {

// TODO Auto-generated method stub

Connection con=null;

Statement st=null;

ResultSet rs=null;

try {

con=ConnectionFactory.getConnection();

st=con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);

rs=st.executeQuery("select * from yuchen_user");

xiangxia(rs);

xiangshang(rs);

juedui(rs);

xiangdui(rs);

} catch (SQLException e) {

// TODO Auto-generated catch block

e.printStackTrace();

}finally{

JdbcUtil.close(con,st,rs);

}

public static void xiangxia(ResultSet rs) throws SQLException{

if(!rs.isBeforeFirst()){

rs.beforeFirst();

}

while(rs.next()){

System.out.print(rs.getInt(1));

System.out.println(rs.getString(2));

}

public static void xiangshang(ResultSet rs) throws SQLException{

if(!rs.isAfterLast()){

rs.afterLast();

}

while(rs.previous()){

System.out.print(rs.getInt(1));

System.out.println(rs.getString(2));

}

public static void juedui(ResultSet rs) throws SQLException{

rs.absolute(2);

System.out.print(rs.getInt(1));

System.out.println(rs.getString(2));

}

public static void xiangdui(ResultSet rs) throws SQLException{

if(rs.getRow()==0||!rs.last()){

rs.last();

}

while(!rs.isBeforeFirst()){

System.out.print(rs.getInt(1));

System.out.println(rs.getString(2));

rs.relative(-1);

}

package moudule1.com;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.SQLException;

public class ConnectionFactory {

public static Connection getConnection() throws Exception{

Class.forName("oracle.jdbc.driver.OracleDriver");

return DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:name","scott","tiger");

}

什么是更新特性?

第一,可修改结果集中的数据,并影响数据库的数据

第二,可以插入行记录,并影响数据库数据

/**

* 知识点:

* jdbc2.0特性:ResultSet特性:数据记录更新特性:修改一条记录

* 程序目标:

* 1.读取一个表的数据

* 2.得到结果集后,修改结果集中的一条数据

* ConnectionFactory.java

* JdbcUtil.java

* UpdataResultSet.java

* 注意点:

* 1.sql语句必须指明要查询的表字段名

* 2.必须是单表查询

package moudule1.resultset20.updata;

import java.sql.Connection;

import java.sql.ResultSet;

import java.sql.Statement;

import moudule1.com.ConnectionFactory;

import moudule1.com.JdbcUtil;

public class UpdataResultSet {

/**

* @param args

public static void main(String[] args) {

// TODO Auto-generated method stub

String sql="select id,name from laji";

System.out.println(sql);

Connection con=null;

Statement st=null;

ResultSet rs=null;

try {

con=ConnectionFactory.getConnection();

st=con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_UPDATABLE);

rs=st.executeQuery(sql);

while(rs.next()){

System.out.print(rs.getInt(1));

System.out.println(rs.getString(2));

}

rs.absolute(1);

// rs.updateInt(1,10);

rs.updateString(2,"laoda");

rs.updateRow();

System.out.print(rs.getInt(1));

System.out.println(rs.getString(2));

} catch (Exception e) {

// TODO Auto-generated catch block

e.printStackTrace();

}finally{

JdbcUtil.close(con,st,rs);

}

/**

* 知识点:

* ResultSet特性:更新数据:插入一行记录

* 程序目标:

* 1.打开一个结果集

* 2.插入一行数据

* InsertResultSet.java

* ConnectionFactory.java

* JdbcUtil.java

* 注意:

* 1.必须有主键字段

* 2.只能是单表查询出来的结果集

package moudule1.resultset20.updata;

import java.sql.*;

import moudule1.com.*;

public class InsertResultSet {

/**

* @param args

* @throws Exception

public static void main(String[] args) {

// TODO Auto-generated method stub

Connection con=null;

Statement st=null;

ResultSet rs=null;

try {

con=ConnectionFactory.getConnection();

st=con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_UPDATABLE);

rs=st.executeQuery("select empno,ename from emp");

// rs.absolute(2);

rs.moveToInsertRow();

rs.updateInt(1,3);

rs.updateString(2,"kongming");

rs.insertRow();

} catch (Exception e) {

// TODO Auto-generated catch block

e.printStackTrace();

}finally{

JdbcUtil.close(con,st,rs);

}

Statement批量更新(批处理):

什么是批处理?

将一组sql语句进行处理

要想执行批处理,前提是数据库驱动要支持这个功能

statement结构:

PreparedStatement: 动态sql

CallabeStatement: 存储过程

/**

* 知识点:

* Statement批处理

* API:

* Statement接口:1.addBatch():添加sql语句 2.executeBatch():执行一组

* sql语句

* 程序目标:

* 批量执行一组sql语句

* AcconuntService.java

* ConnectionFactory.java

* JdbcUtil.java

package moudule1.Statement20;

import java.sql.*;

import moudule1.com.*;

public class AccountService {

public void addByStatement() throws Exception{

Connection con=null;

Statement st=null;

try {

con=ConnectionFactory.getConnection();

st=con.createStatement();

st.addBatch("insert into yuchen_user (id,name) values (7,'zhu')");

st.addBatch("insert into yuchen_user (id,name) values (8,'liu')");

st.executeBatch();

con.commit();

} catch (Exception e) {

// TODO Auto-generated catch block

e.printStackTrace();

try {

con.rollback();

} catch (SQLException e1) {

// TODO Auto-generated catch block

e1.printStackTrace();

}

throw e;

}

public void addByPreparedStatement() throws Exception{

Connection con=null;

PreparedStatement pst=null;

try {

con=ConnectionFactory.getConnection();

pst=con.prepareStatement("insert into yuchen_user (id,name) values (?,?)");

pst.setInt(1,9);

pst.setString(2,"hello");

pst.addBatch();

pst.setInt(1,11);

pst.setString(2,"world");

pst.addBatch();

pst.executeBatch();

con.commit();

} catch (Exception e) {

// TODO Auto-generated catch block

e.printStackTrace();

try {

con.rollback();

} catch (SQLException e1) {

// TODO Auto-generated catch block

e1.printStackTrace();

}

throw e;

}

/**

* @param args

* @throws Exception

public static void main(String[] args) throws Exception {

// TODO Auto-generated method stub

AccountService as=new AccountService();

if(args.length>0){

as.addByStatement();

}else

as.addByPreparedStatement();

}

高级数据类型:sql3类型

Blob: SQLBlob

Clob: SQLClob

Array:SQL (create type as varray)

Struct:Structure (create type as object )

Ref: SQLref

什么是Blob类型?可以存储大型二进制对象，如图形、视频剪辑和声音文件等

CREATE TABLE ImageLibrary

(

id number(9) not null,

name varchar2(30) not null,

image blob,

constraint PK_ImageLibrary primary key(id)

)

/**

* 知识点:

* blob类型

* 问题:

* 1.什么是blob类型,有什么用?

* 用来存储大型二进制对象,如存储图片,图象,音频等

* 2.如何存储在数据库中,怎么读,怎么存?原理是什么?

* 将一个图片存到数据库:blob字段里存放着图片,因为是文件,使用流

* 从数据库的blob字段中得到图片并显示图片,因为是文件,使用流

* 3.empty_blob()是什么意思?

* 这是oracle中的函数,表达是:空blob对象,可以理解为相当java中的null;

* 4.for update是什么意思?

* 5.java中的Blob是什么,有什么用?

* java和各个数据库中的数据类型都有对应的关系:如java中的String

* 对应数据库中的varchar,varchar2等,int对应number等等,那么java

* 中的Blob是个数据类型,它对应的是数据库中的blob类型

* API:

* Blob接口:setBinaryStream(long pos):得到一个输出流,这个输出流是将图片等

* 流向数据库中的blob类型的字段的,可以帮助完成将一个图片写入到数据库

* 参数pos表示写入的开头位置,从0开始

* BufferedInputStream接口:int read():表示以字节为单位进行输入

* 如果返回-1,表示到了末尾了也就是读取完毕了

package moudule1.blob;

import java.sql.*;

import moudule1.com.*;

import java.io.*;

public class ImageLibraryService {

public void addImage(long id,String name,String path){

Connection con=null;

// Statement st=null;

PreparedStatement pst=null;

ResultSet rs=null;

try {

con=ConnectionFactory.getConnection();

con.setAutoCommit(false);

pst=con.prepareStatement("insert into ImageLibrary (id,name,image) values (?,?,empty_blob())");

pst.setLong(1,id);

pst.setString(2,name);

pst.executeUpdate();

JdbcUtil.close(pst);

pst=con.prepareStatement("select image from ImageLibrary where id=? for update");

pst.setLong(1,id);

rs=pst.executeQuery();

if(rs.next()){

Blob blob=rs.getBlob(1);

BufferedInputStream bis=new BufferedInputStream(new FileInputStream(path));

OutputStream os=blob.setBinaryStream(0);

BufferedOutputStream bos=new BufferedOutputStream(os);

int c;

while((c=bis.read())!=-1){

bos.write(c);

}

bis.close();

bos.close();

}

con.commit();

} catch (Exception e) {

// TODO Auto-generated catch block

e.printStackTrace();

try {

con.rollback();

} catch (SQLException e1) {

// TODO Auto-generated catch block

e1.printStackTrace();

}

}finally{

JdbcUtil.close(con,pst,rs);

}

public void restoreImage(long id,String filename){

Connection con=null;

PreparedStatement pst=null;

ResultSet rs=null;

try {

con=ConnectionFactory.getConnection();

pst=con.prepareStatement("select image from ImageLibrary where id=?");

pst.setLong(1,id);

rs=pst.executeQuery();

if(rs.next()){

Blob blob=rs.getBlob(1);

InputStream is=blob.getBinaryStream();

BufferedInputStream bis=new BufferedInputStream(is);

BufferedOutputStream bos=new BufferedOutputStream(new FileOutputStream(filename));

int c;

while((c=bis.read())!=-1){

bos.write(c);

}

bis.close();

bos.close();

}

con.commit();

} catch (Exception e) {

// TODO Auto-generated catch block

e.printStackTrace();

try {

con.rollback();

} catch (SQLException e1) {

// TODO Auto-generated catch block

e1.printStackTrace();

}

}finally{

JdbcUtil.close(con,pst,rs);

}

/**

* @param args

public static void main(String[] args) {

// TODO Auto-generated method stub

ImageLibraryService is=new ImageLibraryService();

if(args.length==3){

is.addImage(Long.parseLong(args[0]),args[1],args[2]);

}else

is.restoreImage(Long.parseLong(args[0]),args[1]);

}

什么是Clob?

相当与long型,但是比它性能还要好,用于字符型的存储,使用字符流处理读取对象

一、 CLOB对象的存取
1、往数据库中插入一个新的CLOB对象
public static void clobInsert(String infile) throws Exception
{
/* 设定不自动提交 */
boolean defaultCommit = conn.getAutoCommit();
conn.setAutoCommit(false);
try {
/* 插入一个空的CLOB对象 */
stmt.executeUpdate("INSERT INTO TEST_CLOB VALUES (’111’, EMPTY_CLOB())");
/* 查询此CLOB对象并锁定 */
ResultSet rs = stmt.executeQuery("SELECT CLOBCOL FROM TEST_CLOB WHERE ID=’111’ FOR UPDATE");
while (rs.next()) {
/* 取出此CLOB对象 */
oracle.sql.CLOB clob = (oracle.sql.CLOB)rs.getClob("CLOBCOL");
/* 向CLOB对象中写入数据 */
BufferedWriter out = new BufferedWriter(clob.getCharacterOutputStream());
BufferedReader in = new BufferedReader(new FileReader(infile));
int c;
while ((c=in.read())!=-1) {
out.write(c);
}
in.close();
out.close();
}
/* 正式提交 */
conn.commit();
} catch (Exception ex) {
/* 出错回滚 */
conn.rollback();
throw ex;
}
/* 恢复原提交状态 */
conn.setAutoCommit(defaultCommit);
}
2、修改CLOB对象（是在原CLOB对象基础上进行覆盖式的修改）
public static void clobModify(String infile) throws Exception
{
/* 设定不自动提交 */
boolean defaultCommit = conn.getAutoCommit();
conn.setAutoCommit(false);
try {
/* 查询CLOB对象并锁定 */
ResultSet rs = stmt.executeQuery("SELECT CLOBCOL FROM TEST_CLOB WHERE ID=’111’ FOR UPDATE");
while (rs.next()) {
/* 获取此CLOB对象 */
oracle.sql.CLOB clob = (oracle.sql.CLOB)rs.getClob("CLOBCOL");
/* 进行覆盖式修改 */
BufferedWriter out = new BufferedWriter(clob.getCharacterOutputStream());
BufferedReader in = new BufferedReader(new FileReader(infile));
int c;
while ((c=in.read())!=-1) {
out.write(c);
}
in.close();
out.close();
}
/* 正式提交 */
conn.commit();
} catch (Exception ex) {
/* 出错回滚 */
conn.rollback();
throw ex;
}
/* 恢复原提交状态 */
conn.setAutoCommit(defaultCommit);
}
3、替换CLOB对象（将原CLOB对象清除，换成一个全新的CLOB对象）
public static void clobReplace(String infile) throws Exception
{
/* 设定不自动提交 */
boolean defaultCommit = conn.getAutoCommit();
conn.setAutoCommit(false);
try {
/* 清空原CLOB对象 */
stmt.executeUpdate("UPDATE TEST_CLOB SET CLOBCOL=EMPTY_CLOB() WHERE ID=’111’");
/* 查询CLOB对象并锁定 */
ResultSet rs = stmt.executeQuery("SELECT CLOBCOL FROM TEST_CLOB WHERE ID=’111’ FOR UPDATE");
while (rs.next()) {
/* 获取此CLOB对象 */
oracle.sql.CLOB clob = (oracle.sql.CLOB)rs.getClob("CLOBCOL");
/* 更新数据 */
BufferedWriter out = new BufferedWriter(clob.getCharacterOutputStream());
BufferedReader in = new BufferedReader(new FileReader(infile));
int c;
while ((c=in.read())!=-1) {
out.write(c);
}
in.close();
out.close();
}
/* 正式提交 */
conn.commit();
} catch (Exception ex) {
/* 出错回滚 */
conn.rollback();
throw ex;
}
/* 恢复原提交状态 */
conn.setAutoCommit(defaultCommit);
}
4、CLOB对象读取
public static void clobRead(String outfile) throws Exception
{
/* 设定不自动提交 */
boolean defaultCommit = conn.getAutoCommit();
conn.setAutoCommit(false);
try {
/* 查询CLOB对象 */
ResultSet rs = stmt.executeQuery("SELECT * FROM TEST_CLOB WHERE ID=’111’");
while (rs.next()) {
/* 获取CLOB对象 */
oracle.sql.CLOB clob = (oracle.sql.CLOB)rs.getClob("CLOBCOL");
/* 以字符形式输出 */
BufferedReader in = new BufferedReader(clob.getCharacterStream());
BufferedWriter out = new BufferedWriter(new FileWriter(outfile));
int c;
while ((c=in.read())!=-1) {
out.write(c);
}
out.close();
in.close();
}
} catch (Exception ex) {
conn.rollback();
throw ex;
}
/* 恢复原提交状态 */
conn.setAutoCommit(defaultCommit);
}

Array类型: 对应的是sql中的自定义类型

/**

* 知识点:

* Array--sql varray(自定义类型)

* 问题:

* 1.什么是array类型?有什么用的?

* 在数据库中自定义类型可以存放一组值,这组值是什么类型自己可以设置

* 例如:可以用来存放用户的注册信息等等

* API:

* ResultSet接口:Array getArray(列的位置号):从指定的表列中读取该列的

* 值,这个值是java Array对象,Array接口相当于指针,指向这个值

* Array接口:Object getArray():得到Array对象中的元素值,一般把它强制

* 转换为String[]类型,然后可以打印出来

* 程序目标:

* JdbcUtil.java

* ConnectionFactory.java

* UserService.java

* 读取带有自定义类型的表中的数据

package moudule1.sql3.array;

import java.sql.*;

import moudule1.com.*;

public class UserService {

public static void addArray(){

Connection con=null;

Statement st=null;

try {

String sql = "insert into User_Array(id, username, password, hobbies) values ";

sql += "(1, 'alan', '123', HOBBIES('swim', 'walking', 'reading', 'skating', 'shooting'))";

con=ConnectionFactory.getConnection();

st=con.createStatement();

st.executeUpdate(sql);

} catch (Exception e) {

// TODO Auto-generated catch block

e.printStackTrace();

}finally{

JdbcUtil.close(con,st);

}

public static void readArray(){

Connection con=null;

Statement st=null;

ResultSet rs=null;

try {

con=ConnectionFactory.getConnection();

st=con.createStatement();

rs=st.executeQuery("select id,username,password,hobbies from User_Array");

while(rs.next()){

System.out.print(rs.getInt(1));

System.out.print(","+rs.getString(2));

System.out.print(","+rs.getString(3));

Array array=rs.getArray(4);

String[] arr=(String[])array.getArray();

System.out.print(", hobbies {");

for(int i=0;i<arr.length;i++){

if(i!=0){

System.out.print(",");

}

System.out.print(arr[i]);

}

System.out.println("}");

}

} catch (Exception e) {

// TODO Auto-generated catch block

e.printStackTrace();

}finally{

JdbcUtil.close(con,st,rs);

}

public static void main(String[] args) {

// TODO Auto-generated method stub

if(args.length==1){

UserService.addArray();

}else

UserService.readArray();

}

Struct:Structure (create type as object )

把sql中的Structure看成是一个对象类型,里面包含属性值,java中的Struct接口就是指向这个对象类型中的属性值的

CREATE TYPE CourseStruct AS object

(

no varchar2(10),

name varchar2(60),

fee number(5)

)

CREATE TABLE Student_Struct

(

id number(9),

name varchar2(20),

attendcourse CourseStruct,

constraint PK_Student primary key(id)

)

/**

* 知识点:

* Struct---Structure(create type 名字 as Object)

* 问题:

* API:

* StringBuffer类:StringBuffer delete(int s,int end):删除StringBuffer中

* 的字符,参数:可以指定删除哪一段

* 程序目标:

* 写入并读取含有Structure类型的字段的表中的值

package moudule1.sql3.struct;

import java.sql.Connection;

import java.sql.ResultSet;

import java.sql.Statement;

import java.sql.Struct;

import moudule1.com.ConnectionFactory;

import moudule1.com.JdbcUtil;

public class StudentService

{

public void addStudents()

{

Connection con = null;

Statement st = null;

try

{

con = ConnectionFactory.getConnection();

st = con.createStatement();

StringBuffer sql = new StringBuffer();

sql.append("insert into Student_Struct(id, name, attendcourse) ");

sql.append("values(1, 'Alan', CourseStruct('c001', 'Object Orient Programming With Java', 13800))");

System.out.println(sql.toString());

st.executeUpdate(sql.toString());

sql.delete(0, sql.length());

sql.append("insert into Student_Struct(id, name, attendcourse) ");

sql.append("values(2, 'elen', CourseStruct('c002', 'Mastering asp.net Using C#', 10000))");

System.out.println(sql.toString());

st.executeUpdate(sql.toString());

} catch (Exception e)

{

e.printStackTrace();

} finally

{

JdbcUtil.close(con, st);

}

public void showStudents()

{

Connection con = null;

Statement st = null;

ResultSet rs = null;

try

{

con = ConnectionFactory.getConnection();

st = con.createStatement();

rs = st.executeQuery("select id, name, attendcourse from Student_Struct order by id");

while (rs.next())

{

System.out.print("id = " + rs.getLong(1));

System.out.print(",name = " + rs.getString(2));

System.out.println(",attendcourse = {");

Struct course = (Struct) rs.getObject("attendcourse");

Object[] attributes = course.getAttributes();

System.out.print("no = " + attributes[0]);

System.out.print(",name = " + attributes[1]);

System.out.println(", fee = " + attributes[2]);

System.out.println("}");

}

} catch (Exception e)

{

e.printStackTrace();

} finally

{

JdbcUtil.close(con, st, rs);

}

public static void main(String[] args)

{

StudentService service = new StudentService();

if (args.length == 0)

{

service.showStudents();

} else

{

service.addStudents();

}

ref:

CREATE TABLE Course_Tbl of CourseStruct

INSERT INTO Course_Tbl VALUES ('c001', 'java', 13800)

INSERT INTO Course_Tbl VALUES ('c002', 'c++', 3000)

INSERT INTO Course_Tbl VALUES ('c002', 'dot net', 8000)

说明:CourseStruct为struct类型,上个例子已经创建了该类型

/**

* 知识点:

* ref

package linshi.allanlxf.jdbc.sql3;

import java.sql.Connection;

import java.sql.Ref;

import java.sql.ResultSet;

import java.sql.Statement;

import java.sql.Struct;

import moudule1.com.ConnectionFactory;

import moudule1.com.JdbcUtil;

/**

* @author alan

* @version 1.0

public class CourseService

{

public void showCourses()

{

Connection con = null;

Statement st = null;

ResultSet rs = null;

try

{

con = ConnectionFactory.getConnection();

st = con.createStatement();

rs = st.executeQuery("select ref (x) from Course_Tbl x");

while (rs.next())

{

Ref courseRef = (Ref) rs.getObject(1);

Struct course = (Struct) courseRef.getObject();

Object[] courseAttributes = course.getAttributes();

System.out.print("x[no = " + courseAttributes[0]);

System.out.print(",name = " + courseAttributes[1]);

System.out.print(",fee = " + courseAttributes[2]);

System.out.println("]");

}

} catch (Exception e)

{

e.printStackTrace();

} finally

{

JdbcUtil.close(con, st, rs);

}

public static void main(String args[]) throws Exception

{

CourseService service = new CourseService();

service.showCourses();

}

综合例子:

package moudule1.com;

import java.sql.*;

public class JdbcUtil{

public static Connection getConnection() throws Exception{

Class.forName("oracle.jdbc.driver.OracleDriver");

return DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:name", "scott","tiger");

}

public static void close(Connection con,Statement st){

close(con);

close(st);

}

public static void close(Connection con,Statement st,ResultSet rs){

close(con,st);

close(rs);

}

public static void close(Connection con){

try{

con.close();

}catch(Exception e){

}

public static void close(Statement st){

try{

st.close();

}catch(Exception e){

}

public static void close(ResultSet rs){

try{

rs.close();

}catch(Exception e){

}

public static void close(ResultSet rs,Statement st){

close(st);

close(rs);

}

public static Timestamp gettime(Connection con){

PreparedStatement pst=null;

ResultSet rs = null;

Timestamp time = null;

try {

pst=con.prepareStatement("select sysdate from dual");

rs=pst.executeQuery();

rs.next();

time=rs.getTimestamp(1);

} catch (SQLException e) {

// TODO Auto-generated catch block

e.printStackTrace();

}finally

{

JdbcUtil.close(rs, pst);

}

return time;

}

/**

* 知识点:

* Oracle数据库的综合访问

* API:

* 1.Date类:static Date valueOf(String date):将输入的字符串时间转化为

* Date的值

* 2.ResultSet:getTimestamp():得到表中的时间值

package moudule1.sql3.zonghe;

import java.sql.Connection;

import java.sql.Date;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import moudule1.com.*;

public class UserAction

{

public void addUser(int id, String name, Date birthday) throws Exception

{

Connection con = null;

PreparedStatement ps = null;

try

{

con = ConnectionFactory.getConnection();

ps = con.prepareStatement("insert into sys_user(id, name, birthday, cretime) values(?, ?, ?, ?)");

ps.setInt(1, id);

ps.setString(2, name);

ps.setDate(3, birthday);

ps.setTimestamp(4, JdbcUtil.gettime(con));

ps.executeUpdate();

}catch(Exception e)

{

e.printStackTrace();

}finally

{

JdbcUtil.close(con, ps);

}

public void showUsers() throws Exception

{

Connection con = null;

PreparedStatement ps = null;

ResultSet rs = null;

try

{

con = ConnectionFactory.getConnection();

ps = con.prepareStatement("select * from sys_user");

rs = ps.executeQuery();

while(rs.next())

{

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

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

System.out.println(rs.getDate("birthday"));

System.out.println(rs.getTimestamp("cretime"));

}

}catch(Exception e)

{

e.printStackTrace();

}finally

{

JdbcUtil.close(con, ps);

}

public static void main(String[] args) throws Exception

{

UserAction action = new UserAction();

if(args.length > 0)

{

action.addUser(2, "guest", Date.valueOf("1985-09-09"));

}else

{

action.showUsers();

}