JAVA資料庫處理(連線,資料查詢,結果集返回)

huidaoli發表於2013-07-23
Java資料庫
package  john
import java.io.IOException;
import java.util.*;
public class QueryDataRow 
{
    public HashMap hashColumn;

    private String[] data;
    public QueryDataRow() 
    {
        super();
    }
    public String get(String str) throws IOException 
    {
        int colindex = ((Integer) (hashColumn.get(str))).intValue();
        return DBUtil.deTransSql(data[colindex]);
    }
    public void setData(String[] s) 
    {
        this.data = new String[s.length];
        for (int i = 0; i < s.length; i++) 
        {
            this.data[i] = s[i];
        }
    }
    public void resetColumn(String columnName, String columnValue) 
    {
        int colindex = ((Integer) (hashColumn.get(columnName))).intValue();
        data[colindex] = columnValue;
    }
}
package john
import java.util.*;
import java.sql.*;
public class QueryData 
{
    private ArrayList rows;
    public QueryData() 
    {
        rows = new ArrayList();
        rows.clear();
    }
    public boolean setData(ResultSet rs) throws Exception 
    {
        boolean ret = false;
        try 
        {
            ResultSetMetaData rsdata = rs.getMetaData();
            int ColumnCount = rsdata.getColumnCount();
            String Columns[] = new String[ColumnCount];

            HashMap hashColumn = new HashMap();

            for (int i = 0; i < ColumnCount; i++) 
            {
                Columns[i] = rsdata.getColumnName(i + 1);
                hashColumn.put(Columns[i], new Integer(i));
            }
            while (rs.next()) 
            {
                String rowdata[] = new String[ColumnCount];

                for (int i = 0; i < ColumnCount; i++) 
                {
                    String column = Columns[i];

                    String value = rs.getString(column);

                    value = (value != null ? value : "");
                    rowdata[i] = value;
                }

                QueryDataRow row = new QueryDataRow();
                row.hashColumn = hashColumn;
                row.setData(rowdata);
                rows.add(row);
            }

            ret = true;
        } 
        catch (Exception e) 
        {
            ret = false;
            throw e;
        }
        return ret;
    }
    public boolean removeRow(int index) throws Exception 
    {
        try 
        {
            if (rows != null) 
            {
                rows.remove(index);
                return true;
            } 
            else 
            {
                return false;
            }
        } 
        catch (Exception ex) 
        {
            throw ex;
        }
    }
    public QueryDataRow getRow(int index) throws Exception 
    {
        try 
        {
            if (rows != null) 
            {
                QueryDataRow row = new QueryDataRow();
                return (QueryDataRow) rows.get(index);
            } 
            else 
            {
                return null;
            }

        } catch (Exception ex) 
        {
            throw ex;
        }
    }
    public int size() 
    {
        if (rows != null) 
        {
            return rows.size();
        } 
        else 
        {
            return 0;
        }
    }
}
package john
import java.io.IOException;
import java.sql.*;
import javax.sql.*;

public class DBUtil 
{
 
 public static final String STRING = "0";
 public static final String DATE = "1";
 public static final String INT = "2";
 
 private Connection conn = null;  //DB接続

 private ResultSet sqlResult = null;
 private DataSource ds = null;
 Statement sqlStatement = null;
 
    /* エラー情報*/
    public String errStr = null; 

 private static Logger logger = Logger.getLogger(DBUtil.class.getName());
 
 static 
 {
  try 
  {
   Class.forName("oracle.jdbc.driver.OracleDriver");
  } 
  catch (Exception ex) 
  {
   ex.printStackTrace();
  }
 } 
 public DBUtil(Connection pConn)
 {
  conn = pConn;  //DB接続

  sqlResult = null;
  ds = null;
  sqlStatement = null;
  errStr = new String("");
 }
  public Connection connOpen() throws Exception
  {
   try 
   {
    if (Common.CONN_FLAG == true)
    {
      String strDbIp = Common.getData("other.dbhost");        
      String strDbPort = Common.getData("other.dbport");      
      String strDbName = Common.getData("other.dbname");     
      String strUsername = Common.getData("other.dbuser");    
      String strPassword = Common.getData("other.dbpswd");   
      String strDBUrl = "jdbc:oracle:thin:@"+strDbIp+":"+strDbPort+":"+strDbName;
      conn = DriverManager.getConnection(strDBUrl, strUsername, strPassword);
     }
    conn.setAutoCommit(false);
    sqlStatement = null;
    sqlStatement = conn.createStatement();
    return conn;
   } 
   catch (SQLException e) 
   {
             String strErr = "SQL connection error: /n"
              + e.getMessage();
             logger.error("DBUTIL",strErr);
             this.errStr += strErr;
    throw new BaseException("syserr");   
   }
  }
 public void connClose() throws Exception
 {
  try 
  {
   if(sqlStatement != null)
    sqlStatement.close();
   sqlStatement = null;
   if(conn != null)
    if(!conn.isClosed())
     conn.close();
   conn = null;
  } 
  catch (SQLException ex) 
  {
   logger.error("DBUTIL",ex.getMessage());
   logger.error("DBUTIL","DB closing exception!");
   throw new BaseException("syserr");
  }
 }
 public QueryData selQuery(String strQuerySQL) throws Exception
 {
  try
  {
   QueryData qd = new QueryData();
   Statement tmpSQLStatement = conn.createStatement();
   tmpSQLStatement.execute(transSql(strQuerySQL));
   sqlResult = null;
   sqlResult = tmpSQLStatement.getResultSet();
   qd.setData(sqlResult);
   if(sqlResult != null) sqlResult.close();
   sqlResult = null;
   tmpSQLStatement.close();
   tmpSQLStatement = null;
   logger.error("DBUTIL",strQuerySQL);
   return qd;
  } 
  catch(Exception e)
  {
   logger.error("DBUTIL","SQL error: " + strQuerySQL);
   logger.error("DBUTIL",e.getMessage());
   throw new BaseException("syserr");   
  } 
  finally
  {
  }
 }
 public QueryData selQureyPrep(String pQuerySql,String[][] conditionlist) throws Exception
 {
  try
  {
   QueryData qd = new QueryData();
   PreparedStatement prep = conn.prepareStatement(transSql(pQuerySql));
   int cnt = 0;
   for (int x = 0; x < conditionlist.length;x++)
   {
    boolean isFalse = true;
    while (isFalse)
    {
     int mode = 0;
     String strMode =conditionlist[x][0];
     if (!Common.checkNull(strMode))
     {
      isFalse = false;
      break;
     }
     else
     {
      cnt++;
      mode = Integer.parseInt(strMode);
     }
     switch (mode) 
     {
     case 0:
      prep.setString(cnt,String.valueOf(conditionlist[x][1]));
      isFalse = false;
      break;
     case 1: 
      prep.setDate(cnt,Date.valueOf(conditionlist[x][1]));
      isFalse = false;
      break;
     case 2:
      prep.setInt(cnt,Integer.parseInt(conditionlist[x][1]));
      isFalse = false;
      break;
     default :
      isFalse = false;
      break;
     }      
    }
   }
   sqlResult = null;
   sqlResult = prep.executeQuery();
   qd.setData(sqlResult);  
   if(sqlResult != null) sqlResult.close();
   sqlResult = null;
   prep.close();
   prep = null;
   logger.error("DBUTIL",pQuerySql);
   return qd;
  } 
  catch(Exception e)
  {
   logger.error("DBUTIL","SQL error: " + pQuerySql);
   logger.error("DBUTIL",e.getMessage());
   throw new BaseException("syserr");   
  } 
  finally
  {
  }  
 }
  public ResultSet selQuery_large(String strQuerySQL) throws Exception
  {
   try
   {
     sqlStatement.execute(transSql(strQuerySQL));   
     sqlResult = sqlStatement.getResultSet();
     logger.error("DBUTIL",strQuerySQL);
     return sqlResult;
   }
   catch(SQLException e)
   {
     logger.error("DBUTIL","SQL error: " + strQuerySQL);
     logger.error("DBUTIL",e.getMessage());
     throw new BaseException("syserr");
   }
   finally
   {
   }
  }  
 public void doQuery(String strQuerySQL) throws Exception 
 {
  try
  {
   Statement SQLStatement = conn.createStatement();

   SQLStatement.executeUpdate(transSql(strQuerySQL));
   logger.error("DBUTIL",strQuerySQL);
   SQLStatement.close();
   SQLStatement = null;

  }
  catch(SQLException e)
  {
   logger.error("DBUTIL","SQL error: " + strQuerySQL);
   logger.error("DBUTIL",e.getMessage());
   throw new BaseException("syserr");
  }
  finally
  {
  }
 }
 public void doQueryPrep(String pQuerySql,String[][] conditionlist) throws Exception 
 {
  try
  {
   PreparedStatement prep = conn.prepareStatement(transSql(pQuerySql));
   int cnt = 0;
   for (int x = 0; x < conditionlist.length;x++){
    boolean isFalse = true;
    while (isFalse){
     int mode = 0;
     String strMode =conditionlist[x][0];
     if (!Common.checkNull(strMode)){
      isFalse = false;
      break;
     }else{
      cnt++;
      mode = Integer.parseInt(strMode);
     }
     switch (mode) {
     case 0:
      prep.setString(cnt,String.valueOf(conditionlist[x][1]));
      isFalse = false;
      break;
     case 1: 
      prep.setDate(cnt,Date.valueOf(conditionlist[x][1]));
      isFalse = false;
      break;
     case 2:
      prep.setInt(cnt,Integer.parseInt(conditionlist[x][1]));
      isFalse = false;
      break;
     default :
      isFalse = false;
      break;
     }      
    }
   } 
   prep.executeUpdate();
    logger.error("DBUTIL","SQL error: " + pQuerySql);
   prep.close();
   prep = null;

  }
  catch(SQLException e)
  {
    logger.error("DBUTIL","SQL error: " + pQuerySql);
   logger.error("DBUTIL",e.getMessage());
   throw new BaseException("syserr");
  }
  finally
  {
  }
 } 
 public boolean doCommit() throws Exception
 {
  try
  {
   conn.commit();
   return true;
  }
  catch (SQLException e)
  {
     logger.error("DBUTIL","SQL commit error! ");
      logger.error("DBUTIL",e.getMessage()); 
   conn.rollback();
   throw new BaseException("syserr");
  }
  finally
  {
  }
 }
 public void doRollback() throws Exception
 {
  try
  {
   if(conn != null)
    conn.rollback();
  }
  catch (Exception e)
  {
      logger.error("DBUTIL","SQL rollback error! " );
      logger.error("DBUTIL",e.getMessage()); 
  }   
 }
 public static String transSql(String strInput) throws IOException
 {
  String strOutput = strInput;
  //return EucJpFile.toJIS(strOutput);
  return strOutput;
 }
 public static String deTransSql(String strInput) throws IOException
 {
  String strOutput = strInput;
  return strOutput;
  //return EucJpFile.toCp932(strOutput);
 }
}

 

以下是JAVA對DB操作的一種實現方式,與大家一起共享,對初學者可能有一定的幫助,當然對高手而 言,用處就不見的大了,歡迎大家提出更好的意見和建議.為了商業規則,程式碼的註釋去掉了,請大家見諒.

 

相關文章