Springboot using JdbcTemplate

Preface

This article is an introduction to Springboot using the JdbcTemplate operation database, and provides a small demo for everyone to refer to.
There are many ways to manipulate databases, this article describes the use of Springboot combined with JdbcTemplate.
You can look at the public number "java architect cheats " pure dry Goods green natural pollution-free

New Project

Create a new project. To add a JDBC dependency to the Pom file, complete the pom as follows:

<?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> &L T;packaging>jar</packaging> <name>springboot_jdbc</name> &LT;DESCRIPTION&GT;SPRINGBOOT_JDBC </description> <parent> <groupId>org.springframework.boot</groupId> <artifacti D>spring-boot-starter-parent</artifactid> <version>1.5.9.RELEASE</version> <relative Path/> <!--lookup parent from repository to </parent> <properties> <project.build.s Ourceencoding>utf-8</project.build.sourCeencoding> <project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding> <ja            va.version>1.8</java.version> </properties> <dependencies> <dependency> <groupId>org.springframework.boot</groupId> &LT;ARTIFACTID&GT;SPRING-BOOT-STARTER-JDBC&LT;/ARTIFAC tid> </dependency> <dependency> <groupid>org.springframework.boot</groupi        D> <artifactId>spring-boot-devtools</artifactId> <scope>runtime</scope> </dependency> <dependency> <groupId>mysql</groupId> &LT;ARTIFAC        Tid>mysql-connector-java</artifactid> <scope>runtime</scope> </dependency> <dependency> <groupId>org.springframework.boot</groupId> &LT;ARTIFACTID&GT;SP Ring-boot-starter-test</artifactid> <scope>test</scope> </dependency> <dependency> <groupId>org.springframework.boot</groupId> <artifactid>spring-boot-starter-web</artifa ctid> </dependency> </dependencies> <build> <plugins> <plugin > <groupId>org.springframework.boot</groupId> <artifactid>spring-boot-m aven-plugin</artifactid> </plugin> </plugins> </build></project>

The configuration file is as follows:

##端口号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

Create a new entity class user, it should be noted that the user class implements the RowMapper class, overriding the Maprow method, the complete code is as follows:

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 [email&    Nbsp;protected] * @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; }}

The

Common curd operation uses the following three methods:
1.execute method for direct execution of SQL statements
2.update method, user new Modify delete operation
3.query method for Query method
This article, as usual, tests with a controller to inject JdbcTemplate. The complete code is as follows, and the test method is described below:

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 [email&nbsp ;p rotected] * @date 2018/7/25 */@RestControllerpublic class Usercontroller {@Autowired private jdbctemplate Jdbctem    Plate Http://localhost:8888/createTable @GetMapping ("createtable") public String CreateTable () {String sql = "CRE ATE TABLE ' user ' (\ n "+" ' id ' int (one) 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 "Create user table succeeded";  }//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 "execution success, impact" +rows+ "line"; }//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 "execution success, impact" +rows+ "line"; }//http://localhost:8888/updateuserpassword?id=1&password=111 @GetMapping ("Updateuserpassword") public Strin G Updateuserpassword (int id,string passWord) {int rows= jdbctemplate.update ("update USER SET Pass_word =?")        WHERE ID =? ", Password,id); Return "executes into"+rows+" line ";         }//http://localhost:8888/deleteuserbyid?id=1 @GetMapping ("Deleteuserbyid") public String Deleteuserbyid (int id) {        int rows= jdbctemplate.update ("DELETE from USER WHERE id =?", id);    Return "execution success, impact" +rows+ "line";        }//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 < i++) {string[] arr = new string[2];            Arr[0] = "Zhangsan" +i;            ARR[1] = "password" +i;        Paramlist.add (arr);        } jdbctemplate.batchupdate (Sql,paramlist);    Return "Successful execution"; }//http://localhost:8888/getuserbyusername?username=zhangsan0 @GetMapping ("Getuserbyusername") public List GetUs Erbyusername (String userName) {String sql = "SELECT * FROM USER WHERE user_name = ?"; Many kinds of writing//The following list of two kinds of writing, can be implemented//list<user> list= jdbctemplate.query (sql,new object[]{username}, new Beanpro        Pertyrowmapper (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) {STR        ing 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; }}

Introduction to test methods
1.createTable method
To create a user table using the Execute method
2.saveUserSql method
Using the Update method, pass in the parameter SQL statement and perform the insert operation directly
3.saveUser method
Use the Update method to pass in SQL statements and corresponding field values for insert operations
4.updateUserPassword method
Use the Update method to pass in SQL statements and corresponding field values for modification
5.deleteUserById method
Use the Update method to pass in the SQL statement and the corresponding field value for the delete operation
6.batchSaveUserSql method
Use the BatchUpdate method to pass in SQL and parameter collections for batch updates
7.getUserByUserName method
Using the Query method, passing in SQL, entity objects, query parameters, here is the Maprow method of the entity class rewrite
8.getMapById method
Using the queryForMap method, pass in SQL and parameters and return to map
9.getUserById method
Use the queryForObject method, pass in SQL, entity object, query parameter, return user entity class, here also use the Maprow method that entity class overrides
There are many more ways to use it, please refer to the documentation:
Docs.spring.io/spring/docs ...

Summary Note

Do not worry about errors,
Https://user-gold-cdn.xitu.io/2018/7/25/164d0e2ea962f31b?imageslim
This error occurs because SQL writes more quotes at the time of the parameter question mark, which is the error I made when I wrote the demo.
Welcome to add Q Group: 230419550 Learning Exchange Discussion architect Advanced knowledge

Springboot using JdbcTemplate