增删改

package com.commom;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.Map;
import java.util.TreeMap;

import com.commom.ReadXml;

public class BaseDao {

public Connection conn =null;//连接变量
public ResultSet rs=null;//数据集
public PreparedStatement pstmt=null;//预处理

//打开连接
public void openConnection(){
if(conn==null){
try{
Class.forName("com.mysql.jdbc.Driver");
conn=DriverManager.getConnection(new ReadXml().Constring());
System.out.println("连接成功");
}
catch(SQLException ex){
System.out.println(ex.getMessage()+"路径错误");
}
catch(ClassNotFoundException ex){
System.out.println(ex.getMessage()+"驱动错误");
}
finally{
}
}
}
//关闭连接释放资源
public void dispose(){
try {
if(rs!=null)rs.close();
if(pstmt!=null)pstmt.close();
if(conn!=null)conn.close();
System.out.println("关闭成功");
}
catch (SQLException e) {
e.printStackTrace();
}
}

/*
* 添加
*/
public int Insert(String tablename,Map map) throws Exception{
String[] s=this.getInsert(map);
String sqlstr="Insert into "+tablename+" ("+s[0]+")values("+s[1]+")";
System.out.println(sqlstr);
return ExecuteNonQuery(sqlstr);
}
/*
* 修改
*/
public void Update(String tablename,Map map,String where) throws Exception{
String sqlstr="UPDATE "+tablename+" set "+getUpdate(map)+" WHERE "+where;
this.ExecuteNonQuery(sqlstr);
}
/*
* 删除
*/
public boolean Delete(String tablename,String where) throws Exception{
String sqlstr="DELETE FROM "+tablename+" WHERE "+where;
return ExecuteNonQuery(sqlstr)>0? true:false;
}

/*
* Update语句
*/
private String getUpdate(Map map){
int i=map.size();
String s="";
if(i>0){
Iterator iter=map.keySet().iterator();
while(iter.hasNext()){
Object key=iter.next();
if(i>1){
s+=key.toString()+"="+this.getvalue(map.get(key))+",";
i--;
}else{
s+=key.toString()+"="+this.getvalue(map.get(key));
}
}
}
return s;
}

/*
* 添加语句
*/
private String[] getInsert(Map map){
int i=map.size();
String s1="";
String s2="";
if(i>0){
Iterator iter=map.keySet().iterator();
while(iter.hasNext()){
Object key=iter.next();
if(i>1){
s1+=key.toString()+",";
s2+=this.getvalue(map.get(key))+",";
i--;
}else{
s1+=key.toString();
s2+=this.getvalue(map.get(key));
}
}
}
String[] s=new String[]{s1,s2};
return s;
}

/*
* 传入object返回所需string，判断类型形成语句
*/
private String getvalue(Object o){
//System.out.println(o);
if (o instanceof Integer) {
return o.toString();
} else if (o instanceof String) {
String s = (String) o;
s=this.sql_inj(s);
return "'"+s+"'";
} else if (o instanceof Double) {
return o.toString();
}else if (o instanceof Short) {
return o.toString();
}
else{
return "''";
}
}
/*
* 过滤防注入
*/
public String sql_inj(String s){
String key="select|update|delete|count|*|sum|master|script|'|declare|or|execute|alter|statement|executeQuery|count|executeUpdate";
String f[] = key.split("|");
for(int i=0; i<f.length;i++) {
s.replace(f[i], "");
}
return s;
}

/**
* 用于执行语句（eg：insert语句，update语句，delete语句）
*
* @param String
* sqlstr,SQL语句
* @param sqlParameter[]
* parms,参数集合
* @return int,SQL语句影响的行数
*/
public int ExecuteNonQuery(String sqlstr)
throws Exception {
try {
this.openConnection();
pstmt = conn.prepareStatement(sqlstr);
System.out.println(pstmt.toString());
int i=pstmt.executeUpdate();
rs= pstmt.getGeneratedKeys();
if ( rs != null && rs.next() )
{
return rs.getInt(1);
}else{
return i;
}
} catch (Exception e) {
throw new Exception("executeNonQuery方法出错:" + e.getMessage());
} finally {
this.dispose();
}
}
}