使用jdbctemplate来对数据库进行增删改查-爱代码爱编程
废话不说,直接上代码:
controller层:
package com.example.demo.controller; //import com.example.demo.pojo.CreateUser; import com.example.demo.pojo.User; //import com.example.demo.service.UserService; import com.example.demo.properties.ProxyProperties; import com.example.demo.util.Operator; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.http.HttpRequest; import org.springframework.jdbc.core.JdbcTemplate; import org.springframework.web.bind.annotation.GetMapping; import org.springframework.web.bind.annotation.PostMapping; import org.springframework.web.bind.annotation.RequestBody; import org.springframework.web.bind.annotation.RestController; import javax.print.attribute.standard.MediaSize; import javax.servlet.http.HttpServlet; import javax.servlet.http.HttpServletRequest; import java.util.ArrayList; import java.util.Arrays; import java.util.HashMap; import java.util.List; import java.util.Map; /** * @author ShiGuoLi */ @RestController public class UsrController { // @Autowired // UserService userService; @Autowired JdbcTemplate jdbcTemplate; @PostMapping ( "/CreateUser" ) public String CreateUser( @RequestBody User user ) { Operator operator=Operator.CREATE; String isOrNot = (String ) userOperator( user,operator,null ); return isOrNot; } @GetMapping ( "/getUser" ) public List< Map< String, Object > > getUser( HttpServletRequest request ) { // List< Map< String, Object > > list = userService.selectUserById( id ); Operator operator=Operator.SEARCH; List< Map< String, Object > > getUser = (List< Map< String, Object > >)userOperator( null, operator, request ); return getUser; } @PostMapping("/deleteUser") public String deleteUser(HttpServletRequest request ) { Operator operator=Operator.DELETE; String user = (String ) userOperator( null, operator, request ); return user; } @PostMapping("/insertUser") public String insertUser(@RequestBody User user){ Operator operator= Operator.INSERT; String insertOkOrNot = (String ) userOperator( user, operator, null ); return insertOkOrNot; } public Object userOperator( User user, Operator operator, HttpServletRequest request ) { Integer id=null; Integer age=null; String name=""; // String[] search=new String[50]; // int i=0; // Integer id; // Integer age=null; // String name=""; // if(user!=null){ // age = user.getAge(); // id = user.getId(); // name = user.getName(); // }else{ // id =Integer.parseInt( request.getParameter( "id" ) ); // } String sql = "select * from user where ids=?"; // switch ( operator ) { // case CREATE: // case INSERT: if ( id < 0 ) { System.out.println( "id为负数,无法插入" ); return "id为负数,无法插入"; } else if ( name.length() == 0 ) { System.out.println( "name没有长度" ); return "name没有长度"; } else if ( age < 0 ) { System.out.println( "年龄小于0,不正确" ); return "年龄小于0,不正确"; } else { String sqlForInsert = "insert into user value(?,?,?)"; jdbcTemplate.update( sqlForInsert, id, name, age ); return "成功"; } // age = user.getAge(); // id = user.getId(); // name = user.getName(); // break; // case DELETE: String sqlForDelete = "delete from user where id =?"; jdbcTemplate.update( sqlForDelete, id ); return "delete_ok"; // case SEARCH: // id =Integer.parseInt( request.getParameter( "id" ) ); // break; String sqlForSearch = "select * from user where id=?"; String userForSearch = jdbcTemplate.queryForList( sqlForSearch, id ).toArray().toString(); return userForSearch; // } if(operator==Operator.CREATE||operator==Operator.INSERT){ age = user.getAge(); id = user.getId(); name = user.getName(); if(id<0){ return "id<0"; }else if(age<0){ return "age<0"; }else if(name.length()==0){ return "name为空"; }else{ List< Map< String, Object > > selectId = jdbcTemplate.queryForList( sql, id ); if (selectId.size()==0){ String sqlForCreate="insert into user value(?,?,?)"; jdbcTemplate.update( sqlForCreate,id,name,age ); return "成功创建"; }else{ return "该用户已存在"; } } }else{ if(operator==Operator.DELETE) { id =Integer.parseInt( request.getParameter( "id" ) ); List< Map< String, Object > > selectId = jdbcTemplate.queryForList( sql, id ); if(selectId.size()!=0){ String sqlForDelete="delete from user where ids=?"; jdbcTemplate.update( sqlForDelete,id ); return "删除成功"; }else{ return "该用户不存在"; } }else{ id =Integer.parseInt( request.getParameter( "id" ) ); List< Map< String, Object > > selectId = jdbcTemplate.queryForList( sql, id ); if(selectId.size()!=0){ String sqlForSearch="select * from user where ids=?"; List< Map< String, Object > > list = jdbcTemplate.queryForList( sqlForSearch, id ); return list; }else{ List< Map< String, Object > > list1=new ArrayList(); Map map=new HashMap(); map.put( "错误", "用户不存在"); list1.add( map ); return list1; } } } } } 实体层:
@Entity @Data public class User { @Id private Integer id; private String name; private Integer age; } 工具类层:
public enum Operator { CREATE, SEARCH, DELETE, INSERT; }
结果展示:
再点击一次:
没有service层的,需要service层形式的可以看我之前那个。
(27条消息) 0基础学springboot之对数据库的增删改查_桃林春风一杯酒的博客-CSDN博客
使用mybatis的会将在下一篇博客里写。
代码讲解:
cntroller层:@RestController注解: 表明这是springboot的一个controller组件,比@Controller注解强在多封装了注解,个人感觉比较重要的是因为封装了@RequestBody注解(接受前端传来的json数据)。
@PostMapping ( "/CreateUser" ) public String CreateUser( @RequestBody User user ) { Operator operator=Operator.CREATE; String isOrNot = (String ) userOperator( user,operator,null ); return isOrNot; } @GetMapping ( "/getUser" ) public List< Map< String, Object > > getUser( HttpServletRequest request ) { // List< Map< String, Object > > list = userService.selectUserById( id ); Operator operator=Operator.SEARCH; List< Map< String, Object > > getUser = (List< Map< String, Object > >)userOperator( null, operator, request ); return getUser; } @PostMapping("/deleteUser") public String deleteUser(HttpServletRequest request ) { Operator operator=Operator.DELETE; String user = (String ) userOperator( null, operator, request ); return user; } @PostMapping("/insertUser") public String insertUser(@RequestBody User user){ Operator operator= Operator.INSERT; String insertOkOrNot = (String ) userOperator( user, operator, null ); return insertOkOrNot; }
@GetMapping和@PostMapping都是发送请求的注解,方式不一样,在postman等工具里也要使用不一样的方法,括号中是URL,一般情况下查询使用@GetMapping,操作使用其他的。
@RequestBody 在参数前时:发送请求的格式要是json格式,拿insertUser这个Url来说:
注意点有以上四个:
因为使用的是@PostMapping,所以要使用这个方法。
因为参数是@RequestBody,所以要点击Body里的raw,选择json格式,再输入相应的参数,因为我在这里的参数是User 类,所以要把User类的属性都给写上。加入User的类里面属性太多,有没必要全写,就可以把@RequestBody注解删掉,然后点击Body里的form-data,就可以自由的输入你想输入的属性。
如果定义的方法的参数为HttpServletRequest,是另外一种写法:
拿getUser的URL举例,这个要点及Params,在里面输入你想输入的数据。
使用request.getParameter("id')就可以获取你在这个params里的id的值。
在这写方法里每一个方法调用了userOperator这个方法,这个方法是我自己写的,在最下面,里面集成了四种操作的方法。(由于我在创建表时没有进行设置主键的操作,所以需要进行部分数据校检)
大概逻辑就是当你进行增删改查操作时,先进行判断这个数据是否存在。如果存在,就可以进行删除和 查询操作,对于创建和插入就会返回该用户已存在的信息;如果不存在就可以进行创建和插入的操作,对于另外两种就是用户不存在的消息。
基本上都是使用的jdbcTempalte的几个方法。
实体层:
@Entity是将你实体层的字段名和postman里的字段名对应起来。
工具类层:
创建一个enum。(单例模式,设计模式的一种。)
小优化
使用switchcase将四种操作所需的属性获取
switch ( operator ) { // case CREATE: // case INSERT: // age = user.getAge(); // id = user.getId(); // name = user.getName(); // break; // case DELETE: // case SEARCH: // id =Integer.parseInt( request.getParameter( "id" ) ); // break; // }
但是如果你不是根据id来进行操作,那么可以使用配置文件的方法:
拿删除和查询举例:
在id值获取的时候可以将
id =Integer.parseInt( request.getParameter( "id" ) );
改为:
id =Integer.parseInt( request.getParameter( proxyProperties.kubesphere.getDefaultId() ) );
同时设置配置文件:
在
application.pro.....文件中加入这句话:
spring.profiles.active=test
然后创建test的properties,并输入
spring.application.name=demo3 # ???? WEB ???? server.port=30999 # ?????? spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver # ????? spring.datasource.name=defaultDataSource # ??????? spring.datasource.url=jdbc:mysql://xxxxxxxxxx:3306/xxxx?useUnicode=true&characterEncoding=utf8&serverTimezone=GMT%2B8 # ??????&??? spring.datasource.username=root spring.datasource.password=123456asd id=44; name=xz; c_id=222; #mybatis-plus.mapper-locations=classpath:mapper/*.xml xxxxxxxxx.xxxxxx.xxxxx.default-id=id
jdbc的url要写成自己的,账号、端口号和密码也是
最下面那一行的x随便输入
在创建几个配置文件:
@Data public class KubeSphereProxy { private String defaultId; }
首先创建一个实体类,来映射你配置文件中的数据,在这里我就一个属性所以就写了一个属性。
@Configuration @EnableConfigurationProperties(ProxyProperties.class) public class ProxyConfig { }
然后在创建一个类,使用@Configuration将该类作为组件,可以被ConfigurationBean扫描到,
@EnableConfigurationProperties 注解的作用是:让使用了 @ConfigurationProperties 注解的类生效,并且将该类注入到 IOC 容器中,交由 IOC 容器进行管理,在springboot中IoC可以理解为@Autowired注解,就是说这个类可以被@Autowired引入,在这里是可以让下面这个类生效
@Data @ConfigurationProperties(prefix = "xxxxx") public class ProxyProperties { public KubeSphereProxy kubesphere= new KubeSphereProxy(); }
@Data这个注解可以省略掉getset等方法。
@ConfigurationProperties需要和@Configuration配合使用,如果不和Configuration使用,那么就要在另外一个类使用@EnableConfigurationProperties(ProxyProperties.class),让该类生效。
@ConfigurationProperties(prefix = "xxxxx")这句话是可以自动匹配以""中间开头的属性的值,可以改为自己的。
同时在这里创建一个映射的实体层的对象,同时在controller层将其引入。
@Autowired ProxyProperties proxyProperties;
因此:
id =Integer.parseInt( request.getParameter( proxyProperties.kubesphere.getDefaultId() ) );
这句话就是获取request中名为proxyProperties.kubesphere.getDefaultId()的值的属性,这里的值是xxxxxxxxx.xxxxxx.xxxxx.default-id=id,因此就是id。
这里你就可以修改配置文件中的数据,可以不用动代码:
整体修改过后的controller层:
package com.example.demo.controller; //import com.example.demo.pojo.CreateUser; import com.example.demo.pojo.User; //import com.example.demo.service.UserService; import com.example.demo.properties.ProxyProperties; import com.example.demo.util.Operator; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.http.HttpRequest; import org.springframework.jdbc.core.JdbcTemplate; import org.springframework.web.bind.annotation.GetMapping; import org.springframework.web.bind.annotation.PostMapping; import org.springframework.web.bind.annotation.RequestBody; import org.springframework.web.bind.annotation.RestController; import javax.print.attribute.standard.MediaSize; import javax.servlet.http.HttpServlet; import javax.servlet.http.HttpServletRequest; import java.util.ArrayList; import java.util.Arrays; import java.util.HashMap; import java.util.List; import java.util.Map; /** * @author ShiGuoLi */ @RestController public class UsrController { // @Autowired // UserService userService; @Autowired JdbcTemplate jdbcTemplate; @Autowired ProxyProperties proxyProperties; @PostMapping ( "/CreateUser" ) public String CreateUser( @RequestBody User user ) { Operator operator=Operator.CREATE; String isOrNot = (String ) userOperator( user,operator,null ); return isOrNot; } @GetMapping ( "/getUser" ) public List< Map< String, Object > > getUser( HttpServletRequest request ) { // List< Map< String, Object > > list = userService.selectUserById( id ); Operator operator=Operator.SEARCH; List< Map< String, Object > > getUser = (List< Map< String, Object > >)userOperator( null, operator, request ); return getUser; } @PostMapping("/deleteUser") public String deleteUser(HttpServletRequest request ) { Operator operator=Operator.DELETE; String user = (String ) userOperator( null, operator, request ); return user; } @PostMapping("/insertUser") public String insertUser(@RequestBody User user){ Operator operator= Operator.INSERT; String insertOkOrNot = (String ) userOperator( user, operator, null ); return insertOkOrNot; } public Object userOperator( User user, Operator operator, HttpServletRequest request ) { Integer id=null; Integer age=null; String name=""; // String[] search=new String[50]; // int i=0; // Integer id; // Integer age=null; // String name=""; // if(user!=null){ // age = user.getAge(); // id = user.getId(); // name = user.getName(); // }else{ // id =Integer.parseInt( request.getParameter( "id" ) ); // } String sql = "select * from user where ids=?"; switch ( operator ) { case CREATE: case INSERT: // if ( id < 0 ) { // System.out.println( "id为负数,无法插入" ); // return "id为负数,无法插入"; // } else if ( name.length() == 0 ) { // System.out.println( "name没有长度" ); // return "name没有长度"; // } else if ( age < 0 ) { // System.out.println( "年龄小于0,不正确" ); // return "年龄小于0,不正确"; // } else { // String sqlForInsert = "insert into user value(?,?,?)"; // jdbcTemplate.update( sqlForInsert, id, name, age ); // return "成功"; // // } age = user.getAge(); id = user.getId(); name = user.getName(); break; case DELETE: // String sqlForDelete = "delete from user where id =?"; // jdbcTemplate.update( sqlForDelete, id ); // return "delete_ok"; case SEARCH: // id =Integer.parseInt( request.getParameter( "id" ) ); id =Integer.parseInt( request.getParameter( proxyProperties.kubesphere.getDefaultId() ) ); break; // String sqlForSearch = "select * from user where id=?"; // String userForSearch = jdbcTemplate.queryForList( sqlForSearch, id ).toArray().toString(); // return userForSearch; } if(operator==Operator.CREATE||operator==Operator.INSERT){ // age = user.getAge(); // id = user.getId(); // name = user.getName(); if(id<0){ return "id<0"; }else if(age<0){ return "age<0"; }else if(name.length()==0){ return "name为空"; }else{ List< Map< String, Object > > selectId = jdbcTemplate.queryForList( sql, id ); if (selectId.size()==0){ String sqlForCreate="insert into user value(?,?,?)"; jdbcTemplate.update( sqlForCreate,id,name,age ); return "成功创建"; }else{ return "该用户已存在"; } } }else{ if(operator==Operator.DELETE) { // id =Integer.parseInt( request.getParameter( proxyProperties.kubesphere.getDefaultId() ) ); List< Map< String, Object > > selectId = jdbcTemplate.queryForList( sql, id ); if(selectId.size()!=0){ String sqlForDelete="delete from user where ids=?"; jdbcTemplate.update( sqlForDelete,id ); return "删除成功"; }else{ return "该用户不存在"; } }else{ // id =Integer.parseInt( request.getParameter( "id" ) ); List< Map< String, Object > > selectId = jdbcTemplate.queryForList( sql, id ); if(selectId.size()!=0){ String sqlForSearch="select * from user where ids=?"; List< Map< String, Object > > list = jdbcTemplate.queryForList( sqlForSearch, id ); return list; }else{ List< Map< String, Object > > list1=new ArrayList(); Map map=new HashMap(); map.put( "错误", "用户不存在"); list1.add( map ); return list1; } } } } }