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<Users> queryUsers();
//添加用户
public boolean addUser(Users user);
//修改用户
public boolean changeUser(Map<String,String> map);
}
2.4、创建接口对应的Mapper(相当于dao的实现类)
UserDaoMapper.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">
<!--命名空间namespace-->
<mapper namespace="com.cugb.dao.UsersDao">
<!--sql语句-->
<select id="queryUsers" resultType="Users">
select * from users
</select>
<select id="addUser" parameterType="Users" >
insert into users (uname, upass) VALUES (#{uname},#{upass});
</select>
<update id="changeUser" parameterType="Map">
update users set upass = #{upass} where uid = #{uid};
</update>
</mapper>
2.5、创建mybatis-config.xml,配置数据库基本信息,并将mapper注册到mybatis-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>
<!--引入外部配置文件-->
<properties resource="db.properties"/>
<settings>
<setting name="logImpl" value="LOG4J"/>
</settings>
<!--可以给实体类起别名-->
<typeAliases>
<typeAlias type="com.cugb.pojo.Users" alias="users"/>
</typeAliases>
<environments default="development">
<environment id="development">
<!-- 事物管理,JDBC的事物管理-->
<transactionManager type="JDBC"/>
<!--数据源,连接池型的数据源-->
<dataSource type="POOLED">
<property name="driver" value="${driver}"/>
<property name="url" value="${url}"/>
<property name="username" value="${username}"/>
<property name="password" value="${password}"/>
</dataSource>
</environment>
</environments>
<!--绑定接口-->
<mappers>
<mapper resource="com/cugb/dao/UserDaoMapper.xml"/>
</mappers>
</configuration>
其中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<Users> 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.