1.创建数据库脚本.db.sql
drop table emp; create table emp ( id integer primary key auto_increment, name varchar(20), password varchar(20), email varchar(20), birthday date, info varchar(100) );
2.编写实体类Emp.java
package cdl; import java.util.Date; public class Emp { private int id; private String name; private String password; private String email; private Date birthday; private String info; public Emp() { } public int getId() { return id; } public void setId(int id) { this.id = id; } public String getName() { return name; } public void setName(String name) { this.name = name; } public String getPassword() { return password; } public void setPassword(String password) { this.password = password; } public String getEmail() { return email; } public void setEmail(String email) { this.email = email; } public Date getBirthday() { return birthday; } public void setBirthday(Date birthday) { this.birthday = birthday; } public String getInfo() { return info; } public void setInfo(String info) { this.info = info; } }
3.编写DAO接口EmpDAO.java
package cdl; import java.util.List; public interface EmpDAO { /** * 添加雇员 * @param emp 要添加的雇员 * @throws Exception */ public void save(Emp emp) throws Exception; /** * 修改雇员 * @param emp 要修改的雇员 * @throws Exception */ public void update(Emp emp) throws Exception; /** * 根据雇员id删除雇员 * @param id 要删除雇员的id * @throws Exception */ public void delete(int id) throws Exception; /** * 根据雇员id查询雇员 * @param id 要查询雇员的id * @return 此id对应的雇员 * @throws Exception */ public Emp findById(int id) throws Exception; /** * 查询全部雇员 * @return 全部雇员的集合 * @throws Exception */ public List findAll() throws Exception; /** * 查询全部记录数字 * @return * @throws Exception */ public int findCount()throws Exception; }
4.编写DAO接口的实现类EmpDAOImpl.java
package cdl; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Types; import java.util.List; import org.springframework.jdbc.core.RowMapper; import org.springframework.jdbc.core.support.JdbcDaoSupport; public class EmpDAOImpl extends JdbcDaoSupport implements EmpDAO { /** * 添加雇员 * @param emp 要添加的雇员 * @throws Exception */ public void save(Emp emp) throws Exception { String sql="insert into emp (name,password,email,birthday,info)values(?,?,?,?,?)"; Object args[]={emp.getName(),emp.getPassword(),emp.getEmail(),emp.getBirthday(),emp.getInfo()}; this.getJdbcTemplate().update(sql, args); //为参数指定数据类型 //int argTypes[]={Types.VARCHAR,Types.VARCHAR,Types.VARCHAR,Types.DATE,Types.VARCHAR}; //this.getJdbcTemplate().update(sql, args, argTypes); } /** * 修改雇员 * @param emp 要修改的雇员 * @throws Exception */ public void update(Emp emp) throws Exception { String sql="update emp set name=?,password=?,email=?,birthday=?,info=? where id=?"; Object args[]={emp.getName(),emp.getPassword(),emp.getEmail(),emp.getBirthday(),emp.getInfo(),emp.getId()}; this.getJdbcTemplate().update(sql, args); //为参数指定数据类型 //int argTypes[]={Types.VARCHAR,Types.VARCHAR,Types.VARCHAR,Types.DATE,Types.VARCHAR,Types.INTEGER} //this.getJdbcTemplate().update(sql, args,argTypes); } /** * 根据雇员id删除雇员 * @param id 要删除雇员的id * @throws Exception */ public void delete(int id) throws Exception { String sql="delete from emp where id=?"; Object args[]={id}; this.getJdbcTemplate().update(sql, args); //为参数指定数据类型 //int argTypes[]={Types.INTEGER}; //this.getJdbcTemplate().update(sql, args, argTypes); } /** * 根据雇员id查询雇员 * @param id 要查询雇员的id * @return 此id对应的雇员 * @throws Exception */ public Emp findById(int id) throws Exception { String sql="select * from emp where id=?"; Object args[]={id}; List list=this.getJdbcTemplate().query(sql, args, new RowMapper(){ public Object mapRow(ResultSet rs, int rowNum) throws SQLException { //rowNum没有用上 Emp emp=new Emp(); emp.setId(rs.getInt("id")); emp.setName(rs.getString("name")); emp.setPassword(rs.getString("password")); emp.setEmail(rs.getString("email")); emp.setBirthday(rs.getDate("birthday")); emp.setInfo(rs.getString("info")); return emp; } }); // if(list.size()>0){ // return (Emp)list.get(0); // } // return null; return list.size()>0?(Emp)list.get(0):null; } /** * 查询全部雇员 * @return 全部雇员的集合 * @throws Exception */ public List findAll() throws Exception { String sql="select * from emp"; List list=this.getJdbcTemplate().query(sql, new RowMapper(){ public Object mapRow(ResultSet rs, int rowNum) throws SQLException { Emp emp=new Emp(); emp.setId(rs.getInt("id")); emp.setName(rs.getString("name")); emp.setPassword(rs.getString("password")); emp.setEmail(rs.getString("email")); emp.setBirthday(rs.getDate("birthday")); emp.setInfo(rs.getString("info")); return emp; } }); //return list; return list.size()>0?list:null; } /** * 查询全部记录数字 * @return * @throws Exception */ public int findCount()throws Exception{ String sql="select count(id) from emp"; return this.getJdbcTemplate().queryForInt(sql); // // String sql="select count(id) from emp where id=?"; // Object args[]={1}; // return this.getJdbcTemplate().queryForInt(sql, args); // String sql="select count(id) from emp where id=?"; // Object args[]={1}; // int argTypes[]={Types.INTEGER}; // return this.getJdbcTemplate().queryForInt(sql, args, argTypes); // String sql="select count(id) from emp where id=?"; // Object args[]={1}; // Class<Integer>requiredType=Integer.class; // return (Integer)this.getJdbcTemplate().queryForObject(sql, args, requiredType); } }
5.编写Spring的配置文件applicationContext.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:aop="http://www.springframework.org/schema/aop" xmlns:tx="http://www.springframework.org/schema/tx" xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-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.5.xsd"> <!-- DataSource --> <bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource" destroy-method="close"> <property name="driverClassName" value="com.mysql.jdbc.Driver"/> <property name="url" value="jdbc:mysql://localhost:3306/cdl"/> <property name="username" value="root"/> <property name="password" value="root"/> </bean> <!-- DAO --> <bean id="empDAO" class="cdl.EmpDAOImpl"> <property name="dataSource" ref="dataSource"/> </bean> </beans>
6.编写测试类Test.java
package cdl; import java.util.Date; import org.springframework.context.ApplicationContext; import org.springframework.context.support.ClassPathXmlApplicationContext; public class Test { public static void main(String[] args) throws Exception { ApplicationContext ctx = new ClassPathXmlApplicationContext("applicationContext.xml"); // 测试数据源 // DataSource dataSource=(DataSource)ctx.getBean("dataSource"); // System.out.println(dataSource.getConnection()); EmpDAO dao = (EmpDAO) ctx.getBean("empDAO"); // 测试save()方法 // Emp emp=new Emp(); // emp.setName("root"); // emp.setPassword("123456"); // emp.setEmail("516673731@qq.com"); // emp.setBirthday(new Date()); // emp.setInfo("I like Java"); // dao.save(emp); // 测试update()方法 // Emp emp=new Emp(); // emp.setId(5); // emp.setName("sun"); // emp.setPassword("654321"); // emp.setEmail("7890@163.com"); // emp.setBirthday(new Date()); // emp.setInfo("I like Java very much!!!"); // dao.update(emp); // 测试delete()方法 // dao.delete(5); // 测试findById()方法 // Emp emp = dao.findById(1); // System.out.println(emp.getId() + " " + emp.getName() + " " // + emp.getPassword() + " " + emp.getEmail() + " " // + emp.getBirthday() + " " + emp.getInfo()); // 测试findAll()方法 // List list = dao.findAll(); // for (int i = 0; i < list.size(); i++) { // Emp emp = (Emp) list.get(i); // System.out.println(emp.getId() + " " + emp.getName() + " " // + emp.getPassword() + " " + emp.getEmail() + " " // + emp.getBirthday() + " " + emp.getInfo()); // } //测试findCount()方法 System.out.println("记录数："+dao.findCount()); } }
附：
