快捷導(dǎo)航

Mybatis的@select和@SelectProvider注解方式動(dòng)態(tài)SQL語(yǔ)句解讀

更新時(shí)間：2023年12月07日 09:06:24 作者：DegenerateAng

這篇文章主要介紹了Mybatis的@select和@SelectProvider注解方式動(dòng)態(tài)SQL語(yǔ)句,具有很好的參考價(jià)值,希望對(duì)大家有所幫助,如有錯(cuò)誤或未考慮完全的地方,望不吝賜教

配置xml文件：Spring+Mybatis

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
	xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xsi:schemaLocation="http://www.springframework.org/schema/beans
http://www.springframework.org/schema/beans/spring-beans.xsd">
	<!-- 封裝數(shù)據(jù)源，連接數(shù)據(jù)庫(kù)屬性 -->
 
	<bean id="dataSouce"
		class="org.springframework.jdbc.datasource.DriverManagerDataSource">
		<property name="driverClassName" value="net.sf.log4jdbc.DriverSpy"></property>
		<property name="url"
			value="jdbc:mysql://localhost:3306/mybas"></property>
		<property name="username" value="root"></property>
		<property name="password" value="scott"></property>
 
		
	</bean>
 
 
	<!-- 創(chuàng)建SqlSessionFactory對(duì)象 -->
	<bean id="factory" class="org.mybatis.spring.SqlSessionFactoryBean">
		<!-- 數(shù)據(jù)庫(kù)連接信息來(lái)源于 dataSource -->
		<property name="dataSource" ref="dataSouce"></property>
	</bean>
 
 
 
	<!-- 掃描器相當(dāng)于mybatis.xml中 mappers下package標(biāo)簽,掃描com.bjsxt.mapper包后會(huì)給對(duì)應(yīng)接口創(chuàng)建對(duì)象 -->
	<bean class="org.mybatis.spring.mapper.MapperScannerConfigurer">
		<!-- 要掃描哪個(gè)包 -->
		<property name="basePackage" value="com.gx.service"></property>
		<!-- 和factory產(chǎn)生關(guān)系 -->
		<property name="sqlSessionFactory" ref="factory"></property>
	</bean>
 
 
	<!-- 由spring管理service實(shí)現(xiàn)類(lèi) account實(shí)現(xiàn)類(lèi)中的set方法 -->
	<bean id="accounts" class="com.gx.serviecImpl.accountserviceImpl">
		<property name="account" ref="accountservice"></property>
	</bean>
</beans>

創(chuàng)建實(shí)體類(lèi)

package com.gx.pojo;
 
public class account {
	private int accountID;
	private String num;
	private String password;
	private Double balance;
	private String name;
	public int getAccountID() {
		return accountID;
	}
	public void setAccountID(int accountID) {
		this.accountID = accountID;
	}
	public String getNum() {
		return num;
	}
	public void setNum(String num) {
		this.num = num;
	}
	public String getPassword() {
		return password;
	}
	public void setPassword(String password) {
		this.password = password;
	}
	public Double getBalance() {
		return balance;
	}
	public void setBalance(Double balance) {
		this.balance = balance;
	}
	public String getName() {
		return name;
	}
	public void setName(String name) {
		this.name = name;
	}
	@Override
	public String toString() {
		return "account [accountID=" + accountID + ", num=" + num
				+ ", password=" + password + ", balance=" + balance + ", name="
				+ name + "]";
	}
	
}

接口文件類(lèi)（注解寫(xiě)法）

里面包含有增刪改查，注意@的注解，有所不同；

@Param("")給參數(shù)取別名；

package com.gx.service;
 
import java.util.List;
 
import mapper.SqlContext;
 
import org.apache.ibatis.annotations.Delete;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.SelectProvider;
import org.apache.ibatis.annotations.Update;
 
import com.gx.pojo.account;
 
public interface accountservice {
	@SelectProvider(method = "selectaccount", type = SqlContext.class)
	List<account> show(account account);
	
	@Select("select * from account ")
	List<account> showTwo();
	
	@Select("select * from account where num=${number }")
	List<account> showThree(@Param("number") String number);
	
	@Select("select * from account where name='${accounts.name}'")
	List<account> showFour(@Param("accounts")account accounts);
	
	@Select("SELECT account.*,accounttow.number FROM account JOIN accounttow ON accounttow.accountID=account.num ")
	List<account> showFive();
	
	@Insert("INSERT account(num,password,balance,name) VALUES('${ac.num}','${ac.password}',${ac.balance},'${ac.name}');")
	boolean insertAccount(@Param("ac")account account);
	
	@Delete("DELETE FROM  account WHERE name='${ac.name}'")
	boolean delectAccount(@Param("ac")account account);
	
	@Update("UPDATE account SET balance=${ac.balance} WHERE NAME='${ac.name}'")
	boolean updataAccount(@Param("ac")account account);
}

@SelectProvider(method = "selectaccount", type = SqlContext.class)

@SelectProvider一般用于多條件查詢(xún)使用，多表查詢(xún)可以直接使用@Select去·寫(xiě)如showFive；

多條件查詢(xún)可以在類(lèi)文件中寫(xiě)，Mybatis支持在類(lèi)文件中寫(xiě)動(dòng)態(tài)SQL；

一個(gè)類(lèi)可以有多個(gè)SQL，type 是類(lèi)文件名稱(chēng)，method是方法指定里面的SQL；

SQL類(lèi)的寫(xiě)法

package mapper;
 
import org.apache.ibatis.jdbc.SQL;
 
import com.gx.pojo.account;
 
public class SqlContext {
public String selectaccount(final account account){
	
	return new SQL(){
		{ SELECT("*");
        FROM("account");
        if(account.getNum()!=null && account.getNum()!="0"){
        	WHERE(" num=#{num } ");
        }
        if (account.getName()!=null && account.getName()!="") {
        	WHERE(" name=#{name } ");
		}}
	}.toString();
	
}
}

serviceImpl(實(shí)現(xiàn)接口)

package com.gx.serviecImpl;
 
import java.util.List;
 
import com.gx.pojo.account;
import com.gx.service.accountservice;
 
public class accountserviceImpl implements accountservice{
	private accountservice  ac;
	
	public accountservice getAccount(){
		return ac;
	}
     //依賴(lài)注入時(shí)必須的setter方法
	public void setAccount(accountservice accountservices){
		this.ac=accountservices;
	}
	@Override
	public List<account> show(account account) {
		// TODO Auto-generated method stub
		return ac.show(account);
	}
	@Override
	public List<account> showTwo() {
		// TODO Auto-generated method stub
		return ac.showTwo();
	}
	@Override
	public List<account> showThree(String num) {
		// TODO Auto-generated method stub
		return ac.showThree(num);
	}
	@Override
	public List<account> showFour(account accounts) {
		// TODO Auto-generated method stub
		return ac.showFour(accounts);
	}
	@Override
	public boolean insertAccount(account account) {
		// TODO Auto-generated method stub
		return ac.insertAccount(account);
	}
	@Override
	public boolean delectAccount(account account) {
		// TODO Auto-generated method stub
		return ac.delectAccount(account);
	}
	@Override
	public boolean updataAccount(account account) {
		// TODO Auto-generated method stub
		return ac.updataAccount(account);
	}
	@Override
	public List<account> showFive() {
		// TODO Auto-generated method stub
		return ac.showFive();
	}
 
}

Servlet：使用（Spring+Mybatis）

創(chuàng)建工廠(chǎng)和實(shí)例化方法，并且調(diào)用方法；

package com.gx.servlet;
 
import java.io.IOException;
 
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
 
import org.springframework.web.context.WebApplicationContext;
import org.springframework.web.context.support.WebApplicationContextUtils;
 
import com.gx.pojo.account;
import com.gx.serviecImpl.accountserviceImpl;
 
public class accountServlet extends HttpServlet {
    //私有化serviceImpl實(shí)現(xiàn)類(lèi)
	private accountserviceImpl accountservice;
 
	@Override
	public void init() throws ServletException {
		WebApplicationContext ac = WebApplicationContextUtils
				.getRequiredWebApplicationContext(getServletContext());
		accountservice = ac.getBean("accounts", accountserviceImpl.class);
	}
 
	@Override
	protected void doGet(HttpServletRequest req, HttpServletResponse resp)
			throws ServletException, IOException {
		doPost(req, resp);
	}
 
	@Override
	protected void doPost(HttpServletRequest req, HttpServletResponse resp)
			throws ServletException, IOException {
		req.setCharacterEncoding("utf-8");
		resp.setCharacterEncoding("UTF-8");
		
		account account=new account();
		account.setNum("4");
		account.setBalance(1000.0);
		account.setName("吳六");
		account.setPassword("4");
		
		
		System.out.println("我是show方法"+accountservice.show(account));
		System.out.println("我是showTwo方法"+accountservice.showTwo());
		System.out.println("我是showThree方法"+accountservice.showThree("2"));
		System.out.println("我是showFour方法"+accountservice.showFour(account));
		System.out.println("我是insertAccount方法"+accountservice.insertAccount(account));
		System.out.println("我是updataAccount方法"+accountservice.updataAccount(account));
		System.out.println("我是delectAccount方法"+accountservice.delectAccount(account));
		
		req.setAttribute("list", accountservice.show(account));
		req.getRequestDispatcher("/index.jsp").forward(req, resp);
	}
 
	/**
	 * 
	 */
	private static final long serialVersionUID = 1L;
}