Spring 框架下的 JDBC

Z时代
2024-01-10
分类：综合

本文内容纲要：

- Spring JDBC

Spring JDBC

Spring 对JDBC技术规范做了进一步封装，它又叫Spring JDBCTemplate(jdbc模板技术)

纯JDBC：代码清晰的、效率最高、代码是最烦的、

Spring JDBCTemplate:代码相对来说就不那么清晰，效率要低一点，代码相对简单

如何进行Spring和jdbc的集合？（数据库的连接池）

(1) 导入jar 包：spring-jdbc.jar 驱动类连接池 DruidUtil2(框架的原型)

(2) 配置类：@Configuration

(3) @Spring IOC + JDBC 实现

JdbcConfig： a.配置属性 b.配置DataSource Bean

(4) 配置JdbcTemplate Bean @Bean

1.pom.xml 所需要的jar包

<!-- https://mvnrepository.com/artifact/org.springframework/spring-jdbc -->
    <dependency>
        <groupId>org.springframework</groupId>
        <artifactId>spring-jdbc</artifactId>
        <version>${spring.version}</version>
    </dependency>
<!-- MySQL数据库连接池 -->
        <!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java -->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.48</version>
        </dependency>
        <!-- Druid -->
        <!-- https://mvnrepository.com/artifact/com.alibaba/druid -->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid</artifactId>
            <version>1.1.20</version>
        </dependency>

一个实体类：Account.java
public class Account {
private int id;
private String name;
private Double balance;
public Account( String name, Double balance) {
super();
this.name = name;
this.balance = balance;
}
public Account(int id, String name, Double balance) {
super();
this.id = id;
this.name = name;
this.balance = balance;
}
public Account() {
super();
}
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 Double getBalance() {
return balance;
}
public void setBalance(Double balance) {
this.balance = balance;
}
@Override
public int hashCode() {
final int prime = 31;
int result = 1;
result = prime * result + ((balance == null) ? 0 : balance.hashCode());
result = prime * result + id;
result = prime * result + ((name == null) ? 0 : name.hashCode());
return result;
}
@Override
public boolean equals(Object obj) {
if (this == obj)
return true;
if (obj == null)
return false;
if (getClass() != obj.getClass())
return false;
Account other = (Account) obj;
if (balance == null) {
if (other.balance != null)
return false;
} else if (!balance.equals(other.balance))
return false;
if (id != other.id)
return false;
if (name == null) {
if (other.name != null)
return false;
} else if (!name.equals(other.name))
return false;
return true;
}
@Override
public String toString() {
return "Account [id=" + id + ", name=" + name + ", balance=" + balance + "]";
}
}

接口：IAccount.java
import java.util.List;
/**
- AccountDao接口
- @author 张泽
  */
  public interface IAccountDao {
  List findAll();
  void delete(Account act);
  void saveOrUpdate(Account act);
  }

4.1 接口实现类(JDBC)：AccountDaoJdbcImpl.java

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import javax.sql.DataSource;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;
//@Component
@Repository("accountDaoJdbcImpl") //-- 存储、仓库/  做存储服务
public class AccountDaoJdbcImpl implements IAccountDao {
    @Autowired
    private DataSource ds;
    @Override
    public List<Account> findAll() {
        try {
            Connection con = ds.getConnection();
            String sql = "select * from account";
            Statement stmt = con.createStatement();
            ResultSet rs = stmt.executeQuery(sql);
            List<Account> acts = new ArrayList<>();
            while(rs.next()) {
                Account act = new Account(rs.getInt(1),rs.getString(2),rs.getDouble(3));
                acts.add(act);
            }
            return acts;
        } catch (Exception e) {e.printStackTrace();}
        return null;
    }
    @Override
    public void delete(Account act) {
        // TODO Auto-generated method stub
    }
    @Override
    public void saveOrUpdate(Account act) {
        // TODO Auto-generated method stub
    }
}

4.2 接口实现类(Spring JDBCTemplate)：AccountDaoTemplateImpl.java

import java.util.List;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;
@Repository("accountDaoTemplateImpl")
public class AccountDaoTemplateImpl implements IAccountDao {
    @Autowired
    private JdbcTemplate jdbcTemplate;
    @Override
    public List<Account> findAll() {
        System.out.println("accountDaoTemplateImpl");
        return jdbcTemplate.query(
                "select * from account",
                new BeanPropertyRowMapper<Account>(Account.class)
                );
    }
    @Override
    public void saveOrUpdate(Account act) {
        if(act.getId()==0) {
            jdbcTemplate.update(
                    "insert into account(name,balance) values(?,?)",
                    new Object[] {act.getName(),act.getBalance()}
                    );
        }else {
            jdbcTemplate.update(
                    "update account set name=?,balance=? where id=?",
                    new Object[] {act.getName(),act.getBalance(),act.getId()}
                    );
        }
    }
    @Override
    public void delete(Account act) {
        jdbcTemplate.update(
                "delete from account where id=?",
                new Object[] {act.getId()}
                );
    }
}

5.1 JDBC 配置资源：jdbc.properties

jdbc.driverClass=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3306/demo
jdbc.username=root
jdbc.password=root
pool.maxActive=10

5.2 JDBC 配置类：JdbcConfig.java

import javax.sql.DataSource;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.PropertySource;
import org.springframework.jdbc.core.JdbcTemplate;
import com.alibaba.druid.pool.DruidDataSource;
/**
 * JdbcConfig类
 * @author 张泽
 *
 */
@Configuration
@PropertySource("classpath:jdbc.properties") 
                //--你的配置信息的位置
public class JdbcConfig {
    //-- 1. 获取配置信息
    @Value("${jdbc.driverClass}")
    private String driver;
    @Value("${jdbc.url}")
    private String url;
    @Value("${jdbc.username}")
    private String username;
    @Value("${jdbc.password}")
    private String password;
    @Value("${pool.maxActive}")
    private int maxActive;
    //-- 2. 数据库连接池对象
    @Bean(name="dataSource")
    public DataSource createDataSource() {
        DruidDataSource ds = new DruidDataSource();    
        ds.setDriverClassName(driver);
        ds.setUrl(url);
        ds.setUsername(username);
        ds.setPassword(password);
        ds.setMaxActive(maxActive);
        return ds;
    }
    //-- 3. 配置JdbcTemplate
    @Bean(name="jdbcTemplate")
    public JdbcTemplate createJdbcTemplate(DataSource ds) {
        return new JdbcTemplate(ds);//-- 利用数据源构造JdbcTemplate
    }
}

Spring 配置类：SpringConfig.java
/**
- Spring 配置类
- 多配置的使用方式
  */
  import org.springframework.context.annotation.ComponentScan;
  import org.springframework.context.annotation.Configuration;
  import org.springframework.context.annotation.Import;
@Configuration
@ComponentScan("day")//--所在包名
@Import(JdbcConfig.class) //-- 在主配置中导入子配置
public class SpringConfig {
}

主函数入口：Invoker.java
import javax.sql.DataSource;
import org.springframework.context.ApplicationContext;
import org.springframework.context.annotation.AnnotationConfigApplicationContext;
public class Invoker {
public static void main(String[] args) {
ApplicationContext ctx=
new AnnotationConfigApplicationContext(SpringConfig.class);
DataSource ds = (DataSource)ctx.getBean("dataSource");
System.out.println(ds);
}
}

测试类：TestAccountDao.java
import java.util.List;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;
@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration(classes= {SpringConfig.class})
public class TestAccountDao {
@Autowired
@Qualifier("accountDaoTemplateImpl")//-- 选择要调用接口的实现类
private IAccountDao actDao;
```
 @Test
 public void testFind() {
     List<Account> acts = actDao.findAll();
     for (Account account : acts) {
         System.out.println(account);
     }
 }
 @Test
 public void testSave() {
     actDao.saveOrUpdate(new Account("xx",12.0));    
 }
 @Test
 public void testUpdate() {
     actDao.saveOrUpdate(new Account(8,"xx",22.0));    
 }
 @Test
 public void testDelete() {
     actDao.delete(new Account(8,"xx",22.0));    
 }
```
}