1.配置环境
做这次的小项目使用到的语言是java,当然eclipse是必不可少的,另外还需要用到的有Tomcat,MySQL,mysql-connector-java的bin包和Navicat。其实Navicat可以不装,但是为了更方便看到我们的数据库,还是建议装一下。这里介绍一下Tomcat和mysql-connector-java的bin包的配置。
- Tomcat的配置
这里为了防止Tomcat出错,安装的是8.3版本的,先打开Preferences,然后执行如下操作
然后找到Tomcat的文件夹,最后Finish
- mysql-connector-java的bin包的配置
将mysql-connector-java的bin包拖到lib文件夹下,并右键build Path
2.大致框架的创建
我们首先创建了一个Dynamic Web Project,随后在这个java项目中创建了两个Source Folder,一个是默认的src,另一个是我们的测试代码。
在src中,我们建立四个包
- controller:放置控制servlet的class
- dao:放置控制数据库的class,其中还包含了imp包,里面放置dao的实现类
- entity:放置用户的实体类
- utils:放置工具类
创建完成后,视图应该是这样的
然后我们需要在entity包中写一个用户的实体类
需要提供的属性有id,userName,nickName,password,birth,imgs,gender,favorite,同时需要提供相应的get和set方法。无参的构造方法,全参的构造方法,无id的构造方法。
public class User {
private int id;
private String userName;
private String nickName;
private String password;
private Date birth;
private String imgs;
private char gender;
private String favorite;
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 getNickName() {
return nickName;
}
public void setNickName(String nickName) {
this.nickName = nickName;
}
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
public Date getBirth() {
return birth;
}
public void setBirth(Date birth) {
this.birth = birth;
}
public String getImgs() {
return imgs;
}
public void setImgs(String imgs) {
this.imgs = imgs;
}
public char getGender() {
return gender;
}
public void setGender(char gender) {
this.gender = gender;
}
public String getFavorite() {
return favorite;
}
public void setFavorite(String favorite) {
this.favorite = favorite;
}
public User(int id, String userName, String nickName, String password, Date birth, String imgs, char gender,
String favorite) {
super();
this.id = id;
this.userName = userName;
this.nickName = nickName;
this.password = password;
this.birth = birth;
this.imgs = imgs;
this.gender = gender;
this.favorite = favorite;
}
@Override
public String toString() {
return "User [id=" + id + ", userName=" + userName + ", nickName=" + nickName + ", password=" + password
+ ", birth=" + birth + ", imgs=" + imgs + ", gender=" + gender + ", favorite=" + favorite + "]";
}
public User() {
super();
}
public User(String userName, String nickName, String password, Date birth, String imgs, char gender,
String favorite) {
super();
this.userName = userName;
this.nickName = nickName;
this.password = password;
this.birth = birth;
this.imgs = imgs;
this.gender = gender;
this.favorite = favorite;
}
}
随后我们应该写的是dao包中的接口,这些接口是整个项目的灵魂。
在这个接口中,我们需要实现的功能有
- 往数据库中添加用户
- 通过用户名和密码查找用户,并返回这个用户
- 返回一个包含所有用户的List
- 通过id查找用户,并返回这个用户
public interface UserDao {
public int insert(User user);
public User select(int id);
public User select(String userName, String password);
public List<User> select();
}
3.Utils类的实现
Utils类中需要实现的方法有:
- 实现连接数据库
- 获取Connection
- 释放内存
- 通过forName来加载Driver驱动
public class DBUtil {
private static final String url = "jdbc:mysql:///test";
private static final String user = "root";
private static final String password = "root";
static {
try {
Class.forName("com.mysql.jdbc.Driver");
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
}
public static Connection getConnection() {
Connection conn = null;
try {
conn = DriverManager.getConnection(url, user, password);
} catch (SQLException e) {
e.printStackTrace();
}
return conn;
}
public static void free(Connection conn , Statement stmt, ResultSet rs){
try {
if(rs != null)
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}finally{
try {
if(stmt != null)
stmt.close();
} catch (SQLException e) {
e.printStackTrace();
}finally{
try {
if(conn != null)
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
}
4.Dao接口的实现
- 首先我们先实现插入功能
,步骤如下:
(1).通过Utils工具类创建连接
(2).通过Connection连接创建statement对象
(3).通过statement对象调用SQL语句
注意:调用SQL语句时,Date不能直接插入,需要用simpledateformat来格式化字符串
代码如下:
@Override
public int insert(User user) {
Connection conn = DBUtils.getConnection();
Statement stmt = null;
SimpleDateFormat sim = new SimpleDateFormat("yyyy-MM-dd");
try {
stmt = conn.createStatement();
} catch (SQLException e1) {
e1.printStackTrace();
}
String sql = "insert into tb_user(id, username, nickname, password, birth, imgs, gender, favorite) "
+ "values("
+ user.getId()
+ ", '"
+ user.getUserName()
+ "', '"
+ user.getNickName()
+ "', '"
+ user.getPassword()
+ "', '"
+ sim.format(user.getBirth())
+ "', '"
+user.getImgs()
+ "', '"
+ user.getGender()
+ "', '"
+ user.getFavorite() + "')";
try {
stmt.execute(sql);
} catch (SQLException e) {
e.printStackTrace();
}
DBUtils.free(conn, stmt, null);
return 0;
}
2 . 实现查找所有用户的接口,步骤如下:
(1).通过Utils工具类创建连接
(2).通过Connection连接创建statement对象
(3).创建List用于保存查找到的所有的用户
(4).使用statemrnt对象中的executeQuery()调用SQL语句,并返回一个ResultSet对象用于遍历所有用户
(5).将遍历到的所有的用户保存到list中,并返回这个List
代码如下
@Override
public List<User> select() throws SQLException {
Connection conn = DBUtil.getConnection();
Statement state = conn.createStatement();
ArrayList<User> list = new ArrayList<>();
String sql = "select * from tb_user";
ResultSet rs = state.executeQuery(sql);
while(rs.next()){
User user = new User();
int id = rs.getInt("id");
user.setId(id);
String username = rs.getString("username");
if (username != null)
user.setUserName(username);
String nickname = rs.getString("nickname");
if (nickname != null)
user.setNickName(nickname);
String pwd = rs.getString("password");
if (pwd != null)
user.setPassword(pwd);
Date birth = rs.getDate("birth");
if (birth != null)
user.setBirth(birth);
String imgs = rs.getString("imgs");
if (imgs != null)
user.setImgs(imgs);
String genderStr = rs.getString("gender");
if (genderStr != null && genderStr.length() > 0) {
char gender = genderStr.charAt(0);
user.setGender(gender);
}
String favorite = rs.getString("favorite");
if(favorite != null)
user.setFavorite(favorite);
list.add(user);
}
return list;
}
3 . 实现通过id查找用户
(1).通过Utils工具类创建连接
(2).通过Connection连接创建statement对象
(3).使用statemrnt对象中的executeQuery()调用SQL语句,并返回一个符合条件的ResultSet对象
(4).利用这个ResultSet对象将查找到的用户返回
代码如下:
@Override
public User select(int id) throws SQLException {
Connection conn = DBUtil.getConnection();
Statement state = conn.createStatement();
String sql = "select * from tb_user " + "where id='" + id +"' ";
ResultSet rs = state.executeQuery(sql);
User user = new User();
if (rs.next()) {
user = new User();
int aid = rs.getInt("id");
user.setId(aid);
String username = rs.getString("username");
if (username != null)
user.setUserName(username);
String nickname = rs.getString("nickname");
if (nickname != null)
user.setNickName(nickname);
String pwd = rs.getString("password");
if (pwd != null)
user.setPassword(pwd);
Date birth = rs.getDate("birth");
if (birth != null)
user.setBirth(birth);
String imgs = rs.getString("imgs");
if (imgs != null)
user.setImgs(imgs);
String genderStr = rs.getString("gender");
if (genderStr != null && genderStr.length() > 0) {
char gender = genderStr.charAt(0);
user.setGender(gender);
}
String favorite = rs.getString("favorite");
if(favorite != null)
user.setFavorite(favorite);
}
return user;
}
4.实现根据用户名和密码查找用户(其实和3差不多,只需要修改SQL语句就行了)
(1).通过Utils工具类创建连接
(2).通过Connection连接创建statement对象
(3).使用statemrnt对象中的executeQuery()调用SQL语句,并返回一个符合条件的ResultSet对象
(4).利用这个ResultSet对象将查找到的用户返回
代码如下:
@Override
public User select(String userName, String password) throws SQLException {
Connection conn = DBUtil.getConnection();
Statement state = conn.createStatement();
String sql = "select * from tb_user " + "where username='" + userName
+ "' and password='" + password + "'";
ResultSet rs = state.executeQuery(sql);
User user = null;
if (rs.next()) {
user = new User();
int id = rs.getInt("id");
user.setId(id);
String username = rs.getString("username");
if (username != null)
user.setUserName(username);
String nickname = rs.getString("nickname");
if (nickname != null)
user.setNickName(nickname);
String pwd = rs.getString("password");
if (pwd != null)
user.setPassword(pwd);
Date birth = rs.getDate("birth");
if (birth != null)
user.setBirth(birth);
String imgs = rs.getString("imgs");
if (imgs != null)
user.setImgs(imgs);
String genderStr = rs.getString("gender");
if (genderStr != null && genderStr.length() > 0) {
char gender = genderStr.charAt(0);
user.setGender(gender);
}
String favorite = rs.getString("favorite");
if(favorite != null)
user.setFavorite(favorite);
}
return user;
}