spring boot (三)对数据库的简单操作

操作数据库需要在pox.xml中增加配置  这里操作的是mysql数据库

<!--阿里数据源-->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid</artifactId>
            <version>1.0.19</version>
        </dependency>
        <!--JDBC-->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-jdbc</artifactId>
        </dependency>

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

添加完等待系统自动导包 

在application.properties配置数据库连接

spring.thymeleaf.cache=false
spring.datasource.url = jdbc:mysql://localhost:3306/自己的数据库名字
spring.datasource.username = root
spring.datasource.password = 123456
spring.datasource.driver-class-name=com.mysql.jdbc.Driver

一  条件查询 

      dao层：

 /*条件查询*/
    List<NameBean>  queryName4Id(String id);

daoimpl:先加载jdbctemplate

@Autowired
private JdbcTemplate  jdbcTemplate;

 @Override
    public List<NameBean> queryName4Id(String id) {
      return  jdbcTemplate.query("select *   from   name  where id = ?",new Object[]{id}, new BeanPropertyRowMapper(NameBean.class));
    }

service  不写了

controller：

/*AJAX前后传参,根据id查找*/
    @RequestMapping(value = "/ajax",method = RequestMethod.POST,produces="application/json;charset=UTF-8")
    @ResponseBody
    public Map<String,Object>  ajax(HttpServletRequest request, HttpServletResponse response) throws JSONException {
        Map<String,Object> map =new HashMap<String,Object>();
        System.out.println("Ajax进来");
        String name= request.getParameter("name");
        String kecheng= request.getParameter("kecheng");
        String fenshu= request.getParameter("fenshu");
        String id= request.getParameter("id");
        System.out.println(name+kecheng+fenshu+id);
        List<NameBean> nameBeans = queryName.queryName4Id(id);
        map.put("success",nameBeans);
        return map;
    }

html:

function  tijiao(){
           $("#div").html("");
            var id=document.getElementById("id").value;
            $.ajax({
               type:"post",
               url:"/users/ajax",
               data:{
                   id: id
               },
               dateType:"json",
                success:function (data) {
                    var obj;
                    obj=document.getElementById("div");
                    obj.style.border="1px   solid  black";
                var arr=data.success;
                    for(var i=0;i<data.success.length;i++){
                        var name=arr[i].name;
                        var id=arr[i].id;
                        var fenshu=arr[i].fenshu;
                        var kecheng=arr[i].kecheng;
                        $('#div').prepend("<tr> <td style='border : 1px  solid black'>姓名</td> <td style='border : 1px  solid black'>课程</td> <td style='border : 1px  solid black'>分数</td> <td style='border : 1px  solid black'>编号</td> </tr>" +
                            "<tr><td  style='border : 1px  solid black'>"+name+"</td><td style='border : 1px  solid black'>"+kecheng+"</td><td style='border : 1px  solid black'>"+fenshu+"</td><td style='border : 1px  solid black'>"+id+"</td></tr>");
                    }
               }
           })
        }

   完成一个查询

         动态SQL查询

        其他的基本一样  sql使用拼接

@Override
    public List<Map<String, Object>> select(NameBean nameBean) {
        StringBuffer sql =new StringBuffer();
        sql.append("select *  from name where 1=1  " );
        if(nameBean.getName()!=null&&""!=nameBean.getName()){
            sql.append("and name =").append("'").append(nameBean.getName()).append("'");
        }
        if (nameBean.getFenshu()!=null&&""!=nameBean.getFenshu()){
            sql.append("and fenshu =").append("'").append(nameBean.getFenshu()).append("'");
        }
        return  jdbcTemplate.queryForList(sql.toString());
    }

还有条件可以继续接  if  

 

二  添加 update

       dao:

/*增加*/
    int  addName(NameBean nameBean);

       daoimpl:

@Override
    public int addName(NameBean nameBean) {
      return  jdbcTemplate.update
              ("insert into name (name,kecheng,fenshu) values (?,?,?)",nameBean.getName(),nameBean.getKecheng(),nameBean.getFenshu());
        
    }

controller

 @RequestMapping(value = "/addName",method = RequestMethod.POST,produces="application/json;charset=UTF-8")
   @ResponseBody
    public  Map<String,Object> addName(HttpServletRequest request, HttpServletResponse response){
        Map<String,Object> map =new HashMap<String,Object>();
        NameBean nameBean=new NameBean();
        String name = request.getParameter("name");
        String kecheng = request.getParameter("kecheng");
        String fenshu = request.getParameter("fenshu");
        nameBean.setName(name);
        nameBean.setKecheng(kecheng);
        nameBean.setFenshu(fenshu);
        System.out.println(nameBean);
        int i = queryName.addName(nameBean);
        if(i==1){
            map.put("result","success");
        }else{
            map.put("result","false");
        }
        return map;
    }

html:

<!DOCTYPE html>
<html xmlns:th="http://www.thymeleaf.org">
<head>
    <meta charset="UTF-8">
    <title>Title</title>
    <script src="../static/jquery-1.10.2.min.js" th:src="@{/jquery-1.10.2.min.js}"></script>
    <script type="application/javascript">
        function  tijiao(){
            var name=document.getElementById("name").value;
            var kecheng=document.getElementById("kecheng").value;
            var fenshu=document.getElementById("fenshu").value;
            alert(name+kecheng+fenshu);
            $.ajax({
                type:"post",
                url:"/users/addName",
                data:{
                    name:name,
                    kecheng:kecheng,
                    fenshu:fenshu
                },
                dateType:"json",
                success:function (data) {
                    if(data.result){
                        alert("插入成功")
                    }
                }
            })
        }
    </script>
</head>
<body>
<form>
    姓名 <input type="text" id="name" value=""/><br>
    课程 <input type="text" id="kecheng" value=""/><br>
    分数 <input type="text" id="fenshu" value=""/><br>
    <input type="button" value="提交" onclick="tijiao()">

</form>
</body>
</html>

删除   和  更新 都是用jdbcTemplate.update   略略略。。。

有点累  源码贴上  https://pan.baidu.com/s/1TiXizkICM890fWVj4eEKAw    密码 bvno