项目:对user表实现增删改查
项目结构一览:
首先创建对应的user表
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`username` varchar(20) NOT NULL,
`password` varchar(20) NOT NULL,
`sex` varchar(10) DEFAULT NULL,
`realname` varchar(20) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8;
根据user表,创建对应的实体类(com.hpe.po.User),这里表字段名跟实体类属性名一致(按照bean的规范,get,set方法一定要有,还需要有无参构造方法,当mxl文件管理bean时候,根据无参构造方法创建对应的bean):
public class User {
private int id;
private String username;
private String password;
private String realname;
private String sex;
public User() {
super();
}
public User(int id, String username, String password, String realname, String sex) {
super();
this.id = id;
this.username = username;
this.password = password;
this.realname = realname;
this.sex = sex;
}
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getUsername() {
return username;
}
public void setUsername(String username) {
this.username = username;
}
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
public String getRealname() {
return realname;
}
public void setRealname(String realname) {
this.realname = realname;
}
public String getSex() {
return sex;
}
public void setSex(String sex) {
this.sex = sex;
}
@Override
public String toString() {
return "User [id=" + id + ", username=" + username + ", password=" + password + ", realname=" + realname
+ ", sex=" + sex + "]";
}
在com.hpe.dao下创建对数据库操作的接口:
public interface IUserDao {
User fndUserByNameAndPwd(User user);
int register(User user);
int checkUserName(String Username);
List<User> selectAll();
int deleteById(int id);
List<User> selectMohu(String username);
}
根据接口方法,实现接口(com.hpe.dao.impl):
package com.hpe.dao.impl;
import java.util.List;
import com.hpe.dao.IUserDao;
import com.hpe.po.User;
import com.hpe.util.DBUtil;
public class UserDaoImpl implements IUserDao{
private DBUtil db=new DBUtil();
@Override
public User fndUserByNameAndPwd(User user) {
String sql="select * from user where username=? and password=?";
Object[] params={user.getUsername(),user.getPassword()};
User us=db.getObject(sql, params,User.class);
return us;
}
@Override
public int register(User user) {
String sql="insert into user(username, password, sex, realname)values(?, ?,?,?)";
Object[] params={user.getUsername(),user.getPassword(),user.getSex(),user.getRealname()};
int res=db.update(sql, params);
return res;
}
@Override
public int checkUserName(String username) {
String sql="select * from user where username=? ";
Object[] params={username};
List<User> list= db.query(sql, params, User.class);
return list.size();
}
@Override
public List<User> selectAll() {
String sql="select * from user";
Object[] params={};
return db.query(sql, params, User.class);
}
@Override
public int deleteById(int id) {
String sql="delete from user where id=?";
Object[] params={id};
return db.update(sql, params);
}
@Override
public List<User> selectMohu(String username) {
String sql="select * from user where username like '%"+username+"%'";
Object[] params={};
List<User> list= db.query(sql, params, User.class);
return list;
}
}
dao.impl连接数据库方法多种多样,这里实用的是jdbc,将数据库连接封装在了工具类。
工具类一览:
package com.hpe.util;
import java.beans.BeanInfo;
import java.beans.IntrospectionException;
import java.beans.Introspector;
import java.beans.PropertyDescriptor;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import javax.management.Query;
public class DBUtil {
// 加载数据库驱动, 创建连接对象并返回
public Connection getConn() throws ClassNotFoundException, SQLException {
// 将指定名字的类, 通过类加载器(ClassLoader)加载到JVM(java 虚拟机)
Class.forName("com.mysql.jdbc.Driver");
//
String url = "jdbc:mysql://127.0.0.1:3306/mybatis?useUnicode=true&characterEncoding=utf-8";
String user = "root";
String pwd = "";
Connection conn = DriverManager.getConnection(url, user, pwd);
/*
*
* 1. 加载驱动 2. 创建连接对象 // 经过三次握手 3. 创建执行对象 4. 执行SQL 5. 处理返回结果 6. 释放资源 //
* 经过四次握手
*
* 数据连接池: 在一个容器中-> 池, 创建好一定数据的连接对象 , 应用程序需要连接对象 时, 直接从容器中取, 使用完毕之后,
* 再将连接对象放回到容器中, 而不是直接关闭.
*/
return conn;
}
// 释放资源 resultset statement connection
public void close(ResultSet rs, Statement stmt, Connection conn) {
try {
if (rs != null) {
rs.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
try {
if (stmt != null) {
stmt.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
try {
if (conn != null) {
conn.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
public Map getObject(String sql, Object[] params) {
// 获取查询结果列表
List<Map<String, Object>> list = query(sql, params);
// 只返回第一个
if (list.size() > 0) {
return list.get(0);
} else {
return null;
}
}
public <T> T getObject(String sql, Object[] params, Class<T> cls) {
// 查询出一条记录
Map map = getObject(sql, params);
// 将Map转换成实体类对象
return (T) toObj(map, cls);
}
// 获取多个对象, 并将每个对象保存到一个Map中, 并且将多个对象保存到List
public List<Map<String, Object>> query(String sql, Object[] params) {
Connection conn = null;
PreparedStatement stmt = null;
ResultSet rs = null;
List<Map<String, Object>> list = new ArrayList<>();
try {
conn = getConn();
//
stmt = conn.prepareStatement(sql);
// 当参数数组不为空时, 进行参数绑定, 此句必须有, 否则params.length会报NullPointerException
if (params != null) { //
for (int i = 0; i < params.length; i++) {
stmt.setObject(i + 1, params[i]);
}
}
//
rs = stmt.executeQuery();
//
while (rs.next()) {
// 由于 查询时, 可能针对的是不同的表, 不同的表结构是不一样的. 能不能有一种统一的数据类型, 能够接收这种可变的数据?
/*
* Map接口 以key-value的形式存放多个数据
*
*
* HashMap
*/
HashMap<String, Object> map = new HashMap<>();
int colCount = rs.getMetaData().getColumnCount();
for (int i = 1; i <= colCount; i++) {
String colName = rs.getMetaData().getColumnName(i);
Object value = rs.getObject(colName);
map.put(colName, value);
}
// 将单条记录的map放到一个数组中.
list.add(map);
}
// 返回数组
return list;
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
} finally {
close(rs, stmt, conn);
}
return null;
}
public <T> List<T> query(String sql, Object[] params, Class<T> cls) {
List<Map<String, Object>> list = query(sql, params);
List<T> res = new ArrayList<>();
for (Map<String, Object> map : list) {
T obj = toObj(map, cls);
res.add(obj);
}
return res;
}
private <T> T toObj(Map<String, Object> map, Class<T> cls) {
try {
// 实体类对象的创建
T obj = cls.newInstance();
// 将Map中的数据设置到obj中
// beanInfo中保存是类相关的信息
BeanInfo info = Introspector.getBeanInfo(cls);
// 获取类的每一个属性的信息
PropertyDescriptor[] properties = info.getPropertyDescriptors();
for (PropertyDescriptor prop : properties) {
// 获取属性的名字
String propName = prop.getName(); // -> name
// 获取属性的set方法
Method setter = prop.getWriteMethod(); // -> setName(String );
// 取出map中属性对应的值
Object value = map.get(propName);
if (value != null) {
// 调用 setter设置值
try {
// 实体类属性的设置
setter.invoke(obj, value);
} catch (IllegalArgumentException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (InvocationTargetException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
return obj;
} catch (InstantiationException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (IllegalAccessException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (IntrospectionException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return null;
}
public int update(String sql, Object[] params) {
Connection conn = null;
PreparedStatement stmt = null;
int res = 0;
ResultSet rs = null;
try {
conn = getConn();
//
stmt = conn.prepareStatement(sql, com.mysql.jdbc.Statement.RETURN_GENERATED_KEYS);
// 当参数数组不为空时, 进行参数绑定, 此句必须有, 否则params.length会报NullPointerException
if (params != null) { //
for (int i = 0; i < params.length; i++) {
stmt.setObject(i + 1, params[i]);
}
}
//
res = stmt.executeUpdate();
rs = stmt.getGeneratedKeys();
if (rs.next()) {
res = rs.getInt(1);
}
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
} finally {
close(rs, stmt, conn);
}
return res;
}
}
然后我们创建service,(com.hpe.service):
package com.hpe.service;
import java.util.List;
import com.hpe.po.User;
public interface IUserService {
User login(User user);
int register(User user);
List<User> selectAll();
int deleteById(int id);
List<User> selectMohu(String username);
}
实现service(com.hpe.service.impl.UserServiceImpl),注意这里我们在调用dao接口时候,不再是IUserDao iud=new UserDaoImpl,这种方式,而是把IUserDao交给配置文件管理,用bean的方式,根据set方法传参。
package com.hpe.service.impl;
import java.util.List;
import com.hpe.dao.IUserDao;
import com.hpe.dao.impl.UserDaoImpl;
import com.hpe.po.User;
import com.hpe.service.IUserService;
public class UserServiceImpl implements IUserService{
private IUserDao iud;//ioc控制反转,用配置文件管理IUserDao的实现类,所以这里要给出该变量,在配置文件实现
//set方法一定要有,ioc控制反转,配置文件管理bean,创建IUserDao的实体类,在property设置属性时候,会调动此处的set方法,将创建的
//IUserDao iud=new UserDaoImpl()实体类用set方法,也就是调用这里的setIud(),给此处声明的IUserDao iud赋值。
//对应配置文件:<bean id="userService" class="com.hpe.service.impl.UserServiceImpl">
//<property name="iud" ref="userDao"></property>
//</bean>
public void setIud(IUserDao iud) {
this.iud = iud;
}
@Override
public User login(User user) {
// TODO Auto-generated method stub
return iud.fndUserByNameAndPwd(user);
}
@Override
public int register(User user) {
// TODO Auto-generated method stub
return iud.register(user);
}
@Override
public List<User> selectAll() {
// TODO Auto-generated method stub
return iud.selectAll();
}
@Override
public int deleteById(int id) {
// TODO Auto-generated method stub
return iud.deleteById(id);
}
@Override
public List<User> selectMohu(String username) {
// TODO Auto-generated method stub
return iud.selectMohu(username);
}
}
是不是有点懵?看看配置文件就明白了
<?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"
xmlns:util="http://www.springframework.org/schema/util"
xsi:schemaLocation="
http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd
http://www.springframework.org/schema/util http://www.springframework.org/schema/util/spring-util.xsd
">
<!-- 注入bean,bean标签管理的类,相当于自动调用无参构造函数创建创建实体类,相当于new UserDaoImpl(),此处的id,是便于调用时候识别该bean,任意取名字,但是要有针对意义。 -->
<bean id="userDao" class="com.hpe.dao.impl.UserDaoImpl"></bean>
<!--注入应用类型 -->
<!-- 调用有参构造器构造 该bean管理的UserServiceImpl类,相当于new UserServiceImpl(property1,property2,...) -->
<bean id="userService" class="com.hpe.service.impl.UserServiceImpl">
<!-- 有参构造器构造参数描写,name是有参构造函数的参数名,value是参数值.此处参数值也是个类对象,所以我们用ref,为了是
ref等于的bean实体类创建对象能够代码复用,ref="某bean的id",id=userDao的bean,是创建UserDaoImpl
的无参构造方法对应的对象。
-->
<property name="iud" ref="userDao" ></property>
<!-- <property name="iud" value="com.hpe.dao.impl.UserDaoImpl" ></property> -->
</bean>
</beans>
最后,我们在com.hpe.test包下创建测试类,用junit测试
package com.hpe.test;
import java.util.List;
import org.junit.Test;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import com.hpe.dao.IUserDao;
import com.hpe.dao.impl.UserDaoImpl;
import com.hpe.po.User;
import com.hpe.service.IUserService;
public class UserTest {
@Test
public void method1()
{
//加载配置文件,初始化ioc容器(创建对应实例)
ApplicationContext context = new ClassPathXmlApplicationContext("applicationContext.xml");
//从ioc容器中获取bean
IUserService userService = context.getBean("userService", IUserService.class);
//通过getBean,读取userService id的IUserServiceImpl的实现类(在bean标签中,class设置为UserServiceImpl。所以获得该实现类)
//然后bean设置属性,《propert》标签,设置userServiceimpl中的属性字段userdao为userDaoImpl实现类的实体。
User user=new User();
user.setUsername("yjz");
user.setPassword("123");
System.out.println(userService.login(user));
}
@Test
public void method2()
{
//加载配置文件,初始化ioc容器(创建对应实例)
ApplicationContext context = new ClassPathXmlApplicationContext("applicationContext.xml");
//从ioc容器中获取bean
IUserService userService = context.getBean("userService", IUserService.class);
//通过getBean,读取userService id的IUserServiceImpl的实现类(在bean标签中,class设置为UserServiceImpl。所以获得该实现类)
//然后bean设置属性,《propert》标签,设置userServiceimpl中的属性字段userdao为userDaoImpl实现类的实体。
User user=new User();
user.setUsername("yjz");
user.setPassword("123");
List<User> list=userService.selectAll();
for(User u:list){
System.out.println(u);
}
}
@Test
public void method3()
{
//加载配置文件,初始化ioc容器(创建对应实例)
ApplicationContext context = new ClassPathXmlApplicationContext("applicationContext.xml");
//从ioc容器中获取bean
IUserService userService = context.getBean("userService", IUserService.class);
//通过getBean,读取userService id的IUserServiceImpl的实现类(在bean标签中,class设置为UserServiceImpl。所以获得该实现类)
//然后bean设置属性,《propert》标签,设置userServiceimpl中的属性字段userdao为userDaoImpl实现类的实体。
System.out.println(userService.deleteById(8));
}
@Test
public void method4()
{
//加载配置文件,初始化ioc容器(创建对应实例)
ApplicationContext context = new ClassPathXmlApplicationContext("applicationContext.xml");
//从ioc容器中获取bean
IUserService userService = context.getBean("userService", IUserService.class);
//通过getBean,读取userService id的IUserServiceImpl的实现类(在bean标签中,class设置为UserServiceImpl。所以获得该实现类)
//然后bean设置属性,《propert》标签,设置userServiceimpl中的属性字段userdao为userDaoImpl实现类的实体。
User user=new User();
user.setUsername("yjz222");
user.setPassword("123");
user.setRealname("asdasd");
user.setSex("男");
System.out.println(userService.register(user));;
}
@Test
public void method5()
{
//加载配置文件,初始化ioc容器(创建对应实例)
ApplicationContext context = new ClassPathXmlApplicationContext("applicationContext.xml");
//从ioc容器中获取bean
IUserService userService = context.getBean("userService", IUserService.class);
//通过getBean,读取userService id的IUserServiceImpl的实现类(在bean标签中,class设置为UserServiceImpl。所以获得该实现类)
//然后bean设置属性,《propert》标签,设置userServiceimpl中的属性字段userdao为userDaoImpl实现类的实体。
List<User> list=userService.selectMohu("y");
for(User u:list){
System.out.println(u);
}
}
}