分类: Java
2008-03-23 20:52:02
#数据库操作接口及其实现 package com.biaoflying; public interface IUserDAO { public void insert(User user); public User find(Integer id); } #UserDAO.java package com.biaoflying; import java.sql.Connection; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import javax.sql.DataSource; public class UserDAO implements IUserDAO { private DataSource dataSource; public void setDataSource(DataSource dataSource){ this.dataSource=dataSource; } public void insert(User user){ String name=user.getName(); int age=user.getAge().intValue(); Connection conn=null; Statement stmt=null; try{ conn=dataSource.getConnection(); stmt=conn.createStatement(); stmt.execute("INSERT INTO user(name,age)" + "VALUES('"+name+"',"+age+")"); }catch(SQLException e){ e.printStackTrace(); } finally{ if(stmt!=null){ try{ stmt.close(); }catch(SQLException e){ e.printStackTrace(); } } if(conn!=null){ try{ conn.close(); }catch(SQLException e){ e.printStackTrace(); } } } } public User find(Integer id){ Connection conn=null; Statement stmt=null; try{ conn=dataSource.getConnection(); stmt=conn.createStatement(); ResultSet result=stmt.executeQuery( "SELECT * FROM user WHERE id="+id.intValue() ); if(result.next()){ Integer i=new Integer(result.getInt(1)); String name=result.getString(2); Integer age=new Integer(result.getInt(3)); User user=new User(); user.setId(i); user.setName(name); user.setAge(age); return user; } }catch(SQLException e){ e.printStackTrace(); } finally{ if(stmt!=null){ try{ stmt.close(); }catch(SQLException e){ e.printStackTrace(); } } if(conn!=null){ try{ conn.close(); }catch(SQLException e){ e.printStackTrace(); } } } return null; } } |
#操作对象User package com.biaoflying; public class User { private Integer id,age; private String name; 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; } } |
#主程序 package com.biaoflying; import org.springframework.context.ApplicationContext; import org.springframework.context.support .FileSystemXmlApplicationContext; public class SpringDAODemo { /** * @param args */ public static void main(String[] args) { // TODO Auto-generated method stub ApplicationContext context= new FileSystemXmlApplicationContext( "beans-config.xml"); User user=new User(); user.setName("abio"); user.setAge(new Integer(22)); IUserDAO userDAO= (IUserDAO)context.getBean("userDAO"); userDAO.insert(user); user =userDAO.find(new Integer(1)); System.out.println("name: "+user.getName()); } } |
#配置文件beans-config.xml ""> //localhost:3306/demo |
#输出 name: abio |
.... .... |
package com.biaoflying; import java.util.Iterator; import java.util.List; import java.util.Map; import javax.sql.DataSource; import org.springframework.jdbc.core.JdbcTemplate; public class UserDAO2 implements IUserDAO { private JdbcTemplate jdbcTemplate; public void setDataSource(DataSource dataSource){ jdbcTemplate=new JdbcTemplate(dataSource); } public void insert(User user){ String name=user.getName(); int age=user.getAge().intValue(); jdbcTemplate.update("INSERT INTO user(name , age)" +"VALUES('"+name+"',"+age+")"); } public User find(Integer id){ List rows=jdbcTemplate.queryForList( "SELECT * FROM user"); Iterator it=rows.iterator(); if(it.hasNext()){ Map userMap=(Map)it.next(); Integer i=new Integer(userMap.get("id").toString()); String name=userMap.get("name").toString(); Integer age= new Integer(userMap.get("age").toString()); User user=new User(); user.setId(i); user.setName(name); user.setAge(age); return user; } return null; } } |
package com.biaoflying; import java.util.Iterator; import java.util.List; import java.util.Map; import java.sql.SQLException; import java.sql.PreparedStatement; import java.sql.Connection; import javax.sql.DataSource; import org.springframework.jdbc.core.JdbcTemplate; import org.springframework.jdbc.core.PreparedStatementCreator; public class UserDAO3 implements IUserDAO { private JdbcTemplate jdbcTemplate; public void setDataSource(DataSource dataSource){ jdbcTemplate=new JdbcTemplate(dataSource); } public void insert(User user){ final String name=user.getName(); final int age=user.getAge().intValue(); jdbcTemplate.update(new PreparedStatementCreator(){ public PreparedStatement createPreparedStatement( Connection con)throws SQLException{ String sql= "INSERT INTO user (name,age) VALUES(?,?)"; PreparedStatement ps= con.prepareStatement(sql); ps.setString(1, name); ps.setInt(2,age); return ps; } }); } public User find(Integer id){ List rows=jdbcTemplate.queryForList( "SELECT * FROM user"); Iterator it=rows.iterator(); if(it.hasNext()){ Map userMap=(Map)it.next(); Integer i=new Integer(userMap.get("id").toString()); String name=userMap.get("name").toString(); Integer age= new Integer(userMap.get("age").toString()); User user=new User(); user.setId(i); user.setName(name); user.setAge(age); return user; } return null; } } |
public void insert(User user){ final String name=user.getName(); final int age=user.getAge().intValue(); jdbcTemplate.update("INSERT INTO user (name,age) VALUES(?,?)", new PreparedStatementSetter(){ public void setValues(PreparedStatement ps)throws SQLException{ ps.setString(1,name); ps.setInt(2,age); } }); } |
jdbcTemplate.update("INSERT INTO user(name,age)" +"VALUES('"+name+"',"+age+")"); ) 或者使用占位符 jdbcTemplate.update("INSERT INTO user(name,age) VALUES(??)",new Object[]{user.getName(),user.getAge()}); ) |
public int[] insertUsers(final List users){ String sql="INSERT INTO user (name,age) VALUES(?,?)"; BatchPreparedStatementSetter setter= new BatchPreparedStatementSetter(){ public void setValues( PreparedStatement ps,int i) throws SQLException{ User user=(User)users.get(i); ps.setString(1,user.getName()); ps.setInt(2,user.getAge().intValue()); } public int getBatchSize(){ return users.size(); } }; return jdbcTemplate.batchUpdate(sql,setter); } |
#单个字段查询 jdbcTemplate.queryForInt("SELECT COUNT(*) FROM user") String name=(String)jdbcTemplate.queryForObject( "SELECT name FROM USER WHERE id=?", new Object[]{id}, java.lang.String.class ); #一次查询多个字段 List rows=jdbcTemplate.queryForList( "SELECT * FROM user WHERE id="+id.intValue() ); List中包含的是Map对象,可以使用如下的方式来取得: Iterator it=rows.iterator(); while(it.hasNext()){ Map userMap=(Map)it.next(); System.out.println(userMap.get("id")); System.out.println(userMap.get("name")); System.out.println(userMap.get("age")); } |
#RowCallbackHandler 在查询到数据之后返回数据之前,可以使用RowCallbackHandler对数据进行一些处理。 public User find(Integer id){ final User user=new User(); jdbcTemplate.query( "SELECT * FROM user WHERE id=?", new Object[]{id}, new RowCallbackHandler(){ public void processRow(ResultSet rs)throws SQLException{ user.setId(new Integer(rs.getInt("id"))); user.setName(rs.getString("name")); user.setAge(new Integer(rs.getInt("age"))); } } ); return user; } |
#如果一次查询返回多行结果,可以使用RowMapper. package com.biaoflying; import java.sql.ResultSet; import java.sql.SQLException; import org.springframework.jdbc.core.RowMapper; public class UserRowMapper implements RowMapper{ public Object mapRow(ResultSet rs, int rowNum)throws SQLException{ User user=new User(); user.setId(new Integer(rs.getInt("id"))); user.setName(rs.getString("name")); user.setAge(new Integer(rs.getInt("age"))); return user; } } #接着在查询的时候使用#org.springframework.jdbc.core.RowMapperResultReader(实现#了ResultReader接口) List users=jdbcTemplate.query( "SELECT * FROM user", new RowMapperResultReader(new UserRowMapper()) ); 传回的users中包含了从数据库中查询出来的结果,并已经封装为User类实例 |