Mybatis(一)
1.百度搜索maven,在其中搜索mybatis并点击第一个
2.导入架包和mysql的架包(依赖)
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.17</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.mybatis/mybatis -->
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis</artifactId>
<version>3.5.2</version>
</dependency>
3.然后在main文件夹下建立resources文件夹并且标记,然后在它里面创建一个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>
<environments default="development"> <!--development:开发模式-->
<environment id="development">
<transactionManager type="JDBC"/>
<!--配置数据库连接信息-->
<dataSource type="POOLED">
<property name="driver" value="com.mysql.cj.jdbc.Driver"/>
<property name="url" value="jdbc:mysql://localhost:3306/bbs?characterEncoding=utf8&useSSL=false&serverTimezone=UTC&rewriteBatchedStatements=true"/>
<property name="username" value="root"/>
<property name="password" value="root"/>
</dataSource>
</environment>
</environments>
</configuration>
4.建立bean包,要求属性名要与数据库中的列名完全一致
5.建立mapper包,并创建一个接口和一个xml文件
UserMapper.java
package mapper;
import bean.UserInfo;
import java.util.List;
public interface UserMapper {
public List<UserInfo> findAllUser();//调用这个方法返回List<UserInfo>集合 Mybatis已经写好了
}
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="mapper.UserMapper">
<select id="findAllUser" resultType="bean.UserInfo"><!--id:是UserMapper接口中声明的方法 resultType:是你查询结果返回的类型,就封装到了UserInfo对象中了-->
SELECT * FROM userinfo
</select>
</mapper>
6.然后在config.xml中加载所有的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>
<environments default="development"> <!--development:开发模式-->
<environment id="development">
<transactionManager type="JDBC"/>
<!--配置数据库连接信息-->
<dataSource type="POOLED">
<property name="driver" value="com.mysql.cj.jdbc.Driver"/>
<property name="url" value="jdbc:mysql://localhost:3306/db1?characterEncoding=utf8&useSSL=false&serverTimezone=UTC&rewriteBatchedStatements=true"/>
<property name="username" value="root"/>
<property name="password" value="root"/>
</dataSource>
</environment>
</environments>
<mappers><!--加载所有的xml文件-->
<mapper resource="mapper/UserMapper.xml"/>
</mappers>
</configuration>
7.测试一下
package test;
import bean.UserInfo;
import mapper.UserMapper;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import java.io.InputStream;
import java.util.List;
public class Test {
public static void main(String[] args) {
//1.加载config.xml文件
InputStream in = Test.class.getClassLoader().getResourceAsStream("config.xml");
//2.得到一个会话工厂 这两个类无任何关系
SqlSessionFactory ssf = new SqlSessionFactoryBuilder().build(in);
//3.打开一个会话(它包装了一个Connection)
SqlSession session = ssf.openSession();
//4.得到相应对象
UserMapper um = session.getMapper(UserMapper.class);
List<UserInfo> list = um.findAllUser();
for (UserInfo ui:list) {
System.out.println(ui);
}
//5.释放资源
session.close();
}
}
8.结果:
9.增删改都要提交
UserMapper.java
public interface UserMapper {
public List<UserInfo> findAllUser();//调用这个方法返回List<UserInfo>集合 Mybatis已经写好了
void saveUser();
}
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="mapper.UserMapper">
<select id="findAllUser" resultType="bean.UserInfo"><!--id:是UserMapper接口中声明的方法 resultType:是你查询结果返回的类型,就封装到了UserInfo对象中了-->
SELECT * FROM userinfo
</select>
<insert id="saveUser">
insert into userinfo values(75,'牛逼','男',66,'西安',md5('222'),1);
</insert>
</mapper>
Test.java
public class Test {
public static void main(String[] args) {
//1.加载config.xml文件
InputStream in = Test.class.getClassLoader().getResourceAsStream("config.xml");
//2.得到一个会话工厂 这两个类无任何关系
SqlSessionFactory ssf = new SqlSessionFactoryBuilder().build(in);
//3.打开一个会话(它包装了一个Connection)
SqlSession session = ssf.openSession();
//4.得到相应对象
UserMapper um = session.getMapper(UserMapper.class);
List<UserInfo> list = um.findAllUser();
for (UserInfo ui:list) {
System.out.println(ui);
}
um.saveUser();
session.commit();//注意:增删改要提交 否则sql执行不成功
//5.释放资源
session.close();
}
}
10.如果要自己传递参数的话
UserMapper.java
public interface UserMapper {
public List<UserInfo> findAllUser();//调用这个方法返回List<UserInfo>集合 Mybatis已经写好了
void saveUser();
void saveAnyUser(UserInfo userInfo);
}
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="mapper.UserMapper">
<select id="findAllUser" resultType="bean.UserInfo"><!--id:是UserMapper接口中声明的方法 resultType:是你查询结果返回的类型,就封装到了UserInfo对象中了-->
SELECT * FROM userinfo
</select>
<insert id="saveUser">
insert into userinfo values(75,'牛逼','男',66,'西安',md5('222'),1);
</insert>
<insert id="saveAnyUser" parameterType="bean.UserInfo">
insert into userinfo values(#{userId},#{userName},#{userSex},#{userAge},#{userAddress},#{userPass},#{userState}); <!--这里的格式就相当于当时dao包下的 ? -->
</insert>
</mapper>
Test.java
public class Test {
public static void main(String[] args) {
//1.加载config.xml文件
InputStream in = Test.class.getClassLoader().getResourceAsStream("config.xml");
//2.得到一个会话工厂 这两个类无任何关系
SqlSessionFactory ssf = new SqlSessionFactoryBuilder().build(in);
//3.打开一个会话(它包装了一个Connection)
SqlSession session = ssf.openSession();
//4.得到相应对象
UserMapper um = session.getMapper(UserMapper.class);
UserInfo ui = new UserInfo();
ui.setUserId(75);
ui.setUserName("厉害啦");
ui.setUserSex("男");
ui.setUserAge(16);
ui.setUserAddress("海岛");
ui.setUserPass("md5('222')");
ui.setUserState(1);
um.saveAnyUser(ui);
session.commit();
//5.释放资源
session.close();
}
}
11.$和#的区别:见代码
UserMapper.java
public interface UserMapper {
UserInfo findUserById1(int id);
UserInfo findUserById2(int id);
UserInfo findUserByName1(String name);
UserInfo findUserByName2(String name);
}
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="mapper.UserMapper">
<select id="findUserById1" resultType="bean.UserInfo" parameterType="int">
select * from userinfo where userid = #{userId};<!--#符号格式相当于 ?通配符 然后通过赋值来完成-->
</select>
<select id="findUserById2" resultType="bean.UserInfo" parameterType="int">
select * from userinfo where userid = ${userId};<!--$符号格式是直接追加-->
</select>
<select id="findUserByName1" resultType="bean.UserInfo" parameterType="String">
select * from userinfo where username = #{userName};<!--#符号格式相当于 ?通配符 然后通过赋值来完成-->
</select>
<select id="findUserByName2" resultType="bean.UserInfo" parameterType="String">
select * from userinfo where username = ${userName};<!--$符号格式是直接追加,所以当传入时应该加上单引号-->
</select>
</mapper>
Test.java
public class Test {
public static void main(String[] args) {
//1.加载config.xml文件
InputStream in = Test.class.getClassLoader().getResourceAsStream("config.xml");
//2.得到一个会话工厂 这两个类无任何关系
SqlSessionFactory ssf = new SqlSessionFactoryBuilder().build(in);
//3.打开一个会话(它包装了一个Connection)
SqlSession session = ssf.openSession();
//4.得到相应对象
UserMapper um = session.getMapper(UserMapper.class);
//um.findUserById1(1);①
//um.findUserById2(1);②
//um.findUserByName1("白莲女");③
//um.findUserByName2("白莲女");//报错,因为没有''④
um.findUserByName2("'白莲女'");⑤
//5.释放资源
session.close();
}
}
config.xml中加入下面代码
<configuration>
<settings><!--控制台可以输出sql语句-->
<setting name="logImpl" value="STDOUT_LOGGING"/>
</settings>
运行图:
①
②
③
④
⑤
12.多参数传递
UserMapper.java
public interface UserMapper {
//多参数传递问题
//List<UserInfo> findUser1(String name,String sex);//报错,严格来说括号里面只能有一个参数
List<UserInfo> findUser1(UserInfo userInfo);
List<UserInfo> findUser2(Map<String,Object> map);
}
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="mapper.UserMapper">
<select id="findUser1" resultType="bean.UserInfo" parameterType="bean.UserInfo">
select * from userinfo where username like #{userName} and usersex = ${userSex};
</select>
<select id="findUser2" resultType="bean.UserInfo" >
select * from userinfo where username like #{nnnn} and usersex = ${dddd};
</select>
</mapper>
Test.java
public class Test {
public static void main(String[] args) {
//1.加载config.xml文件
InputStream in = Test.class.getClassLoader().getResourceAsStream("config.xml");
//2.得到一个会话工厂 这两个类无任何关系
SqlSessionFactory ssf = new SqlSessionFactoryBuilder().build(in);
//3.打开一个会话(它包装了一个Connection)
SqlSession session = ssf.openSession();
//4.得到相应对象
UserMapper um = session.getMapper(UserMapper.class);
/*UserInfo userInfo = new UserInfo();
userInfo.setUserName("白莲女");
userInfo.setUserSex("'女'");//这里必须加上单引号,因为是拼接
um.findUser1(userInfo);*/
Map<String,Object> map = new HashMap<>();
map.put("nnnn","白莲女");
map.put("dddd","'女'");//这里也必须加上单引号,因为是拼接
um.findUser2(map);
//5.释放资源
session.close();
}
}
13.名称不匹配情况
UserMapper.java
public interface UserMapper {
//名称不匹配情况
List<UserInfo> findUser3();//通过as
List<UserInfo> findUser4();//通过手动映射
}
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="mapper.UserMapper">
<select id="findUser3" resultType="bean.UserInfo"><!--解决名称不匹配1 as-->
select userid,username,usersex,userage1 as 'userAge',useraddress from userinfo;
</select>
<!--解决名称不匹配2 resultMAp手动映射 resultType是自动的-->
<resultMap id="abcd1" type="bean.UserInfo">
<id property="userId" column="userid"/><!--主键用id-->
<result property="userName" column="username"/><!--非主键用result-->
<result property="userAge" column="userAge1"/>
</resultMap>
<select id="findUser4" resultMap="abcd1">
select * from userinfo
</select>
</mapper>
Test.java
public class Test {
public static void main(String[] args) {
//1.加载config.xml文件
InputStream in = Test.class.getClassLoader().getResourceAsStream("config.xml");
//2.得到一个会话工厂 这两个类无任何关系
SqlSessionFactory ssf = new SqlSessionFactoryBuilder().build(in);
//3.打开一个会话(它包装了一个Connection)
SqlSession session = ssf.openSession();
//4.得到相应对象
UserMapper um = session.getMapper(UserMapper.class);
List<UserInfo> list = um.findUser4();
for (UserInfo userInfo:list) {
System.out.println(userInfo);
}
//5.释放资源
session.close();
}
}