📄 simplesqlcontrol.java
字号:
/**
* Created on 2005-4-4
* @author 星际浪子 hunter clj
* lastest modify by clj on 11/1/05
*/
package com.jxyd.sql;
import java.io.FileInputStream;
import java.io.InputStream;
import java.lang.reflect.Method;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Enumeration;
import java.util.HashMap;
import java.util.Hashtable;
import java.util.Vector;
public class SimpleSqlControl {
private Connection conn;
private CallableStatement cst;
private Statement st;
private ResultSet rst;
Linkdb linkdb;
public SimpleSqlControl() {
try {
linkdb = new Linkdb();
linkdb.openConnection("dbConn");
this.conn = linkdb.getConnection();
} catch (Exception e) {
e.printStackTrace();
}
}
public ResultSet sqlSearch(String sqlStr) throws Exception {
if (rst != null)
rst.close();
rst = null;
if (st != null)
st.close();
st = null;
// st = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
// ResultSet.CONCUR_READ_ONLY);
st = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_UPDATABLE);
rst = st.executeQuery(sqlStr);
return rst;
}
/*
* Function: 完成ResultSet对象向ArrayList对象为集合的对象的转化 Para:sql,指定的查询Sql
* Para:className、Sql相对应得JavaBean/FormBean类的名字
* Return:以类className为一条记录的结果集，完成ResultSet对象向ArrayList对象为集//合的className对象的转化
*
*/
public ArrayList sqlSearch(String sqlStr, String className)
throws Exception {
ArrayList paraList = new ArrayList();
try {
String recordValue = null;
paraList = new ArrayList();
st = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_UPDATABLE);
rst = st.executeQuery(sqlStr);
ResultSetMetaData rsmd = rst.getMetaData();
int columnCount = rsmd.getColumnCount();
Object c1 = null;
// Object isNull[]=new Object[1];
HashMap hm = null;
hm = getMethod1(className);//保存的是这个类的全部方法名 和方法的参数的键值
Class cx[] = null;
while (rst.next()) {
c1 = Class.forName(className).newInstance();//创建类
for (int i = 1; i <= columnCount; i++) {
if (!rsmd.getColumnName(i).equals("SYSROWNUM")) // 对sysrownum不处理
{
//得到c1 BEAN类的一个列的SET方法
Method m = c1.getClass().getMethod(
getSetMethodName(rsmd.getColumnName(i)),//返回的是一个列的set方法名
//返回参数类型
(Class[]) hm.get(getSetMethodName(rsmd
.getColumnName(i))));
cx = m.getParameterTypes();//返回方法的参数类型
if (rst.getString(rsmd.getColumnName(i)) != null) {
// if ((rsmd.getColumnName(i).equals("zjh")) || (rsmd.getColumnName(i).equals("yhzh")))
// {
// recordValue ="'" +rst.getString(rsmd.getColumnName(i));
// }
// else{
recordValue = rst.getString(rsmd.getColumnName(i));
// System.out.println(rsmd.getColumnName(i)+": "+rst.getString(rsmd.getColumnName(i))+cx[0].toString());
// }
if (cx[0].toString().equals(
"class java.lang.String"))
m.invoke(c1,
new Object[] {(String) recordValue });
if (cx[0].toString().equals("int")||cx[0].toString().equals("class java.lang.Integer"))
m.invoke(c1, new Object[] { Integer
.valueOf(recordValue) });
if (cx[0].toString().equals("class java.util.Date")) {
java.text.SimpleDateFormat formatter = new java.text.SimpleDateFormat(
"yyyy-MM-dd HH:mm:ss");
java.util.Date date = formatter
.parse(recordValue);
m.invoke(c1, new Object[] { date });
}
if (cx[0].toString()
.equals("float")||cx[0].toString().equals("class java.lang.Float")) {
m.invoke(c1, new Object[] { Float
.valueOf(recordValue) });
}
if (cx[0].toString()
.equals("double")||cx[0].toString().equals("class java.lang.Double")) {
m.invoke(c1, new Object[] { Double
.valueOf(recordValue) });
}
}
// else m.invoke(c1,isNull);
}
}
// System.out.println(((ProvinceBean)c1).getPid());
paraList.add(c1);
}
} catch (Exception ex) {
throw ex;
} finally {
}
return paraList;
}
/*
* Function: 完成ResultSet对象向Object对象的转化 Para:sql,指定的查询Sql
* Para:className相对应得JavaBean/FormBean类的名字
* Return:以类className的一个对象，完成ResultSet对象向className对象的转化
*
*/
public Object sqlSearchObject(String sqlStr, String className)
throws Exception {
Object ob = null;
try {
String recordValue = null;
st = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_UPDATABLE);
rst = st.executeQuery(sqlStr);
ResultSetMetaData rsmd = rst.getMetaData();
int columnCount = rsmd.getColumnCount();
HashMap hm = null;
hm = getMethod1(className);//保存的是这个类的全部方法名 和方法的参数的键值
Class cx[] = null;
while (rst.next()) {
ob = Class.forName(className).newInstance();//创建类
for (int i = 1; i <= columnCount; i++) {
if (!rsmd.getColumnName(i).equals("SYSROWNUM")) // 对sysrownum不处理
{
//得到ob BEAN类的一个列的SET方法
Method m = ob.getClass().getMethod(
getSetMethodName(rsmd.getColumnName(i)),//返回的是一个列的set方法名
//返回参数类型
(Class[]) hm.get(getSetMethodName(rsmd
.getColumnName(i))));
cx = m.getParameterTypes();//返回方法的参数类型
if (rst.getString(rsmd.getColumnName(i)) != null) {
//
recordValue = rst.getString(rsmd.getColumnName(i));
// System.out.println(rsmd.getColumnName(i)+": "+rst.getString(rsmd.getColumnName(i))+cx[0].toString());
// }
if (cx[0].toString().equals(
"class java.lang.String"))
m.invoke(ob,
new Object[] {(String) recordValue });
if (cx[0].toString().equals("int")||cx[0].toString().equals("class java.lang.Integer"))
m.invoke(ob, new Object[] { Integer
.valueOf(recordValue) });
if (cx[0].toString().equals("class java.util.Date")) {
java.text.SimpleDateFormat formatter = new java.text.SimpleDateFormat(
"yyyy-MM-dd HH:mm:ss");
java.util.Date date = formatter
.parse(recordValue);
m.invoke(ob, new Object[] { date });
}
if (cx[0].toString()
.equals("float")||cx[0].toString().equals("class java.lang.Float")) {
m.invoke(ob, new Object[] { Float
.valueOf(recordValue) });
}
if (cx[0].toString()
.equals("double")||cx[0].toString().equals("class java.lang.Double")) {
m.invoke(ob, new Object[] { Double
.valueOf(recordValue) });
}
}
// else m.invoke(c1,isNull);
}
}
}
} catch (Exception ex) {
throw ex;
} finally {
}
return ob;
}
public ArrayList sqlSearch1(String sqlStr, String className)
throws Exception { // 分页用，对sysrownum不处理
ArrayList paraList = new ArrayList();
try {
String recordValue = null;
paraList = new ArrayList();
st = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
ResultSet.CONCUR_READ_ONLY);
rst = st.executeQuery(sqlStr);
ResultSetMetaData rsmd = rst.getMetaData();
int columnCount = rsmd.getColumnCount();
Object c1 = null;
// Object isNull[]=new Object[1];
HashMap hm = null;
hm = getMethod1(className);
Class cx[] = null;
while (rst.next()) {
c1 = Class.forName(className).newInstance();
for (int i = 2; i <= columnCount; i++) {
// if (!rsmd.getColumnName(i).equals("SYSROWNUM")) {
// //对sysrownum不处理
Method m = c1.getClass().getMethod(
getSetMethodName(rsmd.getColumnName(i)),
(Class[]) hm.get(getSetMethodName(rsmd
.getColumnName(i))));
cx = m.getParameterTypes();
if (rst.getString(rsmd.getColumnName(i)) != null) {
recordValue = rst.getString(rsmd.getColumnName(i));
if (cx[0].toString().equals("class java.lang.String"))
{
m.invoke(c1, new Object[] { (String) recordValue });
}
if (cx[0].toString().equals("int"))
m.invoke(c1, new Object[] { Integer
.valueOf(recordValue) });
if (cx[0].toString().equals("class java.util.Date")) {
java.text.SimpleDateFormat formatter = new java.text.SimpleDateFormat(
"yyyy-MM-dd HH:mm:ss");
java.util.Date date = formatter.parse(recordValue);
m.invoke(c1, new Object[] { date });
}
}
// else m.invoke(c1,isNull);
// }
}
paraList.add(c1);
}
} catch (Exception ex) {
throw ex;
} finally {
}
return paraList;
}
private String getSetMethodName(String columnName) {
String ret = "";
ret = "set" + columnName.substring(0, 1).toUpperCase()//截取第一个字符转换成大写
+ columnName.substring(1, columnName.length());
return ret; //返回一个SET方法
}
private HashMap getMethod1(String className) throws Exception {
HashMap hm = new HashMap();
Class c = Class.forName(className);
Method mm[] = c.getDeclaredMethods(); // 类的所有的方法
String methodName = "";
// Class cx[] = null;
for (int j = 0; j < mm.length; j++) {
methodName = mm[j].getName();
hm.put(methodName, mm[j].getParameterTypes());
}
return hm;
}
/**
* 在数据库中删除信息
*/
public boolean sqlDelete(String tablename, String searchfile)
throws Exception {
if (rst != null)
rst.close();
rst = null;
if (st != null)
st.close();
st = null;
int succFlag = 0;
conn.setAutoCommit(false);
try {
st = conn.createStatement();
succFlag = st.executeUpdate("delete from " + tablename + " where "
+ searchfile);
conn.commit();
conn.setAutoCommit(true);
} catch (Exception e) {
conn.rollback();
conn.setAutoCommit(true);
throw new SQLException(e.getMessage().substring(
e.getMessage().lastIndexOf("]") + 1));
}
if (st != null)
st.close();
st = null;
return succFlag > 0;
}
/*
* 执行单个无参数存储过程！ created by clj
*/
public void sqlExecProc(String pro_name) throws SQLException {
// int debugFlag = myrequest.getDebugProgramInfo();
try {
cst = conn.prepareCall("{call " + pro_name + "}",
ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_READ_ONLY);
// cst.setString(1,"1");
// cst.registerOutParameter(1, oracle.jdbc.OracleTypes.CURSOR);
cst.execute();
conn.commit();
/*
* if (debugFlag == 1) { System.out.println("执行成功！Procedure output
* is :" + pro_name); }
*/
} catch (Exception e) {
throw new SQLException(e.getMessage());
}
}
public void freeConnection() {
try {
if (rst != null)
rst.close();
if (st != null)
st.close();
if (cst != null)
cst.close();
rst = null;
st = null;
cst = null;
linkdb.releaseConnection();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -