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)