JDBC在Spring中的应用
程序员文章站
2022-05-24 12:28:31
...
JDBC在Spring框架中的使用方法如下:
1.在applicationContext.xml中对数据源和JDBC模版配置如下:
<!-- 配置数据源 -->
<bean id="dataSource"
class="org.springframework.jdbc.datasource.DriverManagerDataSource">
<property name="driverClassName" value="com.mysql.jdbc.Driver"/>
<property name="url" value="jdbc:mysql://localhost:3306/dangdang"/>
<property name="username" value="root"/>
<property name="password" value="root"/>
</bean>
<!-- JDBC模版-->
<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
<property name="dataSource" ref="dataSource"/>
</bean>
<bean id="accountDao" class="com.itheima.jdbc.AccountDaoImpl">
<property name="jdbcTemplate" ref="jdbcTemplate"/>
</bean>
代码中定义了三个Bean,dataSource,jdbcTemplate,和需要注入类的bean.
2.JDBCTemplate中有大量的对数据库更新查询的操作
(1)execute()方法,下面代码用JDBCTemplate的execute()方法创建了account表.前提是applicationContext.xml已经配置好.
public class JdbcTemplateTest {
public static void main(String[] args) {
//加载配置文件
ApplicationContext application =
new ClassPathXmlApplicationContext("applicationContext.xml");
//获取jdbcTemplate对象
JdbcTemplate jdbcTemplate = (JdbcTemplate) application.getBean("jdbcTemplate");
//用execute()执行sql语句,创建表account
jdbcTemplate.execute("create table account(" +
"id int PRIMARY KEY auto_increment," +
"username VARCHAR (40))" +
"balance double)");
}
}
(2)update()方法,update()方法可以完成插入、更新和删除数据的操作。在JdbcTemplate类中,提供了一系列的update()方法,其常用方法下表所示:
以下是用update()方法对数据库操作的小例子:
1>先创建一个Account类(同时在数据库中创建好表与之对应)
package com.itheima.jdbc;
public class Account {
private Integer id;
private String username;
private Double balance;
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;
}
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
@Override
public String toString() {
return "Account{" +
"id=" + id +
", username='" + username + '\'' +
", balance=" + balance +
'}';
}
}
2>再创建AccountDao以及实现类AccountDaoImplpackage com.itheima.jdbc;
import java.util.List;
public interface AccountDao {
public int addAccount(Account account);
public int updateAccount(Account account);
public int deleteAccount(int id);
}
package com.itheima.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;
}
@Override
public int addAccount(Account account) {
String sql = "insert into account(username,balance) values(?,?)";
Object[] objects = new Object[]{
account.getUsername(),
account.getBalance()
};
int num = this.jdbcTemplate.update(sql, objects);
return num;
}
@Override
public int updateAccount(Account account) {
String sql = "update account set username=?,balance=? where id=?";
Object[] params = new Object[]{
account.getUsername(),
account.getBalance(),
account.getId()
};
int num = this.jdbcTemplate.update(sql, params);
return num;
}
@Override
public int deleteAccount(int id) {
String sql = "delete from account where id=?";
int num = this.jdbcTemplate.update(sql, id);
return num;
}
}
3>xml中需要配置accountDao,将jdbcTemplate注入其中.<bean id="accountDao" class="com.itheima.jdbc.AccountDaoImpl">
<property name="jdbcTemplate" ref="jdbcTemplate"/>
</bean>
4>编写测试类, 此例子为添加的代码,删除,修改同理 ApplicationContext application =
new ClassPathXmlApplicationContext("applicationContext.xml");
AccountDao accountDao = (AccountDao) application.getBean("accountDao");
Account account = new Account();
account.setUsername("tom");
account.setBalance(1000.00);
int num = accountDao.addAccount(account);
3>query()方法 JdbcTemplate类中还提供了大量的query()方法来处理各种对数据库表的查询操作。其中,常用的几个query()方法如下表所示:
以下是用query()方法对数据库操作的小例子:
1>在AccountDao和 AccountImpl中添加以下代码
public Account findAccountById(int id);
public List<Account> findAllCount();
@Override
public Account findAccountById(int id) {
String sql = "select * from account where id =?";
RowMapper<Account> rowMapper = new
BeanPropertyRowMapper<Account>(Account.class);
return this.jdbcTemplate.queryForObject(sql,rowMapper,id);
}
@Override
public List<Account> findAllCount() {
String sql = "select * from account";
RowMapper<Account> rowMapper = new
BeanPropertyRowMapper<Account>(Account.class);
return this.jdbcTemplate.query(sql,rowMapper);
}
2> 测试类
ApplicationContext application =
new ClassPathXmlApplicationContext("applicationContext.xml");
AccountDao accountDao = (AccountDao) application.getBean("accountDao");
List<Account> accountList=accountDao.findAllCount();
for (Account act : accountList) {
System.out.println(act);
}
}
上面是用query()查询所有记录的测试,另一个方法findAccountById()同理.
上面代码中的RowMapper可以自动的将数据库中的表的数据映射到用户自定义的类(Account)中.