最近使用maven项目,由于前后端完全分离,不好测试,则先测试dao层的sql和方法是不是正确的
首先使用eclipse创建maven项目
pom.xml
<dependencies> <dependency> <groupId>junit</groupId> <artifactId>junit</artifactId> <version>4.12</version> </dependency> <dependency> <groupId>org.springframework</groupId> <artifactId>spring-context</artifactId> <version>4.2.0.RELEASE</version> </dependency> <dependency> <groupId>org.springframework</groupId> <artifactId>spring-core</artifactId> <version>4.2.0.RELEASE</version> </dependency> <dependency> <groupId>org.springframework</groupId> <artifactId>spring-aop</artifactId> <version>4.2.0.RELEASE</version> </dependency> <dependency> <groupId>org.springframework</groupId> <artifactId>spring-expression</artifactId> <version>4.2.0.RELEASE</version> </dependency> <dependency> <groupId>org.springframework</groupId> <artifactId>spring-beans</artifactId> <version>4.2.0.RELEASE</version> </dependency> <dependency> <groupId>org.springframework</groupId> <artifactId>spring-jdbc</artifactId> <version>4.2.0.RELEASE</version> </dependency> <!-- junit集成测试 --> <dependency> <groupId>org.springframework</groupId> <artifactId>spring-test</artifactId> <version>4.2.0.RELEASE</version> </dependency> <dependency> <groupId>com.mchange</groupId> <artifactId>c3p0</artifactId> <version>0.9.5</version> </dependency> <dependency> <groupId>com.oracle</groupId> <artifactId>ojdbc</artifactId> <version>6.0</version> </dependency> </dependencies>
接着就是spring的配置文件 了
既然做测试,配置文件从开发包复制一份到测试包(src/test/resources)下
jdbc.properties
jdbc.driverClassName=oracle.jdbc.driver.OracleDriver
#jdbc.url=jdbc:mysql://localhost:3306/learn?#autoReconnect=true&useUnicode=true&characterEncoding=UTF-8
jdbc.url=jdbc:oracle:thin:@192.168.1.7:1521:jdbc
jdbc.username=root
jdbc.password=123456
beans.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" xmlns:mvc="http://www.springframework.org/schema/mvc"
xmlns:context="http://www.springframework.org/schema/context"
xmlns:aop="http://www.springframework.org/schema/aop" xmlns:tx="http://www.springframework.org/schema/tx"
xsi:schemaLocation="http://www.springframework.org/schema/beans
http://www.springframework.org/schema/beans/spring-beans.xsd
http://www.springframework.org/schema/mvc
http://www.springframework.org/schema/mvc/spring-mvc.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
http://www.springframework.org/schema/tx
http://www.springframework.org/schema/tx/spring-tx.xsd ">
<!-- 支持注解 -->
<context:annotation-config/>
<!-- 组件扫描 -->
<context:component-scan base-package="com.share.*"/>
<!--数据库配置:配置jdbc.properties -->
<context:property-placeholder location="classpath:jdbc.properties"/>
<!-- 3、配置dataSource数据源c3p0 -->
<bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource">
<property name="driverClass" value="${jdbc.driverClassName}"/>
<property name="jdbcUrl" value="${jdbc.url}"/>
<property name="user" value="${jdbc.username}"/>
<property name="password" value="${jdbc.password}"/>
<!-- <property name="maxPoolSize" value="${c3p0.pool.maxPoolSize}"/>
<property name="minPoolSize" value="${c3p0.pool.minPoolSize}"/>
<property name="initialPoolSize" value="${c3p0.pool.initialPoolSize}"/>
<property name="acquireIncrement" value="${c3p0.pool.acquireIncrement}"/> -->
</bean>
<!-- JDBCTemplate注入 -->
<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
<property name = "dataSource" ref="dataSource"/>
</bean>
<bean id="namedParameterJdbcTemplate" class="org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate">
<constructor-arg ref="dataSource"/>
</bean>
<!-- mybatis配置文件
<bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
<property name="dataSource" ref="dataSource"></property>
自动扫描mapping.xml文件
<property name="mapperLocations" value="classpath:com/share/mapping/*.xml"></property>
<property name="configLocations" value="classpath:mybatis.xml"/> >
定义包的别名
<property name="typeAliasesPackage" value="com.share.domain"></property>
</bean>-->
<!-- DAO接口所在包名,Spring会自动查找其下的类
<bean class="org.mybatis.spring.mapper.MapperScannerConfigurer">
<property name="basePackage" value="com.share.dao" />
<property name="sqlSessionFactoryBeanName" value="sqlSessionFactory"></property>
</bean> -->
<!-- (事务管理)transaction manager, use JtaTransactionManager for global tx
<bean id="transactionManager"
class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
<property name="dataSource" ref="dataSource" />
</bean> -->
</beans>
实体User
public class User implements Serializable {
private static final long serialVersionUID = -1235134923224170420L;
private Integer id;
private String username;
private String password;
private String email;
private String phone;
private Integer age;
private Integer sex;
private String address;
private Date birth;
........get set方法 省略
}
数据库根据实体建就行了
dao层代码
package com.share.dao;
import java.sql.Types;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.support.DataAccessUtils;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.ColumnMapRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapperResultSetExtractor;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.jdbc.core.namedparam.SqlParameterSourceUtils;
import org.springframework.stereotype.Repository;
import com.share.domain.User;
@Repository
public class BaseDao {
@Autowired
private JdbcTemplate jdbcTemplate;
@Autowired
private NamedParameterJdbcTemplate nameParameterJdbcTemplate;
/**
* 添加用户
* @param user
* @return
*/
public int insert(User user) {
String sql = "insert into tuser (id, username, password, email, phone, age, sex, address, birth) values (?, ?, ?, ?, ?, ?, ?, ?, ?)";
Object[] obj = new Object[]{getSeq("seq_user"), user.getUsername(), user.getPassword(), user.getEmail(), user.getPhone(), user.getAge(), user.getSex(), user.getAddress(), user.getBirth()};
int[] types = new int[]{Types.INTEGER, Types.VARCHAR, Types.VARCHAR, Types.VARCHAR, Types.VARCHAR, Types.VARCHAR, Types.VARCHAR, Types.VARCHAR, Types.DATE};
return jdbcTemplate.update(sql, obj, types);
}
private Integer getSeq(String seqName) {
String sql = "select " + seqName + ".nextval from dual";
return jdbcTemplate.queryForObject(sql, Integer.class);
}
/**
* nameParameterJdbcTemplate添加用户
* @param user
* @return
*/
public int insertUser(User user) {
user.setId(getSeq("seq_user"));
String sql = "insert into tuser (id, username, password, email, phone, age, sex, address, birth) values (:id, :username, :password, :email, :phone, :age, :sex, :address, :birth)";
SqlParameterSource source = new BeanPropertySqlParameterSource(user);
return nameParameterJdbcTemplate.update(sql, source);
}
/**
* nameParameterJdbcTemplate添加用户
* @param user
* @return
*/
public int batchInsertUser(User user) {
user.setId(getSeq("seq_user"));
String sql = "insert into tuser (id, username, password, email, phone, age, sex, address, birth) values (:id, :username, :password, :email, :phone, :age, :sex, :address, :birth)";
SqlParameterSource source = new BeanPropertySqlParameterSource(user);
return nameParameterJdbcTemplate.update(sql, source);
}
/**
* 批量添加
* @param users
* @return
*/
public int batchInsert(User[] users) {
String sql = "insert into tuser (id, username, password, email, phone, age, sex, address, birth) values (?, ?, ?, ?, ?, ?, ?, ?, ?)";
int[] types = new int[]{Types.INTEGER, Types.VARCHAR, Types.VARCHAR, Types.VARCHAR, Types.VARCHAR, Types.VARCHAR, Types.VARCHAR, Types.VARCHAR, Types.DATE};
List<Object[]> list = new ArrayList<Object[]>();
for(User user : users) {
list.add(new Object[]{getSeq("seq_user"),user.getUsername(), user.getPassword(), user.getEmail(), user.getPhone(), user.getAge(), user.getSex(), user.getAddress(), user.getBirth()});
}
//list.add(users);
return jdbcTemplate.batchUpdate(sql, list, types).length;
}
/**
* 批量更新
*/
//public int batchUpdateBeans(String insertSql, List<? extends Object> beans) {
public int batchUpdateBeans(List<? extends Object> beans) {
String sql = "insert into tuser (id, username, password, email, phone, age, sex, address, birth) values (seq_user.nextval, :username, :password, :email, :phone, :age, :sex, :address, :birth)";
int[] results = null;
try {
SqlParameterSource[] params = SqlParameterSourceUtils.createBatch(beans.toArray());
results = nameParameterJdbcTemplate.batchUpdate(sql, params);
} catch (Exception e) {
e.printStackTrace();
}
return results.length;
}
/**
* 查询单个对象
* @param id
* @return
*/
public User findUserById(Integer id) {
String sql = "select * from tuser where id = ?";
//return jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<User>(User.class),id);
List<User> query = jdbcTemplate.query(sql, new BeanPropertyRowMapper<User>(User.class), id);
return DataAccessUtils.uniqueResult(query);
}
/**
* 查询单个对象
* @param id
* @return
*/
public Map<String, Object> findUserMap(Integer id) {
String sql = "select * from tuser where id = ?";
Object[] args = new Object[]{id};
List<Map<String, Object>> results =
jdbcTemplate.query(sql, args, new RowMapperResultSetExtractor<Map<String, Object>>(new ColumnMapRowMapper(), 1));
return DataAccessUtils.uniqueResult(results);
}
}
src/test/java中的测试代码
@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration(locations={"classpath:beans.xml"})
public class JdbcTest {
@Autowired
private BaseDao baseDao;
@Test
public void insertTest() {
User user = new User();
user.setUsername("admin");
user.setPassword("123456");
user.setEmail("admin@163.com");
user.setPhone("110");
user.setSex(1);
user.setAge(25);
user.setBirth(DateUtils.str2Date("1990-01-31 13:14:15"));
user.setAddress("上海");
baseDao.insert(user);
}
@Test
public void batchInsert() {
User user = new User();
user.setUsername("share");
user.setPassword("123456");
user.setEmail("admin@163.com");
user.setPhone("120");
user.setSex(1);
user.setAge(25);
user.setBirth(DateUtils.str2Date("1990-01-31 13:14:15"));
user.setAddress("天津");
User user1 = new User();
user1.setUsername("share");
user1.setPassword("123456");
user1.setEmail("admin@163.com");
user1.setPhone("120");
user1.setSex(1);
user1.setAge(25);
user1.setBirth(DateUtils.str2Date("1990-01-31 13:14:15"));
user1.setAddress("北京");
User[] users = new User[]{user, user1};
baseDao.batchInsert(users);
}
@Test
public void findUser() {
User user = baseDao.findUserById(1001);
System.out.println(user);
}
@Test
public void findUserMap() {
Map<String, Object> user = baseDao.findUserMap(1001);
System.out.println(user.get("ID") + "::" + user.get("USERNAME"));
}
@Test
public void addUser() {
User user = new User();
user.setUsername("风平浪静");
user.setPassword("123456");
user.setEmail("fengpinglangjing@163.com");
user.setPhone("110");
user.setSex(1);
user.setAge(25);
user.setBirth(DateUtils.str2Date("1990-01-31 13:14:15"));
user.setAddress("上海");
baseDao.insertUser(user);
}
@Test
public void batchAddUser() {
User user = new User();
user.setUsername("share1");
user.setPassword("123456");
user.setEmail("admin@163.com");
user.setPhone("120");
user.setSex(1);
user.setAge(25);
user.setBirth(DateUtils.str2Date("1990-01-31 13:14:15"));
user.setAddress("天津");
User user1 = new User();
user1.setUsername("share2");
user1.setPassword("123456");
user1.setEmail("admin@163.com");
user1.setPhone("120");
user1.setSex(1);
user1.setAge(25);
user1.setBirth(DateUtils.str2Date("1990-01-31 13:14:15"));
user1.setAddress("北京");
List<User> users = new ArrayList<User>();
users.add(user1);
users.add(user);
//User[] users = new User[]{user, user1};
baseDao.batchUpdateBeans(users);
}
}
接着就是数据库查看记录是否跟自己写的sql匹配判断结果是否正确了……