mybatis-3.5.2增删查改
这玩意没啥技术含量,只不过长时间不用,就显得生疏了。正好今天想读取一个文件写入数据库,平时都是集成环境,都碰不到这些基础东西,看来还是需要时常给自己找活干。
首先建立一个maven工程。
next
next
Finish
引入依赖
<?xml version="1.0" encoding="UTF-8"?> <project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd"> <modelVersion>4.0.0</modelVersion> <groupId>com.demo</groupId> <artifactId>mybatis-crud</artifactId> <version>1.0-SNAPSHOT</version> <dependencies> <!-- https://mvnrepository.com/artifact/com.alibaba/fastjson --> <dependency> <groupId>com.alibaba</groupId> <artifactId>fastjson</artifactId> <version>1.2.59</version> </dependency> <!-- https://mvnrepository.com/artifact/com.belerweb/pinyin4j --> <dependency> <groupId>com.belerweb</groupId> <artifactId>pinyin4j</artifactId> <version>2.5.1</version> </dependency> <!-- https://mvnrepository.com/artifact/org.projectlombok/lombok --> <dependency> <groupId>org.projectlombok</groupId> <artifactId>lombok</artifactId> <version>1.18.8</version> <scope>provided</scope> </dependency> <!-- https://mvnrepository.com/artifact/org.mybatis/mybatis --> <dependency> <groupId>org.mybatis</groupId> <artifactId>mybatis</artifactId> <version>3.5.2</version> </dependency> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <version>5.1.47</version> </dependency> </dependencies> <build> <plugins> <plugin> <groupId>org.apache.maven.plugins</groupId> <artifactId>maven-compiler-plugin</artifactId> <version>3.7.0</version> <configuration> <source>1.8</source> <target>1.8</target> <encoding>UTF-8</encoding> </configuration> </plugin> </plugins> </build> </project>
这里呢,我是读取一个JSON文件,所以需要JSON解析;然后我还需要识别字段名称首字母,所以还引入了pinyin4j;
最终目录结构
数据表:
CREATE TABLE `province` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(32) DEFAULT NULL COMMENT '名称', `level` tinyint(4) DEFAULT NULL COMMENT '级别(1-省;2-市;3-区)', `province` varchar(2) DEFAULT NULL COMMENT '省编号', `city` varchar(2) DEFAULT NULL COMMENT '市编号', `county` varchar(2) DEFAULT NULL COMMENT '区编号', `letter_sort` char(1) DEFAULT NULL COMMENT 'A-Z排序(仅限市级字母开头)', `create_time` datetime DEFAULT NULL COMMENT '创建时间', `create_user` varchar(32) DEFAULT NULL COMMENT '创建人', `update_time` datetime DEFAULT NULL COMMENT '更新时间', `update_user` varchar(32) DEFAULT NULL COMMENT '更新人', `delete_flag` tinyint(1) DEFAULT '0' COMMENT '删除标志(0-否;1-是)', PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COMMENT='省市区';
实体类:
package com.demo.entity; import lombok.Getter; import lombok.Setter; import lombok.ToString; import java.util.Date; @Getter @Setter @ToString public class Province { private int id; private String name; private int level; private String sheng; private String di; private String xian; private String letterSort; private Date createTime; private String createUser; private Date updateTime; private String updateUser; }
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> <environments default="development"> <environment id="development"> <transactionManager type="JDBC"/> <dataSource type="POOLED"> <property name="driver" value="com.mysql.jdbc.Driver"/> <property name="url" value="jdbc:mysql://localhost/mytest?useSSL=false"/> <property name="username" value="root"/> <property name="password" value="1234"/> </dataSource> </environment> </environments> <mappers> <mapper resource="mapper/ProvinceMapper.xml"/> </mappers> </configuration>
ProvinceMapper
package com.demo.dao; import com.demo.entity.Province; import org.apache.ibatis.annotations.Param; import java.util.List; public interface ProvinceMapper { int insert(Province province); List<Province> getList(@Param("limitStart") int limitStart, @Param("limitEnd") int limitEnd); int update(Province province); int delete(int id); }
ProvinceMapper.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.demo.dao.ProvinceMapper"> <resultMap id="BaseMap" type="com.demo.entity.Province"> <id column="id" property="id"/> <result column="name" property="name"/> <result column="level" property="level"/> <result column="province" property="sheng"/> <result column="city" property="di"/> <result column="county" property="xian"/> <result column="letter_sort" property="letterSort"/> <result column="create_time" property="createTime"/> <result column="create_user" property="createUser"/> <result column="update_time" property="updateTime"/> <result column="update_user" property="updateUser"/> </resultMap> <insert id="insert" parameterType="com.demo.entity.Province"> insert into province(name, level, province, city, county, letter_sort, create_time, create_user, update_time, update_user) values(#{name},#{level},#{sheng},#{di},#{xian},#{letterSort},#{createTime},#{createUser},#{updateTime},#{updateUser}) </insert> <select id="getList" resultMap="BaseMap"> select * from province limit #{limitStart},#{limitEnd} </select> <update id="update" parameterType="com.demo.entity.Province"> update province set name = #{name} where id = #{id} </update> <delete id="delete" parameterType="int"> delete from province where id = #{id} </delete> </mapper>
ProvinceUtils
package com.demo; import com.alibaba.fastjson.JSONArray; import com.demo.entity.Province; import com.demo.dao.ProvinceMapper; import net.sourceforge.pinyin4j.PinyinHelper; import net.sourceforge.pinyin4j.format.HanyuPinyinCaseType; import net.sourceforge.pinyin4j.format.HanyuPinyinOutputFormat; import net.sourceforge.pinyin4j.format.exception.BadHanyuPinyinOutputFormatCombination; 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 java.io.*; import java.util.Date; import java.util.List; public class ProvinceUtils { private static void readFileToDB() throws IOException { FileInputStream in = new FileInputStream("省-市-县.json"); BufferedReader reader = new BufferedReader(new InputStreamReader(in)); StringBuilder sb = new StringBuilder(); String t; while ((t = reader.readLine()) != null){ sb.append(t); } List<Province> cities = JSONArray.parseArray(sb.toString(), Province.class); cities.forEach(e -> { e.setLetterSort(getFirstSpell(e.getName())); e.setCreateTime(new Date()); e.setUpdateTime(new Date()); e.setCreateUser("system"); e.setUpdateUser("system"); }); save(getSession(), cities); } /** * 获取首汉字拼音字母 * @param chinese * @return */ private static String getFirstSpell(String chinese) { HanyuPinyinOutputFormat defaultFormat = new HanyuPinyinOutputFormat(); defaultFormat.setCaseType(HanyuPinyinCaseType.UPPERCASE); String[] temp = new String[0]; try { temp = PinyinHelper.toHanyuPinyinStringArray(chinese.charAt(0), defaultFormat); } catch (BadHanyuPinyinOutputFormatCombination e) { e.printStackTrace(); } return temp[0].charAt(0)+""; } private static void save(SqlSession sqlSession, List<Province> cities){ ProvinceMapper mapper = sqlSession.getMapper(ProvinceMapper.class); cities.forEach(e -> { if (!e.getName().equals("市辖区")){ int i = mapper.insert(e); System.out.println(i + ":" + e); } }); sqlSession.commit(); sqlSession.close(); } /** * 获取session * @return * @throws IOException */ private static SqlSession getSession() throws IOException { String resource = "mybatis-config.xml"; InputStream inputStream = Resources.getResourceAsStream(resource); SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(inputStream); return factory.openSession(); } // ---------------------------------------------------------- /** * 测试插入 * @throws IOException */ private static void insertDemo() throws IOException { SqlSession session = getSession(); Province province = new Province(); province.setName("Hello!!!"); ProvinceMapper mapper = session.getMapper(ProvinceMapper.class); int i = mapper.insert(province); session.commit(); System.out.println(i); } /** * 测试读取 * @throws IOException */ private static void selectDemo() throws IOException { ProvinceMapper mapper = getSession().getMapper(ProvinceMapper.class); mapper.getList(0, 10).forEach(System.out::println); } /** * 测试更新 * @throws IOException */ private static void updateDemo() throws IOException { SqlSession session = getSession(); Province province = new Province(); province.setId(3230); province.setName("Change!!!"); ProvinceMapper mapper = session.getMapper(ProvinceMapper.class); int i = mapper.update(province); session.commit(); System.out.println(i); } /** * 测试删除 * @throws IOException */ private static void deleteDemo() throws IOException { SqlSession session = getSession(); ProvinceMapper mapper = session.getMapper(ProvinceMapper.class); mapper.delete(3230); session.commit(); } public static void main(String[] args) throws Exception { // readFileToDB(); // insertDemo(); // selectDemo(); // updateDemo(); // deleteDemo(); } }
插入结果:
更新结果:
读取结果:
如果你想要那个JSON文件:https://github.com/Mysakura/DataFiles
小LUA
面对敌人的严刑逼供,我一个字也没说,而是一五一十写了下来。