Spring和数据库编程

学习日志：资料来自杨开振的《JavaEE 互联网轻量级框架整合开发》

1、配置数据库资源

• 通过Spring内部提供的类
• 使用第三方数据库连接池
• 从Web服务器中通过JNDI获取数据源

一般会采用xml配置，或者以注解。主要以xml

1.1 使用简单数据库配置

** 代码清单 12-2：配置SimpleDriverDtaSource **

  <bean id="dataSource" class="org.springframework.jdbc.datasource.SimpleDriverDataSource">
        <property name="username" value="root"/>
        <property name="password" value="******"/>
        <property name="driverClass" value="com.mysql.cj.jdbc.Driver"/>
        <property name="url" value="jdbc:mysql://localhost:3306/ssm"/>
    </bean>

1.2 使用第三方数据库连接池

     <bean id="dataSource" class="org.apache.commons.dbcp2.BasicDataSource">
        <property name="username" value="root"/>
        <property name="password" value="*"/>
        <property name="driverClassName" value="com.mysql.cj.jdbc.Driver"/>
        <property name="url" value="jdbc:mysql://localhost:3306/ssm"/>
        <property name="maxIdle" value="5"/>
        <property name="maxWaitMillis" value="1000"/>
        <property name="maxTotal" value="255"/>
    </bean>

1.3 使用JNDI数据库连接池

<bean id="dataSource" class="org.springframework.jndi.JndiObjectFactoryBean">
        <property name="jndiName" value="java:comp/env/jdbc/ssm"/>
    </bean>

2 JdbcTemplate

<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
        <property name="dataSource" ref="dataSource"/>
</bean>

package com.learn.ssm.chapter12;

import com.learn.ssm.pojo.Role;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;

import java.sql.ResultSet;
import java.sql.SQLException;

public class Main {
    ApplicationContext context = new ClassPathXmlApplicationContext("com/learn/ssm/chapter12/spring-cfg.xml");
    JdbcTemplate jdbcTemplate = context.getBean(JdbcTemplate.class);
    int id = 1;
    String sql = "select id,role_name,note from t_note where id = " + id;
    Role role = jdbcTemplate.queryForObject(sql, new RowMapper<Role>() {
        @Override
        public Role mapRow(ResultSet resultSet, int i) throws SQLException {
            Role result = new Role();
            while (resultSet.next()) {
                result.setId(resultSet.getInt("id"));
                result.setRoleName(resultSet.getString("role_name"));
                result.setNote(resultSet.getString("note"));
            }
            return result;
        }
    });
}

2.1 JdbcTemplate的增删改查

package com.learn.ssm.chapter12;

import com.learn.ssm.chapter12.JdbcTemplate.impl.JdbcTemplateTest;
import com.learn.ssm.pojo.Role;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import org.springframework.jdbc.core.JdbcTemplate;

import java.util.List;

public class Main {
   public static void main(String[] args){
       ApplicationContext context = new ClassPathXmlApplicationContext("com/learn/ssm/chapter12/spring-cfg.xml");
       JdbcTemplate jdbcTemplate = context.getBean(JdbcTemplate.class);
       JdbcTemplateTest jdbcTemplateTest = new JdbcTemplateTest();
       Role role = new Role(31,"role_name_100","role_note_100");
       jdbcTemplateTest.insertRole(jdbcTemplate, role);
       List<Role> list = jdbcTemplateTest.findRole(jdbcTemplate, "role");
       jdbcTemplateTest.updateRole(jdbcTemplate, role);
       jdbcTemplateTest.deleteRole(jdbcTemplate, 31);
   }
}

---

package com.learn.ssm.chapter12.JdbcTemplate.impl;

import com.learn.ssm.pojo.Role;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

public class JdbcTemplateTest {

    public int insertRole(JdbcTemplate jdbcTemplate, Role role){
        String sql = "insert into t_role(role_name, note) values(?,?)";
        Object[] params = {
                role.getRoleName(),
                role.getNote()
        };
        return jdbcTemplate.update(sql,params);
    }

    public int deleteRole(JdbcTemplate jdbcTemplate, int id){
        String sql = "delete from t_role where id = ?";
        return jdbcTemplate.update(sql, id);
    }

    public int updateRole(JdbcTemplate jdbcTemplate, Role role){
        String sql = "update t_role set role_name = ?, note = ? where id = ?";
        Object[] params = {
                role.getRoleName(),
                role.getNote(),
                role.getId()
        };
        return jdbcTemplate.update(sql, params);
    }

    public List<Role> findRole(JdbcTemplate jdbcTemplate, String roleName){
        String sql = "select id, role_name, note from t_role where role_name like concat('%',?,'%')";
        Object[] params = {
                roleName
        };
        List<Role> list = jdbcTemplate.query(sql, params, new RowMapper<Role>() {
            @Override
            public Role mapRow(ResultSet resultSet, int i) throws SQLException {
                Role result = new Role();
                result.setId(resultSet.getInt("id"));
                result.setNote(resultSet.getString("note"));
                result.setRoleName(resultSet.getString("role_name"));
                return result;
            }
        });
        return list;
    }
}

2.2 执行多条SQL

    public Role getRoleByConnectionCallBack(JdbcTemplate jdbcTemplate, int id){
        Role role = null;
        role = jdbcTemplate.execute(new ConnectionCallback<Role>() {
            @Override
            public Role doInConnection(Connection connection) throws SQLException, DataAccessException {
                Role result = null;
                String sql = "select id, role_name, note from t_role where id = ?";
                PreparedStatement ps = connection.prepareStatement(sql);
                ps.setInt(1,id);
                ResultSet rs = ps.executeQuery();
                while(rs.next()){
                    result = new Role();
                    result.setId(rs.getInt("id"));
                    result.setRoleName(rs.getString("role_name"));
                    result.setNote(rs.getString("note"));
                }
                return result;
            }
        });
        return role;
    }

    public Role getRoleByStatementCallBack(JdbcTemplate jdbcTemplate, int id){
        Role role = null;
        role = jdbcTemplate.execute(new StatementCallback<Role>() {
            @Override
            public Role doInStatement(Statement statement) throws SQLException, DataAccessException {
                Role result = null;
                String sql = "select id, role_name, note from t_role where id = "+ id;
                ResultSet rs = statement.executeQuery(sql);
                while(rs.next()){
                    result = new Role(rs.getInt("id"),rs.getString("role_name"),rs.getString("note"));
                }
                return result;
            }
        });
        return role;
    }

3 Myabtis-Spring 项目

配置Mybatis项目需要这么几步：

• 配置数据源
• 配置SqlSessionFactory
• 可以选择的配置有SqlSessionTemplate，在同时配置SqlSessionTemplate和SqlSessionFactory，优先采用SqlSessionTemplate
• 配置Mapper，可以配置单个Mapper，也可以通过扫描的方法生成Mapper
• 事务管理

---

3.1 配置SqlSesionFactoryBean

从Mybtais的介绍中，可以知道SqlSessionFactory是产生SqlSession的基础。

<bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
        <property name="dataSource" ref="dataSource"/>
        <property name="configLocation" value="classpath:com/learn/ssm/chapter12/sqlMapConfig.xml"/>
</bean>
    

这里配置了SqlSessionFactoryBean，但是只配置了数据源，然后引入一个Mybtais的配置文件。

<?xml version="1.0" encoding="utf-8" ?>
<!DOCTYPE configuration
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
    <settings>
        <setting name="cacheEnabled" value="true"/>
        <setting name="useGeneratedKeys" value="true"/>
        <setting name="defaultExecutorType" value="REUSE"/>
        <setting name="lazyLoadingEnabled" value="true"/>
        <setting name="defaultStatementTimeout" value="25000"/>
    </settings>
    <typeAliases>
        <typeAlias type="com.learn.ssm.pojo.Role" alias="role"/>
    </typeAliases>
    <mappers>
        <mapper resource="com/ssm/learn/chapter12/mapper/RoleMapper.xml"/>
    </mappers>
</configuration>

<?xml version="1.0" encoding="UTF-8" ?>
<beans xmlns="http://www.springframework.org/schema/beans"
       xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
       xmlns:context="http://www.springframework.org/schema/context"
       xmlns:aop="http://www.springframework.org/schema/aop"
       xsi:schemaLocation="http://www.springframework.org/schema/beans
       http://www.springframework.org/schema/beans/spring-beans.xsd
       http://www.springframework.org/schema/context
       http://www.springframework.org/schema/context/spring-context.xsd
       http://www.springframework.org/schema/aop
       http://www.springframework.org/schema/aop/spring-aop.xsd">


    <bean id="dataSource" class="org.apache.commons.dbcp2.BasicDataSource">
        <property name="username" value="root"/>
        <property name="password" value=""/>
        <property name="driverClassName" value="com.mysql.cj.jdbc.Driver"/>
        <property name="url" value="jdbc:mysql://localhost:3306/ssm"/>
        <property name="maxIdle" value="5"/>
        <property name="maxWaitMillis" value="1000"/>
        <property name="maxTotal" value="255"/>
    </bean>

    <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
        <property name="dataSource" ref="dataSource"/>
    </bean>

    <bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
        <property name="dataSource" ref="dataSource"/>
        <property name="configLocation" value="classpath:com/learn/ssm/chapter12/sqlMapConfig.xml"/>
    </bean>
</beans>

3.2 配置SqlSessionTemplate组件

  <bean id="sqlSessionTemplate" class="org.mybatis.spring.SqlSessionTemplate">
        <constructor-arg ref="sqlSessionFactory"/>
        <constructor-arg value="BATCH"/>
    </bean>

配置好了SqlSessionTemplate就可以使用了，比如：

package com.learn.ssm.chapter12;

import com.learn.ssm.pojo.Role;
import org.mybatis.spring.SqlSessionTemplate;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;

public class Main {
    public static void main(String[] args) {
        ApplicationContext context = new ClassPathXmlApplicationContext("com/learn/ssm/chapter12/spring-cfg.xml");
        SqlSessionTemplate sqlSessionTemplate = context.getBean(SqlSessionTemplate.class);
        Role role = new Role();
        role.setRoleName("role_name_sqlSessionTemplate");
        role.setNote("role_note_sqlSessionTemplate");
        sqlSessionTemplate.insert("com.learn.ssm.chapter12.mapper.RoleMapper.insertRole",role);
        int id = role.getId();
        Role role1 = sqlSessionTemplate.selectOne("com.learn.ssm.chapter12.mapper.RoleMapper.getRole",id);
        role.setNote("update_sqlSessionTemplate");
        sqlSessionTemplate.update("com.learn.ssm.chapter12.mapper.RoleMapper.updateRole",role);
        sqlSessionTemplate.delete("com.learn.ssm.chapter12.mapper.RoleMapper.deleteRole",id);
    }
}

3.3 配置MapperFactoryBean

    <bean id="roleMapper" class="org.mybatis.spring.mapper.MapperFactoryBean">
        <property name="mapperInterface" value="com.learn.ssm.chapter12.mapper.RoleMapper"/>
        <property name="sqlSessionFactory" ref="sqlSessionFactory"/>
        <!--如果同时注入sqlSesionTemplate和sqhSessionTemplate，则只会启动sqlSessionTemplate-->
        <!--<property name="sqlSessionTemplate" ref="sqlSessionTemplate"/>-->
    </bean>

这里可以看到MapperFactoryBean存在3个属性可以配置，分别是mapperInterface、sqlSessionTemplate和SqlSessionFactory，其中：

• mapperInterfact 是映射器的接口
• 如果同时配置sqlsessionTemplate和sqlSessionFactory，那么则会启动sqlSessionTemplate，当我们配置这样的一个Bean，就可以通过下面的代码去获取映射器:

    RoleMapper roleMapper = context.getBean(RoleMapper.class);

3.4 配置MapperScannerConfigurer

这是一个通过扫描的形式进行配置Mapper的类，如果一个个去配置Mapper，显然工作量大，并且导致配置泛滥，有了它只需要给予一些简单的配置，它就能生成大量mapper，从而减少工作量。
对于MapperScannerConfigurer主要配置有以下几个:

• basePackage,指定让Spring自动扫描什么包，它会逐层深入扫描，如果遇到多个包可以使用半角逗号分隔
• annotationClass，表示如果类被这个注解表示的时候，才进行扫描。对于开发而言，建议使用这个方式进行注册对应的Mapper。在Spring中往往使用注解@Repository表示数据访问层（DAO，Data Access Object），所以本书的例子也是以此方式为主进行介绍的。
• SqlSessionFactoryBeanName,指定在Spring中定义SqlSessionFactory的Bean名称。如果sqlSessionTemplateBeanName被定义，则失效
• markerInterface，指定实现了什么接口就认为它是mapper
  在Spring配置之前要给Mpaper一个注解，在Spring中往往采用@Repository表示DAO层。

package com.learn.ssm.chapter12.mapper;

import com.learn.ssm.pojo.Role;
import org.springframework.stereotype.Repository;

@Repository
public interface RoleMapper {
    public int insertRole(Role role);

    public int deleteRole(int id);

    public int updateRole(Role role);

    public Role getRole(int id);
}

@Repository标志了这是一个DAO层，我们还要告诉Spring扫描哪个包，这样就可能扫出对应的Mapper到Spring IoC容器中。

   <bean class="org.mybatis.spring.mapper.MapperScannerConfigurer">
       <property name="basePackage" value="com.learn.ssm.chapter12.mapper"/>
       <property name="sqlSessionFactoryBeanName" value="sqlSessionFactory"/>
       <property name="annotationClass" value="org.springframework.stereotype.Repository"/>
   </bean>

package com.learn.ssm.chapter12.mapper;

import com.learn.ssm.chapter12.base.BaseMapper;
import com.learn.ssm.pojo.Role;
import org.springframework.stereotype.Repository;

@Repository
public interface RoleMapper {
    public int insertRole(Role role);

    public int deleteRole(int id);

    public int updateRole(Role role);

    public Role getRole(int id);
}

3.5 测试

package com.learn.ssm.chapter12;

import com.learn.ssm.chapter12.mapper.RoleMapper;
import com.learn.ssm.pojo.Role;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;

public class Main {
    public static void main(String[] args) {
        ApplicationContext context = new ClassPathXmlApplicationContext("com/learn/ssm/chapter12/spring-cfg.xml");
        RoleMapper roleMapper = context.getBean(RoleMapper.class);
        Role role = new Role();
        role.setRoleName("role_name_mapper");
        role.setNote("note_mapper");
        roleMapper.insertRole(role);
        int id = role.getId();
        roleMapper.getRole(id);
        role.setNote("note_mapper_update");
        roleMapper.updateRole(role);
        roleMapper.deleteRole(id);
    }
}

小结