目标
- 数据库工具类 DBUtil
- 工具类测试
- 建包、接口、实现类架构
- 研究功能，画流程图
- 建表
数据库工具类
位置 com.neuedu.utils.DBUtil.java
连接数据库的方法 getConnection
public static Connection getConnection() {
Connection conn=null;
try {
//1)加载驱动
Class.forName("com.mysql.jdbc.Driver");
//2)连接准备
String url="jdbc:mysql://localhost:3306/scott";
String user="root";
String password="root";
//3)获取连接
conn=DriverManager.getConnection(url, user, password);
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
System.out.println(e.getMessage());
e.printStackTrace();
} catch (SQLException e) {
System.out.println(e.getMessage());
// TODO Auto-generated catch block
e.printStackTrace();
}
return conn;
}
关闭连接的方法 CloseConnection
public static void closeConnection(ResultSet rs, PreparedStatement pst, Connection conn) {
try {
if(null!=rs){
rs.close();
}
if(null!=pst){
pst.close();
}
if(null!=conn){
conn.close();
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
相关测试类
public static void main(String[] args) {
Connection conn=DBUtils.getConnection();
PreparedStatement pst=null;
ResultSet rs=null;
System.out.println(conn);
try {
pst=conn.prepareStatement("select * from dept");
rs=pst.executeQuery();
while(rs.next()){
System.out.println(rs.getString("dname"));
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
//关闭连接
DBUtils.closeConnection(rs,pst,conn);
}
}
建包
com.neuedu.dao---放接口
com.neuedu.dao.impl---放接口的实现类
image.png
流程图
image.png
建表语句
drop table if exists tab_user;
/*==============================================================*/
/* Table: tab_user */
/*==============================================================*/
create table tab_user
(
id varchar(32) not null,
username varchar(20),
password varchar(20),
emailvarchar(50),
role varchar(1),
birthday date,
primary key (id)
);
实体类
com.neuedu.pojo---放实体类
package com.neuedu.pojo;
/**
* 用户信息表
*
* @author wang.qj@neusoft.com
*
*/
public class TabUser {
// 用户主鍵id
private String id;
// 用户名
private String userName;
// 密码
private String password;
// 邮箱
private String email;
// 角色
private String role;
// 生日
private String birthday;
public String getId() {
return id;
}
public void setId(String 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 getEmail() {
return email;
}
public void setEmail(String email) {
this.email = email;
}
public String getRole() {
return role;
}
public void setRole(String role) {
this.role = role;
}
public String getBirthday() {
return birthday;
}
public void setBirthday(String birthday) {
this.birthday = birthday;
}
}
dao接口
package com.neuedu.dao;
import java.util.List;
import com.neuedu.pojo.TabUser;
/**
* 用户管理接口
* @author Administrator
*
*/
public interface IUserDao {
//添加用户方法
public int addUser(TabUser user);
//根据用户名密码查询用户信息，用于鉴权
public TabUser getUserByNameAndPwd(String name,String pwd);
//修改用户信息
public int updateUser(TabUser user);
//根据主鍵id删除用户信息
public int delUserById(String id);
//根据ID查询用户信息(因为id为主键，所以返回单用户)
public TabUser getUserById(String id);
//根据姓名查询用户信息(假设：用户名可以重复，如果用户名不可以重复就可以返回单用户TabUser)
public List<TabUser> getUserByName(String name);
//查询所有
public List<TabUser> getAll();
}
dao实现类
package com.neuedu.dao.impl;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.ParseException;
import java.util.ArrayList;
import java.util.List;
import com.neuedu.dao.IUserDao;
import com.neuedu.pojo.TabUser;
import com.neuedu.utils.DBUtils;
import com.neuedu.utils.DateUtils;
import com.neuedu.utils.UUIDGenerateUtils;
public class UserDaoImpl implements IUserDao {
// 前三个必写，用新写的工具类，后四个选写
@Override
public int addUser(TabUser user) {
// 获取连接
Connection conn = DBUtils.getConnection();
PreparedStatement pst = null;
int i = 0;
try {
// 假设用户输入的生日格式 为yyyy-MM-dd
// 把用户输入的字符串型的日期转换成java.sql.Date
// 考虑到以后会经常用到，所以做一个工具类
java.sql.Date birthday = DateUtils.getDateFromStr(user.getBirthday());
// 预处理语句
pst = conn.prepareStatement("insert into tab_user values(?,?,?,?,?,?)");
pst.setString(1, UUIDGenerateUtils.getUUID());
pst.setString(2, user.getUserName());
pst.setString(3, user.getPassword());
pst.setString(4, user.getEmail());
pst.setString(5, user.getRole());
pst.setDate(6, birthday);
i = pst.executeUpdate();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (ParseException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally {
DBUtils.closeConnection(null, pst, conn);
}
return i;
}
@Override
public int updateUser(TabUser user) {
// 获取连接
Connection conn = DBUtils.getConnection();
PreparedStatement pst = null;
int i = 0;
try {
// 假设用户输入的生日格式 为yyyy-MM-dd
// 把用户输入的字符串型的日期转换成java.sql.Date
// 考虑到以后会经常用到，所以做一个工具类
java.sql.Date birthday = DateUtils.getDateFromStr(user.getBirthday());
// 预处理语句
pst = conn.prepareStatement(
"update tab_user set userName=?,password=?,email=?,role=?,birthday=? where id=? ");
pst.setString(1, user.getUserName());
pst.setString(2, user.getPassword());
pst.setString(3, user.getEmail());
pst.setString(4, user.getRole());
pst.setDate(5, birthday);
pst.setString(6, user.getId());
i = pst.executeUpdate();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (ParseException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally {
DBUtils.closeConnection(null, pst, conn);
}
return i;
}
@Override
public int delUserById(String id) {
// 获取连接
Connection conn = DBUtils.getConnection();
PreparedStatement pst = null;
int i = 0;
try {
// 预处理语句
pst = conn.prepareStatement("delete from tab_user where id=?");
pst.setString(1, id);
i = pst.executeUpdate();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally {
DBUtils.closeConnection(null, pst, conn);
}
return i;
}
@Override
public TabUser getUserById(String id) {
// 获取连接
Connection conn = DBUtils.getConnection();
PreparedStatement pst = null;
ResultSet rs = null;
TabUser user = null;
try {
// 预处理语句
pst = conn.prepareStatement("select * from tab_user where id=?");
pst.setString(1, id);
rs = pst.executeQuery();
// 如果有查询结果，我们就拼装一个TabUser类型的对象，返回去
// if/while if返回一条，while 有可能多条 因为主键不会重复，所以用if
if (rs.next()) {
// 实例化对象 大容器
user = new TabUser();
user.setUserName(rs.getString("username"));
user.setId(rs.getString("id"));
user.setUserName(rs.getString("username"));
user.setPassword(rs.getString("password"));
user.setEmail(rs.getString("email"));
user.setRole(rs.getString("role"));
user.setBirthday(rs.getString("birthday"));
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally {
DBUtils.closeConnection(null, pst, conn);
}
return user;
}
//根据用户名查询，用户名可以重复，返回list
@Override
public List<TabUser> getUserByName(String name) {
// 获取连接
Connection conn = DBUtils.getConnection();
PreparedStatement pst = null;
ResultSet rs = null;
List<TabUser> userList=new ArrayList();
try {
// 预处理语句
pst = conn.prepareStatement("select * from tab_user where username=?");
pst.setString(1, name);
rs = pst.executeQuery();
// 如果有查询结果，我们就拼装一个TabUser类型的对象，返回去
// if/while if返回一条，while 有可能多条 因为主键不会重复，所以用if
while (rs.next()) {
// 实例化对象 大容器
TabUser user = new TabUser();
user.setId(rs.getString("id"));
user.setUserName(rs.getString("username"));
user.setPassword(rs.getString("password"));
user.setEmail(rs.getString("email"));
user.setRole(rs.getString("role"));
user.setBirthday(rs.getString("birthday"));
userList.add(user);
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally {
DBUtils.closeConnection(null, pst, conn);
}
return userList;
}
@Override
public List<TabUser> getAll() {
PreparedStatement pst = null;
ResultSet rs = null;
List<TabUser> userList=new ArrayList();
Connection conn=DBUtils.getConnection();
try {
pst=conn.prepareStatement("select * from tab_user");
rs=pst.executeQuery();
//有可能多列
while(rs.next()){
TabUser user=new TabUser();
user.setId(rs.getString("id"));
user.setUserName(rs.getString("username"));
user.setPassword(rs.getString("password"));
user.setEmail(rs.getString("email"));
user.setBirthday(rs.getString("birthday"));
user.setRole(rs.getString("role"));
userList.add(user);
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return userList;
}
@Override
public TabUser getUserByNameAndPwd(String name, String pwd) {
// 获取连接
Connection conn = DBUtils.getConnection();
PreparedStatement pst = null;
ResultSet rs = null;
TabUser user = null;
try {
// 预处理语句
pst = conn.prepareStatement("select * from tab_user where name=? and password=?");
pst.setString(1, name);
pst.setString(2, pwd);
rs = pst.executeQuery();
// if/while if返回一条，while 有可能多条 因为假设用户名不重复，所以用if
if (rs.next()) {
// 实例化对象 大容器
user = new TabUser();
user.setId(rs.getString("id"));
user.setUserName(rs.getString("username"));
user.setPassword(rs.getString("password"));
user.setEmail(rs.getString("email"));
user.setRole(rs.getString("role"));
user.setBirthday(rs.getString("birthday"));
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally {
DBUtils.closeConnection(null, pst, conn);
}
return user;
}
}
