datebase.java

oracle数据库连接类

package bbs; 

/* 
数据库操作类
包括数据库的连接、数据的插入、删除、修改等
2004年8月

*/ 

import java.sql.*; 
import java.lang.*; 
import java.io.*; 
import java.util.*; 
import sun.io.*; 
import oracle.sql.*;

public class CTOdbc 
    { 
    ResultSet rstSql; 
    String strCon; 
    String strSql; 
    boolean status; 
    long rowcount; 
    int page; 
    int pagesize; 
    long pagecount; 
    long firstrecord; 
	//static String 


 private static Connection conn=null;// SQL语句对象
 private static Statement stmt=null;
   /**
    * @roseuid 3EDA089E02BC
    */
    public boolean connect() 
        { 
		  /*选择默认连接*/
        this.strCon = "jdbc:oracle:thin:@Ip:1521:url"; 
		 /*连接默认的数据库*/
        try 
            { 
            Class.forName("oracle.jdbc.driver.OracleDriver"); 
            this.conn = java.sql.DriverManager.getConnection(this.strCon,"user","key");  
			//replace with your default database connection configure option 
			this.stmt = this.conn.createStatement();
            this.status = true; 
            return true; 
            } 
        catch(Exception e) 
            { 
            this.status = false; 
            return false; 
            } 
        } 

    //connect to the custom database 
    public boolean connect(String conName,String username,String password) 
        { 
        this.strCon = conName;                                                   
        try 
            { 
            Class.forName("oracle.jdbc.driver.OracleDriver"); 
			/*加载驱动*/
            this.conn = java.sql.DriverManager.getConnection(this.strCon,username,password);
			/*建立连接*/
			this.stmt = this.conn.createStatement();
            this.status = true; 
            return true; 
            } 
        catch(Exception e) 
            { 
            this.status = false; 
            return false; 
            } 
        } 

    public boolean execute(String s) 
        { /*执行insert、update、delete操作，将SQL语句直接赋给sting行的s即可*/
        try 
            { 
            this.stmt = this.conn.createStatement(); 
            this.stmt.executeUpdate(s); 
            this.status = true; 
            return true; 
            } 
        catch(Exception e) 
            { 
            this.status = false; 
            return false; 
            } 
        } 

    //query the data from database 
    public boolean query(String s) 
        { //查询操作
        try 
            { 
            this.rowcount = 0; 
            this.stmt = this.conn.createStatement(); 
            this.rstSql = this.stmt.executeQuery(s); 
            while (this.nextrecord()) 
                { 
                this.rowcount++; 
                } 
            this.rstSql = this.stmt.executeQuery(s); 
            this.status = true; 
            return true; 
            } 
        catch(Exception e) 
            { 
            this.status = false; 
            return false; 
            } 
        } 
    /**
    * 向数据库中插入一个新的BLOB对象
    *
    * @param infile - 数据文件
    * @throws java.lang.Exception
    * @roseuid 3EDA04E300F6
    */
   public static void blobInsert(String infilename,String BlobClonum,String InsertEmpty,String SelectForUpdate) throws Exception
   {
       /* 设定不自动提交 */
       boolean defaultCommit =conn.getAutoCommit();
       conn.setAutoCommit(false);

       try {
           /* 插入一个空的BLOB对象 */
           stmt.executeUpdate(InsertEmpty);//"INSERT INTO TEST_BLOB VALUES ('222', EMPTY_BLOB())"
           /* 查询此BLOB对象并锁定 */
           ResultSet rs = stmt.executeQuery(SelectForUpdate);//"SELECT BLOBCOL FROM TEST_BLOB WHERE ID='222' FOR UPDATE"
           while (rs.next()) {
               /* 取出此BLOB对象 */
               oracle.sql.BLOB blob = (oracle.sql.BLOB)rs.getBlob(BlobClonum);//oracle.sql.BLOB blob = (oracle.sql.BLOB)rs.getBlob("BLOBCOL")
               /* 向BLOB对象中写入数据 */
               BufferedOutputStream out = new BufferedOutputStream(blob.getBinaryOutputStream());
               BufferedInputStream in = new BufferedInputStream(new FileInputStream(infilename));
               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);
   }

   /**
    * 修改BLOB对象（是在原BLOB对象基础上进行覆盖式的修改）
    *
    * @param infile - 数据文件
    * @throws java.lang.Exception
    * @roseuid 3EDA04E90106
    */
   public static void blobModify(String infile,String BlobClonum,String SelectForUpdate) throws Exception
   {
       /* 设定不自动提交 */
       boolean defaultCommit = conn.getAutoCommit();
       conn.setAutoCommit(false);

       try {
           /* 查询BLOB对象并锁定 */
           ResultSet rs = stmt.executeQuery(SelectForUpdate);//"SELECT BLOBCOL FROM TEST_BLOB WHERE ID='222' FOR UPDATE"
           while (rs.next()) {
               /* 取出此BLOB对象 */
               oracle.sql.BLOB blob = (oracle.sql.BLOB)rs.getBlob(BlobClonum);//"BLOBCOL"
               /* 向BLOB对象中写入数据 */
               BufferedOutputStream out = new BufferedOutputStream(blob.getBinaryOutputStream());
               BufferedInputStream in = new BufferedInputStream(new FileInputStream(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);
   }

   /**
    * 替换BLOB对象（将原BLOB对象清除，换成一个全新的BLOB对象）
    *
    * @param infile - 数据文件
    * @throws java.lang.Exception
    * @roseuid 3EDA0505000C
    */
   public static void blobReplace(String infile,String Blobcolnum,String UpdateEmpty,String SelectForUpdate) throws Exception
   {
       /* 设定不自动提交*/
       boolean defaultCommit = conn.getAutoCommit();
       conn.setAutoCommit(false);

       try {
           /* 清空原BLOB对象 */
           stmt.executeUpdate(SelectForUpdate);//"UPDATE TEST_BLOB SET BLOBCOL=EMPTY_BLOB() WHERE ID='222'"
           /* 查询此BLOB对象并锁定 */
           ResultSet rs = stmt.executeQuery(SelectForUpdate);//"SELECT BLOBCOL FROM TEST_BLOB WHERE ID='222' FOR UPDATE"
           while (rs.next()) {
               /* 取出此BLOB对象 */
               oracle.sql.BLOB blob = (oracle.sql.BLOB)rs.getBlob(Blobcolnum);//"BLOBCOL"
               /* 向BLOB对象中写入数据 */
               BufferedOutputStream out = new BufferedOutputStream(blob.getBinaryOutputStream());
               BufferedInputStream in = new BufferedInputStream(new FileInputStream(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);
   }

   /**
    * BLOB对象读取
    * @param outfile - 输出文件名
    * @throws java.lang.Exception
    * @roseuid 3EDA050B003B
    */
   public static void blobRead(String outfile,String BlobClonum,String SelectOnly) throws Exception
   {
       /* 设定不自动提交 */
       boolean defaultCommit = conn.getAutoCommit();
       conn.setAutoCommit(false);

       try {
           /* 查询BLOB对象 */
           ResultSet rs = stmt.executeQuery(SelectOnly);//"SELECT BLOBCOL FROM TEST_BLOB WHERE ID='222'"
           while (rs.next()) {
               /* 取出此BLOB对象 */
               oracle.sql.BLOB blob = (oracle.sql.BLOB)rs.getBlob(BlobClonum);//"BLOBCOL"
               /* 以二进制形式输出 */
               BufferedOutputStream out = new BufferedOutputStream(new FileOutputStream(outfile));
               BufferedInputStream in = new BufferedInputStream(blob.getBinaryStream());
               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);
   } 



    //return the row count 
    public long getrowcount() 
        { 
        return this.rowcount; 
        } 

    //return the pagecount 
    public long getpagecount() 
        { 
        return this.pagecount; 
        } 
     
    //return the resultset of data 
    public String getstring(String s) 
        { 
        try 
            { 
            return this.rstSql.getString(s); 
            } 
        catch(Exception e) 
            { 
            return "not exists"; 
            } 
        } 

    public int getint(String s) 
        { 
        try 
            { 
            return Integer.parseInt(this.rstSql.getString(s)); 
            } 
        catch(Exception e) 
            { 
            return 0; 
            } 
        } 

    //resultset move forward 
    public boolean nextrecord() 
        { 
        try 
            { 
            return this.rstSql.next(); 
            } 
        catch(Exception e) 
            { 
            return false; 
            } 
        } 
     

    //set current page (recall first) 
    public boolean setpage(int size,int no) 
        { 
        this.pagesize = size; 
        this.page = no; 
        this.pagecount = Math.round((this.rowcount - 1) / this.pagesize)+1;  
        this.firstrecord = this.pagesize * ( this.page - 1 ); 
        try 
            { 
            for(int i=0;i<this.firstrecord;i++) 
                if (!this.nextrecord()) break; 
            return true; 
            } 
        catch(Exception e) 
            { 
            return false; 
            } 
        } 
     
    //get string from database and change it into chinese 
    public String readChinese(String s) 
        { 
        try 
            { 
            String temp = new String(s.getBytes("GB2312"),"8859_1"); 
            return temp; 
            } 
        catch(Exception e) 
            { 
            return s; 
            } 
        } 

    //write string to the database"s chinese transform 
    public static String writeChinese(String s) 
        { 
        char[] orig =s.toCharArray(); 
        byte[] dest =new byte[orig.length]; 
        for(int i=0;i<orig.length;i++) 
        dest[i] =(byte)(orig[i]&0xFF); 
        try 
            { 

            ByteToCharConverter toChar =ByteToCharConverter.getConverter("gb2312"); 

            return new String(toChar.convertAll(dest)); 
            } 
        catch(Exception e) 
            { 
            return s; 
            } 
        } 


    //string"s search and replace 
    public String replace(String con ,String tag,String rep){ 

        int j=0; 

        int i=0; 

        int k=0; 

        String RETU=""; 

        String temp =con; 

        int tagc =tag.length(); 

        while(i<con.length()){ 

            if(con.substring(i).startsWith(tag)){ 

                temp =con.substring(j,i)+rep; 

                RETU+= temp; 

                i+=tagc; 

                j=i; 

            } 

            else{ 

                i+=1; 

            } 

             

        } 

        RETU +=con.substring(j); 

        return RETU; 

    }     
     
        public Vector listValue(String con ,String tag){ 

        int j=0; 

        int i=0; 

        int k=0; 

        Vector vv=new Vector(); 

        String temp =con; 

        int tagc =tag.length(); 

        while(i<con.length()){ 

            if(con.substring(i).startsWith(tag)){ 

                temp =con.substring(j,i); 

                vv.addElement(temp); 

                i+=tagc; 

                j=i; 

            } 

            else{ 

                i+=1; 

            } 

             

        } 

        vv.addElement(con.substring(j)); 

        return vv; 

    }     


    //filt the html code & sql symbol 
    public String htmlencode(String s) 
        { 
        try 
            { 
            String temp = this.replace(s,"<","<"); 
            temp = this.replace(temp,">",">"); 
            temp = this.replace(temp,"",""); 
            temp = this.replace(temp,"",""); 
            temp = this.replace(temp," ","&nbsp;"); 
            temp = this.replace(temp," ","<br>"); 
            return temp; 
            } 
        catch(Exception e) 
            { 
            return s; 
            } 
        } 
     
    //return the status of last operation 
    public boolean getstatus() 
        { 

        return this.status; 
        } 
     
    //close all object 
    public boolean close() 
        { 
        try 
            { 
            if (this.conn != null) this.conn.close(); 
            if (this.rstSql != null) this.rstSql.close(); 
            if (this.stmt != null) this.stmt.close(); 
            this.status = true; 
            return false; 
            } 
        catch(Exception e) 
            { 
            this.status = false; 
            return true; 
            } 
        } 

    }