/**
* @return执行sql语句(delete)
*/
public void execMySQL(String inSQL) throws SQLException {
sql = inSQL;
util = new DBManager();
conn = util.getConn();
conn.setAutoCommit(false); // .setAutoCommit(false);
try {
util.getStmt().execute(sql);
conn.commit();
} catch (SQLException e) {
// TODO Auto-generated catch block
conn.rollback();
e.printStackTrace();
}
util.close();
}
/**
* @return执行语句(insert,update)
* inSQL 1为insert，2为update
* inSQL1 表名
* inSQL2 字段列表
* inSQL3 字段值列表
* inSQL4 update的条件字段列表,insert为null
* inSQL5 update的条件字段值列表,insert为null
*/
public void inOrUpExecMySQL(int inSQL,String inSQL1,String inSQL2,String inSQL3,String inSQL4,String inSQL5) throws SQLException {
int type;
String[] insql2 = inSQL2.split(",");
String[] insql3 = inSQL3.split(",");
String[] insql4 = null;
String[] insql5 = null;
if(inSQL4 != null && inSQL5 != null){
insql4 = inSQL4.split(",");
insql5 = inSQL5.split(",");
}
ExecMySql bean = new ExecMySql();
sql = bean.SpeSQL(inSQL,inSQL1,inSQL2,inSQL4);//拼sql语句
util = new DBManager();
conn = util.getConn();
conn.setAutoCommit(false);
int ii = 0;
try {
stmt = conn.prepareStatement(sql);
List colstype = bean.fieldTypes(inSQL2, inSQL1);//判断字段类型
for (int i = 0; i < insql2.length; i++) {
ii = i + 1;
type = (new Integer(colstype.get(i).toString())).intValue();
bean.setValueList(stmt, type, ii, insql3[i]);
}
if (inSQL == 2 && inSQL5 != null) {
colstype = bean.fieldTypes(inSQL4, inSQL1);
for (int j = 0; j < insql4.length; j++) {
int jj = (ii + 1) + j;
type = (new Integer(colstype.get(j).toString())).intValue();
bean.setValueList(stmt, type, jj, insql5[j]);
}
}
stmt.execute();
conn.commit();
} catch (SQLException e) {
throw e;
}
util.close();
}
/**
* @return拼sql语句
* inSQL 1为insert，2为update
* inSQL1 表名
* inSQL2 字段列表
* inSQL4 update的条件字段列表
*/
public String SpeSQL(int inSQL,String inSQL1,String inSQL2,String inSQL4){
sql ="";
String[] insql2 = inSQL2.split(",");
if (inSQL == 1) {//拼insert字段
String str = "?";
for (int i = 1; i < insql2.length; i++) {
str += ",?";
}
sql = "insert into " + inSQL1 + " (" + inSQL2 + " ) values (" + str + ")";
}
if(inSQL == 2 && inSQL4 != null){//拼update字段
String[] insql4 = inSQL4.split(",");
sql = "update " + inSQL1 + " set ";
for (int i = 0; i < insql2.length; i++) {
if (i == insql2.length - 1) {
sql = sql + insql2[i] + "=?";
} else {
sql = sql + insql2[i] + "=?,";
}
}
for (int i = 0; i < insql4.length; i++) {
if (i == 0) {
sql = sql + " where " + insql4[i] + "=?";
} else {
sql = sql + " and " + insql4[i] + "=?";
}
}
}
return sql;
}
/**
* @return判断字段类型
* str 字段列表
* TBName 表名
*/
public List fieldTypes(String str, String TBName) throws SQLException {
List colsTypes = new ArrayList();
sql = "select " + str + " from " + TBName + " where 1=2";
try{
util = new DBManager();
rs = util.getStmt().executeQuery(sql);
ResultSetMetaData rsmd = rs.getMetaData();
for(int i = 0;i < rsmd.getColumnCount(); i++){
colsTypes.add(i,""+rsmd.getColumnType(i+1));
}
}catch(SQLException e){
throw e;
}
return colsTypes;
}
/**
* @return执行语句stmt.setXXX(xx,xx)
* PreparedStatement stmt
* type 类型(常量字段值)
* ii stmt.setXXX(ii,xx)
* stmt.setXXX(xx,str)
*/
public void setValueList(
PreparedStatement stmt,
int type,
int ii,
String str)
throws SQLException {
int iValue = 0;
float flt = 0.0f;
Integer itg = new Integer(0);
BigDecimal ftg = new BigDecimal(0.0);
try {
switch (type) { //type数值参照java.sql.types
case 1 :
if (str != null && !str.equals("")) {
stmt.setString(ii, str);
} else {
stmt.setString(ii, " ");
}
break;
case 2 :
ftg = new BigDecimal(0.0);
if (str != null && !str.equals("")) {
stmt.setBigDecimal(ii, (new BigDecimal(str)));
} else {
stmt.setBigDecimal(ii, ftg);
}
break;
case 3 :
ftg = new BigDecimal(0.0);
if (str != null && !str.equals("")) {
stmt.setBigDecimal(ii, (new BigDecimal(str)));
} else {
stmt.setBigDecimal(ii, ftg);
}
break;
case 4 :
if (str != null && !str.equals("")) {
stmt.setInt(ii, (new Integer(str)).intValue());
} else {
stmt.setInt(ii, iValue);
}
break;
case 5 :
if (str != null && !str.equals("")) {
stmt.setInt(ii, (new Integer(str)).intValue());
} else {
stmt.setInt(ii, iValue);
}
break;
case 6 :
ftg = new BigDecimal(0.0);
if (str != null && !str.equals("")) {
stmt.setBigDecimal(ii, (new BigDecimal(str)));
} else {
stmt.setBigDecimal(ii, ftg);
}
break;
case 7 :
if (str != null && !str.equals("")) {
stmt.setFloat(ii, Float.valueOf(str).floatValue());
} else {
stmt.setFloat(ii, flt);
}
break;
case 8 :
ftg = new BigDecimal(0.0);
if (str != null && !str.equals("")) {
stmt.setBigDecimal(ii, (new BigDecimal(str)));
} else {
stmt.setBigDecimal(ii, ftg);
}
break;
case 12 :
if (str != null && !str.equals("")) {
stmt.setString(ii, str);
} else {
stmt.setString(ii, " ");
}
break;
case 91 :
if (str != null && !str.equals("")) {
stmt.setDate(ii, Date.valueOf(str));
} else {
stmt.setDate(ii, null);
}
break;
case 92 :
if (str != null && !str.equals("")) {
stmt.setTime(ii, Time.valueOf(str));
} else {
stmt.setTime(ii, null);
}
break;
case 93 :
if (str != null && !str.equals("")) {
stmt.setTimestamp(ii, Timestamp.valueOf(str));
} else {
stmt.setTimestamp(ii, null);
}
break;
default :
if (str != null && !str.equals("")) {
stmt.setString(ii, str);
} else {
stmt.setString(ii, " ");
}
break;
}
} catch (SQLException e) {
throw e;
}
}
/**
* @return执行sql语句(select)
*/
public MyTable execMySQLSelect2(String inSQL) {
MyFunc func = new MyFunc();
sql = inSQL;
//list = new ArrayList();
util = new DBManager();
MyTable kfse = new MyTable();
String s;
int type;
int iValue;
List rows = new ArrayList();
Integer itg = new Integer(0);
BigDecimal ftg = new BigDecimal(0.0);
java.sql.Date date;
try {
rs = util.getStmt().executeQuery(sql);
ResultSetMetaData rsmd = rs.getMetaData();//可用于获取关于 ResultSet 对象中列的类型和属性信息的对象
String[] cols = new String[rsmd.getColumnCount()];//rsmd.getColumnCount()返回此 ResultSet 对象中的列数
String[] colsType = new String[rsmd.getColumnCount()];
for (int i = cols.length; i > 0; i--) {
cols[i - 1] = rsmd.getColumnName(i);//rsmd.getColumnName(i)获取指定列的名称
colsType[i - 1] = String.valueOf(rsmd.getColumnType(i));//rsmd.getColumnType(i)检索指定列的 SQL 类型
}
if (rs != null) {
while (rs.next()) {
Object[] row = new Object[cols.length];
rows.add(row);
for (int i = cols.length; i > 0; i--) {
//row[i - 1] = rs.getString(cols[i - 1]);
type = rsmd.getColumnType(i);
//row[i - 1] = String.valueOf(type);
switch (type) {//type数值参照java.sql.types
case 1 :
row[i - 1] = rs.getString(cols[i - 1]);
break;
case 2 :
ftg = new BigDecimal(0.0);
if (rs.getBigDecimal(cols[i - 1]) != null)
ftg = rs.getBigDecimal(cols[i - 1]);
row[i - 1] = ftg;
break;
case 3 :
ftg = new BigDecimal(0.0);
if (rs.getBigDecimal(cols[i - 1]) != null)
ftg = rs.getBigDecimal(cols[i - 1]);
row[i - 1] = ftg;
break;
case 4 : //对u24212 数u25454 库u23383 段NTEGER//ok
itg = new Integer(rs.getInt(cols[i - 1]));
row[i - 1] = itg;
break;
case 5 :
itg = new Integer(rs.getInt(cols[i - 1]));
row[i - 1] = itg;
break;
case 6 :
ftg = new BigDecimal(0.0);
if (rs.getBigDecimal(cols[i - 1]) != null)
ftg = rs.getBigDecimal(cols[i - 1]);
row[i - 1] = ftg;
break;
case 7 :
row[i - 1] =
new Float(rs.getFloat(cols[i - 1]));
break;
case 8 :
ftg = new BigDecimal(0.0);
if (rs.getBigDecimal(cols[i - 1]) != null)
ftg = rs.getBigDecimal(cols[i - 1]);
row[i - 1] = ftg;
break;
case 12 : //对u24212 数u25454 库u23383 段TRING//ok
row[i - 1] =
func.CL_String(rs.getString(cols[i - 1]));
break;
case 91 : //对u24212 数u25454 库u23383 段ATE//ok
date = (java.sql.Date) rs.getDate(cols[i - 1]);
row[i - 1] = date;
break;
case 92 :
java.sql.Time time =
(java.sql.Time) rs.getTime(cols[i - 1]);
row[i - 1] = time;
break;
case 93 :
java.sql.Timestamp timestamp =
(java.sql.Timestamp) rs.getTimestamp(
cols[i - 1]);
row[i - 1] = timestamp;
break;
default :
row[i - 1] =
func.CL_String(rs.getString(cols[i - 1]));
break;
}
}
}
kfse.setCols(cols);
kfse.setColstype(colsType);
kfse.setRows(rows);
}
util.close();
} catch (SQLException e) {
e.printStackTrace();
}
return kfse;
}
扫一扫
1万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
