Mybatis原生API数据库的增删改查的详细笔记
原生api总结:
1.不用写开Connection,给?存值等繁琐的jdbc代码,
2.总清单文件只写一次,子清单文件可以写多个,都放得sql语句
3.调用原生mybatis原生API,指定号sql语句id ,参数的类型和结果类型即可
一.各种配置文件
1.项目配置 Maven项目的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/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<groupId>com.tarena</groupId>
<artifactId>testmybatis</artifactId>
<version>0.0.1-SNAPSHOT</version>
<properties>
<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
</properties>
<dependencies>
<!-- https://mvnrepository.com/artifact/org.mybatis/mybatis -->
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis</artifactId>
<version>3.4.5</version>
</dependency>
<!-- mysql的驱动 -->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.6</version>
</dependency>
<!-- log4j的依赖包 -->
<dependency>
<groupId>log4j</groupId>
<artifactId>log4j</artifactId>
<version>1.2.17</version>
</dependency>
</dependencies>
<build>
<plugins>
<plugin>
<groupId>org.apache.maven.plugins</groupId>
<artifactId>maven-compiler-plugin</artifactId>
<version>2.0.2</version>
<configuration>
<source>1.8</source>
<target>1.8</target>
<encoding>${project.build.sourceEncoding}</encoding>
</configuration>
</plugin>
</plugins>
</build>
</project>
2.Mybatis的总配置文件用来获取连接池配置文件的信息 数据库的信息 configuration.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>
<properties resource="conf/mysql.properties"></properties>
<typeAliases>
<typeAlias type="com.tarena.entity.User" alias="User"/>
</typeAliases>
<environments default="development">
<environment id="development">
<transactionManager type="JDBC" />
<dataSource type="POOLED">
<property name="driver" value="${jdbc_driverClass}" />
<property name="url" value="${jdbc_url}" />
<property name="username" value="${jdbc_userName}" />
<property name="password" value="${jdbc_userPassword}" />
</dataSource>
</environment>
</environments>
<mappers>
<mapper resource="mapper/user.xml" />
<mapper resource="mapper/UserMapper.xml" />
</mappers>
</configuration>
3.数据库配置文件信息 mysql.properties
jdbc_driverClass=com.mysql.jdbc.Driver
jdbc_url=jdbc:mysql://localhost:3306/user
jdbc_userName=root
jdbc_userPassword=123456
4.Mybatis的子配置文件 用来写sql语句的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="com.xxx.yyy">
<!-- 在一个命名空间里面ID是唯一的 -->
<!-- 根据id查询用户信息 -->
<!-- username (数据库)name(userBean),
userpassword password, -->
<select id="findUserById"
resultType="com.tarena.entity.User"
parameterType="java.lang.Integer">
select
id,
username name,
userpassword password,
address
from t_user
where id = #{id}
</select>
<!-- 查询所有的数据
resultType="com.tarena.entity.User" 返回结果类型 -->
<select id="findAllUser_user"
resultType="com.tarena.entity.User">
select
id,
username name,
userpassword password,
address
from t_user
</select>
<select id="findAllUser_map"
resultType="java.util.Map">
select
id,
username name,
userpassword password,
address
from t_user
</select>
<!-- 增删改 -->
<!-- 插入数据
#{name} name->Name->getName
去parameterType指定的类中寻找,找到就反射调用
-->
<insert id="addUser"
parameterType="com.tarena.entity.User">
insert into t_user
(
username,
userpassword,
address
)
values
(
#{name},
#{password},
#{address}
)
</insert>
<!-- 删除用户 -->
<delete id="deleteUser"
parameterType="java.lang.Integer">
delete from t_user where id=#{id}
</delete>
<!-- 更新用户信息 -->
<update id="updateUser"
parameterType="com.tarena.entity.User">
update t_user set
username=#{name},
userpassword=#{password},
address=#{address}
where
id=#{id}
</update>
</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">
<mapper namespace="com.tarena.dao.UserMapper">
<!-- namespace必须为包名.接口名 -->
<!-- 根据id查询一个对象
id="findUserById" 必须接口中的方法名称
parameterType="java.lang.Integer" 必须对应接口方法参数类型
resultType="User" 必须对应接口中的方法的返回类型
-->
<select id="findUserById"
parameterType="java.lang.Integer"
resultType="User">
select
id,
username name,
userpassword password,
address
from
t_user
where id=#{id}
</select>
</mapper>
二.工具类的代码实现
package com.tarena.util;
import java.io.IOException;
import java.io.InputStream;
import java.sql.DriverManager;
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 com.mysql.jdbc.Connection;
/**
* 此工具用来获取mybatis的连接对象sqlSession 等同于jdbc中的 Connection
* @author admin
*
*/
public class MyBatisUtil {
//工厂对象
private static SqlSessionFactory sqlSessionFactory;
static{
try {
String resource = "conf/configuration.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
sqlSessionFactory =
new SqlSessionFactoryBuilder().build(inputStream);
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
public static SqlSession getSession(){
//开一个连接 事务也开启了
SqlSession sqlSession=sqlSessionFactory.openSession();
return sqlSession;
}
}
三.数据库的增删改查代码实现
1.添加代码
public int addUser(User user){
int rowAffect=0;
SqlSession sqlSession=null;
try{
sqlSession=MyBatisUtil.getSession();//获取连接
rowAffect=sqlSession.insert("com.xxx.yyy.addUser",user);//通过一个int类型获取传输的值
sqlSession.commit();//提交事务
}catch(Exception e){
e.printStackTrace();
sqlSession.rollback();//出现异常回滚事务
}finally{
sqlSession.close(); //关闭
}
return rowAffect; //返回
}
2.删除代码
public int deleteUser(Integer id) {
int rowAffect=0;
SqlSession sqlSession=null;
try{
sqlSession=MyBatisUtil.getSession();
rowAffect=sqlSession.delete("deleteUser",id);
sqlSession.commit();
}catch(Exception e){
e.printStackTrace();
sqlSession.rollback();
}finally{
sqlSession.close();
}
return rowAffect;
}
3.修改代码
@Override
public int updateUser(User user) {
int rowAffect=0;
SqlSession sqlSession=null;
try{
sqlSession=MyBatisUtil.getSession();
rowAffect=sqlSession.update("updateUser",user);
sqlSession.commit();
}catch(Exception e){
e.printStackTrace();
sqlSession.rollback();
}finally{
sqlSession.close();
}
return rowAffect;
}
实现
@Test
public void testUpdateUser(){
UserDao userDao=new UserDaoImpl();
User user=new User();
user.setId(7);
user.setName("eee");
user.setPassword("eee");
user.setAddress("eee");
int rowAffect=userDao.updateUser(user);
System.out.println(rowAffect);
}
4.查询代码
//查询单个数据
@Override
public User findUserById(Integer id) {
User user=null;
SqlSession sqlSession=null;
try{
sqlSession=MyBatisUtil.getSession(); //获取连接
user =sqlSession.selectOne("com.xxx.yyy.findUserById",2);
sqlSession.commit();//开启事务
}catch(Exception e){
e.printStackTrace();
sqlSession.rollback();
}finally{
sqlSession.close();
}
return user;
}
实现
@Test
public void testFindUserById(){
UserDao userDao=new UserDaoImpl();
User user=userDao.findUserById(2);
System.out.println(user);
}
4.1查询代码
//查询所有数据 用list集合存储
@Override
public List<User> findAllUsers1() {
List<User> users=null;
SqlSession sqlSession=null;
try{
sqlSession=MyBatisUtil.getSession();
users=sqlSession.selectList("findAllUser_user");
sqlSession.commit();
}catch(Exception e){
e.printStackTrace();
sqlSession.rollback();
}finally{
sqlSession.close();
}
return users;
}
实现
@Test
public void testFindAllUsers1(){
UserDao userDao=new UserDaoImpl();
List<User> users=userDao.findAllUsers1();
for(User user:users){
System.out.println(user);
}
}
4.2查询代码 ------list
@Override
public List<Map<String,Object>> findAllUsers2() {
List<Map<String,Object>> users=null; //在list集合中存储Map映射的键值对
SqlSession sqlSession=null;
try{
sqlSession=MyBatisUtil.getSession();
users=sqlSession.selectList("findAllUser_map");
sqlSession.commit();
}catch(Exception e){
e.printStackTrace();
sqlSession.rollback();
}finally{
sqlSession.close();
}
return users;
}
//通过debug看到 key是
实现
@Test
public void testFindAllUsers2(){
UserDao userDao=new UserDaoImpl();
List<Map<String,Object>> users=userDao.findAllUsers2();
for(Map<String,Object> user : users){
System.out.println("id="+user.get("id")
+" username="+user.get("name")
+" userpassword="+user.get("password")
+" userAddress="+user.get("address"));
}
}
4.3查询代码-----MAP
@Override
public Map<Integer, User> findAllUsers3() {
Map<Integer,User> users=null;
SqlSession sqlSession=null;
try{
sqlSession=MyBatisUtil.getSession();
users=sqlSession.selectMap("findAllUser_user","id"); //第一个是sql语句跟xml
//id 为Key 主键 为Key User表数据为value
sqlSession.commit();
}catch(Exception e){
e.printStackTrace();
sqlSession.rollback();
}finally{
sqlSession.close();
}
return users;
}
实现
@Test
public void testFindAllUsers3(){
UserDao userDao=new UserDaoImpl();
Map<Integer,User> users=userDao.findAllUsers3();
for(Integer key: users.keySet()){
System.out.println(key); //输出所有Kye
}
for(User user:users.values()){
System.out.println(user);
}
}
4.4查询代码---map(map)
@Override
public Map<String, Map<String, Object>> findAllUsers4() {
Map<String, Map<String, Object>> users=null;
SqlSession sqlSession=null;
try{
sqlSession=MyBatisUtil.getSession();
users=sqlSession.selectMap("findAllUser_map","name"); //用name当key
sqlSession.commit();
}catch(Exception e){
e.printStackTrace();
sqlSession.rollback();
}finally{
sqlSession.close();
}
return users;
}
实现
@Test
public void testFindAllUsers4(){
UserDao userDao=new UserDaoImpl();
Map<String,Map<String,Object>> users=userDao.findAllUsers4();
for(String key : users.keySet()){
System.out.println(key);//拿到最外面map的key
}
for(Map<String,Object> user : users.values()){ //拿到里面的key
System.out.println("id="+user.get("id")
+" username="+user.get("name")
+" userpassword="+user.get("password")
+" userAddress="+user.get("address"));
}
}
4.5 输出json格式的数据
通过策略模式和回调来实现json数据 ResultHandler 是专门处理数据的 例如 json ,xml 格式数据
class JsonHandler implements ResultHandler<User>{
//[{"id":1,"name":"aa","password":"aa","address":"aa"},{},{},{}]
//{"key1":{},"key2":{}...}
StringBuilder sb=new StringBuilder("["); //设置全局属性 为下面获取json数据做铺垫
@Override
public void handleResult(ResultContext<? extends User> resultContext) {
// TODO Auto-generated method stub\
//获取一个用户数据
User user =resultContext.getResultObject();
//把数据封装成json字符串
sb.append("{");
sb.append("\"id\"");
sb.append(":");
sb.append(user.getId());
sb.append(",");
sb.append("\"name\"");
sb.append(":");
sb.append("\""+user.getName()+"\"");
sb.append(",");
sb.append("\"password\"");
sb.append(":");
sb.append("\""+user.getPassword()+"\"");
sb.append(",");
sb.append("\"address\"");
sb.append(":");
sb.append("\""+user.getAddress()+"\"");
sb.append("}");
sb.append(",");
}
public String getJson(){
String temp=sb.toString();
String temp1=temp.substring(0, temp.length()-1)+"]"; //分割 多个数据分割拼装
return temp1;
}
}
@Override
public String findAllUser5() {
String json=null;
SqlSession sqlSession=null;
try{
sqlSession=MyBatisUtil.getSession();
JsonHandler handler=new JsonHandler();
sqlSession.select("findAllUser_user", handler); //接口的子实现对象
json=handler.getJson(); //获取json数据
sqlSession.commit();
}catch(Exception e){
e.printStackTrace();
sqlSession.rollback();
}finally{
sqlSession.close();
}
return json;
}
实现
@Test
public void testFindAllUsers5(){
UserDao userDao=new UserDaoImpl();
String json=userDao.findAllUser5();
System.out.println(json);
}
4.6显示代码