JavaWeb Dao层编写实现操作（增删改查）mysql数据库

          

1.首先准备一个JavaBean对象

这里我以User为例：

package Bean;

public class User {
    private Integer Id;
    private String UserName;
    private String PassWord;
    private String Email;

    public User(Integer id, String userName, String passWord, String email) {
        Id = id;
        UserName = userName;
        PassWord = passWord;
        Email = email;
    }

    public User() {
    }

    @Override
    public String toString() {
        return "User{" +
                "Id=" + Id +
                ", UserName='" + UserName + '\'' +
                ", PassWord=" + PassWord +
                ", Email='" + Email + '\'' +
                '}';
    }

    public Integer getId() {
        return Id;
    }

    public void setId(Integer id) {
        Id = id;
    }

    public String getUserName() {
        return UserName;
    }

    public void setUserName(String userName) {
        UserName = userName;
    }

    public String getPassWord() {
        return PassWord;
    }

    public void setPassWord(String passWord) {
        PassWord = passWord;
    }

    public String getEmail() {
        return Email;
    }

    public void setEmail(String email) {
        Email = email;
    }
}

2.编写配置文件

准备配置jdbc.properties文件并编写

url中，3306端口后面那个跟的是要操作的库，我这里是操作的是book库下的users表，固定位到book库。

：

 

3.导入jar包

所需要jar包有:

4.编写JdbcUtils类(实现获取连接、关闭连接)

package Utils;

import com.alibaba.druid.pool.DruidDataSource;
import com.alibaba.druid.pool.DruidDataSourceFactory;

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 stream = JdbcUtils.class.getClassLoader().getResourceAsStream("jdbc.properties");
            properties.load(stream);
            //创建连接池
             dataSource =(DruidDataSource ) DruidDataSourceFactory.createDataSource(properties);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    //获取连接
    public static Connection getConnection(){
        Connection connection =null;
        try {
             connection =  dataSource.getConnection();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return connection;
    }

    //关闭连接
    public static void close(Connection connection) {
        if (connection != null) {
            try {
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

5.编写BaseDao类实现通用基础操作

package Dao;

import 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 class BaseDao {
    QueryRunner queryRunner = new QueryRunner();

    public int unpdate(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;
    }

    public <T> T queryForOne(Class<T> type,String sql,Object...args) {
        Connection connection = JdbcUtils.getConnection();
        try {
            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 = JdbcUtils.getConnection();
        try {
            return  queryRunner.query(connection,sql,new BeanListHandler<T>(type),args);
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JdbcUtils.close(connection);
        }
        return null;

    }

    public Object queryForSingleList(String sql,Object...args){
        Connection connection = JdbcUtils.getConnection();
        try {
            return queryRunner.query(sql,new ScalarHandler(),args);
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JdbcUtils.close(connection);
        }
        return null;
    }
}

6.编写UserDao实现操作数据库中的Users表

package Dao;

import Bean.User;

import java.util.List;

public class UserDao extends BaseDao {
    //增
    public int addUser(User user){
        String sql = "insert into users(`username`,`password`,`email`)values(?,?,?);";
        return update(sql,user.getUserName(),user.getPassWord(),user.getEmail());
    }

    //删
    public int deleteUser(Integer id){
        String sql = "delete from Users where id = ? ";
        return update(sql,id);
    }


    //改
    public int update(User user){
        String sql = "update Users set `UserName`=?,`PassWord`=?,`Email`=? where `id`= ?";
        return update(sql,user.getUserName(),user.getPassWord(),user.getEmail(),user.getId());
    }

    //查
    public User queryById(Integer id){
        String sql = "select `Id`,`UserName`,`Password`,`Email` from Users Where `id` = ?";
        return queryById(id);
    }

    //查多条

    public List<User> queryUsers(){
        String sql = "select `Id`,`UserName`,`Password`,`Email` from Users";
        return queryForList(User.class,sql);
    }

}

 7.测试

以下增删改查我通过单元测试都没问题

package Test;

import Bean.User;
import Dao.UserDao;
import org.junit.Test;

import static org.junit.Assert.*;

public class UserDaoTest {
    private UserDao userDao = new UserDao();
    @Test
    public void addUser() {
        userDao.addUser(new User(null,"喜羊羊","123456","1234@qq.com"));
    }

    @Test
    public void deleteUser() {
        userDao.deleteUser(1);
    }

    @Test
    public void update() {
    }

    @Test
    public void queryById() {
    }

    @Test
    public void queryUsers() {
    }

8.总结

这里只是提供大致思路，具体实现还要看实际项目需求!!!!!!!