实现spring数据库中的管理员表(manager)的CRUD(增加、删除、修改、查询)操作
Spring、Spring-ioc、 DBUtils 、C3P0
新建maven项目spring003,导包
<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<groupId>com.tyschool</groupId>
<artifactId>spring003</artifactId>
<version>1.0-SNAPSHOT</version>
<dependencies>
<dependency>
<groupId>org.apache.logging.log4j</groupId>
<artifactId>log4j-core</artifactId>
<version>2.13.0</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-beans</artifactId>
<version>5.2.2.RELEASE</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-core</artifactId>
<version>5.2.2.RELEASE</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-expression</artifactId>
<version>5.2.2.RELEASE</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-context</artifactId>
<version>5.2.2.RELEASE</version>
</dependency>
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.12</version>
<scope>test</scope>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.11</version>
</dependency>
<dependency>
<groupId>com.mchange</groupId>
<artifactId>c3p0</artifactId>
<version>0.9.5.3</version>
</dependency>
<dependency>
<groupId>commons-dbutils</groupId>
<artifactId>commons-dbutils</artifactId>
<version>1.6</version>
</dependency>
</dependencies>
</project>
<!-- 配置数据源 -->
<bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource">
<!--连接数据库的必备信息-->
<property name="driverClass" value="com.mysql.cj.jdbc.Driver"></property>
<property name="jdbcUrl" value="jdbc:mysql://192.168.1.123:3306/spring?serverTimezone=UTC"></property>
<property name="user" value="root"></property>
<property name="password" value="Root12345"></property>
</bean>
<!--配置QueryRunner-->
<bean id="runner" class="org.apache.commons.dbutils.QueryRunner" scope="prototype">
<!--注入数据源-->
<constructor-arg name="ds" ref="dataSource"></constructor-arg>
</bean>
注意:
QueryRunner:SQL语句的操作对象,可以设置查询结果集的封装策略,线程安全。
import java.io.Serializable;
import java.util.Date;
public class Manager implements Serializable {
private int mid;
private String uname;
private String pword;
private Date zcsj;
public int getMid() {
return mid;
}
public void setMid(int mid) {
this.mid = mid;
}
public String getUname() {
return uname;
}
public void setUname(String uname) {
this.uname = uname;
}
public String getPword() {
return pword;
}
public void setPword(String pword) {
this.pword = pword;
}
public Date getZcsj() {
return zcsj;
}
public void setZcsj(Date zcsj) {
this.zcsj = zcsj;
}
@Override
public String toString() {
return "Manager{" +
"mid=" + mid +
", uname='" + uname + '\'' +
", pword='" + pword + '\'' +
", zcsj=" + zcsj +
'}';
}
}
IManagerDao.java
import com.tyschool.spring003.javabean.Manager;
import java.util.List;
public interface IManagerDao {
List<Manager> findAll();
Manager findById(int mid);
int addManager(Manager m);
int updateManager(Manager m);
int deleteManager(int mid);
int allCount();
List<Manager> findByUname(String uname);
}
ManagerDaoImpl.java
import com.tyschool.spring003.javabean.Manager;
import com.tyschool.spring003.spring003.manager.dao.IManagerDao;
import java.util.List;
public class ManagerDaoImpl implements IManagerDao {
public List<Manager> findAll() {
return null;
}
public Manager findById(int mid) {
return null;
}
public int addManager(Manager m) {
return 0;
}
public int updateManager(Manager m) {
return 0;
}
public int deleteManager(int mid) {
return 0;
}
public int allCount() {
return 0;
}
public List<Manager> findByUname(String uname) {
return null;
}
}
IManagerService.java
import com.tyschool.spring003.javabean.Manager;
import java.util.List;
public interface IManagerService {
List<Manager> findAll();
Manager findById(int mid);
int addManager(Manager m);
int updateManager(Manager m);
int deleteManager(int mid);
int allCount();
List<Manager> findByUname(String uname);
}
ManagerServiceImpl.java
import com.tyschool.spring003.javabean.Manager;
import com.tyschool.spring003.manager.service.IManagerService;
import java.util.List;
public class ManagerServiceImpl implements IManagerService {
public List<Manager> findAll() {
return null;
}
public Manager findById(int mid) {
return null;
}
public int addManager(Manager m) {
return 0;
}
public int updateManager(Manager m) {
return 0;
}
public int deleteManager(int mid) {
return 0;
}
public int allCount() {
return 0;
}
public List<Manager> findByUname(String uname) {
return null;
}
}
<!-- 配置DAO -->
<bean id="managerDaoImpl" class="com.tyschool.spring003.manager.dao.impl.ManagerDaoImpl" >
<property name="runner" ref="runner"></property>
</bean>
<!-- 配置Service -->
<bean id="managerServiceImpl" class="com.tyschool.spring003.manager.service.impl.ManagerServiceImpl">
<property name="managerDaoImpl" ref="managerDaoImpl"></property>
</bean>
import com.tyschool.spring003.javabean.Manager;
import com.tyschool.spring003.manager.dao.IManagerDao;
import com.tyschool.spring003.manager.dao.impl.ManagerDaoImpl;
import com.tyschool.spring003.manager.service.IManagerService;
import java.util.List;
public class ManagerServiceImpl implements IManagerService {
private IManagerDao managerDaoImpl;
public void setManagerDaoImpl(IManagerDao managerDaoImpl) {
this.managerDaoImpl = managerDaoImpl;
}
public List<Manager> findAll() {
return managerDaoImpl.findAll();
}
public Manager findById(int mid) {
return managerDaoImpl.findById(mid);
}
public int addManager(Manager m) {
return managerDaoImpl.addManager(m);
}
public int updateManager(Manager m) {
return managerDaoImpl.updateManager(m);
}
public int deleteManager(int mid) {
return managerDaoImpl.deleteManager(mid);
}
public int allCount() {
return managerDaoImpl.allCount();
}
public List<Manager> findByUname(String uname) {
return managerDaoImpl.findByUname(uname);
}
}
import com.tyschool.spring003.javabean.Manager;
import com.tyschool.spring003.manager.dao.IManagerDao;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import java.sql.SQLException;
import java.util.List;
public class ManagerDaoImpl implements IManagerDao {
private QueryRunner runner;
public void setRunner(QueryRunner runner) {
this.runner = runner;
}
public List<Manager> findAll() {
List<Manager> list=null;
try {
list=runner.query("select * from manager",new BeanListHandler<Manager>(Manager.class));
} catch (SQLException e) {
e.printStackTrace();
}
return list;
}
public Manager findById(int mid) {
Manager m=null;
try {
m=runner.query("select * from manager where mid=?",new BeanHandler<Manager>(Manager.class),mid);
} catch (SQLException e) {
e.printStackTrace();
}
return m;
}
public int addManager(Manager m) {
try {
runner.update("insert into manager(uname,pword,zcsj)values(?,?,?)",m.getUname(),m.getPword(),m.getZcsj());
} catch (SQLException e) {
e.printStackTrace();
}
return 0;
}
public int updateManager(Manager m) {
try {
runner.update("update manager set uname=?,pword=?,zcsj=? where mid=?",m.getUname(),m.getPword(),m.getZcsj(),m.getMid());
} catch (SQLException e) {
e.printStackTrace();
}
return 0;
}
public int deleteManager(int mid) {
try {
runner.update("delete from manager where mid=?",mid);
} catch (SQLException e) {
e.printStackTrace();
}
return 0;
}
public int allCount() {
int n=0;
try {
Number number=(Number)runner.query("select count(mid) from manager",new ScalarHandler());
n=number.intValue();
} catch (SQLException e) {
e.printStackTrace();
}
return n;
}
public List<Manager> findByUname(String uname) {
List<Manager> list=null;
try {
list=runner.query("select * from manager where uname like ?",new BeanListHandler<Manager>(Manager.class),uname);
} catch (SQLException e) {
e.printStackTrace();
}
return list;
}
}
BeanFactory.java
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
public class BeanFactory {
private static ApplicationContext context=new ClassPathXmlApplicationContext("applicationContext.xml");;
private BeanFactory(){
}
public static Object getBean(String snameBean) {
Object iss = context.getBean(snameBean);
return iss;
}
}
import com.tyschool.spring003.javabean.Manager;
import com.tyschool.spring003.manager.service.IManagerService;
import com.tyschool.spring003.utils.BeanFactory;
import org.junit.Test;
import java.util.List;
public class STest {
private IManagerService ims= (IManagerService) BeanFactory.getBean("managerServiceImpl");
@Test
public void findAll(){
List<Manager> m =ims.findAll();
for(Manager m1:m){
System.out.println(m1);
}
}
}
@Test
public void findById(){
Manager m=ims.findById(1);
System.out.println(m);
}
@Test
public void addManager(){
Manager m=new Manager();
m.setUname("lihao");
m.setPword("lihao");
m.setZcsj(new Date());
ims.addManager(m);
}
@Test
public void updateManager(){
Manager m=ims.findById(1);
m.setUname("zhangniu");
ims.updateManager(m);
}
@Test
public void deleteManager(){
ims.deleteManager(1);
}
@Test
public void allCount(){
int n=ims.allCount();
System.out.println(n);
}
@Test
public void findByUname(){
List<Manager> m=ims.findByUname("li%");
for(Manager m1:m){
System.out.println(m1);
}
}