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文件:(去掉多余的,精简后的内容)
1 <?xml version="1.0" encoding="utf-8"?> 2 <!doctype hibernate-configuration public 3 "-//hibernate/hibernate configuration dtd 3.0//en" 4 "http://www.hibernate.org/dtd/hibernate-configuration-3.0.dtd"> 5 <hibernate-configuration> 6 <session-factory> 7 <!--不采用innodb方式加快速度 --> 8 <property name="hibernate.dialect">org.hibernate.dialect.mysql5dialect</property> 9 10 <!-- 注意update方式时对于属性的删减并不会删除数据库字段 --> 11 <property name="hibernate.hbm2ddl.auto">update</property> 12 13 <!-- 注意注解的话,只能用class一个一个引用。除非与spring整合才能扫描文件夹路径 --> 14 <mapping class="com.sunwii.mybatis.bean.user" /> 15 </session-factory> 16 </hibernate-configuration>
注解的实体类:
@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<string> 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配置文件:
<?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> <!-- 别名 --> <typealiases> <!-- 指定包下所有别名为类名的简名 --> <package name="com.sunwii.mybatis.bean" /> </typealiases> <!-- 类型处理器 --> <typehandlers> <!-- 改变默认处理枚举(枚举转换为int) --> <typehandler handler="org.apache.ibatis.type.enumordinaltypehandler" javatype="com.sunwii.mybatis.enums.level" /> <!-- 自定义处理枚举(枚举转换为枚举键值对的数字值) --> <typehandler handler="com.sunwii.mybatis.typehandle.sexenumtypehandler" javatype="com.sunwii.mybatis.enums.sex" /> <!-- 自定义处理列表(列表转换为字符串连接) --> <!-- 注意,由于是非内置的转换类型,所以仅对select有效,insert/update/delete需要另行指定 --> <!-- 另行指定示例:${tels,typehandler=com.sunwii.mybatis.typehandle.listtypehandler} --> <typehandler handler="com.sunwii.mybatis.typehandle.listtypehandler" javatype="java.util.list" /> </typehandlers> <environments default="development"> <environment id="development"> <transactionmanager type="jdbc" /> <!-- 自定义mypooldatasourcefactory简化配置 --> <datasource type="com.sunwii.mybatis.pool.mypooldatasourcefactory" /> </environment> </environments> <mappers> <package name="com/sunwii/mybatis/mapper" /> </mappers> </configuration>
连接池装饰类(用于简化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(); // 更多属性可以通过<property>来设置。 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<integer, sex> valuemap = new hashmap<integer, sex>(); private static hashmap<string, sex> keymap = new hashmap<string, sex>(); 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<sex> { /** * 转换到数据库的值 */ @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<list<?>> { @suppresswarnings({ "unchecked", "rawtypes" }) @override public void setparameter(preparedstatement ps, int i, list parameter, jdbctype jdbctype) throws sqlexception { string[] strarr = arrayutil.fromlist((list<string>) parameter); string strs = arrayutil.asstring(",", strarr); ps.setstring(i, strs); } @override public list<string> getresult(resultset rs, string columnname) throws sqlexception { list<string> list = null; string strs = rs.getstring(columnname); if (strs != null && strs.length() > 0) { list = arrays.aslist(strs.split(",")); } return list; } @override public list<string> getresult(resultset rs, int columnindex) throws sqlexception { list<string> list = null; string strs = rs.getstring(columnindex); if (strs != null && strs.length() > 0) { list = arrays.aslist(strs.split(",")); } return list; } @override public list<string> getresult(callablestatement cs, int columnindex) throws sqlexception { list<string> 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 <t> list<t> aslist(t... args) { return arrays.aslist(args); } public static <t> list<t> aslistfromset(set<t> set) { if (set == null || set.size() < 1) { return null; } list<t> list = new arraylist<t>(); for (t t : set) { list.add(t); } return list; } public static <t> list<t> aslistfromarray(t[] array) { if (array == null || array.length < 1) { return null; } list<t> list = new arraylist<t>(); for (t t : array) { list.add(t); } return list; } public static <t> list<t> aslistfrommapkey(map<t, ?> map) { if (map == null || map.size() < 1) { return null; } return arrayutil.aslistfromset(map.keyset()); } public static <t> list<t> aslistfrommapvalue(map<?, t> map) { if (map == null || map.size() < 1) { return null; } list<t> list = new arraylist<t>(); collection<t> values = map.values(); for (t t : values) { list.add(t); } return list; } @suppresswarnings("unchecked") public static <t> 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> t[] fromlist(list<t> list) { if (list == null || list.size() < 1) { return null; } class<t> clz = null; for (t t : list) { clz = (class<t>) 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> t[] fromset(set<t> set) { if (set == null || set.size() < 1) { return null; } class<t> clz = null; for (t t : set) { clz = (class<t>) 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> t[] frommapkey(map<t, ?> map) { if (map == null || map.size() < 1) { return null; } set<t> set = map.keyset(); return arrayutil.fromset(set); } public static <t> t[] frommapvalue(map<?, t> map) { if (map == null || map.size() < 1) { return null; } list<t> list = new arraylist<t>(); collection<t> values = map.values(); for (t t : values) { list.add(t); } return arrayutil.fromlist(list); } @suppresswarnings("unchecked") public static <t> set<t> asset(t... args) { if (args == null || args.length < 1) { return null; } set<t> set = new hashset<t>(); for (int i = 0; i < args.length; i++) { if (!set.contains(args[i])) { set.add(args[i]); } } return set; } public static <t> set<t> assetfromarray(t[] array) { if (array == null || array.length < 1) { return null; } set<t> set = new hashset<t>(); for (t t : array) { set.add(t); } return set; } public static <t> set<t> assetfrommapkey(map<t, ?> map) { if (map == null || map.size() < 1) { return null; } return map.keyset(); } public static <t> set<t> assetfrommapvalue(map<?, t> map) { if (map == null || map.size() < 1) { return null; } set<t> set = new hashset<t>(); collection<t> values = map.values(); for (t t : values) { set.add(t); } return set; } public static <t1, t2> map<t1, t2> asmapfrom(set<t1> keyset, set<t2> valueset) { if (keyset == null || keyset.size() < 1 || valueset == null || valueset.size() < 1) { return null; } map<t1, t2> map = new hashmap<t1, t2>(); list<t2> 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 <t> 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<user> selectbyname(string name); public int insert(user user); public int update(user user); public int delete(int id); }
映射文件:usermapper.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.sunwii.mybatis.mapper.usermapper"> <select id="selectbyid" parametertype="integer" resulttype="user"> select * from t_user where id = #{id} </select> <select id="selectbyname" parametertype="string" resulttype="user"> select * from t_user where name like "%"#{name}"%" </select> <insert id="insert"> 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} ) </insert> <update id="update"> 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} </update> <delete id="delete" parametertype="integer"> delete from t_user where id=#{id} </delete> </mapper>
日期工具: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(); } 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<user> users = null; sqlsession session = sf.opensession(); usermapper usermapper = session.getmapper(usermapper.class); users = usermapper.selectbyname(username); for (user user : users) { log.info(user); } } }
<<mybatis总结之如何自动生成数据库表结构>>
说明:
这里多余的步骤仅仅是为了存档下代码(以及方便一些初学者不看代码不知道怎么回事的原因:里边涉及了各种常用类型的转换和映射)。
本文中最重要的就是hibernate的几个包的选取,以及配置文件的精简,还有就是加载hibernate的sessionfactory的方法。
这些说来说去都是hibernate的东西。跟mybatis原本是没有一点关系的。只不过需要用于,那纯属相当于复习一下hibernate了。
上一篇: BeanFactory体系结构
下一篇: PHP基于面向对象封装的分页类示例
推荐阅读
-
Mybatis总结之如何自动生成数据库表结构
-
Python使用win32com模块实现数据库表结构自动生成word表格的方法
-
oracle数据库自动生成数据库表结构文档(亲测有效)
-
Activiti工作流学习笔记之自动生成28张数据库表的底层原理解析
-
Mybatis通过数据库表自动生成实体类和xml映射文件
-
Mybatis总结之如何自动生成数据库表结构
-
Mybatis根据数据库表自动生成实体类和xml映射文件
-
Mybatis根据数据库表自动生成实体类和xml映射文件
-
Mybatis根据数据库表自动生成实体类和xml映射文件及dao
-
Python使用win32com模块实现数据库表结构自动生成word表格的方法