一、知识点回顾
1、Mybatis环境搭建(DAO层的实现)(使用maven项目管理工具)
需要引入的依赖包:
<!-- 单元测试junit --> <dependency> <groupId>junit</groupId> <artifactId>junit</artifactId> <version>4.3</version> <scope>test</scope> </dependency> <!-- log4j --> <dependency> <groupId>log4j</groupId> <artifactId>log4j</artifactId> <version>1.2.17</version> </dependency> <!-- mybatis --> <dependency> <groupId>org.mybatis</groupId> <artifactId>mybatis</artifactId> <version>3.2.2</version> </dependency> <!-- mysql-connector-java --> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <version>5.1.43</version> </dependency>
核心配置文件:configuration.xml
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
<?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> <properties resource="database.properties"></properties> <typeAliases> <package name="cn.tengyu.entity"/> </typeAliases> <!--数据库的连接信息--> <environments default="development"> <environment id="development"> <transactionManager type="JDBC"/> <dataSource type="POOLED"> <!--数据库连接属性--> <property name="driver" value="${jdbc.driver}"/> <property name="url" value="${jdbc.url}"/> <property name="username" value="${jdbc.username}"/> <property name="password" value="${jdbc.password}"/> </dataSource> </environment> </environments> <mappers> <package name="cn.tengyu.dao"/> </mappers> </configuration>
Mapper.xml
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
<?xml version="1.0" encoding="UTF-8"?> <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd"> <mapper namespace="cn.tengyu.dao.DeptMapper"> <!--查询记录数--> <select id="count" resultType="int"> SELECT count(1) as count FROM dept </select> <select id="findAll" resultType="Dept"> SELECT * FROM dept </select> <select id="findByName" resultType="Dept" parameterType="String"> SELECT * FROM dept WHERE deptname=#{deptname} </select> <!--查询某部门员工信息--> <resultMap id="empList" type="Emp"> <id property="empno" column="empno"/> </resultMap> <select id="findByDeptName" parameterType="String" resultMap="empList"> SELECT emp.* FROM emp,dept WHERE dept.deptno=emp.deptno AND deptname like concat ('%',#{detname},'%') </select> <!--添加修改部门信息--> <insert id="addDeptByName" parameterType="string"> INSERT INTO dept(deptname)VALUES (#{deptname}) </insert> <insert id="addDept" parameterType="Dept"> INSERT INTO dept(deptno,deptname)VALUES (#{deptno},#{deptname}) </insert> <!--删除部门信息--> <delete id="delDept" parameterType="int"> DELETE FROM dept WHERE deptno=#{deptno} </delete> <!--修改数据--> <update id="modifyDept" parameterType="string"> UPDATE dept SET deptname=#{newdeptname} WHERE deptname=#{deptname} </update> </mapper>
MybatisUtil.java
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
import org.apache.ibatis.io.Resources; import org.apache.ibatis.session.SqlSession; import org.apache.ibatis.session.SqlSessionFactory; import org.apache.ibatis.session.SqlSessionFactoryBuilder; import java.io.IOException; import java.io.InputStream; public class MybatisUtil { static SqlSessionFactory factory; static SqlSession sqlSession; /** * 初始化SqlSessionFactory对象 */ static { try { InputStream is = Resources.getResourceAsStream("mybatis-config.xml"); factory = new SqlSessionFactoryBuilder().build(is); } catch (IOException e) { e.printStackTrace(); } } /** * 创建SqlSession对象 * * @return */ public static SqlSession createSqlSession() { sqlSession = factory.openSession(false); return sqlSession; } public static void closeSqlSession(SqlSession sqlSession) { if (sqlSession != null) { sqlSession.close(); } } }
测试(使用mybatis实现增删改查)
private static Logger logger = Logger.getLogger(DeptMapperTest.class); @Test public void getCount(){ SqlSession sqlSession = MybatisUtil.createSqlSession(); int count = sqlSession.selectOne("cn.tengyu.dao.DeptMapper.count"); logger.debug("部门表总记录数:"+count); sqlSession.close(); } @Test public void findAll(){ SqlSession sqlSession = MybatisUtil.createSqlSession(); List<Dept> depts = sqlSession.selectList("cn.tengyu.dao.DeptMapper.findAll"); for (Dept dept :depts) { logger.debug("编号:"+dept.getDeptno()+"\t名称:"+dept.getDeptname()); } sqlSession.close(); } @Test public void findByName(){ SqlSession sqlSession = MybatisUtil.createSqlSession(); try { Dept dept = sqlSession.getMapper(DeptMapper.class).findByName("公关部"); logger.debug("编号:"+dept.getDeptno()+"\t名称:"+dept.getDeptname()); sqlSession.close(); } catch (Exception e) { logger.debug("查询结果为空!!!"); e.printStackTrace(); } } @Test public void findByDeptName(){ SqlSession sqlSession = MybatisUtil.createSqlSession(); List<Emp> emps = sqlSession.getMapper(DeptMapper.class).findByDeptName("广告"); for (Emp emp :emps) { logger.debug("编号:"+emp.getEmpno()+"\t姓名:"+emp.getEmpname()); } sqlSession.close(); } @Test public void addDeptByName(){ SqlSession sqlSession = MybatisUtil.createSqlSession(); int i = sqlSession.getMapper(DeptMapper.class).addDeptByName("XXX"); if (i>0){ sqlSession.commit(); logger.debug("添加数据成功"+i+"条"); } sqlSession.close(); this.findAll(); } @Test public void addDept(){ SqlSession sqlSession = MybatisUtil.createSqlSession(); Dept dept = new Dept(); dept.setDeptno(1); dept.setDeptname("娱乐部"); try { int i = sqlSession.getMapper(DeptMapper.class).addDept(dept); if (i>0){ sqlSession.commit(); logger.debug("添加数据成功"+i+"条"); } } catch (Exception e) { sqlSession.rollback(); logger.debug("数据添加失败!"); e.printStackTrace(); } finally { MybatisUtil.closeSqlSession(sqlSession); } this.findAll(); } @Test public void delDept(){ SqlSession sqlSession = MybatisUtil.createSqlSession(); try { int i = sqlSession.getMapper(DeptMapper.class).delDept(12); if (i>0){ sqlSession.commit(); logger.debug("删除数据成功"+i+"条"); } } catch (Exception e) { sqlSession.rollback(); logger.debug("数据删除失败!"); e.printStackTrace(); } finally { MybatisUtil.closeSqlSession(sqlSession); } this.findAll(); } @Test public void modifyDept(){ SqlSession sqlSession = MybatisUtil.createSqlSession(); try { int i = sqlSession.getMapper(DeptMapper.class).modifyDept("测试部", "销售部"); if (i>0){ sqlSession.commit(); logger.debug("修改数据成功"+i+"条"); } } catch (Exception e) { sqlSession.rollback(); logger.debug("修改数据失败!!!"); e.printStackTrace(); } finally { MybatisUtil.closeSqlSession(sqlSession); } }
2.Spring框架搭建
依赖的jar包:(maven添加以下依赖即可)
<dependency> <groupId>junit</groupId> <artifactId>junit</artifactId> <version>4.3</version> <scope>test</scope> </dependency> <dependency> <groupId>org.springframework</groupId> <artifactId>spring-webmvc</artifactId> <version>4.2.0.RELEASE</version> </dependency> <dependency> <groupId>org.springframework</groupId> <artifactId>spring-jdbc</artifactId> <version>4.2.0.RELEASE</version> </dependency> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <version>5.1.43</version> </dependency>
多种数据源的引用:
<!--添加数据源--> <!--spring JDBCTemplate--> <dependency> <groupId>org.springframework</groupId> <artifactId>spring-jdbc</artifactId> <version>4.2.0.RELEASE</version> </dependency> <!--druid--> <dependency> <groupId>com.alibaba</groupId> <artifactId>druid</artifactId> <version>1.0.18</version> </dependency> <!--dbcp 数据源--> <dependency> <groupId>commons-dbcp</groupId> <artifactId>commons-dbcp</artifactId> <version>1.4</version> </dependency> <!--c3p0--> <dependency> <groupId>com.mchange</groupId> <artifactId>c3p0</artifactId> <version>0.9.5.2</version> </dependency>
DAO层实现:(为了使用getJdbcTempalte()方法需要注入/实现JdbcDaoSupport接口)
import org.springframework.jdbc.core.RowMapper; import org.springframework.jdbc.core.support.JdbcDaoSupport; import java.sql.ResultSet; import java.sql.SQLException; import java.util.List; public class NewsDaoImpl extends JdbcDaoSupport implements INewsDao { @Override public List<News> findAll() { String sql = "SELECT * FROM infosm_news"; List<News> list = getJdbcTemplate().query(sql, new RowMapper<News>() { @Override public News mapRow(ResultSet rs, int rowNum) throws SQLException { News news = new News(); news.setNewsid(rs.getInt("newsid")); news.setNewstitle(rs.getString("newstitle")); news.setNewscontent(rs.getString("newscontent")); news.setClickcount(rs.getInt("clickcount")); return news; } }); return list; } }
核心配置文件:applicationContext.xml
<?xml version="1.0" encoding="UTF-8"?> <beans xmlns="http://www.springframework.org/schema/beans" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd"> <!--通过bean元素生命需要Spring创建的实例。该实例的类型通过class属性指定, 并通过id属性为该实例制定一个名称,以便于访问--> <!--DataSource jdbc--> <bean id="dateSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource"> <property name="driverClassName" value="com.mysql.jdbc.Driver"/> <property name="url" value="jdbc:mysql:///infosm"/> <property name="username" value="root"/> <property name="password" value="tengyu"/> </bean> <!--DataSource druid--> <!--<bean id="dateSource" class="com.alibaba.druid.pool.DruidDataSource"> <property name="driverClassName" value="com.mysql.jdbc.Driver"/> <property name="url" value="jdbc:mysql:///infosm"/> <property name="username" value="root"/> <property name="password" value="tengyu"/> </bean>--> <!--DataSource c3p0--> <!--<bean id="dateSource" class="com.mchange.v2.c3p0.ComboPooledDataSource"> <property name="driverClass" value="com.mysql.jdbc.Driver"/> <property name="jdbcUrl" value="jdbc:mysql:///infosm"/> <property name="user" value="root"/> <property name="password" value="tengyu"/> </bean>--> <!--DataSource dbcp--> <!--<bean id="dateSource" class="org.apache.commons.dbcp.BasicDataSource"> <property name="driverClassName" value="com.mysql.jdbc.Driver"/> <property name="url" value="jdbc:mysql:///infosm"/> <property name="username" value="root"/> <property name="password" value="tengyu"/> </bean>--> <!--jdbcTemplate--> <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate"> <property name="dataSource" ref="dateSource"/> </bean> <!--newsDao--> <bean id="newsDao" class="cn.infosm.dao.impl.NewsDaoImpl"> <property name="jdbcTemplate" ref="jdbcTemplate"/> </bean> <!--newsService--> <bean id="newsService" class="cn.infosm.service.impl.NewsServiceImpl"> <property name="dao" ref="newsDao"/> </bean> <!--talkDao--> <bean id="talkDao" class="cn.infosm.dao.impl.TalkDaoImpl"> <property name="jdbcTemplate" ref="jdbcTemplate"/> </bean> <!--talkService--> <bean id="talkService" class="cn.infosm.service.impl.TalkServiceImpl"> <property name="dao" ref="talkDao"/> </bean> </beans>
正常测试即可:
@Test public void findAllNews(){ ApplicationContext context = new ClassPathXmlApplicationContext("applicationContext.xml"); INewsService newsService = (INewsService) context.getBean("newsService"); List<News> list = newsService.findAll(); for (News news :list) { System.out.println(news.getNewstitle()); } } @Test public void findAllTalks(){ ApplicationContext context = new ClassPathXmlApplicationContext("applicationContext.xml"); ITalkService talkService = (ITalkService) context.getBean("talkService"); List<Talk> list = talkService.findAll(); for (Talk talk :list) { System.out.println(talk.getTid()+talk.getContent()+talk.getTalktime()); } }