Spring之JDBC操作

 

为什么要使用Spring的JDBC操作

Spring支持JDBC

jar包准备

第一种JDBC配置方式

具体文件组成：

这种方法以注解为主

applicationContext.xml

<?xml version="1.0" encoding="UTF-8"?>

<beans xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"

xmlns="http://www.springframework.org/schema/beans"

xmlns:context="http://www.springframework.org/schema/context"

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/context

http://www.springframework.org/schema/context/spring-context.xsd

http://www.springframework.org/schema/aop

http://www.springframework.org/schema/aop/spring-aop.xsd">

<!--组件注解解析器-->

<context:annotation-config></context:annotation-config>

<!--扫描包-->

<context:component-scan base-package="springjdbc"></context:component-scan>

<!--AOP自动代理-->

<aop:aspectj-autoproxy></aop:aspectj-autoproxy>

<!--引入属性配置文件-->

<context:property-placeholder location="classpath:db.properties"></context:property-placeholder>

<!--配置连接池-->

<bean id="datasource" class="com.alibaba.druid.pool.DruidDataSource" init-method="init" destroy-method="close">

<property name="driverClassName" value="${jdbc.driverClassName}"></property>

<property name="url" value="${jdbc.url}"></property>

<property name="username" value="${jdbc.username}"></property>

<property name="password" value="${jdbc.password}"></property>

<property name="maxActive" value="${jdbc.maxActive}"></property>

</bean>

 

</beans>

 

 

db.properties

#key=value

jdbc.driverClassName=com.mysql.jdbc.Driver

jdbc.url=jdbc:mysql://localhost:3306/springjdbc?rewriteBatchedStatements=true

jdbc.username=root

jdbc.password=123456

jdbc.maxActive=5

 

employee类

package springjdbc.domain;

 

public class employee {

private Long id;

private String username;

private Integer age;

 

public employee() {

}

 

public employee(String username, Integer age) {

this.username = username;

this.age = age;

}

 

public employee(Long id, String username, Integer age) {

this.id = id;

this.username = username;

this.age = age;

}

 

public Long getId() {

return id;

}

 

public void setId(Long id) {

this.id = id;

}

 

public String getUsername() {

return username;

}

 

public void setUsername(String username) {

this.username = username;

}

 

public Integer getAge() {

return age;

}

 

public void setAge(Integer age) {

this.age = age;

}

 

@Override

public String toString() {

return "employee{" +

"id=" + id +

", username='" + username + '\'' +

", age=" + age +

'}';

}

}

 

employeedao接口

package springjdbc.dao;

 

import springjdbc.domain.employee;

 

import java.util.List;

 

public interface employeedao {

//保存方法

void save(employee emp);

//更新方法

void update(employee emp);

//删除方法

void delete(Long id);

//查询一个

employee getone(Long id);

//查询全部

List<employee>list();

}

 

employeedaoimpl实现类

package springjdbc.dao.impl;

 

import org.springframework.beans.factory.annotation.Autowired;

import org.springframework.jdbc.core.JdbcTemplate;

import org.springframework.jdbc.core.RowMapper;

import org.springframework.jdbc.core.support.JdbcDaoSupport;

import org.springframework.stereotype.Repository;

import springjdbc.dao.employeedao;

import springjdbc.domain.employee;

 

import javax.sql.DataSource;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.util.List;

@Repository("dao")

public class employeedaoimpl implements employeedao {

private JdbcTemplate jdbcTemplate;

@Autowired

public void setDataSource(DataSource dataSource) {

this.jdbcTemplate = new JdbcTemplate(dataSource);

}

 

@Override

public void save(employee emp) {

jdbcTemplate.update("INSERT INTO jdbc(username,age)VALUES (?,?)",emp.getUsername(),emp.getAge());

}

 

@Override

public void update(employee emp) {

jdbcTemplate.update("UPDATE jdbc SET username=?,age=? WHERE id=?",emp.getUsername(),emp.getAge(),emp.getId());

}

 

@Override

public void delete(Long id) {

jdbcTemplate.update("DELETE FROM jdbc WHERE id=?",id);

}

 

@Override

public employee getone(Long id) {

//第一种方法：

// List<employee>list=jdbcTemplate.query("SELECT *FROM jdbc WHERE id=?",new RowMapper<employee>(){

//

// @Override

// public employee mapRow(ResultSet resultSet, int i) throws SQLException {

// employee e=new employee();

// e.setId(resultSet.getLong("id"));

// e.setAge(resultSet.getInt("age"));

// e.setUsername(resultSet.getString("username"));

// return e;

// }

// },id);

// return list.size()==1?list.get(0):null;

//第二种方法:这种方法只能处理单行单例，否则会报错：Incorrect column count: expected 1, actual 3

// return jdbcTemplate.queryForObject("SELECT *FROM jdbc WHERE id=?",employee.class,id);

//第三种方法：

return jdbcTemplate.queryForObject("SELECT *FROM jdbc WHERE id=?",new RowMapper<employee>(){

//把一行数据封装成employee对象

//把每一行封装的employee对象存入到List集合中，并返回该集合

@Override

public employee mapRow(ResultSet resultSet, int i) throws SQLException {

employee e=new employee();

e.setId(resultSet.getLong("id"));

e.setAge(resultSet.getInt("age"));

e.setUsername(resultSet.getString("username"));

return e;

}

},id);

}

 

@Override

public List<employee> list() {

return jdbcTemplate.query("SELECT *FROM jdbc",new RowMapper<employee>(){

//把一行数据封装成employee对象

//把每一行封装的employee对象存入到List集合中，并返回该集合

@Override

public employee mapRow(ResultSet resultSet, int i) throws SQLException {

employee e=new employee();

e.setId(resultSet.getLong("id"));

e.setAge(resultSet.getInt("age"));

e.setUsername(resultSet.getString("username"));

return e;

}

});

}

}

 

test测试类

package springjdbc.test;

 

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;

import springjdbc.dao.employeedao;

import springjdbc.domain.employee;

 

import java.util.List;

 

@RunWith(SpringJUnit4ClassRunner.class)

@ContextConfiguration("classpath:applicationContext.xml")

public class test {

@Autowired

@Qualifier("dao")

employeedao dao;

@Test

public void testsave(){

employee emp=new employee();

emp.setUsername("洲洲");

emp.setAge(24);

dao.save(emp);

}

@Test

public void testupdate(){

employee emp=new employee(2L,"小洲",19);

dao.update(emp);

}

@Test

public void testdelete(){

dao.delete(1L);

}

@Test

public void testgetone(){

employee e=dao.getone(2L);

System.out.println(e);

}

@Test

public void testall(){

List<employee>list=dao.list();

for (employee e : list) {

System.out.println(e);

}

}

}

 

 

测试结果如下

 

 

第二种方法采用继承方法，继承extends JdbcDaoSupport类，由于继承了该类后，private JdbcTemplate jdbcTemplate;在底层，所以必须得通过配置文件来实现上述功能，在原有基础稍微进行修改。

具体改动：

applicationContext.xml上添加了配置

<?xml version="1.0" encoding="UTF-8"?>

<beans xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"

xmlns="http://www.springframework.org/schema/beans"

xmlns:context="http://www.springframework.org/schema/context"

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/context

http://www.springframework.org/schema/context/spring-context.xsd

http://www.springframework.org/schema/aop

http://www.springframework.org/schema/aop/spring-aop.xsd">

<!--组件注解解析器-->

<context:annotation-config></context:annotation-config>

<!--扫描包-->

<context:component-scan base-package="springjdbc"></context:component-scan>

<!--AOP自动代理-->

<aop:aspectj-autoproxy></aop:aspectj-autoproxy>

<!--引入属性配置文件-->

<context:property-placeholder location="classpath:db.properties"></context:property-placeholder>

<!--配置连接池-->

<bean id="datasource" class="com.alibaba.druid.pool.DruidDataSource" init-method="init" destroy-method="close">

<property name="driverClassName" value="${jdbc.driverClassName}"></property>

<property name="url" value="${jdbc.url}"></property>

<property name="username" value="${jdbc.username}"></property>

<property name="password" value="${jdbc.password}"></property>

<property name="maxActive" value="${jdbc.maxActive}"></property>

</bean>

<bean id="dao" class="springjdbc.dao.impl.employeedaoimpl">

<property name="dataSource" ref="datasource"></property>

</bean>

</beans>

 

employeedaoimpl实现类上进行继承

1.JdbcTemplate jdbcTemplate在父类的继承方法上，所以直接通过父类获取

package springjdbc.dao.impl;

 

import org.springframework.beans.factory.annotation.Autowired;

import org.springframework.jdbc.core.JdbcTemplate;

import org.springframework.jdbc.core.RowMapper;

import org.springframework.jdbc.core.support.JdbcDaoSupport;

import org.springframework.stereotype.Repository;

import springjdbc.dao.employeedao;

import springjdbc.domain.employee;

 

import javax.sql.DataSource;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.util.List;

 

public class employeedaoimpl extends JdbcDaoSupport implements employeedao {

 

@Override

public void save(employee emp) {

super.getJdbcTemplate().update("INSERT INTO jdbc(username,age)VALUES (?,?)",emp.getUsername(),emp.getAge());

}

 

@Override

public void update(employee emp) {

super.getJdbcTemplate().update("UPDATE jdbc SET username=?,age=? WHERE id=?",emp.getUsername(),emp.getAge(),emp.getId());

}

 

@Override

public void delete(Long id) {

super.getJdbcTemplate().update("DELETE FROM jdbc WHERE id=?",id);

}

 

@Override

public employee getone(Long id) {

//第一种方法：

// List<employee>list=super.getJdbcTemplate().query("SELECT *FROM jdbc WHERE id=?",new RowMapper<employee>(){

//

// @Override

// public employee mapRow(ResultSet resultSet, int i) throws SQLException {

// employee e=new employee();

// e.setId(resultSet.getLong("id"));

// e.setAge(resultSet.getInt("age"));

// e.setUsername(resultSet.getString("username"));

// return e;

// }

// },id);

// return list.size()==1?list.get(0):null;

//第二种方法:这种方法只能处理单行单例，否则会报错：Incorrect column count: expected 1, actual 3

// return super.getJdbcTemplate().queryForObject("SELECT *FROM jdbc WHERE id=?",employee.class,id);

//第三种方法：

return super.getJdbcTemplate().queryForObject("SELECT *FROM jdbc WHERE id=?",new RowMapper<employee>(){

//把一行数据封装成employee对象

//把每一行封装的employee对象存入到List集合中，并返回该集合

@Override

public employee mapRow(ResultSet resultSet, int i) throws SQLException {

employee e=new employee();

e.setId(resultSet.getLong("id"));

e.setAge(resultSet.getInt("age"));

e.setUsername(resultSet.getString("username"));

return e;

}

},id);

}

 

@Override

public List<employee> list() {

return super.getJdbcTemplate().query("SELECT *FROM jdbc",new RowMapper<employee>(){

//把一行数据封装成employee对象

//把每一行封装的employee对象存入到List集合中，并返回该集合

@Override

public employee mapRow(ResultSet resultSet, int i) throws SQLException {

employee e=new employee();

e.setId(resultSet.getLong("id"));

e.setAge(resultSet.getInt("age"));

e.setUsername(resultSet.getString("username"));

return e;

}

});

}

}

 

 

具体结果：