Spring 对jdbc的增删改查

**

Spring 连接 jdbc xml配置一

**

Spring配置Jdbc的增删改查

建立一个工程，命名为SpringTest,建立jdbc包，新建一个Account类，AccountDao接口以及AccountDaoImpl实现类和测试类JdbcTemplateTest,

Account.java

生成类的Get方法与set方法

package trxy.yd.jdbc;


public class Account {
    private Integer id;
    private String username;
    private Double balance;

    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 Double getBalance() {
        return balance;
    }

    public void setBalance(Double balance) {
        this.balance = balance;
    }

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

AccountDao.java

package trxy.yd.jdbc;
import java.util.List;
public interface AccountDao {
    //添加
    public int addAccount(Account account);
    //更新
    public int updataAccount(Account account);
    //删除
    public int deleteAccount(int id);
    //查询
    public Account findAccountById(int id);
    public List<Account> findAllAccount();
}

AccountDaoImpl.java

package trxy.yd.jdbc;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import java.util.List;
public class AccountDaoImpl implements AccountDao {
    private JdbcTemplate jdbcTemplate;
    public void setJdbcTemplate(JdbcTemplate jdbcTemplate)
    {
        this.jdbcTemplate=jdbcTemplate;
    }
    public int addAccount(Account account) {
        //定义SQL
        String sql = "insert into account(username,balance)values(?,?)";
        //定义数组来存储SQL语句中的参数
        Object[] params = new Object[]{
                account.getUsername(),
                account.getBalance(),
        };
        int num = this.jdbcTemplate.update(sql,params);
        return num;
    }
    /**
     * 数据更新操作
     * */
    public int updataAccount(Account account) {
        //定义SQL
        String sql = "update account set username=?,balance=? where id=?";
        Object[] pms = new Object[] {
                account.getUsername(),
                account.getBalance(),
                account.getId()
        };
            //执行更新操作，返回记录受SQL语句影响的记录条数
        return this.jdbcTemplate.update(sql,pms);
    }
    /**
     * 删除操作
     * */
    @Override
    public int deleteAccount(int id) {
        String sql = "delete from account where id =?";
        return this.jdbcTemplate.update(sql,id);

    }
    public Account findAccountById(int id){
        //定义SQL语句
        String sql="select * from account where id = ?";
        //创建一个新的BeanPropertyPowMapper对象
        RowMapper<Account> rowMapper=new BeanPropertyRowMapper<Account>(Account.class);
        //讲Id绑定到SQL语句中，并通过RowMapper返回一个Object类型的单行记录
        return this.jdbcTemplate.queryForObject(sql,rowMapper,id);
    }
    public List<Account> findAllAccount(){
        //定义SQL语句
        String sql="select *from account";
        //创建一个新的BeanProperYRowMapper对象
        RowMapper<Account> rowMapper= new BeanPropertyRowMapper<Account>(Account.class);
        //执行静态的SQL查询，并通过RowMapper返回结果
        return this.jdbcTemplate.query(sql,rowMapper);
    }
}

JdbcTemplateTest.java

package trxy.yd.jdbc;

import org.junit.Test;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.SqlOutParameter;

import java.util.List;

public class JdbcTemplateTest {
    /**
     * 使用execute()方法建表
     * */
    public static void main(String[] args){
        //加载配置文件
        ApplicationContext ac = new ClassPathXmlApplicationContext("spring_jdbc.xml");
        //获取JdbcTemplate实例
        JdbcTemplate jdTemplate =(JdbcTemplate) ac.getBean("jdbcTemplate");
        //使用execute()方法执行SQL语句，创建用户账户管理account
        jdTemplate.execute("create table account("+"id int primary key auto_increment,"+
                "username varchar(50)," + "balance double)");
        System.out.println("账户表account创建成功！");
    }
    @Test
    public void addAccountTest(){

        //1.加载配置文件
        ApplicationContext ac = new ClassPathXmlApplicationContext("spring_jdbc.xml");
        //获取AccountDao实例
        AccountDao accountDao = (AccountDao) ac.getBean("accountDao");
        //2.创建Account对象，并向Account对象中添加数据
        Account account = new Account();
        account.setUsername("孙孙12");
        account.setBalance(1111.00);
        //account.getId();
       //执行addAccount()方法，并获取返回结果
        int num = accountDao.addAccount(account);
        if(num >0){
            System.out.println("成功的插入了"+num+"条数据！");
        }else
        {
            System.out.println("插入操作执行失败！");
        }
    }

        @Test
        public void updateAccountTest() {
            ApplicationContext ac = new ClassPathXmlApplicationContext("spring_jdbc.xml");
            //获取AccountDao实例
            AccountDao accountDao = (AccountDao) ac.getBean("accountDao");
            //2.创建Account对象，并向Account对象中添加数据
            Account account = new Account();
            account.setId(2);
            account.setUsername("Tom");
            account.setBalance(33.00);
            int num = accountDao.updataAccount(account);
            if(num >0){
                System.out.println("成功修改了"+num+"条数据！");
            }else
            {
                System.out.println("修改操作执行失败！");
            }
    }

    /**
     * 数据删除操作
     * */
    @Test
    public void deletAccountTest(){
        ApplicationContext ac = new ClassPathXmlApplicationContext("spring_jdbc.xml");
        //获取AccountDao实例
        AccountDao accountDao = (AccountDao) ac.getBean("accountDao");
        //2.创建Account对象，并向Account对象中添加数据
        Account account = new Account();
        int num = accountDao.deleteAccount(1);
        if(num>=1){
            System.out.println("成功删除了"+num+"条记录！");
        }else{
            System.out.println("删除操作失败！");
        }
    }

    /*
    * 查询操作
    * */
    @Test
    public void findAccountByIdTest()

    {
        ApplicationContext ac = new ClassPathXmlApplicationContext("spring_jdbc.xml");
        //获取AccountDao实例
        AccountDao accountDao = (AccountDao) ac.getBean("accountDao");
        //执行findAccountById()方法
        Account account = accountDao.findAccountById(1);
        System.out.println(account);
    }


    /*
     * 查询所有数据
     **/
    @Test
    public void findAllAccount() {
        ApplicationContext ac = new ClassPathXmlApplicationContext("spring_jdbc.xml");
        //获取AccountDao实例
        AccountDao accountDao = (AccountDao) ac.getBean("accountDao");
        //执行findAccountById()方法
        List<Account> account = accountDao.findAllAccount();
        for (Account act : account) {
            System.out.println(act);
        }
    }



}

spring_jdbc.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"

	   xsi:schemaLocation="http://www.springframework.org/schema/beans
	http://www.springframework.org/schema/beans/spring-beans-4.3.xsd
	http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context.xsd">
	<!--1 配置数据源  -->
	<bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
	<!-- 数据库驱动  -->
	<property name="driverClassName" value="com.mysql.cj.jdbc.Driver"/>
	<!-- 连接数据库的URL  -->
	<property name="url" value="jdbc:mysql://localhost/spring?serverTimezone=UTC 	"/>
	<!--连接数据的用户名-->
	<property name="username" value="root"/>
	<!-- 连接数据的密码 -->
	<property name="password" value="123456"/>
	</bean>
	<!--2 配置JDBC模板 -->
<bean id="jdbcTemplate"
	  class="org.springframework.jdbc.core.JdbcTemplate">
	<!-- 默认必须使用数据源 -->
<property name="dataSource" ref="dataSource" />


</bean>
	<!-- 2配置JDBC模板 -->
<!--	<bean id="jdbcTemplate"-->
<!--		  class="org.springframework.jdbc.core.JdbcTemplate">-->
<!--		&lt;!&ndash; 默认必须使用数据源 &ndash;&gt;-->
<!--		<property name="dataSource" ref="dataSource" />-->
<!--	</bean>-->


<bean id="accountDao" class="trxy.yd.jdbc.AccountDaoImpl">
	<property name="jdbcTemplate" ref="jdbcTemplate"/>
</bean>


<!--	<context:component-scan-->
<!--			base-package="trxy.yd.jdbc" />-->

</beans>

运行结果