spring中的jdbc连接用户增删改查

20.1 新建一个User的实体类;

packagecom.eduask.spring.jdbc;

importjava.io.Serializable;

//新建一个用户实体类,实现序列化接口;

publicclassUserimplementsSerializable{

privateintid;

privateString name;

privateString password;

publicintgetId() {

returnid;

}

publicvoidsetId(intid) {

this.id = id;

}

publicString getName() {

returnname;

}

publicvoidsetName(String name) {

this.name = name;

}

publicString getPassword() {

returnpassword;

}

publicvoidsetPassword(String password) {

this.password = password;

}

publicUser(intid, String name, String password) {

super();

this.id = id;

this.name = name;

this.password = password;

}

@Override

publicString toString() {

return"User [id=" + id + ", name=" + name + ", password=" + password + "]";

}

publicUser() {

super();

//TODOAuto-generated constructor stub

}

}

20.2新建一个TestUser的类，用于用户的增删改查的操作;

packagecom.eduask.spring.jdbc;

importjava.sql.ResultSet;

importjava.sql.SQLException;

importjava.util.ArrayList;

importjava.util.List;

importjavax.sql.DataSource;

importoracle.net.aso.d;

importorg.junit.Test;

importorg.springframework.context.support.ClassPathXmlApplicationContext;

importorg.springframework.jdbc.core.BeanPropertyRowMapper;

importorg.springframework.jdbc.core.JdbcTemplate;

importorg.springframework.jdbc.core.RowMapper;

publicclassTestUser {

ClassPathXmlApplicationContext cx;

JdbcTemplate jdbcTemplate;

{

cx=newClassPathXmlApplicationContext("springJdbcBean.xml");

//加载配置;

jdbcTemplate=cx.getBean(JdbcTemplate.class);

}

//测试jdbc连接;

@Test

publicvoidtestDataSource(){

//引入配置文件中的dataSource,与数据库连接;

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

System.out.println(dataSource);

}

//测试jdbcTemplate优化数据库;

@Test

publicvoidjdbcTemplate(){

System.out.println(jdbcTemplate);

}

//测试用户的增加;

@Test

publicvoiduserAdd(){

String sql="insert into t_user values(?,?,?)";

try{

jdbcTemplate.update(sql,5,"小明","123456");

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

}catch(Exception e) {

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

}

}

//测试用户的删除;

@Test

publicvoiduserDel(){

String sql="delete from t_user where id=?";

try{

jdbcTemplate.update(sql,5);

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

}catch(Exception e) {

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

}

}

//测试用户的修改;

@Test

publicvoiduserUpd(){

String sql="update t_user set name=? where id=?";

try{

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

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

}catch(Exception e) {

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

}

}

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

@Test

publicvoiduserSelById(){

String sql="select * from t_user where id=?";

try{

//RowMapper<User> rowMapper=new BeanPropertyRowMapper<User>(User.class);

User u=jdbcTemplate.queryForObject(sql,newRowMapper<User>() {

@Override

publicUser mapRow(ResultSet rs,intarg1)throwsSQLException {

User u=newUser();

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

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

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

returnu;

}

},1);

System.out.println(u);

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

}catch(Exception e) {

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

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

}

}

//测试用户的批量添加;

@Test

publicvoiduserManyAdd(){

String sql="insert into t_user values(?,?,?)";

//添加列表;

List<Object[]> userAdd=newArrayList<Object[]>();

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

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

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

try{

jdbcTemplate.batchUpdate(sql, userAdd);

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

}catch(Exception e) {

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

}

}

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

@Test

publicvoiduserQuery(){

String sql="select * from t_user where id>?";

//调用jdbcTemplate的方法，重写mapRow的方法;

try{

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

@Override

publicObject mapRow(ResultSet rs,intarg1)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());

}

}

}

20.3 新建一个config的文件springJdbcBean.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:context="http://www.springframework.org/schema/context"

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

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-4.0.xsd

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

http://www.springframework.org/schema/util/spring-util-4.0.xsd

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

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

">

<context:component-scan base-package="com.eduask.spring"></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-argref="dbProperties"/>

</bean> -->

<bean id="dataSource"class="org.apache.commons.dbcp.BasicDataSource">

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

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

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

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

</bean>

<!-- 配置jdbcTamplate -->

<bean id="jdbcTamplate"class="org.springframework.jdbc.core.JdbcTemplate">

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

</bean>

</beans>

20.4 新建一个db.properties的jdbc的配置文件;

url=jdbc:oracle:thin:@localhost:1521/orcl

user=scott

pwd=tiger

driverClass=oracle.jdbc.driver.OracleDriver