catalogue

1, What have you done recently?

2, Introduction, problems and Solutions

1. what is a JdbcTemplate

2. Preparation of jdbctemplate

3. Use of jdbctemplate - execute and update methods

4. Differences between execute and update methods (Reference)

5. finally, how to solve the problem that execute cannot transfer parameters?

1, What have you done recently?

Since the beginning of May, it has mainly improved the functions of the administrator side and fixed the bug s on the user side.

In the previous work, when the functions of creating a new database and a new data table were implemented, the created tables had no entities, but stored the schema information of the tables in the corresponding system data tables, and did not really create a real database in MySQL or a real table in the corresponding database.

Therefore, it is necessary to improve the back-end method of the system.

Before, the system framework directly used mybatis for database processing, but the data source has been configured, and the new database cannot be dynamically configured into the project, so the project uses JDBC for database operation.

In the part I am responsible for, I use the JdbcTemplate. Let's briefly introduce the JdbcTemplate, and then talk about some problems and solutions I encountered when using it

2, Introduction, problems and Solutions

1. what is a JdbcTemplate

The Spring framework encapsulates JDBC and uses the JdbcTemplate to facilitate database operations

2. Preparation of jdbctemplate

Import jar package

spring-beans-4.1.2.RELEASE.jar
spring-core-4.1.2.RELEASE.jar
spring-jdbc-4.1.2.RELEASE.jar
spring-tx-4.1.2.RELEASE.jar
com.springsource.org.apache.commons.logging-1.1.1.jar

Full xml

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
       xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
       xmlns:context="http://www.springframework.org/schema/context"
       xmlns:aop="http://www.springframework.org/schema/aop"
       xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd
       http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context.xsd
       http://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop.xsd">
 
    <!-- Database connection pool -->
    <bean id="dataSource" class="com.alibaba.druid.pool.DruidDataSource"
          destroy-method="close">
        <property name="url" value="jdbc:mysql:///user_ DB "/ > <! -- corresponding to the database in SQLyog -- >
        <property name="username" value="root" />            <!-- user name -->
        <property name="password" value="4.233928" />        <!-- password -->
        <property name="driverClassName" value="com.mysql.jdbc.Driver" />
    </bean>
 
    <!-- JdbcTemplate object -->
    <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
        <!--injection dataSource attribute-->
        <property name="dataSource" ref="dataSource"></property>
    </bean>
 
    <!-- Component scan -->
    <context:component-scan base-package="JDBC"></context:component-scan>
 
</beans>

3. Use of jdbctemplate - execute and update methods

There are three types of methods for executing SQL statements in JdbcTemplate:

1. Execute: all SQL statements can be executed. It is generally used to execute DDL statements.
2. Update: used to execute DML statements such as INSERT, update, DELETE, etc.
3. Query: used for DQL data query statements such as SELECT.

In my own use, I mainly use the execute and update methods. As a result, I found that an error occurred when I used the execute method to delete the database table. The reason is that the execute method has no way to transfer parameters, which makes it impossible to delete the table referred to by the table name passed through the front end.

4. Differences between execute and update methods( reference resources)

• update can take parameters, but execute cannot. For example:

jdbcTemplate.update("update TableA set name = 'Andy' where id=?", new Object[] {new Integer(3)}); 

jdbcTemplate.update("insert into sys_person(person_id, person_name, gender) values(?,?,?)",new Object[]{"eeee","eeee","eeee"});

jdbcTemplate.execute("update TableA set name = 'Andy' where id=3"); 

• Behind update, java sql. Preparedstatement is completed, and execute is based on java sql. Statement.
• update returns int, that is, the number of rows affected. execute returns void

5. finally, how to solve the problem that execute cannot transfer parameters?

Directly use JDBC statements instead of JdbcTemplate

The codes are as follows:

public int specificDelete(int id,int DBId)throws Exception{
        DB db=dbMapper.findDBById(DBId);
        String dbName=db.getDBEN();
        System.out.println(dbName);
        Chart chart=chartMapper.findChartById(id,DBId);
        String chartName= chart.getEN();
        System.out.println(chartName);

        String driver = "com.mysql.jdbc.Driver";
        String url = "jdbc:mysql://127.0.0.1:3306/"+dbName;
        String user = "root";
        String password = "ｘｘｘｘｘ";

        Connection connection=null;
        try{
            Class.forName(driver);
            connection = DriverManager.getConnection(url, user, password);
            String sql="drop table "+chartName;
            PreparedStatement statement = connection.prepareStatement(sql);
            int resultSet = statement.executeUpdate();
            System.out.println(resultSet);
        }catch (ClassNotFoundException e){
            e.printStackTrace();
        }catch (SQLException e){
            e.printStackTrace();
        }

        connection.close();

        return 1;
    }