Spring-jdbc

Spring-jdbc

1、springJdbc步骤

1. 配置jdbc驱动， 配置JDBC模板
2. 创建实体类
3. 需要实现的方法，定义JdbcTemplate
4. 在配置文件中，配置数据源，配置jdbcTemplate配置bean
5. 测试类

2、代码实现

pojo

// 使用locbok
@Data
public class Account { 
   private int id;
   private String username;
   private double balance;
}

dao接口及实现类

public interface AccountDao {
    public void save(Account account);
    public void update(Account account);
    public void delete(int id);
    public Account findAccountById(int id);
    public List<Account> findAccountAll();
}



public class AccountDaoImpl implements AccountDao {

    private JdbcTemplate jdbcTemplate;

    public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
    }

    @Override
    public void save(Account account) {
        String sql = "insert into account(id,username,balance) values(?,?,?)";
        Object[] obj = new Object[]{
                account.getId(),
                account.getUsername(),
                account.getBalance()
        };
        jdbcTemplate.update(sql, obj);
    }

    @Override
    public void update(Account account) {
        String sql = "update account set username = ?,balance = ? where id = ?";
        Object[] obj = new Object[]{
                account.getUsername(),
                account.getBalance(),
                account.getId()
        };
        jdbcTemplate.update(sql, obj);
    }

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

    @Override
    public Account findAccountById(int id) {
        String sql = "select * from account where id = ?";
        // 创建一个新的rowMaapper对象   (RowMapper结果集映射：将表中的数据映射到 实体类型中)
        RowMapper<Account> rowMapper = new BeanPropertyRowMapper<Account>(Account.class);
        // 将id绑定到sql语句中，queryForObject：通过RowMapper返回一个Object类型的单行记录
        Account account = jdbcTemplate.queryForObject(sql, rowMapper, id);
        return account;
    }

    @Override
    public List<Account> findAccountAll() {
        String sql = "select * from account";
        RowMapper<Account> rowMapper = new BeanPropertyRowMapper<Account>(Account.class);
        // 执行静态Sql查询，通过RowMapper返回结果
        List list = jdbcTemplate.query(sql, rowMapper);
        return list;
    }
}
/*
创建一个新的rowMaapper对象   (RowMapper结果集映射：将表中的数据映射到 实体类型中)
RowMapper<Account> rowMapper = new BeanPropertyRowMapper<>(Account.class);
queryForObject：返回单条数据
query：返回多条数据
*/

配置application.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:tx="http://www.springframework.org/schema/tx"
       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/tx
            http://www.springframework.org/schema/tx/spring-tx.xsd
            http://www.springframework.org/schema/aop
            https://www.springframework.org/schema/aop/spring-aop.xsd">

    <!-- 1、配置数据源 -->
    <bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
        <!-- 数据库驱动 -->
        <property name="driverClassName" value="com.mysql.jdbc.Driver"></property>
        <!-- 连接数据库的url -->
        <property name="url" value="jdbc:mysql://localhost:3306/spring"></property>
        <!-- 连接数据库的用户名 -->
        <property name="username" value="root"></property>
        <!-- 连接数据库的密码 -->
        <property name="password" value=""></property>
    </bean>

    <!--数据源注入到jdbcTemplate对象中-->
    <!-- 配置JDBC模板jdbcTemplate --><!-- 在core中核心 -->
    <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
        <!-- 默认必须使用数据源 -->
        <property name="dataSource" ref="dataSource"></property>
    </bean>

    <!--jdbcTemplate注入到普通类中-->
    <bean id="accountDao" class="com.k2.jdbc.dao.Impl.AccountDaoImpl">
        <property name="jdbcTemplate" ref="jdbcTemplate"></property>
    </bean>

    <!-- jdbc_事务管理器，id必须是transactionManager，依赖于数据源 -->
    <bean id="transactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
        <property name="dataSource" ref="dataSource"></property>
    </bean>

    <!--xml事务配置方式-->
    <!--编写通知：对事务进行增强(通知)，需要编写对切入点和具体执行事务细节 (参考AOP)-->
    <tx:advice id="txAdvice" transaction-manager="transactionManager">
        <tx:attributes>
            <tx:method name="*" propagation="REQUIRED" isolation="DEFAULT" read-only="false" timeout="-1"/>
        </tx:attributes>
    </tx:advice>
    <!--编写aop让spring自动对目标生成代理，需要使用AspectJ的表达式      (事务的AOP)-->
    <!--配置事务切入，在哪织入进去-->
    <aop:config>
        <aop:pointcut id="pointcut" expression="execution(* com.k2.jdbc_事务管理tx.xml.*.*(..))"/>
        <aop:advisor advice-ref="txAdvice" pointcut-ref="pointcut"></aop:advisor>
    </aop:config>

</beans>

测试

public class AccountTest {
    @Test
    public void save(){
        ApplicationContext applicationContext = new ClassPathXmlApplicationContext("applicationContextJdbc.xml");
        AccountDao accountDao = (AccountDao) applicationContext.getBean("accountDao");
        Account acc = new Account();
        acc.setId(1);
        acc.setUsername("张三");
        acc.setBalance(1000.56);
        accountDao.save(acc);
    }

    @Test
    public void update(){
        ApplicationContext applicationContext = new ClassPathXmlApplicationContext("applicationContextJdbc.xml");
        AccountDao accountDao = (AccountDao) applicationContext.getBean("accountDao");
        Account acc = new Account();
        acc.setId(1);
        acc.setUsername("zhangsan");
        acc.setBalance(10086.55);
        accountDao.update(acc);
    }

    @Test
    public void delete(){
        ApplicationContext applicationContext = new ClassPathXmlApplicationContext("applicationContextJdbc.xml");
        AccountDao accountDao = (AccountDao) applicationContext.getBean("accountDao");
        accountDao.delete(1);
    }

    @Test
    public void findAccountById(){
        ApplicationContext applicationContext = new ClassPathXmlApplicationContext("applicationContextJdbc.xml");
        AccountDao accountDao = (AccountDao) applicationContext.getBean("accountDao");
        Account account = accountDao.findAccountById(1);
        System.out.println(account);
    }

    @Test
    public void findAccountAll(){
        ApplicationContext applicationContext = new ClassPathXmlApplicationContext("applicationContextJdbc.xml");
        AccountDao accountDao = (AccountDao) applicationContext.getBean("accountDao");
        List<Account> list = accountDao.findAccountAll();
        for (Account account : list) {
            System.out.println(account);
        }
    }
}