StringMVC 不需要事務再Controller寫SQL語句

標籤：java   encoding   取數   throw   inf   tco   resultset   cut   驅動   

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 [email protected] */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;}}

 

 

StringMVC 不需要事務再Controller寫SQL語句