spring之jdbc使用总结

最新推荐文章于 2024-08-06 23:14:01 发布

sunpy 孙培煜

最新推荐文章于 2024-08-06 23:14:01 发布

阅读量1.7k

点赞数 2

分类专栏： _____3.4 spring

本文链接：https://blog.csdn.net/ya_1249463314/article/details/53385788

版权

_____3.4 spring 专栏收录该内容

9 篇文章 0 订阅

订阅专栏

1.jdbc技术所存在的问题

当我们使用jdbc之前，都需要做通过数据库连接url和数据库身份验证凭证来获取一个数据库连接。如果需要，我们可能还来个事务管理（创建一个事务同时还需要创建一个语句对象来执行SQL）。如果SQL操作如果返回一个ResultSet对象，还得遍历出每一行数据。全过程走try-catch-finally代码块。如果搞失败了，那么可能会泄漏资源，影响程序的性能。

2.spring依赖注入jdbc

导包：

2.1.jdbc配置通过硬编码方式实现

模型：（后面的介绍都采用此模型就不粘贴了）

public class UserInfo {
	private Integer id;
	private String name;
	private Integer age;
	public Integer getId() {
		return id;
	}
	public void setId(Integer id) {
		this.id = id;
	}
	public String getName() {
		return name;
	}
	public void setName(String name) {
		this.name = name;
	}
	public Integer getAge() {
		return age;
	}
	public void setAge(Integer age) {
		this.age = age;
	}
	@Override
	public String toString() {
		return "UserInfo [id=" + id + ", name=" + name + ", age=" + age + "]";
	}
}

Jdbc编码配置：

@Component
public class Jdbc_Configuration {
	
	public DataSource doDataSource(){
		DriverManagerDataSource dataSource =new DriverManagerDataSource();
		dataSource.setDriverClassName("com.mysql.jdbc.Driver");
		dataSource.setUrl("jdbc:mysql://localhost:3306/mydatabase?characterEncoding=utf-8");
		dataSource.setUsername("root");
		dataSource.setPassword(null);
		return dataSource;
	}
}

dao接口：

public interface UserInfoDao {
	public UserInfo find(Integer id);
	public void insert(UserInfo userInfo);
	
}

dao实现：

@Component
public class UserInfoDaoImpl implements UserInfoDao{

	@Resource
	private Jdbc_Configuration config;
	public void setConfig(Jdbc_Configuration config) {
		this.config = config;
	}

	Connection conn =null;
	PreparedStatement ps =null;
	ResultSet rs =null;
	@Override
	public UserInfo find(Integer id) {
		try {
			DataSource dataSource =config.doDataSource();
			conn =dataSource.getConnection();
			ps =conn.prepareStatement("select * from table2 where id=?");
			ps.setInt(1, id);
			rs =ps.executeQuery();
			UserInfo userInfo =new UserInfo();
			while(rs.next()){
				userInfo.setId(rs.getInt("id"));
				userInfo.setName(rs.getString("name"));
				userInfo.setAge(rs.getInt("age"));
			}
			return userInfo;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return null;
	}

	@Override
	public void insert(UserInfo userInfo) {
		try {
			DataSource dataSource =config.doDataSource();
			conn =dataSource.getConnection();
			ps =conn.prepareStatement("insert into table2(id,name,age) values(?,?,?)");
			ps.setInt(1, userInfo.getId());
			ps.setString(2, userInfo.getName());
			ps.setInt(3, userInfo.getAge());
			ps.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
}

bean.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:cache="http://www.springframework.org/schema/cache"
        xmlns:p="http://www.springframework.org/schema/p"  
        xsi:schemaLocation="http://www.springframework.org/schema/beans   
        http://www.springframework.org/schema/beans/spring-beans-3.2.xsd
        http://www.springframework.org/schema/cache
        http://www.springframework.org/schema/cache/spring-cache-3.2.xsd  
        http://www.springframework.org/schema/context  
        http://www.springframework.org/schema/context/spring-context-3.2.xsd">  
       <context:component-scan base-package="cn.spy.jdbc"></context:component-scan>
       <context:annotation-config></context:annotation-config>
</beans>

测试：

public class MyTest {
	public static void main(String[] args) throws Exception {
		ApplicationContext context =new ClassPathXmlApplicationContext("cn/spy/jdbc/bean.xml");
		UserInfoDao userInfoDao =(UserInfoDao) context.getBean("userInfoDaoImpl");
//		UserInfo userInfo =userInfoDao.find(2);
//		System.out.println(userInfo);
		UserInfo userInfo =new UserInfo();
		userInfo.setId(6);
		userInfo.setName("李沁");
		userInfo.setAge(40);
		userInfoDao.insert(userInfo);
	}
}

结果：

2.2.XML配置文件方式实现

对于dao层和model模型都是一样的。只是把硬编码的jdbc配置转移到xml中了。相当于xml方式的依赖注入。

bean.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:cache="http://www.springframework.org/schema/cache"
        xmlns:p="http://www.springframework.org/schema/p"  
        xmlns:jdbc="http://www.springframework.org/schema/jdbc"
        xmlns:tx="http://www.springframework.org/schema/tx"
        xsi:schemaLocation="http://www.springframework.org/schema/beans   
        http://www.springframework.org/schema/beans/spring-beans-3.2.xsd
        http://www.springframework.org/schema/cache
        http://www.springframework.org/schema/cache/spring-cache-3.2.xsd  
        http://www.springframework.org/schema/context  
        http://www.springframework.org/schema/context/spring-context-3.2.xsd
        http://www.springframework.org/schema/jdbc
        http://www.springframework.org/schema/jdbc/spring-jdbc-3.2.xsd
        http://www.springframework.org/schema/tx
        http://www.springframework.org/schema/tx/spring-tx-3.2.xsd"> 
        <context:component-scan base-package="cn.spy.jdbc"></context:component-scan> 
        <context:annotation-config></context:annotation-config>
       <bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
       		<property name="driverClassName" value="com.mysql.jdbc.Driver"></property>
       		<property name="url" value="jdbc:mysql://localhost:3306/mydatabase?characterEncoding=utf-8"></property>
       		<property name="username" value="root"></property>
       		<property name="password" value=""></property>
       </bean>
</beans>

dao实现：

@Component
public class UserInfoDaoImpl implements UserInfoDao{

	@Resource
	private DataSource dataSource;
	public void setDataSource(DataSource dataSource) {
		this.dataSource = dataSource;
	}

	Connection conn =null;
	PreparedStatement ps =null;
	ResultSet rs =null;
	@Override
	public UserInfo find(Integer id) {
		try {
			conn =dataSource.getConnection();
			ps =conn.prepareStatement("select * from table2 where id=?");
			ps.setInt(1, id);
			rs =ps.executeQuery();
			UserInfo userInfo =new UserInfo();
			while(rs.next()){
				userInfo.setId(rs.getInt("id"));
				userInfo.setName(rs.getString("name"));
				userInfo.setAge(rs.getInt("age"));
			}
			return userInfo;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return null;
	}

	@Override
	public void insert(UserInfo userInfo) {
		try {
			conn =dataSource.getConnection();
			ps =conn.prepareStatement("insert into table2(id,name,age) values(?,?,?)");
			ps.setInt(1, userInfo.getId());
			ps.setString(2, userInfo.getName());
			ps.setInt(3, userInfo.getAge());
			ps.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
}

测试：

public class MyTest {
	public static void main(String[] args) throws Exception {
		ApplicationContext context =new ClassPathXmlApplicationContext("cn/spy/jdbc/bean.xml");
		UserInfoDao userInfoDao =(UserInfoDao) context.getBean("userInfoDaoImpl");
		UserInfo userInfo =userInfoDao.find(2);
		System.out.println(userInfo);
	}
}

结果：

总结：

可以看到这个例子还是需要try-catch-finally代码块。这个例子仅仅只是使用了spring的依赖注入的特性，并没有使用spring的jdbc模板支持。而且应当注意在生产环境下，切记别玩DriverManagerDataSource这个类，因为这个类没有连接池功能，不会管理连接回收再利用。而是每次都尝试打开一个新的物理jdbc连接。打开一个jdbc连接是一个非常昂贵的过程。所以应该多使用c3p0或者dbcp连接池的DataSource来实现，切记。

3.使用jdbc模板类（jdbcTemplate）实现增删改查

由于DriverManagerDataSource的缺点，接下来都使用c3p0连接池来实现jdbc连接的管理。

使用c3p0就要导入相关的jar包：c3p0-0.9.1.2.jar

Model模型：

public class Student {

	private String id;
	private String name;
	private int age;
	private String gender;
	对应的getter、setter方法。。。
	@Override
	public String toString() {
		return "Student [id=" + id + ", name=" + name + ", age=" + age
				+ ", gender=" + gender + "]";
	}
}

dao接口层：

public interface IStudentDao {
	public Student findById(String id);
	public int insert(Student stu);
	public int delete(String id);
	public int update(String id,Student stu);
}

dao实现层：

public class StudentDaoImpl implements IStudentDao{

	private JdbcTemplate jdbcTemplate;
	public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
		this.jdbcTemplate = jdbcTemplate;
	}

	@Override
	public Student findById(String id) {
		// TODO Auto-generated method stub
		String sql="select * from student where id=?";
		return jdbcTemplate.queryForObject(sql, new RowMapper<Student>(){

			@Override
			public Student mapRow(ResultSet rs, int rowNum) throws SQLException {
				// TODO Auto-generated method stub
				Student stu =new Student();
				stu.setId(rs.getString("id"));
				stu.setName(rs.getString("name"));
				stu.setAge(rs.getInt("age"));
				stu.setGender(rs.getString("gender"));
				return stu;
			}
		},id);
	}

	@Override
	public int insert(Student stu) {
		// TODO Auto-generated method stub
		String sql="insert into student(id,name,age,gender) values(?,?,?,?)";
		int rowNum =jdbcTemplate.update(sql, new Object[]{stu.getId(),stu.getName(),stu.getAge(),stu.getGender()}, new int[]{Types.VARCHAR,Types.VARCHAR,Types.INTEGER,Types.VARCHAR});
		return rowNum;
	}

	@Override
	public int delete(String id) {
		String sql="delete from student where id=?";
		int rowNum =jdbcTemplate.update(sql, id);
		return rowNum;
	}

	@Override
	public int update(String id, Student stu) {
		// TODO Auto-generated method stub
		String sql="update student set name=?,age=? where id=?";
		int rowNum =jdbcTemplate.update(sql, new Object[]{stu.getName(),stu.getAge(),id}, new int[]{Types.VARCHAR,Types.INTEGER,Types.VARCHAR});
		return rowNum;
	}
}

测试类：

public class JdbcTest {
	public static void main(String[] args) {
		ApplicationContext context =new ClassPathXmlApplicationContext("bean.xml");
		IStudentDao studentDao =(IStudentDao) context.getBean("studentDaoImpl");
		//通过主键查找：
//		Student stu =studentDao.findById("1");
//		System.out.println(stu);
		//插入对象
//		Student stu =new Student();
//		stu.setId("21");
//		stu.setName("李沁");
//		stu.setAge(21);
//		stu.setGender("男");
//		int rowNum =studentDao.insert(stu);
//		System.out.println(rowNum);
		//删除
//		int rowNum =studentDao.delete("21");
//		System.out.println(rowNum);
		//修改对象的属性
		Student stu =new Student();
		stu.setName("张磊");
		stu.setAge(26);
		stu.setGender("男");
		int rowNum =studentDao.update("2", stu);
		System.out.println(rowNum);
	}
}

结果：

查询：

更新：

插入：

。。。

4.使用jdbcTemplate实现批处理

dao实现层：

public class StudentDaoImpl implements IStudentDao{

	private JdbcTemplate jdbcTemplate;
	public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
		this.jdbcTemplate = jdbcTemplate;
	}
	@Override
	public void updateBatch(List<Object[]> list) {
		// TODO Auto-generated method stub
		String sql="insert into student values(?,?,?,?)";
		jdbcTemplate.batchUpdate(sql, list, new int[]{Types.VARCHAR,Types.VARCHAR,Types.INTEGER,Types.VARCHAR});
	}
}

测试类：

public class JdbcTest {
	public static void main(String[] args) {
		List<Object[]> list =new ArrayList<Object[]>();
		for(int i=0;i<4;i++){
			Object[] objs=new Object[4];
			objs[0]="30"+i;
			objs[1]="赵四"+i;
			objs[2]=20+i;
			objs[3]="男";
			list.add(objs);
		}
		ApplicationContext context =new ClassPathXmlApplicationContext("bean.xml");
		IStudentDao studentDao =(IStudentDao) context.getBean("studentDaoImpl");
		studentDao.updateBatch(list);
	}
}

结果：