Mybatis總結之如何自動生成數據庫表結構
- 2019 年 11 月 1 日
- 筆記
一般情況下，用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>
其它東西不需要，只需要在使用SqlSessionFactory（Mybatis）之前就構造SessionFactory（Hibernate）然後銷毀它就可以了。
與Spring整合時：按照正常的Hibernate與Spring整合的方案就可以。
不與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"; }
示例的實體類User：
@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插件，
@Data @NoArgsConstructor @ToString
三個註解屬於Lombok插件註解，分別指示生成SETTER/GETTER、生成無參構造器、生成ToString
其它註解都屬於Hibernate（JPA規範）的註解，生成DDL就靠它們了。
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(); } }
映射文件：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>
測試示例：
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了。
