我在大三的时候写过一个javabean，可以实现连接数据库，对表的操作，以及对其中的字段值经过字符串运算后提取新的SQL条件，我现在拿出来与大家共享。
dbBean.java//连接数据库SQL Server2000
package pub;
import java.sql.*;
import java.io.*;
import java.util.*;
public class dbBean {
public Connection conn = null;
public Statement stmt = null;
public ResultSet rs = null;
public dbBean() {
try {
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
}
catch (ClassNotFoundException e) {
System.err.println("OpenDB():"+e.getMessage());
}
}
public ResultSet executeQuery(String sql) {
try {
conn=DriverManager.getConnection("jdbc:odbc:KMHXBY","sa","");
stmt=conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);
rs = stmt.executeQuery(sql);//execute sql
}
catch (java.sql.SQLException e) {
System.err.println("OpenDB.executeQuery():"+e.getMessage());
}
return rs;
}
public int executeUpdate(String sql) {
int rowscount=0;
try {
conn=DriverManager.getConnection("jdbc:odbc:foxv21","sa","");
stmt=conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);
rowscount = stmt.executeUpdate(sql);//execute sql
}
catch (java.sql.SQLException e) {
System.err.println("OpenDB.executeUpdate():"+e.getMessage());
}
return rowscount;
}
public void destroy() {
try {
stmt.close();
conn.close();
}
catch(Exception e) {
System.err.println("DBOperate.destroy():"+e.getMessage());
}
}
}
GetSQL.java
//操作foa_WebPurview表，它有两个字段，一个f_UserName，一个f_SearchPurview,
这个标注要是纪录用户查询的条件，然后我要加上必需的条件后，形成真正的SQL语句，这就是一个字符串操作。
package StringOP;
import java.lang.*;
import java.util.*;
import java.sql.*;
import pub.*;
public class GetSQL{
public String rsAr; file://档案文件必须要满足的条件
public String rsFile; file://普通文件必须要满足的条件
public String rsSql = "";//读取得SQL语句
public String rsAddAr = ""; file://档案附加的条件
public String rsAddFile = ""; file://普通附加的条件
public ResultSet dbRs;
public GetSQL(String s){
int i = 0; file://String Index
int IsOver = 0; file://Is String Over?
String sql = " ";
file://连接数据库
dbBean dbConn = new dbBean();
file://初始化rsAr,rsFile
rsFile = "SELECT * FROM foa_WJ WHERE (((f_Status <> '已移交') AND (f_Status <> '已归档')) OR (f_Status IS NULL)) AND ((F_MarkDel<>1) OR (f_MarkDel IS NULL)) Order By f_JNSXH";
rsAr = "SELECT * FROM foa_WJ WHERE (f_Status = '已归档') AND ((fcSys_MID > 0) AND (NOT fcSys_MID IS NULL)) AND ((F_MarkDel<>1) OR (f_MarkDel IS NULL)) ORDER BY f_JNSXH";
sql = "select * from foa_WebPurView where f_UserName = '"+s+"' ";
file://从数据库中读取用户条件，初始化rsSql
try {
dbRs = dbConn.executeQuery(sql); file://这里只能有一条纪录
if (dbRs!=null && dbRs.next())
rsSql = dbRs.getString("f_SearchPurview");
} catch(Exception e) {
e.printStackTrace();
}
file://分离rsAddAr和rsAddFile
if (rsSql == ""){
System.out.println("用户自定义条件为空或是没有满足条件的纪录！");
}
else{
while ((IsOver == 0)){
if ((i <= rsSql.length()-5)&&(rsSql.charAt(i) == 'A')){
file://当前为档案
i = i + 1;
while((i <= rsSql.length()-5)&&(rsSql.charAt(i) !='~')){
while((i <= rsSql.length()-5)&&(rsSql.charAt(i) != ':')){
++i;
}
if((i <= rsSql.length()-5)&&(rsSql.charAt(i) == ':')){
i = i + 1;
while((i <= rsSql.length()-1)&&(rsSql.charAt(i) !='~')){
rsAddAr = rsAddAr + rsSql.charAt(i);
++i;
}
}
}
if(rsSql.charAt(i) == '~'){
i = i + 4;
if(i == rsSql.length()){
IsOver = 1;
continue;
}
else{
++i;
continue;
}
}
file://System.out.println(rsSql.charAt(i));
}
else if ((i <= rsSql.length()-5)&&(rsSql.charAt(i) == 'F')){
file://当前为文件
i = i + 1;
while((i <= rsSql.length()-5)&&(rsSql.charAt(i) !='~')){
while((i <= rsSql.length()-5)&&(rsSql.charAt(i) != ':')){
++i;
}
if((i <= rsSql.length()-5)&&(rsSql.charAt(i) == ':')){
i = i + 1;
while((i <= rsSql.length()-1)&&(rsSql.charAt(i) !='~')){
rsAddFile = rsAddFile + rsSql.charAt(i);
++i;
}
}
}
if(rsSql.charAt(i) == '~'){
i = i + 4;
if(i == rsSql.length()){
IsOver = 1;
continue;
}
else{
++i;
continue;
}
}
}
rsAddAr = rsAddAr + ";" + "!";
rsAddFile = rsAddFile +";" +"!";
break;
}
}
}
public String GetArchiveSQL(){
file://得到档案字符串,对rsAr和rsAddAr的操作
String tempAr = "";
String tempAdd = "";
int IndexAr = 0; file://rsAr上的位置
int INdexAdd = 0 ; file://rsAddAr上的位置
int IsFirst = 0;
file://找到rsAr的条件的地方
while((rsAr.charAt(IndexAr) != '(')&&(IndexAr <= rsAr.length()-1)){
tempAr = tempAr + rsAr.charAt(IndexAr);
IndexAr++;
} file://IndexAr现在指在了第一个(的地方
tempAr = tempAr + " ";
while((INdexAdd <= rsAddAr.length()-1)&&(rsAddAr.charAt(INdexAdd) != '!')){
while((INdexAdd <= rsAddAr.length()-1)&&(rsAddAr.charAt(INdexAdd) != ';')){
tempAdd = tempAdd + rsAddAr.charAt(INdexAdd);
++INdexAdd;
file://System.out.println(tempAdd);
} file://分离出了用户的一个条件
if((INdexAdd <= rsAddAr.length()-1)&&(rsAddAr.charAt(INdexAdd) == ';')){
INdexAdd = INdexAdd + 1;
if(IsFirst == 0){
tempAr = tempAr + tempAdd + " "+ "AND" + " ";
IsFirst = 1;
}
else{
tempAr = tempAr + tempAdd + " "+"AND" +" ";
}
tempAdd = "";
}
}//用户的条件都结束了
file://将rsAr中的条件加上；
while((IndexAr <= rsAr.length()-1)&&(rsAr.charAt(IndexAr) != ';')){
tempAr = tempAr + rsAr.charAt(IndexAr);
++IndexAr;
}
tempAr = tempAr + ';';
file://System.out.println(tempAr);
return tempAr;
}
public String GetUserFileSQL(){
file://得到文件字符串,对rsFile和rsAddFile的操作
String tempFile = "";
String tempAdd = "";
int IndexFile = 0; file://rsAr上的位置
int INdexAdd = 0 ; file://rsAddAr上的位置
int IsFirst = 0;
file://找到rsAr的条件的地方
while((rsFile.charAt(IndexFile) != '(')&&(IndexFile <= rsFile.length()-1)){
tempFile = tempFile + rsFile.charAt(IndexFile);
IndexFile++;
} file://IndexAr现在指在了第一个(的地方
tempFile = tempFile + " ";
while((INdexAdd <= rsAddFile.length()-1)&&(rsAddFile.charAt(INdexAdd) != '!')){
while((INdexAdd <= rsAddFile.length()-1)&&(rsAddFile.charAt(INdexAdd)!= ';')){
tempAdd = tempAdd + rsAddFile.charAt(INdexAdd);
++INdexAdd;
file://System.out.println(tempAdd);
} file://分离出了用户的一个条件
if((INdexAdd <= rsAddFile.length()-1)&&(rsAddFile.charAt(INdexAdd) == ';')){
INdexAdd = INdexAdd + 1;
if(IsFirst == 0){
tempFile = tempFile + tempAdd + " "+ "AND" + " ";
IsFirst = 1;
}
else{
tempFile = tempFile + tempAdd + " "+"AND" +" ";
}
tempAdd = "";
}
}//用户的条件都结束了
file://将rsAr中的条件加上；
while((IndexFile <= rsFile.length()-1)&&(rsFile.charAt(IndexFile) != ';')){
tempFile = tempFile + rsFile.charAt(IndexFile);
++IndexFile;
}
tempFile = tempFile + ';';
file://System.out.println(tempFile);
return tempFile;
}
/*public static void main(String[] args){
String s = "";
String ss = "";
GetSQL aa = new GetSQL(s);
s= aa.GetArchiveSQL();
ss = aa.GetUserFileSQL();
System.out.println(s);
System.out.println(ss);
}*/
}
好了，全部代码，jdk下测试通过 
|
本类阅读TOP10