java框架之mybatis

mybasits 配置檔案書寫

1.configer 檔案配置

<?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">

</settings>

</dataSource>

</environment>

</environments>

</configuration>

2.mapper 檔案配置

(1)resultType 是返回值型別

（2 ） pramatetertype 是傳入的引數

<?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">

SELECT * FROM table4012

limit #{offset},#{pagesize}

</select>

insert into table4012(id,username,number,mybasits,android,javaee) values (#{id},#{username},#{number},#{mybasits},#{android},#{javaee})

</insert>

delete from table4012 where id=#{***}

</delete>

update table4012 set username=#{username},number =#{number},mybasits=#{mybasits},android=#{android},javaee=#{javaee} where id=#{id}

</update>

SELECT count (*) from table4012

</select>

SELECT * from table4012

</select>

</mapper>

3.util 類配置

package com.SelfStudy.util;

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 java.io.InputStream;

public class Util {

private static SqlSessionFactory build;// 定義一個物件

static {

String s="mybatis.config.xml";// 載入 config 檔案

InputStream inputStream=null;// 輸入輸出流

try {

inputStream= Resources.getResourceAsStream(s);

build=new SqlSessionFactoryBuilder().build(inputStream);

}

catch (Exception e){

e.printStackTrace();

}

finally {

try {

if ( inputStream !=null){

inputStream.close();

}

}catch (Exception e){

e.printStackTrace();

}

public static SqlSession getSession(){

return build.openSession();

}

4.log4j.properties 檔案配置

log4j.appender.console =org.apache.log4j.ConsoleAppender

log4j.appender.console.Target =System.out

log4j.appender.console.layout =org.apache.log4j.PatternLayout

log4j.appender.console.layout.ConversionPattern =[%-5p][%d{yyyy-MM-dd HH:mm:ss}] %c %L %m%n

### \u914D\u7F6E\u6839 ###

log4j.rootLogger =debug,console

##log4j.logger.com.mapper.StudentMapper=debug,console

四. 動態 sql

1.if set 標籤的使用

Select * from table

if(test="name!=null and name!=''"){

#{id}

}

如果是資料庫新增語句在每一句後面新增，

if(test="name!=null and name!=''"){

#{id} ，

}

2.where 標籤的使用

Select * from table

<where>

if(test="name!=null and name!=''"){

name=#{name}

}

如果是資料庫新增語句在每一句後面新增，

if(test="id!=null and id!=''"){

id=#{id}

}

</where>

3.choose when 標籤的使用

<where>

and name=#{name}

</when>

</choose>

</where>

4. set 標籤的使用

uddate Table

set id=#{id}

<where>

</where>

5. trim 標籤的使用

update Teacher

name=#{name},age=#{age},

</trim>

where id=#{id}

</update>

prefix= “在前面進行新增”

prefixOverrides= “在前面進行去掉”

suffix= “在後面進行新增”

suffixOverrides= “在後面進行去掉”

重點: 執行的順序先去除再新增

6. bind 標籤

slct * from tabl wr nam lik #{nam}

7. foreach （集合查詢，新增）

insert into table () valuse

#{},

</if>

</foreach>

collection: 傳入的引數型別

open: 開啟方式

close ：關閉方式

item:item

8. selectkey 的使用（查詢上一條記錄的一個屬性）

insert table valuse()

select last_insert_name()

</insert>

keypropert 是查詢的屬性的名稱

resulttype 是外匯返傭http://www.fx61.com/返回值型別 , 是查詢結果的返回值型別

order 是執行順序

keycolum: 資料庫中對應的屬性

多種查詢方法的使用

1. 模糊查詢

select * from table where name=#{name}

[1] 在查詢的時候改正

SelectAll("%ko%");

[2] 在 sql 語句中改正

使用concat 函式

select * from table where

id=concat('%',#{id},'%')

2. 多表聯合查詢

(1) mapper 檔案的配置類

<resultMap id="SelectTogether01" type="com.SelfStudy.pojo.PeopleWithDatil">// 封裝連線類的屬性，主要是主類屬性

<association property="PeopleDatil" javaType="com.SelfStudy.pojo.People">// 用 association 封裝子類所有的屬性

</association>

</resultMap>

SELECT * FROM people ,peopledatil// 聯合查詢，動態 sql

<where>

people.`id`=peopledatil.`id`

</where>

</select>

(2) test 測試類

@org.junit.Test

public void TestTogether(){

SqlSession session=Util.getSession();

PeopleMapperDao peopleMapperDao=session.getMapper(PeopleMapperDao.class);

peopleMapperDao.SelectTogether();

List<People> list=null;

list=peopleMapperDao.SelectTogether();

System.out.println(list);

}

3. 一對一聯合查詢

配置類檔案

package com.pojo;

public class Student {

private String name;

private Integer id;

private Integer tid;// 和老師中的 id 對因

private String address;

private String city;

// 一個學生對應一個老師

private Teacher teacher;// 在學生中查詢老師

public String getName() {

return name;

}

public void setName(String name) {

this.name = name;

}

public Integer getId() {

return id;

}

public void setId(Integer id) {

this.id = id;

}

public Integer getTid() {

return tid;

}

public void setTid(Integer tid) {

this.tid = tid;

}

public String getAddress() {

return address;

}

public void setAddress(String address) {

this.address = address;

}

public String getCity() {

return city;

}

public void setCity(String city) {

this.city = city;

}

public Teacher getTeacher() {

return teacher;

}

public void setTeacher(Teacher teacher) {

this.teacher = teacher;

}

public Student(String name, Integer id, Integer tid, String address, String city, Teacher teacher) {

this.name = name;

this.id = id;

this.tid = tid;

this.address = address;

this.city = city;

this.teacher = teacher;

}

public Student() { }

@Override

public String toString() {

return "Student{" +

"name='" + name + '\'' +

", id=" + id +

", tid=" + tid +

", address='" + address + '\'' +

", city='" + city + '\'' +

", teacher=" + teacher +

'}';

}

介面配置

package com.service;

import com.pojo.Student;

import java.util.List;

public interface StudentDao {

public List<Student> Selectall();

public List<Student> SelectByid(Integer id);

// 查詢所有學生

public List<Student> SelectAllStudent();

public List<Student> selct01();

}

mapper 檔案配置

<?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">

</resultMap>

SELECT s.name, s.id ,s.tid ,s.address , s.city ,t.name , school ,age , sex,t.id

FROM student s LEFT JOIN teacher t on

s.`tid`=t.`id`

</select>

4. 多對一聯合查詢

</collection>

</resultMap>

5. 多對多的聯合查詢

實體類的配置

package com.pojo;

import java.util.Date;

import java.util.List;

public class Writer {

private String name;

private Integer id;

private Integer age;

private String sex;

private String book;

private Date birthday;

// 查詢作者裡面包含作品

private List<Works> works;

public List<Works> getWorks() {

return works;

}

public void setWorks(List<Works> works) {

this.works = works;

}

public Writer(List<Works> works) {

this.works = works;

}

@Override

public String toString() {

return "Writer{" +

"name='" + name + '\'' +

", id=" + id +

", age=" + age +

", sex='" + sex + '\'' +

", book='" + book + '\'' +

", birthday=" + birthday +

", works=" + works +

'}';

}

public String getName() {

return name;

}

public void setName(String name) {

this.name = name;

}

public Integer getId() {

return id;

}

public void setId(Integer id) {

this.id = id;

}

public Integer getAge() {

return age;

}

public void setAge(Integer age) {

this.age = age;

}

public String getSex() {

return sex;

}

public void setSex(String sex) {

this.sex = sex;

}

public String getBook() {

return book;

}

public void setBook(String book) {

this.book = book;

}

public Date getBirthday() {

return birthday;

}

public void setBirthday(Date birthday) {

this.birthday = birthday;

}

public Writer(String name, Integer id, Integer age, String sex, String book, Date birthday) {

this.name = name;

this.id = id;

this.age = age;

this.sex = sex;

this.book = book;

this.birthday = birthday;

}

public Writer() { }

}

mapper 檔案配置

<?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">

select * from writer w

</select>

select * from writer w

<where>

id=#{id}

</where>

</select>

</collection>

</resultMap>

SELECT w.age,w.birthday,w.book,w.id,w.name,w.sex ,o.book_id,o.book_press,o.press_date,o.book_name

FROM writer w LEFT JOIN information i ON w.`id`=i.`writer_id`LEFT JOIN works o ON o.`book_id`=i.`works_id`

</select>

</mapper>

註解方式配置

@Results(value={

@Result(id=true,column="",property=""),

@Result(column="",property=""),

@Result(column="",property=""), @Result(column="tid",property="works",many=@Many(Select="com.service.iuserdao.workByid") ),

})

@Select(".......")

java 匯入 Excel 表格

1. 匯入包

<groupId>org.apache.poi</groupId>

</dependency>

<groupId>commons-io</groupId>

<artifactId>commons-io</artifactId>

</dependency>

// 匯入依賴

2. 構建方法

public class ImportExcel {

// 匯入 excel 檔案的方法

public void ReadExcel(File file){

List<String> list=new ArrayList<>();

//1. 傳入需要匯入的 Excel 檔案的路徑

try{

//2. 讀取檔案的內容

HSSFWorkbook workbook=new HSSFWorkbook(FileUtils.openInputStream(file));

//3. 讀取檔案的 sheet 頁 ,sheet 就是 Excel 中的每一張表，也就是 workboo 的 sheet 頁

HSSFSheet sheet=workbook.getSheetAt(0);

//4. 開始讀取表的內容

int startline=0;// 起始行的行數，也就是從 0 到最後一行

int endline=sheet.getLastRowNum()+1;// 最後一行 d 的下表

// 遍歷每一行

Map<Integer,Map<Integer,Object>> map=new HashMap<>();

// 第一行，第一列，值

// 遍歷行

for (int a=1;a<endline;a++){

HSSFRow hssfRow=sheet.getRow(a);

// 遍歷列

// 寫一個集合儲存列和值

Map<Integer,Object> result=new HashMap<>();

int endcell=hssfRow.getLastCellNum();

for (int j=0;j<endcell;j++){

result.put(j, hssfRow.getCell(j));// 用行數去獲取列

}

map.put(a, result);

}

List<People> list1=new ArrayList<>();

for (Integer xxx:map.keySet()){

System.out.print(map.get(xxx).get(0));

System.out.print(map.get(xxx).get(1));

System.out.print(map.get(xxx).get(2));

System.out.print(map.get(xxx).get(3));

People people=new People();

people.setName(String.valueOf(map.get(xxx).get(0)));

String java=String.valueOf(map.get(xxx).get(1));

Integer java1=Integer.parseInt(java.substring(0, java.indexOf(".")));

people.setJava(java1);

String mybas=String.valueOf(map.get(xxx).get(2));

Integer mybas1=Integer.parseInt(mybas.substring(0, mybas.indexOf(".")));

people.setMybas(mybas1);

String androi=String.valueOf(map.get(xxx).get(3));

Integer androi1=Integer.parseInt(androi.substring(0, androi.indexOf(".")));

people.setAndroi(androi1);

list1.add(people);

}

SqlSession session= Util.getSession();

PeopleMapperDao peopleMapperDao=session.getMapper(PeopleMapperDao.class);

peopleMapperDao.ExcelInsert(list1);

session.commit();

}

catch (Exception e){

e.printStackTrace();

}

3. 呼叫方法

@org.junit.Test

public void TestExcel(){

ImportExcel aaa=new ImportExcel();

File file=new File("C:/Users/lenovo/Desktop/study/ssm 筆記 /test4012.xls");

aaa.ReadExcel(file);

}

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/69946279/viewspace-2662892/，如需轉載，請註明出處，否則將追究法律責任。

java框架之mybatis

相關文章