JdbcTemplate CRUD

一）添加依赖 POM.xml

    <build>
        <plugins>
            <plugin>
                <groupId>org.apache.maven.plugins</groupId>
                <artifactId>maven-compiler-plugin</artifactId>
                <version>3.8.1</version>
                <configuration>
                    <target>1.8</target>
                    <source>1.8</source>
                    <encoding>utf-8</encoding>
                </configuration>
            </plugin>
        </plugins>
    </build>
    <dependencies>
        <!--測試包-->
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.12</version>
        </dependency>
        <!--sql server 驅動包-->
        <dependency>
            <groupId>com.microsoft.sqlserver</groupId>
            <artifactId>mssql-jdbc</artifactId>
            <version>7.2.2.jre8</version>
        </dependency>
        <!--Spring 框架容器包-->
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-context</artifactId>
            <version>5.2.0.RELEASE</version>
        </dependency>
        <!--Spring 自帶的測試-->
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-test</artifactId>
            <version>5.2.0.RELEASE</version>
        </dependency>
        <!--JdbcTemplate 包-->
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-jdbc</artifactId>
            <version>5.2.0.RELEASE</version>
        </dependency>
    </dependencies>

二）配置数据库配置文件 db.properties

jdbc.driver=com.microsoft.sqlserver.jdbc.SQLServerDriver
jdbc.url=jdbc:sqlserver://localhost:1433;DatabaseName=testdb
jdbc.username=sa
jdbc.password=123.abc

三）配置Spring配置文件 applicationContext.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"
       xsi:schemaLocation="http://www.springframework.org/schema/beans
       http://www.springframework.org/schema/beans/spring-beans.xsd
       http://www.springframework.org/schema/context
       https://www.springframework.org/schema/context/spring-context.xsd">

    <!--加載數據庫配置文件，讀取文件中數據-->
    <context:property-placeholder location="classpath:db.properties"></context:property-placeholder>

    <!--開啟自動掃描-->
    <context:component-scan base-package="com.company"/>

    <!--配置DriverManagerDataSource Bean，并注入屬性值-->
    <bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
        <property name="driverClassName" value="${jdbc.driver}"></property>
        <property name="url" value="${jdbc.url}"></property>
        <property name="username" value="${jdbc.username}"></property>
        <property name="password" value="${jdbc.password}"></property>
    </bean>

    <!--配置JdbcTemplate Bean，并注入dataSource-->
    <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
        <constructor-arg name="dataSource" ref="dataSource"></constructor-arg>
    </bean>
</beans>

四）逻辑代码

pojo目录下Account.java

public class Account {
    private int id;
    private String name;
    private float money;

    @Override
    public String toString() {
        return "Account{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", money=" + money +
                '}';
    }

    public int getId() {
        return id;
    }

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

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public float getMoney() {
        return money;
    }

    public void setMoney(float money) {
        this.money = money;
    }
}

dao目录下

AccountDao.java

public interface AccountDao {
    void saveAccount(Account account) throws SQLException;
    void updateAccount(Account account) throws SQLException;
    void deleteAccountById(int id) throws SQLException;
    Account queryAccountById(int id) throws SQLException;
    List<Account> queryAccountList() throws SQLException;
}

AccountDaoImpl.java

@Repository("accountDao")
public class AccountDaoImpl implements AccountDao {

    @Autowired
    @Qualifier("jdbcTemplate")
    private JdbcTemplate jdbcTemplate;

    @Override
    public void saveAccount(Account account) throws SQLException {
        String sql="insert into account(name,money) values(?,?)";
        Object[] params={account.getName(),account.getMoney()};
        jdbcTemplate.update(sql,params);
    }

    @Override
    public void updateAccount(Account account) throws SQLException {
         String sql="update account set name=? , money=? where id=?";
         Object[] params={account.getName(),account.getMoney(),account.getId()};
         jdbcTemplate.update(sql,params);
    }

    @Override
    public void deleteAccountById(int id) throws SQLException {
         String sql="delete from account where id=?";
         jdbcTemplate.update(sql,id);
    }

    @Override
    public Account queryAccountById(int id) throws SQLException {
        String sql="select * from account where id=?";
        Account account = jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<>(Account.class), id);
        return account;
    }

    @Override
    public List<Account> queryAccountList() throws SQLException {
        String sql="select * from account";
        List<Account> accountList = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(Account.class));
        return accountList;
    }
}

service目录下

AccountService.java

public interface AccountService {
    void saveAccount(Account account);
    void updateAccount(Account account);
    void deleteAccountById(int id);
    Account queryAccountById(int id);
    List<Account> queryAccountList();
}

AccountServiceImpl.java

@Service("accountService")
public class AccountServiceImpl implements AccountService {

    @Autowired
    @Qualifier("accountDao")
    private AccountDao accountDao;

    @Override
    public void saveAccount(Account account) {
        try {
            accountDao.saveAccount(account);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    @Override
    public void updateAccount(Account account) {
        try {
            accountDao.updateAccount(account);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    @Override
    public void deleteAccountById(int id) {
        try {
            accountDao.deleteAccountById(id);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    @Override
    public Account queryAccountById(int id) {
        Account account = null;
        try {
            account = accountDao.queryAccountById(id);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return account;
    }

    @Override
    public List<Account> queryAccountList() {
        List<Account> accountList = null;
        try {
            accountList = accountDao.queryAccountList();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return accountList;
    }
}

五）测试

@RunWith(SpringJUnit4ClassRunner.class)
@ComponentScan("com.company")
@ContextConfiguration(locations = {"classpath:applicationContext.xml"})
public class JdbcTemplateTest {

    @Autowired
    @Qualifier("accountService")
    private AccountService accountService;

    @Test
    public void saveAccount(){
        Account account = new Account();
        account.setName("august");
        account.setMoney(8000f);
        accountService.saveAccount(account);
    }

    @Test
    public void updateAccount(){
        Account account = new Account();
        account.setId(20);
        account.setName("september");
        account.setMoney(9000f);
        accountService.updateAccount(account);
    }

    @Test
    public void deleteAccountById(){
        accountService.deleteAccountById(20);
    }

    @Test
    public void queryAccountById(){
        Account account = accountService.queryAccountById(18);
        System.out.println(account);
    }

    @Test
    public void queryAccountList(){
        List<Account> accountList = accountService.queryAccountList();
        accountList.forEach(System.out::println);
    }
}