最近,接一个需求,要求清洗存量数据,开发一个可以打印日志的小程序。
经过多天的实验,最终采用mybatis的操作数据库方式,cmd中执行 java -jar clear.jar,来执行方法清洗数据库数据。
第一步,创建一个java project 命名为cleaner
第二步,在src目录下,创建mapper.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="mapper">
<!--根据关联项目表,获取全部数据 -->
<select id="selectDsqpIP" resultType="vo.DsqpInterPlanInfo">
select a.* from dsqp_inter_plan_info a ,dsqp_inter_pro_info b where a.pro_uuid=b.pkid and a.hs_summary='投产'
</select>
<!--根据关联项目表,获取全部数据 -->
<select id="selectPlan" parameterType="String" resultType="vo.ApmProInfo">
select * from apm_pro_info b where b.inter_pro_info_pkid=#{id}
</select>
<!--更新apm_pro_info表数据 -->
<update id="updateOneApmProInfo" parameterType="vo.EmpAndExtend">
update apm_pro_info set operate_date=#{endDate} where inter_pro_info_pkid=#{projectid}
</update>
<!--根据关联项目表,获取全部数据 -->
<select id="selectDsqpInterProInfo" resultType="vo.DsqpInterProInfo">
select * from
dsqp_inter_pro_info
</select>
<!--根据关联开发项目的pkid查询相关联的创建的测试项目 -->
<select id="selectApmProInfo" parameterType="String" resultType="vo.ApmProInfo">
select * from apm_pro_info where inter_pro_info_pkid=#{id}
</select>
<!--根据关联开发项目的pkid查询相关联的创建的测试项目 -->
<select id="ApmProExtend" parameterType="int" resultType="vo.ApmProExtend">
select
* from apm_pro_proextend where proid=#{id}
</select>
<!--根据关联开发项目的所属中心查询组织表的orgid、orgname -->
<select id="Organization" parameterType="String" resultType="vo.Organization">
select * from org_organization where orgname=#{center}
</select>
<!--根据所属中心和pm查询 hfuser和 org_employee -->
<select id="EmpAndExtend" parameterType="String" resultType="vo.EmpAndExtend">
select a.userno,a.username,c.empid from dsqp_hf_user a,org_employee c
where a.userno=c.empcode and a.blngbrchnm=#{project_center} and
a.username=#{pm}
</select>
<!-- 插入apm_pro_proextend表数据 -->
<!--在ibatis中不需要关注这些参数 而转到mybatis后 如果字段值为空 必须设置jdbcType -->
<insert id="insertOne" parameterType="vo.ApmProExtend">
insert into
apm_pro_proextend values
(#{proid,jdbcType=NUMERIC},#{orgid,jdbcType=NUMERIC},#{orgname,jdbcType=VARCHAR},#{empid,jdbcType=VARCHAR},
#{empname,jdbcType=VARCHAR},#{userid,jdbcType=VARCHAR},#{num,jdbcType=INTEGER})
</insert>
<!--更新apm_pro_proextend表数据 -->
<update id="updateOne" parameterType="vo.ApmProExtend">
update apm_pro_proextend
<trim prefix="set" suffixOverrides=",">
<if test="empid!=null">empid=#{empid},</if>
<if test="empname!=null">empname=#{empname},</if>
<if test="userid!=null">userid=#{userid},</if>
<if test="orgid!=null">orgid=#{orgid},</if>
<if test="orgname!=null">orgname=#{orgname},</if>
</trim>
WHERE proid=#{proid}
</update>
</mapper>
第四步:src在创建mybatisConf.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>
<properties resource="dataSource.properties" />
<environments default="development">
<environment id="development">
<transactionManager type="jdbc" />
<dataSource type="pooled">
<property name="driver" value="${driver}" />
<property name="url" value="${url}" />
<property name="username" value="${username}" />
<property name="password" value="${password}" />
</dataSource>
</environment>
</environments>
<mappers>
<mapper resource="mapper.xml" />
</mappers>
</configuration>
第五步:dataSource.properties;
url=jdbc:oracle:thin:@xxx.xxx.xxx.xxx:1521:orcl
username=xxxx
password =xxxx
driver=oracle.jdbc.driver.OracleDriver
第六步:创建vo实体类;
第七步:编写services的main方法
第八步:点击左侧项目名,右键选择export-->runnable jar file-->launch configuration -->选择main主方法,library handling-->如果有引用第三方jar包选择copy required libraries into a sub-folder next to the generated jar.-->finish
第九步:cmd 进入到打包所在的路径下,执行java -jar 打包名.jar;
完美启动;