Mybatis如何自动生成数据库表结构总结

Mybatis如何自动生成数据库表结构总结

一般情况下，用Mybatis的时候是先设计表结构再进行实体类以及映射文件编写的，特别是用代码生成器的时候。

但有时候不想用代码生成器，也不想定义表结构，那怎么办？

这个时候就会想到Hibernate，然后想到它的hibernate.hbm2ddl.auto配置项。

所以手工创表的问题可以很方便的迅速用Hibernate来解决。 那有人问啦：就是不想用Hibernate才换的Mybatis，你这又兜回去了吗？

其实不是的，我们需要的就是单单一个hbm2ddl功能。

其实应该这么想：有一款工具能够自动根据注解的实体类来生成各种数据库相应的表结构，只需要加几个jar包  (经测试后只要7个）并且 少量配置(3个配置项） 。

这款工具就是Hibernate。为什么不能是它呢！！！

原理说来也是超级的简单：   加入hibernate的包， 注解实体类，程序开始时初始化一下hibernate的SessionFactory并清除它。

示例：

需要的Hibernate相关的JAR包 (本例基于Hibernate5.0.7，仅需要7个）：

hibernate-core-5.0.7.Final.jar

hibernate-commons-annotations-5.0.1.Final.jar

hibernate-jpa-2.1-api-1.0.0.Final.jar

geronimo-jta_1.1_spec-1.1.1.jar

jboss-logging-3.3.0.Final.jar

dom4j-1.6.1.jar

javassist-3.18.1-GA.jar

Hibernate.cfg.xml文件：（去掉多余的，精简后的内容）

"-//Hibernate/Hibernate Configuration DTD 3.0//EN"

"http://hibernate.org/dtd/hibernate-configuration-3.0.dtd">

org.hibernate.dialect.mysql5Dialect

update

注解的实体类：

@Entity

@Table(name = "t_user")

@Data

@NoArgsConstructor

@ToString

public class User implements Serializable {

private static final long serialVersionUID = -4013951528313410972L;

@Id

@GeneratedValue(strategy = GenerationType.IDENTITY)

@Column(name = "id")

private Integer id;

@Column(length = 30)

private String name;

@Column

private Float height;

@Column

private Double salary;

@Column

private Integer vip;

@Column

@Temporal(TemporalType.DATE)

private Date birthday;

@Column

@Temporal(TemporalType.TIMESTAMP)

private Date lastlogin;

@Column

@Enumerated(EnumType.STRING)

// mybatis 默认会将枚举转化为字符串类型存储,此时数据库为varchar型

private State state;

@Column

@Enumerated(EnumType.ORDINAL)

// 可以为mybatis设置枚举类型存储为其索引值存储，此时数据库为int型

private Level level;

@Column(length = 10)

@Enumerated(EnumType.ORDINAL)

// mybatis 自定义类型转换器将枚举转化为相应数字类型存储，此时数据库为int型

private Sex sex;

@Column

@Type(type = "string")

// mybatis 自定义类型转换器将列表转化为相应字符串类型存储，此时数据库为varchar型

private List tels;

public User(int id) {

super();

this.id = id;

}

public User(int id, String name) {

super();

this.id = id;

this.name = name;

}

public User(String name) {

super();

this.name = name;

}

}

注意：以上实体类用了Lombok插件来减少代码量（只要是为了不写setter/getter、toString等。Lombok插件的使用请参考其它地方。）

@Data

@NoArgsConstructor

@ToString

三个注解属于Lombok插件注解，分别指示生成SETTER/GETTER、生成无参构造器、生成ToString

其它注解都属于Hibernate（JPA规范）的注解，生成DDL就靠它们了。

注解-说明：

@Entity

@Table(name = "t_user")

这里指实体类对应了t_user表

@Id

@GeneratedValue(strategy = GenerationType.IDENTITY)

@Column(name = "id")

这里指表主健自动增长

@Column

这里指表字段名称与属性同名,它可以另外指定长度，名称。

@Temporal(TemporalType.DATE)

这里指表字段采用Date类型存储

@Enumerated(EnumType.STRING)

这里指表字段采用varchar类型来存储枚举属性

这个简短示例仅列出简单的类型的注解，并不涉及表关联之间的注解，幸好的是，注解并不多，更多注解可以参考Hibernate(JPA)注解相关内容。

至此，只需要在使用SqlSessionFactory（Mybatis）之前就构造SessionFactory（Hibernate）然后销毁它就可以了。至于如何初始化Hibernate的SessionFactory，应该都知道。如下：

Mybatis与Spring整合时：简单地配置一个hibernate的SessionFactory相关的BEAN了事。这里不谈。

Mybatis不与Spring整合时：

Mybatis的工具类中添加新方法，用于自动构造DDL：

package com.sunwii.mybatis.util;

import java.io.IOException;

import java.io.InputStream;

import org.apache.ibatis.io.Resources;

import org.apache.ibatis.session.SqlSessionFactory;

import org.apache.ibatis.session.SqlSessionFactoryBuilder;

import com.sunwii.mybatis.constant.Constants;

public class SessionFactoryUtil {

public static SqlSessionFactory creat(String configFile) {

SqlSessionFactory factory = null;

InputStream inputStream;

try {

inputStream = Resources.getResourceAsStream(configFile);

factory = new SqlSessionFactoryBuilder().build(inputStream);

} catch (IOException e) {

// TODO Auto-generated catch block

e.printStackTrace();

}

return factory;

}

//这里是新方法，通过判断标记决定是否要用于自动生成DDL

public static SqlSessionFactory creat(String configFile, boolean hibernateAutoDdl) {

if (hibernateAutoDdl) {

String hibernateConfigFile = Constants.Hibernate_LOCATION;

// 使用hibernate自动创建DDL

HibernateUtil.buildSessionFactory(hibernateConfigFile);

}

return creat(configFile);

}

}

其中用到的Hibernate工具类为：

package com.sunwii.mybatis.util;

import java.util.Properties;

import org.hibernate.SessionFactory;

import org.hibernate.cfg.Configuration;

import com.sunwii.mybatis.constant.Constants;

public class HibernateUtil {

public static void buildSessionFactory(String hibernateConfigFile) {

String jdbcPropertiesLocation = Constants.JDBC_LOCATION;

Properties jdbcProperties = PropertiesUtil.loadFromClassPath(jdbcPropertiesLocation);

Properties hibernateJdbcProperties = new Properties();

hibernateJdbcProperties.setProperty("hibernate.connection.driver_class", jdbcProperties.getProperty("driver"));

hibernateJdbcProperties.setProperty("hibernate.connection.url", jdbcProperties.getProperty("url"));

hibernateJdbcProperties.setProperty("hibernate.connection.username", jdbcProperties.getProperty("user"));

hibernateJdbcProperties.setProperty("hibernate.connection.password", jdbcProperties.getProperty("password"));

final Configuration cfg = new Configuration();

cfg.addProperties(hibernateJdbcProperties);

cfg.configure(hibernateConfigFile);

SessionFactory sessionFactory = cfg.buildSessionFactory();

// 启动后销毁

sessionFactory.close();

sessionFactory = null;

}

}

PropertiesUtil工具类：

package com.sunwii.mybatis.util;

import java.io.IOException;

import java.io.InputStream;

import java.util.Properties;

public class PropertiesUtil {

public static Properties loadFromClassPath(String fileName) {

Properties props = new Properties();

while(fileName!=null && fileName.length()>0 && (fileName.startsWith("/") || fileName.startsWith("\\"))) {

fileName = fileName.substring(1);

}

InputStream is = Class.class.getResourceAsStream("/"+fileName);

try {

props.load(is);

} catch (IOException e) {

// TODO Auto-generated catch block

e.printStackTrace();

}

return props;

}

}

常量配置类Constant:

package com.sunwii.mybatis.constant;

public class Constants {

public static String JDBC_LOCATION = "jdbc.properties";

public static String Hibernate_LOCATION = "hibernate.cfg.xml";

}

Mybatis配置文件：

PUBLIC "-//mybatis.org//DTD Config 3.0//EN"

"http://mybatis.org/dtd/mybatis-3-config.dtd">

handler="org.apache.ibatis.type.EnumOrdinalTypeHandler"

javaType="com.sunwii.mybatis.enums.Level" />

handler="com.sunwii.mybatis.typehandle.SexEnumTypeHandler"

javaType="com.sunwii.mybatis.enums.Sex" />

handler="com.sunwii.mybatis.typehandle.ListTypeHandler"

javaType="java.util.List" />

连接池装饰类（用于简化Mybatis配置并统一jdbc配置文件路径常量）：

package com.sunwii.mybatis.pool;

import java.util.Properties;

import org.apache.ibatis.datasource.pooled.PooledDataSource;

import org.apache.ibatis.datasource.pooled.PooledDataSourceFactory;

import com.sunwii.mybatis.constant.Constants;

import com.sunwii.mybatis.util.PropertiesUtil;

public class MyPoolDataSourceFactory extends PooledDataSourceFactory {

public MyPoolDataSourceFactory() {

PooledDataSource dataSource = new PooledDataSource();

// 更多属性可以通过来设置。

String jdbcPropertiesFile = Constants.JDBC_LOCATION;

Properties prop = PropertiesUtil.loadFromClassPath(jdbcPropertiesFile);

dataSource.setDriver(prop.getProperty("driver"));

dataSource.setUrl(prop.getProperty("url"));

dataSource.setUsername(prop.getProperty("user"));

dataSource.setPassword(prop.getProperty("password"));

this.dataSource = dataSource;

}

}

用到的几个枚举类：

package com.sunwii.mybatis.enums;

public enum Level {

LEVEL_0,

LEVEL_1,

LEVEL_2,

LEVEL_3,

LEVEL_4,

LEVEL_5

}

package com.sunwii.mybatis.enums;

import java.util.HashMap;

public enum Sex {

MAN("男", 0), WOMAN("女", 1);

private String key;

public String getKey() {

return key;

}

public void setKey(String key) {

this.key = key;

}

public Integer getValue() {

return value;

}

public void setValue(Integer value) {

this.value = value;

}

private Integer value;

private static HashMap valueMap = new HashMap();

private static HashMap keyMap = new HashMap();

static {

for (Sex item : Sex.values()) {

valueMap.put(item.value, item);

keyMap.put(item.key, item);

}

}

Sex(String key, Integer value) {

this.key = key;

this.value = value;

}

public static Sex getByValue(int value) {

Sex result = valueMap.get(value);

return result;

}

public static Sex getByKey(String key) {

Sex result = keyMap.get(key);

return result;

}

}

package com.sunwii.mybatis.enums;

public enum State {

OK, ERROR, UNKNOWN

}

用到的类型转换器：

package com.sunwii.mybatis.typehandle;

import java.sql.CallableStatement;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.sql.SQLException;

import org.apache.ibatis.type.JdbcType;

import org.apache.ibatis.type.TypeHandler;

import com.sunwii.mybatis.enums.Sex;

/**

* -必须实现所有方法，不然的话查询有可能查询到为null

* @author Administrator

*

*/

public class SexEnumTypeHandler implements TypeHandler {

/**

* 转换到数据库的值

*/

@Override

public void setParameter(PreparedStatement ps, int i, Sex parameter, JdbcType jdbcType) throws SQLException {

ps.setInt(i, parameter.getValue());

}

/**

* 从数据库转换得到

*/

@Override

public Sex getResult(ResultSet rs, String columnName) throws SQLException {

return Sex.getByValue(rs.getInt(columnName));

}

/**

* 从数据库转换得到

*/

@Override

public Sex getResult(ResultSet rs, int columnIndex) throws SQLException {

return Sex.getByValue(rs.getInt(columnIndex));

}

/**

* 从数据库转换得到

*/

@Override

public Sex getResult(CallableStatement cs, int columnIndex) throws SQLException {

return Sex.getByValue(cs.getInt(columnIndex));

}

}

package com.sunwii.mybatis.typehandle;

import java.sql.CallableStatement;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.util.Arrays;

import java.util.List;

import org.apache.ibatis.type.JdbcType;

import org.apache.ibatis.type.TypeHandler;

import com.sunwii.mybatis.util.ArrayUtil;

/**

* -必须实现所有方法，不然的话查询有可能查询到为null

* @author Administrator

*

*/

public class ListTypeHandler implements TypeHandler> {

@SuppressWarnings({ "unchecked", "rawtypes" })

@Override

public void setParameter(PreparedStatement ps, int i, List parameter, JdbcType jdbcType) throws SQLException {

String[] strArr = ArrayUtil.fromList((List) parameter);

String strs = ArrayUtil.asString(",", strArr);

ps.setString(i, strs);

}

@Override

public List getResult(ResultSet rs, String columnName) throws SQLException {

List list = null;

String strs = rs.getString(columnName);

if (strs != null && strs.length() > 0) {

list = Arrays.asList(strs.split(","));

}

return list;

}

@Override

public List getResult(ResultSet rs, int columnIndex) throws SQLException {

List list = null;

String strs = rs.getString(columnIndex);

if (strs != null && strs.length() > 0) {

list = Arrays.asList(strs.split(","));

}

return list;

}

@Override

public List getResult(CallableStatement cs, int columnIndex) throws SQLException {

List list = null;

String strs = cs.getString(columnIndex);

if (strs != null && strs.length() > 0) {

list = Arrays.asList(strs.split(","));

}

return list;

}

}

用到的数组集合转换工具类：

package com.sunwii.mybatis.util;

import java.lang.reflect.Array;

import java.util.ArrayList;

import java.util.Arrays;

import java.util.Collection;

import java.util.HashMap;

import java.util.HashSet;

import java.util.List;

import java.util.Map;

import java.util.Set;

public class ArrayUtil {

@SuppressWarnings("unchecked")

public static List asList(T... args) {

return Arrays.asList(args);

}

public static List asListFromSet(Set set) {

if (set == null || set.size() < 1) {

return null;

}

List list = new Ahttp://rrayList();

for (T t : set) {

list.add(t);

}

return list;

}

public static List asListFromArray(T[] array) {

if (array == null || array.length < 1) {

return null;

}

List list = new ArrayList();

for (T t : array) {

list.add(t);

}

return list;

}

public static List asListFromMapKey(Map map) {

if (map == null || map.size() < 1) {

return null;

}

return ArrayUtil.asListFromSet(map.keySet());

}

public static List asListFromMapValue(Map map) {

if (map == null || map.size() < 1) {

return null;

}

List list = new ArrayList();

Collection values = map.values();

for (T t : values) {

list.add(t);

}

return list;

}

@SuppressWarnings("unchecked")

public static T[] fromArray(T... args) {

if (args == null || args.length < 1) {

return null;

}

T[] array = (T[]) Array.newInstance(args[0].getClass(), args.length);

for (int i = 0; i < args.length; i++) {

array[i] = args[i];

}

return array;

}

@SuppressWarnings("unchecked")

public static T[] fromList(List list) {

if (list == null || list.size() < 1) {

return null;

}

Class clz = null;

for (T t : list) {

clz = (Class) t.getClass();

break;

}

T[] array = (T[]) Array.newInstance(clz, list.size());

int i = 0;

for (T t : list) {

array[i] = t;

i++;

}

return array;

}

@SuppressWarnings("unchecked")

public static T[] fromSet(Set set) {

if (set == null || set.size() < 1) {

return null;

}

Class clz = null;

for (T t : set) {

clz = (Class) t.getClass();

break;

}

T[] array = (T[]) Array.newInstance(clz, set.size());

int i = 0;

for (T t : set) {

array[i] = t;

i++;

}

return array;

}

public static T[] fromMapKey(Map map) {

if (map == null || map.size() < 1) {

return null;

}

Set set = map.keySet();

return ArrayUtil.fromSet(set);

}

public static T[] fromMapValue(Map map) {

if (map == null || map.size() < 1) {

return null;

}

List list = new ArrayList();

Collection values = map.values();

for (T t : values) {

list.add(t);

}

return ArrayUtil.fromList(list);

}

@SuppressWarnings("unchecked")

public static Set asSet(T... args) {

if (args == null || args.length < 1) {

return null;

}

Set set = new HashSet();

for (int i = 0; i < args.length; i++) {

if (!set.contains(args[i])) {

set.add(args[i]);

}

}

return set;

}

public static Set asSetFromArray(T[] array) {

if (array == null || array.length < 1) {

return null;

}

Set set = new HashSet();

for (T t : array) {

set.add(t);

}

return set;

}

public static Set asSetFromMapKey(Map map) {

if (map == null || map.size() < 1) {

return null;

}

return map.keySet();

}

public static Set asSetFromMapValue(Map map) {

if (map == null || map.size() < 1) {

return null;

}

Set set = new HashSet();

Collection values = map.values();

for (T t : values) {

set.add(t);

}

return set;

}

public static Map asMapFrom(Set keySet, Set valueSet) {

if (keySet == null || keySet.size() < 1 || valueSet == null || valueSet.size() < 1) {

return null;

}

Map map = new HashMap();

List list = ArrayUtil.asListFromSet(valueSet);

int i = 0;

for (T1 t : keySet) {

try {

map.put(t, list.get(i++));

} catch (Exception e) {// 超长

map.put(t, null);

}

}

return map;

}

@SuppressWarnings("unchecked")

public static String asString(String separator, T... args) {

if (args == null || args.length < 1) {

return null;

}

StringBuilder sp = new StringBuilder();

for (int i = 0; i < args.length; i++) {

sp.append(args[i]);

if (i != args.length - 1) {

sp.append(separator);

}

}

return sp.toString();

}

}

Mapper接口：UserMapper.java

package com.sunwii.mybatis.mapper;

import java.util.List;

import com.sunwii.mybatis.bean.User;

public interface UserMapper {

public User selectById(int id);

public List selectByName(String name);

public int insert(User user);

public int update(User user);

public int delete(int id);

}

映射文件：UserMapper.xml

PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"

"http://mybatis.org/dtd/mybatis-3-mapper.dtd">

resultType="User">

select * from t_user where id = #{id}

resultType="User">

select * from t_user where name like "%"#{name}"%"

insert into

t_user(

name, birthday, vip, salary, height, lastlogin,level,state,sex,tels

)values(

#{name},

#{birthday},

#{vip},

#{salary},

#{height},

#{lastlogin},

#{level},

#{state},

#{sex},

#{tels,typeHandler=com.sunwii.mybatis.typehandle.ListTypeHandler}

)

update t_user set

name=#{name},

birthday=#{birthday},

vip=#{vip},

salary=#{salary},

height=#{height},

lastlogin=#{lastlogin},

level=#{level},

state=#{state},

sex=#{sex},

tels=#{tels,typeHandler=com.sunwii.mybatis.typehandle.ListTypeHandler}

where id=#{id}

delete from t_user where

id=#{id}

日期工具：CurrentUtil.java

package com.sunwii.mybatis.util;

import java.sql.Timestamp;

import java.util.Date;

public class CurrentUtil {

public static long currentTime() {

return new Date().getTime();

}

public static Date currentDate() {

return new Date();

}

public static java.sql.Date currentSqlDate() {

return new java.sql.Date(currentTime());

}

public static Timestamp currentTimestamp() {

return new java.sql.Timestamp(currentTime());

}

}

测试示例：

package com.sunwii.mybatis.test.mapper;

import java.util.Arrays;

import java.util.List;

import org.apache.commons.logging.Log;

import org.apache.commons.logging.LogFactory;

import org.apache.ibatis.session.SqlSession;

import org.apache.ibatis.session.SqlSessionFactory;

import org.junit.Test;

import com.sunwii.mybatis.bean.User;

import com.sunwii.mybatis.enums.Level;

import com.sunwii.mybatis.enums.Sex;

import com.sunwii.mybatis.enums.State;

import com.sunwii.mybatis.mapper.UserMapper;

import com.sunwii.mybatis.test.insert.TestInsert;

import com.sunwii.mybatis.util.CurrentUtil;

import com.sunwii.mybatis.util.SessionFactoryUtil;

public class testMapper {

private static Log log = LogFactory.getLog(TestInsert.class);

private static SqlSessionFactory sf = SessionFactoryUtil.creat("mybatis-config.xml", true);

@Test

public void testMapperInsert() {

User user = new User();

//user.setId(50);

user.setName("sunwii");

user.setVip(1);

user.setSalary(3333.00);

user.setHeight(1.70f);

user.setBirthday(CurrentUtil.currentDate());

user.setLastlogin(CurrentUtil.currentTimestamp());

user.setLevel(Level.LEVEL_3);

user.setState(State.OK);

user.setSex(Sex.WOMAN);

user.setTels(Arrays.asList("133xxxxxxx", "159xxxxxxxx"));

int rs = 0;

SqlSession session = sf.openSession();

UserMapper userMapper = session.getMapper(UserMapper.class);

try {

rs = userMapper.insert(user);

session.commit();

} catch (Exception e) {

rs = 0;

session.rollback();

e.printStackTrace();

} finally {

session.close();

http:// }

log.info("操作结果：" + rs);

}

@Test

public void testMapperUpdate() {

User user = new User();

user.setId(1);

user.setName("sunwii--55550");

user.setVip(1);

user.setSalary(3333.00);

user.setHeight(1.70f);

user.setBirthday(CurrentUtil.currentDate());

user.setLastlogin(CurrentUtil.currentTimestamp());

user.setLevel(Level.LEVEL_2);

user.setState(State.ERROR);

user.setSex(Sex.MAN);

user.setTels(Arrays.asList("136xxxxxx", "139xxxxxxx"));

int rs = 0;

SqlSession session = sf.openSession();

UserMapper userMapper = session.getMapper(UserMapper.class);

try {

rs = userMapper.update(user);

session.commit();

} catch (Exception e) {

rs = 0;

session.rollback();

e.printStackTrace();

} finally {

session.close();

}

log.info("操作结果：" + rs);

}

@Test

public void testMapperDelete() {

User user = new User(50);

int rs = 0;

SqlSession session = sf.openSession();

UserMapper userMapper = session.getMapper(UserMapper.class);

try {

rs = userMapper.delete(user.getId());

session.commit();

} catch (Exception e) {

rs = 0;

session.rollback();

e.printStackTrace();

} finally {

session.close();

}

log.info("操作结果：" + rs);

}

@Test

public void testMapperGetOne() {

Integer id = 50;

User user = null;

SqlSession session = sf.openSession();

UserMapper userMapper = session.getMapper(UserMapper.class);

user = userMapper.selectById(id);

log.info(user);

}

@Test

public void testMapperGetList() {

String userName = "sunwii";

List users = null;

SqlSession session = sf.openSession();

UserMapper userMapper = session.getMapper(UserMapper.class);

users = userMapper.selectByName(userName);

for (User user : users) {

log.info(user);

}

}

}

<>

说明：

这里多余的步骤仅仅是为了存档下代码（以及方便一些初学者不看代码不知道怎么回事的原因:里边涉及了各种常用类型的转换和映射）。

本文中最重要的就是Hibernate的几个包的选取，以及配置文件的精简，还有就是加载Hibernate的SessionFactory的方法。

这些说来说去都是Hibernate的东西。跟Mybatis原本是没有一点关系的。只不过需要用于，那纯属相当于复习一下Hibernate了。

总结

以上就是这篇文章的全部内容了，希望本文的内容对大家的学习或者工作具有一定的参考学习价值，谢谢大家对我们的支持。

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。