StringMVC 不需要事务再Controller写SQL语句

String logsql = "INSERT INTO evrentallogs.log_edaijia(channel,orderId,status,driverNo,sign,createtime) VALUES('"+channel+"','"+orderId+"','"+status+"','"+driverNo+"','"+sign+"',NOW())";

DataBaseUtil.execute(logsql);

package net.joystart.data;

import java.sql.*;
import java.util.Map;
import java.util.HashMap;
import java.util.List;
import java.util.ArrayList;
import net.joystart.common.util.ConfigUtil;

import org.apache.log4j.Logger;

/***
 * 通用数据库查询类
 * 
 * @author lidc@bagechuxing.cn
 */
public class DataBaseUtil {
	// 数据库驱动程序
	static String driverName = "com.mysql.jdbc.Driver";
	static String hostName = ConfigUtil.pro.get("db_host").toString();
	static String readHostName = ConfigUtil.pro.get("db_host_readonly").toString();
	static String userName = ConfigUtil.pro.get("db_username").toString();
	static String userPasswd = ConfigUtil.pro.get("db_password").toString();
	static String dbName = ConfigUtil.pro.get("db_name").toString();

	/***
	 * 读写分离, 执行SQL语句
	 * @param sqlCmd
	 * @return
	 */
	public static List<Map<String, Object>> execute(String sqlCmd) {
		Logger log = Logger.getLogger(DataBaseUtil.class);
		List<Map<String, Object>> resultList = new ArrayList<Map<String, Object>>();
		String driver = "jdbc:mysql://" + readHostName + "/" + dbName + "?useUnicode=true&characterEncoding=UTF-8&user="
				+ userName + "&password=" + userPasswd;
		
		//插入更新操作
		if(sqlCmd.toLowerCase().indexOf("insert")!=-1 || sqlCmd.toLowerCase().indexOf("replace")!=-1 || sqlCmd.toLowerCase().indexOf("update")!=-1){
			driver = "jdbc:mysql://" + hostName + "/" + dbName + "?useUnicode=true&characterEncoding=UTF-8&user="
					+ userName + "&password=" + userPasswd;
		}
		
		try {
			Class.forName(driverName).newInstance();

			Connection conn = DriverManager.getConnection(driver);
			Statement stmt = conn.createStatement();
			if(sqlCmd.toLowerCase().indexOf("insert ")!=-1 || sqlCmd.toLowerCase().indexOf("replace ")!=-1 || sqlCmd.toLowerCase().indexOf("update ")!=-1){
				boolean ret = stmt.execute(sqlCmd);
				Map<String, Object> m = new HashMap<String, Object>();
				m.put("result", ret);
				resultList.add(m);
				log.info(String.format("执行更新结果：%s, %s" , sqlCmd, ret));
			}
			else
			{
				//log.info("执行查询：" + sqlCmd);
				ResultSet rs = stmt.executeQuery(sqlCmd);
				while (rs.next()) {
					Map<String, Object> m = getResultMap(rs);
					resultList.add(m);
				}
				rs.close();
			}
			stmt.close();
			conn.close();
		} catch (Exception e) {
			e.printStackTrace();
			log.info("获取数据失败:" + e.toString()+",sql:" + sqlCmd);
		}
		return resultList;
	}

	
	/***
	 * 主库查询
	 * @param sqlCmd
	 * @return
	 */
	public static List<Map<String, Object>> query(String sqlCmd) {
		Logger log = Logger.getLogger(DataBaseUtil.class);
		List<Map<String, Object>> resultList = new ArrayList<Map<String, Object>>();
		String driver = "jdbc:mysql://" + hostName + "/" + dbName + "?useUnicode=true&characterEncoding=UTF-8&user="
				+ userName + "&password=" + userPasswd;
		
		try {
			Class.forName(driverName).newInstance();

			Connection conn = DriverManager.getConnection(driver);
			Statement stmt = conn.createStatement();
			if(sqlCmd.toLowerCase().indexOf("insert ")!=-1 || sqlCmd.toLowerCase().indexOf("replace ")!=-1 || sqlCmd.toLowerCase().indexOf("update ")!=-1){
				boolean ret = stmt.execute(sqlCmd);
				Map<String, Object> m = new HashMap<String, Object>();
				m.put("result", ret);
				resultList.add(m);
				log.info(String.format("执行更新结果：%s, %s" , sqlCmd, ret));
			}
			else
			{
				ResultSet rs = stmt.executeQuery(sqlCmd);
				while (rs.next()) {
					Map<String, Object> m = getResultMap(rs);
					resultList.add(m);
				}
				rs.close();
			}
			stmt.close();
			conn.close();
		} catch (Exception e) {
			e.printStackTrace();
			log.info("获取数据失败:" + e.toString()+",sql:" + sqlCmd);
		}
		return resultList;
	}
	
	/***
	 * 数据集转Map
	 * 
	 * @param rs
	 * @return
	 * @throws SQLException
	 */
	private static Map<String, Object> getResultMap(ResultSet rs)
			throws SQLException {
		Map<String, Object> hm = new HashMap<String, Object>();
		ResultSetMetaData rsmd = rs.getMetaData();
		int count = rsmd.getColumnCount();
		for (int i = 1; i <= count; i++) {
			String key = rsmd.getColumnLabel(i);
			String value = rs.getString(i);
			hm.put(key, value);
		}
		return hm;
	}

}

 

 

转载于:https://www.cnblogs.com/cuijinlong/p/7657305.html