Spring JDBCTemplate使用方法

原创

大学里的混子

修改于 2018-10-25 11:24:15

1.1K00

代码可运行

文章被收录于专栏：LeetCodeLeetCode

运行总次数：0

代码可运行

1. Customer 表

 CREATE TABLE `customer` (
  `CUST_ID` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `NAME` varchar(100) NOT NULL,
  `AGE` int(10) unsigned NOT NULL,
  PRIMARY KEY (`CUST_ID`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

2.客户模型

public class Customer {
    int custId;
    String name;
    int age;
    }

3.客户数据处理接口

public interface CustomerDAO {

    public void insert(Customer customer);
    public Customer findByCustomerId(int custId);
    public List<Customer> findAll();
    public int findTotalCustomer();
}

4.客户数据处理接口实现类

public class JdbcCustomerDAO implements CustomerDAO {
    private DataSource dataSource;
    private JdbcTemplate jdbcTemplate;

    public JdbcTemplate getJdbcTemplate() {
        return jdbcTemplate;
    }

    public void setDataSource(DataSource dataSource) {
        this.dataSource = dataSource;
    }

    @Override
    public void insert(Customer customer) {
        String sql = "Insert into customer"+"(CUST_ID, NAME, AGE) VALUES (?, ?, ?)";
        jdbcTemplate = new JdbcTemplate(dataSource);
        jdbcTemplate.update(sql,new Object[]{customer.getCustId(),customer.getName(),customer.age});
    }

    @Override
    public Customer findByCustomerId(int custId) {
        jdbcTemplate = new JdbcTemplate(dataSource);
        String sql = "select * from customer where CUST_ID = ?";
        Customer customer = getJdbcTemplate().queryForObject(sql,new Object[]{custId},new BeanPropertyRowMapper<>(Customer.class));
        return customer;
    }

    @Override
    public List<Customer> findAll() {
         String sql = "select * from customer";
         jdbcTemplate = new JdbcTemplate(dataSource);
         List<Customer> customers = getJdbcTemplate().query(sql,new BeanPropertyRowMapper<>(Customer.class));
         return customers;
    }

    @Override
    public int findTotalCustomer() {
        String sql = "select count(*) from customer";
        jdbcTemplate = new JdbcTemplate(dataSource);
        int total  = getJdbcTemplate().queryForObject(sql,Integer.class);
        return total;
    }
}

说明：

JdbcTemplate类的构造函数的参数DataSource 是数据源，配置所要连接的数据库
连接数据库需要导入包mysql-connector-java-5.1.47.jar
For single insert statements, JdbcTemplate’s `insert method is good. But for multiple inserts, it’s better to use batchUpdate.
Use ? for arguments to avoid SQL injection attacks by instructing JDBC to bind variables.
batchUpdate适合于批量增、删、改操作； update(…)：使用于增、删、改操作； execute（）：执行一个独立的sql语句，包括ddl语句； queryForInt ：查询出一个整数值(旧方法，现在取消了)

下面是xml的配置文件的主要内容。

<bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
    <property name="driverClassName" value="com.mysql.jdbc.Driver"/>
    <property name="password" value="123456"/>
    <property name="username" value="root"/>
    <property name="url" value="jdbc:mysql://localhost:3306/spring4"/>
</bean>

<bean id="customerDao" class="SpringJDBC.JdbcCustomerDAO">
    <property name="dataSource" ref="dataSource"/>
</bean>

<bean id="customer" class="SpringJDBC.Customer" p:name="lvchao" p:age="23" p:custId="201872018"></bean>

5.官方文档的进一步理解

<https://docs.spring.io/spring/docs/5.1.1.RELEASE/spring-framework-reference/data-access.html#jdbc-JdbcTemplate>

5.1 querying（select）

The following query gets the number of rows in a relation:(查找总行数)

int rowCount = this.jdbcTemplate.queryForObject("select count(*) from t_actor", Integer.class);

The following query uses a bind variable:(通过一个变量查找对应的数据的条数)

int countOfActorsNamedJoe = this.jdbcTemplate.queryForObject(
        "select count(*) from t_actor where first_name = ?", Integer.class, "Joe");

The following query looks for a String:（通过一个变量查找对应的数据）

String lastName = this.jdbcTemplate.queryForObject(
        "select last_name from t_actor where id = ?",
        new Object[]{1212L}, String.class);

5.2 Updating (`INSERT`, `UPDATE`, and `DELETE`) with `JdbcTemplate`

You can use the update(..) method to perform insert, update, and delete operations. Parameter values are usually provided as variable argumets or, alternatively, as an object array.

The following example inserts a new entry:

this.jdbcTemplate.update(
        "insert into t_actor (first_name, last_name) values (?, ?)",
        "Leonor", "Watling");

The following example updates an existing entry:

this.jdbcTemplate.update(
        "update t_actor set last_name = ? where id = ?",
        "Banjo", 5276L);

The following example deletes an entry:

this.jdbcTemplate.update(
        "delete from actor where id = ?",
        Long.valueOf(actorId));

5.3 Other `JdbcTemplate` Operations

You can use the execute(..) method to run any arbitrary SQL. Consequently, the method is often used for DDL statements. It is heavily overloaded with variants that take callback interfaces, binding variable arrays, and so on. The following example creates a table:

this.jdbcTemplate.execute("create table mytable (id integer, name varchar(100))");

The following example invokes a stored procedure:

this.jdbcTemplate.update(
        "call SUPPORT.REFRESH_ACTORS_SUMMARY(?)",
        Long.valueOf(unionId))

6. 采用.properties配置jdbc连接

    <context:component-scan base-package="org.springframework.docs.test" />

    <bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
        <property name="driverClassName" value="${jdbc.driverClassName}"/>
        <property name="url" value="${jdbc.url}"/>
        <property name="username" value="${jdbc.username}"/>
        <property name="password" value="${jdbc.password}"/>
    </bean>

    <context:property-placeholder location="jdbc.properties"/>

对应的jdbc.properties文件内容如下：

jdbc.username = root
jdbc.password = 123456
jdbc.url = jdbc:mysql://localhost:3306/spring4
jdbc.driverClassName = com.mysql.jdbc.Driver

7.总结

采用JDBCTemplate可以方便以对象为单元进行操作数据库，不需要写复杂的sql语句。

原创声明：本文系作者授权腾讯云开发者社区发表，未经许可，不得转载。

如有侵权，请联系 cloudcommunity@tencent.com 删除。

数据库

原创声明：本文系作者授权腾讯云开发者社区发表，未经许可，不得转载。

如有侵权，请联系 cloudcommunity@tencent.com 删除。

登录后参与评论

0 条评论

热度