Spring数据库开发

Spring的数据库开发

 

#Spring中JDBC模板的作用

  JDBC模板负责数据库资源管理和错误处理;

 

#熟悉Spring  JDBC的配置

    配置数据源和jdbc模板  

<?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">    <!-- 1配置数据源 -->    <bean id="dataSource" class= "org.springframework.jdbc.datasource.DriverManagerDataSource">        <!--数据库驱动 -->        <property name="driverClassName" value="com.mysql.jdbc.Driver" />        <!--连接数据库的url -->        <property name="url" value="jdbc:mysql://localhost:3306/spring" />        <!--连接数据库的用户名 -->        <property name="username" value="root" />        <!--连接数据库的密码 -->        <property name="password" value="root" />    </bean>    <!-- 2配置JDBC模板 -->    <bean id="jdbcTemplate"          class="org.springframework.jdbc.core.JdbcTemplate">        <!-- 默认必须使用数据源 -->        <property name="dataSource" ref="dataSource" />    </bean>        <!--定义id为accountDao的Bean-->    <bean id="accountDao" class="com.sjl.jdbc.AccountDaoImpl">        <!-- 将jdbcTemplate注入到accountDao实例中 -->        <property name="jdbcTemplate" ref="jdbcTemplate" />    </bean>    </beans>

 

# jdbcTemplate 类中几个常用方法

 

update()方法可以完成插入、更新、删除,并返回受影响的行数;

int update(String sql)                                               sql语句

int update(String sql,PreparedStatementSetter pss)     sql语句,预编译参数

int update(String sql,Object...args)                             sql语句,可变长参数

 

query()方法

List query(String sql,RowMapper rowMapper)                             sql语句,数据库中的记录,java实体的映射的对象

queryForObject(String sql,RowMapper rowMapper,Object...args)   sql语句,数据库中的记录,java实体的映射的对象,变长参数

 

 

说明:除了导入Spring的基本包,还要导入connector(驱动连接包),jdbc(连接)和ex(事务)包;

 #Account.java(实体类)
1
package com.sjl.jdbc; 2 public class Account { 3     private Integer id; // 账户id 4     private String username; // 用户名 5     private Double balance; // 账户余额 6     public Integer getId() { 7         return id; 8     } 9     public void setId(Integer id) {10         this.id = id;11     }12     public String getUsername() {13         return username;14     }15     public void setUsername(String username) {16         this.username = username;17     }18     public Double getBalance() {19         return balance;20     }21     public void setBalance(Double balance) {22         this.balance = balance;23     }24     public String toString() {25         return "Account [id=" + id + ", "26                 + "username=" + username + 27                 ", balance=" + balance + "]";28     }29 }

 

#接口的方法的实现

package
com.sjl.jdbc;import java.util.List;public interface AccountDao {    // 添加    public int addAccount(Account account);    // 更新    public int updateAccount(Account account);    // 删除    public int deleteAccount(int id);        // 通过id查询    public Account findAccountById(int id);    // 查询所有账户    public List<Account> findAllAccount();}
#接口的实现类
package com.sjl.jdbc;import java.util.List;import org.springframework.jdbc.core.BeanPropertyRowMapper;import org.springframework.jdbc.core.JdbcTemplate;import org.springframework.jdbc.core.RowMapper;public class AccountDaoImpl implements AccountDao {    // 声明JdbcTemplate属性及其setter方法    private JdbcTemplate jdbcTemplate;    public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {        this.jdbcTemplate = jdbcTemplate;    } // 添加账户    public int addAccount(Account account) {        // 定义SQL        String sql = "insert into account(username,balance) value(?,?)";        // 定义数组来存放SQL语句中的参数        Object[] obj = new Object[] { account.getUsername(), account.getBalance() };        // 执行添加操作,返回的是受SQL语句影响的记录条数        int num = this.jdbcTemplate.update(sql, obj);        return num;    }    // 更新账户    public int updateAccount(Account account) {        // 定义SQL        String sql = "update account set username=?,balance=? where id = ?";        // 定义数组来存放SQL语句中的参数        Object[] params = new Object[] { account.getUsername(), account.getBalance(), account.getId() };        // 执行添加操作,返回的是受SQL语句影响的记录条数        int num = this.jdbcTemplate.update(sql, params);        return num;    }    // 删除账户    public int deleteAccount(int id) {        // 定义SQL        String sql = "delete from account where id = ? ";        // 执行添加操作,返回的是受SQL语句影响的记录条数        int num = this.jdbcTemplate.update(sql, id);        return num;    }        // 通过id查询账户数据信息    public Account findAccountById(int id) {     //定义SQL语句     String sql = "select * from account where id = ?";     // 创建一个新的BeanPropertyRowMapper对象     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";     // 创建一个新的BeanPropertyRowMapper对象     RowMapper<Account> rowMapper =     new BeanPropertyRowMapper<Account>(Account.class);     // 执行静态的SQL查询,并通过RowMapper返回结果     return this.jdbcTemplate.query(sql, rowMapper);    }}

#测试类
package com.sjl.jdbc;import java.util.List;import org.junit.Test;import org.springframework.context.ApplicationContext;import org.springframework.context.support.ClassPathXmlApplicationContext;import org.springframework.jdbc.core.JdbcTemplate;public class JdbcTemplateTest {    /**      * 使用execute()方法建表      *///  public static void main(String[] args) {//      // 加载配置文件//      ApplicationContext applicationContext = //       new ClassPathXmlApplicationContext("applicationContext.xml");//      // 获取JdbcTemplate实例//      JdbcTemplate jdTemplate = //           (JdbcTemplate) applicationContext.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 mainTest() {     // 加载配置文件     ApplicationContext applicationContext =      new ClassPathXmlApplicationContext("applicationContext.xml");     // 获取JdbcTemplate实例     JdbcTemplate jdTemplate =      (JdbcTemplate) applicationContext.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() {     // 加载配置文件     ApplicationContext applicationContext =      new ClassPathXmlApplicationContext("applicationContext.xml");     // 获取AccountDao实例     AccountDao accountDao =      (AccountDao) applicationContext.getBean("accountDao");     // 创建Account对象,并向Account对象中添加数据     Account account = new Account();     account.setUsername("tom");     account.setBalance(1000.00);     // 执行addAccount()方法,并获取返回结果     int num = accountDao.addAccount(account);     if (num > 0) {     System.out.println("成功插入了" + num + "条数据!");     } else {     System.out.println("插入操作执行失败!");     }    }        @Test    public void updateAccountTest() {     // 加载配置文件     ApplicationContext applicationContext =      new ClassPathXmlApplicationContext("applicationContext.xml");     // 获取AccountDao实例     AccountDao accountDao =      (AccountDao) applicationContext.getBean("accountDao");     // 创建Account对象,并向Account对象中添加数据     Account account = new Account();     account.setId(1);     account.setUsername("tom");     account.setBalance(2000.00);     // 执行updateAccount()方法,并获取返回结果     int num = accountDao.updateAccount(account);     if (num > 0) {     System.out.println("成功修改了" + num + "条数据!");     } else {     System.out.println("修改操作执行失败!");     }    }        @Test    public void deleteAccountTest() {     // 加载配置文件     ApplicationContext applicationContext =      new ClassPathXmlApplicationContext("applicationContext.xml");     // 获取AccountDao实例     AccountDao accountDao =      (AccountDao) applicationContext.getBean("accountDao");     // 执行deleteAccount()方法,并获取返回结果     int num = accountDao.deleteAccount(1);     if (num > 0) {     System.out.println("成功删除了" + num + "条数据!");     } else {     System.out.println("删除操作执行失败!");     }    }    @Test    public void findAccountByIdTest() {     // 加载配置文件     ApplicationContext applicationContext =      new ClassPathXmlApplicationContext("applicationContext.xml");     // 获取AccountDao实例     AccountDao accountDao =      (AccountDao) applicationContext.getBean("accountDao");     // 执行findAccountById()方法     Account account = accountDao.findAccountById(1);     System.out.println(account);    }    @Test    public void findAllAccountTest() {     // 加载配置文件     ApplicationContext applicationContext =      new ClassPathXmlApplicationContext("applicationContext.xml");     // 获取AccountDao实例     AccountDao accountDao =      (AccountDao) applicationContext.getBean("accountDao");     // 执行findAllAccount()方法,获取Account对象的集合     List<Account> account = accountDao.findAllAccount();     // 循环输出集合中的对象     for (Account act : account) {     System.out.println(act);     }    }}

 

 

 

 

 

 

 

 

 

 

 

相关文章