《使用dbutils操作数据库》

要引入jar包

 

BaseDao

package com.guigu.dao.impl;

import com.guigu.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();
    /**
     * 增删改通用方法
     * */
    public int update(String sql,Object...args){
        Connection conn = JdbcUtils.getConnection();
        try {
            return queryRunner.update(conn,sql,args);
        }catch (SQLException e){
            e.printStackTrace();
        }finally {
            JdbcUtils.close(conn);
        }
        return -1;
    }
    /**
     * 查询返回一个JavaBean的sql语句
     */
    public <T> T queryForOne(Class<T> type,String sql,Object...args){
        Connection conn = JdbcUtils.getConnection();
        try {
            return queryRunner.query(conn,sql,new BeanHandler<T>(type),args);
        }catch (SQLException e){
            e.printStackTrace();
        }finally {
            JdbcUtils.close(conn);
        }
        return null;
    }

    /**
     * 返回多哦个javaBean的sql语句
     * @param type
     * @param sql
     * @param args
     * @param <T>
     * @return
     */
    public <T> List<T> queryForList(Class<T> type, String sql, Object...args){
        Connection conn = JdbcUtils.getConnection();
        try {
            return queryRunner.query(conn,sql,new BeanListHandler<T>(type),args);
        }catch (SQLException e){
            e.printStackTrace();
        }finally {
            JdbcUtils.close(conn);
        }
        return null;
    }
    /**
     * 查询返回单个值的情况
     */
    public Object queryForSingleValue(String sql,Object...args){
        Connection conn =  JdbcUtils.getConnection();
        try {
            return queryRunner.query(conn,sql,new ScalarHandler(),args);
        }catch (SQLException e){
            e.printStackTrace();
        }finally {
            JdbcUtils.close(conn);
        }
        return null;
    }
}

UsereDao

package com.guigu.dao;

import com.guigu.pojo.User;

public interface UserDao {
//    根据用户名查找用户
    public User queryUserByUsername(String username);
//    根据用户名和密码查询用户信息
    public User queryUserByUsernameAndPassword(String username,String password);
//    保存用户信息
    public int saveUser(User user);
}

UserDaoImpl

package com.guigu.dao.impl;

import com.guigu.dao.UserDao;
import com.guigu.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());
    }
}

UserDaoTest

package com.guigu.test;
import com.guigu.dao.UserDao;
import com.guigu.dao.impl.UserDaoImpl;
import com.guigu.pojo.User;

public class UserDaoTest {
    public void queryUserByName(){
        UserDao userDao = new UserDaoImpl();
        if(userDao.queryUserByUsername("zhangsan") == null){
            System.out.println("用户名没注册过，可用");
        }else {
            System.out.println("用户名已存在");
        }
    }
    public void queryUserByNameAndPsw(){
        UserDao userDao = new UserDaoImpl();
        if(userDao.queryUserByUsernameAndPassword("zhangsan","123456") == null) {
            System.out.println("用户名或密码错误，登陆失败！");
        }else {
            System.out.println("登录成功");
        }

    }
    public void saveUser(){
        UserDao userDao = new UserDaoImpl();
        User user = new User("lisi","123456","2773872239@qq.com");
        System.out.println(userDao.saveUser(user));
    }
    public static void main(String[] args) {
        UserDaoTest userDaoTest = new UserDaoTest();
        userDaoTest.saveUser();
    }
}

UserService

package com.guigu.service;

import com.guigu.pojo.User;

public interface UserService {
//    注册用户
    public void registUser(User user);
//    登录
    public User login(User user);
//    检查用户名是否可用
    public Boolean existUsername(String name);
}

UserServiceImpl

package com.guigu.service.impl;

import com.guigu.dao.UserDao;
import com.guigu.dao.impl.UserDaoImpl;
import com.guigu.pojo.User;
import com.guigu.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.queryUserByUsernameAndPassword(user.getUsername(),user.getPassword());
    }

    @Override
    public Boolean existUsername(String name) {
        if (userDao.queryUserByUsername(name) == null) {
            return false;
        }
        return true;
    }
}

UserServiceTest

package com.guigu.test;

import com.guigu.pojo.User;
import com.guigu.service.UserService;
import com.guigu.service.impl.UserServiceimpl;
import org.junit.Test;

class UserServiceimplTest {
    UserService userService = new UserServiceimpl();
    @Test
    public void registUser() {
        User user = new User("wangwu","123456","1234wangwu@qq.com");
        userService.registUser(user);
    }

    @Test
    public void login() {
        User user = new User("wangwu","123456","1234wangwu@qq.com");
        System.out.println(userService.login(user));
    }

    @Test
    public void existUsername() {
        System.out.println(userService.existUsername("zhangsan"));
    }
}