Spring+Jdbctemplate+springmvc +layui实现基本业务

主要实现了：查询列表   分页查询  模糊查询 图片上传（注意：图片上传用的是aliyun镜像oss实现） 新增 修改 删除 操作

1：导入pom依赖

    <dependencies>

        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.11</version>
            <scope>test</scope>
        </dependency>

        <dependency>
            <groupId>javax.servlet</groupId>
            <artifactId>javax.servlet-api</artifactId>
            <version>3.1.0</version>
        </dependency>

        <dependency>
            <groupId>javax.servlet</groupId>
            <artifactId>jstl</artifactId>
            <version>1.2</version>
        </dependency>

        <dependency>
            <groupId>commons-logging</groupId>
            <artifactId>commons-logging</artifactId>
            <version>1.2</version>
        </dependency>



        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.38</version>
        </dependency>

        <!--spring核心依赖-->

        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-core</artifactId>
            <version>5.1.5.RELEASE</version>
        </dependency>

        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-beans</artifactId>
            <version>5.1.5.RELEASE</version>
        </dependency>

        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-context</artifactId>
            <version>5.1.5.RELEASE</version>

        </dependency>

        <dependency>

            <groupId>org.springframework</groupId>

            <artifactId>spring-aop</artifactId>

            <version>5.1.5.RELEASE</version>
        </dependency>

        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-jdbc</artifactId>
            <version>5.1.5.RELEASE</version>

        </dependency>

        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-web</artifactId>
            <version>5.1.5.RELEASE</version>
        </dependency>

        <dependency>

            <groupId>org.springframework</groupId>
            <artifactId>spring-webmvc</artifactId>
            <version>5.1.5.RELEASE</version>
        </dependency>

        <dependency>

            <groupId>org.springframework</groupId>
            <artifactId>spring-expression</artifactId>
            <version>5.1.5.RELEASE</version>
        </dependency>

        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-tx</artifactId>
            <version>5.1.5.RELEASE</version>
        </dependency>


        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <version>1.18.8</version>
        </dependency>


        <!--转json-->
        <dependency>
            <groupId>com.fasterxml.jackson.core</groupId>
            <artifactId>jackson-databind</artifactId>
            <version>2.11.0</version>
        </dependency>
        <dependency>
            <groupId>com.fasterxml.jackson.core</groupId>
            <artifactId>jackson-annotations</artifactId>
            <version>2.11.0</version>
        </dependency>
        <dependency>
            <groupId>commons-beanutils</groupId>
            <artifactId>commons-beanutils</artifactId>
            <version>1.9.3</version>
        </dependency>

        <!--阿里云镜像-->
        <dependency>
            <groupId>com.aliyun.oss</groupId>
            <artifactId>aliyun-sdk-oss</artifactId>
            <version>3.4.2</version>
        </dependency>

        <!--文件上传jar-->
        <dependency>
            <groupId>commons-fileupload</groupId>
            <artifactId>commons-fileupload</artifactId>
            <version>1.3.1</version>
        </dependency>
        <dependency>
            <groupId>commons-io</groupId>
            <artifactId>commons-io</artifactId>
            <version>2.4</version>
        </dependency>


    </dependencies>

    
</project>

2：xml配置

<!--配置注解要扫描的包-->
    <context:component-scan base-package="com.aaa"></context:component-scan>
    <mvc:annotation-driven></mvc:annotation-driven>
    <!--配置spring-jdbcTemplate-->
    <!--配置数据源-->
    <bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
        <!--MySQL数据库驱动-->
        <property name="driverClassName" value="com.mysql.jdbc.Driver"></property>
        <!--连接数据库的URL-->
        <property name="url" value="jdbc:mysql://localhost:3306/a151?characterEncoding=UTF-8"></property>
        <!--连接数据库的用户名-->
        <property name="username" value="root"></property>
        <!--连接数据库的密码-->
        <property name="password" value="root"></property>
    </bean>

    <!--配置JDBC模板-->
    <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
        <property name="dataSource" ref="dataSource"></property>
    </bean>

    <!--配置事务-->
    <bean id="txManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
        <property name="dataSource" ref="dataSource"></property>
    </bean>

    <!--注册事务注解驱动-->


    <!--配置视图解析器-->
    <bean class="org.springframework.web.servlet.view.InternalResourceViewResolver">
        <!--配置前缀-->
        <property name="prefix" value="/WEB-INF/jsp/"/>
        <!--配置后缀-->
        <property name="suffix" value=".jsp"></property>
    </bean>

    <!--默认 处理-->
    <mvc:default-servlet-handler/>
    <!--直接  放包-->
    <mvc:resources mapping="/static/**" location="/static/"/>
    <!--
       mapping:表示请求中 url 中带有static 以及后面的所有路径全被处理
       location：表示 具体的静态资源线路路径
    -->

    <!--配置文件解析器-->
    <bean id="multipartResolver" class="org.springframework.web.multipart.commons.CommonsMultipartResolver">
     <!--设置文件上传大小-->
      <property name="maxUploadSize" value="10485760"></property>
        <!--设置单个文件的大小-->
        <property name="maxUploadSizePerFile" value="5248000"></property>
        <!--设置上传文件的编码-->
        <property name="defaultEncoding" value="UTF-8"></property>
    </bean>

3：查询操作

    3.1： dao层接口

 List<Map<String,Object>> quertTeacher(Teacher teacher);


    /**
     * create by: aaron
     * description: TODO
     * create time: 2022/7/21 9:50
     * @params   获取所有行
     * @return
     */

    int getTeacherCount();

    /**
     * create by: aaron
     * description: TODO
     * create time: 2022/7/23 9:59
     *   模糊查询
     *@return a
     * @params
     */

    List<Map<String,Object>> quertTeacherLike(String tname);

    /**
     * create by: aaron
     * description: TODO
     * create time: 2022/7/23 11:21
     * @params   新增老师
     * @return
     */

    int AddTeacher(Teacher teacher);

    int updataTea(Teacher teacher);

3.2：dao层实现类

@Repository
public class TeacherDaoImp implements TeacherDao {
    @Resource
    JdbcTemplate jdbcTemplate;
    // 查询 分页查询
    public List<Map<String, Object>> quertTeacher(Teacher teacher) {
        String sql="SELECT * from  teacher a\n" +
                "JOIN dept b  on a.did = b.did     ORDER BY a.tea_id DESC  LIMIT ?,?";
        List<Map<String, Object>> list = jdbcTemplate.queryForList(sql,(teacher.getPage()-1)*teacher.getLimit(),teacher.getLimit());
        return list;
    }

    public int getTeacherCount() {
        String sql="SELECT COUNT(*) c  from teacher ";
        List<Map<String, Object>> list = jdbcTemplate.queryForList(sql);
        //  声明一个总条数
        int allRows=0;
        Map<String, Object> map = list.get(0);
        // 三元表达式
        allRows = map.get("c") != null ? Integer.parseInt(map.get("c").toString()) : 0;
        //  mysql   null?

        return allRows;
    }

    /*模糊查询*/
    public List<Map<String,Object>> quertTeacherLike(String tname) {
        String sql="SELECT * from teacher where  tea_name like '%"+tname+"%'";
        List<Map<String, Object>> list = jdbcTemplate.queryForList(sql);
        return  list;
    }

    public int AddTeacher(Teacher teacher) {
        int update = jdbcTemplate.update("insert  into   teacher (tea_name,tea_age,did,url) values (?,?,?,?)", teacher.getTea_name(), teacher.getTea_age(),teacher.getDid(),teacher.getUrl());
        return update;
    }

    public int updataTea(Teacher teacher) {
        int update = jdbcTemplate.update("update  teacher set  tea_name=? , tea_age=?  , did=?  where  tea_id=?", teacher.getTea_name(), teacher.getTea_age(), teacher.getDid(), teacher.getTea_id());
        return update;
    }

4：业务层

@Service
public class TeacherServiceImp implements TeacherServices {
    @Resource
    TeacherDaoImp teacherDaoImp;
    public LayuiUtil quertTeacher(Teacher teacher) {
        List<Map<String, Object>> list = teacherDaoImp.quertTeacher(teacher);
        int teacherCount = teacherDaoImp.getTeacherCount();
        LayuiUtil layuiUtil = new LayuiUtil(teacherCount,list);
        return layuiUtil;
    }

    public LayuiUtil quertTeaLike(String tname) {
        List<Map<String, Object>> list = teacherDaoImp.quertTeacherLike(tname);
        int teacherCount = teacherDaoImp.getTeacherCount();
        LayuiUtil layuiUtil = new LayuiUtil(teacherCount, list);
        return  layuiUtil;
    }

    public int AddTeacher(Teacher teacher) {
        int i = teacherDaoImp.AddTeacher(teacher);

        return i;
    }

    public int UpdateTeacher(Teacher teacher) {
        int i = teacherDaoImp.updataTea(teacher);
        return i;
    }

    public int delect(Teacher teacher) {

        return 0;
    }

    public int UpdateTeacher(Integer tid) {

        return 0;
    }

    public int delect(Integer tea_id) {
        return 0;
    }
}

5：控制层

@Controller
@RequestMapping("/teacher")
public class TeacherController  {
@Resource
    TeacherServiceImp teacherServiceImp;
@RequestMapping("/list")
@ResponseBody
    public LayuiUtil TeacherShow(HttpServletRequest request, Teacher teacher){
    String teaname = request.getParameter("teaname");
//  如果  taname  ！= null  此时  查询全部   否则：执行模糊查询
    LayuiUtil layuiUtil=null;
    if(teaname==null){
         layuiUtil = teacherServiceImp.quertTeacher(teacher);

    }else{
        layuiUtil = teacherServiceImp.quertTeaLike(teaname);
    }
        System.out.println(layuiUtil);
        return layuiUtil;
    }

    /**
     * create by: aaron
     * description: TODO
     * create time: 2022/7/23 11:20
     *   新增
     * return a
     * @params
     */

    @RequestMapping("/add")
    @ResponseBody
    public  Integer addTea(Teacher teacher){
        //  teaId  注解：处理_    tea_id  teaId
        Integer tea_id = teacher.getTea_id();
        int i=0;
        if(tea_id==null){
             i = teacherServiceImp.AddTeacher(teacher);
        }else{
           i = teacherServiceImp.UpdateTeacher(teacher);
        }
        if(i>0){
            System.out.println("成功");
        }else{
            System.out.println("失败");
        }
        return i;
    }

    /**
     * 图片上传，返回前台得到的url路径
     * @param file
     * @return
     */
    @RequestMapping("/upload")
    @ResponseBody
    public Map upload(MultipartFile file){
        String url = OSSUploadUtil.upload2(file);
        Map<Object, Object> map = new HashMap();
        map.put("url",url);
        return map;
    }


    @RequestMapping("/tea")
    public  String Tea(){
    return "TeacherList";
    }

6：jsp页面

<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<html>
<head>
    <title>Title</title>
    <link rel="stylesheet" href="../../static/layui/css/layui.css">
</head>
<body>
<%--新增和修改的弹出框--%>
<div id="addTea" style="display: none">
    <form class="layui-form"  lay-filter="addForm" id="addForm" >
        <input name="tea_id"  id="tea_id" style="display: none">


        <div class="layui-form-item">
            <label class="layui-form-label">姓名</label>
            <div class="layui-input-block">
                <input type="text" name="tea_name"  id="teana" required  lay-verify="required" placeholder="请输入姓名" autocomplete="off" class="layui-input">
            </div>
        </div>
        <div class="layui-form-item">
            <label class="layui-form-label">年龄</label>
            <div class="layui-input-inline">
                <input type="text" name="tea_age"  id="teaage" required lay-verify="required" placeholder="请输入年龄" autocomplete="off" class="layui-input">
            </div>
            <div class="layui-form-mid layui-word-aux">辅助文字</div>
        </div>

        <div class="layui-form-item">
            <label class="layui-form-label">部门</label>
            <div class="layui-input-inline">
                <select  id="prov"   name="did" lay-verify="" lay-search="">
                </select>
            </div>
        </div>

        <%--文件上传--%>
        <div class="layui-upload-drag" id="test10">
            <i class="layui-icon"></i>
            <p>点击上传，或将文件拖拽到此处</p>
            <div class="layui-hide" id="uploadDemoView">
                <hr>
                <img id="img1" src="" alt="上传成功后渲染" style="width:100px;height:100px">
                <%--用来存储图片上传成功后返回的url--%>
                <input type="hidden" id="imageUrl" name="url"/>
            </div>
        </div>

        <div>
            <%----%>
            <h1>图片展示</h1>
            <img id="img" src=""  style="width:100px;height:100px">
        </div>


        <div class="layui-form-item">
            <div class="layui-input-block">
                <button  type="button"  class="layui-btn" lay-submit lay-filter="addTea">提交</button>
                <button type="reset"   id="reset" class="layui-btn layui-btn-primary">重置</button>
            </div>
        </div>
    </form>
</div>




<%--模糊查询搜索框--%>
<div class="demoTable">
    搜索名称：
    <div class="layui-inline">
        <input class="layui-input" name="teaname" id="teaname" autocomplete="off" placeholder="请输入图书名称">
    </div>
    <button class="layui-btn" data-type="reload" id="likebtn">搜索</button>
</div>



<table class="layui-hide" id="demo"  lay-filter="test"></table>
<script src="../../static/layui/layui.js" charset="utf-8"></script>
<!-- 注意：如果你直接复制所有代码到本地，上述 JS 路径需要改成你本地的 -->

<script>
    <%--  加载  用用  layui  属性--%>
    layui.use(['table', 'layer','form','upload'], function(){
        var table = layui.table;
        var layer = layui.layer;
        var form=layui.form;
        var upload=layui.upload;

        //  渲染
        table.render({
             elem: '#demo'
            ,url: '/teacher/list'
            ,cellMinWidth: 80 //全局定义常规单元格的最小宽度，layui 2.2.1 新增
            ,toolbar: 'default' //开启工具栏，此处显示默认图标，可以自定义模板，详见文档
            // ,totalRow: true //开启合计行
            ,cols: [[
                  {type: 'checkbox', fixed: 'left'}
                ,{field:'tea_id', width:80, title: 'ID', sort: true}
                ,{field:'tea_name', width:80, title: '用户名'}
                ,{field:'tea_age', width:80, title: '年龄', sort: true}
                ,{field:'dname', width:80, title: '部门', sort: true}
                ,{field: 'url', title: '图片',  sort: true,templet:function (d){
                        return '<img src="'+d.url+'" width="100px" height="30px"/>';
                    }}
                ,{field:'did', width:80, title: '部门ID', sort: true ,hide:true}
            ]]
             ,id:'Teaable'
            ,skin: 'line' //表格风格
            ,even: true   //  开启 even时间
            ,page: true //是否显示分页
            ,limits: [5, 7, 10]
            ,limit: 5 //每页默认显示的数量
        });


        //  文件上传的渲染
        upload.render({
            elem: '#test10'
            ,url: '/teacher/upload' //改成您自己的上传接口
            ,//用于文件上传前的回调
            choose: function(obj){
                //  图片路径
                //console.log(obj)
                var files = this.files = obj.pushFile(); //将每次选择的文件追加到文件队列
                //读取本地文件
                obj.preview(function(index, file, result){
                    // attr：改变属性的
                    $("#img").attr("src",result);
                    //result得到图像的base64编码
                });
            }
            ,done: function(res){//上传成功后的回调函数
                //alert(res.url);
                $("#imageUrl").val(res.url);//将上传成功后得到的图片的url返回，并且赋值给表单中隐藏url框
                layer.msg('上传成功');
                layui.$('#uploadDemoView').removeClass('layui-hide').find('img').attr('src', res.str);
            }
        });

        var $ = layui.$, active = {
            reload: function(){
                var teaname= $('#teaname');

                if (teaname.val()==""){
                    layer.msg("查询的数据不能为空", {icon: 7, offset: "auto", time: 1000});
                }
                //执行重载
                table.reload('Teaable', {	//testReload为table中的id

                        page: {
                            curr: 1 //重新从第 1 页开始
                        }
                        ,where: {
                            teaname:teaname.val(),

                        }
                    }
                ),'data';
            }
        };


        //   模糊查询
        $('#likebtn').on('click', function(){	//search为搜索button中设置的id名
            var type = $(this).data('type');
            //  加载 事件  reload
            alert(type)
            active[type] ? active[type].call(this) : '';
        });


//监听头工具栏事件
        table.on('toolbar(test)', function(obj){
            var checkStatus = table.checkStatus(obj.config.id)
                ,data = checkStatus.data; //获取选中的数据
            console.log(data)
            switch(obj.event){
                case 'add':
                    layer.open({
                         type: 1,
                        title:['添加信息','font-size:20px'],
                        content: $('#addTea'), //这里content是一个DOM，注意：最好该元素要存放在body最外层，否则可能被其它的相对元素所影响
                        area: ['50%','90%'],
                    });
                    break;




                case 'update':
                    if(data.length === 0){
                        layer.msg('请选择一行');
                        break;
                    } else if(data.length > 1){
                        layer.msg('只能同时编辑一个');
                        break;
                    } else {
                        layer.open({
                            type: 1,
                            title: '修改',
                            area: ['50%', '70%'],
                            content: $("#addTea")
                        });
                        $("#tea_id").val(checkStatus.data[0].tea_id);//设置选中的值  TypeID为HTML标签ID
                        $("#teana").val(checkStatus.data[0].tea_name);//设置选中的值  TypeID为HTML标签ID
                        $("#teaage").val(checkStatus.data[0].tea_age);//设置选中的值  TypeID为HTML标签ID
                          // .each：遍历循环数据
                        $("#prov").each(function () {
                            $(this).children("option").each(function () {
                                if (this.value==checkStatus.data[0].did){
                                    //  .attr:更改下拉框状态的
                                    $(this).attr("selected","selected");
                                }
                            })
                        });
                        layui.form.render("select");//layUI设置
                        break;
                    }







                case 'delete':
                    if(data.length === 0){
                        layer.msg('请选择一行');
                    } else {
                        layer.msg('删除');
                        layer.alert('删除 [id]：'+ checkStatus.data[0].tea_id);
                        $.ajax({
                            url: "/tu/updaSeleStu",
                            type: "post",
                            dataType: "json",
                            data: {tea_id: checkStatus.data[0].tea_id},
                            success: function (data) {

                              }
                            });
                        }
                    break;
            };
        });


    //       下拉框赋值
        layui.use('form', function () {
            var $ = layui.jquery
            $.ajax({
                url: '/dept/list',
                dataType: 'json',
                type: 'post',
                success: function (data) {
                    if (data.data !== null) {
                        //  清空
                        $("#prov").empty();
                        //  1:10 9 8 7      2:98   97 96
                        $.each(data.data, function (index, item) {
                            $('#prov').append(new Option(item.dname, item.did));
                        });
                    } else {
                        $("#prov").append(new Option("暂无数据", ""));
                    }
                    //重新渲染
                    form.render("select");
                }
            });
        });



    //     提交数据表格   表单数据
        form.on('submit(addTea)', function(data){
             console.log(data.field);
             // 序列化表格
           var pp=  $("#addForm").serialize();
             $.ajax({
                 type:'post',
                 url:'/teacher/add',
                 dataType:'json',
                 data:data.field,
                 success:function (result) {
                     if(result!=null){
                         alert("成功")
                         // 重新加载数据表格
                         table.reload('Teaable',{});
                         layer.closeAll();
                         window.location.reload();
                     }else{
                         alert("失败")
                     }
                 }

             })
        });

    });
</script>

</body>
</html>

7：工具类

  7.1：layui 返回数据格式

public class LayuiUtil {
    // 为了 契合 layui  数据格式
    private Integer  code = 0;   // code =0
    private String msg = "";      //  信息
    private int count;          // 行
    private List<?> data;      // 查到数据

    public LayuiUtil(int count, List<?> data) {
        this.count = count;
        this.data = data;
    }

    public Integer getCode() {
        return code;
    }

    public void setCode(Integer code) {
        this.code = code;
    }

    public String getMsg() {
        return msg;
    }

    public void setMsg(String msg) {
        this.msg = msg;
    }

    public int getCount() {
        return count;
    }

    public void setCount(int count) {
        this.count = count;
    }

    public List<?> getData() {
        return data;
    }

    public void setData(List<?> data) {
        this.data = data;
    }

    @Override
    public String toString() {
        return "Result{" +
                "code=" + code +
                ", msg='" + msg + '\'' +
                ", count=" + count +
                ", data=" + data +
                '}';
    }

  7.2：文件上传工具类

   

public class OSSUploadUtil {

    public static String upload2(MultipartFile file){
        //上传的操作
        //将文件的名字编程唯一的再上传
        // F:123JPG
        int i = file.getOriginalFilename().lastIndexOf(".");//获取上传的文件的原名
        //  8946SDGFDGDFSDFB+123.jpg
        String suffix = file.getOriginalFilename().substring(i);
        System.out.println("fileanme文件原名："+suffix);
        //借助  UUID 将文件名编程唯一的 重新定义
         //
        String uuid = UUID.randomUUID().toString();
        String filename = uuid+suffix;

        // Endpoint以杭州为例，其它Region请按实际情况填写。
        String endpoint = "oss-cn-beijing.aliyuncs.com";
// 阿里云主账号AccessKey拥有所有API的访问权限，风险很高。强烈建议您创建并使用RAM账号进行API访问或日常运维，请登录RAM控制台创建RAM账号。
        String accessKeyId = "---------";
        String accessKeySecret = "-----------";
        String bucketName = "-----------";
// <yourObjectName>上传文件到OSS时需要指定包含文件后缀在内的完整路径，例如abc/efg/123.jpg。
        String objectName = filename;
// 创建OSSClient实例。
        OSS ossClient = new OSSClientBuilder().build(endpoint, accessKeyId, accessKeySecret);
// 上传文件到指定的存储空间（bucketName）并将其保存为指定的文件名称（objectName）。
        try {
            //file.getInputStream() 当前文件的输入流
            ossClient.putObject(bucketName, objectName, file.getInputStream());
        } catch (IOException e) {
            e.printStackTrace();
        }
// 关闭OSSClient。
        ossClient.shutdown();
        //https://tutu-image.oss-cn-beijing.aliyuncs.com/19c55f39-a82a-427c-9e81-25081271de06.jpg
        String url = "https://"+bucketName+"."+endpoint+"/"+filename;
        return url;
    }
}