本系统主要实现了springboot对Access数据库的查询
主要技术
- springboot
- JDK1.6
- Mybatis
- Access数据库分页模糊查询
- Layui开源框架的前端Table页面分页及查询
关键代码介绍
application.yml
spring:
datasource:
driver-class-name: sun.jdbc.odbc.JdbcOdbcDriver
url: jdbc:odbc:land
tomcat:
connection-properties: charSet=gbk
main:
banner-mode: off
mybatis:
mapper-locations: classpath:mapper/*.xml
- 对Access数据库采用的是
odbc方式的连接,这样应该也只适应于windows系统- 关键代码是
connection-properties: charSet=gbk这里使用的是springboot自带的连接池也就是tomcat7以后带的连接池,其他的连接池也应当有此方法,这个编码不写的话 查询都是乱码无法进行
普通的JDBC代码如下
Properties prop = new Properties();
prop.put("charSet", "gb2312"); // 这里是解决中文乱码
prop.put("user", "");
prop.put("password", "");
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
Connection conn = DriverManager.getConnection(url,prop);
POM.xml
这里给出主要的代码
<parent>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-parent</artifactId>
<version>1.4.7.RELEASE</version>
<relativePath/> <!-- lookup parent from repository -->
</parent>
<properties>
<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
<project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding>
<java.version>1.6</java.version>
<tomcat.version>7.0.59</tomcat.version>
<jackson.version>2.6.0</jackson.version>
</properties>
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-jdbc</artifactId>
<exclusions>
<exclusion>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-logging</artifactId>
</exclusion>
</exclusions>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
<exclusions>
<exclusion>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-logging</artifactId>
</exclusion>
</exclusions>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-tomcat</artifactId>
<scope>provided</scope>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
</dependency>
<!-- log4j. -->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-log4j</artifactId>
<version>1.3.8.RELEASE</version>
</dependency>
<!--<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-data-jpa</artifactId>
<exclusions>
<exclusion>
<groupId>javax.transaction</groupId>
<artifactId>javax.transaction-api</artifactId>
</exclusion>
</exclusions>
</dependency>
<!– https://mvnrepository.com/artifact/org.jboss.spec.javax.transaction/jboss-transaction-api_1.2_spec –>
<dependency>
<groupId>org.jboss.spec.javax.transaction</groupId>
<artifactId>jboss-transaction-api_1.2_spec</artifactId>
<version>1.0.0.Final</version>
</dependency>-->
<!--mybatis-->
<dependency>
<groupId>org.mybatis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
<version>1.1.1</version>
</dependency>
<dependency>
<groupId>org.apache.tomcat</groupId>
<artifactId>tomcat-juli</artifactId>
<version>${tomcat.version}</version>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
</dependency>
</dependencies>
1.使得springboot支持JDK1.6有点费事,下边给出主要方法,在上边的代码中均有体现
- 修改Tomcat和JDK的值
<properties>
<tomcat.version>7.0.59</tomcat.version>
<java.version>1.6</java.version>
</properties>
<dependency>
<groupId>org.apache.tomcat</groupId>
<artifactId>tomcat-juli</artifactId>
<version>${tomcat.version}</version>
</dependency>
- 2.修改Jackson的版本
由于默认的Jackson是2.7版本需要在JDK1.7下运行，需要修改为1.6
<jackson.version>2.6.0</jackson.version> - 3.事务
虽然JTA(Java Transaction API)事务本身不需要JDK7,但是官方的API Jar包包含在JDK1.7基础上编译的文件。如果你使用了JTA,你需要将官方的JTA 1.2 API jar 替换成可以在JDK1.6上运行的，例如用
依赖于org.jboss.spec.javax.transaction:jboss-transaction-api_1.2_spec:1.0.0.Final去代替javax.transaction:javax.transaction-api
分页主要代码
<sql id="wheresql">
<where>
<if test="certno!=null and certno!=''">
[证号] LIKE '%'+#{certno}+'%'
</if>
<if test="landno!=null and landno!=''">
AND [地号] LIKE '%'+#{landno}+'%'
</if>
<if test="landowner!=null and landowner!=''">
AND [土地使用者] LIKE '%'+#{landowner}+'%'
</if>
</where>
</sql>
<select id="getLandCertNoListByCertNoNameLandNo" resultMap="LandCertNomapper"
parameterType="com.marry.wucheng.collectionland.model.RequestItem">
SELECT * FROM 集体土地证号
WHERE [ID]
BETWEEN
(SELECT max([ID]) from(
SELECT top ${miniSize} [ID] from 集体土地证号
<include refid="wheresql"></include>
ORDER BY [ID]
)
)
AND
(SELECT max([ID]) FROM (
SELECT top ${maxSize} [ID] from 集体土地证号
<include refid="wheresql"></include>
ORDER BY [ID]
))
<if test="certno!=null and certno!=''">
AND [证号] LIKE '%'+#{certno}+'%'
</if>
<if test="landno!=null and landno!=''">
AND [地号] LIKE '%'+#{landno}+'%'
</if>
<if test="landowner!=null and landowner!=''">
AND [土地使用者] LIKE '%'+#{landowner}+'%'
</if>
ORDER BY [ID]
</select>
<select id="getcountLandCertNoListByCertNoNameLandNo" resultType="java.lang.Integer"
parameterType="com.marry.wucheng.collectionland.model.RequestItem">
SELECT count(*) FROM 集体土地证号
<where>
<if test="certno!=null and certno!=''">
[证号] LIKE '%'+#{certno}+'%'
</if>
<if test="landno!=null and landno!=''">
AND [地号] LIKE '%'+#{landno}+'%'
</if>
<if test="landowner!=null and landowner!=''">
AND [土地使用者] LIKE '%'+#{landowner}+'%'
</if>
</where>
</select>
其实这个分页写的有点投机取巧，因为有一个数字类型的自增，用了
between and方法很是方便，需要注意的是三处的条件、排序均需完全相同
前端页面设计
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title></title>
<link rel="stylesheet" href="layui/css/layui.css">
</head>
<style>
.layui-container {
margin-top: 80px;
}
</style>
<body>
<div class="layui-container">
<div class="landTable">
使用者：
<div class="layui-inline">
<input class="layui-input" name="landowner" id="landowner" autocomplete="off">
</div>
证号：
<div class="layui-inline">
<input class="layui-input" name="certno" id="certno" autocomplete="off">
</div>
<button class="layui-btn" data-type="reload">搜索</button>
</div>
<table class="layui-hide" id="certlist"></table>
</div>
<script src="layui/layui.js"></script>
<script>
layui.use('table', function () {
var table = layui.table;
table.render({
elem: '#certlist'
, url: 'getLandCertNo'
, cols: [[
{field: 'id', width: 80, title: 'ID', sort: true}
, {field: 'landno', width: 120, title: '地号'}
, {field: 'landowner', width: 100, title: '土地使用者'}
, {field: 'certno', width: 80, title: '证号'}
, {field: 'area', title: '面积', minWidth: 80}
, {field: 'location', width: 200, title: '坐落'}
, {field: 'overarea', width: 80, title: '超占面积'}
, {field: 'registerdate', width: 200, title: '发证日期'}
, {field: 'plause', width: 80, title: '用途'}
, {field: 'agent', width: 80, title: '经办人'}
, {field: 'otype', width: 80, title: '权属类型'}
, {field: 'memo', width: 200, title: '备注'}
, {field: 'marker', width: 100, title: '记号'}
, {field: 'verifier', width: 80, title: '审核人'}
]]
, page: true
, id: 'landload'
});
var $ = layui.$, active = {
reload: function () {
table.reload('landload', {
page: {
curr: 1
}
, where: {
landowner: $('#landowner').val().trim()
, certno: $('#certno').val().trim()
}
})
}
};
$('.landTable .layui-btn').on('click',function () {
var type=$(this).data('type');
active[type] ?active[type].call(this) :'';
})
})
</script>
</body>
</html>
主要方法
layui官网均有,这里的参数是封装成对象的
package com.marry.wucheng.collectionland.model;
import lombok.ToString;
/**
* @author ml
* @create 2018-01-15--13:50
*/
@ToString
public class RequestItem {
private String certno;
private String landno;
private String landowner;
private Integer page=1;
private Integer limit=10;
private Integer miniSize;
private Integer maxSize;
public String getCertno() {
return certno;
}
public void setCertno(String certno) {
this.certno = certno;
}
public String getLandno() {
return landno;
}
public void setLandno(String landno) {
this.landno = landno;
}
public String getLandowner() {
return landowner;
}
public void setLandowner(String landowner) {
this.landowner = landowner;
}
public Integer getPage() {
return page;
}
public void setPage(Integer page) {
this.page = page;
}
public Integer getLimit() {
return limit;
}
public void setLimit(Integer limit) {
this.limit = limit;
}
public Integer getMiniSize() {
return (page-1)*limit+1;
}
public void setMiniSize(Integer miniSize) {
this.miniSize = miniSize;
}
public Integer getMaxSize() {
return (page*limit);
}
public void setMaxSize(Integer maxSize) {
this.maxSize = maxSize;
}
}
也给出
controller层
@RequestMapping("/getLandCertNo")
public Map<String, Object> getLandCertNo(RequestItem item){
System.out.println("item = [" + item + "]");
Map<String,Object> map=new HashMap<String, Object>(4);
List<LandCertNo> landCertNoList=landCertNoMapper.getLandCertNoListByCertNoNameLandNo(item);
Integer count=landCertNoMapper.getcountLandCertNoListByCertNoNameLandNo(item);
Integer pages=(count/item.getLimit()+(count%item.getLimit()==0?0:1));
map.put("code",0);
map.put("msg","");
map.put("count",count);
map.put("data",landCertNoList);
return map;
}
