用了一段时间的spring这,闲来没事做一下spring整合jdbc
目录文件
导入jar包
由于spring的jar包是在myeclipse中自动导入的有些暂时用不到的也没有处理。
Emp类
package com.v2uc.entity; import java.util.Date; public class Emp { private Integer empno; private String ename; private String job; private Integer mgr; private Date hiredate; private Double sal; private Double comm; private Integer deptno; public Integer getEmpno() { return empno; } public void setEmpno(Integer empno) { this.empno = empno; } public String getEname() { return ename; } public void setEname(String ename) { this.ename = ename; } public String getJob() { return job; } public void setJob(String job) { this.job = job; } public Integer getMgr() { return mgr; } public void setMgr(Integer mgr) { this.mgr = mgr; } public Date getHiredate() { return hiredate; } public void setHiredate(Date hiredate) { this.hiredate = hiredate; } public Double getSal() { return sal; } public void setSal(Double sal) { this.sal = sal; } public Double getComm() { return comm; } public void setComm(Double comm) { this.comm = comm; } public Integer getDeptno() { return deptno; } public void setDeptno(Integer deptno) { this.deptno = deptno; } }
映射文件EmpMapping
package com.v2uc.entity; import java.sql.ResultSet; import java.sql.SQLException; import org.springframework.jdbc.core.RowMapper; public class EmpMapping implements RowMapper<Emp> { public Emp mapRow(ResultSet rs, int rowIndex) throws SQLException{ Emp emp = new Emp(); emp.setEmpno(rs.getInt("EMPNO")); emp.setEname(rs.getString("ENAME")); emp.setJob(rs.getString("job")); emp.setMgr(rs.getInt("MGR")); emp.setHiredate(rs.getDate("HIREDATE")); emp.setSal(rs.getDouble("SAL")); emp.setComm(rs.getDouble("COMM")); emp.setDeptno(rs.getInt("DEPTNO")); return emp; } }
EmpDao接口
package com.v2uc.dao; import java.util.List; import com.v2uc.entity.Emp; public interface EmpDao { public void save(Emp emp); public void update(Emp emp); public void delete(int no); public Emp findByNo(int no); public List<Emp> findAll(); }
JdbcEmpDao实现
package com.v2uc.dao; import java.util.List; import javax.activation.DataSource; import org.springframework.jdbc.core.RowMapper; import org.springframework.jdbc.core.support.JdbcDaoSupport; import com.v2uc.entity.Emp; import com.v2uc.entity.EmpMapping; public class JdbcEmpDao extends JdbcDaoSupport implements EmpDao { private DataSource dataSource; public void setDataSource(DataSource dataSource){ this.dataSource = dataSource; } @Override public void save(Emp emp) { // TODO Auto-generated method stub } @Override public void update(Emp emp) { // TODO Auto-generated method stub } @Override public void delete(int no) { // TODO Auto-generated method stub } @Override public Emp findByNo(int no) { // TODO Auto-generated method stub return null; } @Override public List<Emp> findAll() { String sql = "select * from t_emp"; RowMapper<Emp> mapper = new EmpMapping(); List<Emp> list = super.getJdbcTemplate().query(sql,mapper); return list; } }
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:p="http://www.springframework.org/schema/p" xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-4.1.xsd"> <bean id="myDataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close"> <property name="driverClassName" value="com.mysql.jdbc.Driver" ></property> <property name="url" value="jdbc:mysql://127.0.0.1:3306/test"></property> <property name="username" value="root"></property> <property name="password" value=""></property> </bean> <bean id="jdbcEmpDao1" class="com.v2uc.dao.JdbcEmpDao"> <property name="dataSource" ref="myDataSource"></property> </bean> </beans>
Test单元测试
package com.v2uc.test; import java.util.List; import org.springframework.context.ApplicationContext; import org.springframework.context.support.ClassPathXmlApplicationContext; import com.v2uc.dao.EmpDao; import com.v2uc.entity.Emp; public class Test { @Test public void test() { String conf = "applicationContext.xml"; ApplicationContext ac = new ClassPathXmlApplicationContext(conf); EmpDao empDao = ac.getBean("jdbcEmpDao1",EmpDao.class); List<Emp> list = empDao.findAll(); for(Emp emp : list){ System.out.println(emp.getEname()); } } }
遇到问题
mysql 创建连接是 Cannot create PoolableConnectionFactory (Unknown character set: 'utf8mb4')
Cannot create PoolableConnectionFactory (Unknown character set: 'utf8mb4')
maven 依赖换版本 <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <version>5.1.6</version> </dependency>
更换5.1.6的jar包即可解决该问题