public Pedido getPedidoDAO(final String idPedido) {
final SqlSession session = ConnectionFactory.getSqlSessionFactory().openSession();
final PedidoDAO pedidoDao = session.getMapper(PedidoDAO.class);
final Pedido pedido = pedidoDao.getPedidoDAO(idPedido);
session.close();
return pedido;
}
@Override
public UserProfile checkAuthenticationDB(String username, String password) throws Exception {
SqlSession session = SqlMapConfig.getSqlSession(false);
try {
// Run command ----------------------------------------------------
Map<String, Object> map = new HashMap<String, Object>();
map.put("username", username);
map.put("password", password);
UserProfile userProfile = session.selectOne("UserProfile.getUserProfile", map);
session.commit();
return userProfile;
} catch (Exception e) {
session.rollback();
throw e;
} finally {
session.clearCache();
session.close();
session = null;
}
}
@SuppressWarnings("unchecked")
@Override
public List<UsuarioDTO> buscarUsuarioPaginados(UsuarioDTO usuario, Integer inicio, Integer tamano)
throws Exception {
SqlSession sesion = sqlMapper.openSession();
List<UsuarioDTO> lstUsuario = new ArrayList<UsuarioDTO>();
try {
if (usuario == null) {
UsuarioDTO provee = new UsuarioDTO();
provee.setInicio(inicio);
provee.setTamano(tamano);
lstUsuario =
(List<UsuarioDTO>) sesion.selectList("usuario.SQL_listaUsuarioPaginados", provee);
} else {
if (!usuario.getNom_usuario().isEmpty()) {
usuario.setNom_usuario("%" + usuario.getNom_usuario() + "%");
usuario.setInicio(inicio);
usuario.setTamano(tamano);
lstUsuario =
(List<UsuarioDTO>)
sesion.selectList("usuario.SQL_listaUsuarioNom_usuarioPaginados", usuario);
}
}
} finally {
sesion.close();
}
return lstUsuario;
}
public void atualizarStatusPedidoDAO(final Pedido pedido) {
final SqlSession session = ConnectionFactory.getSqlSessionFactory().openSession();
final PedidoDAO pedidoDao = session.getMapper(PedidoDAO.class);
pedidoDao.atualizarStatusPedidoDAO(pedido);
session.commit();
session.close();
}
/** USE THIS INSTEAD OF THE BATGEN DELETE It also deletes associated events of the object.s */
public int delete(Long key) throws BoException {
SqlSession session = null;
int result = 0;
String where = "KEY='" + key + "' ";
Map<String, Object> map = new HashMap<String, Object>();
map.put("where", where);
// first delete all events associated with this object
EventBo.getInstance().deleteEventsOf(ItemType.SOFTWARE, key);
try {
session = SessionFactory.getSession();
SoftwareDao mapper = session.getMapper(SoftwareDao.class);
result = mapper.delete(map);
session.commit();
} catch (Exception e) {
session.rollback();
throw new BoException(e);
} finally {
if (session != null) session.close();
}
return result;
}
private int aplicarOperacion(String nombreSQL, BaseBean bean, String comando) {
SqlSession sqlSession = this.sqlSessionFactory.openSession();
int rowAffected = 0;
try {
switch (comando) {
case "INSERT":
rowAffected = sqlSession.insert(nombreSQL, bean);
break;
case "UPDATE":
rowAffected = sqlSession.update(nombreSQL, bean);
break;
case "DELETE":
rowAffected = sqlSession.delete(nombreSQL, bean);
break;
default:
LOGGER_INFO.info("No se reconoce el comando " + comando);
}
if (rowAffected > 0) sqlSession.commit();
} catch (Exception e) {
LOGGER_ERROR.error(
"Se ha presentando el siguiente error a la hora de ejecutar el comando "
+ comando
+ " "
+ nombreSQL,
e);
} finally {
sqlSession.close();
}
return rowAffected;
}
/**
* 获取permission数据集合
*
* @author 尹逊志
* @throws JsonProcessingException
* @time 2015年4月13日下午10:29:36
*/
@ResponseBody
@RequestMapping(value = "/user/permissionList", method = RequestMethod.POST)
public String getPermissonData(HttpServletRequest request, HttpServletResponse response)
throws JsonProcessingException {
String rows = request.getParameter("rows");
String page = request.getParameter("page");
if (rows == null) {
rows = "20";
}
if (page == null) {
page = "1";
}
String resource = "/mybatis-config-test.xml";
InputStream is = UserController.class.getResourceAsStream(resource);
SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(is);
SqlSession session = factory.openSession();
String statement = "com.myproject.mybatis.user.permissionMapper.getAll";
Map<String, Integer> map = new HashMap<String, Integer>();
map.put("pageNum", Integer.parseInt(page));
map.put("pageSize", Integer.parseInt(rows));
PageHelper.startPage(Integer.parseInt(page), Integer.parseInt(rows));
List<Permission> permissList = session.selectList(statement);
PageInfo<Permission> pageInfo = new PageInfo<Permission>(permissList);
session.commit();
session.close();
ResponseResult result = new ResponseResult();
result.setTotal(pageInfo.getTotal());
result.setRows(permissList);
ObjectMapper mapper = new ObjectMapper();
return mapper.writeValueAsString(result);
}
/** @param args */
public static void main(String[] args) {
InputStream inputStream = null;
try {
inputStream = Resources.getResourceAsStream("org/mybatis/example/config.properties");
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
Properties p = null;
try {
p = new Properties();
// load file
p.load(inputStream);
} catch (Exception e) {
e.printStackTrace();
}
String resource = "org/mybatis/example/mybatis-config.xml";
try {
inputStream = Resources.getResourceAsStream(resource);
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream, p);
SqlSession session = sqlSessionFactory.openSession();
try {
Blog blog = (Blog) session.selectOne("org.mybatis.example.BlogMapper.selectBlog", 101);
} finally {
session.close();
}
}
public List<MessageDetailsVO> getMessages() throws Exception {
SqlSession session = ConnectionFactory.getSession().openSession();
MessageDetailsDAO dao = session.getMapper(MessageDetailsDAO.class);
List<MessageDetailsVO> users = dao.getAllMessages();
session.close();
return users;
}
public MessageDetailsVO getMessageById(long id) throws Exception {
SqlSession session = ConnectionFactory.getSession().openSession();
MessageDetailsDAO dao = session.getMapper(MessageDetailsDAO.class);
MessageDetailsVO messageDetailsVO = dao.getMessageById(id);
session.close();
return messageDetailsVO;
}
/**
* 对用户基本信息进行删除操作
*
* @return
* @throws IOException
*/
public String deptuserDelete() throws Exception {
SqlSession sqlSession = null;
try {
sqlSession = DbSessionFactory.getOracleSession();
C_T_Sys_Dept_UserMapper mapper = sqlSession.getMapper(C_T_Sys_Dept_UserMapper.class);
// 如果没有选中的数据的ID则不作任何处理
if (!"".equals(checkedIds)) {
String[] arrUserids;
arrUserids = checkedIds.split("%2C");
C_T_Sys_Dept_UserExample example = new C_T_Sys_Dept_UserExample();
// 循环遍历选中的数据的ID
for (String tempUserid : arrUserids) {
// 将ID作为条件加入条件集合中
example.or().andUuidEqualTo(tempUserid);
}
// 根据条件删除对应的数据
mapper.deleteByExample(example);
sqlSession.commit();
}
jsonResult = Constants.DISP_OPERATION_SUCCESSFUL;
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
return ERROR;
} finally {
if (sqlSession != null) {
sqlSession.close();
sqlSession = null;
}
}
return SUCCESS;
}
public String deptuserDetailsLoad() throws Exception {
SqlSession sqlSession = null;
try {
sqlSession = DbSessionFactory.getOracleSession();
allPermission = "*";
C_T_Sys_Dept_UserMapper mapper = sqlSession.getMapper(C_T_Sys_Dept_UserMapper.class);
C_T_Sys_UserMapper usermapper = sqlSession.getMapper(C_T_Sys_UserMapper.class);
// 根据主键取得流程基本信息
c_T_Sys_Dept_User = (C_T_Sys_Dept_User) mapper.selectByPrimaryKey(uuid);
C_T_Sys_User user = null;
if (c_T_Sys_Dept_User != null) {
user = (C_T_Sys_User) usermapper.selectByPrimaryKey(c_T_Sys_Dept_User.getUserSysName());
userName = user.getUserName();
}
// 取得部门基本信息
listDept = GetBaseInfor.getDepartmentBaseInfor("sort", sqlSession);
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
return ERROR;
} finally {
if (sqlSession != null) {
sqlSession.close();
sqlSession = null;
}
}
return SUCCESS;
}
public static void main(String[] args) {
Reader reader;
try {
reader = Resources.getResourceAsReader("mapper/Configuration.xml");
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(reader);
DBSessionDao dbSessionDao = new DBSessionDao();
System.out.println(" -------- " + dbSessionDao);
SqlSession sqlSession = sqlSessionFactory.openSession(dbSessionDao.getConnection());
// SqlSession sqlSession = sqlSessionFactory.openSession();
System.out.println(sqlSession);
OperatorMapper operatorMapper = sqlSession.getMapper(OperatorMapper.class);
OperatorExample operatorExample = new OperatorExample();
operatorExample.createCriteria().andOpernameEqualTo("tc");
List<Operator> list = operatorMapper.selectByExample(operatorExample);
for (Operator operator : list) {
System.out.println(" 2 " + operator);
}
sqlSession.close();
} catch (IOException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}
}
@Test
public void getAllPayedOrder() {
SqlSession session = sessionFactory.openSession();
try {
OrderMapper orderMapper = session.getMapper(OrderMapper.class);
List<OrderVO> list = orderMapper.getAllPayedOrder();
if (list != null && list.size() > 0) {
System.out.println("getAllPayedOrder:" + list.size());
for (OrderVO order : list) {
System.out.println("orderuser : "******"saleruser : "******"receiveaddress : " + order.getReceiveaddress());
System.out.println("kitchenaddress : " + order.getKitchenaddress());
if (order.getPk_dish() != null)
System.out.println("image : " + order.getPk_dish().getPreviewimagedir());
if (order.getPk_vegetable() != null)
System.out.println("image : " + order.getPk_vegetable().getVimage());
}
}
} catch (Exception e) {
e.printStackTrace();
} finally {
session.close();
}
}
@Override
public T queryOne(T t) {
SqlSession sqlSession = sqlSessionFactory.openSession(false);
t = sqlSession.selectOne(namespace.concat("queryOne"), t);
sqlSession.close();
return t; // 返回类型为T
}
/**
* 強破關閉資料庫連線 回收到pool
*
* @param session SqlSession
*/
public static synchronized void closeCoonnection(SqlSession session) {
try {
session.close();
} catch (Exception e) {
// nothing
}
}
public List<Map<String, Object>> list(Map<String, Object> params) {
List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
SqlSession session = DbConnectionFactory.getSqlSessionFactory(dataSource).openSession();
try {
MainAccountActivityGroupDAO dao = session.getMapper(MainAccountActivityGroupDAO.class);
List<Map<String, Object>> tmpList = dao.list(params);
String name = MainAccountActivityGroupConstant.TFN_NAME + params.get("lang");
for (Map<String, Object> tmpMap : tmpList) {
Map<String, Object> map = new HashMap<String, Object>();
map.put(
JsonConstant.COMBOBOX_ID,
(Integer) tmpMap.get(MainAccountActivityGroupConstant.TFN_ID));
map.put(JsonConstant.COMBOBOX_NAME, (String) tmpMap.get(name.toUpperCase()));
tmpMap = CommonUtil.removeThElement(tmpMap);
map.put(JsonConstant.COMBOBOX_DATA, tmpMap);
list.add(map);
}
} catch (Exception ex) {
log.error(ex);
} finally {
session.close();
}
return list;
}
public String fileDutyOfficerUpdate() throws Exception {
SqlSession sqlSession = null;
try {
sqlSession = DbSessionFactory.getOracleSession();
C_T_SYS_Filedutyofficer record = new C_T_SYS_Filedutyofficer();
C_T_SYS_FiledutyofficerMapper mapper =
sqlSession.getMapper(C_T_SYS_FiledutyofficerMapper.class);
if (null == c_T_SYS_Filedutyofficer.getFiledutyofficerId()
|| "".equals(c_T_SYS_Filedutyofficer.getFiledutyofficerId().trim())) {
c_T_SYS_Filedutyofficer.setFiledutyofficerId((new GUID().toString()));
DbSetData.setPerporyData(c_T_SYS_Filedutyofficer, record, 2);
mapper.insertSelective(record);
} else {
DbSetData.setPerporyData(c_T_SYS_Filedutyofficer, record, 1);
mapper.updateByPrimaryKey(record);
}
jsonResult = Constants.DISP_OPERATION_SUCCESSFUL;
sqlSession.commit();
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
return ERROR;
} finally {
if (sqlSession != null) {
sqlSession.close();
sqlSession = null;
}
}
allPermission = "*";
return SUCCESS;
}
@BeforeClass
public static void setUp() throws Exception {
Reader reader =
Resources.getResourceAsReader(
"org/apache/ibatis/submitted/multipleresultsetswithassociation/mybatis-config.xml");
sqlSessionFactory = new SqlSessionFactoryBuilder().build(reader);
reader.close();
// populate in-memory database
// Could not get the table creation, procedure creation, and data population to work from the
// same script.
// Once it was in three scripts, all seemed well.
SqlSession session = sqlSessionFactory.openSession();
Connection conn = session.getConnection();
reader =
Resources.getResourceAsReader(
"org/apache/ibatis/submitted/multipleresultsetswithassociation/CreateDB1.sql");
runReaderScript(conn, session, reader);
reader.close();
reader =
Resources.getResourceAsReader(
"org/apache/ibatis/submitted/multipleresultsetswithassociation/CreateDB2.sql");
runReaderScript(conn, session, reader);
reader.close();
reader =
Resources.getResourceAsReader(
"org/apache/ibatis/submitted/multipleresultsetswithassociation/CreateDB3.sql");
runReaderScript(conn, session, reader);
reader.close();
session.close();
}
public int update(String title, String content, Date startDate, Date endDate, String tag, int no)
throws Exception {
System.out.println(title + " - " + content + " - " + startDate + " - " + no);
SqlSession sqlSession = sqlSessionFactory.openSession();
HashMap<String, Object> params = new HashMap<String, Object>();
params.put("title", title);
params.put("content", content);
params.put("startDate", startDate);
params.put("endDate", endDate);
params.put("tag", tag);
params.put("no", no);
try {
int rsn = sqlSession.update("net.bitacademy.java41.dao.ProjectMapper.update", params);
sqlSession.commit();
return rsn;
} catch (Exception e) {
throw e;
} finally {
try {
sqlSession.close();
} catch (Exception e) {
}
}
}
public String filedutyofficerDelete() throws Exception {
SqlSession sqlSession = null;
try {
sqlSession = DbSessionFactory.getOracleSession();
if (!"".equals(checkedIds)) {
String[] arrUuids;
arrUuids = checkedIds.split("%2C");
C_T_SYS_FiledutyofficerExample example = new C_T_SYS_FiledutyofficerExample();
C_T_SYS_FiledutyofficerMapper mapper =
sqlSession.getMapper(C_T_SYS_FiledutyofficerMapper.class);
for (String tempUuid : arrUuids) {
example.or().andFiledutyofficerIdEqualTo(tempUuid);
}
mapper.deleteByExample(example);
}
jsonResult = Constants.DISP_OPERATION_SUCCESSFUL;
sqlSession.commit();
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
return ERROR;
} finally {
if (sqlSession != null) {
sqlSession.close();
sqlSession = null;
}
}
allPermission = "*";
return SUCCESS;
}
/**
* Read command.
*
* @param usuarioVO the usuario vo
* @return the usuario vo
*/
private UsuarioVO readCommand(UsuarioVO usuarioVO) {
logger.debug("Entrada createCommand -- OK");
usuarioVO.setIdPerfil(null);
usuarioVO.setNombreUsuario(null);
usuarioVO.setEstatusUsuario(null);
logger.debug("Datos de Entrada createCommand -- " + usuarioVO.toString());
UsuarioVO result = null;
try {
SqlSession session = MapeadorSessionFactory.getSqlSessionFactory().openSession();
MapUsuario mapUsuario = session.getMapper(MapUsuario.class);
if (usuarioVO != null && usuarioVO.getIdCveUsuario() != null) {
logger.debug(":::::::::::::::::::::" + usuarioVO.getIdCveUsuario());
}
try {
result = mapUsuario.obtenerUsuarios(usuarioVO).get(0);
} catch (Exception ex) {
result = null;
} finally {
session.close();
}
logger.debug("result: " + result + " -- **fin**");
logger.debug("Salida invoke -- OK");
return result;
} catch (Exception ex) {
ex.printStackTrace();
return result;
}
}
/* (non-Javadoc)
* @see mx.com.bbva.bancomer.commons.business.BbvaIBusinessObject#createCommand(mx.com.bbva.bancomer.commons.model.dto.BbvaAbstractDataTransferObject)
*/
@Override
public <T extends BbvaAbstractDataTransferObject> T createCommand(
T bbvaAbstractDataTransferObject) {
logger.debug("Entrada createCommand -- OK");
logger.debug("Datos de Entrada createCommand -- " + bbvaAbstractDataTransferObject.toString());
UsuarioVO usuarioVO = ((UsuarioDTO) bbvaAbstractDataTransferObject).getUsuarioVO();
String userName = usuarioVO.getNombreUsuario();
int idPerfil = usuarioVO.getIdPerfil();
int idEstatus = usuarioVO.getEstatusUsuario();
usuarioVO = readCommand(usuarioVO);
((UsuarioDTO) bbvaAbstractDataTransferObject).getUsuarioVO().setIdPerfil(idPerfil);
((UsuarioDTO) bbvaAbstractDataTransferObject).getUsuarioVO().setNombreUsuario(userName);
((UsuarioDTO) bbvaAbstractDataTransferObject).getUsuarioVO().setEstatusUsuario(idEstatus);
if (usuarioVO == null) {
SqlSession session = MapeadorSessionFactory.getSqlSessionFactory().openSession();
MapUsuario mapUsuario = session.getMapper(MapUsuario.class);
try {
mapUsuario.crearUsuario(((UsuarioDTO) bbvaAbstractDataTransferObject).getUsuarioVO());
session.commit();
} catch (Exception ex) {
session.rollback();
ex.printStackTrace();
bbvaAbstractDataTransferObject.setErrorCode("SQL-001");
bbvaAbstractDataTransferObject.setErrorDescription(ex.getMessage());
} finally {
session.close();
}
logger.debug("Datos de Salida invoke -- " + bbvaAbstractDataTransferObject.toString());
logger.debug("Salida invoke -- OK");
} else {
bbvaAbstractDataTransferObject.setErrorCode("0001");
bbvaAbstractDataTransferObject.setErrorDescription("El usuario ya existe");
}
return bbvaAbstractDataTransferObject;
}
/** 回收SqlSession */
public static void closeSession() {
SqlSession session = (SqlSession) Constant.THREADLOCAL.get();
Constant.THREADLOCAL.set(null);
if (session != null) {
session.close();
}
}
@Test
public void testInserUser() throws ParseException {
// 通过sqlSessionFactory创建sqlSession
SqlSession sqlSession = sqlSessionFactory.openSession();
// 通过sqlSession操作数据库
// 插入对象
User user = new User();
user.setUsername("李泽贤");
user.setAddress("广西南宁");
user.setSex("男");
SimpleDateFormat date = new SimpleDateFormat("yyyy-MM-dd");
Date birthday = date.parse("1993-05-06");
user.setBirthday(birthday);
try {
// 第一个参数：statement位置，等于namespace+statement的id
// 第二个参数：传入的参数
sqlSession.insert("test.inserUser", user);
sqlSession.commit();
} catch (Exception e) {
// TODO: handle exception
e.printStackTrace();
} finally {
sqlSession.close();
}
System.out.println("返回的主键" + user.getId());
}
@Before
public void init() {
SqlSession session = MyBatisTestUtil.getSession();
Connection c = session.getConnection();
Statement s = null;
try {
s = c.createStatement();
s.execute("TRUNCATE TABLE entity");
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
if (s != null && !s.isClosed()) {
s.close();
}
if (c != null && c.isClosed()) {
c.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
if (session != null) {
session.close();
}
}
}
/** 测试新增 */
@Test
public void insert() {
SqlSession session = null;
try {
CalculationRecordModel model = new CalculationRecordModel();
model.setCreatedTime(new Date());
model.setCurrentNumber(1.0);
model.setPreNumber(2.0);
model.setOperator("*");
model.setResult(3.0);
model.setStatus(1);
model.setMacAddress("pc1");
session = sessionFactory.openSession();
CalculationDao calculationDao = session.getMapper(CalculationDao.class);
// calculationDao.insertCalculationRecord(model);
List<Integer> Ids = new ArrayList<Integer>();
Ids.add(3);
Ids.add(4);
// int[] Ids = {1,2};
List<CalculationRecordModel> modelList = calculationDao.getCalculationRecordByID(Ids);
for (CalculationRecordModel calculationRecordModel : modelList) {
System.out.println(calculationRecordModel);
}
session.commit();
} catch (Exception e) {
e.printStackTrace();
} finally {
session.close();
}
}
@Test
public void deleteUser() throws IOException {
InputStream inputStream = Resources.getResourceAsStream("SqlMapConfig.xml");
SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
SqlSession sqlSession = sessionFactory.openSession();
sqlSession.delete("test.deleteUserById", 26);
sqlSession.close();
}
protected void delete(String sqlKey, Object object) {
SqlSession session = sqlSessionFactory.openSession();
try {
session.delete(sqlKey, object);
} finally {
session.close();
}
}
protected Integer selectOneAsInt(String sqlKey) {
SqlSession session = sqlSessionFactory.openSession();
try {
return (Integer) session.selectOne(sqlKey);
} finally {
session.close();
}
}
