入門程序 · java

[TOC] # 創建sql ~~~ /* Navicat MySQL Data Transfer Source Server : localhost_3306 Source Server Version : 50521 Source Host : localhost:3306 Source Database : mybatis Target Server Type : MYSQL Target Server Version : 50521 File Encoding : 65001 Date: 2015-04-09 16:03:53 */ 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); ~~~ # 創建類創建pojo包在pojo包下創建User類 ~~~ package pojo; 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;// 地址 public Integer getId() { return id; } public void setId(Integer id) { this.id = id; } public String getUsername() { return username; } public void setUsername(String username) { this.username = username; } public String getSex() { return sex; } public void setSex(String sex) { this.sex = sex; } public Date getBirthday() { return birthday; } public void setBirthday(Date birthday) { this.birthday = birthday; } public String getAddress() { return address; } public void setAddress(String address) { this.address = address; } @Override public String toString() { return "User [id=" + id + ", username=" + username + ", sex=" + sex + ", birthday=" + birthday + ", address=" + address + "]"; } } ~~~ 創建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> ~~~ # 創建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); } } ~~~ # 用戶名模糊查詢 **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); } ~~~