jdbc2

结果可以回滚

结果集可以更新，影响数据库的更新

批处理

 

 

Statement stm=conn.createStatement();

 

stm2=conn.createStatement(int value1,int value2);

  value1:是否可滚动

     ResultSet.TYPE_SCROLL_INSENCITIVE

  value2:是否可更新   

     ResultSet.CONCUR_UPDABLE

 rs=stm2.executeQuery();  

 

 pstm=conn.prepareStatement(sql,int,int);

 

 数据库表数据的分页

 1.db--sql

   子查询和Rownum

 2.jdbc---->可滚动的结果集 

   String sql="select * from s_emp order by start_date";

   rs=stm.execute(sql);

   int num=1; 

   rs.absolute(5);

   while(rs.next()){ 

      if(num>5) return;

      .........

   }    

  

   int[] a=stm.executeBatch(); 

   stm批处理里缓存的是sql命令

   pstm批处理里缓存的是数据    

  

  

   Object----->db

  

  class(entity)-------table

  object(persist object)--------row  

  field------column(oid-----pk)

  relation---------> pk  fk

  

   class Student{

      private Integer oid;

      String name;

      int age;

      String sex;  

      public Student(String name,int age,String sex){

        

      }

   }            

   Student s1=new Student("zhangsan",18,"male");

   Student s2=new Student("lisi",19,"male");

   Student s3=new Student("zhangsan",20,"male");

  

----------------------------------------------------

 one-to-one( Car ---- License )       

 1.java

   class Car{ 

      //oid

      private Integer cid;   

      // 业务属性

      private String manufactory;

      private String productType; 

      //关系属性

      private License license;

      // get 和  set

      // 有参和无参的构造方法  

      public void addLicense(License license){

         this.license=license;

         license.addCar(this);

      } 

      public void removeLicense(){  

         this.license.removeCar();

         this.license=null;

      }

   } 

   class License{   

      private Integer lid;

      private String serialNum;

      private String LicenseType;

      private Car car;

      // get 和  set

      // 有参和无参的构造方法  

      public void addCar(Car car){

         this.car=car;

      } 

      public void removeCar(){ 

         this.car=null;

      }

   }

 2. db

   create table jdbc_car

   ( cid number(7) primary key,

     manufactory varchar2(15) not null,

     producttype varchar2(15) not null,

   );

   create table jdbc_license

   ( lid number(7) primary key,

     serialnum varchar2(15) not null,

     licensetype varchar2(15) not null,

     car_id number(7) references jdbc_car(cid) unique

   );

 3. dao( CarDao--->jdbc_car , LicenseDao--->jdbc_license)

    class CarDao{

      public void insertCar(Car c) throws Exception{

         1.insert into cartable

         2.c.setCid(?);

         3.insert into license 

         4.l.setLid(?);

      }

      public void deleteCar1(Car c) throws Exception{

         1.delete from license

         2.delete from car

         3.c.setOid(null);

         4.license.setLid(null);

         5.c.removeLicense(l);

      }  

      public void deleteCar(Car c) throws Exception{

         1.update license  set  car_id=null

         2.delete from car

         3.c.setOid(null);

         4.license.setLid(null);

         5.c.removeLicense(l);

      }

      public void updateCar(Car c) throws Exception{

          1. update car table;

          2. update license table set car_id=null;

          3?

      }

      public Car queryByCid(Integer cid) throws Exception{

        db-->1.select car table

             2.select license table

        内存--> 3. car.addLicense(license);

        4.return car;

      } 

    }

                 

      main(){ 

         Car c=queryByCarid(222);

         c.addLicense(license);

         dao.update(c);

      }    

  1. 维护数据库

  2. 维护内存中的oid

  3. 维护关系

 

 

TestBatch.Java

import java.sql.*;

 

public class TestBatch {

   public static void main(String[] args) {

       Connection conn=null;

        Statement stm=null;

        try{

          conn=JdbcUtil.getConnection();

          conn.setAutoCommit(false);

          stm=conn.createStatement();

            String sql="insert

into jdbc_account(id,name,passwd,cardid,balance,personId)" +" values(jdbc_account_seq.nextval,'liucy','000','000',10,'0000')";

          stm.addBatch(sql);

         

          sql="update jdbc_account set balance=100000 where name='luxw'";

          stm.addBatch(sql);

         

          sql="delete from  jdbc_account where id=110 or id=104";

          stm.addBatch(sql);

         

          int[] a=stm.executeBatch();

          for(int i:a){

             System.out.println(i);

          }

           conn.commit();

        }catch(Exception e){

          e.printStackTrace();

          if(conn!=null)  try{ conn.rollback();

} catch(Exception ee){}

        }finally{

          JdbcUtil.release(null, stm, conn);

        }

   }

}

 

 

 

 

  

import java.io.FileInputStream;

import java.io.FileOutputStream;

import java.io.InputStream;

import java.io.OutputStream;

import java.sql.Blob;

import java.sql.Connection;

import java.sql.ResultSet;

import java.sql.Statement;

 

public class TestBlob {

    public static void insertBlob(String filename){

       Connection conn=null;

       Statement stm=null;

       ResultSet rs=null;

       try{

          conn=JdbcUtil.getConnection();

         conn.setAutoCommit(false);

         stm=conn.createStatement();

          /*

           * empty_blob()是Oracle提供的单行函数,作用向表里的

          * blob列插入一个空的blob字段,blob字段的值随后用IO

          * 流向数据库里写入

           */

          String sql="insert into largetable(id,name,largefile)"

            +" values(3,'file3',empty_blob())";

         stm.executeUpdate(sql);

         

          sql="select largefile from largetable where id=3";

          rs=stm.executeQuery(sql);

         if(rs.next()){

            Blob blo=rs.getBlob(1);

            System.out.println(blo.getClass().getName());

            oracle.sql.BLOB bo=(oracle.sql.BLOB)blo;

            OutputStream os=bo.getBinaryOutputStream();

            InputStream is=new FileInputStream(filename);

            byte[] b=new byte[1024];

            int len=0,num=0;

            while(true){

                len=is.read(b);

                if(len<=0) break;

                    os.write(b,0,len);

                    if(++num%10==0){

                    System.out.println("10 k  ok");

                    }

            }

            is.close();

              os.close();

         }

         conn.commit();

      }catch(Exception e){

         if(conn!=null)  try{  conn.rollback();} catch(Exception ee){}

      }finally{

         JdbcUtil.release(null,stm,conn);

      }

    }

    public static void queryBlob(String  name){

       Connection conn=null;

       Statement stm=null;

       ResultSet rs=null;

       try{

          conn=JdbcUtil.getConnection();

          conn.setAutoCommit(false);

          stm=conn.createStatement();

         

          String sql="select largefile from largetable where name='"+name+"'";

          System.out.println(sql);

          rs=stm.executeQuery(sql);

          if(rs.next()){

              Blob blo=rs.getBlob(1);

              System.out.println(blo);

              InputStream is=blo.getBinaryStream();

              OutputStream os=new FileOutputStream("copy.rar");

              byte[] b=new byte[1024];

              int len=0,num=0;

              while(true){

                 len=is.read(b);

                 if(len<=0) break;

                    os.write(b,0,len);

                    if(++num%100==0){

                    System.out.println("1m   ok");

                    }

              }

              is.close();

              os.close();

          }

          conn.commit();

       }catch(Exception e){

          if(conn!=null)  try{  conn.rollback();} catch(Exception ee){}

       }finally{

          JdbcUtil.release(null,stm,conn);

       }

    }

   public static void main(String[] args) {

          queryBlob("file3");

   }

}