浅入浅出MyBatis(02):查询密码为123的所有用户

January 14th 2015  | Tags: MyBatis, orm

2015-01-14

该文章是《专题:浅入浅出MyBatis》系列文章的一篇。点击进入该专题目录

工具与环境:Intellij IDEA 14,JDK 1.7,MyBatis 3.2,MySQL 5.5。

数据库设计

数据库使用MySQL,新建数据库blog_db,然后建立表:

CREATE  TABLE `blog_db`.`user` (
  `id` INT NOT NULL AUTO_INCREMENT ,
  `name` VARCHAR(45) NOT NULL ,
  `email` VARCHAR(45) NOT NULL ,
  `password` VARCHAR(45) NOT NULL ,
  PRIMARY KEY (`id`) )
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_general_ci;

ALTER TABLE `blog_db`.`user` 
ADD UNIQUE INDEX `name_unique` (`name` ASC) ;

INSERT INTO `blog_db`.`user` (`name`, `email`, `password`) VALUES ('letian', 'letian@111.com', '123');

INSERT INTO `blog_db`.`user` ( `name`, `email`, `password`) VALUES ('xiaosi', 'xiaosi@111.com', '123');

代码

代码建立在浅入浅出MyBatis(01):查询id为1的用户的信息的基础上。

letian.mybatis.dao下的UserMapper.java

package letian.mybatis.dao;

import letian.mybatis.bean.User;

import java.util.List;

public interface UserMapper {

    User findById(int id);
    List<User> findByPassword(String password);

}

letian.mybatis.mapper下的UserMapper.xml

<?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="letian.mybatis.dao.UserMapper">

    <select id="findById" parameterType="HashMap" resultType="letian.mybatis.bean.User">
        select
        * from blog_db.user where id=#{id}
    </select>

    <select id="findByPassword" parameterType="HashMap" resultType="letian.mybatis.bean.User">
        select
        * from blog_db.user where password=#{password}
    </select>

</mapper>

Main.java

import java.io.IOException;
import java.util.Iterator;
import java.util.List;

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 letian.mybatis.bean.User;
import letian.mybatis.dao.UserMapper;


public class Main {

    public static void main(String[] args) throws IOException {

        SqlSessionFactory sessionFactory;
        sessionFactory = new SqlSessionFactoryBuilder()
                .build(Resources.getResourceAsReader("mybatis-config.xml"));

        SqlSession sqlSession = sessionFactory.openSession();
        UserMapper userMapper = sqlSession.getMapper(UserMapper.class);

//        User user = userMapper.findById(2);
//        System.out.println(user);

        List<User> users = userMapper.findByPassword("123");
        Iterator<User> it = users.iterator();
        while (it.hasNext()) {
            System.out.println(it.next());
        }
    }
}

运行结果

运行Main.java,结果如下:

User{id=1, name='letian', email='letian@111.com', password='123'}
User{id=2, name='xiaosi', email='xiaosi@111.com', password='123'}

(完)