SpringBoot使用JdbcTemplate操作数据库

<?xml version= "1.0" encoding= "utf-8" ?>

<project xmlns= "http://maven.apache.org/pom/4.0.0" xmlns:xsi= "http://www.w3.org/2001/xmlschema-instance"

      xsi:schemalocation= "http://maven.apache.org/pom/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd" >

   <modelversion> 4.0 . 0 </modelversion>

   <groupid>com.dalaoyang</groupid>

   <artifactid>springboot_jdbc</artifactid>

   <version> 0.0 . 1 -snapshot</version>

   <packaging>jar</packaging>

   <name>springboot_jdbc</name>

   <description>springboot_jdbc</description>

   <parent>

     <groupid>org.springframework.boot</groupid>

     <artifactid>spring-boot-starter-parent</artifactid>

     <version> 1.5 . 9 .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.8 </java.version>

   </properties>

   <dependencies>

     <dependency>

       <groupid>org.springframework.boot</groupid>

       <artifactid>spring-boot-starter-jdbc</artifactid>

     </dependency>

     <dependency>

       <groupid>org.springframework.boot</groupid>

       <artifactid>spring-boot-devtools</artifactid>

       <scope>runtime</scope>

     </dependency>

     <dependency>

       <groupid>mysql</groupid>

       <artifactid>mysql-connector-java</artifactid>

       <scope>runtime</scope>

     </dependency>

     <dependency>

       <groupid>org.springframework.boot</groupid>

       <artifactid>spring-boot-starter-test</artifactid>

       <scope>test</scope>

     </dependency>

     <dependency>

       <groupid>org.springframework.boot</groupid>

       <artifactid>spring-boot-starter-web</artifactid>

     </dependency>

   </dependencies>

   <build>

     <plugins>

       <plugin>

         <groupid>org.springframework.boot</groupid>

         <artifactid>spring-boot-maven-plugin</artifactid>

       </plugin>

     </plugins>

   </build>

</project>

##端口号

server.port= 8888

##数据库配置

##数据库地址

spring.datasource.url=jdbc:mysql: //localhost:3306/test?characterencoding=utf8&usessl=false

##数据库用户名

spring.datasource.username=root

##数据库密码

spring.datasource.password= 123456

##数据库驱动

spring.datasource.driver- class -name=com.mysql.jdbc.driver

package com.dalaoyang.entity;

import org.springframework.jdbc.core.rowmapper;

import java.sql.resultset;

import java.sql.sqlexception;

/**

  * @author dalaoyang

  * @project springboot_learn

  * @package com.dalaoyang.entity

  * @email yangyang@dalaoyang.cn

  * @date 2018/7/25

  */

public class user implements rowmapper<user> {

   private int id;

   private string user_name;

   private string pass_word;

   public user( int id, string user_name, string pass_word) {

     this .id = id;

     this .user_name = user_name;

     this .pass_word = pass_word;

   }

   public user() {

   }

   public user(string user_name, string pass_word) {

     this .user_name = user_name;

     this .pass_word = pass_word;

   }

   public int getid() {

     return id;

   }

   public void setid( int id) {

     this .id = id;

   }

   public string getuser_name() {

     return user_name;

   }

   public void setuser_name(string user_name) {

     this .user_name = user_name;

   }

   public string getpass_word() {

     return pass_word;

   }

   public void setpass_word(string pass_word) {

     this .pass_word = pass_word;

   }

   @override

   public user maprow(resultset resultset, int i) throws sqlexception {

     user user = new user();

     user.setid(resultset.getint( "id" ));

     user.setuser_name(resultset.getstring( "user_name" ));

     user.setpass_word(resultset.getstring( "pass_word" ));

     return user;

   }

}

package com.dalaoyang.controller;

import com.dalaoyang.entity.user;

import org.springframework.beans.factory.annotation.autowired;

import org.springframework.jdbc.core.jdbctemplate;

import org.springframework.web.bind.annotation.getmapping;

import org.springframework.web.bind.annotation.restcontroller;

import java.util.arraylist;

import java.util.list;

import java.util.map;

/**

  * @author dalaoyang

  * @project springboot_learn

  * @package com.dalaoyang.controller

  * @email yangyang@dalaoyang.cn

  * @date 2018/7/25

  */

@restcontroller

public class usercontroller {

   @autowired

   private jdbctemplate jdbctemplate;

   //http://localhost:8888/createtable

   @getmapping ( "createtable" )

   public string createtable(){

     string sql = "create table `user` (\n" +

         " `id` int(11) not null auto_increment,\n" +

         " `user_name` varchar(255) default null,\n" +

         " `pass_word` varchar(255) default null,\n" +

         " primary key (`id`)\n" +

         ") engine=innodb auto_increment=7 default charset=utf8;\n" +

         "\n" ;

     jdbctemplate.execute(sql);

     return "创建user表成功" ;

   }

   //http://localhost:8888/saveusersql

   @getmapping ( "saveusersql" )

   public string saveusersql(){

     string sql = "insert into user (user_name,pass_word) values ('dalaoyang','123')" ;

     int rows= jdbctemplate.update(sql);

     return "执行成功，影响" +rows+ "行" ;

   }

   //http://localhost:8888/saveuser?username=lisi&password=111

   @getmapping ( "saveuser" )

   public string saveuser(string username,string password){

     int rows= jdbctemplate.update( "insert into user (user_name,pass_word) values (?,?)" ,username,password);

     return "执行成功，影响" +rows+ "行" ;

   }

   //http://localhost:8888/updateuserpassword?id=1&password=111

   @getmapping ( "updateuserpassword" )

   public string updateuserpassword( int id,string password){

     int rows= jdbctemplate.update( "update user set pass_word = ? where id = ?" ,password,id);

     return "执行成功，影响" +rows+ "行" ;

   }

   //http://localhost:8888/deleteuserbyid?id=1

   @getmapping ( "deleteuserbyid" )

   public string deleteuserbyid( int id){

     int rows= jdbctemplate.update( "delete from user where id = ?" ,id);

     return "执行成功，影响" +rows+ "行" ;

   }

   //http://localhost:8888/batchsaveusersql

   @getmapping ( "batchsaveusersql" )

   public string batchsaveusersql(){

     string sql =

         "insert into user (user_name,pass_word) values (?,?)" ;

     list<object[]> paramlist = new arraylist<>();

     for ( int i = 0 ; i < 10 ; i++) {

       string[] arr = new string[ 2 ];

       arr[ 0 ] = "zhangsan" +i;

       arr[ 1 ] = "password" +i;

       paramlist.add(arr);

     }

     jdbctemplate.batchupdate(sql,paramlist);

     return "执行成功" ;

   }

   //http://localhost:8888/getuserbyusername?username=zhangsan0

   @getmapping ( "getuserbyusername" )

   public list getuserbyusername(string username){

     string sql = "select * from user where user_name = ?" ;

     //写法很多种

     //下面列举两种写法，都可以实现

     //list<user> list= jdbctemplate.query(sql,new object[]{username}, new beanpropertyrowmapper(user.class));

     list<user> list= jdbctemplate.query(sql, new user(), new object[]{username});

     return list;

   }

   //http://localhost:8888/getmapbyid?id=1

   @getmapping ( "getmapbyid" )

   public map getmapbyid(integer id){

     string sql = "select * from user where id = ?" ;

     map map= jdbctemplate.queryformap(sql,id);

     return map;

   }

   //http://localhost:8888/getuserbyid?id=1

   @getmapping ( "getuserbyid" )

   public user getuserbyid(integer id){

     string sql = "select * from user where id = ?" ;

     user user= jdbctemplate.queryforobject(sql, new user(), new object[]{id});

     return user;

   }

}