Spring对Orace数据库进行的建表，建序列、建引擎、增、删、查、改：

电玩女神 2022-08-14 01:57 36阅读 0赞

1、接口

package com.spring.jdbcDAO;
    
    import java.util.List;
    
    import org.springframework.jdbc.support.KeyHolder;
    
    import com.spring.jdbcModel.User;
    
    public interface UserDAO {
    	/**
    	 * 创建表
    	 */
    	public void create(String tableName);
    	/**
    	 * 保存用户
    	 * @param user
    	 */
    	public void save(User user);
    	/**
    	 * 创建序列和引擎，实现主键自增
    	 * @param seq
    	 */
    	public void incrementdKey(String seq);
    	
    	/**
    	 * 插入记录并返回自动生成的主键Id
    	 * @param user
    	 * @return
    	 */
    	KeyHolder insertUser(User user);
    	
    	/**
    	 * 插入/更新/删除数据
    	 * @param sql  有参数语句
    	 * @param obj  参数值数组
    	 * @return
    	 */
    	public int operatorUser(String sql,Object[] obj);
    	
    	/**
    	 * 根据SQL查询记录总数
    	 * @param sql
    	 * @return
    	 */
    	public int findRowBySQL(String sql);
    	
    	/**
    	 * 使用JdbcTemplate,根据id查找指定对象
    	 * @param id
    	 * @return
    	 */
    	public User findUserById(int id);
    	
    	/**
    	 * 返回所有的用户
    	 * @return
    	 */
        public List findAllUsers();
        
        /**
         * 使用JdbcTemplate进行批量更新
         * @param users
         * @return
         */
        public int[]updateBatchUser(final List users);
        
        /**
         * 根据id查找,使用SimpleJdbcTemplate
         * @param id
         * @return
         */
        public User findUserByIdSimple(int id);
        
        /**
         * 批量更新，这里使用SimpleJdbcTemplate  支持泛型
         * @param users
         * @return
         */
        public int[] updateBatchUsersSimple(final List<User> users);
        
        /**
         * 删除用户
         * @param user
         */
        public void delete(User user) ;
        
        
        /**
         * 通过RowCallbackHandler对Select语句得到的每行记录进行解析，并为其创建一个User数据对象。实现了手动的OR映射。  
         * @param id
         * @return
         */
        public User findUserByIdRowCallbackHandler(int id);
    }

2、接口实现类

package com.spring.jdbcDAOImpl;
    
    
    
    public class UserDAOImpl implements UserDAO {
    
    	private JdbcTemplate jdbcTemplate;
    
    	// SimpleJdbcTemplate对JdbcTemplate封装,某些特性要在java5以上才工作
    	private SimpleJdbcTemplate simpleJdbcTemplate;
    
    	// NamedParameterJdbcTemplate对JdbcTemplate封装，增加了命名参数特性
    	private NamedParameterJdbcTemplate namedParameterJdbc;
    
    	// 注意如果这样写的话，配置文件中应该怎样写
    	/*
    	 * public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
    	 * this.jdbcTemplate = jdbcTemplate; }
    	 */
    
    	public void setDataSource(DataSource dataSource) {
    		this.jdbcTemplate = new JdbcTemplate(dataSource);
    		this.namedParameterJdbc = new NamedParameterJdbcTemplate(dataSource);
    		this.simpleJdbcTemplate = new SimpleJdbcTemplate(dataSource);
    	}
    
    	/**
    	 * 创建表、序列、引擎、实现主键自动增长
    	 */
    	@Override
    	public void create(String tableName) {
    		jdbcTemplate
    				.execute("create table "
    						+ tableName
    						+ "(id integer,user_name varchar2(40),password varchar2(40),primary key (id))");
    	}
    
    	// 创建序列
    	public void incrementdKey(String seq) {
    		String sql = "create sequence " + seq + " start with 8"
    				+ "increment by 1" + "minvalue 1" + "maxvalue 999999"
    				+ "nocycle nocache";
    		jdbcTemplate.execute(sql);
    
    		// 实现主键的自增
    		String sql1 = "insert into td(id) values (seq.NEXTVAL)";
    		jdbcTemplate.execute(sql1);
    
    		// 创建引擎
    		String sql2 = "create  trigger tri_user before insert on td for each row begin select seq.NEXTVAL into :new.id from dual;end;";
    		jdbcTemplate.execute(sql2);
    
    	}
    
    	// jdbcTemplate.update适合于insert 、update和delete操作；
    
    	/**
    	 * 第一个参数为执行sql 第二个参数为参数数据库 第三个参数为参数类型
    	 */
    	@Override
    	public void save(User user) {
    		// Assert.isNull(user, "user is not null");
    		jdbcTemplate.update(
    				"insert into td(id,user_name,password) values(?,?,?)",
    				new Object[] { user.getId(), user.getUser_name(),
    						user.getPassword() }, new int[] {
    						java.sql.Types.INTEGER, java.sql.Types.VARCHAR,
    						java.sql.Types.VARCHAR });
    	}
    
    	/**
    	 * 插入记录并返回自动生成的主键Id
    	 */
    	@Override
    	public KeyHolder insertUser(final User user) {
    		final String addSQL = "insert into td(user_name,password) values(?,?)";
    		KeyHolder keyHolder = new GeneratedKeyHolder();
    		jdbcTemplate.update(new PreparedStatementCreator() {
    
    			@Override
    			public PreparedStatement createPreparedStatement(Connection conn)
    					throws SQLException {
    				PreparedStatement ps = conn.prepareStatement(addSQL,
    						new String[] { "id" });
    				ps.setString(1, user.getUser_name());
    				ps.setString(2, user.getPassword());
    				return ps;
    			}
    		});
    		return keyHolder;
    	}
    
    	/**
    	 * 插入/更新/删除数据
    	 */
    	@Override
    	public int operatorUser(String sql, Object[] obj) {
    		return jdbcTemplate.update(sql, obj);
    	}
    
    	/**
    	 * 根据SQL查询记录总数
    	 */
    	@Override
    	public int findRowBySQL(String sql) {
    		return jdbcTemplate.queryForInt(sql);
    	}
    
    	/**
    	 * 根据id查找指定对象,,,注意这里的写法
    	 */
    	@Override
    	public User findUserById(int id) {
    		User user = (User) jdbcTemplate.queryForObject(
    				"select id,user_name,password from td where id=?",
    				new Object[] { new Integer(id) }, new RowMapper() {
    					@Override
    					public Object mapRow(ResultSet rs, int rowNum)
    							throws SQLException {
    						User u = new User();
    						u.setId(rs.getInt("id"));
    						u.setUser_name(rs.getString("user_name"));
    						u.setPassword(rs.getString("password"));
    						return u;
    					}
    
    				});
    		return user;
    	}
    
    	/**
    	 * 
    	 * @author 
    	 * @date 2014年11月21日 上午9:41:44
    	 * @ClassName UserDAOImpl.java
    	 * @description 定义一个静态内部类，在DAO的方法中共享,,,这样就不用每次都需要实现回调函数
    	 */
    	private final static class UserMapper implements RowMapper {
    
    		@Override
    		public Object mapRow(ResultSet rs, int rowNum) throws SQLException {
    			User u = new User();
    			u.setId(rs.getInt("id"));
    			u.setUser_name(rs.getString("user_name"));
    			u.setPassword(rs.getString("password"));
    			return u;
    		}
    
    	}
    
    	/**
    	 * 返回所有的用户
    	 */
    	@Override
    	public List findAllUsers() {
    		return jdbcTemplate.query("select id,user_name,password from td",
    				new UserMapper());
    	}
    
    	/**
    	 * 批量更新
    	 */
    	@Override
    	public int[] updateBatchUser(final List users) {
    		int[] updateCounts = jdbcTemplate.batchUpdate(
    				"update td set user_name=?,password=? where id=?",
    				new BatchPreparedStatementSetter() {
    					@Override
    					public int getBatchSize() {
    						// TODO Auto-generated method stub
    						return users.size();
    					}
    
    					@Override
    					public void setValues(PreparedStatement ps, int i)
    							throws SQLException {
    						ps.setString(1, ((User) users.get(i)).getUser_name());
    						ps.setString(2, ((User) users.get(i)).getPassword());
    						ps.setInt(3, ((User) users.get(i)).getId());
    					}
    				});
    		return updateCounts;
    	}
    
    	/**
    	 * 通过SimpleJdbcTemplate，根据id查找
    	 * ParameterizedRowMapper和RowMapper的最大区别是ParameterizedRowMapper支持泛型
    	 */
    
    	@Override
    	public User findUserByIdSimple(int id) {
    		String sql = "select id,user_name,password from td where id=?";
    		ParameterizedRowMapper mapper = new ParameterizedRowMapper() {
    			@Override
    			public Object mapRow(ResultSet rs, int rowNum) throws SQLException {
    				User u = new User();
    				u.setId(rs.getInt("id"));
    				u.setPassword(rs.getString("password"));
    				u.setUser_name(rs.getString("user_name"));
    				return u;
    			}
    		};
    		// 这里的第三个参数 arguments to bind to the query
    		return this.simpleJdbcTemplate.queryForObject(sql, mapper, id);
    	}
    
    	/**
    	 * 批量更新,使用SimpleJdbcTemplate ，可以支持泛型
    	 */
    	@Override
    	public int[] updateBatchUsersSimple(List<User> users) {
    		List<Object[]> batch = new ArrayList<Object[]>();
    		for (User user : users) {
    			Object[] values = new Object[] { user.getUser_name(),
    					user.getPassword(), user.getId() };
    			batch.add(values);
    		}
    		int[] updateCounts = this.simpleJdbcTemplate.batchUpdate(
    				"update td set user_name=?,password=? where id=?", batch);
    		return updateCounts;
    	}
    
    	/**
    	 * 删除用户
    	 */
    	@Override
    	public void delete(User user) {
    		jdbcTemplate.update("delete from td where id=?",
    				new Object[] { user.getId() },
    				new int[] { java.sql.Types.INTEGER });
    	}
    
    	/**
    	 * 通过RowCallbackHandler对Select语句得到的每行记录进行解析，并为其创建一个User数据对象。实现了手动的OR映射。
    	 */
    	@Override
    	public User findUserByIdRowCallbackHandler(int id) {
    		final User user = new User();
    		this.jdbcTemplate.query("select * from td where id=?",
    				new Object[] { id }, new RowCallbackHandler() {
    					// 注意这里的返回类型是void
    					@Override
    					public void processRow(ResultSet rs) throws SQLException {
    						user.setId(rs.getInt("id"));
    						user.setUser_name(rs.getString("user_name"));
    						user.setPassword(rs.getString("password"));
    					}
    				});
    		return user;
    	}
    }

3、配置文件

<?xml version="1.0" encoding="UTF-8"?>
    <beans xmlns="http://www.springframework.org/schema/beans"
    	xmlns:context="http://www.springframework.org/schema/context" xmlns:tx="http://www.springframework.org/schema/tx"
    	xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:aop="http://www.springframework.org/schema/aop"
    
    	xsi:schemaLocation="http://www.springframework.org/schema/beans
            http://www.springframework.org/schema/beans/spring-beans-2.5.xsd
            http://www.springframework.org/schema/conte xt
            http://www.springframework.org/schema/context/spring-context-2.5.xsd
            http://www.springframework.org/schema/tx
            http://www.springframework.org/schema/tx/spring-tx-2.5.xsd
            http://www.springframework.org/schema/aop
            http://www.springframework.org/schema/aop/spring-aop-2.0.xsd">
    
    	
    	<bean id="dataSource"
    		class="org.springframework.jdbc.datasource.DriverManagerDataSource"
    		destroy-method="close">
    		<property name="driverClassName" value="oracle.jdbc.driver.OracleDriver"></property>
    		<property name="url" value="jdbc:oracle:thin:@localhost:1521:orcl"></property>
    		<property name="username" value="scott"></property>
    		<property name="password" value="123"></property>
    	</bean>
    
    
    	
    	
    
    
    	
    	<bean id="userDAO" class="com.spring.jdbcDAOImpl.UserDAOImpl">
    		<property name="dataSource" ref="dataSource"></property>
    	</bean>
    </beans>

4、测试

package com.spring.jdbcTest;
    
    import static org.junit.Assert.*;
    
    import java.util.ArrayList;
    import java.util.List;
    
    import org.junit.Test;
    import org.springframework.context.ApplicationContext;
    import org.springframework.context.support.ClassPathXmlApplicationContext;
    import org.springframework.jdbc.support.GeneratedKeyHolder;
    import org.springframework.jdbc.support.KeyHolder;
    
    import com.spring.jdbcDAO.UserDAO;
    import com.spring.jdbcModel.User;
    
    public class jdbcTest {
    	@Test
    	public void test() {
    		ApplicationContext ctx = new ClassPathXmlApplicationContext(
    				"/beans.xml");
    		UserDAO userDAO = (UserDAO) ctx.getBean("userDAO");
    
    		/**
    		 * 创建表和序列号 ,已经创建之后就不能再创建了 e
    		 */
    		userDAO.create("td");
    		userDAO.incrementdKey("sssss");
    
    		/**
    		 * 插入数据，并且返回主键 e
    		 */
    		User u3 = new User();
    		u3.setUser_name("cwwww");
    		u3.setPassword("1234567");
    		KeyHolder keyHolder = userDAO.insertUser(u3);
    		System.out.println(keyHolder.getKey());
    
    		// 新增
    		/*
    		 * String addSql="insert into td(user_name,password)values(?,?)";
    		 * Object[]obj=new Object[]{"lisi","2222"};
    		 * System.out.println(userDAO.operatorUser(addSql, obj));
    		 */
    
    		/**
    		 * 查询记录总数
    		 */
    		String countSql = "select count(0) from td";
    		System.out.println(userDAO.findRowBySQL(countSql));
    
    		/**
    		 * 通过id查找 e
    		 */
    		User u4 = userDAO.findUserById(21);
    		System.out.println(u4.getUser_name());
    		System.out.println(u4.getPassword());
    
    		/**
    		 * 查找所有用户
    		 */
    		List<User> users = new ArrayList<User>();
    		for (Object obj : userDAO.findAllUsers()) {
    			User u = (User) obj;
    			System.out.println("id:" + u.getId() + " user_name:"
    					+ u.getUser_name() + " password:" + u.getPassword());
    			u.setPassword("114");
    			users.add(u);
    		}
    
    		/**
    		 * 使用JdbcTemplate进行批量更新 e
    		 */
    		userDAO.updateBatchUser(users);
    		for (Object obj : userDAO.findAllUsers()) {
    			User u = (User) obj;
    			System.out.println("id:" + u.getId() + " user_name:"
    					+ u.getUser_name() + " password:" + u.getPassword());
    		}
    
    		/**
    		 * 使用SimpleJdbcTemplate进行批量更新
    		 */
    		userDAO.updateBatchUsersSimple(users);
    		for (Object obj : userDAO.findAllUsers()) {
    			User u = (User) obj;
    			System.out.println("id:" + u.getId() + " user_name:"
    					+ u.getUser_name() + " password:" + u.getPassword());
    		}
    
    		/**
    		 * 使用SimpleJdbcTemplate，根据id查找用户 e
    		 */
    		User u5 = userDAO.findUserByIdSimple(16);
    		System.out.println(u5.getId() + ":" + u5.getUser_name() + ":"
    				+ u5.getPassword());
    
    		/**
    		 * 删除用户 e
    		 */
    		userDAO.delete(u5);
    
    		/**
    		 * 通过RowCallbackHandler对Select语句得到的每行记录进行解析，并为其创建一个User数据对象。实现了手动的OR映射。
    		 */
    		User u6 = userDAO.findUserByIdRowCallbackHandler(21);
    		System.out.println("id:" + u6.getId() + " user_name:"
    				+ u6.getUser_name() + " password:" + u6.getPassword());
    
    	}
    }

以上就是我实现