API tools faq
paste
Advertisement
Guest User

Untitled

a guest
Mar 3rd, 2017
122
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 12.71 KB | None | 0 0
raw download clone embed print report
  1. package com.diven.game.db;
  2.  
  3. import java.io.IOException;
  4. import java.io.InputStream;
  5. import java.io.Reader;
  6. import java.lang.reflect.Method;
  7. import java.math.BigDecimal;
  8. import java.sql.Array;
  9. import java.sql.Blob;
  10. import java.sql.CallableStatement;
  11. import java.sql.Clob;
  12. import java.sql.Date;
  13. import java.sql.DriverManager;
  14. import java.sql.ResultSet;
  15. import java.sql.ResultSetMetaData;
  16. import java.sql.SQLException;
  17. import java.sql.Time;
  18. import java.sql.Timestamp;
  19. import java.util.ArrayList;
  20. import java.util.List;
  21.  
  22. import com.diven.game.common.CommonMethod;
  23. import com.diven.game.common.SqlConfig;
  24.  
  25. /**
  26. * procedure operate
  27. * @author Administrator
  28. */
  29. public class DbProc {
  30. public java.sql.Connection conn = null;
  31. public ResultSet rs = null;
  32. public CallableStatement callProc = null;
  33. public String _dirver = "oracle.jdbc.driver.OracleDriver";
  34. public DbProc() {
  35. try {
  36. //conn = InitServlet.getDs().getConnection();
  37. } catch (Exception ex) {
  38. ex.printStackTrace();
  39. }
  40. }
  41.  
  42. public DbProc(String sql) {
  43. try {
  44. //conn = InitServlet.getDs().getConnection();
  45. callProc = conn.prepareCall(sql);
  46. } catch (Exception ex) {
  47. ex.printStackTrace();
  48. }
  49. }
  50. /**
  51. * test
  52. *
  53. * @param sql
  54. * @param str
  55. */
  56. public DbProc(String sql, String str) {
  57. try {
  58. String url = "jdbc:oracle:thin:@127.0.0.1:1521:xiaoyi";
  59. String user = "qbxt";
  60. String password = "qbxt";
  61. Class.forName(_dirver);
  62. conn = DriverManager.getConnection(url, user, password);
  63. callProc = conn.prepareCall(sql);
  64. } catch (Exception ex) {
  65. ex.printStackTrace();
  66. }
  67. }
  68. public DbProc(String sql,int test) {
  69. try {
  70. String url = "jdbc:sqlserver://116.93.102.116:1433;DatabaseName=dvtest";
  71. String user = "diven";
  72. String password = "diven";
  73. Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
  74. conn = DriverManager.getConnection(url, user, password);
  75. callProc = conn.prepareCall(sql);
  76. } catch (Exception ex) {
  77. ex.printStackTrace();
  78. }
  79. }
  80. public DbProc(SqlConfig config, String sql) {
  81. try {
  82. // String drivers = "oracle.jdbc.driver.OracleDriver";
  83. String url = config.getDbsource();//"jdbc:oracle:thin:@127.0.0.1:1521:xiaoyi";
  84. String user = config.getUserid();//"qbxt";
  85. String password = config.getPwd();//"qbxt";
  86. Class.forName(_dirver);
  87. conn = DriverManager.getConnection(url, user, password);
  88. callProc = conn.prepareCall(sql);
  89. } catch (Exception ex) {
  90. ex.printStackTrace();
  91. }
  92. }
  93. public DbProc(SqlConfig config) {
  94. try {
  95. //String drivers = "oracle.jdbc.driver.OracleDriver";
  96. String url = config.getDbsource();//"jdbc:oracle:thin:@127.0.0.1:1521:xiaoyi";
  97. String user = config.getUserid();//"qbxt";
  98. String password = config.getPwd();//"qbxt";
  99. Class.forName(_dirver);
  100. conn = DriverManager.getConnection(url, user, password);
  101. } catch (Exception ex) {
  102. ex.printStackTrace();
  103. }
  104. }
  105. public DbProc(String dirv,SqlConfig config) {
  106. try {
  107. _dirver = dirv;
  108. String url = config.getDbsource();//"jdbc:oracle:thin:@127.0.0.1:1521:xiaoyi";
  109. String user = config.getUserid();//"qbxt";
  110. String password = config.getPwd();//"qbxt";
  111. Class.forName(_dirver);
  112. conn = DriverManager.getConnection(url, user, password);
  113. } catch (Exception ex) {
  114. ex.printStackTrace();
  115. }
  116. }
  117. public boolean setSql(String sql)throws Exception{
  118. return setProcSql(sql);
  119. }
  120. public boolean setProcSql(String sql) throws Exception {
  121. if (conn != null) {
  122. callProc = conn.prepareCall(sql);
  123. return true;
  124. } else {
  125. //conn = InitServlet.getDs().getConnection();
  126. callProc = conn.prepareCall(sql);
  127. return true;
  128. }
  129. }
  130. /**
  131. * execute
  132. *
  133. * @throws SQLException
  134. */
  135. public void executeUpdate() throws SQLException {
  136. if (callProc == null)
  137. return;
  138. callProc.executeUpdate();
  139. }
  140.  
  141. public boolean execute() throws SQLException {
  142. if (callProc == null)
  143. return false;
  144. return callProc.execute();
  145. }
  146.  
  147. public Object getObject(int index) throws SQLException {
  148. if (callProc == null)
  149. return null;
  150. return callProc.getObject(index);
  151. }
  152.  
  153. public ResultSet executeQuery() throws SQLException {
  154. if (callProc == null)
  155. return null;
  156. rs = callProc.executeQuery();
  157. return rs;
  158. }
  159. /**
  160. * 返回单个数据(INT i OUT的位�?
  161. * @return
  162. * @throws Exception
  163. */
  164. public Object exec(int i) throws Exception{
  165. if (callProc == null)
  166. return new Object();
  167. callProc.execute();
  168. return callProc.getObject(i);
  169. }
  170. public Object get_one() throws Exception{
  171. if (callProc == null)
  172. return new Object();
  173. rs = callProc.executeQuery();
  174. if(rs.next()){
  175. return rs.getObject(1);
  176. }
  177. return null;
  178. }
  179. public <T> List<T> execExt(Class<T> cls,int index) throws Exception{
  180. List<T> list = new ArrayList<T>();
  181. if (callProc == null)
  182. return list;
  183. ResultSet rs = callProc.executeQuery();
  184. //rs = (ResultSet)callProc.getObject(index);
  185. return toList(rs,cls);
  186. }
  187. /**
  188. * 单个列表
  189. * @param cls
  190. * @param index
  191. * @return
  192. * @throws Exception
  193. */
  194. public <T> List<T> exec(Class<T> cls,int index) throws Exception{
  195. List<T> list = new ArrayList<T>();
  196. if (callProc == null)
  197. return list;
  198. callProc.execute();
  199. rs = (ResultSet)callProc.getObject(index);
  200. return toList(rs,cls);
  201. }
  202. public void exec()throws Exception{
  203. if (callProc == null)
  204. return;
  205. callProc.execute();
  206. }
  207. public <T> List<T> getResult(Class<T> cls,int index)throws Exception{
  208. rs = (ResultSet)callProc.getObject(index);
  209. return toList(rs,cls);
  210. }
  211. public Object getResult(int index) throws SQLException{
  212. return callProc.getObject(index);
  213. }
  214. public String queryClob()throws SQLException, IOException{
  215. String dstr = null;
  216. if (callProc == null)
  217. return dstr;
  218. rs = callProc.executeQuery();
  219. if(rs.next()){
  220. java.sql.Clob c = rs.getClob(1);
  221. Reader inStream = c.getCharacterStream();
  222. char[] cr = new char[(int)c.length()];
  223. inStream.read(cr);
  224. dstr = new String(cr);
  225. inStream.close();
  226. }
  227. rs.close();
  228. return dstr;
  229. }
  230. public Blob queryBlob()throws SQLException, IOException{
  231. if (callProc == null)
  232. return null;
  233. rs = callProc.executeQuery();
  234. java.sql.Blob blob=null;
  235. if(rs.next()){
  236. blob = rs.getBlob(1);
  237. }
  238. rs.close();
  239. return blob;
  240. }
  241. public <T> List<T> toList(ResultSet rs, Class<T> cls) throws Exception{
  242. List<T> list = new ArrayList<T>();
  243. try {
  244. ResultSetMetaData meta = rs.getMetaData();
  245. T obj = null;
  246. while (rs.next()) {
  247. obj = cls.newInstance();//(T) Class.forName(cls.getName()).newInstance();
  248. Method[] meth = obj.getClass().getMethods();
  249. for (int i = 1; i <= meta.getColumnCount(); i++) {
  250. String colName = meta.getColumnName(i);
  251. colName = new String(colName.charAt(0)+"").toUpperCase()+""+colName.substring(1).toLowerCase();
  252. //colName = colName.replace(colName.charAt(0) + "",new String(colName.charAt(0) + "").toUpperCase());
  253. String methodName = "set" + colName;
  254. boolean flag = false;
  255. Class<?> c = null;
  256. for(Method m:meth){
  257. if(methodName.equals(m.getName())){
  258. c = m.getParameterTypes()[0];
  259. flag=true;
  260. break;
  261. }
  262. }
  263. if(!flag){
  264. continue;
  265. }
  266. //////////////////////////////////////////////////////////////
  267. Object value = (Object)rs.getObject(i);
  268. if(value != null){
  269. if("BigDecimal".equals(value.getClass().getSimpleName())){
  270. if("java.lang.Float".equals(c.getName())|| "float".equals(c.getName())){
  271. value=Float.parseFloat(value.toString());
  272. }else if("java.lang.Long".equals(c.getName()) || "long".equals(c.getName())){
  273. double diven = Double.parseDouble(value.toString());
  274. value = (long)Math.floor(diven);
  275. }else if("java.lang.Double".equals(c.getName())){
  276. value = Double.parseDouble(value.toString());
  277. }else if("java.lang.String".equals(c.getName())){
  278. value=value.toString();
  279. }
  280. }else if("Date".equals(value.getClass().getSimpleName())){
  281. value = rs.getString(i).substring(0, 19);
  282. }else if("Timestamp".equals(value.getClass().getSimpleName())){
  283. //value = value.toString();
  284. value = CommonMethod.TimestampFormat((Timestamp)value,"yyyy-MM-dd HH:mm:ss");
  285. }else if("Integer".equals(value.getClass().getSimpleName())){
  286. if("java.lang.Float".equals(c.getName())){
  287. value=Float.parseFloat(value.toString());
  288. }else if("java.lang.Long".equals(c.getName())){
  289. value=Long.parseLong(value.toString());
  290. }else if("java.lang.String".equals(c.getName())){
  291. value=value.toString();
  292. }
  293.  
  294. }
  295. }
  296. //System.out.println(value);
  297. Method method = obj.getClass().getMethod(methodName,c);
  298. method.invoke(obj, value);
  299. }
  300. list.add(obj);
  301. }
  302. }catch(Exception ex) {
  303. ex.printStackTrace();
  304. }finally{
  305. try{
  306. rs.close();
  307. }catch(Exception ex){
  308. throw new Exception(ex.getMessage());
  309. }
  310. }
  311. return list;
  312. }
  313. /**
  314. * set auto commit for roll back
  315. *
  316. * @param value
  317. * @throws SQLException
  318. */
  319. public void setAutoCommit(boolean value) throws SQLException {
  320. if (this.conn != null)
  321. this.conn.setAutoCommit(value);
  322. }
  323.  
  324. /**
  325. * commit current operate
  326. *
  327. * @throws SQLException
  328. */
  329. public void commit() throws SQLException {
  330. this.conn.commit();
  331. }
  332.  
  333. /**
  334. * @function roll back operate
  335. * @throws SQLException
  336. */
  337. public void rollback() throws SQLException {
  338. this.conn.rollback();
  339. }
  340. public void Close(){
  341. close();
  342. }
  343. public void close() {
  344. try {
  345. if (rs != null) {
  346. rs.close();
  347. rs = null;
  348. }
  349. } catch (Exception e) {
  350. System.out.println("close rs error!");
  351. } finally {
  352. try {
  353. if (callProc != null) {
  354. callProc.close();
  355. callProc = null;
  356. }
  357. } catch (Exception e) {
  358. System.out.println("close callProc error!");
  359. } finally {
  360. try {
  361. if (conn != null) {
  362. conn.close();
  363. conn = null;
  364. }
  365. } catch (Exception e) {
  366. System.out.println("close conn error!");
  367. }
  368. }
  369. }
  370. }
  371. public void setString(int index, String value) throws SQLException {
  372. callProc.setString(index, value);
  373. }
  374. public void setLong(int index, Long value) throws SQLException {
  375. callProc.setLong(index, value);
  376. }
  377. public void setInt(int index, int value) throws SQLException {
  378. callProc.setInt(index, value);
  379. }
  380. public void setArray(int index, Array value) throws SQLException {
  381. callProc.setArray(index, value);
  382. }
  383. public void setDouble(int index, Double value) throws SQLException {
  384. callProc.setDouble(index, value);
  385. }
  386. public void setFloat(int index, float value) throws SQLException {
  387. callProc.setFloat(index, value);
  388. }
  389. public void addBatch(String value) throws SQLException {
  390. callProc.addBatch(value);
  391. }
  392. public void setTime(int index, Time value) throws SQLException {
  393. callProc.setTime(index, value);
  394. }
  395. public void setTimestamp(int index, Timestamp value) throws SQLException {
  396. callProc.setTimestamp(index, value);
  397. }
  398. public void setTimestamp(int index, java.util.Date date) throws SQLException {
  399. Timestamp tstamp = new Timestamp(date.getTime());
  400. callProc.setTimestamp(index, tstamp);
  401. }
  402. public void setTimestamp(int index, String value,String type) throws SQLException {
  403. java.util.Date dt = CommonMethod.strToDate(value, type);
  404. Timestamp tstamp = new Timestamp(dt.getTime());
  405. callProc.setTimestamp(index, tstamp);
  406. }
  407. public void setTimestamp(int index, String value) throws SQLException {
  408. java.util.Date dt = CommonMethod.strToDate(value,"yyyy-MM-dd HH:mm:ss");
  409. Timestamp tstamp = new Timestamp(dt.getTime());
  410. callProc.setTimestamp(index, tstamp);
  411. }
  412. public void setDate(int index, Date value) throws SQLException {
  413. callProc.setDate(index, value);
  414. }
  415. public void setBoolean(int index, boolean value) throws SQLException {
  416. callProc.setBoolean(index, value);
  417. }
  418.  
  419. public void setOutParam(int index, int value) throws SQLException {
  420. callProc.registerOutParameter(index, value);
  421. }
  422.  
  423. public void setBytes(int index, byte[] value) throws SQLException {
  424. callProc.setBytes(index, value);
  425. }
  426. public void setBigDecimal(int index,int n) throws SQLException{
  427. BigDecimal bgd = new BigDecimal(n);
  428. callProc.setBigDecimal(index, bgd);
  429. }
  430. public void setBigDecimal(int index,long n) throws SQLException{
  431. BigDecimal bgd = new BigDecimal(n);
  432. callProc.setBigDecimal(index, bgd);
  433. }
  434. public void setBinaryStream(int index, InputStream value, int len)
  435. throws SQLException {
  436. callProc.setBinaryStream(index, value, len);
  437. }
  438. public void setClob(int index, Clob value) throws SQLException {
  439. callProc.setClob(index, value);
  440. }
  441. public void setBlob(int index, Blob value) throws SQLException {
  442. callProc.setBlob(index, value);
  443. }
  444. }
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement
Public Pastes
Advertisement
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy.  OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!