Skip to main content

Spring JDBC client for seekdb

This topic describes how to build an application by using Spring JDBC, MySQL Connector/J, and seekdb. The application performs basic database operations, such as table creation, data insertion, and data query.

Download the spring-jdbc-mysql-client sample project

Features​

  • Supports basic CRUD operations
  • Provides transaction management
  • Exception handling mechanism
  • Connection pool management

Scenarios​

  • A scenario where you need to directly use SQL statements.
  • A scenario where high performance is required.
  • A simple database operation scenario.

Prerequisites​

  • You have installed seekdb.

  • You have installed JDK 1.8 and Maven.

  • You have installed Eclipse.

    info

    The code examples in this document are run in Eclipse IDE for Java Developers 2022-03. You can choose your preferred tool to run the code examples.

Procedure​

info

The following procedure describes how to compile and run the spring-jdbc-mysql-client project in the Eclipse IDE for Java Developers 2022-03 in a Windows environment. If you are using a different operating system or compiler, the procedure may vary slightly.

  1. Import the spring-jdbc-mysql-client project into Eclipse.
  2. Obtain the seekdb URL.
  3. Modify the database connection information in the spring-jdbc-mysql-client project.
  4. Run the spring-jdbc-mysql-client project.

Step 1: Import the spring-jdbc-mysql-client project into Eclipse​

  1. Open Eclipse and select File > Open Projects from File System from the menu bar.

  2. In the dialog box that appears, click Directory to select the project directory and then click Finish to complete the import.

    info

    When importing a Maven project into Eclipse, Eclipse automatically detects the pom.xml file in the project, downloads the required dependency libraries based on the described dependencies in the file, and adds them to the project.

    Import

  3. View the project.

    p1

Step 2: Obtain the seekdb URL​

  1. Contact the seekdb deployment personnel or administrator to obtain the database connection string.

    Example:

    mysql -hxxx.xxx.xxx.xxx -P2881 -uroot -p****** -Dtest

    For more information about the connection string, see Connect to seekdb by using a MySQL client.

  2. Fill in the corresponding information in the URL based on the seekdb connection string.

    jdbc:mysql://$host:$port/$database_name?user=$user_name&password=$password&useSSL=false

    Parameter Description:

    • $host: the IP address for connecting to seekdb. Replace this with the actual IP address. You can also use the local IP address, 127.0.0.1.
    • $port: the port for connecting to seekdb. Replace this with the actual port. The default port is 2881, which can be customized when deploying seekdb.
    • $database_name: the name of the database to be accessed.
    • user_name: the connection account. Format: username.
    • password: the account password.

    For more information about MySQL Connector/J connection properties, see Configuration Properties.

    Example:

    jdbc:mysql://xxx.xxx.xxx.xxx:2881/test?user=root&password=******&useSSL=false

Step 3: Modify the database connection information in the spring-jdbc-mysql-client project​

Modify the database connection information in the spring-jdbc-mysql-client/src/main/java/com/example/Main.java file based on the information obtained in Step 2: Obtain the seekdb URL.

3

Example:

  • The IP address of seekdb is xxx.xxx.xxx.xxx.
  • The access port is 2881.
  • The name of the database to be accessed is test.
  • The connection account is root.
  • The password is ******.

Code:

...
        String url = "jdbc:mysql://xxx.xxx.xxx.xxx:2881/test?useSSL=false";
        String username = "root";
        String password = "******";
...

Step 4: Run the spring-jdbc-mysql-client project​

  1. In the Project Explorer view, locate and expand the src/main/java directory.

  2. Right-click the Main.java file and select Run As > Java Application.

    run

  3. View the output results in the console window of Eclipse.

    5

FAQs​

1. Connection timeout​

If you encounter a connection timeout issue, you can configure the connection timeout parameter in the JDBC URL:

jdbc:mysql://host:port/database?connectTimeout=30000&socketTimeout=60000

2. Character set​

To ensure correct character encoding, set the appropriate character set parameter in the JDBC URL:

jdbc:mysql://host:port/database?characterEncoding=utf8&useUnicode=true

3. SSL connection​

To enable an SSL connection to seekdb, add the following parameter to the JDBC URL:

jdbc:mysql://host:port/database?useSSL=true&requireSSL=true

4. Special characters in the account or password​

If the username or password contains special characters (such as #), you need to URL-encode them:

String encodedPassword = URLEncoder.encode(password, "UTF-8");
info

When using MySQL Connector/J 8.x, ensure that the username and password do not contain the number sign (#). Otherwise, you may encounter a connection error.

Project code​

Click spring-jdbc-mysql-client to download the project code, which is a compressed file named spring-jdbc-mysql-client.zip.

After decompressing it, you will find a folder named spring-jdbc-mysql-client. The directory structure is as follows:

spring-jdbc-mysql-client
├── src
│   └── main
│       └── java
│           └── com
│                └── example
│                   └── Main.java
└── pom.xml

File description:

  • src: the root directory for source code.
  • main: the main code directory, containing the core logic of the application.
  • java: the directory for Java source code.
  • com: the directory for Java package.
  • example: the directory for the package of the sample project.
  • Main.java: the main class, containing logic such as table creation and data insertion.
  • pom.xml: the configuration file for the Maven project, used to manage project dependencies and build settings.

pom.xml code​

The pom.xml file is a configuration file for a Maven project. It defines the project's dependencies, plugins, and build rules. Maven is a Java project management tool that can automatically download dependencies, compile, and package the project.

The pom.xml file in this topic contains the following main sections:

  1. File declaration statement.

    This statement declares the file as an XML file using XML version 1.0 and character encoding UTF-8.

    Sample code:

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

  2. Configure the POM namespace and POM model version.

    1. Use xmlns to specify the POM namespace as http://maven.apache.org/POM/4.0.0.
    2. Use xmlns:xsi to specify the XML namespace as http://www.w3.org/2001/XMLSchema-instance.
    3. Use xsi:schemaLocation to specify the POM namespace as http://maven.apache.org/POM/4.0.0 and the location of the POM XSD file as http://maven.apache.org/xsd/maven-4.0.0.xsd.
    4. Use the <modelVersion> element to specify the POM model version used by the POM file as 4.0.0.

    Sample code:

    <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>

     <!-- Other configurations -->

    </project>

  3. Configure basic information.

    1. Use <groupId> to specify the project's organization as com.example.
    2. Use <artifactId> to specify the project's name as spring-jdbc-mysql-client.
    3. Use <version> to specify the project's version as 1.0-SNAPSHOT.

    Sample code:

        <groupId>com.example</groupId>
        <artifactId>spring-jdbc-mysql-client</artifactId>
        <version>1.0-SNAPSHOT</version>

  4. Configure the properties of the project's source files.

    Specify the Maven compiler plugin as maven-compiler-plugin and set both the source code and target Java versions to 8. This means the project's source code is written using Java 8 features, and the compiled bytecode will also be compatible with the Java 8 runtime environment. This setup ensures that the project can correctly handle Java 8 syntax and features during compilation and runtime.

    info

    Java 1.8 and Java 8 are different names for the same version.

    Sample code:

        <build>
            <plugins>
                <plugin>
                    <groupId>org.apache.maven.plugins</groupId>
                    <artifactId>maven-compiler-plugin</artifactId>
                    <configuration>
                        <source>8</source>
                        <target>8</target>
                    </configuration>
                </plugin>
            </plugins>
        </build>

  5. Configure the components that the project depends on.

    1. Add the mysql-connector-java dependency library for interacting with the database:

      info

      This section of code defines the project's dependency component as MySQL Connector/J V5.1.40. For information about other versions, see MySQL Connector/J.

      1. Use <groupId> to specify the dependency's organization as mysql.
      2. Use <artifactId> to specify the dependency's name as mysql-connector-java.
      3. Use <version> to specify the dependency's version as 5.1.40.

      Sample code:

              <dependency>
                  <groupId>mysql</groupId>
                  <artifactId>mysql-connector-java</artifactId>
                  <version>5.1.40</version>
              </dependency>

    2. Add the spring-jdbc dependency library:

      1. Use <groupId> to specify the dependency's organization as org.springframework.
      2. Use <artifactId> to specify the dependency's name as spring-jdbc.
      3. Use <version> to specify the dependency's version as 5.2.0.RELEASE.

      Sample code:

              <dependency>
                  <groupId>org.springframework</groupId>
                  <artifactId>spring-jdbc</artifactId>
                  <version>5.2.0.RELEASE</version>
              </dependency>

Main.java code​

The Main.java file is part of the sample program, and the code demonstrates how to use the Spring JDBC framework to perform database operations. It first configures the database connection information and then creates a JdbcTemplate object to execute database operations. The code also includes examples of creating tables, inserting data, and querying data.

The code in the Main.java file in this topic mainly includes the following parts:

  1. Define the package and import the necessary classes.

  2. Declare the package name of the current code as com.example. 2. Import the JdbcTemplate class, which is the core class in the Spring JDBC framework and is used to execute database operations. 3. Import the DriverManagerDataSource class, which is a data source class provided by Spring that implements the DataSource interface and is used to configure database connection information.

Code:

    package com.example;

    import org.springframework.jdbc.core.JdbcTemplate;
    import org.springframework.jdbc.datasource.DriverManagerDataSource;

  1. Create a Main class and define the main method.

  2. Define a public class named Main as the entry point of the program. The class name must be consistent with the file name. 2. Define a public static method main as the starting point of the program. 3. Other database operations.

Code:

    public class Main {
        public static void main(String[] args) {
            // Database connection information
            // Create data source
            // Create JdbcTemplate object
            // Create table
            // Insert data
            // Update data
            // Delete data
            // Query data
            // Delete table
        }
    }
  1. Define the database connection information.

Define the URL, username, and password for connecting to the database. You need to replace $host, $port, $database_name, $user_name, and $password with the actual database connection information.

Code:

            String url = "jdbc:mysql://$host:$port/$database_name?useSSL=false";
            String username = "$user_name";
            String password = "$password";

Parameter description:

  • $host: the IP address for connecting to seekdb. Replace it with the actual IP address. You can also use the local IP address 127.0.0.1.
    • $port: the port for connecting to seekdb. Replace it with the actual port number. The default port is 2881, which can be customized when deploying seekdb.
    • $database_name: the name of the database to be accessed.
    • $user_name: the username for connecting to the database. Format: username.
    • $password: the password for the account.
  1. Create a data source.

Create a DriverManagerDataSource object and configure the database connection information, including the driver class name, database URL, username, and password. The specific steps are as follows:

  1. Create a DriverManagerDataSource object to configure the database connection information. 2. Set the driver class name: call the setDriverClassName method to set the database driver class name to com.mysql.jdbc.Driver. 3. Set the database URL: call the setUrl method to set the database URL. 4. Set the username: call the setUsername method to set the database username. 5. Set the password: call the setPassword method to set the database password.

Code:

            DriverManagerDataSource dataSource = new DriverManagerDataSource();
            dataSource.setDriverClassName("com.mysql.jdbc.Driver");
            dataSource.setUrl(url);
            dataSource.setUsername(username);
            dataSource.setPassword(password);
  1. Create a JdbcTemplate object.

Create a JdbcTemplate object, which is one of the core classes in the Spring JDBC framework. The JdbcTemplate class provides methods for performing database operations, such as executing SQL statements, updating data, and querying data.

Code:

            JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);

  1. Create a table.

  2. Define an SQL statement for creating a table. The SQL statement specifies the table name as test_springjdbc and defines two columns: one is an INT column named id, and the other is a VARCHAR(20) column named name. 2. Execute the SQL statement for creating the table. The jdbcTemplate object calls the execute method to execute the defined SQL statement for creating the table. 3. Output a message indicating that the table was successfully created.

Code:

            String createTableSql = "CREATE TABLE test_springjdbc (id INT, name VARCHAR(20))";
            jdbcTemplate.execute(createTableSql);
            System.out.println("Create table successfully.");

  1. Insert data.

  2. Define an SQL statement for inserting data. The SQL statement specifies the table to insert data into as test_springjdbc, with columns id and name. Insert three rows of data: (1,'A1'), (2,'A2'), and (3,'A3'). 2. Execute the SQL statement for inserting data. The jdbcTemplate object calls the update method to execute the defined SQL statement for inserting data. 3. Output a message indicating that the data was successfully inserted.

Code:

            String insertDataSql = "INSERT INTO test_springjdbc (id,name) VALUES (1,'A1'), (2,'A2'), (3,'A3')";
            jdbcTemplate.update(insertDataSql);
            System.out.println("Insert data successfully.");

  1. Update data.

  2. Define an SQL statement for updating the name field of the record with id equal to 1 in the test_springjdbc table to A11. 2. Execute the SQL statement using the jdbcTemplate object to apply the update operation to the database. 3. Output a message indicating that the data was successfully updated.

Code:

            String updateDataSql = "UPDATE test_springjdbc SET name = 'A11' WHERE id = 1";
            jdbcTemplate.update(updateDataSql);
            System.out.println("Update data successfully.");

  1. Delete data.

  2. Define an SQL statement for deleting the record with id equal to 2 from the test_springjdbc table. 2. Execute the SQL statement using the jdbcTemplate object to apply the delete operation to the database. 3. Output a message indicating that the data was successfully deleted.

Code:

            String deleteDataSql = "DELETE FROM test_springjdbc WHERE id = 2";
            jdbcTemplate.update(deleteDataSql);
            System.out.println("Delete data successfully.");

  1. Query data.

  2. Define an SQL statement for querying data. The SQL statement uses SELECT * to select all columns from the test_springjdbc table. 2. Execute the SQL statement for querying data. The jdbcTemplate object calls the query method to execute the defined SQL statement for querying data and receives a callback function as a parameter. 3. The code in the callback function uses the ResultSet object (rs) to retrieve each row of data from the query results and process it. The callback function uses the getInt and getString methods to retrieve the values of the id and name columns from the ResultSet object and prints them to the console. Finally, the callback function returns null. 4. Output a message indicating that the data was successfully queried.

Code:

            String selectDataSql = "SELECT * FROM test_springjdbc";
            jdbcTemplate.query(selectDataSql, (rs, rowNum) -> {
                int id = rs.getInt("id");
                String name = rs.getString("name");
                System.out.println("id: " + id + ", name: " + name);
                return null;
            });
            System.out.println("Query data successfully.");

  1. Delete the table.

  2. Define an SQL statement to drop the test_springjdbc table in the database. 2. Execute the SQL statement using the jdbcTemplate object to apply the drop table operation to the database. 3. Output a message indicating that the table was successfully dropped.

Sample code:

            String deleteTableSql = "DROP TABLE test_springjdbc";
            jdbcTemplate.execute(deleteTableSql);
            System.out.println("Table drop successfully.");

Full code​

pom.xml​

<?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.example</groupId>
    <artifactId>spring-jdbc-mysql-client</artifactId>
    <version>1.0-SNAPSHOT</version>

    <build>
        <plugins>
            <plugin>
                <groupId>org.apache.maven.plugins</groupId>
                <artifactId>maven-compiler-plugin</artifactId>
                <configuration>
                    <source>8</source>
                    <target>8</target>
                </configuration>
            </plugin>
        </plugins>
    </build>

    <dependencies>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.40</version>
        </dependency>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-jdbc</artifactId>
            <version>5.2.0.RELEASE</version>
        </dependency>
    </dependencies>

</project>

Main.java​

package com.example;

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.datasource.DriverManagerDataSource;

public class Main {
    public static void main(String[] args) {
        // Database Connect Information
        String url = "jdbc:mysql://$host:$port/$database_name?useSSL=false";
        String username = "$user_name";
        String password = "$password";

        // create data source
        DriverManagerDataSource dataSource = new DriverManagerDataSource();
        dataSource.setDriverClassName("com.mysql.jdbc.Driver");
        dataSource.setUrl(url);
        dataSource.setUsername(username);
        dataSource.setPassword(password);

        // Create a JdbcTemplate object
        JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);

        // Create table
        String createTableSql = "CREATE TABLE test_springjdbc (id INT, name VARCHAR(20))";
        jdbcTemplate.execute(createTableSql);
        System.out.println("Create table successfully.");

        // Insert data
        String insertDataSql = "INSERT INTO test_springjdbc (id,name) VALUES (1,'A1'), (2,'A2'), (3,'A3')";
        jdbcTemplate.update(insertDataSql);
        System.out.println("Insert data successfully.");

        // Update data
        String updateDataSql = "UPDATE test_springjdbc SET name = 'A11' WHERE id = 1";
        jdbcTemplate.update(updateDataSql);
        System.out.println("Update data successfully.");

        // Delete data
        String deleteDataSql = "DELETE FROM test_springjdbc WHERE id = 2";
        jdbcTemplate.update(deleteDataSql);
        System.out.println("Delete data successfully.");

        // Query data
        String selectDataSql = "SELECT * FROM test_springjdbc";
        jdbcTemplate.query(selectDataSql, (rs, rowNum) -> {
            int id = rs.getInt("id");
            String name = rs.getString("name");
            System.out.println("id: " + id + ", name: " + name);
            return null;
        });
        System.out.println("Query data successfully.");

        // Delete table
        String deleteTableSql = "DROP TABLE test_springjdbc";
        jdbcTemplate.execute(deleteTableSql);
        System.out.println("Table drop successfully.");
    }
}

Best Practices​

Transaction Management​

@Transactional
public void transferMoney(String fromAccount, String toAccount, BigDecimal amount) {
    // Transaction operations
}

References​

For more information about MySQL Connector/J, see Overview of MySQL Connector/J.

Contents

Edit this page