package ng.db;
|
import java.sql.*;
|
import java.util.*;
|
import org.json.*;
|
|
|
|
|
|
|
public class DBSession implements AutoCloseable {
|
private Connection con; //������
|
private boolean auto; //�Ƿ�Ϊ�Զ��ύ
|
private Statement sql; //���һ��ʹ�õ�Statement
|
|
|
|
public Connection getConnection(){
|
return con;
|
}
|
//����
|
private void reset(){
|
if(sql!=null){
|
try{
|
sql.close();
|
}
|
catch(Exception e){
|
|
}
|
sql=null;
|
}
|
}
|
|
|
|
|
//��ѯ�����
|
public class SelectResult{
|
private Statement state;
|
private boolean closed;
|
|
|
public Object getFirst() throws SQLException{
|
ResultSet r= state.getResultSet();
|
if(r.next())
|
return r.getObject(1);
|
else
|
return null;
|
}
|
//��ȡ��ǰ�Ľ��
|
public ResultSet getCurrentResult() throws SQLException {
|
return state.getResultSet();
|
}
|
|
public JSONObject allResultToJson() throws SQLException, JSONException{
|
JSONObject obj=new JSONObject();
|
int idx=1;
|
do{
|
JSONArray a=this.resultToJson(false);
|
obj.put("Table"+idx,a);
|
idx++;
|
}while(this.nextResult());
|
return obj;
|
}
|
|
|
|
//�ƶ�����һ�����,������ŷ���true
|
public boolean nextResult() throws SQLException {
|
return state.getMoreResults();
|
}
|
public void close(){
|
if(closed==false){
|
closed=true;
|
try{
|
state.close();
|
}
|
catch(Exception e){
|
|
}
|
}
|
}
|
|
//���캯��
|
public SelectResult(Statement state){
|
this.state=state;
|
ResultSet set=null;
|
}
|
|
//����ǰ�Ľ����ֱ������JSON����
|
public JSONArray resultToJson(boolean RowAsObject) throws SQLException, JSONException{
|
return DBHelper.resultToJson(this.getCurrentResult(), RowAsObject);
|
}
|
|
public JSONArray resultToJson(String[] fields,boolean RowAsObject) throws SQLException, JSONException{
|
return DBHelper.resultToJson(fields,this.getCurrentResult(), RowAsObject);
|
}
|
|
|
public List<JSONArray> allToJson(boolean RowAsObject) throws SQLException, JSONException{
|
List<JSONArray> arrs=new ArrayList<JSONArray>();
|
do{
|
arrs.add(DBHelper.resultToJson(this.getCurrentResult(), RowAsObject));
|
}while(this.nextResult());
|
return arrs;
|
}
|
|
|
|
}
|
|
//���洢���̵��ã����洢���̶���,���ĵ�
|
public class StdCallResult extends SelectResult{
|
public int ResultCode;
|
public String ResultMessage;
|
public StdCallResult(CallableStatement state,int ResultCode,String ResultMessage){
|
super(state);
|
this.ResultCode=ResultCode;
|
this.ResultMessage=ResultMessage;
|
}
|
}
|
|
|
|
//����һ��SQL����
|
public void createSql(String sql) throws SQLException{
|
reset();
|
PreparedStatement result= con.prepareStatement(sql);
|
this.sql=result;
|
}
|
|
public StdCallResult CallProc(String proc,Object... params) throws SQLException{
|
createStdCall(proc,params.length);
|
return this.stdCall(params);
|
}
|
|
|
|
|
|
|
public void createQueryCall(String procName,int InputParamCount) throws SQLException {
|
reset();
|
StringBuilder sp=new StringBuilder();
|
sp.append("{call ");
|
sp.append(procName);
|
sp.append("(");
|
for(int i=0;i<InputParamCount;i++){
|
if(i==0){
|
sp.append('?');
|
}
|
else{
|
sp.append(",?");
|
}
|
}
|
|
CallableStatement sql=con.prepareCall(sp.toString());
|
sql.registerOutParameter(InputParamCount+1, java.sql.Types.INTEGER);
|
sql.registerOutParameter(InputParamCount+2, java.sql.Types.VARCHAR);
|
this.sql=sql;
|
}
|
|
|
|
//����һ�����洢���̵��ã�����1�Ǵ洢���̣�����2����������ĸ���
|
public void createStdCall(String procName,int InputParamCount) throws SQLException {
|
reset();
|
StringBuilder sp=new StringBuilder();
|
sp.append("{call ");
|
sp.append(procName);
|
sp.append("(");
|
for(int i=0;i<InputParamCount;i++){
|
if(i==0){
|
sp.append('?');
|
}
|
else{
|
sp.append(",?");
|
}
|
}
|
if(InputParamCount==0)
|
{
|
sp.append("?,?)}");
|
}
|
else{
|
sp.append(",?,?)}");
|
}
|
CallableStatement sql=con.prepareCall(sp.toString());
|
sql.registerOutParameter(InputParamCount+1, java.sql.Types.INTEGER);
|
sql.registerOutParameter(InputParamCount+2, java.sql.Types.VARCHAR);
|
this.sql=sql;
|
}
|
|
//�ô�����SQL�������ݿ⣬ִ��INSERT DELETE UPDATE��SQL��䣬 ������?ռλ����Ӧ�ı���
|
public int update(Object... params) throws SQLException {
|
|
PreparedStatement sql=(PreparedStatement)this.sql;
|
if(params!=null){
|
|
for(int i=0;i<params.length;i++){
|
sql.setObject(i+1, params[i]);
|
}
|
|
}
|
return sql.executeUpdate();
|
|
}
|
|
public int _update(Object[] params) throws SQLException {
|
|
PreparedStatement sql=(PreparedStatement)this.sql;
|
if(params!=null){
|
|
for(int i=0;i<params.length;i++){
|
sql.setObject(i+1, params[i]);
|
}
|
|
}
|
return sql.executeUpdate();
|
|
}
|
|
public Object queryFirst(Object... params) throws SQLException{
|
PreparedStatement sql=(PreparedStatement)this.sql;
|
if(params!=null){
|
|
for(int i=0;i<params.length;i++){
|
sql.setObject(i+1, params[i]);
|
}
|
|
}
|
if(sql.execute()){
|
ResultSet r=new SelectResult(sql).getCurrentResult();
|
if(r.next()){
|
return r.getObject(1);
|
}
|
return null;
|
}
|
else
|
return null;
|
}
|
|
|
//�ô�����SQL��ѯ���ݿ⣬ִ��SELECT ��䣬 ������?ռλ����Ӧ�ı���
|
public SelectResult query(Object... params) throws SQLException{
|
PreparedStatement sql=(PreparedStatement)this.sql;
|
if(params!=null){
|
|
for(int i=0;i<params.length;i++){
|
sql.setObject(i+1, params[i]);
|
}
|
|
}
|
if(sql.execute()){
|
return new SelectResult(sql);
|
}
|
else
|
return null;
|
}
|
|
|
|
|
public SelectResult _query(Object[] params) throws SQLException{
|
PreparedStatement sql=(PreparedStatement)this.sql;
|
if(params!=null){
|
|
for(int i=0;i<params.length;i++){
|
sql.setObject(i+1, params[i]);
|
}
|
|
}
|
if(sql.execute()){
|
return new SelectResult(sql);
|
}
|
else
|
return null;
|
}
|
|
|
//�������һ�δ����ĵı��洢���̣�����Ϊ�洢���̵��������������ֵ�DZ����ý��������洢���̴���SELECT ���Ե���SelectResultʹ��
|
public StdCallResult stdCall(Object...params) throws SQLException{
|
CallableStatement sql=(CallableStatement)this.sql;
|
if(params!=null){
|
for(int i=0;i<params.length;i++){
|
sql.setObject(i+1, params[i]);
|
}
|
}
|
sql.execute();
|
sql.getResultSet();
|
int count= sql.getParameterMetaData().getParameterCount();
|
int a1=sql.getInt(count-1);
|
String a2=sql.getString(count);
|
return new StdCallResult(sql,a1,a2);
|
}
|
|
//�رջỰ
|
public void close(){
|
try{
|
reset();
|
if(con!=null){
|
con.close();
|
con=null;
|
}
|
}
|
catch(Exception e){
|
|
}
|
}
|
|
//�ֶ��ύ��ʽ�£����������ύ
|
public void commit() throws SQLException{
|
if(!auto){
|
con.commit();
|
}
|
}
|
//�ֶ��ύ��ʽ�£����ڻع�����
|
public void rollback() throws SQLException{
|
if(!auto){
|
con.rollback();
|
}
|
}
|
|
|
//�Ự�Ĺ��캯��
|
public DBSession(Connection con,boolean ManulCommit) throws SQLException{
|
con.setAutoCommit(!ManulCommit);
|
this.auto=!ManulCommit;
|
this.con=con;
|
}
|
|
public DBSession(Connection con) throws SQLException{
|
this(con,false);
|
}
|
|
|
public static DBSession createSession(String url,String user,String password,boolean ManulCommit) throws SQLException{
|
Connection con=null;
|
DBSession sn=null;
|
try{
|
con= DriverManager.getConnection(url,user,password);
|
sn=new DBSession(con,ManulCommit);
|
return sn;
|
}
|
catch(SQLException e){
|
if(sn!=null)
|
sn.close();
|
else
|
{
|
if(con!=null){
|
con.close();
|
}
|
}
|
throw e;
|
}
|
}
|
|
|
|
|
|
}
|
