mybatisday2

mybatisday2

1.动态修改

在映射文件中配置

 <!--动态修改-->
    <update id="update" parameterType="Student">
        UPDATE t_student
        <set>
            <if test="name!=null and name!=''">
                name=#{name}
            </if>
        </set>
        <set>
            <if test="age!=null">
                age=#{age}
            </if>
        </set>
        <set>
            <if test="email!=null and email!=''">
                email=#{email}
            </if>
        </set>
        <set>
            <if test="sex!=null">
                sex=#{sex}
            </if>
        </set>
         WHERE id=#{id}
    </update>

2.#和$符合的区别

 <!--
         #与$符号的区别：
                共同点： #和$都是用来取值的
                参数传递普通类型(8大基本数据类型8大包装类。再加一个String)
                        #:  正常发送的sql语句   SELECT * FROM t_student WHERE id=?
                        $:  传递普通类型，它是要报错的，取不到值
                参数传递对象
                        #:  正常发送的sql语句   SELECT * FROM t_student WHERE id=?
                        $:  正常发送sql语句     SELECT * FROM t_student WHERE id=2

                区别：
                    1.
                        #它可以取任意类型的参数
                        $只能取对象中的值，不能取普通类型的值
                    2.  #取值可以有效防止sql注入  ，$符号取值它是sql拼接，不能有效防止sql注入

                    3.  #取值让sql语句支持预编译的功能，而$符号是不支持的，所以在性能上来说#取值性能要高于$符号

                    4. 使用#一般是用来取值的，而$符号一般用于动态排序


    -->

    <!--动态排序，根据需要排序的条件来进行排序-->
    <select id="selectByObj" resultType="Student">
        SELECT * FROM t_student WHERE id=#{id} order by
    </select>
    <!--需要按什么条件，就根据什么条件来进行排序-->
    <select id="selectByQuery" resultType="Student">
        SELECT * FROM t_student
        <if test="orderByName!=null and orderByName!=''">
            ORDER BY ${orderByName} ${orderByType}
        </if>
    </select>

3.多对一保存

<insert id="save" parameterType="Employee" useGeneratedKeys="true"
    keyColumn="id" keyProperty="id">
        INSERT INTO t_employee(name,age,dept_id)
        VALUES (#{name},#{age},#{dept.id})
    </insert>

测试

 @Test
    public void save() throws Exception {
        SqlSession sqlSession = MybatisUtil.openSession();
        EmployeeMapper employeeMapper = sqlSession.getMapper(EmployeeMapper.class);
        DeptMapper deptMapper = sqlSession.getMapper(DeptMapper.class);
        Dept dept = new Dept();
        dept.setName("瓜子二手车部");
        deptMapper.save(dept);
        Employee employee = new Employee();
        employee.setName("铁锤");
        employee.setAge(20);
        employee.setDept(dept);
        employeeMapper.save(employee);
        sqlSession.commit();
    }

3.1多对一查询方式一:关联查询

<resultMap id="employeeResultMap" type="com.yyk.many2one.domain.Employee">
        <!--自定义映射后，会默认把以前的数据干掉，所以要在自定义里面显示-->
        <id column="id" property="id"></id>
        <result column="name" property="name"></result>
        <result column="age" property="age"></result>
        <!--
            映射关联对象 association
            property="dept"  映射Employee中的dept属性
            javaType="cn.itsource._02many2one.domain.Dept  dept属性对应的类型

            注意：当你使用了association映射之后，默认映射规则失效
        -->
        <association property="dept" javaType="com.yyk.many2one.domain.Dept">
        <id column="did" property="id"/>
        <result column="dname" property="name"/>
        </association>
    </resultMap>

 <select id="findAll" resultMap="employeeResultMap">
        SELECT e.id,e.name,e.age,e.dept_id,d.id did,d.name dname FROM t_employee e JOIN t_dept d on e.dept_id = d.id
    </select>

dao

/**
     * 关联查询
     */
    List<Employee> findAll();

3.2多对一查询方式一:子查询

 <!--子查询-->
    <resultMap id="employeeResultMap2" type="com.yyk.many2one.domain.Employee">
        <id column="id" property="id"></id>
        <result column="name" property="name"></result>
        <result column="age" property="age"></result>
        <!--
        把dept_id这一列对应的值，到DeptMapper中findone该sql语句中进行查询，把查询出来的对象
        封装到Employee中的dept属性中-->
        <association property="dept" column="dept_id"
                     select="com.yyk.many2one.mapper.DeptMapper.findOne"/>

    </resultMap>

    <select id="findAll2" resultMap="employeeResultMap2">
        SELECT * from t_employee
    </select>

在dept的映射xml中

 <select id="findOne" resultType="Dept">
        SELECT * FROM t_dept WHERE id=#{id}
    </select>

dao

List<Employee> findAll2();

4.一对多的保存

有集合字段的xml

<insert id="save"  useGeneratedKeys="true"
    keyColumn="id" keyProperty="id">
        INSERT INTO t_dept(name)
        VALUES (#{name})
    </insert>

没有集合字段的xml

<insert id="save" >
        INSERT INTO t_employee(name,age,dept_id)
        VALUES (#{e.name},#{e.age},#{deptId})
    </insert>

没有结合字段的dao

/**
     *
     *  employee 员工对象
     *  deptId  部门id
     *  @param取一个别名
     */
    void save(@Param("e") Employee employee, @Param("deptId") Long deptId);

4.1一对多查询方式一:关联查询

resultMap id="deptResultMap" type="com.yyk._03one2many.domain.Dept">
        <id column="id" property="id"/>
        <result column="name" property="name"/>
        <!--
            映射集合要使用collection
            注意：使用了collection映射之后，默认映射规则失效
        -->
        <collection property="employees" ofType="com.yyk._03one2many.domain.Employee">
            <id column="eid" property="id"/>
            <result column="ename" property="name"/>
            <result column="eage" property="age"/>
        </collection>
    </resultMap>
    <!--一对多关联查询
    这里必须进行排序，因为如果有重复的，就会出错，这个是mybatis的底层bug造成的
    -->
    <select id="findAll" resultMap="deptResultMap">
        SELECT d.id,d.name,e.id eid,e.name ename,e.age eage FROM t_dept d JOIN t_employee e ON d.id = e.dept_id ORDER BY d.id
    </select>

dao

 List<Dept> findAll();

4.2一对多查询方式一:子查询

 <!--子查询-->
    <resultMap id="deptResultMap2" type="com.yyk._03one2many.domain.Dept">
        <id column="id" property="id"/>
        <result column="name" property="name"/>

        <collection property="employees" ofType="com.yyk._03one2many.domain.Employee" column="id" select="com.yyk._03one2many.mapper.EmployeeMapper.findOne"/>

    </resultMap>
    <select id="findAll2" resultMap="deptResultMap2">
        SELECT * FROM t_dept
    </select>

没有集合字段的xml

 <select id="findOne" resultType="Employee2">
        SELECT * FROM t_employee WHERE dept_id=#{deptId}
    </select>

没有集合字段的dao

/**
     * 根据部门id查询所对应的员工
     */
    List<Employee> findOne(Long deptId);

5.ssm框架集成

5.1导包

asm-3.3.1.jar
cglib-2.2.2.jar
com.springsource.org.aopalliance-1.0.0.jar
com.springsource.org.apache.commons.dbcp-1.2.2.osgi.jar
com.springsource.org.apache.commons.logging-1.1.1.jar
com.springsource.org.apache.commons.pool-1.3.0.jar
com.springsource.org.aspectj.weaver-1.6.8.RELEASE.jar
commons-logging-1.1.1.jar
javassist-3.17.1-GA.jar
log4j-1.2.17.jar
mybatis-3.2.1.jar
mybatis-spring-1.2.0.jar
mysql-connector-java-5.1.26-bin.jar
slf4j-api-1.7.2.jar
slf4j-log4j12-1.7.2.jar
spring-aop-4.1.2.RELEASE.jar
spring-aspects-4.1.2.RELEASE.jar
spring-beans-4.1.2.RELEASE.jar
spring-context-4.1.2.RELEASE.jar
spring-core-4.1.2.RELEASE.jar
spring-expression-4.1.2.RELEASE.jar
spring-jdbc-4.1.2.RELEASE.jar
spring-orm-4.1.2.RELEASE.jar
spring-test-4.1.2.RELEASE.jar
spring-tx-4.1.2.RELEASE.jar
spring-web-4.1.2.RELEASE.jar
spring-webmvc-4.1.2.RELEASE.jar

5.2配置jdbc.properties

jdbc.username=root
jdbc.password=123456
jdbc.url=jdbc:mysql:///ssm?createDatabaseIfNotExist=true
jdbc.driverClassName=com.mysql.jdbc.Driver

5.3配置dataSource

在脑壳顶部记得引入一个context

<!--引入jdbc.properties  注意:必需写classpath-->
    <context:property-placeholder location="classpath:jdbc.properties" />
    <!--配置dataSource-->
    <bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
        <!--配置连接数据库的四大金刚 -->
        <property name="username" value="root"/>
        <property name="password" value="123456"/>
        <property name="url" value="jdbc:mysql:///ssm?createDatabaseIfNotExist=true"/>
        <property name="driverClassName" value="com.mysql.jdbc.Driver"/>
    </bean>

测试

@Autowired
    private DataSource dataSource;

    @Test
    public void test() throws Exception{
        System.out.println(dataSource.getConnection());
    }

成功后

5.4配置sqlsessionFactory

 <!--配置sqlSessionFactory-->
    <bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
        <property name="dataSource" ref="dataSource"/>
        <!--加载所有的mapper.xml文件-->
        <property name="mapperLocations" value="classpath:cn/itsource/ssm/mapper/*.xml"/>
        <!--定义公共的基础包-->
        <property name="typeAliasesPackage">
            <value>
                com.yyk.ssm.query
                com.yyk.ssm.domain
            </value>
        </property>
    </bean>

5.5配置dao

方式一

太麻烦，不推荐使用

<bean id="employeeMapper" class="org.mybatis.spring.mapper.MapperFactoryBean">
        <property name="sqlSessionFactory" ref="sqlSessionFactory"/>
        <property name="mapperInterface" value="com.yyk.ssm.mapper.EmployeeMapper"/>
    </bean>

方式二，推荐使用

<!--方式二 扫描器的配置-->
    <bean class="org.mybatis.spring.mapper.MapperScannerConfigurer">
        <!--只要扫描到该包下所有的接口，我都使用代理模式进行实现-->
        <property name="basePackage" value="com.yyk.ssm.mapper"/>
    </bean>

5.6配置service

public interface IEmployeeService {
    void save(Employee employee);
    void delete(Long id);
    void update(Employee employee);

    List<Employee> selectAll();
    Employee selectById(Long id);
}

实现类impl

@Service
@Transactional(readOnly = true,propagation = Propagation.SUPPORTS)
public class EmployeeService implements IEmployeeService {
    @Autowired
    private EmployeeMapper employeeMapper;
    @Override
    @Transactional
    public void save(Employee employee) {
        employeeMapper.save(employee);
    }

    @Override
    @Transactional
    public void delete(Long id) {

    }

    @Override
    @Transactional
    public void update(Employee employee) {

    }

    @Override
    public List<Employee> selectAll() {
        return employeeMapper.selectAll();
    }

    @Override
    public Employee selectById(Long id) {
        return null;
    }

记得在脑壳上配置 tx，和上面的context配置一致

<!--配置事务管理器-->
    <bean id="transactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
        <property name="dataSource" ref="dataSource"/>
    </bean>
    <!-- 开启事务注解的支持-->
    <tx:annotation-driven/>

5.7controller

@Controller
@RequestMapping("/employee")
public class EmployeeController {
    @Autowired
    private IEmployeeService employeeService;
    public String index(Model model){
        model.addAttribute("emps",employeeService.selectAll());
        return "employee/employee";
    }
}

配置spring-mvc

添加一个mvc的头在上面，可以直接复制spring的过来，然后把所有的tx替换为mvc

 <!-- 扫描controller层-->
    <context:component-scan base-package="com.yyk.ssm.web.controller"/>

    <!--开启注解-->
    <mvc:annotation-driven/>

    <mvc:default-servlet-handler/>

    <!--视图解析器-->
    <bean class="org.springframework.web.servlet.view.InternalResourceViewResolver">
        <property name="suffix" value=".jsp"/>
        <property name="prefix" value="/WEB-INF/views/"/>
    </bean>

配置web.xml

第一步

<listener>
        <listener-class>org.springframework.web.context.ContextLoaderListener</listener-class>
    </listener>

第二步

<!--在指定的位置加装applicationContext.xml文件-->
    <context-param>
        <param-name>contextConfigLocation</param-name>
        <param-value>classpath:applicationContext.xml</param-value>
    </context-param>

第三步

 <servlet>
        <!--启动springMvc容器-->
        <servlet-name>springMvc</servlet-name>
        <servlet-class>org.springframework.web.servlet.DispatcherServlet</servlet-class>
        <init-param>
            <param-name>contextConfigLocation</param-name>
            <param-value>classpath:applicationContext-mvc.xml</param-value>
        </init-param>
        <load-on-startup>1</load-on-startup>
    </servlet>
    <servlet-mapping>
        <servlet-name>springMvc</servlet-name>
        <url-pattern>/</url-pattern>
    </servlet-mapping>

第四步

 <filter>
        <!--解决post提交乱码问题-->
        <filter-name>CharacterEncodingFilter</filter-name>
        <filter-class>org.springframework.web.filter.CharacterEncodingFilter</filter-class>
        <init-param>
            <param-name>encoding</param-name>
            <param-value>UTF-8</param-value>
        </init-param>
        <init-param>
            <param-name>forceEncoding</param-name>
            <param-value>true</param-value>
        </init-param>
    </filter>
    <filter-mapping>
        <filter-name>CharacterEncodingFilter</filter-name>
        <url-pattern>/*</url-pattern>
    </filter-mapping>

5.8创建一个jsp页面测试

<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<html>
<head>
    <title>Title</title>
</head>
<body>
${emps}
</body>
</html>