目標
- 數(shù)據(jù)庫工具類 DBUtil
- 工具類測試
- 建包蚓再、接口摘仅、實現(xiàn)類架構
- 研究功能问畅，畫流程圖
- 建表
數(shù)據(jù)庫工具類
位置 com.neuedu.utils.DBUtil.java
連接數(shù)據(jù)庫的方法 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---放接口的實現(xiàn)類
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);
//根據(jù)用戶名密碼查詢用戶信息卵皂，用于鑒權
public TabUser getUserByNameAndPwd(String name,String pwd);
//修改用戶信息
public int updateUser(TabUser user);
//根據(jù)主鍵id刪除用戶信息
public int delUserById(String id);
//根據(jù)ID查詢用戶信息(因為id為主鍵灯变，所以返回單用戶)
public TabUser getUserById(String id);
//根據(jù)姓名查詢用戶信息(假設：用戶名可以重復，如果用戶名不可以重復就可以返回單用戶TabUser)
public List<TabUser> getUserByName(String name);
//查詢所有
public List<TabUser> getAll();
}
dao實現(xiàn)類
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;
}
//根據(jù)用戶名查詢颤陶，用戶名可以重復陷遮，返回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;
}
}
