入门程序 · JAVA

[TOC] # 创建sql ~~~ SET FOREIGN_KEY_CHECKS=0; -- ---------------------------- -- Table structure for `orders` -- ---------------------------- DROP TABLE IF EXISTS `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; -- ---------------------------- -- Records of orders -- ---------------------------- INSERT INTO `orders` VALUES ('3', '1', '1000010', '2015-02-04 13:22:35', null); INSERT INTO `orders` VALUES ('4', '1', '1000011', '2015-02-03 13:22:41', null); INSERT INTO `orders` VALUES ('5', '10', '1000012', '2015-02-12 16:13:23', null); -- ---------------------------- -- Table structure for `user` -- ---------------------------- DROP TABLE IF EXISTS `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; -- ---------------------------- -- Records of user -- ---------------------------- INSERT INTO `user` VALUES ('1', '王五', null, '2', null); INSERT INTO `user` VALUES ('10', '张三', '2014-07-10', '1', '北京市'); INSERT INTO `user` VALUES ('16', '张小明', null, '1', '河南郑州'); INSERT INTO `user` VALUES ('22', '陈小明', null, '1', '河南郑州'); INSERT INTO `user` VALUES ('24', '张三丰', null, '1', '河南郑州'); INSERT INTO `user` VALUES ('25', '陈小明', null, '1', '河南郑州'); INSERT INTO `user` VALUES ('26', '王五', null, null, null); ~~~ # 创建类 ~~~ import java.io.Serializable; import java.util.Date; public class User implements Serializable { private static final long serialVersionUID = 1L; private Integer id; private String username;// 用户姓名 private String sex;// 性别 private Date birthday;// 生日 private String address;// 地址 //setter/getter ~~~ # 查询 **创建SqlSessionFactory,并根据用户id查询** ~~~ package junit; import java.io.InputStream; 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 org.junit.Test; import pojo.User; public class MybatisFirstTest { @Test public void testMybatis() throws Exception { // 加载核心配置文件 String resource = "SqlMapConfig.xml"; InputStream in = Resources.getResourceAsStream(resource); // 创建SqlSessionFactory SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(in); // 创建SqlSession SqlSession sqlSession = sqlSessionFactory.openSession(); // 执行sql语句 User user = sqlSession.selectOne("test.findUserById", 1); System.out.println(user); } } ~~~ 创建sqlmap包在sqlmap包下创建User.xml也就是sql映射文件 ~~~ <?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="test">  <select id="findUserById" parameterType="Integer" resultType="pojo.User"> select * from user where id = #{v} </select> </mapper> ~~~ **用户名模糊查询** User.xml ~~~  <select id="findUserByUsername" parameterType="String" resultType="pojo.User"> select * from user where username like #{username} </select> ~~~ SqlSessionFactory ~~~ @Test public void testMybatis() throws Exception { // 加载核心配置文件 String resource = "SqlMapConfig.xml"; InputStream in = Resources.getResourceAsStream(resource); // 创建SqlSessionFactory SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(in); // 创建SqlSession SqlSession sqlSession = sqlSessionFactory.openSession(); // 执行sql语句 List<User> users = sqlSession.selectList("test.findUserByUsername", "%五%"); for (User user2 : users) { System.out.println(user2); } } ~~~ # 添加 **添加用户** User.xml ~~~  <insert id="insertUser" parameterType="pojo.User"> insert into user (username,birthday,address,sex) values (#{username},#{birthday},#{address},#{sex}) </insert> ~~~ SqlSessionFactory ~~~ @Test public void testMybatis() throws Exception { // 加载核心配置文件 String resource = "SqlMapConfig.xml"; InputStream in = Resources.getResourceAsStream(resource); // 创建SqlSessionFactory SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(in); // 创建SqlSession SqlSession sqlSession = sqlSessionFactory.openSession(); // 执行sql语句 User user = new User(); user.setUsername("jdxia"); user.setBirthday(new Date()); user.setAddress("abcdef"); user.setSex("男"); int i = sqlSession.insert("test.insertUser", user); sqlSession.commit(); } ~~~ **添加用户,并返回用户的id** User.xml ~~~  <insert id="insertUser" parameterType="pojo.User">  <selectKey keyProperty="id" resultType="Integer" order="AFTER"> select LAST_INSERT_ID() </selectKey> insert into user (username,birthday,address,sex) values (#{username},#{birthday},#{address},#{sex}) </insert> ~~~ SqlSessionFactory ~~~ @Test public void testMybatis() throws Exception { // 加载核心配置文件 String resource = "SqlMapConfig.xml"; InputStream in = Resources.getResourceAsStream(resource); // 创建SqlSessionFactory SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(in); // 创建SqlSession SqlSession sqlSession = sqlSessionFactory.openSession(); // 执行sql语句 User user = new User(); user.setUsername("jdxia01"); user.setBirthday(new Date()); user.setAddress("abcdef"); user.setSex("男"); int i = sqlSession.insert("test.insertUser", user); sqlSession.commit(); System.out.println(user.getId()); } ~~~ # 更新更新用户 User.xml ~~~  <update id="updateUserById" parameterType="pojo.User"> update user set username = #{username},sex= #{sex},birthday= #{birthday},address= #{address} where id = #{id} </update> ~~~ SqlSessionFactory ~~~ @Test public void testMybatis() throws Exception { // 加载核心配置文件 String resource = "SqlMapConfig.xml"; InputStream in = Resources.getResourceAsStream(resource); // 创建SqlSessionFactory SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(in); // 创建SqlSession SqlSession sqlSession = sqlSessionFactory.openSession(); // 执行sql语句 User user = new User(); user.setId(28); user.setUsername("jdxia11"); user.setBirthday(new Date()); user.setAddress("abcdef"); user.setSex("男"); int i = sqlSession.insert("test.updateUserById", user); sqlSession.commit(); } ~~~ # 删除 **删除用户** User.xml ~~~  <delete id="deleteUserById" parameterType="Integer"> delete from user where id =#{v} </delete> ~~~ SqlSessionFactory ~~~ @Test public void testMybatis() throws Exception { // 加载核心配置文件 String resource = "SqlMapConfig.xml"; InputStream in = Resources.getResourceAsStream(resource); // 创建SqlSessionFactory SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(in); // 创建SqlSession SqlSession sqlSession = sqlSessionFactory.openSession(); // 执行sql语句 int i = sqlSession.insert("test.deleteUserById", 28); sqlSession.commit(); System.out.println(i); } ~~~