package com.comon;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import com.bean.Product;
/**
* 使用纯的JDBC操作DB
* @author tfq
*
*/
public class BaseDao {
/**
* 过滤在sql语句中的单引号,防止注入攻击
* @param sql
* @return
*/
public static String filterSingal(String sql){
if(sql==null||sql.trim().equals("")){
return null;
}else{
return sql.replace("'", "''");
}
}
/**
* 通用的删除方法
* @param tableName 表名
* @param primaryName 主键名
* @param primaryId 值
* @return
* @throws SQLException
*/
public static int del(String tableName,String primaryName,String primaryValue)
throws SQLException{
String delSQL="delete "+filterSingal(tableName)+" where " +
""+filterSingal(primaryName)+"="+primaryValue;
int res=update(delSQL);
return res;
}
/**
* 通用的插入、删除、更新方法
* @param sql
* @return
* @throws SQLException
*/
public static int update(String sql) throws SQLException{
if(sql==null||sql.trim().equals("")){
return 0;
}
Connection conn=DBCon.getCon();
int count=0;
PreparedStatement pst=null;
try {
//false is that set transaction manual commit mode
conn.setAutoCommit(false);
pst=conn.prepareStatement(sql);
count=pst.executeUpdate();
//commit transaction
conn.commit();
} catch (SQLException e) {
e.printStackTrace();
try {
conn.rollback();
} catch (SQLException e1) {
e1.printStackTrace();
}
}finally{
conn.rollback();
DBCon.closeAllCon(pst, conn);
}
return count;
}
/**
* 返回拼装更新sql语句
* @param map
* @param tableName
* @param primaryName
* @param primaryValue
* @return
*/
public static String updateSQL(HashMap<String,String> map,String tableName,
String primaryName,String primaryValue){
//拼装的sql
String sqlStr="";
//在update语句中的字段名和值
String keyValueStr="";
//存取set后的字段名和值除了逗号的值
String keyValue="";
if(map==null||map.size()<=0){
return sqlStr;
}
if(tableName==null||tableName.trim().equals("")){
return sqlStr;
}
if(primaryValue==null||primaryValue.trim().equals("")){
return sqlStr;
}
for(String key:map.keySet()){
//return key in this map
String value=map.get(key);
keyValueStr +=filterSingal(key)+"='"+filterSingal(value)+"',";
}
keyValue=keyValueStr.substring(0, keyValueStr.length()-1);
sqlStr ="update "+tableName+" set "+keyValue+" where "+primaryName+" ="+primaryValue;
return sqlStr;
}
/**
* 插入tabeleName
* @param map 字段名和值的map
* @param tableName 表名
* @return
*/
public static int insert(HashMap<String,String> map,String tableName){
int count=0;
String inserSQL="";
inserSQL=insertSQL(map,tableName);
try {
count=update(inserSQL);
} catch (SQLException e) {
e.printStackTrace();
}
return count;
}
public static String insertSQL(HashMap<String,String> map,String tableName){
//插入sql
String inserSQL="";
//存取要插入的表的字段名
String columns="";
//存取字段名的值
String columnsValue="";
//临时存取字段名和字段值
String tempCols="",tempColsValue="";
if(map==null||map.size()<=0){
return inserSQL;
}
if(tableName==null&&tableName.trim().equals("")){
return inserSQL;
}
for(String key:map.keySet()){
String value=map.get(key);
if(value==null&&value.trim().equals("")){
value="null";
}
tempCols +=key+",";
tempColsValue +="'"+value+"',";
}
columns=tempCols.substring(0, tempCols.length()-1);
columnsValue=tempColsValue.substring(0, tempColsValue.length()-1);
inserSQL="insert into "+tableName+" ("+columns+") values( "+columnsValue+")";
return inserSQL;
}
/**
* 动态sql查询结果
* @param sql
* @param conditionMap 向sql语句中传入实参
* @return
*/
public static List<Map<String, Object>> queryByDynamic(String sql,Map<Integer
, Object> conditionMap){
Connection conn = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
List<Map<String, Object>> resultList = new ArrayList<Map<String, Object>>();
if(sql==null||sql.trim().equals("")){
return resultList;
}
if(conditionMap==null||conditionMap.size()==0){
return resultList;
}
try {
conn = DBCon.getCon();
//send sql to db and prepare to compiling it
pstmt = conn.prepareStatement(filterSingal(sql));
if(conditionMap!=null&&conditionMap.size()!=0){
int paramNum = conditionMap.size();
for(int i=1;i<=paramNum;i++){
//返回传入到sql语句的参数类型
Object paramValue = conditionMap.get(i);
System.out.println(paramValue.getClass().getName());
//传值到sql中的参数
if("java.lang.Integer".equalsIgnoreCase(paramValue.getClass().getName())){
pstmt.setInt(i, Integer.parseInt(filterSingal(paramValue.toString())));
}else if("java.lang.String".equalsIgnoreCase(paramValue.getClass().getName())){
pstmt.setString(i, paramValue.toString());
}else if("java.lang.Double".equalsIgnoreCase(filterSingal(paramValue.getClass().getName()))){
pstmt.setString(i, paramValue.toString());
}else if("java.lang.Boolean".equalsIgnoreCase(filterSingal(paramValue.getClass().getName()))){
pstmt.setString(i, paramValue.toString());
}else if("java.lang.Date".equalsIgnoreCase(filterSingal(paramValue.getClass().getName()))){
pstmt.setString(i, paramValue.toString());
}
}
}
conn.setAutoCommit(false);
//Executes the SQL Query in this PreparedStatement Object and
//return ResultSet object generated by the query
rs = pstmt.executeQuery();
//return resultSet object's columns、type、propertites
ResultSetMetaData rsmd = rs.getMetaData();
//total columns
int columnNum = rsmd.getColumnCount();
while(rs.next()){
Map<String, Object> dataMap = new HashMap<String, Object>(0);
for(int i=1;i<=columnNum;i++){
//property and value
dataMap.put(rsmd.getColumnName(i), rs.getObject(i));
}
resultList.add(dataMap);
}
conn.commit();
} catch (SQLException e) {
try {
conn.rollback();
} catch (SQLException e1) {
e1.printStackTrace();
}
e.printStackTrace();
}finally{
DBCon.closeAllConn(rs, pstmt, conn);
}
return resultList;
}
public static void main(String[] args) {
/*HashMap<String,String> map=new HashMap<String,String>();
map.put("proName", "tsfq");
map.put("proPrice", 2231+"");
map.put("proColor", "rsed");
map.put("proSize", "3234");
map.put("proRemark", "remark");
String sql=updateSQL(map,"product111","proId","6");
try {
System.out.println(update(sql));
} catch (SQLException e) {
e.printStackTrace();
}*/
/*String sql="select * from product111";
ArrayList<String> list=getdata(sql);
for(String str:list){
System.out.println()
}*/
/*ArrayList<Product> list = getProductList();
for (Product p : list) {
System.out.println( p.getProId()+"----"+p.getProName());
}*/
//test queryByDinamic method
//String sql = "select proName,proPrice from product where proName=? and proId=? and proPrice=?";
String sql="select u.userName,p.proName,p.proPrice from product p,userInfo u where p.userId=u.userId " +
"and p.proName=? and p.proId=? and p.proPrice=? and p.isActive=? and p.createTime=?";
Map<Integer, Object> conditionMap = new HashMap<Integer, Object>();
conditionMap.put(1, "tfq");
conditionMap.put(2, 3);
conditionMap.put(3, 221.3);
conditionMap.put(4, true);
conditionMap.put(5, "2012-6-18");
List<Map<String, Object>> resultList = queryByDynamic(sql, conditionMap);
System.out.println(resultList);
}
}
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import com.bean.Product;
/**
* 使用纯的JDBC操作DB
* @author tfq
*
*/
public class BaseDao {
/**
* 过滤在sql语句中的单引号,防止注入攻击
* @param sql
* @return
*/
public static String filterSingal(String sql){
if(sql==null||sql.trim().equals("")){
return null;
}else{
return sql.replace("'", "''");
}
}
/**
* 通用的删除方法
* @param tableName 表名
* @param primaryName 主键名
* @param primaryId 值
* @return
* @throws SQLException
*/
public static int del(String tableName,String primaryName,String primaryValue)
throws SQLException{
String delSQL="delete "+filterSingal(tableName)+" where " +
""+filterSingal(primaryName)+"="+primaryValue;
int res=update(delSQL);
return res;
}
/**
* 通用的插入、删除、更新方法
* @param sql
* @return
* @throws SQLException
*/
public static int update(String sql) throws SQLException{
if(sql==null||sql.trim().equals("")){
return 0;
}
Connection conn=DBCon.getCon();
int count=0;
PreparedStatement pst=null;
try {
//false is that set transaction manual commit mode
conn.setAutoCommit(false);
pst=conn.prepareStatement(sql);
count=pst.executeUpdate();
//commit transaction
conn.commit();
} catch (SQLException e) {
e.printStackTrace();
try {
conn.rollback();
} catch (SQLException e1) {
e1.printStackTrace();
}
}finally{
conn.rollback();
DBCon.closeAllCon(pst, conn);
}
return count;
}
/**
* 返回拼装更新sql语句
* @param map
* @param tableName
* @param primaryName
* @param primaryValue
* @return
*/
public static String updateSQL(HashMap<String,String> map,String tableName,
String primaryName,String primaryValue){
//拼装的sql
String sqlStr="";
//在update语句中的字段名和值
String keyValueStr="";
//存取set后的字段名和值除了逗号的值
String keyValue="";
if(map==null||map.size()<=0){
return sqlStr;
}
if(tableName==null||tableName.trim().equals("")){
return sqlStr;
}
if(primaryValue==null||primaryValue.trim().equals("")){
return sqlStr;
}
for(String key:map.keySet()){
//return key in this map
String value=map.get(key);
keyValueStr +=filterSingal(key)+"='"+filterSingal(value)+"',";
}
keyValue=keyValueStr.substring(0, keyValueStr.length()-1);
sqlStr ="update "+tableName+" set "+keyValue+" where "+primaryName+" ="+primaryValue;
return sqlStr;
}
/**
* 插入tabeleName
* @param map 字段名和值的map
* @param tableName 表名
* @return
*/
public static int insert(HashMap<String,String> map,String tableName){
int count=0;
String inserSQL="";
inserSQL=insertSQL(map,tableName);
try {
count=update(inserSQL);
} catch (SQLException e) {
e.printStackTrace();
}
return count;
}
public static String insertSQL(HashMap<String,String> map,String tableName){
//插入sql
String inserSQL="";
//存取要插入的表的字段名
String columns="";
//存取字段名的值
String columnsValue="";
//临时存取字段名和字段值
String tempCols="",tempColsValue="";
if(map==null||map.size()<=0){
return inserSQL;
}
if(tableName==null&&tableName.trim().equals("")){
return inserSQL;
}
for(String key:map.keySet()){
String value=map.get(key);
if(value==null&&value.trim().equals("")){
value="null";
}
tempCols +=key+",";
tempColsValue +="'"+value+"',";
}
columns=tempCols.substring(0, tempCols.length()-1);
columnsValue=tempColsValue.substring(0, tempColsValue.length()-1);
inserSQL="insert into "+tableName+" ("+columns+") values( "+columnsValue+")";
return inserSQL;
}
/**
* 动态sql查询结果
* @param sql
* @param conditionMap 向sql语句中传入实参
* @return
*/
public static List<Map<String, Object>> queryByDynamic(String sql,Map<Integer
, Object> conditionMap){
Connection conn = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
List<Map<String, Object>> resultList = new ArrayList<Map<String, Object>>();
if(sql==null||sql.trim().equals("")){
return resultList;
}
if(conditionMap==null||conditionMap.size()==0){
return resultList;
}
try {
conn = DBCon.getCon();
//send sql to db and prepare to compiling it
pstmt = conn.prepareStatement(filterSingal(sql));
if(conditionMap!=null&&conditionMap.size()!=0){
int paramNum = conditionMap.size();
for(int i=1;i<=paramNum;i++){
//返回传入到sql语句的参数类型
Object paramValue = conditionMap.get(i);
System.out.println(paramValue.getClass().getName());
//传值到sql中的参数
if("java.lang.Integer".equalsIgnoreCase(paramValue.getClass().getName())){
pstmt.setInt(i, Integer.parseInt(filterSingal(paramValue.toString())));
}else if("java.lang.String".equalsIgnoreCase(paramValue.getClass().getName())){
pstmt.setString(i, paramValue.toString());
}else if("java.lang.Double".equalsIgnoreCase(filterSingal(paramValue.getClass().getName()))){
pstmt.setString(i, paramValue.toString());
}else if("java.lang.Boolean".equalsIgnoreCase(filterSingal(paramValue.getClass().getName()))){
pstmt.setString(i, paramValue.toString());
}else if("java.lang.Date".equalsIgnoreCase(filterSingal(paramValue.getClass().getName()))){
pstmt.setString(i, paramValue.toString());
}
}
}
conn.setAutoCommit(false);
//Executes the SQL Query in this PreparedStatement Object and
//return ResultSet object generated by the query
rs = pstmt.executeQuery();
//return resultSet object's columns、type、propertites
ResultSetMetaData rsmd = rs.getMetaData();
//total columns
int columnNum = rsmd.getColumnCount();
while(rs.next()){
Map<String, Object> dataMap = new HashMap<String, Object>(0);
for(int i=1;i<=columnNum;i++){
//property and value
dataMap.put(rsmd.getColumnName(i), rs.getObject(i));
}
resultList.add(dataMap);
}
conn.commit();
} catch (SQLException e) {
try {
conn.rollback();
} catch (SQLException e1) {
e1.printStackTrace();
}
e.printStackTrace();
}finally{
DBCon.closeAllConn(rs, pstmt, conn);
}
return resultList;
}
public static void main(String[] args) {
/*HashMap<String,String> map=new HashMap<String,String>();
map.put("proName", "tsfq");
map.put("proPrice", 2231+"");
map.put("proColor", "rsed");
map.put("proSize", "3234");
map.put("proRemark", "remark");
String sql=updateSQL(map,"product111","proId","6");
try {
System.out.println(update(sql));
} catch (SQLException e) {
e.printStackTrace();
}*/
/*String sql="select * from product111";
ArrayList<String> list=getdata(sql);
for(String str:list){
System.out.println()
}*/
/*ArrayList<Product> list = getProductList();
for (Product p : list) {
System.out.println( p.getProId()+"----"+p.getProName());
}*/
//test queryByDinamic method
//String sql = "select proName,proPrice from product where proName=? and proId=? and proPrice=?";
String sql="select u.userName,p.proName,p.proPrice from product p,userInfo u where p.userId=u.userId " +
"and p.proName=? and p.proId=? and p.proPrice=? and p.isActive=? and p.createTime=?";
Map<Integer, Object> conditionMap = new HashMap<Integer, Object>();
conditionMap.put(1, "tfq");
conditionMap.put(2, 3);
conditionMap.put(3, 221.3);
conditionMap.put(4, true);
conditionMap.put(5, "2012-6-18");
List<Map<String, Object>> resultList = queryByDynamic(sql, conditionMap);
System.out.println(resultList);
}
}