Mybatis连接数据库实例 + 完整代码

Mybatis连接数据库实例

1、 数据库表

1.1、创建表

CREATE TABLE `users`(
`uid` INT NOT NULL AUTO_INCREMENT COMMENT '用户id',
`uname` VARCHAR(20) NOT NULL COMMENT '用户名',
`upass` VARCHAR(20) NOT NULL COMMENT '用户密码',
PRIMARY KEY (`uid`)
)ENGINE = INNODB DEFAULT CHARSET = utf8;

1.2、插入数据

INSERT INTO `users` (`uname`,`upass`) VALUES
('zhangwuji','123456'),
('zhaomin','123456'),
('zhouzhiruo','123456'),
('xiaozhao','123456');

1.3、查看表

?

2、 java部分

2.1根据数据库表中的字段创建pojo对象

package com.cugb.pojo;

import lombok.ToString;

/**
 * @author huangjian
 * @data 2020/7/6 11:11
 */
public class Users {
    //用户id
    private  int uid;

    //用户名
    private String uname;

    //密码
    private String upass;

    public Users() {
    }

    public Users(int uid, String uname, String upass) {
        this.uid = uid;
        this.uname = uname;
        this.upass = upass;
    }

    public int getUid() {
        return uid;
    }

    public void setUid(int uid) {
        this.uid = uid;
    }

    public String getUname() {
        return uname;
    }

    public void setUname(String uname) {
        this.uname = uname;
    }

    public String getUpass() {
        return upass;
    }

    public void setUpass(String upass) {
        this.upass = upass;
    }

    @Override
    public String toString() {
        return "uid:" +  uid +  "\t" +  "uname:" +  uname+  "\t" +"upass:" +  upass;
    }
}

2.2、dao层创建接口，并添加等方法

import com.cugb.pojo.Users;


import java.util.ArrayList;

public interface UsersDao {

	//查询用户
	public ArrayList queryUsers();

	//添加用户
	public boolean addUser(Users user);

	//修改用户
	public boolean changeUser(Map map);
	

}

2.4、创建接口对应的Mapper(相当于dao的实现类)

UserDaoMapper.xml

<?xml version="1.0" encoding="UTF-8" ?>




    
    
        select * from users
    

    
        insert into users (uname, upass) VALUES (#{uname},#{upass});
    

    
        update users set upass = #{upass} where uid = #{uid};
    
    

2.5、创建mybatis-config.xml,配置数据库基本信息，并将mapper注册到mybatis-config.xml

<?xml version="1.0" encoding="UTF-8" ?>




    
    

    
        
    
    
        
           
        

    
        

            
            

            
            
                
                
                
                
            
        
    

    
    
        
    
        

其中db.properties内容如下

driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/mybatis?characterEncoding=UTF-8
username=root
password=123456

3、测试

3.1测试代码

package com.cugb.test;

import com.cugb.dao.UsersDao;
import com.cugb.pojo.Users;
import lombok.SneakyThrows;
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.junit.Test;

import javax.management.Query;
import java.io.InputStream;
import java.util.ArrayList;

/**
 * @author huangjian
 * @data 2020/7/6 14:42
 */
public class UserDaoTest2 {

    @SneakyThrows
    @Test
    public void Query() {
        //加载配置文件
        InputStream in = Resources.getResourceAsStream("mybatis-config.xml");
		
        //获取SqlSessionFactory 
        SqlSessionFactory sqlSessionFactory = new  SqlSessionFactoryBuilder().build(in);
		
        //获取SqlSession
        SqlSession sqlSession = sqlSessionFactory.openSession();
		
        UsersDao usersDao = sqlSession.getMapper(UsersDao.class);

        ArrayList list = usersDao.queryUsers();

        for (Users users : list) {
            System.out.println(users);
        }

        sqlSession.close();

    }
}

3.2、结果

[org.apache.ibatis.transaction.jdbc.JdbcTransaction]-Opening JDBC Connection
[org.apache.ibatis.datasource.pooled.PooledDataSource]-Created connection 1848415041.
[org.apache.ibatis.transaction.jdbc.JdbcTransaction]-Setting autocommit to false on JDBC Connection [com.mysql.jdbc.JDBC4Connection@6e2c9341]
[com.cugb.dao.UsersDao.queryUsers]-==>  Preparing: select * from users 
[com.cugb.dao.UsersDao.queryUsers]-==> Parameters: 
[com.cugb.dao.UsersDao.queryUsers]-<==      Total: 4
uid:1	uname:zhangwuji	upass:123456
uid:2	uname:zhaomin	upass:123456
uid:3	uname:zhouzhiruo	upass:123456
uid:4	uname:xiaozhao	upass:123456
[org.apache.ibatis.transaction.jdbc.JdbcTransaction]-Resetting autocommit to true on JDBC Connection [com.mysql.jdbc.JDBC4Connection@6e2c9341]
[org.apache.ibatis.transaction.jdbc.JdbcTransaction]-Closing JDBC Connection [com.mysql.jdbc.JDBC4Connection@6e2c9341]
[org.apache.ibatis.datasource.pooled.PooledDataSource]-Returned connection 1848415041 to pool.