spring jdbc模板

Spring JDBC 示例

最新推荐文章于 2022-04-06 14:14:10 发布

原创最新推荐文章于 2022-04-06 14:14:10 发布 · 205 阅读

1 ·

CC 4.0 BY-SA版权

spring 专栏收录该内容

12 篇文章

订阅专栏

本文通过一个具体的例子展示了如何使用 Spring 框架中的 JDBC 模块进行数据库操作，包括实体类定义、DAO 层实现、XML 配置以及测试类编写等。

代码实现：

1.实体类

 public class User {
	private int id;
	
	private String username;
	private String password;
	public int getId() {
		return id;
	}
	public void setId(int id) {
		this.id = id;
	}
	public String getUsername() {
		return username;
	}
	public void setUsername(String username) {
		this.username = username;
	}
	public String getPassword() {
		return password;
	}
	public void setPassword(String password) {
		this.password = password;
	}
	
}

2.数据层：

public class UserDao {
	private JdbcTemplate template;
	
	public void setTemplate(JdbcTemplate template) {
		this.template = template;
	}
	public void save(User user) {
		String sql ="insert into test1 values('"+user.getId()+"','"+user.getUsername()+"','"+user.getPassword()+"')";
		template.execute(sql);
	}
	public void delete() {
		
	}
}

3.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:util="http://www.springframework.org/schema/util"
       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/util 
		http://www.springframework.org/schema/util/spring-util.xsd
		http://www.springframework.org/schema/context 
		http://www.springframework.org/schema/context/spring-context.xsd">
<!--spring管理资源都是bean的形式  -->
<!-- 你的资源是那个类的对象 
	<bean class="类名" id="类名对应的唯一标识"></bean>
-->
	
	<bean class="com.array.jdbc.user.dao.UserDao" id="userDao">
		<property name="template" ref="template"></property>
	</bean>
	<!-- jdbcTemplate -->
	<bean id="template" class="org.springframework.jdbc.core.JdbcTemplate">
		<property name="dataSource" ref="dataSource"></property>
	</bean>
	
	<!-- 设置国际化消息，properties文件 -->
	<bean class="org.springframework.beans.factory.config.PropertyPlaceholderConfigurer">
		<!-- 告诉property文件名称 -->
		<property name="location" value="classpath:jdbc.properties"></property>
		
	</bean>
	<context:property-placeholder location="classpath:*.properties"/>

	
	<!--dataSource -->
	<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/spring"></property>
		<property name="username" value="array"></property>
		<property name="password" value="array"></property>
	</bean>
	<!-- DBCP -->
	<bean id="dataSource1" class="org.apache.commons.dbcp.BasicDataSource">
		<property name="driverClassName" value="com.mysql.jdbc.Driver"></property>
		<property name="url" value="jdbc:mysql://localhost:3306/spring"></property>
		<property name="username" value="array"></property>
		<property name="password" value="array"></property>
	
	</bean>
	<!--c3p0  -->
	<!-- <bean id="dataSource2" class="com.mchange.v2.c3p0.ComboPooledDataSource">
		<property name="driverClassName" value="${jdbc.driver}"></property>
		<property name="url" value="${jdbc.url}"></property>
		<property name="username" value="${jdbc.username}"></property>
		<property name="password" value="${jdbc.password}"></property>
	
	</bean> -->
</beans>

4.测试

public class App {
	public static void main(String[] args) {
		ApplicationContext act = new ClassPathXmlApplicationContext("applicationContext.xml");
		UserDao userDao = (UserDao) act.getBean("userDao");
//		User user = new User();
//		user.setPassword("array2");
//		user.setUsername("array2");
//		user.setId(5);
//		userDao.save(user);
		
		Properties ps = System.getProperties();
		Set set = ps.keySet();
		for (Object object : set) {
			System.out.println(object+"=="+ ps.get(object));
		}
	//	Enumeration enumeration = ps.elements();
		/*while (enumeration.hasMoreElements()) {
			Object object = (Object) enumeration.nextElement();
			System.out.println(object);
			
		}
*/		
	}
}

改进版：

1.实体类同上
2.数据层UserDao

public class UserDao extends JdbcDaoSupport{
	/*继承JdbcDaoSupport后可以不用再写
	 * private JdbcTemplate template;
	
	public void setTemplate(JdbcTemplate template) {
		this.template = template;
	}*/


	/*public void save(User user) {
		String sql="insert into test1 values(7,'array','array3')";
		this.getJdbcTemplate().execute(sql);
		
	}*/
	//预编译
	//保存
	public void save(User user) {
		String sql="insert into test1 values(?,?,?)";
		this.getJdbcTemplate().update(sql, new Object[]{user.getId(),user.getUsername(),user.getPassword()});
		
	}
	//更新
	public void update(User user) {
		String sql="update test1 set username=?,password=? where id=?";
		this.getJdbcTemplate().update(sql, new Object[]{user.getUsername(),user.getPassword(),user.getId()});
		
	}
	//删除
	public void delete(Integer id) {
		String sql="delete from test1 where id = ?";
		this.getJdbcTemplate().update(sql, new Object[]{id});
		
	}
	//查找
	public Object findUser(Integer id) {
		String sql="select username from test1 where id = ?";
		Object[] params =  new Object[]{id};
		return this.getJdbcTemplate().queryForObject(sql,Object.class, params);
		
	}
	public long getCount(){
		String sql = "select COUNT(0) from test1";
		return this.getJdbcTemplate().queryForLong(sql);
		
	}
	
	public List<User> getAll() {
		String sql = "select * from test1";
		RowMapper<User> rm = new RowMapper<User>() {


			public User mapRow(ResultSet rs, int rowNum) throws SQLException {
				User user = new User();
				user.setId(rs.getInt("id"));
				user.setUsername(rs.getString("username"));
				user.setPassword(rs.getString("password"));
				return user;
			}
			
		};
		return this.getJdbcTemplate().query(sql, rm);
	}
	
	/**
	 * //分页
	 * (这里用一句话描述这个方法的作用)
	 * 方法名：getPage
	 * 创建人：蒋川阳 
	 * 时间：2018年7月9日-上午11:09:38 
	 * 手机:13199657597
	 * @param pageNum:显示第几页
	 * @param prePageNum：一页多少条数据
	 * @return List<User>
	 * @exception 
	 * @since  1.0.0
	 */
	public List<User> getPage(int pageNum,int prePageNum) {
		String sql = "select * from test1 limit ?,?";
		RowMapper<User> rm = new RowMapper<User>() {


			public User mapRow(ResultSet rs, int rowNum) throws SQLException {
				User user = new User();
				user.setId(rs.getInt("id"));
				user.setUsername(rs.getString("username"));
				user.setPassword(rs.getString("password"));
				return user;
			}
			
		};
		return this.getJdbcTemplate().query(sql, rm,(pageNum-1)*prePageNum,prePageNum);
	}
}

3.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:util="http://www.springframework.org/schema/util"
       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/util 
		http://www.springframework.org/schema/util/spring-util.xsd
		http://www.springframework.org/schema/context 
		http://www.springframework.org/schema/context/spring-context.xsd">
	
	<bean id="accountService" class="com.array.tx.AccountService">
		<property name="accountDao" ref="accountDao"></property>
		<property name="template" ref="template"></property>
	</bean>
	
	<bean id="accountDao" class="com.array.tx.AccountDao">
		<property name="dataSource" ref="dataSource"></property>
	</bean>
	<!--事务 template -->
	<bean id="template" class="org.springframework.transaction.support.TransactionTemplate">
		<property name="transactionManager" ref="transactionManager"></property>
	</bean>
	<!-- transactionManager -->
	<bean id="transactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
		<property name="dataSource" ref="dataSource"></property>
	</bean>
	
	<!--jdbc dataSource  -->
	<bean class="org.springframework.jdbc.datasource.DriverManagerDataSource" id="dataSource">
		<property name="driverClassName" value="com.mysql.jdbc.Driver"></property>
		<property name="url" value="jdbc:mysql://localhost:3306/spring"></property>
		<property name="username" value="array"></property>
		<property name="password" value="array"></property>
	</bean>
	
	
</beans>

4.测试

public class App {
	public static void main(String[] args) {
		ApplicationContext act =new  ClassPathXmlApplicationContext("applicationContext.xml");
		UserDao userDao = (UserDao) act.getBean("userDao");
		/*User user = new User();
		user.setId(new Integer(5));
		user.setUsername("array3");
		user.setPassword("array3");
		userDao.save(user);*/
		//System.out.println(userDao.findUser(new Integer(1)));
		//userDao.delete(new Integer(7));
		//System.out.println(userDao.getCount());
		/*System.out.println(userDao.getAll());*/
		System.out.println(userDao.getPage(2, 4));
	}

jdbc.properties文件

jdbc.driver=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3306/spring
jdbc.username=array
jdbc.password=array

log4j.properties文件

log4j.rootLogger=off,console

### direct log messages to console ###
log4j.appender.console=org.apache.log4j.ConsoleAppender
log4j.appender.console.layout=org.apache.log4j.PatternLayout
log4j.appender.console.layout.ConversionPattern=%d %5p %c{1}:%L - %m%n