原dbconfig.txt源码如下:
Pool=false
DataSource=java:comp/env/carddata
ConnURL=jdbc:microsoft:sqlserver://10.0.0.222:1433;User=sa;DatabaseName=cardtest;
DriverName=com.microsoft.jdbc.sqlserver.SQLServerDriver
原DataTable.java源码如下,但是为了调用方便,我加了get方法.:
package pub;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
public class DataTable{
private java.util.ArrayList fDataList;
private java.util.ArrayList fColumnList;
private int fColumnCount;
private int fRowCount;
public int getColumnCount(){
return fColumnCount;
}
public int getRowCount(){
return fRowCount;
}
public DataTable(){
fDataList=new java.util.ArrayList();
fColumnList=new java.util.ArrayList();
}
public void setData(ResultSet rs){
try{
fDataList.clear();
fColumnList.clear();
ResultSetMetaData irsm=rs.getMetaData();
fColumnCount=irsm.getColumnCount();
for (int i=0;i<fColumnCount;i++){
fColumnList.add(irsm.getColumnName(i+1).toLowerCase());
}
//rs.beforeFirst();
while (rs.next()){
DataRow dr=new DataRow();
for (int i=0;i<fColumnCount;i++){
String x=rs.getString(i+1);
if (x==null) x="";
if (x.equals("null")) x="";
dr.addItem(x);
}
fDataList.add(dr);
}
fRowCount=fDataList.size();
}catch (Exception e){
}
}
public void close(){
fDataList.clear();
fColumnList.clear();
}
public String getItemForName(int row,String colName){
try{
if (row>-1 && row<fRowCount){
DataRow dr=(DataRow)fDataList.get(row);
int colIndex=fColumnList.indexOf(colName.toLowerCase());
if (colIndex>-1){
return dr.getItem(colIndex);
}
else{
return "";
}
}
else{
return "";
}
}
catch(Exception e){
return "";
}
}
public String getItemForIndex(int row,int colIndex){
try{
if (row>-1 && row<fRowCount){
DataRow dr=(DataRow)fDataList.get(row);
return dr.getItem(colIndex);
}
else{
return "";
}
}
catch(Exception e){
return "";
}
}
}
原数据库连接Conn.java源码如下:
package pub;
import java.sql.*;
import java.io.*;
import java.util.*;
import javax.naming.InitialContext;
import javax.naming.Context;
import javax.sql.DataSource;
public class Conn{
private boolean isPool=true;
private String strDriverName="";
private String strConnURL="";
private String strDataSource="";
private String fErrorMsg;
public String getLastError()
{
return fErrorMsg;
}
public String test(){
DataSource ds = null;
Connection conn = null;
try{
Context initCtx = new InitialContext();
ds = (DataSource)initCtx.lookup(strDataSource);
conn = ds.getConnection();
if(conn!=null){
Statement stmt = conn.createStatement();
ResultSet rst = stmt.executeQuery("select * from sysobjects");
while(rst.next()) {
return rst.getString(1)+strDataSource+""+isPool;
}
return "1";
}else
return "Connection Failed!";
}catch(Exception e){
return e.getMessage();
}
}
public Conn(){
try{
Properties prop = new Properties();
InputStream is = getClass().getResourceAsStream("dbconfig.txt");
prop.load(is);
String ispool=prop.getProperty("Pool");
if (ispool.trim().equals("true")){
isPool=true;
}else{
isPool=false;
}
strDataSource=prop.getProperty("DataSource");
strDriverName=prop.getProperty("DriverName");
strConnURL=prop.getProperty("ConnURL");
}catch(Exception e){
}
}
private Connection getConn(){
try{
if (isPool){
Context initCtx = new InitialContext();
DataSource ds = (DataSource)initCtx.lookup(strDataSource);
return ds.getConnection();
}else{
Class.forName(strDriverName);
return DriverManager.getConnection(strConnURL);
}
}catch(Exception e){
return null;
}
}
// private String getDriverName()
// {
// String istrDriverName="com.microsoft.jdbc.sqlserver.SQLServerDriver";
// try{
// Properties prop = new Properties();
// InputStream is = getClass().getResourceAsStream("dbconfig.txt");
// prop.load(is);
// return prop.getProperty("DriverName");
// }catch(Exception e){
// return istrDriverName;
// }
// //return istrDriverName;
// }
// private String getConnURL(){
// String istrConnURL ="";//"jdbc:microsoft:sqlserver://localhost:1433;User=sa;DatabaseName=card";
// try{
// Properties prop = new Properties();
// InputStream is = getClass().getResourceAsStream("dbconfig.txt");
// prop.load(is);
// return prop.getProperty("ConnURL");
// }catch(Exception e){
// return istrConnURL;
// }
// }
/*
* *通过SQL,在SQL中包含2个字段，第一个是没有处理的号码，第二个是号码中流水部分，处理连续号段的通用方法
*/
public String getCodeNo(String Sql){
try{
DataTable dt=new DataTable();
if (querySql(Sql,dt)){
if (dt.getRowCount()>1){
String sResult="";
int i=0;
while (i<dt.getRowCount()){
String stemp1=dt.getItemForIndex(i,1);
int itemp1=Integer.parseInt(stemp1);
sResult+=dt.getItemForIndex(i,0);
i=findCodeNo(i,itemp1,dt);
if (i>-1){
sResult+="-"+dt.getItemForIndex(i,0)+";";
i++;
}else{
return "";
}
}
if (sResult.length()>0){
sResult=sResult.substring(0,sResult.length()-1);
}
return sResult;
}else{
if (dt.getRowCount()>0){
String temp=dt.getItemForIndex(0,0);
return temp+"-"+temp;
}else{return "";}
}
}else{
return "";
}
}catch(Exception e){
return "";
}
}
private int findCodeNo(int row,int value,DataTable dt){
try{
if (row<(dt.getRowCount()-1)){
String stemp1=dt.getItemForIndex(row+1,1);
int itemp1=Integer.parseInt(stemp1);
if ((itemp1-1)==value){
return findCodeNo(row+1,itemp1,dt);
}else{
return row;
}
}else{
return row;
}
}catch(Exception e){
return -1;
}
}
/*
* 将号段SQL帮定到LIST上
*/
public String bindCodNoList(String ObjName,String Sql){
try{
String mSql=getCodeNo(Sql);
String mScript="";
String[] array=mSql.split(";");
for (int i=0;i<array.length;i++){
mScript+="<script> \n";
mScript+= "aa=document.createElement('OPTION'); \n";
mScript+= "aa.text='"+array[i]+"'; \n";
mScript+= "aa.value='"+array[i]+"'; \n";
mScript+= ObjName+".add(aa,"+i+"); </script>\n";
}
return mScript;
}catch(Exception e){
return "";
}
}
public String bindDropList(String ObjName,String Sql,String Text,String Data)
{
try
{
String mScript="";
DataTable dt=new DataTable();
if (querySql(Sql,dt))
{
for (int i=0;i<dt.getRowCount();i++){
mScript+="<script> \n";
mScript+= "aa=document.createElement('OPTION'); \n";
mScript+= "aa.text='"+dt.getItemForName(i,Text)+"'; \n";
mScript+= "aa.value='"+dt.getItemForName(i,Data)+"'; \n";
mScript+= ObjName+".add(aa,"+i+"); </script>\n";
}
}
return mScript;
}
catch(Exception e)
{
return "";
}
}
public String bindDropListSel(String ObjName,String Sql,String Text,String Data,String value)
{
try
{
String mScript="";
mScript+="<script> \n";
mScript+= "aa=document.createElement('OPTION'); \n";
mScript+= "aa.text='请选择'; \n";
mScript+= "aa.value=''; \n";
mScript+= ObjName+".add(aa,0); </script>\n";
DataTable dt=new DataTable();
if (querySql(Sql,dt))
{
for (int i=0;i<dt.getRowCount();i++){
mScript+="<script> \n";
mScript+= "aa=document.createElement('OPTION'); \n";
mScript+= "aa.text='"+dt.getItemForName(i,Text)+"'; \n";
mScript+= "aa.value='"+dt.getItemForName(i,Data)+"'; \n";
mScript+= ObjName+".add(aa,"+(i+1)+"); </script>\n";
}
}
mScript+="<script> \n";
mScript+=ObjName+".value='"+value+"' \n";
mScript+="</script> \n";
return mScript;
}
catch(Exception e)
{
return "";
}
}
public String bindDropListSel(String ObjName,String Sql,String Text,String Data)
{
try
{
String mScript="";
mScript+="<script> \n";
mScript+= "aa=document.createElement('OPTION'); \n";
mScript+= "aa.text='请选择'; \n";
mScript+= "aa.value=''; \n";
mScript+= ObjName+".add(aa,0); </script>\n";
DataTable dt=new DataTable();
if (querySql(Sql,dt))
{
for (int i=0;i<dt.getRowCount();i++){
mScript+="<script> \n";
mScript+= "aa=document.createElement('OPTION'); \n";
mScript+= "aa.text='"+dt.getItemForName(i,Text)+"'; \n";
mScript+= "aa.value='"+dt.getItemForName(i,Data)+"'; \n";
mScript+= ObjName+".add(aa,"+(i+1)+"); </script>\n";
}
}
return mScript;
}
catch(Exception e)
{
return "";
}
}
public String lookupfirst(String sql,String colname){
try{
DataTable dt=new DataTable();
querySql(sql,dt);
if (dt.getRowCount()>0){
return dt.getItemForName(0,colname);
}else{
return "";
}
}catch(Exception e){
return "";
}
}
public boolean isExistRow(String strSql){
try{
// String istrDriverName =getDriverName();
// String istrConnURL =getConnURL();
Connection iconn=getConn();
try{
if (iconn!=null){
// Class.forName(istrDriverName);
// iconn= DriverManager.getConnection(istrConnURL);
Statement istmt = iconn.createStatement();
java.sql.ResultSet rs=istmt.executeQuery(strSql);
if (rs.next()){
return true;
}else{
return false;
}
}else{
return false;
}
}catch (Exception e){
fErrorMsg=e.getMessage();
return false;
}finally{
if ((iconn!=null) && (!iconn.isClosed())){
iconn.close();
}
}
}catch (Exception e){
return false;
}
}
public boolean querySql(String strSql,DataTable dt)
{
try{
// String istrDriverName =getDriverName();
// String istrConnURL =getConnURL();
Connection iconn=getConn();
try{
if (iconn!=null){
// Class.forName(istrDriverName);
// iconn= DriverManager.getConnection(istrConnURL);
Statement istmt = iconn.createStatement();
java.sql.ResultSet rs=istmt.executeQuery(strSql);
dt.setData(rs);
return true;
}else{
return false;
}
}catch (Exception e){
fErrorMsg=e.getMessage();
return false;
}finally{
if ((iconn!=null) && (!iconn.isClosed())){
iconn.close();
}
}
}catch (Exception e){
return false;
}
}
public boolean execUpdateSql(String strSql)
{
try
{
// String istrDriverName =getDriverName();
// String istrConnURL =getConnURL();
Connection iconn=getConn();
try{
if (iconn!=null){
// Class.forName(istrDriverName);
// iconn= DriverManager.getConnection(istrConnURL);
Statement istmt = iconn.createStatement();
if ( istmt.executeUpdate(strSql)>0){
return true;
}else{
return false;
}
}else{
return false;
}
}catch (Exception e){
fErrorMsg=e.getMessage();
return false;
}finally{
if ((iconn!=null) && (!iconn.isClosed())){
iconn.close();
}
}
}catch (Exception e){
return false;
}
}
public int execUpdateSqltoRow(String strSql){
try{
// String istrDriverName =getDriverName();
// String istrConnURL =getConnURL();
Connection iconn=getConn();
try{
if (iconn!=null){
// Class.forName(istrDriverName);
// iconn= DriverManager.getConnection(istrConnURL);
Statement istmt = iconn.createStatement();
return istmt.executeUpdate(strSql);
}else{
return 0;
}
}catch (Exception e){
fErrorMsg=e.getMessage();
return 0;
}finally{
if ((iconn!=null) && (!iconn.isClosed())){
iconn.close();
}
}
}catch (Exception e){
return 0;
}
}
public String getMaxCode(String table){
try{
// String istrDriverName =getDriverName();
// String istrConnURL =getConnURL();
Connection iconn=getConn();
try{
if (iconn!=null){
// Class.forName(istrDriverName);
// iconn= DriverManager.getConnection(istrConnURL);
CallableStatement cstmt=iconn.prepareCall("{call Pro_getTableKey(?,?,?)}");
cstmt.setString(1,table);
cstmt.setInt(2,1);
cstmt.registerOutParameter(3,Types.VARCHAR,40);
cstmt.executeUpdate();
String result=cstmt.getString(3);
return result;
}else{
return "";
}
}catch (Exception e){
return e.getMessage();
}finally{
if ((iconn!=null) && (!iconn.isClosed())){
iconn.close();
}
}
}catch(SQLException e){
return e.getMessage();
}
}
public String getText(String strSql){
try{
// String istrDriverName =getDriverName();
// String istrConnURL =getConnURL();
Connection iconn=getConn();
try{
if (iconn!=null){
// Class.forName(istrDriverName);
// iconn= DriverManager.getConnection(istrConnURL);
Statement istmt = iconn.createStatement();
java.sql.ResultSet rs=istmt.executeQuery(strSql);
if (rs.next()){
return rs.getString(1);
// InputStream ais=rs.getAsciiStream(1);
// try{
// int c;
// String _strGetText ="";
// char temp;
// StringBuffer sb=new StringBuffer();
// while((c= ais.read())>-1){
// temp=(char)c;
// sb.append(temp);
// }
// return sb.toString();
// }catch(Exception e){
// return "";
// }
}else{
return "";
}
}else{
return "";
}
}catch(Exception e){
return "";
}finally{
if ((iconn!=null) && (!iconn.isClosed())){
iconn.close();
}
}
}catch(Exception e){
return "";
}
}
public String updateText(String strSql,String data){
try{
// String istrDriverName =getDriverName();
// String istrConnURL =getConnURL();
Connection iconn=getConn();
try{
if (iconn!=null){
// Class.forName(istrDriverName);
// iconn= DriverManager.getConnection(istrConnURL);
PreparedStatement pstmt = iconn.prepareStatement(strSql); ;
//pstmt.setBytes(1,data.getBytes("ISO-8859-1"));
pstmt.setString(1,data);
pstmt.execute();
return "";
}else{
return "1";
}
}catch(Exception e){
return e.getMessage();
}finally{
if ((iconn!=null) && (!iconn.isClosed())){
iconn.close();
}
}
}catch(Exception e){
return e.getMessage();
}
}
public boolean execSql(String strSql){
try{
// String istrDriverName =getDriverName();
// String istrConnURL =getConnURL();
Connection iconn=getConn();
try{
if (iconn!=null){
// Class.forName(istrDriverName);
// iconn= DriverManager.getConnection(istrConnURL);
Statement istmt = iconn.createStatement();
return istmt.execute(strSql);
}else{
return false;
}
}catch (Exception e){
return false;
}finally{
if ((iconn!=null) && (!iconn.isClosed()))
{
iconn.close();
}
}
}catch (Exception e){
return false;
}
}
public boolean isPool() {
return isPool;
}
public String getStrConnURL() {
return strConnURL;
}
public String getStrDataSource() {
return strDataSource;
}
public String getStrDriverName() {
return strDriverName;
}
}
对应的jsp页面如下:
<%@ page contentType="text/html; charset=gbk" language="java" import="java.sql.*" errorPage="" %>
<%@ page import="pub.*" %>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<title>测试连接</title>
<META http-equiv=Content-Type content="text/html; charset=gb2312">
</head>
<%
String mScript="";
boolean isPool=true;
//String testDataSource="";
//String testConnURL="";
//String testDriverName="";
try{
Conn conn=new Conn();
String cardno;
String username,password,founderr,sql,id,verifycode;
//cardno=request.getParameter("CardNo");
//username=request.getParameter("UserName");
//password=request.getParameter("Password");
//verifycode=request.getParameter("verifycode");
if (conn.isPool()){
isPool=true;
out.println("<br>连接池:使用连接池");
}else{
isPool=false;
out.println("<br>连接池:没有使用连接池");
}
out.println("<br>DataSource:");
out.println(conn.getStrDataSource());
out.println("<br>服务器和数据库名:");
out.println(conn.getStrConnURL());
out.println("<br>驱动程序名:");
out.println(conn.getStrDriverName());
sql="select * from card03_cardinfo ";
//-------test---------
System.out.println(sql);
out.println("<br>SQL:");
out.println(sql);
DataTable dt=new DataTable();
conn.querySql(sql,dt);
if (dt.getRowCount()>0){
out.println("<br>第一列数据如下:");
//out.println(<br>);
out.println(dt.getItemForName(0,"Cardno"));
//out.println(<br>);
out.println("<br>第二列数据如下:");
out.println(dt.getItemForName(1,"Account"));
}
out.println("<br>测试连接成功");
}catch(Exception e){
//-------test---------
System.out.println("system error");
mScript="<script>alert('系统出错!');</script>";
out.println("<br>测试连接失败");
}
%>
<%=mScript %>
<body>
<br>以上为测试数据库连接结果. <br>
</body>
</html>
2006－7－ 19
