测试 spring链接mysql数据库_Spring 连接数据库测试

1.编写测试对象类

packagemodel;importjava.io.Serializable;/*** Created by xumao on 2016/12/5.*/

public class User implementsSerializable{private intid;privateString name;privateString password;public intgetId() {returnid;

}public void setId(intid) {this.id =id;

}publicString getName() {returnname;

}public voidsetName(String name) {this.name =name;

}publicString getPassword() {returnpassword;

}public voidsetPassword(String password) {this.password =password;

}public User(intid, String name, String password) {super();this.id =id;this.name =name;this.password =password;

}

@OverridepublicString toString() {return "User [id=" + id + ", name=" + name + ", password=" + password + "]";

}publicUser() {super();

}

}

View Code

2.加入数据库配置db.properties

#数据库配置

driver=com.mysql.jdbc.Driver

url=jdbc:mysql://localhost:3306/xumao?useUnicode=true&characterEncoding=utf8

user=root

password=xumao

View Code

3.配置文件

View Code

4.测试类

packagetest;importmodel.User;importorg.junit.Test;importorg.springframework.context.support.ClassPathXmlApplicationContext;importorg.springframework.jdbc.core.JdbcTemplate;importorg.springframework.jdbc.core.RowMapper;importjavax.sql.DataSource;importjava.sql.ResultSet;importjava.sql.SQLException;importjava.util.ArrayList;importjava.util.List;/*** Created by xumao on 2016/12/5.*/

public classTestUser {

ClassPathXmlApplicationContext cx;

JdbcTemplate jdbcTemplate;

{

cx=new ClassPathXmlApplicationContext("springJdbcBean.xml");//加载配置;

jdbcTemplate=cx.getBean(JdbcTemplate.class);

}//测试jdbc连接;

@Testpublic voidtestDataSource(){//引入配置文件中的dataSource,与数据库连接;

DataSource dataSource=(DataSource) cx.getBean("dataSource");

System.out.println(dataSource);

}//测试jdbcTemplate优化数据库;

@Testpublic voidjdbcTemplate(){

System.out.println(jdbcTemplate);

}//测试用户的增加;

@Testpublic voiduserAdd(){

String sql="insert into user values(?,?,?)";try{

jdbcTemplate.update(sql,8,"测试2","123456");

System.out.println("用户添加成功");

}catch(Exception e) {

System.out.println("用户添加失败");

}

}//测试用户的删除;

@Testpublic voiduserDel(){

String sql="delete from user where id=?";try{

jdbcTemplate.update(sql,5);

System.out.println("用户删除成功");

}catch(Exception e) {

System.out.println("用户删除失败");

}

}//测试用户的修改;

@Testpublic voiduserUpd(){

String sql="update user set name=? where id=?";try{

jdbcTemplate.update(sql,"关羽",7);

System.out.println("用户修改成功");

}catch(Exception e) {

System.out.println("用户修改失败");

}

}//测试用户的查询,根据用户的id;

@Testpublic voiduserSelById(){

String sql="select * from user where id=?";try{//RowMapper rowMapper=new BeanPropertyRowMapper(User.class);

User u=jdbcTemplate.queryForObject(sql, new RowMapper() {

@Overridepublic User mapRow(ResultSet rs, int arg1) throwsSQLException {

User u=newUser();

u.setId(rs.getInt("id"));

String result=null;if(rs.getString("name")!=null){try{

result= new String(rs.getString("name").getBytes(),"utf-8");

}catch(Exception e){

result= null;

}

}

u.setName(result);

u.setPassword(rs.getString("password"));returnu;

}

},8);

System.out.println(u);

System.out.println("用户查询成功");

}catch(Exception e) {

System.out.println("用户查询失败");

System.out.println(e.getMessage());

}

}//@Test//public void userSelsById(){//String sql="select * from user where id=?";//try {RowMapper rowMapper=new BeanPropertyRowMapper(User.class);//List u=jdbcTemplate.query(sql);{

@Overridepublic User mapRow(ResultSet rs, int arg1) throws SQLException {User u=new User();u.setId(rs.getInt("id"));u.setName(rs.getString("name"));u.setPassword(rs.getString("password"));return u;}},2);//System.out.println(u);//System.out.println("用户查询成功");//} catch (Exception e) {//System.out.println("用户查询失败");//System.out.println(e.getMessage());//}//}//测试用户的批量添加;

@Testpublic voiduserManyAdd(){

String sql="insert into user values(?,?,?)";//添加列表;

List userAdd=new ArrayList();

userAdd.add(new Object[]{1,"小明","123456"});

userAdd.add(new Object[]{2,"张飞","123456"});

userAdd.add(new Object[]{3,"刘备","123456"});try{

jdbcTemplate.batchUpdate(sql, userAdd);

System.out.println("批量用户添加成功");

}catch(Exception e) {

System.out.println("批量用户添加失败");

}

}//查询用户id大于7的用户的信息;

@Testpublic voiduserQuery(){

String sql="select * from user where id>?";//调用jdbcTemplate的方法，重写mapRow的方法;

try{

List users=jdbcTemplate.query(sql, newRowMapper() {

@Overridepublic Object mapRow(ResultSet rs, int arg1) throwsSQLException {

User u=newUser();

u.setId(rs.getInt("id"));

u.setName(rs.getString("name"));

u.setPassword(rs.getString("password"));returnu;

}

},7);

System.out.println(users);

System.out.println("用户查询成功");

}catch(Exception e) {

System.out.println("用户查询失败");

System.out.println(e.getMessage());

}

}

}

View Code

问题总结：

1.缺少包 ：之前使用的是一个集成的spring的包，但是其中还是少了一些jar包，加c3p0的包；

2.数据库乱码问题，这个问题现在还没很好的解决啊，数据库是用命令行的，程序端插入的中文在命令行窗口还是乱码的，取出的数据是正常的。现在IDEA的编码格式是utf-8，数据库设置了utf-8但是没有生效，再次启动还是会变，

mysql> show variables like '%char%';

+--------------------------+---------------------------------------------------------+

| Variable_name | Value |

+--------------------------+---------------------------------------------------------+

| character_set_client | latin1 |

| character_set_connection | latin1 |

| character_set_database | latin1 |

| character_set_filesystem | binary |

| character_set_results | latin1 |

| character_set_server | latin1 |

| character_set_system | utf8 |

| character_sets_dir | C:\Program Files\MySQL\MySQL Server 5.1\share\charsets\ |

+--------------------------+---------------------------------------------------------+

8 rows in set (0.00 sec)