SpringBoot调用mysql操作

野性酷女 2022-01-29 09:53 210阅读 0赞

### [IDEA配置SpringBoot项目][IDEA_SpringBoot] ###

### [SpringBoot实现增删改查][SpringBoot] ###

> 记录功能很简单，创建Controller，然后调用Service中的SQL语句进行增删改查

用到的jar包

//Gson
    implementation 'com.google.code.gson:gson:2.8.5'
    //json解析
    implementation 'org.json:json:20160810'

### 1. 创建实体类 ###

public class TestBean { 
        private long id;
        private String userName;
        private String password;
        private int age;
        private String phoneNumber;
        private String headPicture;
        private Date addDate;
        private Date updateDate;
        private int state;
    
        @Override
        public String toString() { 
            JSONObject jsonObject = new JSONObject();
            try { 
                jsonObject.put("id",id);
                jsonObject.put("userName",userName);
                jsonObject.put("password",password);
                jsonObject.put("age",age);
                jsonObject.put("phoneNumber",phoneNumber);
                jsonObject.put("headPicture",headPicture);
                jsonObject.put("addDate",addDate);
                jsonObject.put("updateDate",updateDate);
                jsonObject.put("state",state);
            } catch (JSONException e) { 
                e.printStackTrace();
            }
            return jsonObject.toString();
        }
    
        public long getId() { 
            return id;
        }
    
        public String getUserName() { 
            return userName;
        }
    
        public String getPassword() { 
            return password;
        }
    
        public int getAge() { 
            return age;
        }
    
        public String getPhoneNumber() { 
            return phoneNumber;
        }
    
        public String getHeadPicture() { 
            return headPicture;
        }
    
        public Date getAddDate() { 
            return addDate;
        }
    
        public Date getUpdateDate() { 
            return updateDate;
        }
    
        public int getState() { 
            return state;
        }
    
        public void setId(long id) { 
            this.id = id;
        }
    
        public void setUserName(String userName) { 
            this.userName = userName;
        }
    
        public void setPassword(String password) { 
            this.password = password;
        }
    
        public void setAge(int age) { 
            this.age = age;
        }
    
        public void setPhoneNumber(String phoneNumber) { 
            this.phoneNumber = phoneNumber;
        }
    
        public void setHeadPicture(String headPicture) { 
            this.headPicture = headPicture;
        }
    
        public void setAddDate(Date addDate) { 
            this.addDate = addDate;
        }
    
        public void setUpdateDate(Date updateDate) { 
            this.updateDate = updateDate;
        }
    
        public void setState(int state) { 
            this.state = state;
        }
    }

### 2. 创建Service，负责连接数据库的增删改查操作(这里都列出来了) ###

/** * 数据接口协议 * **/
    @Mapper
    public interface TestService { 
        //判断表是否存在
        @Select("SELECT table_name FROM information_schema.TABLES WHERE table_name ='AppComment'")
        String isHaveTable();
    
        //创建
        @Insert("CREATE TABLE IF NOT EXISTS `runoob_bbb`(" +
                "`runoob_id` INT UNSIGNED AUTO_INCREMENT," +//定义自增长
                "`runoob_title` VARCHAR(100) NOT NULL," +//不为空
                "PRIMARY KEY(`runoob_id`)" +//定义主键值对
                ")ENGINE=InnoDB DEFAULT CHARSET=utf8")
        int createComment(TestBean bean);//成功返回0
    
        //插入数据
        @Insert("INSERT INTO `runoob_bbb`(" +
                "`runoob_title`" +
                ")VALUES(" +
                "'不错'" +//注意字符串单引号不同
                ")")
        int addComment(TestBean bean);
    
        //删除数据
        @Delete("DELETE FROM `bingo_comment` WHERE `app_id`=-1")
        int deleteComment(TestBean bean);
    
        //修改数据
        @Update("UPDATE `runoob_bbb` SET `runoob_title`='我不服' WHERE `runoob_id`=1")
        int updateComment(TestBean bean);
    
        //查询数据#{userName}
        @Select("SELECT * FROM `runoob_bbb` where `runoob_id`=5")
        TestBean selectComment(TestBean bean);
    
    
        //更改表结构添加字段
        @Update("ALTER TABLE `runoob_bbb` ADD COLUMN `runoob_test` INT NOT NULL DEFAULT 0 AFTER `runoob_title`")
        int updateTableColumn();
    
        //更改表结构删除字段
        @Delete("ALTER TABLE `runoob_bbb` DROP COLUMN `GATEWAYID`")
        int deleteTableColumn();
    
        //更改表结构调整字段顺序（只能用after）
        @Update("ALTER TABLE `runoob_bbb` CHANGE `runoob_title` `runoob_title` VARCHAR(100) NOT NULL AFTER `runoob_test`")
        int updateTableColumnOrder();
    
        //更改表结构删除主键自增长（先删除自增长，再删除主键，再添加）
        @Update("Alter table `runoob_bbb` change `runoob_test` `runoob_test` int(10)")
        int updateTableDeleteKeyAuto();
        //更改表结构删除主键（先删除自增长，再删除主键，再添加）
        @Delete("alter table `runoob_bbb` drop primary key")
        int updateTableDeleteKey();
        //更改表结构修改主键（先删除自增长，再删除主键，再添加）
        @Update("ALTER TABLE `runoob_bbb` add primary key (runoob_id)")
        int updateTableUpdateKey();
    
        //更改键的类型
    // alter table t1 change b b bigint not null;
    // alter table infos change list list tinyint not null default ‘0′;
        @Update("alter table `bingo_reply` change `reply_time` `reply_time` VARCHAR(100) NOT NULL")
        int updateTableType();
    
        //修改字段名 -- 正常，能修改字段类型、类型长度、默认值、注释
        @Update("alter table bingo_comment change conment_time comment_time int(10) DEFAULT 0 COMMENT '评论时间'")
        int renameComment();
    
    
        //删除表
        @Delete("DROP TABLE `runoob_aaa`")
        int deleteTable();
        //删除表
        @Delete("DROP TABLE `runoob_bbb`")
        int deleteTable1();
        //删除表
        @Delete("DROP TABLE `runoob_tbl`")
        int deleteTable2();
    }

### 3. 创建Controller ###

/** * 前端控制层 * **/
    @RestController
    @RequestMapping("/admin")
    public class TestController { 
        @Autowired
        private TestService service;
        //https://store-api.bingo.fun/files/public/14/icon/x50
        @RequestMapping("addComment")
        public int setComment(TestBean bean){ 
    
            try { 
                int a = service.updateTableType();
                System.out.print(a);
                return a;
            }catch (Exception e){ 
                System.out.print(e.toString());
                return -123;
            }
        }
    }

[IDEA_SpringBoot]: https://blog.csdn.net/yangshuaionline/article/details/85101333
[SpringBoot]: https://blog.csdn.net/yangshuaionline/article/details/88662149