JDBC 测试工程
新建javaProject, 导入:数据库驱动包 mysql-connector-java-5.1.46-bin.jar
package com.zcl.test;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class JdbcTest {
public static void main(String[] args) {
//链接对象
Connection con = null;
//查询对象
PreparedStatement ps = null;
//结果集对象
ResultSet rs = null;
try {
//加载数据库驱动
Class.forName("com.mysql.jdbc.Driver");
//获取连接
con = DriverManager.getConnection("jdbc:mysql://localhost:3306/ssm_mybatis?useUnicode=true&characterEncoding=utf-8&useSSL=false", "root", "123");
String sql = "SELECt * FROM user WHERe u_sex = ?";
//获取statement
ps = con.prepareStatement(sql );
ps.setString(1, "0");
//查询 输出结果
rs = ps.executeQuery();
while(rs.next()) {
System.out.println(rs.getString("u_id") + " " + rs.getString("u_username") +" " + rs.getString("u_sex"));
}
} catch (Exception e) {
e.printStackTrace();
}finally {
if(rs != null) {
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(ps != null) {
try {
ps.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(con != null) {
try {
con.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
}
Mybatis测试工程:
新建javaProject, 导入:
数据库驱动包: mysql-connector-java-5.1.46-bin.jar ojdbc7.jar
mybatis包: mybatis-3.4.6.jar
依赖包:
创建javaBean对象
User
package com.sikiedu.bean;
import java.util.Date;
public class User {
private Integer u_id;
private String u_username;
private String u_password;
private String u_sex;
private Date u_createTime;
private Integer u_cid;
public Integer getU_id() {
return u_id;
}
public void setU_id(Integer u_id) {
this.u_id = u_id;
}
public String getU_username() {
return u_username;
}
public void setU_username(String u_username) {
this.u_username = u_username;
}
public String getU_password() {
return u_password;
}
public void setU_password(String u_password) {
this.u_password = u_password;
}
public String getU_sex() {
return u_sex;
}
public void setU_sex(String u_sex) {
this.u_sex = u_sex;
}
public Date getU_createTime() {
return u_createTime;
}
public void setU_createTime(Date u_createTime) {
this.u_createTime = u_createTime;
}
public Integer getU_cid() {
return u_cid;
}
public void setU_cid(Integer u_cid) {
this.u_cid = u_cid;
}
@Override
public String toString() {
return "User [u_id=" + u_id + ", u_username=" + u_username + ", u_password=" + u_password + ", u_sex=" + u_sex
+ ", u_createTime=" + u_createTime + ", u_cid=" + u_cid + "]";
}
}
创建主配置文件
sqlMapCOnfig.xml
创建UserMapper配置文件
select * from user where u_id = #{id}
使用
HelloMyBatis .java
package com.sikiedu.test;
import java.io.IOException;
import java.io.InputStream;
import java.util.Date;
import java.util.List;
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 com.sikiedu.bean.User;
public class HelloMyBatis {
@Test
//入门程序 通过ID 查询用户
public void Test1() throws IOException {
String resource = "sqlMapConfig.xml";
//读取配置文件
InputStream in = Resources.getResourceAsStream(resource );
//需要sqlSessionFactoryBulider
SqlSessionFactoryBuilder ssfb = new SqlSessionFactoryBuilder();
//创建sqlSessionfactory
SqlSessionFactory ssf = ssfb.build(in);
//生产一个sqlSession
SqlSession session = ssf.openSession();
//操作数据库
//参数1:要操作的sql语句 参数2 sql语句的参数
User user = session.selectOne("UserMapper.selectUserById", 1);
System.out.println(user);
}
@Test
//入门程序 通过ID 查询用户
public void Test2() throws IOException {
String resource = "sqlMapConfig.xml";
//读取配置文件
InputStream in = Resources.getResourceAsStream(resource );
//需要sqlSessionFactoryBulider
SqlSessionFactoryBuilder ssfb = new SqlSessionFactoryBuilder();
//创建sqlSessionfactory
SqlSessionFactory ssf = ssfb.build(in);
//生产一个sqlSession
SqlSession session = ssf.openSession();
//操作数据库
//参数1:要操作的sql语句 参数2 sql语句的参数
List list = session.selectList("UserMapper.selectUserByName", "王");
for (User u : list) {
System.out.println(u);
}
}
@Test
//入门程序 通过ID 查询用户
public void Test3() throws IOException {
String resource = "sqlMapConfig.xml";
//读取配置文件
InputStream in = Resources.getResourceAsStream(resource );
//需要sqlSessionFactoryBulider
SqlSessionFactoryBuilder ssfb = new SqlSessionFactoryBuilder();
//创建sqlSessionfactory
SqlSessionFactory ssf = ssfb.build(in);
//生产一个sqlSession
SqlSession session = ssf.openSession();
//操作数据库
//参数1:要操作的sql语句 参数2 sql语句的参数
User user = new User();
user.setU_username("小明1");
user.setU_password("123123");
user.setU_sex("1");
user.setU_createTime(new Date());
user.setU_cid(1);
session.insert("UserMapper.insertUser", user);
session.commit();
}
@Test
//入门程序 通过ID 查询用户
public void Test4() throws IOException {
String resource = "sqlMapConfig.xml";
//读取配置文件
InputStream in = Resources.getResourceAsStream(resource );
//需要sqlSessionFactoryBulider
SqlSessionFactoryBuilder ssfb = new SqlSessionFactoryBuilder();
//创建sqlSessionfactory
SqlSessionFactory ssf = ssfb.build(in);
//生产一个sqlSession
SqlSession session = ssf.openSession();
//操作数据库
//参数1:要操作的sql语句 参数2 sql语句的参数
User user = new User();
user.setU_id(13);
user.setU_username("小刚");
session.update("UserMapper.updateUser", user);
session.commit();
}
@Test
//入门程序 通过ID 查询用户
public void Test5() throws IOException {
String resource = "sqlMapConfig.xml";
//读取配置文件
InputStream in = Resources.getResourceAsStream(resource );
//需要sqlSessionFactoryBulider
SqlSessionFactoryBuilder ssfb = new SqlSessionFactoryBuilder();
//创建sqlSessionfactory
SqlSessionFactory ssf = ssfb.build(in);
//生产一个sqlSession
SqlSession session = ssf.openSession();
//操作数据库
//参数1:要操作的sql语句 参数2 sql语句的参数
session.delete("UserMapper.deleteUserById", 13);
session.commit();
}
}
DAO层开发
其实就是对上面的使用进一步封装
package com.sikiedu.dao;
import com.sikiedu.bean.User;
public interface UserDao {
//根据id查询用户
public User getUserById(Integer id);
}
package com.sikiedu.dao;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import com.sikiedu.bean.User;
public class UserDaoImpl implements UserDao {
//sqlSession工厂
private SqlSessionFactory ssf;
//通过构造器给ssf 赋值
public UserDaoImpl(SqlSessionFactory ssf) {
this.ssf = ssf;
}
@Override
public User getUserById(Integer id) {
//生产一个sqlSession
SqlSession session = ssf.openSession();
//操作数据库
//参数1:要操作的sql语句 参数2 sql语句的参数
return session.selectOne("UserMapper.selectUserById", id);
}
}
DAO测试
package com.sikiedu.test;
import java.io.IOException;
import java.io.InputStream;
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 com.sikiedu.bean.User;
import com.sikiedu.dao.UserDao;
import com.sikiedu.dao.UserDaoImpl;
public class UserDaoTest {
//sqlSession工厂
private static SqlSessionFactory ssf;
static {
String resource = "sqlMapConfig.xml";
InputStream in;
try {
in = Resources.getResourceAsStream(resource );
SqlSessionFactoryBuilder ssfb = new SqlSessionFactoryBuilder();
ssf = ssfb.build(in);
in.close();
} catch (IOException e) {
e.printStackTrace();
}
}
@Test
public void DaoTest() {
UserDao dao = new UserDaoImpl(ssf);
User user = dao.getUserById(1);
System.out.println(user);
}
}
动态代理开发
//mapper动态代理开发四大原则 + 一个注意
//1、接口方法名需要与mapper.xml的要调用的sql语句的id一致
//2、接口的形参类型需要与mapper.xml parameterType 一致
//3、接口的返回值需要与mapper.xml resultType一致
//4、mapper.xml 中namespace要与接口的全包名一致
//5、注意mapper动态代理开发中,根据返回值类型来自动选择
package com.sikiedu.mapper;
import java.util.List;
import com.sikiedu.bean.User;
import com.sikiedu.bean.UserVo;
public interface UserMapper {
//mapper动态代理开发四大原则 + 一个注意
//1、接口方法名需要与mapper.xml的要调用的sql语句的id一致
//2、接口的形参类型需要与mapper.xml parameterType 一致
//3、接口的返回值需要与mapper.xml resultType一致
//4、mapper.xml 中namespace要与接口的全包名一致
//5、注意mapper动态代理开发中,根据返回值类型来自动选择
//通过id查询一个用户
public User selectUserById(Integer id);
//通过用户名模糊查询 获取用户列表
public List selectUserByName(String name);
//通过UserVo id查询一个用户
public User selectUserByUserVoId(UserVo vo);
//查询用户总条数
public Integer selectUserCount();
//查询所有用户包装类
public List selectAllUserVo();
//多条件查询 通过用户对象中的条件查询用户列表
public List selectUserListByUser(User u);
//多条件查询 通过用户对象中的条件查询用户列表Trim
public List selectUserListByUserTrim(User u);
//更新用户表
public void updateSetUser(User u);
//使用多个id获取用户列表 by array
public List selectUserListByIds(Integer[] ids);
//使用多个id获取用户列表 by list
public List selectUserListByList(List idList);
//使用多个id获取用户列表 by userVO
public List selectUserListByUserVo(UserVo vo);
}
Mapper动态代理开发测试
package com.sikiedu.test;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;
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 com.sikiedu.bean.Country;
import com.sikiedu.bean.CountryVo;
import com.sikiedu.bean.User;
import com.sikiedu.bean.UserVo;
import com.sikiedu.mapper.CountryMapper;
import com.sikiedu.mapper.UserMapper;
public class MapperTest {
@Test
public void Test1() throws IOException {
String resource = "sqlMapConfig.xml";
//读取配置文件
InputStream in = Resources.getResourceAsStream(resource );
//创建sqlSessionfactory
SqlSessionFactory ssf = new SqlSessionFactoryBuilder().build(in);
//生产一个sqlSession
SqlSession session = ssf.openSession();
UserMapper mapper = session.getMapper(UserMapper.class);
User user = mapper.selectUserById(1);
System.out.println(user);
}
@Test
public void Test2() throws IOException {
String resource = "sqlMapConfig.xml";
//读取配置文件
InputStream in = Resources.getResourceAsStream(resource );
//创建sqlSessionfactory
SqlSessionFactory ssf = new SqlSessionFactoryBuilder().build(in);
//生产一个sqlSession
SqlSession session = ssf.openSession();
UserMapper mapper = session.getMapper(UserMapper.class);
List list = mapper.selectUserByName("王");
for (User u : list) {
System.out.println(u);
}
}
@Test
public void Test_selectUserByUserVoId() throws IOException {
String resource = "sqlMapConfig.xml";
//读取配置文件
InputStream in = Resources.getResourceAsStream(resource );
//创建sqlSessionfactory
SqlSessionFactory ssf = new SqlSessionFactoryBuilder().build(in);
//生产一个sqlSession
SqlSession session = ssf.openSession();
UserMapper mapper = session.getMapper(UserMapper.class);
UserVo vo = new UserVo();
User u = new User();
u.setU_id(5);
//vo.setUser(u);
User user = mapper.selectUserByUserVoId(vo );
System.out.println(user);
}
@Test
public void Test_selectUserCount() throws IOException {
String resource = "sqlMapConfig.xml";
//读取配置文件
InputStream in = Resources.getResourceAsStream(resource );
//创建sqlSessionfactory
SqlSessionFactory ssf = new SqlSessionFactoryBuilder().build(in);
//生产一个sqlSession
SqlSession session = ssf.openSession();
UserMapper mapper = session.getMapper(UserMapper.class);
Integer count = mapper.selectUserCount();
System.out.println(count);
}
@Test
public void Test_selectAll() throws IOException {
String resource = "sqlMapConfig.xml";
//读取配置文件
InputStream in = Resources.getResourceAsStream(resource );
//创建sqlSessionfactory
SqlSessionFactory ssf = new SqlSessionFactoryBuilder().build(in);
//生产一个sqlSession
SqlSession session = ssf.openSession();
CountryMapper mapper = session.getMapper(CountryMapper.class);
List list = mapper.selectAll();
for (Country country : list) {
System.out.println(country);
}
}
@Test
public void Test_selectAllUserVo() throws IOException {
String resource = "sqlMapConfig.xml";
//读取配置文件
InputStream in = Resources.getResourceAsStream(resource );
//创建sqlSessionfactory
SqlSessionFactory ssf = new SqlSessionFactoryBuilder().build(in);
//生产一个sqlSession
SqlSession session = ssf.openSession();
UserMapper mapper = session.getMapper(UserMapper.class);
List list = mapper.selectAllUserVo();
for (UserVo userVo : list) {
System.out.println(userVo);
}
}
@Test
public void Test_selectAllCountryVo() throws IOException {
String resource = "sqlMapConfig.xml";
//读取配置文件
InputStream in = Resources.getResourceAsStream(resource );
//创建sqlSessionfactory
SqlSessionFactory ssf = new SqlSessionFactoryBuilder().build(in);
//生产一个sqlSession
SqlSession session = ssf.openSession();
CountryMapper mapper = session.getMapper(CountryMapper.class);
List list = mapper.selectAllCountryVo();
for (CountryVo countryVo : list) {
System.out.println(countryVo);
}
}
@Test
public void Test_selectUserListByUser() throws IOException {
String resource = "sqlMapConfig.xml";
//读取配置文件
InputStream in = Resources.getResourceAsStream(resource );
//创建sqlSessionfactory
SqlSessionFactory ssf = new SqlSessionFactoryBuilder().build(in);
//生产一个sqlSession
SqlSession session = ssf.openSession();
UserMapper mapper = session.getMapper(UserMapper.class);
User u = new User();
u.setU_sex("1");
u.setU_username("王");
//u.setU_cid(1);
List list = mapper.selectUserListByUser(u);
for (User user : list) {
System.out.println(user);
}
}
@Test
public void Test_selectUserListByUserTrim() throws IOException {
String resource = "sqlMapConfig.xml";
//读取配置文件
InputStream in = Resources.getResourceAsStream(resource );
//创建sqlSessionfactory
SqlSessionFactory ssf = new SqlSessionFactoryBuilder().build(in);
//生产一个sqlSession
SqlSession session = ssf.openSession();
UserMapper mapper = session.getMapper(UserMapper.class);
User u = new User();
u.setU_sex("1");
u.setU_username("王");
//u.setU_cid(1);
List list = mapper.selectUserListByUserTrim(u);
for (User user : list) {
System.out.println(user);
}
}
@Test
public void Test_updateSetUser() throws IOException {
String resource = "sqlMapConfig.xml";
InputStream in = Resources.getResourceAsStream(resource );
SqlSessionFactory ssf = new SqlSessionFactoryBuilder().build(in);
SqlSession session = ssf.openSession();
UserMapper mapper = session.getMapper(UserMapper.class);
User u = new User();
u.setU_id(1);
u.setU_username("隔壁老王");
u.setU_password("aaa");
//u.setU_sex("1");
mapper.updateSetUser(u);
session.commit();
}
@Test
public void Test_selectUserListByIds() throws IOException {
String resource = "sqlMapConfig.xml";
InputStream in = Resources.getResourceAsStream(resource );
SqlSessionFactory ssf = new SqlSessionFactoryBuilder().build(in);
SqlSession session = ssf.openSession();
UserMapper mapper = session.getMapper(UserMapper.class);
Integer[] ids = {1, 3, 5, 12};
List list = mapper.selectUserListByIds(ids);
for (User user : list) {
System.out.println(user);
}
}
@Test
public void Test_selectUserListByList() throws IOException {
String resource = "sqlMapConfig.xml";
InputStream in = Resources.getResourceAsStream(resource );
SqlSessionFactory ssf = new SqlSessionFactoryBuilder().build(in);
SqlSession session = ssf.openSession();
UserMapper mapper = session.getMapper(UserMapper.class);
List idList = new ArrayList();
idList.add(1);
idList.add(4);
idList.add(8);
List list = mapper.selectUserListByList(idList );
for (User user : list) {
System.out.println(user);
}
}
@Test
public void Test_selectUserListByUserVo() throws IOException {
String resource = "sqlMapConfig.xml";
InputStream in = Resources.getResourceAsStream(resource );
SqlSessionFactory ssf = new SqlSessionFactoryBuilder().build(in);
SqlSession session = ssf.openSession();
UserMapper mapper = session.getMapper(UserMapper.class);
List idList = new ArrayList();
idList.add(1);
idList.add(4);
idList.add(8);
UserVo vo = new UserVo();
vo.setIdList(idList);
List list = mapper.selectUserListByUserVo(vo );
for (User user : list) {
System.out.println(user);
}
}
}
MGB
根据数据库表生成 bean对象, java 接口, sqlMapper 配置文件, 是不是很强大啊
创建新的Java工程
复制配置文件, 导入依赖包, 运行就可以生成我们需要的文件了 。
Generator.java
package com.sikiedu.test;
import java.io.File;
import java.util.ArrayList;
import java.util.List;
import org.mybatis.generator.api.MyBatisGenerator;
import org.mybatis.generator.config.Configuration;
import org.mybatis.generator.config.xml.ConfigurationParser;
import org.mybatis.generator.internal.DefaultShellCallback;
public class Generator {
public static void main(String[] args) throws Exception {
List warnings = new ArrayList();
boolean overwrite = true;
File configFile = new File("src/generatorConfig.xml");
ConfigurationParser cp = new ConfigurationParser(warnings);
Configuration config = cp.parseConfiguration(configFile);
DefaultShellCallback callback = new DefaultShellCallback(overwrite);
MyBatisGenerator myBatisGenerator = new MyBatisGenerator(config, callback, warnings);
myBatisGenerator.generate(null);
}
}
如何写配置文件 :
generatorConfig.xml
使用生成的文件去操作数据库
package com.sikiedu.test;
import java.io.IOException;
import java.io.InputStream;
import java.util.List;
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 com.sikiedu.bean.User;
import com.sikiedu.bean.UserExample;
import com.sikiedu.mapper.UserMapper;
public class MapperTest {
@Test
public void Test1() throws IOException {
String resource = "sqlMapConfig.xml";
InputStream in = Resources.getResourceAsStream(resource );
SqlSessionFactory ssf = new SqlSessionFactoryBuilder().build(in);
SqlSession session = ssf.openSession();
UserMapper mapper = session.getMapper(UserMapper.class);
//按住键查询
User user = mapper.selectByPrimaryKey(1);
System.out.println(user);
}
@Test
public void Test2() throws IOException {
String resource = "sqlMapConfig.xml";
InputStream in = Resources.getResourceAsStream(resource );
SqlSessionFactory ssf = new SqlSessionFactoryBuilder().build(in);
SqlSession session = ssf.openSession();
UserMapper mapper = session.getMapper(UserMapper.class);
UserExample example = new UserExample();
//将条件封装到createCriteria集合中
example.createCriteria().andUSexEqualTo("1").andUUsernameLike("%王%");
//按条件查询
List list = mapper.selectByExample(example);
for (User user : list) {
System.out.println(user);
}
}
@Test
public void Test3() throws IOException {
String resource = "sqlMapConfig.xml";
InputStream in = Resources.getResourceAsStream(resource );
SqlSessionFactory ssf = new SqlSessionFactoryBuilder().build(in);
SqlSession session = ssf.openSession();
UserMapper mapper = session.getMapper(UserMapper.class);
User user = new User() ;
user.setuUsername("赵云");
mapper.insertSelective(user);
session.commit();
}
}



