Mybatis-plus/Mybatis 通过获取sqlSession执行原生sql（执行程序代码中sql字符串）

约定不等于承诺〃 2022-12-24 00:49 15阅读 0赞

Mybatis-plus/Mybatis通过获取sqlSession执行原生jdbc执行sql;

此处demo只写了执行查询sql，有需要可以执行增删改查都可，与原生jdbc调用方式一样。

@Component
    @Slf4j
    public class DBUtils {
    	
    	private static final String PREFIX_LOG = "【自定义DB工具】";
    
    	@Autowired
    	private SqlSessionTemplate sqlSessionTemplate;
    	
    	public List<Map<String, Object>> excuteQuerySql(String sql){
    		log.info(PREFIX_LOG+"执行查询sql:"+sql);
    		List<Map<String, Object>> list = new ArrayList<Map<String,Object>>();
    		PreparedStatement pst = null;
    		SqlSession session = getSqlSession();
    		ResultSet result = null;
    		try {
    			pst = session.getConnection().prepareStatement(sql);
    			result = pst.executeQuery();
    			ResultSetMetaData md = result.getMetaData(); //获得结果集结构信息,元数据
    			int columnCount = md.getColumnCount();   //获得列数
    			while (result.next()) {
    				Map<String,Object> rowData = new HashMap<String,Object>();
    				for (int i = 1; i <= columnCount; i++) {
    					rowData.put(md.getColumnName(i), result.getObject(i));
    				}
    				list.add(rowData);
    			}
    		} catch (SQLException e) {
    			e.printStackTrace();
    		}finally {
    			if(pst!=null){
    				try {
    					pst.close();
    				} catch (SQLException e) {
    					e.printStackTrace();
    				}
    			}
    			closeSqlSession(session);
    		}
    		return list;
    	}
    	
    	/**
    	 * 获取sqlSession
    	 * @return
    	 */
    	public SqlSession getSqlSession(){
    		return SqlSessionUtils.getSqlSession(sqlSessionTemplate.getSqlSessionFactory(),
    				sqlSessionTemplate.getExecutorType(), sqlSessionTemplate.getPersistenceExceptionTranslator());
    	}
    	
    	/**
    	 * 关闭sqlSession
    	 * @param session
    	 */
    	public void closeSqlSession(SqlSession session){
    		SqlSessionUtils.closeSqlSession(session, sqlSessionTemplate.getSqlSessionFactory());
    	}
    }