JdbcTemplate基礎

JdbcTemplate

**spring 提供用於操作JDBC工具類，類似：DBUtils**
**依賴連線池DataSource（資料來源）**
複製程式碼

建立表

       create database spring_day01;

       use spring_day01;

       create table s_user(
        id int primary key auto_increment,
        username varchar(50),
        password varchar(32)
       );

       insert into s_user(username,password) values('jack','1234');
       insert into s_user(username,password) values('rose','5678');
複製程式碼

javabean

​

package com.adolph.domain;

public class User {
    /**
     * create table s_user(
     *     id int primary key auto_increment,
     *     username varchar(50),
     *     password varchar(32)
     * );
     */
    private Integer id;

    private String username;

    private String password;

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getUsername() {
        return username;
    }

    public void setUsername(String username) {
        this.username = username;
    }

    public String getPassword() {
        return password;
    }

    public void setPassword(String password) {
        this.password = password;
    }

    @Override
    public String toString() {
        return "User{" +
                "id=" + id +
                ", username='" + username + '\'' +
                ", password='" + password + '\'' +
                '}';
    }
}
複製程式碼

使用API

​

public static void main(String[] args) {

    //建立資料來源（連線池）dbcp
    BasicDataSource dataSource = new BasicDataSource();
    //基本四項
    dataSource.setDriverClassName("com.mysql.jdbc.Driver");
    dataSource.setUrl("jdbc:mysql://localhost:3306/spring_day01");
    dataSource.setUsername("root");
    dataSource.setPassword("lv1117");

    //建立模板
    JdbcTemplate jdbcTemplate = new JdbcTemplate();
    jdbcTemplate.setDataSource(dataSource);

    //通過api操作
    jdbcTemplate.update("insert into s_user(username,password) values(?,?);","tom","988");
}
複製程式碼

配置DBCP

public class UserDao {

    //jdbc模板將由spring注入
    private JdbcTemplate jdbcTemplate;


    public void update(User user) {
        String sql = "update s_user set username=?,password=? where id =?";
        Object[] args = {user.getUsername(),user.getPassword(),user.getId()};
        jdbcTemplate.update(sql, args);
    }
    
    
    public List<User> findAll(){
        return jdbcTemplate.query("select * from s_user",new BeanPropertyRowMapper<User>(User.class));
    }

    public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
    }
}
複製程式碼

<!--配置dao-->
<bean id="userDaoId" class="com.adolph.dbcp.UserDao">
    <property name="jdbcTemplate" ref="jdbcTemplateId"></property>
</bean>

<!--配置模板-->
<bean id="jdbcTemplateId" class="org.springframework.jdbc.core.JdbcTemplate">
    <property name="dataSource" ref="dataSourceId"></property>
</bean>

<!--配置資料來源-->
<bean id="dataSourceId" class="org.apache.commons.dbcp.BasicDataSource">
    <property name="driverClassName" value="com.mysql.jdbc.Driver"></property>
    <property name="url" value="jdbc:mysql://localhost:3306/spring_day01"></property>
    <property name="username" value="root"></property>
    <property name="password" value="lv1117"></property>
</bean>
複製程式碼

@Test
public void demo(){

    User user = new User();
    user.setId(1);
    user.setUsername("adolph");
    user.setPassword("998");

    String xmlPath = "com/adolph/dbcp/beans.xml";
    ClassPathXmlApplicationContext applicationContext = new ClassPathXmlApplicationContext(xmlPath);
    UserDao userDao = applicationContext.getBean("userDaoId", UserDao.class);
    userDao.update(user);
}
複製程式碼

配置C3P0

<!--配置資料來源-->
<bean id="dataSourceId" class="com.mchange.v2.c3p0.ComboPooledDataSource">
    <property name="driverClass" value="com.mysql.jdbc.Driver"></property>
    <property name="jdbcUrl" value="jdbc:mysql://localhost:3306/spring_day01"></property>
    <property name="user" value="root"></property>
    <property name="password" value="lv1117"></property>
</bean>
複製程式碼

使用JdbcDaoSupport

​ dao繼承JdbcDaoSupport，之後只需要注入資料來源，底層將自動建立模板

public class UserDao extends JdbcDaoSupport {

    public void update(User user) {
        String sql = "update s_user set username=?,password=? where id =?";
        Object[] args = {user.getUsername(), user.getPassword(), user.getId()};
        this.getJdbcTemplate().update(sql, args);
    }

    public List<User> findAll() {
        return this.getJdbcTemplate().query("select * from s_user", new BeanPropertyRowMapper<User>(User.class));
    }

}
複製程式碼

<!--配置dao-->
<bean id="userDaoId" class="com.adolph.dbcp.UserDao">
    <property name="dataSource" ref="dataSourceId"></property>
</bean>

<!--配置資料來源-->
<bean id="dataSourceId" class="com.mchange.v2.c3p0.ComboPooledDataSource">
    <property name="driverClass" value="com.mysql.jdbc.Driver"></property>
    <property name="jdbcUrl" value="jdbc:mysql://localhost:3306/spring_day01"></property>
    <property name="user" value="root"></property>
    <property name="password" value="lv1117"></property>
</bean>
複製程式碼

配置properties

建立jdbcInfo.properties

jdbc.driverClass=com.mysql.jdbc.Driver
jdbc.jdbcUrl=jdbc:mysql://localhost:3306/spring_day01
jdbc.user=root
jdbc.password=lv1117
複製程式碼

<!--載入配置檔案-->
<context:property-placeholder location="classpath:com/adolph/dbcp/jdbcInfo.properties"></context:property-placeholder>
<!--配置資料來源-->
<bean id="dataSourceId" class="com.mchange.v2.c3p0.ComboPooledDataSource">
    <property name="driverClass" value="${jdbc.driverClass}"></property>
    <property name="jdbcUrl" value="${jdbc.jdbcUrl}"></property>
    <property name="user" value="${jdbc.user}"></property>
    <property name="password" value="${jdbc.password}"></property>
</bean>
複製程式碼