1 ConnectPool .java:
2
3 package pool;
4
5 /**
6 * Title: ConnectPool.Java
7 * Description: 连接池治理器
8 * Copyright: Copyright (c) 2002/12/25
9 * Company:
10 * Author :
11 * Version 2.0
12 */
13
14 import java.io.*;
15 import java.sql.*;
16 import java.util.*;
17 import java.util.Date;
18
19 /**
20 * 治理类DBConnectionManager支持对一个或多个由属性文件定义的数据库连接
21 * 池的访问.客户程序可以调用getInstance()方法访问本类的唯一实例.
22 */
23 public class ConnectPool {
24 static public ConnectPool instance; // 唯一实例
25 static public int clients;
26 public Vector drivers = new Vector(); //驱动
27 public PrintWriter log;
28 public Hashtable pools = new Hashtable(); //连接
29
30 /**
31 * 返回唯一实例.假如是第一次调用此方法,则创建实例
32 *
33 * @return DBConnectionManager 唯一实例
34 */
35 static synchronized public ConnectPool getInstance() {
36 if (instance == null) {
37 instance = new ConnectPool();
38 }
39
40 clients++;
41 return instance;
42 }
43
44 /**
45 * 返回唯一实例.假如是第一次调用此方法,则创建实例
46 *
47 * @return DBConnectionManager 唯一实例
48 */
49 static synchronized public ConnectPool getInstance(String url) {
50 if (instance == null) {
51 instance = new ConnectPool(url);
52 }
53 clients++;
54 return instance;
55 }
56
57
58 /**
59 * 建构函数私有以防止其它对象创建本类实例
60 */
61 public ConnectPool() {
62 init();
63 }
64
65 /**
66 * 建构造带参数的函数
67 *
68 */
69 public ConnectPool(String url) {
70 init(url);
71 }
72
73 /**
74 * 将连接对象返回给由名字指定的连接池
75 *
76 * @param name 在属性文件中定义的连接池名字
77 * @param con 连接对象
78 */
79 public void freeConnection(String name, Connection con) {
80 DBConnectionPool pool = (DBConnectionPool) pools.get(name);
81 if (pool != null) {
82 pool.freeConnection(con);
83 } else {
84 // System.out.println("pool ==null");
85 }
86 clients--;
87 }
88
89 /**
90 * 获得一个可用的(空闲的)连接.假如没有可用连接,且已有连接数小于最大连接数
91 * 限制,则创建并返回新连接
92 *
93 * @param name 在属性文件中定义的连接池名字
94 * @return Connection 可用连接或null
95 */
96 public Connection getConnection(String name) {
97 DBConnectionPool pool = (DBConnectionPool) pools.get(name);
98 if (pool != null) {
99 //return pool.getConnection();
100 return pool.returnConnection();
101 }
102 return null;
103 }
104
105 /**
106 * 获得一个可用连接.若没有可用连接,且已有连接数小于最大连接数限制,
107 * 则创建并返回新连接.否则,在指定的时间内等待其它线程释放连接.
108 *
109 * @param name 连接池名字
110 * @param time 以毫秒计的等待时间
111 * @return Connection 可用连接或null
112 */
113 public Connection getConnection(String name, long time) {
114 DBConnectionPool pool = (DBConnectionPool) pools.get(name);
115 if (pool != null) {
116 return pool.getConnection(time);
117 }
118 return null;
119 }
120
121 /**
122 * 关闭所有连接,撤销驱动程序的注册
123 */
124 public synchronized void release() {
125 // 等待直到最后一个客户程序调用
126 if (--clients != 0) {
127 return;
128 }
129
130 Enumeration allPools = pools.elements();
131 while (allPools.hasMoreElements()) {
132 DBConnectionPool pool = (DBConnectionPool) allPools.nextElement();
133 pool.release();
134 }
135 Enumeration allDrivers = drivers.elements();
136 while (allDrivers.hasMoreElements()) {
137 Driver driver = (Driver) allDrivers.nextElement();
138 try {
139 DriverManager.deregisterDriver(driver);
140
141 log("撤销JDBC驱动程序 " + driver.getClass().getName() + "的注册");
142 } catch (SQLException e) {
143 log(e, "无法撤销下列JDBC驱动程序的注册: " + driver.getClass().getName());
144 }
145 }
146 }
147
148 /**
149 * 根据指定属性创建连接池实例.
150 *
151 * @param props 连接池属性
152 */
153 private void createPools(Properties props) {
154 Enumeration propNames = props.propertyNames();
155 while (propNames.hasMoreElements()) {
156 String name = (String) propNames.nextElement();
157 if (name.endsWith(".url")) {
158 String poolName = name.substring(0, name.lastIndexOf("."));
159 String url = props.getProperty(poolName + ".url");
160 if (url == null) {
161 log("没有为连接池" + poolName + "指定URL");
162 continue;
163 }
164 String user = props.getProperty(poolName + ".user");
165 String passWord = props.getProperty(poolName + ".password");
166 String maxconn = props.getProperty(poolName + ".maxconn", "0");
167 int max;
168 try {
169 max = Integer.valueOf(maxconn).intValue();
170 } catch (NumberFormatException e) {
171 log("错误的最大连接数限制: " + maxconn + " .连接池: " + poolName);
172 max = 0;
173 }
174 DBConnectionPool pool = new DBConnectionPool(poolName, url,
175 user, passWord, max);
176 pools.put(poolName, pool);
177 log("成功创建连接池" + poolName);
178 }
179 }
180 }
181
182 /**
183 * 读取属性完成初始化
184 */
185 private void init() {
186 try {
187 Properties p = new Properties();
188 String configs = System.getProperty("user.dir") + "//conf//poolName.properties";
189 //String configs = System.getProperty("user.dir") + "//conf//poolName.properties";
190
191 //System.out.println("configs file local at " + configs);
192 FileInputStream is = new FileInputStream(configs);
193 Properties dbProps = new Properties();
194 try {
195 dbProps.load(is);
196 } catch (Exception e) {
197 System.err.println("不能读取属性文件. " +
198 "请确保db.properties在CLASSPATH指定的路径中");
199 return;
200 }
201 String logFile = dbProps.getProperty("logfile",
202 "DBConnectionManager.log");
203 try {
204
205 log = new PrintWriter(new FileWriter(logFile, true), true);
206 } catch (IOException e) {
207 System.err.println("无法打开日志文件: " + logFile);
208 log = new PrintWriter(System.err);
209 }
210 loadDrivers(dbProps);
211 createPools(dbProps);
212 } catch (Exception e) {}
213 }
214
215 /**
216 * 读取属性完成初始化
217 */
218 private void init(String url) {
219 try {
220 Properties p = new Properties();
221 String configs = System.getProperty("user.dir") +
222 "//conf//"+url;
223 //System.out.println("configs file local at " + configs);
224 FileInputStream is = new FileInputStream(configs);
225 Properties dbProps = new Properties();
226 try {
227 dbProps.load(is);
228 } catch (Exception e) {
229 System.err.println("不能读取属性文件. " +
230 "请确保db.properties在CLASSPATH指定的路径中");
231 return;
232 }
233 String logFile = dbProps.getProperty("logfile",
234 "DBConnectionManager.log");
235 try {
236
237 log = new PrintWriter(new FileWriter(logFile, true), true);
238 } catch (IOException e) {
239 System.err.println("无法打开日志文件: " + logFile);
240 log = new PrintWriter(System.err);
241 }
242 loadDrivers(dbProps);
243 createPools(dbProps);
244 } catch (Exception e) {}
245 }
246
247
248 /**
249 171 * 装载和注册所有JDBC驱动程序
250 172 *
251 173 * @param props 属性
252 174 */
253 private void loadDrivers(Properties props) {
254 String driverClasses = props.getProperty("drivers");
255 StringTokenizer st = new StringTokenizer(driverClasses);
256 while (st.hasMoreElements()) {
257 String driverClassName = st.nextToken().trim();
258 try {
259 Driver driver = (Driver)
260 Class.forName(driverClassName).newInstance();
261 DriverManager.registerDriver(driver);
262 drivers.addElement(driver);
263 //System.out.println(driverClassName);
264 log("成功注册JDBC驱动程序" + driverClassName);
265 } catch (Exception e) {
266 log("无法注册JDBC驱动程序: " +
267 driverClassName + ", 错误: " + e);
268 }
269 }
270 }
271
272 /**
273 * 将文本信息写入日志文件
274 */
275 private void log(String msg) {
276 log.println(new Date() + ": " + msg);
277 }
278
279 /**
280 * 将文本信息与异常写入日志文件
281 */
282 private void log(Throwable e, String msg) {
283 log.println(new Date() + ": " + msg);
284 e.printStackTrace(log);
285 }
286
287 /**
288 * 此内部类定义了一个连接池.它能够根据要求创建新连接,直到预定的最
289 * 大连接数为止.在返回连接给客户程序之前,它能够验证连接的有效性.
290 */
291
292 class DBConnectionPool {
293 //private int checkedOut;
294 private Vector freeConnections = new Vector();
295 private int maxConn;
296 private String name;
297 private String password;
298 private String URL;
299 private String user;
300
301 /**
302 * 创建新的连接池
303 *
304 * @param name 连接池名字
305 * @param URL 数据库的JDBC URL
306 * @param user 数据库帐号,或 null
307 * @param password 密码,或 null
308 * @param maxConn 此连接池答应建立的最大连接数
309 */
310 public DBConnectionPool(String name, String URL, String user,
311 String password, int maxConn) {
312 this.name = name;
313 this.URL = URL;
314 this.user = user;
315 this.password = password;
316 this.maxConn = maxConn;
317 }
318
319 /**
320 * 将不再使用的连接返回给连接池
321 *
322 * @param con 客户程序释放的连接
323 */
324 public synchronized void freeConnection(Connection con) {
325 // 将指定连接加入到向量末尾
326 try {
327 if (con.isClosed()) {
328 // System.out.println("before freeConnection con is closed");
329 }
330 freeConnections.addElement(con);
331 Connection contest = (Connection) freeConnections.lastElement();
332 if (contest.isClosed()) {
333 // System.out.println("after freeConnection contest is closed");
334 }
335 notifyAll();
336 } catch (SQLException e) {
337 // System.out.println(e);
338 }
339 }
340
341 /**
342 * 从连接池获得一个可用连接.如没有空闲的连接且当前连接数小于最大连接
343 * 数限制,则创建新连接.如原来登记为可用的连接不再有效,则从向量删除之,
344 * 然后递归调用自己以尝试新的可用连接.
345 */
346 public synchronized Connection getConnection() {
347 Connection con = null;
348 if (freeConnections.size() > 0) {
349 // 获取向量中第一个可用连接
350 con = (Connection) freeConnections.firstElement();
351 freeConnections.removeElementAt(0);
352 try {
353 if (con.isClosed()) {
354 log("从连接池" + name + "删除一个无效连接");
355 System.out.println("从连接池" + name + "删除一个无效连接");
356 // 递归调用自己,尝试再次获取可用连接
357 con = getConnection();
358 }
359 } catch (SQLException e) {
360 log("从连接池" + name + "删除一个无效连接时错误");
361 System.out.println("从连接池" + name + "删除一个无效连接出错");
362 // 递归调用自己,尝试再次获取可用连接
363 con = getConnection();
364 }
365 if (freeConnections.size() > maxConn) {
366 System.out.println(" 删除一个溢出连接 ");
367 releaseOne();
368 }
369 } else if ((maxConn == 0) || (freeConnections.size() < maxConn)) {
370 con = newConnection();
371 }
372
373 return con;
374 }
375
376 public synchronized Connection returnConnection() {
377 Connection con = null;
378 //假如闲置小于最大连接,返回一个新连接
379 if (freeConnections.size() < maxConn) {
380 con = newConnection();
381 }
382 //假如闲置大于最大连接，返回一个可用的旧连接
383 else if (freeConnections.size() >= maxConn) {
384
385 con = (Connection) freeConnections.firstElement();
386 System.out.println(" [a 连接池可用连接数 ] : " + "[ " +
387 freeConnections.size() + " ]");
388 freeConnections.removeElementAt(0);
389 System.out.println(" [b 连接池可用连接数 ] : " + "[ " +
390 freeConnections.size() + " ]");
391 try {
392 if (con.isClosed()) {
393 log("从连接池" + name + "删除一个无效连接");
394 System.out.println("从连接池" + name + "删除一个无效连接");
395 returnConnection();
396 }
397 } catch (SQLException e) {
398 log("从连接池" + name + "删除一个无效连接时错误");
399 System.out.println("从连接池" + name + "删除一个无效连接出错");
400 returnConnection();
401 }
402 }
403 return con;
404 }
405
406 /**
407 * 从连接池获取可用连接.可以指定客户程序能够等待的最长时间
408 * 参见前一个getConnection()方法.
409 *
410 * @param timeout 以毫秒计的等待时间限制
411 */
412 public synchronized Connection getConnection(long timeout) {
413 long startTime = new Date().getTime();
414 Connection con;
415 while ((con = getConnection()) == null) {
416 try {
417 wait(timeout);
418 } catch (InterruptedException e) {}
419 if ((new Date().getTime() - startTime) >= timeout) {
420 // wait()返回的原因是超时
421 return null;
422 }
423 }
424 return con;
425 }
426
427 /**
428 * 关闭所有连接
429 */
430 public synchronized void release() {
431 Enumeration allConnections = freeConnections.elements();
432 while (allConnections.hasMoreElements()) {
433 Connection con = (Connection) allConnections.nextElement();
434 try {
435 con.close();
436 log("关闭连接池" + name + "中的一个连接");
437 } catch (SQLException e) {
438 log(e, "无法关闭连接池" + name + "中的连接");
439 }
440 }
441 freeConnections.removeAllElements();
442 }
443
444 /**
445 * 关闭一个连接
446 */
447 public synchronized void releaseOne() {
448 if (freeConnections.firstElement() != null) {
449 Connection con = (Connection) freeConnections.firstElement();
450 try {
451 con.close();
452 System.out.println("关闭连接池" + name + "中的一个连接");
453 log("关闭连接池" + name + "中的一个连接");
454 } catch (SQLException e) {
455
456 System.out.println("无法关闭连接池" + name + "中的一个连接");
457 log(e, "无法关闭连接池" + name + "中的连接");
458 }
459 } else {
460 System.out.println(
461 "releaseOne() bug.......................................................");
462
463 }
464 }
465
466 /**
467 * 创建新的连接
468 */
469 private Connection newConnection() {
470 Connection con = null;
471 try {
472 if (user == null) {
473 con = DriverManager.getConnection(URL);
474 } else {
475 con = DriverManager.getConnection(URL, user, password);
476 }
477 log("连接池" + name + "创建一个新的连接");
478
479 } catch (SQLException e) {
480 log(e, "无法创建下列URL的连接: " + URL);
481 return null;
482 }
483 return con;
484 }
485 }
486 }
487
488 -----------------------------------------------------------------------------------------------------------------
489
490 PoolMan.java:
491
492 package pool;
493
494 /**
495 * Title: ConnectPool.java
496 * Description: 数据库操作
497 * Copyright: Copyright (c) 2002/12/25
498 * Company:
499 * Author :
500 * remark : 加入指针回滚
501 * Version 2.0
502 */
503
504 import java.io.*;
505 import pool.*;
506 import java.sql.*;
507 import java.util.*;
508 import java.util.Date;
509 import java.net.*;
510
511 public class PoolMan extends ConnectPool {
512
513 private ConnectPool connMgr;
514 private Statement stmt;
515 private Connection con;
516 private ResultSet rst;
517
518 /**
519 *对象连接初始化
520 * */
521
522 public Connection getPool(String name) throws Exception {
523 try {
524 connMgr = ConnectPool.getInstance();
525 con = connMgr.getConnection(name);
526 } catch (Exception e) {
527 System.err.println("不能创建连接!请尝试重启应用服务器");
528
529 }
530 return con;
531 }
532
533 /**
534 * 2008-10-14
535 *对象连接初始化
536 * */
537
538 public Connection getPool(String name,String url) throws Exception {
539 try {
540 connMgr = ConnectPool.getInstance(url);
541 con = connMgr.getConnection(name);
542 } catch (Exception e) {
543 System.err.println("不能创建连接!请尝试重启应用服务器");
544
545 }
546 return con;
547 }
548
549
550 /**
551 *同以上方法,加入连接空闲等待时间
552 *待用方法
553 * */
554
555 public Connection getPool_t(String name, long time) throws Exception {
556 try {
557 connMgr = ConnectPool.getInstance();
558 con = connMgr.getConnection(name, time);
559 } catch (Exception e) {
560 System.err.println("不能创建连接!");
561
562 }
563 return con;
564 }
565
566 /**
567 *执行查询方法1
568 * */
569 public ResultSet executeQuery(String SqlStr) throws Exception {
570 ResultSet result = null;
571 try {
572 stmt = con.createStatement();
573 result = stmt.executeQuery(SqlStr);
574 // here add one line by jnma 12.11
575 con.commit();
576 } catch (java.sql.SQLException e) {
577 throw new Exception("执行查询语句出错");
578 }
579 return result;
580 }
581
582 /**
583 *执行查询方法2
584 * */
585 public ResultSet getRst(String SqlStr) throws Exception {
586 // ResultSet result = null;
587 try {
588 stmt = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
589 ResultSet.CONCUR_UPDATABLE);
590 rst = stmt.executeQuery(SqlStr);
591 // here add one line by jnma 12.11
592 con.commit();
593 } catch (java.sql.SQLException e) {
594 throw new Exception("执行查询语句出错");
595 }
596 return rst;
597 }
598
599 /**
600 *执行更新
601 * */
602 public int Update(String SqlStr) throws Exception {
603 int result = -1;
604 try {
605 stmt = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
606 ResultSet.CONCUR_UPDATABLE);
607 result = stmt.executeUpdate(SqlStr);
608 // here add one line by jnma 12.11
609 con.commit();
610 if (result == 0) {
611 System.out.println("执行delete,update,insert SQL出错");
612 }
613 } catch (java.sql.SQLException e) {
614 System.err.println("执行delete,update,insert SQL出错");
615 }
616 return result;
617 }
618
619 /**
620 *执行事务处理
621 * */
622 public boolean handleTransaction(Vector SqlArray) throws Exception {
623 boolean result = false;
624 int ArraySize = SqlArray.size();
625 try {
626 stmt = con.createStatement();
627 con.setAutoCommit(false);
628 System.out.println("ArraySize is" + ArraySize);
629 for (int i = 0; i < ArraySize; i++) {
630 System.out.println(" 开始执行语句" + (String) SqlArray.elementAt(i));
631 stmt.executeUpdate((String) SqlArray.elementAt(i));
632 System.out.println(" 执行成功");
633 }
634 con.commit();
635 con.setAutoCommit(true); //必须
636 System.out.println("事务执行成功");
637 result = true;
638 } catch (java.sql.SQLException e) {
639 try {
640 System.out.println(e.toString());
641 System.out.println("数据库操作失败");
642 con.rollback();
643 } catch (java.sql.SQLException Te) {
644 System.err.println("事务出错回滚异常");
645 }
646 }
647 try {
648 con.setAutoCommit(true);
649 } catch (java.sql.SQLException e) {
650 System.err.println("设置自动提交失败");
651 }
652 return result;
653 }
654
655 /**
656 *释放连接
657 * */
658 public void close(String name) throws Exception {
659 try {
660 if (stmt != null) {
661 stmt.close();
662 }
663 if (con != null) {
664 connMgr.freeConnection(name, con);
665
666 System.out.println(" [c 正在释放一个连接 ] ");
667
668 }
669 } catch (java.sql.SQLException e) {
670 System.err.println("释放连接出错");
671 }
672 }
673
674 }
675
676 -----------------------------------------------------------------------------------------------------------
677
678 MyDB.java
679
680
681
682 package db;
683
684 import java.sql.*;
685 import pool.PoolMan;
686 import base.Constants;
687 import java.util.List;
688 import java.util.ArrayList;
689 import java.util.HashMap;
690 import java.util.Iterator;
691 import org.apache.commons.beanutils.DynaBean;
692 import org.apache.commons.beanutils.RowSetDynaClass;
693 import java.util.Vector;
694 import bean.Log;
695 import bean.AdminBean;
696 import base.FunctionStatic;
697 import forms.system.DeptForm;
698 import org.apache.commons.beanutils.BeanUtils;
699
700 /*
701 //数据层续承此类包
702 例如：public class Function extends MyDB {。。。。。。
703 注意：当调用数据层都必需关闭数据库
704 Function fun=new Function()
705 fun.Close
706 */
707 public class MyDB {
708 public Connection conn = null;
709 public ResultSet rs = null;
710 public ResultSet rs1 = null;
711 public java.sql.PreparedStatement prepar = null;
712 public Statement stmt = null;
713 public boolean flag = false;
714 public java.sql.CallableStatement proc = null;
715 public int pagecount = 0;
716 public int pagedata = 0;
717 PoolMan PoolMan = new PoolMan();
718 public MyDB() {
719
720 //通过名称得到连接池
721 conn = PoolMan.getConnection(Constants.SYSTEM_DB_POOL_NAME);
722 //System.out.println("连接成功");
723 }
724
725 //关闭连接
726 public void Close() {
727 try {
728 if (rs != null) {
729 rs.close();
730 }
731 if (rs1 != null) {
732 rs1.close();
733 }
734 if (prepar != null) {
735 prepar.close();
736 }
737 if (proc != null) {
738 proc.close();
739 }
740 if (stmt != null) {
741 stmt.close();
742 }
743 if (conn != null) {
744 conn.close();
745 }
746 } catch (SQLException ex) {
747 ex.printStackTrace();
748 }
749 // System.out.println("关闭成功");
750 }
751
752 public int executeSql(String sql)throws DbAccessException{
753 int flag = 0;
754 try {
755 stmt = conn.createStatement();
756 flag = stmt.executeUpdate(sql);
757 // return flag;
758 } catch (Exception e) {
759 throw new DbAccessException("操作数据库出错");
760 }finally{
761 //return flag;
762 }
763 return flag;
764 }
765
766 //结果集的数据转存入List
767 public List setResultToList(ResultSet rs) {
768 List list = new ArrayList();
769 try {
770 ResultSetMetaData md = rs.getMetaData();
771
772 /*获取结果集的列数*/
773 int columnCount = md.getColumnCount();
774 while (rs.next()) {
775 HashMap map = new HashMap();
776 /*把每一行以(key,value)存入HashMap, 列名做为key,列值做为value */
777 for (int i = 1; i <= columnCount; ++i) {
778 String tempStr = rs.getString(i);
779 if (tempStr == null) {
780 tempStr = "";
781 }
782 map.put(md.getColumnName(i), tempStr);
783 }
784 /*把装有一行数据的HashMap存入list*/
785 list.add(map);
786 }
787 } catch (SQLException ex) {
788 ex.printStackTrace();
789 }
790 //System.out.println(list.size());
791 return list;
792 }
793
794 /*
795 结果集的数据转存入List 3hsjc
796
797 selectsql:查询的字段
798 wheresql:查询条件的组合语句
799 sortsql：排序的组合语句
800 PageSize：每页显示的条数
801 PageNum：当前条数
802 tableName：表名
803 keyName：表中的主键字段名
804
805 页面调查方法：
806 <%@page import="java.util.*"%>
807
808 List list = (List) request.getAttribute("list"); //得到类包里存的值
809 if (list!=null){
810 for (int i = 0; i < list.size(); i++) {
811 HashMap map = (HashMap) list.get(i);
812 System.out.println(map.get("id")); //字段名同库字段名
813 }
814 }
815
816 */
817 public List getListByMap(String selectsql, String wheresql, String sortsql,
818 int PageSize, int PageNum, String tableName,
819 String keyName) { //获取 列表信息
820 List list = new ArrayList();
821
822 String sql = "";
823 try {
824 if ("".equals(selectsql)) {
825 selectsql = "*";
826 }
827 if ("".equals(tableName) || "".equals(keyName)) {
828 return null;
829 }
830 sql = "SELECT top " + PageSize + " " + selectsql + " FROM " +
831 tableName + " WHERE 1=1 " + wheresql +
832 " and " + keyName + " not in (select top " +
833 PageSize * (PageNum - 1) + " " + keyName + " from " +
834 tableName + " where 1=1 " + wheresql + sortsql + ")" +
835 sortsql + "";
836
837 System.out.println("sql = " + sql );
838 stmt = conn.createStatement();
839 rs = stmt.executeQuery(sql);
840
841 list = this.setResultToList(rs);
842 return list;
843 } catch (SQLException ex) {
844 ex.printStackTrace();
845 }
846 return null;
847 }
848
849
850 /*
851 结果集的数据转存入List 3hzyh
852
853 selectsql:查询的字段
854 wheresql:查询条件的组合语句
855 sortsql：排序的组合语句
856 PageSize：每页显示的条数
857 PageNum：当前条数
858 tableName：表名
859 keyName：表中的主键字段名
860
861 页面调查方法：
862 <%@page import="org.apache.struts.util.RequestUtils"%>
863 <%@page import="org.apache.commons.beanutils.*"%>
864 <%@page import="java.util.*"%>
865
866 Vector list = (Vector) request.getAttribute("list"); //得到类包里存的值
867 if (list!=null){
868 for (int i = 0; i < list.size(); i++) {
869 DynaBean info = (DynaBean) list.get(i);
870 System.out.println(BeanUtils.getSimpleProperty(info,"id")); //字段名全小写
871 }
872 }
873
874 */
875 public Vector getList(String selectsql, String wheresql, String sortsql,
876 int PageSize, int PageNum, String tableName,
877 String keyName) { //获取 列表信息
878 Vector vector = new Vector();
879
880 String sql = "";
881 try {
882 if ("".equals(selectsql)) {
883 selectsql = "*";
884 }
885 if ("".equals(tableName) || "".equals(keyName)) {
886 return null;
887 }
888 sql = "SELECT top " + PageSize + " " + selectsql + " FROM " +
889 tableName + " WHERE 1=1 " + wheresql +
890 " and " + keyName + " not in (select top " +
891 PageSize * (PageNum - 1) + " " + keyName + " from " +
892 tableName + " where 1=1 " + wheresql + sortsql + ")" +
893 sortsql + "";
894
895 System.out.println(sql);
896 stmt = conn.createStatement();
897 rs = stmt.executeQuery(sql);
898
899 /*对查询结果集RS进行行集的转换*/
900 RowSetDynaClass rsdc = new RowSetDynaClass(rs);
901 /*把行集的每一行另存为LIST，以便存取操作*/
902 List rsDynaClass = rsdc.getRows();
903
904 Iterator itr = rsDynaClass.iterator();
905
906 int i = 0;
907 while (itr.hasNext()) {
908 i++;
909 /*对LIST的每一行数据，动态生成虚拟BEAN*/
910 DynaBean dBean = (DynaBean) itr.next();
911 vector.add(dBean);
912
913 }
914 return vector;
915 } catch (SQLException ex) {
916 ex.printStackTrace();
917 }
918 return null;
919 }
920
921 //======================================================================
922 //Created by linjunna 2009-08-13
923 //增加用户操作日志
924 /*
925 ActionCode:
926 1:登录系统
927 2:......
928 */
929 public boolean InsertLog(Log log) {
930 String sql = "INSERT INTO SU_UserLog(userName,note,userAction,ActionCode,dateCreated) VALUES (?,?,?,?,getdate() )";
931 int flag=0;
932 try {
933 prepar = conn.prepareStatement(sql);
934 prepar.setString(1, log.getUserName());
935 prepar.setString(2, log.getNote());
936 prepar.setString(3, log.getUserAction());
937 prepar.setString(4, log.getActionCode());
938 flag = prepar.executeUpdate();
939 if (flag>1) return true;
940 } catch (Exception ex) {
941 }
942 return false;
943 }
944
945 /*
946 函数说明：获取表中某一字段的值
947 参数说明你个：field:要获取字段的名称；keyName:判断的条件；keyValue:判断的条件的值；tableName:数据库表名
948 返回值说明：String
949 */
950 public String getFieldValue(String field,String keyName,String keyValue,String tableName) {
951 String result = "";
952 FunctionStatic fun = new FunctionStatic();
953 try {
954 String sql = " select "+field+" from "+tableName+" where "+keyName+" ='" + keyValue + "' ";
955
956 prepar = conn.prepareStatement(sql);
957 rs = prepar.executeQuery();
958 while (rs.next()){
959 result = fun.getNullString(rs.getString(field));
960 break;
961 }
962 } catch (Exception e) {
963 e.printStackTrace();
964 }
965 return result;
966 }
967
968
969 /*
970 函数描述：修改表的某一字段
971 */
972 public int updateTableField(String TableName, String FieldName,String FieldValue, String PKID) {
973 int ret = 0;
974 String sql = "update ? set ? = ? where ID=?";
975 try {
976 prepar = conn.prepareStatement(sql);
977 prepar.setString(1, TableName);
978 prepar.setString(2, FieldName);
979 prepar.setString(3, FieldValue);
980 prepar.setString(4, PKID);
981
982 ret = prepar.executeUpdate(sql);
983 } catch (Exception e) {
984 e.printStackTrace();
985 }
986 return ret;
987 }
988
989 /*
990 函数说明：批量删除
991 参数说明：FieldName:表的字段名；FieldValues：字段值,以逗号分隔，形式如："h0001,h0002,h0003,..."
992 返回值：整数
993 */
994 public int BatchDeleteByIds( String TableName, String FieldName,String FieldValues)
995 {
996 String[] strs=base.FunctionStatic.splitSkins(FieldValues,",");
997 int flag = strs.length;
998 try {
999
1000 conn.setAutoCommit(false);
1001 for (int i=0;i<strs.length;i++)
1002 {
1003 flag-= Delete(TableName,FieldName,strs[i]);
1004 }
1005 conn.commit();
1006 } catch (Exception e) {
1007 try {
1008 conn.rollback();
1009 } catch (SQLException ex) {
1010 ex.printStackTrace();
1011 }
1012 e.printStackTrace();
1013 }
1014 if (flag==0)
1015 return 1;
1016 else
1017 return 0;
1018
1019 }
1020
1021 /*
1022 功能描述：根据表的主键id删除记录
1023 参数：TableName为要删除记录的表名；tids为一个或多个主键id字符串，以逗号（,）隔开；AutoCommit为是否使用事务
1024 author：曾令启
1025 */
1026 public int Delete(String TableName, String WhereSQL,boolean AutoCommit) {
1027 int flag = 0;
1028 try {
1029 String sql = "";
1030 conn.setAutoCommit(AutoCommit);
1031 sql = " delete from " + TableName + " where 1=1 " + WhereSQL + " ";
1032 flag = executeSql(sql);
1033 conn.commit();
1034 } catch (Exception e) {
1035 try {
1036 conn.rollback();
1037 } catch (SQLException ex) {
1038 ex.printStackTrace();
1039 }
1040 e.printStackTrace();
1041 }
1042 return flag;
1043 }
1044
1045 /**
1046 * 获得用于数据交换的主键ID，使用的存储过程为生成通用主键ID的PR_MakeMax_TYPK
1047 * @param @Bureau_id String 用户登录时选择的机构ID
1048 * @param @lab_Id String 用户登录时选择的实验室ID
1049 * @param @TableName String 表名
1050 * @param @IDNewFormatOut String 输出的ID
1051 * @return String 可以返回以下格式的ID："Bureau_id+lab_id+0000000001"
1052 */
1053 public String getExchangePKID(String Bureau_id,String lab_Id,String TableName) {
1054 String sql ="set nocount on begin declare @NewID varchar(50)"
1055 +" set @NewID=NULL"
1056 +" exec PR_MakeMax_TYPK N'', N'"+Bureau_id+"', N'"+lab_Id+"',N'"+TableName+"',@NewID out "
1057 +" select @NewID end ";
1058 String result ="";
1059 try{
1060 prepar = conn.prepareStatement(sql);
1061 rs = prepar.executeQuery();
1062 if (rs.next()) {
1063 result = rs.getString(1);
1064 }
1065 }
1066 catch(SQLException e){
1067 e.printStackTrace();
1068 }
1069 return result;
1070 }
1071
1072 public String getExchangePKID(AdminBean ab,String TableName) {
1073 String result ="";
1074 result=getExchangePKID(ab.getLoginBureaus(),ab.getLoginLab(),TableName);
1075 return result;
1076 }
1077
1078
1079
1080
1081
1082
1083
1084
1085
1086
1087
1088
1089
1090 //====================私有方法 start=============================================================================
1091
1092 /*
1093 功能描述：根据表的主键id删除记录
1094 参数：TableName为要删除记录的表名；FieldName为数据表字段名；FieldValue为数据库表字段值
1095 返回值：int。成功返回1，失败返回0
1096 */
1097 private int Delete(String TableName, String FieldName, String FieldValue) {
1098 int flag = 0;
1099 String where = " and " + FieldName + " ='" + FieldValue + "'";
1100 flag = Delete(TableName, where);
1101 return flag;
1102 }
1103
1104
1105 //用于批量删除
1106 private int Delete(String TableName, String WhereSQL) {
1107 int flag = 0;
1108 try {
1109 String sql = "";
1110 sql = " delete from " + TableName + " where 1=1 " + WhereSQL + " ";
1111 flag = executeSql(sql);
1112
1113 } catch (Exception e) {
1114 e.printStackTrace();
1115 }
1116 return flag;
1117 }
1118
1119 //====================私有方法 end=============================================================================
1120
1121 public String getListToJason(String sql) throws Exception {
1122 StringBuffer jasonStr= new StringBuffer();
1123 stmt = conn.createStatement();
1124 rs = stmt.executeQuery(sql);
1125 ResultSetMetaData rsmd = rs.getMetaData();
1126 RowSetDynaClass rsdc = new RowSetDynaClass(rs);
1127 List list = rsdc.getRows();
1128 Iterator ite = list.iterator();
1129 int resultCout = list.size();
1130 jasonStr.append("{totalCount:"+String.valueOf(resultCout)).append(",data:[");
1131 while(ite.hasNext()){
1132 DynaBean dyna = (DynaBean)ite.next();
1133 jasonStr.append("{");
1134 for(int i=1;i<=rsmd.getColumnCount();i++){
1135 if (i==rsmd.getColumnCount())
1136 jasonStr.append( rsmd.getColumnName(i)).append(":/"").append(BeanUtils.getSimpleProperty(dyna,rsmd.getColumnName(i).toLowerCase())).append("/"");
1137 else
1138 jasonStr.append( rsmd.getColumnName(i)).append(":/"").append(BeanUtils.getSimpleProperty(dyna,rsmd.getColumnName(i).toLowerCase())).append("/",");
1139 }
1140 jasonStr.append("},");
1141 }
1142 String te = jasonStr.toString();
1143 te = te.substring(0,te.length()-1)+"],success:true}";
1144 return te;
1145 }
1146
1147 public String getListToJason(String sql,int start,int limit) throws Exception {
1148 StringBuffer jasonStr= new StringBuffer();
1149 stmt = conn.createStatement();
1150 rs = stmt.executeQuery(sql);
1151 ResultSetMetaData rsmd = rs.getMetaData();
1152 RowSetDynaClass rsdc = new RowSetDynaClass(rs);
1153 List list = rsdc.getRows();
1154 int resultCout = list.size();
1155 list = list.subList(start,start+limit);
1156 Iterator ite = list.iterator();
1157 jasonStr.append("{totalCount:"+String.valueOf(resultCout)).append(",data:[");
1158 while(ite.hasNext()){
1159 DynaBean dyna = (DynaBean)ite.next();
1160 jasonStr.append("{");
1161 for(int i=1;i<=rsmd.getColumnCount();i++){
1162 if (i==rsmd.getColumnCount())
1163 jasonStr.append( rsmd.getColumnName(i)).append(":/"").append(BeanUtils.getSimpleProperty(dyna,rsmd.getColumnName(i).toLowerCase())).append("/"");
1164 else
1165 jasonStr.append( rsmd.getColumnName(i)).append(":/"").append(BeanUtils.getSimpleProperty(dyna,rsmd.getColumnName(i).toLowerCase())).append("/",");
1166 }
1167 jasonStr.append("},");
1168 }
1169 String te = jasonStr.toString();
1170 te = te.substring(0,te.length()-1)+"],success:true}";
1171 return te;
1172 }
1173
1174 }
1175
1176 -----------------------------------------------------------------------------------------------------------------------------
1177
1178 connect.properties
1179
1180 drivers=net.sourceforge.jtds.jdbc.Driver
1181 #drivers=com.microsoft.jdbc.sqlserver.SQLServerDriver
1182 logfile=D://DBConnectPool-logfile.txt
1183
1184 poolName.maxconn=100
1185 poolName.url=jdbc:jtds:sqlserver://localhost:1433;DatabaseName=my_DB;tds=8.0;lastupdatecount=true
1186 #poolName.url=jdbc:jtds:sqlserver://localhost:1433;DatabaseName=my_db;tds=8.0;lastupdatecount=true
1187 poolName.user=sa
1188 poolName.password=
