java sqlhelper_java版本的sqlHelper

package com.bobo.util;

import java.sql.DriverManager;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.sql.Statement;

import java.util.ArrayList;

import java.util.HashMap;

import java.util.SortedMap;

import java.sql.*;

import com.bobo.db.DataBase;public classSqlHelper {privateConnection con;publicSqlHelper() {

}public voidsetConnection(Connection con) {this.con =con;

}private voidprepareCommand(PreparedStatement pstmt, String[] parms) {try{if (parms != null) {for (int i = 0; i < parms.length; i++) {try{

pstmt.setDate(i+ 1, java.sql.Date.valueOf(parms[i]));

}catch(Exception e) {try{

pstmt.setDouble(i+ 1, Double.parseDouble(parms[i]));

}catch(Exception e1) {try{

pstmt.setInt(i+ 1, Integer.parseInt(parms[i]));

}catch(Exception e2) {try{

pstmt.setString(i+ 1, parms[i]);

}catch(Exception e3) {

System.out.print("SQLHelper-PrepareCommand Err1:"

+e3);

}

}

}

}

}

}

}catch(Exception e1) {

System.out.print("SQLHelper-PrepareCommand Err2:" +e1);

}

}/**

* 执行插入语句，返回对应行的自增key值

*

* @param sqlText

* @param params

* @return

* @throws Exception*/

public int ExecuteInsertReturnKey(String sqlText, String[] params)

throws Exception {

PreparedStatement ps= null;

java.sql.Connection con= null;int key = -1;

ResultSet rs= null;try{

ps=con.prepareStatement(sqlText, Statement.RETURN_GENERATED_KEYS);

prepareCommand(ps,params);

ps.executeUpdate();

rs=ps.getGeneratedKeys();if(rs.next()) {

key= rs.getInt(1);

}

}catch(Exception e) {throw new Exception("ExecuteInsertReturnKey出错:" +e.getMessage());

}finally{if (rs != null) {

rs.close();

}if (ps != null) {

ps.close();

}

}returnkey;

}/**

* 执行非查询sql语句(insert,update,delete)

*

* @param sqlText

* sql命令

* @param params

* 参数值

* @return int 返回操作影响的记录条数

* @throws Exception*/

public int ExecuteNonQuery(String sqlText, String[] params)

throws Exception {

PreparedStatement ps= null;

java.sql.Connection con= null;try{

ps=con.prepareStatement(sqlText);

prepareCommand(ps,params);returnps.executeUpdate();

}catch(Exception e) {throw new Exception("executeNonQuery出错:" +e.getMessage());

}finally{if (ps != null) {

ps.close();

}

}

}/**

*

* @param cmdtext

* 查询语句

* @param parms查询参数

* @return String[] 返回查询结果对应的列信息*/

publicString[] executeColumnInfo(String cmdtext, String[] parms) {

PreparedStatement pstmt= null;

String[] result= null;try{

pstmt=con.prepareStatement(cmdtext);

prepareCommand(pstmt, parms);

ResultSet rs=pstmt.executeQuery();

ResultSetMetaData rsmd=rs.getMetaData();int column =rsmd.getColumnCount();

result= newString[column];for (int i = 1; i <= column; i++) {

result[i- 1] =rsmd.getColumnName(i);

}

}catch(Exception e) {

}finally{if (pstmt != null)try{

pstmt.close();

}catch(SQLException e) {//TODO Auto-generated catch block

e.printStackTrace();

}

}returnresult;

}/**

* 执行查询语句,返回记录内容

*

* @param cmdtext

* sql指令

* @param parms

* 参数

* @return ArrayList 返回一个list，里面是String[列数]对象

* @throws Exception*/

public ArrayListExecuteReader(String cmdtext, String[] parms)

throws Exception {

PreparedStatement pstmt= null;try{

pstmt=con.prepareStatement(cmdtext);

prepareCommand(pstmt, parms);

ResultSet rs=pstmt.executeQuery();

ArrayList al = new ArrayList();

ResultSetMetaData rsmd=rs.getMetaData();int column =rsmd.getColumnCount();while(rs.next()) {

String[] ob= newString[column];for (int i = 1; i <= column; i++) {

ob[i- 1] =rs.getString(i);

}

al.add(ob);

}

rs.close();returnal;

}catch(Exception e) {throw new Exception("executeSqlResultSet出错:" +e.getMessage());

}finally{try{if (pstmt != null)

pstmt.close();

}catch(Exception e) {throw new Exception("executeSqlResultSet出错:" +e.getMessage());

}

}

}/**

*

* @param cmdtext

* 查询的sql语句

* @param parms

* 查询参数

* @return 仅仅返回符合条件的第一条记录

* @throws Exception*/

publicString[] ExecuteFirstRecorder(String cmdtext, String[] parms)

throws Exception {

PreparedStatement pstmt= null;try{

pstmt=con.prepareStatement(cmdtext);

prepareCommand(pstmt, parms);

ResultSet rs=pstmt.executeQuery();

ResultSetMetaData rsmd=rs.getMetaData();int column =rsmd.getColumnCount();

String[] ob= null;if(rs.next()) {

ob= newString[column];for (int i = 1; i <= column; i++) {

ob[i- 1] =rs.getString(i);

}

}

rs.close();returnob;

}catch(Exception e) {throw new Exception("executeSqlResultSet出错:" +e.getMessage());

}finally{try{if (pstmt != null)

pstmt.close();

}catch(Exception e) {throw new Exception("executeSqlResultSet出错:" +e.getMessage());

}

}

}/***

*

* @param cmdtext

* 查询的sql语句

* @param parms

* 查询参数

* @return 返回ArrayList>，map的结构是列名：列值

* @throws Exception*/

public ArrayList>ExecuteMapReader(String cmdtext,

String[] parms) throws Exception {

PreparedStatement pstmt= null;try{

pstmt=con.prepareStatement(cmdtext);

prepareCommand(pstmt, parms);

ResultSet rs=pstmt.executeQuery();

ArrayList> al = new ArrayList>();

ResultSetMetaData rsmd=rs.getMetaData();int column =rsmd.getColumnCount();

System.out.println("SqlHelper:" +rsmd.getColumnName(column));while(rs.next()) {

HashMap map = new HashMap();for (int k = 1; k <= column; k++) {

map.put(rsmd.getColumnName(k), rs.getString(k));

}

al.add(map);

}

rs.close();returnal;

}catch(Exception e) {throw new Exception("executeSqlResultSet出错:" +e.getMessage());

}finally{try{if (pstmt != null)

pstmt.close();

}catch(Exception e) {throw new Exception("executeSqlResultSet出错:" +e.getMessage());

}

}

}/**

* 执行查询语句，返回符合条件的记录数目

*

* @param cmdtext

* sql指令

* @param parms

* 参数

* @return int 返回符合条件的记录数目，如果没有返回-1

* @throws Exception*/

public intExecuteRowCountQuery(String cmdtext, String[] parms)

throws Exception {

PreparedStatement pstmt= null;int result = -1;try{

pstmt=con.prepareStatement(cmdtext);

prepareCommand(pstmt, parms);

ResultSet rs=pstmt.executeQuery();

rs.next();

result= rs.getInt(1);

rs.close();

}catch(Exception e) {throw new Exception("executeSqlResultSet出错:" +e.getMessage());

}finally{try{if (pstmt != null)

pstmt.close();

}catch(Exception e) {throw new Exception("executeSqlResultSet出错:" +e.getMessage());

}

}returnresult;

}/**

* 执行单结果单列查询语句，如果记录存在，返回首条记录的对应列，否则返回空(按照列名查询)

*

* @param cmdtext

* SQL命令

* @param name

* 列名称

* @param parms

* OracleParameter[]

* @return Object 返回列对象

* @throws Exception*/

publicObject ExecuteScalar(String cmdtext, String name, String[] parms)

throws Exception {

PreparedStatement pstmt= null;

ResultSet rs= null;try{

pstmt=con.prepareStatement(cmdtext);

prepareCommand(pstmt, parms);

rs=pstmt.executeQuery();if(rs.next()) {returnrs.getObject(name);

}else{return null;

}

}catch(Exception e) {throw new Exception("executeSqlObject出错:" +e.getMessage());

}finally{try{if (rs != null)

rs.close();if (pstmt != null)

pstmt.close();

}catch(Exception e) {throw new Exception("executeSqlObject出错:" +e.getMessage());

}

}

}/**

* 执行单结果单列查询语句，如果记录存在，返回首条记录的对应列，否则返回空(按照列索引查询)

*

* @param cmdtext

* SQL命令

* @param index

* 第几列

* @param parms

* OracleParameter[]

* @return Object

* @throws Exception*/

public Object ExecuteScalar(String cmdtext, intindex, String[] parms)

throws Exception {

PreparedStatement pstmt= null;

ResultSet rs= null;try{

pstmt=con.prepareStatement(cmdtext);

prepareCommand(pstmt, parms);

rs=pstmt.executeQuery();if(rs.next()) {returnrs.getObject(index);

}else{return null;

}

}catch(Exception e) {throw new Exception("executeSqlObject出错:" +e.getMessage());

}finally{try{if (rs != null)

rs.close();if (pstmt != null)

pstmt.close();

}catch(Exception e) {throw new Exception("executeS qlObject出错:" +e.getMessage());

}

}

}

}