-----------------------------------
mybatis jndi 建立maven web项目
src/main
/java
/resources
/webapp
web.xml
WEB-INF
lib
META-INF
context.xml 配置文件
index.jsp
src/test/java 测试主目录
1)pom.xml 文件内容
<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/maven-v4_0_0.xsd">
<modelVersion>4.0.0</modelVersion>
<groupId>com.fz</groupId>
<artifactId>my12</artifactId>
<packaging>war</packaging>
<version>1.0</version>
<name>my12 Maven Webapp</name>
<url>http://maven.apache.org</url>
<dependencies>
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.12</version>
<scope>test</scope>
</dependency>
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis</artifactId>
<version>3.4.4</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.41</version>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<version>1.16.16</version>
</dependency>
<dependency>
<groupId>javax.servlet</groupId>
<artifactId>javax.servlet-api</artifactId>
<version>3.1.0</version>
<scope>provided</scope>
</dependency>
<dependency>
<groupId>javax.servlet.jsp</groupId>
<artifactId>jsp-api</artifactId>
<version>2.2</version>
<scope>provided</scope>
</dependency>
<dependency>
<groupId>javax.servlet</groupId>
<artifactId>jstl</artifactId>
<version>1.2</version>
</dependency>
<dependency>
<groupId>javax.servlet.jsp</groupId>
<artifactId>jsp-api</artifactId>
<version>2.2</version>
<scope>provided</scope>
</dependency>
</dependencies>
<build>
<finalName>${project.artifactId}</finalName>
<testSourceDirectory>src/test/java</testSourceDirectory>
<sourceDirectory>src/main/java</sourceDirectory>
<!-- 处理无法加载资源配置文件 -->
<resources>
<resource>
<directory>src/main/java</directory>
<includes>
<include>**/*.xml</include>
</includes>
</resource>
<resource>
<directory>src/main/resources</directory>
<includes>
<include>**/*.xml</include>
<include>**/*.properties</include>
</includes>
</resource>
</resources>
</build>
</project>
2、webapp
META-INF
context.xml
<?xml version="1.0" encoding="UTF-8"?>
<Context>
<!-- maxActive: Maximum number of database connections in pool. Make sure
you configure your mysqld max_connections large enough to handle all of your
db connections. Set to -1 for no limit. -->
<!-- maxIdle: Maximum number of idle database connections to retain in pool.
Set to -1 for no limit. See also the DBCP documentation on this and the minEvictableIdleTimeMillis
configuration parameter. -->
<!-- maxWait: Maximum time to wait for a database connection to become available
in ms, in this example 10 seconds. An Exception is thrown if this timeout
is exceeded. Set to -1 to wait indefinitely. -->
<!-- username and password: MySQL username and password for database connections -->
<!-- driverClassName: Class name for the old mm.mysql JDBC driver is org.gjt.mm.mysql.Driver
- we recommend using Connector/J though. Class name for the official MySQL
Connector/J driver is com.mysql.jdbc.Driver. -->
<!-- url: The JDBC connection url for connecting to your MySQL database. -->
<Resource name="jdbc/mysql" auth="Container" type="javax.sql.DataSource"
maxActive="100" maxIdle="30" maxWait="10000" username="root"
password="root" driverClassName="com.mysql.jdbc.Driver"
url="jdbc:mysql://localhost:3306/db" />
</Context>
3、resource/db.properties
jdbc.driver=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3306/db?useSSL=true&useUnicode=true&characterEncoding=UTF8
jdbc.username=root
jdbc.password=root
/mybatis-config.xml
<?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>
<!-- 加载JDBC配置信息 resources目录下db.properties文件 -->
<properties resource="db.properties"/>
<environments default="development">
<environment id="development">
<transactionManager type="JDBC"/>
<dataSource type="JNDI"> <!-- 此处和以前的不一样 -->
<property name="initial_context" value="java:comp/env" />
<property name="data_source" value="jdbc/mysql" />
</dataSource>
</environment>
</environments>
<mappers>
<package name="com.fz.mapper"/>
</mappers>
</configuration>
4、建立接口
Book.java 实体类
BookMapper.java 映射接口
BookMapper.xml 映射配置文件
5、建立DbHelper.java工具
package com.fz.mybatis;
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.apache.ibatis.transaction.jdbc.JdbcTransactionFactory;
/**
* Created by webrx on 2017-06-23.
*/
public class DbHelper {
public SqlSession ss;
public SqlSessionFactory sf;
public DbHelper(){
try {
this.sf = new SqlSessionFactoryBuilder().build(Resources.getResourceAsStream("mybatis-config.xml"));
this.ss = this.sf.openSession();
} catch (Exception e) {
e.printStackTrace();
}
}
public SqlSession getSession(){
return this.ss;
}
}
6、主程序测试
<%@ page import="javax.naming.InitialContext" %>
<%@ page import="javax.naming.Context" %>
<%@ page import="javax.sql.DataSource" %>
<%@ page import="java.sql.Connection" %>
<%@ page import="org.apache.ibatis.session.SqlSession" %>
<%@ page import="com.fz.mybatis.DbHelper" %>
<%@ page import="com.fz.mapper.BookMapper" %>
<%@ page import="com.fz.entity.Book" %>
<%@ page import="java.util.List" %>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<%@ page language="java" pageEncoding="utf-8"%>
<!doctype html>
<html lang="zh">
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, user-scalable=no, initial-scale=1.0, maximum-scale=1.0, minimum-scale=1.0">
<meta http-equiv="X-UA-Compatible" content="ie=edge">
<title>Document</title>
</head>
<body>
<h3>mybatis-jndi-显示书籍</h3>
<%
SqlSession ss = new DbHelper().getSession();
BookMapper bdao = ss.getMapper(BookMapper.class);
List<Book> bks = bdao.query();
pageContext.setAttribute("book",bks);
%>
<c:forEach items="${book}" var="book">
${book.name}<br>
</c:forEach>
<hr>
${2+2}
<hr>
<%
Context initContext = new InitialContext();
Context envContext = (Context) initContext.lookup("java:/comp/env");
DataSource ds = (DataSource) envContext.lookup("jdbc/mysql");
Connection conn = ds.getConnection();
out.print(conn);
%>
</body>
</html>
Mybatis传多个参数
第一种方法
DAO层的函数方法
1 Public User selectUser(String name,String area);
对应的Mapper.xml
<select id="selectUser" resultMap="BaseResultMap">
select * from user_user_t where user_name = #{0} and user_area=#{1}
</select>
其中，#{0}代表接收的是dao层中的第一个参数，#{1}代表dao层中第二参数，更多参数一致往后加即可。
第二种方法
DAO层的函数方法
ser selectUser(@param(“userName”)Stringname,@param(“userArea”)String area);
对应的Mapper.xml
<select id=" selectUser" resultMap="BaseResultMap">
select * from user_user_t where user_name = #{userName，jdbcType=VARCHAR} and user_area=#{userArea,jdbcType=VARCHAR}
</select>
目前个人觉得这两种方法比较实用，当然网上还有其他的方法
普通
@Select("select * from mybatis_Student where id=#{id}")
public Student getStudent(int id);
@Insert("insert into mybatis_Student (name, age, remark, pic,grade_id,address_id) values (#{name},#{age},#{remark}, #{pic},#{grade.id},#{address.id})")
public int insert(Student student);
@Update("update mybatis_Student set name=#{name},age=#{age} where id=#{id}")
public int update(Student student);
@Delete("delete from mybatis_Student where id=#{id}")
public int delete(int id);
@Select("select * from blog_db.user where id=#{id}")
@Results({@Result(id = true, column = "id", property = "id"),@Result(column = "name", property = "name"), @Result(column = "email", property = "email"),@Result(column = "password", property = "password")})
public User findById(int id);
@Insert("INSERT INTO blog_db.user (name, email, password) " + "VALUES (#{name}, #{email}, #{password} )")
@Options(useGeneratedKeys=true, keyProperty="id")
public void insertUser1(User user);
@Insert("INSERT INTO blog_db.user (name, email, password) " +
"VALUES ( #{user.name}, #{user.email}, CONCAT(#{user.password}, #{passwordSuffix}) )")
void insertUser2(@Param("user")User user, @Param("passwordSuffix")String passwordSuffix);
结果集合
@Select("select * from mybatis_Student")
@Results({
@Result(id=true,property="id",column="id"),
@Result(property="name",column="name"),
@Result(property="age",column="age")
})
public List<Student> getAllStudents();
关系1对1
@Select("select * from mybatis_Student")
@Results({
@Result(id=true,property="id",column="id"),
@Result(property="name",column="name"),
@Result(property="age",column="age"),
@Result(property="address",column="address_id",one=@One(select="com.skymr.mybatis.mappers.AddressMapper.getAddress"))
})
public List<Student> getAllStudents();
关系1对多
package com.skymr.mybatis.mappers;
import org.apache.ibatis.annotations.Many;
import org.apache.ibatis.annotations.Result;
import org.apache.ibatis.annotations.Results;
import org.apache.ibatis.annotations.Select;
import com.skymr.mybatis.model.Grade;
public interface Grade2Mapper {
@Select("select * from mybatis_grade where id=#{id}")
@Results({
@Result(id=true,column="id",property="id"),
@Result(column="grade_name",property="gradeName"),
@Result(property="students",column="id",many=@Many(select="com.skymr.mybatis.mappers.Student2Mapper.getStudentsByGradeId"))
})
public Grade getGrade(int id);
}
package com.skymr.mybatis.mappers;
import java.util.List;
import org.apache.ibatis.annotations.Delete;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.One;
import org.apache.ibatis.annotations.Result;
import org.apache.ibatis.annotations.Results;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;
import com.skymr.mybatis.model.Student;
public interface Student2Mapper {
@Select("select * from mybatis_Student where id=#{id}")
public Student getStudent(int id);
@Insert("insert into mybatis_Student (name, age, remark, pic,grade_id,address_id) values (#{name},#{age},#{remark}, #{pic},#{grade.id},#{address.id})")
public int insert(Student student);
@Update("update mybatis_Student set name=#{name},age=#{age} where id=#{id}")
public int update(Student student);
@Delete("delete from mybatis_Student where id=#{id}")
public int delete(int id);
@Select("select * from mybatis_Student")
@Results({
@Result(id=true,property="id",column="id"),
@Result(property="name",column="name"),
@Result(property="age",column="age"),
@Result(property="address",column="address_id",one=@One(select="com.skymr.mybatis.mappers.AddressMapper.getAddress"))
})
public List<Student> getAllStudents();
@Select("select * from mybatis_Student where grade_id=#{gradeId}")
@Results({
@Result(id=true,property="id",column="id"),
@Result(property="name",column="name"),
@Result(property="age",column="age"),
@Result(property="address",column="address_id",one=@One(select="com.skymr.mybatis.mappers.AddressMapper.getAddress"))
})
public List<Student> getStudentsByGradeId(int gradeId);
}
动态sql注解映射 provider
package com.skymr.mybatis.mappers.provider;
import java.util.Map;
import org.apache.ibatis.jdbc.SQL;
import com.skymr.mybatis.model.Student;
public class StudentDynaSqlProvider {
public String insertStudent(final Student student){
return new SQL(){
{
INSERT_INTO("mybatis_Student");
if(student.getName() != null){
VALUES("name","#{name}");
}
if(student.getAge() > 0){
VALUES("age","#{age}");
}
}
}.toString();
}
public String updateStudent(final Student student){
return new SQL(){
{
UPDATE("mybatis_Student");
if(student.getName() != null){
SET("name=#{name}");
}
if(student.getAge() > 0){
SET("age=#{age}");
}
WHERE("id=#{id}");
}
}.toString();
}
public String getStudent(final Map<String,Object> map){
return new SQL(){
{
SELECT("*");
FROM("mybatis_Student");
if(map.containsKey("name")){
WHERE("name like #{name}");
}
if(map.containsKey("age")){
WHERE("age=#{age}");
}
}
}.toString();
}
public String deleteStudent(){
return new SQL(){
{
DELETE_FROM("mybatis_Student");
WHERE("id=#{id}");
}
}.toString();
}
}
接口使用时
@SelectProvider(type=StudentDynaSqlProvider.class,method="getStudent")
public List<Student> getStudents(Map<String,Object> map);