package com.whyc.service;
|
|
import com.whyc.mapper.CallBack;
|
import com.whyc.util.ThreadLocalUtil;
|
import net.sf.json.JSONArray;
|
import net.sf.json.JSONObject;
|
import org.apache.ibatis.session.SqlSession;
|
import org.apache.ibatis.session.SqlSessionFactory;
|
import org.mybatis.spring.SqlSessionTemplate;
|
import org.springframework.beans.factory.annotation.Autowired;
|
import org.springframework.stereotype.Service;
|
|
import java.sql.*;
|
import java.util.ArrayList;
|
import java.util.List;
|
|
@Service
|
public class JdbcSqlExecuteService {
|
@Autowired
|
private SqlSessionTemplate sqlSessionTemplate;
|
|
// 自定义执行SQL
|
public JSONArray executeQuery(String sql){
|
PreparedStatement ps = null;
|
ResultSet rs = null;
|
SqlSession sqlSession = openSession();
|
JSONArray jsonArray = new JSONArray();
|
try {
|
ps=sqlSession.getConnection().prepareStatement(sql);
|
rs=ps.executeQuery();
|
ResultSetMetaData metaData = rs.getMetaData();
|
int colCnt = metaData.getColumnCount();
|
while (rs.next()) {
|
JSONObject jsonObject = new JSONObject();
|
for(int i=1;i<=colCnt;i++){
|
String property=metaData.getColumnName(i);
|
Object obj=rs.getObject(i);
|
if(obj instanceof java.util.Date ){
|
//jsonObject.put(property, ActionUtil.sdf.format(obj));
|
jsonObject.put(property, ThreadLocalUtil.format((java.util.Date) obj,1));
|
}else{
|
jsonObject.put(property,obj);
|
}
|
}
|
JSONObject.toBean(jsonObject,Object.class);
|
jsonArray.add(jsonObject);
|
}
|
} catch (SQLException throwables) {
|
throwables.printStackTrace();
|
}finally {
|
closeSession(rs,ps,sqlSession);
|
}
|
return jsonArray;
|
}
|
|
// 自定义执行SQL
|
public List executeQuery_call(String sql, CallBack call){
|
PreparedStatement ps = null;
|
ResultSet rs = null;
|
SqlSession sqlSession = openSession();
|
try {
|
ps=sqlSession.getConnection().prepareStatement(sql);
|
rs=ps.executeQuery();
|
return call.getResults(rs);
|
} catch (SQLException throwables) {
|
throwables.printStackTrace();
|
}finally {
|
closeSession(rs,ps,sqlSession);
|
}
|
return null;
|
}
|
|
/**
|
* 封装所有更新的操作(添加,删除,修改)
|
* @param sql
|
* @param values
|
* @return
|
*/
|
public int executeUpdate(String sql, Object[] values){
|
PreparedStatement ps = null;
|
ResultSet rs = null;
|
SqlSession sqlSession = openSession();
|
int flag=0;
|
try {
|
ps = sqlSession.getConnection().prepareStatement(sql);
|
if(values != null){
|
for (int i = 0; i < values.length; i++) {
|
ps.setObject(i+1, values[i]);
|
}
|
}
|
flag = ps.executeUpdate();
|
} catch (SQLException e) {
|
e.printStackTrace();
|
}finally{
|
closeSession(rs,ps,sqlSession);
|
}
|
return flag;
|
}
|
//执行sql语句
|
public boolean execute(String sql){
|
PreparedStatement ps = null;
|
SqlSession sqlSession = openSession();
|
boolean rest = true;
|
try{
|
ps = sqlSession.getConnection().prepareStatement(sql);
|
ps.execute();
|
|
}catch(SQLException ex) {
|
ex.printStackTrace();
|
rest = false;
|
}finally {
|
closeSession(null,ps,sqlSession);
|
}
|
|
return rest;
|
}
|
// 开启链接
|
private SqlSession openSession() {
|
SqlSessionFactory sqlSessionFactory = sqlSessionTemplate.getSqlSessionFactory();
|
return sqlSessionFactory.openSession();
|
}
|
|
// 关闭链接
|
private void closeSession(ResultSet rs,PreparedStatement ps,SqlSession sqlSession) {
|
try {
|
if(rs!=null){
|
rs.close();
|
}
|
if(ps!=null){
|
ps.close();
|
}
|
if(sqlSession!=null){
|
sqlSession.close();
|
}
|
|
} catch (SQLException throwables) {
|
throwables.printStackTrace();
|
}
|
}
|
|
public boolean makeManualCommit( ArrayList<String> al_sql_strs){
|
PreparedStatement ps = null;
|
SqlSession sqlSession = openSession();
|
Connection mysql_con=sqlSession.getConnection();
|
boolean exe_res = true;
|
try {
|
mysql_con.setAutoCommit(false);
|
|
for(int n=0; n<al_sql_strs.size(); n++) {
|
if(true == exe_res) {
|
exe_res = executeCreateTableNoclose(mysql_con,al_sql_strs.get(n));
|
} else {
|
break;
|
}
|
}
|
if(true == exe_res) {
|
mysql_con.commit();
|
}
|
} catch (SQLException e) {
|
// TODO Auto-generated catch block
|
e.printStackTrace();
|
exe_res = false;
|
} finally {
|
try {
|
if(false == exe_res) {
|
mysql_con.rollback();
|
}
|
mysql_con.setAutoCommit(true);
|
} catch (SQLException e1) {
|
// TODO Auto-generated catch block
|
e1.printStackTrace();
|
}finally {
|
closeSession(null,ps,sqlSession);
|
}
|
}
|
return exe_res;
|
}
|
|
//执行sql语句with no colse
|
public static boolean executeCreateTableNoclose(Connection mysql_con,String sql_str)
|
{
|
PreparedStatement ps=null;
|
|
boolean rest = true;
|
try
|
{
|
ps = mysql_con.prepareStatement(sql_str);
|
ps.setQueryTimeout(600);
|
ps.execute();
|
}
|
catch(SQLException ex)
|
{
|
ex.printStackTrace();
|
rest = false;
|
|
}finally {
|
try {
|
ps.close();
|
} catch (SQLException e) {
|
// TODO Auto-generated catch block
|
e.printStackTrace();
|
}
|
}
|
return rest;
|
}
|
}
|
