/*当执行SQL时发现不能连接数据库,先重连一次*/
import java.io.FileInputStream;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Date;
import java.util.TimerTask;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.swing.ImageIcon;
import javax.swing.JOptionPane;
class SQL_OPERATION_TRAN {
//private Connection cn;
private PreparedStatement pst;
private ResultSet rs;
private boolean isAutoCommit=true;
private CallableStatement proc;
java.util.Timer tmReceive;
//int counter=0;
Main gbl_Main;
SQL_OPERATION_TRAN(Main main){
//cn=connection;
gbl_Main=main;
//this.CheckConnect();
// this.Connect();
}
public void Connect(){
//gbl_Main.cn=gbl_Main.con.getConnection();
String name = "com.microsoft.jdbc.sqlserver.SQLServerDriver";
String url = "jdbc:microsoft:sqlserver://10.10.10.10:1433;DatabaseName=aa";
String username = "bb";
String password = "cc";
try {
Class.forName(name);
try {
gbl_Main.cnTran = DriverManager.getConnection(url, username, password);
//DriverManager.setLoginTimeout(30000);
gbl_Main.gbl_ErrorCounter = 0;
gbl_Main.gbl_TryCounter = 0;
System.out.println("Connect="+gbl_Main.gbl_ErrorCounter);
} catch (SQLException ex) {
gbl_Main.gbl_ErrorCounter++;
Logger.getLogger(SQL_OPERATION.class.getName()).log(Level.SEVERE, null, ex);
}
} catch (ClassNotFoundException ex) {
Logger.getLogger(SQL_OPERATION.class.getName()).log(Level.SEVERE, null, ex);
}
}
public void prepareCall(String statement){
try {
// proc = this.cn.prepareCall(statement);
proc = gbl_Main.cnTran.prepareCall(statement);
} catch (SQLException ex) {
Logger.getLogger(SQL_OPERATION.class.getName()).log(Level.SEVERE, null, ex);
}
}
public void procSetString(int index,String str){
try {
proc.setString(index, str);
} catch (SQLException ex) {
Logger.getLogger(SQL_OPERATION.class.getName()).log(Level.SEVERE, null, ex);
}
}
public void procSetString(String name, String str){
try {
proc.setString(name, str);
} catch (SQLException ex) {
Logger.getLogger(SQL_OPERATION.class.getName()).log(Level.SEVERE, null, ex);
}
}
public void procsetBinaryStream(int index, FileInputStream fis, int length){
try {
proc.setBinaryStream(index, fis, length);
} catch (SQLException ex) {
Logger.getLogger(SQL_OPERATION.class.getName()).log(Level.SEVERE, null, ex);
}
}
public boolean procExecute(){
boolean Result=false;
try {
Result = proc.execute();
} catch (SQLException ex) {
Logger.getLogger(SQL_OPERATION.class.getName()).log(Level.SEVERE, null, ex);
}
return Result;
}
public PreparedStatement pstmt(String sql){
try {
this.pst = null;
//this.pst.close();
this.pst = gbl_Main.cnTran.prepareStatement(sql);
} catch (SQLException ex) {
System.out.println("pstmt 异常");
if((ex.getMessage().contains("Connection reset"))||(ex.getMessage().contains("Connection reset by peer:"))||(ex.getMessage().contains("Error establishing socket."))){
System.out.println("第一次连接数据库失败!重连...");
this.pst = null;
Connect();
/*重新连接成功*/
if(gbl_Main.gbl_ErrorCounter==0){
try {
this.pst = gbl_Main.cnTran.prepareStatement(sql);
} catch (SQLException ex1) {
if((ex.getMessage().contains("Connection reset"))||(ex.getMessage().contains("Connection reset by peer:"))||(ex.getMessage().contains("Error establishing socket."))){
gbl_Main.gbl_ErrorCounter++;
this.pst = null;
}
Logger.getLogger(SQL_OPERATION.class.getName()).log(Level.SEVERE, null, ex1);
}
}
}
Logger.getLogger(SQL_OPERATION.class.getName()).log(Level.SEVERE, null, ex);
}
return this.pst;
}
public ResultSet executeQuery(){
this.rs=null;
try {
if(this.pst == null){
this.rs = null;
}else{
this.rs = this.pst.executeQuery();
}
} catch (SQLException ex) {
rs = null;
Connect();
if(gbl_Main.gbl_ErrorCounter==0){
try {
this.rs = this.pst.executeQuery();
} catch (SQLException ex1) {
if((ex.getMessage().contains("Connection reset"))||(ex.getMessage().contains("Connection reset by peer:"))||(ex.getMessage().contains("Error establishing socket."))){
gbl_Main.gbl_ErrorCounter++;
this.rs = null;
}
Logger.getLogger(SQL_OPERATION.class.getName()).log(Level.SEVERE, null, ex1);
}
}
Logger.getLogger(SQL_OPERATION.class.getName()).log(Level.SEVERE, null, ex);
}
return this.rs;
}
public int executeUpdate(){
int Result=-1;
try {
Result = this.pst.executeUpdate();
} catch (SQLException ex) {
Logger.getLogger(SQL_OPERATION.class.getName()).log(Level.SEVERE, null, ex);
}
return Result;
}
public void close() {
try {
if(this.rs != null){
this.rs.close();
this.rs = null;
}
if (this.pst != null) {
this.pst.close();
this.pst = null;
}
if(this.proc != null){
this.proc.close();
this.proc = null;
}
} catch (SQLException ex) {
Logger.getLogger(SQL_OPERATION.class.getName()).log(Level.SEVERE, null, ex);
}
}
public void setAutoCommit(boolean tag){
try {
gbl_Main.cnTran.setAutoCommit(tag);
} catch (SQLException ex) {
Logger.getLogger(SQL_OPERATION.class.getName()).log(Level.SEVERE, null, ex);
}
}
public boolean getAutoCommit(){
boolean Result=false;
try {
Result = gbl_Main.cnTran.getAutoCommit();
} catch (SQLException ex) {
Logger.getLogger(SQL_OPERATION.class.getName()).log(Level.SEVERE, null, ex);
}
return Result;
}
public void beginTrans() throws SQLException {
try {
isAutoCommit = gbl_Main.cnTran.getAutoCommit();
gbl_Main.cnTran.setAutoCommit(false);
}
catch (SQLException ex) {
ex.printStackTrace();
System.out.print("beginTrans Errors");
throw ex;
}
}
public void commit() throws SQLException {
try {
gbl_Main.cnTran.commit();
gbl_Main.cnTran.setAutoCommit(isAutoCommit);
}
catch (SQLException ex) {
ex.printStackTrace();
System.out.print("Commit Errors!");
throw ex;
}
}
public void rollback() {
try {
gbl_Main.cnTran.rollback();
gbl_Main.cnTran.setAutoCommit(isAutoCommit);
}
catch (SQLException ex) {
ex.printStackTrace();
System.out.print("Roolback Error!");
}
}
public void setString(int index, String value) throws SQLException {
pst.setString(index, value);
}
public void setInt(int index, int value) throws SQLException {
pst.setInt(index, value);
}
public void setBoolean(int index, boolean value) throws SQLException {
pst.setBoolean(index, value);
}
public void setDate(int index, Date value) throws SQLException {
pst.setString(index, value.toString());
}
public void setLong(int index, long value) throws SQLException {
pst.setLong(index, value);
}
public void setFloat(int index, float value) throws SQLException {
pst.setFloat(index, value);
}
public void setBytes(int index, byte[] value) throws SQLException {
pst.setBytes(index, value);
}
}