Integration of JDBC

Using SpringBoot to operate the database is easier and more black box operation.

Importing dependent dependencies

To create the SpringBoot project, select Web, JDBC API, and MySQL Driver.

<dependencies>
    <! -- web start-->
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-web</artifactId>
    </dependency>
    <! -- jdbc start-->
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-jdbc</artifactId>
    </dependency>
	<! Mysql > select * from 'mysql';
    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <scope>runtime</scope>
    </dependency>
    <! - test - >
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-test</artifactId>
        <scope>test</scope>
    </dependency>
</dependencies>
Copy the code

Writing configuration files

Write configurations related to database connections in the SpringBoot configuration file

spring:
  datasource:
  	# username
    username: root
    # your password
    password: * * * * * * * *
    # database path (version 8 requires time zone configuration)
    url: JDBC: mysql: / / localhost: 3306 / database name? useUnicode=true&characterEncoding=utf-8&useSSL=true
    # driver
    driver-class-name: com.mysql.cj.jdbc.Driver
Copy the code

test

@SpringBootTest
class SqlSpringbootApplicationTests {
    // Inject the data source
    @Autowired
    DataSource dataSource;

    @Test
    void contextLoads(a) throws SQLException {
        // View the default data source
        System.out.println(dataSource.getClass());
        // Get the connection
        Connection connection = dataSource.getConnection();
        // Check the connection implementation
        System.out.println(connection);
        // Close the connectionconnection.close(); }}Copy the code

Output result:

If no error message is displayed, the preparations are ok.

SpringBoot operates databases and is much simpler than traditional JDBC.

In my opinion, it is best to make native JDBC clear and not rely too much on SpringBoot.

Add and delete

Making sure that everything is ready, we can start using SpringBoot directly to manipulate the database.

// Use the restController annotation to return a string instead of the view name
@RestController
public class JDBCController {
    // Inject the JDBC template Bean wrapped by SpringBoot
    // This class is the core class for dealing with databases and encapsulates a number of methods
    @Autowired
    JdbcTemplate jdbcTemplate;

    // Set the query operation and access path
    @GetMapping("/users")
    // select k as String and v as Object
    public List<Map<String,Object>> query(){
        // Write an SQL query for all database fields
        String sql = "select * from user";
        // Use the JDBC template to perform the query operation, passing in the above SQL
        return jdbcTemplate.queryForList(sql);
    }
	// Set the add operation, access path
    @GetMapping("/add/user")
    public String addUser(a){write an add field SQL, omit id because id increment write String SQL =Insert into 'user' (name, PWD) values(' Test','Test');;
        // Pass the SQL to the UPDATE method in the JDBC template
        jdbcTemplate.update(sql);
        // Give front-end feedback
        return "AddSuccess";
    }
	// Set the modification operation, access path.
    // Carry the id argument at the end of the argument
    @GetMapping("/update/user/{id}")
    // Use the @pathVariable annotation to bind the id in the method to the id parameter carried by the path
    public String updateUser(@PathVariable("id")Integer id){
        // Write a modified SQL whose WHERE clause ID condition is passed in as the ID parameter carried in the path
        // The value of the modified 'name' 'PWD' field in this SQL is also passed in
        String sql = "update `user` set `name` =? ,`pwd`=? where id ="+id;
        // Write an Object array
        Object[] objects = new Object[2];
        // The first value of this array is the 'name' field value
        objects[0] = "Lin";
        // The second value of this array is the 'PWD' field value
        objects[1] = "molu270";
        // Pass the Object array as the second argument to the UPDATE method in the JDBC template
        jdbcTemplate.update(sql,objects);
        // Give front-end feedback
        return "UpdateSuccess";
    }
	// Set the delete operation, access path, this path also carries parameters
    @GetMapping("/delete/user/{id}")
    // Use the @pathVariable annotation to bind the id in the method to the id parameter carried by the path
    public String deleteUser(@PathVariable("id")int id){
        // Write a delete SQL whose WHERE clause ID condition is passed in by the id parameter carried by the path
        String sql = "delete from `user` where id ="+id;
        // Pass the SQL to the method in the JDBC template
        jdbcTemplate.update(sql);
        // Give front-end feedback
        return "DeleteSuccess"; }}Copy the code

We can also write the field values and conditions that need to be passed in directly into SQL.

At the end of the day, you’re still using SQL to manipulate the database. It’s not that difficult. Using template classes provided by SpringBoot saves a lot of operations, such as committing transactions.

Test:

Druid data source

introduce

Druid is a database connection pool implementation on Alibaba open source platform. It combines the advantages of C3PO, DBCP, PROXOOL and other DB pools with log monitoring.

Druid is designed to monitor DB connection pool and SQL execution.

Spring Boot 2.0 and beyond uses Hikari data sources by default, and Hikari and Driud are arguably the best data sources on the Java Web today.

Let’s focus on how Spring Boot integrates with Druid data sources and implements database monitoring.

The preparatory work

First you need to import Druid’s initiator, along with log4j

<! -- Druid starter-->
<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>druid-spring-boot-starter</artifactId>
    <version>1.1.21</version>
</dependency>
<! -- log4j dependencies -->
<dependency>
    <groupId>log4j</groupId>
    <artifactId>log4j</artifactId>
    <version>1.2.17</version>
</dependency>
Copy the code

After importing the dependencies, we switched the Default Hikari data source for SpringBoot to Druid

Set data source to Druid
spring:
  datasource:
    type: com.alibaba.druid.pool.DruidDataSource
Copy the code

Configure Druid, more configuration can be DruidAbstractDataSource. Java in view.

Set data source to Druid
type: com.alibaba.druid.pool.DruidDataSource

#druid Data source proprietary configuration
initialSize: 5
minIdle: 5
maxActive: 20
maxWait: 60000
timeBetweenEvictionRunsMillis: 60000
minEvictableIdleTimeMillis: 300000
validationQuery: SELECT 1 FROM DUAL
testWhileIdle: true
testOnBorrow: false
testOnReturn: false
poolPreparedStatements: true
# Configure filters to monitor statistics interception, stat: monitor statistics, log4j: logging, wall: defend against SQL injection
# if an error is allowed, Java. Lang. ClassNotFoundException: org.. Apache Log4j. Properity
Import log4j dependencies
filters: stat,wall,log4j
maxPoolPreparedStatementPerConnectionSize: 20
useGlobalDataSourceStat: true
connectionoProperties: druid.stat.mergeSql=true; druid.stat.slowSqlMillis=500
Copy the code

Experience the

The power of Druid is its built-in SQL monitoring, logging, and more.

Let’s write a simple Config to test it

@Configuration
public class DruidConfig {

    // bind the configuration class to the configuration item in the configuration file
    @ConfigurationProperties(prefix = "spring.datasource")
    @Bean
    public DataSource druidDataSource(a){
        return new DruidDataSource();
    }

    // Background monitoring
    @Bean
    public ServletRegistrationBean statViewServlet(a){
        // Set the background monitoring access path
        ServletRegistrationBean<StatViewServlet> bean = new ServletRegistrationBean<>(new StatViewServlet(),"/druid/*");
        HashMap<String,String> initParameters = new HashMap<>();
        // Set the login name of the background monitoring page. The key is a fixed parameter and cannot be written incorrectly
        initParameters.put("loginUsername"."admin");
        // Set the password of the background monitoring page. The key is a fixed parameter and cannot be written wrong
        initParameters.put("loginPassword"."* * * * * * * *");

        // Set background access permission. If the parameter is empty, all users can access it
        // It can also be written as localhost or a specific IP address that will allow local or specified hosts to access
        initParameters.put("allow"."");
        // Disallow access to objects
        // You can configure the specified host name and IP address in the kv. After the configuration, the host is not allowed to access the monitoring background
        initParameters.put("k"."v");

        // Set the initialization parameters
        bean.setInitParameters(initParameters);
        returnbean; }}Copy the code

The Servlet container is built into SpringBoot, and we can use SpringBoot to provide alternative classes such as ServletRegistrationBean to achieve similar results without configuring servlets

Localhost :8080/druid/ path

Once accessed, we can login to the monitoring background using login.html provided by Druid

The user name, password, and access permission can be changed through Config

When you log in, the index.html page looks like this.

You can view SQL monitoring, logging, firewall and other functions on this page

We can run an SQL test to see the effect.

After performing the add operation:

SQL firewall

---

It’s pretty powerful. In addition to SQL monitoring and firewalls, Druid gets all the information about our data source

---

I can only say NB

---

Relax your eyes

Original picture P station address

Painters home page

---