Spring 连接数据库测试
1.编写测试对象类
package model; import java.io.Serializable; /** * Created by xumao on 2016/12/5. */ public class User implements Serializable{ private int id; private String name; private String password; 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 String getPassword() { return password; } public void setPassword(String password) { this.password = password; } public User(int id, String name, String password) { super(); this.id = id; this.name = name; this.password = password; } @Override public String toString() { return "User [id=" + id + ", name=" + name + ", password=" + password + "]"; } public User() { super(); } }
2.加入数据库配置db.properties
#数据库配置 driver=com.mysql.jdbc.Driver url=jdbc:mysql://localhost:3306/xumao?useUnicode=true&characterEncoding=utf8 user=root password=xumao
3.配置文件
<?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:context="http://www.springframework.org/schema/context" 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"> <context:component-scan base-package="model,test"></context:component-scan> <!-- 配置外部资源 --> <context:property-placeholder location="classpath:db.properties"/> <!-- <util:properties id="dbProperties" location="classpath:db.properties"></util:properties> --> <!-- 配置dbcp连接池 --> <!-- <bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSourceFactory" factory-method="createDataSource"> <constructor-arg ref="dbProperties"/> </bean> --> <bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource" > <property name="jdbcUrl" value="${url}"></property> <property name="password" value="${password}"></property> <property name="user" value="${user}"></property> <property name="driverClass" value="${driver}"></property> </bean> <!-- 配置jdbcTamplate --> <bean id="jdbcTamplate" class="org.springframework.jdbc.core.JdbcTemplate"> <property name="dataSource" ref="dataSource"></property> </bean> </beans>
4.测试类
package test; import model.User; import org.junit.Test; import org.springframework.context.support.ClassPathXmlApplicationContext; import org.springframework.jdbc.core.JdbcTemplate; import org.springframework.jdbc.core.RowMapper; import javax.sql.DataSource; import java.sql.ResultSet; import java.sql.SQLException; import java.util.ArrayList; import java.util.List; /** * Created by xumao on 2016/12/5. */ public class TestUser { ClassPathXmlApplicationContext cx; JdbcTemplate jdbcTemplate; { cx=new ClassPathXmlApplicationContext("springJdbcBean.xml"); //加载配置; jdbcTemplate=cx.getBean(JdbcTemplate.class); } //测试jdbc连接; @Test public void testDataSource(){ //引入配置文件中的dataSource,与数据库连接; DataSource dataSource=(DataSource) cx.getBean("dataSource"); System.out.println(dataSource); } //测试jdbcTemplate优化数据库; @Test public void jdbcTemplate(){ System.out.println(jdbcTemplate); } //测试用户的增加; @Test public void userAdd(){ String sql="insert into user values(?,?,?)"; try { jdbcTemplate.update(sql,8,"测试2","123456"); System.out.println("用户添加成功"); } catch (Exception e) { System.out.println("用户添加失败"); } } //测试用户的删除; @Test public void userDel(){ String sql="delete from user where id=?"; try { jdbcTemplate.update(sql,5); System.out.println("用户删除成功"); } catch (Exception e) { System.out.println("用户删除失败"); } } //测试用户的修改; @Test public void userUpd(){ String sql="update user set name=? where id=?"; try { jdbcTemplate.update(sql,"关羽",7); System.out.println("用户修改成功"); } catch (Exception e) { System.out.println("用户修改失败"); } } //测试用户的查询,根据用户的id; @Test public void userSelById(){ String sql="select * from user where id=?"; try { //RowMapper<User> rowMapper=new BeanPropertyRowMapper<User>(User.class); User u=jdbcTemplate.queryForObject(sql, new RowMapper<User>() { @Override public User mapRow(ResultSet rs, int arg1) throws SQLException { User u=new User(); 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")); return u; } },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<User> rowMapper=new BeanPropertyRowMapper<User>(User.class); // List<User> u=jdbcTemplate.query(sql); //// { //// //// @Override //// public 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()); // } // } //测试用户的批量添加; @Test public void userManyAdd(){ String sql="insert into user values(?,?,?)"; //添加列表; List<Object[]> userAdd=new ArrayList<Object[]>(); 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的用户的信息; @Test public void userQuery(){ String sql="select * from user where id>?"; //调用jdbcTemplate的方法，重写mapRow的方法; try { List<User> users=jdbcTemplate.query(sql, new RowMapper() { @Override public Object 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; } },7); System.out.println(users); System.out.println("用户查询成功"); } catch (Exception e) { System.out.println("用户查询失败"); System.out.println(e.getMessage()); } } }
问题总结：
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)
