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.PreparedStatement;
|
import java.sql.ResultSet;
|
import java.sql.ResultSetMetaData;
|
import java.sql.SQLException;
|
import java.util.List;
|
|
@Service
|
public class MybatisSqlExecuteService {
|
@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();
|
}
|
}
|
}
|
