mysql helper java_java-mysqlhelper

1 packageservlet;2 import java.sql.*;3 import java.util.*;4

5 import javax.sql.*;6 importjavax.naming.InitialContext;7 importjavax.naming.Context;8

9 public classMysqlhelper {10 //public Connection con=null;

11 /*public static PreparedStatement pst=null;12 public static ResultSet res=null;13 public static CallableStatement cst=null;14 */

15

16 /*

17 * CallableStatement 存储过程18 * PreparedStatement.executeUpdate() 处理insert、update、delete19 * PreparedStatement.executeQuery() 处理select20 **/

21 //获取connection

22 publicConnection getconnection(){23 Connection con=null;24 try{25 Context c=newInitialContext();26 Context et=(Context)c.lookup("java:/comp/env");27 DataSource ds=(DataSource)et.lookup("jdbc/mysqldb");28 con=ds.getConnection();29 con.setAutoCommit(false);//禁止自动提交事务

30 }catch(Exception e){31 System.out.println(e);32 }33 returncon;34 }35 /*

36 * 获取preparedstatement对象---执行sql语句37 **/

38 publicPreparedStatement getprepare(Connection conn,String sql){39 PreparedStatement pst=null;40 try{41 pst=conn.prepareStatement(sql);42 }catch(SQLException e){43 e.printStackTrace();44 }45 returnpst;46 }47 /*

48 * 获取callablestatement对象---执行存储过程*49 */

50 publicCallableStatement getcallable(Connection conn,String proc,String[] parm){51 CallableStatement cst=null;52 try{53 cst=conn.prepareCall(proc);54 SetParm(parm,cst);55 }catch(SQLException e){56 e.printStackTrace();57 }58 returncst;59 }60 /*

61 * 执行sql语句，返回影响的记录数*62 */

63 publicBoolean ExecuteMysql(PreparedStatement pst){64 int re=0;65 try{66 re=pst.executeUpdate();67 }catch(Exception e){68 e.printStackTrace();69 }70 return re==0?false:true;71 }72 /*

73 * 执行sql语句，返回结果集*74 */

75 publicResultSet GetDatatable(PreparedStatement pst){76 ResultSet rspart=null;77 try{78 rspart=pst.executeQuery();79 }catch(Exception e){80 System.out.println(e);81 }82 returnrspart;83 }84 /*

85 * 执行查询存储过程*86 */

87 publicResultSet Getcallparm(CallableStatement cst){88 ResultSet res=null;89 try{90 res=cst.executeQuery();91 }catch(Exception e){92 e.printStackTrace();93 }94 returnres;95 }96 /*

97 * 执行增删改存储过程*98 */

99 publicBoolean Getautocallparm(CallableStatement cst,Connection conn){100 int t=0;101 try{102 t=cst.executeUpdate();103 conn.commit();//统一提交

104 }catch(Exception e){105 try{106 conn.rollback();//操作失败，事务回滚

107 }catch(SQLException ex){108 ex.printStackTrace();109 }110 }111 return t==0?false:true;112 }113 /*

114 * 通过execute执行存储过程 返回影响的记录数*115 */

116 public intExecuteAuto(CallableStatement cst,Connection conn){117 int i=0;118 try{119 i=cst.getUpdateCount();120 conn.commit();121 }catch(Exception e){122 try{123 e.printStackTrace();124 conn.rollback();125 }catch(SQLException ex){126 ex.printStackTrace();127 }128 }129 returni;130 }131 //使用execute()方法执行存储过程-可返回多个结果集- /*

132 *使用execute执行存储过程，可返回多个结果集133 * *

134 */

135 publicResultSet ExecuteSelect(CallableStatement cst,Connection conn){136 ResultSet res=null;137 try{138 cst.execute();139 res=cst.getResultSet();140 conn.commit();141 }catch(Exception e){142 try{143 e.printStackTrace();144 conn.rollback();145 }catch(SQLException ex){}146 }147 returnres;148 }149 //设置存储过程的参数

150 public voidSetParm(String[] parm,CallableStatement cst){151 try{152 if(parm.length>0){153 for(int i=0;i

163 public voidclosecon(Connection con){164 try{165 if(con!=null){166 con.close();167 }168 }catch(SQLException e){169 e.printStackTrace();170 }171 }172 public voidclosepst(PreparedStatement pst){173 try{174 if(pst!=null){175 pst.close();176 }177 }catch(SQLException e){178 e.printStackTrace();179 }180 }181 public voidclosecst(CallableStatement cst){182 try{183 if(cst!=null){184 cst.close();185 }186 }catch(SQLException e){187 e.printStackTrace();188 }189 }190 }