mybatis dao 层开发简易版 非整合 spring
同样老习惯，先上项目结构截图
首先 补充上篇文中缺失的 mysql demo 用的 小脚本
drop database if exists mybatis; CREATE DATABASE `mybatis` DEFAULT CHARACTER SET utf8 ; use mybatis; /* SQLyog v10.2 MySQL - 5.1.72-community : Database - mybatis ********************************************************************* */ /*Table structure for table `items` */ CREATE TABLE `items` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(32) NOT NULL COMMENT '商品名称', `price` float(10,1) NOT NULL COMMENT '商品定价', `detail` text COMMENT '商品描述', `pic` varchar(64) DEFAULT NULL COMMENT '商品图片', `createtime` datetime NOT NULL COMMENT '生产日期', PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8; /*Table structure for table `user` */ CREATE TABLE `user` ( `id` int(11) NOT NULL AUTO_INCREMENT, `username` varchar(32) NOT NULL COMMENT '用户名称', `birthday` date DEFAULT NULL COMMENT '生日', `sex` char(1) DEFAULT NULL COMMENT '性别', `address` varchar(256) DEFAULT NULL COMMENT '地址', PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=27 DEFAULT CHARSET=utf8; /*Table structure for table `orders` */ CREATE TABLE `orders` ( `id` int(11) NOT NULL AUTO_INCREMENT, `user_id` int(11) NOT NULL COMMENT '下单用户id', `number` varchar(32) NOT NULL COMMENT '订单号', `createtime` datetime NOT NULL COMMENT '创建订单时间', `note` varchar(100) DEFAULT NULL COMMENT '备注', PRIMARY KEY (`id`), KEY `FK_orders_1` (`user_id`), CONSTRAINT `FK_orders_id` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION ) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8; /*Table structure for table `orderdetail` */ CREATE TABLE `orderdetail` ( `id` int(11) NOT NULL AUTO_INCREMENT, `orders_id` int(11) NOT NULL COMMENT '订单id', `items_id` int(11) NOT NULL COMMENT '商品id', `items_num` int(11) DEFAULT NULL COMMENT '商品购买数量', PRIMARY KEY (`id`), KEY `FK_orderdetail_1` (`orders_id`), KEY `FK_orderdetail_2` (`items_id`), CONSTRAINT `FK_orderdetail_1` FOREIGN KEY (`orders_id`) REFERENCES `orders` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT `FK_orderdetail_2` FOREIGN KEY (`items_id`) REFERENCES `items` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;
/* SQLyog v10.2 MySQL - 5.1.72-community : Database - mybatis ********************************************************************* */ /*Data for the table `items` */ insert into `items`(`id`,`name`,`price`,`detail`,`pic`,`createtime`) values (1,'台式机',3000.0,'该电脑质量非常好！！！！',NULL,'2016-02-03 13:22:53'),(2,'笔记本',6000.0,'笔记本性能好，质量好！！！！！',NULL,'2015-02-09 13:22:57'),(3,'背包',200.0,'名牌背包，容量大质量好！！！！',NULL,'2016-02-06 13:23:02'); /*Data for the table `user` */ insert into `user`(`id`,`username`,`birthday`,`sex`,`address`) values (1,'王五',NULL,'2',NULL),(10,'张三','2016-07-10','1','北京市'),(16,'张小明',NULL,'1','河南郑州'),(22,'陈小明',NULL,'1','河南郑州'),(24,'张三丰',NULL,'1','河南郑州'),(25,'陈小明',NULL,'1','河南郑州'),(26,'王五',NULL,NULL,NULL); /*Data for the table `orders` */ insert into `orders`(`id`,`user_id`,`number`,`createtime`,`note`) values (3,1,'1000010','2016-02-04 13:22:35',NULL),(4,1,'1000011','2016-02-03 13:22:41',NULL),(5,10,'1000012','2016-02-12 16:13:23',NULL); /*Data for the table `orderdetail` */ insert into `orderdetail`(`id`,`orders_id`,`items_id`,`items_num`) values (1,3,1,1),(2,3,2,3),(3,4,3,4),(4,4,2,3);
工程采用 maven 管理，pom.xml 内容如下
<?xml version="1.0" encoding="UTF-8"?> <project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd"> <modelVersion>4.0.0</modelVersion> <groupId>com.ghc</groupId> <artifactId>mybatisdao</artifactId> <version>1.0-SNAPSHOT</version> <packaging>war</packaging> <name>mybatisdao Maven Webapp</name> <!-- FIXME change it to the project's website --> <url>http://www.example.com</url> <properties> <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding> <maven.compiler.source>1.7</maven.compiler.source> <maven.compiler.target>1.7</maven.compiler.target> </properties> <dependencies> <!-- https://mvnrepository.com/artifact/junit/junit --> <dependency> <groupId>junit</groupId> <artifactId>junit</artifactId> <version>4.12</version> <scope>test</scope> </dependency> <!-- https://mvnrepository.com/artifact/org.mybatis/mybatis --> <dependency> <groupId>org.mybatis</groupId> <artifactId>mybatis</artifactId> <version>3.4.6</version> </dependency> <!-- https://mvnrepository.com/artifact/log4j/log4j --> <dependency> <groupId>log4j</groupId> <artifactId>log4j</artifactId> <version>1.2.17</version> </dependency> <!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java --> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <version>5.1.18</version> </dependency> </dependencies> <build> <finalName>mybatisdao</finalName> <pluginManagement><!-- lock down plugins versions to avoid using Maven defaults (may be moved to parent pom) --> <plugins> <plugin> <artifactId>maven-clean-plugin</artifactId> <version>3.0.0</version> </plugin> <!-- see http://maven.apache.org/ref/current/maven-core/default-bindings.html#Plugin_bindings_for_war_packaging --> <plugin> <artifactId>maven-resources-plugin</artifactId> <version>3.0.2</version> </plugin> <plugin> <artifactId>maven-compiler-plugin</artifactId> <version>3.7.0</version> </plugin> <plugin> <artifactId>maven-surefire-plugin</artifactId> <version>2.20.1</version> </plugin> <plugin> <artifactId>maven-war-plugin</artifactId> <version>3.2.0</version> </plugin> <plugin> <artifactId>maven-install-plugin</artifactId> <version>2.5.2</version> </plugin> <plugin> <artifactId>maven-deploy-plugin</artifactId> <version>2.8.2</version> </plugin> </plugins> </pluginManagement> </build> </project>
接下来就是 四个 配置文件了， 其中 db.properties 里的 key 到 SqlMappingConfig.xml 里就引用不到，这点作为初学者也觉得无奈，暂且就硬编码吧
jdbc.driver=com.mysql.jdbc.Driver jdbc.url=jdbc:mysql://localhost:3306/mybatis?characterEncoding=UTF-8 jdbc.username=root jdbc.password=Mede645
log4j.rootLogger=DEBUG,A1 log4j.logger.org.springframework=debug log4j.appender.A1=org.apache.log4j.ConsoleAppender log4j.appender.A1.layout=org.apache.log4j.PatternLayout log4j.appender.A1.layout.ConversionPattern=%d %5p [%t] (%F:%L) - %m%n
下面这个很重要很重要，是入口配置文件
<?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="db.properties"> <!--properties中还可以配置一些属性名和属性值 --> <!-- <property name="jdbc.driver" value=""/> --> </properties> <typeAliases> <typeAlias type="com.ghc.pojo.User" alias="user" /> </typeAliases> <!-- 和spring整合后 environments配置将废除 --> <environments default="development"> <environment id="development"> <!-- 使用jdbc事务管理，事务控制由mybatis --> <transactionManager type="JDBC" /> <!-- 数据库连接池，由mybatis管理 --> <dataSource type="POOLED"> <property name="driver" value="com.mysql.jdbc.Driver" /> <property name="url" value="jdbc:mysql://localhost:3306/mybatis?characterEncoding=UTF-8" /> <property name="username" value="root" /> <property name="password" value="Mede645" /> </dataSource> </environment> </environments> <mappers> <mapper resource="User.xml"/> </mappers> </configuration>
<?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="com.ghc.dao.UserDao"> <select id="findUserById" parameterType="java.lang.Integer" resultType="user"> SELECT * FROM user WHERE id =#{userid} <!--简单类型 随意取名--> </select> </mapper>
<select id="findUserLike" parameterType="java.lang.String" resultType="user"> SELECT * FROM user WHERE username LIKE '%${value}%' </select> <insert id="addUser" parameterType="user"> INSERT INTO user(username,birthday,sex,address) values(#{userName},#{birthday},#{sex},#{address}) </insert> <delete id="deleteUserById" parameterType="int"> DELETE FROM user WHERE id=#{id} </delete> <update id="updateUserById" parameterType="user"> UPDATE user set username=#{userName},birthday= #{birthday},sex=#{sex},address=#{address} WHERE id = #{id} </update> 上面是新增的 部分，下面请拷贝如有需要 <?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="com.ghc.dao.UserDao"> <select id="findUserById" parameterType="java.lang.Integer" resultType="user"> SELECT * FROM user WHERE id =#{userid} <!--简单类型 随意取名--> </select> <select id="findUserLike" parameterType="java.lang.String" resultType="user"> SELECT * FROM user WHERE username LIKE '%${value}%' </select> <insert id="addUser" parameterType="user"> INSERT INTO user(username,birthday,sex,address) values(#{userName},#{birthday},#{sex},#{address}) </insert> <delete id="deleteUserById" parameterType="int"> DELETE FROM user WHERE id=#{id} </delete> <update id="updateUserById" parameterType="user"> UPDATE user set username=#{userName},birthday= #{birthday},sex=#{sex},address=#{address} WHERE id = #{id} </update> </mapper>
与持久层映射的 pojo 对象 ， 就是 简单java 对象的意思，没事儿别整高大上，故作高深。。。。
package com.ghc.pojo; import java.util.Date; public class User { private int id; private String userName; private Date birthday; private String sex; private String address; public int getId() { return id; } public void setId(int id) { this.id = id; } public String getUserName() { return userName; } public void setUserName(String userName) { this.userName = userName; } public Date getBirthday() { return birthday; } public void setBirthday(Date birthday) { this.birthday = birthday; } public String getSex() { return sex; } public void setSex(String sex) { this.sex = sex; } public String getAddress() { return address; } public void setAddress(String address) { this.address = address; } }
第一种方法采用 原始 dao 开发方法即先写 dao 接口，再写其实现类
package com.ghc.dao; import com.ghc.pojo.User; public interface UserDao{ public User findUserById(int id) throws Exception; }
package com.ghc.dao; import com.ghc.pojo.User; import org.apache.ibatis.jdbc.SQL; import org.apache.ibatis.session.SqlSessionFactory; import java.io.IOException; public class UserDaoImp implements UserDao { // 由于没有整合，这里无法使用spring容器自动注入，手动采取构造函数注入 // SqlSessionFactoryBuilder 看做工具类，而 SqlSessionFactor 做成单例 //private SqlSessionFactoryBuilder sqlSessionFactoryBuilder = new SqlSessionFactoryBuilder(); private SqlSessionFactory sqlSessionFactory; public UserDaoImp(SqlSessionFactory sqlSessionFactory){ this.sqlSessionFactory = sqlSessionFactory; } @Override public User findUserById(int id) throws IOException { return sqlSessionFactory.openSession().selectOne("com.ghc.dao.UserDao.findUserById",id); } }
package com.ghc.dao; import com.ghc.pojo.User; import java.util.List; public interface UserDao{ User findUserById(int id) throws Exception; List<User> findUserLike(String userName) throws Exception; void addUser(User user) throws Exception; void deleteUserById(int id) throws Exception; void updateUserById(User user) throws Exception; }
package com.ghc.dao; import com.ghc.pojo.User; import org.apache.ibatis.jdbc.SQL; import org.apache.ibatis.session.SqlSession; import org.apache.ibatis.session.SqlSessionFactory; import java.io.IOException; import java.util.List; public class UserDaoImp implements UserDao { // 由于没有整合，这里无法使用spring容器自动注入，手动采取构造函数注入 // SqlSessionFactoryBuilder 看做工具类，而 SqlSessionFactor 做成单例 //private SqlSessionFactoryBuilder sqlSessionFactoryBuilder = new SqlSessionFactoryBuilder(); private SqlSessionFactory sqlSessionFactory; public UserDaoImp(SqlSessionFactory sqlSessionFactory){ this.sqlSessionFactory = sqlSessionFactory; } @Override public User findUserById(int id) throws IOException { return sqlSessionFactory.openSession().selectOne("com.ghc.dao.UserDao.findUserById",id); } @Override public List<User> findUserLike(String userName) throws Exception { return sqlSessionFactory.openSession().selectList("com.ghc.dao.UserDao.findUserLike",userName); } @Override public void addUser(User user) throws Exception { SqlSession sqlSession = sqlSessionFactory.openSession(); sqlSession.insert("com.ghc.dao.UserDao.addUser",user); sqlSession.commit(); } @Override public void deleteUserById(int id) throws Exception { SqlSession sqlSession = sqlSessionFactory.openSession(); sqlSession.delete("com.ghc.dao.UserDao.deleteUserById",id ); sqlSession.commit(); } @Override public void updateUserById(User user) throws Exception { SqlSession sqlSession= sqlSessionFactory.openSession(); sqlSession.update("com.ghc.dao.UserDao.updateUserById",user); sqlSession.commit(); } }
既然第一个 UserDao 开发好了，我们就可以直接拿来测试一波了
import com.ghc.dao.UserDao; import com.ghc.dao.UserDaoImp; import com.ghc.pojo.User; import org.apache.ibatis.io.Resources; import org.apache.ibatis.session.SqlSessionFactory; import org.apache.ibatis.session.SqlSessionFactoryBuilder; import org.junit.Test; public class UserDaoTest { @Test public void testUserDao() throws Exception{ SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(Resources.getResourceAsStream("SqlMappingConfig.xml")); UserDao userDao = new UserDaoImp(sqlSessionFactory); User user = userDao.findUserById(1); System.out.println(user.getUserName()); } }
import com.ghc.dao.UserDao; import com.ghc.dao.UserDaoImp; import com.ghc.pojo.User; import org.apache.ibatis.io.Resources; import org.apache.ibatis.session.SqlSessionFactory; import org.apache.ibatis.session.SqlSessionFactoryBuilder; import org.junit.Test; import java.util.List; import java.util.Date; public class UserDaoTest { @Test public void testUserDao() throws Exception{ SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(Resources.getResourceAsStream("SqlMappingConfig.xml")); UserDao userDao = new UserDaoImp(sqlSessionFactory); User user = userDao.findUserById(1); System.out.println(user.getUserName()); List<User> userList = userDao.findUserLike("小明"); for(User u:userList){ System.out.println(u.getUserName()); } User user1 = new User(); user1.setUserName("孔方兄"); user1.setSex("某"); user1.setBirthday(new Date()); user1.setAddress("麓谷"); userDao.addUser(user1); // userDao.deleteUserById(1); 因为主外键的问题所以暂注释掉 // update 方法 User user2 = new User(); user2.setId(43); user2.setUserName("黑钻石王老五"); user2.setBirthday(new Date()); user2.setSex("南"); user2.setAddress("北京天安门"); userDao.updateUserById(user2); } }
测试结果：
待续 第二种 只写接口方法。。。
如果有来生，一个人去远行，看不同的风景，感受生命的活力。。。
