Shulou(Shulou.com)06/01 Report--

1. Query for dynamic SQL operations

The query condition is uncertain, and the SQL syntax needs to be generated according to the situation. This situation is called dynamic SQL.

Select emp_id,emp_name,emp_sal from emp and emp_id = # {pid} and emp_name like # {pname} and emp_sal

< #{psal} 2、动态SQL操作之更新 更新条件不确定，需要根据情况产生SQL语法，这种情况叫动态SQL update emp emp_name = #{pname}, emp_sal = #{psal}, where emp_id = #{pid} 3、动态SQL操作之删除 根据多个id进行删除 delete from emp where emp_id in #{pid} delete from emp where emp_id in #{pid} 4、动态SQL操作之添加 根据条件，添加数据 emp_id, emp_name, emp_sal, #{id}, #{name}, #{sal}, insert into emp() values() 5、参考代码 准备SQL CREATE TABLE Emp( emp_id INT(5) PRIMARY KEY, emp_name VARCHAR(20), emp_sal DOUBLE(8,2)); Emp.java package com.rk.entity;public class Emp { private Integer id; private String name; private Double sal; public Emp(){} public Emp(Integer id, String name, Double sal) { this.id = id; this.name = name; this.sal = sal; } public Integer getId() { return id; } public void setId(Integer id) { this.id = id; } public String getName() { return name; } public void setName(String name) { this.name = name; } public Double getSal() { return sal; } public void setSal(Double sal) { this.sal = sal; } } EmpMapper.xml select emp_id,emp_name,emp_sal from emp and emp_id = #{pid} and emp_name like #{pname} and emp_sal < #{psal} update emp emp_name = #{pname}, emp_sal = #{psal}, where emp_id = #{pid} delete from emp where emp_id in #{pid} delete from emp where emp_id in #{pid} emp_id, emp_name, emp_sal, #{id}, #{name}, #{sal}, insert into emp() values() mybatis.xml db.properties mysql.driver=com.mysql.jdbc.Drivermysql.url=jdbc:mysql://127.0.0.1:3306/testdbmysql.username=rootmysql.password=rootoracle.driver=oracle.jdbc.driver.OracleDriveroracle.url=jdbc:oracle:thin:@127.0.0.1:1521:orcloracle.username=scottoracle.password=tiger MyBatisUtils.java package com.rk.utils;import java.io.IOException;import java.io.Reader;import java.sql.Connection;import org.apache.ibatis.io.Resources;import org.apache.ibatis.session.SqlSession;import org.apache.ibatis.session.SqlSessionFactory;import org.apache.ibatis.session.SqlSessionFactoryBuilder;public class MyBatisUtils { private static ThreadLocal threadLocal = new ThreadLocal(); private static SqlSessionFactory sqlSessionFactory; static{ try { Reader reader = Resources.getResourceAsReader("mybatis.xml"); sqlSessionFactory = new SqlSessionFactoryBuilder().build(reader); } catch (IOException e) { e.printStackTrace(); throw new RuntimeException(e); } } private MyBatisUtils() {} public static SqlSession getSqlSession(){ SqlSession sqlSession = threadLocal.get(); if(sqlSession == null){ sqlSession = sqlSessionFactory.openSession(); threadLocal.set(sqlSession); } return sqlSession; } public static void closeSqlSession(){ SqlSession sqlSession = threadLocal.get(); if(sqlSession != null){ sqlSession.close(); threadLocal.remove(); } } public static void main(String[] args) { Connection conn = MyBatisUtils.getSqlSession().getConnection(); System.out.println(conn!=null ? "连接成功" : "连接失败"); }} EmpDao.java package com.rk.dao;import java.util.ArrayList;import java.util.HashMap;import java.util.List;import java.util.Map;import org.apache.ibatis.session.SqlSession;import org.junit.Test;import com.rk.entity.Emp;import com.rk.utils.MyBatisUtils;public class EmpDao { /** * 动态SQL之SELECT */ public List dynamicFind(Integer id,String name,Double sal){ SqlSession sqlSession = null; try { Map map = new HashMap(); map.put("pid", id); map.put("pname", name != null ? "%"+name+"%" : null); map.put("psal", sal); sqlSession = MyBatisUtils.getSqlSession(); return sqlSession.selectList(Emp.class.getName() + ".dynamicFind", map); } catch (Exception e) { e.printStackTrace(); sqlSession.rollback(); } finally{ MyBatisUtils.closeSqlSession(); } return null; } @Test public void testDynamicFind(){ EmpDao dao = new EmpDao(); //List list = dao.dynamicFind(1,null, null); //List list = dao.dynamicFind(null,"tom", null); //List list = dao.dynamicFind(null,null, 350D); List list = dao.dynamicFind(null,"tom", 350D);// List list = dao.dynamicFind(0,"tom", 25D); if(list != null && list.size()>

0) {for (Emp emp: list) {System.out.println (emp.getName () + ":" + emp.getSal ());}} / * * UPDATE * / public int dynamicUpdate (Integer id,String name,Double sal) of dynamic SQL {SqlSession sqlSession = null; try {Map map = new HashMap () Map.put ("pid", id); map.put ("pname", name); map.put ("psal", sal); sqlSession = MyBatisUtils.getSqlSession (); int i=sqlSession.update (Emp.class.getName () + ".dynamicUpdate", map); sqlSession.commit (); return I } catch (Exception e) {e.printStackTrace (); sqlSession.rollback ();} finally {MyBatisUtils.closeSqlSession ();} return 0;} @ Test public void testDynamicUpdate () {int I = dynamicUpdate (3, "Lucy", null) System.out.println ("this operation affects" + I + "row data"); DELETE (array version) / public int dynamicDeleteArray (int...) of dynamic SQL Ids) {SqlSession sqlSession = null; try {sqlSession = MyBatisUtils.getSqlSession (); int i=sqlSession.update (Emp.class.getName () + ".dynamicDeleteArray", ids); sqlSession.commit (); return I;} catch (Exception e) {e.printStackTrace (); sqlSession.rollback () } finally {MyBatisUtils.closeSqlSession ();} return 0;} @ Test public void testDynamicDeleteArray () {int I = dynamicDeleteArray (3); System.out.println ("this operation affects" + I + "row data") DELETE (Collection version) * / public int dynamicDeleteList (List ids) of dynamic SQL {SqlSession sqlSession = null; try {sqlSession = MyBatisUtils.getSqlSession (); int i=sqlSession.update (Emp.class.getName () + ".dynamicDeleteList", ids); sqlSession.commit (); return I } catch (Exception e) {e.printStackTrace (); sqlSession.rollback ();} finally {MyBatisUtils.closeSqlSession ();} return 0;} @ Test public void testDynamicDeleteList () {List ids = new ArrayList (); ids.add (1); ids.add (4) Int I = dynamicDeleteList (ids); System.out.println ("this operation affects" + I + "row data"); INSERT * / public int dynamicInsert (Emp emp) of dynamic SQL {SqlSession sqlSession = null; try {sqlSession = MyBatisUtils.getSqlSession (); int i=sqlSession.insert (Emp.class.getName () + ".dynamicInsert", emp) SqlSession.commit (); return I;} catch (Exception e) {e.printStackTrace (); sqlSession.rollback ();} finally {MyBatisUtils.closeSqlSession ();} return 0;} @ Test public void testDynamicInsert () {Emp emp = new Emp (1, null, 30D) Int I = dynamicInsert (emp); System.out.println ("this operation affects" + I + "row data");}}

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.