书城项目
创建数据库表
CREATE DATABASE book; USE book; CREATE TABLE t_user( `id` INT PRIMARY KEY AUTO_INCREMENT, `username` VARCHAR(20) NOT NULL UNIQUE, `password` VARCHAR(32) NOT NULL, `email` VARCHAR(200) ); INSERT INTO t_user(`username`,`password`,`email`) VALUES('admin','admin','admin@atguifu.com'); SELECT * FROM t_user;
编写数据库表对应的JavaBean对象
package com.atguigu.pojo; public class User { private Integer id; private String username; private String password; private String email; public Integer getId() { return id; } public void setId(Integer 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; } @Override public String toString() { return "User{" + "id=" + id + ", username='" + username + '\'' + ", password='" + password + '\'' + ", email='" + email + '\'' + '}'; } public User() { } public User(Integer id, String username, String password, String email) { this.id = id; this.username = username; this.password = password; this.email = email; } }编写Dao持久层
编写工具类JdbcUtils
package com.atguigu.utils; import com.alibaba.druid.pool.DruidDataSource; import com.alibaba.druid.pool.DruidDataSourceFactory; import com.alibaba.druid.pool.DruidPooledConnection; import java.io.InputStream; import java.sql.Connection; import java.sql.SQLException; import java.util.Properties; public class JdbcUtils { private static DruidDataSource dataSource; static { try { Properties properties = new Properties(); //读取配置文件 InputStream inputStream = JdbcUtils.class.getClassLoader().getResourceAsStream("jdbc" + ".properties"); //从流中加载数据 properties.load(inputStream); //创建数据库连接池 dataSource = (DruidDataSource) DruidDataSourceFactory.createDataSource(properties);
} catch (Exception e) {
e.printStackTrace();
}
}
/**
* 获取数据库连接池中的连接
*
* @return 如果返回null, 说明链接失败
*/
public static Connection getConnection() throws SQLException {
Connection conn = null;
conn = dataSource.getConnection();
return conn;
}
/**
* 关闭连接,放回数据库连接池
*
* @param conn
*/
public static void close(Connection conn){
if (conn != null) {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
```编写BaseDao (abstract, 用来继承)
package com.atguigu.dao.impl; import com.atguigu.utils.JdbcUtils; import org.apache.commons.dbutils.QueryRunner; import org.apache.commons.dbutils.ResultSetHandler; import org.apache.commons.dbutils.handlers.BeanHandler; import org.apache.commons.dbutils.handlers.BeanListHandler; import org.apache.commons.dbutils.handlers.ScalarHandler; import java.sql.Connection; import java.sql.SQLException; import java.util.List;
public abstract class BaseDao {
//使用dbUtils操作数据库
private QueryRunner queryRunner = new QueryRunner();
/**
* update()用来执行增删改操作
*
* @return 如果返回-1说明执行失败
*/
public int update(String sql, Object... args) {
Connection connection = null;
try {
connection = JdbcUtils.getConnection();
return queryRunner.update(connection, sql, args);
} catch (SQLException e) {
e.printStackTrace();
} finally {
JdbcUtils.close(connection);
}
return -1;
}
/**
* 查询返回一个javaBean的sql语句
*
* @param type 返回的对象类型
* @param sql 执行的sql语句
* @param args sql对应的参数值,占位符对应的那些
* @param <T> 返回的类型的泛型
* @return
*/
public <T> T queryForOne(Class<T> type, String sql, Object... args) {
Connection connection = null;
try {
connection = JdbcUtils.getConnection();
return queryRunner.query(connection, sql, new BeanHandler<T>(type), args);
} catch (SQLException e) {
e.printStackTrace();
} finally {
JdbcUtils.close(connection);
}
return null;
}
public <T> List<T> queryForList(Class<T> type, String sql, Object... args) {
Connection connection = null;
try {
connection = JdbcUtils.getConnection();
return queryRunner.query(connection, sql, new BeanListHandler<T>(type), args);
} catch (SQLException e) {
e.printStackTrace();
} finally {
JdbcUtils.close(connection);
}
return null;
}
/**
* 执行返回一行一列的sql语句
*
* @param sql
* @param args
* @return
*/
public Object queryForSingleValue(String sql, Object... args) {
Connection connection = null;
try {
connection = JdbcUtils.getConnection();
return queryRunner.query(connection, sql, new ScalarHandler(), args);
} catch (SQLException e) {
e.printStackTrace();
} finally {
JdbcUtils.close(connection);
}
return null;
}
}
```编写userDao接口, 然后用userDaoImpl继承BaseDao并实现userDao接口
userDao接口
package com.atguigu.dao; import com.atguigu.pojo.User; //取决于我们要实现的功能,也就是用户要执行的操作 public interface UserDao { /** * 根据用户名查询用户信息 * * @param username * @return 如果返回null则说明用户名不存在 */ public User queryUserByUsername(String username); /** * 根据用户名和密码查询用户信息 * * @param username * @param password * @return 如果返回null则说明用户名不存在 */ public User queryUserByUsernameAndPassword(String username, String password); /** * 保存用户信息 * * @param user * @return 如果返回-1,则表示用户名已存在,注册失败,否则注册成功 */ public int saveUser(User user);
}
```
2. userDaoImpl实现类
```java
package com.atguigu.dao.impl;
import com.atguigu.dao.UserDao;
import com.atguigu.pojo.User;
public class UserDaoImpl extends BaseDao implements UserDao {
@Override
public User queryUserByUsername(String username) {
String sql = "select `id`,`username`,`password`,`email` from t_user where username = ?";
return queryForOne(User.class, sql, username);
}
@Override
public User queryUserByUsernameAndPassword(String username, String password) {
String sql = "select `id`,`username`,`password`,`email` from t_user where username = ? and password = ?";
return queryForOne(User.class, sql, username, password);
}
@Override
public int saveUser(User user) {
String sql = "insert into t_user(`username`,`password`,`email`)values(?,?,?)";
return update(sql, user.getUsername(), user.getPassword(), user.getEmail());
}
}
```编写UserService
编写web层
