java数据库基础类

来源：互联网发布：中国银行业数据库编辑：程序博客网时间：2024/05/17 23:24

package database;

import java.sql.Statement;
import java.sql.Connection;
import javax.naming.Context;
import javax.sql.DataSource;
import javax.naming.InitialContext;
import java.sql.SQLException;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.sql.CallableStatement;

/**
* 
*
* Description: 
*
* Copyright: Copyright (c) 2006
*
* Company: 
*
* Create Date: 2006-6-20
* Modify Last Date: 2007-1-8
* 文件：DataBaseOperaotr.java
* 文件作用:数据库基础操作类
* <h2>说明：</h2>
* 本方法在使用Tomcat5.5连接池连接数据库需要注意的配置
* 第一步:在工程文件内 WEB-INF 的 web.xml文件中前面加入：
* {@code
<resource-ref>
 <description>DB Connection</description>
 <res-ref-name>jdbc/DataBase</res-ref-name>
 <res-type>javax.sql.DataSource</res-type>
 <res-auth>Container</res-auth>
</resource-ref>
</web-app>

* }
* 第二步::在项目文件夹:
* 工程中/Tomcat/conf里面修改server8080.xml文件，并且删除注释
* {@code
* <Context debug="0" docBase="F:/TomcatPool/TheWay3/test/testwm" path="/testwm"
 reloaded="true" crossContext="true">
<Resource name="jdbc/DataBase" auto="Container" type="javax.sql.DataSource"
 maxActive="100" maxIdle="30" maxWait="1000"
 username="sa" password="1234"
 driverClassName="com.microsoft.jdbc.sqlserver.SQLServerDriver"
 url="jdbc:microsoft:sqlserver://localhost:1433;DatabaseName=jwgl"/>
</Context> </Host>
* }
* 第三步:在服务器Tomcat/Comm/Lib内拷贝数据库JDBC的jar文件
* {@code
* 将SQLServer2000 JDBC里面 lib里面的三个文件
 msbase.jar
 mssqlserver.jar
 msutil.jar

* }
* 第四步:工程里面要导入包 sql 
* {@code
* 工程里面要导入包 sql JBuider2006菜单栏
* 《Tools》-〉《Configure》-〉《Libraries》
* Configure Libraries 对话框中，导入包《Required Libraries》里面点<add>增加sql包
* }
* 补充::在JBuilder需要配置sql数据库引擎:配置步骤如下：
* {@code
* Step1:在JBuilder2006里面，选择菜单[Tools]->[Configure Libraries]
 点左边列表框下面的[New]按钮，
 输入：sql
 选择：User Home
 然后点 [Add]按钮，加入目录：
 C:/Program Files/Microsoft SQL Server 2000 JDBC/lib
 这个时候看到三个文件
 然后点确定即可
* 
* Step2:在JBuilder2006里面，
 选择主菜单[Tools]->[Enterprise Setup]->[Database Drivers]->[Add]，
 加入User Home下sql，
 添加之后在列表框中显示的是sql.config，确定，重新启动JBuilder 
* }
* 
* @author * @version 1.0
* {@code 编码风格：小写开头单词+大写首字母单词1..n ：变量，方法，}
* {@code 编码风格：大写写开头+大写首字母单词1..n ：类 }
* {@code 编码风格：小写单词_小写1..n ：包 }
*/
public class DataBase_Operator
{
 /**
 * 数据库通用连接变量m_connection
 */
 public Connection m_connection;
 /**
 * 数据库通用会话变量Statement
 */
 public Statement m_statement;

/**
 * 函数功能:初始化连接数据库
 */
 public DataBase_Operator()
 {
 m_connection = getConnection();
 }

/**
 * 函数功能: 使用Tomcat连接池方式打开数据库
 * @return Connection
 */
 public Connection getConnection()
 {
 try
 {
 Context initCtx = new InitialContext();
 //下面的 jdbc/DataBase需要在Web.xml里面和Server8080.xml里面配置数据库连接参数
 DataSource m_datasource = (DataSource) initCtx.lookup("java:comp/env/jdbc/DataBase");
 this.m_connection = m_datasource.getConnection();
 }
 catch(Exception e)
 {
 e.printStackTrace();
 }
 return this.m_connection;
 }
 /**
 * 函数功能: 执行SQL,使用防注入方式
 * @param fieldValue String[]
 * @param sql String
 * 数据库查询语句
 * @return boolean
 * 返回True or False
 * 调用例子: 例如：
 * sql="insert into b_type values(9,?,?,?,'2006-11-03','11','liulili')";
 * String[] m_v = new String[3];
 * m_v[0]="工作";
 * m_v[1]="m_key";
 * m_v[2]="备注";
 * if(m_db.ExecuteSQL(sql,m_v))
 * {
 * System.out.print(sql+"成功");
 * }
 * else
 * {
 * System.out.print(sql+"失败");
 * }
 * 重要说明:如果 FieldValue = null没有参数掺入
 */
 public boolean ExecuteSQL(String sql,String[] fieldValue)
 {
 boolean f= false;
 String m_FieldValue="";
 try
 {
 if (this.m_connection.isClosed()) {
 getConnection();
 }

PreparedStatement m_ps = m_connection.prepareStatement(sql);
 if (fieldValue != null)
 {
 for (int i = 0; i < fieldValue.length; i++)
 {
 m_FieldValue = fieldValue[i].trim();
 m_ps.setString(i + 1, m_FieldValue);
 }
 }
 m_ps.execute();
 m_connection.commit();
 m_ps.close();
 m_connection.close();
 f=true;
 }
 catch(SQLException e)
 {
 System.out.println("Execute SQL 错误！");
 System.out.println(sql);
 System.out.print(e.toString());
 f=false;
 }
 return f;

}
 /**
 * 函数功能 : 通过SQL语句得到一个查询结果字段值。
 * @param whereFieldValues String[]
 * @param sql String
 * @return String
 * 说明: 接受参数 : SQL = 防注入式SQL语句
 * 调用例子: 例如：
 * sql="select id from tablename where name=?";
 * String[] m_v = new String[3];
 * m_v[0]="工作";
 * if(m_db.GetFieldValue(sql,m_v))
 * {
 * System.out.print(sql+"成功");
 * }
 * else
 * {
 * System.out.print(sql+"失败");
 * }
 * 重要说明:如果 FieldValue = null没有参数掺入
 */
 public String GetFieldValue(String sql,String[] whereFieldValues)
 {
 int m_count = 0;
 String m_FieldValue="";
 try
 {
 if (this.m_connection.isClosed())
 {
 getConnection();
 }
 PreparedStatement m_ps = m_connection.prepareStatement(sql);
 ResultSet m_rs=null;
 //判断得到的值是否为空
 if (whereFieldValues!=null)
 {
 for (int i = 0; i < whereFieldValues.length; i++) {
 if (whereFieldValues[i] == null) {
 whereFieldValues[i] = "";
 }
 }
 }
 if (whereFieldValues!=null)
 {
 m_count = whereFieldValues.length;
 for (int i = 0; i < m_count; i++)
 {
 m_ps.setString(i + 1, whereFieldValues[i].trim());
 }
 }
 m_ps.execute();
 m_rs = m_ps.getResultSet();
 m_connection.commit();
 while(m_rs.next())
 {

                 m_FieldValue=m_rs.getString(1);
                 if(m_FieldValue==null)
                 {
                     m_FieldValue = "";
                 }
              }
               m_rs.close();
               m_ps.close();
               m_connection.close();
         }
         catch(Exception E)
         {

System.out.print(E.toString());
 E.printStackTrace();
 }
 return m_FieldValue;
 }
 /**
 * 函数功能 : 得到表 sql 的记录集
 * @param whereFieldValues String[]
 * @param sql String
 * @return ArrayList
 * 调用例子: 例如：
 * sql="select id from tablename where name=?";
 * String[] m_v = new String[3];
 * m_v[0]="工作";
 * ArrayList list =new ArrayList;
 * list = m_db.GetSQL_ArrayList(sql,m_v))
 * 重要说明:如果 FieldValue = null没有参数掺入
 */
 public ArrayList GetSQL_ArrayList(String sql, String[] whereFieldValues)
 {
 ArrayList m_list = new ArrayList();
 int m_FieldCount=0;
 m_FieldCount = GetFieldCount(sql);
 try
 {
 if (this.m_connection.isClosed())
 {
 getConnection();
 }

for(int i=0;i<m_FieldCount;i++ )
{

                                m_FieldValue[i] = m_rs.getString(i + 1);
                                if(m_FieldValue[i]==null)
                                {
                                     m_FieldValue[i]="";
                                }

                        }
                        m_list.add(m_FieldValue);
                    }
                 System.out.println(sql);
                 System.out.println("记录数为:"+m_RecordCount);

/**
 * 函数功能 : 得到表 sql 的记录集
 * @param whereFieldValues String[]
 * @param sql String
 * @return ArrayList
 * 调用例子: 例如：
 * sql="select id from tablename where name=?";
 * String[] m_v = new String[3];
 * m_v[0]="工作";
 * ArrayList list =new ArrayList;
 * list = m_db.GetSQL_ArrayList(sql,m_v))
 * 重要说明:如果 FieldValue = null没有参数掺入
 */
 public ArrayList GetSQL_ArrayList_Back(String sql, String[] whereFieldValues)
 {
 ArrayList m_list = new ArrayList();
 int m_FieldCount=0;
 m_FieldCount = GetFieldCount(sql);
 try
 {
 if (this.m_connection.isClosed())
 {
 getConnection();
 }

PreparedStatement m_ps = m_connection.prepareStatement(sql);
 if (whereFieldValues!=null)
 {
 for (int i = 0; i < whereFieldValues.length; i++)
 {
 m_ps.setString(i + 1, whereFieldValues[i].trim());
 }
 }
 m_ps.execute();
 ResultSet m_rs=m_ps.getResultSet();
 m_connection.commit();
 int m_RecordCount=0;
 while(m_rs.next())
 {
 m_RecordCount++;
 String m_FieldValue[] = new String[m_FieldCount];
 for(int i=0;i<m_FieldCount;i++ )
 {
 if(m_rs.getString(i+1)==null)
 {
 m_FieldValue[i] = "";
 }
 else
 {
 m_FieldValue[i] = m_rs.getString(i + 1);
 }
 }
 m_list.add(m_FieldValue);
 }
 System.out.println(sql);
 System.out.println("记录数为:"+m_RecordCount);

this.ExecuteCloseDataBase();
 }
 catch(Exception e)
 {
 System.out.println("GetSQL_ArrayList SQL 错误！");
 System.out.println(sql);
 e.printStackTrace();
 System.out.print(e.toString());
 }
 return m_list;
 }
 /**
 * 函数功能 : 得到SQL字段个数，从SQL语句中，判断Form前面的","
 * 
 * @param sql String
 * 查询语句
 * @return int
 * 返回查询语句中SQL 中 Select 字段的个数
 */
 public int GetFieldCount(String sql)
 {
 int m_start=100;
 int m_count=0;
 String FindChar=",";
 //从m_str里面判断有几个字段，主要依据","为分割点，如果是from之后的，那就不要了
 //首先判断[]from[]所在位置，然后得到","
 int m_index;
 m_index = sql.indexOf(" from ");
 sql = sql.substring(0,m_index+1);
 while(m_start >0)
 {
 m_start = sql.indexOf(FindChar);
 sql = sql.substring(m_start+1);
 m_count++;
 }
 return m_count;
 }

/**
 * 函数功能:关闭数据库
 */
 public void ExecuteCloseDataBase() {
 //关闭 m_rs
 try {
 if (this.m_statement != null) {
 m_statement.close();
 }
 } catch (SQLException e) {
 e.printStackTrace();
 }
 //Close DataBase
 try {
 if (m_connection != null) {
 m_connection.close();
 }
 } catch (SQLException e) {
 e.printStackTrace();
 }

}

/**
 * 函数功能:执行SQL
 * @param sql String
 * @return boolean
 * @throws SQLException
 */
 public boolean ExecuteSQL(String sql) throws SQLException
 {
 boolean f=false;
 try
 {
 if (this.m_connection.isClosed())
 {
 getConnection();
 }
 this.m_statement = this.m_connection.createStatement();
 f = this.m_statement.execute(sql);
 ExecuteCloseDataBase();
 f=true;
 }
 catch(Exception e)
 {
 f = false;
 }
 return f;
}

/**
* 函数功能: Get FieldValue From SQL
* @param sql String
* @return String
* @throws SQLException
* 创建日期:2006-6-20 By Liulili
*/
 public String GetFieldValue(String sql) throws SQLException
 {
 String m_value="";
 ResultSet m_resultset; //数据查询结果集
 try
 {
 if (this.m_connection.isClosed()) {
 getConnection();
 }
 this.m_statement = this.m_connection.createStatement();
 m_resultset = m_statement.executeQuery(sql);
 while (m_resultset.next()) {

                     m_value = m_resultset.getString(1);
                if (m_value==null)
               {
                   m_value="";
               }
             }
             m_resultset.close();
             ExecuteCloseDataBase();
         }
         catch(Exception e)
         {
            m_value=e.toString();
         }
      return m_value;
}

/**
 * 函数功能：Get FieldValue and FieldValue location From SQL
 * 
 * @param sql String
 * @param location int
 * @return String
 * @throws SQLException
 * 创建日期:2006-6-20 By Liulili
 * ep.
 * CString sql=" select employee_name from employee where employee_id='3'";
 * String m_name = GetFieldValue(sql,1);
 */
 public String GetFieldValue(String sql,int location) throws SQLException
{
 String m_value="";
 ResultSet m_resultset; //数据查询结果集
 try
 {
 if (this.m_connection.isClosed())
 {
 getConnection();
 }

            this.m_statement = this.m_connection.createStatement();
             m_resultset = m_statement.executeQuery(sql);
             m_resultset.next();

                 m_value = m_resultset.getString(location);
   if(m_value==null)
    {
      m_value="";
     }
             m_resultset.close();
             ExecuteCloseDataBase();
         }
         catch(Exception e)
         {
            m_value=e.toString();
         }
      return m_value;
}

/**
 * 函数功能：Get FieldValue and FieldValue location From SQL
 * 
 * @param sql String
 * @return ResultSet
 * @throws SQLException
 * 创建日期:2006-6-20 By Liulili
 */
 public ResultSet GetResultSet(String sql) throws SQLException
 {
 ResultSet m_resultset; //数据查询结果集
 try
 {
 if (this.m_connection.isClosed())
 {
 getConnection();
 }

             this.m_statement = this.m_connection.createStatement();
             m_resultset = m_statement.executeQuery(sql);
             //ExecuteCloseDataBase();
             return m_resultset;
          }
          catch(Exception e)
          {
          }
       return null;
}

/**
* 函数功能:替换非法的HTML字符，为了在分页中显示出来
* @param text String
* @return String
*/
 public String ReplaceString(String text)
 {
 String s = "";
 try
 {
 s = text.replaceAll(" ", " "); //空格
 s = s.replaceAll("'", "&#39"); //单引号
 s = s.replaceAll("/"", "&quot"); //双引号
 s = s.replaceAll("<", "<"); //小于号
 s = s.replaceAll(">", ">"); //大于号
 s = s.replaceAll("/r/n", " "); //回车
 return s;
 }
 catch(Exception e)
 {
 e.printStackTrace();
 }
 return s="";
}

/**
   * get_SQL_InfoList By CXH
   * @param sql String
   * @param FieldCount int
   * @return ArrayList
   */
public ArrayList get_SQL_InfoList(String sql, int FieldCount)
{
        String m_sql = "";
        m_sql = sql;
        System.out.println("sql :"+sql);
        ArrayList infoList = new ArrayList();
        ResultSet m_rs ;
        PreparedStatement pstm = null;

int m_int=0;
 try {
 if (this.m_connection.isClosed()) {
 getConnection();
 }
 pstm = m_connection.prepareStatement(m_sql);
 pstm.executeQuery();
 m_rs = pstm.getResultSet();
 while (m_rs.next()) {
 String m_row[] = new String[FieldCount];
 for (int i = 0; i < FieldCount; i++) {
 m_int = i + 1;
 m_row[i] = m_rs.getString(m_int);
 if(m_row[i]==null)
 {
 m_row[i]="";
 }
 }
 infoList.add(m_row);
 }
 m_rs.close();
 m_connection.close();
 return infoList;

        } catch (Exception ex) {
            System.out.println("执行函数 get_SQL_InfoList()出现异常");
            ex.printStackTrace();
        }
        return infoList;
    }

    /**
         * 获得最大ID
         * @param vTableName String
         * @param vFieldName String
         * @return int
         */
        public int GetMaxID(String vTableName,String vFieldName)
          {
           int mResult=0;
           String mSql=new String();
           mSql = "select max("+vFieldName+")+1 as MaxID from " + vTableName;
           try
           {
               if (this.m_connection.isClosed()) {
                   getConnection();
                }
                m_statement = m_connection.createStatement();
               ResultSet result=m_statement.executeQuery(mSql);
               if (result.next())
               {
                 mResult=result.getInt("MaxID");
               }
               result.close();
               m_connection.close();
             }
             catch(Exception e)
             {
               e.printStackTrace();
           }
           return (mResult);
         }

/**
 * 执行存储过程通用函数
 * @param sql String 例如：sql="{call get_course(?,?)}"
 * @param value String[]
 * @param return_value_count int
 * @return String
 * {@code
 * sql="{call get_course(?,?)}";
 * String[] value = new String[1];
 * String[] return_value = new String[1];
 * value[0] = reporter;
 * return_value="0";
 * return_value = m_db.ExecuteProcedure(sql,value,1);
 * if(return_value!="0")
 * (
 * 执行成功！
 * )
 * }
 * 
 * 注意：这里的返回值必须是数字，返回存储过程操作了多少条记录
 * 
 */
 public String[] ExecuteProcedure(String sql,String[] value,int return_value_count)
 {
 boolean f = false;
 CallableStatement m_proc = null;
 int i=0;
 String[] return_value=new String[return_value_count];
 try
 {
 if (this.m_connection.isClosed())
 {
 getConnection();
 }
 m_proc = m_connection.prepareCall(sql);

//传递输入参数
 if (value!=null)
 {
 for (; i < value.length; i++)
 {
 m_proc.setString(i+1, value[i]);
 }
 }
 if(return_value_count!=0)
 {
 //传递接收参数
 for (int j = 0; j < return_value_count; j++)
 {
 m_proc.registerOutParameter(j+i+1,java.sql.Types.INTEGER);
 }
 }
 //执行
 m_proc.execute();

//返回接受参数值
 if(return_value_count!=0)
 {
 for (int j = 0; j < return_value_count; j++)
 {
 int m_pro_return_value = m_proc.getInt(j+i+1);
 return_value[j] = Integer.toString(m_pro_return_value);
 }
 }
 f = true;
 this.m_connection.close();
 m_proc.close(); //关闭存储过程
 }
 catch (Exception e)
 {
 System.out.println(e.toString());
 e.printStackTrace();
 f = false;
 }
 if (f==false)
 {
 System.out.println("存储过程执行失败！");
 System.out.println(sql);
 }
 else
 {
 System.out.println("存储过程执行成功！");
 }
 return return_value;
 }
 /**
 * 用于执行 select count(*) from table
 * 获得数据记录条数
 * @param sql String
 * @return int
 */
 public int getCount(String sql)
 {
 int mResult=0;
 String mSql=new String();
 mSql = sql;
 try
 {
 if (this.m_connection.isClosed()) {
 getConnection();
 }
 m_statement = m_connection.createStatement();
 ResultSet result=m_statement.executeQuery(mSql);
 if (result.next())
 {
 mResult=result.getInt(1);
 }
 result.close();
 m_connection.close();
 }
 catch(Exception e)
 {
 System.out.println("getCount() 获取纪录条数失败 ! 执行的语句为 : "+ sql);
 e.printStackTrace();
 }
 return (mResult);
 }

}