前言：使用版本
1.mysql使用版本：8.0.20
2.druid使用版本：1.1.22
一、创建jdbc.properties
将jdbc.properties放置于src目录下
username=root
password=root
url=jdbc:mysql://localhost:3306/book?serverTimezone=UTC&characterEncoding=utf-8&useSSL=false
driverClassName=com.mysql.cj.jdbc.Driver
initialSize=5
maxActive=10
二、编写连接池测试代码
代码如下：
import com.alibaba.druid.pool.DruidDataSource;
import com.alibaba.druid.pool.DruidDataSourceFactory;
import java.io.InputStream;
import java.sql.Connection;
import java.util.Properties;
public class JdbcUtils {
private static DruidDataSource dataSource;
static{
try {
Properties properties = new Properties();
// 读取 jdbc.properties 属性配置文件
InputStream inputStream = JdbcUtils.class.getClassLoader().getResourceAsStream("jdbc.properties");
// 从流中加载数据
properties.load(inputStream);
// 创建 数据库连接池
dataSource = (DruidDataSource) DruidDataSourceFactory.createDataSource(properties);
System.out.println(dataSource.getConnection());
} catch (Exception e) {
e.printStackTrace();
}
}
public static void main(String[] args) {
}
}
结果如下：
8月 24, 2020 12:19:38 上午 com.alibaba.druid.pool.DruidDataSource info
信息: {dataSource-1} inited
com.mysql.cj.jdbc.ConnectionImpl@41fbdac4
三、编写连接池工具类JdbcUtils
代码如下：
import com.alibaba.druid.pool.DruidDataSource;
import com.alibaba.druid.pool.DruidDataSourceFactory;
import java.io.InputStream;
import java.sql.Connection;
import java.util.Properties;
public class JdbcUtils {
private static DruidDataSource dataSource;
static{
try {
Properties properties = new Properties();
// 读取 jdbc.properties 属性配置文件
InputStream inputStream = JdbcUtils.class.getClassLoader().getResourceAsStream("jdbc.properties");
// 从流中加载数据
properties.load(inputStream);
// 创建 数据库连接池
dataSource = (DruidDataSource) DruidDataSourceFactory.createDataSource(properties);
System.out.println(dataSource.getConnection());
} catch (Exception e) {
e.printStackTrace();
}
}
/**
* 获取数据库连接池中的连接
* @return 如果返回null，说明获取连接失败
*/
public static Connection getConnection(){
Connection conn = null;
try {
conn = dataSource.getConnection();
} catch (Exception e) {
e.printStackTrace();
}
return conn;
}
/**
* 关闭连接，放回数据库连接池
* @param conn
*/
public static void close(Connection conn){
if(conn != null){
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
四、编写测试类testJdbcUtils
代码如下：
import com.atguigu.utils.JdbcUtils;
import org.junit.Test;
import java.sql.Connection;
public class JdbcUtilsTest {
@Test
public void testJdbcUtils(){
for(int i = 0; i < 100; i++) {
Connection connection = JdbcUtils.getConnection();
System.out.println(connection);
//不释放则最多只能打印jdbc.properties配置文件里maxActive=10个
JdbcUtils.close(connection);
}
}
}
结果如下：
100个
com.mysql.cj.jdbc.ConnectionImpl@587e5365
五、编写BaseDao
代码如下：
import com.atguigu.utils.JdbcUtils;
import org.apache.commons.dbutils.QueryRunner;
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()方法用于执行Insert/Update/Delete语句
* @return 如果返回-1，说明执行失败，返回其他数字表示影响的行数
*/
public int update(String sql, Object...args){
Connection connection = JdbcUtils.getConnection();
try {
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 con = JdbcUtils.getConnection();
try {
return queryRunner.query(con, sql, new BeanHandler<T>(type),args);
} catch (SQLException e) {
e.printStackTrace();
}finally {
JdbcUtils.close(con);
}
return null;
}
/**
*
* 查询返回多个JavaBean的sql语句
* @param type 返回的对象类型
* @param sql 执行的sql语句
* @param args sql对应的参数值
* @param <T> 返回的类型的泛型
* @return
*/
public <T> List<T> queryForList(Class<T> type, String sql, Object...args){
Connection con = JdbcUtils.getConnection();
try {
return queryRunner.query(con, sql, new BeanListHandler<T>(type),args);
} catch (SQLException e) {
e.printStackTrace();
}finally {
JdbcUtils.close(con);
}
return null;
}
/**
* 执行返回一行一列的sql语句
* @param sql 执行的sql语句
* @param args sql对应的参数值
* @return
*/
public Object queryForSingleValue(String sql, Object...args){
Connection con = JdbcUtils.getConnection();
try {
queryRunner.query(con, sql, new ScalarHandler(),args);
} catch (SQLException e) {
e.printStackTrace();
}finally {
JdbcUtils.close(con);
}
return null;
}
}
六、编写UserDao
代码如下：
import com.atguigu.pojo.User;
public interface UserDao {
/**
* 根据用户名查询用户信息，判断用户名是否有效
* @param username 用户名
* @return 如果返回null，说明没有这个用户
*/
public User queryUserByname(String username);
/**
* 根据用户名和密码查询用户
* @param username
* @param password
* @return 如果返回null，说明没有这个用户
*/
public User queryUserBynameAndPassword(String username, String password);
/**
* 保存用户信息
* @param user
* @return
*/
public int saveUser(User user);
}
七、编写UserDao实现类
代码如下：
import com.atguigu.dao.UserDao;
import com.atguigu.pojo.User;
public class UserDaoImpl extends BaseDao implements UserDao {
@Override
public User queryUserByname(String username) {
String sql = "select 'id','username','password','email' from t_user where username = ?";
return queryForOne(User.class,sql,username);
}
@Override
public User queryUserBynameAndPassword(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());
}
}
八、测试UserDao
UserDao文件中，按住Ctrl+Shift+T，创建测试类UserDaoTest
代码如下：
import com.atguigu.dao.UserDao;
import com.atguigu.dao.impl.UserDaoImpl;
import com.atguigu.pojo.User;
import org.junit.Test;
import static org.junit.Assert.*;
public class UserDaoTest {
UserDao userDao = new UserDaoImpl();
@Test
public void queryUserByname() {
if(userDao.queryUserByname("admin") == null){
System.out.println("用户名可用！");
}else{
System.out.println("用户名已存在！");
};
}
@Test
public void queryUserBynameAndPassword() {
if(userDao.queryUserBynameAndPassword("admin","admin") == null){
System.out.println("登录失败");
}else{
System.out.println("登录成功");
}
}
@Test
public void saveUser() {
System.out.println(userDao.saveUser(new User(null,"abc","admin","123@qq.com")));
}
}
九、编写UserService
代码如下：
import com.atguigu.pojo.User;
public interface UserService {
/**
* 注册用户
* @param user
*/
public void registUser(User user);
/**
* 用户登录
* @param user
* @return
*/
public User login(User user);
/**
* 用户名是否可用
* @param username
* @return 返回true表示用户名存在，返回false表示用户名可用
*/
public boolean existsUsername(String username);
}
十、编写UserServiceImpl
import com.atguigu.dao.UserDao;
import com.atguigu.dao.impl.UserDaoImpl;
import com.atguigu.pojo.User;
import com.atguigu.service.UserService;
public class UserServiceImpl implements UserService {
private UserDao userDao = new UserDaoImpl();
@Override
public void registUser(User user) {
userDao.saveUser(user);
}
@Override
public User login(User user) {
return userDao.queryUserBynameAndPassword(user.getUsername(),user.getPassword());
}
@Override
public boolean existsUsername(String username) {
if (userDao.queryUserByname(username) == null){
return false;
}
return true;
}
}
十一、测试UserServiceImpl
import com.atguigu.pojo.User;
import com.atguigu.service.UserService;
import com.atguigu.service.impl.UserServiceImpl;
import org.junit.Test;
import static org.junit.Assert.*;
public class UserServiceTest {
UserService userService = new UserServiceImpl();
@Test
public void registUser() {
userService.registUser(new User(null,"bbj168","666","bbj168@qq.com"));
}
@Test
public void login() {
System.out.println(userService.login(new User(null,"admin","admin",null)));
}
@Test
public void existsUsername() {
if (userService.existsUsername("admin")){
System.out.println("用户名已存在");
}else {
System.out.println("用户名可用");
}
}
}
十二、实现用户注册功能
import com.atguigu.pojo.User;
import com.atguigu.service.UserService;
import com.atguigu.service.impl.UserServiceImpl;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
public class RegistServlet extends HttpServlet {
private UserService userService = new UserServiceImpl();
@Override
protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
// 1.获取请求参数
String username = req.getParameter("username");
String password = req.getParameter("password");
String email = req.getParameter("email");
String code = req.getParameter("code");
// 2.检查验证码是否正确
if ("abcde".equalsIgnoreCase(code)){
// 3.检查用户名是否可用
if(userService.existsUsername(username)){
// 不可用
System.out.println("用户名已存在");
// 跳回注册页面
req.getRequestDispatcher("/pages/user/regist.html").forward(req,resp);
}else{
// 注册成功，保存到数据库
userService.registUser(new User(null,username,password,email));
// 跳转注册成功页面
req.getRequestDispatcher("/pages/user/regist_success.html").forward(req,resp);
}
}else{
// 跳回注册页面
System.out.println("验证码错误");
req.getRequestDispatcher("/pages/user/regist.html").forward(req,resp);
}
}
}
十三、实现用户登录功能
import com.atguigu.pojo.User;
import com.atguigu.service.UserService;
import com.atguigu.service.impl.UserServiceImpl;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
public class LoginServlet extends HttpServlet {
private UserService userService = new UserServiceImpl();
@Override
protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
// 1.获取请求参数
String username = req.getParameter("username");
String password = req.getParameter("password");
// 2.调用userServic.login()登录处理业务
User loginUser = userService.login(new User(null, username, password, null));
if (loginUser == null){
// 登录失败，跳回login页面
req.getRequestDispatcher("/pages/user/login.html").forward(req,resp);
}else{
// 登陆成功，跳转到login_success页面
req.getRequestDispatcher("/pages/user/login_success.html").forward(req,resp);
}
}
}
1595
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
